Forståelse af Excel-celler vs. rækkefunktioner i VBA

Excel er kraftfuldt. Hvis du bruger det meget, kender du sandsynligvis allerede mange trick 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 funktionerne Celler og rækkevidde i VBA er, at på de avancerede niveauer har de fleste svært ved at forstå, hvordan disse funktioner faktisk fungerer. Brug af dem kan blive meget forvirrende. Her er, hvordan du kan bruge dem på måder, du sandsynligvis aldrig har forestillet dig.

Cellefunktionen

Funktioner Celler og rækkevidde giver dig mulighed for at fortælle dit VBA-script 4 fejl, du kan undgå, når du programmerer Excel-makroer med VBA 4-fejl, du kan undgå, når du programmerer Excel-makroer med VBA Enkel kode og makroer er nøglerne til Microsoft Excel-supermagter. Selv ikke-programmerere kan let tilføje imponerende funktionalitet til deres regneark med Virtual Basics for Applications (VBA). Bare undgå disse programmering af begyndelsesfejl! nøjagtigt hvor på dit regneark du vil hente eller placere data. Den største forskel mellem de to celler er, hvad de refererer til.

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

Dette henviser til hver enkelt celle i hele arket. Det er det ene eksempel, hvor cellernes funktion ikke refererer til en enkelt celle:

Regneark ( "Ark1"). Cells

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

Regneark ( "Ark1"). Celler (3)

Følgende kode refererer til celle D15:

Regneark ( "Ark1"). Celler (15,4)

Hvis du ville, kunne du også referere celle D15 med “Celler (15,”D”)”-du har tilladelse til at bruge kolonnebrevet.

Der er en masse fleksibilitet i at kunne henvise til en celle ved hjælp af et tal til kolonne og celle, især med scripts der kan loop igennem et stort antal celler (og udfør beregninger på dem) meget hurtigt. Vi kommer til det mere detaljeret nedenfor.

Range-funktionen

På mange måder er Range-funktionen langt mere kraftfuld end at bruge celler, fordi den giver dig mulighed for enten at referere til en enkelt celle eller et specifikt område af celler på én gang. Du vil ikke gå igennem en rækkefunktion, fordi referencerne for celler ikke er tal (medmindre du integrerer cellefunktionen inde i den).

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

Lad os se på et par eksempler.

Her henviser rækkefunktionen til celle A5:

Arbejdsark ( "Ark1"). Range ( "A5")

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

Arbejdsark ( "Ark1") Range. ( "A1: E20")

Som nævnt ovenfor behøver du ikke bruge celleoverførsler med antal bogstaver. Du kan faktisk bruge to cellefunktioner inde i en rækkefunktion til at identificere et interval på arket som dette:

 Med regneark ("Ark1"). Ranger (.Celler (1, 1), _ .Celler (20, 5)) Slutt med 

Koden ovenfor refererer til det samme interval som området (“A1: E20”) funktion gør. Værdien ved at bruge den er, at det giver dig mulighed for at skrive kode, der dynamisk fungerer med intervaller ved hjælp af løkker. Hvordan gør løbende løkker arbejde i computerprogrammering Sådan gør løbende looper i computerprogrammering, løkker er en af ​​de første kontroltyper, du ' Jeg lærer programmering. Du ved sandsynligvis om mens og til sløjfer, men hvad gør en do-while-sløjfe? .

Nu hvor du forstår, hvordan du formaterer cellerne og rækkefunktionerne, lad os dykke ned i, hvordan du kan gøre kreative brug af disse funktioner i din VBA-kode.

Behandler data med cellefunktion

Cellefunktionen er mest nyttig, når du har en kompleks formel, som du vil udføre på tværs af flere celler. Disse intervaller kan også findes på tværs af flere ark.

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

Det har du måske Ark1 der sporer deres salgstælling og deres salgsvolumen.

På Ark2 er, hvor du sporer deres feedback-rating i de sidste 30 dage fra din virksomheds kunder.

Hvis du vil beregne bonus 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 fungerer.

Et alternativ til dette er at oprette VBA-script, som du enten udløser for at køre, når du åbner arket, eller udløses af en kommandoknap på arket, så du kan kontrollere, hvornår det beregnes. Du bruger muligvis et VBA-script til at hente alle salgsdataene fra en ekstern fil alligevel.

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

Cellefunktionen er i funktion

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

Nu, når du klikker på Okay og gå tilbage til hovedarket, du vil se menuindstillingen Udvikler.

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

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

Privat underarbejdsbog_Åbn () Slut sub

Dit kodevindue ser sådan ud.

Nu er du klar til at skrive koden for at håndtere beregningen. Ved hjælp af en enkelt løkke kan du gå gennem alle 11 ansatte, og med Cells-funktionen trække de tre variabler, der er nødvendige til beregningen.

Husk, at cellefunktionen har række og kolonne som parametre til at identificere hver enkelt celle. Vi laver det “x” i rækken, brug et nummer til at anmode om hver kolonnes data. Antallet af rækker er antallet af ansatte, så dette vil være fra 1 til 11. Kolonneidentifikatoren er 2 for salgstæller, 3 for salgsvolumen 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, salgsvolumen som $ 50.000 og en feedbackscore på 10.

• (Salgstælling / 50) x 0,4
• (Salgsvolumen / 50.000) x 0,5
• (Feedback score / 10) x 0,1

Denne enkle tilgang giver salgsmedarbejdere en vægtet bonus. For et antal på 50, volumen på $ 50.000, og en score på 10 - får de hele den maksimale bonus for måneden. Dog reducerer bonusen noget, der er under perfekt på enhver faktor. Alt, der er bedre end ideelt, øger bonusen.

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

Privat underarbejdsbog_Åbn () For x = 2 til 12 regneark ("Ark1"). Celler (x, 4) = (Arbejdsark ("Ark1"). Celler (x, 2). Værdi / 50) * 0,4 _ + (Arbejdsark ("Ark1"). Celler (x, 3) .værdi / 50000) * 0,5 _ + (regneark ("ark2"). Celler (x, 2) .værdi / 10) * 0,1 _ næste x slutunderdel 

Sådan ser output af dette script ud.

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

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

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

For at udforske alt, hvad du kan gøre videre, skal du tjekke på Microsoft MSDN-siden for Cells-objektet.

Formatering af celler med rækkefunktion

Til cirkulering gennem mange celler ad gangen er cellernes funktion perfekt. Men hvis du vil anvende noget på en hel række celler på én gang, er Range-funktionen langt mere effektiv.

En brugssag til 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 antallet af al salgsmængde på tværs af alle salgsmedarbejdere overgår $ 400.000 i alt, vil du fremhæve alle celler i bonussøjlen i grønt for at indikere, at teamet har tjent en ekstra teambonus.

Lad os tage et kig på, hvordan du kan gøre det med en IF-erklæring Sådan bruges IF-udsagn i Microsoft Excel Sådan bruges IF-udsagn i Microsoft Excel Uanset om du er en erfaren ekspert eller en regnearkbegynder, vil du tjekke dette vejledning til IF-udsagn i Excel. .

Privat underarbejdsbog_Åbn () Hvis regneark ("Ark1"). Celler (13, 3). Værdi> 400000 Derefter ActiveSheet.Range ("D2: D12"). Interiør.ColorIndex = 4 End If End Sub

Når dette kører, hvis cellen er over teammålet, udfyldes alle celler i området grønt.

Dette er kun 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 inkluderer:

• Anvend en disposition omkring gruppen
• Kontroller stavemåden af ​​tekst inden for en række celler
• Ryd, kopier eller klip celler
• Søg gennem en række med “Finde” metode
• Meget mere

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

Tag Excel til næste niveau

Nu hvor du forstår forskellene mellem funktionerne Celler og Range, er det tid til at tage din VBA-scripting til 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 kommet i gang med VBA i Excel, så glem ikke, at vi har en fantastisk introduktionsvejledning til Excel VBA Excel VBA-programmeringsvejledning til begyndere Excel VBA-programmeringsvejledning til begyndere VBA er et Microsoft Office-værktøj. Du kan bruge det til at automatisere opgaver med makroer, indstille triggere og meget mere. Vi introducerer dig til visuel grundlæggende programmering i Excel med et simpelt projekt. også for dig.