Formuła ogólna
=IF(logical_test,"P","")
Podsumowanie
Aby wyświetlić znacznik wyboru, jeśli wartość jest „dozwolona” na podstawie istniejącej listy dopuszczalnych wartości, możesz użyć formuły opartej na funkcji JEŻELI razem z funkcją LICZ.JEŻELI. W tym przykładzie formuła w C5 to:
=IF(COUNTIF(allowed,B5),"✓","")
gdzie dozwolony jest nazwany zakres E5: E9.
Wyjaśnienie
Ta formuła jest dobrym przykładem zagnieżdżania jednej funkcji w drugiej. Zasadniczo ta formuła korzysta z funkcji JEŻELI skonfigurowanej do zwracania znacznika wyboru (✓), gdy test logiczny zwraca wartość PRAWDA:
=IF(logical_test,"✓","")
Jeśli test zwróci FALSE, formuła zwraca pusty ciąg („”). Do testu logicznego używamy funkcji LICZ.JEŻELI w następujący sposób:
COUNTIF(allowed,B5)
LICZ.JEŻELI zlicza wystąpienia wartości w B5 w nazwanym dozwolonym zakresie (E5: E9). Może się to wydawać „wsteczne”, ale jeśli o tym pomyślisz, ma to sens. Jeśli wartość w B5 zostanie znaleziona na liście dozwolonych wartości, LICZ.JEŻELI zwróci liczbę dodatnią (w tym przypadku 1). Jeśli nie, LICZ.JEŻELI zwróci zero. Excel oceni każdą niezerową liczbę jako PRAWDA, więc działa to doskonale jako logiczny test dla JEŻELI.
JEŻELI zwróci wartość TRUE tylko wtedy, gdy wartość zostanie znaleziona na liście dozwolonych, a jeśli tak, wynikiem końcowym jest znacznik wyboru (✓). Jeśli wartość nie zostanie znaleziona na liście dozwolonych, LICZ.JEŻELI zwraca zero, które jest oceniane jako FAŁSZ. W takim przypadku wynikiem końcowym jest pusty ciąg („”), który nic nie wyświetla.
Ze stałymi wartościami
Powyższy przykład pokazuje dozwolone wartości w zakresie komórek, ale dozwolone wartości można również zakodować na stałe w formułach jako stałą tablicową, na przykład:
=IF(COUNTIF(("red","blue","green"),B5),"✓","")
Znak zaznaczenia (✓)
Wstawienie znaku zaznaczenia w programie Excel może być zaskakująco trudne, a w Internecie znajdziesz wiele artykułów wyjaśniających różne podejścia. Najłatwiejszym sposobem uzyskania znaku wyboru (✓) używanego w tej formule do programu Excel jest po prostu skopiowanie go i wklejenie. Jeśli kopiujesz z tej strony internetowej, wklej na pasek formuły, aby uniknąć przeciągania w niepożądanym formatowaniu. Możesz także kopiować i wklejać bezpośrednio z załączonego arkusza.
Jeśli masz problemy z kopiowaniem i wklejaniem, wypróbuj tę odmianę. Sam znak to Unicode 2713 (U + 2713) i można go również wprowadzić w programie Excel za pomocą funkcji UNICHAR w następujący sposób:
=UNICHAR(10003) // returns "✓"
Tak więc oryginalną formułę można zapisać w ten sposób:
=IF(COUNTIF(allowed,B5),UNICHAR(10003),"")
Uwaga: funkcja UNICHAR została wprowadzona w programie Excel 2013.
Przedłużenie formuły
Podstawową ideę tej formuły można rozwinąć na wiele sprytnych sposobów. Na przykład poniższy zrzut ekranu przedstawia formułę, która zwraca znacznik wyboru tylko wtedy, gdy wszystkie wyniki testów mają co najmniej 65:
Wzór w G5 to:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
Funkcja NIE odwraca wynik z LICZ.JEŻELI. Jeśli uznasz to za mylące, możesz na przemian zmienić strukturę wzoru JEŻELI w następujący sposób:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
W wersji formuły logika jest bardziej podobna do oryginalnej formuły powyżej. Jednak przenieśliśmy znacznik wyboru na argument wartość_jeśli_fałsz, więc znacznik wyboru pojawi się tylko wtedy, gdy liczba z LICZ.JEŻELI wynosi zero. Innymi słowy, znacznik wyboru pojawi się tylko wtedy, gdy nie zostaną znalezione żadne wartości mniejsze niż 65.
Uwaga: możesz również użyć formatowania warunkowego, aby podświetlić prawidłowe lub nieprawidłowe dane wejściowe, oraz walidacji danych, aby ograniczyć wprowadzanie, aby zezwalać tylko na prawidłowe dane.
