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.
Formuła, która rozwiąże nam ten problem będzie wyglądać następująco.
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.
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:
- 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ę
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 "*".
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.
Następnie przy pomocy drugiego argumentu funkcji MIN.K, pobierzemy kolejno 1,2,3 i kolejną najmniejszą wartość z tablicy.
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}.
Czy Wy również używacie formuł tablicowych w swoich raportach/dashboardach ?
Jakie są Wasze najciekawsze rozwiązania ?
Zapraszam do komentowania.