Лабораторная работа 2. Построение диаграмм и графиков функций

 

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

 

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

Построение графиков и диаграмм осуществляется с помощью Мастера диаграмм. Его вызов производится либо с помощью команды Вставка | Диаграмма, либо щелчком по кнопке Мастер диаграмм в панели инструментов Стандартная.

 

Как построить диаграмму?

Рассмотрим таблицу 2.1, показывающую рост штатного состава подразделений фирмы. Порядок действий следующий.

 

Таблица 2.1 – Штат фирмы «Шмидт и сыновья»

 

Подразделение	Период
Январь	Октябрь
Офис 1
Офис 2

 

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

2. Нажимаем кнопку Мастер диаграмм и шаг за шагом проходим все этапы построения диаграммы.

Шаг 1. Выбираем тип диаграммы Гистограмма и вид диаграммы Объемная (рис. 2.1).

Шаг 2. Здесь указывается диапазон ячеек, содержащих данные (рис. 2.2). Так как таблица была заранее выделена, диапазон уже установлен. Проверьте в окне предварительного просмотра, как выглядит диаграмма. Если она не соответствует желаемому, укажите другой диапазон.

Отметьте, как расположены данные – в столбцах или строках. Если в строках, то подписями оси Х будут Январь, Октябрь, а категории Офис 1 и Офис 2 уйдут в легенду диаграммы, если в столбцах, то наоборот.

На вкладке Ряд можно указать другие данные для диаграммы, а также удалить или добавить ряды.

Шаг 3. В этом окне находится шесть вкладок (рис. 2.3), на которых можно изменить параметры выбранного типа диаграммы, например, написать название диаграммы, задать сетку, написать значения (или проценты, доли) над столбцами диаграммы и т.д.

Шаг 4. Здесь указывается место размещения новой диаграммы: либо на имеющемся листе, либо на новом листе (рис. 2.4).

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

Существует также удобный способ добавления новых данных к диаграмме: выделить новые данные в таблице и перетащить их на диаграмму. В соответствующем виде они появятся на поле диаграммы.

 

Как построить график зависимости функции одного переменного?

Пусть дана таблица 2.2 зависимости цены единицы некоторого товара от объема его продаж (известная в экономике «кривая спроса D-D»). Сразу отметим: если функция задана аналитической зависимостью y=f(x), то нужно предварительно ее протабулировать, то есть построить таблицу {x_i , y_i}, где x_i=x₀ + i×h – узловые точки; h = (x_n – x₀)/n – шаг табуляции; i = 0…n, а y_i=f(x_i). Для заполнения ряда x можно использовать режим автозаполнения или формулу увеличения значения х на один шаг.

 

Таблица 2.2 – Спрос

 

Объем продаж, тыс. шт.
Цена, руб.

 

Порядок построения графика следующий.

1. Выделяем всю таблицу и вызываем Мастер диаграмм.

2. На первом шаге выбираем Тип: Точечная и Вид: Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров. Обращаем ваше внимание на то, что Тип: График не пригоден в данном случае, так как показывает тенденции изменения данных за равные промежутки времени; при этом обе группы данных (х и у) отображаются в виде графиков.

3. На втором шаге в окне предварительного просмотра проверяем, правильно ли построен график. Обратите внимание: первая строка (или первый столбец, если данные расположены столбцом) воспринимается как данные оси Х, а вторая строка (столбец) или строки (столбцы), если они имеются, как данные оси Y.

4. Следующие шаги выполняются так же, как описано выше.

Результат приведен на рисунке 2.5.

Как построить график зависимости функции двух переменных?

Построим график производственной функции Кобба-Дугласа Y=A×K^aL^b, где А, a, b – константы, K – объем фондов , L – объем трудовых ресурсов, Y – выпуск продукции предприятием или отраслью. Эти переменные могут выражаться либо в стоимостном выражении, либо в натуральном количестве.

Пусть функция имеет вид:

Y=900×K^0,5L^0,25 ( тыс. руб.),

где K=100 ... 200 тыс. руб.; L=30 ... 50 тыс. руб.

Графическое представление функции двух переменных – поверхность в трехмерном пространстве.

Табулируем функцию, располагая значения K по горизонтали, а L – по вертикали; тогда на пересечении столбца со значением K_i и строки со значением L_i будет находиться значение функции Y_i (табл. 2.3).

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

 

Таблица 2.3 – Элемент листа Excel с табулированием функции двух переменных

	A	B	C	D	E
...	Значения L	Значения К
					...
		21063,1	22091,2	23073,5	...
		21890,7	22959,2	23980,1	...
		22633,8	23738,5	24794,1	...
	...	...	...	...	...

 

Например, в таблице 2.3 в ячейке В21 находится формула вида

= 900*B$20^0,5*$A21^0,25

Тогда при копировании формулы на все ячейки таблицы смена адресов для переменных будет проведена корректно (проверьте!).

Порядок построения этой поверхности следующий.

1. Выделяем всю таблицу: и значения аргументов, и значения функции. Обратите внимание: левая верхняя ячейка выделенной области таблицы (у нас это ячейка А20) должна быть пустая.

2. Вызываем Мастера диаграмм.

3. На первом шаге выбираем Вид: Поверхность, Тип: Поверхность.

4. На втором шаге можем предварительно посмотреть построенную поверхность и, при необходимости, изменить ряды данных.

5. На третьем шаге пишем название диаграммы, название оси Х (категорий) - это горизонтальный ряд данных, т.е. K, название оси Y (рядов данных) - это вертикальный ряд данных, т.е. L, и название оси Z (значений) – это наша функция Y.

6. На четвертом шаге размещаем построенную диаграмму на выбранном листе.

Обычно после построения требуется отредактировать диаграмму: сменить размер шрифта, фон стенок, размещение надписей и т.д. Для этого надо подвести стрелку к соответствующему объекту, щелкнуть правой клавишей мыши и из контекстно-зависимого меню выбрать соответствующую опцию. Пробуйте, экспериментируйте. Excel предоставляет для этого массу возможностей!

 

Результаты построения приведены на рисунке 2.6.

 

Отметим также, что, подведя курсор к какому-нибудь углу стенок области построения графика (появится надпись «Углы») и «схватив» мышью этот угол (появится тонкий крестик), можно двигать область диаграммы, рассматривая поверхность в различных ракурсах.

 

Задания

 

Задание 1. Построить диаграммы, иллюстрирующие табличные данные из лабораторной работы 1. Тип диаграммы выбрать исходя из степени наглядности представления информации. Обязательно включить название, подписи рядов данных, легенду. Поместить диаграмму на отдельном листе.

 

Задание 2. Построить графики функции одного переменного на отрезке хÎ[-2; 2] для одного из выбранных вариантов, приведенных ниже, при разных шагах табуляции: 0,5; 0,2; 0,1. Сравнить вид графиков, сделать выводы.

 

 

При записи формулы использовать функцию ЕСЛИ. Она возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис этой функции (рис. 2.7):

ЕСЛИ(условие;значение_если_истина;значение_если_ложь).

В условии для сравнения двух значений используются операторы сравнения: = (равно); > (больше); < (меньше); >= (не меньше); <= (не больше); <> (не равно).

Например, формула

=ЕСЛИ(А1>=0;КОРЕНЬ(A1);”число отрицательное!”),

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

Сравнивать можно как арифметические, так и текстовые выражения.

Примечание. Если после набора формулы в одном из полей (рис. 2.7), она вдруг окажется охваченной двойными кавычками, т.е. интерпретирована как текст – ищите ошибку. Это может быть несоответствие скобок, неверно записанное или использованное имя функции, адрес ячейки, содержащий русские буквы, пропущенный знак умножения и т.п.

 

Задание 3. Существует гипотеза, что параметры физической активности человека, его умственных способностей и эмоционального состояния можно описать периодическими функциями вида:

,

где t – время (дни, отсчитываемые со дня рождения t₀);

T_i – периоды: Т₁=23 дня для физического цикла; Т₂=28 дней для эмоционального цикла; Т₃=33 дня для интеллектуального цикла.

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

Для ячеек, где будут располагаться даты, установить формат Дата (Формат | Ячейки | Число | Дата). Для подсчета прожитых лет использовать функцию Год.

Синтаксис функции:

ГОД(дата)

Дата – это дата, год которой необходимо найти. Даты вводятся как текстовые строки в двойных кавычках, например, “31.12.03”, “7.12.2003”[7].

Вариант расположения информации на листе показан на рис. 2.8.

2. Построить графики биоритмов.

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

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

+ – если значения функции положительные;

– – если значения функции отрицательные;

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

+!!! – при совпадении значений всех трех функций в положительной области;

–!!! – при совпадении значений всех трех функций в отрицательной области.

Сравнивать значения рекомендуется по одному или двум знакам после запятой (т.е. решать задачу в некотором приближении) с использованием функции ОТБР, назначение которой – отсечение дробной части числа до указанного количества разрядов.

Формирование условий для функций ЕСЛИ потребует применения логических функций И и ИЛИ.

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис функции:

И(условие1; условие2; …).

Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис функции:

ИЛИ(условие1; условие2; …).

В рассматриваемой задаче условие – это сравнение типа «равенство».

Примечание. Задания на построение поверхности будут предложены в лабораторных работах 3 и 4.