Excel ist leistungsstark. Wenn Sie es häufig verwenden, kennen Sie wahrscheinlich bereits viele Tricks mit Formeln oder Autoformatierung, aber wenn Sie die Zellen- und Bereichsfunktionen in VBA verwenden, können Sie Ihre Excel-Analysen auf eine ganz neue Ebene heben.
Das Problem bei der Verwendung der Zellen- und Bereichsfunktionen in VBA ist, dass die meisten Benutzer auf den fortgeschrittenen Ebenen schwer verstehen, wie diese Funktionen tatsächlich funktionieren. Ihre Verwendung kann sehr verwirrend sein. So können Sie sie auf eine Art und Weise nutzen, die Sie wahrscheinlich nie für möglich gehalten hätten.
Die Funktion der Zellen
Mit den Zellen- und Bereichsfunktionen können Sie Ihr VBA-Skript erklären 4 Fehler, die Sie beim Programmieren von Excel-Makros mit VBA 4 vermeiden können Fehler, die Sie beim Programmieren von Excel-Makros mit VBA vermeiden können Einfacher Code und Makros sind die Schlüssel zu Microsoft Excel-Superpowers. Auch Nicht-Programmierer können ihren Tabellen mit Virtual Basics for Applications (VBA) mühelos beeindruckende Funktionen hinzufügen. Vermeiden Sie einfach diese Programmierung Anfänger Fehler! Lesen Sie genauer, wo auf Ihrem Arbeitsblatt Sie Daten erhalten oder platzieren möchten. Der Hauptunterschied zwischen den beiden Zellen ist, worauf sie sich beziehen.
Zellen verweisen normalerweise jeweils auf eine einzelne Zelle, während Range eine Gruppe von Zellen gleichzeitig referenziert. Das Format für diese Funktion ist Cells (Zeile, Spalte) .
Dies bezieht sich auf jede einzelne Zelle im gesamten Blatt. Es ist das eine Beispiel, bei dem die Cells-Funktion nicht auf eine einzelne Zelle verweist:
Worksheets("Sheet1").Cells Dies bezieht sich auf die dritte Zelle von links 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 auch auf Zelle D15 mit "Zellen (15, " D ") verweisen - Sie dürfen den Spaltenbuchstaben verwenden.
Es ist sehr flexibel, eine Zelle anhand einer Zahl für Spalte und Zelle referenzieren zu können, insbesondere mit Skripten, die sehr schnell eine große Anzahl von Zellen durchlaufen (und Berechnungen darauf durchführen können). Wir werden darauf im Folgenden näher eingehen.
Die Bereichsfunktion
In vielerlei Hinsicht ist die Range-Funktion viel leistungsfähiger als die Verwendung von Zellen, da Sie damit entweder eine einzelne Zelle oder einen bestimmten Bereich von Zellen gleichzeitig referenzieren können. Sie werden keine Range-Funktion 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 Range (Zelle 1, Zelle 2) . Jede Zelle kann mit einer Buchstabennummer bezeichnet werden.
Schauen wir uns ein paar Beispiele an.
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-Zellenzuweisungen verwenden. Sie könnten tatsächlich zwei Cells-Funktionen innerhalb einer Range-Funktion verwenden, um einen Bereich auf dem Blatt zu identifizieren, beispielsweise:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Der obige Code bezieht sich auf den gleichen Bereich wie die Funktion Range ("A1: E20"). Der Wert bei der Verwendung ist, dass es Ihnen erlauben würde, Code zu schreiben, der dynamisch mit Bereichen funktioniert, die Schleifen verwenden. Wie Do-While-Schleifen in der Computerprogrammierung funktionieren Wie Schleifen in der Computerprogrammierung funktionieren Schleifen sind eine der ersten Steuerarten, die Sie ' ll lernen in der Programmierung. Sie wissen wahrscheinlich über while und for-Schleifen, aber was macht eine Do-while-Schleife? Weiterlesen .
Nachdem Sie nun verstanden haben, wie Sie die Zellen- und Bereichsfunktionen formatieren, lernen Sie, wie Sie diese Funktionen in Ihrem VBA-Code kreativ nutzen können.
Daten mit Zellenfunktion verarbeiten
Die Cells-Funktion ist am nützlichsten, wenn Sie eine komplexe Formel haben, die Sie über mehrere Zellenbereiche hinweg ausführen möchten. Diese Bereiche können auch mehrere Blätter umfassen.
Nehmen wir ein einfaches Beispiel. Nehmen wir an, Sie verwalten ein Verkaufsteam von 11 Mitarbeitern und jeden Monat möchten Sie sich deren Leistung ansehen.
Sie haben möglicherweise Sheet1, die ihre Verkaufsanzahl und ihr Verkaufsvolumen verfolgt.
Auf Sheet2 können Sie die 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. Sie könnten eine Formel in die erste Zelle schreiben, die die Berechnung unter Verwendung von Daten über die beiden Blätter ausführt, und sie nach unten ziehen. Das wird funktionieren.
Eine Alternative dazu ist das Erstellen eines VBA-Skripts, das entweder beim Öffnen des Arbeitsblatts oder bei Auswahl einer Befehlsschaltfläche auf dem Arbeitsblatt ausgelöst wird, sodass Sie steuern können, wann es berechnet wird. Sie können ein VBA-Skript verwenden, um alle Verkaufsdaten aus einer externen Datei trotzdem abzurufen.
Warum also nicht einfach die Berechnungen für die Bonusspalte im selben Skript auslösen?
Die Zellen funktionieren in Aktion
Wenn Sie zuvor noch nie VBA in Excel geschrieben haben, müssen Sie den Menüpunkt "Developer" aktivieren. Um dies zu tun, gehen Sie zu Datei > Optionen . Klicken Sie auf Band anpassen . Wählen Sie schließlich "Entwickler" im linken Fensterbereich, " Hinzufügen" zum rechten Fensterbereich und stellen Sie sicher, dass das Kontrollkästchen aktiviert ist.
Wenn Sie nun auf OK klicken und zum Hauptblatt zurückkehren, sehen Sie die Menüoption Entwickler.
Sie können das Menü Einfügen verwenden, um eine Befehlsschaltfläche einzufügen, oder klicken Sie auf Code anzeigen, um das Codieren zu starten.
In diesem Beispiel wird das Skript jedes Mal ausgeführt, wenn die Arbeitsmappe geöffnet wird. Klicken Sie dazu im Entwicklermenü auf Code anzeigen, und fügen Sie die folgende neue Funktion in das Codefenster ein.
Private Sub Workbook_Open() End Sub Ihr Code-Fenster 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 abrufen.
Denken Sie daran, dass die Funktion Zellen Zeilen und Spalten als Parameter hat, um jede einzelne Zelle zu identifizieren. Wir machen "x" die Zeile, verwenden Sie eine Nummer, um die Daten jeder Spalte anzufordern. Die Anzahl der Zeilen entspricht der Anzahl der Mitarbeiter, also von 1 bis 11. Die Spalten-ID beträgt 2 für Umsatz, 3 für Umsatz und 2 aus Tabelle 2 für den Feedback-Faktor.
Die endgültige Berechnung verwendet die folgenden Prozentsätze, um bis zu 100 Prozent des gesamten Bonuswerts zu addieren. Es basiert auf einer idealen Verkaufszahl von 50, einem Verkaufsvolumen von 50.000 US-Dollar und einem Feedback-Ergebnis von 10.
- (Umsatzzahl / 50) x 0, 4
- (Verkaufsvolumen / 50.000) x 0, 5
- (Feedback Punktzahl / 10) x 0, 1
Dieser einfache Ansatz gibt den Vertriebsmitarbeitern einen gewichteten Bonus. Für eine Zählung von 50, ein Volumen von $ 50.000 und eine Punktzahl von 10 erhalten sie den gesamten maximalen Bonus für den Monat. Aber alles unter perfekt auf jeden Faktor reduziert den Bonus. Alles, was besser ist als ideal, erhöht den Bonus.
Nun schauen wir uns an, wie all diese Logik in einem sehr einfachen, kurzen VBA-Skript abgerufen 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.
Wenn Sie möchten, dass die Spalte "Bonus" den tatsächlichen Dollar-Bonus anstelle des Prozentsatzes anzeigt, können Sie ihn mit dem maximalen Bonusbetrag multiplizieren. Besser noch, legen Sie diese Menge in einer Zelle auf einem anderen Blatt, und verweisen Sie es in Ihrem Code. Dies würde es einfacher machen, den Wert später zu ändern, ohne den Code bearbeiten zu müssen.
Die Schönheit der Cells-Funktion besteht darin, dass Sie eine ziemlich kreative Logik erstellen können, um Daten aus vielen Zellen über viele verschiedene Blätter hinweg zu ziehen und einige ziemlich komplexe Berechnungen mit ihnen durchzuführen.
Mit der Funktion Zellen können Sie alle möglichen Aktionen an Zellen durchführen - z. B. Löschen der Zellen, Ändern der Schriftartformatierung und vieles mehr.
Um alles zu erkunden, was Sie noch tun können, besuchen Sie die Microsoft MSDN-Seite für das Cells-Objekt.
Formatieren von Zellen mit Bereichsfunktion
Um mehrere Zellen einzeln durchlaufen zu können, ist die Zellenfunktion perfekt. Aber wenn Sie etwas gleichzeitig auf eine ganze Reihe von Zellen anwenden wollen, ist die Range-Funktion viel effizienter.
Ein Anwendungsfall hierfür könnte sein, einen Zellbereich mit Hilfe von Skripten zu formatieren, wenn bestimmte Bedingungen erfüllt sind.
Angenommen, die Summe aller Verkaufsvolumen aller Vertriebsmitarbeiter übersteigt insgesamt 400.000 US-Dollar. Sie möchten alle Zellen in der Bonusspalte grün hervorheben, um anzuzeigen, dass das Team einen zusätzlichen Teambonus verdient hat.
Lassen Sie uns einen Blick darauf werfen, wie Sie das mit einer IF-Anweisung machen können. IF-Anweisungen in Microsoft Excel verwenden IF-Anweisungen in Microsoft Excel verwenden Ob Sie ein erfahrener Experte oder ein Anfänger in einer Tabellenkalkulation sind, sollten Sie dies überprüfen Anleitung zu IF-Anweisungen in Excel. Weiterlesen .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4 End If End Sub Wenn die Zelle über dem Teamziel liegt, werden alle Zellen in diesem Bereich grün ausgefüllt.
Dies ist nur ein einfaches Beispiel für die vielen Aktionen, die Sie mit der Range-Funktion für Zellgruppen durchführen können. Andere Dinge, die Sie tun können, sind:
- Wenden Sie einen Umriss um die Gruppe an
- Überprüfen Sie die Schreibweise von Text in einem Bereich von Zellen
- Löschen, kopieren oder schneiden Sie Zellen
- Durchsuchen Sie einen Bereich mit der Methode "Suchen"
- Viel mehr
Stellen Sie sicher, dass Sie die Microsoft MSDN-Seite für das Range-Objekt lesen, um alle Möglichkeiten zu sehen.
Excel auf die nächste Ebene bringen
Jetzt, wo Sie die Unterschiede zwischen den Funktionen Cells und Range verstehen, ist es an der Zeit, Ihre VBA-Skripts auf die nächste Stufe zu bringen. Dann's Artikel über die Verwendung von Zählen und Hinzufügen von Funktionen in Excel ermöglicht es Ihnen, noch komplexere Skripts zu erstellen, die sehr schnell Werte in allen Ihren Datensätzen sammeln können.
Und wenn Sie gerade mit VBA in Excel beginnen, vergessen Sie nicht, wir haben eine fantastische Einführung in Excel VBA Die Excel-VBA-Programmierung Tutorial für Anfänger Die Excel-VBA-Programmierung Tutorial für Anfänger VBA ist ein Microsoft Office-Tool. Sie können damit Aufgaben mit Makros automatisieren, Trigger setzen und vieles mehr. Wir stellen Ihnen die visuelle Grundprogrammierung von Excel mit einem einfachen Projekt vor. Lesen Sie auch mehr für Sie.