この記事では、さまざまなシートの範囲の値の合計を計算するカスタム関数を作成します。

アクティブなシートからのシート名、シート番号、およびシート参照に基づいて、他のシートの範囲の値の合計を計算する3つの異なるカスタム関数を作成します。

この例の生データは4枚のシートで構成されています。各シートには、特定の日にB7からB16の範囲で各チームメンバーが販売したアイテムの数の詳細が含まれています。 1 ^ st ^日、2 ^ nd ^、3 ^ rd ^、および月末のデータを取得しました。

ArrowFirstDay

ロジックの説明

この例では、次の3つのカスタム関数を作成しました。 SumBySheetName。 SumBySheetNumber。 SumByOffsetSheetNumber。 SumBySheetName:-このカスタム関数は、範囲とシート名を入力パラメーターとして受け取ります。定義されたシート名の定義された範囲の値の合計を返します。

。 SumBySheetNumber:-このカスタム関数は、範囲とシート番号を入力パラメーターとして受け取ります。定義されたシート番号の定義された範囲の値の合計を返します。

。 SumByOffsetSheetNumber:-この関数は、アクティブシートからの範囲とオフセットを入力パラメーターとして受け取ります。オフセットインデックスで定義されたシートの定義された範囲の値の合計を返します。

ワークブックの各シートには、初日、前日、当日、翌日、最終日に販売されたアイテムの合計が表示されます。

カスタム関数をよく見ると、4つのシートすべてが同じパラメーターで同じカスタム関数を使用していることがわかります。

「SumBySheetNumber」カスタム関数は、最初のシートが常に月の最初の日のデータを運ぶため、最初の日に販売されたアイテムの合計を計算するために使用されます。

「SumByOffsetSheetNumber」カスタム関数は、ワークブックのシートが日付順に配置されているため、前日、当日、翌日に販売されたアイテムの合計を計算するために使用されます。

最終日のシート名は常に「LastDayOfMonth」であるため、「SumBySheetName」カスタム関数を使用して、その月の最終日に販売されたアイテムの合計を計算します。

ArrowSecondDay

ArrowThirdDay

ArrowLastDay

コードの説明

WorksheetFunction.Sum WorksheetFunctionオブジェクトは、VisualBasicからExcelシート関数にアクセスするために使用されます。 ExcelシートのSUM関数を使用して、範囲内の値を追加しました。

InputRange.Address上記のコードは、「InputRange」範囲オブジェクトによって指定された範囲のアドレスを返すために使用されます。

コードについては以下に従ってください

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

このブログが気に入ったら、FacebookやFacebookで友達と共有してください。

皆様からのご意見をお待ちしております。私たちの仕事を改善し、あなたのために改善する方法をお知らせください。 [email protected]までご連絡ください