ЗАДАНИЕ №1

на распределенную учебную практику

в IV учебном семестре

С использованием теоретического материала, рекомендуемой основной и дополнительной литературы, а также материалов сети Интернет изучить теоретический материал, касающийся:
разработки макросов в MS Excel;
базового программирования на языке Visual Basic for Application (VBA);
итерационной обработки информации в таблицах MS Excel;
работы с элементами управления в MS Excel;
применения для обработки информации в таблицах MS Excel стандартных функций VBA;
В соответствии двумя последними цифрами пароля ΧΧ определить номер варианта индивидуального Nв задания по формуле (MOD – оператор получения остатка от деления):

Nв=(ΧΧ MOD 30)+1

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

ЗАДАНИЕ

1. В приложении А выбрать заданную таблицу. Номер варианта рассчитать как увеличенный на единицу остаток от деления цифровой части пароля на 30.
2. На листе «Лист1» при помощи инструмента Вид-Макросы записать макрос с сочетанием клавиш для вызова Ctrl+<Первая буква фамилии>, выполняющий построение заданной таблицы с типовыми заголовками столбцов и строк, семью пустыми строками для данных и итоговой строкой. В ячейке итоговой строки, соответствующей левому пустому столбцу, ввести формулу для расчета итоговых значений по столбцу в соответствии с индивидуальным заданием (см. приложение А). Формулу распространить на все ячейки итоговой строки, соответствующим пустым столбцам. После этого остановить запись макроса.
3. Выполнить макрос на листе «Лист2» и убедиться в корректности его работы. При некорректной работе исправить текст макроса, либо записать макрос заново.
4. На листе «Лист1» в пустой ячейке данных создать формулу, заполняющую данную ячейку случайным числом в заданном диапазоне из табл. 5.1. Формулу распространить на все ячейки данных посредством автозаполнения.
5. Открыть редактор Visual Basic, создать новый модуль и написать в нем функцию для вычисления итогового значения по строке в соответствии с индивидуальным заданием (см. приложение А). Количество и имена аргументов функции определить самостоятельно на основе формата таблицы.
6. В этом же модуле написать процедуру тестирования функции для вычисления итогового значения. Выполнить ее и убедиться в корректности работы функции. При некорректном выполнении исправить функцию.
7. На листе «Лист2» в верхней ячейке итогового столбца создать формулу для вычисления итогов по строке с использованием написанной в п.5 функции. Распространить формулу на другие ячейки итогового столбца посредством автозаполнения.

Контрольные вопросы и упражнения

1. Как можно запустить макрос на выполнение?
2. Какие существуют возможности для оформления внешнего вида таблицы Excel?
3. Каково назначение окна локальных переменных?
4. Каково назначение окна редактора кодов?
5. Как выглядит маркер отладчика?
6. Каково назначение окна проектов?
7. Как выполнить программу по шагам?
8. Какие команды существуют для выполнения программы и в чем их отличие?
9. Что такое точка останова?
10. Как при выполнении программы по шагам можно автоматически выполнить ее определенную часть?
11. Как запустить программу на выполнение из Excel?
12. Как можно наблюдать результаты выполнения программы в пошаговом режиме?

2. Автоматизация расчетов в таблицах MS Excel

Цель: изучить и освоить средства MS Excel для форматирования и настройки таблиц, освоение средств автоматизации операций в MS Excel для работы с таблицами.

ЗАДАНИЕ

1. В приложении Б выбрать заданные функции преобразования. Номер варианта рассчитать как увеличенный на единицу остаток от деления цифровой части пароля на 30.
2. Создать новую книгу MS Excel и сохранить ее в совей рабочей папке под именем Расчет функций <Фамилия студента>.
3. Разместить на листе «Лист 1» нем три ячейки для данных в соответствии с таблицей 2.3. Подписи к ячейкам разместить с левой стороны от ячейки.
4. Открыть редактор Visual Basic, создать новый модуль и написать в нем процедуру (Sub) Eval_1() для вычисления функции преобразования F1. В теле процедуры объявить переменные x, y и z вещественного типа. Значения для переменных x и y считывать из ячеек в соответствии с индивидуальным заданием (см. табл. 2.3). Рассчитанное значение z также записывать в заданную ячейку.
5. В этом же модуле написать процедуру Eval_2() с использованием структурного оператора ветвления для вычисления функции преобразования F2. Координаты ячеек для x и y такие же, как и при выполнении задания 4.
6. Перейти в книгу MS Excel и добавить на лист два элемента управления «Переключатель» (Разработчик/Элементы управления/Вставить), подписи переключателей установить «Функция 1» и «Функция 2» (Контекстное меню  Изменить текст), оба переключателя связать с ячейкой «ПК» (см. табл. 2.3) (Контекстное меню  Формат объекта…).
7. Добавить на лист элемент управления «Кнопка», для кнопки задать подпись «Расчет» и создать макрос для обработки события «Щелчок». В теле процедуры макроса написать код на VBA, который вызывает процедуру Eval_1() или Eval_2() в зависимости от положения переключателя (использовать структурный оператор ветвления, проверять значение ячейки «ПК», заданной в табл. 2.3).
8. Задать значения x, y, установить переключатель в положение «Функция 1» и выполнить расчет, нажав на кнопку, результаты зафиксировать в отчете.
9. Установить переключатель в положение «Функция 2» и выполнить расчеты при различных значениях аргумента x, результаты зафиксировать в отчете.
10. Проверить адекватность результатов выполнения заданий 8 и 9 на контрольном примере.

Контрольные вопросы и упражнения
1. Основные правила записи имен переменных и констант.
2. Виды переменных и констант.
3. Способы объявления (описания) переменных и констант.
4. Как связаны между собой типы данных Excel и VBA?
5. В чем необходимость использования данных типа Long и Double?
6. Как можно использовать свойство Cells() для организации обмена данными между Excel и VBA?
7. Типы данных, используемые при описании переменных и констант.
8. В чем необходимость использования данных типа Long и Double?
9. Как явно объявить переменную в тексте программы?
10. Какие слова в языке программирования называются ключевыми?
11. В чем недостатки метода объявления переменной по умолчанию?
12. Как включить режим обязательного явного объявления переменных?
13. В чем отличие между строчным и блочным вариантами оператора ветвления?
14. Можно ли вставлять инструкцию Else перед инструкцией ElseIF в блочном варианте оператора ветвления?
15. Сколько инструкций ElseIF может быть в блочном варианте оператора ветвления?
16. В чем преимущество оператора выбора варианта перед многовариантным оператором ветвления?
17. Чем отличается объявление функции от ее определения?
18. В чем заключается практический смысл использования функций или процедур?
19. Какой смысл имеет задание типа функции в ее определении?
20. Чем отличается вызов функции от вызова процедуры?
21. Как создать определенную пользователем функцию Excel?
22. Как результаты работы функции или процедуры могут быть получены в вызывающей программе?
23. Чем элемент управления Кнопка отличается от элемента управления Переключатель?
24. Чем элемент управления Поле со списком отличается от элемента управления Список?
25. Как добавить запись в элемент управления Список?
26. Как узнать, какое значение было выбрано оператором при работе со списком?
27. Чем элемент управления Поле отличается от элемента управления Надпись?
28. Как перейти от программирования событий элемента управления непосредственно к его работе?

3. Автоматизация обработки информационных массивов в MS Excel

Цель: изучение инструментальных средств MS Excel для автоматизированной генерации и обработки больших массивов информации посредством программирования на VBA.

ЗАДАНИЕ

1. В приложении Б согласно номеру своего варианта выбрать заданную функцию преобразования 2. Номер варианта рассчитать как увеличенный на единицу остаток от деления цифровой части пароля на 30.
2. Создать новую книгу MS Excel.
3. При помощи редактора Visual Basic на основе процедуры Eval_2(), разработанной при выполнении заданий раздела №2, написать функцию F2(x As Single) As Single, которая возвращает значение функции от аргумента x.
4. На листе 1 книги MS Excel в произвольном месте создать рабочие диапазоны, как показано на рис. 3.1. Для столбца, соответствующего ячейкам заголовка таблицы s задать формат ячеек «Числовой» с числом десятичных знаков: 5.
1. Создать на листе элемент управления «Кнопка» с надписью «Расчет», в обработчике события «Щелчок» написать код на VBA, который производит вычисления значений ряда
и накапливает сумму
и записывает результаты в таблицу на листе. Вычисления производить до тех пор, пока общий член ряда не окажется меньше заданной точности (
). Четным номерам вариантов использовать цикл с предусловием, нечетным – с постусловием.
2. Выйти из режима конструктора, задать точность и произвольное значение аргумента x. Нажатием на кнопку «Расчет» произвести расчет значений.
3. Создать копию листа 2, провести аналогичные вычисления при помощи формул MS Excel, и убедиться в корректности работы макроса.
4. Повторить вычисления для разных значений точности и аргумента, в том числе для таких, которые предполагают различные варианты расчета функции y(x). Зафиксировать результаты вычислений.
5. На листе 2 книги MS Excel в произвольном месте создать рабочие диапазоны, как показано на рис. 3.2. Для столбцов, соответствующих ячейкам заголовка таблицы x и y , а также для ячеек параметров и шага, задать формат ячеек «Числовой» с числом десятичных знаков: 3.

10. Создать на листе элемент управления «Кнопка» с надписью «Расчет», в обработчике события «Щелчок» написать код на VBA, который производит вычисления значений функции y от аргумента x , изменяющегося с заданным шагом (выбрать из приложения В), и записывает результаты в таблицу на листе. Использовать параметрический цикл For … Next.
11. Выйти из режима конструктора, задать параметры функции и значение шага x. Нажатием на кнопку «Расчет» произвести расчет значений.
12. По диапазону рассчитанных значений на листе 2 построить диаграмму типа График. На втором шаге на вкладке Ряд задать подпись ряда «Значения функции». На третьем шаге задать название диаграммы, легенду не отображать. На четвертом шаге диаграмму поместить на отдельном листе с подписью «График значений функции».
13. Изменить фон области построения на белый, при необходимости скорректировать шкалу вертикальной оси, изменить цвета и толщину линий.
14. Создать копию листа 2, провести аналогичные вычисления при помощи формул и встроенных функций MS Excel, и убедиться в корректности работы макроса.

Контрольные вопросы и упражнения
1. Что такое цикл и для чего он нужен?
2. Виды циклов, отличия и особенности использования.
3. Что такое логическое выражение?
4. Правила составления сложных логических выражений.
5. Как работает оператор ветвления?
6. Как работает оператор множественного выбора?
7. Что такое тело цикла?
8. Что такое параметрический цикл?
9. Структура заголовка параметрического цикла.
10. Что такое итерация?
11. Оператор досрочного выхода из цикла и особенности его использования.
12. В чём основное отличие между циклами с предусловием и с постусловием?
13. В каких случаях целесообразно использовать циклы с предусловием, циклы с постусловием и циклы по счётчику?
14. В чём отличие между имеющимися вариантами циклов с предусловием?
15. В чём отличие между имеющимися вариантами циклов с постусловием?
16. Что такое сложный цикл и каковы основные правила его конструирования?
17. В чем заключается необходимость применения операторов цикла в программировании?
18. В каких случаях целесообразно использовать операторы Do Loop While с постусловием?
19. В каких случаях целесообразно использовать оператор For To Next?
20. Как можно запрограммировать бесконечно выполняющийся цикл?

Варианты индивидуальных заданий к разделу №1

Вариант №1
ФИО продавца Отчисления от продажи, % Сумма продажи товаров, руб. Итог
Июль Август Сентябрь Октябрь Ноябрь Декабрь
Петров 5
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: денежный.
Рассчитать % денег, заработанных каждым продавцом в декабре от общей выручки за декабрь; формат числа: процентный.

Вариант № 2
Город Стоимость 1 мин, руб. Продолжительность переговоров (мин.) Итог
ПН. ВТ СР ЧТ – СБ ВС
Москва 0,30
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать стоимость переговоров за неделю с каждым городом; формат числа: денежный.

Вариант № 3
ФИО Средняя з/п за прошлый год Заработная плата за текущее полугодие Итог
Январь Февраль Март Апрель Май Июнь
Антонов 583
Формула вычисления итогов по столбцам: СРЗНАЧ; формат числа: денежный.
Рассчитать прирост средней зарплаты по каждому работнику по сравнению с прошлым годом; формат числа: денежный.

Вариант № 4
Изделие Себестоимость Затраты, тыс. руб. Итог
Материалы Энергия Зарплата
Станок 32
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: денежный.
Рассчитать разницу между суммарными затратами и себестоимостью по каждому изделию; формат числа: вещественный.

Вариант № 5
Материал Норма расхода Расход за месяц, т Итог
Январь Февраль Март Апрель Май Июнь
Цемент 200
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: целый.
Рассчитать перерасход в январе по каждому материалу по сравнению с нормой расхода; формат числа: вещественный.

Вариант № 6
Группа К-во проп. зан. за 1 полугодие прошлого года К-во пропущенных занятий в текущем году Итог
Январь Февраль Март Апрель Май Июнь
ФБИ-51 428
Функция для вычисления итоговых значений по столбцам: MIN; формат числа: целый.
Рассчитать прирост пропускаемых занятий в каждой группе по сравнению с предыдущим годом; формат числа: вещественный.

Вариант №7
Изделие Количество за прошлую неделю К-во изделий (текущая неделя) Итог
ПН ВТ СР ЧТ ПТ
Телевизор 130
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать прирост выпуска каждого изделия по сравнению с предыдущей неделей; формат числа: вещественный.

Вариант №8
Областной центр К-во жителей на 31.12.92 г. Прирост населения в тыс. чел. Итог
1993 1994 1995 1996 1997
Омск 1180
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: целый.
Рассчитать % прироста населения за пятилетие по сравнению с 1992 г. по каждому городу; формат числа: процентный.

Вариант №9
Название шахты Общее к-во работников Количество травмированных работников Итог
1 кварт. 2 кварт. 3 кварт. 4 кварт.
«Южная» 1500
Функция для вычисления итоговых значений по столбцам: СРЗНАЧ; формат числа: вещественный.
Рассчитать общее число травмированных работников за год по каждой шахте; формат числа: целый.

Вариант №10
Бригада К-во рабочих Добыча бригады, т Итог
ПН. ВТ СР ЧТ ПТ
Галкина 15
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: вещественный.
Рассчитать среднее количество угля, добываемое одним рабочим каждой бригады за неделю; формат числа: вещественный.

Вариант №11
Город Цена билета Количество пассажиров Итог
Октябрь Ноябрь Декабрь Январь Февраль Март
Волгоград 25
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать общую стоимость всех билетов по каждому городу; формат числа: денежный.

Вариант №12
Изделие Стоимость 1 шт., руб Количество поставленных изделий Итог
Январь Февраль Март Апрель Май Июнь
Сервиз 120
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать стоимость произведенных поставок за полугодие по каждому изделию; формат числа: денежный.

Вариант №13
Шифр товара Продано за прошлый год Продано по кварталам Итог
1 2 3 4
1-258 700
Функция для вычисления итоговых значений по столбцам: МИН; формат числа: целый.
Рассчитать прирост продаж по сравнению с прошлым годом; формат числа: вещественный.

Вариант №14
Цех Стоимость 1 дня, руб. Количество дней по больничному Итог
Октябрь Ноябрь Декабрь Январь Февраль Март
№1 15
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать сумму выплат по больничным листам за все месяцы по каждому цеху; формат числа: денежный.

Вариант № 15
Код продукции Норма потребления Душевое потребление продукции Итог
1960 1975 1980 1985 1990
010 130
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый.
Рассчитать превышение нормы потребления в 1980 году по каждому виду продукции; формат числа: целый.

Вариант №16
Изделия Стоимость
1 шт. Количество поставленных изделий Итог
Январь Февраль Март Апрель Май Июнь
Столы 25
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый
Рассчитать общую сумму поставок по каждому изделию; формат числа: вещественный.

Вариант №17
Цех Стоимость 1 дня Количество дней по больничному листу Итог
Октябрь Ноябрь Декабро Январь Февраль Март
Литейный 15
Функция для вычисления итоговых значений по столбцам: СРЗНАЧ; формат числа: вещественный.
Рассчитать сумму выплат по больничным листам за все месяцы по каждому цеху; формат числа: денежный.

Вариант №18
Заболевание Продолжительность лечения Стоимость 1 дня лечения, руб. Итог
2011 2012 2013 2014 2015
Грипп 14
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый
Рассчитать среднюю сумму лечения по каждому заболеванию за 2011 – 2015 гг.; формат числа: вещественный.

Вариант № 19
Код товара Продано, ед. Цена за 1 ед., руб. Остаток на складе, тыс. руб. Итог
А123 125 12 19,57

Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: денежный
Рассчитать сумму остатка товара на складе после продажи по каждому товару; формат числа: вещественный.

Вариант № 20
Наименование быттехники Кол-во Оплата за прокат в месяц, руб. Итог
Июнь Июль Август Сент. Октябрь
Холодильник 4
Функция для вычисления итоговых значений по столбцам: СРЗНАЧ; формат числа: процентный.
Рассчитать среднюю стоимость проката одного вида бытовой техники; формат числа: вещественный.

Вариант №21
Наименование факультета Кол-во студентов Оплата за год Итог
1 курс 2 курс 3 курс 4 курс 5 курс
Экономический 500
Функция для вычисления итоговых значений по столбцам: СРЗНАЧ; формат числа: вещественный.
Рассчитать среднюю стоимость годовой оплаты по каждому факультету; формат числа: вещественный.

Вариант №22
Специальность Оплата за год Количество платных студентов Итог
1 курс 2 курс 3 курс 4 курс 5 курс
Менеджмент 600
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: целый
Рассчитать среднюю стоимость годовой оплаты по каждой специальности; формат числа: вещественный.

Вариант №23
Наименование Розничная цена Кол-во проданного товара Итог
Март Апрель Май Июнь
Карандаш 0,4
Функция для вычисления итоговых значений по столбцам: МИН; формат числа: целый
Рассчитать общую сумму продаж по каждой товарной позиции; формат числа: вещественный.

Вариант №24
Наименование продукции Выпуск продукции, млн. т. Итог
2011 2012 2013 2014 2015
Масло
Функция для вычисления итоговых значений по столбцам: СРЗНАЧ
Рассчитать общий объем выпуска по каждому виду продукции; формат числа: целый.

Вариант №25
Курс обучения Вид занятий Итог
Лекции, час. Лаб. раб., час. Практ. раб., час
1 курс
Функция для вычисления итоговых значений по столбцам: МИН; формат числа: целый
Рассчитать отношение объема практических и лабораторных занятий к объему лекционных занятий; формат числа: вещественный.

Вариант №26
Название книги Тираж, тыс Продано книг Итог
Янв. Февр. Март Апр. Май
Информатика 120
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый
Рассчитать остаток в тираже по каждой товарной позиции; формат числа: вещественный.

Вариант №27
Название игрушки Цена, руб. Количество проданных игрушек Итог
1 декада 2 декада 3 декада
Кукла «Маша» 419,30
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: целый
Рассчитать общую сумму продаж по каждой товарной позиции; формат числа: вещественный.

Вариант №28
Название путевки Стоимость Продано Итог
Июнь Июль Август Сентябрь
Кисловодск 450,00
Функция для вычисления итоговых значений по столбцам: СУММ; формат числа: целый
Рассчитать общую сумму продаж по каждой путевке; формат числа: вещественный.

Вариант №29
Номер по каталогу Наименование Количество Цена, руб. Итог
Октябрь Ноябрь Декабрь Январь
55 Куртка женская 120,0
Функция для вычисления итоговых значений по столбцам: МИН; формат числа: целый
Рассчитать среднюю сумму продаж с октября по январь по каждой товарной позиции; формат числа: вещественный.

Вариант №30
Наименование предприятия Добыча железной руды Итог
К-во рабочих в бригаде, чел. Добыча руды бригадой, т
«Северная» 16
Функция для вычисления итоговых значений по столбцам: МАКС; формат числа: целый
Рассчитать добычу руды на одного рабочего, т/чел; формат числа: вещественный.

Варианты индивидуальных заданий к разделам №2 и №3

Номер варианта Функция преобразования 1 Функция преобразования 2
1 z=(x+44)*y

2 z=x*(y-32)

3 z=13-x-y/2

4 z=25*x+37*y

5 z=67*x*y+8

6 z=33*x+y*(x+28)

7 z=x*(y-17)

8 z=(x-1)*(y+1)

9 z=27/(x+y*2)

10 z=x/3-y/4+1

11 z=(x+44)*27-y/2

12 z=25/x-100/y

13 z=31+x*(x+y)

14 z=21/(x+1)-1

15 z=88+x-100/y

16 z=(x+y)/(x-y)

17 z=x*x*x-y/3

18 z=(78-3*x-2*y)/y

19 z=(x+y)/(x*x+x*y+y*y)

20 z=x/(7-x)+3*y

21 z=(61*x+y)/x/y

22 z=22-(x*y)/(x+y)

23 z=58*(x+3)-15*y

24 z=33-x-1/(7*x+3*y)

25 z=(20+x)/(5+y)

26 z=x/(x+5*y-2)

27 z=44/(x+y+1)

28 z=(2*x+3*y+5)/7

29 z=57-x+1/y

30 z=20-10/x+y

Варианты индивидуальных заданий к разделу №3

Номер варианта Функция Диапазон и шаг изменения аргумента
1

x[0,3; 3], x = 0,15
2
x[-1; 2], x = 0,15
3
x[0,8; 2], x = 0,1
4
x[1; 1,5], x = 0,05
5
x[1; 3], x = 0,1
6
x[0,2; 4], x = 0,2
7
x[0; ], x = /20
8
x[1; 4], x = 0,2
9
x[-5; 3], x = 0,5
10
x[0; 2], x = /10
11
x[2; 7], x = 0,2
12
x[0; /2], x = /60
13
x[0,5; 3,5], x = 0,25
14
x[0,25; 2], x = 0,05
15
x[0; 3/2], x = /10
16
x[-3; -1], x = 0,1
17
x[2; 3], x = 0,05
18
x[1; 4], x = 0,2
19
x[-5; -4], x = 0,025
20
x[0; 2/3], x = /40
21
x[0; /2], x = /40
22
x[0; 2/3], x = /30
23
x[0,5; 2,5], x = 0,1
24
x[0; ], x = /30
25
x[-5; 1], x = 0,25
26
x[1,5; 3], x = 0,05
27
x[0; 2], x = /15
28
x[0,25; 5,25], x = 0,25
29
x[0; /4], x = /60
30
x[-2,5; 2,5], x = 0,25