Fórmulas y Funciones Matriciales en Excel.

Operaciones lógicas matriciales Parte 1.

 

Fórmulas matriciales: Curso de Matrices en Excel para aprender a trabajar con rangos en vez de con celdas con la finalidad de trabajar con datos organizados en rangos más rápidamente.

Temario: trabajar con rangos matriciales: operaciones básicas y función SI(). Fórmulas de resumen de rangos matriciales: SUMA(), CONTAR(), PROMEDIO(). Uso de SUMA(SI()) para contar. CONTAR.SI() matricial. Funciones matriciales específicas: FRECUENCIA(), TENDENCIA() y SUMAPRODUCTO(). Funciones SI CONJUNTO. Lógica Matricial.

Descarga los ejemplos en: https://www.dropbox.com/s/p3ots7xugziesd6/Excel-Matrices.zip?dl=0

 

Puedes ir al índice de contenidos para ver todos los Minicursos o a la primera entrega de este minicurso de Matrices en Excel.

 

Introducción a las operaciones lógicas

 

Cuando hablamos de valores lógicos, estamos diciendo que Excel es capaz de trabajar con la lógica binaria VERDADERO y FALSO, pero también con valores 1 y 0. Los valores lógicos se obtienen como resultado de una operación lógica.

 

En seguida explicamos lo que es una operación lógica que opera con los dos valores VERDADERO y FALSO, pero primero vamos a explicar para qué sirven.

 

Las operaciones lógicas pueden usarse tanto en las fórmulas matriciales como en las fórmulas estándar. Son importantes porque te permitirán crear funciones de resumen (conteo, suma, promedio...) muy versátiles. Esto es debido a que las operaciones lógicas permiten crear fórmulas con muchos criterios o pruebas lógicas (también llamadas "condiciones") sin necesidad de usar los SI() anidados. Recuerda que dijimos que es mejor evitar los SI() anidados porque son muy "liosos". Además, según todo lo visto hasta ahora, si usas fórmulas estándar tendrás que usar rangos intermedios visibles, pero si usas fórmulas matriciales de resumen, Tipo 2 o Tipo 3, no necesitarás rangos con cálculos intermedios.

Por tanto, es muy interesante el hecho de no tener que usar la función SI() ni tener que usar rangos con cálculos intermedios, ya que ambas cosas te permitirán crear hojas de cálculo poco confusas y más potentes.

 

A partir de la introducción en Excel 2007 de las funciones SI CONJUNTO, ya no son necesarios los SI() anidados y las fórmulas lógicas matriciales han perdido un poco de protagonismo, pero siguen siendo unas fórmulas muy versátiles y llegan allí donde las funciones SI CONJUNTO no llegan.

 

Para este tema, seguiremos este método: primero veremos el uso de las operaciones lógicas en las fórmulas estándar. Después veremos la fácil manera de convertir una fórmula estándar en una fórmula matricial de Tipo 1. Y, como siempre, desde el Tipo 1 pasaremos a una función matricial de resumen de Tipo 2 o Tipo 3.

 

Vamos a ver estos pasos.

Por ejemplo, supón una función SI() estándar como esta:

 

=SI(A1>1;"X";"") y ENTER

 

Supón que introducimos esta fórmula en toda la columna B. La columna B mostraría con una "X" qué celdas de la columna A cumplen el criterio (o "condición") de que contengan un valor mayor que 1. Además, podríamos contar cuántas "X" hay en la columna B y ya habríamos contado cuántas celdas de la columna A cumplen dicha condición.

Esto sería útil más bien cuando queremos que el usuario de Excel vea claramente si una celda de la columna A cumple o no la condición, gracias a la "X" que tiene a su derecha en la columna B. Recuerda que una función como CONTAR.SI.CONJUNTO() permite contar las celdas de la columna A mayores que 1 directamente, sin necesidad de poner "X" en la columna B. Aun así, para contar cuántas "X" hay en la columna B (suponiendo las 100 primeras filas) bastaría usar la función:

 

=CONTAR.SI(B1:B100;"X") y ENTER

 

... o bien, usando las más modernas funciones SI CONJUNTO que fueron incorporadas a Excel a partir de la versión Excel 2007, puedes usar como rango la columna B entera:

 

=CONTAR.SI.CONJUNTO(B:B;"X") y ENTER

 

Pero también vimos que había una manera más fácil de contar.

Las dos funciones que acabamos de ver para contar cuántas "X" hay en un rango, son más complicada que una simple SUMA(). Por consiguiente, podíamos cambiar las "X" por 1's, y entonces bastaría con sumar esos 1's con una función tan sencilla como la SUMA() estándar para obtener el conteo. Recordar que a esto lo llamábamos el "truco de usar la función SUMA() para contar". Por ejemplo, en la columna B pondríamos:

 

=SI(A1>1;1;0) y ENTER

 

... y así, para obtener el total de las celdas que cumplen la condición, bastaría una simple SUMA() estándar:

 

=SUMA(B:B)

 

Así, todo es mucho más sencillo.

 

Pero todavía se puede hacer aún más sencillo. Y aquí es donde entran las operaciones lógicas (matriciales o estándares).

Esta sencillez se consigue sustituyendo la función SI() por una operación lógica.

 

Para convertir una función SI() en una operación lógica, basta con borrar la función SI() dejando la prueba lógica o criterio (o "condición") que hay dentro de la función SI().

 

Por ejemplo, para transformar la función:

 

=SI(A1>1;1;0) y ENTER

 

... en una operación lógica, basta con dejar solamente su condición, el primer parámetro:

 

=(A1>1) y ENTER

 

Y ya tenemos la operación lógica.

 

En cuanto veamos el primer ejemplo verás que una fórmula de este tipo devuelve solamente VERDADERO o FALSO. Recuerda que seguiremos estos pasos:

• Primero, veremos el ejemplo usando fórmulas estándar, no matriciales, copiadas con el cuadro de relleno, para averiguar las celdas que cumplen las condiciones que te solicitan.
• Seguidamente, cambiaremos la versión estándar por la versión matricial de Tipo 1 multicelda. El resultado siempre es el mismo que la fórmula estándar, solamente cambia la manera de obtener dicho resultado.
• Y, como siempre, una vez tenemos la versión en Tipo 1, es ya una paso directo obtener la fórmula de resumen matricial unicelda de Tipo 2 para efectuar conteos o sumas. No siempre te pedirán una función de resumen de conteo o suma, pero nosotros siempre la pondremos.

 

Contar valores que cumplen una condición con operaciones lógicas matriciales

Empecemos con un ejemplo sencillo. Si ya has descargado los ejemplos, abre el archivo "Cuestiones.xls" y ponte en la hoja "Verdadero y Falso":

 

 

Supón que te piden que calcules, en la celda E2, cuántos números de la columna A son mayores que 20. Lo primero que podríamos hacer es, en la columna B, poner una fórmula que indique cuándo en la columna A hay un número mayor que 20.

Los pasos que recomendamos para solucionarlo serían los tres pasos que hemos apuntado antes: fórmula estándar, fórmula Tipo 1, función de resumen Tipo 2. Pero lo vamos a hacer usando funciones lógicas, es decir, usando los valores VERDADERO y FALSO o bien 1's y 0's.

Fórmula estándar:

• Haz clic en B2
• Teclea la fórmula =SI(A2>20;"X";"") y ENTER
• Vuelve a hacer clic en B2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna B

El resultado:

 

 

Observa que sólo hay una X en la columna B cuando en la misma fila de la columna A hay un número mayor que 20.

Ahora sería cuestión de contar las "X", pero, atención, hemos dicho que usaremos solamente VERDADERO y FALSO, no "X" o nulos "". Por tanto, sigue el paso que hemos explicado antes para transformar un SI() en una operación lógica, es decir, bórralo todo excepto el "=" al inicio de la fórmula, los paréntesis y la prueba lógica o condición:

• Haz clic en B2
• Teclea o modifica la fórmula así =(A2>20) y ENTER
• Vuelve a hacer clic en B2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna B

El resultado:

 

 

Un valor VERDADERO indica que el número en la columna A es mayor que 20 y un valor FALSO indica que el número en la columna A no es mayor que 20. Ahora la fórmula en la columna B es mucho más sencilla y no requiere de la función SI().

Bastaría ahora con contar los VERDADERO de la columna B y ya tendríamos el conteo de los valores mayores que 20. Pero la función para contar los VERDADERO debería ser una función SI(), CONTAR.SI(), SUMAR.SI() o funciones SI CONJUNTO y hemos dicho que la forma más sencilla posible para contar es usar la función SUMA() con 1's y 0's.

Pero para ello no podemos usar VERDADERO o FALSO. Debemos usar 1's y 0's. La cuestión es: ¿cómo convertimos estos valores VERDADERO o FALSO en 1's y 0's?. La respuesta es que Excel proporciona muchas fórmulas para hacerlo. Pero, de entre todas las fórmulas, hay 6 que son las más usadas.

 

 

Para convertir un valor VERDADERO o FALSO en un valor 1 y 0 respectivamente, podemos usar estas 6 fórmulas:

 

(A2>20)*1
(A2>20)+0
--(A2>20)
N(A2>20)
(A2>20)*VERDADERO
(A2>20)+FALSO

 

Excel considera los 0's como valor FALSO y los 1's como valor VERDADERO, si bien en la práctica, Excel considerará 0 como FALSO, y cualquier valor distinto de cero: ... -4, -3, -2, -1, 1, 2, 3, 4... como VERDADERO.

 

 

Vamos a verlo. Observa que la celda A2 contiene un valor de 21 por tanto la operación lógica =(A2>20) tiene que dar VERDADERO que es equivalente a un valor 1. Vamos a verlo:

• Haz clic en la celda H2
• Teclea la fórmula: =(A2>20) y ENTER

El resultado es VERDADERO:

 

 

Vamos a ver cómo transformar ese VERDADERO en un 1 con las 6 fórmulas. Sigue estos pasos:

• Haz clic en H3
• Teclea la fórmula =(A2>20)*1 y ENTER
• Haz clic en H4
• Teclea la fórmula =(A2>20)+0 y ENTER
• Haz clic en H5
• Teclea la fórmula =--(A2>20) y ENTER
• Haz clic en H6
• Teclea la fórmula =N(A2>20) y ENTER
• Haz clic en H7
• Teclea la fórmula =(A2>20)*VERDADERO y ENTER
• Haz clic en H8
• Teclea la fórmula =(A2>20)+FALSO y ENTER

El resultado:

 

 

 

Por tanto, hemos convertido el valor VERDADERO en un 1 usando 6 fórmulas distintas. Cualquiera de ellas igual de válida, no hay una fórmula mejor que las otras. Pero nosotros, en este Minicurso, elegiremos la primera de ellas y las dos últimas por una razón que explicaremos más adelante.

 

Ahora vamos a usar la celda A3, que tiene el valor de 20 y por tanto la operación lógica =(A3>20) tiene que dar FALSO, que es equivalente a un valor 0. Vamos a verlo:

• Haz clic en la celda I2
• Teclea la fórmula =(A3>20) y ENTER

El resultado es FALSO:

 

 

Vamos a ver cómo transformar ese FALSO en un 0 con las 6 fórmulas. Sigue estos pasos:

• Haz clic en I3
• Teclea la fórmula =(A3>20)*1 y ENTER
• Haz clic en I4
• Teclea la fórmula =(A3>20)+0 y ENTER
• Haz clic en I5
• Teclea la fórmula =--(A3>20) y ENTER
• Haz clic en I6
• Teclea la fórmula =N(A3>20) y ENTER
• Haz clic en I7
• Teclea la fórmula =(A3>20)*VERDADERO y ENTER
• Haz clic en I8
• Teclea la fórmula =(A3>20)+FALSO y ENTER

El resultado:

 

 

 

Por tanto, hemos convertido el valor FALSO en un 0 usando 6 fórmulas distintas. Cualquiera de ellas igual de válida, no hay una fórmula mejor que las otras. Pero nosotros, en este Minicurso, elegiremos la primera de ellas y las dos últimas por una razón que explicaremos más adelante.

 

Si buscas fórmulas matriciales en Internet, te puedes encontrar cualquiera de las 6 fórmulas. Pero insistimos que nosotros usaremos la primera y las dos últimas por una razón que explicaremos más adelante.

 

Una vez vistas las 6 maneras de convertir un VERDADERO en un 1 o un FALSO en un 0, vamos a aplicarlo a nuestro ejemplo con la finalidad de crear una fórmula lo más sencilla posible que me cuente en la celda E2 cuántos números mayores que 20 hay en la columna A. Lo que tenemos que hacer es usar cualquiera de las 6 fórmulas para convertir los VERDADERO y FALSO de la columna B en 1's y 0's. Haz lo siguiente:

• Haz clic en B2
• Teclea o modifica la fórmula así =(A2>20)*1 y ENTER
• Vuelve a hacer clic en B2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna B

El resultado:

 

 

Un valor 1 indica que el número en la columna A es mayor que 20 y un valor 0 indica que el número en la columna A no es mayor que 20. La fórmula sigue siendo muy sencilla y no requiere de la función SI().

Por último, bastará ahora con sumar la columna B y ya tendríamos el conteo de los valores mayores que 20. La fórmula más sencilla que hay sería:

• Haz clic en E2
• Teclea la fórmula =SUMA(B2:B31) y ENTER

El resultado:

 

 

Esto significa que la columna A tiene 17 celdas con valores mayores que 20.

 

Observa que hemos hecho el conteo usando funciones muy sencillas. Es la forma más sencilla que hemos visto hasta ahora de hacer un conteo. Solamente hemos usado estas dos funciones: =(A2>20)*1 en la columna B y =SUMA(B2:B31) en la celda E2. Recuerda que hemos usado el truco de usar la función SUMA() para contar.

 

Sin embargo, aún no hemos acabado, porque para obtener el conteo, hemos necesitado de la columna B, y nosotros lo que queremos es no usar rangos con datos intermedios. Hemos de obtener una fórmula que no necesite de la columna B y, como siempre, esto nos lleva a buscar una función matricial de Tipo 1 que haga lo mismo que la fórmula estándar, y, una vez obtenida esta función de Tipo 1, ya te será fácil en este punto del curso ver cómo obtenemos una función de Tipo 2 o de Tipo 3 para hacer el conteo.

 

Fórmula matricial de Tipo 1:

• Selecciona desde B2 hasta B31
• Pulsa la tecla SUPR para borrar su contenido. No hagas clic en ningún sitio para que no se deshaga la selección.
• Teclea la fórmula =(A2:A31>20)*1 y CONTROL+MAYÚSCULAS+ENTER (resaltamos en color la única parte que cambia con respecto a la fórmula estándar en el apartado anterior)

El resultado:

 

 

Es exactamente igual que antes. No cambia nada. Solamente que ahora tenemos ya la fórmula matricial de Tipo 1.

 

Fórmula de resumen matricial de Tipo 2 o Tipo 3

A partir de la fórmula de Tipo 1 obtenemos la de Tipo 2 o Tipo 3. Basta con teclear la función de resumen y ponerle en su interior como parámetro la misma fórmula de Tipo 1. Vamos a ver dos fórmulas, la de Tipo 2 que será SUMA() acabada con CONTROL+MAYUSCULAS+ENTER y la de Tipo 3 que será SUMAPRODUCTO() acabado en ENTER.

• Haz clic en E2
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula:

=SUMA((A2:A31>20)*1)

• Acaba con CONTROL+MAYUSCULAS+ENTER
• Haz clic en E3
• Teclea la fórmula:

=SUMAPRODUCTO((A2:A31>20)*1)

• Acaba con ENTER

El resultado con ambas fórmulas habría de ser el correcto:

 

 

Ahora ya puedes borrar el contenido de la columna B y verás que las fórmulas en E2 y E3 no cambian, es decir, son fórmulas totalmente independientes que no necesitan de rangos intermedios:

 

 

Es más, si cambiamos el valor de la celda A3 e introducimos el valor 99, las fórmulas E2 y E3 incrementarán el conteo para añadir este nuevo valor mayor que 20:

 

 

Si hay que elegir entre las dos fórmulas, SUMA() introducida con CONTROL+MAYUSCULAS+ENTER o SUMAPRODUCTO() introducida con ENTER, recuera que siempre es más cómodo usar SUMAPRODUCTO() acabado en ENTER y además esta función tiene una gran ventaja que veremos enseguida.

Si recuerdas, en las fórmulas matriciales de Tipo 2, siempre acabábamos diciendo que es lo mismo...

• ... poner en el rango B2:B31 la fórmula matricial  de Tipo 1 =(A2:A31>20)*1, y sumar este rango con =SUMAPRODUCTO(B2:B31) y ENTER, es lo mismo que...
• ... usar directamente en una sola celda la función de Tipo 3 =SUMAPRODUCTO((A2:A31>20)*1) y ENTER

Y también:

• en el primer caso se ve la matriz intermedia en B2:B31 y se ven los elementos de esa matriz que luego sumaremos.
• en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

 

Ahora hazlo tú:

Continuamos en archivo "Cuestiones.xls", en la hoja "Meses":

 

 

En esta hoja hay una lista con muchas fechas en la columna A. Supón que te piden, en la celda E2, una fórmula que haga un conteo de cuántas fechas hay del mes de septiembre en la columna A. Te piden que no uses rangos con datos intermedios. Hazlo usando SUMA() ó SUMAPRODUCTO() para contar y con las menos funciones posibles, teniendo en cuenta que una de las funciones que tendrás que usar es MES(fecha).

El resultado debería ser:

 

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Contar valores repetidos con operaciones lógicas matriciales

Continuaremos con otro ejemplo. Ahora no contaremos elementos que cumplan una condición sino que contaremos valores repetidos en un rango.

 

Las fórmulas que se usan para contar valores repetidos son totalmente distintas de las que se usan para contar valores que cumplan ciertas condiciones.

 

Si ya has descargado los ejemplos, abre el archivo "Cuestiones.xls" y ponte en la hoja "Repetidos":

 

 

Esta hoja ya la hemos usado antes en el tema de usar CONTAR.SI() matricial. Ahora vamos a ver cómo contar repetidos con fórmulas de lógica matricial y verás que es mucho más sencillo. Supón que te piden que calcules, en la celda F2, cuántos códigos de la columna C están repetidos. Lo primero que podríamos hacer es, en la columna B, poner una fórmula que indique cuándo en la columna B hay un código repetido.

Los pasos que recomendamos para solucionarlo serían los tres pasos que hemos apuntado antes: fórmula estándar, fórmula Tipo 1, función de resumen Tipo 2. Pero lo vamos a hacer usando funciones lógicas.

 

Fórmula estándar:

• Haz clic en C2
• Teclea la fórmula:

=SI(CONTAR.SI($B$2:$B$120;B2)>1;"X";"") y ENTER

• Vuelve a hacer clic en C2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna C

El resultado:

 

 

Sólo hay una "X" en la columna C cuando en la misma fila de la columna B hay un código que esté repetido en la propia columna B. Si tienes Excel 2007+ puedes comprobar que realmente hay una X por cada valor repetido, seleccionando la columna B completa y haciendo clic en la ficha Inicio → Formato condicional → Resaltar reglas de celdas* → Duplicar valores** → Aceptar. Aparecerán coloreadas las celdas repetidas de B y verás que coinciden con las "X" de C:

 

 

(*) Mala traducción del inglés "Reglas para resaltar celdas"
(**) Mala traducción del inglés "Resaltar valores duplicados"

 

Ahora sería cuestión de contar las "X", pero, atención, habíamos dicho que trabajaríamos solamente con VERDADERO y FALSO, no con "X" o nulos "". Por tanto, sigue el paso que hemos explicado antes para transformar un SI() en una operación lógica, es decir, bórralo todo excepto el "=" al inicio de la fórmula, los paréntesis y la prueba lógica o condición:

• Haz clic en C2
• Teclea o modifica la fórmula:

=(CONTAR.SI($B$2:$B$120;B2)>1) y ENTER

• Vuelve a hacer clic en C2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna C

El resultado:

 

 

Un valor VERDADERO indica que el código en la columna B está repetido y un valor FALSO indica que el código en la columna B no está repetido. Ahora la fórmula es mucho más sencilla y no requiere de la función SI().

Bastaría ahora con contar los VERDADERO de la columna C y ya tendríamos el conteo de los valores repetidos. Pero ya hemos dicho que la forma más sencilla posible para contar es usar la función SUMA() con 1's y 0's. Para convertir un valor VERDADERO o FALSO en un valor 1 y 0 respectivamente recordar que hay 6 fórmulas:


(CONTAR.SI($B$2:$B$120;B2)>1)*1
(CONTAR.SI($B$2:$B$120;B2)>1)+0
--(CONTAR.SI($B$2:$B$120;B2)>1)
N(CONTAR.SI($B$2:$B$120;B2)>1)
(CONTAR.SI($B$2:$B$120;B2)>1)*VERDADERO
(CONTAR.SI($B$2:$B$120;B2)>1)+FALSO

Excel considera los 0's como valor FALSO y los 1's como valor VERDADERO, si bien en la práctica, Excel considerará 0 como FALSO, y cualquier valor distinto de cero: ... -4, -3, -2, -1, 1, 2, 3, 4 ... como VERDADERO. Como hemos dicho, cualquiera de las 6 fórmulas es igual de válida y, si buscas fórmulas matriciales en Internet, te puedes encontrar cualquiera de ellas. Pero insistimos que nosotros usaremos la primera y las dos últimas por una razón que explicaremos más adelante.

Vamos a convertir los VERDADERO y FALSO de la columna C en 1's y 0's. Haz los siguiente:

• Haz clic en C2
• Teclea la fórmula:

=(CONTAR.SI($B$2:$B$120;B2)>1)*1 y ENTER (resaltamos los paréntesis porque recuerda que es necesario ponerlos)

• Vuelve a hacer clic en C2
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna C

El resultado:

 

 

Un valor 1 indica que el código en la columna B está repetido y un valor 0 indica que el código en la columna B no está repetido. La fórmula ya no requiere de la función SI().

Por último, bastará ahora con sumar la columna C y ya tendríamos el conteo de los valores repetidos en la columna B. La fórmula más sencilla que hay sería:

• Haz clic en F2
• Teclea la fórmula =SUMA(C2:C120) y ENTER

El resultado:

 

 

Esto significa que la columna B tiene 16 celdas con valores repetidos, si bien no queda claro cuántos códigos únicos hay repetidos, ya que este 16 puede significar 1 código repetido 16 veces, 8 códigos repetidos 2 veces, etc.

 

Observa que hemos hecho el conteo usando funciones bastante sencillas. Recuerda que hemos usado el truco de usar la función SUMA() para contar.

 

Sin embargo, aún no hemos acabado, porque para obtener el conteo, hemos necesitado de la columna C, y nosotros lo que queremos es no usar rangos con datos intermedios. Hemos de obtener una fórmula que no necesite de la columna C y, como siempre, esto nos lleva a buscar una función matricial de Tipo 1 que haga lo mismo que la fórmula estándar, y, una vez obtenida esta función de Tipo 1, ya te será fácil en este punto del curso ver cómo obtenemos una función de Tipo 2 o de Tipo 3 para hacer el conteo.

 

Fórmula matricial de Tipo 1:

• Selecciona desde C2 hasta C120 (una manera rápida de hacerlo es hacer clic en C2 y pulsar CTRL+SHIFT+↓)
• Pulsa la tecla SUPR para borrar su contenido. No hagas clic en ningún sitio para que no se deshaga la selección.
• Teclea la fórmula:

=(CONTAR.SI(B2:B120;B2:B120)>1)*1 y CONTROL+MAYÚSCULAS+ENTER

El resultado:

 

 

Es exactamente igual que antes. No cambia nada. Solamente que ahora tenemos ya la fórmula matricial de Tipo 1.

 

Fórmula de resumen matricial de Tipo 2 o Tipo 3

A partir de la fórmula de Tipo 1 obtenemos la de Tipo 2 o Tipo 3. Basta con teclear la función de resumen y ponerle en su interior como parámetro la misma fórmula de Tipo 1. Vamos a ver dos fórmulas, la de Tipo 2 que será SUMA() acabada con CONTROL+MAYUSCULAS+ENTER y la de Tipo 3 que será SUMAPRODUCTO() acabado en ENTER.

• Haz clic en F2
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula:

=SUMA((CONTAR.SI(B2:B120;B2:B120)>1)*1) y CONTROL+MAYÚSCULAS+ENTER

• Haz clic en F3
• Teclea la fórmula:

=SUMAPRODUCTO((CONTAR.SI(B2:B120;B2:B120)>1)*1) y ENTER

• Incluso, si quieres, podrías teclear la siguiente fórmula en F4, el resultado sería siempre el mismo que las dos fórmulas anteriores y no habría ningún inconveniente:

=SUMAPRODUCTO(N(CONTAR.SI(B2:B120;B2:B120)>1)) y ENTER

 

El resultado con estas fórmulas habría de ser el correcto:

 

 

Ahora ya puedes borrar el contenido de la columna C y verás que las fórmulas en F2 y F3 no cambian, es decir, son fórmulas totalmente independientes que no necesitan de rangos intermedios:

 

 

Es más, si cambiamos el valor de, por ejemplo, las celdas B6 y B7 introduciendo el código "AAA", las fórmulas F2 y F3 incrementarán el conteo en 2 para añadir este nuevo código repetido 2 veces:

 

 

Si hay que elegir entre las dos fórmulas, SUMA() introducida con CONTROL+MAYUSCULAS+ENTER o SUMAPRODUCTO() introducida con ENTER, recuera que siempre es más cómodo usar SUMAPRODUCTO() acabado en ENTER y además esta función tiene una gran ventaja que veremos enseguida.

Si recuerdas, en las fórmulas matriciales de Tipo 2, siempre acabábamos diciendo que es lo mismo...

• ... poner en el rango C2:C120 la fórmula matricial  de Tipo 1 =(CONTAR.SI(B2:B120;B2:B120)>1)*1, y sumar este rango con =SUMAPRODUCTO(C2:C120) y ENTER, es lo mismo que...
• ... usar directamente en una sola celda la función de Tipo 3 =SUMAPRODUCTO((CONTAR.SI(B2:B120;B2:B120)>1)*1) y ENTER

Y también:

• en el primer caso se ve la matriz intermedia en C2:C120 y se ven los elementos de esa matriz que luego sumaremos.
• en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

 

_{Continua en la siguiente entrega. Puedes también ir al Indice de los Minicursos o Volver arriba.}