Jak dodać formatowanie warunkowe do komórek zawierających # N / A w programie Excel?

18

Mam kolumnę w programie Excel 2013 wypełnioną wartościami VLOOKUP(). Z jakiegoś powodu nie mogę użyć formatowania warunkowego do wyróżnienia komórek, które zawierają #N/A.

Próbowałem utworzyć reguły wyróżniania dla „Równe do ...” i „Tekst zawierający ...”, ale żadne z nich nie działa.

Jak mogę użyć formatowania warunkowego, aby podświetlić komórki zawierające # Nie dotyczy?

microsoft-excel microsoft-excel-2013 conditional-formatting

— Stevoisiak
źródło

21

#N/Aw przypadku Excela nie jest „tekstem”, po prostu wygląda tak. W rzeczywistości jest to bardzo specyficzny błąd, co oznacza, że wartość jest „Niedostępna” z powodu błędu podczas obliczania.

Możesz użyć, ISNA(Range)aby dopasować do błędu tego typu.

Zamiast „zawiera tekst” chcesz utworzyć nową pustą regułę zamiast ogólnych, a następnie „Użyj formuły, aby określić, które komórki sformatować”.

Tam powinieneś być w stanie ustawić regułę dla pierwszej komórki w swoim zakresie, a ona spłynie przez resztę zakresu.

=ISNA(range)

Na przykład, aby warunkowo sformatować komórki B6:B8:

Wybierz pierwszą komórkę, którą chcesz podświetlić. (B6)
Kliknij Strona glowna -> Formatowanie warunkowe -> Zarządzaj regułami -> Nowa reguła .
Wybierz Użyj formuły, aby określić, które komórki sformatować .
W polu Formatuj wartości, w których ta formuła jest prawdziwa , wprowadź =ISNA($B6).
Kliknij Format, aby ustawić formatowanie komórki, a następnie wybierz OK .
Kliknij ponownie OK, aby utworzyć regułę formatowania.
W Formatowanie warunkowe Menedżer reguł , należy edytować pod zakres dotyczy (np $B6:$B8)
Wybierz OK, aby zastosować regułę.

Który będzie pasował do true, a zatem zastosuje pożądane formatowanie.

W celach informacyjnych Microsoft udostępnia listę funkcji IS, która pokazuje, jakie są, a także przykłady ich użycia.

— Mokubai
źródło

Jednym z powodów, dla których użycie „zawiera tekst” nie zadziałałoby jest to, że jeśli zmieniłeś ustawienia języka, podejrzewam, że „# N / A” może zmienić się na dowolny lokalny skrót N / A.

— Muzer

Gdy dodam regułę, nie spływa ona do innych komórek. Dotyczy to tylko komórki, którą zaznaczyłem

— Stevoisiak

@StevenVascellaro Upewnij się, że masz ustawiony zakres, ale upewnij się również, że znak „$” w formule znajduje się we właściwym miejscu, ponieważ ogranicza on przepływ „reguły”. Aby zejść tylko dane, musi znajdować się przed kolumną, w moim przypadku „B”. Aby przejść przez to musi być przed liczbą, ale nie kolumna. Jeśli znajduje się przed nimi, reguła nie będzie działać. Mam $ B6, aby wpłynęło to na rzeczy z rzędu 6 w dół.

— Mokubai

1

Jeśli masz wielokolumnowy zestaw danych z wieloma wierszami i chcesz, aby obejmował wszystko we wszystkich zakresach „dotyczy”, potrzebujesz po prostu punktu początkowego w tym zakresie dla formuły i żadnego znaku „$” w ogóle.

— Mokubai

@StevenVascellaro zwróć uwagę na formułę i zakres „dotyczy” tutaj: i.stack.imgur.com/mkk0l.png

— Mokubai

4

Użyj niestandardowej formuły:

=ISERROR($C1)

— Scott Craner
źródło

2

Lub użyj opcji „Formatuj tylko komórki zawierające” i zmień pierwszą listę rozwijaną z „Wartość komórki” na „Błędy”

— Dan
źródło

1

Inną opcją jest użycie następującej formuły:

=IFERROR(VLOOKUP(A1,[Some Range],[Some Column]), "Not Found")

Zamień „Nie znaleziono” na odpowiedni tekst błędu.

Następnie ustaw regułę formatu warunkowego dla napisanego tekstu błędu. Jeśli WYSZUKAJ.PIONOWO znajdzie wartość, wyświetli tę wartość. W przeciwnym razie wyświetli tekst błędu, który następnie będzie miał zastosowane reguły formatowania warunkowego.

— SGR
źródło

1

Zwykle po prostu oszukałem system, zaznaczając wszystkie komórki określonym kolorem, a następnie dodałem regułę formatowania „poprawnych” wartości na czarno. w wyniku tego wszystkie nieprawidłowe wartości pozostają kolorowe

— Steph
źródło