Лабораторная работа №7. Построение графиков, поверхностей и диаграмм в Excel.
| Что осваивается и изучается? Диаграммы. Построение и редактирование диаграмм различных типов. Применение диаграмм в прогнозировании. |
Задание 1. Составить таблицу расчета доходов фирмы в абсолютном и процентном отношении и диаграмму роста доходов на основе данных о доходах фирмы.
Выполнение.
1. Составить таблицу расчета доходов фирмы: определить тип, размер и стиль шрифтов для заголовков строк и столбцов: Times New Roman Cyr, размер 12, стиль полужирный; для остального текста - Times New Roman Cyr, размер 10, стиль обычный;
2. Вычислить рост уровня доходов фирмы в процентном отношении в каждом месяце 1999 года по отношению к январю 1999 года (3-й столбец таблицы);
=(Ci-C$3)/C$3 где Сi – адрес ячейки i-го месяца графы Уровень доходов фирмы в 1999 году, С$3 – абсолютный адрес ячейки Уровень доходов фирмы за январь 1999 года;
3. Вычислить суммарный уровень доходов фирмы за 1999 и 1998 годы, результаты поместить в последней строке второго и третьего столбца соответственно;
4. Вычислить среднее значение роста уровня доходов в процентах, результат поместить в последней строке четвертого столбца;
5. Построить диаграмму зависимости уровня доходов фирмы за 1999 и 1998 годы по месяцам в виде гистограммы;
6. Построить диаграмму зависимости уровня доходов фирмы в процентном отношении в виде линейного графика;
7. Построить совмещенную диаграмму (тип нестандартная/график|гистограмма 2) по данным полученной таблицы (второй, третий и четвертый столбцы);
8. Рассмотреть другие типы диаграмм, освоить редактирование элементов диаграмм.
Задание 2. Составить круговую диаграмму с отображением среднего балла по предметам на основании таблицы "Итоги экзаменационной сессии" Лабораторной работы №3_3.
Итоги экзаменационной сессии
| № п/п | Ф. И.О. | Математика | Эконом. Теория | Информатика |
| 1. | Макаров С.П. | |||
| 2. | ... | ... | ||
| 3. | ||||
| … | ||||
| Средний балл |
Задание 3. Построить график функции y=sin x. Значение аргумента х выбрать в пределах от –6 до 6 с шагом 0,5.
Выполнение.
Построим таблицу следующего вида
| X | -6,0 | -5,5 | -5,0 | ... | |||||
| Y | 0,28 | 0,71 | 0,96 | … |
Для чего заполним значениями строку Х путем протягивания. В строку Y вставим формулу =Sin(B2) и протянем до конца таблицы.
Затем выделим построенный диапазон и на панели стандартная нажмем кнопку Мастер диаграмм. Выберем тип диаграммы – график.
Задание 4. Составьте электронную таблицу для вывода графика квадратичной функции 
, считая a, b и с параметрами на интервале [-5;5] с шагом 0.2.
Задание 5. Составьте электронную таблицу для вывода графика
, считая a, b и с параметрами на интервале [n1;n2] с шагом h=(n2-n1)/30.
Дополнительные пояснения даны ниже.
Задание 6. Составьте электронную таблицу для вывода графика функции
Дополнительные пояснения даны ниже
Пояснения к лабораторной работе №7 Задание 5
Задание. Составьте электронную таблицу для вывода графика у=a* sin(bx+c), считая a, b и с параметрами на интервале [ n1;n2 ] с шагом h=(n2-n1)/30.
Выполнение:
1. Ввод параметров:
- В ячейки B4, B5 и B6 введем числовые значения параметров a, b и c, например, 1, 2 и 3.
- В ячейки B8 и B9 введем значения начала и конца интервала построения графика функции, например, -1 и 5.
- В ячейке B10 определим величину шага построения графика по заданной в условиях формуле =(B9-B8)/30. В результате выполнения формулы с вышеприведенными исходными данными в ячейке будет выведено число 0,2.
2. Построение таблицы значений независимой переменной x и зависимой переменной y:
В строке 12 будут размещены значения независимой переменной на интервале построения графика:
- В ячейке A12 запишем в качестве пояснения “ x “,
- В ячейке B12 сделаем ссылку на начало интервала построения графика, т.е. на ячейку B8,
- В ячейке B13 запишем пригодную для копирования (с целью построения графика) формулу для вычисления второй точки интервала, а именно =B12+$B$10 (где $B$10 – абсолютная, то есть не изменяющаяся при копировании ссылка на ячейку, в которой записан шаг, ее можно получить, выделив в строке формул B10 и нажав F4) и
- Скопируем эту формулу в ячейки диапазона D12:AF12 записав таким образом значения x в 30 точках, которые будут использоваться для построения графика.
Найдем соответствующие значения y. Для этого в ячейке A13 запишем в качестве пояснения y, а в ячейке B13 - формулу =$B$4*SIN($B$5*B12+$B$6). В данной формуле все ссылки ($B$4, $B$5 и $B$6) кроме ссылки на ячейку B12 (в которой размещено значение независимой переменной x) – также абсолютные, которые не меняются при копировании формулы в другие ячейки. Скопировав данную формулу в ячейки диапазона C13:AF13 получим искомую таблицу для вывода графика функции у=a* sin(bx+c) (где a, b и с – параметры) на интервале [ n1;n2 ] с шагом h=(n2-n1)/30.
3. Построение графика:
Выделив диапазон A12:AF13, вызвав Мастер построения диаграмм и выбрав Тип диаграммы Точечная со значениями, соединенными сглаживающими линиями, получим искомый график функции:
Задание 6
Задание:
Составьте электронную таблицу для вывода графика функции z=cos(x^2+y^2+1)/(x^2+y^2+1)^(1/2) на интервалах -2<=x<=2 и -2<=y<=2
Выполнение. Следует учесть, что независимых переменных в данном случае две (x и y), в отличие от ранее рассмотренных случаев графиков на плоскости. Соответсвенно, для построения графика надо рассмотреть все варианты изменения каждой из них в заданном интервале от -2 до 2, ( важно: переменные меняются независимо друг от друга, т.е. если шаг для каждой будет равен 0,2 то, соответственно, на заданных интервалах будет 20 значений x и 20 значений y, а для z будет необходимо найти 20×20 = 400 значений).
В превых двух строках листа запишем пояснение – формулировку задания.
В 4 -ой строке начиная со столбца B запишем значения независимой переменной x на интервале от -2 до 2, задав
- - начальное значение -2 (в ячейке В4),
- - второе значение, отстоящее от первого на величину шага 0,2 и -1,8 (в ячейке C4)
и затем выделим обе ячейки и с использованием маркера автозаполнения заполним соответствующими значениями x диапазон B4:V4.
В ячейке A5 запишем первое значение независимой переменной y равное -2, в ячейке A6 – второе значение -1,8, и затем заполним диапазон A5:A25 последовательными значениями y.
Особое внимание следует обратить на пригодный для копирования ввод формулы для вычисления независимой переменной z. Записать формулу для первого, соответствующего x=-2 и y=-2 значения z (в ячейке B5) следует так, чтобы при копировании в ячейки справа и снизу от B5 сохранялись бы ссылки на строку 4 (в которой размещены значения x) и на столбец A ( в котором размещены значения y).
Для этого в формуле для z (в ячейке B5), ссылающейся на ячейки B4 (первое значение x) и A5 ( первое значение y ) будем использовать смешанные ссылки ( то есть такие, у которых абсолютной, не меняющейся при копировании будет только ссылка на номер строки или только ссылка на номер столбца, наример B$4 или $A5, а вторая часть ссылки будет относиельной, то есть меняющейся при копировании ).
Спарвка (для лучшего понимания различий между типами ссылок можно сделать на отдельном листе).
Пусть
- в С3 введена формула =A1 (то есть относительная ссылка ),
- в D3 – формула =A$1, (то есть смешанная ссылка – относительная ссылка на столбец A и абсолютная – на строку 1 )
- в E3 - формула =$A1 (то есть смешанная ссылка – абсолютная ссылка на столбец A и относительная – на строку 1 )
- в F3 - формула =$A$1 (то есть абсолютная ссылка )
Тогда при копировании четырех ячеек диапазона C3:F3 в диапазон D4:G4 ( на одну строку ниже и на один столбец правее ) в ячейках будут размещены ссылки:
- в D4 - формула = B2 (то есть ссылка на ячейку также на одну строку ниже и на один столбец правее ),
- в E4 – формула =B$1, (то ссылка на ячейку, размещенную на один столбец правее но в той же, что и ранее строке 1 т.к ссылка на строку 1 абсолютная и не меняется при копировании)
- в F4 - формула =$A2 (то ссылка на ячейку, размещенную на одну строку ниже но в том же столбце A, поскольку ссылка на столбец A абсолютная и не меняется при копировании)
- в G3 - формула =$A$1 (то есть ссылка на ту же, что и ранее ячейку A1 поскольку ссылка абсолютная и не меняется при копировании)
Соответственно, записаная для z(-2,-2) в ячейке B5 формула будет иметь вид:
=COS(B$4^2+$A5^2+1)/(B$4^2+$A5^2+1)^(1/2)
Эту формулу следует скопировать в ячейку С6 и убедиться в том, что сохранились ссылки на 4 строку (х) и на столбец A ( y ).
После копирования данной формулы в диапазон B5:V25 следует выделить диапазон A4:V25 и вызвать Мастер построения диаграмм, выбрать Тип диаграммы Поверхность и задать заголовок диаграммы (например, Диаграмма поверхности), после чего будет построена такая диаграмма:
