Nauka Excela krok po kroku: od tabeli do raportu

Nauka Excela potrafi zamienić się w kolekcjonowanie zakładek. Tu film o funkcji, tam kurs o dashboardach, obok lista stu skrótów. Po miesiącu rozpoznajesz więcej nazw, ale nadal trudno Ci samodzielnie przygotować raport. Ja układam naukę wokół konkretnego zadania. Potrzebuję danych, pytania, wyniku i sposobu sprawdzenia, czy wynik ma sens.

Pokażę taki plan na prostym zestawieniu zamówień. Kolejne etapy prowadzą od porządnej tabeli przez formuły i podsumowania do powtarzalnego importu. Nie przypisuję im sztywnej liczby dni. Tempo zależy od tego, co już umiesz i ile okazji masz do samodzielnej pracy. Ważniejsze jest kryterium ukończenia etapu niż kolejny odhaczony odcinek.

Zacznij od pytania i małego zbioru

Na potrzeby ćwiczeń przyjmuję syntetyczne dane. Jeden wiersz oznacza jedno zamówienie z jednym produktem. Kwota to wartość całego zamówienia w jednej umownej jednostce. Nie ma tu podatków, zwrotów ani przeliczania walut. Takie uproszczenie pozwala skupić się na nauce, a wyniki policzyć w głowie.

Wpisz nagłówki do A1:E1, dane do wierszy 2:5. Pierwsze pytania brzmią: jaka jest łączna kwota, ile przypada na region i które zamówienia mają wartość co najmniej 30? Zanim użyjesz Excela, znasz odpowiedzi: 120 łącznie, 80 dla Północy, 40 dla Południa i dwa zamówienia spełniające próg.

Ta wiedza będzie Twoją kontrolą. Duży zbiór wygląda bardziej profesjonalnie, ale na początku utrudnia wykrycie pomyłki. Z czterema wierszami łatwo zauważyć, że po sortowaniu klient albo kwota odłączyły się od właściwego zamówienia. Dopiero po zrozumieniu mechanizmu zwiększaj liczbę danych.

Etap pierwszy: tabela, typy i filtrowanie

Zaznacz dane i utwórz tabelę przez Wstawianie, Tabela. W Excelu dla Windows służy do tego również Ctrl+T. Sprawdź, czy zaznaczenie obejmuje wszystkie kolumny i czy Excel rozpoznaje nagłówki. Nadaj tabeli nazwę, na przykład Zamowienia. W opisie odwołań strukturalnych Microsoft znajdziesz zasady używania nazw tabel i kolumn w formułach.

Następnie przefiltruj Region do Północy. Powinieneś zobaczyć Z01 i Z03. Usuń filtr, posortuj całą tabelę malejąco po Kwocie i sprawdź, czy Z03 nadal ma trzy sztuki i kwotę 60. Ten drobiazg jest ważniejszy niż eleganckie kolory nagłówków. Sortowanie oderwanej kolumny może zniszczyć związek między polami.

Rozróżniaj wartość od jej wyglądu. Ustawienie formatu walutowego zmienia prezentację liczby, a nie jej sens biznesowy. Kod produktu z zerami na początku może wymagać tekstu. Datę trzeba rozpoznać jako datę, a nie przypadkowy napis. Przy danych z eksportu zawsze obejrzyj kilka komórek i pasek formuły.

Etap uznaję za opanowany, kiedy potrafisz dopisać rekord, zmienić filtr i posortować tabelę bez uszkodzenia danych. Umiesz też powiedzieć, co oznacza jeden wiersz. Jeśli jeden plik zawiera raz zamówienie, a raz pozycję zamówienia, samo sprawne klikanie nie naprawi raportu.

Etap drugi: formuły i odwołania

Na początek wystarczą proste obliczenia i kilka funkcji. Nie ucz się całego katalogu. Dla kwot z naszego przykładu wpisz poza tabelą:

=SUMA(E2:E5)
=ŚREDNIA(E2:E5)

Otrzymasz 120 oraz 30. Angielskie odpowiedniki to SUM i AVERAGE. Dokumentacja SUM wyjaśnia sumowanie zakresu, a opis AVERAGE przypomina, że puste komórki w odwołaniu są pomijane, podczas gdy zero uczestniczy w średniej. Dlatego brakująca kwota i kwota równa zero wymagają różnych decyzji.

Naucz się także kopiować formułę. Gdy mnożysz wartość przez parametr zapisany w H1, odwołanie $H$1 utrzymuje ten sam adres podczas kopiowania. Odwołanie względne przesuwa się razem z formułą. Przed przeciągnięciem przez tysiąc wierszy skopiuj formułę o jeden wiersz i obejrzyj, co się zmieniło.

Język funkcji i separator argumentów zależą od ustawień. W polskich przykładach używam średników. W angielskich często spotkasz przecinki. Warto rozpoznawać obie nazwy, ale nie musisz zmieniać całego interfejsu, żeby rozpocząć naukę. Zapisuj sobie mały słownik funkcji, których rzeczywiście używasz.

Dobrym sprawdzianem jest wyjaśnienie formuły bez czytania jej znak po znaku. „Dodaję kwoty czterech zamówień” mówi więcej o zrozumieniu niż wyrecytowanie adresu zakresu. Jeśli nie potrafisz opisać mianownika średniej, wróć do danych i ustal, jakie obserwacje powinny do niego wejść.

Etap trzeci: warunki i słowniki

Do klasyfikacji zamówienia użyję JEŻELI. W dodatkowej kolumnie dla pierwszego wiersza wpisz:

=JEŻELI(E2>=30;"większe";"mniejsze")

Z01 dostanie etykietę „mniejsze”, Z02 „większe”. Granica jest częścią reguły: kwota równa 30 ma trafić do większych. Microsoft opisuje JEŻELI jako wybór między dwoma wynikami na podstawie testu logicznego. W ćwiczeniu zmień próg i sprawdź wiersz równy nowej granicy.

Osobnym problemem jest pobieranie informacji ze słownika. Kod produktu w zamówieniu może wskazywać jego nazwę w cenniku. Do tego przydają się WYSZUKAJ.PIONOWO i X.WYSZUKAJ. Zanim poznasz kolejną funkcję, sprawdź, czy w słowniku jest jeden właściwy wiersz na klucz. Dwie ceny dla tego samego kodu wymagają reguły wyboru.

X.WYSZUKAJ domyślnie szuka dokładnego dopasowania i pozwala wskazać osobno kolumnę klucza oraz wyniku. Microsoft zaznacza, że funkcji nie ma w Excelu 2016 i 2019. Gdy uczysz się na pliku współdzielonym, najpierw ustal wersje używane przez odbiorców. Nowoczesna formuła niewiele daje, jeśli druga osoba nie może jej obliczyć.

Nie zamieniaj automatycznie każdego braku dopasowania na zero. Brak produktu w cenniku oznacza coś innego niż produkt z ceną zero. Zapisz, jak raport ma komunikować brak danych i kto powinien go wyjaśnić. To dobry moment, żeby zacząć uczyć się również diagnozowania błędów.

Etap czwarty: podsumowania z warunkami

Kiedy prosta suma jest zrozumiała, dołóż warunek. Dla Północy formuła wygląda tak:

=SUMA.WARUNKÓW(E2:E5;B2:B5;"Północ")

Wynik to 80, ponieważ biorę 20 z Z01 i 60 z Z03. Zakres sumowania i zakres regionów obejmują te same wiersze. Opis SUMA.WARUNKÓW pokazuje kolejność argumentów i sposób dokładania kolejnych par zakresu oraz kryterium.

Na tej samej zasadzie ucz się LICZ.WARUNKI i ŚREDNIA.WARUNKÓW. Za każdym razem najpierw ustal pytanie. Liczba wierszy z regionem Północ to dwa zamówienia, suma kolumny Liczba to pięć sztuk, a suma Kwoty to 80. Te trzy liczby odpowiadają na różne pytania, chociaż wszystkie dotyczą tej samej grupy.

Jako ćwiczenie dodaj warunek produktu Teczka. W Północy zostaje wtedy jedno zamówienie za 60. Potem zmień produkt w jednym rekordzie i przewidź wynik przed przeliczeniem. Takie drobne zmiany uczą więcej niż bezbłędne przepisanie gotowej formuły z filmu.

Etap piąty: tabela przestawna i wykres

Z tabeli zamówień utwórz tabelę przestawną. Region umieść w wierszach, a Kwotę w wartościach. Sprawdź nazwę obliczenia: potrzebujesz sumy, a nie liczby rekordów. Wyniki powinny zgadzać się z wcześniejszym sprawdzeniem: Północ 80, Południe 40. Procedurę tworzenia i odświeżania opisuje instrukcja Microsoft.

Teraz dodaj Produkt pod Regionem. Zobacz, jak zmienia się poziom szczegółowości. Nie dopisujesz nowej funkcji dla każdej kombinacji, tylko zmieniasz układ podsumowania. Dopisz do źródła nowe zamówienie i odśwież tabelę przestawną. Nie zakładaj, że widoczny raport sam już uwzględnia ostatnią zmianę źródła.

Do porównania dwóch regionów wybrałbym prosty wykres słupkowy. Podpisz jednostkę i okres danych. Pokaż informację, dla której wykres powstał, zamiast demonstrować liczbę dostępnych efektów. Jeżeli dane obejmują tylko część miesiąca, czytelnik powinien zobaczyć to od razu, zanim zacznie porównywać wynik z pełnym poprzednim miesiącem.

Po tym etapie umiesz dojść do tego samego podsumowania dwiema drogami i wyjaśnić rozbieżność. To praktyczna umiejętność kontroli raportu. Gdy wyniki się różnią, sprawdź zakres źródła, filtry, wybraną agregację i stan odświeżenia. Dokładanie kolejnego wykresu poczeka.

Etap szósty: arkusz dla innej osoby

Kiedy ktoś poza Tobą wpisuje dane, ważne stają się przewidywalne wartości. Dla Regionu przygotuj listę Północ i Południe, a następnie użyj poprawności danych z typem Lista. Instrukcja tworzenia listy rozwijanej opisuje wybór źródła i komunikat przy niewłaściwym wpisie. Lista ogranicza część literówek, ale nie zastępuje kontroli całego importu.

Oddziel pola do wpisywania od obliczeń i od wyniku. Dodaj krótką instrukcję: co uzupełnić, gdzie są dane źródłowe, co odświeżyć. Nie oczekuj, że kolor żółty będzie dla wszystkich oczywistym sygnałem do edycji. Wyjaśnij go tekstem, zwłaszcza gdy inne kolory oznaczają statusy zamówień.

Formatowanie warunkowe stosuję do konkretnego problemu, na przykład wskazania brakującego kodu. Nie potrzebuję tęczy na każdej kolumnie. Reguła ma kierować do działania: popraw dane, sprawdź przekroczony próg albo przeczytaj komentarz. Jeżeli wszystkie komórki wołają o uwagę, odbiorca szybko przestaje na nie patrzeć.

Przekaż kopię ćwiczenia komuś, kto nie zna Twojego zamysłu, albo otwórz ją po kilku dniach bez zaglądania do notatek. Spróbuj wykonać raport wyłącznie na podstawie instrukcji w pliku. Miejsca, w których trzeba zgadywać, są następnym zadaniem do poprawienia.

Etap siódmy: powtarzalny import w Power Query

Jeżeli co tydzień pobierasz podobny CSV, naucz się importować go przez Power Query. Ustal separator, kodowanie i typy kolumn, usuń niepotrzebne pola, a następnie załaduj wynik. Microsoft opisuje Power Query jako proces łączenia ze źródłem, przekształcania, łączenia danych i ładowania z możliwością odświeżania.

Dla ćwiczenia przygotuj dwa małe eksporty o tym samym układzie, ale innych rekordach. Zapisz kroki importu dla pierwszego i sprawdź, czy drugi przechodzi tą samą drogą. Osobno porównaj liczbę wierszy i sumę kwot. Jeśli zmieniła się nazwa kolumny albo format daty, chcesz to zauważyć, zanim pokażesz wynik.

Dostępność konektorów oraz funkcji różni się między platformami i wersjami. Dlatego opisuj swoje środowisko, zamiast obiecywać, że każdy przycisk będzie identyczny na Windowsie, Macu i w przeglądarce. Więcej o samym imporcie znajdziesz w poradniku CSV.

Automatyzacja i plan samodzielnej pracy

VBA możesz poznać wtedy, gdy masz konkretną czynność do automatyzacji. Nie traktuję go jako obowiązkowego egzaminu kończącego naukę Excela. Część problemów rozwiążą tabele, formuły i Power Query. W innym środowisku przydadzą się Office Scripts. Porównanie Microsoft wyjaśnia różnicę między desktopowym zakresem VBA i podejściem Office Scripts do pracy między platformami.

Najpierw opisz proces zwykłymi zdaniami: skąd biorę dane, jakie kroki wykonuję, jakie pliki oddaję i jak rozpoznaję błąd. Dopiero potem wybierz narzędzie. Automatyzowanie niezrozumiałego procesu pozwala szybciej powtarzać tę samą pomyłkę. Szczególnie pilnuj sytuacji, w których rekord zostaje pominięty bez komunikatu.

Na każdą sesję nauki wybierz jeden rezultat, na przykład działający filtr, poprawne wyszukanie ceny lub odświeżenie importu. Zostaw sobie plik przed zmianą i po zmianie. Dopisz jedno zdanie o problemie, który rozwiązałeś, oraz jedno o ograniczeniu. Taki zbiór ćwiczeń będzie później lepszym przypomnieniem niż katalog certyfikatów.

Na koniec zbuduj raport z nowych danych bez otwierania poprzedniego rozwiązania. Możesz korzystać z dokumentacji, tak jak w normalnej pracy. Sprawdź sumy, dopisz brakujący produkt, zmień region i poproś plik o nowe podsumowanie. Kiedy potrafisz wyjaśnić wynik i poprawić go po zmianie założeń, masz konkretny dowód postępu. Kolejny temat dobierz do miejsca, w którym nadal potrzebujesz pomocy.

Jak wybrać ćwiczenie i materiał do nauki

Przy wyborze kursu zajrzyj do zadań, zanim obejrzysz zapowiedź wszystkich modułów. Szukam materiału, który daje dane wejściowe i pytanie, a potem pozwala samodzielnie podjąć decyzję. Jeżeli każde kliknięcie jest pokazane z góry, łatwo pomylić rozpoznawanie ekranu z umiejętnością pracy. Dobre ćwiczenie powinno dać się zrobić ponownie z inną nazwą produktu, innym progiem albo dodatkowym rekordem.

Zwróć uwagę na omawianą wersję programu. Starszy materiał może świetnie tłumaczyć odwołania i nadal być przydatny, choć nie pokaże nowych funkcji. Z kolei nowy kurs może opierać się na narzędziu niedostępnym w Twojej pracy. Nie odrzucam automatycznie żadnej z tych sytuacji. Chcę wiedzieć, co z lekcji zastosuję od razu, a co zostawię na później.

Oceń także sposób wyjaśniania błędów. Czy prowadzący pokazuje, jak dojść do źródła złej wartości? Czy tłumaczy, dlaczego dany rekord należy do grupy? Czy rozdziela liczbę zamówień od liczby sztuk? Taki materiał daje Ci metodę myślenia, którą przeniesiesz do własnego pliku. Sam gotowy dashboard przenosi się znacznie trudniej, kiedy zmieniają się kolumny i pytania.

Podczas ćwiczenia zostaw sobie czas na celowe zepsucie danych. Usuń jeden kod ze słownika. Dopisz produkt zapisany z dodatkową spacją. Wprowadź brakującą kwotę i sprawdź, czy nadal umiesz opowiedzieć o średniej. Nie chodzi o konkurs na najwięcej błędów, tylko o poznanie granic własnego rozwiązania. W normalnej pracy dane też nie pytają, czy jesteś już gotowy na trudniejszy wariant.

Przy każdym raporcie zapisz odbiorcę i decyzję, którą ma podjąć. Innego układu potrzebuje osoba sprawdzająca pojedyncze zamówienia, a innego ktoś porównujący regiony. Dzięki temu łatwiej odpuścisz funkcję, która jest efektowna, ale niczego w tym zadaniu nie poprawia. Nauka ma kolejność, lecz nie musi zamieniać się w marsz przez wszystkie zakładki wstążki.

Jeśli utkniesz, zapisz najkrótszy opis problemu: oczekiwałem 80, dostałem 40, źródło ma cztery zamówienia, filtr pokazuje dwa. Taki komunikat porządkuje myśli i ułatwia szukanie pomocy. Czasami już przy jego pisaniu zauważysz, że patrzysz na niewłaściwy region. To również jest postęp, choć nie kończy się poznaniem żadnej nowej funkcji.

Materiały wideo do poszczególnych etapów

Do wybranych kroków zachowuję osobne materiały z oryginalnego poradnika. Możesz otworzyć ten, który odpowiada aktualnemu ćwiczeniu, a potem wrócić do własnego pliku. Przykłady w tym artykule są samodzielne; filmy stanowią dodatkową ścieżkę nauki.