Funkcje Tablicowe – jak wykorzystać je w Twojej firmie?

Automatyzacja pracy
Średniozaawansowany
Porządkowanie danych
0:00czas trwania

Funkcje tablicowe, pozwalają na wykonywanie operacji, które z pozoru wydają się być bardzo trudne i pracochłonne. Są one nazywane również formułami „Ctrl Shift Enter” lub „CSE”, ponieważ do ich wpisania konieczne jest naciśnięcie kombinacji właśnie tych klawiszy. Zaprezentowane przykłady to tylko niewielka cześć możliwych zastosowań formuł tablicowych, ale umożliwi ona ogólne zrozumienie tematu i będzie świetnym początkiem do ćwiczeń oraz pogłębiania excelowych praktyk. W codziennej pracy z arkuszem kalkulacyjnym, niejednokrotnie zdarza się nam wykorzystywać funkcje tablicowe, dlatego ich znajomość jest niezwykle cenna i pozwala znacznie ułatwić pracę z Excelem. Zapraszamy do lektury!

Opublikowane: 28 maja 2019
Lekcję prowadzi: Kamil Skonecki
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Zobacz krok po kroku jak wykonać lekcję

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)?

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

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.

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz gotowy arkusz z lekcji
Dostępne po opłaceniu
Kolejne kroki dostępne dla użytkowników którzy wykupili pełny dostęp

Jeszcze się wahasz?

Zobacz, co zyskasz, mając pełny dostęp:

  • Dostęp do wszystkich lekcji video
  • Materiały do ćwiczeń
  • Dodatkowe artykuły z trikami
  • Formularze gotowe od razu do użycia
  • E-booki pogłębiające Twoją wiedzę
  • Certyfikat potwierdzający Twoje umiejętności
Sprawdź co oferuje serwis Sprytny Excel »

Masz już konto w serwisie?

Zaloguj się

Adres e-mail:

Hasło

Nie pamiętam hasła
- Twój pierwszy krok do lepszych wyników
Oglądaj pełne lekcje i wiedz więcej!
Masz już konto w serwisie?
Zaloguj się »

Certyfikat rzetelności Laur zaufania SMB logo Top firma