- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 29882
- Информация о материале
- Автор: Serge 007
- Категория: Microsoft Excel
- Просмотров: 379
Функция ЕСЛИ() - одна из самых часто используемых в Excel. Однако в одной ЕСЛИ() можно задать не более двух вариантов "ветвления" (да/нет). И если для новичков является сложным разобраться с тем, как в ЕСЛИ() увеличить количество вариантов, то для пользователей, обладающих опытом применения этой функции, возникает прямо противоположный вопрос - как избавиться от большого числа вложенных ЕСЛИ().
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 306
Автоматизация заполнения расписания морских грузоперевозок методами VBA Excel
Подробнее: Автоматизация заполнения расписания морских грузоперевозок
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 124
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 71
Подробнее: Применение комбинированного метода MS Excel для расчета дохода дилера
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 50
Настоящая статья исследует вопрос экспресс визуализации данных по аренде нескольких автомобилей.
Построение модели визуализации процессов в Excel включает в себя: (1) настройку шкалы календарного графика формулами MS Excel и (2) применение пользовательских функций MS Excel.
Подробнее: Построение модели экспресс визуализации графика аренды автомобилей методами MS Excel
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 15
Введение
Одной из задач, реализуемых с помощью сводных таблиц в 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, а также аналитикам баз данных.