Función SUMIF en Excel e suma por varias condicións

Excel é un programa incriblemente funcional. Incluso o conxunto de funcións incorporado é suficiente para completar case calquera tarefa. E ademais dos estándar, coñecidos para moitos, tamén os hai dos que pouca xente escoitou falar. Pero ao mesmo tempo, non deixan de ser útiles. Teñen unha especialización máis estreita, e non sempre hai necesidade deles. Pero se coñeces deles, nun momento crítico poden ser moi útiles.

Hoxe falaremos dunha destas funcións − SUMMESLIMN.

Se o usuario enfróntase á tarefa de resumir varios valores, centrándose en certos criterios, entón é necesario utilizar a función SUMMESLIMN. A fórmula que usa esta función toma estas condicións como argumentos, despois suma os valores que as cumpren e, a continuación, insírese o valor atopado na cela na que está escrito. 

Descrición detallada da función SUMIFS

Antes de considerar a función SUMMESLIMN, primeiro debes comprender cal é unha versión máis sinxela dela - SUMMESLI, xa que nel se basea a función que estamos considerando. Cada un de nós probablemente xa estea familiarizado con dúas funcións que se usan a miúdo: SUM (realiza a suma de valores) e SE (proba un valor contra unha condición especificada).

Se os combinas, obtén outra función: SUMMESLI, que comproba os datos con criterios especificados polo usuario e suma só os números que cumpren eses criterios. Se falamos da versión en inglés de Excel, esta función chámase SUMIF. En palabras sinxelas, o nome -language é unha tradución directa do inglés. Esta función pódese usar para diversos propósitos. En particular, pódese usar como alternativa VPR, é dicir, anotar

A principal diferenza entre a función SUMMESLIMN  da función habitual SUMMESLI é que se utilizan varios criterios. A súa sintaxe é bastante complexa a primeira vista, pero tras un exame máis detallado, resulta que a lóxica desta función é moi sinxela. Primeiro cómpre seleccionar o intervalo onde se comprobarán os datos e, a continuación, establecer as condicións de cumprimento das que se realizará a análise. E tal operación pódese realizar para un número bastante grande de condicións.

A sintaxe en si é:

SUMIFS(intervalo_suma, rango_condición1, condición1, [rango_condición2, condición2], ...)

En lugares axeitados é necesario poñer matrices de celas adecuadas nun caso particular. 

Vexamos os argumentos con máis detalle:

1. Rango_suma. Este argumento, así como o rango de condición 1 e condición 1, é necesario. É un conxunto de celas que hai que sumar.
2. Intervalo_condición1. Este é o intervalo onde se comprobará a condición. Combínase co seguinte argumento: Condición1. A suma dos valores correspondentes ao criterio realízase dentro das celas especificadas no argumento anterior.
3. Condición 1. Este argumento especifica os criterios que se deben comprobar. Pódese establecer, por exemplo, deste xeito: “> 32”.
4. Rango de condicións 2, Condición 2... Aquí, as seguintes condicións establécense do mesmo xeito. Se é necesario especificar máis dunhas poucas condicións, engádense os argumentos Intervalo de condicións 3 e Condición 3. A sintaxe é a mesma para os seguintes argumentos.

A función permite o procesamento máximo de ata 127 pares de condicións e intervalos. 

Podes usalo en varias áreas á vez (daremos só algunhas, a lista é aínda máis longa):

1. Contabilidade. Por exemplo, é bo usar a función SUMMESLIMN para crear informes de resumo, por trimestre para gastos superiores a unha determinada cantidade, por exemplo. Ou crea un informe sobre un produto dunha determinada categoría de prezo.
2. Xestión de vendas. Aquí tamén a función pode ser moi útil. Por exemplo, estamos ante a tarefa de resumir só o custo dos bens que foron vendidos a un determinado cliente nun determinado momento. E en tal situación, a función SUMMESLIMN pode ser moi útil.
3. Educación. Hoxe daremos exemplos máis prácticos desta área. En particular, pode usalo para obter un resumo das cualificacións dos estudantes. Podes escoller para unha única materia ou para as notas individuais. Unha persoa pode establecer inmediatamente varios criterios polos que se seleccionará a avaliación, o que é moi cómodo e pode aforrar moito tempo.

Como podes ver, o abano de aplicacións desta función é moi amplo. Pero este non é o seu único mérito. Vexamos algúns beneficios máis que ten esta función:

1. Capacidade de establecer varios criterios. Por que isto é unha vantaxe? Podes usar a función normal SUMMESLI! E todo porque é conveniente. Non é necesario realizar cálculos separados para cada un dos criterios. Todas as accións pódense programar con antelación, incluso antes. como se formará a táboa de datos. Isto é un gran aforro de tempo.
2. Automatización. A era moderna é a era da automatización. Só a persoa que sabe automatizar correctamente o seu traballo pode gañar moito. É por iso que a capacidade de dominar Excel e a función SUMMESLIMN en particular, é tan importante para calquera persoa que queira construír unha carreira. Coñecer unha función permítelle realizar varias accións á vez, como unha soa. E aquí pasamos ao seguinte beneficio desta función.
3. Aforrando tempo. Só polo feito de que unha función realiza varias tarefas á vez.
4. Sinxeleza. A pesar de que a sintaxe é bastante pesada a primeira vista debido ao seu volume, de feito, a lóxica desta función é moi sinxela. En primeiro lugar, selecciónase un intervalo de datos e, a continuación, un intervalo de valores, que se comprobará o cumprimento dunha determinada condición. E, por suposto, tamén hai que especificar a propia condición. E así varias veces. De feito, esta función baséase só nunha construción lóxica, o que a fai máis sinxela que a coñecida VPR a pesar de que se pode utilizar para os mesmos fins, tendo en conta tamén un maior número de criterios. 

Características do uso da función SUMIFS

Hai varias características de uso desta función ás que debes prestar atención. En primeiro lugar, esta función ignora intervalos con cadeas de texto ou nulos, xa que estes tipos de datos non se poden sumar nun patrón aritmético, só concatenarse como cadeas. Esta función non pode facelo. Tamén cómpre prestar atención ás seguintes condicións:

1. Podes usar estes tipos de valores como condicións para seleccionar celas para engadir aínda máis os valores contidos nelas: valores numéricos, expresións booleanas, referencias de celas, etc. 
2. Se se están verificando texto, expresións lóxicas ou signos matemáticos, tales criterios especifícanse entre comiñas.
3. Non se poden usar termos de máis de 255 caracteres.
4. É posible utilizar criterios aproximados para seleccionar valores mediante comodíns. O signo de interrogación úsase para substituír un só carácter e o signo de multiplicación (asterisco) é necesario para substituír varios caracteres. 
5. Os valores booleanos que están no intervalo de suma convértense automaticamente en valores numéricos segundo o seu tipo. Así, o valor "VERDADEIRO" convértese nun e "FALSO" - en cero. 
6. Se un #VALOR! aparece un erro nunha cela, significa que o número de celas dos intervalos de condición e suma é diferente. Debes asegurarte de que os tamaños destes argumentos sexan os mesmos. 

Exemplos de uso da función SUMIFS

función SUMMESLIMN non é tan complicado como parece a primeira vista, resulta. Pero para máis claridade, vexamos algúns exemplos prácticos de como pode usar a función SUMMESLIMN. Isto fará que sexa moito máis fácil afondar no tema.

Rango dinámico de suma de condicións

Entón, imos comezar co primeiro exemplo. Digamos que temos unha táboa que contén información sobre como afrontan os estudantes o currículo nunha determinada materia. Hai un conxunto de cualificacións, o rendemento avalíase nunha escala de 10 puntos. A tarefa consiste en buscar a nota do exame daqueles alumnos cuxo apelido comece pola letra A e a súa puntuación mínima é de 5.

A mesa ten este aspecto.

Para poder calcular a puntuación total en función dos criterios descritos anteriormente, debemos aplicar a seguinte fórmula.

Imos describir os argumentos con máis detalle:

1. C3:C14 é o noso rango de suma. No noso caso, coincide co rango de condicións. A partir del seleccionaranse os puntos que se empregarán para calcular a cantidade, pero só os que se enmarcan nos nosos criterios.
2. ">5" é a nosa primeira condición.
3. B3:B14 é o segundo intervalo de suma que se procesa para coincidir co segundo criterio. Vemos que non hai coincidencia co rango de suma. A partir diso, concluímos que o rango de suma e o rango da condición poden ser ou non idénticos. 
4. "A*" é o segundo rango, que especifica a selección de notas só para aqueles estudantes cuxo apelido comeza por A. No noso caso, un asterisco significa calquera número de caracteres. 

Despois dos cálculos, obtemos a seguinte táboa.

Como podes ver, a fórmula resumiu os valores en función do rango dinámico e en función das condicións especificadas polo usuario.

Suma selectiva por condición en Excel

Supoñamos agora que queremos obter información sobre os produtos que se enviaron a que países durante o último trimestre. Despois diso, atopa os ingresos totais dos envíos de xullo e agosto.

A mesa en si ten este aspecto. 

Para determinar o resultado final, necesitamos tal fórmula.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Como resultado dos cálculos realizados por esta fórmula, obtemos o seguinte resultado.

Atención! Esta fórmula parece bastante grande aínda que só utilizamos dous criterios. Se o intervalo de datos é o mesmo, pode reducir significativamente a lonxitude da fórmula, como se mostra a continuación.

Función SUMIFS para sumar valores en varias condicións

Agora imos dar outro exemplo para ilustralo. Neste caso, a táboa segue sendo a mesma que no caso anterior. 

Usamos a seguinte fórmula (pero escribimola como unha fórmula matricial, é dicir, introducímola a través da combinación de teclas CTRL + MAYÚS + ENTRAR).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Despois da función SUMMESLIMN sumará a matriz de valores en función dos criterios especificados na fórmula (é dicir, os países de China e Xeorxia), a matriz resultante sumarase coa función habitual SUMA, que se escribe como unha fórmula matricial.

Se as condicións se pasaron como unha constante de matriz para máis dun par, entón a fórmula dará un resultado incorrecto.

Agora vexamos a táboa que contén os totais.

Como vedes, conseguímolo. Seguramente tamén terás éxito. Gran éxito neste campo. Esta é unha función moi sinxela que pode entender unha persoa que acaba de poñer un pé no camiño da aprendizaxe de Excel. E xa sabemos que a función SUMMESLIMN permítelle ser eficaz en calquera campo de actividade, desde a contabilidade ata a educación. Aínda que esteas a construír unha carreira noutra área que non se describiu anteriormente, esta función aínda che axudará a gañar cartos. Por iso é valiosa.

O máis importante é que permítelle aforrar tempo, que é, por desgraza, un recurso limitado. Parece que hai un par de segundos para aplicar dúas funcións, pero cando tes que realizar un gran número de operacións repetitivas, estes segundos suman horas que se poderían dedicar a outra cousa. Polo tanto, recomendamos que practique o uso desta función. Ademais, é incriblemente sinxelo.