Forståelse af Excel -celler versus rækkeviddefunktioner i VBA

Forståelse af Excel -celler versus rækkeviddefunktioner i VBA

Excel er kraftfuld. Hvis du bruger det meget, kender du sikkert allerede mange tricks ved hjælp af formler eller autoformatering, men gør brug af Celler og Rækkevidde funktioner i VBA, kan du øge din Excel -analyse til et helt nyt niveau.





Problemet med at bruge Cells and Range -funktionerne i VBA er, at på avancerede niveauer har de fleste mennesker svært ved at forstå, hvordan disse funktioner rent faktisk fungerer. Brug af dem kan blive meget forvirrende. Sådan kan du gøre brug af dem på måder, du sandsynligvis aldrig havde forestillet dig.



Celler -funktionen

Celler og rækkevidde -funktioner lader dig fortælle dit VBA -script præcis, hvor på dit regneark du vil indhente eller placere data. Hovedforskellen mellem de to celler er, hvad de refererer til.





Celler refererer normalt til en enkelt celle ad gangen, mens Rækkevidde refererer til en gruppe celler på én gang. Formatet for denne funktion er Celler (række, kolonne) .

Dette refererer til hver enkelt celle i hele arket. Det er det ene eksempel, hvor Cells -funktionen ikke refererer til en enkelt celle:



Worksheets('Sheet1').Cells

Dette refererer til den tredje celle fra venstre i øverste række. Celle C1:

Worksheets('Sheet1').Cells(3)

Følgende kode refererer til celle D15:



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

Hvis du ville, kunne du også henvise til celle D15 med 'Celler (15,' D ')' --- du har lov til at bruge kolonnen bogstav.

Der er stor fleksibilitet i at kunne henvise til en celle ved hjælp af et tal til kolonne og celle, især med scripts der kan sløjfe igennem et stort antal celler (og udfør beregninger på dem) meget hurtigt. Det kommer vi nærmere til nedenfor.



Områdefunktionen

På mange måder er Range -funktionen langt mere kraftfuld end at bruge celler, fordi den lader dig henvise til enten en enkelt celle eller et specifikt cellecelle på én gang. Du vil ikke gå gennem en Range -funktion, fordi referencer til celler ikke er tal (medmindre du integrerer Cells -funktionen inde i den).

Formatet for denne funktion er Område (celle #1, celle #2) . Hver celle kan betegnes med et bogstavnummer.

Lad os se på et par eksempler.

hvordan man finder en bygnings historie

Her refererer rækkefunktionen til celle A5:

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

Her refererer rækkefunktionen til alle celler mellem A1 til E20:

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

Som nævnt ovenfor behøver du ikke bruge celleopgaver til nummerbogstaver. Du kan faktisk bruge to cellefunktioner inde i en områdefunktion til at identificere et område på arket, sådan her:


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

Koden ovenfor refererer til det samme område som funktionen Range ('A1: E20') gør. Værdien ved at bruge det er, at det ville give dig mulighed for at skrive kode, der dynamisk fungerer med intervaller ved hjælp af sløjfer.

Nu hvor du forstår, hvordan du formaterer Celler og Range -funktionerne, lad os dykke ned i, hvordan du kreativt kan bruge disse funktioner i din VBA -kode.

Behandling af data med cellerfunktion

Celler -funktionen er mest nyttig, når du har en kompleks formel, som du vil udføre på tværs af flere celleceller. Disse intervaller kan også eksistere på tværs af flere ark.

Lad os tage et enkelt eksempel. Lad os sige, at du leder et salgsteam på 11 personer, og hver måned vil du se på deres præstationer.

Det har du måske Ark 1 der sporer deres salgstal og deres salgsmængde.

På Ark 2 er det, hvor du sporer deres feedback -rating for de sidste 30 dage fra din virksomheds kunder.

Hvis du vil beregne bonusen på det første ark ved hjælp af værdier fra de to ark, er der flere måder at gøre dette på. Du kan skrive en formel i den første celle, der udfører beregningen ved hjælp af data på tværs af de to ark og trække den ned. Det virker.

Et alternativ til dette er at oprette VBA -script, som du enten udløser til at køre, når du åbner arket eller udløses af en kommandoknap på arket, så du kan styre, hvornår det beregner. Du kan måske alligevel bruge et VBA -script til at hente alle salgsdata fra en ekstern fil.

Så hvorfor ikke bare udløse beregningerne for bonuskolonnen i det samme script på det tidspunkt?

Cellerne fungerer i aktion

Hvis du aldrig har skrevet VBA i Excel før, skal du aktivere menupunktet Udvikler. For at gøre dette skal du gå til Fil > Muligheder . Klik på Tilpas bånd . Til sidst skal du vælge Udvikler i venstre rude, Tilføje det til højre rude, og sørg for, at afkrydsningsfeltet er markeret.

Nu, når du klikker Okay og gå tilbage til hovedarket, får du vist menuen Udvikler.

Du kan bruge Indsæt menu for at indsætte en kommandoknap, eller bare klik Se kode at starte kodning.

I dette eksempel får vi scriptet til at køre hver gang projektmappen åbnes. For at gøre dette skal du bare klikke Se kode fra udviklermenuen, og indsæt følgende nye funktion i kodevinduet.

Private Sub Workbook_Open()
End Sub

Dit kodevindue ser sådan ud.

Nu er du klar til at skrive koden til at håndtere beregningen. Ved hjælp af en enkelt sløjfe kan du gå igennem alle 11 medarbejdere, og med funktionen Celler trække i de tre variabler, der er nødvendige til beregningen.

Husk, at cellerfunktionen har række og kolonne som parametre til at identificere hver enkelt celle. Vi laver 'x' til rækken, bruger et tal til at anmode om hver kolonnenes data. Antallet af rækker er antallet af medarbejdere, så dette vil være fra 1 til 11. Kolonne -id'et vil være 2 for salgstælling, 3 for salgsmængde og 2 fra ark 2 for feedback -score.

Den endelige beregning bruger følgende procenter til at tilføje op til 100 procent af den samlede bonusscore. Det er baseret på, at et ideelt salgstal er 50, salgsmængde som $ 50.000 og en feedback score på 10.

  • (Salgstal/50) x 0,4
  • (Salgsmængde/50.000) x 0,5
  • (Feedback score/10) x 0,1

Denne enkle tilgang giver salgsmedarbejdere en vægtet bonus. Ved optælling af 50, volumen på $ 50.000 og en score på 10 --- får de hele den maksimale bonus for måneden. Men alt under perfekt på enhver faktor reducerer bonusen. Alt bedre end ideelt øger bonussen.

Lad os nu se, hvordan al den logik kan trækkes ud i et meget enkelt, kort VBA -script:

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

Sådan ser output fra dette script ud.

hvad kan du gøre med en hindbær pi

Hvis du ville have bonuskolonnen til at vise den faktiske dollarbonus frem for procentdelen, kan du gange den med det maksimale bonusbeløb. Endnu bedre, placer beløbet i en celle på et andet ark, og henvis det til din kode. Dette ville gøre det lettere at ændre værdien senere uden at skulle redigere din kode.

Skønheden ved Cells -funktionen er, at du kan bygge en temmelig kreativ logik at hente data fra mange celler på tværs af mange forskellige ark, og udfør nogle ret komplekse beregninger med dem.

Du kan udføre alle slags handlinger på celler ved hjælp af Cells-funktionen --- ting som at rydde cellerne, ændre skrifttypeformatering og meget mere.

For at udforske alt, hvad du kan gøre yderligere, skal du tjekke Microsoft MSDN -side for Cells -objektet.

Formatering af celler med rækkefunktion

For at sløjfe gennem mange celler én ad gangen er Cells -funktionen perfekt. Men hvis du vil anvende noget på en hel række celler på én gang, er Range -funktionen langt mere effektiv.

Et eksempel på dette kan være at formatere en række celler ved hjælp af script, hvis visse betingelser er opfyldt.

Lad os f.eks. Sige, at hvis salget af alle salgsmængder på tværs af alle salgsmedarbejdere i alt overstiger $ 400.000, vil du markere alle celler i bonuskolonnen i grønt for at angive, at teamet har optjent en ekstra teambonus.

Lad os se på, hvordan du kan gøre det med en IF -erklæring .

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 kører, hvis cellen er over teammålet, udfyldes alle celler i området med grønt.

Dette er blot et enkelt eksempel på de mange handlinger, du kan udføre på grupper af celler ved hjælp af funktionen Område. Andre ting du kan gøre er:

  • Anvend en oversigt omkring gruppen
  • Kontroller stavningen af ​​tekst inde i en række celler
  • Ryd, kopier eller klip celler
  • Søg gennem et område med metoden 'Find'
  • Meget mere

Sørg for at læse Microsoft MSDN -side for Range -objektet at se alle mulighederne.

Tag Excel til det næste niveau

Nu hvor du forstår forskellene mellem cellerne og rækkefunktionerne, er det tid til at tage din VBA -scripting til det næste niveau. Danns artikel om brug af tælle- og tilføjelsesfunktioner i Excel giver dig mulighed for at opbygge endnu mere avancerede scripts, der kan akkumulere værdier på tværs af alle dine datasæt meget hurtigt.

Og hvis du lige er begyndt med VBA i Excel, så glem ikke, at vi har en fantastisk introduktionsguide til Excel VBA også for dig.

Del Del Tweet E -mail Canon vs Nikon: Hvilket kameramærke er bedre?

Canon og Nikon er de to største navne i kameraindustrien. Men hvilket mærke tilbyder den bedre serie af kameraer og objektiver?

Læs Næste Relaterede emner
  • Produktivitet
  • Programmering
  • Visual Basic programmering
  • Microsoft Excel
Om forfatteren Ryan Dube(942 artikler udgivet)

Ryan har en bachelorgrad i elektroteknik. Han har arbejdet 13 år inden for automationsteknik, 5 år inden for IT, og er nu Apps Engineer. Tidligere administrerende redaktør for MakeUseOf har han talt på nationale konferencer om datavisualisering og er blevet vist på nationalt tv og radio.

Mere fra Ryan Dube

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere