Разработка программы определения запасов сырья
Содержание
Задание 17
. Исходные данные и принятые обозначения
. Математическая постановка задачи
. Графическая схема алгоритма
. Решение задачи с помощью программы MS Excel
. Разработка макроса для построения диаграммы
. Разработка интерфейса
. Использование интерфейса для работы с таблицей
. Разработка базы данных
Список использованных источников
Задание 17
Разработать программу определения запасов сырья. На нескольких
предприятиях используется сырье одного вида. Для каждого предприятия заданы
верхние и нижние границы норм хранения этого сырья.
) Определить номера предприятий, имеющих запасы сырья выше нормы, ниже
нормы и общие запасы сырья на этих предприятиях.
) Определить количество предприятий, имеющих излишки сырья, недостаток
сырья, суммарный излишек и недостаток сырья на этих предприятиях.
) Найдите величину излишков сырья по всем предприятиям и определите,
хватит ли этих излишков для покрытия недостатков сырья для всех нуждающихся
предприятий.
1. Исходные
данные и принятые обозначения
Исходные данные задачи могут быть представлены в виде матрицы запасов
сырья Q(I,M), где I - номер предприятия,
(1=1,N), M-величина запасов сырья. При этом
целевая функция системы MAX<M>MIN.
Исходные данные для расчета и принятые обозначения у переменных приведены
в таблице 1 и таблице 2.
Таблица 1 -Запасы сырья, тыс.т.
Номер предприятия
|
Запасы сырья
|
Нижняя граница
|
Верхняя граница
|
1
|
170
|
90
|
120
|
2
|
220
|
170
|
220
|
3
|
120
|
140
|
140
|
Таблица 2 - Принятые обозначения и описание данных задачи
Вид данных
|
Наименование показателя
|
Обозначение
|
Единицы измерения
|
Вид переменной
|
Формат
|
Входные
|
Количество предприятий
|
N
|
Шт.
|
Простая
|
Целый
|
|
Номер предприятия
|
I
|
-
|
Простая
|
Целый
|
|
Запасы сырья
|
М
|
Тыс.т.
|
Простая
|
Целый
|
|
Верхниеграницы норм хранения
|
МАХ
|
Тыс.т.
|
Простая
|
Целый
|
|
Нижние границы норм хранения
|
MIN
|
Тыс.т.
|
Простая
|
Целый
|
|
Остатки сырья наi-омпредприятии
|
Q(I, M)
|
Тыс.т
|
Целый
|
Выходные
|
Номера предприятий, имеющих запасы сырья выше нормы
|
MAX(IM)
|
-
|
Индексированная
|
Целый
|
|
Номера предприятий, имеющих запасы сырьяниже нормы
|
MIN(IM)
|
-
|
Индексированная
|
Целый
|
|
Общие запасы сырья
|
SUM(N)
|
Тыс.т
|
Индексированная
|
Целый
|
|
Количество предприятий, имеющих запасы сырья выше нормы
|
MAX(NM)
|
Шт.
|
Индексированная
|
Целый
|
|
Количество предприятий, имеющих запасы сырьяниже нормы
|
MIN(MN)
|
Шт.
|
Индексированная
|
Целый
|
Промежуточный
|
Суммарный излишек сырья на предприятиях
|
MAXSUM(NM)
|
Тыс.т
|
Индексированная
|
Целый
|
|
Суммарный недостаток сырья на предприятиях
|
MINSUM(NM)
|
Тыс.т
|
Индексированная
|
Целый
|
2.
Математическая постановка задачи
Общие запасы сырья
Суммарный
излишек сырья на предприятиях
Суммарный
недостаток сырья на предприятиях
3.
Графическая схема алгоритма
.
Решение задачи с помощью программы MS Excel
Загружаем
программу MicrosoftExcel [2]. Введем исходную (табл. 3) таблицу (рис 1).
Таблица
3 -Обеспеченность предприятий сырьем
Номер предприятия
|
Запасы сырья
|
Нижняя граница
|
Верхняя граница
|
1
|
170
|
90
|
120
|
2
|
220
|
170
|
220
|
3
|
120
|
140
|
140
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рисунок 1 - Решение задачи в Excel
Определить номера предприятий, имеющих запасы сырья выше или ниже нормы
(таблица 4).
Таблица 4 - Запасы сырья на предприятиях
Номер предприятия
|
Излишки
|
1
|
50
|
|
2
|
|
|
3
|
|
20
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого
|
50
|
20
|
Для нахождения излишков или недостатка сырья используем функцию ЕСЛИ [3]
(логическое выражение; значение, если истина; значение, если ложь) и получаем
=ЕСЛИ(B5-D5>0;B5-D5;"").
Проверяет, выполняется ли условие, и возвращает одно значение, если оно
выполняется, и другое значение, если нет.
Находим общую сумму излишков или недостатка сырья по всем предприятиям,
используя функцию СУММА [4] и получаем ==СУММ(B16:B23).
Определяем общие запасы руды =СУММ(B5:B12).
Определим количество предприятий, имеющих запасы сырья выше или ниже
нормы. Для определения количества предприятий используем функциюСЧЕТ,
считающуюколичество ячеек указанного интервала, в которых содержаться числа и
получаем =СЧЁТ(B16:B23).
Таблица с расчетами представлена на рис. 2.
Рисунок 2 - Расчетная таблица с формулами
Построенная диаграмма представлена на рис. 3.
Рисунок 3. Диаграмма определения запасов сырья
. Разработка
макроса для построения диаграммы
Удалим диаграмму, построенную в п.4. и запишем все действия построения
диаграммы в макрос. Для этого выполним следующие действия[5]:
Выполним команду Сервис - Макрос - Начать запись - введем имя макроса
«График».
Выполнимпостроение диаграммы и назначение ей параметров.
Выполним команду Сервис - Макрос - Остановить Запись.
Sub График()
' ГрафикМакрос.Offset(-3,
-6).Range("A1:D4").Select.Shapes.AddChart.Select.ChartType =
xlColumnClustered.SetSourceData Source:=Range("'ЗапасыСырья'!$A$4:$D$7").SeriesCollection(2).Select.SetSourceData.PlotArea.Select
ActiveChart.ChartArea.Select
Данный макрос используется при проектировании интерфейса. Необходимо
будет привязать его к кнопке в п. 6.
6. Разработка
интерфейса
Интерфейс программы предусматривается для удобства ввода исходных данных
и вывода результатов расчета. Реальная расчетная таблица может быть очень
большой, данные и результаты могут быть не видны на одном экране, разбросаны по
таблице. Для редактирования данных, их ввода, удаления и просмотра результатов
расчета будет требоваться перемещение по большой таблице, что не очень удобно.
Разработка интерфейса программы включает проектирование форм для ввода исходных
данных и вывода результатов. Написания кода для процедур отклика элементов управления:
кнопок, кнопок выбора, счетчиков и т.д. Написаниякода модулей, если программа
объемная. Проверка работы интерфейса на контрольном примере.
Запуск интерфейса программы, осуществляется кнопкой «ДОБАВЛЕНИЕ И
УДАЛЕНИЕ предприятий», расположенной на рабочем листе (рисунок 1). Для
установки кнопки подключим панель инструментов «Элементы управления» командой
ВИД - Панели инструментов - Элементы управления [6]. На ней нажмем на кнопку
«Конструктор» (она должна быть утоплена) - тем самым включится режим конструктора.
Выбираем на панели инструментов кнопку «Кнопка», переходим в область рабочего
листа и, нажав левую клавишу мыши, рисуем кнопку. Нажав кнопку «Свойства» на
ПИ, устанавливаем свойство Caption = «ДОБАВЛЕНИЕ И УДАЛЕНИЕ предприятий».
Привязываем к кнопке процедуру запуска формы 1. Для этого 2 раза щелкаем по ней
левой кнопкой мыши и между строк начала и конца процедуры, которые генерируются
автоматически, вставляем команду показать (Show) форму UserForml.
Private Sub CommandButtonl_Click().Show
EndSub
Нажатие на кнопку «ДОБАВЛЕНИЕ И УДАЛЕНИЕ предприятий» открывает форму 1.
Проект формы 1 представлен на рисунке 4 с обозначением элементов управления.
программа алгоритм таблица база
Рисунок 4 - Модуль работы с исходными данными
Для создания формы 1 перейдем в редактор VBA командой Сервис - Макрос -
Редактор VisualBasic. В редакторе выполним команду Insert -UserForm. В окне
свойств UserForml изменим свойство Caption = Работа с исходными данными - этот
текст появится в строке надписи формы 1.
На вид формы 1 оказывает влияние структура таблицы в Excel. На ней
размещены поля (TextBox) для ввода номеров предприятий, запасов сырья на каждом
из них, а также верхних и нижних норм хранения. Т.е. предполагается ввод в
таблицу по строкам (как в базах данных) - это интервалы ячеек:A5:D5, A6:D6 и т.д. Кроме того на форме1
размещены надписи (lebel).
На форме расположены элементы управления. Названия кнопок формы введены
аналогично, т.е.путем изменения её свойства Caption. Элементы управления выполняют следующие функции:
Кнопка «Добавить запись» - из полей формы 1 записывает новую строку в
ячейки таблицы Excel, например,
А8:D8, для этого пользователь должен
ввести в поля формы значения для новой строки таблицы.
Кнопка «Удалить запись» - удаляет выбранную запись, переписыванием
нижележащей записи на выбранную.
Кнопка «Посмотреть ИТОГИ» - загружает форму2.
Кнопка «Закрыть Форму» - закрывает форму1.
Программирование отклика кнопки «Добавить запись»
Private Sub CommandButton1_Click()= TextBox1.Value + 4(i,
1).Value = TextBox1.Value(i, 2).Value = TextBox4.Value(i, 3).Value =
TextBox2.Value(i, 4).Value = TextBox3.Value
EndSub
Программирование отклика кнопки «Удалить запись»
Private Sub CommandButton2_Click()= TextBox1.Value +
4Worksheets(1).Cells(j, 1).Value <> ""(j, 1).Value = Cells(j +
1, 1).Value(j, 2).Value = Cells(j + 1, 2).Value(j, 3).Value = Cells(j + 1,
3).Value(j, 4).Value = Cells(j + 1, 4).Value
j = j + 1
Программирование отклика кнопки «Просмотреть итоги»
Private Sub CommandButton3_Click().Hide2.Show
EndSub
Программирование отклика кнопки «Закрыть форму»
Private Sub CommandButton4_Click().Hide
Проект формы 2 представлен на рисунке 5 с обозначением элементов
управления.
Рисунок 5. Форма с итоговыми данными
При открытие формы происходит ее инициализация итоговыми данными из листа
Excel.
Private Sub UserForm_Initialize().Value = Cells(13,
2).Value.Value = Cells(24, 2).Value.Value = Cells(24, 3).Value.Value =
TextBox2.Value - TextBox3.Value
EndSub
При формировании графика происходит запуск макроса, созданного в п.5.
Сформированная диаграмма представлена на рис. 3.
7.
Использование интерфейса для работы с таблицей
Добавление записей в таблицу. Нажмем на кнопку «ДОБАВЛЕНИЕ И УДАЛЕНИЕ
предприятий», расположенную на рабочем листе. Откроется форма1 (рис.6),
счетчиком установите значения полей на номере последнейзаписи (в нашем примере
запись 3), заполним поля формы 1 значениями, нажмите на кнопку «Добавить
запись», данные перепишутся в таблицу. По введённым данным рассчитаются общие
запасы, излишки или недостаток сырья и количество предприятий.
Рисунок 6 - Добавление новой записи
Удалим запись 3 из таблицы, нажав кнопку «Удалить запись» (рис. 7).
Рисунок 7 - Удаление записи
Просмотрим итоговые данные (рис. 8).
Рисунок 8 - Форма «Итоги»
8. Разработка
базы данных
В программе Access создаем базу данных, в которой будут храниться данные
аналогичные тем, что приведены таблице Excel.
Рисунок 9 - Таблица «Запасы»
В окне базы данных выбираем объект «Формы» и команду «Создать» -
«Автоформа:в столбец». Форму сохраняем под именем «Запасы» (см. рисунок 10).
Рисунок 10 - Форма «Запасы»
Разработаем запрос с вычисляемым полем по остаткам сырья от нижней
границы хранения. Переходим на вкладку «Запросы». Выбираем «Создание запроса в
режиме конструктора», выбираем таблицу «Запасы», отбираем все поля из этой
таблицы в запрос. При закрытии запроса сохраняем его, открываем, проверяем
правильность выполнения. Создаем вычисляемое поле в запросе, для чего открываем
запрос в Конструкторе, выбираем свободное поле, в конце занятых полей,
открываем «Построитель выражений» и формируем в нем выражение для вычисляемого
поля «Остаток» (рис. 11).
Рисунок 11 - Построитель выражений Access
Сохраняем и выполняем запрос, результат выполнения запроса представлен на
рисунке 12.
Рисунок 12 - Конструктор запросов
Для просмотра кода SQL созданного запроса, откроем его в Конструкторе
запросов и выполним команду ЗАПРОС - Запрос SQL [8]. Ниже приведен код запроса
«Остаток запасов».Запасы.[Номер предприятия], Запасы.[Запасы сырья],
Запасы.[Верхняя граница], Запасы.[Нижняя граница], [Запасы]![Запасы
сырья]-[Запасы]![Нижняя граница] AS ОстатокЗапасы;
После слова SELECT указаны поля, отобранные в запрос и в том числе
вычисляемое поле. После слова FROM указано имя таблицы из которой отобраны
поля.
Отчет разрабатывается с использованием «Мастера отчетов», основой для
отчета могут служить таблицы и запросы. В нашем случае основой отчета будет
запрос, отбираем в него нужные поля из запроса, если данные можно
сгруппировать, то нужно выполнить группировку и получить итоги по группе. В нашем
примере проводим группировку по полю «Запасы Сырья» (рис. 13).
Разработанный отчет представлен на рисунке 14.
Рисунок 13 - Конструктор запросов
Рисунок 14 - Результат выполнения запроса
Список
использованных источников
1.
Межгосударственный стандарт ГОСТ 7.1-2003 «Система стандартов по информации,
библиотечному и издательскому делу. Библиографическая запись. Библиографическое
описание. Общие требования и правила составления»
. ГарнаевА.,РудиковаЛ.
Microsoft Office Excel 2010. Разработка приложений (+ CD-ROM) -
СПб.:БХВ-Петербург, 2011. - 514 с.
. Кузьменко
В. VBA. Эффективное использование - М.:Бином-Пресс, 2012. - 624 с.
. Белоусова
С., Бессонова И. Основные принципы и концепции программирования на языке VBA в
Excel - М.:Интернет-университет информационных технологий, Бином. Лаборатория
знаний, 2010. - 200 с.
. Уокенбах Д.
Формулы в MicrosoftExcel 2010 (+ CD-ROM) - М.: Диалектика, 2011. - 704 с.
. Гарбер Г.
Основы программирования на VBA Excel и численных методов (+ CD-ROM) - М.:
Принтком, 2009. - 432 с.
.
СурядныйА.С. Microsoft Access 2010. Лучший самоучитель - М.:Астрель, ВКТ , 2002. -
448 с.
. Смирнова
О.В. Access 2007 на практике - М.: Народный самоучитель, 2009. - 160 с.