Policz brakujące wartości
Przypuśćmy, że masz dwie listy, aby policzyć całkowitą liczbę wartości na jednej liście, które nie istnieją na innej liście, możesz użyć SUMPRODUCT formuła przy pomocy MATCH i funkcje ISNA lub COUNTIF funkcja.
Policz brakujące wartości za pomocą SUMPRODUCT, MATCH i ISNA
Policz brakujące wartości za pomocą SUMPRODUCT i COUNTIF
Policz brakujące wartości za pomocą SUMPRODUCT, MATCH i ISNA
Aby policzyć całkowita liczba wartości z listy B, których nie ma na liście A jak pokazano powyżej, możesz najpierw użyć funkcji PODAJ.POZYCJĘ, aby zwrócić tablicę względnej pozycji wartości z listy B na liście A. Jeśli wartość nie istnieje na liście A, zostanie zwrócony błąd #N/D. Funkcja ISNA zidentyfikuje następnie błędy #N/D, a SUMPRODUCT policzy całkowitą liczbę błędów.
Ogólna składnia
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- zakres_do_liczenia: Zakres, od którego należy liczyć brakujące wartości. Tutaj odwołuje się do listy B.
- zakres_wyszukiwania: Zakres do porównania z zakres_do_liczenia. Tutaj odwołuje się do listy A.
- 0: Połączenia typ_dopasowania 0 zmusza MATCH do wykonania dokładnego dopasowania.
Aby policzyć całkowita liczba wartości z listy B, których nie ma na liście A, skopiuj lub wprowadź poniższą formułę w komórce H6 i naciśnij Wchodzę aby uzyskać wynik:
=PRODUKCJA SUMA(--ISNA(PODSTAW()F6: F8,B6: B10,0)))
Wyjaśnienie formuły
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- PODAJ.POZYCJĘ(F6:F8,B6:B10,0): Połączenia typ_dopasowania 0 wymusza na funkcji PODAJ.POZYCJĘ zwracanie wartości liczbowych, które wskazują względne pozycje wartości w komórkach F6 do F8 w zasięgu B6: B10. Jeśli wartość nie istnieje na liście A, zostanie zwrócony błąd #N/D. Tak więc wyniki będą w tablicy takiej jak ta: {2;3;#nie dotyczy}.
- ISNA(PODAJ.POZYCJĘ(F6:F8,B6:B10,0)) = ISNA({2;3;#nie dotyczy}): ISNA stara się ustalić, czy wartość jest błędem „#N/A”, czy nie. Jeśli tak, funkcja zwróci TURE; Jeśli nie, zwróci FALSE. Tak więc formuła ISNA powróci {FAŁSZ;FAŁSZ;PRAWDA}.
- SUMA PRODUKT(--ISNA(PODAJ.POZYCJĘ(F6:F8,B6:B10,0))) = PRODUKT SUMA(--{FAŁSZ;FAŁSZ;PRAWDA}): Podwójny znak ujemny konwertuje TRUE na 1s i FALSE na 0s: {0; 1; 0}. Następnie funkcja SUMPRODUCT zwraca sumę: 1.
Policz brakujące wartości za pomocą SUMPRODUCT i COUNTIF
Aby policzyć całkowita liczba wartości z listy B, których nie ma na liście A, można również użyć funkcji LICZ.JEŻELI, aby stwierdzić, czy wartość istnieje na liście A z warunkiem „=0”, ponieważ w przypadku braku wartości zostanie wygenerowane 0. SUMPRODUCT zliczy wtedy całkowitą liczbę brakujących wartości.
Ogólna składnia
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- zakres_wyszukiwania: Zakres do porównania z zakres_do_liczenia. Tutaj odwołuje się do listy A.
- zakres_do_liczenia: Zakres, od którego należy liczyć brakujące wartości. Tutaj odwołuje się do listy B.
- 0: Połączenia typ_dopasowania 0 zmusza MATCH do wykonania dokładnego dopasowania.
Aby policzyć całkowita liczba wartości z listy B, których nie ma na liście A, skopiuj lub wprowadź poniższą formułę w komórce H6 i naciśnij Wchodzę aby uzyskać wynik:
=PROD.SUMA(--(LICZ.JEŻELI(B6: B10,F6: F8)=0))
Wyjaśnienie formuły
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- LICZ.JEŻELI(B6:B10;F6:F8): Funkcja LICZ.JEŻELI zlicza liczbę wystąpień komórek do F6 do F8 w zasięgu B6: B10. Wynik będzie w tablicy takiej jak ta: {1; 1; 0}.
- - (LICZ.JEŻELI(B6:B10;F6:F8)= 0) = - ({1; 1; 0}= 0): Fragment kodu {1;1;0}=0 daje tablicę PRAWDA i FAŁSZ {FAŁSZ;FAŁSZ;PRAWDA}. Podwójny znak ujemny następnie zmienia TRUE na 1s, a FALSE na 0s. Ostateczna tablica wygląda tak: {0; 0; 1}.
- SUMA PRODUKT(- (LICZ.JEŻELI(B6:B10;F6:F8)= 0)) = PRODUKT SUMA({0; 0; 1}): SUMPRODUCT następnie zwraca sumę: 1.
Powiązane funkcje
W programie Excel funkcja SUMPRODUCT może służyć do mnożenia dwóch lub więcej kolumn lub tablic, a następnie do uzyskania sumy iloczynów. W rzeczywistości SUMPRODUCT jest użyteczną funkcją, która może pomóc zliczać lub sumować wartości komórek przy użyciu wielu kryteriów, takich jak funkcja LICZ.WARUNKI lub SUMA.WARUNKÓW. W tym artykule przedstawimy składnię funkcji i kilka przykładów dla tej funkcji SUMPRODUCT.
Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.
Funkcja LICZ.JEŻELI to funkcja statystyczna w programie Excel, która służy do zliczania liczby komórek spełniających kryterium. Obsługuje operatory logiczne (<>, =, > i <) oraz symbole wieloznaczne (? i *) do częściowego dopasowania.
Powiązane formuły
Istnieją przypadki, w których trzeba porównać dwie listy, aby sprawdzić, czy wartość listy A istnieje na liście B w programie Excel. Na przykład masz listę produktów i chcesz sprawdzić, czy produkty z Twojej listy istnieją na liście produktów dostarczonej przez Twojego dostawcę. Aby wykonać to zadanie, poniżej wymieniliśmy trzy sposoby poniżej, możesz wybrać ten, który Ci się podoba.
W tym artykule skupimy się na formułach programu Excel do liczenia komórek, które są dokładnie równe określonemu ciągowi tekstowemu lub częściowo równe podanemu ciągowi tekstowemu, jak pokazano poniżej. Po pierwsze, wyjaśni składnię i argumenty formuły, a przykłady pomogą Ci lepiej zrozumieć.
Policz liczbę komórek nie między dwiema podanymi liczbami
Liczenie liczby komórek między dwiema liczbami jest dla nas powszechnym zadaniem w programie Excel, ale w niektórych przypadkach możesz chcieć policzyć komórki nie między dwiema podanymi liczbami. Na przykład mam listę produktów ze sprzedażą od poniedziałku do niedzieli, teraz muszę uzyskać liczbę komórek, które nie znajdują się między określonymi niskimi i wysokimi liczbami, jak pokazano na zrzucie ekranu. W tym artykule przedstawię kilka formuł radzenia sobie z tym zadaniem w programie Excel.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
