Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

Главная

Информация о материале
Категория: 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, а также аналитикам баз данных.

 

Информация о материале
Категория: Microsoft Excel
Просмотров: 312

Настоящая статья исследует  вопрос  экспресс визуализации  данных  по аренде нескольких автомобилей.
Построение модели визуализации  процессов в Excel включает в себя: (1) настройку шкалы календарного графика формулами MS Excel и (2) применение пользовательских функций  MS Excel.  

Подробнее: Построение модели экспресс визуализации графика аренды автомобилей методами MS Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 295

Подробнее: Применение комбинированного метода MS Excel для расчета дохода дилера

Информация о материале
Категория: Microsoft Excel
Просмотров: 389

Подробнее: Вариант пересчета срока исполнения заявок в формат 9 часового рабочего дня методами MS Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 614

Автоматизация заполнения расписания морских грузоперевозок методами VBA Excel

Подробнее: Автоматизация заполнения расписания морских грузоперевозок

Информация о материале
Категория: Microsoft Excel
Просмотров: 815

Функция ЕСЛИ() - одна из самых часто используемых в Excel. Однако в одной ЕСЛИ() можно задать не более двух вариантов "ветвления" (да/нет). И если для новичков является сложным разобраться с тем, как в ЕСЛИ() увеличить количество вариантов, то для пользователей, обладающих опытом применения этой функции, возникает прямо противоположный вопрос - как избавиться от большого числа вложенных ЕСЛИ().

Подробнее: Если у вас слишком много ЕСЛИ)

Информация о материале
Категория: Microsoft Excel
Просмотров: 36767

VBA (Visual Basic for Applications) – разновидность языка программирования Visual Basic, включенная в пакет программ Microsoft Office. Знание VBA облегчает выполнение рутинных задач на компьютере, в частности в Excel. К примеру, вам нужно назначить гиперссылки на слово, которое повторяется 80 раз. Создание макрокоманд (макросов) в VBA автоматизирует этот процесс и оставит здоровыми нервы.Язык программирования VBA легок в освоении и может использоваться обычными пользователями. Единственное, что вам потребуется – свободное время и знание Excel.

Подробнее: Программирование в Excel VBA: с чего начать

Информация о материале
Категория: Microsoft Excel
Просмотров: 37303

Пользователи Excel часто сталкиваются с проблемой печати громоздких таблиц на принтере. Будем вместе разбираться с этой задачей.

Подробнее: Подготовка документа Excel к печати

Информация о материале
Категория: Microsoft Excel
Просмотров: 40385
Система 1С позволяет гибко интегрировать приложения,написанные в этой среде с другими приложениями, например Microsoft Office. Имеется возможность как обращаться ко внешним приложениям из кода 1С, так и наоборот, из внешних приложений к 1С. Рассмотрим пример обращения к 1С из эксель с помощью макросов VBA через механизм OLE.

Подробнее: Заполнение справочника 1C данными из Excel через механизм OLE

Информация о материале
Категория: Microsoft Excel
Просмотров: 30116

Архив форума до февраля 2008 года

Информация о материале
Категория: Microsoft Excel
Просмотров: 150123
Надстройка MS Excel "Поиск решений" позволяет решать широкий круг задач на оптимизацию. Думаю, многие посетители сайта изучали в институте линейное программирование или исследования операций. "Поиск решений" в Excel позволяет в считанные секунды находить оптимальные решения достаточно сложных моделей, кстати не только линейных, без знания алгоритмов, макросов, формул и длительных рутинных итерраций.

Подробнее: Решение задач на оптимизацию с помощью MS Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 81658
Эта заметка открывает серию статей, посвященных разбору полезных алгоритмов (макросов, функций) для эффективной работы в MS Office.

Подробнее: Коллекция алгоритмов. Массовое исправление ошибок #ДЕЛ/0!, #Н/Д! с помощью макросов

Информация о материале
Категория: Microsoft Excel
Просмотров: 232518
Использование макросов на языке программирования Visual Basic очень облегчает жизнь пользователям Excel и делает из этого пакета поистине мощную среду для решения широкого круга экономических и технических задач. Изучение макросов эксел лучше всего начать, как ни странно, с создания своего первого собственного макроса.

Подробнее: Первые шаги в использовании макросов VBA в Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 66863
Суть инструмента проста. Выбираем любую ячейку, добавляем ее в список контрольных значений и видим ее значение в специальном окне.

Подробнее: Окно контрольного значения в Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 97440
Условное форматирование позволяет в зависимости от значений ячеек эксел изменять их числовой формат и оформление.

Подробнее: Условное форматирование ячеек Excel

Информация о материале
Категория: Microsoft Excel
Просмотров: 90912
Почти все предыдущие темы нашего курса посвящены работе со списками.
Вы будете смеяться, но в русском переводе Excel словом «список» обозначается два совершенно разных понятия.

Подробнее: Списки-2 или Федот, да не тот

Информация о материале
Категория: Microsoft Excel
Просмотров: 117517
В завершение темы обработки текста в Excel поговорим о функциях форматирования и преобразования значений ячеек.

Подробнее: Текстовые функции в Excel. Часть 4.

Информация о материале
Категория: Microsoft Excel
Просмотров: 77687
Еще одна группа текстовых функций рабочего листа Excel - функции для преобразования регистра букв.

Подробнее: Текстовые функции в Excel. Часть 3.

Информация о материале
Категория: Microsoft Excel
Просмотров: 123529
Сегодня поговорим о функциях для поиска и замены части текстовой строки с помощью функций рабочего листа Excel.

Подробнее: Текстовые функции в Excel. Часть 2.

Информация о материале
Категория: Microsoft Excel
Просмотров: 70815
В продолжение темы обработки текста в Excelрассмотрим использование для этих целей текстовых функций.

Подробнее: Текстовые функции в Excel

  1. Написание дробных чисел в Excel'е
  2. Обработка текстовых данных в Excel
  3. Суммирование по критерию
  4. Присоединение автофигуры к ячейке Excel
  5. Комментарий в формуле Excel
  6. Число или сумма прописью
  7. Функции для работы с базами данных
  8. Полезные сочетания клавиш. Работа с ячейками.
  9. Хитрости использования ссылок на ячейки в Excel
  10. Условное форматирование с использованием пользовательских функций
  11. Знакомтесь, Microsoft Office Excel 2007!
  12. Сводные таблицы и диаграммы в MS Excel
  13. Итоги
  14. Как скрыть нулевые значения?
  15. Автофильтр
  16. Сортировка списка
  17. Еще про перенос слов. Разрыв строки внутри ячейки.
  18. Перенос текста в ячейках
  19. Функция =ДВССЫЛ()
  20. Группа и структура
  21. Функция =ВПР()
  22. Функция =ЕСЛИ()
  23. Знак & (амперсанд)
  24. Настройка панелей инструментов.
  25. Работа со списками.
  26. Представления.
  27. Как извлечь корень n-ной степени с помощью Excel?

Страница 1 из 3