Poznaj 10 częstych i denerwujących problemów w pracy z arkuszami kalkulacyjnymi Excel i dowiedz się jak sobie z nimi poradzić.
1. FORMATY PRZY WYSZUKAJ.PIONOWO
Najpopularniejsza według strony Microsoftu funkcja szukania nie zadziała, jeśli dane w tabelach będą traktowane przez program Excel w różny sposób. Brzmi jak enigma, nie? Chodzi o to, że arkusze kalkulacyjne mogą traktować liczby jako wartości tekstowe. Są dwa omeny takiej sytuacji:
- zielony trójkąt w lewym górnym rogu komórki, która zawiera liczby traktowane jako tekst
- domyślne wyrównanie takich danych w komórce do lewej krawędzi
Funkcja WYSZUKAJ.PIONOWO porównuje ze sobą dane, a następnie zwraca nam wartość z wybranej przez nas kolumny. Jeśli kolumna, której używamy do porównywania (w przykładzie kolumna ID), jest traktowana w jednej z tabel jak tekst, a w drugiej jako liczba, pojawia się błąd #N/D. Komunikat ten oznacza brak danych. Excel nie może ich znaleźć, bo liczba nie równa się tekst.
Jak to naprawić? Sposobów jest kilka. Jeden z nich to:
- zaznacz kolumnę z danymi liczbowymi traktowanymi jako tekst,
- kliknij wykrzyknik,
- skonwertuj tekst na liczby.
2. SZUKANIE TYLKO W JEDNĄ STRONĘ
Życie to nie bajka, a WYSZUKAJ.PIONOWO, mimo swojej popularności, nie jest idealną formułą do wyszukiwania. Jej największą wadą jest brak możliwości wyszukiwania w lewą stronę (a przynajmniej nie da się tego zrobić w prosty sposób, bo ten problem można obejść, zagnieżdżając formuły).
Co to znaczy, że nie szuka w lewo? Ano kolumna, po której porównujemy nasze tabele, musi być z lewej względem kolumny, z której dane chcemy otrzymać. Na poniższym przykładzie obrazuję ten problem. Najprostszym rozwiązaniem będzie przesuniecie kolumny ID przed kolumnę VAT. Jeśli jednak mamy obostrzenia w pracy na pliku i nie możemy tego zrobić, proponuję skorzystać z funkcji INDEKS i PODAJ.POZYCJĘ albo zespolić tabele przy użyciu PowerQuery (dla Excela 2010 i nowszych).
3. PREZENTACJA DUŻYCH LICZB W ARKUSZU EXCEL
Dopóki pracujemy na setkach czy tysiącach, czytanie liczb nie stanowi problemu. Co jednak jeśli zmieniamy rząd wartości na setki tysięcy, miliony czy ich dziesiątki? Liczba cyfr zdecydowanie utrudni nam szybkie i sprawne przeczytanie wartości z komórki.
Na wstążce w zakładce Narzędzie główne możesz znaleźć grupę Liczba, która pozwala zarządzać formatowaniem wartości. Pamiętaj, że to tylko format. Nie wpływa w żaden sposób na obliczenia, a jedynie na sposób prezentacji danych dla naszych oczu.
Z grupy tej możesz korzystać przy użyciu myszy, rozwijając i wybierając domyślne formaty (np. procenty, liczby czy daty) albo skorzystać z formatowania niestandardowego, które umożliwi Ci dodanie separatorów tysięcy. Ta opcja dodaje spację pomiędzy setkami i tysiącami oraz pomiędzy setkami tysięcy i milionami i tak dalej. Tak sformatowane liczby czyta się zdecydowanie łatwiej.
Zwróć uwagę, że w przykładzie nie użyłem myszy. Owszem! Możesz zastosować to formatowanie przy użyciu skrótów klawiszowych. Wygląd formatów jest zależny od ustawień Twojego systemu Windows.
- CTRL + SHIFT + ` – formatowanie ogólne
- CTRL + SHIFT + 1 – formatowanie liczbowe z separatorem tysięcy (to, które widzisz na przykładzie)
- CTRL + SHIFT + 2 – formatowanie godzin
- CTRL + SHIFT + 3 – formatowanie dat
- CTRL + SHIFT + 4 – formatowanie walutowe
- CTRL + SHIFT + 5 – formatowanie procentowe
Jak zapamiętać aż tyle skrótów? Spójrz na swoją klawiaturę: CTRL + SHIFT + 5 – nad piątką znajduje się symbol procenta. Już widzisz skojarzenie? 4 to dolary, a więc waluty. 3 to symbol # więc trochę jak kartka z kalendarza. 2 to @, jeśli uruchomisz wyobraźnię, to przypomina zegar. Zostaje nam już tylko ! nad 1, a więc CTRL + SHIFT + 1 to formatowanie z poniższego przykładu.
4. NIEPRZELICZAJĄCE SIĘ FORMUŁY
To było moje ulubione pytanie zadawane przez kolegów, gdy jeszcze pracowałem na etacie: „Czemu formuły mi się nie przeliczają?”. A no nie przeliczają Ci się, bo masz włączone przeliczanie ręczne. Będą się przeliczać jeśli wywołasz przeliczenie np. skrótem F9 lub przełączysz sposób przeliczania na automatyczny. Problematyczny, ale i prosty przypadek ?
Jak przełączyć sposób przeliczania?
- Wybierz kartę formuły,
- rozwiń opcje obliczania,
- wybierz przeliczanie, które Cię interesuje.
5. DŁUGO OBLICZAJĄCE SIĘ PLIKI
Skoro jesteśmy już przy przeliczaniu, istnieją pliki, które potrafią przeliczać się godzinami. Tak, dobrze czytasz ? I właśnie po to jest nasze przeliczanie ręczne.
Jeśli pracujesz na dużym pliku, powiedzmy 50 tys. wierszy i więcej, i chcesz dodać do niego kolumnę z przeliczeniem, jego responsywność może się obniżyć. Chodzi o to, że Excel przelicza komórki z formułami za każdym razem, gdy dokonasz edycji jakiejkolwiek komórki. Im więcej takich komórek tym proces przeliczenia trwa dłużej. Oczywiście zależy to też od wydajności Twojego komputera, ale ten aspekt możemy pominąć. Dobrze zrobiony plik będzie działał znośnie na komputerach kupionych po roku 2010. Aby uniknąć czekania po każdej edytowanej komórce, włącz przeliczanie ręczne. Wiesz już jak to zrobić, a jeśli nie – przeczytaj opis problemu 4.
Włączone przeliczanie ręczne umożliwi Ci decydowanie kiedy Twój plik ma zacząć kalkulację. Możesz dokonać części zmian, a następnie uruchomić obliczenia. To zdecydowanie wydajniejszy sposób pracy niż czekanie na formuły po każdej edycji komórki. Warto też zastanowić się nad doborem rozwiązań technicznych. Jeśli pracujesz na dużych plikach, pomyśl o PowerQuery i Tabelach Przestawnych. Te narzędzia działają zdecydowanie szybciej niż formuły i przy tego rodzaju operacjach mocno podniosą tempo Twojej pracy.
A przy okazji, jeśli myślisz o nauczeniu się pracy z Tabelami Przestawnymi od A do Z – koniecznie sprawdź ten link (kliknij tu). To sprawdzone źródło wiedzy z tego zakresu ?
6. DATY TRAKTOWANE JAKO TEKST
Kolejna częsta bolączka nas, pracujących w Excelu. Ten program traktuje daty jako liczbę dni, która upłynęła po 1 stycznia 1900 roku. Dzięki takim podejściu do dat twórcy programu Excel umożliwiają nam łatwe wykonywanie operacji na nich. Jeśli chcemy policzyć datę, która nastąpi za 7 dni od dziś, wystarczy, że do daty dnia dzisiejszego dodamy 7. Każdy dzień to 1. Godziny i minuty są częściami doby, więc są to liczby po przecinku. 1/24 czyli 0,0416 (w przybliżeniu) to 1 godzina. Wartość 0,5 to 12:00. Minuta to 1/1440 dnia i działa analogicznie do godzin.
Co jeśli nasza data jest traktowana jako tekst?
Możesz ją naprawić przy użyciu opcji tekstu do kolumn:
- zaznacz kolumnę, w której są wadliwe daty,
- wybierz kartę Dane,
- następnie Tekst do kolumn,
- pomiń dwa pierwsze okna poprzez kliknięcie dalej,
- w ostatnim oknie zaznacz datę i wybierz jej układ (w przykładzie DMR, czyli dzień, miesiąc, rok)
- i gotowe, od teraz możesz wykonywać operacje na swoich datach.
7. ODWOŁANIE DO TABELI PRZESTAWNEJ
Wejdźmy na moment do Tabeli Przestawnej. Domyślnie, jeśli chcesz odwołać się do danych, które znajdują się w jej obrębie, zobaczysz funkcję WeźDaneTabeli.
Co jednak jeśli uparliśmy się i nie chcemy automatycznie jej dostawać przy odwoływaniu się do Tabeli Przestawnej?
- Zaznacz dowolną komórkę Twojej Tabeli Przestawnej,
- wybierz kartę Analiza tabeli przestawnej,
- rozwiń Opcje
- odznacz Generuj funkcję WeźDaneTabeli
- od tego momentu funkcja przestaje być generowana, możesz ją ponownie włączyć w ten sam sposób.
Spróbujmy zrozumieć działanie funkcji WeźDaneTabeli. Działa ona trochę tak, jakbyśmy grali w statki. Spójrzmy na składnię z przykładu:
=WEŹDANETABELI(„Wynik”;$A$1;”Nazwisko”;”Hajto”)
Pierwsza część funkcji informuje nas, z którego pola są pobierane dane. Chcemy uzyskać wartości z komórki B2, w której znajdują się dane wynikowe dla pierwszego nazwiska.
Druga część formuły wskazuje komórkę, od której zaczyna się nasza Tabela Przestawna. To na wypadek, gdybyśmy mieli ich więcej w pliku.
Trzecia część formuły mówi, że dane mają zostać pobrane na podstawie nazwiska, a czwarta, że tym nazwiskiem jest Hajto. Jeśli zestawimy ze sobą wszystkie części formuły, wychodzi nam proste zdanie: Pobierz wartości z kolumny wynik, z tabeli, która zaczyna się w A1, dla nazwiska Hajto.
