Fórmulas y Funciones Matriciales en Excel.

Operaciones lógicas matriciales Parte 2.

 

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.

 

Definición de "operación lógica"

Ya hemos visto lo que son las operaciones lógicas, sabemos que son "funciones SI() pero quitando el SI()", pero nos falta una definición más estricta. La definición la podemos hacer comparando una operación aritmética estándar con una operación lógica. Estamos acostumbrados a oír que una operación aritmética estándar se denomine "operación", que tenga uno o más "operadores" y que tenga uno o más "operandos". Por ejemplo, "2+4" es una "operación", sólo hay un "operador" que es "más" (+) y dos "operandos" que son los números 2 y 4. El resultado de esta operación es 6. También una función aritmética puede ser un operador matemático, como RAIZ(), ENTERO(), PROMEDIO(), SUMA() y otras funciones llamadas "matemáticas y trigonométricas". Por último, el resultado de una operación aritmética es siempre un número.

 

Pues bien, de la misma manera, una "operación lógica" tiene también sus "operandos" y sus "operadores", como pueden ser los operadores lógicos estándar =, >, <, >=, <=, <>, pero también hay funciones que hacen de operadores como ES.IMPAR(), ES.PAR(), ESBLANCO(), ESERROR(), ESNOTEXTO(), ESNUMERO(), ESTEXTO() y otras funciones llamadas "de información". Por último, el resultado de una operación lógica es siempre VERDADERO o FALSO.

 

También hay otras dos operaciones lógicas importantes que son "*" y "+". Ya sabemos que el operador "*" en una operación aritmética significa "multiplicación" y que el operador "+" en una operación aritmética significa "suma". Además, sabemos que los paréntesis "(" y ")" cambian la precedencia de los operadores.

 

Pero en una operación lógica, el operador "*" equivale a "AND" y el operador "+" equivale a "OR". Además, los paréntesis "(" y ")" también cambian la precedencia de los operadores.

 

Vamos a verlo y también veremos por qué hemos dicho antes que, de las 6 funciones que transformaban VERDADERO y FALSO en 1's y 0's, nosotros preferimos (operación)*1, (operación)*VERDADERO y (operación)+FALSO.

 

Producto de varias operaciones lógicas (AND lógico)

Ya hemos visto que multiplicando una operación lógica por 1, convertimos el VERDADERO en un 1, y el FALSO en un 0. Pero hay otra manera de convertir VERDADERO y FALSO en 1's y 0's, que es usar el operador "*" como "AND" lógico, usando este operador cuando nos piden una fórmula que requiere de dos o más operaciones lógicas.

 

Vamos a verlo.

 

Seguimos en el archivo "Cuestiones.xls" en la hoja "Meses". Supón que te piden que, en la celda F4, calcules cuántas fechas de la columna A son del tercer trimestre del año.

Esto es lo mismo que decir que cuentes las fechas entre los meses de julio y septiembre ambos inclusive, lo que, en una operación lógica, sería decir que se han de cumplir 2 condiciones a la vez: que el mes de la fecha sea mayor o igual que 7, y a la vez, que el mes de la fecha sea menor o igual que 9. Pero este "y a la vez" es en realidad un "AND" lógico, lo que se consigue en Excel con el operador "*".

Vamos a verlo siguiendo los pasos que hemos seguido hasta ahora: función estándar transformando VERDADERO y FALSO en 1's y 0's en la columna B, función equivalente de Tipo 1 y función de resumen de Tipo 2 o Tipo 3.

 

Fórmula estándar con los valores lógicos convertidos ya a 1's y 0's

Usaremos la columna B como rango intermedio para hacer este cálculo. Sigue estos pasos:

• Haz clic en la celda B3
• Teclea la siguiente fórmula (observa que no hay que poner *1 como hasta ahora):

=(MES(A3)>=7)*(MES(A3)<=9) y ENTER

• Vuelve a hacer clic en  la celda B3
• 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 la fecha en la columna A está entre julio y septiembre y un valor 0 indica que no lo está. La fórmula no requiere de la función SI() y por tanto es una operación lógica, y es más sencilla que usar un SI().

Pero hay una diferencia con lo que hemos hecho hasta ahora, y es que no hemos hecho la multiplicación por 1 para transformar los VERDADERO en 1's y los FALSO en 0's.

 

Ya no necesito multiplicar por 1, ni necesito ninguna de las 6 maneras que dijimos de transformar VERDADERO y FALSO en 1's y 0's porque cuando multiplico 2 ó más operaciones lógicas entre paréntesis, Excel ya hace automáticamente la conversión en 1's y 0's. Por tanto, sólo es necesario hacer (operación lógica)*1 cuando solamente tengo 1 operación lógica que quiero convertir en 1 ó 0.

Dicho de otra manera, cuando se usa el operador * como "AND" lógico con 2 ó más operaciones lógicas, Excel siempre da como resultado un 1 ó un 0. Debe interpretarse entonces ese 1 como VERDADERO y ese 0 como FALSO.

Aun en estos casos en que se multipliquen varias operaciones lógicas, los paréntesis alrededor de cada operación lógica hay que seguir manteniéndolos. Es decir, la operación sin paréntesis  =MES(A3)>=7*MES(A3)<=9 dará resultados totalmente erróneos.

Antes hemos dicho que preferíamos usar la fórmula (operación lógica)*1 de entre las 6 fórmulas que podemos usar para convertir VERDADERO y FALSO en 1's y 0's. La razón es que así usaremos siempre el operador "*".

Una vez dicho esto, para obtener el total de fechas del tercer trimestre, puedo, provisionalmente, poner en F4 el total con una función SUMA() estándar para sumar todos los 1's:

• Haz clic en F4
• Teclea la fórmula =SUMA(B3:B102) y ENTER

El resultado:

 

 

 

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 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 B3 hasta B102 (una manera rápida de hacerlo es hacer clic en B3 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:

=(MES(A3:A102)>=7)*(MES(A3:A102)<=9) 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 F4
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula:

=SUMA((MES(A3:A102)>=7)*(MES(A3:A102)<=9)) y CONTROL+MAYÚSCULAS+ENTER

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

=SUMAPRODUCTO((MES(A3:A102)>=7)*(MES(A3:A102)<=9)) y 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 F4 y F5 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, la celda A3 por una fecha del mes de julio, las fórmulas F4 y F5 incrementarán el conteo en 1 para añadir esta nueva fecha del segundo semestre:

 

 

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:B102 la fórmula matricial  de Tipo 1 =(MES(A3:A102)>=7)*(MES(A3:A102)<=9), y sumar este rango con =SUMAPRODUCTO(B2:B120) y ENTER, es lo mismo que...
• ... usar directamente en una sola celda la función =SUMAPRODUCTO((MES(A3:A102)>=7)*(MES(A3:A102)<=9)) y ENTER

Y también:

• en el primer caso se ve la matriz intermedia en B2:B102 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.

 

Sería interesante que vieses cómo opera Excel con el operador * usado como AND lógico. Haz lo siguiente:

Ponte en la hoja "Compras Meses 2" del archivo "Cuestiones.xls":

 

 

Vamos a hacer el mismo ejemplo que el anterior pero lo haremos por partes: usaremos la columna D para indicar si el mes de la fecha en la columna A es mayor o igual que 7 y usaremos la columna E para indicar si el mes de la fecha en la columna A es menor o igual que 9. Lo haremos con operaciones lógicas no matriciales para verlo más claramente:

• Haz clic en D3
• Teclea la fórmula =(MES(A3)>=7)*1 y ENTER (resaltamos los paréntesis porque recuerda que es necesario ponerlos)
• Vuelve a hacer clic en D3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna D
• Haz clic en E3
• Teclea la fórmula =(MES(A3)<=9)*1 y ENTER
• Vuelve a hacer clic en E3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna E

El resultado debería ser:

 

 

Fíjate que la columna D muestra el valor 1 (VERDADERO) si el mes de la fecha en la columna A es julio, agosto, septiembre, octubre, noviembre o diciembre. Y también fíjate que la columna E muestra el valor 1 (VERDADERO) si el mes de la fecha en la columna A es septiembre, agosto, julio, junio, mayo, abril, marzo, febrero o enero. Por tanto, si te fijas en las columnas D y E por separado, aparentemente no estamos resolviendo lo que te piden.

Pero si te fijas en las dos columnas D y E a la vez, verás que las filas que tienen un 1 en la columna D y, a la vez, un 1 en la columna E, son justamente las filas de la columna A que son de los meses que te están solicitando: julio, agosto y septiembre.

Por tanto, podemos crear, en la columna F, una fórmula que me indique claramente las filas que tienen un 1 a la vez en las columnas D y E. La mejor fórmula es, precisamente, multiplicar (operador "*") las filas de la columna D y E, de forma que en la columna F solamente habrá un 1 si D y E son 1 a la vez. Para verlo, haz lo siguiente:

• Haz clic en F3
• Teclea la fórmula =E3*D3 y ENTER.
• Vuelve a hacer clic en F3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna F

El resultado debería ser:

 

 

Para que lo veas mejor, hemos coloreado las celdas que en la columna D contienen un 1, las celdas que en la columna E tienen un 1 y comprueba que solamente las filas que son todas verdes tienen un 1 en la columna F:

 

 

 

Por consiguiente, al trabajar con 1's y 0's en vez de con VERDADERO y FALSO, el operador * hace las veces de operador lógico "AND" porque, como ves en las celdas naranjas de la imagen anterior, solamente 1*1 = 1 (VERDADERO), mientras que en resto de los casos es 0 (FALSO).

 

Ahora es cuestión de sumar los 1's de la columna F para saber cuántas fechas de la columna A son del tercer trimestre:

• Haz clic en G1
• Teclea la fórmula =SUMA(F:F) y ENTER

El resultado es el mismo que antes:

 

 

Sin embargo, podemos hacer este cálculo si unimos los cálculos de las columnas D y E en una sola operación en la columna F, haciendo lo siguiente:

• Selecciona las columnas D y E y pulsa la tecla SUPR para borrar su contenido
• Haz clic en F3 y pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula =(MES(A3)>=7)*(MES(A3)<=9) y ENTER (resaltamos los paréntesis porque recuerda que es necesario ponerlos)
• Vuelve a hacer clic en F3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna F

El resultado debería ser:

 

 

Nuevamente es el resultado en la celda G1 es el correcto, pero ahora solamente hemos usado la columna F.

Y por último, podemos dejarlo todo en una sola fórmula matricial de Tipo 3, solamente habrá que tener en cuenta usar rangos en vez de celdas, en concreto, el rango A3:A102:

• Haz clic en G1
• Teclea o modifica la fórmula así:

=SUMAPRODUCTO((MES(A3:A102)>=7)*(MES(A3:A102)<=9)) y ENTER

• Selecciona la columna F y pulsa la tecla SUPR para borrar su contenido

El resultado no cambia pues ahora la fórmula en G1 es totalmente independiente de rangos externos:

 

 

 

Esperamos que con esto te haya quedado más claro por qué el operador * actúa como si fuese un AND lógico.

 

Cambiando un poco lo que hemos estado diciendo hasta ahora, podemos ahora decir que, es lo mismo:

• Poner en D3 la fórmula =(MES(A3)>=7)*1, acabar con ENTER y rellenar la columna. Poner en E3 la fórmula =(MES(A3)<=9)*1, acabar con ENTER y rellenar la columna. Poner en F3 la fórmula =D3*E3 para hacer un "AND", acabar con ENTER y rellenar la columna. Y por último poner en G1 la fórmula  =SUMAPRODUCTO(F3:F102) y acabar con ENTER, Es lo mismo que:
• Es lo mismo que usar directamente en G1 la fórmula =SUMAPRODUCTO((MES(A3:A102)>=7)*(MES(A3:A102)<=9)) y acabar con ENTER.

 

Ahora hazlo tú:

Seguimos en el archivo "Cuestiones.xls" en la hoja "Compras Meses":

 

 

Es la misma hoja que "Compras Meses 2". Tenemos las mismas fechas en la columna A, pero además hemos añadido a esa fecha el número de compras de los clientes y el volumen de facturación de esa fecha en las columnas B y C.

Supón que te piden que uses SUMAPRODUCTO() para calcular en F3 cuántas fechas de los meses entre septiembre y noviembre hay con 10 ó más compras y con un volumen de 20.000 ó más. Es decir, te piden usar SUMAPRODUCTO() para hacer un conteo. El resultado debería ser:

 

 

Te damos una pista 1:

Clic aquí para ver

 

Te damos una pista 2:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Sumar valores que cumplen una o varias condiciones lógicas

Hasta ahora hemos usado SUMA() o SUMAPRODUCTO() para contar valores. Ahora vamos a ver cómo usar estas dos funciones para sumar valores, lo que a fin de cuentas es la utilidad de estas funciones. Recuerda que, el uso de SUMA() y SUMAPRODUCTO() para contar, no es más que uno de los "trucos" muy usados en las fórmulas matriciales.

 

Continuaremos usando el operador * como AND lógico para obtener la suma de valores que cumplan varias condiciones lógicas a la vez, pero además esta vez lo mezclaremos con el operador * usado como multiplicación aritmética, la multiplicación "de toda la vida".

 

Seguimos en el archivo "Cuestiones.xls" en la hoja "Compras meses":

 

 

Esta vez te piden, en la celda F11, una fórmula que calcule la suma total del volumen de compras, pero solamente de las compras hechas entre los meses de septiembre a noviembre, con más de 10 compras y que facturen más de 20.000. Fíjate que no te piden "cuántas" sino que "sumes" el volumen de compras.

Primero te diremos la fórmula y luego te explicaremos por qué esta fórmula da la suma correcta:

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

=SUMAPRODUCTO((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000)*(C3:C102)) y ENTER

El resultado debería ser:

 

 

Esto significa que la suma de los valores de la columna C que cumplen las 4 condiciones que nos han pedido, es el valor que se muestra en la celda F11.

• También puedes teclear en H11 la fórmula:

=SUMA((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000)*(C3:C102)) y CONTROL+MAYUSCULAS+ENTER

Y el resultado será el mismo.

 

El quid de la fórmula es la multiplicación por el último rango *(C3:C102), que hemos resaltado en color. Este rango es justamente el rango que contiene los valores que te han pedido sumar. Cuando cada una de las 4 condiciones lógicas sean VERDADERO ó 1, la multiplicación de todas ellas también dará un 1. En el resto de los casos dará FALSO ó 0. Hasta aquí consideramos el operador * como un AND lógico. El uso del operador * con las 4 condiciones lógicas solamente puede dar un resultado 1 ó 0.

Pero si añadimos un * más para multiplicar dicho resultado final 1 ó 0 por un número (en este caso el número que hay en el rango C3:C102) ya no estamos usando * como AND lógico sino como una multiplicación aritmética "de toda la vida". Cuando el resultado final es 1, la multiplicación del valor en la columna C por un 1 da como resultado ese mismo valor. Cuando el resultado final es 0, la multiplicación del valor en la columna C por un 0 da como resultado 0. Por tanto, solamente hay que sumar esos valores para obtener el total ya que los ceros no influyen en la suma.

 

Al igual que antes, sería interesante que vieses cómo opera Excel con el producto * usado como AND lógico y, al mismo tiempo, usado para obtener los valores a sumar. Haz lo siguiente:

 

Ponte en la hoja "Compras Meses 2" del archivo "Cuestiones.xls":

 

 

Borra todas las fórmulas que hayan podido quedar de ejemplos anteriores.

Vamos a hacer el mismo ejemplo que el anterior pero lo haremos por partes: usaremos la columna D para indicar si el mes de la fecha en la columna A es mayor o igual que 9, usaremos la columna E para indicar si el mes de la fecha en la columna A es menor o igual que 11, usaremos la columna F para indicar si la cantidad de compras de la columna B es mayor o igual que 10 y usaremos la columna G para indicar si el volumen de compras de la columna C es mayor o igual que 20.000. Lo haremos con operaciones lógicas no matriciales para verlo más claramente:

• Haz clic en D3
• Teclea la fórmula =(MES(A3)>=9)*1 y ENTER (resaltamos los paréntesis porque recuerda que es necesario ponerlos)
• Haz clic en E3
• Teclea la fórmula =(MES(A3)<=11)*1 y ENTER
• Haz clic en F3
• Teclea la fórmula =(B3>=10)*1 y ENTER
• Haz clic en G3
• Teclea la fórmula =(C3>=20000)*1 y ENTER
• Selecciona el rango desde D3 hasta G3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda G3) para rellenar las columnas D, E, F y G con las fórmulas que has tecleado

El resultado debería ser:

 

 

En este caso, la columna D muestra el valor 1 (VERDADERO) si el mes de la fecha en la columna A es desde septiembre hasta diciembre. La columna E muestra el valor 1 (VERDADERO) si el mes de la fecha en la columna A es desde enero hasta noviembre. La columna F muestra el valor 1 (VERDADERO) si la cantidad de compras de la columna B es mayor o igual que 10. La columna G muestra el valor 1 (VERDADERO) si el volumen de compras de la columna C es mayor o igual que 20.000. Por tanto, si te fijas en las columnas D, E, F y G por separado, aparentemente no estamos resolviendo lo que te piden.

Pero si te fijas en todas las columnas a la vez, verás que las filas que tienen un 1 en las cuatro columnas D, E, F y G a la vez son justamente las filas que cumplen las 4 condiciones que te han pedido.

Por tanto, podemos crear, en la columna H, una fórmula que me indique claramente las filas que tienen un 1 a la vez en las columnas D, E, F y G. La mejor fórmula es, precisamente, multiplicar (operador "*") las filas de las columnas D, E, F y G de forma que en la columna H solamente habrá un 1 si todas las filas son 1 a la vez. Para verlo, haz lo siguiente:

• Haz clic en H3
• Teclea la fórmula =E3*D3*F3*G3 y ENTER.
• Vuelve a hacer clic en H3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna H

El resultado debería ser:

 

 

Para que lo veas mejor, en la siguiente imagen hemos coloreado las celdas que contienen un 1 en las columnas D, E, F y G y comprueba que solamente las filas que son todas verdes tienen un 1 en la columna H:

 

 

 

Por consiguiente, al trabajar con 1's y 0's en vez de con VERDADERO y FALSO, el operador * hace las veces de operador lógico "AND" porque, como ves en las celdas naranjas de la imagen anterior, solamente 1*1*1*1 = 1 (VERDADERO), mientras que en resto de los casos es 0 (FALSO).

 

Pero aún no hemos acabado. Ahora hay que multiplicar la columna H por el valor que te han pedido sumar, que es el volumen de compras de la columna C. Lo haremos en la columna I. Haz lo siguiente:

• Haz clic en I3
• Teclea la fórmula =H3*C3 y ENTER (en este caso no estamos usando el operador * como AND lógico, sino como una multiplicación aritmética "de toda la vida")
• Vuelve a hacer clic en I3
• Haz doble clic en el cuadro de relleno (parte inferior derecha de la celda) para rellenar toda la columna I

El resultado:

 

 

Por tanto, la columna I contiene los mismos valores que la columna C pero solamente cuando el valor en la columna H es 1, es decir, cuando las 4 condiciones que te han pedido son 1 (VERDADERO). Por tanto, la columna I contiene los volúmenes de ventas de la columna C solamente cuando se cumplen las 4 condiciones a la vez.

Ahora es cuestión de sumar los valores de la columna I para saber la suma de los valores de la columna C que cumplen las 4 condiciones solicitadas:

• Haz clic en J1
• Teclea la fórmula =SUMA(I:I) y ENTER

El resultado es el mismo que antes:

 

 

Sin embargo, podemos hacer este cálculo si unimos los cálculos de las columnas D, E, F, G, H e I en una sola operación en la columna I, haciendo lo siguiente:

• Selecciona las columnas D, E, F, G y H y pulsa la tecla SUPR para borrar su contenido
• Haz clic en I3
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula:

=(MES(A3)>=9)*(MES(A3)<=11)*(B3>=10)*(C3>=20000)*(C3) y ENTER

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

El resultado debería ser:

 

 

Nuevamente es el resultado en la celda J1 es el correcto, pero ahora solamente hemos usado la columna I.

Y por último, podemos dejarlo todo en una sola fórmula matricial de Tipo 3, solamente habrá que tener en cuenta usar rangos en vez de celdas, en concreto, los rangos A3:A102, B3:B102 y C3:C102:

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

=SUMAPRODUCTO((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000)*(C3:C102)) y ENTER

• Selecciona la columna I y pulsa la tecla SUPR para borrar su contenido

El resultado no cambia al borrar la columna I pues ahora la fórmula en G1 es totalmente independiente de rangos externos.

 

Esperamos que con esto te haya quedado más claro por qué el operador * actúa como si fuese un AND lógico pero a la vez puede actuar como multiplicación aritmética usada para sumar valores que cumplan condiciones en una lista.

 

Como conclusión final, es mucho mejor y más sencillo usar fórmulas de lógica matricial que otras fórmulas, por ejemplo, los SI() anidados.

 

Otras operaciones aparte de Contar y Sumar

Como ya hemos dicho, las operaciones de conteo y suma no dan problemas porque los 0 no influyen. Recuerda que los 0 no influyen en los conteos siempre y cuando uses el truco de usar la función SUMA() para contar.

Pero en otras operaciones como máximo, mínimo, promedio, desviación estándar, etc., los ceros sí que influyen en el resultado final. Por tanto, cuando necesitemos estas operaciones, la lógica matricial de usar 1's y 0's puede dar lugar a resultados erróneos si no usamos la lógica matricial con precaución.

La solución es bastante simple: cuando queramos que los ceros no influyan en los resultados de una fórmula, basta con añadir a esa fórmula una función SI() adicional que cambie los 0's por valores nulos "".

Por ejemplo, siguiendo con el mismo ejemplo anterior de la hoja "Compras Meses 2", vamos a resaltar en color la función SI() que hay añadir en cada caso.

 

• Si te piden calcular el volumen de ventas máximo, tendrías que usar una función MAX() matricialmente. Por ejemplo, haz clic en K3 y teclea:

=MAX(SI((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000);(C3:C102);"")) y CONTROL+MAYUSCULAS+ENTER

 

• Si te piden calcular el volumen de ventas mínimo, tendrías que usar una función MIN() matricialmente. Por ejemplo, haz clic en K4 y teclea:

=MIN(SI((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000);(C3:C102);"")) y CONTROL+MAYUSCULAS+ENTER

 

• Si te piden calcular el promedio del volumen de ventas, tendrías que usar una función PROMEDIO() matricialmente. Por ejemplo, haz clic en K5 y teclea:

=PROMEDIO(SI((MES(A3:A102)>=9)*(MES(A3:A102)<=11)*(B3:B102>=10)*(C3:C102>=20000);(C3:C102);"")) y CONTROL+MAYUSCULAS+ENTER

 

Lo que hemos hecho es lo siguiente: para las condiciones lógicas hemos usado el operador * como AND lógico. Pero para devolver los valores del rango C3:C102 que cumplen todas las condiciones lógicas, ya no hemos usado el operador * como multiplicación aritmética estándar, sino que hemos usado una función SI() que retorne un nulo "" cuando no se cumplen todas las condiciones lógicas, y que retorne el valor correspondiente de C3:C102 cuando se cumplen todas las condiciones lógicas.

El resultado debería ser:

 

 

Suma de varias operaciones lógicas (OR lógico) y precaución a tomar

Hasta ahora hemos usado el operador multiplicación "*" como "AND" lógico. Ahora vamos a ver el uso del operador suma "+" como OR lógico.

Como ya hemos dicho, en el lenguaje cotidiano, suele usarse la palabra "y" de modo tal que en el lenguaje informático debe interpretarse muchas veces como "sumado a". Por ejemplo, si te piden el total de las compras de los meses de enero "y" de abril de un mismo año, en Excel no deberías usar el operador * (AND), porque ninguna fecha es "a la vez" del mes de enero "y" del mes de abril. La solución es usar el operador "+" como suma aritmética, es decir, "el total del mes de enero sumado a el total del mes de abril".

 

Ahora vamos a ver que también se puede usar el operador "+" como "OR" lógico cuando sea necesario.

 

Recuerda que, cuando usamos en Excel fórmulas matriciales de varios rangos, Excel opera por orden: primero con la 1ª celda de todos esos rangos, segundo con la 2ª celda de todos esos rangos, tercero con la 3ª celda de todos esos rangos, etc. Al conjunto de las celdas que están en el mismo lugar de orden en todos esos rangos se le denomina "fila". En una función como SUMAR.SI.CONJUNTO(), por ejemplo, si pones 10 criterio, estos serán tratados por Excel como AND, es decir, Excel sumará las celdas que cumplan esos 10 criterios a la vez en la misma "fila". Pero esto no quiere decir que no puedas usar SUMAR.SI.CONJUNTO() con 10 criterios y que Excel sume las celdas que cumplan solamente alguno de los criterios (OR lógico).

 

Primero lo veremos sin usar la lógica matricial, es decir, usando solamente funciones matriciales.

 

En este caso usaremos SUMAR.SI.CONJUNTO(), para lo cual has de disponer de Excel 2007 o mayor, o bien una cuenta de OneDrive.

Seguimos en el archivo "Cuestiones.xls" en la hoja "Ventas Meses":

 

 

Se trata de una lista de ventas de productos con un cierto código (columna A) que se han vendido en cierta fecha (columna B), una cierta cantidad de unidades (columna C) y por un cierto valor en euros (columna D).

Supón que te piden el total de ventas en euros (columna D) de aquellos productos que se hayan vendido una cantidad menor o igual a 10 unidades (columna C) pero que hayan facturado igual o más de 10.000 euros (columna D nuevamente). Pero te piden dicho total solamente de los productos PR-001 "y" PR-003 "y" PR-005. Las dos primeras condiciones hay que interpretarlas como un "AND" normal, pero la condición de los tres códigos de producto ha de interpretarse como "sumado a" debido a que ningún producto es a la vez el producto PR-001 y PR-003 y PR-005. Vamos primero a calcularlo por pasos, por ejemplo, en la columna L. Resaltamos en color la parte que cambia en cada fórmula:

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

=SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-001") y ENTER

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

=SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-003") y ENTER

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

=SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-005") y ENTER

El resultado debería ser:

 

 

Sin embargo, te han pedido la suma, por lo que debes teclear por ejemplo en L5 la fórmula:

=L2+L3+L4

El resultado:

 

 

Sin embargo, lo correcto es poner el cálculo en una sola celda. Haz lo siguiente:

• Haz clic en M2
• Teclea la siguiente fórmula que no es más que la suma de las tres fórmulas anteriores, por lo que si quieres puedes copiarlas y pegarlas para ahorrarte de teclear:

=SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-001") + SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-003") + SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-005") y ENTER

El resultado es el mismo que antes:

 

 

En este caso, por tanto, hemos usado el operador "+" como suma aritmética para "unir" el total de los tres productos y obtener así el total de ventas de tres los productos por separado pero que cumplan las dos primeras condiciones (menos de 10 ventas pero más de 10.000 de facturación). Dicho de otra manera, la siguiente fórmula incorrecta habría dado un resultado totalmente erróneo:

 

  =SUMAR.SI.CONJUNTO(D:D;C:C;"<=10";D:D;">=10000";A:A;"PR-001";A:A;"PR-003";A:A;"PR-005")

 

... ya que ningún producto es el producto con el código PR-001, el código PR-003 y el código PR-005 a la vez.

 

Ahora veremos el mismo ejemplo usando la lógica matricial donde el operador suma "+" se usa como "OR" lógico.

 

Con la lógica matricial ya puedes usar Excel 2003 o mayor. Lo haremos también por pasos. Indicaremos con 1's y 0's en las columnas E, F y G las filas que cumplan todas las condiciones:

• Haz clic en E2
• Teclea la siguiente fórmula estándar que indica con 1's si un producto es el PR-001 "o" el PR-032 "o" el PR-005. Observa que usamos el operador "+" como "OR" lógico y la condiciones lógicas también han de estar entre paréntesis:

=(A2="PR-001")+(A2="PR-003")+(A2="PR-005") y ENTER (resaltamos en color los paréntesis porque también es necesario ponerlos cuando se usa el operador "+" como OR lógico)

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

El resultado:

 

 

 

• Haz clic en F2
• Teclea la siguiente fórmula estándar que indica con 1's si de un producto se ha vendido igual o menos que 10 unidades pero ha facturado igual o más que 10.000 euros. Esta vez las condiciones lógicas sí que han de cumplirse a la vez:

=(C2<=10)*(D2>=10000) y ENTER (observa el operador * usado como AND lógico)

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

El resultado:

 

 

Por último, las filas que tengan 1 en la columna E y F son las que hay que sumar. Podemos indicar con un 1 en la columna G estas filas usando el operador * como AND. Haz lo siguiente:

• Haz clic en G2
• Teclea la siguiente fórmula estándar:

=E2*F2 y ENTER

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

El resultado:

 

 

 

Sumando la columna G podrías contar cuántas de las ventas cumplen estas condiciones, pero te han pedido la suma de las cantidades en euros, por tanto, puedes usar en la columna H el operador * como multiplicación aritmética para obtener los valores de la columna D que cumplen todas las condiciones (que en la columna G haya un 1):

• Haz clic en H2
• Teclea la siguiente fórmula estándar:

=G2*D2 y ENTER

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

El resultado:

 

 

Vamos a hacer la suma en la celda K2:

• Haz clic en K2
• Teclea la fórmula =SUMA(H2:H101) y ENTER

El resultado nuevamente debería ser el correcto:

 

 

Sin embargo, las fórmulas matriciales tienen como principal y más importante utilidad el no tener que usar rangos con cálculos intermedios, por tanto usaremos una fórmula matricial de Tipo 2 o Tipo 3 para hacer la suma sin rangos con cálculos intermedios. Nos saltamos el paso de convertir las fórmulas estándares en fórmulas matriciales de Tipo 1 y solamente te decimos que has de cambiar las celdas por rangos, que los rangos a usar para la fórmula matricial van desde la fila 2 hasta la fila 101 y que usaremos SUMAPRODUCTO() con ENTER (Tipo 3):

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

=SUMAPRODUCTO(((A2:A101="PR-001")+(A2:A101="PR-003")+(A2:A101="PR-005"))*(C2:C101<=10)*(D2:D101>=10000)*(D2:D101)) y ENTER

• Selecciona las columnas E, F, G y H y pulsa la tecla SUPR para borrar su contenido.

El resultado:

 

 

La fórmula en K3 muestra el resultado correcto sin rangos intermedios (la fórmula en K2 da resultado incorrecto pues necesita de las columnas E hasta H).

Recuerda que los valores cero no influyen en una suma pero sí en otras funciones. Si te pidiesen el promedio, máximo, mínimo, etc., habrías de sustituir el último operador *, el que actúa como multiplicación aritmética, por una función SI() que devuelva un nulo "" cuando no se cumplan las condiciones:

=PROMEDIO(SI(((A2:A101="PR-001")+(A2:A101="PR-003")+(A2:A101="PR-005"))*(C2:C101<=10)*(D2:D101>=10000);(D2:D101);"")) y CONTROL+MAYUSCULAS+ENTER

El promedio habría de ser en este caso 60.000.

 

Precaución a tomar cuando se usa el operador + como OR lógico

Hemos dicho que Excel trabaja indistintamente con FALSO o con 0's, es decir, para Excel un 0 y FALSO son lo mismo. Pero, en cambio, Excel considera VERDADERO cualquier otro valor distinto de cero, como 1, 2, 3, 4, 5, 6... o incluso números negativos. Esto hace que cuando usemos el operador + como OR lógico con varias condiciones lógicas, si el resultado es FALSO podemos estar seguros de que es un 0.

 

Pero si usamos el operador + como OR lógico y el resultado es VERDADERO, no podemos saber si ese VERDADERO es 1, 2, 3, 4, 5, 20, 80, -1, -2, -3, -25, 4000, 10.000,... Puede ser cualquier valor distinto de cero. Esto hace que necesitemos tomar precauciones cuando usemos + como OR lógico junto con el operador * como multiplicación, porque podemos pensar que siempre estaremos multiplicando por 1, pero en realidad podemos estar multiplicando por 1, 2, 3, 4, 5, 20, 80, -1, -2, -3, -25, 4000, 10.000,... o cualquier valor distinto de cero.

 

Para solucionar este inconveniente, se usa la función SI(). Vamos a ver un ejemplo y cómo se soluciona este inconveniente. Seguimos en el archivo "Cuestiones.xls" en la hoja "Cambios":

 

 

Se trata de una hoja de una supuesta empresa que vende unos productos. En esta hoja se anotan las incidencias en las ventas. Es una lista de productos mal entregados y que ha habido que cambiar. En la columna A están los códigos de los productos que fueron entregados al cliente y en la columna B los códigos de los productos que el cliente realmente solicitó. En la columna C está la cantidad de unidades cambiadas.

Supón que te piden que calcules la suma de unidades cambiadas que afectan a los productos PR-001 "y" PR-003, pero te lo piden tanto si esos dos productos son 'a cambiar' como si son 'cambiar por', es decir, tanto si aparecen en la columna A como si aparecen en la B. La solución es comprobar si PR-001 "y" PR-003 aparecen en la columna A "y" en la B. Podemos decidir usar operaciones lógicas con OR (ya sabemos entonces que esos "y" son un "+") y, una vez obtenido el VERDADERO o FALSO, usar el operador "*" como multiplicación aritmética "de toda la vida" para multiplicar por la columna C y así obtener la cantidad. Esto no va a funcionar en este caso. Te lo vamos a mostrar por pasos. Haz lo siguiente:

• Haz clic en D2
• Para comprobar si PR-001 ó PR-003 están en la columna A teclea esta fórmula (con + como OR lógico):

=(A2="PR-001")+(A2="PR-003") y ENTER

• Haz clic en E2
• Para comprobar si PR-001 ó PR-003 están en la columna B teclea esta fórmula (con + como OR lógico):

=(B2="PR-001")+(B2="PR-003") y ENTER

• Haz clic en F2
• Para comprobar si ambos productos están en la columna A o en la columna B teclea esta fórmula aparentemente correcta según lo que hemos explicado hasta ahora (con + como OR lógico):

=D2+E2 y ENTER

• Por último, dado que nos piden las cantidades de la columna C, podemos usar el operador * como multiplicación aritmética y multiplicar la cantidad de la columna C por el resultado que nos ha dado en la columna F (que nosotros por ahora estamos pensando que solamente puede ser 0 ó 1 y que enseguida veremos que no es así). Por tanto, haz clic en G2 y teclea la siguiente fórmula (con * como multiplicación):

=F2*C2 y ENTER

• Selecciona el rango D2:G2 y rellena las columnas haciendo doble clic en el cuadro de relleno de la celda G2.

El resultado:

 

 

Verás en la columna F unas celdas rojas. Estas celdas rojas indican un valor que no sea 1 ó 0. Observa que aparecen algunos valores 2. Para Excel un 2 significa VERDADERO, pero para una multiplicación aritmética sigue siendo un 2, por tanto, la fórmula en la columna G no repite el valor de la columna C como hemos visto hasta ahora sino que en algunas celda multiplica por 0, en otras multiplica por 1 (hasta aquí correcto) y en otras multiplica por 2, lo que da un resultado totalmente incorrecto.

Por tanto, la fórmula correcta en la columna F es una función SI() que cambie cualquier valor igual o mayor que 2 por un 1. Haz lo siguiente:

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

=SI(D2+E2=0;0;1) y ENTER

• Vuelve a hacer clic en F2 y haz doble clic en el cuadro de relleno para rellenar la columna entera.

El resultado ahora es correcto, ya no hay ningún 2 y por tanto las cantidades en la columna G ahora son correctas:

 

 

Haz la suma de la columna G y verás que el resultado es 1905, que es correcto.

Por último, tras hacer la "traducción" a una fórmula matricial de resumen de Tipo 3, la fórmula final que no depende de rangos intermedios es:

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

=SUMA(C2:C60*SI((A2:A60="PR-001")+(A2:A60="PR-003")+(B2:B60="PR-001")+(B2:B60="PR-003")=0;0;1))

• Y acaba con CONTROL+MAYUSCULAS+ENTER

Ya puedes borrar las columnas D hasta G y el resultado correcto final es:

 

 

 

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