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
- Számolja a cellákat több VAGY feltétellel az Excelben
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:
- 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:
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:
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ő:
- 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:
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:
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:
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ámítsa ki a két vagy több kritériumnak megfelelő cellákat
- Az Excelben a cellák számának két vagy több feltétel alapján történő számításához a COUNTIFS funkció segíthet ennek a feladatnak a gyors és egyszerű megoldásában.
- 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
A Kutools for Excel több mint 300 funkcióval büszkélkedhet, Győződjön meg arról, hogy amire szüksége van, csak egy kattintásnyira van...
Office lap – A lapos olvasás és szerkesztés engedélyezése a Microsoft Office programban (beleértve az Excelt 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 Excelt is), akárcsak a Chrome, az Edge és a Firefox.
