Home-theater-designers
Excel is krachtig. Als je het veel gebruikt, ken je waarschijnlijk al veel trucs met formules of autoformattering, maar gebruik makend van Cellen en Bereik functies in VBA, kunt u uw Excel-analyses naar een geheel nieuw niveau tillen.
Het probleem met het gebruik van de functies Cellen en Bereik in VBA is dat de meeste mensen op de geavanceerde niveaus moeite hebben om te begrijpen hoe deze functies echt werken. Het gebruik ervan kan erg verwarrend zijn. Hier leest u hoe u ze kunt gebruiken op manieren die u zich waarschijnlijk nooit had voorgesteld.
De celfunctie
De functies Cellen en Bereik laten u zien: je VBA-script precies waar op uw werkblad u gegevens wilt ophalen of plaatsen. Het belangrijkste verschil tussen de twee cellen is waarnaar ze verwijzen.
Cellen verwijzen meestal naar één cel tegelijk, terwijl Bereik verwijst naar een groep cellen tegelijk. Het formaat voor deze functie is Cellen (rij, kolom) .
Dit verwijst naar elke afzonderlijke cel in het hele blad. Dit is het enige voorbeeld waarbij de functie Cellen niet naar een enkele cel verwijst:
Worksheets('Sheet1').CellsDit verwijst naar de derde cel van links, van de bovenste rij. Cel C1:
Worksheets('Sheet1').Cells(3)De volgende code verwijst naar cel D15:
Worksheets('Sheet1').Cells(15,4)Als je wilt, kun je ook naar cel D15 verwijzen met 'Cells(15,'D')'---je mag de kolomletter gebruiken.
Er is veel flexibiliteit om naar een cel te kunnen verwijzen met een nummer voor kolom en cel, vooral met scripts die dat kunnen doorlussen een groot aantal cellen (en daarop berekeningen uitvoeren) zeer snel. We gaan daar hieronder nader op in.
De bereikfunctie
In veel opzichten is de functie Bereik veel krachtiger dan het gebruik van Cellen, omdat u hiermee in één keer naar een enkele cel of een specifiek cellenbereik kunt verwijzen. U zult niet door een Range-functie willen lopen, omdat de verwijzingen naar cellen geen getallen zijn (tenzij u de Cells-functie erin insluit).
Het formaat voor deze functie is Bereik (cel #1, cel #2) . Elke cel kan worden aangeduid met een letternummer.
Laten we een paar voorbeelden bekijken.
versnellen harde schijf windows 10
Hier verwijst de bereikfunctie naar cel A5:
Worksheets('Sheet1').Range('A5')Hier verwijst de bereikfunctie naar alle cellen tussen A1 tot en met E20:
Worksheets('Sheet1').Range('A1:E20')Zoals hierboven vermeld, hoeft u geen celtoewijzingen met cijfers te gebruiken. Je zou eigenlijk twee Cells-functies binnen een Range-functie kunnen gebruiken om een bereik op het blad te identificeren, zoals dit:
With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
De bovenstaande code verwijst naar hetzelfde bereik als de functie Range ('A1:E20'). De waarde bij het gebruik ervan is dat je hiermee code kunt schrijven die dynamisch werkt met bereiken met behulp van loops .
Nu u begrijpt hoe u de functies Cells en Range moet opmaken, gaan we eens kijken hoe u creatief gebruik kunt maken van deze functies in uw VBA-code.
Gegevens verwerken met cellenfunctie
De functie Cellen is vooral handig als u een complexe formule hebt die u op meerdere celbereiken wilt toepassen. Deze bereiken kunnen ook op meerdere bladen voorkomen.
Laten we een eenvoudig voorbeeld nemen. Stel dat u een verkoopteam van 11 mensen aanstuurt en dat u elke maand wilt kijken naar hun prestaties.
Je hebt misschien Blad1 die hun verkoopaantallen en hun verkoopvolume bijhoudt.
Op Blad2 hier volgt u hun feedbackscore van de afgelopen 30 dagen van de klanten van uw bedrijf.
Als u de bonus op het eerste blad wilt berekenen met behulp van waarden van de twee bladen, zijn er meerdere manieren om dit te doen. U kunt een formule in de eerste cel schrijven die de berekening uitvoert met gegevens over de twee bladen en deze naar beneden slepen. Dat zal werken.
Een alternatief hiervoor is het maken van een VBA-script dat u activeert om uit te voeren wanneer u het blad opent, of dat wordt geactiveerd door een opdrachtknop op het blad, zodat u kunt bepalen wanneer het wordt berekend. U kunt toch een VBA-script gebruiken om alle verkoopgegevens uit een extern bestand op te halen.
Dus waarom niet gewoon de berekeningen voor de bonuskolom in hetzelfde script op dat moment activeren?
De celfunctie in actie
Als u nog nooit VBA in Excel hebt geschreven, moet u het menu-item Ontwikkelaar inschakelen. Ga hiervoor naar Bestand > Opties . Klik op Lint aanpassen . Kies ten slotte Ontwikkelaar in het linkerdeelvenster, Toevoegen het naar het rechterdeelvenster en zorg ervoor dat het selectievakje is ingeschakeld.
Nu, wanneer u klikt Oke en ga terug naar het hoofdblad, je ziet de menuoptie Ontwikkelaar.
U kunt de Invoegen menu om een opdrachtknop in te voegen, of klik gewoon op Bekijk code om te beginnen met coderen.
In dit voorbeeld laten we het script elke keer dat de werkmap wordt geopend, worden uitgevoerd. Om dit te doen, klikt u gewoon op Bekijk code uit het ontwikkelaarsmenu en plak de volgende nieuwe functie in het codevenster.
Private Sub Workbook_Open()
End SubUw codevenster ziet er ongeveer zo uit.
Nu bent u klaar om de code te schrijven om de berekening uit te voeren. Met behulp van een enkele lus kunt u alle 11 medewerkers doorlopen en met de functie Cellen de drie variabelen invoeren die nodig zijn voor de berekening.
Onthoud dat de functie Cellen rij en kolom als parameters heeft om elke afzonderlijke cel te identificeren. We maken 'x' de rij, gebruiken een nummer om de gegevens van elke kolom op te vragen. Het aantal rijen is het aantal werknemers, dus dit is van 1 tot 11. De kolom-ID is 2 voor Verkooptelling, 3 voor Verkoopvolume en 2 van Blad 2 voor Feedbackscore.
De uiteindelijke berekening gebruikt de volgende percentages om op te tellen tot 100 procent van de totale bonusscore. Het is gebaseerd op een ideale verkooptelling van 50, een verkoopvolume van $ 50.000 en een feedbackscore van 10.
- (Verkooptelling/50) x 0.4
- (Verkoopvolume/50.000) x 0,5
- (Feedbackscore/10) x 0,1
Deze eenvoudige aanpak geeft verkoopmedewerkers een gewogen bonus. Voor een telling van 50, een volume van $ 50.000 en een score van 10 --- krijgen ze de volledige maximale bonus voor de maand. Maar alles wat onder perfect is op welke factor dan ook, vermindert de bonus. Alles wat beter is dan ideaal verhoogt de bonus.
Laten we nu eens kijken hoe al die logica kan worden uitgevoerd in een heel eenvoudig, 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
Dit is hoe de uitvoer van dit script eruit zal zien.
wat kun je doen met een frambozenpi
Als u wilt dat de bonuskolom de werkelijke dollarbonus toont in plaats van het percentage, kunt u dit vermenigvuldigen met het maximale bonusbedrag. Beter nog, plaats dat bedrag in een cel op een ander blad en verwijs ernaar in uw code. Dit zou het gemakkelijker maken om de waarde later te wijzigen zonder uw code te hoeven bewerken.
Het mooie van de Cells-functie is dat je behoorlijk creatieve logica kunt bouwen om gegevens uit te halen veel cellen over veel verschillende bladen, en voer enkele uit behoorlijk complexe berekeningen met hen.
U kunt allerlei acties op cellen uitvoeren met behulp van de functie Cellen --- dingen zoals het wissen van de cellen, het wijzigen van de opmaak van lettertypen en nog veel meer.
Om alles te ontdekken wat je verder kunt doen, bekijk de Microsoft MSDN-pagina voor het Cells-object.
Cellen opmaken met bereikfunctie
Om veel cellen één voor één te doorlopen, is de functie Cellen perfect. Maar als u iets in één keer op een hele reeks cellen wilt toepassen, is de functie Bereik veel efficiënter.
Een gebruiksvoorbeeld hiervoor kan zijn om een celbereik op te maken met behulp van script, als aan bepaalde voorwaarden wordt voldaan.
Laten we bijvoorbeeld zeggen dat als het totaal van alle verkoopvolumes van alle verkoopmedewerkers in totaal $ 400.000 overschrijdt, u alle cellen in de bonuskolom groen wilt markeren om aan te geven dat het team een extra teambonus heeft verdiend.
Laten we eens kijken hoe je dat kunt doen met een IF-verklaring .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubWanneer dit wordt uitgevoerd en de cel boven het teamdoel is, worden alle cellen in het bereik groen ingevuld.
Dit is slechts een eenvoudig voorbeeld van de vele acties die u op groepen cellen kunt uitvoeren met behulp van de functie Bereik. Andere dingen die u kunt doen, zijn onder meer:
- Een omtrek toepassen rond de groep
- Controleer de spelling van tekst in een celbereik
- Cellen wissen, kopiëren of knippen
- Doorzoek een bereik met de 'Zoeken' methode
- Veel meer
Lees zeker de Microsoft MSDN-pagina voor het Range-object om alle mogelijkheden te zien.
Breng Excel naar het volgende niveau
Nu u de verschillen tussen de functies Cells en Range begrijpt, is het tijd om uw VBA-scripting naar een hoger niveau te tillen. Danns artikel over het gebruik van functies voor tellen en toevoegen in Excel stelt u in staat om nog geavanceerdere scripts te bouwen die zeer snel waarden in al uw datasets kunnen verzamelen.
En als je net begint met VBA in Excel, vergeet dan niet dat we een fantastische inleidende gids voor Excel VBA ook voor jou.
Deel Deel Tweeten E-mail Canon versus Nikon: welk cameramerk is beter?Canon en Nikon zijn de twee grootste namen in de camera-industrie. Maar welk merk biedt de betere reeks camera's en lenzen?
Lees volgende Gerelateerde onderwerpen- productiviteit
- Programmeren
- Visual Basic-programmering
- Microsoft Excel
Ryan heeft een BSc-graad in Elektrotechniek. Hij heeft 13 jaar in de automatiseringstechniek gewerkt, 5 jaar in de IT en is nu een Apps Engineer. Als voormalig hoofdredacteur van MakeUseOf heeft hij gesproken op nationale conferenties over datavisualisatie en is hij te zien geweest op nationale tv en radio.
Meer van Ryan DubeAbonneer op onze nieuwsbrief
Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!
Klik hier om je te abonneren