Jeśli pracujesz z informacjami rozłożonymi na kilka arkuszy — na przykład budżety z różnych regionów lub raporty utworzone przez wielu współautorów — możesz zechcieć połączyć te dane w jednym miejscu. W programie Excel można to zrobić na kilka sposobów w zależności od tego, czy chcesz podsumować wartości , czy po prostu połączyć listy.
Przed rozpoczęciem
Upewnij się, że dane źródłowe mają prawidłową strukturę.
-
Użyj formatu listy (bez całkowicie pustych wierszy ani kolumn).
-
Zachowaj spójność etykiet (nagłówków kolumn) we wszystkich arkuszach.
-
Jeśli w Twojej wersji programu Excel nie ma funkcji Data > Consolidate, być może korzystasz z Excel dla sieci web lub platformy, która nie obsługuje tej funkcji. W takim przypadku zobacz sekcję "Opcja 2: Łączenie lub dołączanie danych zamiast ich podsumowywania".
Opcja 1. Podsumowywanie danych za pomocą funkcji Konsoliduj
Funkcji Konsoliduj należy używać do obliczania sum, średnich, zliczeń lub innych wyników podsumowania w arkuszach. Konsolidację można skonsolidować według pozycji (tego samego układu) lub według kategorii (pasujące etykiety).
Konsolidacja według położenia
Użyj tego ustawienia, gdy w każdym arkuszu jest używany ten sam układ komórek.
-
Otwórz arkusze źródłowe i potwierdź, że dane są wyświetlane w tej samej lokalizacji w każdym arkuszu.
-
Przejdź do arkusza, w którym chcesz uzyskać połączone wyniki.
-
Zaznacz lewą górną komórkę zakresu, w którym mają zostać wyświetlone skonsolidowane dane.
-
Upewnij się, że jest miejsce na rozwijanie danych w dół i w prawo.
-
-
Wybierz pozycję Dane >
-
Wybierz funkcję (na przykład Suma, Średnia lub Liczba).
-
W każdym arkuszu źródłowym:
-
Zaznacz zakres danych.
-
Wybierz pozycję Dodaj , aby uwzględnić ją we wszystkich odwołaniach.
-
-
Wybierz przycisk OK , aby wygenerować skonsolidowany raport.
Konsolidacja według kategorii
Użyj tego ustawienia, gdy arkusze mają te same etykiety, nawet jeśli dane nie są umieszczone identycznie. Zwróć uwagę, że jeśli w jednym arkuszu jest używany tekst "Średnia", podczas gdy w innym jest używany ciąg "Średnia", musisz najpierw ujednolicić etykiety, aby program Excel mógł je poprawnie dopasować.
-
Potwierdź, że w każdym arkuszu są używane pasujące etykiety w górnym wierszu lub w lewej kolumnie.
-
W arkuszu docelowym zaznacz lewą górną komórkę zakresu, w którym mają zostać wyświetlone skonsolidowane dane.
-
Upewnij się, że jest miejsce na rozwijanie danych w dół i w prawo.
-
-
Przejdź do pozycji Dane >
-
Wybierz funkcję (na przykład Suma, Średnia lub Liczba).
-
Zaznacz pola wyboru w obszarze Użyj etykiet w (Górny wiersz, Lewa kolumna lub oba).
-
W każdym arkuszu źródłowym:
-
Zaznacz zakres danych.
-
Wybierz pozycję Dodaj , aby uwzględnić ją we wszystkich odwołaniach.
-
-
Wybierz przycisk OK , aby wygenerować skonsolidowany raport.
Jeśli etykieta jest wyświetlana w jednym arkuszu, ale nie w innym, program Excel nadal ją zawiera. W wyniku zostanie utworzony nowy wiersz lub nowa kolumna.
Opcja 2. Łączenie lub dołączanie danych zamiast ich podsumowywania
Jeśli chcesz scalić lub ułożyć wiersze z wielu arkuszy, a nie obliczać sumy, potrzebujesz innego podejścia.
Kopiowanie i wklejanie
Jest to szybka, ręczna opcja scalania danych. Sprawdza się najlepiej, gdy trzeba scalić tylko kilka arkuszy.
-
Tworzenie nowego arkusza.
-
Skopiuj całą listę z pierwszego arkusza i wklej ją.
-
Powtórz te czynności dla innych arkuszy, wklejając bezpośrednio pod istniejącymi danymi.
-
W razie potrzeby usuń zduplikowane nagłówki.
Stosowanie danych za pomocą formuły VSTACK
Jeśli arkusze mają taką samą strukturę kolumn, można je dynamicznie układać w stos przy użyciu funkcji VSTACK. W poniższym przykładzie można połączyć dane z trzech arkuszy.
=VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50)
Spowoduje to utworzenie jednej połączonej listy aktualizowanej po zmianie danych w arkuszach źródłowych.
Korzystanie z Power Query
Power Query umożliwia automatyczne importowanie i łączenie danych z wielu tabel lub arkuszy, nawet w skoroszytach. Jest to najlepsze rozwiązanie w przypadku dużych zestawów danych i ciągłego scalania.
-
Zaznacz każdy zakres danych i naciśnij Ctrl+T , aby przekonwertować go na tabelę.
-
Przejdź do >Pobieranie> danych z innych źródeł > pustego zapytania.
-
Aby wyświetlić tabele, użyj pozycji Excel.CurrentWorkbook() na pasku formuły.
-
Rozwiń je i połącz za pomocą ikony podwójnej strzałki.
-
Wybierz pozycję Zamknij & Załaduj , aby utworzyć połączony arkusz.
Ta metoda tworzy dynamiczny zestaw danych, który można odświeżać za każdym razem, gdy dane się zmienią.
Rozwiązywanie problemów i porady
Na podstawie opinii użytkowników poniżej przedstawiono najczęstsze przeszkody.
Nie można znaleźć pozycji "Konsoliduj"
Być może masz Excel dla sieci web lub wersję, która jej nie obsługuje. Zamiast tego użyj Power Query lub formuł.
Okno dialogowe Konsolidowanie nie zezwala na zaznaczanie zakresów
Upewnij się, że okno dialogowe pozostaje aktywne. Jeśli blokuje ono klikanie w innych oknach, spróbuj zmienić jego rozmiar lub przenieść go.
Wyniki konsolidacji wyglądają nieprawidłowo
Sprawdź, czy:
-
Etykiety są zgodne dokładnie (np. "Średnia", a nie "Średnia").
-
Nie ma pustej struktury list podziału wierszy/kolumn.
-
Wybrano poprawną funkcję (suma a średnia).
Dane są wyświetlane w niespójnych wierszach lub kolumnach
Jeśli arkusze nie są wyrównane, użyj konsolidowania według kategorii , a nie według położenia.
Chcesz dołączyć dane, a nie je podsumowywać
Zamiast tego użyj języka VSTACK lub Power Query. Lepiej nadają się do scalania.
