Desplegables sin Filas en Blanco

"Tengo una columna con registros y necesito generar una lista desplegable con dichos registros y en el mismo orden que aparecen en el listado original. El problema es que existen celdas en blanco y, en consecuencia, me aparecen dichos espacios en la lista desplegable".

Partimos del siguiente ejemplo:

En C2 queremos crear una lista desplegable con validación de datos, que presente los distintos códigos existentes en el rango E3:E20, en el mismo orden, pero sin los espacios en blanco. Lo primero que hacemos es generar un número de orden en la columna G (a partir de G3) del 1 al 18. En la celda H3 escribimos =E3 y copiamos hasta la celda H20, para generar un "espejo" de los códigos originales: 

Lo que vamos a hacer a continuación es "reordenar" estos códigos dándoles un nuevo número de orden. Para ello nos ponemos en la celda I3 y escribimos la fórmula:

=SI(H3=0;MAX($G$3:$G$20)+1;G3)  y la copiamos hasta I20.

Cunado el valor de la celda es 0 le estamos pidiendo que calcule el máximo de nuestro lista de orden (en nuestro ejemplo es 18) y le sume 1. De esta manera, todas las celdas en blanco de nuestra listado original de códigos, pasará a tener un nuevo número de orden que, en nuestro ejemplo, será de 19 (18+1). En el caso de que el valor de la celda no sea 0, le pedimos que escriba el valor número de orden que le corresponde a dicho código. Esto nos servirá para "reordenar" a continuación la información manteniendo el orden original pero evitando las celdas en blanco:

Ya sólo nos queda "recolocar" de menor a mayor estos nuevos números de orden y obtener el código correspondiente. Nos situamos en J3 y escribimos:

=SI.ERROR(BUSCARV(K.ESIMO.MENOR($I$3:$I$20;G3);$G$3:$H$20;2;FALSO);"X")

K.ESIMO.MENOR nos reordena de menor a mayor los números que hemos obtenido en el rango I3:I20. Al anidar esta función dentro de BUSCARV lo que hacemos es obtener el código relacionado con el número de orden. Finalmente, anidamos dentro de SI.ERROR para que aquellos valores que no se encuentran dentro de la tabla de búsqueda (en concreto el valor 19, que se corresponde con las celdas en blanco) no devuelvan el valor de error #N/A y se conviertan en X:

Ya sólo nos queda crear la fórmula en la validación de datos. Seleccionamos el rango J3:J20 y le creamos el nombre Listado. Desde C2 vamos a Validación de datos. En "Permitir" seleccionamos Lista y escribimos la siguiente fórmula:

=DESREF(J3;0;;CONTAR.SI(listado;"<>X"))

De esta manera obtendremos nuestra lista desplegable, manteniendo el mismo orden que el listado original, sin incluir ninguna fila en blanco:

Máximos, Mínimos y Promedios por Columnas

Descargar Archivo

"He leído el post de "Resaltar Máximos, Mínimo  y Promedios con Formato Condicional" y me gustaría saber si se puede realizar el mismo cálculo pero por columnas".

La solución es muy sencilla. Partimos del siguiente ejemplo:

Nos situamos en la celda C3 y escribimos:

=MAX(C$8:C$19)  y copiamos hasta la celda G3

En C4 escribimos:

=MIN(C$8:C$19)    y copiamos hasta la celda G4

En C5 escribimos:

=PROMEDIO(C$8:C$19)   y copiamos hasta la celda G5

Seleccionamos ahora el rango C8:G19 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C8=C$3  y pulsamos el botón Formato. Ahora seleccionamos el Relleno de color naranja y la fuente negrita (por ejemplo) y aceptamos.

Realizamos la misma operación de nuevo pero escribiendo ahora la fórmula:

=C8=C$4  y en el Formato seleccionamos el color verde y fuente negrita.

Finalmente vamos a resaltar aquellas zonas cuyo promedio se encuentre por encima del promedio total. Para ello seleccionamos el rango C7:G7 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C$5>=PROMEDIO($C$5:$G$5)   y en el botón Formato seleccionamos, por ejemplo, relleno rosa y fuente negrita. 

Texto más Repetido en un Campo

Descargar el Archivo

"Tengo varios campos de una tabla que contienen texto en los que me gustaría saber cuál es el nombre más repetido. Si es posible, me gustaría que los más repetidos destacasen en un color".

Este problema es idéntico al cálculo de la MODA sólo que en este caso, al tratarse de texto, no podemos utilizar dicha función. Lo solucionaremos con las función CONTAR.SI y con la herramienta de Formato Condicional. Partimos del siguiente ejemplo:

1. Procedemos a crear el nombre de este rango. Seleccionamos B4:B25 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre Colores, y pulsamos enter.

2. Pasamos a generar una lista con los registros únicos a partir de la celda D4. Para realizar esta tarea puede consultar Copiar Registros Únicos en este blog. Obtenemos lo siguiente:

3. En la columna E vamos a proceder a contar el número de registros de cada taexto del rango colores. Para ello nos situamos en la celda E4 y escribimos la fórmula:

=CONTAR.SI(colores;D4)

Hacemos doble clic en la parte inferior derecha de la celda E4 y obtendremos el siguiente resultado:

De esta manera ya tendremos la información deseada de cuáles son los textos más repetidos.

4. Seleccionamos el rango Colores (B4:B25) y vamos al menú Formato/ Formato condicional y escribimos lo siguiente:

La fórmula que hemos escrito es:

=SI(BUSCARV(B4;$D$4:$E$8;2;)=MAX($E$4:$E$8);1;0)

De esta manera buscará cada texto del rango B4:B25, ya que lo tenemos preseleccionado, y comprobará si se corresponde con el valor máximo del rango E4:E8, que es donde hemos contado el número de repeticiones de cada texto. Si es, o son, el/los más repetido/s entonces lo/s sombreará con trama naranja:

Uso de la Función SUBTOTALES

Descargar el Archivo

"Tengo una tabla con mucha información sobre la que aplico habitualmente filtros para realizar cálculos ¿Hay alguna manera de, por ejemplo, sumar sólo las celdas que aparecen una vez filtrada la tabla?"

Para realizar esta labor vamos a ver la función SUBTOTALES. Partimos del siguiente ejemplo:

Como se puede apreciar en la imagen, he aplicado autofiltros a la tabla. Para ello sólo tenemos que situarnos en cualquier celda de dicha tabla, debajo de los rótulos (nombres de campo), e ir al menú Datos/Filtro/Autofiltro. La función SUBTOTALES está especialmente pensada para realizar cálculos en tablas. Veamos cómo:

1. Disponemos la siguiente salida de datos encima de nuestra tabla:

2. En la celda C2 escribimos la fórmula:

=SUBTOTALES(9;C$9:C$28)

La función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

De esta manera, la fórmula =SUBTOTALES(9;C$9:C$28) calculará la SUMA (el 9 es el número de función correspondiente a la suma) del rango C9:C28. Pero con una peculiaridad (ya que en caso contrario podríamos realizar directamente el sumatorio) y es que si procedemos a filtrar la tabla realizará la suma de los valores visibles. Para comprobarlo procedemos a filtrar el campo denominado Variable. Vamos a ver sólo aquellos cargos que tienen como sueldo variable 20.000€:

Como puede apreciar en la imagen, hay seis cargos con este variable y la función SUBTOTALES de la celda C2 nos muestra el sumatorio del sueldo bruto pero precisamente de sólo esos seis cargos (y no del total de la tabla).

3. Copiamos la fórmula de C2 en el rango C3:C6 y procedemos a sustituir el número de función para que realice el cálculo correspondiente:

En C3 =SUBTOTALES(1;C$9:C$28)

En C4 =SUBTOTALES(4;C$9:C$28)

En C5 =SUBTOTALES(5;C$9:C$28)

En C6 =SUBTOTALES(2;C$9:C$28)

4. Como hemos utilizado referencias mixtas en el rango de la función podemos proceder a copiar el rango C2:C6 en D2:D6. El resultado es el que podemos ver en la siguiente imagen:

Puede comprobar que si cambiamos el filtro y le pedimos que, por ejemplo, nos muestre los cargos con un sueldo variable mayor o igual que 15.000€ y menor o igual que 25.000€, la función SUBTOTALES recalculará y mostrará los siguientes resultados:

Resaltar Máximos, Mínimos y Promedios con Formato Condicional

Descargar el Archivo

"Mi empresa está compuesta por cinco zonas. Tengo una tabla en la que reflejo las ventas mensuales de cada zona. Me gustaría saber si es posible destacar los encabezados de zona que superan individualmente el promedio del conjunto. También me gustaría que resaltase las cifras máximas y mínimas de ventas".

Partimos del siguiente ejemplo:

Además de calcular los valores máximos, mínimos y promedio general, queremos que las zonas que individualmente superen dicho promedio general aparezcan sombreadas (el nombre de la zona) en color, por ejemplo, verde.

1. Seleccionamos el rango C8:H19 y hacemos un clic en el cuadro de nombres (a la izquierda de la barra de fórmulas). Escribimos el nombre ventas09.

2. Nos situamos en la celda C2 y escribimos la fórmula:

=MAX(ventas09)

3. En C3:

=MIN(ventas09)

4. En C4:

=PROMEDIO(ventas09)

5. Seleccionamos el rango C7:H7 y vamos a Formato/Formato condicional. Como Condición1 seleccionamos fórmula (en vez de valor de la celda) y a continuación escribimos la siguiente fórmula:

=SI(PROMEDIO(C$8:C$19)>=$C$4;1;0)

Fíjese que al utilizar referencias mixtas (fijando fila pero no columna) la fórmula que aplicará a cada columna será la adecuada, es decir, comparará el promedio de cada columna (zona) con el promedio general (C4).

6. Pulsamos el botón Formato, vamos a la pestaña Tramas y seleccionamos el color verde. Aceptamos.

A partir de este momento las zonas cuyo promedio de ventas supere el promedio general se rellenarán (el encabezado) de color verde:

7. Seleccionamos el rango ventas09 (por ejemplo haciendo directamente clic sobre este nombre dentro del cuadro de nombres) y vamos a Formato/Formato condicional. Establecemos las condiciones como se muestran en la imagen y aceptamos:

El resultado será el solicitado...

Puede conseguir los mismos resultados sin tener que realizar los pasos 1, 2 y 3, es decir, sin la necesidad de mostrar en la hoja (rango C2:C4) los valores máximo, mínimo y promedio respectivamente. Para ello debe sustituir dentro del primer formato condicional la fórmula indicada en el paso 5 por la siguiente:

=SI(PROMEDIO(C$8:C$19)>=PROMEDIO(ventas09);1;0)

En el segundo formato condicional debe sustituir las condiciones por las siguientes:

donde la primera fórmula dice =SI(C8=MAX(ventas09);1;0) y en donde la segunda fórmula dice =SI(C8=MIN(ventas09);1;0)

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:

Resumen de Ventas con SUMAR.SI

Utilizando el ejemplo del artículo anterior vamos a realizar un resumen de la información semanal de ventas por ciudades (por delegación) utilizando las funciones SUMAR.SI y CONTAR.SI. Además aplicaremos formato condicional con fórmula para destacar la delegación que más ha vendido y la que menos. Nuestros datos de partida son los que se muestran en la figura:

Y queremos obtener un informe resumen de las ventas por delegación, el importe medio de cada delegación y el número de operaciones realizadas por cada una:1. Seleccionamos el rango B4:B20 y hacemos clic en el cuadro de nombres (a la derecha de la barra de fórmulas) y escribimos el nombre importe.

2. Seleccionamos el rango C4:C20 y de la misma manera que en el paso anterior le ponemos el nombre delegacion.

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

=SUMAR.SI(delegacion;H4;importe)

Con esta fórmula conseguimos en I4 que seleccione dentro del rango delegacion aquellas celdas que coincidan con la celda H4 (en nuestro ejemplo La Coruña) y sumará todos los importes de dicha delegación.

4. Nos ponemos en la celda K4 y escribimos:

=CONTAR.SI(delegacion;H4)

De esta forma contará cuántas veces aparece La Coruña (H4) en el rango delegacion (que es lo mismo que el número de operaciones realizadas en dicha delegación).

5. Nos ponemos en J4 y escribimos: =I4/K4 para calcular el importe promedio.

6. Seleccionamos el rango I4:K4 y hacemos doble clic en el recuadrito negro que aparece en la parte inferior derecha de la celda K4 (copiado inteligente).

7. Finalmente nos ponemos en I11 y calculamos la Suma del rango I4:I9 y copiamos la fórmula en K11.

8. Copiamos la fórmula de J9 en J11 y... trabajo terminado.

Si además queremos que aparezca la cifra de ventas máxima de un color y la cifra de ventas mínima en otro color tendremos que:

9. Seleccionamos el rango I4:I9 y vamos al menú Formato/Formato condicional y seleccionamos fórmula (en el recuadro que aparece valor de la celda).

10. En el recuadro de la derecha escribimos la siguiente fórmula:

=SI(I4=MAX($I$4:$I$9);1;0) y pulsamos el botón formato y seleccionamos, por ejemplo, un color de trama azul.

11. En el recuadro de la derecha escribimos la siguiente fórmula:
=SI(I4=MIN($I$4:$I$9);1;0) y pulsamos el botón formato y seleccionamos, por ejemplo, un color de trama amarillo. Pulsamos aceptar.

El resultado es el siguiente:

Obtener Datos de una tabla con COINCIDIR y BUSCARV

Supongamos que tenemos una lista de empleados con su número de empleado, su nombre y sus ganancias y queremos saber cuánto gana el que más y el que menos y sus nombres.

Lo solucionaremos de dos maneras diferentes. En nuestra primera solución calcularemos la cifra máxima y mínima de ganancia por medio de las funciones MAX y MIN respectivamente:

1. Nos situamos en la celda F2 y escribimos =MAX(C2:C21)

2. Nos situamos en la celda F3 y escribimos =MIN(C2:C21)

En estos momentos ya tenemos las cifras máximas y mínimas pero no sabemos los empleados a los que corresponden. Como el campo a buscar serán estas cifras no podemos utilizar directamente la función BUSCARV ya que ésta busca siempre en la primera columna de una matriz y nos devuelve un valor de la misma columna o de n columnas hacia la derecha(partiendo de dicha primera columna de la tabla). Lo que hacemos entonces es utilizar la función COINCIDIR, que nos proporciona la posición (no el resultado) que ocupa en una matriz el valor buscado:

3. Nos situamos en la celda G2 y escribimos la fórmula: =COINCIDIR(F2;$C$2:$C$21;0)

4. Copiamos esta fórmula en G3.

De esta manera ya sabemos que el empleado que más gana ocupa la posición 18 del rango C2:C21 y el que menos la posición 14. Como hemos identificado cada empleado con un número en el rango A2:A21 ya sólo nos queda utilizar BUSCARV para obtener sus correspondientes nombres. A saber:

5. Le damos el nombre de "Empleados" a la tabla (costumbre siempre muy recomendable...) seleccionando el rango A2:C21 y yendo al menú Insertar/Nombre/Definir. Escribimos dicho nombre(Empleados) y aceptamos directamente.

6. Nos situamos en la celda H2 y escribimos: =BUSCARV(G2;Empleados;2;Falso)

7. Copiamos esta fórmula en H3.

Hemos "desgranado" la fórmula pero podríamos escribir directamente las dos partes. A saber:

Nos situamos, por ejemplo, en la celda E8 y escribimos:

=BUSCARV(COINCIDIR(F2;$C$2:$C$21;0);Empleados;2;Falso)

Copiamos esta fórmula una celda más abajo y obtendremos los dos nombres buscados.

Otra manera de solucionar este problema es utilizando las funciones DESREF, INDIRECTO y DIRECCION tal y como se muestra en la figura:

Cálculo de acumulados con referencias absolutas

Para realizar el cálculo de acumulados normalmente no es necesario realizar más de una fórmula. Si por ejemplo tenemos las cifras de ventas mensuales y queremos que a la derecha nos calcule la cifra de ventas acumulada en cada mes podemos resolverlo con la función SUMA y las referencias absolutas. Lo mismo ocurre si queremos calcular el promedio móvil o la cifra máxima y mínima de ventas de un rango que va variando al transcurrir los meses. Para ello debemos escribir las siguientes fórmulas:

Fíjese que hemos utilizado las referencias absolutas (los $) en la primera parte del rango. De esta manera dejamos fijo el origen del rango. Al copiar una fila más abajo la fórmula de la SUMA se convertirá en =SUMA($B$5:B6) con lo que estaremos acumulando las ventas de enero y febrero. Lo mismo ocurrirá con la fórmula de PROMEDIO.

Una vez escritas estas fórmulas no tenemos más que situarnos en la celda D5 en la parte inferior derecha de la celda (como si fuésemos a rellenar hacia abajo). Hacemos doble clic encima del pequeño cuadrado negro que tiene la celda (en la parte inferior derecha de la misma) y el Copiado Inteligente hará el resto rellenando las fórmulas para todos los meses.

Podemos añadir otras funciones que nos calculen por ejemplo la cifra máxima y la cifra mínima de ventas (funciones MAX y MIN respectivamente) como se muestra en la siguiente figura. Puede comprobar realizando los totales (sobre el rango B5:B16) que los resultados son correctos.