Dostajesz na koniec miesiąca raport godzin. Cztery tabele w jednym arkuszu, każda za inny tydzień. Chcesz z tego zrobić jedno zestawienie, więc kopiujesz i wklejasz. Cztery razy.
A w połowie następnego miesiąca robisz to samo od nowa, bo dane w pierwszym tygodniu się zmieniły.
Nie trzeba. W Excelu 365 jest funkcja, która skleja tabele za Ciebie i aktualizuje wynik sama: STOS.PION.
Punkt wyjścia: cztery tabele, jeden raport
Układ kolumn jest wszędzie taki sam: dzień tygodnia, data, liczba godzin. Różnią się tylko dane. To jest dokładnie ta sytuacja, w której STOS.PION robi robotę za Ciebie.
Jak działa STOS.PION
Funkcja przyjmuje kolejne zakresy jako argumenty i układa je jeden pod drugim. Nic więcej.
=STOS.PION(B5:D12;F6:H12;B19:D25;F19:H25)
Trzy zasady, które warto zapamiętać:
- Pierwszy zakres zaznaczasz razem z nagłówkami. One trafią na górę wyniku.
- Kolejne zakresy zaznaczasz już bez nagłówków. Inaczej nazwy kolumn powtórzą się w środku tabeli.
- Po zamknięciu nawiasu i wciśnięciu Enter wynik rozlewa się w dół sam. Nie kopiujesz formuły do kolejnych komórek.
Daty pokazują się jako liczby? To normalne
Zaznacz kolumnę z datami i ustaw format Data krótka. To kwestia wyświetlania, nie błąd formuły.
Dlaczego to jest coś więcej niż kopiuj-wklej
Kopiowanie tworzy nowe dane. Od momentu wklejenia żyją własnym życiem i przy każdej poprawce w źródle musisz je robić od nowa.
STOS.PION niczego nie kopiuje. On tylko pokazuje dane z innych miejsc. Zmienisz liczbę w tygodniu pierwszym, wynik zmienia się w tej samej sekundzie.
To jest cała różnica i to jest powód, dla którego warto poświęcić na to pięć minut.
Krok dalej: połącz to z tabelami strukturalnymi
Sama formuła ma jedno ograniczenie. Jeżeli podasz sztywny zakres B5:D12, to po dopisaniu kolejnego wiersza dane wyjdą poza zakres i nie pojawią się w wyniku. Trzeba wracać i poprawiać formułę.
Rozwiązanie: zamień każdą tabelę źródłową w tabelę strukturalną. Zaznacz dane, wciśnij Ctrl+T, potwierdź nagłówki i nadaj tabeli sensowną nazwę w polu Nazwa tabeli (karta Projekt tabeli), na przykład Tydzien1.
Wtedy formuła wygląda tak:
=STOS.PION(Tydzien1;Tydzien2;Tydzien3;Tydzien4)Dwie rzeczy, które warto o tym wiedzieć:
- Odwołanie do tabeli po samej nazwie zwraca tylko obszar danych, bez wiersza nagłówków. Nie musisz więc kombinować z pomijaniem nagłówków w kolejnych argumentach.
- Tabela strukturalna rośnie sama. Dopisujesz wiersz na dole, a on od razu wchodzi do wyniku. Formuły nie ruszasz już nigdy.
Jeżeli chcesz mieć nagłówki nad wynikiem, dołóż jako pierwszy argument sam wiersz nagłówka, na przykład Tydzien1[#Nagłówki].
Cały układ: dane, sklejenie, raport
Dopiero razem te trzy elementy dają efekt, dla którego to robimy:
- Dane w tabelach strukturalnych (Ctrl+T), po jednej na tydzień, miesiąc albo oddział. Tam wpisujesz nowe wiersze.
- STOS.PION, który skleja te tabele w jedną listę. Najlepiej w osobnym arkuszu. To jest Twoja warstwa danych.
- Tabela przestawna zbudowana na tej liście. To jest raport, który pokazujesz szefowi.
Efekt: dopisujesz godziny w arkuszu tygodnia, klikasz Odśwież i cały raport jest gotowy. Jedno kliknięcie zamiast pół godziny sklejania.
Jedna sztuczka przy tabeli przestawnej
Skoro wynik formuły rośnie razem z danymi, źródło tabeli przestawnej też musi być elastyczne.
Przy tworzeniu tabeli przestawnej zaznacz całe kolumny, na przykład Dane!$A:$C, zamiast konkretnego zakresu wierszy. Dzięki temu, gdy wynik STOS.PION wydłuży się o kolejne wiersze, tabela przestawna złapie je po zwykłym odświeżeniu.
Dlatego wynik formuły warto trzymać w osobnym arkuszu. Wtedy w tych kolumnach nie ma nic poza nagłówkami i danymi, a puste komórki poniżej nie przeszkadzają.
Pamiętaj tylko o jednym: sam wynik formuły aktualizuje się natychmiast, ale tabela przestawna czeka na kliknięcie Odśwież.
STOS.POZ, czyli to samo w bok
Gdy chcesz sklejać tabele obok siebie, a nie pod sobą, użyj STOS.POZ. Składnia jest identyczna.
=STOS.POZ(Styczen;Luty;Marzec)Kiedy to nie zadziała
- Excel 2019 i 2021 nie mają tych funkcji. STOS.PION i STOS.POZ działają w Microsoft 365 oraz w Excelu dla sieci web. W starszych wersjach zostaje Power Query albo klasyczne sklejanie.
- W wersji angielskiej funkcja nazywa się VSTACK (i odpowiednio HSTACK). Jeżeli szukasz materiałów po angielsku, szukaj pod tą nazwą.
- Błąd #ROZLANIE! oznacza, że w miejscu, gdzie wynik ma się rozlać, coś już stoi. Wyczyść komórki pod formułą.
- Różna liczba kolumn w łączonych zakresach nie zatrzymuje formuły. Excel wyrówna tabelę do najszerszej, a brakujące miejsca wypełni wartościami #N/D. To zwykle sygnał, że tabele źródłowe różnią się układem i trzeba je najpierw ujednolicić.
Gdzie to się przydaje w prawdziwej pracy
Wszędzie tam, gdzie te same dane przychodzą w kawałkach:
- raporty godzin albo sprzedaży rozbite na tygodnie i miesiące,
- zestawienia z kilku oddziałów w tym samym układzie kolumn,
- eksporty z systemu wrzucane co miesiąc do osobnych arkuszy,
- budżet, w którym każdy dział wypełnia swoją tabelę.
Zamiast dwunastu arkuszy i jednego wielkiego kopiowania na koniec roku masz jedną listę, która buduje się sama.
Sprawdź, ile Excela już umiesz
Jeżeli chcesz zobaczyć, gdzie masz luki i co warto podciągnąć w pierwszej kolejności, przygotowałem bezpłatny test z 25 zadaniami.
Pobierz test Excela z 25 zadaniami
A jeżeli STOS.PION przyda Ci się w konkretnym raporcie, napisz w komentarzu w jakim. Zbieram takie przykłady pod kolejne materiały.