15 February 2023
Wyodrębnianie danych przy pomocy makr
Poprzednie dwa odcinki poświęciłem tabelom, ale czasem naszym głównym problemem jest przekształcenie dostępnych informacji w tabelę. Jeżeli na przykład nasze dane są ukryte w nieregularnym tekście i jest go bardzo dużo praca może nam zająć długie godziny. Ale od czego mamy makra.
Opis filmu
Nasze ćwiczenie przeprowadzimy na standardowym tekście Lorm Ipsum z nieregularnie wplecionymi danymi o cenach różnych pojazdów które zaznaczyłem na żółto.
……………………..
.
Naszym pierwszym zadaniem będzie pozbycie się wszystkich linijek które nie zawierają na początku słowa „Pojazd”. Zacznijmy jak zawsze od zarejestrowania prostego makra które tym razem kasuje jedną linijkę. A więc Menu Widok -> Makra -> Zarejestruj makro
.
.
Wybierzmy nazwę makra np.: „Konwersja_tekstu_do_tabeli”
.
.
A następnie skasujmy 4 linię, zaznaczając ją, klikając prawym klawiszem myszy i wybierając Usuń
.
.
Następnie zatrzymajmy rejestrowanie makra Menu Widok -> Makra -> Zatrzymaj rejestrowanie oraz zobaczmy nasze makro Menu Widok -> Makra -> Wyświetl makra
.
.
To co zarejestrował Excel wygląda następująco:
Sub Konwersja_tekstu_do_tabeli()
‘
‘ Konwersja_tekstu_do_tabeli Makro
‘
Rows(“4:4”).Select
Selection.Delete Shift:=xlUp
End Sub
‘definicja makra
komentarz
komentarz
komentarz
‘
‘zaznaczenie 4 wiersza
‘skasowanie zaznaczonego wiersza
‘zakończenie makra
Którego uruchomienie da nam już całkiem przyzwoitą choinkę wraz z Excel zarejestrował je w taki sposób jak wykonywaliśmy czynności jednak sam umie tą operację kasowania pojedynczego wiersza wykonać w jednym wierszu kodu.
Rows(4).Delete
Teraz zastanówmy się and algorytmem naszego makra czyli opisem kolejnych czynności które powinno ono wykonać. Chcemy aby: dla każdej linijki sprawdziło ono czy jej początek równa się „Pojazd” a jeżeli nie to ją skasowało.
Z trzeciego odcina mojego poradnika wiemy już że :
„Cells(W,K) =A” nadaje wartość A komórce o wierszu W i kolumnie K
Cells(1,2)=3 oznacza przypisanie komórce B1 wartości 3
Jednocześnie samo Cells(W,K) oznacza wartość komórki w wierszu W i kolumnie K
„For Z=X to Y
Next” powtarza zawarte pomiędzy liniami For oraz Next linie kodu zwiększając za każdym razem zmienną Z o jeden od wartości X aż do Y.
Czyli np. For i=1 to 10
Cells(i,1)=i
Next oznacza że i po kolei przyjmuje wartości 1,2,3,4 …. 10 wpisując te wartości w komórki A1,A2, A3, A4, ….A10
Teraz potrzebne będą nam jeszcze funkcje :
“If Warunek Then
Kod 1
Else
Kod 2
End If
Która realizuje Kod 1 gdy postawiony Warunek jest spełniony a Kod 2 gdy nie
Czyli np.
If X>0 Then
X=X+1
Else
X=X-2
End If
Sprawdza czy zmienna X jest większa od 0 i dodaje do niej 1 jeżeli jest a odejmuje 2 jeżeli nie jest
„Left(Tekst,Ilość_Znaków)” która zwraca Ilość_Znaków Tekstu od lewej strony
Czyli np.:
Tekst=Left(„Wesołych Świąt,8) przypisze zmiennej Tekst wartość „Wesołych”
„Split (Tekst,”znak rozdzielający”)” która dziali tekst na szereg mniejszych tekstów które w tekście źródłowym były rozdzielone wskazanym znakiem
Czyli np.
Wynik = Split(„Anna;Adam;Andrzej;Waldemar,”;”) spowoduje przypisanie tabeli Wynik poszczególnych imion a w szczególności Wynik(0)=”Anna”, Wynik(1)=”Adam”, Wynik(3)=”Andrzej”, Wynik(4)=”Waldemar”
Ostatnią potrzebną nam do realizacji zadania rzeczą będzie definicja tabeli tekstów:
Dim Tabela() As String gdzie Tabela jest nazwą tabeli a String określa że chodzi o tabelę tekstów.
Teraz mamy już komplet wiedzy teoretycznej do stworzenia naszego makra. Więc piszmy
.
Sub Konwersja_tekstu_do_tabeli()
‘
‘ Konwersja_tekstu_do_tabeli Makro
‘
L = 1
For i = 1 To 100
If Left(Cells(L,1),6) = “Pojazd”Then
L = L + 1
Else
Rows(L).Delete
End If
Next
End Sub
‘definicja makra
komentarz
komentarz
komentarz
‘numer linii od której zaczynamy
‘przez 100 kolejnych linii
‘jeżeli pierwsze 6 znaków tekstu równa się “Pojazd”
‘idziemy do następnej linii
‘a jeżeli nie to
‘kasujemy linię
‘następne i / linia
‘zakończenie makra
Po jego uruchomieniu dostaniemy następujący wynik
.
Jak widać zniknęły nam wszystkie zbędne linie ale przydało by się jeszcze rozdzielić do osobnych kolumn opisy pojazdów od opisów cen. Zastosujmy więc funkcję split
Sub Konwersja_tekstu_do_tabeli()
‘
‘ Konwersja_tekstu_do_tabeli Makro
Dim Linia() As String
L = 1
For i = 1 To 100
If Left(Cells(L, 1), 6) = “Pojazd” Then
Linia = Split(Cells(L, 1), “,”)
Cells(L, 2) = Linia(0)
Cells(L, 3) = Linia(1)
L = L + 1
Else
Rows(L).Delete
End If
Next
End Sub
‘definicja makra
komentarz
komentarz
‘definicja tabeli tekstów o nazwie Linia
‘numer linii od której zaczynamy
‘przez 100 kolejnych linii
‘jeżeli pierwsze 6 znaków tekstu równa się “Pojazd”
‘wczytujemy dane linii rozdzielone przecinkami do tabeli Linia
‘pierwszą wartość tabeli Linia wpisujemy w kolumnę 2
‘drugą wartość tabeli Linia wpisujemy w kolumnę 3
‘idziemy do następnej linii
‘a jeżeli nie to
‘kasujemy linię
‘następne i / linia
‘zakończenie makra
Tym razem uzyskamy następujący efekt
.
Jest dużo lepiej ale jeszcze przydało by się pozbyć napisów Pojazd i Cena. Zobaczcie, że tym razem nasze teksty od faktycznych danych rozdzielone są dwukropkiem. Wykorzystamy ten fakt i użyjemy jeszcze raz funkcję Split w następujący sposób
Sub Konwersja_tekstu_do_tabeli()
‘
‘Konwersja_tekstu_do_tabeli Makro
Dim Linia() As String
Dim Pojazd() As String
Dim Cena() As String
L = 1
For i = 1 To 100
If Left(Cells(L, 1), 6) = “Pojazd” Then
Linia = Split(Cells(L, 1), “,”)
Pojazd = Split(Linia(0), “:”)
Cena = Split(Linia(1), “:”)
Cells(L, 2) = Pojazd(1)
Cells(L, 3) = Cena(1)
L = L + 1
Else
Rows(L).Delete
End If
Next
End Sub
‘definicja makra
komentarz
komentarz
‘definicja tabeli tekstów o nazwie Linia
‘definicja tabeli tekstów o nazwie Pojazd
‘definicja tabeli tekstów o nazwie Cena
‘numer linii od której zaczynamy
‘przez 100 kolejnych linii
‘jeżeli pierwsze 6 znaków tekstu równa się “Pojazd”
‘wczytujemy dane linii rozdzielone przecinkami do tabeli Linia
‘wczytujemy dane Pojazdu rozdzielone dwukropkiem z pierwszej wartości tabeli Linia
‘wczytujemy dane Cen rozdzielone dwukropkiem z drugiej wartości tabeli Linia
‘drugą wartość tabeli Pojazd wpisujemy w kolumnę 2
‘drugą wartość tabeli Linia wpisujemy w kolumnę 3
‘idziemy do następnej linii
‘a jeżeli nie to
‘kasujemy linię
‘następne i / linia
‘zakończenie makra
Teraz już otrzymamy dokładnie to co chcieliśmy czyli dane pojazdów oraz ich ceny w osobnych kolumnach.
.
To bardzo krótkie makro może umożliwić wam konwersję danych tekstowych na konkretne wartości niezależnie od ich ilości i stopnia „zaśmiecenia”.
Zapraszam Was do korespondencji na temat makr i wszystkiego co się wiąże z Excelem a co sprawia wam jakieś problemy na adres konrad.pogodz@itch.pl
Pobierz darmowy materiał szkoleniowy: