Formuły tablicowe dla analityków w excelu

Formuły tablicowe dla Analityków

Posted on Posted in Raport

Formuły tablicowe w Excelu to przydatne narzędzie, pomocne w pracy każdego Analityka, zajmującego się na co dzień raportowaniem. Dziś przedstawie Wam 4 formuły, którymi często posługuję się tworząc różnego rodzaju zestawienia. Przy ich omawianiu będziemy posługiwali się klawiszem F9 za pomocą, którego będziemy mogli podejrzeć wynik poszczególnych funkcji składających się na formułę. Zapraszam to lektury !

 

Znajdowanie "n" największej pozycji z kryterium

W przykładzie pierwszym, widzimy tabelkę, w której znajdują się dane dotyczące sprzedaży. Naszym zadaniem będzie znalezienie drugiej największej ilościowo pozycji dla danej kategorii - w tym wypadku dla Laptopów.

 

Tableka z danymi

 

Formuła, która rozwiąże nam ten problem będzie wyglądać następująco.

 

Formuła tablicowa w Excelu

 

A zatem, jeżeli w tablicy, określonej jako obszar A2:A13, znajdzie się słowo "Laptop", to funkcja JEŻELI, zwróci nam ilościową wartość sprzedaży. W przeciwnym wypadku w miejscu wartości pojawi się słowo FAŁSZ.

 

Formuła tablicowa

 

Następnie funkcja MAX.K, z argumentem "2" pobierze drugą największą wartość z uzyskanej tablicy, podając tym samym wynik równy 5.

 

Zapisz

Zapisz

Lista unikatowych wartości

Tym razem, za pomocą formuły tablicowej, utworzymy listę unikatowych wartości znajdujących się w kolumnie "A". Nasza formuła tablicowa zadziała w następujący sposób:

 

Tabela w Excelu

 

- na początku z tablicy podanej jako pierwszy argument funkcji INDEKS tzn. $A$2:$A$39, pobierzemy dane z konkretnych wierszy, których numery poznamy przy pomocy funkcji PODAJ.POZYCJĘ

- w funkcji PODAJ.POZYCJĘ, będziemy wyszukiwali pozycji liczby zero w tablicy utworzonej dzięki funkcji LICZ.JEŻELI

- za pomocą funkcji LICZ.JEŻELI stworzymy zero-jedynkową tablicę, gdzie pierwsze w kolejności zero będzie oznaczało nową pozycję, która nie pojawiła się jeszcze w argumencie "zakres" np. w komórce D6 funkcja LICZ.JEŻELI zwraca następującą tablicę

 

Tablica zero jedynkowa

 

 

1 oznaczają nazwy miast, których nazwy pokryły się w argumentach funkcji "zakres" i "kryteria". Natomiast pierwsze w kolejności zero (szóste na liście) oznacza nową nazwę miasta czyli w tym wypadku Kraków

- przeciągając funkcję w dół zwiększamy badany "zakres" funkcji LICZ.JEŻELI, uzupełniając tablicę kolejnymi jedynkami

 

Zapisz

Lista wartości z kryteriami

Stosując trzecią formulę tablicową, będziemy mogli utworzyć listę wartości spełniających dane kryteria. W naszym przypadku będziemy wyszukiwali kobiet o stażu pracy większym niż 6 lat. Na początek posłużymy się funkcją INDEKS, której to pierwszym argumentem będzie kolumna z imionami pracowników. Numer wiersza, z którego pobierzemy dane uzyskamy korzystając z funkcji MIN.K, której pierwszym argumentem będzie lista utworzona przy pomocy dwóch funkcji JEŻELI. Kryteria, które będziemy ze sobą sprawdzali połączymy znakiem "*".

 

Formuła tablicowa

 

 

Jeżeli każde z kryteriów zostanie spełnione, pierwszy argument funkcji JEŻELI, zwróci nam tablicę PRAWDA/FAŁSZ, dla której w przypadku PRAWDY, zostanie nadany nr wiersza za pomocą funkcji WIERSZ.

 

Formuła tablicowa

 

Następnie przy pomocy drugiego argumentu funkcji MIN.K, pobierzemy kolejno 1,2,3 i kolejną najmniejszą wartość z tablicy.

 

Formuła tablicowa

 

Zapisz

Suma maksymalnych kilku wartości

Ostatnia formuła pozwoli nam dodać do siebie wybraną przez nas ilość największych wartości na liście. Do funkcji SUMA, wstawiamy funkcję MAX.K, której pierwszy argument ustawiamy na zakres A2:A6. Argument "k", czyli k-ta największa wartość w zbiorze danych, w naszym przypadku przyjmie formę tablicy składającej się z 3 elementów {1;2;3}.

 

Formuła tablicowa

 

Czy Wy również używacie formuł tablicowych w swoich raportach/dashboardach ?

Jakie są Wasze najciekawsze rozwiązania ?

Zapraszam do komentowania.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *