Cómo utilizar la función Excel SUMPRODUCT en modelos de datos

  1. Software
  2. Oficina de Microsoft
  3. Excel
  4. Cómo utilizar la función Excel SUMPRODUCT en modelos de datos

Libro Relacionado

Cuadros de mando e informes de Excel para tontos, 3ª edición

Por Michael Alexander

La función SUMPRODUCT está listada bajo la categoría de matemáticas y trigonometría de las funciones de Excel. Debido a que el propósito principal de SUMPRODUCTO es calcular la suma del producto, la mayoría de la gente no sabe que realmente se puede utilizar para buscar valores. De hecho, puede utilizar esta versátil función con bastante eficacia en la mayoría de los modelos de datos.

Conceptos básicos de SUMPRODUCTO

La función SUMPRODUCT está diseñada para multiplicar valores de dos o más rangos de datos y luego sumar los resultados para obtener la suma de los productos. Eche un vistazo a la siguiente figura para ver un escenario típico en el que el SUMPRODUCTO es útil.

SUMPRODUCTO, obtener el total de ventas implica multiplicar el precio» width=»458″/>Sin el SUMPRODUCTO, obtener el total de ventas implica multiplicar el precio por unidades y luego sumar los resultados.

Verá un análisis común en el que necesita el total de ventas para los años 2011 y 2012. Como puede ver, para obtener el total de ventas de cada año, primero tiene que multiplicar el Precio por el número de Unidades para obtener el total para cada Región. Luego hay que sumar esos resultados para obtener el total de ventas de cada año.

Con la función SUMPRODUCT, puede realizar el análisis de dos etapas con una sola fórmula. La siguiente figura muestra el mismo análisis con fórmulas SUMPRODUCTO. En lugar de usar 11 fórmulas, puede realizar el mismo análisis con sólo 3!

La función SUMPRODUCT le permite realizar el mismo análisis con sólo» width=»535″/>La función SUMPRODUCT le permite realizar el mismo análisis con sólo 3 fórmulas en lugar de 11.

La sintaxis de la función SUMPRODUCT es bastante simple

:

SUMPRODUCTO(Array1, Array2, ....)

Array:Array representa un rango de datos. Puede utilizar entre 2 y 255 matrices en una fórmula SUMPRODUCTO. Las matrices se multiplican entre sí y luego se añaden. La única regla rápida que hay que recordar es que todos los arrays deben tener el mismo número de valores. Es decir, no se puede utilizar el SUMPRODUCTO si el rango X tiene 10 valores y el rango Y tiene 11 valores. De lo contrario, obtendrá el error #VALUE!

Un giro en la función SUMPRODUCTO

Lo interesante de la función SUMPRODUCTO es que se puede utilizar para filtrar valores. Eche un vistazo a la siguiente figura:


La función SUMPRODUCT se puede utilizar para filtrar datos basados en criterios» width=»535″/>La función SUMPRODUCT se puede utilizar para filtrar datos basados en criterios.

La fórmula en la celda E12 está jalando la suma de las unidades totales sólo para la región Norte. Mientras tanto, la celda E13 está jalando las unidades registradas para la región Norte en el año 2011.

Para entender cómo funciona esto, mira la fórmula en la celda E12. Esa fórmula dice SUMPRODUCTO ((C3:C10=»Norte»)*(E3:E10)).

En Excel, TRUE evalúa a 1 y FALSE evalúa a 0. Cada valor en la columna C que equivale a North evalúa a TRUE o 1. Donde el valor no es Norte, se evalúa a FALSO o 0. La parte de la fórmula que dice (C3:C10=»Norte») enumera a través de cada valor en el rango C3:C10, asignando un 1 o 0 a cada valor. Entonces internamente, la fórmula SUMPRODUCTO se traduce en

(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).

Esto te da la respuesta de 1628 porque

(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)

es igual a 1628.

Aplicación de fórmulas SUMPRODUCTO en un modelo de datos

Como siempre en Excel, no tiene que codificar los criterios en sus fórmulas. En lugar de utilizar explícitamente «Norte» en la fórmula SUMPRODUCTO, podría hacer referencia a una celda que contenga el valor del filtro. Puede imaginar que la celda A3 contiene la palabra Norte, en cuyo caso puede usar (C3:C10=A3) en lugar de (C3:C10=»Norte»). De esta manera, puede cambiar dinámicamente sus criterios de filtro y su fórmula se mantiene al día.

La siguiente figura muestra cómo puede utilizar este concepto para extraer datos de una tabla de puesta a disposición de material basada en múltiples criterios. Note que cada una de las fórmulas de SUMPRODUCTO mostradas aquí hace referencia a las celdas B3 y C3 para filtrar en Cuenta y Línea de Producto. Una vez más, puede agregar listas desplegables de validación de datos a las celdas B3 y C3, permitiéndole cambiar fácilmente los criterios.

La función SUMPRODUCT se puede utilizar para extraer números resumidos de la capa de datos a las tablas de puesta a disposición.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *