fbpx

Pobieranie danych z arkusza wskazanego na liście rozwijanej

03.01.2017 | Analizy sprzedaży, ECP2, Triki

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

BTW: jeśli chcesz zobaczyć, jak automatycznie stworzyć listę rozwijaną ze sposobem arkuszy – zajrzyj tutaj.

Ok, 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 Tobie 🙂

Plik do pobrania znajdziesz tutaj:
MalinowyExcel_POST_Adres pośredni dw.xlsx

 

I wideo z instrukcją krok po kroku:

Powiązane produkty:

  • WEBINAR: Funkcja WYSZUKAJ.PIONOWO. Dowiesz się na nim jak działa ta funkcja, łącznie ze wszystkimi jej haczykami (a jest ich trochę…), poznasz jej ciekawe zastosowania i przestaniesz już używać wielokrotnie zagnieżdżonego JEŻELI 🙂
  • WEBINAR: Listy rozwijane. Tutaj zobaczysz jeszcze inne opcje wykorzystania list rozwijanych na potrzeby wykresów i nie tylko.

 

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

6 komentarzy

  1. 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.

    Reply
    • Super, jak najbardziej!

      Reply
  2. ADR.POŚR… Excelu znowu cie kocham! dzięki piękne, zmarnowałem na to parę godzin!:)

    Reply
    • Mam nadzieję, że następnym razem już ADR.POŚR będzie na tapecie 😉

      Reply
  3. Cześć, a jeśli chciałbym zrobić raport roczny dla każdego pracownika oddzielnie?
    Np. w arkuszu RAPORT z listy rozwijanej wybieram imię a w tabeli poniżej wyświetlają się wyniki z kilku miesięcy (kilku arkuszy).
    Dzięki za pomoc! 😀

    Reply
    • Hej, to ja tutaj zastosowałabym formułę 3D, czyli tak:
      1. oddzielny arkusz ze wszystkimi nazwiskami
      2. Formuła do pierwszego nazwiska: =SUMA(Sty:Lut!F3)

      A najlepiej to w Power Query 😉

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest