In Werte aus dem vorherigen oder nächsten Arbeitsblatt mit VBA in Microsoft Excel
In diesem Artikel erstellen wir eine benutzerdefinierte Funktion zur Berechnung der Wertesumme im Bereich verschiedener Blätter.
Wir werden drei verschiedene benutzerdefinierte Funktionen erstellen, die die Summe der Werte im Bereich anderer Blätter basierend auf dem Blattnamen, der Blattnummer und der Blattreferenz aus dem aktiven Blatt berechnen.
Die Rohdaten für dieses Beispiel bestehen aus vier Blättern. Jedes Blatt enthält Angaben zur Anzahl der von jedem Teammitglied verkauften Artikel im Bereich von B7 bis B16 für einen bestimmten Tag. Wir haben Daten für den 1. Tag, den 2., den 3. und den letzten Tag des Monats genommen.
Logische Erklärung
In diesem Beispiel haben wir drei benutzerdefinierte Funktionen erstellt: -. SumBySheetName. SumBySheetNumber. SumByOffsetSheetNumber. SumBySheetName: – Diese benutzerdefinierte Funktion verwendet den Bereich und den Blattnamen als Eingabeparameter. Es gibt die Summe der Werte im definierten Bereich des definierten Blattnamens zurück.
-
SumBySheetNumber: – Diese benutzerdefinierte Funktion verwendet den Bereich und die Blattnummer als Eingabeparameter. Es gibt die Summe der Werte im definierten Bereich für die definierte Blattnummer zurück.
-
SumByOffsetSheetNumber: – Diese Funktion verwendet den Bereich und den Versatz des aktiven Blattes als Eingabeparameter. Es gibt die Summe der Werte im definierten Bereich des durch den Versatzindex definierten Blattes zurück.
Jedes Blatt in der Arbeitsmappe zeigt die Gesamtzahl der verkauften Artikel am ersten Tag, am vorherigen Tag, am aktuellen Tag, am nächsten Tag und am letzten Tag.
Wenn Sie sich die benutzerdefinierte Funktion genauer ansehen, werden Sie feststellen, dass alle vier Blätter dieselbe benutzerdefinierte Funktion mit denselben Parametern verwenden.
Die benutzerdefinierte Funktion „SumBySheetNumber“ wird verwendet, um die Gesamtzahl der am ersten Tag verkauften Artikel zu berechnen, da das erste Blatt immer Daten des ersten Tages des Monats enthält.
Die benutzerdefinierte Funktion „SumByOffsetSheetNumber“ wird verwendet, um die Gesamtzahl der am vorherigen, aktuellen und nächsten Tag verkauften Artikel zu berechnen, da die Blätter in der Arbeitsmappe in Datumsreihenfolge angeordnet sind.
Die benutzerdefinierte Funktion „SumBySheetName“ wird verwendet, um die Gesamtzahl der am letzten Tag des Monats verkauften Artikel zu berechnen, da der Blattname des letzten Tages immer „LastDayOfMonth“ lautet.
Code Erklärung
WorksheetFunction.Sum WorksheetFunction-Objekt wird verwendet, um über Visual Basic auf Excel-Blattfunktionen zuzugreifen. Wir haben die SUMME-Funktion der Excel-Tabelle verwendet, um die Werte im Bereich hinzuzufügen.
InputRange.Address Der obige Code wird verwendet, um die Adresse des angegebenen Bereichs durch das Bereichsobjekt „InputRange“ zurückzugeben.
Bitte folgen Sie unten für den Code
Option Explicit Function SumBySheetName(InputRange As Range, Optional SheetName As Variant) 'Declaring variable Dim SheetIn As Worksheet 'Checking whether value is assigned to optional parameter 'IF optional parameter is missing then generate sum for range in the active sheet If IsMissing(SheetName) Then SumBySheetName = WorksheetFunction.Sum(InputRange) Exit Function End If 'Looping through sheets in the worksheets collection For Each SheetIn In Worksheets 'Checking whether specified sheet exist in the worksheets collection If SheetName = SheetIn.Name Then 'Calculating the sum for specified range of the specified sheet SumBySheetName = WorksheetFunction.Sum(Worksheets(SheetName).Range(InputRange.Address)) Exit Function End If Next SumBySheetName = "Specified Sheet doesn't exist in the workbook" End Function Function SumBySheetNumber(InputRange As Range, Optional SheetIndex As Integer = 0) 'Checking whether optional parameter is missing or sheet index is assigned zero value If SheetIndex = 0 Then SumBySheetNumber = WorksheetFunction.Sum(InputRange) 'Checking whether sheet index is greater than number of sheets in the workbook ElseIf SheetIndex > Sheets.Count Then SumBySheetNumber = "Sheet Index is greater than number of sheets in the workbook" Else SumBySheetNumber = WorksheetFunction.Sum(Worksheets(SheetIndex).Range(InputRange.Address)) End If End Function Function SumByOffsetSheetNumber(InputRange As Range, Optional SheetOffset As Integer = 0) 'Error Handling On Error GoTo Last 'Calculating sum SumByOffsetSheetNumber = WorksheetFunction.Sum(Worksheets(InputRange.Worksheet.Index + _ SheetOffset).Range(InputRange.Address)) Exit Function Last: SumByOffsetSheetNumber = "Referred Sheet not exists" End Function
Wenn dir dieser Blog gefallen hat, teile ihn mit deinen Freunden auf Facebook und Facebook.
Wir würden gerne von Ihnen hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern und für Sie verbessern können. Schreiben Sie uns unter [email protected]