Konsolidacja programu Excel (spis treści)
- Wprowadzenie do konsolidacji w programie Excel
- Jak konsolidować dane w wielu arkuszach?
Konsoliduj dane w programie Excel
Konsolidacja ogólnie oznacza zbieranie lub gromadzenie niektórych wspólnych rzeczy w jednym miejscu. Może to być dowolny rodzaj danych lub jakakolwiek informacja lub liczba. Konsolidacja w programie Excel oznacza funkcję, która łączy informacje lub dane z różnych skoroszytów / arkuszy w jeden arkusz / skoroszyt. Innymi słowy, ta funkcja pobiera dane z różnych arkuszy, aby połączyć je w pojedynczy arkusz, który można łatwo zaktualizować.
Na przykład, jeśli masz cztery arkusze. Każdy arkusz ma wydatki na kwartał. Możesz potrzebować jednego arkusza z wydatkami ze wszystkich czterech kwartałów. Wtedy zaczyna się funkcja konsolidacji. Gdy dane w arkuszach roboczych znajdują się w tej samej pozycji, to znaczy pod tymi samymi etykietami i w tej samej kolejności. Używamy „Konsolidacji według pozycji”. A kiedy dane w arkuszach nie są pod tymi samymi etykietami i nie w tej samej kolejności. Następnie używamy „Konsolidacji według kategorii”. To jest jak tworzenie tabeli przestawnej.
Jak konsolidować dane w wielu arkuszach?
Zobaczmy, jak skonsolidować dane w wielu arkuszach z kilkoma przykładami.
Przykład 1 - Konsolidacja danych w tym samym skoroszycie
Załóżmy, że mamy arkusz roboczy, w którym mamy dane sprzedaży z czterema arkuszami oznaczonymi według ich regionu jako Północ, Południe, Wschód i Zachód. Teraz chcielibyśmy skonsolidować dane w jednym miejscu, a nie w arkuszu w tym samym skoroszycie. Istnieje piąty arkusz o nazwie skonsolidowany plik.
Ten przykład pokazuje skonsolidowaną sprzedaż dla wszystkich regionów. Oto przykładowe pliki danych. Tutaj możesz zobaczyć różne nazwy arkuszy i ostatni skonsolidowany plik, na którym musimy pracować.
To jest nasz szablon w arkuszu „skonsolidowanego pliku” i teraz zaczniemy konsolidować dane z arkuszy.
Teraz klikniemy komórkę B3.
Gdzie chcemy, aby funkcja „Konsolidacja” wstawiała dane z innych arkuszy. Jak widać powyżej, komórka B3 jest zaznaczona, a teraz przejdziemy do karty Dane na wstążce Excela i przejdziemy do Konsolidacji.
Po kliknięciu opcji Konsoliduj pojawi się poniższe okno:
Pierwszą rzeczą, na którą patrzymy, jest menu „Funkcja”, które pokazuje wiele funkcji arytmetycznych, takich jak suma, liczba, maksimum, średnia itp. Ponieważ chcemy sumy sprzedaży, wybieramy z menu „Suma”. Teraz przejdziemy do zakładki odniesienia, aby dodać odniesienie do naszych danych z różnych arkuszy. Następnie przejdziemy do naszego pierwszego arkusza, który zawiera dane dotyczące sprzedaży dla północy. Musimy tylko wybrać dane dotyczące sprzedaży, a nie nagłówek i wiersze. Pokazano to poniżej.
Odniesienie jest pokazane w polu „Odniesienie” w ten sposób.
Kliknij „Dodaj”, a odniesienie zostanie dodane w polu „Wszystkie odniesienia”.
W ten sam sposób dodamy teraz odniesienie ze wszystkich innych arkuszy, takich jak Północ, Południe, Wschód i Zachód. Po zakończeniu dodawania referencji kliknij „OK”.
Teraz skonsolidowaliśmy dane dotyczące sprzedaży dla kadry kierowniczej w ujęciu miesięcznym, na poziomie krajowym.
Przykład # 2 - Konsolidacja rocznej sprzedaży mądrych produktów
W ten sposób mamy kwartalną sprzedaż produktów A, B, C i D i chcemy skonsolidowanej rocznej sprzedaży pod względem produktu.
Tutaj mamy dane w czterech arkuszach, pierwszy arkusz z danymi sprzedaży za pierwszy kwartał, następnie dane za drugi kwartał, a następnie dane za trzeci i czwarty kwartał. Przejdziemy teraz do zakładki odniesienia, aby dodać odniesienia do naszych danych z różnych arkuszy. Przejdziemy do naszego pierwszego arkusza zawierającego dane dotyczące sprzedaży za pierwszy kwartał. Wybierzemy dane, jak pokazano poniżej.
Teraz przejdziemy do zakładki Dane i klikniemy opcję Konsoliduj, a pojawi się poniższe okno.
Klikamy „Dodaj”, a odniesienie zostanie dodane w polu „Wszystkie odniesienia”.
Klikamy „Dodaj”, a odniesienie zostanie dodane w polu „Wszystkie odniesienia”.
Klikamy „Dodaj”, a odniesienie zostanie dodane w polu „Wszystkie odniesienia”.
Poniżej znajduje się nasz szablon skonsolidowanego arkusza danych. Teraz wybierzemy komórkę B2, aby uzyskać dane o całkowitej sprzedaży z innych arkuszy.
Z rozwijanej listy wybieramy funkcję „Sum”. Podobnie dodamy referencje z arkuszy Q2, Q3 i Q4. Będzie to wyglądać tak. Wszystkie odniesienia ze wszystkich arkuszy są teraz dodawane. Kliknij OK"
Mamy teraz nasze skonsolidowane roczne dane sprzedaży z sumami dla każdego produktu.
W przypadku, gdy chcemy wstawić skonsolidowaną tabelę do pustego arkusza roboczego zamiast tworzenia tabeli szablonów. My też możemy to zrobić. Biorąc nasze poprzednie przykładowe dane, przeprowadzimy poniższą konsolidację.
Nie mamy szablonu tabeli konsolidacji i chcemy uzyskać skonsolidowane dane w pustym arkuszu z etykietami wierszy i kolumn. Dodamy nowy arkusz roboczy, w naszym przypadku jest to „plik skonsolidowany”.
Teraz, podobnie jak wcześniej, przejdziemy do zakładki Dane Teraz wybierzemy odniesienie z naszych kart danych. Zaczniemy od arkusza „Północ”, a następnie przejdziemy do arkuszy „Południe”, „Wschód” i „Zachód”. Wybierzemy dane, jak pokazano poniżej, które obejmują etykiety wierszy i kolumn. Następnie dodamy referencję w polu „Wszystkie referencje”: Teraz dodaj wszystkie odniesienia w ten sam sposób ze wszystkich arkuszy danych. Teraz sprawdzimy zarówno „Górny wiersz”, jak i „Lewą kolumnę”, a następnie naciśnij OK. Teraz widzimy skonsolidowane dane sprzedaży z etykietami wierszy i kolumn. Jeśli chcesz zaktualizować skonsolidowane dane, gdy pojedynczy arkusz zostanie zaktualizowany, kliknij pole „Utwórz łącza, aby utworzyć dane”. Jeśli chcesz ręcznie zaktualizować dane, nie zaznaczaj tego pola i kliknij OK. Komórki w skonsolidowanej tabeli zawierają teraz wzór sumy, jak pokazano poniżej. To sprawia, że ma charakter dynamiczny. To jest tabela dynamicznej konsolidacji. Jeśli mamy dane w różnych skoroszytach i chcemy skonsolidować je w jednym skoroszycie. Możemy to również zrobić. To daje nam dużą elastyczność i łatwość. Mamy kwartalne dane dotyczące sprzedaży różnych produktów, takie jak A, B, C i D w różnych skoroszytach, jak pokazano poniżej. Po otwarciu wszystkich skoroszytów przejdziemy do nowego skoroszytu i klikniemy komórkę B3. Dodaj referencje ze wszystkich poniższych arkuszy. Skonsolidowaliśmy dane ze wszystkich skoroszytów w nowy skoroszyt. Ponadto wszelkie zmiany danych w dowolnym skoroszycie zostaną również zaktualizowane w nowym skonsolidowanym skoroszycie danych. Nauczyliśmy się więc, jak korzystać z funkcji konsolidacji w programie Excel za pomocą przykładów. Jest to bardzo przydatna funkcja do łączenia lub gromadzenia danych w jednym arkuszu z różnych arkuszy lub skoroszytów. Jest to przewodnik po konsolidacji w programie Excel. Tutaj omawiamy sposób konsolidacji danych w wielu arkuszach roboczych wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne powiązane artykuły -
Przykład 3 - Konsolidacja danych z różnych skoroszytów
O czym należy pamiętać o konsolidacji w programie Excel
Polecane artykuły