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

  1. Címkék keresése
lopakodás »2011. június 11. Dmitry 243582 megtekintés

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

A cikk segített? Ossza meg a kapcsolatot a barátaival! Videó oktatóanyagok

{"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

hozzáférés almaóra Multex kilátás Power Query és Power BI A VBA a szerkesztőben dolgozik VBA kódkezelés Ingyenes bővítmények Dátum és idő Diagramok és grafikonok papírok Adatvédelem Az internet Képek és objektumok Lapok és könyvek Makrók és VBA Bővítmények beállítás nyomtatás Keresési adatok Adatvédelmi irányelvek levél programok Alkalmazásokkal való munka Fájlok használata Alkalmazásfejlesztés Összefoglaló táblázatok listák Képzések és webináriumok pénzügyi formázás Formulák és funkciók Excel funkciók VBA funkciók Sejtek és tartományok Multex részvények adatelemzés hibák és hibák az Excelben referenciák A "*" és "?
Quot;?
Quot; - csak egy karaktert helyettesít, azaz a "mas?
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?