Kako upravljati podatke v Excelu z uporabo VBA

Microsoft Excel je močno orodje, ki ga lahko uporabite za obdelavo podatkov. Da bi kar najbolje izkoristili programsko opremo, morate uporabiti VBA. Visual Basic for Applications ali VBA omogoča uporabnikom programa Excel, da ustvarijo makre, ki so zmogljive funkcije, ki prihranijo čas in omogočajo manipulacijo in analizo podatkov.

Makroji obdelujejo VBA kodo za upravljanje velikih podatkovnih nizov, ki bi sicer potrebovali veliko časa za spreminjanje. Na primer, z VBA lahko ustvarite makro za samodejno oblikovanje določenih polj, ki ustrezajo vašim določenim kriterijem.

Spodaj je primer skripta VBA, ki se uporablja v Excelu:

Sub ConfigureLogic ()

Dim qstEntries

Dim dqstEntries

Dim qstCnt, dqstCnt

qstEntries = Območje ("QualifiedEntry")

qst = qstEntries - WorksheetFunction.CountIf (območje ("QualifiedEntry"), "")

ReDim QualifiedEntryText (qst)

"MsgBox (qst)

dqstEntries = Območje ("DisQualifiedEntry")

dqst = dqstEntries - WorksheetFunction.CountIf (Razpon ("DisQualifiedEntry"), "")

ReDim DisqualifiedEntryText (dqst)

'MsgBox (dqst)

Za qstCnt = 1 Za qst

QualifiedEntryText (qstCnt) = ThisWorkbook.Worksheets ("Kvalifikatorji"). Razpon ("J" & 8 + qstCnt).

'MsgBox (QualifiedEntryText (qstCnt))

beleženje ("Konfiguriran vnos s kvalificiranim vnosom #" & qstCnt & "kot {" & QualifiedEntryText (qstCnt) & "}")

Naslednji

Za dqstCnt = 1 Za dqst

DisqualifiedEntryText (dqstCnt) = ThisWorkbook.Worksheets ("Kvalifikatorji"). Razpon ("M" & 8 + dqstCnt).

'MsgBox (DisqualifiedEntryText (dqstCnt))

beleženje ("Konfiguriran vnos v disketni pogovor #" & qstCnt & "kot {" & DisqualifiedEntryText (dqstCnt) & "}")

Naslednji

includeEntry = ThisWorkbook.Worksheets ("Kvalifikatorji"). Razpon ("IncludeSibling").

"MsgBox (vključi vnos)

beleženje ("Entrys vključen v iskanje -" & includeEntry)

End Sub

Kako analizirati in upravljati vnose v preglednici

Za uporabo VBA za analizo podatkov boste morali preveriti nastavitve v Excelu za orodje za razvijalce. Če ga želite najti, poiščite trak Excel in poiščite zavihek Razvijalec . Če ni prikazan, ga morate aktivirati v meniju Excelove nastavitve .

Nato ustvarite nov delovni list in ga poimenujte "Kvalifikatorji". Ta list bomo uporabili za preverjanje vseh stvari, ki izpolnjujejo pogoje za izbiro.

Nato nastavite kvalifikatorje na listu v skladu s kodo. Vnesti ga morate ročno; cut in paste ne bo delovalo.

ThisWorkbook.Worksheets ("Kvalifikatorji"). Razpon ("J" & 8 + qstCnt) .vrednost

Kako najti območje in zgraditi matriko

Območje v zgornji funkciji je celica J9. Funkcija obsega beleži 8; vendar je dejanski razpon 9, ker:

 Za qstCnt = 1 Za qst 

Zgornji stavek se začne pri 1, ne v 0. Zato se seznam začne pri 9. V tem primeru upoštevajte (qstCnt = 1).

Če želite izdelati matriko iz vnosov na delovni listi Kvalifikatorji, postavite naključne besede v celice J9-J13. Ko so vrstice končane, lahko nadaljujemo z iskanjem in manipuliranjem podatkov v Excelu.

 Zasebna podračuna ()

Dimni list

Dim WS kot delovni list

stanja = 0

beleženje ("***** Začetek čiščenja *********")

Za vsako WS v tem knjigi

sheetcount = sheetcount + 1

Če nato WS.Name = "Izbrano"

'moram zapisati datum in čas v list z imenom "Prijava"

ActionCnt = ActionCnt + 1

beleženje ("Klicni list:" & WS.Name)

čiščenje (število listov)

Drugače

ActionCnt = ActionCnt + 1

beleženje ("Preskočeno nad poljem:" & WS.Name)

End If

Naprej WS

"MsgBox (" konec ")

ActionCnt = ActionCnt + 1

beleženje ("**** Čiščenje storjeno!")

Application.ScreenUpdating = True

End Sub

Obstaja primer delovnega števca zavihkov.

Dimni list

Dim WS kot delovni list

stanja = 0

beleženje ("***** Začetek čiščenja *********")

Za vsako WS v tem knjigi

sheetcount = sheetcount + 1

Po inicializaciji števca listov nastavite na 0, da znova zaženete števec.

Logging () je še en podprogram, ki sledi vsem dejanjem za preverjanje izbir.

Naslednja zanka For nastavi Active Workbook za štetje. WS je inicializiran in ThisWorkbook. Delovni listi so aktivni zavihek v knjigi. Ker delovnega zvezka nismo imenovali, bo ta modul deloval na katerem koli aktivnem delovnem zvezku. Če delate na več delovnih zvezkih in imate napačen aktiviran, bo poskusil zagnati na njem. Da bi se izognili napakam, upoštevajte varnostne ukrepe, da določite svoj delovni zvezek, ali pa delajte samo na enem.

Vsakič, ko se zanka prižge, doda eno spremenljivko štetju listov, da spremlja število zavihkov. Nato se premaknemo na:

 Če nato WS.Name = "Izbrano"

'moram zapisati datum in čas v list z imenom "Prijava"

ActionCnt = ActionCnt + 1

beleženje ("Klicni list:" & WS.Name)

čiščenje (število listov)

Drugače

ActionCnt = ActionCnt + 1

beleženje ("Preskočeno nad poljem:" & WS.Name)

End If

Tukaj iščemo zavihek Izbrano.

Če je spremenljivka WS enaka izbrani, jo zapišemo in sprožimo podprogram Scrub Sheet. Če spremenljivka WS ni enaka izbrani, se zabeleži, da je bil ta list preskočen in da se dejanje prešteje. Zgornja koda je primer, kako preštejemo število in poiščemo določen zavihek.

Naslednji listng je vse različne metode, ki se lahko uporabljajo za manipulacijo podatkov!

Zabavaj se!

Kako preštejte število listov v delovnem zvezku

 Dim TAB

Za vsak TAB v tem priročniku

Tukaj je nekaj rutinskega

Naslednji

Poiščite zadnji vrstico, stolpec ali celico na delovnem listu

 Število celic

cellcount = Celice (ThisWorkbook.Worksheets ("delovni list"). Rows.Count, 1) .End (xlUp).

Filtrirajte s pomočjo naprednih kriterijev

 Razpon ("A2: Z99") Razvrsti ključ1: = Območje ("A5"), vrstni red1: = xlAscending, Glava: = xlNo 

Uporabite Samodejno prilagodi lastnost na stolpec

 Stolpci ("A: A"). EntireColumn.AutoFit 

Pridobivanje vrednosti iz drugega delovnega lista

 dim newvalue

newvalue = ThisWorkbook.Worksheets ("delovni list"). Razpon ("F1"). vrednost

Vstavljanje stolpca v delovni list

 Dimna vrstica, stolpec

Celice (vrstica, stolpec) .EntireColumn.Select

Vstavi

Vstavite več stolpcev v delovni list

 Dim insertCnt

Dimna vrstica, stolpec

Za insertCnt = 1 v N

ThisWorkbook.Worksheets ("delovni list")

Celice (vrstica, stolpec) .EntireColumn.Select

Vstavi

Naslednji

Dodajanje imenovanega območja v določen list

 ThisWorkbook.Worksheets ("delovni list") Names.Add Name: = "Status", RefersToR1C1: = "= delovni list! C2"

Vstavite celotno vrstico v delovni list

 Dimna vrstica, stolpec

Celice (vrstica, stolpec) .EntireRow.Select

Vstavi

Kopirajte celotno vrstico za lepljenje

 ActiveSheet.Range ("A1")

Izbira. Kopiraj

Brisanje celotne vrstice

 ActiveSheet.Range ("A1")

Izbira

Izberite posamezni list

 ThisWorkbook.Worksheets ("delovni list") 

Primerjajte vrednosti razpona

 Dim firstrange

Dim Logictest

Logictest = "nekaj besed ali vrednosti"

Če (Območje (firstrange) .value = Logictest), potem

Tukaj je nekaj rutinskega

End If

Slika: © Microsoft.

Prejšnji Članek Naslednji Članek

Top Nasveti