Formuła sumuje 11 wierszy, a w zestawieniu jest 12. Które kontrole wyłapią to przed wysłaniem?

W arkuszu zamknięcia miesiąca formuła w wierszu podsumowania brzmi =SUMA(B2:B12). W lutym doszedł nowy zespół i ktoś wstawił wiersz 13 – pod spodem, poza zakresem. Arkusz nie zgłosił błędu, bo żadnego błędu nie popełnił: policzył dokładnie to, co mu kazano. Zestawienie poszło do zarządu z kosztem niższym o jedną pozycję, a marża wyszła o kilka punktów za wysoko.

Takich błędów nie wyłapuje czytanie arkusza, bo arkusz wygląda poprawnie. Wyłapuje je tylko druga, niezależna droga do tej samej liczby.

Dlaczego intensywne inspekcje znajdują błędy w 94 proc. arkuszy operacyjnych

Ray Panko, który zebrał badania nad błędami w arkuszach, podaje dwie liczby wyjaśniające, dlaczego problem jest systemowy, a nie kwestią czyjejś staranności. Pierwsza: w 14 badaniach laboratoryjnych z udziałem 967 osób średni odsetek błędnych komórek wyniósł 3,9 proc. – czyli mniej więcej tyle, ile daje człowiek przy każdym innym zadaniu wymagającym precyzji. Druga: na 85 arkuszy operacyjnych poddanych wielodniowej inspekcji błędy znalazły się w 94 proc. z nich, przy wskaźnikach błędnych komórek na poziomie 1,2-2,5 proc.

Te dwie liczby razem dają wniosek, który jest sednem sprawy: skoro pojedyncza komórka myli się rzadko, a arkusz ma ich setki połączonych w łańcuchy, to prawdopodobieństwo błędu w liczbie końcowej rośnie z każdym kolejnym ogniwem. Panko zwraca też uwagę, że autorzy arkuszy są mocno przekonani o ich poprawności – co jest dokładnie tym, czego należy się spodziewać, bo błąd, który widać, zostaje poprawiony od razu. Do zarządu idą wyłącznie błędy niewidoczne.

Dlatego kontrola arkusza nie polega na jego przeczytaniu. Polega na zbudowaniu w nim pozycji, które same się odezwą.

Pięć kontroli, które wchodzą do arkusza raz i działają co miesiąc

Wszystkie pięć zwraca zero albo słowo „ok”, kiedy jest dobrze. Umieść je w jednym bloku na górze pierwszej zakładki, nie na końcu i nie w osobnym pliku – kontrola, do której trzeba przewinąć, nie jest sprawdzana. Jedna komórka zbiorcza nad blokiem: =JEŻELI(SUMA(zakres_kontrolny)=0;"arkusz czysty";"BŁĄD").

Dlaczego suma kontrolna musi iść inną drogą niż raport

Kontrola, która powtarza tę samą operację co raport, nie kontroluje niczego – powtórzy ten sam błąd. Jeśli koszty sumujesz w pionie po pozycjach, to suma kontrolna musi iść w poziomie po miesiącach albo po innym wymiarze, na przykład po projektach. Dwie drogi muszą się spotkać na tej samej liczbie, mimo że przechodzą przez inne komórki. Różnica inna niż zero oznacza, że jedna z dróg pominęła albo policzyła dwa razy.

Warunek praktyczny: porównuj po zaokrągleniu do dwóch miejsc, bo arytmetyka zmiennoprzecinkowa zostawia różnice rzędu 0,0000001 i kontrola bez zaokrąglenia będzie świecić na czerwono zawsze. Kontrola, która myli się regularnie, zostaje wyłączona w trzecim miesiącu – to częstszy powód braku kontroli niż jej brak od początku.

Twarde wartości w kolumnie formuł: jak je znaleźć w pół minuty

Najtrudniejszy do wykrycia błąd w arkuszu zarządczym to liczba wklejona ręcznie tam, gdzie powinna być formuła. Zdarza się przy ręcznej korekcie: ktoś widzi, że wynik się nie zgadza, wpisuje właściwą kwotę i zapomina. W kolejnym miesiącu ta kwota zostaje, bo nie zależy już od niczego.

Dwa sposoby, oba działają bez dodatków. Pierwszy: zaznacz kolumnę i użyj przejścia do komórek specjalnych z opcją „stałe” – w Excelu pod klawiszem F5, w Arkuszach przez wyszukiwanie w zakresie. Zaznaczone zostaną wyłącznie komórki bez formuł. Drugi, lepszy, bo automatyczny: dodaj kolumnę pomocniczą z =CZY.FORMUŁA(C2) i policz, ile razy wychodzi FAŁSZ. Liczba oczekiwana jest znana – tyle, ile wierszy wejściowych – i każde odstępstwo ma powód do sprawdzenia.

Ta jedna kontrola wyłapuje też sytuację odwrotną i rzadziej omawianą: formułę wklejoną w kolumnę danych wejściowych, która zaczyna liczyć coś z samej siebie.

Zakresy, które nie rozjeżdżają się po wstawieniu wiersza

Błąd z pierwszego akapitu ma rozwiązanie strukturalne, nie kontrolne. Trzy opcje w kolejności od najlepszej:

• Tabela nazwana (w Excelu wstawiana skrótem Ctrl+T, w Arkuszach odpowiednikiem jest zakres nazwany rozszerzany automatycznie). Odwołanie przez nazwę kolumny rozszerza się samo przy dopisaniu wiersza na końcu i jest odporne na wstawienie w środku.
• Zakres z nadmiarem, na przykład =SUMA(B2:B500) przy 12 pozycjach. Rozwiązanie brzydkie i skuteczne, pod warunkiem, że pod zakresem nie ma niczego innego – a to trzeba utrzymać regułą, nie pamięcią.
• Suma całej kolumny pomniejszona o nagłówki – działa tylko w arkuszu, w którym jedna kolumna ma jedno znaczenie i nic poniżej.

Czego nie robić: rozszerzać zakresu ręcznie po każdym dopisaniu wiersza. To zadanie, które raz na dziewięć miesięcy zostanie pominięte, a jego pominięcie nie zostawia śladu.

Jak sprawdzić, czy kontrola w ogóle działa

Kontrola, która nigdy się nie odezwała, jest nie do odróżnienia od kontroli zepsutej. Oba stany wyglądają identycznie: komórka pokazuje zero. Dlatego każdą z nich trzeba raz celowo złamać.

Test zajmuje minutę i wygląda tak: na kopii arkusza zmień jedną liczbę wejściową o 100 zł i sprawdź, czy komórka zbiorcza przeszła w stan błędu. Potem cofnij. Jeśli nie przeszła, kontrola odwołuje się do złego zakresu albo porównuje liczbę samą ze sobą – ten drugi przypadek zdarza się, gdy obie strony porównania pochodzą z tej samej komórki pośredniej.

Powtarzaj ten test raz na kwartał, nie raz na początku. Arkusz zarządczy żyje: dochodzą zakładki, zmieniają się nazwy zakresów, ktoś przenosi blok kontrolny niżej, żeby zrobić miejsce na nowy wskaźnik. Każda z tych zmian potrafi odciąć kontrolę od tego, co miała pilnować, nie zmieniając jej wyglądu.

Kto może nadpisać formułę

Większość błędów w arkuszu zarządczym powstaje nie przy jego budowie, a przy comiesięcznym użyciu – i to przez osoby, które nie wiedzą, że właśnie nadpisały formułę. Rozwiązanie jest techniczne i zajmuje kwadrans: zablokuj wszystkie komórki poza kolumną danych wejściowych.

• W Arkuszach: chroń zakres i zostaw wyjątek na kolumnę wejściową. Komunikat ostrzegawczy przy edycji jest wystarczający, blokada twarda przeszkadza przy poprawkach.
• W Excelu: odblokuj komórki wejściowe we właściwościach formatowania, a potem ochroń arkusz. Bez hasła – celem nie jest zabezpieczenie przed kimś, a ostrzeżenie dla kogoś, kto nie wie.
• Kolorystyka wejścia: jedno tło dla komórek, które wolno wpisywać ręcznie, i brak koloru dla wszystkiego pozostałego. Konwencja czytelna bez instrukcji po pół roku przerwy.

Przy arkuszu, który dotyka więcej niż dwóch osób, dochodzi jeszcze jedna rzecz: historia zmian musi być włączona i sprawdzalna. Pytanie „kto zmienił tę liczbę i kiedy” pojawia się zawsze w najgorszym momencie, czyli po wysłaniu raportu.

Czego te kontrole nie wyłapią

Wszystkie pięć kontroli sprawdza spójność arkusza, nie poprawność metody. Arkusz, w którym koszty ogólne dzielone są na projekty po równo, zamiast proporcjonalnie do zaangażowania, przejdzie każdą z nich – sumy będą się zgadzać, licznik pozycji będzie poprawny, twardych wartości nie będzie. Wynik będzie błędny mimo tego.

Błędy metody wyłapuje tylko jedno: opisana i sprawdzalna definicja każdej liczby w raporcie. Taki opis ma być wykonywalny przez kogoś innego niż autor arkusza, bo arkusz, którego metodę zna wyłącznie jego twórca, jest ryzykiem operacyjnym niezależnie od liczby kontroli.

Drugi obszar poza zasięgiem: dane wejściowe, które są kompletne i błędne naraz. Jeśli eksport z systemu kadrowego ma zły narzut, uzgodnienie z systemem źródłowym wyjdzie zerowe, bo arkusz uzgodni się z błędem.

Decyzja: które kontrole wchodzą przed najbliższym zamknięciem

Dwie, nie pięć. Wdrażanie pięciu kontroli naraz w arkuszu, który już działa, kończy się tym, że żadna nie zostaje. Zacznij od sumy na dwóch drogach i licznika pozycji – razem około 20 minut pracy i razem łapią najczęstszą kategorię błędu, czyli rozjechany zakres.

Warunek, bez którego to nie zadziała: ustal, co się dzieje, gdy komórka zbiorcza pokazuje „BŁĄD”. Jeśli odpowiedź brzmi „sprawdzimy przy okazji”, kontrola jest ozdobą. Jedyna odpowiedź, która ma sens, to wstrzymanie wysyłki raportu do czasu wyjaśnienia różnicy – z tego wynika, że kontrola musi działać co najmniej dzień przed terminem wydania zestawienia, a nie w dniu wydania. Pozostałe trzy kontrole dodaj w kolejnym miesiącu, kiedy pierwsze dwie przestaną się mylić.