Jak łączyć dane
z wielu plików
Po dwóch tygodniach przerwy, wracamy z kolejną propozycją wykorzystania Excela w zarządzaniu produkcją. Tym razem pójdziemy krok dalej i rozpoczniemy pierwszy z trzech artykułów poświęconych możliwościom języka VBA w automatyzacji i ułatwianiu pracy z Excelem.
Gdzie funkcja nie może tam makro pośle… Parafrazując to przysłowie, mam na myśli, że pisanie procedur w języku VBA otwiera zupełnie nowe możliwości. Za pomocą makr możesz przygotowywać i wysyłać maile, importować zdjęcia do arkusza, czy też wypełniać arkusze danymi z różnych źródeł. Brzmi dobrze?
Mam nadzieję, że jesteś już wystarczająco zainteresowany. Muszę jednak nieco ostudzić twój zapał, ponieważ jak to mówią „first things first”. W dzisiejszym wpisie pokaże Ci prosty przykład jak połączyć dane z wielu plików do jednej zbiorczej bazy.
Dla uproszczenia i uprzyjemnienia tej, być może dla Ciebie zupełnie nowej, porcji excelowej wiedzy, wprowadzę kilka pojęć.
Makro, czy też procedura VBA, to nic innego jak zestaw operacji zapisany w postaci kodu. Kod ten przechowywany jest w Modułach wewnątrz edytora VBA. Edytor dostępny jest z poziomu zakładki Deweloper lub po wciśnięciu skrótu klawiszowego Alt + F11.
Jeżeli nie widzisz wśród swoich kart zakładki DEWELOPER, dodaj ją w następujący sposób:
Kliknij prawym przyciskiem myszy na wstążkę i wybierz opcję Dostosuj Wstążkę…
- Zaznacz na liście po lewej stronie pozycję Deweloper.
- Kliknij przycisk Dodaj>> na środku okna.
- Zaznacz okienko wyboru przy pozycji Deweloper. W tym miejscu możesz również zmieniać pozycję kart na wstążce. Wystarczy, że przeciągniesz wybraną kartę w inne miejsce na liście.
- Potwierdź wybór przyciskiem OK.
Jeżeli wykonałeś powyższe kroki, to wśród dotychczas widocznych kart pojawi się zakładka Deweloper. W moim przypadku karta umiejscowiona jest zaraz po karcie Narzędzia Główne. Dodając ją do wstążki, przesunąłem ją na początek ponieważ korzystam z niej dosyć często. Jeżeli nie przesuwałeś umiejscowienia zakładki Deweloper u siebie, najpewniej pojawi się ona za kartą Widok.
Szkolenie Online
Excel na produkcji -MASTER
TOP funkcji używanych na produkcji
Narzędzie Excel wykorzystywane na produkcji
Wskaźniki KPI i wizualizacja
Edytor VBA
Okno edytora składa się z trzech zasadniczych obszarów widocznych na poniżej:
Okno listy projektów to miejsce, w którym znajduje się spis wszystkich otwartych w tej chwili plików, wraz z należącymi do nich obiektami, formularzami i modułami. Wiesz już, że w modułach będziemy przechowywać kod naszych procedur. Formularzami zajmiemy się w przyszłym tygodniu, więc na razie nie zaprzątaj sobie nimi głowy. Obiekty programu Excel to nic innego jak skoroszyty, arkusze czy też komórki. Są to te elementy, które mają pewien zestaw właściwości, a my za pomocą kodu możemy je przekształcać. Przykładowo, komórka ma takie właściwości jak wartość, wysokość, szerokość, czcionkę czy też kolor tła.
Okno właściwości obiektu to miejsce, w którym możesz zobaczyć i edytować niektóre właściwości obiektów Excela takie jak, arkusze i formularze.
Okno kodu VBA to twój stół roboczy, na którym będziesz „rzeźbił makra”.
Myślę, że na tym zakończymy podstawową teorię obsługi edytora VBA. Przejdę teraz do przykładu, jaki dla Ciebie dzisiaj przygotowałem.
Rejestr czasu pracy
Omawiane narzędzie gromadzi dane na temat zadań, jakie w danym okresie realizowali pracownicy wraz z czasem ich wykonania. Takie narzędzie pozwoli uzyskać precyzyjne wartości pracochłonności oraz wyrównać obciążenie pracą.
Problem, z jakim dzisiaj się zmierzymy, to sytuacja, w której potrzebujesz zestawić dane z różnych obszarów, a znajdują się one w kilku plikach. Do rozwiązania tego problemu możesz oczywiście podejść na różne sposoby. Na piechotę – ręcznie kopiując dane z każdego pliku, a następnie tworzyć raport. Możesz również tworzyć arkusze z dużą ilością łączy, które następnie skonsolidujesz w jedną tabelę przestawną. Ani jedna, ani druga opcja nie należą do najszybszych.
Makro załatwi sprawę szybciej i dużo mniej obciąży arkusz. Dodatkowo wraz ze wzrostem twoich umiejętności, będziesz mógł dodawać do niego kolejne funkcjonalności.
Założenia:
Każdy z pracowników ma swój indywidualny arkusz rejestracji zadań.
Administrator głównej bazy importuje zadania z arkuszy pracowniczych na koniec zmiany.
Pracownicy każdego dnia czyszczą arkusz z wykonanych zadań przed rozpoczęciem pracy.
Wszystkie arkusze pracownicze mają taką samą strukturę, a dane wprowadzane są tam w standardowy sposób.
Jeśli Excel to nie rozwiązanie dla Twojej firmy, sprawdź aplikację: Karty pracy – raportowanie na produkcji
Kod VBA
Pierwszą i najważniejszą rzeczą przy tworzeniu procedur VBA jest deklaracja zmiennych.
Zmienne to elementy makr, które przechowują wartości liczbowe, tekstowe lub obiekty programu Excel. Poprawne zdefiniowanie zmiennych jest szalenie istotne z punktu widzenia optymalizacji Twoich programów. Można powiedzieć, że deklaracja zmiennych jest standardem pracy dla edytora VBA. Tak jak precyzyjne instrukcje niezbędnych narzędzi i kolejności wykonania czynności podnoszą jakość pracy pracowników, tak właściwie zdefiniowane zmienne podnoszą jakość Twojego kodu. Deklaracja zmiennych oznacza precyzyjne wskazanie – ile zasobów musi zużyć edytor VBA. Jeżeli nie przypiszemy typów do zmiennych lub zrobimy to niewłaściwie, nasze makro obciąży Excela dużo bardziej, a co za tym idzie, wydłuży czas wykonywania procedury.
W poniższej tabeli znajdują się podstawowe typy danych stosowane w pisaniu makr:
| Typ danych | Wartość | Pochłaniania pamięć [bajt] |
|---|---|---|
| Byte | Od 0 do 255 | 1 |
| Boolean | TRUE / FALSE Prawda / Fałsz | 2 |
| Integer | Liczby całkowite z zakresu od: – 32 768 do 32 767 | 2 |
Long | Liczby całkowite z zakresu od: – 2 147 483 648 do 2 147 483 648 | 4 (zajmuje więcej pamięci niż Integer, ale jest czytany szybciej) |
| Single | Liczby zmiennoprzecinkowe – pojedyncze od -3.402823 E38 do – 1.401298E-45 oraz od 1.401298E-45 do 3.402823 E38 | 4 |
| Double | Liczby zmiennoprzecinkowe – podwójne od -1.79769313486231E308 do – 4.94065645841247E-324 oraz od 4.94065645841247E-324 do 1.79769313486231E308 | 4 |
| Object | Dowolny obiekt | 8 |
| String | Tekst | 1 bajt na każdy znak |
| Variant | Domyślny typ (w przypadku braku deklaracji). Wymaga więcej zasobów i spowalnia działanie programu, ale za to może przyjmować dowolny typ danych | 16 |
Przyjrzyj się teraz fragmentowi kodu z deklaracją zmiennych w mechanizmie dzisiejszego pliku. Kod dostępny jest w Module IMPORT. Dwukrotne kliknięcie w oznaczony na zdjęciu moduł spowoduje wyświetlenie kodu.
Deklaracja zmiennych
Dim plik As Workbook, arkusz As Worksheet, wybrany_plik As FileDialog, element As Variant
Dim baza As Worksheet, i As Long, pwolny As Long, ostatni As Long
Plik – to zmienna, w której będą przechowywane wybrane pliki do importu. Jest to zmienna obiektowa typu Workbook, czyli po prostu skoroszyt programu Excel.
arkusz – to zmienna, w której będą przechowywane arkusze z rejestrami czynności w wybranych plikach. Jest ro również zmienna obiektowa, lecz typu Worksheet, czyli arkusza programu Excel.
wybrany_plik – to zmienna przechowująca okienko wyboru, które pojawi się po uruchomieniu makra. Z poziomu tego okna będziesz mógł wybrać pliki do importu danych. Jest to zmienna typu FileDialog, czyli kolejny szczególny wariant zmiennej obiektowej.
element – to zmienna typu Variant. Nasze okienko będzie zwracać nazwy wybranych plików. Nie możemy tu jednak użyć typu tekstowego. W następnym kroku wykorzystamy tzw. Pętlę For Each, która wymaga zmiennych typu obiektowego lub właśnie – Variant.
baza – jest to zmienna przechowująca arkusz z rejestrem zadań w głównej bazie. Typ danych jest tu analogiczny jak w zmiennej arkusz.
i – to zmienna typu Long, czyli duże liczby całkowite. W tej zmiennej będziemy przechowywać ilość wpisów w plikach z danymi do importu. Posłuży ona nam jak licznik do pętli For.
pwolny – numer pierwszego niewypełnionego wiersza. Posłuży nam do wskazania miejsca, od którego można będzie rozpocząć uzupełnianie danych w bazie. Typ Long.
ostatni – numer ostatniego wypełnionego wiersza w arkuszach z danymi do importu. Posłuży nam za górną granicę pętli importującej dane. Typ Long.
Wyłączenie odświeżania ekranu – znacznie przyspieszy to wykonanie procedury importu.
Application.ScreenUpdating = False
Przypisanie do zmiennej arkusza z bazą zadań – przed zmiennymi obiektowymi zawsze musi się pojawić słowo klucz „set”.
Set baza = ThisWorkbook.Worksheets(„Rejestr”)
Poniższą instrukcję możemy przetłumaczyć dosłownie jako – przypisz do zmiennej baza = w tym skoroszycie – arkusz „Rejestr”
Przypisanie do zmiennej okna dialogowego z wyborem plików do importu danych
Set wybrany_plik = Application.FileDialog(msoFileDialogFilePicker)
Inicjacja operacji na zmiennej „wybrany_plik”, czyli naszym okienku dialogowym
With wybrany_plik
.InitialFileName = ThisWorkbook.Path ’ przypisanie lokalizacji startowej – w tym przypadku wybór pliku rozpocznie się w tym samym folderze, w którym znajduje się plik z bazą zadań.
.InitialView = msoFileDialogViewDetails ’ wybór sposobu wyświetlania plików w folderze
.AllowMultiSelect = True ’ włączenie możliwości wyboru wielu plików
If .Show <> -1 Then ’ instrukcja warunkowa powodująca wyświetlenie okienka
Exit Sub
End If
End With
Inicjacja pętli – otwieranie wybranych plików
For Each element In wybrany_plik.SelectedItems
Set plik = Workbooks.Open(element) ’ przypisanie pliku do zmiennej i otworzenie go
Set arkusz = plik.Worksheets(„Rejestr”) ’ przypisanie arkusza z wypisanymi zadaniami w wybranym pliku do zmiennej
pwolny = baza.Cells(Rows.Count, 2).End(xlUp).Row – 3 ’ pierwszy wolny wiersz w bazie zadań. Wartość zmiennej jest pomniejszona o 3, ponieważ w kolejnej pętli będziemy ją inkrementować, czyli dodawać do niej wartość zmiennej i (nasz licznik). Ta linijka kodu mówi dosłownie – przejdź z arkusza zapisanego w zmiennej baza – z komórki o następującym adresie (ostatni wiersz w całym arkuszu – 1048576, kolumna nr 2 – B) do pierwszej wypełnionej komórki, jaką napotkasz idą w górę. Pobierz jej numer wiersza i pomniejsz go o 3.
ostatni = arkusz.Cells(Rows.Count, 2).End(xlUp).Row ’ ostatni wypełniony wiersz w pliku pracowniczym.
Inicjacja pętli – wprowadzanie danych z pliku pracowniczego do bazy.
For i = 4 To ostatni – wykonaj kroki w ilości równej działaniu ostatni – 4
baza.Cells(pwolny + i, 1).Value = arkusz.Range(„nazwa”).Value ’ wprowadzanie nazwy pracowanika do pierwszej wolnej komórki w kolumnie z pracownikiem w bazie zadań. Myślę, że sam dasz radę rozszyfrować składnię.
baza.Cells(pwolny + i, 2).Value = arkusz.Cells(i, 1).Value ’ wprowadzanie czasu rozpoczęcia do pierwszej wolnej komórki w kolumnie z czasem rozpoczęcia w bazie zadań. Dalsze linie kodu pętli są analogiczne. Zauważ, że jedyne co się zmienia to indeksy kolumn w poleceniu Cells(wiersz, kolumna). Przesuwamy się w prawo zgodnie ze strukturą tabeli.
baza.Cells(pwolny + i, 3).Value = arkusz.Cells(i, 2).Value
baza.Cells(pwolny + i, 5).Value = arkusz.Cells(i, 4).Value
baza.Cells(pwolny + i, 8).Value = arkusz.Cells(i, 7).Value
baza.Cells(pwolny + i, 9).Value = arkusz.Cells(i, 8).Value
baza.Cells(pwolny + i, 10).Value = arkusz.Cells(i, 9).Value
Next i ’ przejście do kolejnej iteracji pętli – następny wiersz danych
Zamknięcie pliku po zakończeniu wypełniania danych
plik.Close False ’ wartość FALSE w tym fragmencie kodu oznacza, że nie chcemy zapisywać zmian przy zamykaniu pliku z danymi do importu. Fizycznie w arkuszu nie zmieniło się nic. Excel traktuje większość operacji, w tym kopiowanie danych jako potencjalną edycję, więc precyzując, że nie chce zapisywać zmian – oszczędzasz sobie konieczności klikania w okienko z potwierdzeniem.
Next element ’ następna iteracja pętli – następny plik
End Sub ’ zakończenie procedury
Wszystkie opisy do poszczególnych fragmentów kodu możesz zobaczyć również wewnątrz przygotowanego pliku.
Uwierz mi, że pisanie makr w języku VBA nie jest niczym trudnym i aby Cię zmotywować do poszerzania wiedzy na ten temat, specjalnie przygotowałem niedoskonałe narzędzie. Arkusze pracownicze rejestrują dokładny moment rozpoczęcia oraz zakończenia pracy nad danym zadaniem, ale nie zostały wyposażone w mechanizm zabezpieczający przed ingerencją we wprowadzone dane. Baza części wymaga dużej systematyki w pracy z plikami. Pracownicy muszą co dzień rano czyścić arkusze z wykonanych dzień wcześniej zadań. A co jeśli tego nie zrobią? Wtedy w bazie pojawią się duplikaty danych.
To tylko dwa z potencjalnych usprawnień, jakie mogą znaleźć się w dzisiejszym narzędziu. Oczywiście, jeśli zechcesz wykorzystać je w swoim miejscu pracy, nic nie stoi na przeszkodzie. Musisz jedynie trzymać się kroków instrukcji zawartych w plikach i dbać by dane w plikach były uzupełniane w standardowy sposób.
Jak zwykle oddaję sekcję komentarzy do twojej dyspozycji. Jestem ciekawy, jakie pomysły na rozwój tego narzędzia lub tworzenie innych coprzyjdą Ci do głowy. W przyszłym tygodniu zapoznam Cię z obiecanymi formularzami.
Pobierz bazę wskaźników produkcyjnych
za darmo!
Zobacz pozostałe artykuły dotyczące opracowania narzędzi Excel:
Absolwent Zarządzania i Inżynierii Produkcji Krakowskiej AGH.
Projektant i wykonawca narzędzi Excel z ponad 3 letnim doświadczeniem.
Wdrożył ponad 100 projektów opartych na arkuszach kalkulacyjnych w przedsiębiorstwach produkcyjnych i usługowych. Autor kursów online „Excel na Produkcji”, „Excel w Usługach” oraz Aplikacji „Analiza Nagrań”. Przeprowadził liczne szkolenia z zakresu metodologii Lean oraz pracy w Excelu w branży na produkcji.
