Sprawdź, co zawiera komórka, zanim zaczniesz na niej liczyć, i wyłapuj tylko te błędy, których się spodziewasz.
Błędy i warunki · 6 krótkich ćwiczeń w żywym arkuszu: sam wpisujesz każdą formułę i odpowiadaj na pytania, które każą przewidywać wyniki lub wykrywać błędy. Każda odpowiedź jest sprawdzana od razu.
Co będziesz ćwiczyć
Sprawdzanie, czy komórka jest pusta
Sprawdzanie, czy wartość jest liczbą
Oznaczanie błędnych wpisów funkcjami IF i ISNUMBER
Funkcje IS odpowiadają na pytanie o komórkę wartością PRAWDA lub FAŁSZ. CZY.PUSTA (Angielski: ISBLANK)(komórka) jest PRAWDA tylko wtedy, gdy komórka jest całkowicie pusta.
CZY.LICZBA (Angielski: ISNUMBER)(wartość) jest PRAWDA dla liczb i dat, a FAŁSZ dla tekstu, pustych komórek i błędów. Tekst, na przykład „n/a”, nie jest liczbą.
Umieść funkcję IS wewnątrz JEŻELI (Angielski: IF), żeby zamienić sprawdzenie w komunikat albo decyzję.
JEŻELI.ND (Angielski: IFNA)(wartość; wartość, jeśli #N/A) zastępuje tylko błąd #N/A, który zwraca nieudane wyszukiwanie. Inne błędy (na przykład #DIV/0!) nadal się wyświetlają, dzięki czemu prawdziwe pomyłki nie są ukrywane.
Przewidź.CZY.BŁĄD (Angielski: ISERROR) jest PRAWDA dla każdego rodzaju błędu.
Funkcje IS zadają pytanie tak lub nie o komórkę: czy to liczba, czy jest pusta, czy to błąd? Zwracają TRUE lub FALSE i same nigdy się nie psują. Dzięki temu są najbezpieczniejszym sposobem sprawdzenia danych, zanim zaczniesz na nich liczyć.
Jedno pytanie, jedna odpowiedź
Każda funkcja sprawdza jedną rzecz. CZY.LICZBA (Angielski: ISNUMBER) daje TRUE dla liczb i dat. ISTEXT daje TRUE dla tekstu. CZY.PUSTA (Angielski: ISBLANK) daje TRUE tylko dla naprawdę pustej komórki. CZY.BŁĄD (Angielski: ISERROR) daje TRUE dla każdego błędu, a ISNA tylko dla błędu #N/D.
Wyobraź sobie, że A2 zawiera liczbę 120, A3 zawiera tekst "120" (wpisany z apostrofem na początku), a A4 jest pusta. Wtedy =CZY.LICZBA(A2) daje TRUE, ale =CZY.LICZBA(A3) daje FALSE, a =ISTEXT(A3) daje TRUE. Liczba zapisana jako tekst wygląda dobrze na ekranie, a mimo to psuje sumy, dlatego ten test jest tak przydatny.
W tej rodzinie jest jeszcze kilka. ISLOGICAL rozpoznaje TRUE i FALSE. ISERR daje TRUE dla każdego błędu oprócz #N/D. ISFORMULA mówi, czy komórka zawiera formułę, a ISEVEN i ISODD sprawdzają liczby całkowite. Wszystkie działają według tego samego schematu: jedna wartość na wejściu, TRUE lub FALSE na wyjściu.
Zamiana testu w decyzję
Same TRUE i FALSE rzadko są celem. Umieść test w JEŻELI (Angielski: IF), żeby wybrać, co dalej.
=JEŻELI(CZY.LICZBA(B2);B2*1,2;0)
Dolicz 20 procent tylko wtedy, gdy B2 jest prawdziwą liczbą; w przeciwnym razie zwróć 0.
Możesz też zbudować szybką kolumnę kontrolną obok zaimportowanych danych: jedna kolumna z =CZY.LICZBA(B2), a potem filtr na FALSE, żeby zobaczyć każdą komórkę, którą trzeba poprawić.
Świadome łapanie błędów
Błąd bywa czasem poprawną odpowiedzią. Wyszukiwanie, które zwraca #N/D, po prostu oznacza „nie znaleziono”. Owinięcie go w JEŻELI.ND (Angielski: IFNA) zastępuje tylko ten błąd i pozwala, by prawdziwe problemy, takie jak #ADR!, pozostały widoczne.
=JEŻELI.ND(WYSZUKAJ.PIONOWO(E2;A2:B9;2;FAŁSZ);0)
JEŻELI.BŁĄD (Angielski: IFERROR) ukrywa każdy rodzaj błędu, także te wywołane literówką w twojej własnej formule. Gdy spodziewasz się tylko „nie znaleziono”, wybierz JEŻELI.ND (Angielski: IFNA) albo ISNA.
Wzorce warte znajomości
Połącz funkcję IS z SZUKAJ.TEKST (Angielski: SEARCH), żeby sprawdzić, czy komórka zawiera słowo. SZUKAJ.TEKST (Angielski: SEARCH) zwraca pozycję, gdy znajdzie tekst, a błąd, gdy go nie znajdzie, więc CZY.LICZBA (Angielski: ISNUMBER) zamienia to na czyste TRUE lub FALSE.
Oznacza każdą notatkę, w której gdziekolwiek pojawia się słowo „pilne”, niezależnie od wielkości liter.
Żeby policzyć, ile komórek w kolumnie nie zawiera użytecznych liczb, odejmij od siebie dwa rodzaje liczenia: =ILE.NIEPUSTYCH(B2:B20)-ILE.LICZB(B2:B20). ILE.NIEPUSTYCH (Angielski: COUNTA) liczy każdą niepustą komórkę, a ILE.LICZB (Angielski: COUNT) tylko liczby, więc różnica to komórki do oczyszczenia.
Typowe błędy
Pusty tekst to nie to samo co pusta komórka. Formuła zwracająca "" nie jest pusta, więc =CZY.PUSTA(A5) daje dla niej FALSE. Żeby złapać oba przypadki, sprawdź =A5="", co daje TRUE zarówno dla pustej komórki, jak i pustego tekstu.
Spacja to tekst. Komórka, która wygląda na pustą, ale zawiera spację, nie jest pusta. Oczyść ją funkcją USUŃ.ZBĘDNE.ODSTĘPY (Angielski: TRIM).
Daty to liczby.CZY.LICZBA (Angielski: ISNUMBER) daje TRUE dla prawdziwej daty, a FALSE dla daty wpisanej jako tekst, którego Excel nie rozpoznał.
Sprawdź przed konwersją. Najpierw testuj funkcją CZY.LICZBA (Angielski: ISNUMBER), a dopiero potem użyj WARTOŚĆ (Angielski: VALUE) tylko w komórkach, które nie przeszły testu.