Konsolidowanie danych w wielu arkuszach

Aby podsumować i raportować wyniki z osobnych arkuszy, możesz skonsolidować dane z każdego arkusza do głównego arkusza. Arkusze mogą znajdować się w tym samym skoroszycie co główny arkusz lub w innych skoroszytach. Konsolidowanie danych polega na złożeniu danych w celu ułatwienia ich 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: Często konsolidowane dane mogą ułatwić tworzenie nowych arkuszy z szablonu arkusza, w którym jest używany jednolity układ. 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 położenia: dane w obszarach źródłowych mają taką samą kolejność i używa 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. Jeśli potrzebujesz bardziej elastycznego konsolidacji według kategorii, warto utworzyć tabelę przestawną .

Uwaga: Przykłady w tym artykule zostały utworzone w programie Excel 2016. Widok może się różnić w przypadku korzystania z innej wersji programu Excel, ale te kroki są takie same.

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

  1. Jeśli jeszcze tego nie zrobiono, skonfiguruj dane w poszczególnych arkuszach, wykonując następujące czynności:

    • Upewnij się, że każdy zakres danych jest w formacie listy. Każda kolumna musi mieć etykietę (nagłówek) w pierwszym wierszu i zawierać podobne dane. Lista nie może zawierać pustych wierszy ani kolumn.

    • Umieść poszczególne zakresy w osobnych arkuszach, ale nie wprowadzaj żadnych danych w głównym arkuszu, w którym planujesz skonsolidować dane. Program Excel wykona to za Ciebie.

    • Upewnij się, że każdy zakres ma ten 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ąć nadpisywania istniejących danych w głównym arkuszu, upewnij się, że na prawo i poniżej tej komórki dla skonsolidowanych danych znajduje się wystarczająca liczba komórek.

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

    Grupa Narzędzia danych na karcie Dane

  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:

    Okno dialogowe Konsolidowanie danych

  5. Zaznacz dane.

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

    Konsolidowanie danych — zwiń okno dialogowe

    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, które należy skonsolidować, znajduje się w innym skoroszycie, kliknij przycisk Przeglądaj , aby zlokalizować ten skoroszyt. Po znalezieniu i kliknięciu przycisku OKprogram Excel wprowadzi ścieżkę pliku w polu odwołanie i doda wykrzyknik do tej ścieżki. Następnie możesz kontynuować zaznaczanie innych danych.

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

    Okno dialogowe Konsolidowanie danych

  6. W oknie podręcznym konsolidowanie kliknij pozycję Dodaj. Powtórz tę czynność, aby dodać wszystkie konsolidowane 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 Utwórz linki do danych źródłowych . Jeśli to pole pozostanie niezaznaczone, 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 menu podręcznym Konsoliduj i zaktualizuj go, wykonując kroki opisane powyżej. Spowoduje to utworzenie nowego odwołania do zakresu, dlatego przed ponownym przeprowadzeniem konsolidacji należy usunąć poprzednie odwołanie. Po prostu wybierz stare odwołanie i naciśnij klawisz Delete.

  8. Kliknij przycisk OK, a program Excel wygeneruje konsolidację dla Ciebie. Opcjonalnie możesz zastosować formatowanie. Formatowanie jest wymagane tylko raz, chyba że zostanie ponownie wybrana Konsolidacja.

    • 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óre nie mają być konsolidowane, mają unikatowe etykiety wyświetlane tylko w jednym zakresie źródłowym.

Jeśli dane do skonsolidowania znajdują się 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łę:

Odwołanie do wielu arkuszy w formule programu Excel
 

Porada: Aby wprowadzić odwołanie do komórki, na przykład sprzedaż! B4 — w formule bez pisania wpisz formułę do miejsca, w którym jest wymagane odwołanie, a następnie kliknij kartę arkusz, a następnie kliknij komórkę. Program Excel zakończy nazwę arkusza i adres komórki. Uwaga: formuły w takich przypadkach mogą być podatne na błędy, ponieważ bardzo łatwo można przypadkowo zaznaczyć niewłaściwą komórkę. Po wprowadzeniu złożonej formuły może być trudne odnalezienie błędu.

Jeśli dane do skonsolidowania znajdują się 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 ze sprzedaży przez marketing włącznie, w komórce E5 arkusza głównego należy wprowadzić następujące polecenie:

Formuła z odwołaniem 3D do arkuszy w programie Excel

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

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

Funkcje programu Excel (lista alfabetyczna)

Funkcje programu Excel (według kategorii)

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×