La función del SUMMESLE, así como los SUMMESLES por dos criterios

1. Buscar por etiquetas

Imagine una tabla en la que los nombres de los departamentos (o cuentas, o algo más) se enumeran en filas en una fila.

Suma las celdas por criterio.
Es necesario calcular el monto total para cada departamento. Muchos lo hacen con un filtro y escribiendo con bolígrafos en las celdas.
Aunque se puede hacer de forma sencilla y sencilla con una sola función: SUMMESLI .
SUMMESLES (SUMIF): resume las celdas que satisfacen una condición determinada (solo se puede especificar una condición). Esta función también se puede usar si la tabla está dividida en columnas por períodos (mensual, en cada mes, tres columnas - Ingresos | Gastos | Diferencia) y usted necesita calcular el monto total de todos los períodos solo por Ingresos, Gastos y Diferencias.

Hay tres argumentos en total para SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Rango (A1: A20000): indica el rango con los criterios. Es decir La columna en la que se busca el valor indicado por el argumento Criterio .
• El criterio (A1) es el valor (texto o numérico, así como la fecha) que debe encontrarse en el rango . Puede contener los caracteres comodín "*" y "?". Es decir especificando el Criterio "* masa *" para resumir los valores en los que aparece la palabra "masa". Al mismo tiempo, la palabra "masa" puede aparecer en cualquier parte del texto, o solo puede haber una palabra en una celda. Y especificando "masa *", se resumirán todos los valores que comiencen con "masa". "?" - reemplaza un solo carácter, es decir, especificando "mas? a" puede resumir las líneas con el valor "masa" y el valor "máscara", etc.
  Si el criterio está escrito en una celda y aún necesita usar caracteres comodín, puede crear un enlace a esta celda agregando el necesario. Supongamos que necesita resumir los valores que contienen la palabra "total". La palabra "total" está escrita en la celda A1, mientras que en la columna A puede haber varios valores ortográficos que contengan la palabra "total": "totales de junio", "totales de julio", "totales de marzo". La fórmula entonces debería verse así:
  = VERANO (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*": el signo & (símbolo comercial) combina varios valores en uno. Es decir el resultado será "* resultado *".
  Para comprender mejor el principio de cómo funcionan las fórmulas, es mejor usar la herramienta Calcular fórmula : Cómo ver los pasos para calcular fórmulas.
  Todos los criterios textuales y los criterios con signos lógicos y matemáticos deben incluirse entre comillas dobles (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Si el criterio es un número, no se requieren comillas. Si desea encontrar un signo de interrogación o un asterisco directamente, debe colocar una tilde (~) delante de él.
  Sobre la tilde y sus características se pueden encontrar en este artículo: ¿Cómo reemplazar / eliminar / encontrar un asterisco?
• Sum_Range (B1: B20000) (argumento opcional): especifica el rango de sumas o valores numéricos que deben sumarse.

Cómo funciona: la función busca en el Rango el valor especificado por el argumento Criterio , y cuando se encuentra una coincidencia, suma los datos indicados por el argumento Rango-Cantidad. Es decir Si tenemos un nombre de departamento en la columna A y una cantidad en la columna B, especificar el Departamento de Desarrollo como criterio dará como resultado la suma de todos los valores de la columna B, frente al cual se encuentra el Departamento de Desarrollo en la columna A. De hecho, es posible que SumArrangement no tenga el mismo tamaño que el argumento de Rango y esto no causará un error de la función en sí. Sin embargo, al definir celdas para la suma, la celda superior izquierda del argumento Range_Amount se usará como la celda de inicio para la suma, y ​​luego se sumarán las celdas correspondientes en tamaño y forma al argumento Range.

Algunas caracteristicas
El último argumento de la función (Sum_And_Band: B1: B20000) es opcional. Esto significa que no se puede especificar. Si no lo especifica, la función sumará los valores especificados por el argumento de Rango . ¿Para qué sirve? Por ejemplo, necesita obtener la suma de solo aquellos números que son mayores que cero. En la columna A de la cantidad. Entonces la función se verá así:
= VERANO (A1: A20000; "> 0")

Lo que debe considerarse: el rango_summing y el rango deben ser iguales en el número de líneas. De lo contrario, puede obtener el resultado equivocado. De manera óptima, si se ve como en las fórmulas que he dado: el rango y el rango de las sumas comienzan desde una línea y tienen el mismo número de líneas: A1: A20000; B1: B20000

Suma sobre dos o más criterios
Pero, ¿qué hacer cuando los criterios para la suma 2 y más? Supongamos que necesita resumir solo las cantidades que pertenecen a un departamento y solo para una fecha determinada. Los felices propietarios de las versiones de Office 2007 y superiores pueden usar la función SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. El primer argumento especifica el rango de celdas que contienen las cantidades que se recopilarán en una sola.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Especifica el rango de celdas en las que desea buscar una coincidencia por criterio.
$ I $ 3, $ H8 - criterio. Aquí, como en SUMMESLI, se permiten los caracteres comodín * y ? y funcionan de la misma manera.

Específicos de los argumentos de especificación: primero, se especifica el rango de criterio (están numerados), luego el valor (criterio) se indica directamente en punto y coma, que en este rango se debe encontrar - $ A $ 2: $ A $ 50; $ I $ 3. Y nada más. No debe intentar especificar primero todos los rangos, y luego los criterios para ellos: la función dará un error o no resumirá lo que sea necesario.

Todas las condiciones se comparan de acuerdo con el principio I. Esto significa que si se cumplen todas las condiciones enumeradas. Si al menos una condición no se cumple, la función omite la línea y no agrega nada.
En cuanto a los SUMMERS, los rangos de suma y criterios deben ser iguales en el número de filas.

Porque SUMMESLIMN apareció solo en las versiones de Excel, a partir de 2007, entonces, ¿cómo pueden ser los usuarios infelices de versiones anteriores en estos casos? Muy simple: usar otra función - SUMPRODUCT. No pintaré los argumentos, porque Hay muchos de ellos y todos son matrices de valores. Esta función multiplica las matrices indicadas por los argumentos. Trataré de describir el principio general de usar esta función para resumir datos en varias condiciones.
Para resolver el problema de suma por varios criterios, la función se verá así:
= SUMPRODUCTO (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - rango de fechas. $ I $ 3 es la fecha del criterio para el cual es necesario sumar los datos.
$ B $ 2: $ B $ 50 - los nombres de los departamentos. H5: el nombre del departamento, los datos sobre los que se deben sumar.
$ C $ 2: $ C $ 50 - rango con cantidades.

Analizamos la lógica, porque para muchos, será completamente poco claro con solo mirar esta función. Si sólo porque en la ayuda esta aplicación no se describe. Para una mayor legibilidad, reducir el tamaño de los rangos:
= SUMPRODUCTO (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Por lo tanto, la expresión ($ A $ 2: $ A $ 5 = $ I $ 3) y ($ B $ 2: $ B $ 5 = H5) son lógicas y devuelven matrices de lógica FALSO y VERDADERO. VERDADERO si la celda del rango $ A $ 2: $ A $ 5 es igual al valor de la celda $ I $ 3 y la celda del rango $ B $ 2: $ B $ 5 es igual al valor de la celda H5. Es decir tenemos los siguientes
= SUMPRODUCTO ({FALSO; VERDADERO; VERDADERO; FALSO} * {FALSO; FALSO; VERDADERO; FALSO}; $ C $ 2: $ C $ 50)
Como puede ver, en la primera matriz hay dos coincidencias para la condición y en la segunda. Además, estas dos matrices se multiplican (el signo de multiplicación (*) es responsable de esto). Cuando se produce la multiplicación, se produce la conversión implícita de las matrices FALSE y TRUE a las constantes numéricas 0 y 1, respectivamente ({0; 1; 1; 0} * {0; 0; 1; 0}). Como saben, cuando se multiplican por cero, obtenemos cero. Y el resultado es una matriz única:
= SUMPRODUCTO ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Luego la matriz {0; 0; 1; 0} se multiplica por una matriz de números en el rango $ C $ 2: $ C $ 50:
= SUMPRODUCTO ({0; 0; 1; 0}; {10; 20; 30; 40})
Y como resultado, obtenemos 30. Lo que necesitábamos: solo obtenemos la cantidad que cumple con el criterio. Si hay más de una suma que satisfaga el criterio, entonces se resumirán.

Ventaja de SUMMYROIZV
Si los argumentos tienen el signo más en lugar del signo de multiplicación:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
entonces las condiciones se compararán de acuerdo con el principio OR: es decir, las sumas totales se resumirán si se cumple al menos una condición: $ A $ 2: $ A $ 5 es igual al valor de celda $ I $ 3 o celda de rango $ B $ 2: $ B $ 5 es igual al valor de celda H5.
Esta es la ventaja de SUMMPRODUCT sobre SUMMESLIMN. SUMMESLIMN no puede resumir los valores según el principio OR, solo según el principio AND (se deben cumplir todas las condiciones).

Desventajas
SUMPRODUCT no puede usar comodines * y? Es posible usarlo con mayor precisión, pero se percibirán no como caracteres especiales, sino como un asterisco y un signo de interrogación. Creo que esto es una desventaja significativa. Y aunque esto se puede omitir, uso otras funciones dentro de SUMPRODUCT; aún así sería genial si la función pudiera usar comodines de alguna manera.

En el ejemplo, encontrará un par de ejemplos de funciones para una mejor comprensión de lo que está escrito anteriormente.

Descargar un ejemplo

Cantidad por varios criterios (41.5 KiB, 10,477 Descargas)

Ver tambien
Sumando celdas por color de relleno
Suma de celdas por color de fuente
Sumando celdas por formato de celda
Calcula la cantidad de celdas por color de relleno.
Calcula la cantidad de celdas por fuente de color.
Cómo resumir datos de varias hojas, incluso por condición

{"Barra inferior": {"estilo de texto": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": " textcss ":" pantalla: bloque; relleno: 12px; text-align: left; "," textbgcss ":" pantalla: bloque; posición: absoluta; superior: 0px; izquierda: 0px; ancho: 100%; altura: 100% ; color de fondo: # 333333; opacidad: 0.6; filtro: a lpha (opacidad = 60); "," titlecss ":" display: block; posición: relativa; fuente: negrita 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; posición: relativa; fuente: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; posición: relativa; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforststst": 40}}

Buscar por etiquetas