Разработка программы определения запасов сырья

Содержание

Задание 17

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

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

. Графическая схема алгоритма

. Решение задачи с помощью программы MS Excel

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

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

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

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

Список использованных источников

Задание 17

Разработать программу определения запасов сырья. На нескольких предприятиях используется сырье одного вида. Для каждого предприятия заданы верхние и нижние границы норм хранения этого сырья.

) Определить номера предприятий, имеющих запасы сырья выше нормы, ниже нормы и общие запасы сырья на этих предприятиях.

) Определить количество предприятий, имеющих излишки сырья, недостаток сырья, суммарный излишек и недостаток сырья на этих предприятиях.

) Найдите величину излишков сырья по всем предприятиям и определите, хватит ли этих излишков для покрытия недостатков сырья для всех нуждающихся предприятий.

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

Исходные данные задачи могут быть представлены в виде матрицы запасов сырья Q(I,M), где I - номер предприятия, (1=1,N), M-величина запасов сырья. При этом целевая функция системы MAX<M>MIN.

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

Таблица 1 -Запасы сырья, тыс.т.

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

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

Общие запасы сырья

Суммарный излишек сырья на предприятиях

Суммарный недостаток сырья на предприятиях

3. Графическая схема алгоритма

. Решение задачи с помощью программы MS Excel

Загружаем программу MicrosoftExcel [2]. Введем исходную (табл. 3) таблицу (рис 1).

Таблица 3 -Обеспеченность предприятий сырьем

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

Определить номера предприятий, имеющих запасы сырья выше или ниже нормы (таблица 4).

Таблица 4 - Запасы сырья на предприятиях

Для нахождения излишков или недостатка сырья используем функцию ЕСЛИ [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 с.