Exceli lahtrite ja vahemiku funktsioonide mõistmine VBA -s

Exceli lahtrite ja vahemiku funktsioonide mõistmine VBA -s

Excel on võimas. Kui kasutate seda palju, teate ilmselt juba paljusid nippe valemite või automaatse vormindamise abil, kuid kasutades Rakud ja Vahemik funktsioone VBA -s, saate oma Exceli analüütika täiesti uuele tasemele tõsta.





VBA rakkude ja ulatuse funktsioonide kasutamisel on probleemiks see, et kõrgtasemel on enamikul inimestel raske mõista, kuidas need funktsioonid tegelikult töötavad. Nende kasutamine võib olla väga segane. Siit saate teada, kuidas saate neid kasutada viisil, mida te pole kunagi ette kujutanud.



Rakkude funktsioon

Funktsioonid Lahtrid ja vahemik võimaldavad teil seda öelda teie VBA skript täpselt selle kohta, kust oma töölehel soovite hankida, või asetage andmed. Peamine erinevus kahe lahtri vahel on see, millele nad viitavad.



Rakud viitavad tavaliselt ühele lahtrile korraga, samas Vahemik viitab korraga rakkude rühmale. Selle funktsiooni vorming on Lahtrid (rida, veerg) .

See viitab kogu lehe igale lahtrile. See on üks näide, kus funktsioon Lahtrid ei viita ühele lahtrile:



Worksheets('Sheet1').Cells

See viitab ülemise rea vasakule kolmandale lahtrile. Lahter C1:

Worksheets('Sheet1').Cells(3)

Järgmine kood viitab lahtrile D15:



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

Kui soovite, võite viidata lahtrile D15 ka lahtritega (15, „D”)-teil on lubatud kasutada veerutähte.

Lahtrile viitamisel, kasutades veeru ja lahtri numbrit, on palju paindlikkust, eriti skriptide puhul, mis seda võimaldavad silmus läbi suur hulk lahtreid (ja teeme nende kohta arvutusi) väga kiiresti. Allpool käsitleme seda üksikasjalikumalt.



Vahemiku funktsioon

Funktsioon Vahemik on paljuski palju võimsam kui lahtrite kasutamine, kuna see võimaldab korraga viidata kas ühele lahtrile või kindlale lahtrivahemikule. Te ei soovi funktsiooni Vahemik läbi vaadata, sest lahtrite viited ei ole numbrid (kui te ei sisalda funktsiooni Lahtrid selle sees).

Selle funktsiooni vorming on Vahemik (lahter 1, lahter 2) . Iga lahtrit saab tähistada tähe-numbriga.

Vaatame mõnda näidet.

miks mu ps4 kontroller ei tööta?

Siin viitab vahemiku funktsioon lahtrile A5:

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

Siin viitab vahemiku funktsioon kõigile lahtritele vahemikus A1 kuni E20:

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

Nagu eespool mainitud, ei pea te kasutama numbritähtede lahtrite määramist. Lehel asuva vahemiku tuvastamiseks võite tegelikult kasutada vahemiku funktsiooni sees kahte lahtri funktsiooni:


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

Ülaltoodud kood viitab samale vahemikule kui funktsioon Vahemik ('A1: E20'). Selle kasutamise väärtus on see, et see võimaldab teil kirjutada silmuste abil dünaamiliselt vahemikega töötavat koodi.

Nüüd, kui mõistate lahtrite ja vahemiku funktsioonide vormindamist, sukeldume sellesse, kuidas saate neid funktsioone oma VBA -koodis loominguliselt kasutada.

Andmete töötlemine rakkude funktsiooniga

Funktsioon Lahtrid on kõige kasulikum, kui teil on keeruline valem, mida soovite täita mitmes lahtrivahemikus. Need vahemikud võivad eksisteerida ka mitmel lehel.

Võtame lihtsa näite. Oletame, et juhite 11 -liikmelist müügimeeskonda ja soovite iga kuu vaadata nende tegevust.

Sul võib olla Leht1 mis jälgib nende müügiarvu ja müügimahtu.

Peal Leht2 kus saate jälgida nende ettevõtte klientide viimase 30 päeva tagasisidet.

Kui soovite boonust arvutada esimesel lehel, kasutades kahe lehe väärtusi, on selleks mitmeid viise. Võite kirjutada valemi esimesse lahtrisse, mis teostab arvutuse, kasutades kahe lehe andmeid, ja lohistage see alla. See töötab.

Selle alternatiiviks on VBA skripti loomine, mille käivitate lehe avamisel või käivitatakse lehel oleva käsunupu abil, et saaksite kontrollida selle arvutamist. Võite siiski kasutada VBA skripti, et hankida kõik müügiandmed välisest failist.

Miks mitte käivitada sel ajal sama skripti boonusveeru arvutused?

Rakud toimivad

Kui te pole kunagi varem Excelis VBA -d kirjutanud, peate lubama menüü Arendaja. Selleks minge aadressile Fail > Valikud . Kliki Kohandage lint . Lõpuks valige vasakult paanilt Arendaja, Lisama paremale paanile ja veenduge, et märkeruut oleks märgitud.

Nüüd, kui klõpsate Okei ja minge tagasi põhilehele, näete menüüvalikut Arendaja.

Võite kasutada Sisesta menüüst käsunupu sisestamiseks või lihtsalt klõpsake Vaata koodi kodeerimise alustamiseks.

Selles näites paneme skripti tööle iga kord, kui töövihik avatakse. Selleks klõpsake lihtsalt nuppu Vaata koodi arendaja menüüst ja kleepige järgmine uus funktsioon koodiaknasse.

Private Sub Workbook_Open()
End Sub

Teie koodiaken näeb välja umbes selline.

Nüüd olete valmis arvutuse tegemiseks koodi kirjutama. Ühe silmuse abil saate astuda läbi kõik 11 töötajat ja funktsiooniga Lahtrid tõmmata sisse kolm arvutamiseks vajalikku muutujat.

Pidage meeles, et funktsioonil Lahtrid on iga üksiku lahtri tuvastamiseks parameetrid rida ja veerg. Teeme rea „x”, iga veeru andmete taotlemiseks kasutage numbrit. Ridade arv on töötajate arv, seega on see vahemikus 1 kuni 11. Veeru identifikaator on 2 müügiloenduse jaoks, 3 müügimahu jaoks ja 2 lehe 2 kohta tagasiside skoori jaoks.

Lõplik arvutus kasutab järgmisi protsente, et lisada kuni 100 protsenti kogu boonuskoorist. Selle aluseks on ideaalne müügiarv 50, müügimaht 50 000 dollarit ja tagasiside skoor 10.

  • (Müükide arv/50) x 0,4
  • (Müügimaht/50 000) x 0,5
  • (Tagasiside skoor/10) x 0,1

See lihtne lähenemisviis annab müügitöötajatele kaalutud lisatasu. Kui arvestada 50, maht 50 000 dollarit ja skoor 10 ---, saavad nad kogu kuu maksimaalse boonuse. Kuid kõik, mis pole täiuslik mis tahes teguril, vähendab boonust. Kõik parem kui ideaalne suurendab boonust.

Nüüd vaatame, kuidas kogu seda loogikat saab lihtsa ja lühikese VBA -skripti abil välja tõmmata:

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

Selle skripti väljund näeb välja selline.

mida saab teha vaarika pi

Kui soovite, et veerus Boonus kuvataks tegelik dollari boonus, mitte protsent, võite selle korrutada maksimaalse boonussummaga. Veelgi parem, asetage see summa teise lehe lahtrisse ja viitage sellele oma koodis. See hõlbustaks hiljem väärtuse muutmist ilma koodi muutmata.

Lahtrite funktsiooni ilu seisneb selles, et saate andmete loomiseks luua üsna loomingulise loogika palju rakke erinevatel lehtedel ja esitage mõned päris keerulised arvutused nendega.

Funktsiooni Lahtrid abil saate lahtritel teha igasuguseid toiminguid-näiteks lahtrite tühjendamine, fondi vormingu muutmine ja palju muud.

Et uurida kõike, mida saate edasi teha, vaadake Microsofti MSDN -i leht Cells objekti jaoks.

Lahtrite vormindamine vahemiku funktsiooniga

Rakkude funktsioon sobib ideaalselt paljude lahtrite ükshaaval läbimiseks. Aga kui soovite midagi rakendada tervele lahtrivahemikule korraga, on funktsioon Vahemik palju tõhusam.

Üks kasutamisjuhtum võib olla lahtrite vahemiku vormindamine skripti abil, kui teatud tingimused on täidetud.

Oletame näiteks, et kui kõigi müügitöötajate kogu müügimahu summa ületab kokku 400 000 dollarit, siis soovite boonusveeru kõik lahtrid roheliselt esile tõsta, mis tähendab, et meeskond on teeninud täiendava meeskonna boonuse.

Vaatame, kuidas saate seda teha IF avaldus .

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

Kui see käivitub, täidetakse lahtri meeskonna eesmärgi kohal kõik rohelised lahtrid.

See on vaid üks lihtne näide paljudest toimingutest, mida saate teha vahemikfunktsiooni abil lahtrirühmadega. Muud asjad, mida saate teha, on järgmised:

  • Rakenda rühma ümber ülevaade
  • Kontrollige teksti õigekirja lahtrite vahemikus
  • Lahtrite kustutamine, kopeerimine või lõikamine
  • Otsige vahemikust otsimismeetodiga
  • Palju rohkem

Lugege kindlasti Microsofti MSDN -i leht et objekt Range näeks kõiki võimalusi.

Viige Excel järgmisele tasemele

Nüüd, kui mõistate lahtrite ja vahemiku funktsioonide erinevusi, on aeg viia oma VBA skriptimine järgmisele tasemele. Danni artikkel Exceli funktsioonide Loendamine ja lisamine kasutamise kohta võimaldab teil luua veelgi täiustatud skripte, mis võivad väga kiiresti koguda väärtusi kõigi teie andmekogumite vahel.

Ja kui alles alustate VBA kasutamist Excelis, ärge unustage, et meil on fantastiline Exceli VBA sissejuhatav juhend ka teile.

Jaga Jaga Piiksuma E -post Canon vs Nikon: milline kaamera kaubamärk on parem?

Canon ja Nikon on kaameratööstuse kaks suurimat nime. Kuid milline kaubamärk pakub paremat kaamerate ja objektiivide valikut?

Loe edasi Seotud teemad
  • Tootlikkus
  • Programmeerimine
  • Visuaalne põhiprogrammeerimine
  • Microsoft Excel
Autori kohta Ryan Dube(Avaldatud 942 artiklit)

Ryanil on elektrotehnika bakalaureusekraad. Ta on töötanud 13 aastat automaatikainsenerina, 5 aastat IT -alal ja on nüüd rakenduste insener. MakeUseOfi endine tegevtoimetaja on rääkinud üleriigilistel andmete visualiseerimise konverentsidel ning esinenud üleriigilises televisioonis ja raadios.

Rohkem Ryan Dube'ilt

Telli meie uudiskiri

Liituge meie uudiskirjaga, et saada tehnilisi näpunäiteid, ülevaateid, tasuta e -raamatuid ja eksklusiivseid pakkumisi!

Tellimiseks klõpsake siin