Excel – konsolidacje danych
Często w pracy z Excelem spotykamy się z sytuacją, kiedy z kilku - kilkunastu arkuszy źródłowych trzeba stworzyć jeden wspólny – zbiorczy. Metodą na wykonanie tego zadania jest funkcja konsolidacji, która z wielu zakresów pozwala stworzyć jeden. Ogólnie istnieją dwa sposoby konsolidacji, według położenia danych (obszary danych dokładnie się pokrywają) oraz według kategorii - etykiet (obszary źródłowe mają różne zakresy, ale te same etykiety wierszy i (lub) kolumn).
Przed przygotowaniem danych, zanim przystąpimy do procesu konsolidacji, dobrze jest przygotować stosowny wzorcowy dokument, najlepiej szablon programu Excel (*.xltx, *.xltm).
W szablonie ustalamy reguły edycji i wprowadzania danych przez użytkowników, korzystamy z funkcji Poprawność danych, blokowania - odblokowania odpowiednich zakresów oraz ochrony (Dane Chroń arkusz).
(Jeśli będziemy ustanawiać w konsolidacji łącza z danymi źródłowymi, należy obszary danych wypełnić zerami!) Następnie zapisujemy plik jako szablon programu Excel i udostępniamy użytkownikom.
Po otrzymaniu kilku - kilkunastu wypełnionych plików danych możemy przystąpić do procesu konsolidacji poprzez uruchomienie polecenia Dane Konsoliduj.
Na rysunku poniżej widać wypełnione okno funkcji Konsoliduj dla jednakowych obszarów położenia danych z włączoną opcją tworzenia łączy z danymi źródłowymi.
W polu Odwołanie przechodzimy do kolejnego pliku danych, zaznaczamy wybrany zakres i poleceniem Dodaj dołączamy do listy (Wszystkie odwołania). Czasami potrzebujemy do konsolidowanych danych dołączyć odpowiednio etykiety tekstowe, wybieramy jedną z opcji Użyj etykiet w Górny wiersz lub Lewa kolumna. Po ostatecznym potwierdzeniu przyciskiem OK otrzymujemy skonsolidowany plik. W powyższym przykładzie końcowy plik zawiera odpowiednio utworzony konspekt oraz łącza z danymi.
Poniżej widać podgląd odwołań do plików źródłowych
Do aktualizacji (odświeżania) danych, podglądu, edycji i zamiany źródeł, ewentualnego przerwania łączy warto użyć polecenia Dane Edytuj łącza.
Jeśli chcemy konsolidować dane jednorazowo, bez możliwości odświeżania, nie uruchamiamy polecenia Utwórz łącze z danymi źródłowymi. Wtedy końcowy plik zawiera wyłącznie dane liczbowe i tekstowe etykiety. Chcąc mieć aktualne wartości należy każdorazowo przeprowadzać cały proces konsolidacji od nowa.
Na praktyczne przećwiczenie wspomnianych funkcji, jak i wielu innych możliwości arkusza Excel tradycyjnie zapraszam na otwarte szkolenia prowadzone w BDO "Excel 2010 w praktyce finansowej. Warsztaty komputerowe."
Janusz Gach
wykładowca, trener z wieloletnim stażem. Prowadzi szkolenia dla BDO. Autor wielu programów szkoleniowych dotyczących wykorzystania programu Excel, informatyki we wspomaganiu zarządzania przepływem informacji w nowoczesnym przedsiębiorstwie. Specjalizuje się m.in. w zastosowaniach arkuszy kalkulacyjnych, a także w analizach związanych z potrzebami biznesu
Newsletter BDO
Aktualności
- Egzekucja administracyjna z wynagrodzenia za pracę
- Klika słów o urlopie na żądanie
- Cyfryzacja dokumentów księgowych
- Nowa definicja budynku i budowli
- Limit płatności gotówkowych z wątpliwościami
- Amortyzacja łodzi sezonowej
- Ochrona sygnalistów już obowiązuje
- Podatek od budowli wchodzącej w skład przedsiębiorstwa
- Pojęcie zagranicznej jednostki kontrolowanej
- Tajemnica zawodowa i raportowanie MDR