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

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

Ź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

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.