Buscar en este blog

LinkWithin

Related Posts with Thumbnails

13 de noviembre de 2019

Uso de la Función Indice

Veremos como utilizar la funcíón INDICE, COINCIDIR y el Formato Condicional de Fórmula

El usuario quiere que al seleccionar un Estado y un mes de Enero a Junio en la celda F2 se visualice el importe correspondiente al Estado y mes en cuestión.

Para ello utilicé la funcion INDICE la cual me devuelve el valor de una referencia de fila y columna en una matriz dada.

 Descargar el archivo en Excel desde este enlace



Solución (ver celda F2)
Sintaxis: INDICE(matriz; núm_fila; [núm_columna]) En forma de Matriz

=INDICE(C6:H37,COINCIDIR(C2,B6:B37,0),COINCIDIR(C3,C5:H5,0))

Matriz (obligatorio): En este caso es el rango C6:H37 el cual corresponde a todos los valores de las sucursales de los meses de enero a junio.

núm_fila (obligatorio) : Número de fila en la matriz (no la hoja) del valor que queremos.
COINCIDIR(C2,B6:B37,0) La función COINCIDIR devuelve la posición relativa de un valor buscado en una matriz, en este caso se desea saber que posición tiene el Estado (C2) en la lista de Estados (B6:B37), el cero quiere decir encontrar el primer valor que es exactamente igual que el valor_buscado.

[núm_columna] (opcional): Número de columna en la matriz (no la hoja) del valor que queremos.
COINCIDIR(C3,C5:H5,0) Lo mismo que en núm_fila pero esta vez se necesita la posición relativa del mes en la lista de meses C5:H5, el cero se utiliza para encontrar el primer valor que es exactamente igual que el valor_buscado.

Formato condicional
Para que el número correspondiente en la matriz se vea de un color de manera automática se utilizó el formato condicional de fórmula.

  1. Selecciona el rango de celdas C6:H37
  2. Clic en Inicio > Formato condicional y en Editar una descripción de regla poner la siguiente fórmula =Y(C$5=$C$3,$B6=$C$2)
  3. Clic en el botón "Formato" y selecciona el formato que deseas.
  4. Clic en aceptar en las ventanas que aparecen (deben ser 3) y listo.




El archivo contiene una segunda opción en la cual se utiliza el formato condicional. Ver imagen



Ver calendario de cursos de Excel en Mérida Yucatán



11 de noviembre de 2019

Cómo fijar Filas y Columnas en Excel

En este artículo se explicará la referencia a celdas las cuales pueden ser Absolutas y Relativas:

Celdas Relativas
Una celda relativa es aquella que guarda una relación a al Columna y Fila a la cual está vinculada y si esta se copia a otra celda esta relación se modifica.

En la siguiente imagen se puede ver que la celda D6 tiene la fórmula C6*5%, si copiamos esta fórmula hacia abajo la relación tambien cambia, C7*5%, C8*5%.



No siempre las celdas deben ser relativas, en ocasiones necesitamos que una referencia esté fija.

Formula A
En la celda D6 se ingresó la fórmula =C6*C3, es la misma operación que el ejemplo anterior pero ahora en vez de utilizar el valor 5% directamente en la fórmula se toma el valor de la celda C3 que tiene el mismo porcentaje. Si copiamos la fórmula hacia abajo la relación cambiaría y daría un error.

Formula B
En la celda H6 se ingresó la fórmula =G6*G$3, (G$3 tiene el valor 5%). Si copiamos la fórmula hacia abajo la relación cambiaa  =G7*G$3 , =G8*G$3 y subsecuente, pero se puede ver que G$3 nunca cambia. Esto sucede porque la fila 3 de G$3 está fija con el símbolo $.  


Celdas Mixtas
Son aquellas que solo está fija la columna o la fila pero no ambas =G6*G$3, G$3 tiene fija la fila pero no la columna G.

Celdas Absolutas
Son aquellas celdas que está fija la celda y la columna. En el ejemplo de la imagen la fórmula
=($C4*D$3)*$C$1 tiene referencias mixtas y absolutas. $C$1 es una referencia absoluta pues tiene fija la columna y la fila.





Este tema y otros son tratados en el curso de Excel Básico impartido en Mérida Yucatán, solicita informes.


8 de noviembre de 2019

Obtener Denominaciones de un Importe

En este caso se necesita obtener el número de denominaciones que tiene un pago. Para ello he creado un modelo en Excel en el cual solo necesitas escribir una cantidad sin centavos y el modelo te calculará el número de denominaciones de mayor a menor.

Notas previas:

  • El modelo solo acepta números sin decimales
  • Se tomaron las denominaciones existentes en México pero puede ser adaptado a cualquier país ingresando las denominaciones de mayor a menor de izquierda a derecha en la fila 3.



Solución al Pago 1 (Fila 4 de la hoja) 
  1. Celda C4 =ENTERO(B4/C$3) El importe se divide entre 500 lo que da un importe de 27.544 pero como esta operación se encuentra dentro de la función ENTERO la cual obtiene de un valor dado el valor entero quedando un importe de 27.
  2. Celda D4 =ENTERO(($B4-SUMAPRODUCTO($C4,$C$3))/D$3), esta vez vamos por partes. Primero al importe del "Pago 1" (B4) se le resta la suma de la multiplicación del valor  de la celda C4 por la denominación de 500, con esto obtenemos el saldo que queda por obtener su denominación. El resultado antes mencionado se divide entre la denominación de 200 ( resultado 1.36), a todo el resultado se le saca su parte entera utilizando la función ENTERO.
  3. Celda E4 =ENTERO(($B4-SUMAPRODUCTO($C4:D4,$C$3:D$3))/E$3). Esta funció es la misma que la anterior con el detalle que se amplia el rango de los argumentos de la función SUMAPRODUCTO. Con esto solo resta copiarla hasta la columna K.
  4. Para copiar toda la fila para los otros pagos selecciona el rango C4:K4, copialo y pegalo en las filas restantes.
Columna L de cuadre,
Se ingresa la función para cuadre =SUMAPRODUCTO(C4:K4,$C$3:$K$3)