Extraer elementos repetidos

Vamos a continuar con la idea expresada en el último artículo, Extraer elementos no repetidos, pero, ahora, extraeremos los elementos repetidos. El problema se puede definir de esta manera: dada una lista de nombres, extraer, únicamente, aquéllos que estén repetidos.

La hoja donde vamos a hacer el ejercicio se llama Repetidos.

Primera solución: Poner fondo amarillo a los países repetidos

Accedemos a Fórmulas + Asignar nombre + Definir nombre y creamos el nombre Países con la siguiente definición:

Países

=DESREF(Repetidos!$B$2;1;0;CONTARA(Repetidos!$B:$B)-1;1)

Seleccionamos B3:B25 y vamos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula siguiente:

=Y(CONTAR.SI(Países;B3)<>1;B3<>"")

... pulsamos el botón Formato y, en la pestaña Relleno, elegimos el color amarillo.

Creamos una segunda regla con esta fórmula:

=NO(ESBLANCO(B3))

... pulsamos el botón Formato y, en la pestaña Bordes, elegimos el color gris y Contorno.

Segunda solución: Copiar los nombres repetidos en otra columna en orden invertido

Seleccionamos H3:H16 y escribimos:

=CONTAR.SI(Países;Países)<>1      [Terminar con Ctrl + Mayús + Intro]

Seleccionamos I3:I16 y escribimos:

=(H3:H16)*FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

Seleccionamos J3:J16 y escribimos:

=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

Seleccionamos K3:K16 y escribimos:

=K.ESIMO.MAYOR(I3:I16;J3:J16)      [Terminar con Ctrl + Mayús + Intro]

Seleccionamos L3:L16 y escribimos:

=INDICE(Países;K3:K16)      [Terminar con Ctrl + Mayús + Intro]

Omito la justificación de estos pasos porque ya se han explicado en el artículo Extraer elementos no repetidos.

Seleccionamos D3:D25 y escribimos:

=INDICE(Países;K.ESIMO.MAYOR((CONTAR.SI(Países;Países)<>1)*FILA(Países)-2;FILA(Países)-2))     [Terminar con Ctrl + Mayús + Intro]

Los formatos condicionales para el rango D3:D25 son: 

=ESERROR(D3)

... y, en la pestaña Fuente, color blanco.

=NO(ESERROR(D3))

... y, en la pestaña Bordes, color gris y Contorno.

Esta solución tiene el inconveniente de que los nombres aparecen repetidos tantas veces como lo están en la lista original. Quizás sería mejor que sólo apareciesen una vez, y esto es lo que vamos a tratar de conseguir con la tercera solución.

Tercera solución: Copiar una sola vez los nombres repetidos en orden natural

Aislamos los nombres de los países repetidos.

En N3:

=SI(B3="";"";SI(CONTAR.SI(Países;B3)<>1;B3;""))    [Copiamos la fórmula hasta la fila 25]

Vamos contando las veces que cada nombre va apareciendo a medida que bajamos en la lista de la columna N. También contaremos las apariciones de las celdas en blanco aunque después tendremos que desestimarlas.

En O3:

=CONTAR.SI($N$3:N3;N3)    [Copiamos la fórmula hasta la fila 25]

Nos quedamos con los nombres que han aparecido la primera vez.

En P3:

=SI(Y(N3<>"";O3<>1);"";N3)    [Copiamos la fórmula hasta la fila 25]

Si en la columna P hay un nombre, ponemos su posición en la lista original; en caso contrario, ponemos un número muy grande (10³⁰⁰).

En Q3:

=SI((P3)="";10^300;FILA()-2)     [Copiamos la fórmula hasta la fila 25]

Generamos una lista de números consecutivos del 1 al 23.

En R3:

=FILA()-2     [Copiamos la fórmula hasta la fila 25]

Ordenamos los números de la columna Q.

En S3:

=K.ESIMO.MENOR($Q$3:$Q$25;R3)      [Copiamos la fórmula hasta la fila 25]

Extraemos los nombres de los países.

En F3:

=INDICE(Países;S3)      [Copiamos la fórmula hasta la fila 25]

Ponemos en la columna F un formato condicional similar el de la columna D y el ejercicio quedará terminado.

Si utilizamos la segunda solución podremos eliminar las columnas H a L porque hemos creado una fórmula matricial compuesta en la columna D. En la tercera solución no he encontrado una fórmula que permita eliminar las columnas auxiliares.

Descargar archivo (S346-Repetidos.xlsx)

Descargar archivo (S346-Repetidos.xls)

Extraer elementos no repetidos

Si en la columna B hay elementos repetidos y no repetidos y queremos extraer los que no están repetidos, podremos destacarlos con un color, utilizando Formato condicional, o copiarlos en otro lugar de la hoja usando fórmulas.

Daremos tres soluciones al ejercicio. La primera consistirá en poner un fondo verde a las celdas que cumplen el requisito mencionado Las otras dos consistirán en obtener sendas copias en las columnas D (orden invertido) y F (orden natural). Las columnas H:R se usarán para hacer cálculos intermedios. Finalizado el ejercicio podrán borrarse.

Primera solución: Poner fondo verde a los países no repetidos

Si prevemos que la lista vaya a ampliarse o reducirse, lo mejor será crear un nombre dinámico con la función DESREF. Este nombre nos servirá en cualquiera de los métodos que usemos para resolver el ejercicio.

Accedemos a Fórmulas + Administrador de nombres. Pulsamos el botón Nuevo y creamos el nombre Países:

NoRepetidos es el nombre de la hoja.

Vamos a aplicar el Formato condicional al rango B3:B25, pero si se van a añadir más nombres, tendremos que ampliarlo.

Seleccionamos B3:B25 y vamos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula siguiente:

=CONTAR.SI(Países;B3)=1

Pulsamos el botón Formato y, en la pestaña Relleno del cuadro de diálogo Formato de celdas, elegimos el color verde.

Necesitamos una nueva condición para poner bordes a las celdas. Volvemos a crear una nueva regla con la fórmula siguiente:

=NO(ESBLANCO(B3))

En este caso, hay que ir a la pestaña Bordes y elegir un color gris y la opción Contorno.

El resultado será, como se muestra en la primera figura, que las nombres no repetidos aparecerán sobre un fondo verde y las celdas tendrán un borde gris.

Segunda solución: Copiar los nombres no repetidos en otra columna en orden invertido

Seleccionamos H3:H16 y escribimos:

=CONTAR.SI(Países;Países)=1     [Terminar con Ctrl + Mayús + Intro]

Con esta fórmula contamos el número de veces que aparece cada país en la lista. Si aparece una vez, la fórmula devuelve VERDADERO; en caso contrario, devuelve FALSO.

Seleccionamos I3:I16 y escribimos:

=(H3:H16)*FILA(Países)-2     [Terminar con Ctrl + Mayús + Intro]

FILA(Países)  devuelve el número de la fila de cada país. Si le restamos dos unidades obtendremos números consecutivos del 1 en adelante (el primer país está en la fila 3). Si multiplicamos la matriz (H3:H16) (recordemos que VERDADERO equivale a 1 y FALSO a cero) por la matriz FILA(Países)-2, habremos convertido la columna H en una serie de números que indican la posición en la lista (en el caso de que el dato VERDADERO) o -2 (si es FALSO).

Ahora, necesitamos una serie correlativa de números del 1 al 14 (es el total de elementos de la lista).

Seleccionamos J3:J16 y escribimos:

=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

Esta serie la usaremos en el siguiente paso. Vamos a utilizar K.ESIMO.MAYOR para ordenar la columna I de mayor a menor.

Seleccionamos K3:K16 y escribimos:

=K.ESIMO.MAYOR(I3:I16;J3:J16)      [Terminar con Ctrl + Mayús + Intro]

Finalmente, con la función INDICE determinaremos los valores de la columna B que están en las filas indicadas en la columna K.

Seleccionamos L3:L16 y escribimos:

=INDICE(Países;K3:K16)      [Terminar con Ctrl + Mayús + Intro]

Naturalmente, cuando intenta encontrar un dato en la fila -2, la fórmula no encuentra nada y devuelve un error. No importa porque vamos a crear una fórmula compuesta en D3:D25 y, luego, podremos borrar las columnas H:L.

Seleccionamos D3:D25 y escribimos:

=INDICE(Países;K.ESIMO.MAYOR((CONTAR.SI(Países;Países)=1)*FILA(Países)-2;FILA(Países)-2))     [Terminar con Ctrl + Mayús + Intro]

Tenemos que aplicar un formato condicional para poner bordes y ocultar los errores (fuente blanca).

La primera regla que hay que poner en D3:D25 es:

=ESERROR(D3)

... y, en la pestaña Fuente, elegimos el color blanco.

La segunda regla es:

=NO(ESERROR(D3))

... y, en la pestaña Bordes, elegimos el color gris y la opción Contorno.

Tercera solución: Copiar los nombres no repetidos en otra columna en orden natural

Aislamos los países no repetidos en N3:N16. 

Seleccionamos N3:N16 y escribimos:

=SI(CONTAR.SI(Países;Países)=1;Países;"")      [Terminar con Ctrl + Mayús + Intro]

A los países que hemos aislado les asociamos su posición en la lista original. A las celdas en blanco les asociamos un número muy grande; por ejemplo, 10³⁰⁰

Seleccionamos O3:O16 y escribimos:

=SI((N3:N16)="";10^300;FILA(Países)-2)     [Terminar con Ctrl + Mayús + Intro]

Generamos una lista de números del 1 al 14 (hay 14 países en la lista).

Seleccionamos P3:P16 y escribimos:

=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

Ordenamos la columna P de menor a mayor.

Seleccionamos Q3:Q16 y escribimos:

=K.ESIMO.MENOR(O3:O16;P3:P16)      [Terminar con Ctrl + Mayús + Intro]

Los números mayores (que corresponden a los países repetidos) están al final de la lista. Usando INDICE buscaremos los países que ocupan en la lista original las posiciones indicadas en la columna P. Lógicamente, no hay ningún país en la posición 10³⁰⁰ y, en consecuencia, se obtendrá un error.

Seleccionamos R3:R16 y escribimos:

=INDICE(Países;Q3:Q16)      [Terminar con Ctrl + Mayús + Intro]

Ponemos la fórmula definitiva en la columna F.

Seleccionamos F3:F25 y escribimos:

=INDICE(Países;K.ESIMO.MENOR(SI((SI(CONTAR.SI(Países;Países)=1;Países;""))="";10^300;FILA(Países)-2);FILA(Países)-2))      [Terminar con Ctrl + Mayús + Intro]

El formato condicional que debemos poner a la columna F es similar al de la columna D.

Seleccionamos F3:F25 y ponemos la primera regla:

=ESERROR(F3)

... y, en la pestaña Fuente, elegimos el color blanco.

La segunda regla es:

=NO(ESERROR(F3))

... y, en la pestaña Bordes, elegimos el color gris y la opción Contorno.

Descargar archivo (S346-No_Repetidos.xlsx)

Descargar archivo (S346-No_Repetidos.xls)

Utilización de símbolos para personalizar formatos

En Excel 2010, utilizando Formato condicional, es muy fácil asociar símbolos de diferentes formas y colores a las celdas según sus valores. Combinando varios formatos condicionales y utilizando Formato personalizado, podemos conseguir que los valores numéricos también cambien de color. Por ejemplo, en la lista de resultados del Gran Premio de Australia de F1 del año 2009, hemos añadido una columna para calcular, al final de la carrera, los puestos que adelantó cada piloto con respecto a su posición en la parrilla de salida.

El ejercicio lo resolveremos por tres métodos distintos (en la figura, son las columnas etiquetadas como Uno, Dos y Tres). Los pilotos que terminaron la carrera en el mismo puesto que tenían en la parrilla de salida están marcados en marrón; los que avanzaron algún puesto están en verde; y los que se retrasaron, aparecen en rojo.

Primer método: Formato condicional (no funciona en Excel 2003)

En J4:

=H4-B4    (Copiamos la fórmula hasta la fila 23)

Seleccionamos J4:J23, accedemos a Inicio + Formato condicional + Nueva regla y elegimos Aplicar formato a todas las celdas según sus valores. Rellenamos el cuadro de diálogo con estos valores:

El resultado será:

Para que los números queden del mismo color que las flechas podemos utilizar el formato condicional o el personalizado. Por el momento, seguiremos usando el condicional y dejaremos el personalizado para otro caso.

Volvemos a seleccionar J4:J23 y accedemos a Inicio + Formato condicional + Administrar reglas. Hacemos clic en Nueva regla y elegimos Utilice una fórmula que determine las celdas para aplicar formato. La fórmula que pondremos será: =J4=0

Pulsamos el botón Formato y, en el apartado Color de la pestaña Fuente, seleccionamos un color anaranjado. Terminamos pulsando Aceptar para pasar al siguiente formato.

Volvemos a pulsar Nueva regla, ponemos la fórmula: =J4>0 y elegimos una fuente de color verde. Para terminar, pulsamos de nuevo en Nueva regla, ponemos la fórmula: =J4<0 y seleccionamos la fuente roja.

Han sido necesarios cuatro formatos condicionales.

Segundo método: Formato personalizado (vale para cualquier versión de Excel)

En L4:

=H4-B4    (Copiamos la fórmula hasta la fila 23)

Necesitamos tres símbolos de tipo flecha; por ejemplo, de la fuente Arial podemos sacar ▲, ▼ y ►. Lo más cómodo es usar una calda vacía y colocar en ella los tres símbolos. Se hace así:

• Hacemos clic en Q1 (vale cualquier celda vacía) y le aplicamos la fuente Arial.
• Accedemos a Insertar + Símbolo y elegimos fuente Arial.

• Hacemos doble clic en cada uno de los tres símbolos (o elegimos Insertar) y pulsamos Cerrar. En Q1 aparecerá: ▲▼►.
• Nos ponemos en Q1 y pulsamos Ctrl + C para copiar los tres símbolos en el portapapeles. Ya estamos preparados para el siguiente paso.

Seleccionamos L4:L23 y pulsamos Ctrl + 1 para entrar en Formato de celdas. En la pestaña Número, elegimos la opción Personalizada y, en Tipo,  ponemos: [Verde]"▲ "0;[Rojo]"▼ "-0;[Color45]"► "0;

Lógicamente, los símbolos copiados en el portapapeles son para insertarlos en los lugares correspondientes del formato. Cuando tengamos que escribir uno de ellos, pulsamos Ctrl + V y se insertarán los tres. Luego, tendremos que eliminar los que sobren.

Para finalizar, borramos los símbolos de la celda Q1.

Tercer método: Formato condicional + 2 columnas (vale para cualquier versión de Excel)

En este método, los números y los símbolos van en celdas separadas. Podemos poner diferentes símbolos extraídos de distintas fuentes en una zona vacía de la hoja y utilizar los que queramos. Por ejemplo, en Q4:T6 ponemos:

Para insertar estos símbolos, a las celdas R6:T6 debemos ponerles formato Wingdings 3, ya que de no hacerlo, se insertará un carácter distinto. Lo mismo vale para los otros rangos.

Si vamos a usar los símbolos de la fuente Wingdings 3, al rango N4:N23 deberemos asignarle fuente Wingdings 3.

En O4:

=H4-B4     (Copiamos la fórmula hasta la fila 23)

En N4:

=ELEGIR(SIGNO(O4)+2;$S$6;$T$6;$R$6)     (Copiamos la fórmula hasta la fila 23)

La función SIGNO devuelve -1 (si el número es negativo), 0 (si es cero) ó 1 (si es positivo). Añadiéndole 2, nos aseguramos que SIGNO(O4)+2 sea 1, 2 ó 3. La función ELEGIR comprueba el valor de SIGNO(O4)+2 y devuelve el símbolo de S6 (si es 1), de T6 (si es 2) o de R6 (si es 3).

Para poner el color, seleccionamos N4:O23 y accedemos a Inicio + Formato condicional + Nueva regla. Siguiendo el procedimiento descrito en el primer caso, creamos las siguientes condiciones:

Si preferimos usar los símbolos de la fuente Wingdings tendremos que poner formato Wingdings en el rango N4:N23 y sustituir la fórmula de N4 por: =ELEGIR(SIGNO(O4)+2;$S$5;$T$5;$R$5)

Para los símbolos en Arial: =ELEGIR(SIGNO(O4)+2;$S$4;$T$4;$R$4)

Descargar archivo (S293-GP_Australia.xlsx)

Descargar archivo (S293-GP_Australia.xls)

Contar el número de palabras que hay en un texto

En un texto, todas las palabras están separadas entre sí por espacios en blanco. Es cierto que, además, pueden existir paréntesis, corchetes, llaves, comillas, guiones, signos de interrogación y exclamación, signos de puntuación, etc. Pero, siempre hay un espacio en blanco antes de la siguiente palabra. Además, no hay espacios entre las palabras y los signos mencionados.

Así pues, para saber el número de palabras que hay en un texto basta contar el número de espacios en blanco y sumarle uno.

En B3 pondremos el texto y en E2 calcularemos el número de palabras que contiene. Usaremos las filas 6 a 10 para hacer cálculos intermedios. Finalizado el ejercicio, podremos borrar estas filas ya que crearemos una fórmula compuesta en E2.

Resolveremos el ejercicio en cinco pasos:

1. Nos aseguramos de que entre las palabras haya un espacio y sólo uno. Este paso lo damos porque, en ocasiones, pulsamos, sin querer, más de una vez la barra espaciadora para separar las palabras.
2. Calculamos el número de caracteres (incluidos los espacios) que tiene el texto.
3. Eliminamos todos los espacios.
4. Calculamos el número de caracteres del nuevo texto (que ya no tiene espacios).
5. Restamos ambos valores y le sumamos uno.

La función ESPACIOS elimina todos los espacios sobrantes que hay en una frase. Quita los espacios que hay al principio o al final del texto y deja un único espacio entre las palabras.

En C6:

=ESPACIOS(B3)

La función LARGO devuelve el número de caracteres de una cadena de texto.

En C7:

=LARGO(C6)

Con SUSTITUIR eliminamos todos los espacios.

En C8:

=SUSTITUIR(C6;" ";"")

Calculamos los caracteres de la nueva cadena de texto.

En C9:

=LARGO(C8)

El resultado, en C10, es la diferencia más uno.

En C10:

=C7-C9+1

Ahora, sólo es necesario crear la fórmula compuesta en E2 y borrar las filas 6 a 10.

En E2:

=LARGO(ESPACIOS(B3))-LARGO(SUSTITUIR(B3;" ";""))+1

Descargar archivo (SE55-Contar palabras.xlsx)

Descargar archivo (SE55-Contar palabras.xls)

Operar con condiciones

En este ejercicio vamos a realizar cálculos que en sí mismos no tienen demasiado interés, pero que quizás puedan servirnos en un proyecto futuro. Consiste en hallar sumas o contar valores con condiciones poco habituales.

En la columna B tenemos los datos con los que vamos a hacer los cálculos. En D2:E14 pondremos los resultados. Las columnas que van de la G a la O las utilizaremos para obtener valores intermedios. Finalizado el ejercicio, podremos borrarlas.

Sumar los cinco números mayores

Para no complicar el ejercicio consideraremos que no hay números repetidos en la columna B.

Para sumar los cinco números más grandes, primero los aislaremos y luego los sumaremos. Bastarán tres pasos:

1. Si el número está entre los cinco mayores, lo marcamos como VERDADERO. En caso contrario lo marcamos como FALSO.
2. Una vez marcados todos los números, al que está marcado con VERDADERO le asignamos su propio valor; al que está marcado con FALSO le asignamos el valor cero.
3. Ahora, sólo hay que sumar la lista de números

Seleccionamos G3:G20 y escribimos:

=JERARQUIA.EQV(B3:B20;B3:B20;0)<6    [Terminamos con Ctrl + Mayús + Intro]

Ya tenemos la lista de valores VERDADEROS y FALSOS.

La función JERARQUIA.EQV se introdujo en Excel 2010 para sustituir a la función JERARQUIA de Excel 2003 y 2007. El tercer argumento (0 ó 1) de la función permite considerar un orden descendente (0 u omitido) o ascendente (1) de la lista. En el ejemplo, se ha utilizado el orden descendente.

Nota: Por razones de compatibilidad, Excel 2010 conserva la función JERARQUIA.

Seleccionamos H3:H20 y escribimos:

=(B3:B20)*(G3:G20)    [Terminamos con Ctrl + Mayús + Intro]

Hemos multiplicado la columna B por la columna G. Si recordamos que VERDADERO y FALSO equivalen respectivamente a 1 y a 0, se comprende que los cinco mayores conserven su valor y el resto se conviertan en cero.

La suma la pondremos en E3:

=SUMA(H3:H20)    [Resultado 466]

Como una vez acabado el ejercicio queremos borrar las columnas auxiliares, debemos crear una fórmula compuesta en E3. ¿Cómo se hace? Muy sencillo.

Editamos la fórmula de la celda E3 pulsando la tecla F2. Si nos resulta más cómodo, podemos hacer las modificaciones en la barra de fórmulas.

En la fórmula =SUMA(H3:H20) sustituimos H3:H20 por la fórmula que hemos puesto en la columna H. El resultado será: =SUMA((B3:B20)*(G3:G20)). La fórmula se ha convertido en una fórmula matricial y hay que acabar con Ctrl + Mayús + Intro.

Ahora, sustituimos G3:G20 por la fórmula de la columna G. El resultado final será:

=SUMA((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;0)<6))    [Terminamos con Ctrl + Mayús + Intro]

Ya podemos borrar las columnas G y H.

Si no nos gustan las fórmulas matriciales bastará sustituir SUMA por SUMAPRODUCTO.

En E4:

=SUMAPRODUCTO((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;0)<6))    [Terminamos con Intro]

Sumar los cinco números menores

Es lo mismo sumar los cinco mayores o los cinco menores. Basta sustituir por 1 el tercer argumento de la función JERARQUIA.EQV.

En E5:

=SUMA((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;1)<6))    [Terminamos con Ctrl + Mayús + Intro]

En E6:

=SUMAPRODUCTO((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;1)<6))    [Terminamos con Intro]

Sumar los números impares

Tenemos que aislar los impares y sustituir los pares por ceros. Lo primero que se me ocurre es utilizar la función ES.IMPAR para determinar los impares, pero no funciona con matrices. Lo comprobamos:

Seleccionamos J3:J20 y escribimos:

=ES.IMPAR(B3:B20)    [Terminamos con Ctrl + Mayús + Intro]

Excel devuelve un error.

ES.IMPAR funciona correctamente si en J3 escribimos: =ES.IMPAR(B3) y, luego, extendemos la fórmula hasta la fila 20. Mejor aún, si en J3 escribimos: =ES.IMPAR(B3)*B3 y copiamos la fórmula hacia abajo, habremos aislado todos los impares. Sólo habrá que sumarlos y el ejercicio estará resuelto. Pero nuestra intención es prescindir de las celdas auxiliares y escribir una única fórmula que nos devuelva el resultado correcto. Esto sólo se puede hacer si utilizamos fórmulas matriciales. Habrá que ir por otro camino.

Seleccionamos J3:J20 y escribimos:

=(RESIDUO(B3:B20;2)=1)*(B3:B20)     [Terminamos con Ctrl + Mayús + Intro]

Ya están aislados todos los impares.

En E7:

=SUMA(J3:J20)     [Resultado: 340]

Como en el ejemplo anterior, volvemos a crear una fórmula compuesta en E5:

=SUMA((RESIDUO(B3:B20;2)=1)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E8 usamos SUMAPRODUCTO:

=SUMAPRODUCTO((RESIDUO(B3:B20;2)=1)*(B3:B20))     [Terminamos con Intro]

Ya podemos borrar la columna J.

Sumar los números pares

La única diferencia con el caso anterior es que el resto de la división por 2 es cero.

En E9:

=SUMA((RESIDUO(B3:B20;2)=0)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E10:

=SUMAPRODUCTO((RESIDUO(B3:B20;2)=0)*(B3:B20))     [Terminamos con Intro]

Sumar los múltiplos de tres

No hay ninguna diferencia con los dos casos anteriores. Al dividir un número por 3 dará resto cero si es múltiplo de 3.

En E11:

=SUMA((RESIDUO(B3:B20;3)=0)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E12:

=SUMAPRODUCTO((RESIDUO(B3:B20;3)=0)*(B3:B20))     [Terminamos con Intro]

Contar los números que no son múltiplos de tres

Primero aislaremos los números no múltiplos de tres y luego los sustituiremos por unos. Finalmente, sumaremos todos los unos que hayamos obtenido y el ejercicio estará resuelto.

Seleccionamos N3:N20 y escribimos:

=(RESIDUO(B3:B20;3)<>0)*(B3:B20)     [Terminamos con Ctrl + Mayús + Intro]

Seleccionamos O3:O20 y escribimos:

=--NO(N3:N20=-N3:N20)     [Terminamos con Ctrl + Mayús + Intro]

En E13:

=SUMA(O3:O20)     [Resultado: 10]

Ahora, creamos la fórmula compuesta sustituyendo la fórmula de E13 por ésta:

=SUMA(--NO((RESIDUO(B3:B20;3)<>0)*(B3:B20)=-(RESIDUO(B3:B20;3)<>0)*(B3:B20)))     [Terminamos con Ctrl + Mayús + Intro]

En E14:

=SUMAPRODUCTO(--NO((RESIDUO(B3:B20;3)<>0)*(B3:B20)=-(RESIDUO(B3:B20;3)<>0)*(B3:B20)))     [Terminamos con Intro]

Descargar archivo (SE134-Condiciones.xlsx)

Descargar archivo (SE134-Condiciones.xls)

Mostrar en un gráfico los datos comprendidos entre dos fechas

Si durante un largo período de tiempo se han registrado las ventas semanales de una empresa, tendremos una lista de valores de varios cientos de filas. No tiene sentido representar estos valores en un único gráfico porque no se apreciará nada. Por ejemplo, con los datos de las trescientas semanas registradas del ejercicio que vamos a desarrollar, he hecho un gráfico de líneas y me ha quedado así:

Este gráfico no sirve. Pero, si representamos únicamente las ventas de 10 semanas consecutivas, el resultado es muy representativo:

Veamos cómo podemos extraer los datos de 10 semanas elegidos por el usuario y representarlos en forma gráfica mediante un diagrama de columnas o de líneas.

El usuario elegirá la semana inicial (en H2) y, a partir de esa semana, se acotarán los datos de las 10 semanas siguientes.

Comenzaremos creando dos nombres: Fecha1 y Ventas1

Accedemos a Fórmulas + Asignar nombre y creamos Fecha1 con la siguiente definición: =DESREF(Hoja1!$C$3;Hoja1!$H$2-1;0;10;1)

Aceptamos y pulsamos el botón Nuevo para crear Ventas1: =DESREF(Hoja1!$E$3;Hoja1!$H$2-1;0;10;1)

Pulsamos en una celda vacía y accedemos a Insertar + Columna + Columna agrupada. Como no hemos seleccionados ningún dato, el gráfico será un marco vacío. Con la tecla Alt pulsada, lo ajustamos hasta que ocupe el rango G4:L18.

Con el gráfico seleccionado, vamos a Herramientas de gráficos + Diseño + Seleccionar datos, pulsamos el botón Agregar del panel Entrada de leyenda (Series) y ponemos estos valores:

Hacemos clic en el botón el botón Editar del panel Etiquetas del eje horizontal (categoría) y rellenamos el cuadro de diálogo así:

El gráfico nos ha quedado de esta manera:

Eliminamos la leyenda y hacemos doble clic en uno de los rótulos del eje horizontal para entrar en el cuadro de diálogo Dar formato al eje. En Opciones de eje, marcamos el botón Eje de texto del apartado Tipo de eje y pulsamos Cerrar.

Eliminamos las líneas de división principales haciendo clic en una cualquiera y pulsando Supr.

Para aumentar el grosor de las columnas, hacemos doble clic en una de ellas y, en Opciones de serie, ponemos el ancho del intervalo en 80%. Sin salirnos de este cuadro de diálogo, podemos cambiar el color de las columnas eligiendo Relleno en el panel de la izquierda y Relleno sólido en el panel de la derecha; el color lo elegiremos en Color de relleno.También podemos eliminar el Color del borde.

Para que se muestren los valores de las columna, hacemos clic con el botón derecho en una cualquiera y, en el menú emergente, seleccionamos Agregar etiqueta de datos.

Podemos comprobar que al cambiar el valor de H2 el gráfico se adapta correctamente.

Si queremos que el usuario decida el número de columnas del gráfico, será necesario habilitar una celda para que introduzca ese dato; por ejemplo en H3.

El gráfico se hace igual que el anterior pero, si lo vamos a crear en la Hoja3, los nombres Fecha1 y Ventas1 deben sustituirse por Fecha3 y Ventas3, cuyas definiciones deberán ser:

Fecha3	=DESREF(Hoja3!$C$3;Hoja3!$H$2-1;0;Hoja3!$H$3;1)
Ventas3	=DESREF(Hoja3!$E$3;Hoja3!$H$2-1;0;Hoja3!$H$3;1)

También podemos poner una barra de desplazamiento u otro control para modificar los valores de la fila inicial y el número de semanas que deseamos mostrar en el gráfico.

En el ejemplo, los cambios se hacen con barras de desplazamiento (de formulario o ActiveX) y con un control de número (ActiveX). Naturalmente, se puede prescindir de los controles y escribir los datos directamente en las celdas H2 y H4.

Será necesario crear otros dos nuevos nombres: Fecha2 y Ventas2.

Fecha2	=DESREF(Hoja2!$C$3;Hoja2!$H$2-1;0;10;1)
Ventas2	=DESREF(Hoja2!$E$3;Hoja2!$H$2-1;0;10;1)

Accedemos a Programador + Insertar + Barra de desplazamiento (control de formulario) y, con la tecla Alt pulsada, insertamos el control en J2:M2. Ahora, en el menú contextual, seleccionamos Formato de control y ponemos los valores siguientes:

De este modo, hemos vinculado el valor asociado a la barra con la celda H2. Además, como hay 300 filas, el desplazamiento de la barra lo hemos restringido al rango 1 a 299.

Debajo, en J4:M4 ponemos otra barra igual con estas propiedades:

La barra estará vinculada con la celda H4 y podrá tomar valores entre 5 y 20; es decir, el número de datos que mostrará el gráfico estará comprendido entre esos valores.

En G6:M20, hacemos un gráfico igual que en los dos casos anteriores pero eligiendo Línea con marcadores en vez de Columna. Lo adornamos como queramos y comprobamos que las barras de desplazamiento funcionan correctamente.

Para ver cómo se usan los controles ActiveX ponemos dos barras de desplazamiento en G22:K22 y en G24:K24. Para hacerlo hay que acceder a Programador + Insertar + Barra de desplazamiento (control ActiveX). Cuando lo hagamos quedará activado el Modo Diseño:

En el menú contextual de la primera barra seleccionamos Propiedades y ponemos los valores que están marcados en rojo:

En la segunda, las propiedades deberán ser:

Terminamos haciendo clic en Modo Diseño para desactivarlo.

Para finalizar el ejercicio, insertamos dos Controles de número (control ActiveX) en M21 y M23. Las propiedades de ambos serán:

Todos los controles deberán funcionar correctamente.

Descargar archivo (E156-Mostrar 10 datos.xlsx)

Descargar archivo (E156-Mostrar 10 datos.xls)