Pamiętam swoje początki pracy w Excelu. Od pierwszego kontaktu z tym narzędziem byłem nim zafascynowany. Oczywiście, na początku niewiele potrafiłem zrobić, co nie zniechęcało mnie przed tym, aby poświęcać sporą część mojego dnia na poznawaniu jego funkcjonalności.
W tym artykule opisuje funkcje, które moim zdaniem warto znać, gdy chcesz analizować dane w Excelu. Mam nadzieję, że dzięki temu oszczędzisz trochę czasu i od razu zastosujesz zdobytą wiedzę w praktyce.
Tradycyjnie, poniżej możesz znaleźć krótki opis działania formuł, którymi zajmuję się w artykule:
- DŁ – zwraca liczbę znaków z ilu złożony jest ciąg znaków
- ILE.NIEPUSTYCH / LICZ.PUSTE – określa ile niepustych (lub pustych) komórek znajduje się w określonym zakresie
- DNI / DNI.ROBOCZE – umożliwia obliczenie ile dni (lub dni roboczych) znajduje się pomiędzy wskazanymi datami
- ŚREDNIA.JEŻELI / ŚREDNIA.WARUNKÓW – umożliwiają obliczenie średniej arytmetycznej dla komórek spełniających jeden (lub więcej) z określonych warunków
- LICZ.JEŻELI – zwraca liczbę komórek z wybranego zakresu spełniających określone kryterium
- SUMA.ILOCZYNÓW – pozwala przemnożyć przez siebie wartości z min. dwóch tablic i na końcu ze sobą zsumować
- ORAZ / LUB – umożliwia sprawdzenie prawdziwości wszystkich (lub min. jednego) warunków wpisanych w funkcję
- INDEKS – pozwala pobrać wartość znajdującą się w tablicy, na przecięciu określonego wiersza i kolumny
- PODAJ.POZYCJĘ – zwraca informację o położeniu szukanej wartości w określonym zakresie
Na końcu pokazuję także jak połączyć funkcje INDEKS i PODAJ.POZYCJĘ. Jeśli chcesz poznać praktyczne wykorzystanie powyższych formuł zapraszam do dalszej części artykułu.
Funkcja DŁ
Przydatną funkcją w procesie analizy danych w Excelu jest funkcja DŁ, która umożliwia uzyskanie informacji o liczbie znaków znajdujących się w danym ciągu tekstowym. Funkcja sama w sobie nie oferuje wielu możliwości jej wykorzystania, ale w połączeniu z innymi funkcjami dostępnymi w programie można stworzyć ciekawe rozwiązania ułatwiające proces analizy lub modyfikacji danych.
Budowa funkcji
Funkcja ta jest bardzo prosta w swojej budowie. Do jej poprawnego działania wystarczy wskazać tylko jeden argument – Tekst. Może on zostać wpisany bezpośrednio do formuły, w postaci np. „Przykładowy tekst” lub jako odwołanie do określonej komórki. Podsumowując, funkcja może mieć następującą postać:
=DŁ("Przykładowy tekst")
Lub
=DŁ(A1), gdzie w komórce A1 znajduję się ciąg tekstowy, którego liczbę znaków ma zwrócić formuła
Bardzo ważną właściwością funkcji DŁ jest uwzględnianie spacji jako znaków, przy zliczaniu liczby znaków w danym tekście. Oznacza to, że dla ciągu tekstowego „Przykładowy tekst” funkcja zwróci 17, ponieważ składa się on z 16 liter i 1 spacji pomiędzy wyrazami.
Przykład
Formułę DŁ w połączeniu z funkcjami ZNAJDŹ, PRAWY oraz LEWY można np. wykorzystać do rozdzielenia imienia i nazwiska wpisanego razem do jednej komórki. O wymienionych funkcjach pisałem w tym artykule, więc jeśli zasady działania tych formuł nie są Ci znane, to zachęcam do zapoznania się z artykułem.
Mając imię i nazwisko wprowadzone razem do jednej komórki, dzięki połączeniu wyżej wymienionych funkcji możemy rozdzielić imienia i nazwiska do osobnych komórek, niezależnie od liczby znaków w imieniu oraz nazwisku. Dzięki temu nie będzie potrzeby ingerowania później w formułę, aby zadziałała tak, jak się od niej oczekuje.
Do wyodrębnienia z powyższych ciągów tekstowych imienia każdej osoby możemy wykorzystać funkcje LEWY oraz ZNAJDŹ. Formuła LEWY umożliwi wskazanie, z którego tekstu Excel ma wyodrębnić określoną liczbę znaków, licząc od lewej strony. Natomiast funkcję ZNAJDŹ wykorzystamy do określenia liczby znaków, którą należy wyodrębnić. Zauważ, że imię i nazwisko są od siebie rozdzielone spacją – wykorzystamy tę informację w formule:
=LEWY(A2;ZNAJDŹ(" ";A2)-1)
Chcemy, aby Excel wskazał nam określoną liczbę znaków, zaczynając od lewej strony, z tekstu znajdującego się w komórce A2. Do wyliczenia liczby znaków składających się na imię korzystamy z funkcji ZNAJDŹ, która w tekście z komórki A2 ma znaleźć spację – stąd symbol ” ” w formule. Na końcu odejmujemy 1, gdyż chcemy, aby funkcja zwróciła całe imię bez spacji na końcu.
Aby wyciągnąć nazwisko skorzystamy z funkcji PRAWY, DŁ oraz ZNAJDŹ. Dzięki formule PRAWY możliwe będzie wyodrębnienie interesującej nas liczby znaków, natomiast połączenie formuł DŁ i ZNAJDŹ wyliczy z ilu liter składa się nazwisko każdej osoby na liście:
=PRAWY(A2;DŁ(A2)-ZNAJDŹ(" ";A2))
Analogicznie jak w przypadku poprzedniej formuły – wskazujemy Excelowi, aby wyciągnął interesującą nas liczbę znaków z tekstu z komórki A2, zaczynając od prawej strony. Aby formuła zwróciła nazwisko, wykorzystamy funkcję DŁ do obliczenia długości całego ciągu tekstowego (imie, nazwisko i spacja pomiędzy nimi). Następnie za pomocą funkcji ZNAJDŹ określimy na którym miejscu znajduje się spacja. Różnica między tymi wartościami, czyli wynik funkcji DŁ minus wyniki funkcji ZNAJDŹ wskaże z ilu znaków składa się nazwisko. W tej funkcji nie ma potrzeby odejmowania 1 na końcu.
Funkcja ILE.NIEPUSTYCH / LICZ.PUSTE
Jak nazwa funkcji wskazuje, umożliwia ona uzyskanie informacji ile niepustych (prościej: wypełnionych) komórek znajduje się w zaznaczonym obszarze. Istotną właściwością tej funkcji jest fakt, że jako niepuste rozumie także komórki wypełnione znakami niedrukowalnymi (np. spacja).
Bardzo podobnie działa funkcja LICZ.PUSTE, dzięki której można uzyskać informację, o tym ile pustych komórek znajduje się we wskazanym zakresie. Ta funkcja również klasyfikuje komórki zawierające znaki niedrukowalne jako wypełnione.
Budowa funkcji
Do poprawnego działania tych funkcji należy wskazać przynajmniej jeden argument wartość / zakres. Wtedy Excel sprawdzi, czy ta jedna komórka spełnia warunek określony w funkcji, czyli jest niepusta (dla funkcji ILE.NIEPUSTYCH) lub pusta (dla funkcji LICZ.PUSTE). Można również wskazać zakres komórek, w tej sytuacji formuła sprawdzi, ile w zaznaczonym zakresie znajduje się niepustych lub pustych komórek.
Przykład
Mając dane w tabeli umieszczone w kolumnach B-F i korzystając z funkcji ILE.NIEPUSTYCH oraz LICZ.PUSTE możemy uzyskać informację o tym, ile niepustych i pustych komórek znajduje się we wskazanym zakresie:
Funkcje dla zakresu znajdującego się w wierszu 3 powinny wyglądać tak:
ILE.NIEPUSTYCH: =ILE.NIEPUSTYCH(B3:F3)
LICZ.PUSTE: =LICZ.PUSTE(B3:F3)
Suma tych dwóch wyników, dla tego przypadku powinna zawsze wynosić 5, gdyż zakres w każdym wierszu składa się z 5 kolumn.
Funkcja DNI / DNI.ROBOCZE
Bardzo przydatne funkcje, które umożliwiają obliczanie różnicy pomiędzy dwiema datami. Korzystając z funkcji DNI, można obliczyć prostą różnicę pomiędzy dwiema datami, bez żadnych dodatkowych warunków. Natomiast funkcja DNI.ROBOCZE jest bardziej rozbudowana i umożliwia uzyskanie informacji, o tym ile dni roboczych było pomiędzy dwoma wskazanymi datami. Dodatkowo umożliwia wpisanie własnych dat (np. świąt lub innych dni wolnych), które nie zostaną uwzględnione w końcowym wyniku.
Budowa funkcji
Aby uzyskać informację o liczbie dni pomiędzy dwoma datami, przy wykorzystaniu funkcji DNI należy wskazać dwa parametry: data_końcowa oraz data_początkowa. Te dwa argumenty mogą zostać wskazane jako odwołanie do komórek zawierających daty lub wpisane ręcznie w treść formuły.
Analogicznie działa funkcja DNI.ROBOCZE, tylko w niej pojawia się opcjonalny argument [święta]. Warto jednak zwrócić uwagę na kolejność podawania dat, gdyż funkcja DNI wymaga podania najpierw argumentu data_końcowa i następnie data_początkowa. Natomiast w funkcji DNI.ROBOCZE w pierwszej kolejności podaje się parametr data_pocz, później data_końc i ewentualnie na końcu [święta].
Przykład
Mając do dyspozycji tabelę z danymi zawierającymi daty przy wykorzystaniu powyższych formuł, możemy obliczyć różnicę dni oraz dni roboczych pomiędzy wskazanymi datami. W niektórych przypadkach dodatkowo skorygujemy wynik o święta, które są dniami wolnymi od pracy, a więc nie chcemy ich uwzględniać w naszym wyniku.
Formuły możemy zapisać następująco:
=DNI(C3;B3)
=DNI.ROBOCZE(B3;C3;D3)
Funkcja ŚREDNIA.JEŻELI / ŚREDNIA.WARUNKÓW
Kolejne ciekawe funkcje, które umożliwiają obliczenie średniej arytmetycznej dla komórek, które spełniają określone kryterium. Funkcja ŚREDNIA.JEŻELI umożliwia wskazanie tylko jednego kryterium, natomiast funkcja ŚREDNIA.WARUNKÓW umożliwia wskazania jednego lub więcej kryterium. Bardzo przydatne funkcje, które po odpowiednim określeniu warunków mogą szybko dostarczyć Użytkownikowi niezbędnych informacji.
Budowa funkcji
Do poprawnego działania formuły ŚREDNIA.JEŻELI należy wskazać dwa argumenty wymagane: zakres, określający obszar z przynajmniej jedną komórką, który ma zostać uśredniony oraz kryteria, zawierający wartości, dla których ma zostać obliczona średnia. Ostatnim argumentem, który jest opcjonalny jest średnia_zakres. W tym zakresie można określić, z jakich komórek Excel ma rzeczywiście policzyć średnią. Gdy ten argument zostanie pominięty, średnia zostanie wyliczona z komórek określonych w argumencie zakres.
Analogiczne informacje należy podać, chcąc skorzystać z funkcji ŚREDNIA.WARUNKÓW. Tam w pierwszej kolejności należy wskazać argument średnia_zakres, a więc obszar, z którego Excel będzie miał obliczyć średnią. Drugim argumentem wymaganym jest kryteria_zakres1, zawierający odwołanie do komórek, które mają spełniać określone kryteria. Trzecim wymaganym argumentem jest kryteria1, który określa wartości, dla których mają zostać przeprowadzone obliczenia. Następne kryteria są już opcjonalne i umożliwiają wskazywanie kolejnych zakresów i kryteriów, które mogą dokładać kolejne warunki do wykonywanych obliczeń.
Przykład
Wyobraź sobie, że w tabeli mamy listę produktów dostępnych w pewnym sklepie. Tabela zawiera informacje o produktach (A, B, C), serii (I, II, III) tych produktów oraz ich wartości sprzedaży.
Funkcję ŚREDNIA.JEŻELI możemy wykorzystać do obliczenia średniej wartości sprzedaży dla poszczególnego typu produktu. Natomiast jeśli chcielibyśmy obliczyć średnią wartość sprzedaży dla poszczególnego typu produktu i poszczególnej serii, musielibyśmy do tego wykorzystać funkcję ŚREDNIA.WARUNKÓW. Zakładając, że w komórce G6 i H6 mamy informacje o typie i serii produktu funkcje powinny wyglądać następująco:
=ŚREDNIA.JEŻELI($B$3:B20;G6;$D$3:$D$20)
=ŚREDNIA.WARUNKÓW($D$3:$D$20;$B$3:$B$20;G6;$C$3:$C$20;H6)
Pierwsza funkcja zwróci nam średnią wartość sprzedaży dla pojedynczego warunku – określony typ produktu. Natomiast druga funkcja pozwoli dodać jeszcze drugie kryterium, serię produktu i dla tych dwóch warunków obliczy średnią.
Funkcja LICZ.JEŻELI
Ta funkcja umożliwia policzenie liczby komórek, które spełniają określone kryteria. Korzystając z tej funkcji, można w łatwy sposób uzyskać informację o tym, ile razy dany ciąg znaków występuje na liście. Na przykład mając listę samochodów sprzedanych w danym kraju w ubiegłym roku, można bez problemu policzyć, ile razy dana marka samochodu występuje na tej liście.
Budowa funkcji
Funkcja LICZ.JEŻELI składa się z dwóch argumentów – zakres, czyli obszar, z którego Excel ma zliczyć dane wartości oraz kryteria określający jakie wartości ma znaleźć. W pierwszym argumencie należy określić gdzie chcemy szukać, a w drugim co chcemy znaleźć.
Przykład
W poniższej tabeli znajduje się lista 50 nowo zarejestrowanych samochodów w pewnym mieście. Chcielibyśmy zliczyć sumę wystąpień każdego rodzaju samochodu na liście.
Korzystając z funkcji LICZ.JEŻELI możemy uzyskać następującą informację w bardzo prosty sposób:
Dla pierwszego samochodu na liście funkcja powinna wyglądać tak:
=LICZ.JEŻELI($B$3:$B$52;E3)
Po zablokowaniu odpowiednich komórek i przeciągnięciu ich w dół otrzymujemy informację, jakiej oczekiwaliśmy. Na liście jest łącznie 50 rekordów i suma wystąpień każdego samochodu jest równa 50 – wszystko się zgadza 🙂 . Jako argument określający kryteria można również wskazywać wartości logiczne, np. mając listę pracowników zawierającą informację o ich zarobkach, moglibyśmy wykorzystać formułę do uzyskania informacji ilu pracowników zarabia więcej niż X złotych. Wystarczyłoby jako kryterium podać „> 2500”, gdybyśmy chcieli, aby formuła zwróciła liczbę pracowników zarabiających więcej niż 2500. Bez problemu moglibyśmy także odnosić się do dat i obliczać ilu pracowników urodziło się w danym roku lub przed danym rokiem.
Funkcja SUMA.ILOCZYNÓW
Funkcja, dzięki której można przemnożyć przez siebie dwie tablice i na końcu dodać do siebie wynik tego mnożenia. Aby uzyskać taki efekt bez użycia funkcji należałoby przemnożyć odpowiednie wartości z pierwszej tabeli przez wartości z drugiej tabeli i na końcu podsumować otrzymane wartości. Używając formuły SUMA.ILOCZYNÓW wystarczy wskazać tablice, na podstawie których Excel ma wykonać obliczenia i przy użyciu tylko jednej funkcji zwróci gotowy wynik.
Budowa funkcji
Aby funkcja zadziałała, wystarczy wskazać jeden argument – tablica1. Jednak mając tylko dane z jednej tablicy formuła zsumuje te wartości – zgodnie z powyższym opisem działania funkcji, mnoży ona przez siebie wartości z dwóch tablic i dodaje te wartości. W przypadku wskazania tylko jednej tablicy Excel nie będzie miał przez co mnożyć, a więc zwróci sumę wartości wskazanych jako tablica1. Jeśli zostaną wskazane kolejne argumenty, np. tablica2 wtedy formuła przemnoży przez siebie te dwie tablice i na końcu zsumuje wyniki.
Przykład
Możemy porównać działanie tej funkcji na prostym przykładzie, w którym chcemy podsumować miesięczną sprzedaż wyrobów w sklepie. Dane przechowywane są w następującej formie:
Aby uzyskać informację o tym ile wyniosła całkowita sprzedaż, musielibyśmy dla każdego produktu przemnożyć liczbę sprzedanych sztuk danego produktu przez jego cenę jednostkową i dopiero po zsumowaniu tych wartości uzyskalibyśmy dane, na których nam zależy. Dzięki zastosowaniu formuły SUMA.ILOCZYNÓW możemy wykonać to znacznie szybciej.
Jako pierwszy argument tablica1 wskażemy wartości z kolumny B, natomiast wartości z kolumny C to będzie nasza tablica2. Powiemy Excelowi, aby odpowiednie wartości z kolumny B (tablica1) przemnożył przez odpowiadające im wartości z kolumny C (tablica2) i na końcu zwrócił nam sumę tych iloczynów. Podsumowując, funkcja powinna wyglądać tak:
=SUMA.ILOCZYNÓW(B2:B21;C2:C21)
Oczywiście moglibyśmy uzyskać ten sam efekt bez użycia powyższej formuły. Moglibyśmy w jednej kolumnie wyznaczyć iloczyn liczby sprzedanych sztuk i ceny jednostkowej dla każdego produktu i na końcu wykonać sumowanie otrzymanych wyników.
Funkcja ORAZ / LUB
Użycie funkcji ORAZ i LUB umożliwia zwiększenie możliwości płynących z korzystania z innych formuł dostępnych w Excelu, jak np. JEŻELI, o której więcej możesz przeczytać tutaj. Funkcja JEŻELI umożliwia sprawdzenie prawdziwości jednego warunku i zareagowania na wynik sprawdzenia. Natomiast dzięki użyciu funkcji ORAZ, LUB można zwiększyć liczbę warunków, które będą przez formułę sprawdzane.
Budowa funkcji
ORAZ umożliwia sprawdzenie, czy wszystkie warunki określone przez Użytkownika są prawdziwe. Funkcja składa się argumentów, które sprawdzają, czy określone warunki są spełnione i na tej podstawie zwraca wynik PRAWDA lub FAŁSZ. LUB działa bardzo analogicznie, aczkolwiek w przypadku tej formuły wystarczy, że jeden warunek jest spełniony. Warunki mogą być bezpośrednio wpisane w treści formuły lub być odwołaniem do komórek, np.:
=ORAZ(1=1;2=2;3=3)
=ORAZ(A1=1;A2=2;A3=3)
=LUB(1=1;2=2;3=3)
=LUB(A1=1;A2=2;A3=3)
Przykład
Spróbujmy wykorzystać dwie powyższe funkcje do rozszerzenia możliwości funkcji JEŻELI. Z definicji wewnątrz funkcji możemy jedynie wskazać jeden argument do sprawdzenia:
= JEŻELI (sprawdzenie prawdziwości warunku ; jeśli prawda wykonaj to ; w przeciwnym razie wykonaj to)
Dzięki funkcjom ORAZ i LUB możemy zwiększyć liczbę warunków, które będą sprawdzane w funkcji. Sprawdźmy, na podstawie stażu pracy oraz wartości sprzedaży, jaką wysokość premii pracownik powinien uzyskać. Dane są przechowywane w następujący sposób:
Używając funkcji ORAZ w pierwszej kolejności zakładamy, że premię wypłacimy pracownikom, którzy przepracowali w firmie minimum 12 miesięcy. Następnie, jeżeli pracownik zdołał wypracować min. 50 000 PLN sprzedaży to wysokość premii niech wyniesie 20%, jeżeli mniej niż 50 000 PLN to 10%.
W drugim przypadku, korzystając z funkcji sprawdzimy, czy pracownik przepracował w firmie minimum 12 miesięcy. Następnie używając funkcji LUB określimy, że pracownicy, którzy wypracowali min. 50 000 PLN sprzedaży lub sprzedali min. 200 sztuk powinni otrzymać 20%. Pozostali pracownicy powinni otrzymać 10% premii. Końcowe funkcje:
=JEŻELI(ORAZ(C3>=12;D3>=50000);20%;JEŻELI(ORAZ(C3>=12;D3<50000);10%;0%))
=JEŻELI(C3<12;0;JEŻELI(LUB(D3>50000;E3>200);20%;10%))
Funkcja INDEKS
Ta funkcja umożliwia pobranie wartości znajdującej się na przecięciu wskazanego wiersza i kolumny z wybranej tablicy. Dzięki niej można wskazać, aby Excel pobrał ze wskazanej tabeli wartość znajdującą się na przecięciu np. 3 wiersza i 5 kolumny.
Budowa funkcji
Formuła wymaga wskazania argumentu tablica, określającego zakres komórek. Drugim argumentem wymaganym jest nr_wiersza, wskazujący, z którego wiersza ma zostać pobrana wartość. Ostatni, opcjonalny, argument to nr_kolumny, określający, z której kolumny ma zostać pobrana wartość. Jeśli argument nr_kolumny nie zostanie określony, funkcja domyślnie wstawi tam 1, pobierając wartość z pierwszej kolumny.
Przykład
W poniższej tabeli znajdują się jednostkowe koszty dostawy produktów dostępnych w pewnym sklepie, w zależności od wagi przesyłki.
Chcąc pobrać wartość dla PRODUKTU 3, o wadze Max 4 kg moglibyśmy napisać następującą formułę:
=INDEKS(C3:G7;4;3) , która zwróci 4.
W formule określiliśmy tablicę jako C3:G7 (zaznaczony na czerwono), nr_wiersza = 4 i nr_kolumny = 3. Funkcja zwróciła wartość 4, czyli tak jak oczekiwaliśmy 😉.
Funkcja PODAJ.POZYCJE
Funkcja INDEKS bardzo często wykorzystywane jest razem z funkcją PODAJ.POZYCJĘ, dzięki której nr_wiersza i nr_kolumny – argumenty podawane w funkcji INDEKS mogą być wartościami dynamicznymi, a nie wpisywanymi ręcznie, jak w powyższym przykładzie. Formuła PODAJ.POZYCJĘ umożliwia uzyskanie informacji o położeniu szukanej wartości w określonej tabeli.
Budowa funkcji
Aby formuła zadziałała poprawnie należy wskazać dwa argumenty wymagane: szukana_wartość oraz przeszukiwana_tab. Trzeci argument typ_porównania jest opcjonalny. W argumencie szukana_wartość określa się jaka wartość ma zostać znaleziona przez funkcję. Obszar, w którym ta wartość ma być szukana jest określany w argumencie przeszukiwana_tab. Typ_porównania określa w jaki sposób Excel ma dopasować szukaną wartość – jeśli argument nie zostanie wskazany to program automatycznie określi go jako 1, czyli „mniejsze niż”. Znaczenie poszczególnych opcji wyboru można znaleźć podczas pisania formuły:
Przykład
Mając listę pracowników z wyszczególnionym wiekiem możemy uzyskać informację, który pracownik ma np. 43 lata.
Wartością, którą będziemy chcieli znaleźć, jest w tym przypadku 43 i to wskażemy jako argument szukana_wartość. Jako przeszukiwana_tab wpiszemy komórki z kolumny B, bo tam znajduje się informacja o wieku każdego pracownika. Ostatni argument typ_porównania określimy jako 0, gdyż interesuje nas dokładne dopasowanie.
=PODAJ.POZYCJĘ(43;B2:B16;0)
Wynik jaki otrzymamy to 5, gdyż piąty pracownik na liście – Jan Filipowski spełnia warunek przez nas określony.
Połączenie funkcji INDEKS i PODAJ.POZYCJE
Moim zdaniem, bardzo ciekawy efekt można uzyskać łącząc ze sobą dwie powyższe funkcje. Dzięki temu jako argumenty formuły INDEKS można wskazać wartości dynamicznie, np. które są wynikami innych formuł lub wynikają z wartości wybranych z list rozwijanych.
Budowa funkcji
Połączenie funkcji INDEKS oraz PODAJ.POZYCJĘ polega na tym, że w miejsce argumentów wymaganych w formule INDEKS wpisuje się funkcję PODAJ.POZYCJĘ.
Przykład
Mając w tabeli dane o wysokości kwartalnej sprzedaży w podziale na kierowników możemy przygotować w pełni automatyczną formułę, która na bazie wybranych opcji (kierownik i kwartał) wskaże nam łączną sprzedaż.
Możemy stworzyć dwie listy wyboru, z których będziemy wybierali kierownika oraz kwartał. Aby stworzyć listę wyboru należy wybrać opcję Dane na wstążce głównej i następnie, w obszarze Narzędzia danych wybrać Poprawność danych.
Wybieramy pierwszą pozycję z listy – Poprawność danych …. Okienko, które pojawi się na ekranie uzupełniamy jak na poniższym obrazku:
I dzięki temu otrzymamy pierwszą listę wyboru z danymi kierowników. Podobnie tworzymy drugą listę wyboru z kwartałami, jedyna różnica polega na tym, aby w źródle wpisać I;II;III;IV, co będzie odpowiadało kwartałom. Sprawdźmy jak odwołać się do tych wartości z połączonej formule:
=INDEKS(B8:F12;PODAJ.POZYCJĘ(B3;B8:B12;0);PODAJ.POZYCJĘ(C3;B8:F8;0))
Możemy w niej wyróżnić następujące elementy:
B8:F12 – tutaj definiujemy argument tablica, czyli zakres, w którym będziemy szukać określonych wartości
PODAJ.POZYCJĘ(B3;B8:B12;0) – za pomocą funkcji PODAJ.POZYCJĘ określamy argument nr_wiersza funkcji INDEKS. Formuła PODAJ.POZYCJĘ wskaże nam, w którym wierszu znajduje się wybrany kierownik.
PODAJ.POZYCJĘ(C3;B8:F8;0) – analogicznie, jak w powyższej części, tutaj wskazujemy nr_kolumny. Dzięki PODAJ.POZYCJĘ określamy, w której kolumnie znajduje się wybrany kwartał.
Zgodnie z opisem działania funkcji INDEKS powyższa formuła wskaże nam wartość łącznej sprzedaży znajdującej się na przecięciu wskazanego wiersza i kolumny.W pliku zamieszczonym na końcu artykułu możesz znaleźć także rozbudowaną wersję tej formuły, która umożliwia także wybranie roku.
Podsumowanie
Mam nadzieję, że formuły przedstawione w tym artykule pomogą Ci w codziennej pracy lub okażą się dobrym punktem wyjścia do rozpoczęcia nauki analizy danych w Excelu.
Tutaj możesz znaleźć plik, na którym pracowałem podczas tworzenia artykułu.
