10-rzeczy-ktore-sa-denerwujace-w-pracy-z-excelem-okladka

Top 10 problemów w pracy z Excelem

Top 10 problemów w pracy z Excelem

Poznaj 10 częstych i denerwujących problemów w pracy z arkuszami kalkulacyjnymi Excela 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 Excela 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. Czytanie dużych liczb

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 komórki z formułami muszą ulec przeliczeniu 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 Excela 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,416 (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.

8. Zmiana formatu po odświeżeniu tabeli przestawnej

Edycja Tabeli Przestawnej czy jej odświeżenie powoduje zresetowanie szerokości kolumn. Bywa to uciążliwe, szczególnie jeśli już ułożyliśmy wszystko i jedyne, co robimy, to wklejamy nowe dane i klikamy Odśwież.

Możesz wyłączyć dopasowanie szerokości Twojej Tabeli Przestawnej do danych:

  • wybierz dowolną komórkę, należącą do Tabeli Przestawnej,
  • wybierz pasek Analiza tabeli przestawnej,
  • wybierz Opcje,
  • na dole karty Układ i formatowanie odznacz opcję Automatycznie dopasuj szerokość kolumn podczas aktualizacji,
  • w tym miejscu możesz również włączyć na powrót tę funkcjonalność.

 

9. Błąd #REF po usunięciu komórek

Błąd #REF to zmora każdego, pracującego w Excelu. Nie mam tutaj dla Ciebie prostego rozwiązania. Przede wszystkim zastanów się trzy razy, zanim usuniesz jakiś arkusz. Od tego nie ma odwrotu i CTRL + Z tutaj nie zadziała. Warto zawsze zachować oryginalny plik przed takimi operacjami. Albo jak już się „wysypało” szybko zamknąć plik bez zapisu. To może uratować Ci skórę.

Na karcie formuł możesz znaleźć opcje Śledź poprzedniki Śledź zależności. Te funkcje mogą Ci nieco ułatwić decyzję, czy możesz bezpiecznie usunąć komórki. Polecam Ci też skorzystać ze skrótu klawiszowego CTRL+`. Pokazuje on formuły w Twoim arkuszu, więc nieco łatwiej będzie Ci zobaczyć co z czego wynika. Jeśli użyjesz skrótu ponownie, wrócisz do domyślnego widoku.

Sprawdź, co się da, zrób kopię zapasową i usuwaj 🙂

10. Brak możliwości wklejania do filtrowanego obszaru

Wielu narzeka na brak takiej możliwości i niestety trzeba z tym żyć. Jeśli chcesz coś dokleić do swojej tabeli, to znaczy, że masz jakąś inną tabelę, z której dane chcesz scalić z tą obecną. Skorzystaj z funkcji wyszukiwania takich, jak WYSZUKAJ.PIONOWO, INDEKS i PODAJ.POZYCJĘ, a może z PowerQuery, albo zrób relację w Tabelach Przestawnych, albo PoverPivot? Dzięki temu unikniesz wklejania danych w filtrowany obszar, a i sam plik będzie działał nieco szybciej.

Podsumowanie i rekomendacje

Niełatwo było wybrać tylko 10 uciążliwości Excela. Ma ich multum, ale grunt to je znać i umieć je obejść. Gorąco wierzę, że po przeczytaniu tego posta Twoje życie z tym programem stanie się jeszcze prostsze. Jeśli chcesz polubić się z Excelem, musisz też czasami uzbroić się w cierpliwość. Ten program, jak chyba każdy, ma swoje blaski i cienie.

Jeśli chcesz nauczyć się pracy z programem Excela od podstaw i umieć unikać wyżej wymienionych błędów, ale i nie tylko, sprawdź koniecznie ten link (kliknij tu). Powodzenia w zaprzyjaźnianiu się z arkuszami kalkulacyjnymi!

Michał Kowalczyk

Cała moja kariera zawodowa związana jest z cyframi i pracą z programem Excel. W każdej z firm, których pracowałem, byłem uznawany za specjalistę i „tego gościa od Excela”. Swoją wiedzą dzielę się z szerszym gronem przy pomocy Facebooka.

Zapis_na_Zaawansowany