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

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 1

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: Podróż Ruta de la Plata w liczbach 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)))

policz brakujące wartości 2

Wyjaśnienie formuły

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

  • PODAJ.POZYCJĘ(F6:F8,B6:B10,0): Podróż Ruta de la Plata w liczbach 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: Podróż Ruta de la Plata w liczbach 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))

policz brakujące wartości 3

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

Funkcja Excel SUMPRODUCT

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 MATCH

Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.

Funkcja LICZ.JEŻELI Excel

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

Znajdź brakujące wartości

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.

Policz komórki równe

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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL