Excel

0 of 78 lessons complete (0%)
Exit Course

Funkcje wyszukiwania

Funkcja INDEKS

This is a preview lesson

Register or sign in to take this lesson.

Angielska nazwa funkcji: INDEX

Funkcja INDEKS w Excelu pozwala na zwrócenie wartości znajdującej się w tabeli na przecięciu wybranego wiersza i wybranej kolumny. Funkcja ta jest bardzo często łączona z funkcjami:

  • PODAJ.POZYCJĘ (MATCH)
  • X.DOPASUJ (XMATCH)

Funkcja INDEKS znajdzie zastosowanie w takich sytuacjach jak:

  • Analiza sprzedaży produktów w różnych regionach: Określenie wartości sprzedaży konkretnego produktu w danym regionie, bazując na przecięciu wiersza z produktem i kolumny z regionem w tabeli sprzedaży.
  • Określenie premii dla pracowników na podstawie osiągniętych celów: Wyszukiwanie konkretnej kwoty premii dla pracownika, bazując na przecięciu wiersza z nazwiskiem pracownika i kolumny z osiągniętym progiem sprzedaży w tabeli premii.
  • Zarządzanie stanem magazynowym: Sprawdzanie dostępności konkretnego produktu w danym magazynie, bazując na przecięciu wiersza z produktem i kolumny z lokalizacją magazynu w tabeli stanów magazynowych.
  • Planowanie budżetu marketingowego: Wyszukiwanie kosztów dla konkretnej kampanii marketingowej w danym miesiącu, bazując na przecięciu wiersza z kampanią i kolumny z miesiącem w tabeli budżetowej.
  • Analiza wyników ankiety klienta: Określenie satysfakcji klienta z konkretnego produktu w danym miesiącu, bazując na przecięciu wiersza z produktem i kolumny z miesiącem w tabeli wyników ankiety.

Struktura argumentów funkcji INDEKS:

=INDEKS(tablica; nr_wiersza; nr_kolumny)

Opis argumentów:

  • tablica (array): Zakres komórek lub stała tablicowa, z której chcemy pobrać wartość.
  • nr_wiersza (row_num): Numer wiersza w tablicy, z którego chcemy pobrać wartość.
  • nr_kolumny (column_num): Numer kolumny w tablicy, z której chcemy pobrać wartość.

Praktyczny przykład biznesowy:

Załóżmy, że prowadzisz sklep odzieżowy i masz tabelę z ilością dostępnych ubrań w różnych rozmiarach. Chcesz szybko sprawdzić dostępność wybranego ubrania w określonym rozmiarze.

Przykład zastosowania funkcji w Excelu:

Zakres komórek A9:F39 zawiera poniższą tabelę.:


S M L XL
Koszulka 5 3 2 0
Spodnie 4 4 3 1
Bluza 6 5 2 2

Chicałbym sprawdzić jaki jest stan magazynowy spodni w romiarze L. Zaczynając od najprostszego podejścia załóżmy, że wiem, że spodnie znajdują się w wierszu trzecim tabeli, a rozmiar L w kolumnie czwartej.
W dalszej części lekcji zajmiemy się sytuacją, w której zadaniem Excela będzie ustalenie numeru wiersza i kolumny.

Do komórki B5 wprowadzam następującą formułę:

=INDEKS($B$10:$F$39;3;4)

Zauważ, że zakres tabeli wprowadzony do funkcji nie obejmuje nagłówków wierszy i nagłówków kolumn.

Działanie funkcji

  1. Przedmiotem zainteresowania funkcji jest zakres komórek B10:F39, zawierający stany magazynowe produktów dostępnych w sklepie.
  2. Funkcja przechodzi w podanym zakresie danych do wiersza numer 3, odpowiadającego spodniom.
  3. Następnie, w trzecim wierszu funkcja przechodzi do kolumny czwartej, odpowiadającej rozmiarowi L.
  4. W ten sposób funkcja natrafia na komórkę E12 zawierająca stan magazynowy spodni w rozmiarze L.
  5. Jako wynik funkcja zwraca wartość 21.

Instrukcja wstawienia funkcji za pomocą Biblioteki funkcji:

  1. W arkuszu kliknij na komórkę, w której chcesz wyświetlić wynik – na przykład obok wybranego rozmiaru w komórce B5.
  2. Przejdź na wstążce do karty Formuły (Formulas).
  3. Na tej karcie odszukaj grupę Funkcje wyszukiwania i odwołania (Lookup & Reference Functions).
  4. Na liście funkcji odszukaj i kliknij funkcję INDEKS (INDEX).
  5. W oknie argumentów funkcji:
    • Kliknij w polu Tablica (Array), a następnie w arkuszu zaznacz zakres B10:F39.
    • W polu Nr wiersza (Row num) wprowadź wartość3.
    • W polu Nr kolumny (Column num) wprowadź wartość 4.
  6. Naciśnij przycisk OK.
  7. W komórce B5 zostanie wyświetlona dostępna ilość spodni w rozmiarze L.

Łączenie funkcji INDEKS z funkcją X.DOPASUJ

Przejdźmy teraz do sytuacji, w której to zadaniem Excela jest ustalenie numeru wiersza dla interesującego nas rodzaju ubrania i numeru kolumny dla wybranego rozmiaru.
W tym celu możemy posłużyć się funkcją PODAJ.POZYCJĘ lub funkcją X.DOPASUJ. W przykładzie wykorzystana zostanie druga z wymienionych funkcji.

W pliku z przykładem przejdźmy do drugiego arkusza.

Ustalenie numeru wiersza

Rozpoczniemy od ustalenia za pomocą funkcji numeru wiersza w tabeli, który zawiera spodnie wybrane z listy rozwijanej w komórce B2.

Do komórki D2 wprowadź następującą formułę:

=X.DOPASUJ(B2;$A$10:$A$39;0)

Działanie funkcji

  1. Funkcja pobiera nazwę ubrania wybranego z listy rozwijanej w komórce B2
  2. Funkcja przeszukuje podaną listę w zakresie komórek A10:A39 w poszukiwaniu wybranej nazwy ubrania.
  3. W przykładzie wartość trzeciego argumentu jest równa 0, co odpowiada dokładnemu dopasowaniu, czyli funkcja tak długo przeszukuje listę, aż trafi na dokładnie taką samą nazwę, jak wybrana z listy.
  4. Trzeba pamiętać, że domyślnie wyszukiwanie odbywa się od pierwszej do ostatniej zaznaczonej komórki (w tym przypadku z góry na dół listy) i funkcja kończy swoją pracę na pierwszej komórce spełniającej kryteria wyszukiwania.
  5. W rezultacie funkcja zwraca wartość 3, czyli w przykładzie numer odpowiadający spodniom.

Ustalenie numeru kolumny

Kolejnym krokiem jest ustalenie numeru kolumny zawierającej wybrany rozmiar, czyli L.

Do komórki D3 wprowadź następującą formułę:

=X.DOPASUJ(B3;$B$9:$F$9;0)

Działanie funkcji

  1. Funkcja pobiera rozmiar ubrania wybrany z listy rozwijanej w komórce B3
  2. Następnie przeszukuje podaną listę rozmiarów znajdującą się w zakresie komórek B9:F9.
  3. Tak jak poprzednio zostało wybrane dokładne dopasowanie.
  4. Trzeba pamiętać, że domyślnie wyszukiwanie odbywa się od pierwszej do ostatniej zaznaczonej komórki (w tym przypadku od lewej do prawej strony zaznaczonej listy) i kończy swoją pracę na pierwszej komórce spełniającej kryteria wyszukiwania.
  5. W rezultacie funkcja zwraca wartość 4, czyli w przykładzie numer odpowiadający spodniom.

Ustalenie stanu magazynowego

Mając ustalone za pomocą funkcji numery wiersza i kolumny, możemy połączyć otrzymane wyniki z funkcją INSEKS i w ten sposób sprawdzić ilość wybranego ubrania w żądanym rozmiarze. W tym celu do komórki B5 wprowadź poniższą formułę. Zauważ, że jako argumenty funkcji nie są wpisane na sztywno numery wierszy i kolumn, lecz adresy komórek, zawierające te wartości.

=INDEKS($B$10:$F$39;D2;D3)

Działanie funkcji

  1. Funkca odwołuje się do zakresu komórek B10:F39, zawierającego stany magazynowe produktów dostępnych w sklepie.
  2. Funkcja przechodzi w podanym zakresie danych do wiersza numer 3, numer wiersza został wcześniej ustalony za pomocą funkcji X.DOPASUJ
  3. Następnie, w trzecim wierszu funkcja przechodzi do kolumny czwartej, odpowiadającej rozmiarowi L. Numer tej kolumny jest także efektem działania funkcji X.DOPASUJ
  4. W ten sposób funkcja INDEKS natrafia na komórkę E12 zawierająca stan magazynowy spodni w rozmiarze L.
  5. Jako wynik funkcja zwraca wartość 21.

Zagnieżdżenie funkcji

Pisząc funkcję nie musimy zwracać numerów wierszy i kolumn do komórek pomocniczych, do których odwołuje się funkcja INDEKS. Chociaż takie rozwiązanie wydaje się przejrzystsze i łatwiej pozwala na odnalezienie ewentualnych błędów.
Rozwiązanie scalające wszystkie funkcje do jednej komórki polega na zagnieżdżeniu obu funkcji X.DOPASUJ w funkcji INDEKS odpowiednio jako argumenty odpowiadające numerowi wiersza i numerowi kolumny.

=INDEKS($B$10:$F$39;
X.DOPASUJ(B2;$A$10:$A$39;0);
X.DOPASUJ(B3;$B$9:$F$9;0)
)

Na koniec przypomnienie, że podzielenie zagnieżdżonej funkcji na linie zwiększa jej czytelność, a do kolejnych linii przechodzimy za pomocą kombinacji klawiszy LEWY ALT + ENTER