w ramach mojej pełnoetatowej pracy kilka lat temu jedną z rzeczy, z którymi miałem do czynienia, było łączenie danych z różnych skoroszytów udostępnionych przez inne osoby.
i jednym z powszechnych zadań było łączenie danych w taki sposób, aby nie było duplikatów rekordów.
na przykład poniżej znajduje się zbiór danych zawierający wiele rekordów dla tego samego regionu.,
a końcowy wynik musi być skonsolidowanym zbiorem danych, w którym każdy kraj jest zgłaszany tylko raz.
w tym samouczku pokażę Ci, jak łączyć zduplikowane wiersze i sumować wartości, aby utworzyć pojedynczy skonsolidowany zbiór danych.
Ten samouczek obejmuje:
Łącz i Sumuj dane za pomocą opcji Konsoliduj
Jeśli wszystko, co musisz zrobić, to skonsolidować Dane i dodać wszystkie wartości dla powtarzających się rekordów, najlepiej jest użyć funkcji konsoliduj w programie Excel.,
inną metodą jest użycie tabeli przestawnej i podsumowanie danych (omówione w następnym tutorialu).
Załóżmy, że masz zestaw danych, jak pokazano poniżej, gdzie nazwa kraju powtarza się wiele razy.
chociaż są to unikalne rekordy, ponieważ wartość sprzedaży jest inna, do celów raportowania możesz usunąć wiele wystąpień z tego samego kraju i pokazać wartość sprzedaży jako jedną skonsolidowaną sumę.,
poniżej znajdują się kroki, aby to zrobić:
- skopiuj nagłówki oryginalnych danych i wklej je tam, gdzie chcesz skonsolidowane dane
- wybierz komórkę poniżej nagłówka po lewej stronie
- kliknij kartę Dane
- w grupie Narzędzia danych kliknij ikonę konsolidacja
- w oknie dialogowym konsolidacja wybierz sum z listy rozwijanej funkcja (jeśli nie jest jeszcze wybrana domyślnie)
- kliknij ikonę wyboru zakresu w polu odniesienia.,
- Wybierz zakres A2: B9 (dane z wyłączeniem nagłówków)
- Wybierz pole wyboru lewej kolumny
- kliknij Ok
powyższe kroki skonsolidują dane, usuwając zduplikowane wpisy i dodając wartości dla każdego kraju.
w wyniku końcowym otrzymasz unikalną listę krajów wraz z wartością sprzedaży z oryginalnego zestawu danych.
zdecydowałem się pobrać sumę wartości z każdego rekordu. Możesz również wybrać inne opcje, takie jak liczba, średnia lub Max/Min.,
w tym przykładzie pokazałem, jak skonsolidować dane w jednym zestawie danych w arkuszu roboczym. możesz również użyć tej funkcji do konsolidacji danych z wielu arkuszy roboczych w tym samym skoroszycie, a nawet wielu różnych skoroszytów.
łączenie i sumowanie danych za pomocą tabel przestawnych
Tabela przestawna to Szwajcarski nóż wojskowy do krojenia i krojenia danych w Excelu.
może łatwo dać podsumowanie, które jest połączonym zestawem danych bez duplikatów i wartości, które są sumą wszystkich podobnych rekordów, i zrobić o wiele więcej.,
minusem tej metody w porównaniu do poprzedniej jest to, że ta zajmuje więcej kliknięć i kilka sekund więcej w porównaniu do poprzedniej.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, gdzie nazwa kraju powtarza się wiele razy i chcesz skonsolidować te dane.,
poniżej znajdują się kroki tworzenia tabeli przestawnej:
- Wybierz dowolną komórkę w zbiorze danych
- kliknij kartę Wstaw
- w grupie tabele kliknij opcję tabeli przestawnej
- w oknie dialogowym Utwórz tabelę przestawną upewnij się, że Tabela/Zakres jest prawidłowy
- kliknij istniejący arkusz roboczy
- Wybierz lokalizację, w której chcesz wstawić wynikową tabelę przestawną.,
- kliknij OK
powyższe kroki wstawią tabelę przestawną w wybranej komórce docelowej.
teraz możemy robić różne rzeczy za pomocą tabeli przestawnej – w tym konsolidować nasz zestaw danych i usuwać duplikaty.,
poniżej znajdują się kroki, aby to zrobić:
- kliknij w dowolnym miejscu w obszarze tabeli przestawnej, aby otworzyć panel tabeli przestawnej po prawej stronie
- przeciągnij pole umieść kraj w obszarze wiersza
- przeciągnij i umieść pole sprzedaży w obszarze wartości
powyższe kroki podsumują danych i daje sumę sprzedaży dla wszystkich krajów.
Jeśli jest to wszystko, co chcesz i nie potrzebujesz tabeli przestawnej, możesz skopiować dane i wkleić je jako wartości gdzie indziej i usunąć tabelę przestawną.,
pomoże to również zmniejszyć rozmiar skoroszytu programu Excel.
są to dwie szybkie i łatwe metody, których można użyć do konsolidacji danych, gdzie połączyłyby zduplikowane wiersze i zsumowałyby wszystkie wartości w tych rekordach.
mam nadzieję, że ten tutorial ci się przydał!,
Możesz również polubić następujące samouczki programu Excel:
- Połącz dane z wielu arkuszy roboczych w jeden arkusz roboczy w Excelu
- Połącz dane z wielu skoroszytów w Excelu
- Jak połączyć komórki w Excelu
- Jak połączyć wiele plików Excela w jeden skoroszyt Excela
- Jak połączyć zduplikowane wiersze i zsumować wartości w Excelu