- •Введение
- •Лабораторная работа №1 общие приемы работы в среде ms excel
- •Методические указания Структура окна ms Excel.
- •Управление окнами и режимы отображения рабочих книг.
- •Перемещение по рабочей книге и выделение диапазонов ячеек.
- •Загрузка и сохранение документа.
- •Работа со справочной системой ms Excel.
- •Задания для выполнения работы Задание 1. Настройка оконного интерфейса ms Excel.
- •Задание 2. Выделение диапазонов ячеек.
- •Задание 3. Создание, редактирование и сохранение рабочей книги.
- •Задание 4. Работа со справочной системой ms Excel.
- •Контрольные вопросы
- •Лабораторная работа №2 Форматирование и рЕдактирование ячеек и таблиц
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №3 Числовые (пользовательские) форматы данных
- •Методические указания
- •Положит.Число; отрицат.Число; нуль; @ "текст".
- •Коды числовых форматов.
- •Коды цветов.
- •Коды условий.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №4 Реализация арифметических и логических вычислений
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №5 Обработка Массивов в ms excel
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №6 Адресация в ms Excel
- •Методические указания
- •Абсолютная адресация:
- •Относительная адресация:
- •Смешанная адресация:
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №7 использование встроенных функций
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №8 Построение графиков и диаграмм
- •Методические указания
- •Редактирование диаграмм.
- •Применение диаграмм для анализа данных.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №9 поиск решения средствами ms excel
- •Методические указания
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №10 Работа с базами данных
- •Методические указания Обработка списков данных. Основные понятия.
- •Рекомендации по организации списка.
- •Ввод данных в список.
- •Форматирование и сортировка списка.
- •Фильтрация списков.
- •Автоматическое подведение промежуточных итогов.
- •Сводная таблица.
- •Консолидация.
- •Задания для выполнения работы
- •Исходные данные
- •Контрольные вопросы
- •Лабораторная работа №11 основы программирования в среде vba
- •Методические указания Основные понятия языка vba.
- •Range("f2").Select ‘выделение ячейки f2
- •Редактор vba.
- •Правила задания имен объектов.
- •Типы данных vba.
- •Описание переменных.
- •Использование констант.
- •Организация ввода/вывода информации.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Лабораторная работа №12 создание пользовательских функций средствами vba
- •Методические указания Создание пользовательских функций.
- •Основные операторы языка vba.
- •Задания для выполнения работы
- •Контрольные вопросы
- •Список использованной литературы
- •Приложение а Структура отчета
- •Приложение б Пример титульного листа
Задания для выполнения работы
Создайте новую рабочую книгу. На отдельном листе расположите таблицу с данными (табл. 8.1).
Таблица 8.1
Данные прихода и расхода по годам
Постройте гистограмму (столбиковую диаграмму) прихода и расхода в зависимости от года. Исходная таблица имеет 2 ряда данных: Приход и Расход и категорию - Год. Перед построением диаграммы необходимо выделить на рабочем листе таблицу с данными, включая строку с заголовками данных, на основе которых будет строиться диаграмма. Затем вызвать Мастера диаграмм из меню Вставка – Диаграмма.
На 1-м шаге в диалоговом окне Мастера диаграмм выбирать тип диаграммы «Обычная гистограмма» (рис. 8.10).
На 2-м шаге Мастера диаграмм, если таблица с исходными данными была выделена заранее, можно пропустить вкладку «Диапазон» и перейти на вкладку «Ряд». Так как ряды данных «Приход» и «Расход» располагаются в столбцах, то переключатель «Ряды в:» поставим в положение «в столбцах». Щелкнем по вкладке «Ряд», где показан предварительный вид диаграммы. Т.к «Год» является категорией, а не рядом данных, выделяем «Год» и щелкаем кнопку «Удалить». Помещаем курсор в поле ввода «Подписи оси X» и выделяем на рабочем листе диапазон, содержащий годы. Теперь вместо порядковых номеров по оси категорий будут проставлены годы. В списке «Ряд» выделим имя ряда «Приход», в поле ввода «Имя:» укажем адрес ячейки с заголовком «Приход», а в поле ввода «Значения:» - адрес блока с данными. Аналогичные действия выполним для ряда данных «Расход» (рис. 8.11).
Рис. 8.10. 1-й шаг Мастера диаграмм
Рис. 8.11. 2-й шаг Мастера диаграмм
На 3-м шаге Мастера диаграмм зададим параметры диаграммы на каждой из 6 вкладок. На вкладке «Заголовки» укажем название диаграммы «Результаты работы», оси X – заголовок «Годы». На вкладке «Подписи данных» выделим переключатель «значение» и над столбиками появятся числовые значения из исходной таблицы (рис. 8.12).
Рис. 8.12. 3-й шаг Мастера диаграмм
На последнем шаге Мастера диаграмм выбираем размещение диаграммы на исходном листе рядом с таблицей и щелкаем кнопку «Готово». В результате будет построена обычная гистограмма (рис. 8.13).
Рис. 8.13. Диаграмма «Обычная гистограмма»
Добавьте на построенную диаграмму новые данные: 2004 - 590 - 320. Для того, чтобы не перестраивать диаграмму, скопируйте данные в буфер обмена, перейдите на лист с диаграммой, выберите в меню Диаграмма – Добавить данные. В появившемся диалоговом окне «Новые данные» введите новый диапазон. Появится диалоговое окно Специальная вставка. Установите переключатель Добавить значения как новые ряды и флажок Категории (подписи оси X) в первой строке. Новые данные будут добавлены.
Спрогнозируйте рост расходов на полтора года вперед, используя линию тренда (Диаграмма – Добавить линию тренда… – Параметры… – ОК).
Постройте круговую диаграмму по данным «Год - Расход». Перед вызовом Мастера диаграмм выделите несмежные блоки – диапазон «Годы» и диапазон «Расходы».
Постройте нормированную гистограмму с накоплением, по данным табл. 8.2.
Таблица 8.2
Хранение овощей и фруктов
|
План |
Факт |
Картофель |
945 |
434 |
Свекла |
451 |
329 |
Лук |
310 |
215 |
Морковь |
330 |
195 |
Добавьте новые строки в таблицу и откорректируйте диаграмму.
Груши |
15 |
8 |
Яблоки |
80 |
45 |
Поверните диаграмму на 45 градусов (Диаграмма – Объемный вид – Формат трехмерной проекции… – ОК).
Напечатайте диаграмму на листе с данными (КЗМ – Формат области диаграммы и на вкладке Свойства установите флажок «Выводить объект на печать»). Затем снимите выделение с диаграммы, используйте меню Файл – Параметры страницы, кнопку Просмотр.
Постройте диаграмму заданного вида в соответствии с вариантом:
7.1. По данным табл. 8.3 постройте:
обычную гистограмму, отображающую сведения об изменении приходов, расходов и прибыли фирмы по результатам работы в период с 2003 по 2006 год, выполните форматирование диаграммы в соответствии с рисунком 8.14;
график, отражающий динамику изменения роста прибыли по годам;
добавьте на график линию тренда, спрогнозируйте изменение прибыли в 2008 году.
Таблица 8.3
Данные по результатам работы фирмы
Год |
Приход |
Расход |
Прибыль |
2003 |
2000 |
1500 |
500 |
2004 |
3600 |
2300 |
1300 |
2005 |
4100 |
2500 |
1600 |
2006 |
4500 |
2800 |
1700 |
Рис. 8.14. Пример обычной гистограммы
7.2. По данным табл. 8.4 постройте:
обычную гистограмму, отображающую сведения о количестве используемых марок комбайнов в период с 2002 по 2004 год, выполните форматирование диаграммы в соответствии с рис. 8.15;
график, динамики использования комбайнов каждой марки по годам;
добавьте на график линию тренда, прогнозирующую использование различных марок комбайнов в 2005 году.
Таблица 8.4
Сведения о количестве использовавшихся комбайнов
Марка |
Год |
||
2002 |
2003 |
2004 |
|
Нива |
680 |
810 |
3030 |
Дон |
330 |
780 |
2340 |
Енисей |
900 |
1500 |
3500 |
Прочие |
1500 |
1600 |
1300 |
Рис. 8.15. Пример обычной гистограммы
По данным таблицы 8.4 постройте:
круговую диаграмму, отображающую доли комбайнов каждой марки в общем парке комбайнов в течение трех лет, выполните форматирование диаграммы в соответствии с рис. 8.16;
график динамики использования комбайнов марки Дон по годам;
добавьте на график линию тренда, спрогнозируйте использование комбайна марки Дон в странах СНГ в 2007 году.
Рис. 8.16. Пример объемной круговой диаграммы
7.4. По данным таблицы 8.5 постройте:
гистограмму с накоплением, отражающую долю женщин и мужчин в учебных группах за время обучения, выполните форматирование диаграммы в соответствии с рис. 8.17;
график динамики изменения количества женщин за время обучения;
добавьте на график линию тренда, спрогнозировав изменение состава учебных групп, при шестилетнем обучении.
Таблица 8.5
Сведения об изменении состава учебных групп
Год обучения |
Женщины |
Мужчины |
1 |
12 |
12 |
2 |
14 |
11 |
3 |
13 |
11 |
4 |
15 |
9 |
5 |
15 |
9 |
Рис. 8.17. Пример гистограммы с накоплением
7.5. По данным таблицы 8.5 постройте:
нормированную диаграмму с областями, иллюстрирующими изменение доли женщин и мужчин в учебных группах за время обучения, выполните форматирование диаграммы в соответствии с рис. 8.18;
обычную гистограмму, отражающую динамику изменения количества женщин и мужчин в учебных группах за время обучения;
добавьте на обычную гистограмму линию тренда, спрогнозировав изменение количества мужчин в учебной группе при шестилетнем обучении.
Рис. 8.18. Пример нормированной диаграммы с областями
По данным таблицы 8.6 постройте:
объемную гистограмму, отображающую сведения о количестве побед, которые одержала команда в период с 2000 по 2004 год, выполните форматирование диаграммы в соответствии с рис. 8.19;
график, отражающий динамику изменения количества побед команды в чемпионатах по футболу в период с 2000 по 2004 год;
добавьте на график линию тренда, спрогнозировав изменение количества побед в 2006 году.
Таблица 8.6
Сведения о победах команды в чемпионатах по футболу
Год |
2000 |
2001 |
2002 |
2003 |
2004 |
Кол-во побед |
5 |
3 |
1 |
4 |
2 |
Рис. 8.19. Пример объемной гистограммы
По данным таблицы 8.7 постройте:
обычную гистограмму, отражающую сведения о количестве юношей и девушек в составе групп, выполните форматирование диаграммы в соответствии с рисунком 8.20;
график, отображающий изменение количества юношей и девушек в учебных группах;
добавьте на график линии тренда, спрогнозировав состав 3-ей группы.
Таблица 8.7
Сведения о количестве юношей и девушек в группах
№ группы |
105001 |
105002 |
Юноши |
8 |
10 |
Девушки |
14 |
12 |
Рис.8.20. Пример обычной гистограммы
По данным таблицы 8.8 постройте:
объемную гистограмму, отображающую сведения о количестве пассажиров, перевозимых различными видами городского транспорта в период с 1995 по 2005 год, выполните форматирование диаграммы в соответствии с рис. 8.21;
обычную гистограмму, отражающую динамику изменения количества перевезенных пассажиров в период с 1995 по 2005 года;
добавьте на обычную гистограмму линию тренда, спрогнозируйте изменение количества перевезенных пассажиров различными видами транспорта в 2010 году.
Таблица 8.8
Сведения о перевозке пассажиров различными видами городского транспорта
Вид транспорта |
Год |
||
1995 |
2000 |
2005 |
|
Метрополитен |
38 |
42 |
53 |
Троллейбус |
25 |
27 |
27 |
Автобус |
31 |
26 |
16 |
Трамвай |
6 |
5 |
4 |
Рис. 8.21. Пример объемной гистограммы
По данным таблицы 8.9 постройте:
линейчатую диаграмму, отображающую сведения рейтинга телевизионных передач в 2000 году, выполните форматирование диаграммы в соответствии с рис. 8.22;
график, отражающий динамику изменения рейтинга различных телевизионных передач по годам;
добавьте на график линию тренда, спрогнозировав изменение рейтинга телевизионных передач на 2010 год.
Таблица 8.9
Сведения о рейтинге телевизионных передач
Годы |
Номер передачи |
||||
1 |
2 |
3 |
4 |
5 |
|
2000 |
18,98% |
20,27% |
9,76% |
14,32% |
36,67% |
2005 |
11,34% |
15,46% |
22,9% |
30,3% |
20% |
Рис. 8.22. Пример линейчатой диаграммы
По данным таблицы 8.10 постройте:
линейчатую диаграмму, отображающую сведения о среднем балле студентов по математике, в разных учебных годах, выполните форматирование диаграммы в соответствии с рис. 8.23;
обычную гистограмму, отражающую динамику изменения среднего балла по математике у различных студентов;
добавьте на обычную гистограммы тренда, спрогнозировав средние баллы студентов на 2009-2010 учебный год.
Таблица 8.10
Сведения об успеваемости студентов по математике
ФИО студентов |
Средний балл |
||
2006-2007 уч.год |
2007-2008 уч.год |
2008-2009 уч.год |
|
Иванов В.В. |
8 |
5 |
7 |
Петров А.Л. |
9 |
7 |
8 |
Сидоров А.К. |
5 |
8 |
7 |
Рис. 8.23. Пример линейчатой диаграммы
По данным таблицы 8.11 постройте:
объемную диаграмму с областями (с накоплением), отображающую сведения об объемах продаж продукции фирмы в разных странах в период с 2000 по 2004 год, выполните форматирование диаграммы в соответствии с рисунком 8.24;
обычную гистограмму, отображающую динамику изменения объемов продаж в период с 2000 по 2004 год;
добавьте на обычную гистограмму линию тренда, спрогнозировав изменение объемов продаж в 2008 году.
Таблица 8.11
Сведения об объемах продаж
Страна |
Год |
||||
2000 |
2001 |
2002 |
2003 |
2004 |
|
Аргентина |
23,3 |
21,2 |
17,9 |
22,2 |
23,0 |
Чили |
12,3 |
14,5 |
15,0 |
16,2 |
17,0 |
Бразилия |
46,2 |
67,8 |
77,4 |
80,6 |
91,3 |
Рис. 8.24. Пример объемной диаграммы с областями (с накоплением)
По данным таблицы 8.12 постройте:
объемную диаграмму с областями (с накоплением), отображающую сведения о площадях занимаемых различными культурами в период с 2000 по 2004 год, выполните форматирование диаграммы в соответствии с рис. 8.26;
обычную гистограмму, отображающую динамику изменения объемов площадей различных культур в период с 2000 по 2004 год;
добавьте на обычную гистограмму линию тренда, спрогнозировав изменение площадей к 2006 году.
Таблица 8.12
Сведения о площадях
Культура |
2000 |
2001 |
2002 |
2003 |
2004 |
Пшеница |
20 |
23 |
27 |
30 |
35 |
Рожь |
8 |
8 |
12 |
14 |
17 |
Ячмень |
3 |
4 |
6 |
8 |
9 |
Кукуруза |
7 |
7 |
11 |
12 |
13 |
Рис. 8.26. Пример объемной диаграммы с областями (с накоплением)
Постройте график функции согласно варианту (табл. 8.13):
Таблица 8.13
Исходные данные для выполнения задания 8
Вариант |
Задания |
|
Постройте график функции при x [-1,8; 1,6] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-1,4; 1,8] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
Продолжение табл. 8.13
Вариант |
Задания |
|
Постройте график функции при x [-2; 1,4] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-2; 1,6] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-3; 1] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-1,4; 2] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-1,6; 1,8] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
Окончание табл. 8.13
Вариант |
Задания |
|
Постройте график функции при x [-1,8; 1,8] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-1,6; 1,6] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-2; 1,8] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-1,8; 1,6] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
|
Постройте график функции при x [-2; 2] с шагом 0,2.
На оси категорий должны отображаться значения х. На легенде должно отображаться имя ряда y. Оси должны пересекаться в нуле. |
Постройте поверхность согласно варианту (табл. 8.14).
Таблица 8.14
Исходные данные для выполнения задания 9
Вариант |
Задания |
|
Постройте поверхность z для х и у [0; 2] с шагом 0,1.
|
|
Постройте поверхность z для х и у [0; 2] с шагом 0,1.
|
|
Постройте поверхность z для х и у [-1; 1] с шагом 0,1.
|
|
Постройте поверхность z для х и у [-1; 1] с шагом 0,1.
|
|
Постройте поверхность z для х и у [-1; 1] с шагом 0,1.
|
|
Постройте поверхность z для х и у [0; 2] с шагом 0,1.
|
|
Постройте поверхность z для х и у [-4; 4] с шагом 0,2.
|
|
Постройте поверхность z для х и у [-5; 5] с шагом 1.
|
|
Постройте поверхность z для х [-2; 3] с шагом 0,5 и у [0; 1] с шагом 0,2.
|
Окончание табл. 8.14
Вариант |
Задания |
|
Постройте поверхность z для х и у [-8; 8] с шагом 1.
|
|
Постройте поверхность z для х и у [1; 20] с шагом 1.
|
|
Постройте поверхность z для х и у [-90; 90] с шагом 10.
|