ul. Zygmunta Starego 1/3, 44-100 Gliwice, (32)724-14-29

W codziennej pracy z Excelem zazwyczaj w jednej komórce wpisujemy dane, a w kolejnej wstawiamy funkcję, która do tych danych się odnosi. Dla przykładu, jeśli używamy funkcji JEŻELI, to wstawiamy ją, przy każdej komórce z danymi i otrzymujemy żądany wynik. Wynik ten jest zawsze jednoelementowy. Formuły tablicowe różnią się pod tym względem od standardowego zastosowana funkcji, ponieważ wynikiem działania funkcji tablicowej jest tablica danych, którą możemy wykorzystać do dalszych obliczeń.

Dlaczego warto znać formuły tablicowe? Na pewno dlatego, że dzięki nim zaoszczędzisz sporo miejsca na dysku. Weźmy za przykład sytuację, w której posiadasz tabelę zawierającą prawie milion wierszy. Chcesz teraz policzyć sumę znaków w słowach zawartych w jednej z trzech kolumn. Dokładasz więc kolejną kolumnę, wstawiasz funkcję DŁ i obok każdej komórki ze słowem otrzymujesz wynik, który na dole sumujesz wykorzystując dodatkową funkcję SUMA. Po zapisaniu pliku i sprawdzeniu jego rozmiaru można się mocno zdziwić. Po dodaniu czwartej kolumny plik zyskał kilka dodatkowych MB.

Alternatywą są formuły tablicowe, które pozwalają wykonywać działania na przechowywanych tablicach. Wystarczy użyć formuły, w której zamiast podawania jednej komórki jako atrybutu funkcji DŁ podasz cały zakres, aż do ostatniej wypełnionej komórki. Następnie funkcję DŁ wstawiasz w SUMĘ i zatwierdzasz kombinacją klawiszy Ctrl + Shift + Enter. Jeżeli nie użyjesz tej kombinacji, tylko użyjesz samego Entera formuła wyświetli pierwszy element tablicy jakim jest długość frazy w pierwszej komórce zakresu. Dopiero użycie kombinacji CSE powoduje potraktowanie formuły jako tablicowa i wyciągnięcie wartości z wykorzystaniem wszystkich elementów tablicy. Dlatego też formuły tablicowe nazywane są formułami CSE. Podglądając nową funkcję w pasku formuły można zauważyć, że całość została objęta nawiasami klamrowymi, co oznacza, że dla Excela jest to formuła tablicowa.

Wynik formuły tablicowej może być przedstawiony w jednej komórce jak w zaprezentowanym wyżej przykładzie, albo też jako tablica. Jeśli chcesz stworzyć tablicę o rozmiarze 3×3, której elementami są liczby od 1 do 9, to musisz po zaznaczeniu wybranego obszaru wpisać formułę ={1\2\3;4\5\6;7\8\9} i zatwierdzić kombinacją klawiszy CSE.

Znaki oddzielające od siebie elementy tablicy oznaczają przejście do nowej kolumny dla znaku \ (backspace), lub do kolejnego wiersza jeśli jest to ; (średnik). Znaki te uzależnione są od ustawień regionalnych i w Polsce w większości przypadków będą wyglądały dokładnie tak. Symbole rozpoznawane przez swoje ustawienia regionalne możesz sprawdzić wstawiając dowolną funkcję np. SUMA i zaznaczając dwuwymiarową tablicę danych. Wtedy w podpowiedziach Excela widać jakie zastosował znaki do oddzielenia kolumn i wierszy.

Tablice mają dodatkową korzyść, mianowicie nie da się usunąć jej części. Wyłącznie usunięcie całej tablicy przyniesie skutek. Jeśli użyjesz formuły tablicowej do sporządzenia liczb porządkowych zabezpieczysz w ten sposób swoje dane, przed ich przypadkowych usunięciem.

Jest w Excelu kilka funkcji tablicowych, w których wymagane jest użycie tablic i zatwierdzenie ich kombinacją CSE. Świetnym przykładem jest funkcja TRANSPONUJ, która podobnie jak jedna z opcji wklejania specjalnego powoduje przekopiowanie zaznaczonego obszaru i wklejenie go z równoczesną zamianą wierszy na kolumny i odwrotnie. Jednak funkcja TRANSPONUJ jest funkcją dynamiczną, która w przeciwieństwie do wklejania specjalnego będzie aktualizować dane, do których się odnosi. Aby użyć wspomnianej funkcji, na początku zaznaczę puste pole o odpowiedniej wielkości.

Na obrazku widać, że dane zawierają 5 wierszy i 2 kolumny, dlatego nowo zaznaczony zakres musi zawierać 5 kolumn i 2 wiersze. Następnie z funkcji wyszukiwania i adresu wybieram TRANSPONUJ i zaznaczam pierwotne dane. W oknie kreatora funkcji obok wskazanego zakresu danych pokazuje się odczytana przez Excel tablica zawierająca wszystkie elementy odpowiednio oddzielone znakami.

Teraz zatwierdzam funkcję przez użycie kombinacji CSE, aby uzyskać oczekiwany wynik, czyli nową tablicę w zaznaczonym obszarze. Nowa tablica jest dynamiczna i na bieżąco aktualizuje zmiany danych w źródle.

Aby pokazać Ci inne możliwości formuł tablicowych wykonam kilka działań. Na początek chcę policzyć liczbę niepustych komórek w kolumnie C. Wstawiam znak równości i zaznaczam zakres od C2 do C12, a także przyrównuję go do wartości „” oznaczającej pustą komórkę. Kiedy zaznaczę tę formułę i użyję klawisza F9 otrzymam widok tablicy, która wskazuje wyniki według domyślnych wartości Excela tzn. PRAWDA I FAŁSZ.

Z uwagi na to, że takich wartości nie da się sumować muszę zamienić je na 1 i 0. Użyję do tego pewnej sztuczki. Zaraz po znaku równości wstawiam – (2 myślniki), a działanie obejmuję w nawiasy.

Teraz po użyciu F9 widzę tablicę 0 i 1, które już bezproblemowo mogę sumować.

Teraz całość umieszczam w funkcji SUMA, zatwierdzam przez CSE i otrzymuję wynik.

Kolejny przykład to policzenie ile razy występuje mleko. Tutaj sytuacja jest identyczna, tylko zamiast przyrównać dane z kolumny C do pustego ciągu znaków, sprawdzam, czy w kolumnie D znajduje się wyraz „mleko”, a następnie sumuję te wystąpienia.

W ostatnim działaniu chcę wyświetlić najwyższą sprzedaż mleka i tutaj użyję już funkcji JEŻELI, w której jako atrybuty umieszczę tablice. Na początku sprawdzam czy w kolumnie B jest wyraz mleko i kiedy stwierdzenie jest prawdziwe, to funkcja ma zwrócić wartość z kolumny C. Całość wstawiam do funkcji MAX, aby wydobyć najwyższą sprzedaż.

Na koniec pokażę Ci jeszcze jeden przykład, który świetnie prezentuje możliwości tablic. W zadaniu muszę wyliczyć udział udział w rynku 3 największych firm. Wiesz pewnie, że najwyższą wartość zwraca funkcja MAX. Kiedy jednak chce się zwrócić kolejną najwyższą wartość należy użyć funkcji MAX.K, która pozwala określić, którą z kolei najwyższą wartość chcemy uzyskać.

Na załączonym obrazku widać, że wskazałem drugą wartość i zamiast 98 zł pojawiło się 35 zł. Skoro mogłem wybrać 1 element z tablicy, to mogę również wyciągnąć kilka elementów, które ostatecznie zsumuję. W funkcji MAX.K zamiast jednej wartości wpisuję tablicę, której elementy oddzielane są średnikami, ponieważ znajdują się w jednej kolumnie. Po wstawieniu funkcji MAX.K z tablicą 3 pierwszych wyników do funkcji SUMA otrzymuję wynik sprzedaży 3 największych firm.

Wystarczy teraz podzielić wynik przez sumę sprzedaży wszystkich firm i zmienić format komórki na procenty.

Mam nadzieję, że ten krótki artykuł pomoże Ci zrozumieć czym są formuły tablicowe i zacząć odważnie je stosować w swoich obliczeniach. Nie jest to proste zagadnienie, ale po opanowaniu okazuje się niezwykle pomocne w pracy z Excelem.

Tags: