Razumevanje Excelovih celic proti funkcijam obsega v VBA

Razumevanje Excelovih celic proti funkcijam obsega v VBA

Excel je močan. Če ga veliko uporabljate, verjetno že poznate veliko trikov z uporabo formul ali samodejnega oblikovanja, vendar uporabite Celice in Domet funkcij v VBA, lahko izboljšate svojo analitiko v Excelu na povsem novo raven.





Težava pri uporabi funkcij celic in obsega v VBA je, da večina ljudi na naprednih ravneh težko razume, kako te funkcije dejansko delujejo. Njihova uporaba je lahko zelo zmedena. Evo, kako jih lahko uporabite na načine, ki si jih verjetno niste predstavljali.



Funkcija celic

Funkcije celic in obsega vam povedo vaš skript VBA točno kje na svojem delovnem listu želite pridobiti ali postaviti podatke. Glavna razlika med obema celicama je tisto, na kar se sklicujejo.





Celice se običajno sklicujejo na eno celico hkrati Domet se nanaša na skupino celic hkrati. Oblika te funkcije je Celice (vrstica, stolpec) .

To se nanaša na vsako posamezno celico na celotnem listu. To je en primer, kjer se funkcija Celice ne sklicuje na eno celico:



Worksheets('Sheet1').Cells

To se nanaša na tretjo celico na levi strani zgornje vrstice. Celica C1:

Worksheets('Sheet1').Cells(3)

Naslednja koda se sklicuje na celico D15:



Worksheets('Sheet1').Cells(15,4)

Če želite, se lahko sklicujete tudi na celico D15 z 'celice (15,' D ')' --- lahko uporabite črko stolpca.

Sposobnost sklicevanja na celico s številko za stolpec in celico je zelo prilagodljiva, zlasti s skripti, ki to lahko storijo zanka veliko število celic (in na njih opravite izračune) zelo hitro. To bomo podrobneje preučili spodaj.



Funkcija obsega

V mnogih pogledih je funkcija Range veliko močnejša od uporabe celic, saj omogoča sklicevanje na eno celico ali na določen obseg celic hkrati. Funkcije Range ne boste želeli prečkati, ker sklice na celice niso številke (razen če vanj vstavite funkcijo Celice).

Oblika te funkcije je Obseg (celica #1, celica #2) . Vsako celico lahko označimo s črko.

Poglejmo si nekaj primerov.

gledajte brezplačne filme brez prenosov ali prijav

Tukaj se funkcija obsega nanaša na celico A5:

Worksheets('Sheet1').Range('A5')

Tukaj se funkcija obsega nanaša na vse celice med A1 in E20:

Worksheets('Sheet1').Range('A1:E20')

Kot je navedeno zgoraj, vam ni treba uporabljati dodelitev celic s številskimi črkami. Za identifikacijo obsega na listu bi lahko dejansko uporabili dve funkciji celic, kot je ta:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

Zgornja koda se sklicuje na enako območje kot funkcija Range ('A1: E20'). Vrednost pri uporabi je, da vam omogoča pisanje kode, ki dinamično deluje z obsegi z uporabo zank.

Zdaj, ko razumete, kako formatirate funkcije Celice in obseg, se poglejmo, kako lahko te funkcije kreativno uporabite v kodi VBA.

Obdelava podatkov s funkcijo celic

Funkcija Celice je najbolj uporabna, če imate kompleksno formulo, ki jo želite izvesti v več razponih celic. Ti obsegi lahko obstajajo tudi na več listih.

Vzemimo preprost primer. Recimo, da vodite prodajno skupino 11 ljudi in vsak mesec si želite ogledati njihovo uspešnost.

Morda ste List1 ki spremlja njihovo prodajo in obseg prodaje.

Vklopljeno List 2 kjer spremljate njihovo oceno strank vašega podjetja v zadnjih 30 dneh.

Če želite izračunati bonus na prvem listu z uporabo vrednosti iz dveh listov, lahko to storite na več načinov. V prvo celico, ki izvede izračun z uporabo podatkov na dveh listih, lahko napišete formulo in jo povlečete navzdol. To bo delovalo.

Druga možnost je ustvarjanje skripta VBA, ki ga sprožite, ko odprete list, ali pa ga sproži ukazni gumb na listu, tako da lahko nadzirate, kdaj izračuna. Skript VBA lahko tako ali tako povlečete vse podatke o prodaji iz zunanje datoteke.

Zakaj torej ne bi takrat sprožili izračunov stolpca bonus v istem skriptu?

Funkcija celic v akciji

Če še nikoli niste pisali VBA v Excelu, morate omogočiti točko menija Razvijalec. Če želite to narediti, pojdite na mapa > Opcije . Kliknite na Prilagodite trak . Na koncu v levem podoknu izberite Razvijalec, Dodaj v desno podokno in preverite, ali je potrditveno polje izbrano.

Zdaj, ko kliknete v redu in se vrnite na glavni list, videli boste možnost menija Razvijalec.

Uporabite lahko Vstavi meni za vstavljanje ukaznega gumba ali pa samo kliknite Poglej kodo za začetek kodiranja.

V tem primeru bomo skript zagnali vsakič, ko odprete delovni zvezek. Če želite to narediti, samo kliknite Poglej kodo iz menija za razvijalce in v okno za kodo prilepite naslednjo novo funkcijo.

Private Sub Workbook_Open()
End Sub

Okno vaše kode bo videti nekako tako.

Zdaj ste pripravljeni napisati kodo za obravnavo izračuna. Z eno samo zanko lahko stopite skozi vseh 11 zaposlenih in s funkcijo Celice potegnete tri spremenljivke, potrebne za izračun.

Ne pozabite, da ima funkcija Celice vrstico in stolpec kot parametre za identifikacijo vsake posamezne celice. Vrstico bomo označili z 'x', s številko pa bomo zahtevali podatke vsakega stolpca. Število vrstic je število zaposlenih, zato bo to od 1 do 11. Identifikator stolpca bo 2 za število prodaje, 3 za obseg prodaje in 2 iz lista 2 za oceno povratnih informacij.

Končni izračun uporabi naslednje odstotke, da sešteje do 100 odstotkov skupne bonus točke. Temelji na idealnem številu prodaje 50, obsegu prodaje kot 50.000 USD in oceni povratnih informacij 10.

  • (Število prodaje/50) x 0,4
  • (Obseg prodaje/50.000) x 0,5
  • (Ocena povratnih informacij/10) x 0,1

Ta preprost pristop daje prodajnim uslužbencem tehtano nagrado. Za štetje 50, obseg 50.000 USD in oceno 10 --- dobijo celoten najvišji bonus za mesec. Vendar pa vse, kar je pod določenim faktorjem, zmanjša bonus. Vse, kar je boljše od idealnega, poveča bonus.

Zdaj pa poglejmo, kako lahko vso preprosto logiko izvlečemo v zelo preprostem, kratkem skriptu VBA:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

Tako bo videti rezultat tega skripta.

kaj lahko narediš z malinovim pi

Če želite, da stolpec Bonus prikaže dejanski dolar dolar in ne odstotek, ga lahko pomnožite z najvišjim zneskom bonusa. Še bolje, vnesite to količino v celico na drugem listu in se sklicujte nanjo v svoji kodi. To bi olajšalo poznejšo spremembo vrednosti, ne da bi morali urejati kodo.

Lepota funkcije celic je v tem, da lahko zgradite nekaj precej ustvarjalne logike, iz katere potegnete podatke veliko celic na številnih različnih listih in nekatere izvedite precej zapleteni izračuni z njimi.

S celicami lahko izvajate vse vrste dejanj s celicami-na primer počistite celice, spremenite obliko pisave in še veliko več.

Če želite raziskati vse, kar lahko še storite, si oglejte Microsoftova stran MSDN za predmet Celice.

Oblikovanje celic s funkcijo obsega

Funkcija Celice je popolna za kroženje po številnih celicah naenkrat. Če pa želite nekaj naenkrat uporabiti za celoten obseg celic, je funkcija Range veliko učinkovitejša.

Eden od primerov uporabe tega je lahko oblikovanje vrste celic s skriptom, če so izpolnjeni nekateri pogoji.

Recimo, na primer, če skupni obseg celotne prodaje vseh prodajnih uslužbencev skupaj preseže 400.000 USD, želite označiti vse celice v stolpcu z bonusi zeleno, kar pomeni, da je ekipa zaslužila dodaten bonus ekipe.

Poglejmo, kako lahko to storite stavek IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

Ko se to izvede, če celica presega cilj ekipe, bodo vse celice v obsegu napolnjene z zeleno.

To je le en preprost primer številnih dejanj, ki jih lahko izvedete na skupinah celic s funkcijo Range. Druge stvari, ki jih lahko storite, vključujejo:

  • Uporabite oris okoli skupine
  • Preverite črkovanje besedila v obsegu celic
  • Počistite, kopirajte ali izrežite celice
  • Iščite po obsegu z metodo 'Najdi'
  • Veliko več

Ne pozabite prebrati Microsoftova stran MSDN da si objekt Range ogleda vse možnosti.

Pojdite Excel na naslednjo stopnjo

Zdaj, ko razumete razlike med funkcijami Celice in Razpon, je čas, da svoje skriptno VBA premaknete na naslednjo stopnjo. Dannov članek o uporabi funkcij štetja in dodajanja v Excelu vam bo omogočil izdelavo še naprednejših skriptov, ki lahko zelo hitro kopičijo vrednosti v vseh vaših naborih podatkov.

In če šele začenjate z VBA v Excelu, ne pozabite, da imamo fantastično uvodni vodnik za Excel VBA tudi zate.

Deliti Deliti Cvrkutati E-naslov Canon proti Nikon: katera znamka fotoaparata je boljša?

Canon in Nikon sta dve največji imeni v industriji fotoaparatov. Toda katera blagovna znamka ponuja boljšo ponudbo fotoaparatov in objektivov?

Preberite Naprej Sorodne teme
  • Produktivnost
  • Programiranje
  • Visual Basic programiranje
  • Microsoft Excel
O avtorju Ryan Dube(942 objavljenih člankov)

Ryan je diplomiral iz elektrotehnike. 13 let je delal na področju avtomatizacije, 5 let na področju IT, zdaj pa je inženir aplikacij. Nekdanji glavni urednik MakeUseOfa je govoril na nacionalnih konferencah o vizualizaciji podatkov in je bil predstavljen na nacionalni televiziji in radiu.

Več od Ryana Dubeja

Naročite se na naše novice

Pridružite se našemu glasilu za tehnične nasvete, ocene, brezplačne e -knjige in ekskluzivne ponudbe!

Kliknite tukaj, če se želite naročiti