Расчет производительности буровых станков

1. Исходные данные и принятые обозначения

Исходные данные задачи могут быть представлены в виде матрицы ежемесячной производительности буровой установки Q (I, J), где I — номер бурового станка, (I=1, N), J — номер месяца, (J=1, M), N — количество буровых станков, M — количество месяцев.

Исходные данные для расчета и принятые обозначения у переменных приведены в таблице 1 и таблице 3.

Таблица 1 — Производительность буровых станков, пог. м/мес.

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

Таблица 2 — Плановое задание для буровых станков, пог. м/мес.

Таблица 3 — Принятые обозначения и описание данных задачи

2. Математическая постановка задачи

Определение номера бурового станка с максимальной суммарной производительностью за весь период

MAXPR = MAX {S (I)}.

Определение номера бурового станка с минимальной суммарной производительностью за весь период

MINPR = MAX {S (I)}.

Определение суммарного количества пробуренных метров каждым станком

SKOLM = .

Определение процента выполнения плана

PVP =

где — фактическая сумма производительности всех буровых станков за каждый месяц;

P (J) — плановое задание на каждый месяц.

3. Решение задачи с помощью электронных таблиц

Для решения задачи о буровых установках используем программу MS Excel.

Загружаем программу MS Excel.

Далее формируем заголовки таблиц (таблица 3, таблица 4), поместив в них соответствующий текст. Результат представлен на рисунке 1.

Т.к. на нашем предприятии имеются буровые станки одинакового вида БТС-150А, то каждый буровой станок имеет свой инвентарный номер.

Форматируем полученные таблицы.

Для таблицы 3 предусматриваем внизу несколько дополнительных строк (до 14 строки) для последующего добавления данных с помощью разработанной формы1 (рисунок 3).

Вводим данные для решения контрольного примера (ячейки В6: H8). Числа для контрольного примера взяты из таблицы 1.

Для вычисления суммарного количества пробуренных метров каждым буровым станком используем функцию СУММ мастера функций, например для первого бурового станка =СУММ (C6:H6), поместив её в ячейку I6. Копируем формулу =СУММ (C6:H6) из ячейки I6 в ячейки I7 до I14. Для этого используем маркер заполнения.

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

Определим количество работающих буровых станков в каждый из месяцев. Для определения количества работающих буровых станков в 1 месяц вводим в ячейку С15 формулу =СЧЁТЗ (C6:C14). Далее копируем формулу из ячейки С15 по строке до ячейки Н15.

Для вычисления средней производительности всех буровых станков за месяц вводим в ячейку С21 формулу =СРЗНАЧ (С6:С14) (выбираем данную формулу из категории — Статистические) и копируем эту формулу по строке до ячейки Н21.

Рисунок 1 — Решение задачи в MS Excel

Для выбора номера бурового станка с наибольшей производительностью в ячейку I23 вводим формулу =ВЫБОР (ПОИСКПОЗ (МАКС (I6:I14); I6: I14; 0); B6; B7; B8; B9; B10; B11; B12; B12; B14).

Для выбора номера бурового станка с наименьшей производительностью в ячейку I25 вводим формулу =ВЫБОР (ПОИСКПОЗ (МИН (I6:I14); I6: I14; 0); B6; B7; B8; B9; B10; B11; B12; B12; B14).

На рисунке 2 представлена расчетная таблица с формулами.

Рисунок 2 — Расчетная таблица с формулами Для расчета процента выполнения плана буровыми станками за полугодие необходимо суммарное количество пробуренных метров всеми станками разделить на плановое задание для буровых станков на шесть месяцев.

Таким образом, в результате проведенных расчетов с помощью средств MS Excel были получены следующие результаты:

— номер бурового станка с наибольшей производительностью БТС-150А с инвентарным номером 1245;

— номер бурового станка с наименьшей производительностью БТС-150А с инвентарным номером 1105;

— суммарное количество пробуренных метров каждым буровым станком за анализируемый период представлено в таблице 3 (в 9 столбце): 1011 пробурил 6150 пог. м, 1105 — 5950 пог. м, 1245 — 9100 пог. м.;

— определен процент выполнения плана за шесть месяцев, который составил 106% и процент выполнения плана каждым буровым станком за анализируемый период (таблица 6).

4. Разработка макроса для построения графика

Создадим макрос для построения графика путем выполнения

— Выполняем команду Вид — Макросы — Запись макроса — введем имя макроса, например, «График».

Далее по результатам расчетов построим совмещенный с диаграммой (количество работающих буровых станков) график (средняя месячная производительность буровых станков).

Для построения диаграммы выделяем ячейки В15: Н15, затем нажимаем клавишу Ctrl и выделяем ячейки В20: Н20, вызываем Мастер Диаграмм. Выбираем вид диаграммы «гистограмма». Для ряда «Количество работающих буровых станков» изменим тип диаграммы на график. Далее в меню формат ряда выбираем параметры ряда по «вспомогательной оси». В результате получаем график с двумя осями «у», одна ось для указания «количества работающих буровых станков», другая — для указания «процента выполнения планового задания каждым буровым станком». Присваиваем названия осям, а также название диаграмме и размещаем легенду сверху над диаграммой. Размещаем диаграмму на отдельном листе. Полученная диаграмма представлена на рисунке 3.

Рисунок 3 — Показатели эффективности работы буровых станков предприятия Выполните команду Вид — Макросы — Остановить Запись.

Созданный макрос далее применяется при проектировании интерфейса, который будет привязан к кнопке.

5. Разработка интерфейса

Разработаем интерфейс программы, которая осуществляет добавление и удаление строчек в таблицу 3 при помощи кнопки «ДОБАВЛЕНИЕ И УДАЛЕНИЕ буровых станков».

Для создания кнопки открываем меню «Разработчик» и нажимаем на кнопку «Конструктор» (она должна быть утоплена) — тем самым включится режим конструктора. Далее нажимаем вставить и выбираем «Элементы ActiveX» кнопку «Кнопка», переходим в область рабочего листа и, нажав левую клавишу мыши, рисуем кнопку. Нажав кнопку «Свойства» на панели инструментов, устанавливаем свойство Caption = «ДОБАВЛЕНИЕ И УДАЛЕНИЕ буровых станков». Привязываем к кнопке процедуру запуска формы1. Для этого 2 раза щелкаем по ней левой кнопкой мыши и между строк начала и конца процедуры, которые генерируются автоматически вставляем команду показать (Show) форму UserForm1.

Private Sub CommandButton1_Click ()

UserForm1. Show

End Sub

Данная программа создается в модуле рабочего листа 1 (того, где расположена кнопка).

Нажатие на кнопку «ДОБАВЛЕНИЕ И УДАЛЕНИЕ буровых станков» открывает форму1. Проект формы1 представлен на рисунке 4.

Форма1 предназначена для добавления и удаления исходных данных. Исходные данные отмечены в таблице 2, как «Входные».

Для создания формы1 перейдем в редактор VBA командой Разработчик — Редактор Visual Basic. В редакторе выполним команду Insert — UserForm. В окне свойств UserForm1 изменим свойство Caption = Работа с исходными данными — этот текст появится в строке надписи формы1.

Рисунок 4 — Проект Формы1

На вид формы1 оказывает влияние структура таблицы в Excel. На ней размещены поля (TextBox) для ввода № самосвала и его объемы перевозок за месяцы январь, февраль, март, апрель, май, июнь. Т. е. предполагается ввод в таблицу по строкам (как в базах данных) — это интервалы ячеек: В6: Н6, В7: Н7 и т. д. Дополнительно в таблицу и в форму1 добавлено поле «№ записи». В поле «Суммарное количество пробуренных метров» осуществляется вывод этого результата из ячеек диапазона I6: I14. Кроме того на форме1 размещены надписи (lebel).

На форме расположены элементы управления. Названия кнопок формы введены аналогично п. 6.2 путем изменения её свойства Caption. Элементы управления выполняют следующие функции:

— Счетчик (SpinButton1) — осуществляет переход по записям таблицы и позволяет отражать в полях формы (TextBox) значения из ячеек таблицы и записывать значения полей формы (когда добавляется новая запись) в ячейки таблицы Cells (i, j).

— Кнопка «Добавить запись» — из полей формы1 записывает новую строку в ячейки таблицы Excel, например, A9: I9, для этого пользователь должен ввести в поля формы значения для новой строки таблицы.

— Кнопка «Удалить запись» — удаляет выбранную запись, переписыванием нижележащей записи на выбранную.

— Кнопка «Посмотреть ИТОГИ» — загружает форму2.

— Кнопка «Закрыть Форму» — закрывает форму1.

Пропишем код передвижения счетчика вверх и вниз. Для написания кода два раза щелкаемь по счетчику на форме. В модуле формы в списке событий выбраем для SpinButton1 события SpinUp (счетчик возрастает) и SpinDown (счетчик уменьшается). Между строками начала и конца процедуры набираем следующий код:

Private Sub SpinButton1_SpinDown ()

n = Val (TextBox8. Text)

If n > 1 Then

n = n — 1

TextBox8. Text = Str (n)

ReadRec (n)

End If

End Sub

Private Sub SpinButton1_SpinUp ()

n = Val (TextBox8. Text)

n = n + 1

TextBox8. Text = Str (n)

ReadRec (n)

End Sub

В процедурах используется обращение к процедуре ReadRec (n) — читать n-ю запись из ячеек таблицы и писать в поля формы.

Создаем код процедуры ReadRec для Для этого выполняем команду Insert — Module и в открывшемся модуле набираем следующий код.

Public n As Integer

Public Sub ReadRec (rec)

If rec > 0 Then

With UserForm1

TextBox1. Value = Cells (rec + 5, 2).Value

TextBox2. Value = Cells (rec + 5, 3).Value

TextBox3. Value = Cells (rec + 5, 4).Value

TextBox4. Value = Cells (rec + 5, 5).Value

TextBox5. Value = Cells (rec + 5, 6).Value

TextBox6. Value = Cells (rec + 5, 7).Value

TextBox7. Value = Cells (rec + 5, 8).Value

TextBox9. Value = Cells (rec + 5, 9).Value

End With

End If

End Sub

Программируем отклик кнопки «Добавить запись». Открываем форму1, затем 2 раза щелкаем по кнопке «Добавить запись» и прописываем процедуру:

Private Sub CommandButton1_Click ()

TextBox1. SetFocus

WriteRec (n)

n = n + 1

TextBox8. Text = Str (n)

ReadRec (n)

End Sub

В отклике кнопки «Добавить запись» содержится обращение к процедуре WriteRec — писать запись из полей формы1 в ячейки таблицы.

Для написания кода этой процедуры переходим в модуль (Modul), где содержится код процедуры ReadRec и далее набираем код процедуры WriteRec:

Public Sub WriteRec (rec)

If rec > 0 Then

With UserForm1

Cells (rec + 5, 1).Value =.TextBox8. Value

Cells (rec + 5, 2).Value =.TextBox1. Value

Cells (rec + 5, 3).Value =.TextBox2. Value

Cells (rec + 5, 4).Value =.TextBox3. Value

Cells (rec + 5, 5).Value =.TextBox4. Value

Cells (rec + 5, 6).Value =.TextBox5. Value

Cells (rec + 5, 7).Value =.TextBox6. Value

Cells (rec + 5, 8).Value =.TextBox7. Value

Cells (rec + 5, 9).FormulaR1C1 = «=SUM (RC[-6]: RC[-1])»

End With

End If

End Sub

Программируем отклик кнопки «Удалить запись». Открываем форму1, затем 2 раза щелкаем по кнопке «Удалить запись» и прописываем процедуру:

Private Sub CommandButton2_Click ()

DelRec (n)

ReadRec (n)

End Sub

Для написания кода процедуры DelRec переходим в модуль, где содержатся коды процедур ReadRec и WriteRec и далее набираем код процедуры DelRec:

Public Sub DelRec (rec)

i = rec + 5

While Worksheets (1).Cells (i, 1).Value <> ««

Cells (i, 2).Value = Cells (i + 1, 2).Value

Cells (i, 3).Value = Cells (i + 1, 3).Value

Cells (i, 4).Value = Cells (i + 1, 4).Value

Cells (i, 5).Value = Cells (i + 1, 5).Value

Cells (i, 6).Value = Cells (i + 1, 6).Value

Cells (i, 7).Value = Cells (i + 1, 7).Value

Cells (i, 8).Value = Cells (i + 1, 8).Value

Cells (i, 9).FormulaR1C1 = Cells (6, 9).FormulaR1C1

i = i + 1

Wend

End Sub

Программируем отклик кнопки «Посмотреть Итоги». Открываем форму1, 2 раза щелкаем по кнопке «Посмотреть Итоги» и прописываем процедуру:

Private Sub CommandButton4_Click ()

UserForm1. Hide

UserForm2. Show

End Sub

Программируем отклик кнопки «Закрыть форму» для этого открываем форму1 путем двойного щелчка по кнопке «Закрыть форму» и прописываем процедуру:

Private Sub CommandButton3_Click ()

UserForm1. Hide

End Sub

Далее продолжим программирование процедур откликов кнопок формы2 «Просмотр результатов».

Для кнопки «ПОКАЗАТЬ» необходимо из 21 строки таблицы вывести в надписи значения ячеек в формате с двумя знаками после запятой.

Для этого нажимаем два раза кнопку «ПОКАЗАТЬ» и прописываем следующую процедуру:

Private Sub CommandButton1_Click ()

Label11. Caption = FormatNumber (Cells (21, 3), 2)

Label12. Caption = FormatNumber (Cells (21, 4), 2)

Label13. Caption = FormatNumber (Cells (21, 5), 2)

Label14. Caption = FormatNumber (Cells (21, 6), 2)

Label15. Caption = FormatNumber (Cells (21, 7), 2)

Label16. Caption = FormatNumber (Cells (21, 8), 2)

Label28. Caption = FormatNumber (Cells (23, 9), 0)

Label30. Caption = FormatNumber (Cells (25, 9), 0)

End Sub

Далее программируем отклик кнопки «ГРАФИК» путем прописывания следующей процедуры:

Private Sub CommandButton2_Click ()

График

End Sub

Макрос «ГРАФИК» получен действиями п. 5 и результат выполнения представлен на рисунке 3.

Программирование отклика кнопки «Закрыть форму». Открываем форму2, затем 2 раза щелкаем по кнопке «ЗАКРЫТЬ» и прописываем процедуру:

Private Sub CommandButton2_Click ()

UserForm2. Hide

End Sub

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

6. Использование интерфейса для работы с таблицей

Осуществляем добавление записей в таблицу. Нажимаем на кнопку «ДОБАВЛЕНИЕ И УДАЛЕНИЕ буровых станков», расположенную на рабочем листе. Откроется форма1, счетчиком устанавливаем значения полей на первой пустой записи (в нашем примере запись 4), заполняем поля формы1 значениями, нажмите на кнопку «Добавить запись», данные перепишутся в таблицу. По введённым данным рассчитается суммарная производительность.

Добавление записей при помощи формы1

Осуществим удаление записей из таблицы. Счетчиком формы1 выбираем номер, удаляемой записи. В полях формы отразятся значения ячеек таблицы из этой записи. Нажимаем на кнопку «Удалить запись». В таблице запись будет удалена, нижележащие записи поднимутся вверх. В полях формы отразятся значения ячеек следующей строки. В примере удалена 8 запись — бурового станка под инвентарным номером 1079.

Удаление 8 записи из таблицы с помощью формы1

Запуск формы2

Вывод итоговых результатов. Нажимаем на кнопку «Посмотреть ИТОГИ», расположенную на форме1. Будет запущена форма2. Нажимаем на кнопку «ПОКАЗАТЬ» формы2. В форму2 будут выведены итоговые показатели с листа Excel.

Просмотр результатов расчета Для вывода полученных результатов в графическом виде нажимаем на кнопку «ГРАФИК», которая представит показатели выполнения плана и количество работающих буровых станков наглядно (рисунок 10).

Просмотр графика В следующем разделе разработаем базу данных для ввода.

7. Разработка базы данных

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

На основании приведенных данных в таблице Excel создаем таблицу 3, содержащую описание полей производительности автосамосвалов по месяцам: январь, февраль, март, апрель, май, июнь. Эту таблицу создадим в среде Access.

Таблица 3 — Проектирование таблицы «Производительность»

Загружаем программу Access и выбираем команду «Создание новой базы данных», задаем имя базы данных Буровые станки.mdb. Выбираем объект «Таблицы», команду «Создать таблицу» в режиме «Конструктор». Вводим имена полей, задаем тип полей и заносим характеристики полей создаваемой таблицы (см. таблицу 3). После конструирования таблицы сохраняем её с заданием имени таблицы «Производительность». Открываем созданную таблицу (рисунок 11) и заносим в неё значения аналогичные таблице Excel (рисунок 1 или таблица 1).

Таблица «Производительность»

В окне базы данных выбираем объект «Формы» и команду «Создать» — «Автоформа: в столбец». Форму сохраняем под именем «Производительность» (рисунок 12).

Форма «Производительность»

Разработаем запрос с вычисляемым полем по суммарной производительности каждого бурового станка. Переходим на вкладку «Запросы». Выбираем «Создание запроса в режиме конструктора», выбираем таблицу «Производительность», отбираем все поля из этой таблицы в запрос. При закрытии запроса сохраняем его, открываем, проверяем правильность выполнения. Создаем вычисляемое поле в запросе, для чего открываем запрос в Конструкторе, выбираем свободное поле, в конце занятых полей, открываем «Построитель выражений» и формируем в нем выражение для вычисляемого поля «СумПроизв»: СумПроизв: [Январь]+[Февраль]+[Март]+[Апрель]+[Май]+[Июнь].

Запрос «Суммарная производительность»

Разработаем отчет с использованием «Мастера отчетов», основой для отчета могут служить таблицы и запросы. В нашем случае основой отчета будет запрос, отбираем в него нужные поля из запроса, если данные можно сгруппировать, то нужно выполнить группировку и получить итоги по группе. В нашем примере группировки нет, поэтому итоговые поля вставим в отчет вручную в режиме «Конструктора». Для этого после создания отчета «Мастером» открываем его, посмотрим на результат, чтобы увидеть все недостатки и переключите отчет в режим «Конструктора».

Добавление вычисляемого поля в отчет в режиме «Конструктора»

Увеличиваем мышью раздел «Примечание отчета» и вставляем в него поле ввода с Панели инструментов «Элементы управления», например под столбец Январь и набираем в поле формулу =Sum ([Январь]). Название поля [Январь] не вводится вручную, а перетаскивается мышкой из списка полей, который вызывается командой «ВИД — Список полей». Проверяем работоспособность этой формулы и скопируем эту формулу под все столбцы осуществив замену в них названия полей на Февраль, Март, Апрель, Май, Июнь и СумПроизв соответственно. Вставляем надписи в области заголовка и примечания отчета и введите в них текст «Итоги работы буровых станков» и «Директор предприятия Харитонов Игорь Олегович». Отформатируем эти надписи, изменив шрифт, его высоту, начертание, цвет.

Библиография

электронный формула макрос программа

1 Петров Ю. С., Маркс С. Р. Система управления базами данных. ACCESS 7.0. Учебное пособие. — Екатеринбург: Изд. УГГГА, 1998. — 35 с.

2 ПетровЮ.С. Мультимедийные лекции по дисциплине «Информатика», раздел «Базы данных», 2010 г. — http://www.ursmu.ru/sitemap.html.

3 Тимухина В. В. Мультимедийные лекции по дисциплине «Информатика», разделы «Программное обеспечение» и «Алгоритмизация и программирование», 2010. — http://www.ursmu.ru/sitemap.html.

4 Программирование на языке VISUAL BASIC FOR APPLICATION // Логунова О. С., Тутарова В. Д., Ильина Е. А., Кирпичева Н. Т., Вяльцина Т. Н. Магнитогорск, 2000.

5 Современный язык программирования VISUAL BASIC. РАЗДЕЛ «БАЗЫ ДАННЫХ». Якушева Н. М. // Учеб. пособие для студентов всех специальностей / Н. М. Якушева; М-во образования Рос. Федерации. Гос. образоват. учреждение высш. проф. образования Гос. ун-т упр., Ин-т информ. систем упр. Москва, 2003. (2. изд., испр. и доп.).

6 Программирование на MS VISUAL BASIC. Назаров С. В. // Учеб. пособие для экон. вузов, обучающихся по специальности «Финансы и кредит» / С. В. Назаров, П. П. Мельников. Москва, 2003.

7 Программирование на VISUAL BASIC 6.0. Глушаков С.В.

Основы программирования на VB 6.0: Среда разраб. Visual Basic 6.0: Объект.-ориентир. программирование и Visual Basic / С. В. Глушаков, А. С. Сурядный. Москва, 2003. Сер. Учебный курс.