Funkcje Tablicowe – jak wykorzystać je w Twojej firmie?

Z lekcji dowiesz się:

• Czym są formuły„CSE” i dlaczego warto je znać?

• Jak policzyć medianę wyników dla każdego z departamentów w firmie?

• Jak dzięki formule CSE ocenić wyniki departamentu na tle pozostałych?

• Jak przyporządkować premie, zależne od 3 zmiennych (np. departamentu, działu i regionu)?

Przykład 1

Przedstawiamy dane dotyczące zarobków w jednej z firm - dane o pracowniku, departamencie w którym pracuje oraz rocznym wynagrodzeniu (Rysunek 1).

(Rysunek 1)

Naszym zadaniem jest policzyć medianę dla każdego z departamentów. Zadanie jest o tyle trudne, że w przeciwieństwie np. do funkcji suma.jeżeli, mediana nie posiada takiego rozszerzenia, dlatego musimy użyć funkcji mediana i jeżeli. Tutaj widzimy, że użycie formuły tablicowej umożliwia porównanie argumentu (nazwa departamentu) z całą tablica argumentów. W przypadku użycia funkcji jeżeli w postaci nietablicowej, można porównywać argument jedynie z drugim pojedynczym argumentem.

Prześledźmy zatem naszą funkcję (Rysunek 2)

(Rysunek 2)

Na początku funkcja mediana, następnie funkcja jeżeli, w której wskazujemy kryterium porównawcze komórki, a następnie zakres z którego chcemy wyliczyć medianę. Na koniec formułę zatwierdzamy wspomnianym na początku skrótem klawiaturowym „Ctrl Shift Enter”. Można zauważyć, że na początku i na końcu funkcji pojawiły się nawiasy klamrowe, które mówią o tym, że została użyta funkcja tablicowa. Możemy swobodnie przeciągać funkcje, bez każdorazowego zatwierdzania jej skrótem klawiaturowym.

Oczywiście, mediana to tylko jeden z przykładów, funkcji, którą możemy używać w takiej sytuacji. Moglibyśmy użyć ich znacznie więcej, np. formuły max, min itp... (Rysunek 3).

(Rysunek 3)

Dodatkowo można dostosować więcej warunków, wtedy należałoby dodać odpowiednią ilość formuł jeżeli.

Przykład 2

Po lewej stronie przedstawione zostały dane dotyczące pracowników pewnej firmy (Rysunek 4). Po prawej dane dotyczące wypłacanych premii, które są zależne od departamentu, działu i regionu firmy (Rysunek 5). Naszym zadaniem jest przyporządkować wartość premii do każdego pracownika. W tej sytuacji logiczne wydaje się wykorzystanie funkcji WYSZUKAJ.PIONOWO. Niestety w naszym zadaniu dopasowanie premii do pracownika, jest zależne od 3 argumentów, więc funkcja WYSZUKAJ.PIONOWO będzie tu niewystarczająca. Musimy więc do funkcji dodać formuły jeżeli, które pozwolą na prawidłową identyfikację większej ilości argumentów.

(Rysunek 4)                                                                                      (Rysunek 5)

Prześledźmy więc naszą funkcję (Rysunek 6)

(Rysunek 6)

Zaczynamy od WYSZUKAJ.PIONOWO, komórka B2, czyli szukana wartość w drugiej tablicy. Następnie stosujemy podwójnie funkcje jeżeli aby zidentyfikować nasze pozostałe 2 argumenty i na końcu deklarujemy całą naszą tablicę, tak jak ma to miejsce w funkcji WYSZUKAJ.PIONOWO, dodajemy warunki fałszu czyli ”” brak wartości jeśli funkcja nie znajdzie dopasowania, i wskazujemy 4 kolumnę tablicy - ponieważ tam właśnie znajduje się szukana premia. Ponownie stosujemy „CSE” i przeciągamy funkcje w dół.

Dla lepszego zrozumienia zobaczmy, jak wyglądałaby funkcja WYSZUKAJ.PIONOWO, gdybyśmy wartość premii determinowali tylko przypisaniem do jednego argumentu np. departamentu. Widzimy, że funkcja tablicowa zawiera w środku dwa warunki jeżeli, a pozostała cześć to klasyczna budowa funkcji WYSZUKAJ.PIONOWO (Rysunek 7).

(Rysunek 7)

Oczywiście moglibyśmy mieć więcej warunków sprawdzających niż te wskazane w przykładzie, należałoby wtedy dodać po prostu kolejną konstrukcję jeżeli.

Przykład 3

W ostatnim przykładzie przedstawimy wykorzystanie formuł tablicowych przy działaniach na dwóch tablicach. W tym przypadku pomnożymy przez siebie dwie macierze.

Na początku należy zaznaczyć obszar jaki będzie miała wynikowa tablica. W pierwszą komórkę wpisujemy funkcję. Skorzystaliśmy z funkcji macierz.iloczyn, która służy do mnożenia macierzy (Rysunek 8).

(Rysunek 8)

Prześledźmy funkcję (Rysunek 9).

(Rysunek 9)

Funkcja macierz.iloczyn, następnie deklaracja dwóch tablic i zatwierdzamy znanym skrótem „CSE”. Widzimy, że w bardzo prosty i szybki sposób udało nam się przemnożyć przez siebie dwie tablice.

Polecamy także:

• Funkcja WARUNKI zamiast JEŻELI - jak szybciej przyporządkować dane?
• Formatowanie warunkowe – jak wykorzystać je w praktyce?