|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Кафедра прикладной математикиСтр 1 из 8Следующая ⇒
ФЕДЕРАЛЬНОЕ АГЕНСТВО ВОЗДУШНОГО ТРАНСПОРТА ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ГРАЖДАНСКОЙ АВИАЦИИ МГТУ ГА
Кафедра прикладной математики Е.П. Пегова
Методические указания к выполнению лабораторных работ по дисциплине
для студентов I курса специальности 080507
Москва – 2006 ББК 6Ф6.5 П 23
Рецензент: кандидат технических наук , доцент Кишенский С.Ж..
Пегова Е.П. П 23
21 Методические указания к выполнению лабораторных работ по дисциплине ИНФОРМАТИКА для студентов I курса специальности 080507 IV курса специальности 030602 дневного обучения. М.: МГТУ ГА, 2006.- 72с.
Данное пособие издаётся в соответствии с учебной программой для студентов 1 курса специальности 080507 дневного обучения и lV курса специальности 030602 дневного обучения. Рассмотрено и одобрено на заседании кафедры ПМ № 1 от 06.10.06 и методического совета факультета ЭВМ по специальности 230401 №1 от 06.10.06.
Редактор: Поталова ЛР№ от . . г. Подписано в печать . . г. Печать офсетная Формат 3,62 изд.л 4,19 Печ.л. Заказ Тираж 200 экз.
Московский государственный университет ГА Редакционно-издательский отдел 125493 Москва, ул. Пулковская, д.6а
Московский государственный Содержание Введение. 5 Лабораторная работа 1. 6 Рабочее окно программы EXCEL. .................................................... 6 Маркёры. 7 Прогрессии. 9 Форматирование ячеек. Ввод вторичных данных. 9 Упражнение 1. «Обменный пункт». 9 Построение графиков. ........................................................................ 13 Упражнение 2. «График линейной функции». 13 Самостоятельная работа 1. 15 Самостоятельная работа 2. 16 Абсолютные ссылки. 16 Упражнение 3. «Мороженое». 17 Упражнение 4. «Геометрическая прогрессия». 19 Самостоятельная работа 3. «Расчёт электроэнергии». 20 Упражнение 5. «Таблица умножения». 21 Самостоятельная работа 4. «Таблица квадратов». 22 Дополнительные упражнения к лабораторной 1. 23 Самостоятельная работа 5. 23 Самостоятельная работа 6. 23 Самостоятельная работа 7. 24 Лабораторная работа 2. 25 Мастер функций. ................................................................................ 25 Упражнение 6. «Результаты вступительных экзаменов». 25 Самостоятельная работа 8. 28 Упражнение 7. «Возраст». 28 Самостоятельная работа 9. 33 Фильтры. 33 Упражнение 8. «Аукцион». 33 Самостоятельная работа 10. 37 Дополнительные задания к лабораторной 2. ................................... 40 Самостоятельная работа 11. 40 Самостоятельная работа 12. 40 Самостоятельная работа 13. 40 Самостоятельная работа 14. 41 Упражнение 9. «Табель». 42 Лабораторная работа 3. 44 Подбор параметра. ............................................................................ 44 Упражнение 10. «Проходной балл». 45 Упражнение 11. «Корни кубического уравнения». 46 Самостоятельная работа 15. 47 Поиск решения . 48 Упражнение 12. «Закупки». 49 Самостоятельная работа 16. 51 Упражнение 13. «Предприятие» . 51 Самостоятельная работа 17. 53 Дополнительные задания к лабораторной 3. 53 Самостоятельная работа 18. 53 Самостоятельная работа 19. 53 Лабораторная работа 4. 54 Макросы. 54 Макросы, создаваемые с помощью макрорекордера...................... 55 Упражнение 14. «День рождения». 55 Размещение элементов управления на листе . 58 Упражнение 15. «Бабочка». 58 Самостоятельная работа 20. 60 Макросы в диаграммах. .................................................................... 60 Упражнение 16. «Анализ функции». 60 Упражнение 17. «Звёзды». 64 Самостоятельная работа 21. 67 Составление макросов с помощью написания кода в Visual Basic.. 67 Упражнение 18. «Таблица истинности». 67 Самостоятельная работа 22. 68 Дополнительные задания к лабораторной 4. 69 Самостоятельная работа 23. 69 Упражнение 19. «Журнал пользователей». 69 Самостоятельная работа 24. 71 Самостоятельная работа 25. 72 Литература 72 Введение. Целью данного курса является освоение навыков работы в электронных таблицах (Microsoft Excel).Изучение этой программы имеет основную направленность на делопроизводство, но также включает дополнительный модуль «Знакомство с языком программирования Visual Basic for Applications, составление макросов». Эта тема является частью углублённого курса изучения офисных программ и открывает новые возможности и горизонты их использования. Курс состоит из набора упражнений, имеющих подробное описание порядка выполнения, и самостоятельных работ по пройденному материалу, не имеющих подробных инструкций для выполнения. Каждая лабораторная работа имеет своей целью освоение знаний и умений по определённым темам, которые перечислены в разделе «Цель работы». В основном знания приобретаются в процессе выполнения упражнений, но некоторые темы, например, абсолютные ссылки, или основы Visual Basic , требуют более развёрнутого объяснения, и краткая теория этих тем представлена в данном методическом пособии. Методическое пособие состоит из четырёх лабораторных работ. Каждая лабораторная работа рассчитана на четыре академических часа. Время лабораторных работ выверено в результате многолетнего использования методики в различных аудиториях учащихся и ориентировано для студентов не технических специальностей, например, экономистов. Каждое новое упражнение внутри раздела построено на базе предыдущих усвоенных знаний, но обязательно содержит определённый процент новой информации (отработка темы – не простое копирование, а продвижение вперёд небольшими шагами с опорой на предыдущее). В конце каждой лабораторной работы есть раздел «Дополнительные задания», содержащий самостоятельные работы и упражнения, которые преподаватель может использовать при необходимости более точной оценки знаний учащихся. Каждая лабораторная работа заканчивается разделом «Вопросы для самоконтроля», предназначенным для закрепления знаний учащихся по пройденному материалу. Для выполнения некоторых упражнений лабораторной работы 4 необходимо установить на компьютере файлы – заготовки. Это графические файлы с изображением бабочки с открытыми крыльями, закрытыми крыльями и звёздного неба. Лабораторная работа 1. Темы работы. 1. Рабочее окно программы EXCEL. 2. Маркёры. 3. Прогрессии. 4. Форматирование ячеек. Ввод вторичных данных. 5. Построение графиков. 6. Абсолютные ссылки. Маркёры. В процессе работы в электронных таблицах указатель мыши выглядит по – разному : то он принимает вид белого крестика, то маленького чёрного , то стрелки или черного крестика со стрелками, то становится мигающим курсором для ввода текста как в Word. В зависимости от этого изменяются и назначения маркёра. Перечислим все состояния маркёра и назначения каждого из них. · ·
Выделенная ячейка или область имеет черную рамку с маленьким квадратиком в нижнем правом углу (рис 5) Добиться появления маркёра заполнения можно, если навести мышь на этот квадратик.
Рис. 5 Приёмы работы с маркёром заполнения сведены в табл.1 . Таблица 1.
· · Курсор ввода – мигающий курсор. Он появляется, когда ячейка находится в режиме ввода. При этом становится неактивной панель ФОРМАТИРОВАНИЕ (см. описание строки формул). Прогрессии. Кроме использования маркёра заполнения есть ещё одна возможность построения последовательностей – с помощью диалогового окна «Прогрессия»: ПРАВКА – ЗАПОЛНИТЬ – ПРОГРЕССИЯ… (рис. 6 ).
Рис.6 Обменный пункт | |||||||||||||||||||||||
| 2 | |||||||||||||||||||||||
| 3 | На сегодня, | 23.мар | Курс продаж | 27,50 р. | За 1$ | ||||||||||||||||||
| 4 | |||||||||||||||||||||||
| 5 | Курс покупки | 27, 85 р. | За 1$ | ||||||||||||||||||||
| 6 | 
| ||||||||||||||||||||||
| 7 | Сумма в рублях, предложенная на покупку $ | 100 р. | Эквивалентная сумма в $ | ||||||||||||||||||||
| 8 | |||||||||||||||||||||||
| 9 | Сумма в долларах, предложенная на продажу | $ 300 | Эквивалентная сумма в рублях | ||||||||||||||||||||
| 10 |
Рис. 7
3. В ячейку В3 внести дату, например 23.03(программа сама преобразует число месяца в строку). В ячейки С3 и С5 внести «Курс продаж» и «Курс покупки» соответственно. В ячейки Е3 и Е5 внести «за 1 $».
4. В ячейки D3 и D4 внести числа 27,50 и 27, 85 соответственно. Затем выделить эти ячейки ( удерживая Ctrl) и заказать денежный формат, щёлкнув по кнопке с контекстной подсказкой «Денежный формат» на панели «форматирование» (рис. 8).
Рис. 8
5. В ячейку А7 внести запись « Сумма в рублях, предложенная на покупку $». Зафиксировать значение и заказать перенос по словам. Для этого нужно выбрать пункт меню «Формат - ячейки», затем выбрать закладку» выравнивание и установит переключатель против фразы «переносить по словам». Закрыть диалог. При необходимости отрегулировать ширину столбца.
Для того, чтобы текст выводился по центру ячейки, в бланке «Выравнивание» нужно заказать выравнивание по горизонтали и вертикали ( рис. 9).
Рис. 9
6. Аналогичным образом заполнить ячейки А9, С7, С9.
7. В ячейку В7 внести 100, зафиксировать и заказать денежный формат как для ячеек D3, D5.
8. В ячейку В9 внести 300, зафиксировать. Чтобы сделать денежную размерность $, нужно выделить ячейку, затем выбрать пункт меню «формат - ячейки», выбрать закладку «Число», указать на «денежный» числовой формат, затем развернуть список обозначений и выбрать, например, английский денежный формат (рис.10).
Рис. 10
9. В ячейках D7 и D9 должны формироваться вторичные данные, т.е. данные, вычисляемые по формулам в зависимости от первичных.
· Чтобы сформировать вторичные данные в ячейке D7, нужно указать на неё, набрать с клавиатуры знак =, затем указать на ячейку В7, набрать с клавиатуры знак / ( в правой клавиатуре), а затем указать мышкой на ячейку D5 и зафиксировать ввод (нажать Enter). В результате в строке формул появится запись =В7/D5, а в ячейке результат вычисления 3,59 р. Заказать долларовую размерность как в п.8.
· Чтобы сформировать вторичные данные в ячейке D9, нужно указать на неё, набрать с клавиатуры =, затем указать на ячейку В9, затем набрать с клавиатуры знак умножения * (в правой клавиатуре), и потом указать на ячейку D3. В результате в строке формул появится запись =В9*D3, а в ячейке D9 результат вычисления 8250. Заказать рублёвый формат.
10. Вставить изображение для оформления таблицы. Знак доллара находится в галерее картинок, поставляемой с пакетом OFFICE 2003. Его можно вставить как файл (ВСТАВКА – РИСУНОК ИЗ ФАЙЛА), расположенный по следующему пути:
С:\Program Files\Microsoft Office\Media\CAGCAT10\J0222015.WMF
Можно выбрать рисунок непосредственно из галереи. Для этого нужно дать команду ВСТАВКА – РИСУНОК – КАРТИНКИ, затем в правой части окна щёлкнуть на ссылку «Упорядочить картинки», в появившемся списке раскрыть папку «Коллекции Microsoft Office», раздел «Бизнес», найти картинку с долларом и перетащить её на лист.
11. Закончить оформление таблицы, выполнив заливку и обрамление ячеек как в задании.
12. Переименовать лист, щёлкнув два раза отрывисто по закладке листа и ввести новое имя «Обменный пункт».
13. Теперь следует защитить таблицу от случайных изменений. Оператор должен будет вносить значения только в ячейки В3, В7, В9, D3 и D5. Чтобы оператор случайно не нарушил формулы и форматирование ячеек, следует поступить так:
1) выделить ячейки В3, В7, В9, D3,D5 (удерживая клавишу CTRL).
2) выбрать пункт меню ФОРМАТ – ЯЧЕЙКИ. В разделе ЗАЩИТА убрать галочку против фразы «Защищаемая ячейка».
3) выбрать пункт меню СЕРВИС – ЗАЩИТА – ЗАЩИТИТЬ ЛИСТ. В списке «Разрешить всем пользователям этого листа» установить галочку против фразы «Выделение незаблокированных ячеек». Пароль на снятие защиты назначать не обязательно.
14. Сохранить книгу под своей фамилией.
15. Назначить пароль на открытие файла через пункт меню «Сервис - параметры - безопасность». Не рекомендуется составлять пароль из букв русского алфавита. Лучше составить пароль из цифр и английских букв.
16. Закрыть файл, затем открыть и убедиться, что запрашивается пароль.
Построение графиков.
Порядок выполнения.
1. Выбрать лист 2 и переименовать его в «Таблицы значений функций».
2. Выделить ячейку А1 и внести в неё текст заголовка. Зафиксировать значение. Выделить область А1- N1 и разместить текст по центру выделенной области, щёлкнув по кнопке с контекстной подсказкой «Объединить и поместить в центре» на панели «Форматирование» (рис 12).
Рис. 12
3. Внести в ячейки А3, А4 «х» и «у» соответственно.
4. Внести в ячейку В3 число -6. Зафиксировать значение, выделить ячейку В3, навести мышь на левый нижний угол ячейки В3 и добиться появления маркёра заполнения. Затем, удерживая клавишу CTRL, потянуть маркёр вправо до ячейки N3. Все ячейки должны заполниться значениями от -6 до 6 шагом 1.
5. Чтобы сделать таблицу более компактной, нужно выделить область значений по х , затем выбрать пункт меню «Формат – столбец –автоподбор ширины».
6. Установить курсор в ячейке В4, набрать с клавиатуры = и сформировать вторичные данные в соответствии с заданием:
=3*В3+9,5
Адрес В3 удобнее не вводить с клавиатуры (буква В должна быть набрана обязательно на английской раскладке), а указать мышкой на ячейку, адрес которой нужно ввести, и адрес пропишется автоматически.
Зафиксировать данные .
7. Навести мышь на ячейку В4, добиться появления маркёра заполнения и потянуть вправо до ячейки N4. Чтобы увеличить ширину столбцов, нужно, не снимая выделения, дать команду автоподбор ширины как в п.5.
8. Не снимая выделения с области В4-N4, запустить мастер диаграмм (рис. 13).
Рис. 13
Выбрать тип диаграммы «График» - обычный (самый первый в списке). Затем «далее».
9. В следующем шаге выбрать закладку «Ряд».Установить курсор в окне «Подписи оси Х», щёлкнуть по кнопке 
. При этом откроется лист с таблицей значений и нужно выделить область аргументов В3-N3.Нажать Enter. Эта область будет внесена в окно «Подписи оси Х» для соответствующих значений У. Затем можно щёлкнуть по кнопке «Готово».
10. Для того, чтобы сдвинуть ось У в точку 0 по оси Х, нужно выделить ось Х, нажать правую кнопку мыши, выбрать «Формат оси», в появившемся диалоге выбрать закладку «Шкала» и установить значение «Пересечение с осью У .. в категории» 7. Число 7 выбирается потому, что в таблице значений Х=0 в седьмой по счёту точке (категории).
11. Для того, чтобы по оси У выводились целые числа, нужно выделить ось У , щёлкнуть правой кнопкой мыши, выбрать «Формат оси», закладку «Число» и заказать число десятичных знаков 0.
12. Чтобы сделать градиентную заливку области построений, нужно выделить область построения, щёлкнуть правой кнопкой мыши, выбрать «Формат области построения», закладку «Заливка» и настроить диагональную заливку из двух цветов от желтого к голубому.
13. Чтобы сделать линию графика большей толщины, нужно выделить линию графика, щёлкнуть правой кнопкой мыши, «формат рядов данных», закладку «Вид» настроить толщину линии графика.
14. Надпись Ряд 1 удалить , т.к. в области построений изображен всего один график.
Самостоятельная работа 1.
Задание. Построить таблицу значений и график квадратной функции
у = 3х2 -10 в пределах -12 £ Х £ 12 шагом 2 (рис. 14).
Рис.14
Пояснения к работе.
· Для заполнения значений по Х следует воспользоваться приёмом работы с маркёром заполнения для получения арифметической прогрессии, т.е. заполнить две ячейки по Х, затем выделить обе ячейки, добиться появления маркёра заполнения и потянуть вправо.
· Чтобы в заголовке двойку поместить на верхний индекс, нужно набрать текст, затем в строке формул выделить эту двойку и выбрать пункт меню «Формат - ячейки», закладку «шрифт» и поставить галочку против слова «Надстрочный». Затем ОК.
· При формировании вторичных данных знак степени записывается как во всех языках программирования ^, например :
=-3*В3^2-10
Самостоятельная работа 2.
Задание. Построить таблицу значений кубической функции у= 1,2х3+3,6 в пределах -6 £ Х £ 4,5 шагом 1,5. Построить график. (рис. 15).
Рис.15
Абсолютные ссылки.
Маркёр заполнения изменяет адреса в формулах последующих ячеек относительно предыдущих ячеек. Такой подход называется «относительными ссылками». Но в некоторых ситуациях не нужно, чтобы адреса в формулах изменялись. Символом, замораживающим адрес в формуле, в электронных таблицах принят знак доллара - $. Если этот знак стоит перед буквой столбца, то при протяжении маркёром заполнения в формуле не будет меняться столбец, если этот знак поставить перед цифрой строки, то не будет меняться строка, соответственно можно заморозить адрес абсолютно:
o $A5- заморожен столбец.
o D$10- заморожена строка.
o $C$7 – абсолютно замороженный адрес.
Процесс расстановки абсолютных ссылок иногда требует сложных логических рассуждений (упражнения «Таблица умножения», «Таблица квадратов»). Задача упрощается, когда адрес нужно заморозить абсолютно, например, поделить все значения на общий объём продаж (упражнение «Мороженное»), или умножить на тариф (самостоятельная работа «Расчёт электроэнергии»).
Упражнение 3. «Мороженое».
Задание. Создать таблицу и диаграмму по образцу на рис. 16. Правильно определить первичные и вторичные данные. Оформление таблицы выполнить с помощью библиотеки форматов.
Рис.16
Порядок выполнения:
1. В ячейку А2 внести текст заголовка таблицы.
2. В ячейку А3 внести текст «Лето 2004».
3. В ячейки А4, В4,..G4 внести заголовки столбцов соответственно. Там где требуется задать перенос по словам. Не нужно закрашивать шрифт и ячейки, т.к. оформление таблицы можно сделать с помощью автоформата.
4. Заполнить таблицу первичными данными (табл.2). Для внесения месяца и номера по порядку можно воспользоваться приёмами работы с маркером заполнения.
Таблица 2.
| №пп |
Округ
Июнь
Июль
Август
1
Центральный
140
160
120
2
Западный
85
80
100
3
Северный
120
135
140
4
Южный
110
115
105
5
Северо-Восточный
80
100
78
Всего за месяц
5. Итоговые значения «Всего за месяц» и «Всего за лето» подсчитывать с помощью кнопки «Автосуммирование» на панели «Стандартная» (рис.17).
. Рис.17
6. Процент от общей продажи за лето подсчитывать, используя абсолютные ссылки в формуле:
= F 5/ F $10
Знак абсолютной ссылки ($) в знаменателе применён для того, чтобы продажи за лето по округу всегда делились на общий процент продаж (1 668). Для результатов вычислений задать процентный формат числа. Для этого выделить область G5:G10 и щёлкнуть по кнопке с процентами на панели ФОРМАТИРОВАНИЕ (рис. 118).
Рис. 18
7. Оформление таблицы выполнить с помощью библиотеки форматов : выделить область таблицы А4:G10, затем выбрать пункт меню формат – автоформат. Выбрать стиль оформления «Список 1».
8. Построить диаграмму. Выделить область G5:G9 и запустись мастер диаграмм. Выбрать тип диаграммы – круговая, далее «объёмный вариант разрезной диаграммы», затем щёлкнуть по кнопке ДАЛЕЕ. В следующем бланке выбрать закладку РЯД, установить курсор в строке ПОДПИСИ ПО ОСИ Х и указать на область В5:В9. Затем ДАЛЕЕ.
В следующем бланке выбрать закладку ПОДПИСИ ДАННЫХ. Установить галочки против фраз ИМЕНА КАТЕГОРИЙ и ЗНАЧЕНИЯ. Затем ДАЛЕЕ, разместить диаграмму на том же листе.
Порядок выполнения.
1. В ячейки А1 и В1 внести заголовки столбцов.
2. В ячейки А1 и В2 внести начальное значение прогрессии – 3.
3. Выделить область А2:А12. Выбрать пункт меню Правка – Заполнить – Прогрессия… Заполнить бланк прогрессии (рис. 20). Область заполнится членами прогрессии.
4. Установить курсор в ячейке В3. Сформировать в ней формулу:
=В2*2
5. Зафиксировать значение в ячейке В3, добиться появления маркёра заполнения в этой ячейке, потянуть вниз до ячейки В12. Область заполнится прогрессией.
Рис. 19
Рис. 20
Результаты в обоих столбцах получились одинаковые, но в случае автозаполнения данные являются первичными, т.е. не связанными, а во втором случае данные связаны, и при изменении значения в одной ячейке изменятся значения в последующих.
Самостоятельная работа 3. «Расчёт электроэнергии».
Задание. Создать таблицу по образцу на рис. 21. Правильно определить первичные и вторичные данные. Использовать при оформлении таблицы автоформат. Построить диаграмму в соответствии с рис. 22.
Пояснения к выполнению.
1. Правильно определить первичные и вторичные данные.
2. При внесении месяца и даты использовать маркёр заполнения.
3. Расход подсчитывается как разность между показанием счётчика данного месяца и предыдущего.
4. Сумма к оплате подсчитывается с использованием абсолютных ссылок.
5. В оформлении таблицы использовать автоформат «Список2».
6. В заливке диаграммы использовать заготовку «Спокойная вода».
Рис.21
Рис. 22
Упражнение 5. «Таблица умножения».
Задание. Составить таблицу умножения целых чисел от 1 до 10 , используя абсолютные ссылки (Рис. 23).
Порядок выполнения.
1. А ячейку А1 внести текст заголовка. Зафиксировать данные. Центрировать текст по области А1:К1.
2. Заполнить значениями от 1 до 10 область В2:К2.
3. Заполнить значениями от 1 до 10 область А3:А12.
4. Установить курсор в ячейке В3 и создать формулу:
=А3*В2
В ячейке В3 появится значение 1.
Рис.23
5. Чтобы можно было воспользоваться маркёром заполнения для формирования значений других ячеек, нужно в исходной формуле правильно расставить абсолютные ссылки. При движении маркёра вправо не должно изменяться значение адреса А3. Но значение строки и так не будет меняться. Значит, знак абсолютной ссылки следует поставить перед значением столбца ($А3). При движении маркёра вниз не должен изменяться адрес В2. Но значение столбца не будет меняться и так. Значит, знак абсолютной ссылки следует поставить перед значением строки (В$2). Окончательный вид формулы:
=$А3*В$2
6. Потянуть маркёром заполнения от ячейки В3 вправо до ячейки К3, затем, не отпуская левую клавишу мыши, потянуть вниз до ячейки К12. При этом заполнится сразу вся таблица.
Самостоятельная работа 4. «Таблица квадратов».
Задание. Составить таблицу квадратов целых чисел от 10 до 109, используя абсолютные ссылки (Рис. 24).
Рис. 24
Работа с матрицами.
Сочетание клавиш CTRL+SHIFT+ENTER – признак матричной операции умножения. В этом случае формула в строке формул будет записана в фигурных скобках.
Порядок выполнения.
1. Создать таблицу в соответствии с рис. 25.
2. Выделить ячейку О7.
3. Щёлкнуть по пиктограмме 
.
4. Выделить область D4:F6.
5. Щёлкнуть по кнопке со звёздочкой (*-символ умножения).
6. Выделить область G4:I6.
7. Набрать сочетание клавиш Ctrl+Shift+Enter – признак того, что операция умножения будет производиться над выделенными ячейками как над матрицами. В строке формул появится выражение : {=СУММ(D4:F6*G4:I6)}, а в ячейке D7 число 1395000. Если число будет представлено в экспоненциальной форме как 1,Е+06, можно отформатировать его в обычный вид через пункт меню ФОРМАТ – ЯЧЕЙКИ – закладка ЧИСЛО, выбрать числовой формат.
Самостоятельная работа 5.
Задание. Построить таблицу значений квадратной функции у = 1,5х2+3 в пределах -5 £ Х £ 7 шагом 1. Построить график (рис. 26).
Рис.26
Самостоятельная работа 6.
Задание. Создать таблицу продаж фирмы (табл. 3 ). Подсчитать объём прибыли.
Таблица 3
| №пп | Наименование изделия | Кол- во | Цена единицы продукции (руб.) |
| 1. | Компьютер | 10 | 25 000 |
| 2. | Принтер | 40 | 12 000 |
| 3. | Компьютерный стол | 8 | 10 000 |
| 4. | Флэш -карта | 30 | 2 000 |
| 5. | Клавиатура | 50 | 4 000 |
| 6. | Монитор | 15 | 15 000 |
| Итого |
Самостоятельная работа 7.
Задание. Используя диалог «Прогрессия», составить табель работы сотрудников на ближайшие три недели (пятидневная рабочая неделя). Пример в табл.4 дан по расчёту от 18.09.2006г.
Табл. 4.
| №пп |
ФИО
18.9
19.9
20.9
21.9
22.9
25.9
26.9
27.9
28.9
29.9
2.10
3.10
4.10
5.10
6.10
1
2
3
4
5
6
7
8
9
10
Вопросы для самоконтроля.
1. Как сделать перенос по словам внутри ячейки?
2. Как можно быстро пронумеровать строки по порядку?
3. Как повернуть текст в ячейке на 90 градусов?
4. Как меняются адреса при использовании относительных ссылок? Как при использовании абсолютных?
5. Каким символом обозначаются абсолютные ссылки?
6. Какое сочетание клавиш позволяет выполнять действия над матрицами?
Лабораторная работа 2.
Темы работы.
1. Мастер функций.
2. Фильтры
Мастер функций.
На строке формул есть небольшая кнопка fx , с помощью которой можно запустить мастер функций – список всевозможных функций, разделённых по категориям, которые можно использовать для сложных вычислений.
Порядок выполнения.
1. Первичными данными являются оценки по предметам. Вторичными являются данные в столбцах «Средний балл», «Принят, не принят».
2. Значения в столбце «Средний балл» и в строке «Средний балл по предмету» можно подсчитывать с помощью мастера функций , используя функцию СРЗНАЧ категории «статистические», или выбрать из списка итоговых функций на панели «Стандартная» (рис. 28).
Рис.28
3. Анализ «Принят, не принят» сначала выполнить без учёта двоек, только по среднему баллу. Для этого нужно вызвать мастер функций (fx), выбрать категорию «логические», функцию ЕСЛИ, и заполнить бланк функции выбора (Visual Basic). Условие выбора - средний балл больше или равен 3. Кавычки не вносить – они добавляются автоматически (рис. 29).
Рис.29
4. Создать анализ «Принят ,не принят с учётом двоек», чтобы двоечники не были приняты. Для этого нужно усложнить конструкцию выражения, добавив ещё одно условие – минимум оценок должен быть больше 2.
Последовательность заполнения бланка следующая:
1) внести слова «Принят» и «Не принят». Кавычки не писать – они добавляются в бланк автоматически.
2) установить курсор в строке «Лог_выражение». В этой строке нужно сформировать условие выбора. В эту строку нужно вложить функцию «И». Для этого нужно развернуть панель дополнительных вложений, появляющуюся в левой области строки формул (на месте поля имени), найти в списке функцию «И», если её нет, то выбрать «другие функции», и найти её в категории «логические». В первой строке сформировать первое условие (Средний балл>3) (рис. 30):
Рис.30
3) во вторую строку нужно вложить функцию «МИН» области оценок. Для этого установить курсор во второй строке и, с помощью панели дополнительных вложений на слева в строке формул найти функцию МИН категории СТАТИСТИЧЕСКИЕ. Установить курсор в строке «Число1» и маркёром выделения описать область С4-:F4 (Рис.31).
Рис.31
4) при необходимости скорректировать расстановку скобок в строке формул, чтобы выражение приобрело вид как на рис. 32.
Рис.32.
5. Для выявления наиболее частой оценки нужно воспользоваться функцией «МОДА» из категории «статистические».
6. Для подсчёта количества принятых подойдёт функция «СЧЁТЕСЛИ» категории «статистические». Эта функция подсчитывает количество определённых значений в указанной области. Бланк функции изображен на рис. 33. Кавычки в строке «Критерий» следует вносить с клавиатуры вручную, в отличие о заполнения бланка функции «ЕСЛИ».
Рис.33
Самостоятельная работа 8.
Задание. Составить таблицу корней квадратного уравнения ( Табл. 5).
Таблица 5.
| A | B | С | D | E | F | |||
| 1 | Корни уравнения вида aX2+bx+c=0 | |||||||
| 2 | a | b | c | |||||
Дискриминант
x1
x2
1
3
0
9
0
-3
2
6
3
12
-0,63
-2,37
3
9
6
9
-1
-2
4
12
9
0
-1,5
-1,5
5
15
12
-15
решения нет
решения нет
6
18
15
-36
решения нет
решения нет
7
21
18
-63
решения нет
решения нет
8
24
21
-96
решения нет
решения нет
9
27
24
-135
решения нет
решения нет
Пояснения к выполнению.
Образец для вычисления дискриминанта = B3^2-4*A3*C3.
Образец для формулы корня:
=ЕСЛИ(D3<0;"решения нет";(КОРЕНЬ(D3)-B3)/(2*A3))
Функция КОРЕНЬ находится в категории «Математические».
Упражнение 7. «Возраст».
Задание. Создать электронную таблицу, которая автоматически высчитывает возраст по дате рождения и определяет социальную группу по возрасту ( рис. 34).
Примечание. Так как электронная таблица связана с системной датой, в таблице приведены сведения по состоянию на 15 апреля 2006. Поэтому в выполняемой таблице значения должны соответствовать состоянию на конкретную дату.
Рис.34
Порядок выполнения.
1. Заполнить первичными данными столбцы «№», «Фамилия», Дата рождения», «Пол». Следует помнить, что даты нужно вносить числами, разделёнными точкой, например 12.12.1950. Чтобы месяц выводился словом и к году приписывалась буква «г.», нужно отформатировать ячейку через пункт меню ФОРМАТ- ЯЧЕЙКИ, выбрать закладку ЧИСЛО, далее ДАТА и в списке найти нужный вариант представления даты.
2. В столбце «Возраст без учёта даты» возраст подсчитывается первым способом: как разницу между годом рождения и текущим годом – столбец Е. Выражение, сформированное мастером функций, в этом случае будет выглядеть так-
= ГОД(СЕГОДНЯ())- ГОД(С3)
Вычисленное таким образом значение является числом. Но программа может ошибочно отнести его к типу «Дата-время». В этом случае нужно вручную задать тип данных «Числовой». Для этого следует выделить область Е3:Е7, затем пойти в пункт меню ФОРМАТ-ЯЧЕЙКИ, выбрать закладку ЧИСЛО и указать на строку ЧИСЛОВОЙ.
Недостаток такого вычисления очевиден, так как не в этом случае возраст вычисляется без учёта месяца и дня рождения. Но полученное значение является числом и его можно использовать в дальнейшем в математических вычислениях.
3. В столбце «Возраст с учётом Даты» (столбец F) значение возраста подсчитывается другим способом : как разница между сегодняшней датой и датой рождения. В этом случае выражение будет выглядеть так:
= СЕГОДНЯ()-С3
Вычисленное таким образом значение будет являться датой. Чтобы дата выводилась в полных годах, нужно ввести дополнительный формат. Для этого следует выделить область F3:F7, выбрать пункт меню ФОРМАТ – ЯЧЕЙКИ, затем закладку ЧИСЛО, указать в списке форматов на строку (все форматы), а затем, в окно «Тип» ввести «ГГ» (рис. 35).
Рис. 35
Возраст при таком подсчёте вычисляется точно, с учётом месяца и дня рождения, но, так как значение является датой, то его сложно использовать в дальнейших расчётах, например, для определения социальной группы. Кроме того, такой приём годится только для возраста меньше 100 лет. При разности дат больше 100 перевод в года получается ошибочным.
4. Месяц рождения подсчитывается с помощью функции МЕСЯЦ мастера функций как МЕСЯЦ (С3).
5. День рождения подсчитывается с помощью мастера функций ДЕНЬ как ДЕНЬ(С3).
6. Чтобы подсчитать возраст с точностью до дня как число, нужно сначала создать промежуточную дату, у которой год совпадает с текущим годом, а месяц и день недели совпадает с днём и месяцем дня рождения. Промежуточную дату можно создать с помощью функции ДАТА, аналога функции Visual Basic DATESERIAL, которая создаёт дату из чисел, поставленных в неё в качестве аргументов. Бланк функции для первой строки списка представлен на рис.36.
Рис.36
7. Чтобы определить возраст точно как число, нужно сравнить текущую дату с датой, полученной в п.6. Если сегодняшняя дата больше, т.е. дня рождения ещё не было, то от возраста по годам нужно отнять единицу (рис. 37).
Рис. 37
8. Анализ «Пенсионер- работающий- иждивенец» выполнить с помощью тройного вложения функции «ЕСЛИ», используя подсказки на рис. 38. Логика отбора следующая:
для мужчин - если пол = «м»(рис. 38,бланк 1), то – если возраст больше 60 (рис. 38,бланк 2), то отнести к категории пенсионеров, а иначе- если возраст меньше 18 лет, то иждивенец, а иначе работающий (рис. 38, бланк 3).
Бланк 1.
Бланк 2.
Бланк 3.
Рис. 38
Чтобы вернуться к бланку 1 и продолжать формировать выражение для женщин, нужно в строке формул установить курсор на первом «ЕСЛИ» в выражении и щёлкнуть по fx.(рис. 39).
Рис.39
для женщин - если пол не равен «м» (установить курсор в ветви «…ложь» бланка 1 ), то (вызвать ещё одно «ЕСЛИ») - если возраст больше 55, то отнести к категории пенсионеров (рис. 40,бланк 4), а если возраст меньше 18 лет, то к иждивенкам (рис. 40,бланк 5). В результате бланк 1 примет вид как на рис. 41.
Бланк 4.
Бланк 5
Рис. 40
Итог заполнения бланка 1.
Рис. 41
Обратите внимание, что если для построения выражения социальной группы использовать возраст с учётом даты (ДАТА), т.е. столбец F, то ничего не получится, так как тип данных в этом столбце не является числом.
Самостоятельная работа 9.
Задание. В таблице есть три отдельных столбца с фамилиями, именами и отчествами сотрудников. Создать ещё один столбец, в котором средствами мастера функций автоматически будут формироваться фамилии с инициалами (табл. 6).
Таблица 6
| №пп |
Фамилия
Имя
Отчество
ФИО
1
Иванов
Иван
Иванович
Иванов И.И.
2
Петров
Пётр
Петрович
Петров П.П.
3
Никитин
Николай
Иванович
Никитин Н.И.
4
Николаева
Людмила
Петровна
Николаева Л.П.
5
Федосова
Нина
Петровна
Федосова Н.П.
Пояснения к выполнению.
В этом упражнении следует использовать функцию обработки строковых величин ЛЕВСИМВ, выделяющую из записи заданное количество символов слева.
Логика формирования записи в столбце ФИО следующая:
=Фамилия &” “&ЛЕВСИМВ(Имя, 1)&”.“&ЛЕВСИМВ(Отчество, 1)&”.”
Знак & означает сложение строк. В кавычках записаны пробел и точки соответственно.
Фильтры.
Фильтрация относится к процедурам анализа баз данных, т.е. позволяет из основной таблицы (базы данных) создавать вторичные таблицы, содержащие записи, удовлетворяющие заданным условиям. Чтобы войти в режим фильтрации в EXCEL, нужно выделить область заголовков, затем выбрать пункт меню ДАННЫЕ - ФИЛЬТР – АВТОФИЛЬТР. Следует иметь в виду, что если таблица имеет сложную многоуровневую шапку, то нужно выделять область нижних заголовков.
Инструмент АВТОФИЛЬТР позволяет фильтровать только по одному столбцу (полю). При необходимости фильтровать по нескольким столбцам, следует использовать расширенный фильтр. Но всё-таки для сложного анализа баз данных лучше использовать специальную программу Access.
Упражнение 8. «Аукцион».
Задание. Создать таблицу по образцу рис. 42.
Выполнить следующие фильтрации :
1. Создать фильтр, в котором отображался список только покупателей из Киева скопировать фильтр под основной таблицей. Дать заголовок новой таблице «Фильтр по городу Киев».
2. Создать фильтр, в котором отображался список всех покупателей, принявших участие в торгах до 01.07, скопировать этот фильтр под предыдущим. Дать имя таблице «Фильтр по дате».
3. Сделать сводку по продаже книг №3. Дать имя «Фильтр по книге №3».
4. Создать сводку клиентов, сделавших самые большие закупки (Сумма>15 000) .Скопировать и дать имя «Фильтр по сумме».
5. Оформить таблицу с помощью команды автоформат (стиль Объёмный 2).
6. Отсортировать таблицу по алфавиту городов, а, в свою очередь, каждый город по дате.
7. Создать условное форматирование столбца "Сумма" по принципу: если сумма>10000,то цвет шрифта красный, а цвет фона жёлтый.
Рис. 42
1. Чтобы задать режим фильтрации, нужно выделить заголовки таблицы (область А2:Н2) и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. В результате в заголовках появятся стрелочки с разворачивающимися списками (рис. 43). Следует иметь в виду, что если таблица имеет сложную шапку, то нужно выделять нижние заголовки.
Рис. 43
2. Чтобы выполнить фильтр по городу Киев, нужно развернуть список критериев в столбце «Город» и выбрать Киев. Скопировать новую таблицу ниже на листе под основной и дать имя «Фильтр по городу Киев».
3. Для выполнения фильтра по дате следует развернуть список критериев в столбце «Дата» и выбрать строку УСЛОВИЕ. Заполнить бланк условия в соответствии с рис. 44.
Рис. 44
4. Скопировать фильтр ниже по листу. Назвать таблицу «Фильтр по дате».
5. Фильтр по книге №3 выполнить аналогично фильтру по городу.
6. Фильтр по сумме выполнить с помощью бланка условия :
БОЛЬШЕ 15000
7. Скопировать фильтр ниже по листу. В результате получится список фильтров как на рис. 45.
8. Закончить фильтрацию. Выделить область А2:Н2 и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. При этом исчезнут галочки в шапке основной таблицы.
9. Выделить область А2:Н15 и пойти в пункт меню ФОРМАТ – АВТОФОРМАТ. Выбрать стиль «Объёмный 2».
10. Для выполнения сортировки выделить область А2:Н15 и выбрать пункт меню ДАННЫЕ – СОРТИРОВКА. Убедиться, что сортировка будет производиться по городу, а затем по дате. Так как выделена была вся таблица, перестроится не только столбец «Город» и «Дата», но и другие данные (рис. 46).
11. Для выполнения условного форматирования нужно выделить область Н2:Н15 и выбрать пункт меню ФОРМАТ – УСЛОВНОЕ ФОРМАТИРОВАНИЕ. Заполнить бланк условного форматирования, щёлкнув по кнопке Формат… . В разделе «Шрифт» задать красный цвет шрифта, а в разделе «Вид» заказать желтую заливку (рис. 47):
Рис. 45
Рис. 46
Рис. 47
Самостоятельная работа 10.
Задание. Составить таблицу в соответствии с табл. 7.
Создать фильтры (табл.8):
1. Всех товаров, поступивших после 01.04.
2. Всех товаров, имеющих цену прихода менее 20 р.
3. Товаров полностью реализованных, т.е. имеющих количество остатка 0.
4. Прибыльных товаров, имеющих прибыль более 200 р.
Отсортировать базу данных по алфавиту наименований товаров.
Создать условное форматирование столбца «Прибыль» таким образом, чтобы отрицательные значения выводились красным цветом, а товары , имеющие прибыль более 100 р., имели желтый фон.
Пояснения к выполнению.
1. Сумма расхода подсчитывается следующим образом:
(Цена расхода)*(Количество расхода)
2. Количество остатка подсчитывается как:
(Количество прихода)-(Количество расхода)
3. Формула для суммы остатка:
(Количество остатка)*(Цена расхода)
4. Прибыль считается как :
(Сумма расхода )-(Сумма прихода)
5. При выполнении условного форматирования применить два условия.
|
№ |
|
НАИМЕНОВАНИЕ ТОВАРА
ЕДИНИЦА ИЗМЕРЕНИЯ
Дата поступления товара
ПРИХОД
РАСХОД
ОСТАТОК
ПРИБЫЛЬ
1
упак.
10.янв
30 р.
15
450
34
15
510 р.
0
0
60
2
упак.
01.апр
15 р.
50
750
21
43
903р.
7
147
153
3
кг
10.апр
140 р.
10
1 400
164
9
1 476 р.
1
164
76
4
кг
12.апр
80 р.
12
960
97
10
970 р.
2
194
10
5
бут.
01.фев
27 р.
32
864
34
25
850 р.
7
238
- 14
6
упак.
12.апр
12,00р.
20
240
17,50
20
350 р.
0
0
110
7
кг
15.мар
15,45р.
50
773
22,00
45
990 р.
5
110
218
8
упак.
25.мар
5,00р.
100
500
8,00р
90
720 р.
10
80
220
Итого
832,5
Таблица 7
Таблица 8
|
Фильтр по дате |
|
|
|
|
|
|
|
|
|
| ||
| № | НАИМЕНОВАНИЕ ТОВАРА | ЕДИНИЦА ИЗМЕРЕНИЯ | Дата поступ-ления товара | ЦЕНА ПРИХОДА | КОЛ-ВО ПРИХОДА | Сумма прихода | ЦЕНА РАСХОДА | КОЛ-ВО РАСХОДА | Сумма расхода | КОЛ-ВО ОСТАТКА | СУММА ОСТАТКА | ПРИБЫЛЬ |
| 3 | Колбаса | кг | 10.апр | 140р. | 10 | 1 400 | 164 | 9 | 1 476р. | 1 | 164 | 76,0 |
| 4 | Сосиски | кг | 12.апр | 80р. | 12 | 960 | 97 | 10 | 970р. | 2 | 194 | 10,0 |
| 6 | Сметана | упак. | 12.апр | 12,00р. | 20 | 240 | 17,5 | 20 | 350р. | 0 | 0 | 110,0 |
|
Дешёвые товары |
|
|
|
|
|
|
|
|
|
| ||
| 2 | Молоко | упак. | 01.апр | 15р. | 50 | 750 | 21 | 43 | 903р. | 7 | 147 | 153,0 |
| 6 | Сметана | упак. | 12.апр | 12,00р. | 20 | 240 | 17,5 | 20 | 350р. | 0 | 0 | 110,0 |
| 7 | Песок сахарный | кг | 15.мар | 15,45р. | 50 | 773 | 22 | 45 | 990р. | 5 | 110 | 218,0 |
| 8 | Сигареты "Прима" | упак. | 25.мар | 5,00р. | 100 | 500 | 8,00р | 90 | 720р. | 10 | 80 | 220 |
|
Проданные товары |
|
|
|
|
|
|
|
|
|
| ||
| 1 | Зефир | упак. | 10.янв | 30р. | 15 | 450 | 34 | 15 | 510р. | 0 | 0 | 60,0 |
| 6 | Сметана | упак. | 12.апр | 12,00р. | 20 | 240 | 17,5 | 20 | 350р. | 0 | 0 | 110,0 |
|
Прибыльные товары |
|
|
|
|
|
|
|
|
|
| ||
| 7 | Песок сахарный | кг | 15.мар | 15,45р. | 50 | 773 | 22 | 45 | 990р. | 5 | 110 | 218,0 |
| 8 | Сигареты "Прима" | упак. | 25.мар | 5,00р. | 100 | 500 | 8,00р | 90 | 720р. | 10 | 80 | 220 |
Дополнительные задания к лабораторной работе 2.
Самостоятельная работа 11.
Задание. Составить таблицу успеваемости учеников (табл. 9).
Создать подсчёт:
o успевающих на 4 и 5.
o количество отличников.
o хорошистов с одной 4 .
o успевающих без двоек, с одной 3.
o количество двоечников.
Таблица 9
| A | B | С | D | E | F | G | H | I | К | L | М | |
| 1 | №пп |
Фамилия
Математика
Физика
Русский
Химия
Физ-ра
Отличники
Хорошисты
Хорошисты с 1 четвёркой
Троечники с одной тройкой
Двоечники
1
Иванов
5
5
5
5
Осв.
да
2
Петров
5
4
5
5
5
да
да
3
Никитин
5
2
5
5
5
да
4
Николаева
4
4
3
4
4
да
5
Федосова
3
3
4
2
Осв.
да
Итого
1
1
1
1
2
Самостоятельная работа 12.
Задание. Составить таблицу успеваемости из табл. 9 (столбцы А- G ). Создать фильтры:
o Всех успевающих на 5 по математике.
o Успевающих на 5 или 4 по физике.
o Не имеющих 2 по русскому языку.
o Освобожденных по физкультуре.
Самостоятельная работа 13.
Задание. Создать электронную таблицу, в которой при внесении в столбец ФИО (фамилии, имени и отчества через пробел), в столбец Фамилия автоматически вносилась фамилия (табл. 10 ).
Пояснения к выполнению.
Выделить фамилию следует в два этапа:
1. Найти позицию первого пробела с помощью функции НАЙТИ категории ТЕКСТОВЫЕ.
2. Выделить соответствующий фрагмент слева с помощью функции ЛЕВСИМВ.
Таблица 10
| №пп |
ФИО
Фамилия
1
Иванов Иван Иванович
7
Иванов
2
Петов Пётр Петрович
6
Петов
3
Скворцова Ольга Львовна
10
Скворцова
Самостоятельная работа 14.
Задание. Найти корни системы линейных уравнений с помощью функции МОПРЕД мастера функций, вычисляющую определитель матрицы.
x1 + 2x2 + 3x3 + 4x4 =30
-x1 + 2x2 - 3x3 + 4x4 =10
x2 - x3 + x4 =3
x1 + x2 + x3 + x4 =10
Пояснения к выполнению.
o Схема решения представлена на рис. 48.
o Для простоты набора определители обозначаются буквой D.
o Напоминаем формулу Крамера для решения корней системы линейных уравнений:
Xi=Di/D
Рис. 48
Упражнение 9. «Табель».
Задание. Составить табель работы сотрудников по образцу рис. 49.
Рис. 49
Порядок выполнения.
1. Создать заголовок и шапку таблицы. Заполнить табель.
При заполнении табеля использовать следующие обозначения:
· п - прогул, отпуск без оплаты.
· б- больничный.
· о- отпуск, отгул.
2. Дни явок подсчитываются с помощью мастера функций, категория «статистические», функция СЧЁТ. Эта функция подсчитывает количество чисел в выбранном диапазоне.
Отпуск, больничный и прогул подсчитываются функцией СЧЁТЕСЛИ, которая считает количество заданного символа в выбранном диапазоне. Вид бланка для столбца «Болезнь» представлен на рис. 50. В окне «Критерий» символ обязательно ставить в кавычках.
3. Отработано часов считается с помощью АВТОСУММИРОВАНИЯ по области D3:j3.
4. Заработано вычисляется как произведение:
=Почасовая ставка*отработано часов.
Рис.50
5. Средняя з/п в день вычисляется как частное :
Заработано/7(количество дней в неделе).
6. Оплата по средней вычисляется как произведение :
Средняя з/п в день*(отпуск+больничный).
7. Начислено вычисляется как сумма
Заработано+Оплата по средней.
8. В столбце «Проверка» организовать проверку табеля на правильность заполнения на случай, если оператор внесёт в ячейки значения, отличные от «о», «п» и «б». Бланк логического выражения изображен на рис. 51.
Рис.51
9. В ячейке D9 организовать проверку незаполненных ячеек на случай, если оператор забудет заполнить какие- то ячейки. Для этого можно воспользоваться функцией СЧИТАТЬПУСТОТЫ категории СТАТИСТИЧЕСКИЕ (рис. 52).
Рис. 52.
Вопросы для самоконтроля.
1. Месяц дня рождения можно подсчитывать двумя способами:
a. С помощью функции Месяц(), извлекающую из даты число, равное месяцу даты.
b. Форматированием ячейки, хранящей дату через пункт меню ФОРМАТ – ЯЧЕЙКИ , закладку ЧИСЛО, выбрать ВСЕ ФОРМАТЫ и введением нового формата - ММ.
Какой тип данных будет иметь месяц, созданный тем или другим способом? В каком из вариантов полученное значение можно использовать в выражениях, в каком нельзя?
2. Перечислить функции выделения фрагментов из строк в Excel и аналоги их в Visual Basic . Как записывается в Visual Basic аналог функции мастера функций НАЙТИ?
Лабораторная работа 3.
Темы работы.
1. Подбор параметра.
2. Поиск решения.
Подбор параметра.
В Excel есть возможность выполнять подбор значений в одной ячейке , являющейся параметром , так чтобы в целевой ячейке установилось значение, равное требуемому (рис. 53 ). Эту задачу можно выполнить через пункт меню СЕРВИС- ПОДБОР ПАРАМЕТРА… Подбор параметра осуществляется методом последовательных приближений.
Рис. 53
При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением, т. е. эти ячейки должны быть связаны формулами.
Решение может быть и не найдено, например, уравнение не имеет решений. В силу используемого метода решение может быть найдено приблизительно, т.е. значение в целевой ячейке после выполнения операции не совсем совпадает с требуемым. В этом случае можно поступить следующим образом:
o Увеличить количество итераций поиска (по умолчанию 100)-
СЕРВИС – ПАРАМЕТРЫ- ВЫЧИСЛЕНИЯ – ПРЕДЕЛЬНОЕ ЧИСЛО ИТЕРАЦИЙ.
o Увеличить относительную погрешность (по умолчанию 0,001) –
СЕРВИС – ПАРАМЕТРЫ – ВЫЧИСЛЕНИЯ – ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТЬ.
o Изменить начальное значение параметра.
Если на параметр или на значение в целевой ячейке требуется наложить ограничения, или в задаче нужно использовать несколько параметров, то такая задача может быть решена средством «Поиск решения», приведенным в следующем разделе.
Фамилия
1
Арбузова
50
2
Богогмолов
49
3
Высотчина
50
4
Ганохина
30
5
Гиясова
97
6
Гончаренко
40
7
Грицай
35
8
Дудина
20
9
Зеров
45
10
Иванова
10
Принято
0
Проходной балл
0
План приёма
6
Порядок выполнения.
1. Вывести выражение для вычисления значений в столбце «Принят, не принят». Для этого нужно составить Логическую конструкцию как в подобном упражнении в лабораторной работе 2 («Результаты вступительных экзаменов»), только в условии поставить адрес проходного балла. (рис. 54).
Рис. 54
При проходном балле 0 , естественно, все будут приняты.
2. Организовать подсчёт количества принятых с помощью функции СЧЁТЕСЛИ – так же как в лабораторной работе 2. Раз все приняты, то в ячейке D12 появится число10.
3. Выделить ячейку D13. Вызвать диалог «Подбор параметра». Заполнить его как на рис. 55 , щёлкнуть ОК.
Рис. 55
В результате подсчета параметра значение проходного бала станет 37 и количество принятых станет равно плану приёма, (табл. 12 )
Таблица 12
| №пп |
Фамилия
1
Арбузова
50
Принят
2
Богогмолов
49
Принят
3
Высотчина
50
Принят
4
Ганохина
30
Не принят
5
Гиясова
97
Принят
6
Гончаренко
40
Принят
7
Грицай
35
Не принят
8
Дудина
20
Не принят
9
Зеров
45
Принят
10
Иванова
10
Не принят
Принято
6
Проходной балл
37
План приёма
6
Порядок выполнения.
1. Подготовить таблицу (табл.13) .
Таблица 13
| А | В | С | |
| 1. | Корни уравнения У=0,5Х3+2Х2-Х-3 | ||
| 2. | |||
Левая часть
Корни
2. В ячейке В3 вывести формулу: =0,5*C3^3+2*C3^2-C3-3
3. С помощью маркёра заполнения продолжить эту формулу на ячейки В5, В5.
4. Так как в задании указана область поиска от -5 до 2, то расставим начальные значения корней -5,0 и 2 соответственно. Тогда исходное состояние таблицы примет вид табл. 14.
Таблица 14
| А | В | С | |
| 1. | Корни уравнения У=0,5Х3+2Х2-Х-3 | ||
| 2. |
Левая часть |
Корни | |
| | 1 | -10,5 | -5 |
| | 2 | -3 | 0 |
| | 3 | 7 | 2 |
5. Установить курсор в ячейке В3, вызвать диалог «Подбор параметра», заполнить бланк как на рис. 56.
Рис. 56.
В результате выполнения этой операции в ячейке В3 появится число, записанное в экспоненциальной форме, близкое к нулю, а в ячейке значение первого корня -4,13264.
6. Повторить подбор параметра для ячеек В4 и В5 . В результате должна получиться табл. 14.
Таблица 14
|
|
Левая часть |
Корни |
| 1 | -3,29599E-05 | -4,1326416 |
| 2 | -0,000197104 | -1,1403808 |
| 3 | 0,000296781 | 1,2731184 |
Самостоятельная работа 15.
Задание. Отделу выделена премия(варианты в табл. 15 ) . Распределить премию сотрудникам отдела в зависимости от должностного оклада (табл. 16 ).
Пояснения к выполнению.
Предполагается, что премия будет начисляться в процентах от оклада, т.е.
премия =оклад*процент.
Сумма же всех премий должна равняться общей выданной на отдел премии.
Таблица 15
| 1 вариант | 2 вариант | 3 вариант | 4 вариант | 5 вариант | |
| премия | 10 000 | 15 000 | 20 000 | 25 000 | 30 000 |
Таблица 16
| №пп |
Фамилия
1
Арбузова
10 000
2
Богогмолов
7 000
3
Высотчина
5 000
4
Ганохина
5 000
5
Гиясова
2 000
Сумма премий
Общая премия
Из табл.
Процент
Поиск решения.
Большинство задач, решаемых методом подбора параметра, можно решить аналитически, причём точно, а не приближенно. Более интересен другой инструмент, имеющийся в Excel, позволяющий накладывать ограничения на параметры, применять в качестве параметров области, решать задачи со многими неизвестными - поиск решения (СЕРВИС – ПОИСК РЕШЕНИЯ). При этом используется метод линейной оптимизации.
Если пункт меню ПОИСК РЕШЕНИЯ отсутствует в списке СЕРВИС, то его можно установить через СЕРВИС – НАСТРОЙКИ. В открывшемся диалоге следует поставить галочку против ПОИСК РЕШЕНИЯ.
С помощью этого инструмента можно решать сложные экономические задачи. В комплекте Excel поставляется файл Solvsamp.xls, демонстрирующий возможности метода. Мы же разберём простейшие задачи оптимизации.
Так же как и в методе «Подбор параметра», результат зависит от начальных условий, количества итераций и относительной погрешности. Но «Поиск решения» позволяет более детально настраивать процесс оптимизации с помощью бланка ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ (рис. 57), который можно вызвать из бланка ПОИСК РЕШЕНИЯ через кнопку ПАРАМЕТРЫ.
Так же, как и при подборе параметра данные в целевой ячейке должны быть связаны с изменяемыми данными формулами.
Рис. 57
Упражнение 12. «Закупки».
Задание. Необходимо произвести закупки товаров со склада для магазина на определённую сумму. Известна цена каждого товара. Даны ограничения на максимальное и минимальное количество закупаемого товара (табл. 17). Определить оптимальное количество закупаемых товаров.
Порядок выполнения.
1. Выполнить табл. 17 . В области F2:F6 (столбец сумма) сформировать выражение по образцу: =E2*B2 (=цена*количество).
2. В ячейке F7 составить формулу для суммы: =СУММ(F2:F6).
3. Установить курсор в ячейке F7. Вызвать бланк поиска решения (СЕРВИС – ПОИСК РЕШЕНИЯ) . Заполнить бланк как на рис. 58.
Условия в области «Ограничения» создать с помощью кнопки «Добавить», последовательно формируя условия и щёлкая по кнопке «Добавить», затем ОК (рис. 59 ).
Таблица 17
| A | B | C | D | Е | F | |
| 1 |
Товар
Цена
Мин
Макс
Количество
Сумма
Тетрадь
10
10
70
Ручка
20
20
70
Карандаш
5
30
100
Пенал
50
3
10
Учебник
70
5
30
Всего в итоге
Всего задано
5000
Рис. 58.
Рис. 59.
4. Нажать на кнопку «Выполнить». В результате должна получиться табл. 18.
Таблица 18
|
Товар |
Цена |
Мин |
Макс |
Кол |
Сумма |
| Тетрадь | 10 | 10 | 70 | 70 | 700 |
| Ручка | 20 | 20 | 70 | 70 | 1400 |
| Карандаш | 5 | 30 | 100 | 60 | 300 |
| Стиплер | 50 | 3 | 10 | 10 | 500 |
| Учебник | 70 | 5 | 30 | 30 | 2100 |
|
|
|
|
| Итого | 5000 |
|
|
|
|
| Всего | 5000 |
Самостоятельная работа 16.
Задание. Требуется расфасовать D кг сыпучего материала по контейнерам, бочкам и канистрам. Дана вместимость каждого из видов хранения. (табл. 19). Определить, сколько контейнеров, бочек и канистр потребуется для расфасовки всего сыпучего материала. Исходные значения даны по вариантам в табл. 20.
Таблица 19
Наименование
Вместимость
Количество
Объём
Контейнер
А
0
0
Бочка
В
0
0
Канистра
С
0
0
Всего
0
Исходное количество
N
Таблица 20
| Вариант | А | В | С | D |
| 1. | 270 | 130 | 90 | 1400 |
| 2. | 200 | 100 | 50 | 1600 |
| 3. | 100 | 120 | 20 | 1000 |
| 4. | 60 | 40 | 5 | 500 |
| 5. | 160 | 150 | 50 | 2000 |
Порядок выполнения.
1. Составить таблицу и связать данные как на рис. 60.
2. Вызвать бланк «Поиск решения» и заполнить бланк в соответствии с рис. 61.
Рис. 60
Рис. 61
3. Результат выполнения поиска решения должен быть как в табл. 21.
Таблица 21
Изделие А
Изделие В
Сырьё
4
7
19
22
Люди
8
5
29
30
Транспорт
3
4
13
20
3
1
Цена продажи
7
6
Всего затрат
21
6
Итог
27
Самостоятельная работа 17.
Задание. Результат поиска решения в упражнении «Предприятие» оказался не совсем удачным, так как запланированные средства не полностью реализованы. Сырьё берётся со склада, поэтому можно снять ограничение на него. Увеличим количество людей до 34. Изменить условие поиска максимальной прибыли производства таким образом, чтобы были заняты все рабочие, и в смену производилось не менее одного изделия каждого вида.
Дополнительные задания к лабораторной работе 3.
Самостоятельная работа 18.
Задание. Вкладчик желает положить сумму в банк под 10% годовых сроком на 18 лет таким образом, чтобы по истечении указанного срока получить определённую сумму выплат (табл. 22). Определить сумму вклада (табл. 23).
Таблица 22
| 1 вариант | 2 вариант | 3 вариант | 4 вариант | 5 вариант | |
| Сумма выплат | 100 000 р. | 200 000 р. | 300 000 р. | 400 000 р. | 500 000 р. |
Таблица 23
| Размер вклада | - р. |
| Срок вклада | 18 |
| Процентная ставка | 10% |
| Коэффициент наращивания | 5,559917313 |
| Сумма выплат | - р. |
Пояснения к выполнению.
o Коэффициент наращивания вычисляется по формуле:
Коэф. Нар. = (1+ процентная ставка) Количество лет
o Сумма выплат вычисляется по формуле:
Сумма выплат = Размер вклада х Коэффициент наращивания.
Самостоятельная работа 19.
Задание. Изменим условия самостоятельной работы 18 . Вкладчик может положить на счёт не более 70 тыс. рублей. На какой срок ( в полных годах) и какую сумму ему нужно положить, чтобы по истечении срока вклада получить полмиллиона?
Вопросы для самоконтроля.
1. Чем отличаются методы «Подбор параметра» и «Поиск решения»? Когда какой следует использовать?
2. Какой алгоритмической конструкции в программировании соответствует процесс «Подбор параметра»?
Лабораторная работа 4.
Тема работы: Макросы.
1. Запись макросов с помощью макрорекордера.
2. Размещение элементов управления на листе EXCEL.
3. Макросы в диаграммах.
4. Выполнение макроса написанием кода в Visual Basic.
Макросы.
Во всех приложениях Office, так же , как и в Word, и в EXCEL можно программировать на языке Visual Basic for Applications (VBA). Программы, написанные таким образом, присоединяются к файлу книги и называются макросами. Макросы можно создавать, не зная языка программирования VBA с помощью макрорекордера (макрос «День рождения»)). Можно основные действия записать с помощью макрорекордера и затем дописать код в редакторе Visual Basic (макрос «Клиенты»). Можно размещать на листе элементы управления и описывать код этих элементов в редакторе Visual Basic (макросы «Бабочка», «Анализ функции»). Очень эффектно можно использовать макросы в диаграммах (макрос «Звёзды»). Макросы можно использовать в вычислениях значений в ячейках, когда недостаточно арсенала мастера функций( макросы «Таблица истинности», «Возраст»).
Недостатком макропрограммирования как в Word так и в EXCEL, является то, что макросы можно отключать через пункт меню СЕРВИС – МАКРОСЫ – БЕЗОПАСНОСТЬ. Это делается для защиты книги от макровирусов – нового класса вирусов, появившегося вместе с таким удобным инструментом Microsoft Office, как VBA. Поэтому при получении книги, содержащей макросы, из ненадёжных источников (например, по электронной почте), макросы следует отключать.
Использование VBA в EXCEL имеет свои особенности по сравнению с WORD:
o Объект Selection означает выделенную ячейку или область. Если ничего в момент обращения не выделено, то программа выдаёт ошибку.
o Объект Range(“ столбец-строка”) позволяет обратиться к ячейке или области, например Range(“A5”), – обращение к ячейке А5.
o Объект ActiveSheet.Cells(строка, столбец) также позволяет обратиться к ячейке по номеру строки и номеру столбца, например ActiveSheet.Cells(2, 5), – обращение к ячейке Е2.
o Объект ActiveCell позволяет обратиться к выделенной ячейке.
В упражнениях, предлагаемых для выполнения, требуется минимальное знание основ программирования, например, знание таких тем, как:
o Оператор ветвления If…THEN…ELSE.
o Операторы цикла FOR…NEXT, DO…LOOP.
o Диалоговые окна MSGBOX…
o Оператор объявления типов данных DIM…
o Функция нахождения остатка от деления a MOD b.
o Функция, возвращающая случайное число в заданных пределах RND.
Список
№пп
Фамилия
Дата рождения
Возраст
1
Абрамов
12 Декабрь, 1950
55
12,00
2
Иванов
20 Апрель, 1989
16
4,00
!!
3
Петров
11 Ноябрь, 1970
35
11,00
4
Николаева
12 Март, 1978
28
3,00
5
Гиясова
1 Апрель, 1910
96
1,00
!!
6
Гончаренко
12 Февраль, 1915
91
2,00
7
Грицай
12 Июнь, 1977
28
6,00
8
Дудина
3 Март, 1999
07
3,00
9
Зеров
9 Сентябрь, 1980
25
9,00
Порядок выполнения.
1. Создать шапку таблицы и внести первичные данные – столбцы «№пп», «Фамилия» и «Дата рождения». Дату нужно вносить числами в формате дд.мм.гггг, а затем форматировать через ФОРМАТ – ЯЧЕЙКИ... (см. лабораторную работу 2).
2. Возраст с учётом даты создаётся выражением СЕГОДНЯ()-С3 с последующим заданием дополнительного формата числа «ГГ» (см. лабораторную работу 2).
3. Месяц подсчитать выражением МЕСЯЦ(С3) с помощью мастера функций.
4. В столбце «День рождения в этом месяце» нужно применить оператор мастера функции категории ЛОГИЧЕСКИЕ «ЕСЛИ» (рис. 62).
Рис.62.
Запись “”(две кавычки подряд) в синтаксисе Visual Basic означает пустую строку.
5. Дать имя листу «Список». Создать новый лист. Назвать его «Дни рождения текущего месяца».
6. Создать фильтрацию вручную, без макроса. Для этого нужно выполнить следующие действия:
1) на листе «Список» выделить область заголовков А2:F2.
2) выбрать пункт меню ДАННЫЕ –ФИЛЬТР АВТОФИЛЬТР. В результате в каждом заголовке появится разворачивающийся список.
3) развернуть список критериев для столбца F и выбрать «!!». В результате создастся список из двух сотрудников –Иванова и Гиясовой.
4) выделить новую таблицу , скопировать её . Так как в общем случае неизвестно, сколько строк будет содержать таблица, выделять лучше столбцы А:F.
5) активизировать лист «Дни рождения текущего месяца». Установить курсор в ячейке А1 и дать команду ПРАВКА – ВСТАВИТЬ.
6) в ячейку А1 внести текст : Дни рождения в этом месяце.
7) активизировать лист «Список». Завершить режим фильтрации, дать команду ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР.
8) активизировать лист «Дни рождения текущего месяца». В ячейку G1 внести текст : «Чтобы обновить , наберите CTRL+d».
7. Чтобы не выполнять эту процедуру многократно вручную, можно записать все действия и в последующем вызывать их сочетанием клавиш, например, CTRL+d. Перед записью нужно построить четкий алгоритм действий, лучше записать его, чтобы не сбиваться во время действия макрорекордера. В данном упражнении нужно выполнить следующую последовательность действий.
1) сделать активным лист «Список».
2) вызвать макрорекордер через пункт меню Сервис –макрос- начать запись.
3) дать имя макросу «День_рождения». Задать сочетание клавиш CTRL+d.
4) с помощью макрорекордера записать следующие действия:
1. Активизировать лист «Дни рождения».
2. На листе «Дни рождения» выделить столбцы A:F и удалить их содержимое (нажать Delete).
3. Перейти на лист «Список», выделить область заголовков А2:F2.
4. Выбрать пункт меню Данные- фильтр- автофильтр.
5. Развернуть список критериев в столбце «Дни рождения в этом месяце» и выбрать критерий «!!».
6. На листе «Список» выделить столбцы А:F и дать команду «копировать».
7. Перейти на лист «Дни рождения», установить курсор в ячейку А1 и дать команду «Вставить»
8. В ячейку А1 внести текст «Дни рождения в этом месяце»
9. Перейти на лист «Список», восстановить таблицу- выбрать пункт меню ДАННЫЕ-ФИЛЬТР-АВТОФИЛЬТР.
10. Сделать активным лист «Дни рождения».
11. Остановить запись.
8. Можно убедиться, что создался текст макроса, если выбрать пункт меню СЕРВИС- МАКРОС – МАКРОСЫ, указать на макрос «День_рождения» и щёлкнуть по кнопке Изменить. Текст макроса будет следующим:
Sub День_рождения()
' Сочетание клавиш: Ctrl+d
Sheets("Дни рождения").Select
Columns("A:F").Select
Selection.ClearContents
Sheets("Список").Select
Range("A2:F2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="!!"
Columns("A:F").Select
Selection.Copy
Sheets("Дни рождения").Select
Range("A1").Select
ActiveSheet.Paste
Columns("D:D").ColumnWidth = 14.14
Columns("C:C").ColumnWidth = 14.71
Columns("C:C").ColumnWidth = 16.71
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Дни рождения в этом месяце"
Sheets("Список").Select
Selection.AutoFilter
Range("A2").Select
Sheets("Дни рождения").Select
End Sub
Закрыть Visual Basic .
9. Проверить работу макроса. Изменить базу данных –сделать день рождения Дудиной тоже в апреле. Запустить макрос и убедиться, что список на листе «Дни рождения текущего месяца» обновился.
Вопросы к практической работе.
1. Значения в столбце «Месяц» можно вычислять с помощью мастера функций выражением Месяц(«День рождения»),как в практической работе, а можно форматированием даты рождения, создав дополнительный формат «ММ». В чём разница данных, полученных этими способами?
Упражнение 15. «Бабочка».
Задание. Разместить на листе объект РИСУНОК, кнопку ПОЛЁТ и кнопку ДОМОЙ. Написать код программы, в результате которого при щелчке по кнопке ПОЛЁТ объект РИСУНОК должен двигаться к выделенной ячейке. При щелчке по кнопке ДОМОЙ объект РИСУНОК должен скачком уходить к ячейке А1.
Порядок выполнения.
1. Убедиться, что на диске компьютера есть файлы - картинки бабочки с открытыми крыльями и закрытыми крыльями BFLY1.bmp и BFLY2.bmp.
2. Приготовить чистый лист. Дать ему имя «Бабочка». Вызвать панель управления Visual Basic (Вид- ПАНЕЛИ ИНСТУМЕНТОВ –ЭЛЕМЕНТЫ УПРАВЛЕНИЯ). Войти в режим конструктора, щёлкнув по голубому треугольнику на панели ЭЛЕМЕНТЫ УПРАВЛЕНИЯ.
3. Выбрать на панели ЭЛЕМЕНТЫ УПРАВЛЕНИЯ инструмент «Рисунок» и разместить три объекта «Рисунок» в любой области таблицы. Настроить свойства объектов:
Первый объект «Рисунок»- Name- Img
Autosize – True
BackColor- белый
BorderColor- белый
Второй объект «Рисунок»– Name –Img1
Autosize – True
BackColor- белый
BorderColor- белый
Visible – Fale
Picture- щёлкнуть по трём точкам и по файловой системе найти файл BFLY1.
Третий объект «Рисунок»- Name –Img2
Autosize – True
BackColor- белый
BorderColor- белый
Visible – Fale
Picture- щёлкнуть по трём точкам и по файловой системе найти файл BFLY2.
4. Выбрать на панели управления инструмент «кнопка» и разместить его на листе. Настроить свойства кнопки:
Caption –Полёт
Name- CmdFly
5. Разместить на листе ещё одну кнопку и настроить её таким образом:
Caption- ДОМОЙ
Name- CmdHome.
В результате на листе в режиме конструктора будут видны следующие объекты (рис.63):
Рис. 63
6. Щёлкнуть два раза отрывисто по кнопке CmdFly (Полёт) и войти в процедуру обработки события CmdFly_Click, щелчка по кнопке ПОЛЁТ. Написать следующий код:
Private Sub cmdfly_Click()
Dim i As Integer Объявляем вспомогательную переменную i (будет управлять открытием –закрытием крыльев)
Dim t As Single Объявляем вспомогательную переменную t (будет управлять скоростью процессов)
Do Запускаем бесконечный цикл полёта.
If i Mod 2 = 0 Then Если i чётное , то
Img.Picture = Img1.Picture открытые крылья
Else Иначе
Img.Picture = Img2.Picture Закрытые крылья
End If
t = Timer фиксируем время
Do Запускаем пустой цикл задержки на 0,5 сек иначе всё будет происходить слишком быстро
DoEvents оператор прерывания процедуры «осмотрись»
Loop While Timer - t < 0.5 Закрываем цикл задержки 0,5сек.
Img.Left = Img.Left + (ActiveCell.Left - Img.Left) / 10 Задаём траекторию
Img.Top = Img.Top + (ActiveCell.Top - Img.Top) / 10 движения по направлению
к выделенной ячейке
i=i+1 изменяем значение параметра i
If i> 100 THEN i=0 делаем защиту от переполнения по i
DoEvents вставляем оператор прерывания
Loop Закрываем бесконечный цикл полёта
End Sub
7. Щёлкнуть два раза отрывисто по кнопке ДОМОЙ и внести следующий код:
Private Sub cmdhome_Click()
Img.Left = 0 Приводим бабочку в ячейку А1
Img.Top = 0
End Закрываем программу
End Sub
8. Закончить режим конструктора и убедиться, что при щелчке по кнопке «Полёт» бабочка летит к выделенной ячейке, а при щелчке по кнопке «ДОМОЙ» бабочка скачком возвращается в начало координат.
Самостоятельная работа 20.
Задание. Изменить код программы «Бабочка» таким образом, чтобы:
o бабочка махала крыльями чаще в 5 раз.
o Чтобы бабочка подлетала к выделенной ячейке в два раза быстрее.
Макросы в диаграммах.
Порядок выполнения.
1. В ячейку А1 внести заголовок: Таблица значений функции у=ах2+вх+с.
2. В ячейку А3 внести имя аргумента: х.
3. В А4 имя функции: у.
4. В В23 внести имя коэффициента : а.
5. В в О23 значение коэффициента а : -4.
6. В В25 имя коэффициента - в, а в ячейку О25 его значение -70.
7. В В27 имя коэффициента - с, а в ячейку О27 его значение -662.
8. Заполнить В3:N3 значениями аргумента от -12 до 12 шагом 2.
9. Заполнить В4:N4 значениями функции и построить график (как в лр.1).
10. Выделить ось Х графика , щёлкнуть правой клавишей мыши, выбрать «формат оси», закладку «шкала» и перенести ось У в категорию 7.
11. Аналогично войти в формат оси У, закладку «шкала» и настроить шкалу как на рис.64.
Рис. 64
12. Вызвать панель управления Visual Basic (Вид- панели инструментов – Панель управления). Задать режим конструктора, щёлкнув по треугольнику на панели управления.
13. Из набора инструментов панели управления выбрать «полосу прокрутки» и растянуть прямоугольник в области С23:N23. Не снимая выделения с появившейся полосы прокрутки, щёлкнуть по кнопке «свойства» панели управления и настроить свойства нового объекта:
Полоса прокрутки для а
Name-Scra MIN- -10 MAX- 10 Value - -4
14. Аналогично разместить ещё две полосы прокрутки, настроив их следующим образом:
Полоса прокрутки для в
Name – ScrB MIN- -100 MAX- 100 Value- -70
Полоса прокрутки для с
Name- ScrC MIN—1000 MAX- 1000 Value- -662
В результате на листе будут размещены объекты как на рис. 65.
Рис. 65
15. Щелкнуть два раза отрывисто по ScrA. При этом откроется процедура обработки события изменения положения бегунка полосы прокрутки коэффициента «а» ScrA_Change. Внести следующий код, связывающий значение в ячейке А23 и положение бегунка.
Private Sub scra_Change()
Range("O23").Value = scra.Value
End Sub
16. Повторить этот код в процедуре Scroll. Это рекомендуется делать для того, чтобы значения в ячейке А23 менялись не рывком, а плавно.
Private Sub scra_Scroll()
Range("O23").Value = scra.Value
End Sub
17. Повторить эти действия для двух других линеек прокрутки.
Private Sub scrb_Change()
Range("O25").Value = scrb.Value
End Sub
Private Sub scrb_Scroll()
Range("O25").Value = scrb.Value
End Sub
Private Sub scrc_Change()
Range("O27").Value = scrc.Value
End Sub
Private Sub scrc_Scroll()
Range("O27").Value = scrc.Value
End Sub
18. Закончить режим конструктора, щёлкнув по треугольнику на панели управления, изменить положение бегунков и убедиться, что связь линеек с функцией работает. При изменении положения бегунка «а» график изменяет направление ветвей, при изменении положения бегунка «в» график перемещается по горизонтали, при изменении «с» график перемещается по вертикали.
Упражнение 17. «Звёзды».
Задание. Создать точечный график. Фоном графика задать рисунок с изображением звездного неба (заранее разместить на жестком диске). Настроить вид точек графика как большие жёлтые круги – звёзды. Написать код, при котором изменяются положения точек:
o При щелчке по кнопке «Мерцание» звёзды случайным образом появляются и гаснут
o по щелчку по кнопке «звездопад» звёзды движутся в сторону Земли.
o При щелчке по кнопке «Конец» всё останавливается.
Рис 66
Порядок выполнения.
1. Заполнить область A1:F2 первичными данными в соответствии с рис. 66.
2. Создать в ячейке А3 формулу =А2+0,5 и протянуть маркёром заполнения до С3.
3. Создать в ячейке D3 формулу = D2-1 и протянуть маркёром заполнения до F3.
4. Выделить область А2:F2 и вызвать мастер диаграмм. Заказать точечную диаграмму.
5. В процессе построения диаграммы выбрать закладку «Ряд», определить область значений по оси Х - А2:С2, а область значений по оси У - D2:F2. Затем можно щёлкнуть по кнопке ГОТОВО. Получится график как на рис. 67.
Рис. 67
6. Для осей Х и У произвести следующие настройки:
· Убрать галочки с «авто», т.е. сделать фиксированные шкалы осей.
· Значение мин=0, макс=40.
· Убрать метки делений (закладка «Вид»).
7. Для «Формата области построения» заказать рисунок звёздного неба, например Star.jpg, предварительно расположенный на жестком диске.
8. Выделить любую точку диаграммы, щёлкнуть правой кнопкой мыши и выбрать «Формат рядов данных». Настроить вид точки так, чтобы она выглядела типа жёлтой звёздочки. Размер маркёра задать 10.
9. Удалить надпись Ряд 1.
10. Указать левой кнопкой мыши на линии сетки , затем щёлкнуть правой и выбрать «Очистить». В результате пропадут линии сетки.
11. На листе EXCEL Вызвать панель инструментов «Элементы управления». Задать Режим конструктора. Разместить рядом с диаграммой три кнопки. Настроить свойства каждой кнопки следующим образом (табл. 25 ):
Таблица 25
| №пп | Свойство | Значение |
| 1 | Name | CMDgo |
| Caption | Звездопад | |
| 2 | Name | CMDstar |
| Caption | Мерцание | |
| 3 | Name | CMDend |
| Caption | Конец |
12. Щёлкнуть два раза отрывисто по кнопке «Звездопад», при этом откроется процедура описания кода события «Щёлчок по кнопке» CMDgo_Click, внести следующий код:
Private Sub CMDGo_Click()
Do Запускаем бесконечный цикл
For j = 1 To 6 Запускаем цикл заполнения исходных данных
ActiveSheet.Cells(2, j).Value = 40 * Rnd + 5 область А2: F 2 заполняется случайными числами
Next в пределах от 5 до 45
Do Запускаем цикл движения звёзд
Range("A2:F2").Value = Range("A3:F3").Value С каждым шагом цикла координата по Х каждой точки графика изменяется на 0,5, а по У на -1.
t = Timer Цикл задержки на 0,1 сек
Do Оператор DoEvents означает «посмотри
DoEvents вокруг», используется для того, чтобы во время паузы все процессы продолжались
Loop While Timer - t < 0.1
Loop While Range("F2").Value > 0 Условие закрытия цикла движения - пока У3>0
Loop Закрываем бесконечный цикл
End Sub
13. Щёлкнуть два раза отрывисто по кнопке «Мерцание» и внести следующий код, (он отличается от предыдущего только тем, что нет цикла движения и уменьшена область случайных чисел, но увеличен цикл задержки):
Private Sub CMDStar_Click()
Do Запускаем бесконечный цикл
For j = 1 To 6 Заполняем исходные данные случайными числами
ActiveSheet.Cells(2, j).Value = 30 * Rnd + 5 заполняем область А2:F2 случайными числами от 5 до 35
Next Закрываем цикл заполнения
t = Timer Запускаем цикл задержки на 0,2 ек
Do
DoEvents
Loop While Timer - t < 0.2 Закрываем цикл задержки
Loop Закрываем бесконечный цикл
End Sub
14. В процедуре кнопки «Конец» написать только одно слово «End», что означает останов всех процессов.
Private Sub cmdend_Click()
End закончить программу
End Sub
15. Закончить режим конструктора, убедиться в правильной работе кнопок. При щелчке по кнопке «Звездопад» точки начинают двигаться, при щелчке по кнопке «Мерцание» появляются и через некоторое время пропадают, при щелчке по кнопке «Конец» все останавливается.
Самостоятельная работа 21.
Задание. Изменить код программы таким образом, чтобы звёзды мерцали чаще, а падали не слева направо, а справа налево.
Составление макросов с помощью написания кода в Visual Basic.
Упражнение 18. «Таблица истинности».
Задание. Составить таблицу истинности функции F = 
Порядок выполнения.
Создать макрос «Табл», в который записать следующий код:
Sub Табл ()
Dim А, В, Z, F As Boolean
Range("B1") = "А": Range("C1") = "В" Формируем строку заголовка
Range("D1") = "Z" : Range("E1") = "F"
i = 2 Начинаем таблицу со 2 строки
For А = True To False Цикл перебора по А
For В = True To False Цикл перебора по В
For Z = True To False Цикл перебора по Z
With Application Формируем значения в столбцах
Cells(i, 1) = i - 1
Cells(i, 2) = А
Cells(i, 3) = В
Cells(i, 4) = Z
Cells(i, 5) = Not A Or B Imp Not Z
End with
i = i + 1 переходим на следующую строку
Next закрываем цикл по Z
Next закрываем цикл по В
Next закрываем цикл по А
End Sub
В результате запуска макроса создаётся таблица истинности заданной функции (табл. 26).
Таблица 26
|
| A | B | Z | F |
| 1 | -1 | -1 | -1 | 0 |
| 2 | -1 | -1 | 0 | -1 |
| 3 | -1 | 0 | -1 | -1 |
| 4 | -1 | 0 | 0 | -1 |
| 5 | 0 | -1 | -1 | 0 |
| 6 | 0 | -1 | 0 | -1 |
| 7 | 0 | 0 | -1 | 0 |
| 8 | 0 | 0 | 0 | -1 |
.
Вопросы к упражнению.
1. Сколько строк будет в таблице истинности, если функция будет зависеть от четырёх переменных?
2. Какой код следует дописать в программе, чтобы текст в строке заголовка выводился жирным шрифтом?
Самостоятельная работа 22.
Задание. Составить таблицу истинности функции–
1 вариант. Логического сложения.
2 вариант. Логического следования
3 вариант. Логического умножения.
4 вариант. Функции F = 
Дополнительные задания к лабораторной работе 4.
Самостоятельная работа 23.
Задание. По аналогии с упражнением «Анализ функции» выполнить анализ синусоиды Y = ASin (х W ), в зависимости от значений A и W . Показать, что при изменении А (изменение от 0 до 50 шагом 1) изменяется амплитуда синусоиды , а при изменении W (от 0 до 10 шагом 1) изменяется частота. Пределы изменения х от 0 до 3,4 шагом 0,15. Пример размещения объектов на рис. 68.
Рис. 68
Пояснения к выполнению.
Now – функция Visual Basic, возвращающая системную дату и время компьютера.
Порядок выполнения.
1. Создать в своей директории новый файл Excel. Набрать в нём заголовок «Журнал пользователей». Создать шапку таблицы как на рис. 69. В ячейке F1 организовать подсчёт количества записей в столбце А помощью функции МАКС: =МАКС(A:A).
Выделить столбец В, выбрать пункт меню ФОРМАТ – ЯЧЕЙКИ , в разделе ЧИСЛО задать тип данных в этом столбце – текстовый. Аналогично настроить типы данных для столбца С – дата, для столбцов D и Е – время. Сохранить файл под именем “s.xls”. Закрыть файл.
Рис. 69
2. Открыть свою рабочую книгу. Войти в редактор Visual Basic. В проводнике проекта указать на «Эта книга», затем представить документ в виде кода (View – Code), выбрать в списке объектов Workbook, событие Open (Рис. 70 ).
Рис. 70
3. Внести в процедуру следующий код:
Private Sub Workbook_Open()
Dim i As String ' Объявляем строковую переменную
Dim row As Integer ' объявляем вспомогательную переменную row
i = InputBox("Ваше имя?", "", "ХХ") ' Запрашиваем имя пользователя
Application.ScreenUpdating = False ' Даём команду не обновлять экран
Workbooks.Open ("D:\....\s.xls") ' Необходимо указать полный путь до файла s.xls
With Workbooks("s.xls").Worksheets("Лист1")
row = Range("F1").Value + 3 ' Вычисляем номер заполняемой строки
Cells(row, 1).Value = row - 2 ' Создаём порядковый номер строки
Cells(row, 2).Value = i ' Вносим пользователя
Cells(row, 3).Value = Date ' Вносим дату начала работы
Cells(row, 4).Value = Time Вносим время начала работы
End With
Workbooks("s.xls").Save ‘Сохраняем файл “s.xls”.
Workbooks("s.xls").Close ‘ Закрываем файл “s.xls”
Application.ScreenUpdating = True ' Разрешаем обновление экрана
End Sub
4. Выбрать в списке событий событие BeforeClose.
Внести следующий код:
Private Sub Workbook_Close()
Dim i As String ' Объявляем строковую переменную
Dim row As Integer ' объявляем вспомогательную переменную row
Application.ScreenUpdating = False ' Даём команду не обновлять экран
Workbooks.Open ("D:\издат\s.xls") ' Необходимо указать полный путь до файла s.xls
With Workbooks("s.xls").Worksheets("Лист1")
row = Range("F1").Value + 2 ' Вычисляем номер заполняемой строки
Cells(row, 5).Value = Time ‘Вносим время окончания работы
End With
Workbooks("s.xls").Save
Workbooks("s.xls").Close
Application.ScreenUpdating = True ‘Разрешаем обновлять экран
End Sub
5. Запустить поочерёдно процедуру Open. Перейти в Excel, открыть файл “s.xls” и убедиться, что внесено имя пользователя и время начала работы. Закрыть файл “s.xls”, затем свою рабочую книгу. Открыть файл “s.xls” и убедиться, что время окончания внесено по аналогии с табл. 27.
Таблица 27
Журнал пользователей
Всего записей
2
№пп
Пользователь
1
Паша Филонов
29 сентября 2006 г.
19:10
19:11
2
Миша Белокриницкий
29 сентября 2006 г.
19:15
19:16
6. Закрыть файл “s.xls”.
Самостоятельная работа 24.
Задание. Применив функцию Visual Basic - TimeDiff, дописать код процедуры BeforeClose в предыдущем упражнении так, чтобы в столбец F первого листа книги S .хls записывалось общее время сеанса работы в минутах.
Самостоятельная работа 25.
Задание. Предположим, что вам нужно сформировать регистрационный номер каждого клиента так, чтобы он состоял из буквы от А до Д из цифры от 1 до 9, например Б7. Создать макрос, который выводит на лист Excel список всех комбинаций номеров и их порядковый номер.
Вопросы для самоконтроля.
1. Какой код нужно дописать в процедуру BeforeClose в упражнении «Журнал пользователей», чтобы заполненные ячейки имели обрамление и шрифт вносимого текста был 14 размера?
2. В каких случаях удобнее обращаться к ячейкам как к объекту Range , а в каких как к объекту Cells?
3. Чем отличается объект Range в Word и в Excel?
Литература
1. Пастко В., Microsoft Office для пользователей. Киев,BHV, 1998.
2. Иванов В, Microsoft Office System 2003. Питер, BHV, 2004.
3. Зеньковский В., Excel в экономических и инженерных расчётах. Москва: Солон- Пресс, 2004.
4. Столяров А., Шпаргалка по Excel. Москва: Вербо, 1997.
5. Морозов В., Практикум по компьютерным технологиям. Москва: ABF, 1996.
6. Шафрин Ю., Основы компьютерных технологий. Москва: ABF, 1996г.
7. . Кузьменко В, VBA 2000. Москва: Бином, 2000.
8. Эйткен П., Разработка приложений на VBA в среде Office XP. Москва: РН PTR, 2003.
9. . Гарнаев А, VBA- самоучитель. Санкт- Петербург: БХВ-Петербург, 2004г.
10. . Гарнаев А, VBA. Наиболее полное руководство. Санкт – Петербург: БХВ – Петербург,2005.
ФЕДЕРАЛЬНОЕ АГЕНСТВО ВОЗДУШНОГО ТРАНСПОРТА
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО
ПРОФФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ГРАЖДАНСКОЙ АВИАЦИИ
МГТУ ГА
Кафедра прикладной математики
Е.П. Пегова
Последнее изменение этой страницы: 2019-03-22; Просмотров: 445; Нарушение авторского права страницы
Маркёр выделения – выглядит как большой белый крестик. Предназначен для выделения ячейки или области. Приёмы работы с маркёром выделения : удерживая клавишу Shift можно указать на две диагонально расположенные ячейки, при этом будет выделена прямоугольная область , диагональю которой являются эти ячейки. Этот приём используется для выделения больших областей, не умещающихся на экране; 
Маркёр заполнения – выглядит как маленький черный крестик. Задаёт алгоритм заполнения последующих ячеек.
Маркёр перемещения - крестик со стрелками. Он появляется, если навести мышь на границу выделенной области. Удерживая левую клавишу мыши, можно перемещать содержимое выделенной ячейки. Если при этом удерживать клавишу Ctrl, то содержимое ячейки будет копироваться.