Palíndromos (3 de 3)

Después de dos intentos de crear un método fácil, cómodo, comprensible y elegante para comprobar si una frase es palíndromo (usando únicamente funciones de Excel), sólo me cabe un consejo: ¡escriba una macro!

Vamos a crear una UDF (Función Definida por el Usuario), a la que llamaremos PALINDROMO, que la usaremos como si se tratara de una función nativa de Excel.

Abrimos un libro nuevo, accedemos a VBA (Visual Basic para Aplicaciones) pulsando Alt + F11 y seleccionamos Insertar + Módulo. A continuación, escribimos la siguiente función:

Salimos de Visual Basic con la combinación de teclas Alt + Q.

En la celda B3 escribimos una frase: ¿Son robos? No, sólo son sobornos

En C3:

=PALINDROMO(B3)     [Resultado: Sí]

Descargar archivo (SE99-Palíndromo.xls)

Descargar archivo (SE99-Palíndromo.xlsm)

Palíndromos (2 de 3)

El método estudiado en la entrada anterior para ver si una frase es palíndromo es largo, confuso y poco elegante. Veamos otra solución.

Comencemos escribiendo un conjunto de frases a partir de la celda B3.

Paso 1: Poner la frase en mayúscula y eliminar los acentos.

En E3:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(MAYUSC(B3);"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U");"Ü";"U")

Extendemos la fórmula hasta la fila 18.

Paso 2: Eliminar los signos de puntuación y los espacios.

En F3:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(E3;",";"");";";"");".";"");":";"");"!";"");"¡";"");"¿";"");"?";"");"""";"");" ";"")

Extendemos la fórmula hasta la fila 18.

Si hacemos el ejercicio con Excel 2003, este paso hay que hacerlos en dos o mas pasos, ya que sólo admite 7 niveles de anidamiento. Excel 2010 admite 64.

Ahora, dividiremos la frase por la mitad (no importa que el número de caracteres sea par o impar). De la primera mitad extraeremos los caracteres empezando por la izquierda y los compararemos con los que extraigamos de la segunda mitad empezando por la derecha.Si todos coinciden es un palíndromo.

Paso 3: Contar el número de caracteres y dividir por dos (división entera).

En G3:

=COCIENTE(LARGO(F3);2)    [Resultado: 14]

De la primera frase, extraeremos los primeros 14 caracteres y los 14 últimos. En el ejemplo, la frase tiene 29 caracteres, de modo que el central (es la letra R) se desestima puesto que siempre ocupa el lugar decimoquinto, ya sea leyendo por la izquierda, ya sea leyendo por la derecha.

Paso 4: Extraer la mitad de los caracteres por la izquierda y la otra mitad por la derecha.

Seleccionamos H3:H50 y escribimos:

=EXTRAE(F3;FILA(INDIRECTO("1:"&G3));1)    [Terminar con Ctrl + Mayús + Intro]

Seleccionamos I3:I50 y escribimos:

=EXTRAE(F3;LARGO(F3)-FILA(INDIRECTO("1:"&G3))+1;1)    [Terminar con Ctrl + Mayús + Intro]

Ya hemos extraído los primeros 14 caracteres por la izquierda y por la derecha. Las últimas filas dan error porque hemos seleccionado un rango muy superior al que necesitamos (no podemos saber de antemano la longitud de la frase). Cuando compongamos la fórmula final prescindiremos de estas columnas y ya no habrá ningún error.

Comparando los caracteres de las columnas H e I por parejas obtendremos un conjunto de valores VERDADERO (si coinciden) y FALSO (si no coinciden).

Paso 5: Comparar las columnas H e I.

En J3:

=H3=I3

Extendemos la fórmula hasta la fila 50.

Paso 6: Comprobar el número de valores VERDADERO que hay en la columna J.

Si es un palíndromo, deberá haber 14 valores VERDADERO. Para contarlos podemos usar SUMA o, mejor aún, SUMAPRODUCTO, que nos evitará la necesidad de usar fórmulas matriciales.

En K3:

=SUMAPRODUCTO(--(J3:J16))=G3    [Resultado: VERDADERO]

Hemos seleccionado hasta la fila 16, ya que si incluimos más filas se produce un error. Dejando que Excel seleccione el número correcto de filas nos evitamos este problema.

Sustituimos la fórmula de K3 por:

=SUMAPRODUCTO(--(EXTRAE(F3; FILA(INDIRECTO("1:"&G3));1)=EXTRAE(F3;LARGO(F3)-FILA(INDIRECTO("1:"&G3))+1;1)))=G3

 Paso 7: Ponemos el resultado final.

En L3:

=SI(K3;"Sí";"No")

Vamos a crear una fórmula compuesta usando únicamente los datos de la celda F3. La fórmula definitiva la pondremos en C3.

En C3:

=SI(SUMAPRODUCTO(--(EXTRAE(F3;FILA(INDIRECTO("1:"&COCIENTE(LARGO(F3);2)));1)=EXTRAE(F3;LARGO(F3)-FILA(INDIRECTO("1:"&COCIENTE(LARGO(F3);2)))+1;1)))=COCIENTE(LARGO(F3);2);"Sí";"No")    [Resultado: Sí]

Extendemos la fórmula hasta la fila 18.

Podemos eliminar sin problemas las columnas G, H, I, J, K, y L; ya no son necesarias.

Paso 8: Crear dos nuevos "nombres" para eliminar las columnas E y F.

Hacemos clic en E3 (imprescindible), accedemos a Fórmulas + Asignar nombre y creamos el "nombre" siguiente:

En_mayus

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(MAYUSC($B3);"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U");"Ü";"U")

Hacemos clic en F3 (imprescindible), accedemos a Fórmulas + Asignar nombre y creamos el "nombre" siguiente:

Sin_puntos

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR( SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR( SUSTITUIR(SUSTITUIR(SUSTITUIR(MAYUSC($B3);"Á";"A");"É";"E");"Í";"I");"Ó"; "O");"Ú";"U");"Ü";"U");",";"");";";"");".";"");":";"");"!";"");"¡";"");"¿";"");"?";"");"""";"");" ";"")

Sustituimos la fórmula de C3 por la siguiente:

=SI(SUMAPRODUCTO(--(EXTRAE(Sin_puntos;FILA(INDIRECTO("1:"&COCIENTE(LARGO(Sin_puntos);2)));1)=EXTRAE(Sin_puntos;LARGO(Sin_puntos)-FILA(INDIRECTO("1:"&COCIENTE(LARGO(Sin_puntos);2)))+1;1)))=COCIENTE(LARGO(Sin_puntos);2);"Sí";"No")

Extendemos la fórmula hasta la fila 18 y eliminamos todas las columnas que hay a la derecha de la D.

Nota: En Excel 2003 es necesario cargar el complemento "Herramientas para análisis" para que la función COCIENTE esté disponible (Herramientas + Complementos + Herramientas para análisis). Si no se ha cargado, hay que sustituir COCIENTE por la función ENTERO. Con ENTERO, la fórmula de C3 sería:

=SI(SUMAPRODUCTO(--(EXTRAE(Sin_puntos;FILA(INDIRECTO("1:"&ENTERO(LARGO(Sin_puntos)/2)));1)=EXTRAE(Sin_puntos;LARGO(Sin_puntos)-FILA(INDIRECTO("1:"&ENTERO(LARGO(Sin_puntos)/2)))+1;1)))=ENTERO(LARGO(Sin_puntos)/2);"Sí";"No")

Descargar archivo (S98-Palíndromo.xls)

Descargar archivo (S98-Palíndromo.xlsx)

Palíndromos (1 de 3)

Siempre que puedo prescindo de las macros para resolver problemas en Excel. He empezado a hacer un ejercicio —sin usar macros— para comprobar si una frase es un palíndromo y me he encontrado con que el problema es más difícil de lo que había imaginado.

Una frase es un palíndromo si se puede leer igual de derecha a izquierda que de izquierda a derecha. Por ejemplo: Diré: "si Mari vive, ¿revivirá mi ser?". ¡Id!

El problema que se presenta es múltiple. Excel considera diferentes las mayúsculas y las minúsculas (D≠d), las letras sin acentuar y las acentuadas (é≠e) y, además, hay que prescindir de los signos de puntuación (puntos, comas, signos de admiración, guiones, etc).

Pongamos una frase en B3 y comencemos a resolver el ejercicio.

Paso 1: Extraer todos los caracteres, de uno en uno, en la columna E y ponerlos en mayúscula.

En E3:

=MAYUSC(EXTRAE($B$3;FILA(A1);1))

Extendemos la fórmula hasta la fila 100.

Paso 2: Crear un "nombre" para poder prescindir de esta columna.

Accedemos a Fórmulas + Asignar nombre y creamos el siguiente:

Deletreado

=MAYUSC(EXTRAE(Hoja1!$B$3;FILA(DESREF(Hoja1!$A$1;0;0;LARGO(Hoja1!$B$3)));1))

Borramos los datos de la columna E, seleccionamos E3:E100 y escribimos:

=Deletreado   [Terminamos con Ctrl + Mayús + Intro]

El resultado es el mismo que el obtenido con la fórmula anterior. Así pues, ya sabemos que el "nombre" Deletreado nos genera la lista de caracteres de la frase en mayúsculas. Borramos los datos de la columna E.

Paso 3: Crear una matriz con los caracteres que se deben eliminar. Esta matriz se guardará en otro "nombre" cuyo valor será:

No_valen

={".";",";":";":";"?";"¿";"¡";"!";" ";""""}

Hemos incluido los signos ortográficos habituales y el espacio en blanco. Podríamos haber añadido algunos más: guión corto, guión largo, guión bajo, comillas angulares («»), signo de párrafo (§), apóstrofo, asterisco, llave, paréntesis...

Probemos este nuevo "nombre" en la columna E. Seleccionamos E3:E20 y escribimos:

=No_valen   [Terminamos con Ctrl + Mayús + Intro]

Una vez que hayamos comprobado que la matriz es correcta, volvemos a borrar los datos de la columna E.

Paso 4: Eliminar los caracteres incluidos en la matriz No_valen.

Volvemos a crear el tercer "nombre".

Con_tilde

=SI(ESERROR(BUSCARV(Deletreado;No_valen;1;FALSO));Deletreado;"")

Comprobamos que este "nombre" genera la lista de caracteres de la frase prescindiendo de los caracteres de puntuación. Para ello, seleccionamos E3:E100 y escribimos:
=Con_tilde    [Terminamos con Ctrl + Mayús + Intro]

De nuevo, borramos la lista de la columna E.

Paso 5: Crear una matriz con los 6 caracteres que llevan tilde. Esta matriz se guardará en un "nombre" cuyo valor será:

Tildes

={"Á";"É";"Í";"Ó";"Ú";"Ü"}

Podemos volver a probarlo en la columna E, borrándolo posteriormente.

Paso 6: Crear una lista con los los caracteres de la frase excluidos los signos de puntuación y con las vocales acentuadas sustituidas por vocales sin acentuar. Esta lista estará contenida en un "nombre" con el siguiente valor:

Sin_tilde

=SI(ESERROR(COINCIDIR(Con_tilde;Tildes;0));Con_tilde;ELEGIR(COINCIDIR(Con_tilde;Tildes;0);"A";"E";"I";"O";"U";"U"))

Seleccionamos F3:F100 y escribimos:
=Sin_tilde    [Terminamos con Ctrl + Mayús + Intro]

Paso 7: Asignar números correlativos a cada carácter. Si la celda está en blanco tendrán el mismo número que la celda precedente.

En G3:

=CONTARA($F$3:F3)-CONTAR.BLANCO($F$3:F3)

Extendemos la fórmula hasta la fila 100.

Paso 8: Crear una lista sin celdas en blanco.

En H3:

=INDICE(Sin_tilde;COINCIDIR(FILA(A1);$G$3:$G$100;0))

Extendemos la fórmula hasta la fila 100.

Paso 9: Obtener el código ASCII de cada carácter.

En I3:

=CODIGO(H3)

Extendemos la fórmula hasta la fila 100.

Paso 10: Contar cuantas celdas tienen números en la columna I. Es decir, cuantos caracteres han quedado después de eliminar signos de puntuación y espacios en blanco.

En J3:

=CONTAR(I:I)

Paso 11: Poner la frase al revés.

En K3:

=INDIRECTO(DIRECCION($J$3+5-FILA();COLUMNA($H$1)))

Extendemos la fórmula hasta la fila 100.

Paso 12: Comparar las columnas H y K. Si los valores coinciden, poner 1; si no coinciden, poner 0.

En L3:

=--(H3=K3)

Extendemos la fórmula hasta la fila 100.

Paso 13: Sumar los números de la columna L.

En M3:

=SUMA(DESREF(L3;0;0;J3))

Paso 14: Comparar el valor de M3 con el valor de J3. Si coinciden, la frase es una palíndromo; en caso contrario, no lo es.

En N3 y en C3:

=SI(J3=M3;"Sí";"No")

Descargar archivo (SE98-Palíndromo.xls)

Descargar archivo (SE98-Palíndromo.xlsx)

Extraer determinados campos de una base de datos

Cuando estaba escribiendo el último artículo, que trata de la manera de importar datos de la Web, recordé una consulta que me hicieron para extraer determinados campos de una base de datos de tipo texto que usaba la coma (,) como delimitador.

Si abrimos la base de datos (S56-Registros.txt) con el Bloc de notas de Windows podremos ver los campos que componen cada registro (creo recordar que correspondían al programa de una máquina de control numérico). Debajo se muestra la tabla que se quiere obtener.

Necesitamos extraer dos campos de cada registro (el tercero y el sexto) y agruparlos de tres en tres de la siguiente manera: el tercer campo de cada tres registros consecutivos seguido del sexto campo de los mismos tres registros. A continuación, de los tres siguientes, y así sucesivamente.

Abrimos un libro en blanco, seleccionamos todas las columnas desde la A hasta la I, pulsamos Ctrl +1 para entrar en la ventana Formato de celdas y, en la ficha Número, ponemos 6 decimales y separador de miles.

Accedemos a Datos + Desde texto. En el cuadro de diálogo Importar archivo de texto, buscamos el fichero S56-Registros.txt y pulsamos el botón Importar. Se abrirá el asistente para importar texto.

Paso 1 de 3: Nos aseguramos de que el botón Delimitados esté marcado y de que comenzamos a importar los datos desde la fila 1. Pulsamos Siguiente.

Paso 2 de 3: Desmarcamos Tabulación, marcamos Coma y pulsamos Siguiente.

Paso 3 de 3: Hay 7 columnas pero sólo tenemos que importar datos de las columnas 2 y 5. Seleccionamos la primera columna y marcamos el botón No importar columna (saltar). Hacemos lo mismo con las columnas 3, 4, 6 y 7.

Ahora, debemos pulsar el botón Avanzadas para indicar que en los datos originales los decimales están separados por puntos (.) y los miles por comas (,). Además, quitaremos la marca del cuadro de verificación Signo menos delante de los números negativos.

Pulsamos Aceptar para volver al asistente y terminamos haciendo clic en Finalizar.

En el cuadro de diálogo Importar datos sólo necesitamos indicar el lugar donde queremos colocar los datos; por ejemplo, $A$1. Si queremos, podemos pulsar el botón Propiedades para acceder al cuadro de diálogo correspondiente, que es similar al del ejercicio Importar datos de la Web. Terminamos pulsando Aceptar.

El resultado es el siguiente:

Ya tenemos todos los datos y, ahora, debemos agruparlos convenientemente. Lo haremos a partir de la columna D.

En D1:

=INDICE($A$1:$A$219;ELEGIR(RESIDUO(FILA()-1;3)+1; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3-1; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3-2);1)

Extendemos la fórmula hasta la celda F1.

En G1:

=INDICE($B$1:$B$219;ELEGIR(RESIDUO(FILA()-1;3)+1; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3-1; RESIDUO(FILA()+2;3)+COLUMNA(A$1)+(FILA()-1)*3-2);1)

Extendemos la fórmula hasta la celda I1.

Seleccionamos D1:I1 y copiamos las fórmulas hacia abajo hasta la fila 73.

Si no hemos activado la actualización automática (ventana Propiedades) y la base de datos sufre alguna alteración y queremos que dichos cambios se concreten en nuestra hoja, bastará hacer clic con el botón derecho en cualquier número de las columnas A o B y, en el menú emergente, seleccionar Actualizar. 

Podemos hacer una prueba abriendo el fichero S56-Registros.txt con el Bloc de notas y sustituyendo algunos valores de las columnas 2 y 5. Guardamos los cambios y actualizamos la hoja seleccionando Actualizar en el menú contextual de las columnas A o B.

Descargar archivos (S56-Registros.txt)

Descargar archivos (S56-Registros.xls)

Descargar archivos (S56-Registros.xlsx)

Importar datos de la Web

Tenemos una serie de productos cuyos precios están expresados en euros y necesitamos convertirlos a otras monedas. Además, los resultados deben actualizarse automáticamente (por ejemplo, cada cinco minutos) siguiendo las fluctuaciones del mercado de divisas.

La solución consiste en mantener un enlace permanente con una página Web que actualice, en tiempo real, las cotizaciones de las distintas monedas; por ejemplo: http://es.money.msn.com/investor/market/ratestable.asp

Los datos importados, y periódicamente actualizados, los almacenaremos en la hoja "Cambio".

Hacemos clic en la celda B2 de la hoja "Cambio" y accedemos a Datos + Desde web. Se mostrará la pagina principal de nuestro navegador.

Si es necesario, hacemos doble clic en la barra de título del navegador para maximizarlo y escribimos la dirección de la que queremos obtener los datos. Después, hacemos clic en las flechas amarillas de las tablas que queramos importar. Estas flechas se transformarán en cuadros de verificación de color verde.

Terminamos pulsando el botón Importar. Excel mostrará el siguiente cuadro de diálogo:

Hacemos clic en Propiedades y ponemos las siguientes:

Hemos marcado la casilla de actualización al abrir; de esta manera, cada vez que abramos el libro, Excel irá a la web asociada y buscará los datos actualizados.También hemos fijado en 5 minutos las actualizaciones sucesivas y hemos desmarcado el ajuste del ancho de la columna ya que, previamente, habíamos establecido un formato adecuado. Terminamos pulsando dos veces Aceptar.

Ya tenemos la tabla de equivalencias y podemos seguir con nuestro ejercicio.

En D3 de la hoja Productos:

=C3*Cambio!$F$7

En E3:

=D3*Cambio!$G$7

En F3:

=C3*Cambio!$C$7

Extendemos las tres fórmulas hasta la fila 12.

Si esperamos 5 minutos comprobaremos que las hoja se actualiza automáticamente.

Pulsando con el botón derecho en la tabla de la hoja "Cambio" accederemos al menú contextual en el que podremos elegir tres opciones importantes: Modificar consulta, Propiedades del rango de datos y Actualizar.

Descargar archivo (SE17-Valor en otras divisas.xls)

Descargar archivo (SE17-Valor en otras divisas.xlsx)

Suma de los dígitos de un número

Dado un número entero (por ejemplo, el 35012690784), ¿a cuánto asciende la suma de sus dígitos? (3+5+0+1+2+6+9+0+7+8+4=45).

El procedimiento que vamos a emplear para resolver el problema consta de 5 pasos:

1. Contar los dígitos que tiene el número (11).
2. Generar una lista de números desde 1 hasta el total de dígitos (del 1 al 11).
3. Extraer (función EXTRAE) uno a uno todos los dígitos creando una matriz.
4. Transformar los caracteres de la matriz (EXTRAE funciona en modo texto) en valores numéricos.
5. Sumar los números.

Usaremos el rango de celdas E2:I22 para hacer el ejercicio paso a paso.

En E4:

=LARGO($C$2)    [Resultado: 11]

Seleccionamos F4:F22 y escribimos la fórmula:

=FILA(INDIRECTO("1:"&LARGO($C$2)))    [Terminamos con Ctrl + Mayús + Intro]

Hemos creado una lista de números consecutivos que comienza en 1 y acaba en 11 (número de cifras que tiene el valor de la celda C2). Este método nos permite generar una matriz de números consecutivos que comienzan y acaban donde nosotros queramos, Por ejemplo, si en M4:M50 ponemos: =FILA(INDIRECTO("1:"&LARGO("Amazonas"))) obtendremos la lista del 1 al 8 (longitud de la palabra "Amazonas"). Para crear la lista del 20 al 35, la fórmula sería: =FILA(INDIRECTO("20:35")). No debemos olvidar que se trata de una fórmula matricial y que se debe acabar con Ctrl + Mayús + Intro.

También se puede utilizar la función DESREF en vez de INDIRECTO. Así, la fórmula de la columna F, utilizando DESREF, sería: =FILA(DESREF($A$1;0;0;LARGO($C$2)))

Este tipo de fórmulas tiene su verdadera razón de ser cuando se crea un "nombre" que ha de ser utilizado, posteriormente, en otra fórmula, o cuando sirve de entrada a otra función.

Seleccionamos G4:G22 y escribimos:

=EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1)    [Terminamos con Ctrl + Mayús + Intro]

Ya hemos conseguido la lista de todos los dígitos del número que hay en C2. Como los dígitos son tratados como textos debemos transformarlos en números para poderlos sumar. Esto se consigue multiplicándolos por 1. Como es lógico, también se puede multiplicar dos veces por -1, o bien, poniendo dos signos menos delante de la fórmula. Así pues, tenemos tres opciones:

Seleccionamos H4:H22 y escribimos cualquiera de estas fórmulas:

1. =1*EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1)    [Terminamos con Ctrl + Mayús + Intro]
2. =(-1)*(-1)*EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1)    [Terminamos con Ctrl + Mayús + Intro]
3. =--EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1)    [Terminamos con Ctrl + Mayús + Intro]

En I4:

=SUMA(--EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1))    [Terminamos con Ctrl + Mayús + Intro. Resultado: 45]

El resultado es correcto. Sólo nos falta copiar esta última fórmula en la celda C5 y eliminar las fórmulas auxiliares que hemos puesto en E2:I22.

Sustituyendo la función SUMA por la función SUMAPRODUCTO transformamos la fórmula matricial en una fórmula normal.

En C6:

=SUMAPRODUCTO(--EXTRAE($C$2;FILA(INDIRECTO("1:"&LARGO($C$2)));1))    [Resultado: 45]

La tercera solución la obtendremos combinando DESREF y SUMAPRODUCTO.

En C7:

=SUMAPRODUCTO(--EXTRAE($C$2;FILA(DESREF($A$1;0;0;LARGO($C$2)));1))    [Resultado: 45]

Descargar archivo (S57-Sumar dígitos de un número.xls)

Descargar archivo (S57-Sumar dígitos de un número.xlsx)

Semáforo

Imaginemos una hoja de cálculo donde se muestren las ventas mensuales de una empresa. Si se supera un determinado valor el resultado será satisfactorio y lo señalaremos con un indicador verde. Si las ventas no alcanzan una cifra prefijada, el indicador será rojo. En cualquier otro caso, el indicador deberá ser amarillo.

Emplearemos este código de colores utilizando la imagen de un semáforo. Podemos buscar imágenes de semáforos en Internet o construirlo nosotros. Si optamos por esta solución, bastan las herramientas de dibujo de Excel.

1. Con Insertar + Formas, hacemos un rectángulo redondeado y tres círculos (Figura 1).
2. Con Inicio + Buscar y seleccionar + Seleccionar objetos, seleccionamos los tres círculos (Figura 2) y accedemos a Herramientas de dibujo + Formato + Alinear + Alinear a la derecha (Figura 3).
3. Con los tres círculos seleccionados, accedemos a Herramientas de dibujo + Formato + Alinear + Distribuir verticalmente (Figura 4).
4. Seleccionamos el círculo de arriba y accedemos a Formato + Relleno de forma + Rojo. Ahora, accedemos a Formato + Contorno de forma + Rojo (Figura 5).
5. Utilizando este procedimiento, coloreamos de gris los otros dos círculos y de negro el rectángulo redondeado (Figura 6).
6. Arrastramos el rectángulo hasta situarlo sobre los tres círculos (Figura 7). Quizás tengamos que ajustar la altura y la anchura. Usaremos Ctrl + Flechas para un ajuste fino.
7. Seleccionamos las cuatro figuras y, manteniendo pulsadas las teclas Mayúscula y Ctrl, arrastramos el conjunto a la derecha para obtener dos copias exactas (Figura 8).
8. Coloreamos los círculos de los dos semáforos de la derecha de amarillo y verde (Figura 9).
9. Seleccionamos las cuatro figuras del semáforo rojo y accedemos a Herramientas de dibujo + Agrupar + Agrupar. Repetimos la operación con los otros dos semáforos.

Ahora, utilizando Paint, convertiremos cada semáforo en un fichero .jpg.

1. Seleccionamos el semáforo rojo y pulsamos Ctrl + C.
2. Abrimos Paint y pulsamos Ctrl +V.
3. Accedemos a Imagen + Recortar.
4. Guardamos el dibujo con el nombre E81-Rojo.jpg y salimos de Paint.
5. Repetimos estos pasos con los otros semáforos y los guardamos con los nombres E81-Amarillo.jpg y E81-Verde.jpg

Abrimos un nuevo libro de Excel y creamos una hoja, llamada "Semáforos", en la que insertamos los semáforos agrupados de tres en tres (Insertar + Imagen). Debemos hacer las celdas C2, C3 y C4 suficientemente grandes para que quepan dentro las tres imágenes.

En la hoja Datos, hacemos clic en B2, seleccionamos Datos + Validación de datos y ponemos los valores siguientes:

Accedemos a Fórmulas + Asignar nombre y creamos el "nombre" siguiente:

Color

=DESREF(Semáforos!$C$2;COINCIDIR(Datos!$B$2;Semáforos!$B$2:$B$4;0)-1;0;1;1)

En la celda B2 de la hoja Datos, elegimos el color Rojo.

Hacemos clic en el primer semáforo verde de la hoja Semáforos y pulsamos Ctrl + C.

Hacemos clic en la celda D2 de la hoja Datos y pulsamos Ctrl + V.

Con el semáforo seleccionado, escribimos en la barra de fórmulas: =Color

Al pulsar Intro aparecerán tres semáforo rojos.

Probamos las otras opciones de la lista.

Descargar archivo (S81-Semáforo.xls)

Descargar archivo (S81-Semáforo.xlsx)

Las nuevas funciones «___.SI.CONJUNTO»

Las funciones CONTAR.SI, SUMAR.SI y PROMEDIO.SI solamente admiten un criterio. Pero, a veces, es necesario contar, sumar o promediar rangos de celdas que cumplan más de una condición. En Excel 2007 y 2010, hay tres funciones nuevas que sirven para este propósito: CONTAR.SI. CONJUNTO, SUMAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO.

Las tres funciones tienen una sintaxis similar. Por ejemplo, para CONTAR.SI. CONJUNTO la ayuda de Excel muestra la siguiente información.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONTAR.SI.CONJUNTO(rango_criterio1;criterio1;[rango_criterio2;criterio2]...)

Aplica criterios a las celdas en varios rangos y cuenta cuántas veces se cumplen dichos criterios.

• rango_criterio1: Obligatorio. El primer rango en el que se evalúan los criterios asociados.
• criterio1: Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que determinan las celdas que se van a contar. Por ejemplo, los criterios que se pueden expresar como 32, ">32", B4, "manzanas" o "32".
• rango_criterio2;criterio2...: Opcional. Rangos adicionales y criterios asociados. Se permiten hasta 127 pares de rango/criterio.

Importante: Cada rango adicional debe tener la misma cantidad de filas y columnas que el argumento rango_criterio1. No es necesario que los rangos sean adyacentes.

Observaciones:

• Los criterios de cada rango se aplican a una celda cada vez. Si todas las primeras celdas cumplen los criterios asociados, el número aumenta en 1. Si todas las segundas celdas cumplen los criterios asociados, el número aumenta en 1 nuevamente y así sucesivamente hasta evaluar todas las celdas.
• Si el argumento de los criterios hace referencia a una celda vacía, la función CONTAR.SI.CONJUNTO trata dicha celda como un valor 0.

En los criterios se pueden usar caracteres comodín, el signo de interrogación (?) y el asterisco (*). El signo de interrogación se corresponde con un solo carácter y el asterisco se corresponde con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter que desea buscar.
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Vamos a aplicar las tres funciones en un ejercicio basado en la siguiente tabla:

En J2 calcularemos las medallas de oro obtenidas por los deportistas europeos de atletismo menores de 30 años.

En J2:

=SUMAR.SI.CONJUNTO(E2:E24;B2:B24;"Europa";D2:D24;"<30";C2:C24;"Atletismo")

En J3 hallaremos la edad media de los deportistas americanos que no practican el atletismo y han obtenido más de una medalla de plata.

En J3:

=PROMEDIO.SI.CONJUNTO(D2:D24;B2:B24;"América";C2:C24;"<>Atletismo";F2:F24;">1")

En J4 contaremos cuántos deportistas africanos de vela han obtenido alguna medalla de bronce.

En J4:

=CONTAR.SI.CONJUNTO(B2:B24;"África";C2:C24;"Vela";G2:G24;">0")

Descargar archivo (S50-Funciones .CONJUNTO.xlsx)

Ordenación por varios criterios

En pasados artículos hemos estudiado diversos métodos de ordenación por un solo criterio. Pero, en ocasiones, es posible hacer ordenaciones por múltiples criterios, como se comprueba al ordenar la tabla B3:D15 por la columna Indicador.

Se observa que la tabla ha quedado perfectamente ordenada de mayor a menor por la columna Indicador, pero las ciudades que tienen el mismo valor no están ordenadas por orden alfabético (Nantes, París, Burdeos y Varsovia).

Nuestro ejercicio consistirá en hacer una ordenación triple: en primer lugar, por Indicador; en segundo lugar, por País y, en tercer lugar, por Ciudad. Pero antes, veamos cómo podemos conseguir la ordenación simple por Indicador.

En F4:

=JERARQUIA(D4;$D$4:$D$15;)+CONTAR.SI($D$4:D4;D4)-1

Extendemos la fórmula hasta la fila 15.

En H4:

=INDICE($B$4:$D$15;COINCIDIR(FILA(A1);$F$4:$F$15;0);COLUMNA(A1))

Extendemos la fórmula hasta la fila 15. Seleccionamos H4:H15 y extendemos las fórmulas hasta la columna J. La primera parte del ejercicio está hecha.

Para realizar la ordenación por tres criterios, empezaremos creando una ordenación por dos criterios: primero, por País y, en segundo lugar, por Ciudad.

La ordenación será muy sencilla si creamos un campo combinado en cada fila formado por la concatenación del País y la Ciudad. Luego, asignaremos un número de orden a cada combinación.

En F4:

=B4&C4

Extendemos la fórmula hasta la fila 15.

En G4:

=CONTAR.SI($F$4:$F$15;"<="&F4)

Extendemos la fórmula hasta la fila 15.

Ahora, ya podemos crear la ordenación doble por País y Ciudad.

En I4:

=INDICE($B$4:$D$15;COINCIDIR(FILA(A1);$G$4:$G$15;0);COLUMNA(A1))

Extendemos la fórmula hasta la fila 15. Seleccionamos I4:I15 y extendemos la fórmula hasta la columna K.

En la columna M crearemos la "jerarquía" de la columna K.

En M4:

=JERARQUIA(K4;$K$4:$K$15)+CONTAR.SI($K$4:K4;K4)-1

Extendemos la fórmula hasta la fila 15. Ahora, ya podemos crear la triple ordenación.

En O4:

=INDICE($I$4:$K$15;COINCIDIR(FILA(A1);$M$4:$M$15;0);COLUMNA(A1))

Extendemos la fórmula hasta la fila 15. Seleccionamos O4:O15 y extendemos las fórmulas hasta la columna Q.

Descargar archivo (SE16-Múltiples.xls)

Descargar archivo (SE16-Múltiples.xlsx)