W Excelu trafiamy czasem na bardziej skomplikowane obliczenia, które wymagają operacji na tablicach danych, czyli tzw. formuły tablicowe. W tym artykule pokażemy kilka przykładów, kiedy mogą być użyteczne.

Formuły tablicowe w Excelu

Suma wszystkich przychodów

Zacznijmy od prostego przykładu – mamy tabelkę sprzedaży, gdzie widzimy odpowiednio kolumny Produkt, Cenę i Ilość (rys. 1). Dodatkowo mamy kolumnę z obliczonym przychodem, czyli po prostu przemnożoną wartością z kolumny Cena razy wartość z kolumny Ilość.

Nas nie interesuje pojedyncza komórka przychodu, ale przychód całkowity. Ponieważ przychód już jest obliczony dla każdej transakcji sprzedaży, to możemy po prostu je zsumować (=SUMA(D2:D10)).

W tym podejściu istnieje drobna niedogodność – musimy obliczyć przychód każdej transakcji, żeby uzyskać wynik końcowy, który nas interesuje. Powoduje to przede wszystkim konieczność wstawienia dodatkowej kolumny obliczeniowej, a tym samym zwiększenie objętości pliku i dodatkowe obciążenie procesora. Żeby uniknąć zbędnych obliczeń, możemy obliczyć całkowity przychód w jednej formule (formule tablicowej).

Możemy skorzystać z funkcji SUMA, w której zamieścimy wyniki poszczególnych iloczynów – {=SUMA(B2:B10*C2:C10)} (rys. 2).

Istotne jest dla nas, jak dokładnie Excel liczy powyższy zapis mnożenia dwóch tablic (B2:B10*C2:C10). Excel mnoży przez siebie elementy poszczególnych tablic, czyli pierwszy element z pierwszej tablicy (B2) mnoży przez pierwszy element drugiej tablicy (C2) itd. W ten sposób uzyskujemy przychód z wszystkich transakcji wewnątrz funkcji SUMA ({400;20;20;700;4473;2000;60;1750;150}) i w ten sposób możemy uzyskać interesujący nas wynik za pomocą jednej formuły, ale musimy pamiętać o jednej istotnej rzeczy – musimy zatwierdzić formułę za pomocą kombinacji klawiszy Ctrl + Shift + Enter, ponieważ funkcja SUMA, nie radzi sobie z obliczeniami na tablicach w normalnym trybie i bez tej kombinacji klawiszy przy jej zatwierdzaniu zwróci niepoprawny wynik.

To, że formuła została zatwierdzona jako formuła tablicowa, możemy poznać po tym, że w pasku formuły jest ona otoczona nawiasami klamrowymi { }. Te nawiasy pełnią rolę informacyjną i nie ma ich faktycznie w formule.

Funkcja SUMA nie radzi sobie z obliczeniami na tablicach, ale istnieją funkcje, dla których nie jest to problemem. Jedną z takich funkcji jest funkcja SUMA.ILOCZYNÓW. Nawet jej poszczególne argumenty są opisane jako tablice (rys. 3).

Funkcja SUMA.ILOCZYNÓW mnoży przez siebie kolejne elementy wszystkich podanych tablic/zakresów, a na koniec sumuje uzyskane wyniki. Ponieważ funkcja SUMA.ILOCZYNÓW radzi sobie z operacjami na tablicach, nie musimy jej zatwierdzać za pomocą kombinacji klawiszy Ctrl + Shift + Enter. Właśnie zapominanie o tym sposobie zatwierdzania jest najczęstszym problemem związanym z formułami tablicowymi, dlatego jeśli tylko możemy (i nie spowoduje to większego obciążenia procesora), staramy się pisać takie formuły tablicowe, które nie wymagają zatwierdzania tą specjalną kombinacją klawiszy.

Uwaga 1.

Na początku 2019 roku Microsoft planuje wprowadzić dla subskrybentów Office 365 nowy sposób obliczania formuł tablicowych, który m.in. nie będzie wymagał zatwierdzania żadnej formuły za pomocą skrótu Ctrl + Shift + Enter. Więcej na ten temat pod koniec artykułu.

Uwaga 2.

Warto tutaj jeszcze zwrócić uwagę na to, że wiele funkcji Excela działa na zakresach danych (np.: B2:B10), które wyglądają jak tablice, ale ze względu na to, że obliczenia na nich wykonywane są na innej zasadzie niż na tablicach (chociażby w VBA), to nie są w stanie sobie poradzić z obliczeniami na tablicach, natomiast w drugą stronę nie ma takich problemów, czyli np.: funkcja SUMA.ILOCZYNÓW może pracować zarówno z tablicami danych, jak i zakresami danych.

MIN.JEŻELI i MAX.JEŻELI

Kolejnym przykładem, jaki omówimy, jest obliczanie minimalnej/maksymalnej wartości (np.: produkcji/sprzedaży) po warunku, czyli przykładowo chcemy obliczyć minimalną wartość produkcji dla poszczególnych pracowników (rys. 4), czyli po warunku.

Podobne funkcjonalności realizuje np.: funkcja SUMA.JEŻELI, a od wersji Excel 2019 zostały wprowadzone funkcje MIN.WARUNKÓW i MAKS.WARUNKÓW, ale jeśli mamy wcześniejszą wersję Excela, musimy sami sobie stworzyć formułę, która wykonuje obliczenia w analogiczny sposób.

Z naszego zapisu widzimy, jakich funkcji potrzebujemy użyć: MIN i JEŻELI. Zaczniemy od funkcji JEŻELI, za pomocą której sprawdzimy warunek, a tym samym ograniczymy zwracane z zakresu liczby (sprawimy, że zostaną one pominięte przy obliczeniach w funkcji MIN).

Pozostałe 67% artykułu dostępne jest dla zalogowanych użytkowników serwisu.

Jeśli posiadasz aktywną prenumeratę przejdź do LOGOWANIA. Jeśli nie jesteś jeszcze naszym Czytelnikiem wybierz najkorzystniejszy WARIANT PRENUMERATY.

Zaloguj Zamów prenumeratę Kup dostęp do artykułu

Pobierz pliki:

Ulubione Drukuj

Zobacz również

Ciągi w Excelu

Ciągi w Excelu

Przy tworzeniu raportów bądź podsumowań, często korzysta się z różnego rodzaju ciągów. Programiści Microsoftu zdają sobie z tego sprawę, dlatego w Excelu można tworzyć różnorodne ciągi.

Czytaj więcej

Wizualizacja KPI w Excelu, czyli jak zbudować gauge chart

Wizualizacja KPI w Excelu, czyli jak zbudować gauge chart

Wizualizacja, obok analizy danych, jest niezwykle istotną częścią pracy kontrolera finansowego. To, jak wizualizujemy dane ma znaczny wpływ na to, jak je zrozumieją menedżerowie. Jednym z ciekawszych, choć bardziej pracochłonnych wykresów jest gauge chart (miernik), który pokazuje graficznie performance wybranego przez nas KPI. Niniejszy artykuł opisuje jak krok po kroku stworzyć taki wykres, a także jak powinny wyglądać dane wejściowe, by taki wykres skutecznie zbudować.

Czytaj więcej

Polecamy

Przejdź do

Partnerzy

Reklama

Polityka cookies

Dalsze aktywne korzystanie z Serwisu (przeglądanie treści, zamknięcie komunikatu, kliknięcie w odnośniki na stronie) bez zmian ustawień prywatności, wyrażasz zgodę na przetwarzanie danych osobowych przez EXPLANATOR oraz partnerów w celu realizacji usług, zgodnie z Polityką prywatności. Możesz określić warunki przechowywania lub dostępu do plików cookies w Twojej przeglądarce.

Usługa Cel użycia Włączone
Pliki cookies niezbędne do funkcjonowania strony Nie możesz wyłączyć tych plików cookies, ponieważ są one niezbędne by strona działała prawidłowo. W ramach tych plików cookies zapisywane są również zdefiniowane przez Ciebie ustawienia cookies. TAK
Pliki cookies analityczne Pliki cookies umożliwiające zbieranie informacji o sposobie korzystania przez użytkownika ze strony internetowej w celu optymalizacji jej funkcjonowania, oraz dostosowania do oczekiwań użytkownika. Informacje zebrane przez te pliki nie identyfikują żadnego konkretnego użytkownika.
Pliki cookies marketingowe Pliki cookies umożliwiające wyświetlanie użytkownikowi treści marketingowych dostosowanych do jego preferencji, oraz kierowanie do niego powiadomień o ofertach marketingowych odpowiadających jego zainteresowaniom, obejmujących informacje dotyczące produktów i usług administratora strony i podmiotów trzecich. Jeśli zdecydujesz się usunąć lub wyłączyć te pliki cookie, reklamy nadal będą wyświetlane, ale mogą one nie być odpowiednie dla Ciebie.