Размещено на http://www.allbest.ru/

Содержание

Задание 17

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

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

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

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

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

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

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

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

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



Задание 17

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

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

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

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



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. Графическая схема алгоритма

4. Решение задачи с помощью программы 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. Диаграмма определения запасов сырья

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

Удалим диаграмму, построенную в п.4. и запишем все действия построения диаграммы в макрос. Для этого выполним следующие действия[5]:

Выполним команду Сервис - Макрос - Начать запись -- введем имя макроса «График».

Выполнимпостроение диаграммы и назначение ей параметров.

Выполним команду Сервис - Макрос - Остановить Запись.

Sub График()

' ГрафикМакрос

ActiveCell.Offset(-3, -6).Range("A1:D4").Select

ActiveSheet.Shapes.AddChart.Select

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Range("'ЗапасыСырья'!$A$4:$D$7")

ActiveChart.SeriesCollection(2).Select

ActiveChart.SetSourceData

ActiveChart.PlotArea.Select

ActiveChart.ChartArea.Select

EndSub

Данный макрос используется при проектировании интерфейса. Необходимо будет привязать его к кнопке в п. 6.

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

Интерфейс программы предусматривается для удобства ввода исходных данных и вывода результатов расчета. Реальная расчетная таблица может быть очень большой, данные и результаты могут быть не видны на одном экране, разбросаны по таблице. Для редактирования данных, их ввода, удаления и просмотра результатов расчета будет требоваться перемещение по большой таблице, что не очень удобно. Разработка интерфейса программы включает проектирование форм для ввода исходных данных и вывода результатов. Написания кода для процедур отклика элементов управления: кнопок, кнопок выбора, счетчиков и т.д. Написаниякода модулей, если программа объемная. Проверка работы интерфейса на контрольном примере.

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

Private Sub CommandButtonl_Click()

UserForml.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()

i = TextBox1.Value + 4

Cells(i, 1).Value = TextBox1.Value

Cells(i, 2).Value = TextBox4.Value

Cells(i, 3).Value = TextBox2.Value

Cells(i, 4).Value = TextBox3.Value

EndSub

Программирование отклика кнопки «Удалить запись»

Private Sub CommandButton2_Click()

j = TextBox1.Value + 4

While Worksheets(1).Cells(j, 1).Value <> ""

Cells(j, 1).Value = Cells(j + 1, 1).Value

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

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

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

j = j + 1

Wend

EndSub

Программирование отклика кнопки «Просмотреть итоги»

Private Sub CommandButton3_Click()

UserForm1.Hide

UserForm2.Show

EndSub

Программирование отклика кнопки «Закрыть форму»

Private Sub CommandButton4_Click()

UserForm1.Hide

EndSub

Проект формы 2 представлен на рисунке 5 с обозначением элементов управления.

Рисунок 5. Форма с итоговыми данными

При открытие формы происходит ее инициализация итоговыми данными из листа Excel.

Private Sub UserForm_Initialize()

TextBox1.Value = Cells(13, 2).Value

TextBox2.Value = Cells(24, 2).Value

TextBox3.Value = Cells(24, 3).Value

TextBox4.Value = TextBox2.Value - TextBox3.Value

EndSub

При формировании графика происходит запуск макроса, созданного в п.5. Сформированная диаграмма представлена на рис. 3.

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

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

Рисунок 6 - Добавление новой записи

Удалим запись 3 из таблицы, нажав кнопку «Удалить запись» (рис. 7).



Рисунок 7 - Удаление записи

Просмотрим итоговые данные (рис. 8).

Рисунок 8 - Форма «Итоги»

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

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

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

Рисунок 9 - Таблица «Запасы»

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

Рисунок 10 - Форма «Запасы»

Разработаем запрос с вычисляемым полем по остаткам сырья от нижней границы хранения. Переходим на вкладку «Запросы». Выбираем «Создание запроса в режиме конструктора», выбираем таблицу «Запасы», отбираем все поля из этой таблицы в запрос. При закрытии запроса сохраняем его, открываем, проверяем правильность выполнения. Создаем вычисляемое поле в запросе, для чего открываем запрос в Конструкторе, выбираем свободное поле, в конце занятых полей, открываем «Построитель выражений» и формируем в нем выражение для вычисляемого поля «Остаток» (рис. 11).

Рисунок 11 - Построитель выражений Access

Сохраняем и выполняем запрос, результат выполнения запроса представлен на рисунке 12.



Рисунок 12 - Конструктор запросов

Для просмотра кода SQL созданного запроса, откроем его в Конструкторе запросов и выполним команду ЗАПРОС -- Запрос SQL [8]. Ниже приведен код запроса «Остаток запасов».

SELECT Запасы.[Номер предприятия], Запасы.[Запасы сырья], Запасы.[Верхняя граница], Запасы.[Нижняя граница], [Запасы]![Запасы сырья]-[Запасы]![Нижняя граница] AS Остаток

FROM Запасы;

После слова SELECT указаны поля, отобранные в запрос и в том числе вычисляемое поле. После слова FROM указано имя таблицы из которой отобраны поля.

Отчет разрабатывается с использованием «Мастера отчетов», основой для отчета могут служить таблицы и запросы. В нашем случае основой отчета будет запрос, отбираем в него нужные поля из запроса, если данные можно сгруппировать, то нужно выполнить группировку и получить итоги по группе. В нашем примере проводим группировку по полю «Запасы Сырья» (рис. 13).

Разработанный отчет представлен на рисунке 14.

Рисунок 13 - Конструктор запросов

Рисунок 14 - Результат выполнения запроса



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

1. Межгосударственный стандарт ГОСТ 7.1-2003 «Система стандартов по информации, библиотечному и издательскому делу. Библиографическая запись. Библиографическое описание. Общие требования и правила составления»

2. ГарнаевА.,РудиковаЛ. Microsoft Office Excel 2010. Разработка приложений (+ CD-ROM) - СПб.:БХВ-Петербург, 2011. - 514 с.

3. Кузьменко В. VBA. Эффективное использование - М.:Бином-Пресс, 2012. - 624 с.

4. Белоусова С., Бессонова И. Основные принципы и концепции программирования на языке VBA в Excel - М.:Интернет-университет информационных технологий, Бином. Лаборатория знаний, 2010. - 200 с.

5. Уокенбах Д. Формулы в MicrosoftExcel 2010 (+ CD-ROM) - М.: Диалектика, 2011. - 704 с.

6. Гарбер Г. Основы программирования на VBA Excel и численных методов (+ CD-ROM) - М.: Принтком, 2009. - 432 с.

7. СурядныйА.С. Microsoft Access 2010. Лучший самоучитель - М.:Астрель, ВКТ , 2002. - 448 с.

8. Смирнова О.В. Access 2007 на практике - М.: Народный самоучитель, 2009. - 160 с.

Размещено на Allbest.ru