Obecnie starożytna technika zwana konsolidacją danych. Jeśli musisz połączyć dane z wielu arkuszy lub wielu skoroszytów, konsolidacja może obsłużyć zadanie, jeśli dane są w określonym formacie. Dowiedz się więcej o tym starożytnym narzędziu.
W programie Excel istnieją dwa starożytne narzędzia do konsolidacji.
Aby je zrozumieć, powiedz, że masz trzy zestawy danych. Każdy ma nazwy po lewej stronie i miesiące u góry. Zwróć uwagę, że nazwy są różne i w każdym zestawie danych występuje inna liczba miesięcy.
Chcesz połączyć je w jeden zestaw danych. Przejdź na stronę, aby zapoznać się z omówieniem tych dwóch metod.
Pierwszym narzędziem jest polecenie Konsoliduj na karcie Dane. Wybierz pustą sekcję skoroszytu przed uruchomieniem polecenia. Użyj przycisku RefEdit, aby wskazać każdy zestaw danych, a następnie kliknij przycisk Dodaj. W lewym dolnym rogu wybierz Górny wiersz i Lewa kolumna.
Po kliknięciu przycisku OK zostanie utworzony nadzbiór wszystkich trzech zestawów danych. Pierwsza kolumna zawiera dowolną nazwę w dowolnym z trzech zestawów danych. Wiersz 1 zawiera dowolny miesiąc w dowolnym zbiorze danych.
Na powyższym rysunku zwróć uwagę na trzy niedogodności. Komórka A1 jest zawsze pusta. Dane w A nie są sortowane. Jeśli w zbiorze danych brakowało osoby, komórki są pozostawione puste, zamiast wypełniać je 0.
Wypełnienie komórki A1 jest dość łatwe. Sortowanie według nazwy polega na użyciu funkcji Flash Fill, aby uzyskać nazwisko w kolumnie N. Oto jak wypełnić puste komórki wartością 0:
- Wybierz wszystkie komórki, które powinny mieć numery: B2: M11.
- Wybierz Home, Find & Select, Go To Special.
- Wybierz Puste, a następnie kliknij OK. Zostaną wybrane wszystkie puste komórki.
-
Wpisz 0, a następnie Ctrl + Enter.
Idź do specjalnego
Rezultat: ładnie sformatowany raport podsumowujący.
Obejrzyj wideo
- Konsolidacja to starożytna funkcja programu Excel
- Określ wiele zakresów do konsolidacji
- Użyj etykiet w górnym wierszu i lewej kolumnie
- Irytacje: A1 jest zawsze puste, kolumna A nie jest posortowana, puste dane
- Idź do Special, Blanks, 0, Ctrl + Enter
- Konsolidacja może wskazywać na zewnętrzne skoroszyty
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2046 - Skonsoliduj arkusze!
Będę podcastował wszystkie moje wskazówki z tej książki. Kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania!
W porządku, mam niesamowitą, niesamowitą starą sztuczkę. Mam tutaj trzy zbiory danych, Q1, Q2, Q3, wszystkie mają podobny kształt i mają nazwy po lewej stronie, miesiące biegną u góry, ale nie dokładnie w tym samym kształcie. Pierwszy kwartał to styczeń-luty-marzec, drugi kwartał ma 5 miesięcy, chyba się rozleniwiamy i nie wymyśliliśmy, jak to zrobić pod koniec czerwca, a ktoś w końcu zrobił to pod koniec sierpnia, a czwarty kwartał ma 4 miesiące. No dobra, pierwsza to A: D, druga A: F, trzecia A: E. Różne imiona, niektóre z nich są takie same, jak Michael Seeley jest w każdym z nich, ale inne nazwiska pojawiają się i znikają. W porządku, to niesamowita funkcja, istnieje od zawsze. Pamiętam, że robiłem to nawet w 1995 roku, Data, Consolidates!
W porządku, użyjemy funkcji SUMA, nigdy nie używałem żadnej z pozostałych, ale myślę, że one tam są. SUMA, pierwszą rzeczą, którą zrobimy, jest powrót do Q1 i wskazanie na ten zakres, te cztery kolumny, kliknij Dodaj, a następnie przejdź do Q2, wybierz te kolumny, kliknij Dodaj, a następnie Q4, wybierz te kolumny . W porządku, zaznacz to pole wyboru Użyj etykiet w górnym wierszu i lewej kolumnie, ten przycisk Przeglądaj oznacza, że te zestawy danych mogą znajdować się w różnych skoroszytach! Utwórz linki do danych źródłowych, o tym porozmawiamy na końcu. Kiedy kliknę OK, otrzymają wszystkie nazwiska, które znajdują się na którejkolwiek z 3 list, miesiące na którejkolwiek z 3 list, a teraz mamy tę niesamowitą nadzbiór, w porządku, irytujące!
To świetna funkcja, ale oto rzeczy, które właśnie mnie zhakowały. Nie podają mi etykiety w A1, nie przejmują się sortowaniem danych, które spadają, a jeśli ktoś nie miał rekordu w pierwszym, zamiast zer dają mi spacje. W porządku, aby wypełnić puste miejsca zerami, Home, Find and Select, Go To Special, wybierz Blanks, kliknij OK, wpisz zero, Ctrl + Enter je wypełni. Wystarczająco łatwe do sortowania danych, Data, AZ i to posortuje dane, w porządku. Twórz linki, Jezu, to nigdy nie działa dobrze, w porządku, twórz linki, aby funkcja tworzenia łączy działała, musi znajdować się w zewnętrznym skoroszycie. W porządku, więc kliknę tutaj Przeglądaj, utworzyłem skoroszyt o nazwie OtherWorkbook, a dane znajdują się w A1: D7, kliknij Dodaj, w porządku, i jest pierwszy. Następne dane znajdują się w G1: L8, więc przejdę do OtherWorkbook, G1: L8,kliknij Dodaj, dobrze. Więc teraz mam dwa odniesienia do innych skoroszytów, górny wiersz, lewa kolumna, tworzenie łączy do danych źródłowych. Pomoc programu Excel mówi, że gdy użyjesz Utwórz łącza do danych źródłowych, nigdy nie będziesz w stanie edytować tych zakresów, ponownie kliknij OK i oto, co otrzymujemy.
W porządku, po pierwsze, wygląda na to, że dało nam wyniki, dało nam wyniki, ale jest tutaj dodatkowa kolumna B, a mamy grupę i konspekt. A kiedy przejdziemy do widoku numer 2, ach. Idealnie byłoby, gdybyś miał jeden skoroszyt o nazwie Styczeń i inny skoroszyt o nazwie Luty, i pokaże ci, że jest styczeń, tutaj jest luty. Oto suma dla Mike'a Seeleya, to są formuły wskazujące na te sprzedaże, a tutaj jest suma tych dwóch, w porządku, to dziwne.
Jeśli faktycznie używasz tego przez cały czas, chcę usłyszeć od ciebie w komentarzach na YouTube, jestem pewien, że jest łatwiejszy sposób, aby to zrobić dla mnie, nigdy w życiu tego nie robiłem, dobrze kiedyś, dziś, a potem dzisiaj, tylko w ten sposób mógłbym to wyjaśnić, w porządku. Konsoliduj jednak, gdy konsolidujemy arkusze z bieżącego skoroszytu, niesamowita, niesamowita sztuczka. Jutro porównamy zakres konsolidacji wielokrotnych, tabele przestawne, ale wszystkie te sztuczki są w książce, kliknij „i” w prawym górnym rogu, aby przejść do tej książki.
Konsoliduj, starożytną, starożytną funkcję w programie Excel, określasz wiele zakresów do skonsolidowania, zawsze zaznaczam pole dla górnego wiersza i lewej kolumny. Wyniki są świetne, ale A1 jest puste, kolumna A nie jest posortowana, aw danych są puste miejsca, użyj opcji Przejdź do specjalnych spacji, wpisz 0, Ctrl + Enter, aby wypełnić te puste miejsca. A potem ten ostatni przykład, konsolidacja, może wskazywać na zewnętrzne skoroszyty, może być przydatny!
W porządku hej, chcę podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2046.xlsm
