Buscar en este blog

LinkWithin

Related Posts with Thumbnails

17 de noviembre de 2019

Auditoría de Fórmulas en Excel - Relación entre celdas


Auditoría de Fórmulas en Excel utilizando las flechas de relación entre celdas.

En ocasiones es necesario analizar o auditar algunas fórmulas o funciones, ya sea con el fin de verificar su correcta aplicación o para entender como funcionan.

Para poder ver la relación que existe entre las celdas podemos utilizar las herramientas de Auditoría de Formulas que se encuentra en la pestaña Fórmulas.

El grupo de Auditoría de Fórmulas
Rastrear precedentes.- Nos muestras mediante una flecha la celda o celdas que afectan la celda actual seleccionada.
Rastrear dependientes.- Muestra mediante una flecha las celdas que se ven afectadas por la celda actualmente seleccionada.
Quitar Flechas.- Elimina las flechas pudiendo eliminar en un orden de precedencia de dependencia o todas a la vez.


Rastrear precedentes; Ejemplo
Selecciona la celda I4 y haz clic en el botón de Rastrear precedentes que se encuentra en el grupo de Auditoría de Fórmulas de la pestaña Fórmulas. 
Podrás apreciar como se despliegan las flechas donde los puntos de inicio son las celdas C4  y F8 la punta de flecha se encuentra en la celda I4 previamente seleccionada.


Cuando la celda precedente se encuentra en otra hoja u otro libro de Excel se verá de la siguiente manera, veremos que la flecha apunta a un ícono de hoja de cálculo, lo que significa que el dato precedente se encuentra en otra hoja del libro.

Hoja Total


Rastrear dependientes; Ejemplo
Selecciona la celda F10 y haz clic en el botón de Rastrear dependientes que se encuentra en el grupo de Auditoría de Fórmulas de la pestaña Fórmulas. 
Podrás apreciar como se despliegan las flechas donde los puntos de inicio son las celdas F10  y la punta de flecha se encuentra en la celda I6 con lo que indica que el valor de la celda F10 alimenta la celda F6.


Cuando la celda dependiente se encuentra en otra hoja u otro libro de Excel se verá de la siguiente manera, veremos que la flecha apunta a un ícono de hoja de cálculo, lo que significa que el dato dependiente se encuentra en otra hoja del libro. El número 12 de la celda C3 alimenta a una celda fuera del la hoja actual.

Hoja Precio


¿Y como ver la hoja externa precedente o dependiente?
Para ello solo seleccionamos con el puntero alguna parte de la flecha y hacemos doble clic con ello se despliega la siguiente venta flotante.

La Hoja Precio Precede a la Hoja Total

Te invito a descargar el archivo de abajo y practicas para aprender más.

Quitar Flechas
Para quitar flechas solo haz clic en el botón Quitar flechas que se encuentra en el grupo de Auditoría de Fórmulas de la pestaña Fórmulas.


Descarga aquí el archivo de prácticas


En el siguiente artículo veremos como utilizar las demás herramientas de Auditoría de Fórmulas.

Si te ha gustado el artículo compártelo con los demás, haciendo clic en las herramientas de redes sociales del blog.

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.