• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Pobieranie danych z arkusza wskazanego na liście rozwijanej

Dziś miało być o tabelach przestawnych, ale Arek zapytał mnie o to, jak napisać formułę, która będzie pobierała dane z arkusza wskazanego w jakiejś komórce. Temat ten chodził za mną już od dawna, więc postanowiłam przełożyć wpis o tabelach przestawnych (które lubię używać, ale nie lubię o nich mówić i pisać ;)) i zająć się tym właśnie przypadkiem. Dorzuciłam też coś od siebie, czyli wybór arkusza z listy rozwijanej – zawsze to jakiś dodatkowy bajerek 🙂

A więc dziś mamy podaną sprzedaż (ilość i wartość) handlowców w styczniu i lutym, a chcemy mieć raport, w którym zdecydujemy, z którego miesiąca dane chcemy oglądać i dodatkowo – jaka jest średnia cena sprzedanych przez handlowców produktów. W Excelu mamy więc 2 arkusze z danymi: sty i lut, oraz arkusz Raport, w którym chcemy wyświetlić dane z odpowiedniego arkusza dla wybranych osób. Tak wygląda ta sytuacja:

Struktura danych

Struktura danych

Do dzieła!

Tworzenie listy rozwijanej

Aby stworzyć listę rozwijaną w żółtej komórce I2, w arkuszu Raport, należy po pierwsze ją zaznaczyć. Następnie z menu Dane wybrać przycisk Poprawność danych. W okienku, które się pojawi trzeba wybrać, że dozwolona ma być lista z takim źródłem: Sty;Lut. Na obrazku poniżej możecie zobaczyć poprawnie uzupełnione okienko:

Lista rozwijana

Lista rozwijana

Źródło można oczywiście zdefiniować jako zakres, ja jednak na potrzeby tego ćwiczenia wybrałam metodę ręczną.

Formuła

Ok. To teraz trudniejsza sprawa, czyli formuła. Znów – wybrałam metodę z funkcją PODAJ.POZYCJĘ, choć oczywiście nie jest to konieczne. O to poprosił mnie Arek, więc jest. Zamiast tej funkcji możecie oczywiście z palca wskazać numer kolumny do wyświetlania (w funkcji WYSZUKAJ.PIONOWO). Aczkolwiek rozwiązanie z PODAJ.POZYCJĘ jest zdecydowanie bardziej uniwersalne.

Formuła w komórkach dla ilości i wartości (E3:F12) wygląda następująco:

=WYSZUKAJ.PIONOWO($C3;ADR.POŚR($I$2&"!$D$3:$F$15");PODAJ.POZYCJĘ(E$2;ADR.POŚR($I$2&"!$D$2:$F$2");0);0)

Wszystko wrzucamy do funkcji WYSZUKAJ.PIONOWO. Ma ona za zadanie wyszukanie odpowiedniej wartości dla osoby: mamy różną liczbę osób w arkuszach i ich kolejność jest inna, dlatego właśnie musimy użyć funkcji do odnalezienia wartości, a nie wyświetlić je jak leci. Więcej o funkcji WYSZUKAJ.PIONOWO możesz przeczytać tutaj.

ADR.POŚR ma za zadanie parametryzację nazwy arkusza – pobiera ją sobie z żółtej komórki I2 – tej, w której przed chwilą tworzyliśmy listę rozwijaną. Funkcja ta służy do podawania adresu zakresów za pomocą tekstu. Dzięki temu adresy te można parametryzować, a właśnie o to nam tutaj chodzi.

Na koniec funkcja PODAJ.POZYCJĘ, która sprawdza, z której kolumny WYSZUKAJ.PIONOWO ma pobierać dane. Jedyne co ona robi, to podaje numer pozycji, na której znajduje się nagłówek ilość/wartość, czyli dokładnie to, czego potrzebuje od niej WYSZUKAJ.PIONOWO.

W kreatorze funkcja ta wygląda tak:

Kreator funkcji

Kreator funkcji

Zauważcie, że Excel twierdzi, że drugi i trzeci argument są Nietrwałe. Tak ma być 🙂

Żeby było ładnie, można wszystko wrzucić do funkcji JEŻELI.BŁĄD i wyświetlić np. zero, w przypadku braku danych. Ale to już zostawiam Wam 🙂

Plik do pobrania znajdziecie tutaj:

I filmik:

 

 

 

 


Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych





Dodaj do zakładek Link.

2 odpowiedzi na „Pobieranie danych z arkusza wskazanego na liście rozwijanej

  1. robik mówi:

    Działa i to najważniejsze. Ale mam propozycję na uproszczenie formuły.
    W pierwszej kolejności nazywam zakresy danych: Sty!$D$2:$F$15 =”Sty”
    Lut!$D$2:$F$15 =”Lut”
    następnie odwołuję się do tych nazw zamiast do arkuszy.
    =WYSZUKAJ.PIONOWO($C3;ADR.POŚR($I$2);2;0)
    Numer kolumny podaję cyframi 2 dla ilość i 3 dla wartość bo w tym przypadku tabelki mają taki sam układ, gdyby mogły być różne to wtedy podaj pozycję czy też wyszukaj poziomo.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *