Przyjemna praca z wprowadzaniem danych
Jak wykorzystać Excel przy wprowadzaniu danych? Zobacz przykłady, jak usprawnić pracę z danymi – ułatwić i przyśpieszyć czynności z tym związane.
Spis treści:
Przed Tobą kolejny artykuł dotyczący możliwości wykorzystania języka VBA do usprawniania pracy w Excelu. Na wstępie chciałbym pokazać Ci fragment zrzutu ekranu i od razu zadać pierwsze pytanie. Przyjrzyj się i powiedz, czy taki obraz jest Ci znajomy.
Jeżeli kiedykolwiek pracowałeś na pliku, który edytuje kilka osób, to przedstawiony wycinek danych, na pewno wzbudził u Ciebie nieprzyjemne wspomnienia. Jest to fragment pliku z harmonogramem produkcji, do którego trafia każde nowe zlecenie, przedstawia odpowiedzialne za realizacją brygady. Jeżeli dobrze się przyjrzysz, zobaczysz niekonsekwencję w prowadzeniu pliku. Zaznaczyłem dziewięć różnych wpisów z czego cztery dotyczą tej samej brygady, a zapisano je na trzy różne sposoby.
Dzisiaj chciałbym pokazać Ci konsekwencję wynikające z braku powtarzalności w uzupełnianiu danych oraz sposoby na zabezpieczenie się przed tym zjawiskiem.
Czytasz już jedenasty artykuł z serii narzędzi Excel do opracowania danych (ZOBACZ POZOSTAŁE 
). Oznacza to, że miałeś przynajmniej tyle samo okazji do poznania różnych możliwości i zastosowań arkusza kalkulacyjnego. Jest to potężne narzędzie, ale, do osiągnięcia maksymalnego potencjału, potrzebuje standaryzacji.
Zdaje sobie sprawę, że w trakcie lektury któregoś z wpisów w twojej głowie mogły zrodzić się wątpliwości dotyczące standardowego sposób prowadzenia plików. Skorzystajmy, dla przykładu, z bazy danych przedstawionej powyżej. Jeżeli będzie ona uzupełniana przez jednego użytkownika, nie powinno być problemów. Najczęściej jednak do baz danych trafiają informacje z wielu źródeł lub są wprowadzane przez różne osoby, zazwyczaj – w niejednolity sposób.
Poniżej widzisz efekt mojej próby stworzenia raportu. Zostałem poproszony przez szefa działu o podsumowanie liczby zleceń wykonanych przez poszczególne brygady.
Zaleta tabeli przestawnej, jaką jest błyskawiczne podsumowanie danych, przestaje mieć w tym przypadku znaczenie. Co z tego, że dostałem sumy dla każdej z brygad, skoro i tak muszę zsumować sześć wersji tego samego wpisu. Wyobraź sobie teraz, że zamiast kilku brygad, w polu wierszy twojej tabeli przestawnej znajdują się tysiące produktów i okazuje się, że przynajmniej połowa z nich została zdublowana przez niestandardowy sposób prowadzenia bazy.
Aby uniknąć konieczności poprawiania tysięcy rekordów, musisz zadbać, aby każdy prowadził twój plik w standardowy sposób. Jeżeli to zrobisz, praca z danymi w Excelu stanie się czystą przyjemnością
Tak! Praca z danymi w Excelu może być przyjemna.
Sposoby na usprawnienie pracy z arkuszem kalkulacyjnym
Moim zdaniem, komfort pracy z arkuszem kalkulacyjnym najlepiej odzwierciedla przysłowie – jak sobie pościelisz, tak się wyśpisz. Dr Deming w swojej książce pt. Out of the Crisis pisze, że środowisko pracy, w jakim znajdują się ludzie, warunkuje w 90 do 95% wydajność tego systemu. O ile wartości będą się wahać w zależności od sytuacji, to niezaprzeczalnym faktem jest, iż właściwy system zapewni warunki do utrzymania stabilności i jakości pracy. Jeżeli chcesz, by twoje arkusze wyróżniały się tymi cechami, musisz zestandaryzować sposób ich prowadzenia.
Przykład opisanego rozwiązania do pobrania:
Harmonogram produkcji
Formularze użytkownika VBA – przepis na powtarzalność plików Excel
Jedną z najlepszych metod zapewnienia powtarzalności i systematyki prowadzenia plików Excel są tzw. formularze użytkownika, które są częścią Edytora VBA. Zobowiązałem się w zeszłym tygodniu pokazać takie rozwiązanie, więc zgodnie z obietnicą – przechodzę do przygotowanego na dzisiaj przykładu.
Powyższy plik to prosty harmonogram produkcji z przedsiębiorstwa branży motoryzacyjnej. Wprowadzane do niego dane to: numer wewnętrzny i model samochodu, data przyjęcia zlecenia oraz nazwa zamawiającego (klienta) i odpowiedzialna za realizację brygada. Harmonogram to prosta checklista oparta o trzy wartości: 1 – gotowe, 0,5 – w trakcie i 0 – nierozpoczęte. W kolumnach planu produkcji widnieją poszczególne procesy rozbite na elementy: materiał i operację. Jedynka w kolumnie M: Spawanie oznacza, że dostępne są wszystkie materiały, potrzebne do rozpoczęcia tej operacji. Jedynka w polu P: Spawanie oznacza, że ta część procesu została zrealizowana.
Rejestr jest prowadzony przez dwie osoby, które dopiero rozpoczynają swoją przygodę z obsługą arkuszy kalkulacyjnych. Implementując wewnątrz formularz do wprowadzania nowych zleceń, zyskałem nie tylko standardowy zestaw danych, ale również: ograniczenie liczby pomyłek, skrócenie czasu wprowadzania zleceń, wzrost komfortu pracy i co bardzo istotne – znacząco obniżenie czasochłonności szkolenia nowych osób z prowadzenia pliku.
Jeżeli jeszcze nie zapoznałeś się z poprzednim artykułem Jak łączyć dane z wielu plików , koniecznie musisz nadrobić zaległości. Ta wiedza będzie Ci potrzebna do zrozumienia poniższych instrukcji!
Formularz VBA – zastosowanie Excela na produkcji
Poniżej możesz przyjrzeć się finalnemu efektowi kroków opisanych w tym artykule. W kolejnych krokach wyjaśnię Ci, jak zbudować taki formularz.
Jak wstawić formularz VBA?
Formularze użytkownika wstawia się z poziomu edytora VBA w oknie listy projektów.
- W edytorze VBA w oknie projektu kliknij prawym przyciskiem myszy w dowolnym miejscu
- Wybierz pozycję Insert – User Form
Jak wstawić pola (kontrolki) wewnątrz formularza?
Kontrolki formularza to nic innego jak pola umieszczane na formularzu. W naszym przykładzie będziesz potrzebować poniższych kontrolek:
4 x pole kombi
2 x przycisk opcji
9 x CheckBox
1 x SpinButton
2 x pole tekstowe
6 x etykieta
3 x przycisk akcji
Kontrolki umieszczamy na formularzu metodą przeciągnij i upuść:
Dobrą praktyką jest wcześniejsze sformatowanie kontrolek – dzięki temu oszczędzimy sobie pracy. Umieść po jednej kontrolce z każdego rodzaju, który potrzebujesz, a każde kolejne będziesz mógł kopiować razem z formatowaniem.
Rozmiar formularza i układ kontrolek nie ma większego znaczenia. Zachęcam Cię jednak do dopilnowania logicznego układu pól i ogólnej czytelności. Zostaw odpowiednie odstępy między kontrolkami, zadbaj o odpowiedni rozmiar pól i poukładaj je intuicyjnie, czyli analogicznie do ich kolejności w tabeli docelowej. Wysokość i szerokość formularza możesz zmieniać wewnątrz okna właściwości lub po prostu rozciągając go jak zdjęcie wstawione do dokumentu Word.
Umieść po jednej kontrolce z wyżej wymienionych (oprócz Spinbutton’a) na formularzu.
Formatowanie kontrolek w formularzu VBA
Ogromną część właściwości powtarza się niezależnie od typu kontrolki, więc formatowanie będzie szybkim i prostym zabiegiem.
Czcionka i wielkość tekstu
1. Użyj skrótu Ctrl + A lub zaznacz wszystkie pola za pomocą myszki.
2. Z okna właściwości wybierz opcję Font i użyj przycisku opcji, który pojawi się po prawej stronie.
3. Okno, które masz przed oczami powinno być Ci znane. Jest to takie samo okno formatowania czcionki jak to wewnątrz arkusza kalkulacyjnego. Nie znajdziesz tu jedynie koloru czcionki. Aby odczytanie etykiet na formularzu nie wymagało mikroskopu, zwiększ czcionkę do rozmiaru 14 i potwierdź wybór przyciskiem OK.
Zmiana koloru pól i formularza VBA
1. Kliknij gdziekolwiek w pustą przestrzeń formularza, aby zaznaczyć go jako aktywny i wybierz kolor tła używając pozycji Back color z okna właściwości. Jeżeli użyjesz prawego przycisku myszy, na jednym z pól w dwóch ostatnich rzędach, będziesz mógł wybrać dowolny kolor z palety RGB
2. Przytrzymując Ctrl, zaznacz następujące kontrolki: OptionButton, CheckBox i etykietę. Z okna Właściwości wybierz opcję BackStyle i z listy rozwijanej pozycję nr 0 – fmBackStyleTransparent.
Dodawanie znaczników
Znaczniki to opcje kontrolek, które znacznie ułatwiają programistyczną część przygotowania formularza.
Możemy nadać kilku polom ten sam znacznik i w ten sposób stworzyć grupy pól. Pozwoli to edytować wszystkie obiekty danej grupy tym samym fragmentem kodu, czyli przyspieszy naszą pracę nad tworzeniem formularza.
Zaznacz pole tekstowe oraz pole kombi i w oknie Właściwości odnajdź pozycję Tag. Następnie wpisz w to pole „1”. Pozwoli nam to później szybko zaimplementować mechanizm sprawdzania kompletności danych.
Powielanie kontrolek
Jak widzisz wyrównywanie pól formularza bywa irytujące i mozolne. Zapewniam Cię, że jeżeli będziesz pracował mądrze i zastosujesz się do poniższych wskazówek, wszystkie operacja związane z tworzeniem formularzy będą czystą przyjemnością.
1. Stosuj skrót Ctrl + a do zaznaczania wszystkich kontrolek wewnątrz formularza.
2.Przytrzymuj Ctrl, aby zaznaczyć różne kontroli.
3. Przytrzymuj Shift, aby zaznaczyć wszystkie kontrolki pomiędzy pierwszą kontrolką, którą wybrałeś, a drugą.
4. Pamiętaj, że możesz wybierać wszystkie kontrolki na danym obszarze, używając przeciągania myszy.
5. Możesz edytować precyzyjnie położenie kontrolek używając właściwości Top i Left.
Zmniejszenie wartość Left przesunie kontrolkę w lewo, natomiast zwiększenie w prawo. Zmniejszenie wartość Top przesunie kontrolkę w górę, natomiast zwiększenie w dół.
6. Używaj opcji wyrównaj do. Opcja ta pozwala Ci wyrównać położenie kontrolek do jednej wybranej.
7. Zaznacz kilka kontrolek, a następnie z paska narzędzi wybierz opcję wyrównaj.
Ważna jest tu kolejność wyboru i sposób w jaki zaznaczasz kontrolki.
Zmiana rozmiaru kontrolek
Zaznacz kontrolkę z przyciskiem opcji i dwukrotnie kliknij na prawym dolnym kwadracie zaznaczenia. Rozmiar kontrolki zostanie automatycznie dostosowany do wypełnienia. Przyciski i pola tekstowe możesz rozszerzać i zmniejszać tak jak formularz – oknem właściwości lub chwytając i przeciągając kwadraty na granicy zaznaczenia.
Zmiana tekstu w polach formularza
Etykiety, przyciski opcji, checkboxy oraz przyciski akcji mają właściwość Caption. Zmiana tej właściwości spowoduje zmianę tekstu wyświetlanego na formularzu. Samo główne okno formularza również ma taką właściwość.
Możesz również edytować tekst kontrolki bezpośrednio na formularzu. Zaznacz dowolną kontrolkę, a następnie ponownie kliknij na nią lewym przyciskiem myszy. Przy tej metodzie musisz zachować ostrożność, ponieważ zbyt szybkie dwukrotne kliknięcie, przeniesie Cię do okna kodu danego pola. Aby powrócić do widoku edycji formularza możesz użyć ikony w oknie projektów lub kliknąć dwukrotnie na nazwę formularza na liście.
Nadawanie wartości domyślnej
W opisywanym przypadku, zamówienie najczęściej dotyczy pojedynczego samochodu. Jeżeli pozostawisz w polu ilość domyślnie liczbę 1, zwiększysz komfort pracy i przyspieszysz nieco wprowadzanie danych. Aby to zrobić zaznacz pole ilość i znajdź wpis Value w oknie Właściwości. Wprowadź w nie liczbę 1.
Wykorzystując powyższe wskazówki, doprowadź formularz do tej postaci:
Nadawanie nazw kontrolkom
Jedną z najważniejszych czynności w projektowaniu formularzy jest właściwe nazewnictwo obiektów. Mam tu namyśli wewnętrzne nazwy, po których będziesz odnosić się do konkretnych pól, programując wszystkie mechanizmy. Wewnętrzna nazwa to pierwsze pole w oknie właściwości obiektu oznaczone w nawiasach – (Name)
Wartość tego pola nie może zawierać: samych liczb, znaków specjalnych oraz spacji. Jeżeli potrzebujesz nazwy dwuczłonowej, zamiast spacji użyj tzw. podłogi – „_”. Nazwa powinna odnosić się w sposób jasny do wartości, jaka będzie przechowywana w danym polu np.: jeżeli nadajesz nazwę polu, w którym będziesz wprowadzał datę, niech brzmi ona po prostu „data”. Jeżeli wprowadzisz nieintuicyjne oznaczenia, będzie Ci dużo trudniej programować formularz.
Odpowiednie przygotowanie danych kluczem do sukcesu formularza
W polach kombi będą znajdować się listy wybierane. Aby zadziałały musimy przygotować odpowiednie zestawy danych wewnątrz arkusza pomocniczego. Formularz będzie zaciągał stamtąd dane, a użytkownicy będą mogli w prosty sposób, bez konieczności zmiany kodu, je edytować.
Najlepiej jest to zrobić stosując sformatowane tabele. W trzecim artykule naszej serii opisałem sposób wstawiania takiej tabeli (ZOBACZ TUTAJ ). Zachęcam Cię do odświeżenia wiedzy z tego artykuły zanim przejdziesz dalej.
Będziesz potrzebował następujących tabel danych:
Odpowiednie nazewnictwo tych tabel jest równie ważne, ponieważ umożliwi nam tworzenie precyzyjnych i odpornych, na zmiany struktury arkuszy, mechanizmów.
Wszystkie nazwy tabel oraz pól formularza będziesz mógł zobaczyć wewnątrz przygotowanego przeze mnie pliku.
Programowanie formularza w języku VBA
Osobiście uważam, że największą frajdę z nauki nowych rzeczyma się wtedy, gdy samodzielnie odkrywa się wiedzę i dochodzi do pewnych wniosków. W myśl tej zasady opiszę najważniejsze mechanizmy programowania formularzy, a sam kod zostawię Ci do samodzielnej analizy. Oczywiście będzie on dokładnie opisany wewnątrz pliku, który znajdziesz na końcu artykułu.
Programowanie zdarzeń formularza
Formularz i wszystkie jego kontrolki mają zestaw zdarzeń, pod które można zaprogramować mechanizmy w języku VBA. Zdarzeniem może być np.: zmiana wartości w polu, kliknięcie w pole danych, czy uruchomienie formularza.
Poszczególne zdarzenia można wybierać z poziomu okna kodu w edytorze VBA. Aby przejść do okna kodu, kliknij dwukrotnie w puste pole na formularzu. Zauważ, że edytor VBA automatycznie przypisze zdarzenie, które właśnie miało miejsce, czyli kliknięcie w arkusz.
Jeżeli pozostawimy kod w takiej postaci, wszystko co zapiszemy pomiędzy słowami kluczowymi, wykona się w momencie, gdy klikniemy lewym przyciskiem myszy na formularz.
Inne zdarzenia możemy wybierać z listy znajdującej się w prawym górnym rogu okna kodu. Lista po lewej stronie służy do wyboru obiektu formularza.
Ładowanie danych do formularza
Wypełnianie danymi pól wewnątrz formularza programujemy w zdarzeniu inicjującym Initialize. Każda procedura zapisana w tym zdarzeniu wykona się po uruchomieniu formularza. Aby przejść do zdarzenia inicjującego, ustaw się pomiędzy słowami kluczowymi i wybierz je z listy.
Wewnątrz słów kluczowych zdarzenia inicjującego wprowadź kod widoczny poniżej. Składa się on z następujących elementów:
1. Zmienne:
- arkusz – w którym znajdują się dane do list
- element – zmienna do pętli, będą tam przechowywane komórki z zakresu
- zakres – tu będą przechowywane zakresy danych, czyli tabele które stworzyłeś w arkuszu
2. Obiekty
3. Pętla ładująca dane do pola tabeli
Set arkusz = LISTY
przypisanie do zmiennej arkusza z listami danych. Zastosowałem tutaj odwołanie do wewnętrznej nazwy arkusza. Dzięki temu będę mógł zmieniać nazwę arkusza z poziomu arkusza, nie psując mechanizmu zaciągania danych. Nazwę wewnętrzną arkusza możesz sprawdzić w oknie Właściwości.
Set zakres = arkusz.Range(„modele[Model:]”)
przypisanie do zmiennej zakresu danych. W tym przypadku jest to tabela z modelami samochodów. Tabela ma dwie kolumny, dlatego musiałem sprecyzować kolumnę docelową w nawiasie kwadratowym. „modele” to nazwa całej tabeli, „[Model:]” to konkretna kolumna tej tabeli.
For Each element In zakres ‘ pętla przechodząca przez każdą komórkę w zakresie
If element.Value <> „” Then ‘ instrukcja warunkowa if sprawdzająca, czy wewnątrz zakresu nie ma pustych pól. Jeżeli pojawi się puste pole, zostanie ono pominięte.
Me.model.AddItem element.Value ‘Me.model – to odniesienie do pola z modelem samochodu wewnątrz formularza. .AddItem – to polecenie dodania elementu do pola. element.Value – to dodawany obiekt.
End If ’zakończenie instrukcji warunkowej
Next element ’kolejny element pętli
Aby załadować kolejne pola wystarczy powielić kod od fragmentu Set zakres = arkusz.Range(„modele[Model:]”) do Next element i zmienić nazwę zakresu wewnątrz nawiasu.
Wypełnianie tabeli danych
Tabelę docelowa ma być uzupełniona danymi z formularza, dopiero po zatwierdzeniu przyciskiem Wprowadź. Kliknij dwukrotnie na przycisk, aby zainicjować odpowiednie zdarzenie. Nie musisz nic zmieniać. Edytor VBA sam przypisze zdarzenie kliknięcia do przycisku. Twoim zadaniem jest wpisać między słowa kluczowe poniższy kod
Znakomitą większość powyższych instrukcji poznałeś w poprzednim artykule. Nowością jest tu instrukcja With, która pozwala odnosić się do danego obiektu za pomocą kropki – „.”. W tym przypadku każda instrukcja po kropce oznacza odniesienie do arkusza z harmonogramem. Nową instrukcją będzie również polecenie Unload Me. Jest to zamknięcie formularza.
Dodatkowe mechanizmy
Wypełnianie danych w zależności od wybranej opcji
Jeżeli wybrana zostanie opcja Pełna Zabudowa, automatycznie zostaną zaznaczone i zablokowane wszystkie procesy. Chronisz w ten sposób arkusz przed pominięciem operacji w pełnym zleceniu. Wybranie opcji Usługa przerobienia, usunie blokadę i pozwoli wybrać zestaw operacji dla danego zamówienia. Domyślną opcją jest wybór pełnej zabudowy.
Szczegóły dotyczące tego mechanizmu zostały opisane w pliku.
Zmiana daty z wykorzystaniem przycisku Spinbutton
Być może zwróciłeś uwagę na to, iż do tej pory na formularzu nie pojawiła się jedna kontrolka, którą od początku mamy na liście. Przyszedł czas, aby weszła do gry.
1. Pod pole tekstowe z datą przyjęcia wstaw z Toolboxa kontrolkę Spinbutton. Zaznacz wstawiony przycisk i rozciągnij go na szerokość pola z datą. Kontrolka Spinbutton ustawiona jest domyślnie na zmianę orientacji w zależności od rozmiaru, dlatego układ przycisków zmienił się na poziomy.
2. Kolejnym etapem będzie stworzenie mechanizmu pomocniczego, który pozwoli Ci zmniejszać i powiększać datę. Ponieważ arkusz kalkulacyjny radzi sobie lepiej z operacjami na różnych formatach zaimplementujemy ten mechanizm w komórkach Excela.
W arkuszu z listami danych wybierz jedną komórkę oddaloną od list z danymi i nadaj jej nazwę Data. Poniżej tej komórki nadaj nazwę Różnica. Unikaj w nazwach znaków specjalnych.
3. Ostatnią rzeczą będzie wprowadzenie do kodu formularza poniższych akcji:
Wskazówki ułatwiające tworzenie i programowanie formularzy w języku VBA
Jak widzisz tworzenie i programowanie formularzy nie jest niczym skomplikowanym. Mam nadzieję, że po dzisiejszym artykule ten kawałek Excelowej magii, stanie się dla Ciebie przydatnym trickiem, którym nie raz zadziwisz swoich przełożonych i współpracowników. Zanim pobiegniesz testować przygotowany specjalnie dla Ciebie plik z formularzem, zapamiętaj proszę kilka rad:
Każdy element formularza możesz programować pod różne zdarzenia
Zdarzenia są dostępne z list wewnątrz edytora VBA
Jeżeli przejdziesz z formularza do okna kodu przez dwukrotne kliknięcie, użyj ikony widoku obiektu nad listą projektów
Ułatwiaj sobie pracę z pozycjonowaniem kontrolek używając klawiszy shift i ctrl, a także funkcji wyrównaj do
Dwukrotne kliknięcie w prawy dolny kwadracik zaznaczonej kontrolki, automatycznie dopasujesz jej rozmiar do treści
Możesz tworzyć własne kolory klikając prawym przyciskiem myszy na dowolne pola w palecie barw
Każde zdarzenie zaczyna się od słów kluczowych Private Sub, a kończy na End Sub
Pracuj mądrzej, a nie więcej!
Zalety tworzenia formularzy użytkownika
Standaryzacja prowadzenia plików
Eliminacja błędów przy wprowadzaniu danych
Gwarancja kompletności danych
Przyspieszenie i poprawa komfortu wprowadzania danych
Ułatwienie i przyspieszenie wdrażanie nowych osób w prowadzenie pliku
Ułatwienie pracy osobom nie znającym Excela
Możliwość stosowania wielu mechanizmów poprawiających jakość danych np.: listy wybierane
Wizualizacja sposobu wprowadzania danych
Szkolenie Online
Planowanie produkcji – narzędzie Excel
Układanie planu produkcji
Monitorowanie dostępności materiałów
Opieranie decyzji na danych, faktach i liczbach
Szybkie podejmowanie decyzji planistycznych
Chcesz uzyskać pomoc w planowaniu produkcji w Twojej firmie? Sprawdź projekt, który realizujemy: Planowanie produkcji.
To projekt, który ma na celu rozwiązanie problemów takich jak wysokie koszty produkcji, duże zapasy, ciągłe przezbrojenia, opóźnienia wysyłek czy brak mocy produkcyjnych.
Aby sprawniej zarządzać produkcją wypróbuj aplikację ZaPro, która została stworzona, mając na uwadzę specyfikę branży.
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.
