W ramach mojej pełnoetatowej pracy kilka lat temu jedną z rzeczy, z którymi musiałem się zmierzyć, było łączenie danych z różnych skoroszytów udostępnianych przez inne osoby.
A jednym z typowych zadań było łączenie danych w taki sposób, aby nie było zduplikowanych rekordów.
Na przykład poniżej znajduje się zestaw danych, który zawiera wiele rekordów dla tego samego regionu.
A ostatecznym wynikiem musi być skonsolidowany zbiór danych, w którym każdy kraj jest zgłaszany tylko raz.
W tym samouczku pokażę, jak łączyć zduplikowane wiersze i sumować wartości, aby utworzyć jeden skonsolidowany zestaw danych.
Połą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 użyć funkcji konsolidacji w programie Excel.
Inną metodą jest użycie tabeli przestawnej i podsumowanie danych (omówione w dalszej części tego samouczka).
Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym nazwa kraju powtarza się wiele razy.
Chociaż są to unikalne rekordy, ponieważ wartość sprzedaży jest różna, do celów raportowania możesz usunąć wiele wystąpień tego samego kraju i pokazać wartość sprzedaży jako jedną sumę skonsolidowaną.
Poniżej znajdują się kroki, aby to zrobić:
- Skopiuj nagłówki oryginalnych danych i wklej je tam, gdzie chcesz skonsolidowane dane
- Wybierz komórkę pod lewym nagłówkiem
- Kliknij kartę Dane
- W grupie Narzędzia danych kliknij ikonę Konsoliduj
- W oknie dialogowym Konsoliduj wybierz Suma z listy rozwijanej funkcji (jeśli nie jest jeszcze wybrana domyślnie)
- Kliknij ikonę wyboru zakresu w polu Referencja.
- Wybierz zakres A2:B9 (dane bez nagłówków)
- Zaznacz pole wyboru Lewa kolumna
- Kliknij OK
Powyższe kroki skonsolidowałyby dane poprzez usunięcie zduplikowanych wpisów i dodanie wartości dla każdego kraju.
W rezultacie otrzymujesz unikalną listę krajów wraz z wartością sprzedaży z oryginalnego zestawu danych.
Zdecydowałem się uzyskać SUMA wartości z każdego rekordu. Możesz także wybrać inne opcje, takie jak Licznik lub Średnia lub Maks./Min.
W tym przykładzie pokazałem, jak skonsolidować dane w pojedynczym zestawie danych w arkuszu. możesz również użyć tej funkcji, aby skonsolidować dane z wielu arkuszy roboczych w tym samym skoroszycie, a nawet z wielu różnych skoroszytów.
Łączenie i sumowanie danych za pomocą tabel przestawnych
Tabela przestawna to szwajcarski scyzoryk armii danych o krojeniu i kostce w Excelu.
Może z łatwością przedstawić podsumowanie, które jest połączonym zestawem danych bez duplikatów i wartościami, które są sumą wszystkich podobnych rekordów, i zrobić o wiele więcej.
Wadą 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, w którym nazwa kraju powtarza się wiele razy i chcesz skonsolidować te dane.
Poniżej znajdują się kroki, aby utworzyć tabelę przestawną:
- Wybierz dowolną komórkę w zbiorze danych
- Kliknij kartę Wstaw
- W grupie Tabele kliknij opcję Tabela przestawna
- W oknie dialogowym Utwórz tabelę przestawną upewnij się, że tabela/zakres jest poprawny
- Kliknij istniejący arkusz roboczy
- Wybierz lokalizację, w której chcesz wstawić wynikową tabelę przestawną.
- Kliknij OK
Powyższe kroki spowoduje wstawienie tabeli przestawnej do wybranej komórki 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, a otworzy się okienko tabeli przestawnej po prawej stronie
- Przeciągnij pole Umieść kraj w obszarze Wiersz
- Przeciągnij i umieść pole Sprzedaż w obszarze Wartości
Powyższe kroki podsumowują dane i dają sumę sprzedaży dla wszystkich krajów.
Jeśli to wszystko, czego potrzebujesz i nie potrzebujesz tabeli przestawnej, możesz skopiować dane i wkleić je jako wartości w innym miejscu, a następnie usunąć tabelę przestawną.
Pomoże to również zmniejszyć rozmiar skoroszytu programu Excel.
Są to więc dwie szybkie i łatwe metody, których można użyć do skonsolidowania danych, w których łączą się zduplikowane wiersze i sumują wszystkie wartości w tych rekordach.
Mam nadzieję, że ten samouczek okazał się przydatny!
