La combinación de poder
Combinando las fórmulas SUMIF y OFFSET, una lista de validación y un cuadro combinado para devolver un resumen de los datos que se seleccionarán por mes, ¡una verdadera combinación de poder!
Paso 1: Seleccionar elementos de las listas de validación
Seleccionar un elemento de una lista de validación (columna A en la captura de pantalla)
permite que las fórmulas ingresadas en las celdas de las columnas C y D identifiquen el texto y devuelvan los resultados resumidos de una columna que contiene los criterios (el elemento elegido) para el mes seleccionado en el cuadro combinado.
Para agregar una lista de validación a un rango de celdas:
-
Seleccione el rango de celdas (en la captura de pantalla de la página anterior, las celdas seleccionadas son A12: A15)
-
Seleccione las celdas A19: A23.
-
En el menú Datos, seleccione Validación.
-
En el cuadro de diálogo Validación de datos, seleccione la pestaña Configuración y luego seleccione Lista en el cuadro Permitir.
-
En el cuadro Fuente, presione F3, seleccione el Nombre definido para la lista (Nivel3 en este Ejemplo, vea la captura de pantalla en la última página para este consejo) y haga clic en Aceptar.
Paso 2: Ingresar fórmulas que devuelvan los saldos resumidos de los elementos seleccionados La fórmula en las celdas C12: C15 es:
SUMIF (Nivel de datos3, A12, DESPLAZAMIENTO (Nivel de datos3,0, Número de selección de mes + 2))
La fórmula en las celdas D12: D15 es:
SUMAIF (Nivel de datos3, A12, DESPLAZAMIENTO (Nivel de datos3,0, Número de selección de mes + 2-12))
Explicación:
La fórmula SUMIF en la columna C resume los saldos de la columna de diciembre de 2003; la fórmula SUMIF en la columna D resume los saldos de la columna de diciembre de 2002.
La fórmula SUMIF tiene tres argumentos:
Primer argumento:
El rango a evaluar de acuerdo con los criterios ingresados en el segundo argumento de la fórmula SUMIF. En este ejemplo, el rango es DataLevel3, que es el Nombre definido para la columna C en la base de datos del Balance. Contiene elementos de nivel 3 del balance, como efectivo, cuentas por cobrar, inventarios, etc.
Segundo argumento:
El criterio es el elemento elegido de la lista de validación Nivel 3.
Tercer argumento:
La columna de la que se resumirán los datos. Esto se elegirá de acuerdo con la fórmula OFFSET para la columna del mes, que se ajusta por el número seleccionado de MonthsList en el Combo Box. La fórmula OFFSET permite desviar el mes seleccionado de la columna base (columna C en la captura de pantalla a continuación).
Cómo funciona la fórmula OFFSET
La columna 29 es el número de columna de diciembre de 2003 y el número de columna de diciembre de 2002 es 17, que son 12 columnas antes (consulte la captura de pantalla a continuación).
Cómo cambiar los títulos de los encabezados en una hoja de caracteres a números: {vacío} 1. En el menú Herramientas, seleccione Opciones.
-
En la pestaña General, marque el estilo de referencia R1C1.
Cuando se selecciona diciembre de 2003 de la lista desplegable del cuadro combinado (lista de meses), el número de mes en esa lista es 24 (esto se calcula determinando el número de meses entre enero de 2002 y diciembre de 2003:
2 años * 12 meses = 24). La celda vinculada al cuadro combinado recibe el valor de 24.
En la hoja de datos, la columna 3 es la columna base que la fórmula SUMIF evalúa para los criterios del segundo argumento de la fórmula SUMIF.
En este caso, diciembre de 2003 se distancia de la columna C en 24+ 2 = 26 (2 = Columna D y Columna E).
En el tercer argumento, Sum_range debe estar a 26 columnas de distancia de la columna base. La fórmula OFFSET devuelve el resultado de 26 y hace que la fórmula SUMIF resuma las cifras de la columna de diciembre de 2003.