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

Formuła programu Excel: sprawdź, czy komórka zawiera jedną z kilku wartości, ale wyklucz inne wartości

Przypuśćmy, że istnieją dwie listy wartości, chcesz sprawdzić, czy komórka B3 zawiera jedną z wartości z zakresu E3: E5, ale jednocześnie nie zawiera żadnych wartości z zakresu F3: F4, jak pokazano na zrzucie ekranu. Ten samouczek zawiera formułę umożliwiającą szybkie wykonanie tego zadania w programie Excel i wyjaśnienie argumentów formuły.
doc sprawdź, czy zawiera jedną z rzeczy, ale wyklucz 1

Wzór ogólny:

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0)

Argumenty

Text: the text string you want to check.
Include: the values you want to check if argument text contains.
Exclude: the values you want to check if argument text does not contain.

Wartość zwracana:

Formuła zwraca 1 lub 0. Gdy komórka zawiera jedną z wartości, które należy uwzględnić, i nie zawiera żadnych wartości do wykluczenia, zwraca 1 lub zwraca 0. Ta formuła 1 i 0 są traktowane jak wartości logiczne Prawda i fałsz.

Jak działa ta formuła

Przypuśćmy, że chcesz sprawdzić, czy komórka B3 zawiera jedną z wartości z zakresu E3: E5, ale jednocześnie wykluczyć wartości z zakresu F3: F4, użyj poniższej formuły

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

Naciśnij przycisk Wchodzę aby uzyskać wynik sprawdzania.
doc sprawdź, czy zawiera jedną z rzeczy, ale wyklucz 2

Wyjaśnienie

1 część: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) sprawdza, czy komórka zawiera wartości w E3: E5

SZUKAJ funkcja: funkcja SZUKAJ zwraca pozycję pierwszego znaku ciągu tekstowego wewnątrz innego, jeśli funkcja SZUKAJ znajduje dopasowany tekst, zwraca pozycję względną, jeśli nie, zwraca # WARTOŚĆ! błąd. Na przykład tutaj formuła SEARCH($E$3:$E$5,B3) wyszuka każdą wartość z zakresu E3: E5 w komórce B3 i zwróci lokalizację każdego ciągu tekstowego w komórce B3. Zwróci wynik tablicy w następujący sposób: {1; 7; 12}.

Funkcja ISNUMBER: funkcja ISNUMBER zwraca wartość TRUE, gdy komórka jest liczbą. Więc ISNUMBER(SEARCH($E$3:$E$5,B3)) zwróci wynik tablicy jako {prawda, prawda, prawda}, ponieważ funkcja SZUKAJ wyszukuje 3 liczby.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) konwertuje wartość TRUE na 1 i konwertuje wartość FALSE na 0, więc ta formuła zmienia wynik tablicy na {1; 1; 1}.

SUMPRODUCT funkcja: służy do mnożenia zakresów lub sumowania tablic i zwraca sumę produktów. Plik SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) zwraca 1 + 1 + 1 = 3.

W końcu porównaj lewą formułę SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) i 0, o ile wynik lewej formuły jest większy niż 0, wynik będzie TRUE lub zwróci FALSE. Tutaj zwraca TRUE.
doc sprawdź, czy zawiera jedną z rzeczy, ale wyklucz 3

2 część: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) sprawdza, czy komórka nie zawiera wartości w F3: F4

Formula SZUKAJ ($ F $ 3: $ F $ 4, B3) wyszuka każdą wartość z zakresu E3: E5 w komórce B3 i zwróci lokalizację każdego ciągu tekstowego w komórce B3. Zwróci wynik tablicy w następujący sposób: {# WARTOŚĆ!; # WARTOŚĆ!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) zwróci wynik tablicy jako {fałsz; fałsz} ponieważ funkcja SEARCH znajduje 0 liczby.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) konwertuje wartość TRUE na 1 i konwertuje wartość FALSE na 0, więc ta formuła zmienia wynik tablicy na {0; 0}.

SUMPRODUCT funkcja: służy do mnożenia zakresów lub sumowania tablic i zwraca sumę produktów. Plik SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) zwraca 0 + 0 = 0.

W końcu porównaj lewą formułę SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) i 0, tak długo, jak wynik lewej formuły jest równy 0, wynik zwróci TRUE lub FALSE. Tutaj zwraca TRUE.
doc sprawdź, czy zawiera jedną z rzeczy, ale wyklucz 4

Część 3: Wiele dwóch formuł

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Ta formuła 1 i 0 są traktowane jak wartości logiczne PRAWDA i FAŁSZ.

Przykładowy plik

przykładowy dokumentKliknij, aby pobrać przykładowy plik


Wzory względne

  • Sprawdź, czy komórka zawiera określony tekst
    Aby sprawdzić, czy komórka zawiera niektóre teksty w zakresie A, ale nie zawiera tekstów w zakresie B, możesz użyć formuły tablicowej, która łączy funkcję LICZNIK, SZUKAJ i ORAZ w programie Excel
  • Sprawdź, czy komórka zawiera jedną z wielu rzeczy
    W tym samouczku przedstawiono formułę umożliwiającą sprawdzenie, czy komórka zawiera jedną z kilku wartości w programie Excel, oraz wyjaśniono argumenty w formule i sposób działania formuły.
  • Sprawdź, czy komórka zawiera jedną z rzeczy
    Przypuśćmy, że w programie Excel jest lista wartości w kolumnie E, chcesz sprawdzić, czy komórki w kolumnie B zawierają wszystkie wartości w kolumnie E i zwracają PRAWDA lub FAŁSZ.
  • Sprawdź, czy komórka zawiera liczbę
    Czasami możesz chcieć sprawdzić, czy komórka zawiera znaki numeryczne. Ten samouczek zawiera formułę, która zwróci wartość TRUE, jeśli komórka zawiera liczbę, FALSE, jeśli komórka nie zawiera liczby.

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 (1)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Liste;B2)))
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