Funció SUMIF a Excel i suma per múltiples condicions

Excel és un programa increïblement funcional. Fins i tot el conjunt de funcions integrat és suficient per completar gairebé qualsevol tasca. I a més dels estàndards, coneguts per a molts, també n'hi ha dels que poca gent ha sentit a parlar. Però al mateix temps, no deixen de ser útils. Tenen una especialització més estreta, i no sempre n'hi ha necessitat. Però si els coneixeu, en un moment crític poden ser molt útils.

Avui parlarem d'una d'aquestes funcions − SUMMESLIMN.

Si l'usuari s'enfronta a la tasca de resumir diversos valors, centrant-se en determinats criteris, cal utilitzar la funció SUMMESLIMN. La fórmula que utilitza aquesta funció pren aquestes condicions com a arguments, després suma els valors que les compleixen i, a continuació, s'introdueix el valor trobat a la cel·la on està escrit. 

Descripció detallada de la funció SUMIFS

Abans de considerar la funció SUMMESLIMN, primer heu d'entendre quina és una versió més senzilla d'això - SUMMESLI, ja que és en ell on es basa la funció que estem considerant. Cadascun de nosaltres probablement ja està familiaritzat amb dues funcions que s'utilitzen sovint: SUM (realitza la suma de valors) i SI (prova un valor amb una condició especificada).

Si les combineu, obtindreu una altra funció: SUMMESLI, que compara les dades amb els criteris especificats per l'usuari i suma només els números que compleixen aquests criteris. Si parlem de la versió anglesa d'Excel, aquesta funció s'anomena SUMIF. En paraules senzilles, el nom -language és una traducció directa del nom en anglès. Aquesta funció es pot utilitzar per a diversos propòsits. En particular, es pot utilitzar com a alternativa VPR, és a dir, anotar

La principal diferència entre la funció SUMMESLIMN  de la funció habitual SUMMESLI és que s'utilitzen diversos criteris. La seva sintaxi és bastant complexa a primera vista, però després d'un examen més atent, resulta que la lògica d'aquesta funció és molt senzilla. Primer cal seleccionar l'interval on es comprovaran les dades i, a continuació, establir les condicions de compliment amb què es realitzarà l'anàlisi. I aquesta operació es pot realitzar per a un nombre bastant gran de condicions.

La sintaxi en si és:

SUMIFS(interval_suma, rang_condició1, condició1, [interval_condició2, condició2], …)

En llocs adequats cal posar matrius de cel·les adequades en un cas concret. 

Vegem els arguments amb més detall:

1. Interval_sum. Aquest argument, així com el rang de condició 1 i condició 1, és necessari. És un conjunt de cel·les que cal sumar.
2. Interval_condició1. Aquest és l'interval on es comprovarà la condició. Es combina amb el següent argument: Condició1. La suma dels valors corresponents al criteri es realitza dins de les cel·les especificades a l'argument anterior.
3. Condició 1. Aquest argument especifica els criteris amb els quals s'ha de comprovar. Es pot configurar, per exemple, d'aquesta manera: “> 32”.
4. Interval de condicions 2, Condició 2... Aquí, les condicions següents s'estableixen de la mateixa manera. Si cal especificar més d'unes poques condicions, s'afegeixen els arguments Interval de condicions 3 i Condició 3. La sintaxi és la mateixa per als arguments següents.

La funció permet el processament màxim de fins a 127 parells de condicions i rangs. 

Podeu utilitzar-lo en diverses àrees alhora (en donarem només algunes, la llista és encara més llarga):

1. Comptabilitat. Per exemple, és bo utilitzar la funció SUMMESLIMN per crear informes de resum, per trimestre, per a despeses superiors a una quantitat determinada, per exemple. O creeu un informe sobre un producte d'una categoria de preu determinada.
2. Gestió de vendes. Aquí també la funció pot ser molt útil. Per exemple, ens enfrontem a la tasca de sumar només el cost de les mercaderies que es van vendre a un determinat client en un moment determinat. I en aquesta situació, la funció SUMMESLIMN pot ser molt útil.
3. Educació. Avui donarem exemples més pràctics d'aquesta àrea. En particular, podeu utilitzar-lo per obtenir un resum de les qualificacions dels estudiants. Podeu triar per a una sola assignatura o per a notes individuals. Una persona pot establir immediatament diversos criteris pels quals es seleccionarà l'avaluació, cosa que és molt convenient i pot estalviar molt de temps.

Com podeu veure, el ventall d'aplicacions d'aquesta funció és molt ampli. Però aquest no és el seu únic mèrit. Fem una ullada a alguns avantatges més que té aquesta funció:

1. Capacitat per establir múltiples criteris. Per què això és un avantatge? Podeu utilitzar la funció normal SUMMESLI! I tot perquè és convenient. No cal fer càlculs separats per a cadascun dels criteris. Totes les accions es poden programar amb antelació, fins i tot abans. com es formarà la taula de dades. Això és un gran estalvi de temps.
2. Automatització. L'edat moderna és l'era de l'automatització. Només la persona que sap automatitzar correctament el seu treball pot guanyar molt. Per això la capacitat de dominar Excel i la funció SUMMESLIMN en particular, és tan important per a qualsevol persona que vulgui construir una carrera. Conèixer una funció us permet realitzar diverses accions alhora, com una. I aquí passem al següent benefici d'aquesta funció.
3. Estalvi de temps. Només pel fet que una funció realitza diverses tasques alhora.
4. Senzillesa. Malgrat que la sintaxi és força pesada a primera vista a causa del seu volum, de fet, la lògica d'aquesta funció és molt senzilla. En primer lloc, es selecciona un rang de dades, després un rang de valors, que es comprovarà que compleixen una determinada condició. I, per descomptat, també s'ha d'especificar la condició en si. I així diverses vegades. De fet, aquesta funció es basa només en una construcció lògica, cosa que la fa més senzilla que la coneguda VPR malgrat que es pot utilitzar amb les mateixes finalitats, tenint també en compte un major nombre de criteris. 

Característiques de l'ús de la funció SUMIFS

Hi ha diverses característiques de l'ús d'aquesta funció a les quals cal prestar atenció. En primer lloc, aquesta funció ignora els intervals amb cadenes de text o nuls, ja que aquests tipus de dades no es poden sumar en un patró aritmètic, només concatenats com cadenes. Aquesta funció no pot fer-ho. També heu de parar atenció a les condicions següents:

1. Podeu utilitzar aquests tipus de valors com a condicions per seleccionar cel·les per afegir més els valors continguts en elles: valors numèrics, expressions booleanes, referències de cel·les, etc. 
2. Si s'estan comprovant text, expressions lògiques o signes matemàtics, aquests criteris s'especifiquen entre cometes.
3. No es poden utilitzar termes de més de 255 caràcters.
4. És possible utilitzar criteris aproximats per seleccionar valors amb comodins. El signe d'interrogació s'utilitza per substituir un sol caràcter, i el signe de multiplicació (asterisc) és necessari per substituir diversos caràcters. 
5. Els valors booleans que es troben en l'interval de suma es converteixen automàticament en valors numèrics segons el seu tipus. Així, el valor "VERTADER" es converteix en un i "FALSE" - en zero. 
6. Si un #VALOR! l'error apareix en una cel·la, vol dir que el nombre de cel·les en els intervals de condició i de suma és diferent. Heu d'assegurar-vos que les mides d'aquests arguments són les mateixes. 

Exemples d'ús de la funció SUMIFS

function SUMMESLIMN resulta que no és tan complicat com sembla a primera vista. Però per a més claredat, mirem alguns exemples pràctics de com podeu utilitzar la funció SUMMESLIMN. Això farà que sigui molt més fàcil aprofundir en el tema.

Interval dinàmic de suma de condicions

Per tant, comencem pel primer exemple. Suposem que tenim una taula que conté informació sobre com s'enfronten els estudiants al currículum d'una assignatura concreta. Hi ha un conjunt de qualificacions, el rendiment s'avalua en una escala de 10 punts. La tasca consisteix a trobar la nota de l'examen d'aquells alumnes el cognom dels quals comenci per la lletra A, i la seva puntuació mínima és de 5.

La taula es veu així.

Perquè puguem calcular la puntuació total a partir dels criteris descrits anteriorment, hem d'aplicar la fórmula següent.

Anem a descriure els arguments amb més detall:

1. C3:C14 és el nostre rang de suma. En el nostre cas, coincideix amb el rang de condicions. A partir d'ella es seleccionaran els punts utilitzats per calcular l'import, però només els que entren sota els nostres criteris.
2. ">5" és la nostra primera condició.
3. B3:B14 és el segon interval de suma que es processa per coincidir amb el segon criteri. Veiem que no hi ha coincidència amb el rang de suma. D'això concloem que el rang de suma i el rang de la condició poden ser o no idèntics. 
4. “A*” és el segon rang, que especifica la selecció de notes només per a aquells estudiants el cognom dels quals comença per A. En el nostre cas, un asterisc significa qualsevol nombre de caràcters. 

Després dels càlculs, obtenim la següent taula.

Com podeu veure, la fórmula resumia els valors en funció del rang dinàmic i en funció de les condicions especificades per l'usuari.

Suma selectiva per condició en Excel

Ara suposem que volem obtenir informació sobre quines mercaderies es van enviar a quins països durant l'últim trimestre. Després d'això, cerqueu els ingressos totals dels enviaments de juliol i agost.

La taula en si es veu així. 

Per determinar el resultat final, necessitem aquesta fórmula.

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

Com a resultat dels càlculs realitzats per aquesta fórmula, obtenim el següent resultat.

Atenció! Aquesta fórmula sembla bastant gran tot i que només hem utilitzat dos criteris. Si l'interval de dades és el mateix, podeu reduir significativament la longitud de la fórmula, tal com es mostra a continuació.

Funció SUMIFS per sumar valors en múltiples condicions

Ara donem un altre exemple per il·lustrar. En aquest cas, la taula segueix sent la mateixa que en el cas anterior. 

Utilitzem la fórmula següent (però l'escrivim com una fórmula matricial, és a dir, l'introduïm mitjançant la combinació de tecles CTRL + MAJÚS + INTRO).

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

Després de la funció SUMMESLIMN sumarà la matriu de valors segons els criteris especificats a la fórmula (és a dir, els països de la Xina i Geòrgia), la matriu resultant es suma amb la funció habitual SUMA, que s'escriu com una fórmula matricial.

Si les condicions es van passar com a constant de matriu per a més d'un parell, la fórmula donarà un resultat incorrecte.

Ara fem una ullada a la taula que conté els totals.

Com podeu veure, ho hem aconseguit. Segur que també ho aconseguiràs. Gran èxit en aquest camp. Aquesta és una funció molt senzilla que una persona que acaba de posar un peu en el camí d'aprendre Excel pot entendre. I ja sabem que la funció SUMMESLIMN et permet ser eficaç en qualsevol àmbit d'activitat, des de la comptabilitat fins a l'educació. Fins i tot si esteu construint una carrera en qualsevol altra àrea que no s'hagi descrit anteriorment, aquesta característica us ajudarà a guanyar diners. Per això és valuosa.

El més important és que us permet estalviar temps, que és, malauradament, un recurs limitat. Sembla que hi ha un parell de segons per aplicar dues funcions, però quan heu de realitzar un gran nombre d'operacions repetitives, aquests segons sumen hores que es podrien dedicar a una altra cosa. Per tant, us recomanem que practiqueu amb aquesta funció. A més, és increïblement senzill.