Aby podsumować i przedstawić wyniki z oddzielnych arkuszy, można skonsolidować dane z każdego arkusza w głównym arkuszu. Arkusze mogą znajdować się w tym samym skoroszycie co główny arkusz lub w innych skoroszytach. Podczas konsolidacji dane są zbierane w celu łatwego aktualizowania i agregowania w razie potrzeby.
Na przykład jeśli masz arkusze wydatków poszczególnych oddziałów regionalnych, możesz użyć funkcji konsolidacji w celu wprowadzenia tych danych do głównego arkusza wydatków firmy. Ten główny arkusz może również zawierać sumy i średnie wartości sprzedaży, bieżące stany magazynowe i najlepiej sprzedające się produkty w całej firmie.
Porada: Jeśli dane są często konsolidowane, może to ułatwić tworzenie nowych arkuszy na podstawie szablonu arkusza ze spójnym układem. Aby dowiedzieć się więcej na temat szablonów, zobacz: Tworzenie szablonu. To również doskonały moment na skonfigurowanie szablonu przy użyciu tabel programu Excel.
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.
Uwaga: Przykłady w tym artykule zostały utworzone w programie Excel 2016. Choć widok może się różnić w przypadku korzystania z innej wersji programu Excel, kroki są takie same.
Wykonaj poniższe czynności, aby skonsolidować kilka arkuszy w głównym arkuszu:
-
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.
-
-
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.
-
Kliknij pozycję Dane>Konsoliduj (w grupie Narzędzia danych).
-
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:
-
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:
-
W oknie podręcznym Konsoliduj kliknij przycisk Dodaj. Powtórz tę czynność, aby dodać wszystkie skonsolidowane zakresy.
-
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ąć Delete.
-
-
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łę:
Porada: Aby wprowadzić odwołanie do komórki, na przykład Sprzedaż! B4 — w formule bez wpisywania wpisz formułę do punktu, w którym jest potrzebne odwołanie, a następnie kliknij kartę arkusza, a następnie kliknij komórkę. Program Excel uzupełni nazwę arkusza i adres komórki. UWAGA: formuły w takich przypadkach mogą być podatne na błędy, ponieważ bardzo łatwo jest przypadkowo zaznaczyć niewłaściwą komórkę. Ponadto wykrycie błędu po wprowadzeniu złożonej formuły może być trudne.
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:
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
Zobacz też
Omówienie formuł w programie Excel
Jak unikać niepoprawnych formuł
Znajdowanie i poprawianie błędów w formułach
Skróty klawiaturowe i klawisze funkcyjne w programie Excel