Konsolidowanie danych w wielu arkuszach

Aby podsumować wyniki z różnych arkuszy i utworzyć raport na ich temat, można skonsolidować dane z poszczególnych arkuszy w głównym arkuszu. Arkusze mogą być w tym samym skoroszycie co główny arkusz lub w innych skoroszytach. Podczas konsolidowania dane są zbierane w celu ich ł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: W przypadku częstego konsolidowania danych warto utworzyć nowe arkusze na podstawie szablonu arkusza o spójnym układzie. 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ą takie same porządek i mają takie same etykiety. 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 utworzono za pomocą Excel 2016. Mimo że widok może się różnić w przypadku używania innej wersji Excel, kroki są takie same.

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

  1. Jeśli jeszcze tego nie zrobić, skonfiguruj dane w każdym arkuszu składników, 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. W dowolnym miejscu listy nie mogą znajdować się żadne puste wiersze ani kolumny.

    • Umieść każdy zakres w osobnym arkuszu, ale nie wprowadzaj żadnych danych w głównym arkuszu, w którym mają być konsolidowanie danych. 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ąć zapisania istniejących danych w głównym arkuszu, należy upewnić się, że z prawej i poniżej tej komórki jest wystarczająco dużo komórek na konsolidowane dane.

  3. Kliknij pozycję>danych (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 lokalizacji i kliknięciu przycisku OKExcel wprowadź ścieżkę pliku w polu Odwołanie i dołączy do tej ścieżki wykrzyknik. 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 konsolidowanie zakresów.

  7. Automatyczne a ręczne aktualizacje: Jeśli chcesz, Excel automatyczną aktualizację tabeli konsolidacji po zmianie danych źródłowych, po prostu zaznacz pole wyboru Utwórz łącza do danych źródłowych. Jeśli to pole wyboru pozostanie niezaznaczone, można 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 — lub zamienić zakres — kliknij zakres w wyskakującym okienku Konsoliduj i zaktualizuj go, korzystając z powyższych kroków. 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,Excel spowoduje wygenerowanie konsolidacji. Opcjonalnie możesz zastosować formatowanie. Formatowanie jest konieczne tylko raz, chyba że ponownie uruchomić 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, które są 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łę:

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 wpisywania wpisz formułę do miejsca, w którym potrzebujesz odwołania, a następnie kliknij kartę arkusza, a następnie kliknij komórkę. Excel uzupełni nazwę arkusza i adres komórki. UWAGA: formuły w takich przypadkach mogą być podatne na błędy, ponieważ łatwo jest przypadkowo zaznaczyć niewłaściwą komórkę. Ponadto po wprowadzeniu złożonej formuły zauważenia błędu 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 z komórek A2 w komórkach od Sprzedaż do Marketing włącznie w komórce E5 głównego arkusza, można wprowadzić następującą nazwę:

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.

×