Imagina que tenemos unas cifras de ventas para una empresa:
Y que deseamos encontrar las cifras totales para el año hasta la fecha. Podemos agregar un menú desplegable así:
Para que podamos especificar el mes actual. Por lo tanto, ahora queremos calcular el año hasta la fecha de marzo. El formato más simple sería tener fórmulas que se extendieran por todo el rango:
Y luego simplemente cambiamos las fórmulas todos los meses.
Sin embargo, Excel permite otro enfoque. Podríamos configurar un rango dinámico cuyo tamaño varíe según el mes en el que nos encontremos. A medida que cambiamos el mes en el menú desplegable, el tamaño del rango cambia.
Entonces, para el mes de marzo, el rango es de 3 columnas y para el mes de junio sería de 6 meses.
El tamaño del rango se rige por el mes. Una forma de formular esto es usar la función Mes:
= Mes (c8)
Donde c8 es la dirección de celda de nuestro menú desplegable. Sin embargo, el método que se prefiere es utilizar la función COINCIDIR para determinar la posición de los meses actuales en todos los meses de nuestro informe:
COINCIDIR (c8, $ c $ 3: $ j $ 3,0)
Dónde:
• c8 es la dirección de celda del mes actual
• C3: J3 es la dirección de todos nuestros meses.
• 0 es para asegurar una coincidencia exacta
Ahora podemos especificar el tamaño de nuestro rango dinámico mediante la función OFFSET que tiene 5 argumentos:
= DESPLAZAMIENTO (referencia, filas, columnas, alto, ancho)
Dónde:
• La referencia es la esquina superior izquierda de nuestro rango dinámico - celda C5 - la primera celda que queremos sumar
• Filas: el número de filas hacia abajo desde nuestra celda base; esto es 0
• Cols: el número de cols frente a nuestra llamada base: este es 0
• El ancho de nuestro rango dinámico, que es 3 en este caso. Sin embargo, como deseamos que el rango varíe según el mes, pondremos nuestras fórmulas MATCH aquí.
• Esta es la altura de nuestro rango dinámico que es 1
Entonces nuestras fórmulas OFFSET son:
= COMPENSACIÓN (c5,0,0, COINCIDIR (c8, $ c $ 3: $ j $ 3,0), 1)
Finalmente, necesitamos decirle a Excel que SUMA esto para dar las fórmulas completas como:
= SUMA (COMPENSACIÓN (c5,0,0, COINCIDIR (c8, $ c $ 3: $ j $ 3,0), 1))
Tenemos:
Ahora, si cambiamos el mes en el menú desplegable, la cifra correcta del año hasta la fecha fluye a través de:
Como se trata de una actualización automática, este enfoque tiene las siguientes ventajas:
• No es necesario cambiar las fórmulas todos los meses
• Como hay menos cambios de fórmulas, menos margen de error
• La hoja de cálculo puede ser utilizada por alguien que tenga conocimientos limitados de Excel; simplemente puede cambiar el menú desplegable y no molestarse con las fórmulas.
