Konsolidowanie danych w wielu arkuszach

Istnieją dwa sposoby konsolidowania danych według pozycji lub kategorii.

Konsolidacja według pozycji: Dane w obszarach źródłowych mają tę samą kolejność i używają tych samych etykiet. Użyj tej metody, aby skonsolidować dane z serii arkuszy, na przykład arkuszy budżetu poszczególnych działów, utworzonych na podstawie tego samego szablonu.

Konsolidacja według kategorii: gdy dane w obszarach źródłowych nie są uporządkowane w tej samej kolejności, ale mają takie same etykiety. Użyj tej metody, aby skonsolidować dane z serii arkuszy, które mają różne układy, ale takie same etykiety danych.

• Konsolidowanie danych według kategorii przypomina tworzenie tabeli przestawnej. Jednak w przypadku tabeli przestawnej można łatwo zmieniać rozmieszczenie kategorii. Rozważ utworzenie tabeli przestawnej , jeśli potrzebujesz bardziej elastycznej konsolidacji według kategorii.

Wykonaj poniższe czynności, aby skonsolidować kilka arkuszy w głównym arkuszu:

1. Jeśli jeszcze tego nie zrobiono, skonfiguruj dane w każdym arkuszu składów, wykonując następujące czynności:

   • Upewnij się, że każdy zakres danych ma format listy. Każda kolumna musi mieć etykietę (nagłówek) w pierwszym wierszu i zawierać podobne dane. Na liście nie mogą znajdować się puste wiersze ani kolumny.

   • Umieść każdy zakres w osobnym arkuszu, ale nie wprowadzaj niczego w głównym arkuszu, w którym chcesz skonsolidować dane. Program Excel zrobi to za Ciebie.

   • Upewnij się, że każdy zakres ma taki sam układ.

2. W głównym arkuszu kliknij komórkę w lewym górnym rogu obszaru, w którym mają się pojawić skonsolidowane dane.

   Uwaga: Aby uniknąć zastępowania istniejących danych w głównym arkuszu, upewnij się, że na prawo i pod tą komórką jest wyświetlana wystarczająca liczba komórek na potrzeby skonsolidowanych danych.

3. Kliknij pozycję Dane>Konsoliduj (w grupie Narzędzia danych).

   

4. W polu Funkcja wybierz, jaka funkcja podsumowująca programu Excel ma zostać użyta do skonsolidowania danych. Funkcja domyślna to SUMA.

   Oto przykład, w którym wybrano trzy zakresy arkuszy:

   

5. Zaznacz dane.

   Następnie w polu Odwołanie kliknij przycisk Zwiń , aby zmniejszyć panel i zaznaczyć dane w arkuszu.

   

   Kliknij arkusz zawierający dane, które chcesz skonsolidować, zaznacz je, a następnie kliknij przycisk Rozwiń okno dialogowe po prawej stronie, aby wrócić do okna dialogowego Konsolidowanie.
   
   Jeśli arkusz zawierający dane do skonsolidowania znajduje się w innym skoroszycie, kliknij przycisk Przeglądaj , aby zlokalizować ten skoroszyt. Po zlokalizowaniu i kliknięciu przycisku OK program Excel wprowadzi ścieżkę pliku w polu Odwołanie i dołączy do tej ścieżki wykrzyknik. Następnie możesz nadal wybierać inne dane.

   Oto przykład, w którym zaznaczono trzy zakresy arkuszy:

   

6. W oknie podręcznym Konsoliduj kliknij przycisk Dodaj. Powtórz tę czynność, aby dodać wszystkie skonsolidowane zakresy.

7. Aktualizacje automatyczne i ręczne: Jeśli chcesz, aby program Excel automatycznie aktualizował tabelę konsolidacji po zmianie danych źródłowych, po prostu zaznacz pole wyboru Utwórz linki do danych źródłowych . Jeśli to pole nie jest zaznaczone, możesz zaktualizować konsolidację ręcznie.

   Uwagi: 

   • Nie można utworzyć połączeń, jeśli obszar źródłowy i docelowy znajdują się w tym samym arkuszu.

   • Jeśli chcesz zmienić zakres zakresu lub zamienić zakres, kliknij zakres w wyskakującym okienku Konsoliduj i zaktualizuj go, wykonując powyższe kroki. Spowoduje to utworzenie nowego odwołania do zakresu, dlatego przed ponownym przeprowadzeniem konsolidacji należy usunąć poprzednie odwołanie. Wystarczy wybrać stare odwołanie i nacisnąć klawisz Delete.

8. Kliknij przycisk OK, a program Excel wygeneruje konsolidację za Ciebie. Opcjonalnie możesz zastosować formatowanie. Formatowanie jest konieczne tylko raz, chyba że ponownie uruchomisz konsolidację.

   • Dla wszystkich etykiet, które nie są zgodne z etykietami w innych obszarach źródłowych, są tworzone oddzielne wiersze lub kolumny w konsolidacji.

   • Upewnij się, że wszystkie kategorie, których nie chcesz konsolidować, mają unikatowe etykiety wyświetlane tylko w jednym zakresie źródłowym.

Jeśli dane do skonsolidowania są w różnych komórkach w różnych arkuszach:

Wprowadź formułę zawierającą odwołania do komórek w każdym z pozostałych arkuszy. Aby skonsolidować na przykład dane z arkuszy Sprzedaż (w komórce B4), Kadry (w komórce F5) i Marketing (w komórce B9) w komórce A2 głównego arkusza, można wprowadzić następującą formułę:

Jeśli dane do skonsolidowania są w tych samych komórkach w różnych arkuszach:

Wprowadź formułę zawierającą odwołanie 3-W, które korzysta z zakresu nazw arkuszy. Aby na przykład skonsolidować dane w komórkach A2 od Sprzedaż do Marketing włącznie, w komórce E5 głównego arkusza należy wprowadzić następujące dane: