Ajouter les valeurs de la feuille de calcul précédente ou suivante en utilisant VBA dans Microsoft Excel
Dans cet article, nous allons créer une fonction personnalisée pour calculer la somme des valeurs dans la plage de différentes feuilles.
Nous allons créer trois fonctions personnalisées différentes qui calculeront la somme des valeurs dans la plage d’autres feuilles, en fonction du nom de la feuille, du numéro de feuille et de la référence de la feuille active.
Les données brutes pour cet exemple se composent de quatre feuilles. Chaque feuille contenant des détails sur le nombre d’articles vendus par chaque membre de l’équipe dans la plage B7 à B16 pour un jour particulier. Nous avons pris les données pour le 1 ^ er ^ jour, 2 ^ nd ^, 3 ^ rd ^ et le dernier jour du mois.
Explication logique
Dans cet exemple, nous avons créé trois fonctions personnalisées qui sont: -. SumBySheetName. SumBySheetNumber. SumByOffsetSheetNumber. SumBySheetName: – Cette fonction personnalisée prend la plage et le nom de la feuille comme paramètres d’entrée. Il renvoie la somme des valeurs dans la plage définie du nom de feuille défini.
-
SumBySheetNumber: – Cette fonction personnalisée prend la plage et le numéro de feuille comme paramètres d’entrée. Il renvoie la somme des valeurs de la plage définie pour le numéro de feuille défini.
-
SumByOffsetSheetNumber: – Cette fonction prend la plage et le décalage de la feuille active comme paramètres d’entrée. Il renvoie la somme des valeurs dans la plage définie de la feuille définie par l’index de décalage.
Chaque feuille du classeur affiche le nombre total d’articles vendus le premier jour, le jour précédent, le jour en cours, le jour suivant et le dernier jour.
Si vous examinez de près la fonction personnalisée, vous constaterez que les quatre feuilles utilisent la même fonction personnalisée avec les mêmes paramètres.
La fonction personnalisée «SumBySheetNumber» est utilisée pour calculer le total des articles vendus le premier jour, car la première feuille contiendra toujours les données du premier jour du mois.
La fonction personnalisée «SumByOffsetSheetNumber» est utilisée pour calculer le total des articles vendus le jour précédent, en cours et le jour suivant, car les feuilles du classeur sont organisées par ordre de dates.
La fonction personnalisée «SumBySheetName» est utilisée pour calculer le total des articles vendus le dernier jour du mois, car le nom de la feuille du dernier jour sera toujours «LastDayOfMonth».
Explication du code
WorksheetFunction.Sum L’objet WorksheetFunction est utilisé pour accéder aux fonctions de feuille Excel à partir de Visual Basic. Nous avons utilisé la fonction SOMME de la feuille Excel pour ajouter les valeurs de la plage.
InputRange.Address Le code ci-dessus est utilisé pour renvoyer l’adresse de la plage spécifiée par l’objet de plage «InputRange».
Veuillez suivre ci-dessous pour le 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
Si vous avez aimé ce blog, partagez-le avec vos amis sur Facebook et Facebook.
Nous aimerions avoir de vos nouvelles, faites-nous savoir comment nous pouvons améliorer notre travail et le rendre meilleur pour vous. Écrivez-nous à [email protected]