Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 3.

 

Fórmulas matriciales: minicurso 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/s6o0zylb6thit0y/Excel-Matrices.zip?dl=1

 

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

 

Operaciones matriciales de Tipo 3: Funciones SI CONJUNTO.

Seguimos con las funciones matriciales de Tipo 3, es decir, aquellas funciones de Excel que tienen un comportamiento matricial, pues operan ordenadamente con las celdas de los rangos y por tanto tratan a los rangos como matrices, pero que se introducen con un ENTER normal, no es necesario acabar con CONTROL+MAYUSCULAS+ENTER, lo que las hace más cómodas y fáciles de usar.

Hasta ahora hemos visto SUMAPRODUCTO(), que es una función auténticamente matricial, y hemos visto también CONTAR.SI(), SUMAR.SI() y PROMEDIO.SI(), que, si bien son funciones que no son estrictamente matriciales, en este Minicurso las trataremos como si fuesen matriciales por su capacidad de trabajar ordenadamente con las celdas de los rangos.

Hemos visto que estas tres últimas funciones, CONTAR.SI(), SUMAR.SI() y PROMEDIO.SI(), solamente admiten una prueba lógica.

Con el tiempo, los usuarios de Excel solicitaron a Microsoft que incluyese nuevas funciones que permitiesen poner varias pruebas lógicas para así poder hacer cálculos potentes pero de manera sencilla. De este modo, a partir de la versión Excel 2007, Microsoft añadió a Excel las denominadas funciones SI CONJUNTO, que son una serie de funciones condicionales que admiten hasta 127 criterios o pruebas lógicas. Estas funciones SI CONJUNTO son:

 

• CONTAR.SI.CONJUNTO() a partir de Excel 2007
• SUMAR.SI.CONJUNTO() a partir de Excel 2007
• PROMEDIO.SI.CONJUNTO() a partir de Excel 2007
• MAX.SI.CONJUNTO() a partir de Office 365 versión enero 2016*
• MIN.SI.CONJUNTO() a partir de Office 365 versión enero 2016*

 

(*) En el momento de crear este Minicurso aún no existía una versión de Office de escritorio independiente de Office 365 que implementase estas funciones.

Nuevamente, estas funciones SI CONJUNTO no son estrictamente matriciales, pero aquí las trataremos como matriciales. De hecho, hasta la versión Excel 2003 inclusive (antes de que existiesen estas funciones SI CONJUNTO) era obligatorio usar fórmulas matriciales para trabajar con criterios múltiples, como veremos dentro de un momento.

Estas funciones SI CONJUNTO están pensadas para ser usadas en listas de Excel. Una lista de Excel equivale a organizar en columnas muchos elementos de una misma entidad, como si fuese una tabla de Access. Por ejemplo, una lista de Estudiantes, una lista de Profesores, una lista de Trabajadores, una lista de Proveedores, una lista de Productos, una lista de Facturas... Por ello, veremos los ejemplos de todas estas funciones sobre una serie de listas que hemos preparado en el archivo de Excel 2007, con extensión xlsx, con el nombre de "FuncionesSiConjunto.xlsx". Este archivo no se puede abrir en Excel 2003. La hoja "funciones SI CONJUNTO" debe tener este aspecto:

 

 

Esta hoja contiene una lista de facturas en sistema europeo en euros (€), con su fecha de emisión, plazo de pago en días, código de Cliente, cantidad, y si está pagada, vencida. Si una factura no está pagada y está vencida se considera impagada.

También tiene la hoja "Cine":

 

 

Esta hoja contiene una lista de películas con su director, protagonista, año, valoración en IMDB.com, número de votantes en IMDB.com, país, productora y un supuesto número de disco externo donde está la película.

Usaremos estas dos hojas para hacer cálculos con las funciones multicriterio SI CONJUNTO que salieron a partir de la versión Excel 2007.

 

Nota: En Excel, como en Access o cualquier base de datos, existe el concepto de "tabla", pero, estrictamente hablando, una "tabla" en Excel tiene más funcionalidades que una "lista". Las listas en Excel son más simples.

 

 

Vamos a verlas todas ellas. Haremos una subdivisión en dos partes de este tema:

 

- En la primera parte veremos las funciones SI CONJUNTO que se añadieron a partir de Excel 2007, con lo que necesitaréis la versión Excel 2007 o mayor para poder verlas.

La versión Office 2007+ (2007, 2010, 2013, 2016, 365) es la que deberíais usar ya que la versión de Office 2003 se encuentra descatalogada y puede dar problemas de seguridad. Office 2003 es, además, muy ineficiente para trabajar en una empresa si lo comparamos con Office 2007+.

 

- En la segunda parte veremos las funciones SI CONJUNTO que se añadieron a partir de Office 365 versión Enero 2016 (Versión 16.0.7070.2036), con lo que necesitaréis esta versión o superior para poder verlas.

La versión Office 365 es un servicio de suscripción de "MS Office en la nube" por medio del cual se tiene acceso a MS Office desde cualquier dispositivo con acceso a Internet, si bien también se puede instalar una copia en local en un dispositivo y trabajar sin necesidad de Internet. Office 365 requiere una suscripción de pago y, dado que la copia local ha de hacerse a través de la suscripción, nosotros no tenemos acceso a una versión de prueba gratuita de Office 365.

 

No obstante, si tienes acceso a Internet con tarifa plana, puedes usar Office Online Gratuito, también conocido como Microsoft OneDrive, que te permite crear documentos de Excel en la nube con una versión más simple que la versión de pago. Para acceder a Microsoft OneDrive ve al sitio web:

 

https://onedrive.live.com/

 

... haz clic en la opción "Suscríbete gratuitamente" y crea una cuenta "xxx@outlook.com" o bien xxx@outlook.[tu país]. Entonces usa la opción "Nuevo → Libro de Excel". El inconveniente de Microsoft OneDrive es que las fórmulas hay que teclearlas en inglés. Por ello, en este Minicurso, indicaremos la manera de introducir la fórmula en inglés. En inglés los parámetros de una función van separados por comas (,).Tienes un listado de equivalencias de nombres de funciones en inglés y español en el sitio web:

 

http://www.piuha.fi/excel-function-name-translation/index.php?page=espanol-english.html

 

 

 

Mira cómo se hace:

 

Nota: Si queréis estar informados de qué funciones nuevas se incorporarán a Excel y en qué fecha, podéis visitar el sitio web "Hoja de Ruta de Excel" (en inglés):

http://fasttrack.microsoft.com/roadmap

Incluso, si os atrevéis, podéis sugerir la incorporación de nuevas fórmulas que vosotros creáis que son necesarias. Microsoft recibirá vuestra sugerencia y la sopesará. Esto se hace en el sitio web "La Voz del Usuario" (en inglés):

https://excel.uservoice.com/

 

Función CONTAR.SI.CONJUNTO() para versiones Excel 2007 y posteriores

Su sintaxis es:

 

=CONTAR.SI.CONJUNTO(rango de datos a contar;"criterio a comprobar en ese rango";otro rango de datos;"otro criterio para ese rango";otro rango de datos;"otro criterio para ese rango";...)

 

Nota: En inglés es =COUNTIFS(rango,"criterio",rango,"criterio",...)

 

CONTAR.SI.CONJUNTO() hace un conteo de cuántos elementos de una lista cumplen uno o más criterios (pruebas lógicas) que se comprueban en una o varias columnas de la lista. Observa que a la derecha de cada rango está el criterio a comprobar en ese rango. Se puede poner hasta 127 rangos con sus correspondientes 127 criterios. Entre estos 127 rangos se pueden repetir rangos pero poniendo criterios distintos.

Los criterios siguen la misma sintaxis que hemos visto hasta ahora: los criterios o pruebas lógicas van entre comillas porque las pruebas lógicas se han de poner en forma de texto, pero por lo demás se usan los operadores lógicos normales = , > , >= , <  , <= , <>. En el caso del operador igual (=) puede sustituirse por no poner ningún operador y además puede ponerse el valor de comparación en una celda externa.

Si has descargado los ejemplos, abrir el archivo "FuncionesSiConjunto.xlsx" en la hoja "funciones SI CONJUNTO".

Vamos a hacer un conteo con más de un criterio, empezaremos por poner dos criterios: contaremos en la celda K6 cuántas facturas no pagadas tiene el cliente C001. Es decir, las dos pruebas lógicas son: que en la columna D haya un "C001" y que en la columna F haya un "NO". Sigue estos pasos:

• Haz clic en la celda K6
• Teclea la fórmula =CONTAR.SI.CONJUNTO(D2:D21;"C001";F2:F21;"NO"), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Y acaba con un ENTER normal.

El resultado ha de ser:

7

Es decir, hay 7 facturas del cliente C001 no pagadas. Fíjate en que esta función no tiene un parámetro que sea "rango a contar", simplemente el conteo se hace en los mismos rangos en los que se ponen los criterios. Los rangos son comprobados por columnas y cuando una fila de la lista cumple todos los criterios, se añade a la cuenta.

Para ver más claramente qué celdas ha contado la función CONTAR.SI.CONJUNTO(), te las mostramos coloreadas en verde:

 

 

Hemos coloreado primero en amarillo todas las filas con un C001 en la columna D, luego en amarillo todas las filas con un NO en la columna F, y por último, y, por último, las filas que coinciden que son amarillas a la vez en las columnas  D y F, las hemos señalado en verde, que son las que entran en el conteo.

Dado que los criterios son comparación de igualdad (=), podemos obviar el signo igual y además podemos poner el parámetro del criterio en una celda externa, en este caso podemos usar las celdas K2 y K3. Sigue estos pasos:

• Haz clic en la celda K6
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula =CONTAR.SI.CONJUNTO(D2:D21;K2;F2:F21;K3), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Y acaba con un ENTER normal.

El resultado ha de ser igual que en la fórmula anterior:

7

Es decir, hay 7 facturas del cliente C001 no pagadas. Pero si vas cambiando el contenido de las celdas K2 y K3, el resultado en K6 también va cambiando. Puedes ir probando con valores de C001 hasta C006 en K2 y con valores SI o NO en K3. Por ejemplo, C001 y SI da un valor de 1 en K6, C002 y SI da un valor de 3 en K6, C002 y NO da un valor de 1 en K6, etc.

 

Mira cómo se hace:

 

Nota: Tal como hemos dicho, para conseguir el mismo resultado en Excel 2003, antes de que existiesen las funciones SI CONJUNTO, habría que haber usado la siguiente fórmula de lógica matricial:

 

=SUMA((D2:D21=K2)*(F2:F21=K3)) y CONTROL+MAYUSCULAS+ENTER

 

o bien

 

=SUMAPRODUCTO((D2:D21=K2)*(F2:F21=K3)) y ENTER

 

... pero el tema de la lógica matricial lo veremos más adelante.

 

Recuerda que puedes poner hasta 127 pares de rango-criterio. Vamos a probar con 4 criterios. Supón que te piden que pongas en la celda K6 cuántas facturas hay del cliente C001 pagadas pero solamente las facturas del año que hay en la celda L1. Aunque a primera vista no lo parezca, esto implica poner 4 criterios, esto es debido a que para filtrar las facturas que sean de un cierto año, hay que poner 2 criterios: que la fecha en la columna B sea mayor o igual que el 1 de enero de tal año y, al mismo tiempo, que la fecha en la columna B sea menor o igual que el 31 de diciembre de tal año. Si a esto añadimos los otros dos criterios de que la columna D tenga un C001 y la columna F tenga un SI, da un total de 4 criterios. Resumiendo, necesitamos contar cuántas filas de la lista de facturas cumplen lo siguiente:

 

• Criterio 1: que la columna D tenga un C001, y...
• Criterio 2: que la columna F tenga un texto SI, y...
• Criterio 3: que la columna B tenga una fecha mayor o igual que FECHA(L1;1;1), y...
• Criterio 4: que la columna B tenga una fecha menor o igual que FECHA(L1;12;31)

Recordemos que en Excel, dentro de una fórmula, se puede poner una fecha entre comillas para hacer criterios (por ejemplo "31/12/2010"), pero ello no es en absoluto aconsejable porque cada país tiene un formato de fecha diferente y el orden de "día/mes/año" no es válido en todas partes. Por eso, es aconsejable usar la función FECHA(año;mes;día) que dará siempre un valor correcto independiente del país o región en que se use Excel.

Hasta ahora hemos dicho que cuando un criterio es de igualdad, el signo igual (=) se puede obviar, y poner el criterio en una celda externa.

 

Pero lo que no hemos dicho hasta ahora es que, para los demás criterios >, >=, <, <=, <>, dado que no es posible obviarlos, si queremos usar dichos criterios con una celda externa o con una fórmula, debe enlazarse dicha celda externa o dicha fórmula con un carácter de concatenación (&).

 

Por ejemplo, supongamos que queremos usar una celda externa como A1 para hacer una comparación en un criterio, todos los criterios posibles serían estos:

 

• Para igual que...  =nombrefuncion( ... A1 ...)
• Para mayor que... =nombrefuncion( ... ">"&A1 ...)
• Para mayor o igual que...  =nombrefuncion( ... ">="&A1 ...)
• Para menor que...  =nombrefuncion( ... "<"&A1 ...)
• Para menor o igual que...  =nombrefuncion( ... "<="&A1 ...)
• Para distinto que...  =nombrefuncion( ... "<>"&A1 ...)

Y en nuestro caso concreto, para la función FECHA(año;mes;día) deberíamos poner:

 

• Para igual que...  =nombrefuncion( ... FECHA(año;mes;día) ...)
• Para mayor que...  =nombrefuncion( ... ">"&FECHA(año;mes;día) ...)
• Para mayor o igual que...  =nombrefuncion( ... ">="&FECHA(año;mes;día) ...)
• Para menor que...  =nombrefuncion( ... "<"&FECHA(año;mes;día) ...)
• Para menor o igual que...  =nombrefuncion( ... "<="&FECHA(año;mes;día) ...)
• Para distinto que...  =nombrefuncion( ... "<>"&FECHA(año;mes;día) ...)

Por tanto, sigue estos pasos para crear la fórmula que nos piden:

• Haz clic en la celda K3
• Teclea el texto "SI" y pulsa la tecla ENTER
• Haz clic en la celda K6
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula
   
  =CONTAR.SI.CONJUNTO(D2:D21;K2;F2:F21;K3;B2:B21;">="&FECHA(L1;1;1);B2:B21;"<="&FECHA(L1;12;31))
 
puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Y acaba con un ENTER normal.

El resultado debería ser:

1

 

Mira cómo se hace:

 

Para que veas más claramente qué celdas ha contado la función CONTAR.SI.CONJUNTO(), te las mostramos coloreadas en verde:

 

 

Hemos coloreado primero en amarillo todas las filas con un C001 en la columna D, luego en amarillo todas las filas con un SI en la columna F, luego en amarillo todas las filas con la fechas del año que hay en L2 y, por último, las filas que coinciden que son amarillas a la vez en las columnas B, D y F, las hemos señalado en verde, que son las que entran en el conteo.

Es decir, hay 1 facturas del cliente C001 del año en L1 que SI están pagadas. Pero si vas cambiando el contenido de las celdas K2 y K3, o incluso el año en L1, el resultado en K6 también va cambiando. Puedes ir probando con valores de C001 hasta C006 en K2 y con valores SI o NO en K3. Por ejemplo, C001 y NO da un valor de 5 en K6, C002 y NO da un valor de 0 en K6, C002 y SI da un valor de 3 en K6, etc.

Ahora hazlo tú:

Siguiendo en el archivo "FuncionesSiConjunto.xlsx", ve a la hoja "Cine" y, en la celda N2, pon una fórmula para contar cuántas películas de "Aventura" de la década desde 2010 hasta 2020 ambos inclusive tienen 500.000 o más votantes. El resultado debería ser:

3

Nota: los rangos abarcan desde la fila 2 hasta la fila 178, pero puedes poner los rangos desde la fila 2 hasta la fila 3000 en previsión de que se añadan más películas con el tiempo.

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Los diversos criterios de una función SI CONJUNTO son interpretados por Excel como un AND

En cualquier función SI CONJUNTO, Excel solamente contea (o suma, o promedia...) las filas que cumplan todas las pruebas lógicas a la vez. Esto es lo que en informática se denomina "un AND lógico", que vendría a ser lo que en el lenguaje hablado diríamos "a la vez". Una función como CONTAR.SI.CONJUNTO(), solamente incluye en el conteo las filas que cumplen todos los criterios "a la vez", es decir: los criterios están formulados como un AND.

 

Una función SI CONJUNTO (ya sea sumar, contar, promediar, etc.) que tenga varios criterios, interpreta dichos criterios como un AND, es decir, solamente tomará en cuenta las filas que cumplan todos esos criterios "a la vez": criterio1 AND criterio2 AND criterio3 AND criterio4 AND ...

 

Por ejemplo, dijimos en un tema anterior que si alguien te pide cuántas facturas a 60 días hay del cliente C001 "y" del cliente C005, ese "y" habría que "traducirlo" a Excel como una suma de funciones, y ello sigue siendo válido para las funciones SI CONJUNTO. En ningún caso la solución es poner en una misma función dos criterios para un mismo rango, en este caso el rango de clientes, dado que ningún cliente es el cliente C001 y "a la vez" (AND) el C005.

Por ejemplo, siguiendo en el archivo "FuncionesSiConjunto.xlsx" en la hoja "funciones SI CONJUNTO", si te piden contar cuántas facturas hay a 60 días del cliente C001 "y" del cliente C005, habría que "traducir" a Excel como "las facturas a 60 días del cliente C001" más "las facturas a 60 días del cliente C005". Entonces, la fórmula correcta es:

• Haz clic en una celda vacía, por ejemplo en N2
• Teclea la fórmula:

=CONTAR.SI.CONJUNTO(C2:C21;60;D2:D21;"C001")+CONTAR.SI.CONJUNTO(C2:C21;60;D2:D21;"C005")

• Y acaba con un ENTER normal.

El resultado es:

1

... que corresponde a las celdas contadas, que son las que aquí mostramos en color verde:

 

 

Nunca habrá que usar una función como la que sigue porque dará un resultado erróneo:

=CONTAR.SI.CONJUNTO(C2:C21;60;D2:D21;"C001";D2:D21;"C005")  → ¡siempre dará resultado cero porque ningún cliente es el cliente C001 y "a la vez" (AND) es el cliente C005!

 

Uso del carácter de concatenación & para cambiar el operador lógico (tema opcional)

Aprovechando que hemos visto cómo usar el carácter de concatenación & para crear una prueba lógica usando una celda externa o una fórmula, vamos a ver que & también puede usarse para cambiar el propio operador lógico de la propia prueba lógica usando una celda externa. Ya hemos visto que los operadores lógicos son 6, es decir, = , > , >=, < , <= , <>. Ahora vamos a ver una fórmula en la que ponemos uno de estos 6 operadores lógicos en una celda externa para permitir al usuario más versatilidad para elegir qué datos quiere ver.

 

Este es un tema opcional. Haz clic aquí para mostrar/ocultar el tema.

 

Función SUMAR.SI.CONJUNTO() para versiones Excel 2007 y posteriores

Su sintaxis es:

 

=SUMAR.SI.CONJUNTO(rango de datos a sumar;rango de datos;"criterio a comprobar en ese rango";otro rango de datos;"otro criterio para ese rango";otro rango de datos;"otro criterio para ese rango";...)

 

Nota: En inglés es =SUMIFS(rango,rango,"criterio",rango,"criterio",...)

 

SUMAR.SI.CONJUNTO() hace una suma de los valores que hay en el primer rango de datos, es decir, en el primer parámetro de la función. El resto de los criterios indican qué filas de dicho rango usará Excel para calcular la suma. Observa que a la derecha de cada rango está el criterio a comprobar en ese rango. Se puede poner hasta 127 rangos con sus correspondientes 127 criterios. Entre estos 127 rangos se pueden repetir rangos pero poniendo criterios distintos.

 

Nota: observa que en la función SUMAR.SI() el primer parámetro es el rango al que se aplica el criterio, mientras que en la función SUMAR.SI.CONJUNTO() el primer parámetro es el rango al que se aplica la suma.

 

Los criterios siguen la misma sintaxis que hemos visto hasta ahora: los criterios (pruebas lógicas) van entre comillas porque las pruebas lógicas se han de poner en forma de texto, pero por lo demás se usan los operadores lógicos normales = , > , >= , <  , <= , <>. En el caso del operador igual (=) puede sustituirse por no poner ningún operador y además puede ponerse el valor de comparación en una celda externa.

Si ya has descargado los ejemplos, abre el archivo "FuncionesSiConjunto.xlsx" en la hoja "funciones SI CONJUNTO".

Vamos a hacer una suma con más de un criterio, empezaremos por poner dos criterios: sumaremos en la celda K5 las facturas no pagadas del cliente C001. Es decir, las dos pruebas lógicas son: que en la columna D haya un "C001" y que en la columna F haya un "NO", pero lo que se suma serán los valores de la columna E. En este caso usaremos directamente celdas externas para los criterios. Sigue estos pasos:

• Haz clic en la celda K5
• Teclea la fórmula =SUMAR.SI.CONJUNTO(E2:E21;D2:D21;K2;F2:F21;K3), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Y acaba con un ENTER normal.

El resultado ha de ser:

5.898,00

... que es la suma de las cantidades de las facturas no pagadas de C001. Observa que el primer parámetro de la función es la columna E y por tanto, como hemos dicho, es el rango sobre el que Excel hace la suma.

Si vas cambiando los valores de las celdas K2 y K3, el resultado ira cambiando según el cliente (pon en K2 los valores desde C001 hasta C006) y según desees ver las facturas pagadas (pon un SI en K3) o las no pagadas (pon un NO en K3).

Observa que al primer parámetro, el "rango a sumar", no se le aplica ningún criterio. Los rangos en los que Excel comprueba los criterios son los que se ponen a partir del segundo parámetro. Estos rangos son comprobados por columnas y cuando una fila de la lista cumple todos los criterios, se añade a la suma. Por ejemplo, en este caso, con "C001" en K2 y "NO" en K3, las cantidades sumadas han sido las que aquí indicamos en color verde:

 

 

Para conseguir el mismo resultado en Excel 2003, habría que haber usado la función de lógica matricial:

=SUMA((E2:E21)*(D2:D21=K2)*(F2:F21=K3)) y CONTROL+MAYUSCULAS+ENTER

o bien

=SUMAPRODUCTO((E2:E21)*(D2:D21=K2)*(F2:F21=K3)) y ENTER

... pero el tema de la lógica matricial lo veremos más adelante.

 

A pesar de que al primer parámetro de la función SUMAR.SI.CONJUNTO(), es decir, el rango a sumar, no se le aplica ningún criterio, nada impide usar ese rango a partir del segundo parámetro para ponerle algún criterio. Por ejemplo, supón que te piden calcular cuánto totalizan las facturas no pagadas de C001 mayores o iguales de 1000 euros a 30 días. Haz lo siguiente:

• Haz clic en la celda L2 e introduce el valor 30
• Haz clic en la celda L3 e introduce el valor 1000
• Haz clic en la celda K2 e introduce el texto "C001"
• Haz clic en la celda K3 e introduce el texto "NO"
• Haz clic en la celda K5 y pulsa la tecla SUPR para borrar su contenido
• Teclea la siguiente fórmula:

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;K2;C2:C21;L2;F2:F21;K3;E2:E21;">="&L3)

• Y acaba con un ENTER normal.

El resultado ha de ser:

3.443,00

... que es la suma de las cantidades de las facturas NO pagadas de C001 a 30 días mayores o iguales que 1000 euros.

 

Mira cómo se hace:

 

Observa que el primer parámetro de la función es la columna E y por tanto, como hemos dicho, es el rango sobre el que Excel hace la suma, pero el último rango también es la columna E sobre la que aplicamos el criterio que sea mayor o igual que la cantidad que hay en L3. Hemos coloreado en amarillo el cliente C001, 30 días, NO pagadas y cantidad mayor o igual que 1000. Las filas que coincide que son todas amarillas indican las celdas a las que afecta la suma, que te mostramos en color verde:

 

 

Ahora hazlo tú:

Siguiendo en el archivo "FuncionesSiConjunto.xlsx", ve a la hoja "Cine" e intenta poner en la celda N2 una función SUMAR.SI.CONJUNTO() que sume los votantes de las películas con 500.000 o más votantes del género "Ciencia Ficción" (con tilde la ó) a partir del año 2000 inclusive. Usa como rangos las columnas completas. El resultado debería ser:

2388996

Nota: los rangos abarcan desde la fila 2 hasta la fila 178, pero puedes poner los rangos desde la fila 2 hasta la fila 3000 en previsión de que se añadan más películas con el tiempo.

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Función PROMEDIO.SI.CONJUNTO() para versiones Excel 2007 y posteriores

Su sintaxis es totalmente igual que SUMAR.SI.CONJUNTO(), la diferencia es que PROMEDIO.SI.CONJUNTO() calcula el promedio de las celdas del primer rango:

 

=PROMEDIO.SI.CONJUNTO(rango de datos a promediar;rango de datos;"criterio a comprobar en ese rango";otro rango de datos;"otro criterio para ese rango";otro rango de datos;"otro criterio para ese rango";...)

 

Nota: En inglés es =AVERAGEIFS(rango,rango,"criterio",rango,"criterio",...)

 

PROMEDIO.SI.CONJUNTO() hace un promedio de los valores que hay en el primer rango de datos, es decir, en el primer parámetro de la función. El resto de los criterios indican qué filas de dicho rango usará Excel para calcular el promedio. Observa que a la derecha de cada rango está el criterio a comprobar en ese rango. Se puede poner hasta 127 rangos con sus correspondientes 127 criterios. Entre estos 127 rangos se pueden repetir rangos pero poniendo criterios distintos.

 

Nota: observa que en la función PROMEDIO.SI() el primer parámetro es el rango al que se aplica el criterio, mientras que en la función PROMEDIO.SI.CONJUNTO() el primer parámetro es el rango al que se aplica el promedio.

Tener siempre en cuenta que, en una suma, las celdas con valores cero o vacías no influyen en el resultado de la suma.

Por el contrario, un promedio da un cierto valor si las celdas a promediar contienen algunos ceros y otro valor distinto si las celdas a promediar no contienen ceros. Por tanto, es conveniente que te acostumbres a usar la función promedio de manera diferente a la función suma según desees que los ceros influyan o no en el resultado. Las celdas vacías no influyen en el cálculo del promedio.

 

Los criterios siguen la misma sintaxis que hemos visto hasta ahora: los criterios (pruebas lógicas) van entre comillas porque las pruebas lógicas se han de poner en forma de texto, pero por lo demás se usan los operadores lógicos normales = , > , >= , <  , <= , <>. En el caso del operador igual (=) puede sustituirse por no poner ningún operador y además puede ponerse el valor de comparación en una celda externa.

Si ya has descargado los ejemplos, abre el archivo "FuncionesSiConjunto.xlsx" en la hoja "funciones SI CONJUNTO".

Vamos a hacer un promedio con más de un criterio: supón que te piden promediar en la celda K4 las facturas no pagadas del cliente C001 y que los ceros no influyan en el cálculo del promedio. Es decir, las pruebas lógicas son: que en la columna D haya un "C001", que en la columna F haya un "NO" y que el valor en la columna E sea mayor que cero. Lo que se promedia serán los valores de la columna E. En este caso usaremos directamente celdas externas para los criterios. Sigue estos pasos:

• Haz clic en la celda K4
• Entonces tienes dos opciones:

• Y acaba con un ENTER normal.

El resultado debería ser:

842,57

 

Mira cómo se hace:

 

Nota: Tal como hemos dicho, para conseguir el mismo resultado en Excel 2003, antes de que existiesen las funciones SI CONJUNTO, habría que haber usado la siguiente fórmula de lógica matricial:

 

=PROMEDIO(SI((D2:D21=K2)*(F2:F21=K3)*(E2:E21>0)=1;(E2:E21);"")) y CONTROL+MAYUSCULAS+ENTER

 

... pero el tema de la lógica matricial lo veremos más adelante.

Para comprobar que los ceros no influyen, puedes hacer clic en la celda E3, que contiene el valor 345, e introducir un cero. Como resultado, el promedio aumenta a 925,50 porque el valor cero no influye, si influyese el cero, es decir, si hubieses tecleado la primera de las dos fórmulas, el promedio habría disminuido a 793,29.

Haz clic en E3 y vuelve a introducir el valor 345.

Ahora hazlo tú:

Teclea en la celda K12 una fórmula con PROMEDIO.SI.CONJUNTO() que calcule el promedio de las facturas NO pagadas del cliente C001, del año que hay en la celda L9 y que los ceros no influyan en el cálculo del promedio. Usa las celdas externas K10, K11 y L9 para la fórmula. El resultado debería ser:

1.226,00

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

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