Como he mencionado en muchos de mis blogs, un SUMPRODUCTO es una función muy versátil y se puede utilizar para múltiples propósitos. En este artículo veremos cómo podemos usar esta función para contar valores con múltiples o criterios.
Fórmula genérica SUMPRODUCT para contar con varios criterios o
| = SUMPRODUCTO (- (((criterio1) + (criterio2) +…)> 0) |
Criterio1: Este es cualquier criterio que devuelva una matriz de VERDADERO y FALSO.
Criterios2: Este es el siguiente criterio que desea verificar. Del mismo modo, puede tener tantos criterios como desee.
La fórmula genérica anterior se modifica a menudo para adaptarse a los requisitos para contar con múltiples criterios de quirófano. Pero la fórmula base es esta. Primero veremos cómo funciona esto a través de un ejemplo y luego discutiremos otros escenarios en los que necesitará modificar un poco esta fórmula.
Problema:
El precio en una celda debe dividirse con precisión en 5 pagos similares. Al simplemente dividir el precio $ 88.04 por 5 y redondear el resultado a dos lugares decimales, obtenemos pagos individuales de $ 17.61. Sin embargo, esto sumaría un pago total de $ 88.05, que no es la cantidad exacta con la que comenzamos. Por lo tanto, queremos calcular 5 números similares que suman la cantidad total.
Solución:
Primero Calcule el primer pago dividiendo el precio por 5 y redondeando el resultado.
Utilice la función REDONDEAR en la siguiente fórmula:
= REDONDO (B1 / 5,2)
Paso 2:
Calcula cada pago restante.
Utilice las funciones REDONDEAR, SUMA y FILA en la siguiente fórmula:
= REDONDA (($ B $ 1-SUMA ($ B $ 2: B2)) / (5-FILAS () + FILA ($ B $ 2)), 2)
Ejemplo:
Cuente los usuarios si el código del distribuidor coincide o el año que usa SUMPRODUCT
Entonces aquí tenemos un conjunto de datos de vendedores. Los datos contienen muchas columnas. Lo que tenemos que hacer es contar el número de usuarios que tienen el código "INKA" o el año es "2016". Asegúrese de que si alguien tiene ambos (código como "inka" y año 2016) debe contarse como 1.
Entonces, escuche que tenemos dos criterios. Usamos la fórmula SUMPRODUCT mencionada anteriormente:
| = SUMPRODUCTO (- (((Código = I3) + (Año = K3))> 0)) |
Aquí, el código y el año se denominan rangos. Esto devuelve 7.
En los datos tenemos 5 registros de código INKA y 4 registros del año 2016. Pero 2 registros tienen tanto "INKA" y 2016 como código y año respectivamente. Y estos registros se cuentan como 1. Y así es como obtenemos 7.
¿Vamos a ver cómo funciona?
Entonces, echemos un vistazo a cómo se resuelve la fórmula paso a paso, luego discutiré cómo funciona.
-
- = SUMPRODUCTO(- (((Código = I3) + (Año = K3))> 0))
- SUMPRODUCTO(- (({VERDADERO; FALSO; VERDADERO; VERDADERO; VERDADERO; VERDADERO;…} + {FALSO; FALSO; FALSO; VERDADERO; VERDADERO;…})> 0))
- SUMPRODUCTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCTO(- ({VERDADERO; FALSO; VERDADERO; VERDADERO; VERDADERO; VERDADERO; VERDADERO;…})
- SUMPRODUCTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Los signos dobles negativos (-) se utilizan para convertir valores booleanos en unos y ceros. Entonces, cada valor VERDADERO en la matriz se convierte en 1 y FALSO en 0.
Agregar más o criterios para contar usando SUMPRODUCT, por lo que si necesita agregar más o criterios para contar, puede agregar criterios usando el signo + a la función.
Por ejemplo, si desea agregar otro criterio a la fórmula anterior para que agregue el número de empleados que han vendido más de 5 productos. La fórmula SUMPRODUCT simplemente se verá así:
| = SUMPRODUCTO (- (((Código = I3) + (Año = K3) + (Ventas> 5))> 0)) |
¡Sencillo! ¿no es así?
Pero digamos que desea tener dos criterios del rango de código. Digamos que quiere contar "INKB". Entonces, ¿cómo haces esto? Un método es utilizar la técnica anterior, pero sería repetitivo. Digamos que quiero agregar 10 criterios más del mismo rango. En tales casos, esta técnica no es tan inteligente para contar con SUMPRODUCT.
Digamos que tenemos datos organizados de esta manera.
Los códigos de criterios están en una fila I2: J2. La disposición de los datos es importante aquí. La fórmula SUMPRODUCT para la configuración de recuento de 3 criterios OR será:
| = SUMPRODUCTO (- (((Código = I2: J2) + (Año = I3: J3))> 0)) |
Esta es la fórmula SUMPRODUCT para contar con varios criterios cuando se escriben en fila varios criterios de un rango.
La fórmula anterior devuelve la respuesta correcta que es 10.
Si escribe cualquier año en J3, la fórmula también agregará ese recuento.
Se utiliza cuando los criterios están en una fila. ¿Funcionará cuando los criterios en una columna para el mismo rango? No. No lo hará.
En este ejemplo, tenemos varios códigos para contar, pero estos códigos de tipo están escritos en una columna. Cuando usamos la fórmula SUMPRODUCT anterior, obtenemos un error ans # N / A. No entraremos en cómo se produce este error, ya que hace que este artículo sea demasiado extenso. Veamos cómo podemos hacer que esto funcione.
Para que esta fórmula funcione, debe ajustar los criterios del código en la función TRANSPONER. Esto hará que la fórmula funcione.
| = SUMPRODUCTO (- (((Código = TRANSPONER (H3: H4)) + (Año = TRANSPONER (I3: I4)))> 0)) |
Esta es la fórmula para contar con múltiples o condiciones en el mismo rango cuando los criterios se enumeran en una columna.
Aquí están todas las notas de observación sobre el uso de la fórmula.
Notas:
- La fórmula funciona tanto para texto como para números.
- Los valores de búsqueda se pueden dar como referencia de celda o directamente usando el símbolo de comillas (") en la fórmula como argumentos.
Espero que haya entendido cómo dividir una cantidad en pagos iguales evitando errores de división / redondeo en Excel. Explore más artículos sobre el valor de búsqueda de Excel y las funciones de Excel 2019 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.
Utilice ÍNDICE y COINCIDIR para buscar valor : Función INDICE & MATCH para buscar el valor requerido.
Rango SUM con INDICE en Excel : Utilice la función INDICE para encontrar la SUMA de los valores según sea necesario.
Cómo usar la función INDICE en Excel : Encuentre el ÍNDICE de una matriz usando la función ÍNDICE explicada con un ejemplo.
Cómo usar la función COINCIDIR en Excel : Busque el COINCIDIR en la matriz usando el valor INDICE dentro de la función COINCIDIR explicada con el ejemplo.
Cómo usar la función BUSCAR en Excel : Busque el valor de búsqueda en la matriz usando la función BÚSQUEDA explicada con un ejemplo.
Articulos populares :
Cómo usar la función SI en Excel : La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO.
Cómo utilizar la 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 SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
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.