Главная
Автоматизация оценки стоимости работ методами VBA Excel
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 301
Введение
Одной из задач, реализуемых с помощью сводных таблиц в 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, а также аналитикам баз данных.
Построение модели экспресс визуализации графика аренды автомобилей методами MS Excel
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 312
Настоящая статья исследует вопрос экспресс визуализации данных по аренде нескольких автомобилей.
Построение модели визуализации процессов в Excel включает в себя: (1) настройку шкалы календарного графика формулами MS Excel и (2) применение пользовательских функций MS Excel.
Подробнее: Построение модели экспресс визуализации графика аренды автомобилей методами MS Excel
Применение комбинированного метода MS Excel для расчета дохода дилера
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 295
Подробнее: Применение комбинированного метода MS Excel для расчета дохода дилера
Вариант пересчета срока исполнения заявок в формат 9 часового рабочего дня методами MS Excel
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 389
Автоматизация заполнения расписания морских грузоперевозок
- Информация о материале
- Автор: Никита Дворец
- Категория: Microsoft Excel
- Просмотров: 614
Автоматизация заполнения расписания морских грузоперевозок методами VBA Excel
Подробнее: Автоматизация заполнения расписания морских грузоперевозок
Если у вас слишком много ЕСЛИ)
- Информация о материале
- Автор: Serge 007
- Категория: Microsoft Excel
- Просмотров: 815
Функция ЕСЛИ() - одна из самых часто используемых в Excel. Однако в одной ЕСЛИ() можно задать не более двух вариантов "ветвления" (да/нет). И если для новичков является сложным разобраться с тем, как в ЕСЛИ() увеличить количество вариантов, то для пользователей, обладающих опытом применения этой функции, возникает прямо противоположный вопрос - как избавиться от большого числа вложенных ЕСЛИ().
Программирование в Excel VBA: с чего начать
- Информация о материале
- Автор: Serge 007
- Категория: Microsoft Excel
- Просмотров: 36767
VBA (Visual Basic for Applications) – разновидность языка программирования Visual Basic, включенная в пакет программ Microsoft Office. Знание VBA облегчает выполнение рутинных задач на компьютере, в частности в Excel. К примеру, вам нужно назначить гиперссылки на слово, которое повторяется 80 раз. Создание макрокоманд (макросов) в VBA автоматизирует этот процесс и оставит здоровыми нервы.Язык программирования VBA легок в освоении и может использоваться обычными пользователями. Единственное, что вам потребуется – свободное время и знание Excel.
Подготовка документа Excel к печати
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 37303
Пользователи Excel часто сталкиваются с проблемой печати громоздких таблиц на принтере. Будем вместе разбираться с этой задачей.
Заполнение справочника 1C данными из Excel через механизм OLE
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 40385
Система 1С позволяет гибко интегрировать приложения,написанные в этой среде с другими приложениями, например Microsoft Office. Имеется возможность как обращаться ко внешним приложениям из кода 1С, так и наоборот, из внешних приложений к 1С. Рассмотрим пример обращения к 1С из эксель с помощью макросов VBA через механизм OLE.
Подробнее: Заполнение справочника 1C данными из Excel через механизм OLE
Архив форума
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 30116
Решение задач на оптимизацию с помощью MS Excel
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 150123
Надстройка MS Excel "Поиск решений" позволяет решать широкий круг задач на оптимизацию. Думаю, многие посетители сайта изучали в институте линейное программирование или исследования операций. "Поиск решений" в Excel позволяет в считанные секунды находить оптимальные решения достаточно сложных моделей, кстати не только линейных, без знания алгоритмов, макросов, формул и длительных рутинных итерраций.
Коллекция алгоритмов. Массовое исправление ошибок #ДЕЛ/0!, #Н/Д! с помощью макросов
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 81658
Эта заметка открывает серию статей, посвященных разбору полезных алгоритмов (макросов, функций) для эффективной работы в MS Office.
Подробнее: Коллекция алгоритмов. Массовое исправление ошибок #ДЕЛ/0!, #Н/Д! с помощью макросов
Первые шаги в использовании макросов VBA в Excel
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 232518
Использование макросов на языке программирования Visual Basic очень облегчает жизнь пользователям Excel и делает из этого пакета поистине мощную среду для решения широкого круга экономических и технических задач. Изучение макросов эксел лучше всего начать, как ни странно, с создания своего первого собственного макроса.
Окно контрольного значения в Excel
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 66863
Суть инструмента проста. Выбираем любую ячейку, добавляем ее в список контрольных значений и видим ее значение в специальном окне.
Условное форматирование ячеек Excel
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 97440
Условное форматирование позволяет в зависимости от значений ячеек эксел изменять их числовой формат и оформление.
Списки-2 или Федот, да не тот
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 90912
Почти все предыдущие темы нашего курса посвящены работе со списками.
Вы будете смеяться, но в русском переводе Excel словом «список» обозначается два совершенно разных понятия.
Текстовые функции в Excel. Часть 4.
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 117517
В завершение темы обработки текста в Excel поговорим о функциях форматирования и преобразования значений ячеек.
Текстовые функции в Excel. Часть 3.
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 77687
Еще одна группа текстовых функций рабочего листа Excel - функции для преобразования регистра букв.
Текстовые функции в Excel. Часть 2.
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 123529
Сегодня поговорим о функциях для поиска и замены части текстовой строки с помощью функций рабочего листа Excel.
Текстовые функции в Excel
- Информация о материале
- Автор: Алексей Шмуйлович
- Категория: Microsoft Excel
- Просмотров: 70815
В продолжение темы обработки текста в Excelрассмотрим использование для этих целей текстовых функций.
- Написание дробных чисел в Excel'е
- Обработка текстовых данных в Excel
- Суммирование по критерию
- Присоединение автофигуры к ячейке Excel
- Комментарий в формуле Excel
- Число или сумма прописью
- Функции для работы с базами данных
- Полезные сочетания клавиш. Работа с ячейками.
- Хитрости использования ссылок на ячейки в Excel
- Условное форматирование с использованием пользовательских функций
- Знакомтесь, Microsoft Office Excel 2007!
- Сводные таблицы и диаграммы в MS Excel
- Итоги
- Как скрыть нулевые значения?
- Автофильтр
- Сортировка списка
- Еще про перенос слов. Разрыв строки внутри ячейки.
- Перенос текста в ячейках
- Функция =ДВССЫЛ()
- Группа и структура
- Функция =ВПР()
- Функция =ЕСЛИ()
- Знак & (амперсанд)
- Настройка панелей инструментов.
- Работа со списками.
- Представления.
- Как извлечь корень n-ной степени с помощью Excel?
Страница 1 из 3