Formuła programu Excel: sprawdź, czy komórka zawiera wiele 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, jak pokazano poniżej. W tym samouczku zawiera formułę rozwiązania tego zadania.
Wzór ogólny:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Argumenty
Things: the list of values that you want to use to check if argument text contains. |
Text: the cell or text string you want to check if containing argument things. |
Wartość zwracana:
Ta formuła zwraca wartość logiczną FALSE: komórka nie zawiera wszystkich elementów; PRAWDA : komórka zawiera wszystko.
Jak działa ta formuła
Na przykład w kolumnie B znajduje się lista ciągów tekstowych, które chcesz sprawdzić, czy zawierają wszystkie wartości z zakresu E3: E5, użyj poniższego wzoru.
=SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Naciśnij przycisk Wchodzę klawisz, przeciągnij uchwyt wypełnienia na komórki, które chcesz sprawdzić. FALSE wskazuje, że komórka nie zawiera wszystkich wartości z zakresu E3: E5, a TURE wskazuje, że komórka względna zawiera wszystkie wartości.
Wyjaśnienie
Funkcja SEARCH: funkcja SEARCH zwróci położenie pierwszego znaku ciągu tekstowego w innym. Jeśli funkcja SZUKAJ znajdzie tekst, zwraca pozycję, jeśli nie, zwraca #ARG! błąd. Na przykład tutaj formuła SEARCH($E$3:$E$5,B4) wyszuka każdą wartość z zakresu E3: E5 w komórce B4 i zwróci lokalizację każdego ciągu tekstowego w komórce B4. Zwróci wynik tablicy w następujący sposób: {1;7;12}
Funkcja ISNUMBER: funkcja ISNUMBER sprawdzi, czy wartość jest liczbą i zwróci TRUE lub False. Tutaj ISNUMBER(SEARCH($E$3:$E$5,B4)) zwróci wynik tablicy jako {true;true;true} ponieważ funkcja SEARCH znajduje 3 liczby.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) przekonwertuje wartość TRUE na 1 i przekonwertuje wartość FALSE na 0, więc ta formuła zmieni wynik tablicy na {1;1;1}.
Funkcja SUMPRODUCT: służy do mnożenia zakresów lub sumowania tablic i zwraca sumę iloczynów. Plik SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) zwraca 1 + 1 + 1 = 3.
Funkcja COUNTA: zwraca liczbę niepustych komórek. COUNTA($E$3:$E$5) zwraca 3, więc wynik SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) jest równa 3, cała formuła zwróci TRUE lub FALSE.
Uwagi:
Formula =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) nie do końca sprawdza. Zobacz zrzut ekranu:
Przykładowy plik
Kliknij, aby pobrać przykładowy plik
Wzory względne
- Policz komórki równe
Funkcja LICZ.JEŻELI umożliwia zliczanie komórek, które są równe lub nie zawierają określonej wartości. - Policz komórki równe x lub y
Czasami możesz chcieć policzyć liczbę komórek spełniających jedno z dwóch kryteriów, w tym przypadku możesz użyć funkcji LICZ.JEŻELI. - Policz komórki równe x i y
W tym artykule przedstawiono formułę do liczenia komórek, które w międzyczasie spełniają dwa kryteria. - Liczba komórek nie jest równa
W tym artykule omówiono, jak używać funkcji LICZ.JEŻELI do zliczania liczby komórek, które nie są równe określonej wartości
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.