Dieses Tutorial zeigt Ihnen, wie Sie die Excel-Funktionen SUMIF und SUMIFS in VBA verwenden
VBA hat kein Äquivalent zu den SUMIF- oder SUMIFS-Funktionen, die Sie verwenden können - ein Benutzer muss die integrierten Excel-Funktionen in VBA mit dem ArbeitsblattFunktion Objekt.
SUMIF-Arbeitsblattfunktion
Das WorksheetFunction-Objekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfeld Funktion einfügen in Excel verfügbar sind. Die SUMIF-Funktion ist eine davon.
| 123 | Sub TestSumIf()Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))End Sub |
Das obige Verfahren addiert die Zellen in Range (D2: D9) nur, wenn die entsprechende Zelle in Spalte C = 150 ist.
Zuweisen eines SUMIF-Ergebnisses zu einer Variablen
Möglicherweise möchten Sie das Ergebnis Ihrer Formel an anderer Stelle im Code verwenden, anstatt es direkt in den Excel-Bereich zurückzuschreiben. In diesem Fall können Sie das Ergebnis einer Variablen zuweisen, die später in Ihrem Code verwendet wird.
| 1234567 | Sub AssignSumIfVariable()Ergebnis als Double dimmen'Variable zuweisenresult = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))'Ergebnis anzeigenMsgBox "Die Summe des Ergebnisses, das dem 150-Verkaufscode entspricht, ist " & resultEnd Sub |
Verwenden von SUMIFS
Die SUMIFS-Funktion ähnelt der SUMIF-Arbeitsblattfunktion, ermöglicht jedoch die Überprüfung auf mehr als ein Kriterium. Im folgenden Beispiel möchten wir den Verkaufspreis addieren, wenn der Verkaufscode 150 ist UND der Einstandspreis größer als 2 ist. Beachten Sie, dass in dieser Formel der zu addierende Zellenbereich vor den Kriterien liegt, während in der SUMIF-Funktion ist es hinten.
| 123 | Sub MultipleSumIfs()Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")End Sub |
Verwenden von SUMIF mit einem Range-Objekt
Sie können dem Range-Objekt eine Gruppe von Zellen zuweisen und dieses Range-Objekt dann mit dem ArbeitsblattFunktion Objekt.
| 123456789101112 | Unter TestSumIFRange()Dim rngCriteria As RangeDim rngSum als Bereich'Zellbereich zuweisenSet rngCriteria = Range("C2:C9")Setze rngSum = Range("D2:D9")'benutze den Bereich in der FormelRange("D10") = WorksheetFunction.SumIf(rngCriteria, 150, rngSum)'die Reichweitenobjekte freigebenSetze rngCriteria = NichtsSetze rngSum = NichtsEnd Sub |
Verwenden von SUMIFS für mehrere Bereichsobjekte
Ebenso können Sie SUMIFS für mehrere Range-Objekte verwenden.
| 123456789101112131415 | Unter TestSumMultipleRanges()Dim rngCriteria1 als BereichDim rngCriteria2 als BereichDim rngSum als Bereich'Zellbereich zuweisenSet rngCriteria1= Range("C2:C9")Set rngCriteria2 = Range("E2:E10")Setze rngSum = Range("D2:D10")'benutze die Bereiche in der FormelRange("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")'Entferne das EntfernungsobjektSetze rngCriteria1 = NichtsSetze rngCriteria2 = NichtsSetze rngSum = NichtsEnd Sub |
Beachten Sie, dass das Kriterium größer als 2 in Klammern stehen muss, da Sie ein Größer-als-Zeichen verwenden.
SUMIF-Formel
Wenn Sie das verwenden ArbeitsblattFunktion.SUMIF Um einem Bereich in Ihrem Arbeitsblatt eine Summe hinzuzufügen, wird eine statische Summe zurückgegeben, keine flexible Formel. Das bedeutet, wenn sich Ihre Zahlen in Excel ändern, wird der Wert, der von der ArbeitsblattFunktion wird sich nicht ändern.
Im obigen Beispiel hat die Prozedur Range(D2:D9) addiert, wobei der SaleCode in Spalte C gleich 150 ist, und das Ergebnis wurde in D10 eingetragen. Wie Sie in der Bearbeitungsleiste sehen können, handelt es sich bei diesem Ergebnis um eine Zahl und nicht um eine Formel.
Wenn sich einer der Werte in Range(D2:D9) oder Range(C2:D9) ändert, wird das Ergebnis in D10 NICHT Veränderung.
Anstatt die WorksheetFunction.SumIf, können Sie mit VBA eine SUMIF-Funktion auf eine Zelle anwenden, indem Sie die Formel oder FormelR1C1 Methoden.
Formelmethode
Mit der Formelmethode können Sie gezielt auf einen Zellbereich zeigen, zB: D2:D10, wie unten gezeigt.
| 123 | Sub TestSumIf()Range("D10").FormulaR1C1 = "=SUMIF(C2:C9,150,D2:D9)"End Sub |
FormelR1C1-Methode
Die Methode FormulaR1C1 ist flexibler, da sie Sie nicht auf einen bestimmten Zellbereich einschränkt. Das folgende Beispiel gibt uns die gleiche Antwort wie das obige.
| 123 | Sub TestSumIf()Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C )"End Sub |
Um die Formel jedoch flexibler zu gestalten, könnten wir den Code so ändern, dass er wie folgt aussieht:
| 123 | Sub TestSumIf()ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"End Sub |
Wo auch immer Sie sich in Ihrem Arbeitsblatt befinden, die Formel addiert dann die Zellen, die die Kriterien direkt darüber erfüllen, und fügt die Antwort in Ihre ActiveCell ein. Auf den Bereich innerhalb der SUMIF-Funktion muss mit der Zeilen- (R) und Spalte (C)-Syntax verwiesen werden.
Mit beiden Methoden können Sie dynamische Excel-Formeln in VBA verwenden.
In D10 gibt es jetzt eine Formel anstelle eines Wertes.
