Построение графиков и диаграмм

⇐ ПредыдущаяСтр 4 из 11Следующая ⇒

Цель занятия . Изучение информационной технологии использования возможностей Excel для статистических расчетов, графического представления данных и прогнозирования.

Инструментарий. ПЭВМ IBM PC, пакет программ MS.

Порядок выполнения работы:

1. Записать тему и цель работы в тетрадь.

2. Оформить отчет о выполненной работе.

Задание 1. С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн. чел.) за 1970- 2005 гг.

Исходные данные представлены на рис.1, результаты работы на рис.3

Рис.1. Исходные данные

Порядок работы.

1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте на листе 1 таблицу численности населения по образцу (рис.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет (введите первые два значения даты - 1970 и 1977, выделите обе ячейки и протяните вправо за маркер автозаполнения до нужной конечной даты).
3. Постройте диаграмму (обычная гистограмма) по данным таблицы. Для этого выделите интервал ячеек с данными численности населения А3: G3 и выберите команду Вставка/ Диаграмма. На первом шаге работы с Мастером диаграмм выберите тип диаграммы – гистограмма обычная; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с годами В2: G2 (рис.2). Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляется по подсказкам мастера.

Рис.2. Мастер диаграмм

Задание 2. Осуществить прогноз численности населения России на 2012 г. добавлением линии тренда к ряду данных графика
Краткая справка. Для наглядного показа тенденции изменения некоторой переменной целесообразно на график вывести линию тренда. Это возможно не для всех типов диаграмм, а только для гистограмм, линейчатых диаграмм, диаграмм с областями, графиков. Введенная линия тренда сохраняет связь с исходным рядом, т.е. при изменении данных соответственно изменяется линия тренда. Линию тренда можно использовать для прогноза данных.
Порядок работы.

1. Добавьте линию тренда к диаграмме, построенной в Задании 1. Для этого по диаграмме выполните щелчок правой кнопки мыши и выберите команду Добавить линию тренда.

2. В открывшемся окне Линия тренда (рис.3) на вкладке Тип выберите вид тренда полиномиальный 4-й степени;

Рис.3. Выбор типа линии тренда

3. Для осуществления прогноза на вкладке Параметры введите название кривой «Линия тренда» и установите параметр прогноза - вперед на 1 период (рис.4).

Рис.4. Параметры линии тренда

4. На диаграмме будет показана линия тренда и прогноз на один период вперед (рис.5).

Рис.5. Линия тренда и прогноз на один период вперед

5. Добавьте линии сетки на диаграмме (выполните щелчок правой кнопкой мыши по области диаграммы и выберите пункт Параметры диаграммы).

Рис.6. Параметры диаграммы

6. Измените цену одного деления оси Y с 5 на 1 (Выполните двойной щелчок по оси и на вкладке " Шкала" задайте новые значения (рис.7)).

Рис.7. Формат оси

7. Средствами рисования проведите линию красного цвета (на ось Y) для определения значения прогноза на 2012 г.

Рис.8. Конечный вид диаграммы

8. Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн.чел. Внесите численное значение прогноза на 2012 г. в исходную таблицу.

Требования к содержанию и оформлению отчета по работе.

Отчет по работе выполняется письменно в тетради. Отчет должен содержать: тему работы; условие и ход решения для каждого задания.

Практическая работа №12

Вычисления в электронных таблицах, использование логических функций

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

Порядок выполнения работы:

1. Записать тему и цель работы в тетрадь.

2. Оформить отчет о выполненной работе.

Инструментарий. ПЭВМ IBM PC, пакет программ MS.

Использование функции " Если"

Основное богатство табличного процессора Microsoft Excel составляют функции. С их помощью можно решить практически любую задачу, важно только верно их использовать и знать, что именно они умеют делать.

Представьте себе, что вам необходимо заполнить колонку на рабочем листе разными данными, которые зависят от значений другой колонки. Например, вы хотите отобразить на рабочем листе наличие скидки на купленные товары, но знаете, что скидка размером в 5% предоставляется только в том случае, если товаров было куплено на сумму больше чем 5000 руб. Таким образом, в зависимости от значений колонки " общая сумма покупки" колонка " величина скидки в процентах" будет равна 5 или 0. Для решения такой задачи в Excel предназначена функция " Если".

Рис. 1. Исходная таблица

Активизировав нужную ячейку, запустите мастер функций и выберите функцию " Если".

Функция " Если" содержит три аргумента. Первый является проверяемым условием, другие два - значениями, записываемыми в ячейку в случае выполнения или невыполнения условия.

В нашем случае проверяется условие, больше ли значение колонки " Общая сумма покупки" числа 5000. Поэтому в строке " Лог_выражение" запишем следующее: " E2< =5000" (в ячейке E2 размещена соответствующая сумма покупки). В строке " Значение_если_истина" должна быть записана величина, которая будет размещена в ячейке в том случае, если условие, записанное строкой выше, выполнится. А в нашем случае здесь необходимо записать величину скидки, если сумма покупки превышает 5000 руб. Поэтому в строке " Значение_если_истина" запишем число 0. В строке " Значение_если_ложь" запишем число 5, ибо здесь размещается значение, которое запишется в ячейку в случае невыполнения условия.

Рис. 2. Так выглядят параметры функции " Если"

После ввода всех параметров нажимаем " Оk", и в первой ячейке диапазона будет размещен результат работы функции " Если". Для заполнения остальных ячеек диапазона необходимо осуществить автозаполнение формулы на весь диапазон ячеек.

Рис. 3. Полученный результат

Вложенная функция " Если" используется в тех случаях, когда значений, от которых зависят значения другой колонки, больше двух. Например относительно таблицы, рассмотренной выше, если размер скидки не только зависит от общей суммы покупки, но и увеличивается с увеличением суммы (при сумме больше 5000 скидка равна 5%, при сумме, превышающей 15000 - 10%, если сумма больше 25000 - скидка равна 15%).

Вначале построим функцию " Если" для определения скидки в случае, когда сумма покупки превышает 5000 руб. В поле " Лог_выражение" в этот раз запишем последнее условие (E2> =25000), а в поле " Значение_если_истина" - соответствующее значение скидки (15). В поле " Значение_если_ложь" необходимо предусмотреть все остальные возможные варианты, а именно, когда сумма покупки меньше 25000, но может превышать 15000 или 5000. То есть в случае невыполнения изначального условия в ячейке может быть записано одно из нескольких значений (10, 5 или 0). В этом случае необходимо использовать вложенные функции. Для повторного использования функции " Если" необходимо поставить курсор в поле " Значение_если_ложь" и нажать на кнопку " Если", расположенную в поле имени (рис. 4).

Рис. 4. Построение формулы для вложенных " Если".

На экране появится окно мастера функций для заполнения полей первого вложения функции " Если". И в этом случае при заполнении поля " Значение_если_ложь" необходимо задать использование еще одной вложенной функции " Если".

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

Рис. 5. Построение последнего вложения

Обратите внимание на то, что все параметры функций разделены точкой с запятой, а параметры каждой из функций " Если" взяты в скобки. Если помнить это правило, вложенные " Если" можно записывать вручную без использования мастера функций.

При использовании вложенных функций необходимо помнить еще одно правило: количество вложений равно количеству значений, с которыми осуществляется сравнение минус один. В нашем примере сравнение происходит с тремя значениями (5000, 15000 и 25000), это означает, что мы должны использовать две вложенных функции " Если".

Практическая часть

Задание 1. Расчет скидок

Создайте в своей рабочей папке папку с названием Практика 30 Фамилия, где вместо Фамилия вставьте свою фамилию. В этой папке создайте новый документ Microsoft Excel и назовите его Расчет скидок.

Первый лист файла переименуйте в Расчет скидок 1. Для этого выполните двойной клик на Лист1, введите новое название листа и нажмите Enter

Создайте шапку таблицы: в первой строке в первом столбце напишите Цена, во втором “Цена со скидкой”, выравнивание по центру, шрифт – полужирный

Для остальной таблицы также установите выравнивание по центру.

В первом столбце под шапкой таблицы с помощью функции СЛУЧМЕЖДУ создайте ряд из 30 случайных чисел в диапазоне от 1 до 10000

В соответствии с информацией, имеющейся в теоретической части данной работы, во втором столбце с помощью функции ЕСЛИ реализуйте такую обработку чисел первого столбца:

· если число в первом столбце больше 5000, число во втором столбце равно числу в первом столбце минус 10 процентов

· если число в первом столбце меньше или равно 5000, число во втором столбце равно числу в первом столбце минус 5 процентов

Задание 2. Расчет скидок – вложенная функция ЕСЛИ

Второй лист файла переименуйте в Расчет скидок 2.

Создайте таблицу, аналогичную заданию 1: первый столбец – Цен, в нем 30 значений от 1 до 10000, второй столбец – Цена со скидкой.

· если число в первом столбце больше 2000, число во втором столбце равно числу в первом столбце минус 5 процентов

· если число в первом столбце больше 7500, число во втором столбце равно числу в первом столбце минус 15 процентов

Задание 3.

Скопируйте из папки с заданием в папку Практическая 30 файл Продажи книг.

Откройте его в своей папке.

В данном файле представлены данные о продаже книг. При покупке более 100 штук устанавливается скидка в размере 5% от общей стоимости продажи, при покупке более 200 штук – 10% от общей стоимости продажи. Необходимо рассчитать стоимость проданных магазину книг, скидку с учетом льгот, итог с учетом скидки и сумму в строке Итого.

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

Требования к содержанию и оформлению отчета по работе.

Практическая работа №13

Формулы и функции

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

Порядок выполнения работы:

1. Записать тему и цель работы в тетрадь.

2. Оформить отчет о выполненной работе.

Инструментарий. ПЭВМ IBM PC, пакет программ MS.

Задание 1. Переименование ячеек и создание ссылок

Создайте лист Excel Правая кнопка/Создать/Лист Microsoft Office Excel и назовите его по своему усмотрению.

В данном листе переименуйте ячейку А1 в Приход, B1 в Расход, C1 в Итог. Правая кнопка\Имя диапазона, ввести имя и нажать ОК. Либо чтобы переименовать ячейку, нужно ввести имя в название ячейки вверху слева листа Excel.

В Приход введите одно число, в Расход другое. В ячейке Итог создайте формулу =Приход-Расход.

В ячейке B5 создайте ссылку на внешний файл: Правая кнопка/Гиперссылка, выберите какой-то из Ваших файлов и нажмите OK.

Сохраните полученный лист и покажите результат преподавателю.

Задание 2. Работа с формулами

Чтобы переименовать ячейку, нужно ввести имя в название ячейки вверху слева листа Excel.

За основу задания возьмем Таблицу 1. В данной таблице подсчитайте по формулам поля:

* Закупочная цена в $: - в зависимости от текущего курса $, который заносится в отдельную ячейку D1, которую переименовать в kurs.

* Сумма закупки: = Закупочная цена товара (столбец D)*Количество товара (столбец F)

* Сумма реализации: = Розничная цена товара (ячейка I2-бананы, I3-виноград, I4-ананасы, I5-апельсины)*Количество

* Валовой доход: = Сумма реализации (столбец H) - Сумма закупки (столбец G)

Таблица 3

A	B	C	D	E	F	G	H	I
		Сегодня курс					Розничная цена, руб
							бананы	12, 9
							виноград	15, 2
							ананасы	14, 8
							апельсины	14, 5
№ п/п	Наименование товара	Фирма поставщик	Закуп. цена (руб)	цена ($)	Кол-во	Сумма закупки	Сумма реализации	Валовой доход
	бананы	Frutis		=D7/kurs		=D7*F7	=F7*I$2	=H7-G7
	бананы	SUMP	12, 32	=D8/kurs		=D8*F8	=F8*I$2	=H8-G8
	бананы	Forum	11, 98	=D9/kurs		=D9*F9	=F9*I$2	=H9-G9
	бананы	UFO	11, 57	=D10/kurs		=D10*F10	=F10*I$2	=H10-G10
	виноград	Frutis	13, 1	=D11/kurs			= F11*I$3
	виноград	SUMP		…		…	…	…
	виноград	Forum	11, 32
	виноград	UFO	11, 55
	ананасы	Frutis					= F14*I$4
	ананасы	SUMP	13, 21
	ананасы	Forum	11, 59
	ананасы	UFO	12, 31
	апельсины	Frutis	12, 11				= F14*I$5
	апельсины	SUMP	12, 23
	апельсины	Forum	11.17
	апельсины	UFO	11, 31

В данном примере ячейке D1 присвоено имя « kurs», которое используется во всех формулах для пересчета закупочной цены в $. Эта ячейка содержит только число (в нашем примере 32), а пояснительный текст содержится в ячейке С1.

При подсчете суммы реализации, использован другой прием для того, чтобы можно было правильно копировать формулы: для ссылки на розничную цену конкретного товара используется частично абсолютный адрес: I$2 - в этом адресе запрещено изменение номера строки, поэтому при копировании такой формулы для товара " бананы" ошибок не возникнет (и для других товаров – тоже). Для других товаров и фирм нужно создать формулы со ссылкой на их розничную цену.

По приведенным выше формулам подсчитайте валовый доход для всех товаров и фирм.

Требования к содержанию и оформлению отчета по работе.

Практическая работа №14

⇐ Предыдущая 1 2 345 6 7 8 9 10 Следующая ⇒