5 trików w Excelu, które warto znać
Znajdowanie różnic pomiędzy zbiorami danych w 3 sekundy, tworzenie listy numerycznej na 3 różne sposoby czy dodawanie 3000 wierszy naraz – to tylko 3 z 5 trików, jakie zaprezentuję w niniejszym artykule. Warto je znać, by maksymalnie zautomatyzować swoją codzienną pracę na dużych zbiorach danych.
Znajdowanie różnicy w zbiorach danych
Spójrzmy na zestaw danych na Rysunku 1. Prezentuje on dwie kolumny. Wyobraźmy sobie, że zamiast kilkunastu wierszy mamy 500 wierszy, z czego wiemy, że pewna część danych w drugiej kolumnie jest niepoprawna. To mogą być różne typy danych i sytuacje. Chcemy jak najszybciej znaleźć różnice. Jak najłatwiej to zrobić? Wykonaj następujące kroki:
- Zaznacz zakres danych.
- Zastosuj skrót klawiaturowy CTRL + \
Po zastosowaniu skrótu Excel zaznaczy wszystkie komórki, które zawierają inną wartość względem referencyjnej kolumny.
Tworzenie listy numerów
Pierwszy trik na rozgrzewkę mamy za sobą. Teraz sprawdźmy, w jaki sposób możemy tworzyć listę numeryczną. Sposobów mamy minimum 3, zależnie od poziomu zaawansowania użytkowania. Spójrzmy, jak może sobie z tym poradzić osoba początkująca. Wpiszmy odpowiednio „1” i „2” w pierwszych dwóch wierszach, a następnie przeciągamy listę w dół do nr 20. To pierwszy sposób. Osoba średniozaawansowana najprawdopodobniej wykona inne, następujące kroki:
- Wpisze „1” w pierwszym wierszu.
- Przeciągnie listę w dół.
- Na dole pojawi się ikona z opcjami. Spośród tych opcji wybierze „Fill Series”.
Osoba zaawansowana może wykonać to samo zadanie w jeszcze inny sposób:
- Ustawiamy się w pierwszym wierszu.
- Wciskamy po kolei ALT H F I S, by otworzyć okienko edytora serii (lub szukamy go następująco: Home → Editing → Fill → Series).
- Wybieramy „Series in = Columns”, step value 1, stop value 20, klikamy OK.
Automatyczna lista TOP 5
Często w controllingu czy generalnie w finansach musimy stworzyć listę TOP 5, np. pięciu najwyżej marżowych projektów czy usług. Spójrzmy na poniższy przykładowy zestaw danych. Prezentuje on listę sprzedawców i osiągniętą przez nich sprzedaż. Chcemy zbudować automatyczną listę TOP 5 sprzedawców. Co możemy zrobić? Możemy oczywiście zestaw danych po prostu posortować, lecz to nie będzie optymalny sposób. Dlaczego? Przede wszystkim ingerujemy wówczas w pierwotny zestaw danych. Po drugie, jeśli do tego zestawu byłyby dobudowane kolumny z formułami (blokujące komórki) to automatycznie nasze sortowanie „wysypie” formuły. Po drugie, dodając dane za każdym razem należałoby dane ponownie sortować, a tego chcemy uniknąć. Oczekujemy w pełni automatycznego raportu. Po trzecie, sortując uzyskamy faktycznie TOP 5, ale co, jeśli chcemy zobaczyć TOP 25 czy TOP 100? W takim momencie lepiej mieć osobny raport niż sortować dane i wybierać TOP 100.
Z tego względu obok zestawu danych zbudujemy bardzo prosty raport. W pierwszej kolumnie wprowadźmy listę od 1–5 (znamy już na to min. 3 sposoby zaprezentowane powyżej). W drugiej kolumnie wykorzystamy funkcję LARGE, która jest niezwykle prosta w użyciu. Zgodnie z rys. 5 wystarczy w pierwszym argumencie zaznaczyć zakres danych, który byśmy normalnie sortowali (a w tym przypadku chcemy tylko 5 największych liczb). Drugi argument to zakres ze stworzoną przez nas listą od 1–5. Klikamy Enter i nasz mini raport jest już prawie gotowy. Czego brakuje? Oczywiście przypisania sprzedawcy. Bez tego raport jest niepełny. Co robimy? Używamy dobrze znanej już funkcji XLOOKUP, wybierając w pierwszym argumencie kwotę z kolumny F. Naszym lookup array jest oczywiście kolumna C, a return array kolumna B. Tak oto otrzymujemy automatyczny raport ukazujący TOP 5 sprzedawców (Rysunek 5).
Wstawianie 3000 wierszy w 10 sekund
Jak wiele razy spotkałeś/-aś się z irytującym problemem w postaci dodania wierszy między danymi z zakresu? Tak, to ten moment, gdy skróty klawiaturowe są na wagę złota, zwłaszcza jeśli masz nietypowy problem – musisz dodać wiele wierszy naraz, np. 3000. Jak sobie poradzić z takim zadaniem? Bardzo prosto. Wykonaj następujące kroki:
- Kliknij na komórkę w wierszu, od którego chcesz dodać wiersze, np. A10.
- W polu zaznaczonym na rys. 6 wpisz „A3009”.
- Użyj skrótu klawiszowego SHIFT + ENTER, by zaznaczyć cały zakres od wiersza A9–A3009.
- Użyj kolejnego skrótu, tym razem SHIFT + SPACE, by zaznaczyć całe wiersze w danym zakresie.
- Kliknij prawy przycisk myszy.
- Wciśnij Insert.
Excel wstawił dokładnie 3000 wierszy naraz.
Menedżer nazw
Ostatni trik Excelowy, o którym chciałbym wspomnieć, to menedżer nazw. W Excelu można sobie ułatwić życie na wiele sposobów. Jednym z nich jest właśnie menedżer nazw. Jak go mądrze wykorzystać? Na przykład tak jak na rys. 7 i 8. Załóżmy, że chcemy użyć menedżera dla naszego kursu EUR/USD. W tym celu wpisujemy kurs w komórce C2, a następnie w lewym górnym rogu wprowadzamy wartość tekstową FX. Każda komórka ma swoją nazwę, a my jedynie zamieniamy C2 na FX, co spowoduje, że za każdym razem, gdy w formule (jak na rys 8) wprowadzimy wartość FX, to Excel automatycznie zrozumie, że chodzi nam o wartość z komórki C2. Dlaczego to doskonałe narzędzie? Przede wszystkim dlatego, że uwalnia nas od konieczności blokowania komórek lub zakresu. Jest to zwłaszcza istotne, gdy w wielu miejscach w pliku odnosimy się do tej jednej wartości, a jak wiemy, jeśli musimy wykazać rachunek zysków i strat w dwóch walutach, to zdecydowanie lepiej użyć menedżera nazw niż w każdej formule referować do zablokowanej komórki C2.
Podsumowanie
Każdy z opisanych trików pomaga oszczędzić czas, co w dłuższym terminie jest niezwykle istotne. Najczęściej z powyższych tworzymy listy numeryczne czy dat. Natomiast dla szybkich check najważniejszy powinien być pierwszy trik. Jeśli pracujesz w controllingu czy analizach – zdecydowanie zapamiętaj funkcję menedżera nazw oraz funkcję LARGE (i SMALL, która zwraca 5 najniższych wartości).
