Informationen zu Excel-Zellen im Vergleich zu Bereichsfunktionen in VBA

Excel ist mächtig. Wenn Sie es häufig verwenden, kennen Sie wahrscheinlich bereits viele Tricks, die Formeln oder die automatische Formatierung verwenden, aber die Verwendung von Zellen und Angebot Funktionen in VBA können Sie Ihre Excel-Analyse auf eine völlig neue Ebene bringen.

Das Problem bei der Verwendung der Zellen- und Bereichsfunktionen in VBA ist, dass die meisten Benutzer auf fortgeschrittenen Ebenen Schwierigkeiten haben, die Funktionsweise dieser Funktionen zu verstehen. Ihre Verwendung kann sehr verwirrend werden. So können Sie sie auf eine Weise nutzen, wie Sie sie sich wahrscheinlich nie vorstellen können.

Die Zellenfunktion

Mit den Zellen- und Bereichsfunktionen können Sie Ihrem VBA-Skript 4 mögliche Fehler mitteilen, die Sie beim Programmieren von Excel-Makros mit VBA vermeiden können. 4 Fehler, die Sie beim Programmieren von Excel-Makros mit VBA vermeiden können. Einfacher Code und Makros sind die Schlüssel zu Microsoft Excel-Superkräften. Auch Nicht-Programmierer können mit Virtual Basics for Applications (VBA) auf einfache Weise beeindruckende Funktionen in ihre Tabellenkalkulation einfügen. Vermeiden Sie einfach diese Anfängerfehler beim Programmieren! Lesen Sie genauer, wo Sie in Ihrem Arbeitsblatt abrufen möchten, oder legen Sie Daten ab. Der Hauptunterschied zwischen den beiden Zellen ist das, worauf sie sich beziehen.

Zellen Beziehen Sie sich in der Regel auf eine einzelne Zelle Angebot 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 Arbeitsblatt. Dies ist ein Beispiel, bei dem die Cells-Funktion nicht auf eine einzelne Zelle verweist:

Arbeitsblätter ("Sheet1"). Zellen

Dies verweist auf die dritte Zelle von links in der obersten Zeile. Zelle C1:

Arbeitsblätter ("Sheet1"). Zellen (3)

Der folgende Code verweist auf Zelle D15:

Arbeitsblätter ("Sheet1"). Zellen (15,4)

Wenn Sie möchten, können Sie auch mit Zelle D15 referenzieren “Zellen (15,”D”)”-Sie dürfen den Spaltenbuchstaben verwenden.

Es ist sehr flexibel, eine Zelle anhand einer Nummer für Spalte und Zelle referenzieren zu können, insbesondere bei Skripts, die dies können durchlaufen eine große Anzahl von Zellen (und führt Berechnungen an) sehr schnell aus. Wir werden darauf im Folgenden näher eingehen.

Die Bereichsfunktion

In vielerlei Hinsicht ist die Range-Funktion weitaus leistungsfähiger als die Verwendung von Zellen, da Sie auf eine einzelne Zelle oder auf einen bestimmten Zellbereich gleichzeitig referenzieren können. Sie wollen keine Range-Funktion durchlaufen, da die Bezüge für Zellen keine Zahlen sind (es sei denn, Sie binden die Cells-Funktion darin ein)..

Das Format für diese Funktion ist Bereich (Zelle # 1, Zelle # 2). Jede Zelle kann durch eine Buchstabennummer gekennzeichnet werden.

Schauen wir uns einige Beispiele an.

Hier bezieht sich die Bereichsfunktion auf Zelle A5:

Arbeitsblätter ("Sheet1"). Bereich ("A5")

Hier bezieht sich die Bereichsfunktion auf alle Zellen zwischen A1 und E20:

Arbeitsblätter ("Sheet1"). Bereich ("A1: E20")

Wie oben erwähnt, müssen Sie keine Zellenzuweisungen für Zahlen verwenden. Sie könnten tatsächlich zwei Zellenfunktionen innerhalb einer Range-Funktion verwenden, um einen Bereich auf dem Arbeitsblatt zu identifizieren.

 Mit Arbeitsblättern ("Sheet1") .Range (.Zellen (1, 1), _ .Zellen (20, 5)) Ende mit 

Der obige Code verweist auf den gleichen Bereich wie der Bereich (“A1: E20”) Funktion tut. Der Wert bei der Verwendung besteht darin, dass Sie damit Code schreiben könnten, der dynamisch mit Bereichen arbeitet, indem Schleifen verwendet werden. Funktionsweise von Do-While-Schleifen in der Computerprogrammierung Funktionsweise von Do-While-Schleifen in der Computerprogrammierung Schleifen sind eine der ersten Steuerungstypen, die Sie verwenden lerne in der Programmierung. Sie wissen wahrscheinlich, wann und für Schleifen, aber was leistet eine do-while-Schleife? Weiterlesen .

Nachdem Sie nun verstanden haben, wie Sie die Zellen- und Bereichsfunktionen formatieren, wollen wir uns näher mit der kreativen Verwendung dieser Funktionen in Ihrem VBA-Code beschäftigen.

Datenverarbeitung mit Zellenfunktion

Die Funktion Zellen ist am nützlichsten, wenn Sie über eine komplexe Formel verfügen, die Sie über mehrere Zellbereiche hinweg ausführen möchten. Diese Bereiche können auch über mehrere Blätter hinweg existieren.

Nehmen wir ein einfaches Beispiel. Angenommen, Sie führen ein Verkaufsteam von elf Mitarbeitern, und Sie möchten jeden Monat die Leistung überprüfen.

Vielleicht hast du Blatt1 das verfolgt ihre Umsatzzahlen und ihr Umsatzvolumen.

Auf Sheet2 Hier können Sie die Feedback-Bewertung der Kunden Ihres Unternehmens für die letzten 30 Tage nachverfolgen.

Wenn Sie den Bonus auf dem ersten Blatt anhand der Werte aus den beiden Blättern berechnen möchten, gibt es mehrere Möglichkeiten, dies zu tun. Sie können eine Formel in die erste Zelle schreiben, die die Berechnung mit Daten über die beiden Arbeitsblätter durchführt, und sie nach unten ziehen. Das wird funktionieren.

Eine Alternative dazu ist das Erstellen eines VBA-Skripts, das entweder ausgelöst wird, wenn Sie das Arbeitsblatt öffnen, oder durch eine 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 trotzdem alle Verkaufsdaten aus einer externen Datei abzurufen.

Warum also nicht einfach die Berechnungen für die Bonusspalte im selben Skript auslösen?

Die Zellen funktionieren in Aktion

Wenn Sie VBA noch nie in Excel geschrieben haben, müssen Sie den Menüpunkt Entwickler aktivieren. Um dies zu tun, gehen Sie zu Datei > Optionen. Klicke auf Band anpassen. Wählen Sie schließlich im linken Bereich Entwickler aus, Hinzufügen Stellen Sie sicher, dass das Kontrollkästchen aktiviert ist.

Nun, wenn Sie klicken OK Wenn Sie zum Hauptblatt zurückkehren, wird die Menüoption Entwickler angezeigt.

Du kannst den ... benutzen Einfügen Menü, um eine Befehlsschaltfläche einzufügen, oder klicken Sie einfach auf Code anzeigen um mit der Codierung zu beginnen.

In diesem Beispiel wird das Skript jedes Mal ausgeführt, 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 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 schrittweise durchgehen und mit der Funktion Zellen die drei für die Berechnung benötigten Variablen einlesen.

Denken Sie daran, dass die Zellenfunktion Zeilen und Spalten als Parameter enthält, um jede einzelne Zelle zu identifizieren. Wir machen es “x” Verwenden Sie in der Zeile eine Nummer, um die Daten jeder Spalte abzufragen. Die Anzahl der Zeilen gibt die Anzahl der Mitarbeiter an. Dies entspricht 1 bis 11. Die Spaltenbezeichnung lautet 2 für Umsatz, 3 für Umsatz und 2 aus Blatt 2 für den Feedback-Wert.

Bei der abschließenden Berechnung werden die folgenden Prozentsätze verwendet, um insgesamt 100 Prozent der gesamten Bonuspunkte zu addieren. Es basiert auf einer idealen Verkaufszahl von 50, einem Verkaufsvolumen von 50.000 USD und einem Feedback-Score von 10.

• (Umsatzanzahl / 50) x 0,4
• (Verkaufsvolumen / 50.000) x 0,5
• (Bewertungspunktzahl / 10) x 0,1

Dieser einfache Ansatz bietet Vertriebsmitarbeitern einen gewichteten Bonus. Bei einer Zählung von 50, einem Volumen von 50.000 $ und einer Punktzahl von 10 erhalten sie den gesamten maximalen Bonus für den Monat. Alles, was unter einem perfekten Faktor liegt, 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 abgerufen werden kann:

Private Sub Workbook_Open () Für x = 2 bis 12 Arbeitsblätter ("Sheet1"). Zellen (x, 4) = (Arbeitsblätter ("Sheet1"). Zellen (x, 2) .Value / 50) * 0.4 _ + (Arbeitsblätter ("Sheet1"). Zellen (x, 3) .Value / 50000) * 0,5 _ + (Arbeitsblätter ("Sheet2"). Zellen (x, 2) .Value / 10) * 0,1 _ Next x End Sub 

So wird die Ausgabe dieses Skripts aussehen.

Wenn Sie möchten, dass die Spalte "Bonus" anstelle des Prozentsatzes den tatsächlichen Dollar-Bonus anzeigt, können Sie ihn mit dem maximalen Bonusbetrag multiplizieren. Besser noch, platzieren Sie diesen Betrag in einer Zelle auf einem anderen Blatt und verweisen Sie in Ihrem Code darauf. Dies macht es später einfacher, den Wert zu ändern, ohne den Code bearbeiten zu müssen.

Das Schöne an der Cells-Funktion ist, dass Sie eine ziemlich kreative Logik erstellen können, aus der Daten abgerufen werden viele zellen über viele verschiedene Blätter, und führen Sie einige ziemlich komplexe Berechnungen mit ihnen.

Sie können mit der Funktion Zellen alle möglichen Aktionen für Zellen ausführen. Dazu gehören das Löschen der Zellen, das Ändern der Schriftformatierung und vieles mehr.

Um alles weitere zu erkunden, besuchen Sie die Microsoft MSDN-Seite für das Cells-Objekt.

Zellen mit Bereichsfunktion formatieren

Für das Durchlaufen vieler Zellen nacheinander ist die Funktion Zellen perfekt. Wenn Sie jedoch auf einen ganzen Zellbereich gleichzeitig etwas anwenden möchten, ist die Range-Funktion wesentlich effizienter.

Ein Anwendungsfall hierfür könnte das Formatieren eines Zellbereichs mithilfe von Skript sein, wenn bestimmte Bedingungen erfüllt sind.

Nehmen wir beispielsweise an, wenn die Summe aller Verkäufe über alle Vertriebsmitarbeiter hinweg insgesamt 400.000 US-Dollar übersteigt, möchten Sie alle Zellen in der Spalte „Bonus“ grün markieren, um anzuzeigen, dass das Team einen zusätzlichen Teambonus verdient hat.

Lassen Sie uns einen Blick darauf werfen, wie Sie dies mit einer IF-Anweisung tun können. Verwenden von IF-Anweisungen in Microsoft Excel Verwenden von IF-Anweisungen in Microsoft Excel Unabhängig davon, 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 dies der Fall ist und sich die Zelle über dem Teamziel befindet, werden alle Zellen im Bereich grün gefüllt.

Dies ist nur ein einfaches Beispiel für die vielen Aktionen, die Sie mit der Range-Funktion für Zellgruppen ausführen können. Andere Dinge, die Sie tun können, sind:

• Wenden Sie eine Gliederung um die Gruppe an
• Überprüfen Sie die Schreibweise von Text innerhalb eines Zellbereichs
• Zellen löschen, kopieren oder ausschneiden
• Durchsuchen Sie einen Bereich mit der “Finden” Methode
• Viel mehr

Lesen Sie 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-Scripting auf die nächste Stufe zu bringen. Dann's Artikel über die Verwendung der Funktionen zum Zählen und Hinzufügen von Funktionen in Excel ermöglicht Ihnen die Erstellung noch komplexerer Skripts, die sehr schnell Werte über alle Ihre Datensätze hinweg akkumulieren können.

Und wenn Sie gerade erst mit VBA in Excel beginnen, sollten Sie nicht vergessen, dass wir eine fantastische Einführung in Excel VBA haben. Das Excel VBA-Lernprogramm für Anfänger Das Excel VBA-Lernprogramm für Anfänger VBA ist ein Power-Tool von Microsoft Office. Sie können damit Aufgaben mit Makros automatisieren, Trigger setzen und vieles mehr. Wir führen Sie mit einem einfachen Projekt in die Visual Basic-Programmierung von Excel ein. Lesen Sie auch mehr für Sie.

.