Введение
Одной из задач, реализуемых с помощью сводных таблиц в 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, а также аналитикам баз данных.