Búsqueda de los Meses de Más y Menos Ventas

Descargar el Archivo

"Tengo la siguiente tabla con las cifras de ventas mensuales de los últimos 6 años. Me gustaría saber cuál fue la cifra máxima y mínima de ventas de cada año y en qué mes ocurrieron".

La solución es bastante sencilla utilizando las funciones: MAX, MIN, BUSCARH y COINCIDIR. A saber:

1. Diseñamos la siguiente salida de datos:

2. Nos situamos en B11 y escribimos la siguiente fórmula:

=MAX($B3:$M3)

3. Copiamos la anterior fórmula y la pegamos en D11 y sustituimos la función MAX por MIN:

=MIN($B3:$M3)

4. Nos situamos en B1 y escribimos un 1. En C1 un 2. Seleccionamos estas dos celdas y rellenamos hasta M1, con lo que obtenemos una serie de números del 1 al 12.

5. Seleccionamos el rango B1:M2 y vamos al Cuadro de nombres (a la izquierda de la barra de fórmulas) y escrinbimos el nombre meses. Pulsamos enter.

6. Nos situamos en la celda C11 y escribimos la siguiente fórmula:

=BUSCARH(COINCIDIR(B11;$B3:$M3;0);meses;2;FALSO)

La función COINCIDIR nos devolverá en qué número de columna se encuentra el valor máximo (B11). El número se encontrará entre 1 y 12, ya que es el número de columnas que abarca el rango consultado (B3:M3). Una vez tenemos este número lo aplicamos a la "función hermana" de BUSCARV, que es BUSCARH. Esta función realiza el mismo trabajo que BUSCARV pero en vez de realizar la búsqueda verticalmente lo hace horizontalmente (por filas).

7. Copiamos la fórmula de C11 en E11.

8. Seleccionamos el rango B11:E11 y hacemos doble clic en la esquina inferior derecha (Copiado inteligente). Trabajo terminado:

Importar Rangos Completos con INDIRECTO y Fórmula Matricial

Descargar el Archivo

Hoy, jueves 14 de mayo, comienzo este artículo felicitando a mi queridísimo amigo, y también uno de mis "mentores" -junto a mi también queridísimo amigo Julián de Cabo-, Enrique Dans que está de cumpleañitos. Sí, Enrique Dans el del Blog de Enrique Dans... Enriquiño mi más cariñosa felicitación.

Dicho lo cuál nos ponemos manos a la obra para dar respuesta a una pequeña consulta que me habéis realizado y que paso a describir. La empresa X maneja un pequeño cuadro de mando, de periodicidad mensual, donde se recogen distintos datos relativos a distintas sucursales de dicha empresa. En concreto el cuadro original contempla 56 sucursales y 18 medidores. En nuestro ejemplo trabajaremos con 12 sucursales y 4 medidores pero, como ya se imagina, la solución es exactamente la misma. El cuadro de mando, que se puede descargar en el vínculo del comienzo del artículo, es el siguiente:

Este cuadro se encuentra en una hoja denominada DATOS. Tenemos tres hojas más denominadas Enero, Febrero y Marzo con la información relativa a dichos meses:

Hoja denominada Enero:

Hoja denominada Febrero:

Hoja denominada Marzo:

Lo que queremos conseguir es que seleccionado un mes en de la lista desplegable que montaremos en la celda B1 de la hoja DATOS, automáticamente "me traiga" a esta hoja y dentro de esta tabla toda la información del mes solicitado. Los pasos a seguir son los siguientes:

1. Escribimos los nombres de los meses que queremos que aparezcan en nuestra lista desplegable. Dentro de la hoja DATOS nos situamos, por ejemplo, en la celda A21 y escribimos Enero; en A22 Febrero; y en A23 Marzo. Evidentemente, lo normal será que tenga 12 hojas con los 12 meses y que, en consecuencia, tenga que escribir la lista completa con los 12 meses. En nuestro ejemplo utilizaremos sólo 3.

2. Una vez introducidos los nombres de los 3 meses nos situamos en la celda B1. Abrimos el menú Datos/Validación y seleccionamos Permitir/Lista.

En el cuadro Origen escribimos =A21:A23 y,  finalmente, pulsamos Aceptar. Con esto ya tendremos nuestra lista desplegable con los meses en B1.

3. Vamos a la hoja denominada Enero, seleccionamos el rango B3:E14 y hacemos clic en el Cuadro de nombres (el que se encuentra a la izquierda de la barra de fórmulas y que puede ver en la siguiente imagen). Escribimos el nombre enero y pulsamos Enter.

4. Repetimos el paso 3 con las hojas denominadas Febrero y Marzo (seleccionando sus correspondientes tablas y dándoles el correspondiente nombre del mes).

5. Nos situamos en la hoja DATOS y seleccionamos el rango B5:E16 y escribimos la siguiente y única fórmula:

=INDIRECTO(B1) pero NO PULSAMOS ENTER. Pulsamos la combinación de teclasCtrl+Shift+Enter  para que, como ya hemos visto en diversos artículos, Excel lo trate como una entrada matricial. La fórmula resultante será:

{=INDIRECTO(B1)}

Fíjese que en B1 tendremos el nombre del mes que hemos asociado a su correspondiente tabla. Con la función INDIRECTO conseguimos que el nombre del mes sea una referencia válida para Excel. Pero como dicho nombre hace referencia a una tabla (o matriz) necesitamos concluir nuestra fórmula convirtiéndola en una entrada matricial. De esta sencilla manera habrá conseguido "importar" toda la tabla correspondiente al mes seleccionado en la celda B1 de una sola "atacada".

JERARQUIA de un valor dentro de un rango o matriz

Descargar Archivo del  Ejemplo

Hoy, con vuestro permiso, resolveremos una consulta que me habéis planteado. Partiendo de una tabla en la que tenemos información relativa a los empleados de la empresa y su sueldo anual bruto, queremos generar una Entrada de Datos que nos permita seleccionar, de una lista desplegable, a uno de dichos empleados y que me indique qué puesto ocupa por ganancias dentro de la empresa. La tabla de partida es la que se puede ver a continuación:

La solución, como veremos enseguida, es bastante sencilla. Para ello utilizaremos, entre otras, la función JERARQUIA, que paso a explicar brevemente:

=JERARQUIA(Número;Referencia;Orden)

* El argumento Número    hace referencia a la cifra concreta (en nuestro caso será el sueldo bruto) cuya jerarquía deseamos conocer.

* El argumento Referencia    es una matriz de una lista de números o una referencia a una lista de números (en nuestro caso será el rango de sueldos).

* Y finalmente el argumento Orden es un número que especifica cómo clasificar el argumento número. Los valores que puede tomar este argumento son básicamente dos: cero o cualquier valor distinto de cero. Si ponemos cero (o lo omitimos) la referencia de orden será descendente, mientras que en el caso contrario la referencia de orden será ascendente.

Vamos a utilizar dos soluciones distintas aunque muy similares.

Solución 1: En el rango G3:G17 resolvemos el orden para todos y cada uno de los empleados de la tabla. Seguimos estos pasos:

1. Nos situamos en G2 y escribimos el rótulo Orden.

2. Seleccionamos el rango E3:G17 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre tabla2 y pulsamos enter.

3. Seleccionamos el rango E2:F17 y vamos al menú Insertar/Nombre/Crear y dejamos marcado sólo nombres en la columna superior. De esta manera ya habremos dado nombre a los distintos rangos que luego utilizaremos en nuestras fórmulas.

4. Nos situamos en la celda B2 y preparamos los rótulos como se ve en la imagen. Vamos a C2 y abrimos el menú Datos/Validación de datos y seleccionamos Permitir/Lista y en Origen escribimos =Empleado. De esta manera ya tendremos nuestra lista desplegable en C2 para elegir el empleado del que queramos la información (evidentemente en C5 no debe escribir nada por ahora ya que es donde resolveremos la fórmula):

5. Nos situamos en G3 y escribimos la siguiente fórmula:

=JERARQUIA(F3;Sueldo)

Esta fórmula nos devolverá el orden que ocupa el sueldo que aparece en F3 dentro del rango Sueldo. Si quiere que aparezca como "6º" en vez de "6" sólo tiene que añadir a la fórmula la función CONCATENAR, o lo que es lo mismo, el operador &:

=JERARQUIA(F3;Sueldo)&"º"

6. Copiamos la fórmula de G3 en el resto de celdas del rango hasta G17. De esta manera ya tenemos el orden que ocupa cada uno según su sueldo:

7. Nos situamos en C5 y escribimos la siguiente fórmula:

=BUSCARV(C2;tabla2;3;FALSO)

De esta manera estaremos buscando el puesta que ocupa solamente el empleado elegido en C2 en nuestra lista desplegable.

Solución2: Resolvemos directamente en C8 el problema sin calcular el orden de todos y cada uno de los empleados. Hacemos lo siguiente:

1. Si no hemos desarrollado la primera solución ejecutamos los pasos 2, 3 y 4 de la misma.

2. Seleccionamos el rango E3:F17 y le damos el nombre tabla1.

3. Nos situamos en C8 y escribimos la siguiente fórmula:

=JERARQUIA(BUSCARV(C2;tabla;2;FALSO);Sueldo)&"º"

Fíjese que hemos anidado la función BUSCARV como primer argumento de la función JERARQUIA. Esto nos proporcionará la búsqueda del sueldo del empleado que introduzcamos en C2. Evidentemente, el resultado obtenido es el mismo que en la solución 1.

 Si desea añadir una Salida de Datos como la que se muestra en la imagen en B11, sólo tiene que añadir la siguiente fórmula:

="Es el "&C8&" que más gana"

Fíjese que de nuevo hemos utilizado la función CONCATENAR (&) para unir varios textos y una referencia al valor de la celda C8, además de decorar un poco el contorno. En este sentido (en el de la decoración) permitidme que os recomiende que jamás utilicéis la opción de formato combinar y centrar, que tenéis en vuestra barra de formato y que es el icono que podéis ver en la imagen:

este tipo de formato da muchísimos problemas, como sabréis. Es mucho mejor que seleccionéis, en este caso, B11 y C11 y vayáis al menú Formato/Celdas/Alineación y en la opción Horizontal seleccionéis Centrar en la selección. El resultado es el mismo pero esta opción no os dará ningún problema.

Como apunte final indicaros que la función JERARQUIA es como la "inversa" de K.ESIMO.MAYOR (o MENOR). Para que se comprenda correctamente con K.ESIMO.MAYOR estaríamos preguntando (en nuestro ejemplo) quién es el 6º que más gana, mientras que con JERARQUIA lo que calculamos es precisamente el orden que ocupa un determinado empleado.

Búsqueda de elementos idénticos en distintas tablas

Es muy habitual que la información con la que vamos a trabajar se encuentre dispersa en distintas hojas de un libro o incluso en distintos libros (archivos). Sin entrar en la discusión de cómo se debe plantear correctamente nuestro modelos en Excel para sacarle el mayor rendimiento posible con el mínimo esfuerzo (artículo/s que prometo abordar en este blog) me planteáis la siguiente consulta:

“Dentro de un archivo tengo los nombres de 32 comerciales y 10 hojas distintas con información relativa a sus salarios, ventas, comisiones, etcétera. Cuando deseo realizar un resumen que incluya toda esta información acabo copiando y pegando a mano ¿Hay alguna manera de que, por ejemplo, busquemos los nombres de los comerciales y nos devuelva toda la información relativa a éstos que se encuentre en las distintas hojas?”. 

En este caso que me proponéis, la solución es bastante sencilla, ya que la información sigue un mismo patrón aunque se encuentre dispersa en distintas hojas. A saber: todas las tablas tienen dos columnas, siendo la primera el nombre de los comerciales y dónde la segunda contiene la información sobre ventas, comisiones, etcétera. En nuestro ejemplo vamos a plantear el caso para 10 comerciales y 3 tablas repartidas en 3 hojas distintas: 

1. En la hoja Salarios tenemos nuestra tabla en el rango A6:B16 y lo primero que tenemos que hacer es, precisamente darle nombre al rango. Para ello seleccionamos A7:B16 (no incluimos los nombres de campo), hacemos clic en el cuadro de nombres ( a la izquierda de la barra de fórmulas) y escribimos salarios:

2. En la hoja Ventas tenemos nuestra tabla en el rango A2:B12 y hacemos lo mismo que en el caso anterior: seleccionamos A3:B12, hacemos clic en el cuadro de nombres y  escribimos ventas.

3. En la hoja Comisiones tenemos nuestra tabla en el rango A13:B23 y hacemos lo mismo que en el caso anterior: seleccionamos A14:B23, hacemos clic en el cuadro de nombres y  escribimos comisiones.

4. Nos situamos en la hoja Resumen y preparamos el cuadro (es imprescindible que los nombres de campo (rango B3:D3) sean IDÉNTICOS a los nombres que hemos creado para los distintos rangos):

5. Nos situamos en la celda B4 y escribimos la siguiente fórmula:

=BUSCARV($A4;INDIRECTO(B$3);2;FALSO)

Fíjese que en el segundo argumento de la función BUSCARV (matriz_buscar_en) hemos anidado la función INDIRECTO. El objetivo es aprovechar el nombre del campo, por ejemplo salarios, para que BUSCARV localice en dicha tabla la información deseada. Cuando copiemos la fórmula de B4 hacia la derecha la matriz_buscar_en irá variando con el nombre del campo. Para ello hemos utilizado referencias mixtas fijando la columna para los nombres de los comerciales ($A4) y fijando la fila para los nombres de las tablas (B$3). De esta manera podremos copiar hacia la derecha y hacia abajo la fórmula de B4 y con una única fórmula habremos terminado nuestro resumen. 

 

Comprobar registros con Fórmulas Matriciales y Formato Condicional

Hoy, 16 de abril, quiero felicitar el cumpleaños a mi hermano Miguel que, aunque ya no está entre nosotros, le seguimos queriendo, echando mucho de menos y teniendo SIEMPRE muy presente ¡Felicidades MIGUELÓN!

Supongamos que tenemos una tabla con numerosas entradas de números de factura, importes, etcétera (en nuestro ejemplo utilizaremos pocas entradas para que se vea bien la solución pero ésta es la misma para 5 entradas que para 5.000). Lo que queremos hacer es tener una celda de entrada de datos donde escribamos, por ejemplo, un número de factura y la hoja me indique si dicha referencia existe o no. Además queremos que si existe me sombree la celda dónde se encuentra y si no existe entonces que me sombree la celda donde he escrito la referencia y que me aparezca tachada. Nuestro modelo de partida es el siguiente:

Buscamos que en B4 nos indique si existe o no el número de factura que escribamos en B2. Si existe pondrá VERDADERO y coloreará la celda dónde se encuentra. Si no existe pondrá FALSO y coloreará la celda B2 y tachará su contenido (para indicarnos que dicha referencia es incorrecta). Manos a la obra:

1. Seleccionamos el rango D3:D22 y hacemos clic en el cuadro de nombres (a la derecha de la barra de fórmulas) y escribimos el nombre facturas.

2. Nos situamos en B4 y escribimos: =O(B2=facturas) y NO PULSAMOS ENTER. Lo que vamos a hacer ahora es realizar una entrada matricial. Para ello debemos pulsar las teclas Ctrl+Shift+Enter. Al hacerlo observará que la fórmula aparece encerrada por unas llaves { } quedando de la siguiente manera:

{=O(B2=facturas)}

Estas llaves no puede introducirlas manualmente porque entonces excel no lo reconocerá como una entrada matricial y no funcionará. La entrada matricial nos permite, en este caso, comprobar un valor, B2, contra todo un rango, D3:D22, rango al que hemos llamado facturas. Esta fórmula nos devolverá dos valores posibles: VERDADERO ó FALSO. Ya hemos conseguido que nos indique la existencia o no del número de factura solicitado. Sigamos con nuestro modelo:

3. Seleccionamos el rango facturas y vamos al menú Formato/Formato condicional y ponemos valor de la celda igual a y en el recuadro de la derecha hacemos clic en B2, que aparecerá como $B$2 (y así debemos dejarlo, con los dólares).

4. Pulsamos el botón Formato y seleccionamos, por ejemplo, trama (sombreado) de color naranja y pulsamos Aceptar. De esta manera cuando escribamos un número de factura que exista en B2 la celda que contenga dicho número se coloreará de naranja, haciendo muy sencilla su localización.5. Nos situamos en B2 y vamos al menú Formato/Formato condicional y donde pone valor de la celda lo cambiamos por fórmula. En el recuadro de la derecha escribimos:

=SI(B4=FALSO;1;0)

6. Pulsamos el botón Formato y en la pestaña Fuentes seleccionamos Tachado dentro del apartado Estilo. Además vamos a la pestaña Tramas y seleccionamos, por ejemplo, el color naranja "chillón". Pulsamos Aceptar.

Evitar datos repetidos con Validación Datos Personalizado

Supongamos que tenemos una tabla en la que vamos introduciendo los datos relativos a las ventas producidas en cada delegación de nuestra compañía semanalmente. La primera columna hace referencia al código de la operación. Dicho código es único, es decir, no se puede repetir, y representa la delegación donde se ha producido la venta (dos primeros dígitos) y el número de factura (tres últimos dígitos). Queremos que si por error intentamos introducir un código repetido la hoja no nos lo permita y nos avise con un mensaje. También queremos que en la tercera columna de la tabla nos indique, tras introducir el código, a qué delegación pertenece la venta en cuestión. Para todas estas labores utilizaremos Formato Condicional, Validación de Datos y las funciones CONTAR.SI, SI, O, BUSCARV y IZQUIERDA.

Lo primero que buscamos es lo que puede ver en la siguiente imagen: que no nos permita introducir datos repetidos en el campo Código.

1. Seleccionamos el rango donde vayamos a introducir los datos, en nuestro caso A4:A20 y vamos al menú Datos/Validación de datos.

2. Seleccionamos Permitir/Personalizada y en el recuadro Fórmula escribimos:

=CONTAR.SI($A$4:$A$20;A4)=1

3. Antes de aceptar pulsamos la pestaña de mensaje de error y personalizamos el mensaje que queramos que aparezca si intentamos duplicar un registro. Una vez hecho pulsamos Aceptar.

Gracias a esta sencilla fórmula cada registro (A4, A5, A6...) es comparado con el rango $A$4:$A$20. Como establecemos la condición de que al contar cada registro debe ser igual a 1 si intentamos volver a introducir el mismo registro nos saldrá el mensaje de error que le hayamos indicado.

Por otro lado nos interesa que en C4 y siguientes filas nos indique automáticamente en qué delegación se ha realizado la venta (utilizaremos esta información más adelante para generar un informe resumen por delegación). Para ello elaboramos la tabla que se muestra en la figura en el rango E3:F9.

4. Seleccionamos el rango E4:F9 y le damos el nombre de ciudades.

5. Para resolver este problema además de la función BUSCARV necesitaremos otra función que nos permita extraer del código los dos primeros dígitos. Dicha función será IZQUIERDA. Si escribiéramos =IZQUIERDA(A4;2) obtendríamos los dos primeros caracteres de la celda A4, que es precisamente lo que necesitamos para realizar después la búsqueda en nuestra tabla de ciudades. Así pues, nos situamos en la celda C4 y escribimos la siguiente fórmula:

=BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso) De esta manera obtendremos la ciudad a la que hace referencia el código. Si queremos mejorar nuestra fórmula para que sólo aparezca la ciudad en caso de que introduzcamos un código en la columna A y un importe en la columna B deberemos añadir dos funciones lógicas, a saber, SI y O:

=SI(O(A4="";B4="");"";BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso))

Obtener datos de una tabla con INDIRECTO y NOMBRES

Supongamos que tenemos una tabla como la que se muestra en la siguiente figura donde se encuentran las ventas de nuestras distintas delegaciones a lo largo de todo un año.

Lo que buscamos es tener una zona de entrada de datos donde escribamos, o mejor dicho, seleccionemos de una lista la Delegación (celda C3) y de otra lista el mes (celda C5) y nos proporcione la cifra de ventas en cuestión.

Hay diversas maneras de solucionar este problema. Nosotros lo haremos por medio de Crear Nombres, Validación de Datos y de la función INDIRECTO. Veamos cómo.

1. Seleccionamos el rango A10:E22 y vamos al menú Insertar/Nombre/Crear. Automáticamente se abrirá la siguiente ventana:Por defecto nos muestra seleccionadas las casillas de verificación de Fila Superior y Columna izquierda. Esto es debido a que se ha encontrado nombres dentro del rango que hemos seleccionados tanto en la fila superior como en la columna izquierda de nuestra selección. Pulsamos aceptar. A partir de este momento podemos utilizar este tipo de entradas para encontrar la información requerida: =orense may_09 y nos devolverá el valor 10.300€ (fíjese que entre el nombre orense y may_09 debe dejar un espacio).

Esta es una manera sencilla pero requiere que escribamos cada vez el nombre de la delegación y el del mes para obtener la cifra de ventas deseada. A continuación veremos una forma más ágil de solucionar el problema:

1. En el rango G10:G14 escribimos la lista de delegaciones tal y como excel las ha nombrado. Dichos nombres los podemos consultar en el menú Insertar/Nombre/Definir. También podemos proceder directamente a pegar en un rango de excel los distintos nombres creados yendo a Insertar/Nombre/Pegar. En el rango I10:I22 escribimos los nombres referentes a los meses.

2. Nos situamos en la celda C3, abrimos el menú Datos/Validación y seleccionamos la opción Permitir: Lista. Como origen marcamos el rango donde tenemos las delegaciones (sin incluir el título), es decir, G11:G14 y pulsamos aceptar.

3. A partir de este momento en la celda C3 ya tenemos disponible una lista desplegable con el nombre de nuestras delegaciones.

4. Nos situamos en la celda C5 y realizamos el mismo paso anterior pero en este caso el origen será I11:I22 (la lista de meses).

Con esto hemos conseguido que en C3 y en C5 aparezcan listas desplegables con las opciones disponibles en delegaciones y meses. Ahora sólo nos falta conseguir que seleccionando elementos de estas listas la fórmula que debe proporcionarnos la cifra de ventas funcione. Para ello vamos a utilizar la función INDIRECTO. Esta función (que encontrará dentro del apartado de funciones de búsqueda y referencia) devuelve la referencia especificada por una cadena de texto. En nuestro ejemplo nos situamos en C7 y escribimos la siguiente fórmula:

=INDIRECTO(C3) INDIRECTO(C5) (deje un espacio entre ambas expresiones)

Esta fórmula convertirá el nombre que aparezca en C3 y el nombre que aparezca en C5 en una referencia válida para excel y nos mostrará el resultado correcto: 10.300€