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

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.

Polecamy także:

• Jak stworzyć panel z wykresami o sprzedaży handlowców?
• Jak wyszukać auto funkcjami: ORAZ, LUB i formułą JEŻELI?

Kamil Skonecki