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)

Generar Contraseña Alfanúmerica Aleatoria

Descargar el Archivo

"Necesito generar contraseñas alfanuméricas (concretamente dos letras, cuatro números y otras dos letras) de forma aleatoria con excel"

Para dar solución a este problema utilizaremos las funciones CARACTER y ALEATORIO.ENTRE

La función CARACTER devuelve el caracter especificado por un número comprendido entre el 1 y el 255 (código ASCII). Para ver la correspondencia de dichos caracteres puede escribir una lista de números del 1 al 255 a partir de la celda, por ejemplo, N1 y escribir en M1 la fórmula =CARACTER(N1). Si aplica el copiado inteligente (doble clic para copiar hacia abajo en la celda M1) verá dicha correspondencia. Pruebe a ponerse en una celda en blanco y pulse la tecla Alt + el número de caracter que desee que aparezca.

La función ALEATORIO.ENTRE genera un número aleatorio entre los números que se especifiquen en los argumentos. Por ejemplo, ALEATORIO.ENTRE(1;100) devolverá un número entre dichas cifras.

Dentro de la categoría de funciones Matemáticas puede que no le aparezca por defecto la función ALEATORIO.ENTRE Para activarla, debe ir a Herramientas/Complementos y seleccionar la opción Herramientas para análisis. Una vez activada le aparecerá dicha función.

Antes de realizar nuestras fórmulas nos ponemos en E5 y escribimos el número 48 y en F5 el 57. Esto es debido a que entre estos dos números se encuentran los caracteres correspondientes a los números del 0 al 9. En E6 escribimos el 65 y en F6 el 90. Esto es debido a que entre estos números se encuentran las letras de la A a la Z.

1. Nos situamos en la celda E2 y escribimos la siguiente fórmula:

=CARACTER(ALEATORIO.ENTRE($E$6;$F$6))

2. Copiamos esta fórmula en D2, K2 y L2.

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

=CARACTER(ALEATORIO.ENTRE($E$5;$F$5))

4. Copiamos esta fórmula a la derecha hasta J2

De esta forma ya dispondremos de la solución buscada. Si desea generar una nueva contraseña aleatoria sólo tendrá que pulsar la tecla F9.

Puede CONCATENAR el resultado y escribirlo en la celda B2:

=E2&F2&G2&H2&I2&J2&K2&L2

También puede resolverlo con una única fórmula, por ejemplo en B4, como la que sigue:

=CARACTER(ALEATORIO.ENTRE($E$6;$F$6))&

CARACTER(ALEATORIO.ENTRE($E$6;$F$6))&

CARACTER(ALEATORIO.ENTRE($E$5;$F$5))&

CARACTER(ALEATORIO.ENTRE($E$5;$F$5))&

CARACTER(ALEATORIO.ENTRE($E$5;$F$5))&

CARACTER(ALEATORIO.ENTRE($E$5;$F$5))&

CARACTER(ALEATORIO.ENTRE($E$6;$F$6))&

CARACTER(ALEATORIO.ENTRE($E$6;$F$6))

Formato de Celda Tipo DNI

Descargar el Archivo

"Todos los días tengo que introducir numerosos datos referentes al DNI de clientes. Me gustaría introducir en la celda una entrada del tipo 33444555b y que automáticamente se transformara en 33.444.555-B"

El problema tiene solución "razonablemente fácil" mediante fórmula en una nueva columna. Pero no podemos resolverlo directamente con Formato de Celda Personalizado porque excel reconoce números o textos pero no ambos a la vez.

Voy a proponer dos planteamientos distintos, a saber:

1. Tenemos dos columnas de entrada de datos. En la primera introducimos el número del DNI y en la segunda la letra

2. Tenemos una sola celda de entrada donde escribimos directamente el número y letra del DNI.

Aclaración: Los DNI utilizados en los siguientes ejemplos son inventados y, por lo tanto, no tienen por qué corresponderse con la letra adecuada. Al final de este post incluyo una fórmula para obtener la letra correspondiente a cada DNI.

Planteamiento 1:

1. Nos situamos en la celda D4 y escribimos la siguiente fórmula:

=TEXTO(B4;"#.##0-")&MAYUSC(C4)

(Consultar la función TEXTO) Es importante destacar que el formato establecido dentro de la función TEXTO entre comillas concluye con un guión al final.

El símbolo & corresponde a la función CONCATENAR.

Finalmente la función MAYUSC convierte un texto a mayúsculas. El resultado será:

Planteamiento 2:

1. Nos situamos en la celda C10 y escribimos la siguiente fórmula:

=TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")&MAYUSC(DERECHA(B10;1))

Evidentemente la cosa se ha complicado un poco. El motivo es que pueden existir DNI con distintos números de dígitos. Empezando por el final:

MAYUSC(DERECHA(B10;1)) Esta parte de la fórmula convierte en MAYÚSCULA el caracter(uno solo) que se encuentre a la DERECHA del dato introducido en B10. Es decir, que extraemos la letra del final y la ponemos en mayúscula.

Ahora nos queda extraer los dígitos que compongan el número del DNI y darles el formato adecuado. Para extraerlos utilizamos la función LARGO anidada en la función IZQUIERDA. La función LARGO nos indica el número de caracteres que hay en una celda. Si por ejemploescribieramos en una celda la fórmula =LARGO ("hola") el resultado sería 4, que son el número de caracteres de dicha palabra. La parte de nuestra fórmula LARGO(B10)-1 nos devolverá el número total de caracteres existentes en B10 menos 1 (este menos 1 es para restarle la letra). Este número restante será el número de dígitos que compongan el DNI. Ya sólo nos queda extraer dicho número con la función IZQUIERDA: IZQUIERDA(B10;LARGO(B10)-1) y trabajo casiresuelto. Digo casi porque nos faltaría darle el formato adecuado, tarea que realizaremos, como ya sabemos, con la función TEXTO: TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")

A estas alturas de la película ya tenemos el número por un lado y la letra por otro. Los unimos con el símbolo & (CONCATENAR) y trabajo resuelto.

Fórmula para el cálculo de la letra del DNI:

Con la siguiente fórmula puede obtener la letra correspondiente a un DNI determinado:

=ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")

Haciendo uso de esta fórmula en nuestro primer planteamiento podríamos resolver directamente, sin necesidad de introducir la letra correspondiente en la columna C, de la forma: (pruebe a escribirlo en la celda, por ejemplo, E4)

=TEXTO(B4;"#.##0-")&

ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")

Insertar Viñetas (Bullets) en Celdas con Fórmulas

"Tengo una hoja en la que recopilo los ingresos obtenidos en distintas zonas. Me gustaría destacar el total de cada zona con una viñeta en la misma celda en la que calculo dicho sumatorio ¿Es posible?".

Partimos del siguiente ejemplo:

Queremos que en las celdas C4, C9 y C14 inserte una viñeta antes del número (que en realidad es una fórmula que suma las tres celdas que se encuentran debajo). Para ello debemos seguir los siguientes pasos:

1. Nos situamos en la celda C4 y vamos al menú Formato/Celdas.

2. Dentro de la pestaña Número seleccionamos la Categoría Personalizada:

3. Debajo de Tipo (normalmente nos encontraremos la opción Estándar) escribimos lo siguiente:

Pulsamos la tecla Alt y con ella pulsada tecleamos el número 16 pero CON EL TECLADO NUMÉRICO. Al soltar la tecla Alt aparecerá la viñeta ►. Dejamos un espacio en blanco y a continuación escribimos #.###,00€ Este formato corresponde a separador de miles, con dos decimales y el símbolo final del euro. En definitiva:

► #.###,00€

4. Pulsamos Aceptar. De esta manera ya hemos conseguido el formato deseado en la celda C4. Sólo nos queda copiar y pegar dicho formato en aquellas celdas que nos interese (C9 y C14). El resultado obtenido será:

Si queremos otra viñeta distinta podemos probar distintas combinaciones pulsando la tecla Alt+ un número (con el teclado numérico). También podemos acudir al menú Insertar/Símbolo y seleccionar el que deseemos y copiarlo y pegarlo en vez del ►.

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:

Cuadro Combinado con Contenido Variable

Descargar el Archivo

En el post de hoy veremos como realizar cuadros combinados cuyo contenido dependa de lo que seleccionemos en los botones de opción. Para entender mejor nuestro objetivo veamos la siguiente figura:

Lo que queremos conseguir es que una vez seleccionemos la opción deseada en el botón de opción correspondiente, nos aparezca el contenido adecuado en el cuadro combinado.

Los pasos que debemos seguir son los siguientes:

1. Creamos los Botones de Opción (si no recuerda cómo insertar dichos botones consulte Formato Condicional con Botones de Opción) y los vinculamos con la celda D11.

2. Dibujamos el cuadro combinado (véase el post Cálculos con Rangos Dinámicos y Cuadros Combinados) pero todavía no procedemos a configurarlo.

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

=DIRECCION(12;D11)&":"&DIRECCION(19;D11)

La función DIRECCION, ya tratada en este blog, crea una referencia de celda con formato de texto, una vez indicada la fila y la columna. En nuestro ejemplo la fila en la que comienzan las tres listas (fijos, eventuales y extras) es la número 12. La columna dependerá de lo que seleccionemos en el botón de opción. Dicho botón lo hemos vinculado con la celda D11 y nos devolverá 1, 2 ó 3. Si por ejemplo seleccionamos la opción Fijos entonces D11 valdrá 1 por lo que la función Direccion nos devolverá como texto la referencia de la columna 1 y fila 12, es decir, A12. De esta manera ya tenemos la celda en la que comienza el rango seleccionado por medio del botón de opción. Ahora tenemos que conseguir la celda en la que concluye dicho rango y eso lo logramos con DIRECCION (19;D11).

Ya tenemos la celda inicial y la celda final de un rango. Para unir ambas utilizamos la función CONCATENAR (o lo que es lo mismo &) y colocamos en medio los dos puntos que identifican un rango:

celda inicial A12

celda final A19

A12&":"&A19

Nos devuelve el rango requerido en forma de texto: A12:A19. Para transformar esta referencia de texto en referencia válida para excel utilizaremos la función INDIRECTO.

4. Vamos al menú Insertar/Nombre/Definir. Escribimos el nombre, por ejemplo, Rango_lista. En el cuadro Se refiere a escribimos la siguiente fórmula y pulsamos Aceptar:

=INDIRECTO($D$13)

5. Nos situamos encima del Cuadro Combinado, hacemos clic en el botón derecho y seleccionamos Formato de Control. En Rango de entrada introducimos Rango_lista (que es precisamente el rango variable que hemos generado) y vinculamos, por ejemplo, con la celda D12. Pulsamos Aceptar.

A partir de este momento cuando pulsemos un botón de opción la lista que aparecerá en el cuadro combinado será la correspondiente al botón seleccionado: