Home-theater-designers
Excel ist mächtig. Wenn Sie es häufig verwenden, kennen Sie wahrscheinlich bereits viele Tricks mit Formeln oder Autoformatierung, verwenden jedoch Zellen und Bereich Funktionen in VBA können Sie Ihre Excel-Analyse auf ein ganz neues Niveau heben.
Das Problem bei der Verwendung der Zellen- und Bereichsfunktionen in VBA besteht darin, dass die meisten Leute auf den fortgeschrittenen Ebenen Schwierigkeiten haben, zu verstehen, wie diese Funktionen tatsächlich funktionieren. Ihre Verwendung kann sehr verwirrend sein. So können Sie sie auf eine Weise nutzen, die Sie sich wahrscheinlich nie vorgestellt haben.
Die Zellenfunktion
Mit den Zellen- und Bereichsfunktionen können Sie es erkennen dein VBA-Skript genau dort, wo Sie auf Ihrem Arbeitsblatt Daten abrufen oder platzieren möchten. Der Hauptunterschied zwischen den beiden Zellen besteht darin, worauf sie sich beziehen.
Zellen referenzieren normalerweise jeweils eine einzelne Zelle, während Bereich verweist auf eine Gruppe von Zellen gleichzeitig. Das Format für diese Funktion ist Zellen (Zeile, Spalte) .
Dies verweist auf jede einzelne Zelle im gesamten Blatt. Dies ist das einzige Beispiel, bei dem die Funktion Cells nicht auf eine einzelne Zelle verweist:
Worksheets('Sheet1').CellsDies verweist auf die dritte Zelle von links in der obersten Zeile. Zelle C1:
Worksheets('Sheet1').Cells(3)Der folgende Code verweist auf Zelle D15:
Worksheets('Sheet1').Cells(15,4)Wenn Sie möchten, können Sie die Zelle D15 auch mit 'Cells(15,'D')' referenzieren - Sie dürfen den Spaltenbuchstaben verwenden.
Es ist sehr flexibel, eine Zelle mit einer Zahl für Spalte und Zelle zu referenzieren, insbesondere bei Skripten, die dies können durchschleifen eine große Anzahl von Zellen (und Berechnungen für sie) sehr schnell durchführen. Darauf gehen wir weiter unten noch genauer ein.
Die Range-Funktion
In vielerlei Hinsicht ist die Range-Funktion weitaus leistungsfähiger als die Verwendung von Zellen, da Sie entweder auf eine einzelne Zelle oder einen bestimmten Zellbereich gleichzeitig verweisen können. Sie werden eine Range-Funktion nicht durchlaufen wollen, da die Referenzen für Zellen keine Zahlen sind (es sei denn, Sie betten die Cells-Funktion darin ein).
Das Format für diese Funktion ist Reichweite(Zelle #1,Zelle #2) . Jede Zelle kann mit einer Buchstabennummer bezeichnet werden.
Schauen wir uns einige Beispiele an.
Beste Notizanwendung für Android
Hier bezieht sich die Bereichsfunktion auf Zelle A5:
Worksheets('Sheet1').Range('A5')Hier bezieht sich die Bereichsfunktion auf alle Zellen zwischen A1 bis E20:
Worksheets('Sheet1').Range('A1:E20')Wie oben erwähnt, müssen Sie keine Zahlen-Buchstaben-Zellzuweisungen verwenden. Sie könnten tatsächlich zwei Zellenfunktionen innerhalb einer Range-Funktion verwenden, um einen Bereich auf dem Blatt zu identifizieren, wie folgt:
With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
Der obige Code verweist auf denselben Bereich wie die Funktion Range('A1:E20'). Der Vorteil bei der Verwendung besteht darin, dass Sie Code schreiben können, der mithilfe von Schleifen dynamisch mit Bereichen arbeitet.
Nachdem Sie nun wissen, wie Sie die Zellen- und Bereichsfunktionen formatieren, wollen wir uns ansehen, wie Sie diese Funktionen in Ihrem VBA-Code kreativ nutzen können.
Verarbeitung von Daten mit Zellenfunktion
Die Funktion Zellen ist am nützlichsten, wenn Sie eine komplexe Formel haben, die Sie über mehrere Zellbereiche hinweg ausführen möchten. Diese Bereiche können auch über mehrere Blätter hinweg vorhanden sein.
Nehmen wir ein einfaches Beispiel. Angenommen, Sie leiten ein Vertriebsteam von 11 Mitarbeitern und möchten jeden Monat deren Leistung überprüfen.
Vielleicht hast du Blatt1 die ihre Verkaufszahlen und ihr Verkaufsvolumen verfolgt.
Auf Blatt2 Hier können Sie deren Feedback-Bewertungen der letzten 30 Tage von den Kunden Ihres Unternehmens verfolgen.
Wenn Sie den Bonus auf dem ersten Blatt mit Werten aus den beiden Blättern berechnen möchten, gibt es mehrere Möglichkeiten, dies zu tun. Sie könnten in die erste Zelle eine Formel schreiben, die die Berechnung mit Daten aus den beiden Blättern durchführt, und sie nach unten ziehen. Das wird funktionieren.
Eine Alternative dazu ist das Erstellen eines VBA-Skripts, das Sie entweder auslösen, wenn Sie das Blatt öffnen, oder das durch eine Befehlsschaltfläche auf dem Blatt ausgelöst wird, damit Sie steuern können, wann es berechnet wird. Sie können sowieso ein VBA-Skript verwenden, um alle Verkaufsdaten aus einer externen Datei abzurufen.
Warum also nicht zu diesem Zeitpunkt die Berechnungen für die Bonusspalte im selben Skript auslösen?
Die Zellenfunktion in Aktion
Wenn Sie noch nie VBA in Excel geschrieben haben, müssen Sie das Menüelement Developer aktivieren. Gehen Sie dazu zu Datei > Optionen . Klicke auf Menüband anpassen . Wählen Sie schließlich im linken Bereich Entwickler aus, Hinzufügen es in den rechten Bereich und vergewissern Sie sich, dass das Kontrollkästchen aktiviert ist.
Wenn du jetzt klickst OK und gehen Sie zurück zum Hauptblatt, Sie sehen die Menüoption Entwickler.
Du kannst den ... benutzen Einfügung Menü, um eine Befehlsschaltfläche einzufügen, oder klicken Sie einfach auf Code anzeigen Codierung zu starten.
In diesem Beispiel führen wir das Skript jedes Mal aus, wenn die Arbeitsmappe geöffnet wird. Klicken Sie dazu einfach auf Code anzeigen aus dem Entwicklermenü und fügen Sie die folgende neue Funktion in das Codefenster ein.
Private Sub Workbook_Open()
End SubIhr Codefenster sieht ungefähr so aus.
Jetzt können Sie den Code für die Berechnung schreiben. Mit einer einzigen Schleife können Sie alle 11 Mitarbeiter durchlaufen und mit der Funktion Zellen die drei für die Berechnung benötigten Variablen eingeben.
Denken Sie daran, dass die Zellenfunktion Zeilen und Spalten als Parameter hat, um jede einzelne Zelle zu identifizieren. Wir machen 'x' zur Zeile und verwenden eine Zahl, um die Daten jeder Spalte anzufordern. Die Anzahl der Zeilen ist die Anzahl der Mitarbeiter, also von 1 bis 11. Die Spaltenkennung lautet 2 für die Verkaufszahl, 3 für das Verkaufsvolumen und 2 aus Blatt 2 für den Feedback-Score.
Die endgültige Berechnung verwendet die folgenden Prozentsätze, um 100 Prozent der gesamten Bonuspunktzahl zu ergeben. Es basiert auf einer idealen Verkaufszahl von 50, einem Verkaufsvolumen von 50.000 USD und einem Feedback-Score von 10.
- (Verkaufszähler/50) x 0,4
- (Verkaufsvolumen/50.000) x 0,5
- (Feedback-Score/10) x 0,1
Dieser einfache Ansatz gibt Vertriebsmitarbeitern einen gewichteten Bonus. Bei einer Zählung von 50, einem Volumen von 50.000 USD und einer Punktzahl von 10 erhalten sie den gesamten maximalen Bonus für den Monat. Alles, was bei einem Faktor nicht perfekt ist, reduziert jedoch den Bonus. Alles, was besser als ideal ist, erhöht den Bonus.
Schauen wir uns nun an, wie all diese Logik in einem sehr einfachen, kurzen VBA-Skript umgesetzt werden kann:
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
So sieht die Ausgabe dieses Skripts aus.
was macht man mit einem himbeer pi
Wenn Sie möchten, dass die Bonusspalte den tatsächlichen Dollarbonus anstelle des Prozentsatzes anzeigt, können Sie ihn mit dem maximalen Bonusbetrag multiplizieren. Noch besser, platzieren Sie diesen Betrag in einer Zelle auf einem anderen Blatt und verweisen Sie in Ihrem Code darauf. Dies würde es einfacher machen, den Wert später zu ändern, ohne Ihren Code bearbeiten zu müssen.
Das Schöne an der Cells-Funktion ist, dass Sie eine ziemlich kreative Logik zum Abrufen von Daten erstellen können viele Zellen über viele verschiedene Blätter, und führen Sie einige aus ziemlich komplexe Berechnungen mit ihnen.
Mit der Zellenfunktion können Sie alle möglichen Aktionen für Zellen ausführen - Dinge wie das Löschen der Zellen, das Ändern der Schriftformatierung und vieles mehr.
Um alles zu erkunden, was Sie weiter tun können, besuchen Sie die Microsoft MSDN-Seite für das Cells-Objekt.
Formatieren von Zellen mit Bereichsfunktion
Um mehrere Zellen nacheinander durchzuschleifen, ist die Funktion Zellen perfekt. Wenn Sie jedoch etwas auf einen ganzen Zellbereich gleichzeitig anwenden möchten, ist die Range-Funktion viel effizienter.
Ein Anwendungsfall hierfür könnte sein, einen Zellbereich mit Skript zu formatieren, wenn bestimmte Bedingungen erfüllt sind.
Nehmen wir beispielsweise an, wenn die Summe des gesamten Verkaufsvolumens aller Vertriebsmitarbeiter insgesamt 400.000 US-Dollar übersteigt, möchten Sie alle Zellen in der Bonusspalte grün hervorheben, um anzuzeigen, dass das Team einen zusätzlichen Teambonus verdient hat.
Schauen wir uns an, wie Sie das mit machen können eine IF-Anweisung .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubWenn dies ausgeführt wird und sich das Feld über dem Teamziel befindet, werden alle Felder im Bereich grün ausgefüllt.
Dies ist nur ein einfaches Beispiel für die vielen Aktionen, die Sie mit der Bereichsfunktion für Zellgruppen ausführen können. Andere Dinge, die Sie tun können, sind:
- Wenden Sie einen Umriss um die Gruppe an
- Überprüfen Sie die Rechtschreibung von Text innerhalb eines Zellbereichs
- Zellen löschen, kopieren oder ausschneiden
- Durchsuchen Sie einen Bereich mit der 'Find'-Methode
- Viel mehr
Lesen Sie unbedingt die Microsoft MSDN-Seite für das Range-Objekt, um alle Möglichkeiten zu sehen.
Bringen Sie Excel auf die nächste Stufe
Nachdem Sie nun die Unterschiede zwischen den Zellen- und Bereichsfunktionen verstanden haben, ist es an der Zeit, Ihr VBA-Skripting auf die nächste Stufe zu heben. Danns Artikel über die Verwendung von Zähl- und Addierfunktionen in Excel ermöglicht es Ihnen, noch fortschrittlichere Skripte zu erstellen, die sehr schnell Werte für alle Ihre Datensätze akkumulieren können.
Und wenn Sie gerade erst mit VBA in Excel beginnen, vergessen Sie nicht, dass wir ein fantastisches Einführung in Excel VBA für dich auch.
Teilen Teilen Tweet Email Canon vs. Nikon: Welche Kameramarke ist besser?Canon und Nikon sind die beiden größten Namen in der Kamerabranche. Aber welche Marke bietet die bessere Auswahl an Kameras und Objektiven?
Weiter lesen Verwandte Themen- Produktivität
- Programmierung
- Visual Basic-Programmierung
- Microsoft Excel
Ryan hat einen BSc-Abschluss in Elektrotechnik. Er hat 13 Jahre in der Automatisierungstechnik, 5 Jahre in der IT gearbeitet und ist jetzt Apps Engineer. Als ehemaliger Chefredakteur von MakeUseOf hat er auf nationalen Konferenzen zur Datenvisualisierung gesprochen und wurde im nationalen Fernsehen und Radio vorgestellt.
Mehr von Ryan DubeAbonniere unseren Newsletter
Abonnieren Sie unseren Newsletter für technische Tipps, Rezensionen, kostenlose E-Books und exklusive Angebote!
Klicken Sie hier, um zu abonnieren