A SUMMESLE, valamint a SUMMESLES funkciója két kritérium szerint

1. Címkék keresése

Képzeld el egy táblázatot, amelyben a szervezeti egységek (vagy számlák, vagy valami más) nevét sorokban soroljuk fel.

A cellák összege a kritérium szerint
Szükséges az egyes osztályok teljes összegének kiszámítása. Sokan ezt egy szűrővel és tollakkal írják a sejtekben.
Bár egyszerűen és egyszerűen csak egy funkcióval - SUMMESLI- val lehet elvégezni.
SUMMESLES (SUMIF) - összegyűjti az adott feltételnek megfelelő cellákat (csak egy feltételt lehet megadni). Ez a funkció akkor is használható, ha a táblát oszlopokra osztjuk, időszakonként (havonta, havonta, három oszlopban - jövedelem | kiadás | különbség), és minden időszakra csak a jövedelem, költség és különbség alapján kell kiszámítani a teljes összeget.

Összesen három érv van a SUMMESLI: Range , Criterion , Range_Summing számára .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Tartomány (A1: A20000) - a tartományt jelöli a kritériumokkal. Ie Az a oszlop, amelyen a Criterion argumentum által jelzett értéket kell keresni.
• Az (A1) kritérium az érték (szöveg vagy szám, valamint a dátum), amelyet a tartományban kell megtalálni. A "*" és "?" Ie meghatározza a "* tömeg *" kritériumot az "érték" szó összegzéséhez. Ugyanakkor a „tömeg” szó akár a szövegben is előfordulhat, vagy csak egy szó lehet a cellában. A "tömeg *" megadásával minden "tömeggel" kezdődő értéket összegezünk. "?" - csak egy karaktert helyettesít, azaz a "mas? a" megadásával összegezheti a "tömeg" és a "maszk" értékét stb.
  Ha a kritérium egy cellában van írva, és még mindig helyettesítő karaktereket kell használnia, akkor a szükséges link hozzáadásával hivatkozhat erre a cellára. Tegyük fel, hogy össze kell foglalnunk az „összes” szót tartalmazó értékeket. Az "összes" szó az A1 cellában van írva, míg az A oszlopban különböző helyesírási értékek találhatók, amelyek a "teljes" szót tartalmazzák: "júniusi összesítések", "júliusi összesítések", "márciusi összesítések". A képlet ekkor így néz ki:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - a & jel (ampersand) több értéket egyesít. Ie az eredmény "* eredmény *" lesz.
  Ahhoz, hogy jobban megértsük a képletek működésének elvét, jobb a Formula kiszámítása eszköz használata: A képletek kiszámításának lépéseinek megtekintése
  A logikai és matematikai jelekkel ellátott összes szöveges kritériumot és kritériumot idézőjelekbe kell foglalni (= SUMMESLI (A1: A20000; "összesen"; B1: B20000)). Ha a kritérium szám, akkor az idézetek nem szükségesek. Ha egy kérdőjelet vagy csillagot szeretne közvetlenül találni, tilde (~) kell elhelyeznie.
  A tilde-ről és annak tulajdonságairól ebben a cikkben olvashat: Csillagok cseréje / eltávolítása / keresése?
• Sum_Range (B1: B20000) (opcionális argumentum) - megadja az összegezni kívánt összegek vagy számértékek tartományát.

Hogyan működik: a függvény a Kritérium argumentum által megadott értéket keresi a tartományon , és ha találatot talál, összegzi a Range_Amount argumentum által jelzett adatokat. Ie ha van egy osztály neve az A oszlopban, és egy összeg a B oszlopban, akkor a Fejlesztési Osztály megadása a kritériumként a B oszlop összes értékének összegét fogja eredményezni, szemben azzal, amit a Fejlesztési Osztály az A. oszlopban talál. Valójában a SumArrangement nem lehet ugyanolyan méretű, mint a Range argumentum, és ez nem okoz hibát a függvényben. Azonban az összegzéshez szükséges cellák meghatározásakor a Range_Amount argumentum bal felső celláját használjuk kiindulási cellaként az összegzéshez, majd a mérettől és alaktól a Range argumentumhoz tartozó cellák összeadódnak.

Néhány funkció
A függvény utolsó argumentuma (Sum_And_Band: B1: B20000) opcionális. Ez azt jelenti, hogy nem határozható meg. Ha nem határozza meg, a függvény hozzáadja a Range argumentum által megadott értékeket. Mi az. Például csak a nullánál nagyobb számok összegét kell kapnia. Az összeg A oszlopában. Ezután a funkció így néz ki:
= SUMMERS (A1: A20000; "> 0")

Mit kell figyelembe venni: a range_summing és a tartománynak egyenlőnek kell lennie a vonalak számában. Ellenkező esetben rossz eredményt kaphat. Optimális esetben, ha úgy néz ki, mint az általam megadott képletekben: az összegzések tartománya és tartománya egy sorból indul, és ugyanolyan sorszámú: A1: A20000; B1: B20000

Két vagy több kritérium összevonása
De mit kell tennünk, ha a 2-es és annál több összegző kritériumokat? Tegyük fel, hogy csak azokat az összegeket kell összegeznie, amelyek egy osztályhoz tartoznak, és csak egy bizonyos időpontig. A 2007-es és újabb irodai verziók boldog tulajdonosai használhatják a SUMMESLIMN funkciót:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Az első argumentum meghatározza a cellákat, amelyek az összegyűjtött összegeket tartalmazzák.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Megadja azt a cellatartományt, amelyben kritérium szerint keresni kíván egy mérkőzést.
$ I $ 3, $ H8 - kritérium. Itt, mint a SUMMESLI-ben, a helyettesítő karakterek * és ? és ugyanúgy működnek.

Az érvek megadásának sajátosságai: először a kritériumtartomány meg van adva (számozott), majd az érték (kritérium) közvetlenül pontosvesszőben van feltüntetve, amely ebben a tartományban - $ A $ 2: $ A $ 50; $ I $ 3. És semmi más. Ne próbálja meg először megadni az összes tartományt, majd a kritériumokat - a függvény hibákat fog adni, vagy nem fogja összefoglalni a szükséges adatokat.

Minden feltétel összehasonlítása az I. elv szerint történik. Ez azt jelenti, hogy ha az összes felsorolt ​​feltétel teljesül. Ha legalább egy feltétel nem teljesül, a függvény átugorja a sort, és nem ad hozzá semmit.
Ami a SUMMERS-t illeti, az összegzési és kritériumtartományoknak meg kell egyezniük a sorok számával.

mert A SUMMESLIMN csak az Excel verzióiban jelent meg, 2007-től kezdődően, akkor hogyan lehet ilyen esetekben a korábbi verziók boldogtalan felhasználói? Nagyon egyszerű: használjon egy másik funkciót - SUMPRODUCT. Nem fogom festeni az érveket, mert Sokan vannak, és ezek az értékek tömbjei. Ez a függvény szorozza meg az argumentumok által jelzett tömböket. Megpróbálom leírni azt az általános elvet, hogy ezt a funkciót több feltételre vonatkozó adatok összegzésére használjuk.
Az összegzési probléma több kritérium alapján történő megoldásához a funkció így néz ki:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5), $ C $ 2: $ C $ 50
$ A $ 2: $ A $ 50 - dátumtartomány. A $ I $ 3 az a kritérium dátuma, amelyre szükség van az adatok összegzésére.
$ B $ 2: $ B $ 50 - a szervezeti egységek neve. H5 - a tanszék neve, az adatok összegzése.
$ C $ 2: $ C $ 50 - tartomány összegekkel.

Elemezzük a logikát, mert sokak számára teljesen tisztázatlan lesz, ha ezt a funkciót nézzük. Ha csak azért, mert az alkalmazás nem írja le az alkalmazást. A jobb olvashatóság érdekében csökkentse a tartományok méretét:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Tehát a kifejezés ($ A $ 2: $ A $ 5 = $ I $ 3) és ($ B $ 2: $ B $ 5 = H5) logikai FALSE és TRUE logikai és visszatérő tömbök. TRUE, ha a $ A $ 2: $ A tartományban lévő cellának meg kell egyeznie a $ I $ 3 cellának és a $ B $ 2: $ 5 értékű cellának az értéke a H5 cellának. Ie van a következő:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Amint láthatod, az első tömbben két illeszkedés van a feltételhez, és a másodikban. Ezenkívül ezeket a két tömböt megszorozzuk (ennek a szorzójelnek (*) van felelős). Szaporodás esetén a FALSE és TRUE tömbök implicit átalakítása a 0 és 1 numerikus konstansokra, illetve ({0; 1; 1; 0} * {0; 0; 1; 0}). Mint tudjuk, nullával szorozva nulla értéket kapunk. És az eredmény egy tömb:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ 50)
Ezután a {0; 0; 1; 0} tömböt a $ C $ 2: $ C $ 50 tartományban lévő számok tömbjével megszorozzuk.
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Ennek eredményeként 30-at kapunk. Amire szükségünk van - csak a kritériumnak megfelelő összeget kapjuk. Ha több mint egy összeg felel meg a kritériumnak, akkor azokat összegezzük.

A SUMMYROIZV előnye
Ha az érvek a szorzójel helyett pluszjelet tartalmaznak:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
akkor a feltételeket az OR elv szerint hasonlítjuk össze: azaz az összes összeg akkor kerül összegzésre, ha legalább egy feltétel teljesül: vagy $ A $ 2: $ A $ 5 a $ I $ 3 vagy a $ B $ 2 tartományban lévő cellaszámmal egyenlő: $ B $ 5 egyenlő a H5 sejtértékkel.
Ez a SUMMRODUCT előnye a SUMMESLIMN-en. A SUMMESLIMN nem tudja összegezni az OR elv szerinti értékeket, csak az AND-elv szerint (minden feltételnek teljesülnie kell).

hiányosságokat
A SUMPRODUCT nem használható * és? Lehetséges, hogy pontosabban használhatók, de nem különleges karakterekként fognak érzékelni, hanem csillagként és kérdőjelként. Azt hiszem, ez jelentős hátrány. És bár ez megkerülhető, más funkciókat is használok a SUMPRODUCT-on belül - még mindig nagyszerű lenne, ha a függvény valamilyen módon helyettesítő karaktereket használna.

A példában néhány példát találsz a fent leírtak jobb megértéséhez.

Töltsön le egy példát

Összeg több kritérium szerint (41,5 KiB, 10 477 letöltés)

Lásd még:
A sejtek összeadásával töltött szín
A sejtek összegzése betűszín szerint
A sejtek cellamódosítása
Számolja ki a töltet színét a sejtek mennyiségével
Számolja ki a sejtek számát betűszín szerint
Hogyan összegezhetünk adatokat több lapról, beleértve a feltételeket is

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": igaz, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" dia "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" bal "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": hamis, "texteffect1": "dia", "texteffectslidedirection1": "jobb", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "texteffectduration1": 600 "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" kijelző: blokk, párnázás: 12px; szöveg-igazítás: balra; "," textbgcss ":" kijelző: blokk; pozíció: abszolút; felső: 0px; bal: 0px; szélesség: 100%; magasság: 100% háttérszín: # 333333, opacitás: 0,6, szűrő: a lpha (opacitás = 60); "," titlecss ":" kijelző: blokk; pozíció: relatív; betűtípus: vastag 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; szín: #fff; "," descriptioncss ":" kijelző: blokk; pozíció: relatív; betűtípus: 12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; szín: #fff; margin-top: 8px; "," buttoncss ":" kijelző: blokk; pozíció: relatív; margin-top: 8px; "," texteffectresponsive ": igaz," texteffectesponsivesize ": 640," titlecssrespiveiv ":" font-size: 12px; "," descriptioncssresponsive ":" kijelző: nincs: fontos; "," buttoncssresponsive ": "", "addgooglefonts": hamis, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Címkék keresése