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:
Si el número está entre los cinco mayores, lo marcamos como VERDADERO. En caso contrario lo marcamos como FALSO.
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.
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]
