Niektórzy twierdzą, że znajomość funkcji i formuł tablicowych jest, zaraz przed makropoleceniami i VBA, najbardziej zaawansowaną funkcjonalnością programu MS Excel. Pewnie poznałeś już podstawowe i zaawansowane funkcjonalności takie jak funkcje, wykresy czy tabele przestawne.
Sprawdź 15 funkcji Excel, które musisz znać.
Być może ta wiedza i umiejętności w zupełności wystarczają Ci w Twojej pracy. A może wciąż czujesz, że mógłbyś pewne formuły zapisać sprawniej i prościej. Zastanawiasz się czy dałoby się napisać jedną sprytną formułę zamiast dodawać kolejne kolumny do przeprowadzenia prostej analizy? Okazuje się, że z pomocą przychodzą nam tablice i formuły tablicowe.
Formuły tablicowe to nie jest odrębna grupa funkcji Excel. Jest to zupełnie inne podejście do użycia funkcji programu. Bazujemy na nowym obiekcie - tablicy. Stosując wybraną funkcję, lub kombinację funkcji do całej tablicy danych możemy wykonać wiele operacji przy użyciu zwięzłych i zrozumiałych formuł.
Formuł tablicowych można użyć do wielu z pozoru skomplikowanych zadań, takich jak:
- Zliczanie znaków znajdujących się w zakresie komórek.
- Sumowanie tylko liczb spełniających określone kryteria, takich jak większe od zera lub różne od zera.
- Sumowanie co n-tej wartości w zakresie wartości.
Najważniejsze w całej zabawie jest to, że nie musimy w żaden sposób modyfikować naszych danych, dodawać nowych kolumn z dodatkowymi obliczeniami. Wystarczy sprytna formuła w jednej komórce.
Prosty przykład formuły tablicowej
Załóżmy, że naszym zadaniem jest obliczenie maksimum z wielkości powstałej po przemnożeniu jednej kolumny przez drugą. Wtedy możemy użyć zwykłej funkcji MAX. Jako argumenty podamy wprost mnożenie dwóch kolumn.
W takiej wersji ta formuła zwróci błąd. Jednak, gdy zatwierdzimy ją kombinacją klawiszy CTRL + SHIFT + ENTER, to formuła zadziała już poprawnie. Możesz sprawdzić wykonując ręcznie mnożenie poszczególnych wartości 🙂 Dla której pozycji wartość sprzedaży jest największa?
Spójność formuł
Pierwszym powodem, dla którego warto stosować formuły tablicowe jest dodatkowa spójność. Możesz stosować funkcję SUMA.JEŻELI wskazując pojedyncze kryterium. Ale możesz też wskazać od razu cały zakres kryteriów dla wszystkich rekordów/wierszy, w których zamierzasz umieścić funkcję. W ten sposób w każdej komórce jest dokładnie taka sama formuła. Jest ona przez to dużo prostsza w analizie dla całego zakresu.
Pamiętaj! Taka formuła zadziała dopiero po zatwierdzeniu kombinacją klawiszy CTRL + SHIFT + ENTER.
Bezpieczeństwo
Używając formuły tablicowej do całego wybranego zakresu masz ograniczone możliwości modyfikacji formuły w poszczególnych komórkach. Każdą zmianę musisz ponownie zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER. W ten sposób zmiany mają zastosowanie do całego zakresu, do którego odnosi się tablica i formuła tablicowa. Twoja formuła jest dodatkowo zabezpieczona przed niepożądanymi zmianami w wybranych komórkach.
Przejrzystość formuł
Spójrz jeszcze raz na pierwszy przykład z funkcją MAX zastosowaną do operacji mnożenia na kolumnach. Dzięki takiemu zabiegowi od razu widać co się dzieje w formule. Nie potrzebujemy żadnych dodatkowych kolumn, w których mnożylibyśmy przez siebie dwie inne kolumny. Wystarczy taką operację wykonać bezpośrednio w formule.
Innym przykładem jest użycie warunków, funkcji JEŻELI, zagnieżdżonej w funkcji MIN. W poniższym przykładzie wyznaczamy minimalną wartość z kolumny z pominięciem wartości równych zero. Warunek stosujemy do całej kolumny. Proste i zrozumiałe, prawda? 😉
Mniejszy rozmiar plików
Na końcu wisienka na torcie. Używanie formuł tablicowych może znacząco zmniejszyć rozmiar Twojego pliku! To prawda. Jest tak dlatego, że nie potrzebujemy tworzyć żadnych dodatkowych kolumn z obliczeniami pomocniczymi. Wystarczy jedna formuła. Excel wykonuje obliczenia pomocnicze na tablicach w pamięci. Nie zapisuje ich w postaci wartości i formuł w komórkach arkusza.
Ponadto, plik, w którym używasz formuł tablicowych szybciej się otwiera i odświeża. Jest to szczególnie ważne w pracy z dużymi zbiorami danych. Zastąpienie klasycznych formuł i części obliczeń pomocniczych za pomocą formuł tablicowych przynosi duże korzyści. Oczywiście tam gdzie takie zastąpienie ma sens i jest właściwe.
Jestem ciekawy czy używasz w swojej pracy formuł tablicowych. A może jest to pierwszy raz, gdy się z nimi spotykasz. Te i wiele innych przykładów zastosowania formuł tablicowych znajdziesz w kursie Excel Formuły Tablicowe.
Jeśli szukasz więcej zaawansowanych funkcjonalności, to zapraszam na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.
Dzięki za wyjaśnienie. Drobna uwaga: ,,poŻądany”, przez ,,Z z kropką”.
Pozdrawiam,
Bardzo dobra uwaga 🙂 Już poprawione!