Supongamos que tenemos las ventas anuales, con detalle mensual de una serie de productos (en nuestro ejemplo utilizaremos 10 para hacerlo más visual pero, como suelo decir, me da igual que sean 10 que 800 productos: la solución es la misma). Lo que queremos hacer es ser capaces de sumar las ventas acumuladas del producto que elijamos entre los meses que elijamos. Para ello utilizaremos, entre otras, la función DESREF y cuadros combinados. Nuestros datos de partida (productos y ventas) son los que se muestran en la siguiente tabla:
Nuestra entrada de datos la diseñamos en las celdas que se muestran en la imagen, donde B2 será el producto que queremos analizar; B3 el mes inicial de la suma; B4 el mes final de la suma; y de la siguiente manera,y B6 el resultado final:
1. Lo primero que tenemos que hacer es generar una lista con los meses del año que utilizaremos más adelante. Lo hacemos en el rango, por ejemplo B22:B33 (en realidad, todo este tipo de tablas y operaciones intermedias necesarias para alguna función y/o herramienta se suelen poner en una zona apartada de la hoja y no visible). Es necesario indicar que no podemos utilizar para nuestra lista de meses el rango C9:N9 porque la herramienta que usaremos (cuadro combinado) no permite que los orígenes de las listas se encuentren en disposición horizontal.
2. A continuación vamos a gener nuestros cuadros combinados. Para ello vamos al menú Ver/Barras de herramientas/Formularios y hacemos un clic encima de cuadros combinados, como se observa en la imagen:
3. Nos situamos en la hoja a la altura de las celda C2 y hacemos clic con el botón izquierdo del ratón y sin soltar el clic arrastramos hacia la derecha para dibujar nuestro cuadro combinado:
4. Una vez dibujado le hacemos clic encima con el botón DERECHO del ratón y del menú emergente seleccionamos la opción Formato de control. En la pestaña Control nos situamos en el recuadro Rango de entrada y seleccionamos el rango B10:B19, que es donde se encuentran los productos que queremos que aparezcan en nuestra lista. En el recuadro Vincular con la celda seleccionamos B2. Y finalmente en el recuadro Lineas de unión verticales escribimos 10. Este último parámetro determina cuántos productos podremos ver a la vez (sin necesidad de utilizar la barra de desplazamiento de nuestro cuadro combinado) cuando despleguemos la lista del cuadro combinado. Pulsamos Aceptar.
A partir de este momento ya tenemos nuestro cuadro combinado generado (si despliega la lista verá los distintos productos) y vinculado con la celda B2. ES IMPORTANTE destacar las diferencias de estos cuadros combinados con las listas desplegables que podemos generar con Validación de Datos. Los cuadros combinados son fijos, es decir, si los coloca encima de una celda, como haremos, siempre estarán visibles, mientras que las listas de Validación de Datos sólo se activan cuando hacemos clic en la celda en la que se encuentran. Pero la diferencia quizas más importante es que cuadros combinados trabaja con números de orden. Puede comprobar que si selecciona uno de los productos de la lista en la celda B2 pondrá NO el nombre del producto sino el número de orden que ocupa en la lista (a diferencia de Validación de Datos que pone exactamente el nombre del elemento seleccionado de la lista). En nuestro caso esta diferencia nos permitirá formular directamente con la función DESREF como veremos enseguida.
5. Ahora tenemos que generar los dos cuadros combinados referentes a los meses. Seguimos el mismo proceso para dibujarlos que en el paso anterior y, una vez abierto el formato de control en la pestaña Control, nos situamos en el recuadro Rango de entrada y seleccionamos el rango B22:B33, que es donde tenemos los nombres de los meses (este rango será el mismo para los dos cuadros combinados que hemos dibujado). En el recuadro Vincular con la celda seleccionamos B3 para uno de los cuadros combinados y B4 para el otro. Y finalmente en el recuadro Lineas de unión verticales escribimos 12.
6. Colocamos cada cuadro combinado encima de su celda correspondiente como se puede ver en la imagen:
7. Ya tenemos terminada nuestra entrada de datos. Ya sólo nos queda realizar la fórmula que calcule la suma de los miles de litros vendidos del producto que seleccionemos y entre los meses que le indiquemos. Nos situamos en B6 y escribimos la siguiente fórmula:=SUMA(DESREF(B9;B2;B3;1;B4-B3+1))
La función DESREF tiene la siguiente sintaxis: =DESREF(ref;filas;columnas;alto;ancho). Nos permite realizar una desviación desde una celda concreta (argumento ref). Desde esta celda "de partida" nos podemos mover un número de filas hacia arriba o hacia abajo (argumento fila) y un número de columnas hacia la derecha o izquierda (argumento columnas). Una vez hecha la desviación podemos indicarle el alto y el ancho de la matriz a tener en cuenta. En nuestro ejemplo partimos de B9 y le indicamos que desde B9 se mueva tantas filas hacia abajo como indique la celda B2 (que es donde tenemos el número de producto). Si por ejemplo seleccionamos el producto Coke L, en B2 aparecerá (aunque no lo vea porque tiene el cuadro combinado encima) el número 3, por lo que partiendo desde B9 se situará precisamente en B12. En el argumento columnas le indicamos B3, que es el número de mes. Si en B3 seleccionamos, por ejemplo, Abril entonces el número que aparecerá será el 4. De esta manera, y dado que "nos encontrabamos" en B12 la nueva posición será F12. Fíjese que ya nos encontramos en la celda del producto y mes inicial que queremos sumar. Sólo nos resta indicarle hasta donde debe realizar dicha suma. Para eso utilizamos los argumento Alto y Ancho. Como sólo nos interesan los datos del producto seleccionado el Alto será 1 (la misma fila) pero el ancho será la resta entre el mes final, por ejemplo junio, y el inicial: Junio-Abril = 6-4. De esta resta obtenemos el resultado de 2 al que siempre tendremos que sumar 1 para que incluya el mes inicial: 2+1=3 que son los meses que queremos que sume desde la celda F12.
Aunque pueda parecer un poco "lioso" en cuanto utilice esta función un par de veces se familizará con ella rápidamente (cosa que, por otro lado, es muy recomendable).
8. Para cerrar nuestro modelo le añadiremos un condicional a la fórmula con el objetivo de evitar errores en la introducción de fechas (mes final debe ser mayor o igual que el inicial). A saber:
=SI(B3>B4;"Error Fechas";SUMA(DESREF(B9;B2;B3;1;B4-B3+1)))
No hay comentarios:
Publicar un comentario