Számolja meg a hiányzó értékeket
Ha két listája van, akkor az egyik listában lévő azon értékek teljes számának megszámlálásához, amelyek egy másik listában nem léteznek, használhat egy SUMPRODUCT képlet segítségével MÉRKŐZÉS és ISNA függvények, vagy a COUNTIF funkciót.
Számolja meg a hiányzó értékeket a SUMPRODUCT, MATCH és ISNA paraméterekkel
Számolja meg a hiányzó értékeket a SUMPRODUCT és a COUNTIF paraméterekkel
Számolja meg a hiányzó értékeket a SUMPRODUCT, MATCH és ISNA paraméterekkel
Számolni a azon értékek teljes száma a B listában, amelyek hiányoznak az A listából amint fentebb látható, először a MATCH függvénnyel visszaadhatja az A listában lévő B listából származó értékek relatív helyzetét tartalmazó tömböt. Ha az A listában nem létezik érték, akkor #N/A hibaüzenet jelenik meg. Az ISNA függvény ezután azonosítja a #N/A hibákat, és a SUMPRODUCT megszámolja a hibák teljes számát.
Általános szintaxis
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- range_to_count: Az a tartomány, amelyből a hiányzó értékeket meg kell számolni. Itt a B listára hivatkozunk.
- lookup_range: Az összehasonlítandó tartomány a tartomány_szám. Itt az A listára hivatkozunk.
- 0: A match_type 0 arra kényszeríti a MATCH-t, hogy pontos egyezést hajtson végre.
Számolni a azon értékek teljes száma a B listában, amelyek hiányoznak az A listából, másolja vagy írja be az alábbi képletet a H6 cellába, majd nyomja meg a gombot belép az eredmény eléréséhez:
=ÖSSZEG(--ISNA(MATCH(F6: F8,B6: B10, 0)))
A képlet magyarázata
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- EGYEZÉS(F6:F8;B6:B10,0;XNUMX): A match_type 0 arra kényszeríti a MATCH függvényt, hogy olyan numerikus értékeket adjon vissza, amelyek a cellákon keresztül jelzik az értékek egymáshoz viszonyított helyzetét F6 nak nek F8 határon belül B6: B10. Ha egy érték nem létezik az A listában, akkor #N/A hibaüzenet jelenik meg. Tehát az eredmények egy ilyen tömbben lesznek: {2;3;#N/A}.
- ISNA(EGYEZÉS(F6:F8;B6:B10,0;XNUMX)) = ISNA({2;3;#N/A}): Az ISNA azon dolgozik, hogy kiderítse, hogy egy érték „#N/A” hiba-e vagy sem. Ha igen, a függvény egy TURE-t ad vissza; Ha nem, akkor HAMIS értéket ad vissza. Tehát az ISNA képlet visszatér {FALSE;FALSE;TRUE}.
- SUMPRODUCT(--ISNA(EGYEZÉS(F6:F8;B6:B10,0;XNUMX))) = ÖSSZEG(--{FALSE;FALSE;TRUE}): A kettős negatív előjel az IGAZ-okat 1-ekké, a HAMIS-okat pedig 0-kká konvertálja: {0; 1; 0}. Ezután a SUMPRODUCT függvény az összeget adja vissza: 1.
Számolja meg a hiányzó értékeket a SUMPRODUCT és a COUNTIF paraméterekkel
Számolni a azon értékek teljes száma a B listában, amelyek hiányoznak az A listából, használhatja a COUNTIF függvényt is annak megállapítására, hogy létezik-e érték az A listában vagy sem, a „=0” feltétellel, mivel a 0 értéket generál, ha hiányzik egy érték. A SUMPRODUCT ezután megszámolja a hiányzó értékek teljes számát.
Általános szintaxis
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- lookup_range: Az összehasonlítandó tartomány a tartomány_szám. Itt az A listára hivatkozunk.
- range_to_count: Az a tartomány, amelyből a hiányzó értékeket meg kell számolni. Itt a B listára hivatkozunk.
- 0: A match_type 0 arra kényszeríti a MATCH-t, hogy pontos egyezést hajtson végre.
Számolni a azon értékek teljes száma a B listában, amelyek hiányoznak az A listából, másolja vagy írja be az alábbi képletet a H6 cellába, majd nyomja meg a gombot belép az eredmény eléréséhez:
=SUMPRODUCT(--(COUNTIF(B6: B10,F6: F8)=0))
A képlet magyarázata
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10;F6:F8): A COUNTIF függvény megszámolja a cellák előfordulási számát F6 nak nek F8 határon belül B6: B10. Az eredmény egy ilyen tömbben lesz: {1; 1; 0}.
- - (COUNTIF(B6:B10;F6:F8)= 0) = - ({1; 1; 0}= 0): Az {1;1;0}=0 kódrészlet TRUE és FALSE tömböt eredményez {FALSE;FALSE;TRUE}. A kettős negatív előjel ezután az IGAZ értékeket 1s-re, a FALSE-t pedig 0-ra változtatja. A végső tömb a következő: {0; 0; 1}.
- SUMPRODUCT(- (COUNTIF(B6:B10;F6:F8)= 0)) = SUMPRODUCT({0; 0; 1}): A SUMPRODUCT ezután a következő összeget adja vissza: 1.
Kapcsolódó funkciók
Az Excelben a SUMPRODUCT függvény felhasználható két vagy több oszlop vagy tömb összeszorozására, majd a termékek összegének megszerzésére. Valójában a SUMPRODUCT egy hasznos függvény, amely több kritériummal, például a COUNTIFS vagy a SUMIFS függvénnyel segíthet a cellaértékek számlálásában vagy összegzésében. Ez a cikk bemutatja a függvény szintaxisát és néhány példát ehhez a SUMPRODUCT függvényhez.
Az Excel MATCH függvény egy adott értéket keres egy cellatartományban, és visszaadja az érték relatív pozícióját.
A COUNTIF függvény egy statisztikai függvény az Excelben, amely a feltételnek megfelelő cellák számának megszámlálására szolgál. Támogatja a logikai operátorokat (<>, =, > és <), valamint a helyettesítő karaktereket (? és *) a részleges egyeztetéshez.
Kapcsolódó képletek
Keresse meg a hiányzó értékeket
Vannak esetek, amikor össze kell hasonlítania két listát, hogy ellenőrizze, létezik-e az A lista értéke az Excel B listájában. Például van egy terméklistája, és szeretné ellenőrizni, hogy a listán szereplő termékek szerepelnek-e a szállítója által biztosított terméklistában. Ennek a feladatnak az elvégzéséhez az alábbiakban három módszert soroltunk fel, bátran válassza ki a kívántat.
Ez a cikk azokra a cellákra vonatkozó Excel képletekre összpontosít, amelyek pontosan megegyeznek egy megadott szöveglánccal vagy részlegesen megegyeznek az adott szöveglánccal, ahogy az alábbi képernyőképeken látható. Először megmagyarázza a képlet szintaxisát és argumentumát, valamint példákat ad a jobb megértéshez.
A cellák számát ne két megadott szám között számolja
A cellák számának két szám közötti megszámlálása gyakori feladat számunkra az Excelben, de bizonyos esetekben érdemes a cellákat nem két megadott szám között számolni. Például van egy terméklistám a hétfőtől vasárnapig tartó eladásokkal, most meg kell szereznem a cellák számát, amely nem a konkrét alacsony és magas számok között van, amint az a képernyőképen látható. Ebben a cikkben bemutatok néhány képletet ennek a feladatnak az Excelben való kezelésére.
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.
