Funkcja konsolidacji programu Excel
Konsolidacja to funkcja wbudowana w programie Excel, która służy do konsolidacji danych z różnych skoroszytów, które są otwierane w tym samym czasie, funkcja ta jest obecna w zakładce danych, co robi ta funkcja, że pozwala nam wybierać wiele danych z różnych skoroszytów i skonsoliduj go w ostatecznym skoroszycie.
Jak korzystać z konsolidacji w programie Excel? (z przykładami)
Przykład 1: Konsolidacja wielu arkuszy w jeden
Zanim wyjaśnię Ci funkcję konsolidacji w programie Excel, chciałbym, abyś pobrał skoroszyt z powyższego linku, aby ćwiczyć razem ze mną i nauczyć się tej funkcji.
Załóżmy, że otrzymałeś dane z trzech arkuszy w tym samym skoroszycie, jak pokazano na poniższym obrazku.
Mamy dane sprzedaży czterech stref w trzech różnych arkuszach.
Teraz musimy zebrać te dane w jednym arkuszu; w tym celu w osobnym arkuszu utworzyłem szablon o nazwie Skonsolidowany arkusz.
- Krok 1: wybierz komórkę B2 w „Skonsoliduj arkusz”.
- Krok 2: Przejdź do wstążki Dane i kliknij Konsoliduj.
- Krok 3: Jak tylko klikniemy „Konsoliduj”, pojawi się poniżej okna Konsolidacja.
W powyższym oknie pierwszym elementem do zobaczenia jest lista rozwijana Funkcja w programie Excel. Z tej rozwijanej listy możemy wybrać dowolną funkcję do konsolidacji danych.
W zależności od rodzaju konsolidacji, jaką musimy wykonać, musimy wybrać żądaną funkcję. W tym przykładzie chcę utworzyć podsumowanie całkowitej sprzedaży trzech różnych arkuszy. Więc wybiorę funkcję SUMA w programie Excel, która jest domyślna.
Następnie musimy dodać odwołanie do komórek numeru sprzedaży każdego arkusza. Kliknij pole odniesienia i wybierz numer sprzedaży w arkuszu „Plik 1”.
Po wybraniu odniesienia kliknij DODAJ. Doda wybrane odniesienia do wszystkich odniesień.
Teraz umieszczając kursor z powrotem w odnośniku, przejdź do drugiego arkusza, tj. „Plik 2”.
Automatycznie wybierze zakres komórek, który wybraliśmy w poprzednim arkuszu. Teraz ponownie kliknij Dodaj, aby dodać odniesienie.
Teraz w końcu powtórz ten sam krok dla arkusza roboczego Plik 3.
Ok, skończyliśmy z procesem referencyjnym. Kliknij OK, aby uzyskać skonsolidowane podsumowanie w „Skonsoliduj arkusz”.
Wow to słowo, które należy opisać, prawda?
Jak fajnie jest, jeśli otrzymamy podział wszystkich liczb wraz z sumą. Tak, wiem, czego teraz potrzebujesz. Stwórzmy to również.
Przykład 2: Konsolidacja dynamiczna z linkami do danych źródłowych
Jak fajnie jest, jeśli możemy utworzyć link do źródła, aby za każdym razem, gdy wprowadzimy jakieś zmiany w arkuszu źródłowym, powinno to natychmiast odzwierciedlić się w arkuszu podsumowania.
Poniżej znajduje się przykładowy zrzut ekranu tego samego.
Jak widać, niektóre wiersze są zgrupowane razem, które można rozwinąć, klikając przycisk PLUS. Tak więc strefa „E” ma trzy różne numery arkuszy, aw komórce B5 mamy podsumowanie wszystkich trzech numerów arkuszy.
Aby utworzyć łącze podczas podawania odniesienia, musimy zaznaczyć pole „Utwórz łącze do danych źródłowych”.
Spowoduje to utworzenie łącza do komórek danych źródłowych. Teraz widzimy to w odwołaniach do komórek.
Przykład 3 - Konsolidacja danych w pustym arkuszu
W powyższym przykładzie utworzyliśmy szablon w arkuszu „Konsolidacja arkusza”, a następnie skonsolidowaliśmy dane. Możemy również konsolidować bez tworzenia szablonu.
W tym celu najpierw dodaj pusty arkusz, a następnie postępuj zgodnie z tą samą procedurą, co powyżej, ale podczas podawania referencji musimy wybrać cały zakres danych.
Wybierz pełny zakres danych zamiast tylko kolumny liczb.
Powtórz tę samą czynność dla wszystkich trzech arkuszy.
Po podaniu odniesienia musimy kliknąć pola wyboru „Górny wiersz”, „Lewa kolumna”, aby utworzyć etykiety.
Gdy to otrzymasz, kliknij OK, aby przejść do pustego arkusza; utworzy skonsolidowane podsumowanie, takie jak poniższe.
W ten sposób możemy użyć funkcji Consolidate programu Excel do konsolidacji danych z różnych arkuszy.
