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.

Wyodrębnianie danych przy pomocy makr
theme-w-ico
theme-ico

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 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:

Dzielimy się
wiedzą

Nasza Firma zapewnia nie tylko najnowsze rozwiązania z dziedziny IT ale również nie pozostaje w miejscu każdego dnia poszerza swoją wiedzę którą bardzo chętnie dzielimy się z innymi. Zapraszamy do zapoznania się z ostatnimi nowinkami.

theme-w-ico
arrows-ico-wh
Imię i Nazwisko łączenie, rozdzielanie, zamiana kolejności

Z doświadczenia wiem, że często powtarzającym się problemem jest obróbka imion i nazwisk. Czy powinny być w dwóch kolumnach czy w jednej i w jakiej kolejności ?

theme-w-ico
arrows-ico-wh
POWER APPS – AUDYT część 4

Zapraszamy na ostatni odcinek instrukcji tworzenia w Power Apps aplikacji umożliwiającej audytowanie dowolnych procesów przedsiębiorstwa.

theme-w-ico
arrows-ico-wh
POWER APPS – AUDYT część 3

Zapraszam na trzeci odcinek instrukcji tworzenia w Power Apps aplikacji umożliwiającej edytowanie dowolnych procesów przedsiębiorstwa.

 

theme-w-ico
theme-ico

Poznajmy się
i porozmawiajmy!

arrows-b

Skontaktuj się z nami i otwórz drzwi do innowacyjnych rozwiązań technologicznych dla Twojej firmy. Nasz zespół ekspertów jest gotów odpowiedzieć na Twoje pytania, dostosować ofertę do Twoich potrzeb i wesprzeć Cię w osiągnięciu sukcesu.

Wykorzystaj potencjał technologii już dziś, by zwiększyć efektywność, rozwijać biznes i zadbać o bezpieczeństwo swoich danych.

zespół
theme-ico