EXCEL – для заочного(самостоятельного ) обучения САВЕНКОВ А. И. ПОЛЯНИЧКО А. Л. АСТИСТОВА Т. И.

ПОСТРОЕНИЕ ГРАФИКА.

РАБОТА С МАСТЕРОМ ФУНКЦИИ И МАСТЕРОМ ДИАГРАММ

Здесь мы рассмотрим, как с помощью электронных таблиц выполняется построение графиков. Для построение графиков в Excelимеетсямастер диаграмм с большим набором графиков и диаграмм, позволяющим представить данные наглядно, в наиболее выгодном свете.

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

Как видно из условия задачи для построения графика функции необходимо сначала построить таблицу ее значений при различных значениях аргумента. Причем аргумент изменяется с фиксированным шагом, а затем построить ее график. В нашем случае шаг изменения аргумента ровен 0,2 и при m= 10.

Вид таблицы для получения решения:

№	Х	Y1= x^2-1	y2=sin(y1)	y3=x^3+1	y=m*(y2/y3)

Начнем с заполнения шапки таблицы с ячейки А1 (Шапка – это верхняя строка таблицы с названием столбцов.)

В ячейку А1 занесем № (название первого столбца таблицы); в ячейку В1 занесем – х; в ячейку С1 занесем y1=x^2-1 и т. д. Ширину столбцов установим такой, чтобы надписи были видны полностью

Используя функцию автозаполнения, заполним столбецАчислами от1до21, начиная с ячейкиА2и заканчивая ячейкойА22. (Число 21 – это количество шагов табулирования. Оно вычисляется по выражению (Хнач-Хкон)/Хшаг+1). Для этого в ячейкиА2иА3вводим первый и второй члены арифметической прогрессии (т.е. цифры1и2) и затем выделяем эти ячейки. После этого устанавливаем указатель мышки на маркере заполнения выделенного диапазонаУказатель мышки на маркере заполненияи протаскиваем его вниз до тех пор, пока не получится числовой ряд нужной длины.

В ячейку В2 занесем начальное значение аргумента функции -2, а в ячейку В3 занесем значение –2,2 т.е. начальное значение увеличенное на шаг и снова выделим эти ячейки. Установим указатель мышки на маркер заполнения выделенного диапазона и протащим его вниз до тех пор, пока не получим число 2, конечное значение аргумента. (т.е до значения 21 в столбце А)

Заполним столбец С значениями функции y1=x^2-1 для этого в ячейку С2 занесем =В2*В2-1 и заполним этой формулой ячейки В3 : В22. Столбец заполнится как положительными, так и отрицательными значениями функции y1. Начальное значение 3 и конечное значение 3

В ячейку D2 введем формулу =sin(y1). Ввод формул в ячейку можно производить с клавиатуры или с помощью диалогового окна Мастер функции (Function Wizard), вызываемого командой Вставка +Функция (Insert+Function) или нажатием соответствующей кнопки на панели инструментов. Мастер функций содержит список всех встроенных в Excel функций, а также справки по ним.

Выделим ячейку D2 и нажмем кнопку Вставка функции панели инструментов Стандартная. На экране появится диалоговое окно Мастер функций

Первое диалоговое окно МАСТЕР ФУНКЦИЙ. Это окно содержит два списка Категория и Функция – список имен функций, входящих в выбранную категорию

Функция SIN относится к категории Математические. Выделим ее и нажмем кнопку Далее. На экране появится новое диалоговое окно мастера функций

Второе диалоговое окно МАСТЕРА ФУНКЦИЙ В поле число вводим аргумент функции. В рассматриваемом примере это адрес ячейки С2 и нажмем кнопку Готово(ОК). Теперь выделим ячейку D2, установим указатель мышки на маркере заполнения этой ячейки и протащим его вниз до 22 строки.

Аналогичным образом заполняем столбцы E функцией y3=x^3+1 и F функцией y=m*(y2/y3). В ячейку Е2 введем формулу =С2^3+1, а в ячейку F2 введем формулу =10*(D2/E2), выделим каждую из ячеек и указателем мышки протащим вниз до строки 22

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

Таблица табулирования функции

Для построения графика функции выделим диапазон ячеек В2 : В22иF2 ^F22. Для того, чтобы одновременно выделить эти диапазоны, необходимо сначала выделить первый диапазон ячеек, а затем нажать клавишуCTRL, и выделить – второй. Эти столбцы таблицы содержат значения функции и ее аргумент. Теперь воспользуемся мастером диаграмм. Вызов мастера диаграмм производится либо с помощью командыВставка+Диаграмма,либо нажатием кнопкиМастер диаграмм панели инструментов Стандартная.

Выделенные, указателем мышки области построения диаграммы и окно МАСТЕРА ДИАГРАММ

Выбираем тип диаграммы на закладкеСтандартные, например Точечная, а на закладке Вид –выбираем Третье диалоговое окно МАСТЕРА ДИАГРАММ

Точечная диаграмма со значениями, соединенными сглаживающими линиями.После выбора типа диаграммы для предварительного просмотра можно нажав на кнопкуПросмотр результата. Затем нажать клавишуДальше. Второе окно Мастера –источник данных диаграмм отображает диапазон ячеек, по которому строить график

На третьем шаге мастера диаграмм выбираем и вводим параметры диаграммы

На четвертом шаге мастера диаграмм – размещение диаграммы необходимо установить переключатель либо в положение имеющемсят.е. на листе на котором расположена таблица либо на отдельном.

Наиболее часто используемые стандартные математические функции:

ABS(аргумент)- Абсолютная величина

ACOS(аргумент)- Арккосинус.

ASIN(аргумент) Арксинус

ATAN(аргумент) Арктангенс

COS(аргумент) Косинус

EXP(аргумент) Экспонента

LN() Натуральный логарифм

LOG10(аргумент) Десятичный логарифм

LOG(аргумент, основание)- Логарифм аргумента по данному основанию

SIN(аргумент) Синус

TAN(аргумент) Тангенс

ПИ() число ПИ

ПОСТРОЕНИЕ ДВУХ ГРАФИКОВ В ОДНОЙ СИСТЕМЕ КООРДИНАТ

Рассмотрим пример построения в одной системе координат графиков следующих двух функций:

В диапазон ячеек А2 : А12 вводим значения переменной х от –1 до 1 с шагом 0,2. В ячейкиA1,В1 и С1 вводимX Y Z, соответственно. В ячейки В2 и С2 введем формулы:

=(1,3*sin(A2)-0,3)^(1/3)

=cos(A2)-0,4

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

С помощью мышки на рабочем листе выделим прямоугольную область ячеек А2 : С12. Нажмем кнопку Мастер диаграммна панели инструментовСтандартная. В диалоговом окне Мастера (первый шаг) –тип диаграммы выбираем –точечная, авид – точечная диаграмма со значениями, соединенными сглаживающими линиями.Нажмем кнопкуДальше. На втором шаге Мастера, в окнеисточник данных диаграммы проверим, правильно ли выбран диапазон ячеек, по которым строится график. Затем переходим на закладкуРяди для каждого из графиков в окнеимя: введемy = (1,3*sin(X)-0,3)^(1/3) иz = cos(X)-0,4.

На третьем шаге Мастера – параметры диаграммы в закладкезаголовкив полеНазвание диаграммы вводим ГРАФИК ДВУХ ФУНКЦИЙ, в полеОсьХ(категории) вводим текст Х – АРГУМЕНТ, в полеОсьY(значений) введем ЗНАЧЕНИЯ X, Z.Переходим на закладку ОСИ и установим флажки для Ось Х и для ОсьY. На закладке ЛИНИИ СЕТКИ устанавливаем флажки -основные линии для оси Х иY. На закладке ЛЕГЕНДА переключатель установим в положениеСправа.

На четвертом шаге Мастера установим переключатель в положение Поместить диаграмму на листе: - имеющемся. Нажатие кнопкиГотово завершает построение график (смотри на следующей странице).

Как видно, графики функций y и zразличаются, для наглядности , по типу линий. Если вы захотите изменить, то с помощью контекстного меню вобласти построения диаграммвыбирается диалоговое окноФормат области построения,которое позволяет изменить тип, толщину и цвет линии, а также тип, цвет и фон области диаграммы.

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

№ п/п	Сообщение в ячейке	Расшифровка сообщений
1	#######	Размеры ячейки недостаточны для размещения результата.
2	#ДЕЛ/0!	Деление на ноль невозможно.
3	#ЗНАЧ!	Недопустимый тип аргумента.
4	#ИМЯ!	Неверное имя функции.
5	#Н/Д	Неопределенные данные – данные не подготовлены.
6	#ПУСТО!	Попытка задания пересечения двух областей, не имеющих общих ячеек.

Нахождение корней уравнения

Рассмотрим пример нахождения всех корней уравнения

X³ – 0,01X² – 0,7044X + 0,139104 = 0

ПРИМЕЧАНИЕ: у полинома третьей степени имеется не более трех вещественных корней.

Для нахождения корней их предварительно нужно локализировать. С этой целью строим график функции (т.е . протабулируем ее). Например, протабулируем наше уравнение на отрезке [ -3,3 ] c шагом 0,2. Результат табуляции рассмотрим в таблице и на графике. Заметим , что в ячейку В2 введена формула:

=a2^3-0,01*a2^2-0,7044*a2+0,139104

{0,2 0,4

{-1,0 -0,8}

{0,6 0,8}

РЕЗУЛЬТАТЫ ТАБУЛИРОВАНИЯ ПОЛИНОМА ТРЕТЬЕЙ СТЕПЕНИ И ЛОКАЛИЗАЦИЯ КОРНЕЙ

Из таблицы и графика видно, что полином меняет знак на интервалах - [ -1, -0,8], [0,2, 0,4] и[0,6, 0,8]. Это означает, что на каждом из отрезков имеется корень.

Уточним корни полинома методом пследовательных приближений при помощи команды Сервис-Подбор параметров. Но вначале зададим относительную погрешность и предельное число итераций, например 0,001 и 100, соответственно на вкладкеВычислимдиалогового окнаПараметры , открываемого командойСервис.

Вкладка вычисления диалогового окна параметры

В качестве начальных значений приближений к корням возьмем любые точки из отрезков локализации корней. Например, их средние значения, а именно –0,9; 0,3 и 0,7. Введем эти точки в ячейки С2, С3 и С4 Соответственно, а ячейку D2 введем формулу :

=С2^3-0,01*C2^2-0,7044*C2 +0,14

Выделим эту ячейку и с помощью маркера заполнения протащим вниз, заполнив диапазон D2:D4. В этих ячейках вычисляются значения полинома при заданных в ячейках С2:С4 аргументах.

Теперь выбираем команду Сервис-Подбор параметраи заполняем диалоговые окнаПодбора параметра следующим образом.

Диалоговое окно подбор параметра

В поле Установить в ячейку введем$D$2. Отметим, что в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения. В полеЗначение введем 0, в этом поле указывается правая часть уравнения. В полеИзменяя значение ячейки введем$C$2 – в этом поле дается ссылка на ячейку, отведенную под переменную. После нажатия кнопкиОК программа находит приближенное значение корня, которое помещает в ячейку С2 в нашем случае оно будет равно

- 0,9204. Аналогично в ячейках С3 и С4 можно определить два других корня. Они равны 0,2104 и 0,7206.

Диалоговое окно Результат подбора параметра в ячейке D3

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

Работа с базами данных в Excel

В процессах обработки информации с помощью компьютера особое место занимают БАЗЫ ДАННЫХ, которые позволяют заменить традиционные формы хранения картотек, каталогов на более современную и удобную форму.

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

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

Каждая запись располагается в отдельной строке, а для каждого поля отводится один столбец.

Основными задачами при работе с Базой Данных являются: Организация ввода данных; просмотр данных; сортировка и фильтрация данных в таблицах; подведение итогов. И так БАЗА ДАННЫХ в Excel ­ это таблица, только специальным образом организованная, а именно: Первая строка ­ заголовок полей.

Строка ­запись Базы Данных.

Столбец ­поле записи (в столбце находятся данные одного типа).

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

Весь инструмент работы с Базой Данных сосредоточен в одном пункте меню ­ДАННЫЕ (Data).

Необходимо помнить, что про вызове пунктов из меню Данные (Data) необходимо установить курсор в одной из ячеек таблицы Базы Данных. Выполним эту команду. На экране появится окно формы. В окне формы отображаются все поля одной записи базы данных. Название окна соответствует ярлычку листа Excel.

Окно формы

Слева ­ название полей, рядом с названиями расположеныполя текущей записив Базе Данных. Если поле вычисляемое, то оно недоступно для редактирования.

Вам необходимо дополнить таблицу созданную в первом задании контрольной работы 5 – 8 новыми записями. Для того чтобы перемещаться по полямзаписи необходимо воспользоваться клавишейTab ­ переход к следующему полю;Shift+Tab ­ вернуться к предыдущему полю либощелкнуть мышкой по полю.

Перемещение по записямосуществляетсяполосой прокрутки, клавишамистрелка вверх либострелка вниз.Переход на первую запись ­Ctrl+стрелка вверх, на последнюю запись ­Ctrl+стрелка вниз

Добавление/удаление записей

Кнопка Добавить(New) ­ ввод новой записи.Новая запись вносится в конец таблицы.

Кнопка Удалить(Delete) ­ удалить текущую запись.Удаленную запись невозможно восстановить.

Удаленную запись невозможно восстановить с помощью Undo !

Редактирование полей.

Нажатие клавиши Deleteпри редактировании поля удаляет символ справа от курсора

Если поле выделено, то нажатие клавиши Delete удаляет содержимое этого поля.

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

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

Рассмотрим порядок действий при работе с формой. Вспомним задание. В таблицу из первого задания необходимо добавить 5 – 8 новых записей. И так

1. Запустить Excel. На экран выводится окно Excel Книга 1 Лист 1.

2. Выполнить команду ФАЙЛ, ОТКРЫТЬ… На экран выводится диалоговое окно ОТКРЫТИЕ ДОКУМЕНТА. С помощью этого окна производится поиск файла, созданного при выполнении первого задания.

3. Установить курсор мышки на любую ячейку таблицы.

4. Выполнить команду из главного меню: Данные=Форма=Добавить (Data=Form=New). На экран, по верх таблицы, выводится форма.

5. Выполнить ввод, редактирование, просмотр новых данных.

6. После каждого ввода новой записи нажимать кнопку Добавить(New)

7. В конце ввода нажать клавишу Закрыть(Close)