В этой статье мы создадим пользовательскую функцию для вычисления суммы значений в диапазоне разных листов.

Мы создадим три разные пользовательские функции, которые будут вычислять сумму значений в диапазоне других листов на основе имени листа, номера листа и ссылки на лист из активного листа.

Исходные данные для этого примера состоят из четырех листов. Каждый лист содержит подробную информацию о количестве товаров, проданных каждым членом команды в диапазоне от B7 до B16 за определенный день. Мы взяли данные за 1-й день, 2-й день, 3-й день и последний день месяца.

ArrowFirstDay

Логическое объяснение

В этом примере мы создали три пользовательские функции: -. SumBySheetName. SumBySheetNumber. SumByOffsetSheetNumber. SumBySheetName: — эта настраиваемая функция принимает диапазон и имя листа в качестве входных параметров. Он возвращает сумму значений в заданном диапазоне определенного имени листа.

  1. SumBySheetNumber: — эта настраиваемая функция принимает диапазон и номер листа в качестве входных параметров. Он возвращает сумму значений в заданном диапазоне для определенного номера листа.

  1. SumByOffsetSheetNumber: — Эта функция принимает диапазон и смещение от активного листа в качестве входных параметров. Он возвращает сумму значений в заданном диапазоне листа, определяемом индексом смещения.

На каждом листе книги показано общее количество проданных товаров в первый, предыдущий, текущий, следующий и последний день.

Если вы внимательно посмотрите на настраиваемую функцию, вы обнаружите, что все четыре листа используют одну и ту же настраиваемую функцию с одинаковыми параметрами.

Пользовательская функция SumBySheetNumber используется для подсчета общего количества товаров, проданных в первый день, так как на первом листе всегда будут данные первого дня месяца.

Пользовательская функция SumByOffsetSheetNumber используется для расчета общего количества проданных товаров в предыдущий, текущий и следующий день, поскольку листы в рабочей книге расположены в последовательности дат.

Пользовательская функция SumBySheetName используется для подсчета общего количества товаров, проданных в последний день месяца, так как название листа в последний день всегда будет «LastDayOfMonth».

ArrowSecondDay

ArrowThirdDay

ArrowLastDay

Объяснение кода

WorksheetFunction.Sum Объект WorksheetFunction используется для доступа к функциям листов Excel из Visual Basic. Мы использовали функцию SUM листа Excel, чтобы сложить значения в диапазоне.

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]