Note: The other languages of the website are Google-translated. Back to English

Egyezik a VAG logikával az Excel több kritériuma esetén

Általában a COUNTIFS függvénnyel számolhatja a cellákat egy vagy több feltétel alapján az ÉS logika segítségével az Excelben. Volt már olyan helyzetben, amikor egynél több értéket kell számlálnia egyetlen oszlopból vagy cellatartományból? Ez azt jelenti, hogy több feltétellel és VAGY logikával kell számolni. Ebben az esetben a SUM és a COUNTIFS függvényeket együtt is alkalmazhatja, vagy használhatja a SUMPRODUCT funkciót.


Számolja a sejteket VAGY feltételekkel az Excelben

Például számos adatom van, mint az alábbi képernyőképen látható, most szeretném megszámolni a „Ceruza” vagy „Vonalzó” termék számát, itt két képlettel foglalkozom ennek a feladatnak az Excelben történő megoldására.

Számolja a VAGY feltételekkel rendelkező cellákat a SUM és a COUNTIFS függvények használatával

Az Excelben több VAGY feltétellel történő számoláshoz használhatja a SUM és COUNTIFS függvényeket tömbállandóval, az általános szintaxis:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Az adattartomány tartalmazza azokat a kritériumokat, ahonnan a cellákat számolja;
  • criterion1, criterion2, criterion3…: Azok a feltételek, amelyek alapján számolni szeretné a cellákat.

A „Ceruza” vagy „Vonalzó” termékek számolásához másolja vagy írja be az alábbi képletet egy üres cellába, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


A képlet magyarázata:

= SUM (COUNTIFS (B2: B13, {"Ceruza", "Vonalzó"})))

  • {"Ceruza", "Vonalzó"}: Először az összes feltételt egy ilyen tömbállandóba kell csomagolnia: {"Ceruza", "Vonalzó"}, válassza el az elemeket vesszővel.
  • SZÁMOK (B2: B13, {"Ceruza", "Vonalzó"}): Ez a COUNTIFS függvény egyedi számokat kap a „Ceruza” és a „Vonalzó” számára, és az eredmény a következő lesz: {2,3}.
  • SUM (COUNTIFS (B2: B13, {"Ceruza", "Vonalzó"}))) = SUM ({2,3}): Végül ez a SUM függvény összesíti a tömb összes elemét, és visszaadja az eredményt: 5.

tippek: Feltételekhez cellahivatkozásokat is használhat, kérjük, alkalmazza az alábbi tömbképletet, majd nyomja meg a gombot Ctrl + Shift + Enter kulcsok együtt a helyes eredmény eléréséhez:

=SUM(COUNTIF(B2:B13,D2:D3))


Számolja meg a VAGY feltételekkel rendelkező cellákat a SUMPRODUCT függvénnyel

Itt van egy másik képlet, amelyet a SUMPRODUCT függvény hoz létre, és amely segíthet a VAG logikával rendelkező cellák számolásában is. Az általános szintaxis a következő:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Az adattartomány tartalmazza azokat a kritériumokat, ahonnan a cellákat számolja;
  • criterion1, criterion2, criterion3…: Azok a feltételek, amelyek alapján számolni szeretné a cellákat.

Másolja vagy írja be a következő képletet egy üres cellába, majd nyomja meg a gombot belép kulcs az eredmény visszaadásához:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


A képlet magyarázata:

= SUMPRODUCT (1*(B2: B13 = {"Ceruza", "Vonalzó"}))

  • B2: B13 = {"Ceruza", "Vonalzó"}: Ez a kifejezés összehasonlítja a „Ceruza”, „Vonalzó” minden feltételt a B2: B13 tartományú cellával. Ha a feltétel teljesül, akkor IGAZ értéket ad vissza, ellenkező esetben HAMIS jelenik meg, így kapja az eredményt: {IGAZ, HAMIS; HAMIS, HAMIS; HAMIS, HAMIS; HAMIS, IGAZ; HAMIS, HAMIS; IGAZ, HAMIS ; HAMIS, HAMIS; HAMIS, IGAZ; HAMIS, HAMIS; HAMIS, HAMIS; HAMIS, IGAZ; HAMIS, HAMIS}.
  • 1*(B2: B13 = {"Ceruza", "Vonalzó"}): A szorzás a TRUE és FALSE logikai értékeket 1 -re és 0 -ra konvertálja, így a következő eredményt kapja: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
  • SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Végül a SUMPRODUCT függvény összeadja a tömb összes számát, hogy megkapja az eredményt: 5.

Számolja a cellákat több VAGY feltétellel az Excelben

Néha előfordulhat, hogy a cellákat két vagy több VAGY feltételkészlettel kell számolni, ebben az esetben használhatja a SUM és a COUNTIFS tömbállandóval, vagy a SUMPRODUCT funkciót ISNUMBER MATCH függvényekkel.

Számolja a cellákat két VAGY feltétellel a SUM és a COUNTIFS függvények használatával

Ha csak két VAGY feltételcsoportot szeretne kezelni, akkor csak hozzá kell adnia egy másik tömbállandót a COUNTIFS képlethez.

Például az adattartományom az alábbi képernyőképen látható, most szeretném számolni azokat az embereket, akik „ceruzát” vagy „vonalzót” rendeltek, és az összeg <100 vagy> 200.

Kérjük, írja be vagy másolja a következő képletet egy cellába, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez, lásd a képernyőképet:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

Megjegyzések: A képletben pontosvesszőt kell használni a második tömbállandóhoz, amely függőleges tömböt hoz létre.


Számolja a cellákat több VAGY feltétellel a SUMPRODUCT függvény használatával

A fenti képlet csak két VAGY feltételcsoportra vonatkozik, ha több feltétellel kell számolnia, akkor egy összetett SUMPRODUCT képlet az ISNUMBER MATCH függvényekkel együtt segíthet.

Vegye például az alábbi adatokat, ha össze szeretné számolni azokat az embereket, akik "ceruzát" vagy "vonalzót" rendeltek, és az állapot "kézbesítve" vagy "szállítás alatt", és "Bob" vagy "Eko" aláírta, akkor komplex képletet kell alkalmaznia .

Másolja vagy írja be az alábbi képletet egy üres cellába, majd nyomja meg a gombot belép kulcs a számításhoz, lásd a képernyőképet:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


A képlet magyarázata:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

SZÁM (METCH (B2: B13, {"Ceruza", "Vonalzó"}, 0)):

  • MATCH (B2: B13, {"Ceruza", "Vonalzó"}, 0): Ez a MATCH függvény a B2: B13 tartomány minden cellájának összehasonlítására szolgál a megfelelő tömbállandóval. Ha az egyezés megtalálható, akkor az érték relatív pozícióját adja vissza a tömbben, ellenkező esetben hibaérték jelenik meg. Tehát a tömblistát így kapja: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# N/A}.
  • ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Az ISNUMBER függvény a számokat IGAZRA, a hibaértékeket HAMIS értékre alakítja át, így: {IGAZ; HAMIS; HAMIS; IGAZ; HAMIS; IGAZ; HAMIS; IGAZ; IGAZ; HAMIS; IGAZ; HAMIS}.

A fenti logika alkalmazható a második és harmadik ISNUMBER kifejezésre is.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):

  • Ezután ezt a három tömböt megszorozzuk a SUMPRODUCT -ban, amely automatikusan átalakítja az IGAZ és HAMIS értékeket 1 -re és 0 -ra a matematikai művelet részeként: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} *{1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} *{1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}) = ÖSSZEFOGLALÓ ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; XNUMX}).
  • Végül a SUMPRODUCT függvény összesíti a tömb összes számát, hogy megkapja az eredményt: 3.

Használt relatív függvény:

  • ÖSSZEG:
  • Az Excel SUM függvény a megadott értékek összegét adja vissza.
  • COUNTIF:
  • A COUNTIF függvény az Excel statisztikai függvénye, amely a kritériumnak megfelelő cellák számának felhasználására szolgál.
  • ÖSSZEFOGLALÓ:
  • A SUMPRODUCT funkcióval két vagy több oszlop vagy tömb összeszorozható, majd megkapjuk a szorzat összegét.
  • SZÁM:
  • Az Excel ISNUMBER függvény IGAZ értéket ad vissza, ha egy cella számot tartalmaz, és hamis, ha nem.
  • MÉRKŐZÉS:
  • A Microsoft Excel MATCH függvény egy adott értéket keres egy cellatartományban, és visszaadja ennek az értéknek a relatív helyzetét.

További cikkek:

  • Számoljon egyedi számértékeket kritériumok alapján
  • Az Excel munkalapon problémát tapasztalhat, amely egy adott feltétel alapján számolja az egyedi számértékek számát. Például hogyan tudom megszámolni a „póló” termék egyedi mennyiségi értékeit a jelentésből az alábbi képernyőkép alatt? Ebben a cikkben bemutatok néhány képletet ennek a feladatnak az Excelben történő eléréséhez.
  • Számos VAGY feltétellel rendelkező sorok száma
  • Ha több kritériummal rendelkező sorok számát szeretné számolni különböző oszlopokban, VAGY logikával, a SUMPRODUCT funkció segíthet. Például van egy termékjelentésem az alábbi képernyőképen, most szeretném számolni azokat a sorokat, ahol a termék „póló” vagy a szín „fekete”. Hogyan kell kezelni ezt a feladatot Excelben?

A legjobb irodai termelékenységi eszközök

Kutools for Excel - segít kitűnni a tömegből

Szeretnéd gyorsan és tökéletesen elvégezni mindennapi munkádat? A Kutools for Excel 300 hatékony speciális funkciót kínál (munkafüzetek összevonása, színenkénti összegzés, cellatartalom felosztása, dátum konvertálása stb.), és 80%-os időt takaríthat meg Önnek.

  • 1500 munkaforgatókönyvhöz tervezve, segít megoldani az Excel problémák 80%-át.
  • Csökkentse a napi billentyűzet és egér kattintások ezreit, enyhítse fáradt szemét és kezét.
  • Legyen Excel szakértő 3 perc alatt. Nem kell többé emlékezni semmilyen fájdalmas képletre és VBA kódra.
  • 30 napos korlátlan ingyenes próbaidőszak. 60 napos pénzvisszafizetési garancia. Ingyenes frissítés és támogatás 2 évre.
Az Excel szalagja (telepítve a Kutools for Excel programmal)

Office fül - Füles olvasás és szerkesztés engedélyezése a Microsoft Office alkalmazásban (beleértve az Excel programot is)

  • Egy másodperc a tucatnyi nyitott dokumentum közötti váltáshoz!
  • Csökkentse az egérkattintások százait az Ön számára minden nap, búcsút mondjon az egér kezének.
  • 50% -kal növeli a termelékenységet több dokumentum megtekintésekor és szerkesztésekor.
  • Hatékony lapokat hoz az Office-ba (beleértve az Excel-t is), akárcsak a Chrome-ot, a Firefox-ot és az új Internet Explorert.
Az Excel képernyőképe (az Office Tab telepítve)
A megjegyzések rendezése szerint
Hozzászólások (0)
Még nincs értékelés. Legyen Ön az első, aki értékel!
Még senki sem írt megjegyzést
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi