Excel er kraftfuldt. Hvis du bruger det meget, har du sikkert allerede mange tricks ved hjælp af formler eller autoformatering, men ved at bruge Cells and Range- 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 svært ved at forstå, hvordan disse funktioner rent faktisk virker. Brug af dem kan blive meget forvirrende. Sådan kan du gøre brug af dem på måder, du sandsynligvis aldrig har forestillet dig.
Cellens funktion
Celler og Range-funktioner 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 Enkle kode og makroer er nøglerne til Microsoft Excel supermagter. Selv ikke-programmører kan nemt tilføje imponerende funktionalitet til deres regneark med Virtual Basics for Applications (VBA). Bare undgå disse programmering nybegynder fejl! Læs mere præcis, hvor i dit regneark du ønsker at få eller placere data. Hovedforskellen mellem de to celler er, hvad de refererer til.
Celler refererer normalt til en enkelt celle ad gangen, mens Range refererer til en gruppe af 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 cellens funktion ikke refererer til en enkelt celle:
Worksheets("Sheet1").Cells Dette refererer til den tredje celle fra venstre, øverste række. Celle C1:
Worksheets("Sheet1").Cells(3) De følgende kodehenvisninger celle D15:
Worksheets("Sheet1").Cells(15, 4) Hvis du vil, kan du også referere til celle D15 med "Celler (15, " D ")" - du må bruge kolonnebrevet.
Der er meget fleksibilitet i at kunne referere til en celle ved hjælp af et nummer for kolonne og celle, især med scripts, der kan løbe gennem et stort antal celler (og udføre beregninger på dem) meget hurtigt. Vi kommer nærmere til det nedenfor.
Range-funktionen
På mange måder er Range-funktionen langt mere kraftfuld end at bruge celler, fordi den lader dig referere til enten en enkelt celle eller et specifikt udvalg af celler alt sammen på én gang. Du vil ikke lykkes gennem en Range-funktion, fordi referencerne for celler ikke er tal (medmindre du integrerer cellens funktion inde i det).
Formatet for denne funktion er Range (Cell # 1, Cell # 2) . Hver celle kan betegnes med et bogstavnummer.
Lad os se på nogle få eksempler.
Her refererer afstandsfunktionen til celle A5:
Worksheets("Sheet1").Range("A5") Her refererer afstandsfunktionen til alle celler mellem A1 til E20:
Worksheets("Sheet1").Range("A1:E20") Som nævnt ovenfor behøver du ikke bruge nummerbogstavsopgaver. Du kunne faktisk bruge to cellefunktioner inde i en Range-funktion til at identificere en rækkevidde på arket, som dette:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Koden ovenfor refererer til samme område som funktionen Range ("A1: E20") gør. Værdien ved at bruge den er, at det ville give dig mulighed for at skrive kode, der dynamisk arbejder med intervaller ved hjælp af sløjfer. Hvordan-mens sløjfer arbejder i computerprogrammering. Sådan gør-mens sløjfer arbejder i computerprogrammering Sløjfer er en af de første kontroltyper, du ' ll lære i programmering. Du ved nok nok om, mens og for sløjfer, men hvad opnår en runde? Læs mere .
Nu hvor du forstår, hvordan du formaterer Celler og Range-funktioner, lad os dykke ind i, hvordan du kan gøre kreativ brug af disse funktioner i din VBA-kode.
Behandling af data med cellefunktion
Celler-funktionen 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å eksistere 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æstation.
Du kan have Sheet1, der følger deres salgstal og deres salgsvolumen.
På Sheet2 er du sporet deres feedbackbedømmelse for de sidste 30 dage fra virksomhedens 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 at bruge data på tværs af de to ark og trække den ned. Det vil fungere.
Et alternativ til dette er at skabe 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, når det beregner. Du kan muligvis bruge et VBA-script til at trække alle salgsdata fra en ekstern fil alligevel.
Så hvorfor ikke bare udløse beregningerne for bonuskolonnen i det samme script på det tidspunkt?
Cellens funktion 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 Filer > Indstillinger . Klik på Tilpas bånd . Endelig skal du vælge Udvikler fra venstre rude, Tilføj den til den højre rude, og sørg for, at afkrydsningsfeltet er markeret.
Nu, når du klikker på OK og går tilbage til hovedarket, får du vist menuen Udvikler menu.
Du kan bruge menuen Indsæt til at indsætte en kommandoknap eller bare klikke på Vis kode for at starte kodningen.
I dette eksempel får vi scriptet til at køre, hver gang arbejdsbogen åbnes. For at gøre dette skal du bare klikke på Vis kode fra udviklermenuen og indsætte den følgende nye funktion i kodevinduet.
Private Sub Workbook_Open() End Sub Dit kodevindue vil se sådan ud.
Nu er du klar til at skrive koden til at håndtere beregningen. Ved hjælp af en enkelt loop kan du træde igennem alle 11 medarbejdere, og med Celler-funktionen trækkes de tre variabler, der er nødvendige til beregningen.
Husk, at cellefunktionen har række og kolonne som parametre for at identificere hver enkelt celle. Vi laver "x" rækken, brug et nummer til at anmode om hver kolonne data. Antallet af rækker er antallet af medarbejdere, så dette vil være fra 1 til 11. Søjleidentifikatoren vil være 2 for salgstal, 3 for salgsmængde og 2 fra ark 2 til feedbackresultat.
Den endelige beregning bruger følgende procentdele for at tilføje op til 100 procent af den samlede bonus score. Det er baseret på et ideelt salgstal på 50, salgsvolumen som $ 50.000, og en feedback score på 10.
- (Salgstal / 50) x 0, 4
- (Salgsvolumen / 50.000) x 0, 5
- (Feedback score / 10) x 0, 1
Denne enkle tilgang giver salgsansatte en vægtet bonus. For et tal på 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å nogen faktor reducerer bonusen. Noget bedre end ideelt øger bonusen.
Lad os nu se, hvordan al den logik kan trækkes i et meget simpelt, 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 Dette er, hvad produktionen af dette script vil se ud.
Hvis du ville have bonuskolonnen vist den faktiske dollarbonus i stedet for procentdelen, kan du multiplicere den med det maksimale bonusbeløb. Endnu bedre, læg det beløb i en celle på et andet ark, og referer det til i din kode. Dette ville gøre det nemmere at ændre værdien senere uden at skulle redigere din kode.
Skønheden i Cells-funktionen er, at du kan opbygge nogle temmelig kreative logik til at trække data fra mange celler på tværs af mange forskellige ark og udføre 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 undersøge alt, hvad du kan gøre yderligere, kan du tjekke Microsoft MSDN-siden for Cells-objektet.
Formatering af celler med rækkefølge
For at løbe igennem mange celler en 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 brugs tilfælde 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, om antallet af salgsmængder på tværs af alle salgsansatte overstiger $ 400.000 i alt. Du vil markere alle celler i bonuskolonnen i grøn for at betyde, at holdet har opnået en ekstra holdbonus.
Lad os se på, hvordan du kan gøre det med en IF-erklæring Sådan bruges IF-erklæringer i Microsoft Excel Sådan bruges IF-erklæringer i Microsoft Excel Uanset om du er en erfaren ekspert eller et regnearkbegynder, vil du gerne tjekke dette vejledning til IF-udsagn i Excel. Læs mere .
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 dette kører, hvis cellen er over holdets mål, bliver alle celler i området fyldt i grønt.
Dette er blot et simpelt eksempel på de mange handlinger, du kan udføre på grupper af celler ved hjælp af Range-funktionen. Andre ting du kan gøre er:
- Anvend en omrids rundt om gruppen
- Kontroller stavningen af tekst inden for en række celler
- Ryd, kopier eller skar celler
- Søg gennem en rækkevidde med "Find" -metoden
- 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 Celler og Range-funktionerne, er det tid til at tage din VBA scripting til næste niveau. Danns artikel om brugen af optælling og tilføjelse af funktioner i Excel giver dig mulighed for at opbygge endnu mere avancerede scripts, der kan samle 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 introduktionskurs til Excel VBA Excel VBA Programmering Tutorial for Beginners Excel VBA Programmering Tutorial for Beginners VBA er et Microsoft Office power tool. Du kan bruge den til at automatisere opgaver med makroer, sæt triggere og meget mere. Vi introducerer dig til Excel Visual Basic Programmering med et simpelt projekt. Læs mere for dig også.