Jak pobrać dane o sprzedaży z różnych arkuszy?

Formuły
Automatyzacja pracy
Średniozaawansowany
0:00czas trwania

Załóżmy, że mamy bazę danych prezentującą wyniki sprzedaży handlowców z naszego zespołu za ten i poprzedni rok. Chcielibyśmy za pomocą listy rozwijanej dynamicznie zmieniać dane zagnieżdżone w dwóch innych arkuszach prezentujących również wyniki za 2017 i 2018 rok. W dzisiejszej lekcji pokażemy, jak stworzyć formułę umożliwiającą dynamiczne pobieranie danych z różnych arkuszy z użyciem listy rozwijanej. Jest to niezwykle przydatne w szczególności kiedy np. mamy bardzo dużo arkuszy, a musimy w szybki i przejrzysty sposób prezentować skumulowane dane.

Opublikowane: 29 kwietnia 2020
Lekcję prowadzi: Kamil Skonecki
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Zobacz krok po kroku jak wykonać lekcję

Z lekcji dowiesz się:

  • Jak skumulować dane z wielu arkuszy?

  • Jak stworzyć listę rozwijaną?

  • Przy jakich zadaniach używać dynamicznego pobierania danych?

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Z lekcji dowiesz się:

  • Jak skumulować dane z wielu arkuszy?

  • Jak stworzyć listę rozwijaną?

  • Przy jakich zadaniach używać dynamicznego pobierania danych?

W kilku arkuszach posiadamy dane o sprzedaży z kilku ostatnich lat – w arkuszu głównym (rys. 1), arkuszu prezentującym dane z 2017 r. (rys. 2) oraz arkuszu prezentującym dane z 2018 r. (rys. 3).

(Rys. 1)

(Rys. 2)

(Rys. 3)

Arkusze z których chcemy pobrać dane, to nic innego, jak rok w którym dane te zostały zaraportowane.

Chcemy aby nasza tabelka wypełniała się prawidłowymi danymi w zależności od roku (jednocześnie nazwa arkusza) jaki wybierzemy z listy rozwijanej.

Zacznijmy od przygotowania listy rozwijanej. Z zakładki dane wybieramy poprawność danych. Korzystamy z opcji listy i zaznaczamy wartości jakie nas interesują (2017, 2018) (rys. 4) i (rys. 5).

(Rys. 4)

(Rys. 5)

Teraz możemy przejść do najważniejszej części czyli do napisania formuły.

Zaczynamy od funkcji wyszukaj pionowo i jako wartość po której będziemy wyszukiwać dalsze zmienne wskazujemy danego handlowca czyli komórkę B2. Następnie jako zakres tablicy wybieramy obszar danych z drugiego arkusza. Tutaj stosujemy odwołanie za pomocą funkcji adres pośredni. Umożliwi to dynamiczna zmianę arkusza poprzez wybór wartości z listy rozwijanej - komórka G2 (rys. 6).

(Rys. 6)

Dla lepszego zrozumienia tego fragmentu, zobacz jak działałoby odwołanie, do innego arkusza, bez zastosowania jakiejkolwiek funkcji (rys. 7).

(Rys. 7)

Czyli reasumując ADR.POŚR($G$2 = ‘2017’ – oczywiście przy założeniu ze w komórce G2 wybierzemy 2017.

Wykorzystamy opcje szacuj formulę aby zaprezentować jak finalnie wygląda ten fragment funkcji (rys. 8).

(Rys. 8)

Widać, ze wartość jest identyczna jak w momencie pobieranie ręcznie formuły z innego arkusza.

Możemy przejść do dalszego fragmentu. Za pomocą funkcji podaj pozycje ustalamy numer indeksu kolumny, który ma być przeszukiwany w funkcji wyszukaj pionowo. Ponownie korzystamy z zabiegu wykorzystania funkcji adres pośredni, wiec nie będę ponownie tłumaczył tego fragmentu. Aby poprawnie dopasować do siebie dane jako wartość do której się odnosimy wskazujemy komórkę C1 czyli nazwę kolumny Ilość produktów, a w przeszukiwanym zakresie cały zakres kolumn w arkuszy do którego odnosić się będzie formuła. W ten sposób będziemy mogli swobodnie przesuwać formulę (rys. 9).

(Rys. 9)

Formuła mogłaby wyglądać teoretycznie w ten sposób, ale przecież chcemy pisać bardziej „Inteligentne” formuły. Załóżmy że mamy 100 kolumn i taką formule trzeba tyle razy przekopiować, dodatkowo zmieniając numer indeksu, nie wspominając o tym, że kolumny mogę być ułożone w inny sposób. Przygotowana formuła nie dość że ogranicza i optymalizuje czas naszej pracy, to dodatkowo jest dużo bardziej funkcjonalna.

Końcowa postać formuły (rys. 10).

(Rys. 10)

Widzimy, że napisana formuła to nic innego jak klasyczne wyszukaj pionowo, tylko odpowiednio zmodyfikowane.

Wyszukaj pionowo:

Szukana wartość – komórka z handlowcem

Tabela_tablica - ADR.POŚR($G$2&"!$B$2:$D$13")

Nr indeksu kolumny - PODAJ.POZYCJĘ(C$1;ADR.POŚR($G$2&"!$B$1:$D$1");0)

Przeszukiwany zakres – 0 (dokładne dopasowanie)

Jak widać w stosunkowo prosty sposób można napisać bardzo fajną formułę, która umożliwia dynamiczną zmianę danych w zależności od wybranego arkusza. Jest to niezwykle przydatne w szczególności, kiedy np. mamy bardzo dużo arkuszy, a musimy w szybki i przejrzysty sposób prezentować skumulowane dane.

Kamil Skonecki

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz gotowy arkusz z lekcji
Dostępne po opłaceniu
Kolejne kroki dostępne dla użytkowników którzy wykupili pełny dostęp

Jeszcze się wahasz?

Zobacz, co zyskasz, mając pełny dostęp:

  • Dostęp do wszystkich lekcji video
  • Materiały do ćwiczeń
  • Dodatkowe artykuły z trikami
  • Formularze gotowe od razu do użycia
  • E-booki pogłębiające Twoją wiedzę
  • Certyfikat potwierdzający Twoje umiejętności
Sprawdź co oferuje serwis Sprytny Excel »

Masz już konto w serwisie?

Zaloguj się

Adres e-mail lub login:

Hasło

Nie pamiętam hasła
- Twój pierwszy krok do lepszych wyników
Oglądaj pełne lekcje i wiedz więcej!
Masz już konto w serwisie?
Zaloguj się »

wiper-pixel