Материал: MS EXCEL в расчетных задачах(учебник,по которому выполнены работы )

42

•

Рис. 4.12. Таблица и график функции, зависящей от параметра

Замечание. Меняя значения параметров, можно получить совершенно другое поведение функции. Рекомендуется проанализировать поведение функции при a>0 и a<0, а также рассмотреть уменьшение и увеличение b.

Пример 5. (Функция, заданная параметрическими уравнениями). Вычислить таблицу значений функции, заданной параметрическими уравнениями и построить ее график. В качестве примера рассмотрим построение окружности.

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

Построение таблицы значений функции

•Перейдем на новый рабочий лист.

•Зададим заголовки столбцов t, x, y.

•Заполним первый столбец значениями t, применив еще один способ задания аргумента: каждое последующее значение вычислим через предыдущее, добавляя шаг. В ячейке D2 вычислим h =π /16 по формуле =ПИ()/16. В ячейку A2 введем 0, в ячейку A3 введем формулу =A2+$D$2, которую копируем вниз до значения 2π.

•Введем в ячейку B2 формулу =COS(A2); в ячейку C2 формулу

=SIN(A2)

•Выделим ячейки B2, C2 и копируем их для всех значений t с помощью заполнения.

•Форматируем таблицу по образцу.

43

Построение графика функции

•Выделим диапазон B1:C22

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

•Затем отредактируем диаграмму: по команде Формат оси зададим точность – один знак после запятой, по команде Формат области построения укажем рамку Невидимая.

•Выполним растяжение-сжатие диаграммы, так чтобы получилась окружность, а не эллипс.

•Результат построения показан на рис. 4.13.

•

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

4.4. ВАРИАНТЫ ЗАДАНИЙ

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

44

5. РЕШЕНИЕ ЗАДАЧ С ИСПОЛЬЗОВАНИЕМ ФУНКЦИЙ ВПР, СУММЕСЛИ, СЧЕТЕСЛИ

5.1. ФУНКЦИЯ ВПР

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

Простейший вариант обращения к этой функции может быть таким:

ВПР(А; Т; N)

где:

A - это значение (искомое_значение), которое должно быть найдено в первом столбце массива; искомое_значение может быть значением, ссылкой или текстовой строкой.

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

N - это номер столбца в массиве табл_масс , в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента табл_масс; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента табл_масс и т.д. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше,

Если ВПР не может найти искомое значение А, то используется наибольшее значение, которое меньше, чем А.

Если А меньше, чем наименьшее значение в первом столбце аргумента табл_масс, то функция ВПР возвращает значение ошибки #Н/Д.

Пример 1.

Функцию ВПР удобно использовать при организации таблицсправочников. Обычно при их использовании один из столбцов является входом в таблицу-справочник. Зная значение из этого столбца, получаем интересующие нас значения - выходные данные.

Рассмотрим фрагмент рабочего листа (рис.5.1).

Здесь входом в справочник является наименование элемента, выходными данными - Номер элемента в "Периодической системе“ и Атомный вес. Таблица-справочник должна быть организована таким образом, чтобы вход в таблицу был первым столбцом.

Рис.5.1.

Значение функции:

=ВПР("Cu"; B3:D7;2) в ячейке D9 равняется 29, так как здесь Cu - искомое_значение; B3:D7- табл_масс (информативная часть справочника без заголовков); 2 - номер_столбца (номер столбца, откуда берем результат);

46

=ВПР("Cu"; B3:D7;3) в ячейке D10 равняется 63,5 , поскольку результат ищем в третьем столбце (номер_столбца равен 3);

=ВПР("Cr"; B3:D7;3) в ячейке D11 равен 197,2, так как значение “Cu”,

которое есть в таблице превышает значение "Cr” и используются данные относящиеся к предыдущей строке “Au”;

=ВПР("Ac"; B3:D7;2) равняется #Н/Д, поскольку "Ac" меньше, чем наименьшее значение в первом столбце;

=ВПР("Mg"; B3:D7;4) равняется #ССЫЛКА! так как в табл_масс всего 3 столбца.

Пример 2.

Ставка прогрессивного подоходного налога зависит от величины заработанной платы, эта зависимость приведена в таблице 5.1:

Таблица 5.1

Принципиальным отличием от предыдущего примера применения функции ВПР является проверка не на точное совпадение искомого значения, а попадание его в интервал. На рис 5.2. приводится возможный вариант решения поставленной задачи.

Рис.5.2

В ячейке С11 содержится формула =ВПР(B11;$A$4:$C$7;3), которая по значению в ячейке В11 (значение =0) отыскивает подходящее значение в первом столбце интервала $A$4:$C$7 и в соответствующей строке выбирает значение из третьего столбца справочника. Заметим, что второй столбец добавлен в качестве пояснения и в дальнейших вычислениях участия не принимает.