Dziś chciałbym pokazać Wam gdzie najczęściej zdarzają się błędy w wykorzystaniu Excela. Błędy te najczęściej są winą niewiedzy lub braku spostrzegawczości użytkownika ale możecie też spotkać błędy samej aplikacji. Naczelna zasada brzmi jeżeli na oko (lub na kalkulatorze) wynik wychodzi inny to należy szukać błędu w swoim działaniu lub algorytmie postępowania bo arkusz myli się niezmiernie rzadko i tylko w specyficznych przypadkach które Wam pokażę.

Formatowanie

Suma dziesięciu jedynek daje sześć. To przykład błędu który każdemu aktywnemu użytkownikowi Excela zdarzył się co najmniej raz. Oczywiście problemem jest formatowanie jedynek z wierszy 2,4,7 i 9 jako tekst. Te wartości nie są dodawane do naszego zestawienia przez co suma na oko wydaje się błędna. Istotną podpowiedzią są w tym przypadku zielone trójkąciki z prawej strony tekstowych komórek gdy staniemy w komórce z takim trójkącikiem z jej prawej strony pojawi nam się znak wykrzyknika który po rozwinięciu da nam możliwość zamiany tekstu na liczbę lub anulowanie błędu jako świadomego działania.

Podpowiedzi jednak wcale nie muszą się pojawić, zobaczcie poniższy przykład.

Co tu się stało ? Tylko naprawdę wprawne oko zauważy, że część liczb ma postać „1 000.00” czyli zawiera spację i kropkę przez co jest interpretowana jako tekst i wcale nie jest zaznaczana jako potencjalny błąd. Taki efekt możemy uzyskać całkiem nieświadomie kopiując np. dane z krajów gdzie kropka jest rozdzielaczem miejsc dziesiętnych. Co zrobić w takim przypadku ? najprościej zaznaczyć interesujący nas fragment i Menu-> Narzędzia główne -> Znajdź i zaznacz -> Zamień szukamy kropki zamieniamy na przecinek.

I ostatni przykład z tej serii

Chcieliśmy zsumować jedynki ale zahaczyła nam się data i wyszedł jakiś abstrakcyjny wynik. W Excelu data ma wymierną wartość liczbową w związku z tym należy uważać aby nie była ona w niezamierzony sposób sumowana z innymi liczbami bo drastycznie zaburzy nam to wyniki.

Zaokrąglenia

Kolejna suma dziesięciu jedynek daje 5 i nic nie wskazuje na to by gdzieś ukryty był tekst. Jednak stanięcie na jednej z jedynek ujawnia, że ukrywa się pod nią wartość 0,5

To przejaskrawiony przykład tego, że to co widzicie w arkuszu niekoniecznie jest tym co znajduje się w danej komórce. Formatowanie może istotnie zakłócić naszą percepcję w tym zakresie.  To co najczęściej nas spotka w praktyce prezentuje przykład poniżej.

Dziesięć modelowo sformatowanych liczb które w sumie rozjeżdżają się o 2 grosze. Przy tych liczbach możemy to zauważyć „na oko” ale wyobraźcie sobie sytuację, że każda z tych liczb ma groszową końcówkę a liczb jest kilkadziesiąt. Błąd takiego sumowania w zasadzie można zauważyć tylko porównując dane z innym systemem lub kalkulatorem. Przyczyną tego zjawiska jest fakt, iż niektóre z sumowanych liczb mają dalsze miejsca po przecinku niż widoczne dwa zgodnie z poniższym przykładem.

Dlatego jeżeli używacie Excela do obliczeń finansowych w których mnożycie / dzielicie liczby zawsze na końcu musicie zastosować funkcję zaokrąglenia do 2 miejsc po przecinku. Brak tego działania narazi was na błędy w obliczeniach i długotrwałe szukanie pojedynczych groszy w różnicach pomiędzy Waszymi wyliczeniami a danych uzyskiwanymi z systemów finansowych.

Precyzja

Poniżej przykład sumowania 3 liczb z których dwie mają te same wartości lecz ujemne znaki. Z matematyki wynika, że wynikiem powinna być trzecia liczba jednak nie zawsze się tak dzieje.

 

O dziwo wynik sumowania zależy od kolejności jego składników. Dzieję się tak z uwagi na wymierną precyzję obliczeń na naszych komputerach. Excel nie jest w stanie prezentować dużych liczb z nieograniczoną dokładnością więc jeżeli dodamy do takiej liczby bardzo mała wielkość zostanie ona pominięta. Równocześnie precyzja staje się większa gdy liczby stają się mniejsze więc w naszym przypadku gdy wynikiem pierwszego dodawania jest zero automatycznie wynikiem kolejnego staje się nasza trzecia bardzo mała liczba. Podobny efekt możecie zauważyć wpisując liczby do komórki w Excelu.

Choć bez problemu można wpisać liczbę z 12 zerami po przecinku

Scalanie komórek

Choć scalanie komórek jest szybko dostępne w Narzędziach głównych pod ikonką osobiście zalecam unikania tej formy prezentacji danych. Należy zawsze pamiętać, że scalone komórki otrzymują wartość komórki z lewego górnego narożnika scalenia. Próby odwoływania się do komórek scalonych mogą rodzić błędy w obliczeniach gdyż są zależne od metody którymi je wykonano. I tak: wskazanie H4 na komórkę H4 i rozciągnięcie tego obszaru w bok da nam w komórce I4 odwołanie do komórki I2 która nie ma własnej wartości i nie ma również wartości H2 pomimo tego iż jest z nią scalona. Natomiast stanięcie na komórce I5 wpisanie = i wskazanie na I2 automatycznie spowoduje wpisanie komórki H2. Uwierzcie że to może rodzić błędy w większych arkuszach.

Ukrywanie zawartości

Zdarza się, że chcemy ukryć jakieś współczynniki przed użytkownikami arkusza. Najprostszą do tego metodą wydaje się wpisanie tych wartości do osobnego arkusza i odwoływanie się do nich w obliczeniach a następnie ukrycie arkusza i założenia hasła na skoroszyt.

Faktem jest, że użytkownik, nie znając hasła nie może odkryć ukrytego arkusza. Wiele osób nie zwraca jednak uwagi na fakt, iż nie oznacza to, że nie może się do ukrytego arkusza odwołać. Widząc nazwę ukrytego arkusza w dostępnych formułach i mając możliwość działania w arkuszu który jest dla niego dostępny użytkownik może wywołać  dowolne komórki z naszego ukrytego arkusza.

Dlatego jeżeli zależy Wam na prawdziwym ukryciu jakichś informacji musicie stosować opcję Ukryj w zakładce Ochrona menu Formatowanie we wszystkich miejscach które odwołują się do poufnych wyliczeń oraz do ukrytego arkusza. Konieczne jest również blokowanie arkusza roboczego z hasłem aby użytkownik nie mógł go użyć jako narzędzia do ujawnienia danych z ukrytego arkusza.

Odwołania do zakresów danych

Nagminnie stosowanym przez użytkowników Excela w mojej dotychczasowej karierze zjawiskiem jest „rysowanie tabelek”. Narysowana tabelka do złudzenia przypomina prawdziwą lecz nie jest zdefiniowanym obiektem Tabela jaki możemy stworzyć poprzez Narzędzia główne -> Formatuj jako tabelę tylko odpowiednio dobranym formatowaniem pewnego obszaru komórek. Przykład poniżej

Oczywiście nie jest to jakieś karygodne rozwiązanie niemniej głównym problemem jaki się z nim wiąże jest brak nadążania obliczeń za dynamiką zmian takiego obiektu. W prawdziwej tabeli możemy zsumować wartości kolumn lub uzyskać sumy odfiltrowanych fragmentów danych. W „narysowanej tabeli” bazujemy zazwyczaj na sumowaniu konkretnego obszaru a efektem dodawania kolejnych linijek do naszej „tabeli” jest wynik pokazany na powyższym przykładzie tzn. obszar sumowania nie odpowiada obszarowi „tabeli”. Są to ciężkie do znalezienia błędy, szczególnie gdy arkuszem zajmuje się kilka osób. Praktycznie po każdym użyciu tabeli przez inne osoby należałoby sprawdzać czy odwołania do tego typu tabel są nadal aktualne.  Dlatego zdecydowanie zalecam definiujcie Tabele !

Bardzo przydatną funkcjonalnością jest możliwość zdefiniowania sobie nazwy dla pojedynczej komórki lub ich obszaru. Pomaga to istotnie w analizie formuł arkusza gdzie np. zamiast mnożenia przez komórkę D14 które nikomu nic nie mówi możemy przemnożyć przez „USD” które już sugeruje, że jest to kurs dolara. Dlaczego jednak na poniższym przykładzie kursy EUR i USD w wierszach 17 i 18 są takie same ?

Zwróćcie uwagę, że jednemu obszarowi / komórce można przypisać wiele nazw. Czyli nic nie stoi na przeszkodzie aby nazwę USD i EUR w szczególności przypisać do tej samej komórki D14. Taka pomyłka może być ciężka do znalezienia więc pamiętajcie, że wszystkie zdefiniowane nazwy znajdziecie w Menu -> Formuły -> Menadżer nazw.

Filtrowanie

W poprzednim temacie zachęcałem Was aby definiować Tabele, jednak i tu trzeba uważać na pułapki które mogą nam zaburzyć prawidłowe wyliczenia. Popatrzcie na poniższy przykład.

Mamy dwie tabele zdefiniowane obok siebie. Ktoś zastosował filtr w kolumnie Miasto co spowodowało dobre wyliczenie sumy częściowej w tabeli niebieskiej. Jednak ta operacja spowodowała również niezauważalne wycięcie z tabeli zielonej części wierszy i utworzenie sumy częściowej tylko widocznych elementów. Pamiętajcie żeby starać się nie umieszczać tabel obok siebie tylko jedna pod drugą. Jeżeli już musicie je umieścić obok siebie dbajcie o ich świadome filtrowanie i zwracajcie uwagę na fakt, że w danej chwili tylko w filtrowanej tabeli sumy mają sens. Z drugiej strony pamiętajcie że pola Sumy na dole tabel są Sumami Częściowymi czyli pokazują tylko wyfiltrowane w danym momencie wartości. Odwoływanie się do nich jako do Sum całej tabeli jak to ma miejsce w komórce D29 na powyższym przykładzie jest oczywistym błędem.

Wyszukiwanie

Na koniec chciałbym Wam jeszcze pokazać dlaczego zdecydowanie należy stosować funkcję X.Wyszukaj zamiast używanej dawniej funkcji Wyszukaj

W komórce D42 i D44 mamy wynik szukania wartości 40 w kolumnie „Wartość 1” oraz zwrócenia wartości z kolumny „Wartość 2”. W D42 szukamy za pomocą Wyszukaj w D44 za pomocą X.Wyszukaj. Gdy zbiory są uporządkowane od najmniejszej do największej wartości dla kolumny 1 oba wyszukiwania znajdują tę samą wartość 4. Jednak gdy do komórki A46 wpiszemy wartość 60 a do komórki A48 wartość 40 jak poniżej

Efekty naszych poszukiwań będą już drastycznie różne. Wyszukaj zakończy wyszukiwanie na 4 linijce naszej tabeli gdyż uzna, że nie znalazło wartości 40 bo już ma wartość 60 i zwróci poprzednią wartość czyli 3. Pamiętajcie proszę o tym i zdecydowanie używajcie funkcji X.Wyszukaj.

 

Mam nadzieję, że ten krótki przegląd ciekawostek które mogą Wam utrudnić życie z Excelem da wam szansę zaoszczędzić w przyszłości mnóstwo czasu na szukanie potencjalnych błędów.

Jak zawsze zapraszam Was do kontaktu na adres  konrad.pogodz@itch.pl  gdy traficie na jakieś ciekawe zagadnienie, błąd lub scenariusz z którym coś ewidentnie działa nie tak jak powinno.