Wyszukiwania danych w zbiorach rekordów za pomocą funkcji WYSZUKAJ.PIONOWO

Załóżmy, że mamy opracować skoroszyt zawierający dane przychodów i obrotów firmy cateringowej serwującej obiady w szkołach w poszczególnych województwach, w ostatnich trzech latach. Dodatkowo do tych danych mamy utworzyć wyszukiwarkę. Założenia są takie, że po określeniu województwa i rodzaju danych powinna pojawić się nam odpowiednia kwota. Dodatkowo kryteria wyszukiwania powinny być wybierane z rozwijanych list, aby uniknąć literówek przy wpisywaniu. Pobierz plik z przykładowymi danymi znajdujący się poniżej filmu.

Aby w komórce C2 drugiego arkusza była pokazywana wartość określona przez kryteria w komórkach A2 i B2, wpisujemy w komórce C2 następującą formułę:

=WYSZUKAJ.PIONOWO(A2;Dane!A1:G17;PODAJ.POZYCJĘ(B2;Dane!A1:G1;0))

W trzecim argumencie funkcji WYSZUKAJ. PIONOWO, za pomocą funkcji PODAJ.POZYCJĘ obliczany jest numer kolumny, w której znajduje się wartość wpisana do komórki B2.

Jeśli chcemy stworzyć listy rozwijane w komórkach A2 i B2, utwórzmy zakresy nazwane, a następnie korzystając z okna dialogowego sprawdzimy poprawność danych.

W tym celu:

1. W arkuszu Dane zaznacz komórki zawierające nazwy województw: A2:A17 i na karcie Formuły, w grupie poleceń Nazwy zdefiniowane wybierz Menedżer nazw i naciśnij Nowy.

2. W polu Nazwa wpisz: Województwa i naciśnij OK, a potem ponownie przycisk Nowy.

3. Kliknij w polu Odwołuje się do i usuń wpisany zakres komórek. Następnie zaznacz komórki zawierające rodzaj danych: B1:G1, w polu Nazwa wpisz: Rodzaj i naciśnij OK.

4. Przejdź do drugiego arkusza, zaznacz komórkę A2 i na karcie Dane, w grupie poleceń Narzędzia danych wybierz Poprawność danych. W oknie, które się pojawi, w polu Dozwolone wybierz Lista, a w polu Źródło wpisz formułę:

=Województwa

i naciśnij przycisk OK.

5. Zaznacz komórkę B2 i ustaw listę rozwijaną w komórce za pomocą okna Sprawdzanie poprawności danych w polu Źródło, wpisując formułę:

=Rodzaj

Teraz w komórkach A2 i B2 będziemy mogli wybierać z listy rozwijanej odpowiednio nazwę województwa i rodzaj danych.

Jeśli w przyszłości zmieni się zakres komórek, w których wpisane są nazwy województw lub rodzaje danych, to wystarczy, że zmodyfikujemy odwołanie do komórek nazwanego zakresu w oknie dialogowym służącym do edytowania nazw. Komórek z listą rozwijaną nie musimy modyfikować.

R1GtSn65V3JJ8

Nagranie filmowe dotyczące wyszukiwania danych w zbiorach rekordów za pomocą funkcji WYSZUKAJ.PIONOWO

Nagranie filmowe dotyczące wyszukiwania danych w zbiorach rekordów za pomocą funkcji WYSZUKAJ.PIONOWO

Film dostępny pod adresem /preview/resource/R1GtSn65V3JJ8

Nagranie filmowe dotyczące wyszukiwania danych w zbiorach rekordów za pomocą funkcji WYSZUKAJ.PIONOWO

Ćwiczenie 1

Załóżmy, że pomagasz w sklepie rodziców. Przyjmijmy, że mamy arkusz w którym znajdują się informacje o zawartości magazynu z danego miesiąca. Umożliw wyszukiwanie liczby sztuk danego produktu w danym magazynie.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Formuła z komórki C2:

=WYSZUKAJ.PIONOWO(A2;Styczeń!A1:G17;PODAJ.POZYCJĘ(B2;Styczeń!A1:G1;0))

R1Z4y8kIp82li

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł NAZWA PRODUKTU. W komórce A2 wprowadzono symbol produktu. W kolumnie B, w komórce B1 wpisano tytuł MAGAZYN. W komórce B2 wprowadzono symbol magazynu. W kolumnie C, w komórce C1 wpisano tytuł LICZBA SZTUK. W komórce C2 wprowadzono daną liczbową.

2

Ćwiczenie 2

Podobnie jak w ćwiczeniu pierwszym przyjmijmy, że mamy arkusz, w którym znajdują się informacje o zawartości magazynu z danego miesiąca. Zmodyfikuj teraz ten akrusz. Umożliw wyszukiwanie liczby sztuk danego produktu w danym magazynie w konkretnym miesiącu.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Zawartość komórki D2:

=WYSZUKAJ.PIONOWO(B2;ADR.POŚR(A2&”!A1:G17”);PODAJ.POZYCJĘ(C2;ADR.POŚR(A2&”!A1:G1”);0))

RwrSNvykXdIgf

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł MIESIĄC. W komórce A2 wprowadzono nazwę miesiąca. W kolumnie B, w komórce B1 wpisano tytuł NAZWA PRODUKTU. W komórce B2 wprowadzono symbol produktu. W kolumnie C, w komórce C1 wpisano tytuł MAGAZYN. W komórce C2 wprowadzono symbol magazynu. W kolumnie D, w komórce D1 wpisano tytuł LICZBA SZTUK. W komórce D2 wpisano daną liczbową.