Табулирование функций и построение графиков

Аннотация

Методические указания «электронные таблицы Microsoft Excel» предназначены для приобретения и закрепления навыков работы с приложением Microsoft Excel на занятиях по дисциплине «Информатика» студентами специальностей МТЗ, СТЗ, ГТЗ, ГРЗ, ГБЗ».

Методические указания содержат рекомендации для решения прикладных задач обработки данных и рассчитаны на 7-8 занятий по следующим темам:

1. итоговые вычисления;

2. использование функций;

3. табулирование функций и построение графиков;

4. решение нелинейных уравнений;

5. решение систем нелинейных уравнений;

6. решение систем линейных уравнений;

7. оптимизация.

Каждое занятие содержит 15 индивидуальных заданий. Для закрепления полученных навыков студентам предлагается выполнить индивидуальное задание, которое позволяет преподавателю дифференцированно оценить уровень подготовки каждого студента.

Занятие №1

Итоговые вычисления

Цель: приобрести навыки выполнения итоговых вычислений и оформления их в виде диаграмм.

Задание

Данные о результатах сбора урожая картофеля с трех полей сведены в таблицу

Поля	Площадь, га	Вес, ц	Урожайность, ц/га
Поле 1
Поле 2
Поле 3
Итого:

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

Методические указания

1. Сформируйте таблицу, как показано на рис.1.1.

Рис.1.1. Исходные данные для итоговых расчетов

2. Выделите в столбце B блок ячеек B2:B4 и на панели инструментов нажмите кнопку Автосумма. В ячейке B5 Microsoft Excel автоматически сформирует формулу для расчета суммы =СУММ(B2:B4) и занесет готовый результат расчета по этой формуле.

3. Проделайте аналогичные действия в столбце C.

4. Для расчета урожайности в ячейку D2 занесите формулу =C2/B2.

5. Выделите блок ячеек D2:D5 и выполните команду меню Правка\Заполнить\Вниз. В ячейках D3:D5 Microsoft Excel автоматически сформирует формулы для расчета урожайности и занесет готовые результаты расчета по этим формулам.

6. Убедитесь, что итоговая таблица выглядит так, как показано на рис.2.2 и сохраните книгу.

Рис.1.2. Итоговые результаты

7. На отдельном листе с помощью Мастера диаграмм представьте полученные результаты урожайности в виде обычной гистограммы, как показано на рис.2.3. Методика построения диаграммы описана ниже.

Рис.2.3. Диаграмма урожайности

В Microsoft Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называется группа ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.

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

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

Тип диаграммы.

На первом этапе работы Мастера диаграммвыбирают форму диаграммы. Доступные формы перечислены в списке Типна вкладке Стандартные. Для выбранного типа диаграммы справа указываются несколько вариантов представления данных (палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке Нестандартные отображается набор полностью сформированных типов диаграмм с готовым форматированием. После задания формы диаграммы следует щелкнуть на кнопке Далее.

Выбор данных.

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

Оформление диаграммы.

Третий этап работы Мастера диаграмм состоит в выборе оформления диаграммы. На вкладках окна Мастера диаграммзадаются следующие параметры:

· название диаграммы и подписи осей (вкладка Заголовки);

· отображение и маркировка осей координат (вкладка Оси);

· отображение сетки линий, параллельных осям координат (вкладка Линии сетки);

· описание построенных графиков (вкладка Легенда);

· отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);

· представление данных, использованных при построении графика, в виде таблицы (вкладкаТаблица данных).

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.

Размещение диаграммы.

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

Редактирование диаграммы.

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

При щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши – описывается всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента диаграммы можно через меню Формат (для выделенного элемента) или через контекстное меню, которое появляется при нажатии правой кнопки мыши (команда Формат). Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных.

Если требуется внести в диаграмму существенные изменения, следует вновь воспользоваться Мастером диаграмм. Для этого необходимо открыть рабочий лист с диаграммой или выбрать диаграмму, внедренную в рабочий лист с данными. Запустив Мастер диаграмм, можно изменить текущие параметры, которые рассматриваются в окнах Мастера, как заданные по умолчанию.

Вопросы для самоконтроля

1. Как выделить блок ячеек?

2. Как занести в ячейку формулу?

3. Как заполнить формулой блок ячеек?

4. Как при использовании Мастера диаграммвыбрать форму диаграммы?

5. В каких случаях при выборе данных используют вкладку Диапазон данных, а в каких – Ряд?

6. Как с помощью Мастера диаграмм производится оформление диаграммы?

7. Какие варианты размещения построенной диаграммы предлагает Мастер диаграмм и как ими воспользоваться?

8. Каким образом можно осуществить редактирование готовой диаграммы?

Занятие №2

Использование функций

Цель: приобрести навыки использования встроенных математических функций Microsoft Excel при обработке данных.

Задание

Записать формулу для вычисления выражения

.

Проверить правильность вычисления выражения при следующих значениях исходных данных: . Результат: .

Методические указания

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

Для уменьшения количества возможных ошибок при записи формулы ее лучше составить из нескольких выражений с помощью промежуточных вычислений. При записи формул удобно использовать режим проверки формул (Сервис\Зависимости формул\Режим проверки формул). Один из возможных вариантов такой записи в режиме проверки формул представлен на рис.3.1.

Рис.3.1. Панель интерфейса Microsoft Excel в режиме проверки формул.

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

Рис.3.2. Панель интерфейса Microsoft Excel с результатами вычислений

В некоторых случаях Microsoft Excelне может получить результат из-за наличия ошибок в выражениях. Ниже приведена таблица наиболее часто возникающих ошибок при вычислениях, возможные причины появления этих ошибок и рекомендации по их устранению.

Ошибка	Причина возникновения	Рекомендации
#ИМЯ?	В формуле неверно задано имя функции, или есть ссылка на отсутствующее имя области данных (возможно адреса введены не латинскими, а русскими буквами)	Проверить существует ли имя, правильно ли оно написано, нет ли пробелов между именем функции и скобками, правильно ли указан диапазон
#ЧИСЛО!	Используется недопусти­мый аргумент в выраже­ниях, например, попытка вычислить логарифм отри­цательного числа	Проверить может ли функция по­лучить результат при заданных численных значениях аргументов
#Н/Д	Неопределенные или отсутствующие данные («нет данных»)	Проверить наличие данных
#ДЕЛ/0!	Попытка деления на ноль	Проверить, нет ли в ссылках на ячейки пробелов или нулей
#ПУСТО!	В формуле задано пересе­чение двух интервалов, ко­торые на самом деле не имеют общих ячеек	Проверить правильность ввода ссылки на ячейку или диапазон
#ССЫЛКА!	Неверная (недопустимая или отсутствующая) ссылка на ячейку	Проверить наличие ячеек, на ко­торые ссылаются формулы
#ЗНАЧ!	Недопустимый тип аргу­мента, например, исполь­зуется текстовый аргумент вместо числового	Проверить правильность исполь­зования аргумента соответст­вующего типа

Вопросы для самоконтроля

1. Что такое встроенные функции?

2. Для чего используется панель формул?

3. Что необходимо указать при вызове встроенной функции?

4. Как вызвать панель формул?

5. Как пользоваться панелью формул?

6. Для чего используется Мастер Функций?

7. Как вызвать Мастер функций?

8. Каким образом производится ввод параметров функции в панели формул?

Занятие №3

Табулирование функций и построение графиков

Цель: приобрести навыки вычисления таблицы значений функции и построения графиков.

Задание

Вычислить таблицу функции Y(x) для различных значений аргумента x от x_нач до x_кон c шагом h=(x_кон – x_нач)/10. Функция Y(x) вычисляется по формуле . Построить графики функции Y(x).

Методические указания

По умолчанию адреса в формулах рассматриваются как относительные. Это означает, что при копировании формулы адреса автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии. Например, пусть в ячейке B2имеется формула с адресом A3. Ячейка A3 относительно ячейки B2 находится на один столбец левее и на одну строку ниже. При копировании формулы в любую ячейку такое относительное расположение адресов сохранится.Например, при копировании формулы в ячейку C4 адрес в формуле автоматически изменится, и будет указывать на ячейку, которая находится на один столбец левее и на одну строку ниже C4, т.е. B5.

Чтобы запретить автоматическое изменение адреса при копировании, используется абсолютный адрес. Перед строкой или столбцом, изменение которых необходимо запретить, ставится символ $, например:

Адрес	Способ адресации
A1	относительный
$A1	абсолютный по столбцу, относительный по строке
A$1	относительный по столбцу, абсолютный по строке
$A$1	абсолютный

Для изменения способа адресации при редактировании формулы символ $ можно вводить непосредственно с клавиатуры, а можно выделить адрес в формуле и нажать функциональную клавишу F4. При последовательных нажатиях клавиши F4 адрес, например, A1, будет модифицироваться как $A$1, A$1, $A1,A1.

1. Сформируйте таблицу, как показано на рис.4.1. Здесь для вычисления текущего значения аргумента x используется выражение x=x+h. Чтобы запретить при копировании изменение адреса ячейки C2, где находится значение шага h, используется абсолютный адрес $C$2.

Рис.4.1. Панель интерфейса Microsoft Excel в режиме проверки формул.

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

Рис.4.2. Результаты табулирования функции Y(x) и построения графика

Для построения графика функции Y(x) выделите блок ячеек A4:B14 и вызовите Мастер диаграмм. На вкладке Стандартныевыберите тип диаграммы – Точечная и вид – Точечная диаграмма со значениями, соединенными сглаживающими линиями(средняя в первом столбце). Руководствуясь указаниями Мастера диаграмм, постройте график и расположите диаграмму на одном листе Y вместе с результатами табуляции, как показано на рис.4.2.

Вопросы для самоконтроля

1. Как отредактировать название диаграммы?

2. Как изменить названия осей Х и Y?

3. Как изменить легенду?

4. Как изменить тип диаграммы?

5. Как отредактировать тип, цвет и толщину линий?

Занятие №4