Konsoliduj dane w programie Excel

Co to jest konsolidacja danych w programie Excel?

Podczas pracy w programie Excel mamy wiele danych dostępnych w różnych arkuszach roboczych, a jeśli chcemy scalić lub skonsolidować dane w jednym arkuszu, program Excel ma funkcję, aby to zrobić po otwarciu wszystkich arkuszy, które mają dane do skonsolidowania, przycisk Konsoliduj jest dostępne w sekcji narzędzi danych na karcie Dane.

Jeśli nie znasz narzędzia Konsolidacja w programie Excel, ten artykuł pomoże Ci zrozumieć dynamikę narzędzia Konsolidacja w programie Excel.

Zwykle, gdy otrzymujemy dane od różnych osób, otrzymujemy wiele arkuszy roboczych. Konsolidacja wszystkiego to herkulesowe zadanie, szczególnie dla średniozaawansowanych. Na początku mojej kariery zbierałem dane z wielu różnych źródeł.

Na przykład byłem używany do pobierania danych sprzedaży z 4 różnych stref z głowic strefowych. Cztery różne strefy zawierają 4 różne arkusze. Na końcu danych musiałem zebrać wszystko w jednym arkuszu i utworzyć bazę danych do celów analizy. Robiłem to ręcznie, tradycyjnym sposobem kopiowania i wklejania.

Ale spędziłem trochę czasu ucząc się nowych rzeczy, a konsolidacja narzędzi jest jednym z nich i bardzo pomogła mi w zestawianiu danych przy minimalnym wysiłku.

Jak skonsolidować dane w programie Excel? (Krok po kroku z przykładem)

Możesz pobrać ten szablon programu Excel do konsolidacji danych - Szablon programu Excel do konsolidacji danych

Mam dane sprzedaży z czterech różnych regionów w czterech różnych arkuszach.

Każdy zawiera ten sam format i taką samą liczbę wierszy i kolumn. Muszę skonsolidować wszystkie arkusze w jednym arkuszu o nazwie Podsumowanie.

Utworzyłem ten sam szablon, co w pozostałych 4 arkuszach.

  • Krok 1: Po utworzeniu powyższego szablonu w arkuszu Podsumowanie wybierz komórkę B2, w której chcemy skonsolidować wszystkie 4 różne regiony.

  • Krok 2: Teraz przejdź do Dane> Konsoliduj

  • Krok 3: Po wybraniu opcji Konsoliduj zobaczysz poniższe okno.

  • Krok 4: To jest okno, którego potrzebujemy, aby zrobić magię. Po pierwsze, widzimy rozwijaną listę Funkcja z tej rozwijanej listy, musimy wybrać nasz typ konsolidacji. Tutaj musimy wybrać funkcję SUMA w programie Excel, ponieważ konsolidujemy wszystkie dane sprzedaży strefy i tworzymy raport podsumowujący. Więc wybierz SUMA pod tą opcją.
  • Krok 5: Następnym krokiem jest odniesienie do wszystkich 4 arkuszy różnych regionów. Kliknij pole odniesienia i przejdź do pierwszego arkusza arkusza całej strefy, tj . Południe. Po wybraniu zakresu kliknij przycisk Dodaj.

  • Krok 6: Po kliknięciu przycisku Dodaj dodaje odwołania do wszystkich odwołań do komórek.

  • Krok 7: Podobnie utwórz łącze programu Excel do arkuszy wszystkich innych regionów. Wreszcie będzie wyglądać tak.

  • Krok 8: Teraz kliknij przycisk OK, Twoje skonsolidowane dane są gotowe bez żadnych błędów.

  • Krok 9: Utworzyliśmy teraz raport podsumowujący, który nie jest raportem dynamicznym, ponieważ nie utworzyliśmy łącza do innych tabel. Jeśli są jakieś zmiany, tabele pozostają takie same i nasz raport w tym przypadku nie działa. Musimy połączyć tabelę z innymi arkuszami, aby raport był dynamiczny.
  • Krok 10: W oknie dialogowym konsolidacji zaznacz pole wyboru Utwórz łącza do danych źródłowych. Kliknij OK.

Następnie kończy proces.

  • Krok 11: Teraz utworzył funkcję SUMA. Po prawej stronie arkusza kalkulacyjnego znajduje się ikona plusa (+), kliknij ikonę plusa (+), aby zobaczyć podział.

Inne opcje w konsolidacji

  1. Funkcja: to jest lista wszystkich funkcji dostępnych w narzędziu Konsolidacja. Możesz użyć funkcji SUMA, ŚREDNIA, LICZBA, MAX, MIN w programie Excel itp
  2. Odniesienie: tutaj musimy wybrać zakresy różnych arkuszy, aby skonsolidować dane w jednym arkuszu.
  3. Przeglądaj: na tym polega piękno opcji konsolidacji. Nie tylko konsoliduje się z tego samego skoroszytu, ale może również pobierać dane z innych skoroszytów.
  4. Dodaj i usuń: tutaj możemy dodawać i usuwać wszystkie odniesienia.
  5. Górny wiersz: użyj górnego wiersza jako nagłówków wierszy
  6. Lewa kolumna: użyj lewej strony danych jako nagłówków kolumn.
  7. Utwórz łącza do danych źródłowych: spowoduje to, że raport będzie dynamiczny. Opcja skonsolidowana tworzy formułę dla wszystkich pozostałych arkuszy, aby raport był naprawdę dynamiczny.

Rzeczy do zapamiętania

  • W przypadku konsolidacji zakresy opcji wszystkich arkuszy roboczych powinny być takie same we wszystkich arkuszach.
  • W opcji konsolidacji możemy skorzystać z dowolnej funkcji.