Konsolidowanie danych w wielu arkuszach

Źródło: https://support.office.com/pl-pl/article/Konsolidowanie-danych-w-wielu-arkuszach-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b

Aby podsumować i przedstawić wyniki z różnych arkuszy, można skonsolidować dane z poszczególnych arkuszy w jednym arkuszu głównym. Arkusze te mogą znajdować się w tym samym skoroszycie co główny arkusz lub w innych skoroszytach. Podczas konsolidacji dane są zbierane w celu ułatwienia ich aktualizowania i agregowania stosownie do potrzeb.

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 skorzystać z 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 kategorii i według położenia.

Konsolidacja według położenia: gdy dane w obszarach źródłowych są uporządkowane w tej samej kolejności 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. Jeśli zależy Ci na większej elastyczności konsolidacji danych według kategorii, rozważ utworzenie tabeli przestawnej.

Uwaga : Przykłady w tym artykule utworzono za pomocą programu Excel 2016, więc widok może się różnić, jeśli używasz innej wersji. Jednak czynności do wykonania są takie same.

Kroki konsolidacji

1. W każdym arkuszu zawierającym dane, które chcesz skonsolidować, wykonaj następujące czynności konfiguracyjne (jeśli jeszcze nie zostały wykonane):

   • Upewnij się, że poszczególne zakresy danych są zapisane w formacie listy: każda kolumna ma etykietę w pierwszym wierszu i zawiera podobne dane, a na liście nie ma pustych wierszy ani kolumn.

   • Umieść poszczególne zakresy w oddzielnych arkuszach, ale nie wprowadzaj żadnych danych w arkuszu głównym, w którym chcesz umieścić skonsolidowane dane — program Excel uzupełni je automatycznie.

   • 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 : Upewnij się, że na prawo od zaznaczonej komórki i poniżej niej jest wystarczająco dużo komórek, aby pomieścić skonsolidowane dane. W przeciwnym razie może dojść do nadpisania istniejących danych.

3. Na karcie Dane w grupie Narzędzia danych kliknij pozycję Konsoliduj.

   

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

   Poniżej przedstawiono przykład z zaznaczonymi trzema zakresami arkuszy.

   

5. Wybieranie danych

   • Jeśli arkusz zawierający dane znajduje się w innym skoroszycie, najpierw kliknij przycisk Przeglądaj, aby zlokalizować skoroszyt, a następnie kliknij przycisk OK, aby zamknąć okno dialogowe Przeglądanie. Program Excel wprowadzi ścieżkę do pliku oraz znak wykrzyknika w polu Odwołanie, po czym będzie można kontynuować wybieranie danych.

   Następnie w polu Odwołanie kliknij przycisk Zwiń okno dialogowe w celu zaznaczenia danych 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.

6. W oknie dialogowym Konsolidowanie kliknij przycisk Dodaj, a następnie powtarzaj powyższe czynności, aby dodać wszystkie potrzebne zakresy.

7. Automatyczne i ręczne aktualizowanie: jeśli chcesz, aby tabela konsolidacji była automatycznie aktualizowana przez program Excel w przypadku zmiany danych źródłowych, zaznacz pole wyboru Utwórz łącze z danymi źródłowymi. Jeśli to pole nie zostanie zaznaczone, można będzie ręcznie aktualizować konsolidację.

   Uwagi : 

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

   • Jeśli po dodaniu zakresów będzie konieczne wprowadzenie zmian, możesz kliknąć zakres w oknie dialogowym Konsolidowanie i zaktualizować go po wyświetleniu w polu Odwołanie, a następnie kliknąć przycisk Dodaj. Spowoduje to utworzenie nowego odwołania do zakresu, dlatego przed ponownym przeprowadzeniem konsolidacji należy usunąć poprzednie odwołanie. Wystarczy zaznaczyć poprzednie odwołanie i nacisnąć klawisz Delete.

8. Po naciśnięciu przycisku OK program Excel wygeneruje skonsolidowane dane. Będą one niesformatowane, dlatego konieczne będzie ich sformatowanie — ale tylko raz, o ile nie uruchomisz konsolidacji ponownie.

   • 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, występujące tylko w jednym zakresie źródłowym.

Konsolidowanie danych za pomocą formuły

• 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. Na przykład aby skonsolidować 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żesz wprowadzić następującą formułę:

  

  Porada : Aby w formule wprowadzić odwołanie do komórki, takie jak Sprzedaż!B4, bez wpisywania całej formuły, należy wpisać formułę do miejsca przeznaczonego dla odwołania, kliknąć kartę arkusza, a następnie kliknąć komórkę. Program Excel automatycznie uzupełni nazwę arkusza i adres komórki. Pamiętaj jednak, że tego typu formuły są podatne na błędy, ponieważ łatwo jest przypadkowo zaznaczyć niewłaściwą komórkę. Ponadto po wprowadzeniu formuły zauważenie błędów może być trudne.

• Jeśli dane do skonsolidowania znajdują się w takich samych komórkach w różnych arkuszach

  Wprowadź formułę zawierającą odwołanie 3-W, które korzysta z zakresu nazw arkuszy. Na przykład, aby skonsolidować dane z komórek A2 w zakresie arkuszy od Sprzedaż do Marketing włącznie w komórce E5 głównego arkusza, możesz wprowadzić następującą formułę: