ПРИМЕР И МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ КУРСОВОЙ РАБОТЫ

Рассмотрим следующую задачу.

Фирма ООО «Инициатива» осуществляет деятельность, связанную со строительными работами и продажей строительных материалов. Цены на товары и услуги представлены на рис. 1, данные о фирмах, заказы которых должны быть выполнены – на рис. 2. Бланк списка заказов приведен на рис. 3. Поля Дата выполнения, Наименование фирмы, Код заказа, Наименование товара, Количество заполняются пользователем. Необходимо:

• построить таблицы по приведенным ниже табличным данным (рис. 2,3,4);

• автоматизировать заполнение бланка списка заказов (рис. 4), используя данные таблиц с информацией о товарах (рис. 2) и заказчиках (рис. 3);

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

• рассчитать и заполнить форму (рис. 6), где с помощью макроса по наименованию фирмы-заказчика из списка заказов выбирается и суммируется необходимая информация;

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

код товара	наименование товара	характеристика	ед. измер	цена, руб.
1	Внутренние работы	разное	кв. м.	5000,00
2	Изготовление дверей	материал фирмы, сосна	кв. м.	3000,00
3	Изготовление окон	материал фирмы, сосна	кв. м.	4500,00
4	Изготовление фундамента	материал фирмы, бетон	куб. м.	2000,00
5	Кровельные работы	материал фирмы, сталь	кв. м.	2200,00
6	Пиломатериалы	хвойные породы дерева	куб. м.	1200,00

Рис.2. Базовый прайс-лист

Рис. 3. Список фирм-заказчиков

Рис. 4. Список заказов за месяц

Рис. 5. Форма заказа по номеру

Рис. 6. Форма заказа по названию фирмы-заказчика

Рис. 7. Итоговые суммы заказов по фирмам-заказчикам

Для решения задачи необходимо:

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Инициатива».

3. Лист 1 переименовать в лист с названием товары.

4. На рабочем листе товары MS Excel создать таблицу базового прайс-листа и заполнить ее исходными данными (см. рис. 2). На листе создать именованный диапазон товар,который необходимо использовать для заполнения данными других таблиц и как аргумент функций (см. рис. 8).

Рис. 8 Фрагмент листа товары

5. Лист 2 переименовать в лист с названием заказчики.

6. На рабочем листе заказчики MS Excel создать таблицу, в которой будет содержаться список фирм-заказчиков (см. рис. 3). На листе создать именованный диапазон заказчик,который необходимо использовать для заполнения данными других таблиц и как аргумент функций (см. рис. 9).

Рис. 9 Фрагмент листа заказчики

7. Лист 3 переименовать в лист с названием список заказов.

8. Создание бланка формы «Список заказов» необходимо выполнять автоматически, а следовательно необходимо создать с помощью макрорекордера макрос, который запишет последовательность действий, создающих бланк формы.

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

· выбрать вкладку Вид,а затем щелкнуть на стрелке направленной вниз рядом с кнопкой  и выбрать опцию Запись макроса. Заполнить диалоговое окно Запись макроса (см. рис. 10);

Рис. 10. Диалоговое окно Запись макроса

· выполнить удаление с листа предыдущей информации;

· создать структуру таблицы, в которой будет содержаться список заказов, выполняемых предприятием «Инициатива», используя для ввода данных поля со списками, там где это необходимо;

· в ячейку D3 листа список заказовввести следующую формулу =ЕСЛИ(C3="";"";ПРОСМОТР(C3;заказчик;заказчики!$A$3:$A$8)) и скопировать ее в смежные ячейки вниз по столбцу;

· в ячейку G3 листа список заказовввести следующую формулу =ЕСЛИ(F3="";"";ПРОСМОТР(F3;товар;товары!$A$3:$A$8)) и скопировать ее в смежные ячейки вниз по столбцу;

· в ячейку I3 листа список заказовввести следующую формулу =ЕСЛИ(ЕОШИБКА(H3*ВПР(G3;товары!$A$3:$E$8;5;ЛОЖЬ));"";H3*ВПР(G3;товары!$A$3:$E$8;5;ЛОЖЬ)) и скопировать ее в смежные ячейки вниз по столбцу;

· по завершении выполнения требуемых действий необходимо выбрать вкладку Вид,а затем щелкнуть на стрелке направленной вниз рядом с кнопкой  и выбрать опцию Остановить запись;

· для того, чтобы с рабочей книгой мог работать пользователь, не являющийся разработчиком проекта, необходимо закрепить выполнение макроса за кнопкой , для этого нужно:

выбрать вкладку Разработчик,а затем щелкнуть на стрелке направленной вниз рядом с кнопкой Вставить (см. рис. 11) и в списке Элементы управления выбрать управляющий элемент Кнопка;

разместить управляющий элемент Кнопка на рабочем листе список заказови назначить ей макрос Список_заказов_бланк (см. рис. 12), переименовать кнопку, дать ей имя Бланк.

Рис. 11. Добавление элемента управления на лист MS Excel

Рис. 12. Назначение макроса элементу управления Кнопка: щелчок мыши по кнопке запустит на выполнение назначенный ей макрос

В результате с помощью щелчка мышью по кнопке Бланк можно сформировать бланк формы Список заказов (см. рис. 13). Следующим этапом решения задачи является ввод в бланк формы исходных данных (см. рис. 14).

Текст кода макроса Список_заказов_бланк приведен в приложении 4.

Рис. 13. Бланк формы Список заказов

 Рис. 14. Заполненная форма Список заказов

9. Добавить новый лист и переименовать в лист с названием список заказов фирма.Создание бланка формы «Форма заказа по названию фирмы-заказчика» необходимо выполнять автоматически, а следовательно необходимо создать с помощью макрорекордера макрос, который запишет последовательность действий, создающих бланк формы. Создание макроса с помощью макрорекордера и назначение его выполнения кнопке нужно выполнить аналогично предыдущему заданию. Результаты работы макроса по созданию бланка формы «Форма заказа по названию фирмы-заказчика» приведены на рисунке 15.

Рис. 15. Бланк формы «Форма заказа по названию фирмы-заказчика»

Обработку формы необходимо выполнить с помощью процедуры, блок-схема которой представлена на рисунке 16, а текст кода приведен в приложении 4.

Рис. 16. Блок-схема процедуры обработки формы «Форма заказа по названию фирмы-заказчика»

Порядок создания процедуры обработки формы «Форма заказа по названию фирмы-заказчика»:

перейти в редактор VBA выбрав вкладку Разработчик,а затем щелкнуть на кнопке ;

в окне Редактор Visual Basic(см. рис. 17) в меню редактора выбрать последовательно опции Insert ÞModule. В появившемся окне создать процедуру, которая решает поставленную задачу;

Рис. 17. Окно Редактор Visual Basic

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

проверку выполнения макроса выполнить щелкнув на кнопке  окна Редактор Visual Basic

результаты работы макроса, вызываемого на выполнение щелчком мыши по кнопке Обработать приведены на рисунке 18.

Рис. 18. Заполненная форма заказа по названию фирмы-заказчика

10. Добавить новый лист и переименовать в лист с названием форма заказа.Создание бланка формы «Форма заказа по номеру» необходимо выполнять автоматически, а следовательно необходимо создать с помощью макрорекордера макрос, который запишет последовательность действий, создающих бланк формы. Создание макроса с помощью макрорекордера и назначение его выполнения кнопке выполнить аналогично предыдущим заданиям. Результаты работы макроса по созданию бланка формы «Форма заказа по номеру» приведены на рисунке 19.

Рис. 19. Бланк формы «Форма заказа по номеру»

Обработку формы необходимо выполнить с помощью процедуры, блок-схема которой представлена на рисунке 20, а текст кода приведен в приложении 4.

Рис. 20. Блок-схема процедуры обработки формы «Форма заказа по названию фирмы-заказчика»

Порядок создания процедуры обработки формы «Форма заказа по номеру» аналогичен предыдущему заданию. Исходнымиданными для обработки является значение номера заказа, который вводят с использованием диалогового окна (см. рис. 21). Если введенный номер заказа отсутствует в списке заказов, то выполнение макроса прекращается и выводится сообщение об ошибке (см. рис. 22).

     Рис. 21. Диалоговое окно ввода                   Рис. 22. Сообщение об ошибке

Проверку выполнения макроса необходимо выполнить, щелкнув на кнопке  окна Редактор Visual Basic.Результаты работы макроса, вызываемого на выполнение щелчком мыши по кнопке Обработать приведены на рисунке 23.

Рис. 23. Заполненная форма заказа по номеру

11. Добавить новый лист и переименовать в лист с названием итого по фирмам.Создание бланка формы «Итоговые суммы заказов по фирмам-заказчикам» необходимо выполнять автоматически, а следовательно необходимо создать с помощью макрорекордера макрос, который запишет последовательность действий, создающих бланк формы. Создание макроса с помощью макрорекордера и назначение его выполнения кнопке выполнить аналогично предыдущим заданиям. Результаты работы макроса по созданию формы «Форма заказа по номеру» приведены на рисунке 24.

Рис. 24. Бланк формы «Итоговые суммы заказов по фирмам-заказчикам»

Обработку формы необходимо выполнить с помощью функции пользователя, блок-схема которой представлена на рисунке 25, а текст кода приведен в приложении 4.

Рис. 25. Блок-схема процедуры обработки формы «Итоговые суммы заказов по фирмам-заказчикам»

Порядок создания функции пользователя для обработки формы «Итоговые суммы заказов по фирмам-заказчикам» аналогичен предыдущим заданиям.

После ввода кода функции, необходимо перейти на лист MS Excel итого по фирмам и щелкнуть на ячейке F4, где будет рассчитываться итоговая сумма заказа для фирмы, наименование которой введено в ячейку С4. Затем вызвать мастер функции и в категории Определенные пользователем выбрать функцию общая_сумма_по_фирме (см. рис. 26), а затем в появившемся окне указать аргументы функции (см. рис. 27), скопировать полученную формулу в смежные ячейки.

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

Рис. 27. Диалоговое окно Аргументы функции для функции, созданной пользователем

Сформированная форма «Итоговые суммы заказов по фирмам-заказчикам» приведены на рисунке 28.

Рис. 28. Заполненная форма «Итоговые суммы заказов по фирмам-заказчикам»

ВАРИАНТЫ ЗАДАНИЙ ДЛЯ ВЫПОЛНЕНИЯ КУРСОВОЙ РАБОТЫ

ВАРИАНТ 1

Предприятие ООО «Энергосбыт» осуществляет деятельность, связанную с обеспечением электроэнергией физических и юридических лиц, и производит расчеты по предоставленным услугам. Информация о плательщиках, представлена на рис. 1. Тарифы на электроэнергию на рис. 2. Бланк расчета оплаты за электроэнергию приведен на рис. 3. ПоляКод плательщика, Месяц, Показания счетчика на начало месяца, Показания счетчика на конец месяцазаполняются пользователем. Необходимо:

1. построить таблицы по приведенным ниже табличным данным (рис. 1,2,3);

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

3. рассчитать и заполнить форму «Квитанция на оплату электроэнергии» (рис. 4), где с помощью макроса по коду плательщика из бланка расчета оплаты за электроэнергию выбирается и суммируется необходимая информация;

4. заполнить форму, в которой выполняется расчет среднего расхода электроэнергии для каждого плательщика (рис. 5); расчет для каждого плательщика выполнить с помощью функции, определенной разработчиком проекта.

Рис.1. Данные о плательщиках

Рис.2. Тарифы на электроэнергию