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

Для создания диаграммы в Excel необходима таблица значений. Рассмотрим построение декартовых графиков функциональной зависимости y=f(x). Пусть имеется экспериментальные данные измерения пройденного пути в зависимости от времени. Заполнив ими два соседних столбца (или строки) на рабочем листе Excel, выделим их вместе с названиями, вызовем «Мастер диаграмм» (пункт меню «Вставка»à «Диаграмма»), и выберем тип диаграммы «Точечная».

 

 

Тип графика «Точечный»

 

Тип «Точечная» берёт значения из одного столбца (самого левого при таком способе построения) и откладывает их по оси «х», а соответствующие им из других столбцов по оси «y». Частой ошибкой бывает выбор для подобного типа диаграмм пункта «График». Посмотрим, что произойдёт при таком выборе.

 

Тип диаграммы «График»

 

 

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

 

Возможно построение нескольких графиков в одном окне диаграммы. Пусть надо добавить новый ряд данных по температуре. Щелкнув правой кнопкой мыши в окне диаграммы, выбираем в контекстном меню пункт «Исходные данные»,

 

 

 

В диалоговом окне исходных данных на вкладке «Ряд», нажимаем кнопку «Добавить» и указываем в соответствующих полях диапазоны значений.

 

 

Так как данные в рядах «Путь» и «Температура» различаются на два порядка, вид графика для данных «Путь» мало показателен. В Excel существует возможность построить один из графиков во второй, вспомогательной оси. Для создания вспомогательной оси, надо щёлкнуть правой кнопкой по линии графика и, выделив её таким образом, в появившемся контекстном меню выбрать пункт «Формат рядов данных» и установить переключатель «по вспомогательной оси».

 

 

 

При построении графика функции заданной в аналитическом виде, например

y=a*sin(x-b), где x изменяется от -3 до 2 с шагом dx=0,25 a=2,4 b=0,3

таблицу значений можно построить следующим образом: в ячейку, например «D4», ввести начальное значение х то есть -3 , в ячейку «D5» ввести следующее значение ( для шага изменения по х равного 0,25 это будет -2,75 ), затем, выделив эти две ячейки, протянуть за правый нижний уголок , заполнив таким образом столбец значений по «x». Для значений «y» в ячейку «E4» вводим формулу «=$B$4*SIN(D4-$B$5)», где ««$B$4» и «$B$5» ссылки на ячейки со значениями «a» и «b» соответственно, эти адреса должны быть абсолютными, а «D4» - ссылка на ячейку со значениями «х», этот адрес должен быть относительным. Копируя методом протягивания ячейку «E4» вниз на нужное количество строк, получаем столбец значений «y». Затем строим график, выбирая тип «Точечный», и форматируем, задавая заголовки, линии сетки и т.п.

При построении и исследовании удобно применять имена диапазонов. Создадим таблицу входных значений для построения графика, где n – число точек. Выделив диапазон А4:В9 через пункты меню «Вставка»à «Имя» à «Создать» дадим имена соответствующим ячейкам. В ячейку для dx введем формулу «=(xk-xn)/n». В ячейке D4 напишем «=xn» а в ячейку ниже (D5) – «=D4+dx» и скопируем ячейку D5 методом протаскивания до ячейки с адресом D24, заполнив, таким образом, 20 ячеек значениями x.

Выделив диапазон D4:D24, присвоим ему имя x. Теперь, при написании формул можно использовать не адреса ячеек, а имена. Это дает более наглядное написание формул.

Напишем в ячейке E4 формулу «=a*sin(x-b)» и, скопировав ее методом протаскивания до ячейки E24, заполним значениями функции.

 

 

 

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

 

 

Для построения графика функции, имеющего разный функциональный вид для разных значений аргумента (это задача «Развилка») используется логическая функция «=ЕСЛИ(условие; значение истинно; значение ложь)». Для графика функции заданной условием Y=a*sin(x) если x<-1, Y=b*√│x│ если x>=-1 формула принимаем следующий вид

=ЕСЛИ(D4<-1;$B$4*SIN(D4);$B$5*КОРЕНЬ(ABS(D4)))

или при применении имен «=ЕСЛИ(X<-1;a*SIN(X);b*КОРЕНЬ(ABS(X)))»

Можно создать, а затем применить пользовательскую функцию, написанную в VBA, код которой для последней рассматриваемой задачи будет иметь вид:

Public Function Развилка(a, b, x) As Double

Dim y As Double

If x < -1 Then

y = a * Sin(x)

Else

y = b * Sqr(Abs(x))

End If

Развилка = y

End Function

 

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

 

 

При построении графика функции, имеющей три условия, применяется вложенная развилка. Например, при условии:

Y=a*sin(x) если x<-1

Y=b*√│x│ если -1<= x <0,5

Y= x если x>=0,5

формула при применении адресов следующая:

=ЕСЛИ(D4<-1;$B$4*SIN(D4);ЕСЛИ(И(D4>-1;D4<=0,5);$B$5*КОРЕНЬ(ABS(D4));D4))

Здесь для условия -1<=x<0,5 применена логическая функция

И(логическое_значение1; логическое_значение2; ...)

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