Как построить график с условием в excel

Строим график функции, заданный системой уравнений, в EXCEL

history 8 января 2018 г.

Контрольные работы и задания
Диаграммы и графики

Построим в MS EXCEL график функции, заданный системой уравнений. Эта задача часто встречается в лабораторных работах и почему-то является "камнем преткновения" для многих учащихся.

Пусть дана система уравнений

Требуется на отрезке [-1; 4] построить график функции f(x). Параметры a = 5 и b = 2 необходимо задать в отдельных ячейках.

Решение (1 ряд данных)

Чтобы построить график функции в MS EXCEL можно использовать диаграмму типа График или Точечная.

СОВЕТ : О построении диаграмм см. статью Основы построения диаграмм в MS EXCEL . О различии диаграмм Точечная и График см. статью График vs Точечная диаграмма в MS EXCEL .

Создадим таблицу с исходными данными для x от -1 до 4, включая граничные значения (см. файл примера, лист Ряд1 ):

Шаг по х выберем равным 0,2, чтобы график содержал более 20 точек.

Чтобы построить диаграмму типа Точечная:

выделите любую ячейку таблицы;
во вкладке Вставка в группе Диаграммы выберите диаграмму Точечная с прямыми отрезками и маркерами .

Чтобы построить диаграмму типа График:

выделите любую столбец f(x) вместе с заголовком;
во вкладке Вставка в группе Диаграммы выберите диаграмму График маркерами .

У обеих диаграмм один общий недостаток — обе части графика соединены линией (в диапазоне х от 1 до 1,2). Из этого можно сделать ошибочный вывод, что, например, для х=1,1 значение функции равно около -15. Это, конечно же, не так. Кроме того, обе части графика одного цвета, что не удобно. Поэтому, построим график используя 2 ряда данных .

Решение (2 ряда данных)

Создадим другую таблицу с исходными данными в файле примера, лист График :

Второй и третий столбец таблицы будут использоваться для построения 2-х рядов данных. Первый столбец — для подписей по оси х. Для значений x>1 будет построен второй график (в степени 3/2), для остальных — парабола. Значения #Н/Д (нет данных) использованы для удобства — в качестве исходных данных для ряда можно брать значения из целого столбца. В противном случае пришлось бы указывать диапазоны соответствующих ячеек при построении диаграммы. При изменении шага по х — это вызвало бы необходимость перестроения диаграммы.

У такой диаграммы имеется недостаток — в диапазоне х от 1 до 1,2 на диаграмме теперь нет вообще значений. Чтобы избежать этого недостатка — построим диаграмму типа Точечная с 3-мя рядами данных.

Решение (3 ряда данных)

Для построения графика используем 2 таблицы с данными для каждого уравнения, см. файл примера, лист График .

Первое значение второго графика возьмем чуть больше 1, например, 1,00001, чтобы как можно ближе приблизиться к значению, в котором происходит разрыв двух графиков. Также для точки со значением х=1 построим на диаграмме одну точку (ряд №3), чтобы показать, что для этого х значение второго уравнения не вычисляется (хотя фактически вычисляется).

График функции с тремя условиями

Рассмотрим пример построения графика функции y при x [0; 1]:

График строится, как описано ранее в этой главе, за исключением того, что в ячейку B1 вводится формула

=ЕСЛИ(A2 =0,2;A1 Ячейки. На вкладке Выравнивание появившегося диалогового окна Формат ячеек в группе Выравнивание в списке по горизонтали укажите значение поправомукраю. Нажмите кнопку ОК. Заголовки столбцов окажутся выровненными по правому краю.

3. В диапазон ячеек A2:A17 введите значения аргумента x от -3 до 0 с шагом 0.2.

4. В ячейки B2 и C2 введите формулы

5. Выделите диапазон B2:C2, расположите указатель мыши на маркере заполнения этого диапазона и протяните его вниз так, чтобы заполнить диапазон BЗ:C17.
6. Выберите команду Вставка > Диаграмма
7. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в списке Тип выберите значение График, а в списке Вид укажите стандартный график. Нажмите кнопку Далее.
8. В появившемся окне Исходныеданные на вкладке диапазон данных вы берите переключатель Рядывстолбцах, т. к. данные располагаются в столбцах. В поле ввода диапазон приведите ссылку на диапазон данных В2:С17, значения из которого откладываются вдоль оси ординат.
9. На вкладке Ряд диалогового окна Исходныеданные в поле ввода ПодписиосиХ укажите ссылку на диапазон А2:А17, значения из которого откладываются по оси абсцисс (рис. 2.14).

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

10. Выберите в списке Ряд элемент Ряд1. В поле ввода Имя укажите ссылку на ячейку B1, значение из которой будет использоваться в качестве идентификатора данного ряда. Это приведет к тому, что в поле Имя автоматически будет введена ссылка на ячейку в абсолютном формате. В данном случае, =Лист1!$B$1. Теперь осталось только щелкнуть на элементе Ряд1 списка Ряд. Это приведет к тому, что элемент Ряд1 поменяется на y, т. е. на то значение, которое содержится в ячейке B1. Аналогично поступите с элементом Ряд2 списка Ряд. Сначала выберите его, затем в поле ввода Имя укажите ссылку на ячейку C1, а потом щелкните на элементе Ряд2. На рис.2.15 показана вкладка Ряд диалогового окна Исходные данные после задания имен рядов. Теперь можно нажать кнопку Далее.

11. В появившемся диалоговом окне Мастердиаграмм(шаг З из 4):Параметрыдиаграммы на вкладке Заголовки в поле Названиедиаграммы введите график двух функций, в поле Ось Х (категорий) введитеx, в поле ОсьY(значений) введите y и z . На вкладке Легенда установите флажок Добавитьлегенду. Нажмите кнопку Далее.
12. В появившемся диалоговом окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы выберите переключатель Поместитьдиаграммуналисте имеющемся. Диаграмма будет внедрена в рабочий лист, имя которого указывается в соответствующем списке. Если выбрать переключатель Поместитьдиаграммуна листе отдельном, то диаграмма будет помещена на листе диаграмм. Нажмите кнопку Готово.

Для большей презентабельности построенной диаграммы в ней были произведены следующие изменения по сравнению с оригиналом.

o Изменена ориентация подписи оси ординат с вертикальной на горизонтальную. Для этого выберите подпись оси ординат. Нажмите правую кнопку мыши и в появившемся контекстном меню укажите команду Формат названия оси. На вкладке Выравнивание диалогового окна Формат названия оси в группе Ориентация установите горизонтальную ориентацию. Нажмите кнопку ОК.
o Для того чтобы пользователю было легче отличить, какая линия является графиком функции у, а какая z — , изменен вид графика функции. С этой целью выделите график функции. Нажмите правую кнопку мыши и в появившемся контекстном меню выберите команду Формат рядов данных. На вкладке Вид диалогового окна Формат ряда данных, используя элементы управления групп, Маркер и Линия, установите необходимый вид линии графика. Нажмите кнопку ОК.
o Изменен фон графика. С этой целью выделите диаграмму (но не область построения). Нажмите правую кнопку мыши и в появившемся контекстном меню выберите команду Формат области диаграммы. На вкладке Вид диалогового окна Форматобластидиаграммы установите флажок скругленныеуглы, а, используя, элементы управления группы Заливка, установите цвет и вид заливки фона. Нажмите кнопку ОК.