Descarga el libro de trabajo de ejemplo
Este tutorial demuestra cómo utilizar el Función SUMPRODUCT de Excel en Excel.
Descripción general de la función SUMPRODUCT
La función SUMPRODUCT Multiplica matrices de números y suma la matriz resultante.
Para usar la función de hoja de cálculo de Excel SUMPRODUCT, seleccione una celda y escriba:
(Observe cómo aparecen las entradas de la fórmula)
Función SUMPRODUCT Sintaxis y entradas:
| 1 | = SUMPRODUCTO (matriz1, matriz2, matriz3) |
array1 - Matrices de números.
¿Qué es la función SUMPRODUCT?
La función SUMPRODUCTO es una de las funciones más poderosas dentro de Excel. Su nombre, puede llevarlo a creer que solo está destinado a cálculos matemáticos básicos, pero puede usarse para mucho más.
Matrices
SUMPRODUCT requiere entradas de matrices.
Entonces, primero, ¿qué queremos decir con "matriz"? Una matriz es un grupo simple de elementos (por ejemplo, números) dispuestos en un orden específico, al igual que un rango de celdas. Entonces, si tuviera los números 1, 2, 3 en las celdas A1: A3, Excel leería esto como una matriz {1,2,3}. De hecho, puede ingresar {1,2,3} directamente en las fórmulas de Excel y reconocerá la matriz.
Hablaremos más sobre las matrices a continuación, pero primero veamos un ejemplo simple.
Matemáticas básicas
Veamos un ejemplo básico de SUMPRODUCT, usándolo para calcular las ventas totales.
Tenemos nuestra tabla de productos y queremos calcular las ventas totales. Tiene la tentación de agregar una nueva columna, tomar la cantidad vendida * precio y luego resumir la nueva columna. En su lugar, sin embargo, puede simplemente usar la función SUMPRODUCT. Repasemos la fórmula:
| 1 | = SUMPRODUCTO (A2: A4, B2: B4) |
La función cargará los rangos de números en matrices, los multiplicará entre sí y luego sumará los resultados:
| 1234 | = SUMPRODUCTO ({100, 50, 10}, {6, 7, 5})= SUMPRODUCTO ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCTO ({600, 350, 50}= 1000 |
La función SUMPRODUCT fue capaz de multiplicar todos los números por nosotros Y hacer la suma.
Peso promedio
Otro caso en el que resulta útil utilizar SUMPRODUCT es cuando necesita calcular un promedio ponderado. Esto ocurre con mayor frecuencia cuando se trata de tareas escolares, así que consideremos la siguiente tabla.
Podemos ver cuánto valen los cuestionarios, las pruebas y las tareas para la calificación general, así como cuál es el promedio actual para cada elemento en particular. Podemos calcular la calificación general luego escribiendo
| 1 | = SUMPRODUCTO (B2: B4, C2: C4) |
Nuestra función nuevamente multiplica cada elemento en las matrices antes de sumar el total. Esto funciona así
| 123 | = SUMPRODUCTO ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCTO ({22%, 45%, 19%})= 86% |
Varias columnas
Otro lugar en el que podríamos usar SUMPRODUCT es con aún más columnas que deben multiplicarse entre sí. Veamos un ejemplo en el que necesitamos calcular el volumen en piezas de madera.
En lugar de crear una columna auxiliar para calcular la venta total de cada fila, podemos hacerlo con una única fórmula. Nuestra formula sera
| 1 | = SUMPRODUCTO (B2: B5, C2: C5, D2: D5) |
Los primeros elementos de cada matriz se multiplicarán entre sí (por ejemplo, 4 * 2 * 1 = 8). Luego, el segundo (4 * 2 * 2 = 16) y 3rd, etc. En general, esto producirá el conjunto de productos que se parecen a {8, 16, 16, 32). Entonces el volumen total sería la suma de esa matriz, 72.
Un criterio
Bien, agreguemos otra capa de complejidad. Hemos visto que SUMPRODUCT puede manejar matrices de números, pero ¿qué pasa si queremos verificar los criterios? Bueno, también puede crear matrices para valores booleanos (los valores booleanos son valores que son VERDADEROS o FALSOS).
Por ejemplo, tome una matriz básica {1, 2, 3}. Creemos una matriz correspondiente que indique si cada número es mayor que 1. Esta matriz se vería como {FALSE, TRUE, TRUE}.
Esto es extremadamente útil en fórmulas, porque podemos convertir fácilmente VERDADERO / FALSO en 1/0. Veamos un ejemplo.
Con la siguiente tabla, queremos calcular "¿Cuántas unidades vendidas eran rojas?"
Podemos hacerlo, con esta fórmula:
| 1 | = SUMPRODUCTO (A2: A4, - (B2: B4 = "Rojo")) |
"¡Esperar! ¿Qué pasa con el símbolo del doble menos allí? " tu dices. ¿Recuerdas cómo dije que podíamos convertir de Verdadero / Falso a 1/0? Hacemos esto obligando a la computadora a realizar una operación matemática. En este caso, estamos diciendo "tome el valor negativo y luego vuelva a tomar el negativo". Escribiendo eso, nuestra matriz va a cambiar así:
| 123 | {Verdadero, Verdadero, Falso}{-1, -1, 0}{1, 1, 0} |
Entonces, volviendo a la fórmula SUMPRODUCT completa, se cargará en nuestras matrices y luego se multiplicará, así
| 123 | = SUMPRODUCTO ({100, 50, 10}, {1, 1, 0})= SUMPRODUCTO ({100, 50, 0})= 150 |
Note como el 3rd El elemento se convirtió en 0, porque cualquier cosa multiplicada por 0 se convierte en cero.
Múltiples criterios
Podemos cargar hasta 255 matrices en nuestra función, por lo que ciertamente podemos cargar más criterios. Veamos esta tabla más grande donde agregamos el mes vendido.
Si queremos saber cuántos artículos vendidos fueron rojos y estuviéramos en el mes de febrero, podríamos escribir nuestra fórmula como
| 1 | = SUMPRODUCTO (A2: A4, - (B2: B4 = "Rojo"), - (C2: C4 = "Feb")) |
Luego, la computadora evaluaría nuestras matrices y las multiplicaría. Ya hemos explicado cómo las matrices True / False se cambian a 1/0, por lo que voy a omitir ese paso por ahora.
| 123 | = SUMPRODUCTO ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCTO ({0, 50, 0})= 50 |
Solo teníamos una fila en nuestro ejemplo que coincidía con todos los criterios, pero con datos reales, es posible que haya tenido varias filas que necesita agregar juntas.
Criterios complejos
De acuerdo, hasta este punto, es posible que no esté impresionado porque todos nuestros ejemplos podrían haberse hecho usando otras funciones como SUMIF o COUNTIF. Ahora vamos a hacer algo con esas otras funciones hipocresía hacer. Anteriormente, nuestra columna Mes tenía los nombres reales de los meses. ¿Y si en cambio tuviera fechas?
No podemos hacer un SUMIF ahora, porque SUMIF no puede manejar los criterios que necesitamos. Sin embargo, SUMPRODUCT puede manejarnos manipulando la matriz y haciendo una prueba más profunda. Ya hemos estado manipulando matrices cuando traducimos Verdadero / Falso a 1/0. Vamos a manipular esta matriz con la función MES. Aquí está la fórmula completa que usaremos
| 1 | = SUMPRODUCTO (A2: A4, - (B2: B4 = "Rojo"), - (MES (C2: C4) = 2)) |
Veamos los 3rd matriz más de cerca. Primero, nuestra fórmula va a extraer el número de mes de cada fecha en C2: C4. Esto nos dará {1, 2, 2}. A continuación, verificamos si ese valor es igual a 2. Ahora nuestra matriz se ve como {Falso, Verdadero, Verdadero}. Hacemos el doble menos de nuevo y tenemos {0, 1, 1}. Ahora estamos de vuelta en un lugar similar al que teníamos en el Ejemplo 3, y nuestra fórmula podrá decirnos que se vendieron 50 unidades en febrero que eran rojas.
Doble menos frente a multiplicar
Si ha visto antes la función SUMPRODUCT en uso, es posible que haya visto una notación ligeramente diferente. En lugar de usar un doble menos, puede escribir
| 1 | = SUMPRODUCTO (A2: A4 * (B2: B4 = "Rojo") * (MES (C2: C4) = 2)) |
La fórmula seguirá funcionando de la misma manera, simplemente le decimos manualmente a la computadora que queremos multiplicar las matrices. SUMPRODUCT iba a hacer esto de todos modos, así que no hay ningún cambio en cómo funcionan las matemáticas. Realizar la operación matemática convierte nuestro Verdadero / Falso en 1/0 igual. Entonces, ¿por qué la diferencia?
La mayoría de las veces, no importa demasiado y se reduce a las preferencias del usuario. Sin embargo, hay al menos un caso en el que se necesita multiplicar.
Cuando usa SUMPRODUCT, la computadora espera que todos los argumentos (matriz1, matriz2, etc.) tengan el mismo tamaño. Esto significa que tienen el mismo número de filas o columnas. Sin embargo, puede hacer lo que se conoce como un cálculo de matriz bidimensional con SUMPRODUCT que veremos en el siguiente ejemplo. Cuando hace eso, las matrices son de diferentes tamaños, por lo que debemos omitir la verificación de "todos del mismo tamaño".
Dos dimensiones
Todos los ejemplos anteriores tenían nuestras matrices yendo en la misma dirección. SUMPRODUCT puede manejar las cosas en dos direcciones, como veremos en la siguiente tabla.
Aquí está nuestra tabla de unidades vendidas, pero los datos se reordenan según las categorías que van en la parte superior. Si queremos saber cuántos elementos eran rojos y en la categoría A, podemos escribir
| 1 | = SUMPRODUCTO ((A2: A4 = "Rojo") * (B1: C1 = "A") * B2: C4) |
¿¿Que esta pasando aqui?? Resulta que vamos a multiplicar en dos direcciones diferentes. Visualizar esto es más difícil de hacer con solo una oración escrita, por lo que tenemos algunas imágenes para ayudarnos. Primero, nuestro criterio de fila (¿es rojo?) Se multiplicará en cada fila de la matriz.
| 1 | = SUMPRODUCTO ((A2: A4 = "ROJO") * B2: C4) |
A continuación, el criterio de la columna (¿es categoría A?) Se multiplicará por cada columna.
| 1 | = SUMPRODUCTO ((A2: A4 = "Rojo") * (B1: C1 = "A") * B2: C4) |
Después de que ambos criterios hayan hecho su trabajo, los únicos no ceros que quedan son el 5 y el 10. SUMPRODUCT nos dará el gran total de 15 como nuestra respuesta.
¿Recuerda cómo hablamos de que las matrices deben ser del mismo tamaño a menos que esté haciendo dos dimensiones? Eso fue parcialmente correcto. Mira de nuevo las matrices que usamos en nuestra fórmula. los altura de dos de nuestros arreglos es el mismo, y el ancho de dos de nuestras matrices son iguales. Por lo tanto, aún debe asegurarse de que las cosas se alineen correctamente, pero puede hacerlo en diferentes dimensiones.
Dos dimensiones y complejo
Muchas veces se nos presentan datos que no están en el mejor diseño adecuado para nuestras fórmulas. Podríamos intentar reorganizarlo manualmente, o podemos ser más inteligentes con nuestras fórmulas. Consideremos la siguiente tabla.
Aquí tenemos los datos de nuestros artículos y ventas mezclados para cada mes. ¿Cómo podríamos averiguar cuántos artículos ha vendido Bob durante todo el año?
Para hacer esto, usaremos dos funciones adicionales: SEARCH e ISNUMBER. La función de BÚSQUEDA nos permitirá buscar nuestra palabra clave "elementos" dentro de las celdas del encabezado. La salida de esta función va a aparecer por un número o por un error (si no se encuentra la palabra clave). Luego, usaremos ISNUMBER para convertir ese salida en nuestros valores booleanos. Nuestra fórmula se verá a continuación.
Ya debería estar bastante familiarizado con la primera matriz. Creará una salida como {0, 1, 0, 1}. La siguiente matriz de criterios de la que acabamos de hablar. Va a crear un número para todas las celdas que contengan "Elementos" y un error para las demás {5, # N / A !, 5, # N / A!}. El ISNUMBER luego convierte esto a booleano {Verdadero, Falso, Verdadero, Falso}. Luego, cuando multiplicamos, solo mantendrá los valores de la primera y tercera columna. Después de que todas las matrices se multipliquen entre sí, los únicos números distintos de cero que tendremos serán los resaltados aquí:
| 1 | = SUMPRODUCTO ((A2: A5 = "Bob") * (ISNUMBER (SEARCH ("Items", B1: E1)) * B2: E5)) |
El SUMPRODUCTO los sumará todos y obtendremos nuestro resultado final de 29.
SUMPRODUCT o
Surgen muchas situaciones en las que nos gustaría poder sumar valores si nuestra columna de criterios tiene un valor U otro valor. Puede lograr esto en SUMPRODUCT agregando dos matrices de criterios entre sí.
En este ejemplo, queremos sumar las unidades vendidas tanto para rojo como para azul.
Nuestra fórmula se verá así
| 1 | = SUMPRODUCTO (A2: A7, (B2: B7 = "Rojo") + (B2: B7 = "Azul")) |
Veamos la matriz de criterios roja. Producirá una matriz que se ve así: {1, 1, 0, 0, 0, 0}. La matriz de criterios azul se verá como {0, 0, 1, 0, 1, 0}. Cuando los suma, la nueva matriz se verá como {1, 1, 1, 0, 1, 0}. Podemos ver cómo las dos matrices se han combinado en una única matriz de criterios. La función luego multiplicará eso por nuestra primera matriz y obtendremos {100, 50, 10, 0, 75, 0}. Observe que los valores de Green se han puesto a cero. El paso final del SUMPRODUCTO es sumar todos los números para llegar a nuestra solución de 235.
Una advertencia aquí. Tenga cuidado cuando las matrices de criterios no sean mutuamente excluyentes. En nuestro ejemplo, los valores de la columna B podrían ser Rojo o Azul, pero sabíamos que nunca podrían ser ambos. Considere si habíamos escrito esta fórmula:
| 1 | = SUMPRODUCTO (A2: A7, (A2: A7> = 50) + (B2: B7 = "Azul")) |
Nuestra intención es encontrar artículos azules que se vendieron o que estuvieran en una cantidad superior a 50. Sin embargo, estas condiciones no son exclusivas, ya que una sola fila podría tener más de 50 en la columna A y ser azul. Esto daría como resultado que la primera matriz de criterios se pareciera a {1, 1, 0, 1, 1, 0}, y la segunda matriz de criterios sería {0, 0, 1, 0, 1, 0}. Sumarlos produjo {1, 1, 1, 1, 2, 0}. ¿Ves cómo tenemos un 2 ahí ahora? Si se deja solo, el SUMPRODUCTO terminaría duplicando el valor en esa fila, cambiando el 75 a 150, y obtendríamos el resultado incorrecto. Para corregir esto, colocamos una verificación de criterios externos en nuestra matriz, así:
| 1 | = SUMPRODUCTO (A2: A7, - ((A2: A7> = 50) + (B2: B7 = "Azul")> 0)) |
Ahora, después de que se hayan sumado las dos matrices de criterios internos, verificaremos si el resultado es mayor que 0. Esto elimina los 2 que teníamos antes y, en su lugar, tendremos una matriz como {1, 1, 1 , 1, 1, 0} que producirá el resultado correcto.
SUMPRODUCT Exacto
La mayoría de las funciones en Excel no distinguen entre mayúsculas y minúsculas, pero a veces necesitamos poder hacer una búsqueda teniendo en cuenta la distinción entre mayúsculas y minúsculas. Cuando el resultado deseado es numérico, podemos lograr esto usando EXACT dentro de la función SUMPRODUCT. Considere la siguiente tabla:
Queremos encontrar la puntuación del ítem “ABC123”. Normalmente, la función EXACTA comparará dos elementos y devolverá una salida booleana que indique si los dos elementos son exactamente lo mismo. Sin embargo, dado que estamos dentro de un SUMPRODUCTO, nuestra computadora sabrá que estamos tratando con matrices y podrá comparar un elemento con cada elemento de una matriz. Nuestra fórmula se verá así
| 1 | = SUMPRODUCTO (- EXACTO ("ABC123", A2: A5), B2: B5) |
La función EXACTA luego verificará cada elemento en A2: A5 para ver si coincide con el valor y el caso. Esto producirá una matriz que se parece a {0, 1, 0, 0}. Cuando se multiplica por B2: B5, la matriz se convierte en {0, 2, 0, 0}. Después de la suma final, obtenemos nuestra solución de 2.
SUMPRODUCT en Google Sheets
La función SUMPRODUCT funciona exactamente igual en Google Sheets que en Excel:
Ejemplos de SUMPRODUCT en VBA
También puede usar la función SUMPRODUCT en VBA. Escriba: application.worksheetfunction.sumproduct (matriz1, matriz2, matriz3)
Ejecutando las siguientes declaraciones de VBA
| 1 | Rango ("B10") = Application.WorksheetFunction.SumProduct (Rango ("A2: A7"), Rango ("B2: B7")) |
producirá los siguientes resultados
Para los argumentos de la función (matriz1, etc.), puede ingresarlos directamente en la función o definir variables para usar en su lugar.
