Forstå Excel Cells vs. Range Funksjoner i VBA

Forstå Excel Cells vs. Range Funksjoner i VBA

Excel er kraftig. Hvis du bruker det mye, vet du sannsynligvis allerede mange triks ved å bruke formler eller autoformatering, men du bruker det Celler og Område funksjoner i VBA, kan du øke Excel -analysene dine til et helt nytt nivå.





Problemet med å bruke Cells and Range -funksjonene i VBA er at på de avanserte nivåene har de fleste problemer med å forstå hvordan disse funksjonene faktisk fungerer. Å bruke dem kan bli veldig forvirrende. Slik kan du bruke dem på måter du sannsynligvis aldri hadde forestilt deg.



Celler -funksjonen

Cells and Range -funksjonene lar deg fortelle det ditt VBA -skript nøyaktig hvor i regnearket du vil skaffe, eller plasser data. Hovedforskjellen mellom de to cellene er det de refererer til.





Celler refererer vanligvis til en enkelt celle om gangen, mens Område refererer til en gruppe celler på en gang. Formatet for denne funksjonen er Celler (rad, kolonne) .

Dette refererer til hver enkelt celle i hele arket. Det er det ene eksempelet der Cells -funksjonen ikke refererer til en enkelt celle:



Worksheets('Sheet1').Cells

Dette refererer til den tredje cellen fra venstre, i den øverste raden. Celle C1:

Worksheets('Sheet1').Cells(3)

Følgende kode refererer til celle D15:



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

Hvis du ville, kan du også referere til celle D15 med 'Cells (15,' D ')' --- du har lov til å bruke kolonnen bokstaven.

Det er mye fleksibilitet i å kunne referere til en celle ved hjelp av et tall for kolonne og celle, spesielt med skript som kan sløyfe gjennom et stort antall celler (og utfør beregninger på dem) veldig raskt. Vi kommer til det mer detaljert nedenfor.



Range -funksjonen

På mange måter er Range -funksjonen langt kraftigere enn å bruke celler, fordi den lar deg referere til enten en enkelt celle eller et bestemt celleområde, alt på en gang. Du kommer ikke til å ønske å gå gjennom en Range -funksjon, fordi referansene for celler ikke er tall (med mindre du legger inn Cells -funksjonen inne i den).

Formatet for denne funksjonen er Rekkevidde (celle #1, celle #2) . Hver celle kan angis med et bokstavnummer.

La oss se på noen eksempler.

hvordan du øker dedikert videoram på bærbar PC

Her refererer områdefunksjonen til celle A5:

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

Her refererer områdefunksjonen til alle cellene mellom A1 til E20:

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

Som nevnt ovenfor trenger du ikke bruke celleoppgaver med tallbokstaver. Du kan faktisk bruke to cellefunksjoner inne i en områdefunksjon for å identifisere et område på arket, slik:


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

Koden ovenfor refererer til det samme området som funksjonen Range ('A1: E20') gjør. Verdien i å bruke den er at den lar deg skrive kode som dynamisk fungerer med områder med sløyfer.

Nå som du forstår hvordan du formaterer Cells and Range -funksjonene, la oss dykke ned i hvordan du kan bruke disse funksjonene kreativt i VBA -koden din.

Behandle data med cellerfunksjon

Celle -funksjonen er mest nyttig når du har en kompleks formel som du vil utføre på tvers av flere celleområder. Disse områdene kan også eksistere på tvers av flere ark.

La oss ta et enkelt eksempel. La oss si at du leder et salgsteam på 11 personer, og hver måned vil du se på resultatene deres.

Du har kanskje Ark 1 som sporer salgstallet og salgsvolumet.

På Ark 2 er der du sporer tilbakemeldingene deres for de siste 30 dagene fra selskapets kunder.

Hvis du vil beregne bonusen på det første arket ved hjelp av verdier fra de to arkene, er det flere måter å gjøre dette på. Du kan skrive en formel i den første cellen som utfører beregningen ved hjelp av data på tvers av de to arkene og dra den ned. Det vil fungere.

Et alternativ til dette er å lage VBA -skript som du enten utløser for å kjøre når du åpner arket, eller utløses av en kommandoknapp på arket, slik at du kan kontrollere når det beregnes. Du kan uansett bruke et VBA -skript for å hente inn alle salgsdataene fra en ekstern fil.

Så hvorfor ikke bare utløse beregningene for bonuskolonnen i samme manus på den tiden?

Cellenes funksjon i bruk

Hvis du aldri har skrevet VBA i Excel før, må du aktivere utviklermenyelementet. For å gjøre dette, gå til Fil > Alternativer . Klikk på Tilpass båndet . Til slutt velger du Utvikler fra venstre rute, Legge til den til høyre rute, og kontroller at avmerkingsboksen er merket.

Nå, når du klikker OK og gå tilbake til hovedarket, ser du alternativet Utvikler -menyen.

Du kan bruke Sett inn menyen for å sette inn en kommandoknapp, eller bare klikk Se kode å begynne å kode.

I dette eksemplet får vi skriptet til å kjøre hver gang arbeidsboken åpnes. For å gjøre dette, klikker du bare Se kode fra utviklermenyen, og lim inn følgende nye funksjon i kodevinduet.

Private Sub Workbook_Open()
End Sub

Kodevinduet vil se slik ut.

Nå er du klar til å skrive koden for å håndtere beregningen. Ved å bruke en enkelt sløyfe kan du gå gjennom alle 11 ansatte, og trekke inn de tre variablene som trengs for beregningen med Cells -funksjonen.

Husk at Cells -funksjonen har rad og kolonne som parametere for å identifisere hver enkelt celle. Vi lager 'x' til raden, bruker et tall for å be om hver kolonnenes data. Antall rader er antall ansatte, så dette vil være fra 1 til 11. Kolonneidentifikatoren vil være 2 for salgstall, 3 for salgsvolum og 2 fra ark 2 for tilbakemeldingspoeng.

Den endelige beregningen bruker følgende prosenter for å legge opp til 100 prosent av den totale bonuspoengsummen. Den er basert på at et ideelt salgstall er 50, salgsvolum som $ 50 000 og en tilbakemelding på 10.

  • (Salgstelling/50) x 0,4
  • (Salgsvolum/50 000) x 0,5
  • (Tilbakemeldingspoeng/10) x 0,1

Denne enkle tilnærmingen gir salgsmedarbeidere en vektet bonus. For en telling på 50, volum på $ 50 000 og en poengsum på 10 --- får de hele maksimal bonus for måneden. Men alt som er perfekt på en hvilken som helst faktor reduserer bonusen. Noe bedre enn ideelt øker bonusen.

La oss nå se hvordan all den logikken kan trekkes ut i et veldig enkelt, kort VBA -skript:

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

Slik vil utgangen av dette skriptet se ut.

hvor mange følgere på tiktok som skal gå live

Hvis du ønsket at bonuskolonnen skulle vise den faktiske dollarbonusen i stedet for prosentandelen, kan du multiplisere den med det maksimale bonusbeløpet. Enda bedre, legg det beløpet i en celle på et annet ark, og referer det til koden din. Dette vil gjøre det lettere å endre verdien senere uten å måtte redigere koden.

Det fine med Cells -funksjonen er at du kan bygge en ganske kreativ logikk å hente inn data fra mange celler på tvers av mange forskjellige ark, og utføre noen ganske komplekse beregninger med dem.

Du kan utføre alle slags handlinger på celler ved hjelp av Cells-funksjonen --- ting som å slette cellene, endre skriftformatering og mye mer.

For å utforske alt du kan gjøre ytterligere, sjekk ut Microsoft MSDN -side for Cells -objektet.

Formatering av celler med rekkeviddefunksjon

For sløyfe gjennom mange celler en om gangen, er Cells -funksjonen perfekt. Men hvis du vil bruke noe på et helt celleområde på en gang, er Range -funksjonen langt mer effektiv.

En brukstilfelle for dette kan være å formatere en rekke celler ved hjelp av script, hvis visse betingelser er oppfylt.

La oss for eksempel si at hvis summen av alt salgsvolum på tvers av alle salgsmedarbeidere totalt overstiger $ 400 000, vil du markere alle cellene i bonuskolonnen i grønt for å indikere at teamet har tjent en ekstra lagbonus.

La oss ta en titt på hvordan du kan gjøre det med en IF -uttalelse .

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

Når denne kjører, hvis cellen er over lagmålet, vil alle cellene i området bli fylt med grønt.

Dette er bare et enkelt eksempel på de mange handlingene du kan utføre på grupper av celler ved hjelp av Range -funksjonen. Andre ting du kan gjøre inkluderer:

  • Bruk en disposisjon rundt gruppen
  • Kontroller skrivemåten i en rekke celler
  • Fjern, kopier eller klipp celler
  • Søk gjennom et område med 'Finn' -metoden
  • Mye mer

Sørg for å lese Microsoft MSDN -side for at Range -objektet skal se alle mulighetene.

Ta Excel til neste nivå

Nå som du forstår forskjellene mellom Cells og Range -funksjonene, er det på tide å ta VBA -skriptingen til neste nivå. Danns artikkel om bruk av Counting and Adding -funksjoner i Excel lar deg bygge enda mer avanserte skript som kan akkumulere verdier på tvers av alle datasettene dine veldig raskt.

Og hvis du bare har begynt med VBA i Excel, ikke glem at vi har en fantastisk introduksjonsguide til Excel VBA for deg også.

Dele Dele kvitring E -post Canon vs Nikon: Hvilket kameramerk er bedre?

Canon og Nikon er de to største navnene i kameraindustrien. Men hvilket merke tilbyr den bedre serien med kameraer og objektiver?

Les neste Relaterte temaer
  • Produktivitet
  • Programmering
  • Visual Basic programmering
  • Microsoft Excel
Om forfatteren Ryan Dube(942 artikler publisert)

Ryan har en bachelorgrad i elektroteknikk. Han har jobbet 13 år innen automasjonsteknikk, 5 år innen IT, og er nå en Apps Engineer. En tidligere administrerende redaktør for MakeUseOf, han har talt på nasjonale konferanser om datavisualisering og har blitt omtalt på nasjonal TV og radio.

Mer fra Ryan Dube

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere