En este artículo, aprenderemos cómo usar la función SI en lugar de la función SUMPRODUCT y SUMIFS en Excel.
Guión:
En palabras sencillas, cuando se trabaja con un conjunto de datos muy disperso, a veces necesitamos encontrar la suma de números con algunos criterios sobre ella. Por ejemplo, encontrar la suma de los salarios en un departamento en particular o tener varios criterios sobre la fecha, los nombres, el departamento o incluso los datos de números como los salarios por debajo del valor o la cantidad por encima del valor. Para esto, usualmente usa la función SUMPRODUCT o SUMIFS. Pero no lo creerías, realizas la misma función con la función básica de Excel Función SI.
¿Como resolver el problema?
Debe estar pensando cómo es esto posible, para realizar operaciones lógicas sobre matrices de tablas usando la función SI. Si la función en Excel es muy útil, le ayudará a superar algunas tareas difíciles en Excel o en cualquier otro lenguaje de codificación. La función SI prueba las condiciones en la matriz correspondientes a los valores requeridos y devuelve el resultado como una matriz correspondiente a las condiciones verdaderas como 1 y Falso como 0.
Para este problema, usaremos las siguientes funciones:
- Función SUM
- Función SI
Necesitaremos estas funciones anteriores y un sentido básico de operación de datos. Las condiciones lógicas en matrices se pueden aplicar utilizando operadores lógicos. Estos operadores lógicos funcionan tanto con texto como con números. A continuación, se muestra la fórmula genérica. { } llaves es la herramienta mágica para realizar fórmulas de matriz con la función SI.
Fórmula genérica:
| { = SUMA (SI ((logico_1) * (logico_2) *… * (logico_n), suma_array)) } |
Nota: para llaves ( { } ) Usar Ctrl + Mayús + Entrar al trabajar con matrices o rangos en Excel. Esto generará Curly Braces en la fórmula de forma predeterminada. NO intente codificar de forma rígida los caracteres de llaves.
Lógico 1: prueba la condición 1 en la matriz 1
Lógico 2: prueba la condición 2 en la matriz 2 y así sucesivamente
sum_array: matriz, se realiza la operación suma
Ejemplo :
Todos estos pueden resultar confusos de entender. Entonces, probemos esta fórmula ejecutándola en el ejemplo que se muestra a continuación. Aquí tenemos datos de productos entregados a diferentes ciudades junto con los campos de categoría correspondientes y las cantidades. Aquí tenemos los datos y necesitamos encontrar la cantidad de cookies enviadas a Boston donde la cantidad sea mayor a 40.
La tabla de datos y la tabla de criterios se muestran en la imagen de arriba. Para comprender el propósito, usamos rangos con nombre para las matrices utilizadas. Los rangos con nombre se enumeran a continuación.
Aquí :
Ciudad definida para la matriz A2: A17.
Categoría definida para la matriz B2: A17.
Cantidad definida para la matriz C2: C17.
Ahora está listo para obtener el resultado deseado utilizando la siguiente fórmula.
Usa la fórmula:
| { = SUMA (SI ((Ciudad = "Boston") * (Categoría = "Cookies") * (Cantidad> 40), Cantidad)) } |
Explicacion:
- City = "Boston": comprueba los valores en el rango de ciudades para que coincidan con "Boston".
- Categoría = "Cookies": comprueba los valores en el rango de Categoría para que coincidan con "Cookies".
- Cantidad> 40: comprueba los valores en el rango de cantidad a ma
- La cantidad sea una matriz donde se requiera la suma.
- La función SI comprueba todos los criterios y el asterisco char (*) multiplica todos los resultados de la matriz.
= SUMA (SI ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Ahora la función SI solo devuelve las cantidades correspondientes a los 1 y el resto se ignora.
- La función SUM devuelve SUM.
Ahora, la cantidad correspondiente a unos solo se suma para obtener el resultado.
Como puede ver, se devuelve la cantidad 43, pero hay tres pedidos de galletas entregados a "Boston" que tienen la cantidad 38, 36 y 43. Necesitábamos una suma de cantidad donde la cantidad fuera superior a 40. Por lo tanto, la fórmula devuelve 43 solamente. Ahora use otros criterios para obtener que la cantidad SUM para la ciudad: "Los Ángeles" y la categoría: "Bares" y la cantidad sea inferior a 50.
Usa la fórmula
| { = SUMA (IF ((Ciudad = "Los Ángeles") * (Categoría = "Bares") * (Cantidad <50), Cantidad)) } |
Como puede ver, la fórmula devuelve los valores 86 como resultado. Que es la suma de 2 pedidos que satisfacen las condiciones que tienen la cantidad 44 y 42. Este artículo ilustra cómo reemplazar una fórmula IF anidada con un IF único en una fórmula de matriz. Esto se puede utilizar para reducir la complejidad en fórmulas complejas. Sin embargo, este problema en particular podría resolverse fácilmente con la función SUMIFS o SUMPRODUCT.
Uso de la función SUMPRODUCTO:
La función SUMPRODUCTO devuelve la suma de los valores correspondientes en la matriz. Por lo tanto, obtendremos que las matrices devuelvan 1 a los valores de la declaración Verdadero y 0 a los valores de la declaración Falsa. Entonces, la última suma corresponderá donde todas las declaraciones sean verdaderas.
Usa la fórmula:
| = SUMPRODUCTO (- (Ciudad = "Boston"), - (Categoría = "Cookies"), - (Cantidad> 40), Cantidad) |
-: operación utilizada para convertir todos los VERDADEROS a 1 y los Falso a 0.
La función SUMPRODUCTO vuelve a verificar la SUMA de la cantidad devuelta por las funciones SUMA e SI explicadas anteriormente.
De manera similar, para el segundo ejemplo, el resultado es el mismo.
Como puede ver, la función SUMPRODUCT puede realizar la misma tarea.
Aquí están todas las notas de observación sobre el uso de la fórmula.
Notas:
- Sum_array en la fórmula solo funciona con números.
- Si la fórmula devuelve el error #VALOR, verifique que las llaves deben estar presentes en la fórmula como se muestra en los ejemplos del artículo.
- El carácter de negación (-) cambia los valores, VERDADERO o 1 a FALSO o 0 y FALSO o 0 a VERDADERO o 1.
- Operaciones como es igual a ( = ), menor que igual a ( <= ), mas grande que ( > ) o no igual a () se puede realizar dentro de una fórmula aplicada, solo con números.
Espero que este artículo sobre cómo usar la función SI en lugar de la función SUMPRODUCT y SUMIFS en Excel sea explicativo. Encuentre más artículos sobre fórmulas de suma aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico
Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel.
SUM si la fecha está entre : Devuelve la SUMA de valores entre fechas o períodos dados en Excel.
Suma si la fecha es mayor que la fecha dada: Devuelve la SUMA de los valores posteriores a la fecha o período especificado en Excel.
2 formas de sumar por mes en Excel: Devuelve la SUMA de valores dentro de un mes específico dado en Excel.
Cómo sumar varias columnas con condición: Devuelve la SUMA de los valores en varias columnas con condición en Excel.
Articulos populares :
50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.