Odwołania 3D w Excelu, czy słyszałeś kiedyś o nich? A może używałeś ich nie zdając sobie sprawy czym dokładnie są? Posługiwanie się odwołaniami 3D daje nam nowe możliwości rozwiązania problemów przy budowaniu różnych raportów i zestawień, dlatego warto zrozumieć ich działanie oraz przykłady zastosowania.
Spis Treści
Czym są odwołania 3D w Excelu?
Pojęcie 3D oczywiście odnosi się do wymiarów. Zwykle nasz świat postrzegamy w maksymalnie 4 wymiarach. Świat jednowymiarowy to oś, dwuwymiarowy to np. układ współrzędnych, trójwymiarowych widzimy rozglądając się dookoła. Czwarty wymiar to czas, w którym zmienia się otaczająca nas rzeczywistość. Więcej wymiarów trudno byłoby nam zrozumieć i prosto wytłumaczyć ich działanie. Co i innego w świecie programów komputerowych.
Jak myślisz, ile wymiarów posiada Excel? Odpowiedzi będą różne. Na pierwszy rzut oka widzimy Excel jak dwuwymiarowy układ składający się z wierszy i kolumn. Patrząc trochę szerzej pojawia się trzeci wymiar, czyli arkusze, bo przecież komórka A1 nie występuje tylko raz w całym skoroszycie, jest w każdym arkuszu. Czwarty wymiar to skoroszyty, ta sama komórka w takim samym arkuszu, ale w różnych plikach. W ten sposób patrząc możemy dostrzec wielowymiarową budowę Excela.
Zatem czym są odwołania 3D w Excelu? To nic innego jak odwołanie w formule do zakresu komórek z różnych arkuszy jednocześnie. Ma to swoje bardzo praktyczne zastosowanie szczególnie w przypadku, gdy w poszczególnych arkuszach mamy wystandaryzowane układy danych.
Przykład użycia odwołań 3D w Excelu – podstawowe zastosowanie
Nasz przykładowy plik zawiera tabelę z wartością sprzedanych produktów od poszczególnych przedstawicieli handlowych. W każdym arkuszu mamy wartości z odrębnych miesięcy, a układ danych jest identyczny w każdej tabeli.
Naszym zadaniem będzie przygotować podsumowanie, które zsumuje wartości ze wszystkich miesięcy. W tym celu w arkuszu podsumowujących wstawiamy sobie tabelę z takim samym układem jak pozostałe i wpisujemy sumę i klikamy na komórkę D4 w arkuszu Styczeń. Aby sumowane były wartości z całego roku klikamy na arkusz Grudzień trzymając wciśnięty klawisz Shift. W ten sposób suma dotyczy komórki D4 od arkusza Styczeń do arkusza Grudzień.
Jak widać formuła jest skrajnie prosta i nie musimy się męczyć z sumowaniem 12 komórek, przez które formuła stałaby się bardzo długa. Poniżej gotowe podsumowanie przygotowane w kilka sekund.
Przykład użycia odwołań 3D w Excelu – wybór zakresu sumowania na podstawie list wyboru
Jeśli tworzymy raport, w którym użytkownik chciałby przeglądać sumy z dowolnych zakresów czasu to wprowadzenie sztywnych formuł nie ma większego sensu. Warto tu wprowadzić listy rozwijane z nazwami miesięcy tak, aby użytkownik w łatwy sposób określił okres do podsumowania. Jak przygotować zwykłe listy rozwijane oraz listy zależne pisałem tutaj Lista rozwijana zależna.
Tak więc najpierw przygotowujemy układ podsumowania. Wstawiamy tabelę taką jak w pozostałych arkuszach, dodajemy dwie listy rozwijane dla zakresu od oraz zakresu do. Po prawej lista miesięcy, którą oczywiście dobrze byłoby ukryć przed użytkownikiem raportu albo przenieść do innego arkusza. Zawsze warto trzymać się zasady, aby pokazywać tylko to co jest istotne.
Poniżej na screenie przedstawiona jest cała formuła dla pierwszej komórki. W celu wyjaśnienia jej działania rozbijmy ją na fragmenty.
Wyjaśnienie formuł
Suma dla zakresu jak zawsze musi mieć określoną komórkę, od której oraz do której będzie sumować wartości. Stąd jej zawartość składa się z dwóch bloków które opierają się na formule PRZESUNIĘCIE(), lewy odnosi się do miesiąca wybranego z listy Zakres od, a prawy blok do miesiąca dla Zakresu do. Dwie funkcje PRZESUNIĘCIE() rozdzielone dwukropkiem tworzą zakres komórek. Zakres jaki nas interesuje to zakres nazw arkuszy. Funkcja PODAJ.POZYCJĘ określa na którym miejscu na liście znajduje się dana wartość, w tym przypadku nazwa miesiąca. Dla lutego jest to wartość 2, dla czerwca 6.
W funkcji PRZESUNIĘCIE() pierwszy argument to komórka, od której chcemy się przesuwać, drugi to o ile wierszy chcemy przysunąć się, trzeci to przesunięcie w kolumnach. Zatem funkcja PODAJ.POZYCJE użyta jako drugi argument funkcji PRZESUNIĘCIE określa, że pierwsza komórka to będzie K4, a druga K8. W efekcie otrzymaliśmy zakres K4:K8 i jest to nic innego jak tablica nazw arkuszy. Jest to takie proste dzięki tablicom dynamicznym, o których pisałem tutaj Dynamiczne formuły tablicowe.
Trzecia używana formuła to ADRES(). Tworzy ona tekstową nazwę zakresu z podanych argumentów. W formule tej pierwszy argument to numer wiersza, dalej numer kolumny, typ zakresu (względny/bezwzględny), styl odwołań (A1/W1K1) oraz na końcu nazwa arkusza. Np. ADRES(1,1) to po prostu komórka A1. My zaczynamy sumowe dla od komórki D4 z arkuszy z miesięcy, dlatego pierwszy i drugi argument tej funkcji to 4. Aby te wartości były zmienne to wystarczy wprowadzić zapis Wiersz()-5 (dziewiąty wiersz – 5 = 4), oraz NR.KOLUMNY()-1 (piąta kolumna – 1 = 4). Trzeci i czwarty argument nie ma większego znaczenia, więc wprowadzone są wartości 1. Ostatni argument, czyli nazwa miesiąca, a listę nazw miesięcy otrzymaliśmy już wcześniej.
Na sam koniec używamy formuły ADR.POŚR. Formuła adres pośredni najczęściej wykorzystywana jest z formułą ADRES, ponieważ tłumaczy zapis tekstowy na zwykłe odwołania do komórek. Wrzucając to wszystko do sumy, o której pisałem na początku tego przykładu otrzymujemy sumę ze wszystkich komórek D4 od lutego do czerwca i co ważne możemy tę formułę przeciągać w każdą stronę.
Na co zwrócić uwagę
Praca na zakresie arkuszy ma podobne ograniczenia jak praca na zakresie komórek. Zakres to nic innego jak wartość początkowa, wartość końcowa i wszystko co jest pomiędzy. Dlatego kolejność arkuszy jest tutaj kluczowa do poprawnego działania formuł. Jeśli Luty przesuniemy za kwiecień to w tym przykładzie otrzymamy sumę z lutego, maja i czerwca. Tak samo, jeśli marzec przesunęlibyśmy za czerwiec to też nie zostanie uwzględniony w sumowaniu. Jak zawsze o poprawności działania raportów decyduje poprawność danych, porządek i standaryzacja.
Ogromną korzyść z korzystania z odwołań 3D uzyskujemy, gdy nie chcemy angażować Power Query do przekształceń i połączenia danych lub raport ma działać w sposób bardzo prosty.
