Введение

Одной из задач, реализуемых с помощью сводных таблиц в MS Excel, является детализация итоговых данных по интересующим показателям. Однако, встречаются случаи, когда отсутствует возможность вносить в  аналитическую таблицу дополнительную справочную информацию. Дополнительная информация позволила бы аналитику сформировать  сведения, недостижимые  только с помощью сводной таблицы.
Таким случаем может являться аналитическая таблица с календарным графиком выполненных работ производственного цеха. В частности, руководству предприятия в рамках анализа финансовой отчетности может потребоваться детальная оценка стоимости этих работ. В настоящей статье анализируется возможность решения этой проблемы с помощью пользовательских функций VBA Excel.

1. Постановка задачи

В состав исходных данных для решения задачи оценки стоимости слесарных работ входят две таблицы:

(1) Аналитическая таблица выполненных слесарных операций в формате календарного графика. В таблице по трем строкам (для каждого работника) размещены сведения по видам операций, которые выполнял работник в каждый отчетный день. Количество выделенных строк обусловлено практикой выполнения не более трёх операций в день. Фрагмент аналитической таблицы приведен на Рис. 1.

Рис. 1 – Календарный график операций

(2) Тарифная сетка на выполненные работы. В связи с тем, что работник может выполнить от одной (норматив) до 3-х операций в день, тарифная сетка включает в себя коэффициент переработки, который пересчитывает норматив каждого работника в суточную стоимость всех выполненных им операций (см. на Рис. 2).

Рис. 2 – Тарифная сетка работников

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

2. Автоматизация оценки стоимости работ

Автоматизация оценки стоимости работ была реализована с помощью пользовательских функций (udf – User Defined Function), привязанных к ФИО работников и выполненным операциям.

В целях повышения информативности анализа стоимости работ были разработаны два вида пользовательских функций:

Разработка udf VBA Excel по расчету стоимости работ

Это пользовательские функции, определяющие стоимость операций работников за период. Код соответствующей udf приведен ниже.

 
' пользовательская функция SumOrdersByPerson для расчета стоимости операций по ФИО

Option Explicit
Public Function SumOrdersByPerson(UserName As String, Task As String) As Variant
Application.Volatile True
Application.ScreenUpdating = False
' Task - название операции
' UserName - ФИО работника

Dim rCell, cCell, dCell As Range ' текущая ячейка
Dim i, j, NTasks As Long ' переменная цикла

Dim EndRow, LastColumn As Long
Dim TargetRow As Long
Dim Tariff As Variant
Dim s, stotal As Variant

Dim TariffQty As Long
Dim TariffQtyCell As Range
Dim WSInputData As Worksheet
Dim WSActiveSheet As Worksheet
Dim TariffSheet As Worksheet

Dim DateCell, DateRange As Range
Dim OrdersQty As Integer
Dim TargetDateCell As Range
Dim TariffCell As Range
Dim UserNameCell As Range
Dim TaskRange As Range

Set TariffSheet = ActiveWorkbook.Worksheets("Тарифы")
Set WSInputData = ActiveWorkbook.Worksheets("Таблица (исх данные)")
Set WSActiveSheet = ActiveWorkbook.ActiveSheet

EndRow = WSInputData.Cells(WSInputData.Rows.Count, 1).End(xlUp).Row ' номер последней строки в A ' число строк в столбце данных
LastColumn = WSInputData.Rows(2).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column ' номер последнего столбца данных во 2 строке

' поиск работника в таблице "Таблица (исх данные)"
 
 Set UserNameCell = WSInputData.Range(WSInputData.Cells(2, 1), WSInputData.Cells(EndRow, 1)).Find(UserName) ' найдена ячейка с ФИО
 TargetRow = UserNameCell.Row ' найден номер строки ФИО
 
 ' ищем задания по всем столбцам диапазона из 3-х строк для выбранного ФИО
 stotal = 0

 For j = 2 To LastColumn ' цикл по столбцам
 
 With WSInputData
 Set TaskRange = .Range(.Cells(TargetRow, j), .Cells(TargetRow + 2, j)) ' определяем диапазон поиска операций
 End With 
 
 s = 0 
 For Each cCell In TaskRange.Cells
 ' находит первую из ФИО и анализирует 3 строки зарезервированных для каждого ФИО
 
 If cCell.Value Like Task Then ' если ячейка содержит название операции 
 TariffQty = 0 ' начальное значение количества операций
 For Each dCell In TaskRange.Cells ' для каждой ячейки в диапазоне
 If dCell.Text Like Task Then ' если ячейка содержит название операции
 TariffQty = TariffQty + 1 ' расчет числа заданной операции в день
 Else
 End If
 Next dCell 
 
 ' находим ФИО в таблице "тарифы"
 Set TariffCell = TariffSheet.Range("C4:C12").Find(UserName) ' найдена ячейка с ФИО
 ' тариф зависит от коэффициента перевыполнения суточного задания, определяем его по таблице
 
 
 If TariffQty = 1 Then
 Tariff = TariffCell.Offset(0, 2).Value
 ElseIf TariffQty = 2 Then
 Tariff = TariffCell.Offset(0, 3).Value
 ElseIf TariffQty = 3 Then
 Tariff = TariffCell.Offset(0, 4).Value
 Else
 Tariff = TariffCell.Offset(0, 1).Value
 End If
 
 ' тариф определен, наращиваем стоимость операций
 
 ' s = s + Tariff
 s = Tariff ' суточная стоимость заданной операции 
 Else ' если ячейка не содержит название операции
 End If
 Next cCell ' следующая ячейка диапазона
 
 stotal = stotal + s ' стоимость заданной операции за период нарастающим итогом
 Next j ' следующий столбец
 
 SumOrdersByPerson = stotal ' стоимость заданной операции за период
 Application.ScreenUpdating = True 
 End Function

Разработка udf VBA Excel по расчету количества работ Это справочные пользовательские функции, определяющие количество операций работников за период. Код соответствующей udf приведен ниже.

 
' пользовательская функция SumOrdersByPerson для расчета количества операций по ФИО

Option Explicit
Public Function OrdersQtyByPerson(UserName As String, Task As String) As Variant
Application.Volatile True
Application.ScreenUpdating = False
' Task - название операции
' UserName - ФИО работника

Dim cCell, dCell As Range ' текущая ячейка
Dim i As Long ' переменная цикла

Dim EndRow, LastColumn As Long
Dim TargetRow As Long
Dim Tariff As Variant
Dim TariffQty As Double

Dim WSInputData As Worksheet
Dim WSActiveSheet As Worksheet
Dim TariffSheet As Worksheet
Dim UserNameCell As Range
Dim TaskRange As Range

Set TariffSheet = ActiveWorkbook.Worksheets("Тарифы")
Set WSInputData = ActiveWorkbook.Worksheets("Таблица (исх данные)")
Set WSActiveSheet = ActiveWorkbook.ActiveSheet

EndRow = WSInputData.Cells(WSInputData.Rows.Count, 1).End(xlUp).Row ' номер последней строки в A ' число строк в столбце данных
LastColumn = WSInputData.Rows(2).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column ' номер последнего столбца данных во 2 строке

' поиск работника в таблице "Таблица (исх данные)"
 
 Set UserNameCell = WSInputData.Range(WSInputData.Cells(2, 1), WSInputData.Cells(EndRow, 1)).Find(UserName) ' найдена ячейка с ФИО
 TargetRow = UserNameCell.Row ' найден номер строки ФИО
 
 ' ищем задания по всем столбцам диапазона из 3-х строк для выбранного ФИО 
 
 With WSInputData
 Set TaskRange = .Range(.Cells(TargetRow, 2), .Cells(TargetRow + 2, LastColumn)) ' определяем диапазон поиска операций
 End With 
 
 For Each cCell In TaskRange.Cells
 ' находит первую из ФИО и анализирует 3 строки зарезервированных для каждого ФИО
 
 
 If cCell.Value Like Task Then ' если ячейка содержит название операции
 TariffQty = TariffQty + 1 ' расчет числа заданных операций в день
 
 Else ' если ячейка не содержит название операции
 End If
 
 Next cCell ' следующая ячейка диапазона
 
 OrdersQtyByPerson = TariffQty ' число заданной операции за период
 Application.ScreenUpdating = True
 End Function

3. Результаты расчетов стоимости работ Фрагмент результатов расчетов пользовательских функций SumOrdersByPerson(UserName As String, Task As String) и OrdersQtyByPerson(UserName As String, Task As String) представлен на Рис. 3.

Рис. 3 – Фрагмент расчета пользовательскими функциями стоимости и количества операций за период

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

Изменения в таблице исходных данных вызовут перерасчет результирующей таблицы.

Информация будет полезна аналитикам, которые изучают VBA Excel, а также аналитикам баз данных.