W programie Excel, jak sprawdzić, czy komórka znajduje się na liście wartości (zakres komórek)

88

Mam zakres (A3: A10) zawierający nazwy i chciałbym sprawdzić, czy zawartość innej komórki (D1) pasuje do jednej z nazw na mojej liście.

Nazwałem zakres A3: A10 „some_names” i chciałbym formułę programu Excel, która da mi wartość True / False lub 1/0 w zależności od zawartości.

microsoft-excel worksheet-function

— joseph.hainline
źródło

92

= COUNTIF (some_names, D1)

powinien działać (1, jeśli nazwa jest obecna - więcej, jeśli więcej niż jedna instancja).

— orzechy
źródło

jak mogę zmodyfikować formułę, aby działała w przypadku, gdy some_nameszawiera 2 kolumny, a także zamiast D1 mam D1: E1?

— user1993,

66

Moja preferowana odpowiedź (zmodyfikowana przez Iana) to:

=COUNTIF(some_names,D1)>0

która zwraca PRAWDA, jeśli D1 zostanie znalezione w zakresie jakieś_nazwy przynajmniej raz, lub FALSE w przeciwnym razie.

(COUNTIF zwraca liczbę całkowitą, ile razy kryterium zostało znalezione w zakresie)

— joseph.hainline
źródło

26

Wiem, że OP wyraźnie stwierdził, że lista pochodzi z szeregu komórek, ale inni mogą się na to natknąć, szukając określonego zakresu wartości.

Za pomocą funkcji można także wyszukać określone wartości zamiast zakresu MATCH. To da ci numer, do którego pasuje (w tym przypadku drugie miejsce, więc 2). Zwróci # N / A, jeśli nie ma dopasowania.

=MATCH(4,{2,4,6,8},0)

Możesz również zastąpić pierwsze cztery komórką. Umieść 4 w komórce A1 i wpisz ją w dowolnej innej komórce.

=MATCH(A1,{2,4,6,8},0)

— RPh_Coder
źródło

1

Bardzo dobrze. Nie zapomnij dodać „cudzysłowów”, jeśli twoja wartość nie jest liczbą (zajęło mi kilka prób, aby to rozwiązać).

— dav

1

Niestety nie można tego użyć w formatowaniu warunkowym :(

— StarWeaver 18.04.16

Oczywiście że możesz. W programie Excel 2007 i nowszych można użyć funkcji JEŻELI. = JEŻELI.BŁĄD (PODAJ.POZYCJĘ (A1, {2,4,6,8}, 0), 0). Następnie możesz wykonać formatowanie warunkowe na podstawie tego, czy komórka = 0 lub> 0, w zależności od tego, co wolisz.

— RPh_Coder

6

=OR(4={2,4,6,8})

— Slai

Ta odpowiedź jest jasna, że rozwiązanie zwraca # N / A - to prawda. Ale wydaje się nieprzydatny: nie można użyć # N / A w klauzuli if, więc nie można powiedzieć IF (MATCH (4 {2,3}, 0), „yay”, „boo”) ... odpowiedź brzmi # nie dotyczy nie „boo”

— GreenAsJade

18

Jeśli chcesz zamienić licznik w jakieś inne wyjście (jak boolean), możesz również:

= JEŻELI (COUNTIF (some_names, D1)> 0, TRUE, FALSE)

Cieszyć się!

— Ian McClellan
źródło

3

PRAWDA i FAŁSZ pomagają wskazać, co należy wymienić

— Darcys22,

8

Dla odmiany możesz użyć MATCH, np

=ISNUMBER(MATCH(D1,A3:A10,0))

— Barry Houdini
źródło

4

istnieje sprytna sztuczka zwracająca wartość logiczną, na wypadek gdyby some_namesmożna było wyraźnie określić zakres, na przykład "purple","red","blue","green","orange":

=OR("Red"={"purple","red","blue","green","orange"})

Uwaga: NIE jest to formuła tablicowa

— Vrun
źródło

W rzeczywistości jest to formuła tablicowa. To, co nie jest, to formuła wprowadzona do tablicy ;-)

— robinCTS

2

Można zagnieździć --([range]=[cell])w sposób IF, SUMIFSalbo COUNTIFSargumentem. Na przykład IF(--($N$2:$N$23=D2),"in the list!","not in the list"). Wierzę, że może to bardziej efektywnie wykorzystywać pamięć.

Alternatywnie możesz owinąć ISERRORwokół VLOOKUP, wszystkie owinięte wokół IFinstrukcji. Polub IF( ISERROR ( VLOOKUP() ) , "not in the list" , "in the list!" ).

— John Skilbeck
źródło

0

Wersja formuły tablicowej (wprowadź za pomocą Ctrl + Shift + Enter):

=OR(A3:A10=D1)

— Slai
źródło

To działa. Myślę, że otrzymało ocenę negatywną, ponieważ downvoter nie wiedział, jak wprowadzić formułę tablicową ... po jej wprowadzeniu powinien wyglądać jak {= OR (R34: R36 = T34)}, jeśli wprowadzono ją poprawnie

— GreenAsJade

-1

W takich sytuacjach chcę być tylko powiadamiany o możliwych błędach, więc rozwiązałbym tę sytuację w ten sposób ...

=if(countif(some_names,D1)>0,"","MISSING")

Następnie skopiowałbym tę formułę z E1do E100. Jeśli wartości w Dkolumnie nie ma na liście, otrzymam komunikat BRAK, ale jeśli wartość istnieje, otrzymam pustą komórkę. Dzięki temu brakujące wartości znacznie się wyróżniają.

— Catchoval
źródło