Förstå Excel -celler kontra intervallfunktioner i VBA

Förstå Excel -celler kontra intervallfunktioner i VBA

Excel är kraftfullt. Om du använder det mycket vet du förmodligen redan många knep med formler eller autoformatering, men använder dig av Celler och Räckvidd funktioner i VBA kan du öka din Excel -analys till en helt ny nivå.





Problemet med att använda Cells and Range -funktionerna i VBA är att på avancerade nivåer har de flesta svårt att förstå hur dessa funktioner faktiskt fungerar. Att använda dem kan bli väldigt förvirrande. Så här kan du använda dem på sätt som du förmodligen aldrig hade föreställt dig.



Cells funktion

Cellerna och intervallfunktionerna låter dig berätta ditt VBA -skript exakt var i ditt kalkylblad du vill hämta eller placera data. Huvudskillnaden mellan de två cellerna är vad de refererar till.





Celler brukar referera till en enda cell i taget, medan Räckvidd refererar till en grupp celler på en gång. Formatet för denna funktion är Celler (rad, kolumn) .

Detta refererar till varje cell i hela arket. Det är ett exempel där Cells -funktionen inte refererar till en enda cell:



Worksheets('Sheet1').Cells

Detta refererar till den tredje cellen från vänster, på den översta raden. Cell C1:

Worksheets('Sheet1').Cells(3)

Följande kodreferenser cell D15:



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

Om du ville kan du också referera till cell D15 med 'Celler (15,' D ')' --- du får använda kolumnbokstaven.

Det finns stor flexibilitet i att kunna referera till en cell med hjälp av ett nummer för kolumn och cell, särskilt med skript som kan slinga igenom ett stort antal celler (och utför beräkningar på dem) mycket snabbt. Vi kommer till det mer detaljerat nedan.



Räckviddsfunktionen

På många sätt är Range -funktionen mycket kraftfullare än att använda celler, eftersom den låter dig referera antingen till en enda cell eller till ett specifikt cellintervall på en gång. Du kommer inte att vilja gå igenom en intervallfunktion, eftersom referenserna för celler inte är nummer (såvida du inte bädda in Cells -funktionen inuti den).

Formatet för denna funktion är Område (cell #1, cell #2) . Varje cell kan betecknas med ett bokstavsnummer.

Låt oss titta på några exempel.

hur man konverterar pdf till word på mac

Här refererar intervallfunktionen till cell A5:

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

Här refererar intervallfunktionen till alla celler mellan A1 till E20:

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

Som nämnts ovan behöver du inte använda cellbokstäver med nummerbokstäver. Du kan faktiskt använda två Cells -funktioner inuti en Range -funktion för att identifiera ett område på arket, så här:


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

Koden ovan refererar till samma intervall som funktionen Range ('A1: E20') gör. Värdet i att använda det är att det låter dig skriva kod som dynamiskt fungerar med intervall med hjälp av loopar.

Nu när du förstår hur du formaterar Cells and Range -funktionerna, låt oss dyka in i hur du kan använda dessa funktioner kreativt i din VBA -kod.

Bearbetar data med cellfunktion

Cells -funktionen är mest användbar när du har en komplex formel som du vill utföra över flera cellområden. Dessa intervall kan också finnas över flera ark.

Låt oss ta ett enkelt exempel. Låt oss säga att du leder ett säljteam på 11 personer, och varje månad vill du titta på deras prestanda.

Du kan ha Ark 1 som spårar deras försäljningsantal och deras försäljningsvolym.

På Ark 2 är där du spårar deras feedbackbetyg under de senaste 30 dagarna från ditt företags kunder.

Om du vill beräkna bonusen på det första arket med värden från de två arken finns det flera sätt att göra detta. Du kan skriva en formel i den första cellen som utför beräkningen med hjälp av data över de två arken och dra ner den. Det kommer att fungera.

Ett alternativ till detta är att skapa VBA -skript som du antingen utlöser för att köra när du öppnar arket eller utlöses av en kommandoknapp på arket så att du kan styra när det beräknas. Du kan ändå använda ett VBA -skript för att hämta all försäljningsdata från en extern fil.

Så varför inte bara utlösa beräkningarna för bonuskolumnen i samma manus vid den tiden?

Cellerna fungerar i aktion

Om du aldrig tidigare har skrivit VBA i Excel måste du aktivera menyalternativet Utvecklare. För att göra detta, gå till Fil > alternativ . Klicka på Anpassa band . Slutligen väljer du Utvecklare från den vänstra rutan, Lägg till den till höger rutan och se till att kryssrutan är markerad.

Nu, när du klickar OK och gå tillbaka till huvudarket, ser du menyn Alternativ för utvecklare.

Du kan använda Föra in -menyn för att infoga en kommandoknapp, eller bara klicka Visa kod att börja koda.

I det här exemplet gör vi att skriptet körs varje gång arbetsboken öppnas. För att göra detta, klicka bara på Visa kod från utvecklarmenyn och klistra in följande nya funktion i kodfönstret.

Private Sub Workbook_Open()
End Sub

Ditt kodfönster kommer att se ut ungefär så här.

Nu är du redo att skriva koden för att hantera beräkningen. Med en enda slinga kan du gå igenom alla 11 anställda och dra in de tre variablerna som behövs för beräkningen med Cells -funktionen.

Kom ihåg att Cells -funktionen har rad och kolumn som parametrar för att identifiera varje enskild cell. Vi gör 'x' till raden, använder ett nummer för att begära varje kolumns data. Antalet rader är antalet anställda, så detta kommer att vara från 1 till 11. Kolumnidentifieraren kommer att vara 2 för försäljningsräkning, 3 för försäljningsvolym och 2 från blad 2 för feedbackpoäng.

Den slutliga beräkningen använder följande procentsatser för att lägga till upp till 100 procent av den totala bonuspoängen. Det är baserat på att en idealisk försäljningsräkning är 50, försäljningsvolym som $ 50 000 och en feedbackpoäng på 10.

  • (Försäljningsantal/50) x 0,4
  • (Försäljningsvolym/50 000) x 0,5
  • (Feedbackpoäng/10) x 0,1

Detta enkla tillvägagångssätt ger försäljningsanställda en viktad bonus. För en räkning av 50, volym på $ 50.000 och en poäng på 10 --- får de hela den maximala bonusen för månaden. Men allt under perfekt på någon faktor minskar bonusen. Allt som är bättre än ideal ökar bonusen.

Låt oss nu se hur all den logiken kan dras av i ett mycket 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

Så här kommer utmatningen av detta skript att se ut.

vad kan du göra med en hallon pi

Om du vill att bonuskolumnen ska visa den faktiska dollarbonusen snarare än procentsatsen kan du multiplicera den med det maximala bonusbeloppet. Ännu bättre, placera den summan i en cell på ett annat blad och referera till den i din kod. Detta skulle göra det lättare att ändra värdet senare utan att behöva redigera din kod.

Skönheten i Cells -funktionen är att du kan bygga en ganska kreativ logik att hämta data från många celler över många olika ark och utför några ganska komplexa beräkningar med dem.

Du kan utföra alla slags åtgärder på celler med hjälp av Cells-funktionen --- saker som att rensa cellerna, ändra teckensnittsformatering och mycket mer.

För att utforska allt du kan göra ytterligare, kolla in Microsoft MSDN -sida för Cells -objektet.

Formatera celler med intervallfunktion

För att slinga igenom många celler en i taget är Cells -funktionen perfekt. Men om du vill tillämpa något på ett helt cellintervall på en gång är intervallfunktionen mycket effektivare.

Ett användningsfall för detta kan vara att formatera ett cellintervall med skript, om vissa villkor är uppfyllda.

Låt oss till exempel säga om summan av all försäljningsvolym för alla försäljningsanställda överstiger 400 000 dollar totalt, du vill markera alla celler i bonuskolumnen i grönt för att indikera att laget har tjänat en extra lagbonus.

Låt oss ta en titt på hur du kan göra det med ett IF -uttalande .

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 denna körs, om cellen är över lagmålet, kommer alla celler i intervallet att fyllas i grönt.

Detta är bara ett enkelt exempel på de många åtgärder du kan utföra på grupper av celler med funktionen Range. Andra saker du kan göra är:

  • Tillämpa en disposition runt gruppen
  • Kontrollera stavningen av text i ett cellområde
  • Rensa, kopiera eller klipp ut celler
  • Sök igenom ett område med metoden 'Sök'
  • Mycket mer

Se till att läsa Microsoft MSDN -sida för att Range -objektet ska se alla möjligheter.

Ta Excel till nästa nivå

Nu när du förstår skillnaderna mellan cellerna och intervallfunktionerna är det dags att ta ditt VBA -skript till nästa nivå. Danns artikel om hur du räknar och lägger till funktioner i Excel låter dig bygga ännu mer avancerade skript som snabbt kan samla värden i alla dina datamängder.

Och om du precis har börjat med VBA i Excel, glöm inte att vi har en fantastisk introduktionsguide till Excel VBA för dig också.

Dela med sig Dela med sig Tweet E-post Canon vs Nikon: Vilket kameramärke är bättre?

Canon och Nikon är de två största namnen i kameraindustrin. Men vilket märke erbjuder den bättre sortimentet av kameror och objektiv?

Läs Nästa Relaterade ämnen
  • Produktivitet
  • Programmering
  • Visual Basic programmering
  • Microsoft excel
Om författaren Ryan Dube(942 artiklar publicerade)

Ryan har en kandidatexamen i elektroteknik. Han har arbetat 13 år inom automationsteknik, 5 år inom IT och är nu en Apps Engineer. Han var tidigare chefredaktör för MakeUseOf och talade vid nationella konferenser om datavisualisering och har presenterats på nationell TV och radio.

Mer från Ryan Dube

Prenumerera på vårt nyhetsbrev

Gå med i vårt nyhetsbrev för tekniska tips, recensioner, gratis e -böcker och exklusiva erbjudanden!

Klicka här för att prenumerera