Тема: Решение задачи табулирования функции. Построение и редактирование диаграммы

Вычислить значения функции у = для всех х наинтервале [-2,2] с шагом 0,2 при k= 10.

Решение должно быть получено в виде таблицы:

	A	B	C	D	E	F
	№	х	k	у1=х2-1	у2=х2+1	у=

Рис.1. Основные поля таблицы

Ход работы

Задание 1. Заполните таблицу.

1.1. Заполните шапку основной таблицы начиная с ячейки А1 в соответствии с рисунком 1. Установите ширину столбцов такой, чтобы надписи быливидны полностью.

1.2. Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22.

1.3. Заполните столбец В значениями х:

· в ячейку В2 занесите начальное значение х: -2.В ячейку ВЗ занесите =B2+0,2;

· заполните этой формулой ячейки В4:B22, используя функцию автозаполнения. Столбец заполнится значениями х от -2 до 2.

1.4. Заполните столбец С значениями коэффициента k (const)=10, используя функцию автозаполнения.

1.5. Заполните столбец В значениями функции у₁=х²-1. Начальное и конечное значения равны 3.

1.6. Аналогичным образом заполните столбец Е значениями функции у₂=х²+1. Проверьте! Все значения положительные, начальное и конечное значения равны 5.

1.7. Заполните столбец F2 значениями функции у= . Проверьте! Значения функции как положительные, так и отрицательные, начальное и конечное значения равны 6.

Задание 2.Оформите таблицу.

2.1. Вставьте пустую строку сверху для оформления заголовка.

3.2. Введите в ячейку А1 заголовок: «Табулирование функции». Объедините ячейки А1:F1 и разместите заголовок «Табулирование функции» по центру.

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

2.4. Задайте обрамление таблицы.

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

Рис. 2. Оформление таблицы

Задание 3. Подведение итогов.

3.1. Используя кнопку ∑ вкладки Главная, в конце таблицы вставьте сумму вычисленныхзначений у, среднее значение у, максимальное и минимальное значение у.

Задание 4. Форматы чисел в Excel.

4.1. Скопируйтезначения у из столбца F в столбцы H, I, J, K. Столбец F заполнен формулами, а скопировать требуется значения. Для этого:

· выделите все полученные значения у в столбце F;

· подведите курсор к границе выделенного блока (он должен принять форму разнонаправленного креста) и, удерживая правую клавишу мыши, переместите блок до столбца H;

· в открывшемся контекстно-зависимом меню выберите команду Копировать только значения.При этом столбец H заполнится теми же значениями, что и столбец F, но он не будет содер­жать формул. Аналогично заполните значениями столбца F столбцы I, J, K.

4.2. Перейдите на вкладку Главная, раздел Ячейки кнопка Формат. В открывшемся меню «Формат ячеек» выберите вкладку Число. В столбце H задайте формат числовой, в котором отражаются две значащие цифры после запятой 0,00; в столбце I задайте формат Экспоненциальный; в столбце J задайте формат Процентный, в столбце K установите любойформат по вашему выбору.

Задание 5. Постройте график функции. Для этого перейдите на вкладку Вставка, раздел Диаграммы и нажмите кнопку в строке заголовка раздела.

5.1. Укажите диапазон значений: три столбца (3 ряда значений игрека) и столбец со значениями икса. Области, содержащие значения игрека и значения икса - не смежные, поэтому для их выделения следует удерживать на­жатой клавишу Ctrl;

5.2. На вкладке Главная в разделе Диаграммы выберите тип диаграммы Точечная(с гладкими кривыми и маркерами) (рис. 3).

Рис. 3. График функции.

Задание 6. Редактирование диаграммы. Активизируйте диаграмму и на ленте инструментов образуется вкладка Работа с диаграммами. Добавьте название диаграммы «Совмещенные графики», название по оси X — «X», название по оси Y — «Y». Отформатируйте область диаграммы: измените рамку диаграммы, цвет и форму маркеров, толщину линий (выделяя каждый график отдельно), и пр.

Задание 7. Подготовьте таблицу и график к печати. Нажав главную кнопку программы, выберите Печать/Предварительный просмотр. Здесь вы должны увидеть и таблицу, и диаграмму. Выберите альбомное расположение. Добейтесь хорошего расположения таблицы и диаграммы на листе. Добавьте колонтитулы. Укажите в верхнем колонтитуле свою фамилию, а в нижнем - дату и время.

Задание 9. Самостоятельная работа. Постройте совмещенные графики по одному из вариантов, указанному преподавателем (см. таблицу ниже)

Варианты заданий для самостоятельной работы

задание	функция 1	функция 2	предел изменения	шаг
	Y= -2/x	Y=4/x	a) [0,5; 5] b) [1;10]	0,5
	Y=x-2	Y=x-3	a) [-3;-0,5] b) [-2;-0,75]	0,5 0,25
	Y=x2	Y=x3	a) [-3; 3] b) [-8; 6]	0,5
	Y=2x	Y=(1/2)x	a) [-2; 2] b) [-4; 4]	0,5

Лабораторная работа №7

Тема: Использование средства Подбор параметров

Средство Подбор параметров находит такое значение параметра, которое обеспечит требуемое значение, вычисленное по формуле, зависящей от этого параметра. Средство Подбор параметраприменяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающеежелаемый результат вычислений. Другими словами, это средство применяете в случае, если на рабочем листе имеется одна ячейка с числовым значением, а другая — с формулой, зависящей от значения в первой ячейке, и необходимо подобрать такое значение в первой ячейке, чтобы в ячейке с формулой получилось заданное вами значение. Например, в ячейке А1записана оптовая цена некоторого товара (595 руб.), а в ячейке А2записана формула =ОКРУГЛ(А1*(А1*8,8%);2),которая вычисляет розничную цену этого же товара, увеличивая его оптовую цену на 8,8%.Теперь необходимо узнать, какова оптовая цена другого товара, если его розничная цена равна 1099руб. Подборпараметра быстро определит, что в этом случае оптовая цена составляет 1010руб.

Чтобы применить средство Подбор параметра,на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Подбор параметра (рис. 1).

Рис. 1. Открытие средства Подбор параметра

Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Подбор параметра (рис 2).

Рис.2. Окно Подбор параметра

1. В поле ввода Установить в ячейке введите адрес ячейки, содержащей формулу, для результата вычислений которой вы хотите получить значение.

2. В поле ввода Значение введите число, которое должно получиться в ячейке, указанной в поле Установить в ячейке.

3. В поле ввода Изменяя значение ячейки введите адрес ячейки, содержащей числовое значение, которое вы хотите определить

Заполнив все три поля диалогового окна Подбор параметра, щёлкните на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено и покажет два числа: Подбираемое значение (то, которое вы указали) и Текущее значение (то, которое Excel смогла добиться от формулы). Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.

1. Вычисление корней алгебраических уравнений.

Алгебраическое выражение ax+ by+cz=d по значениям переменных a, b, c, x, y и z вычисляет значение переменной d. Имея значения любых шести переменных, с помощью средства Подбор параметра можно вычислить значение седьмой переменной.

Подготовьте рабочий лист, содержащий следующие данные (рис. 3):

	А	В	С	D
	Решение корней уравнения ax+ by+cz=d
		a		x
		b		y
		c		z
	=(А3*С3)+(А4*С4)+(А5*С5)	d

Рис. 3. Таблица данных для вычисления корней уравнения

1.1. Подбор параметров для вычисления переменной С.

Значения переменных:

а=1; b=2; d=12; x=1; y=2; z=1, найти значение переменной С.

Введите следующие значения в указанные ячейки: А3: 1, А4: 2, С3: 1, С4: 2, С5: 1.

1) Вызовите окно Подбор параметров.

2) В поле ввода Установить в ячейке введите имя ячейки, содержащей формулу (в данном примере А6), щёлкнув кнопкой по нужной ячейке.

3) В поле ввода Значение введите 12 (значение переменной d).

4) В поле ввода Изменяя значения в ячейке введите имя ячейки, содержащей значения переменной с (в данном примере А5). Нажмите ОК. При данном наборе переменных в результате вычисления уравнения получится 12, если переменная с=7.

1.2. Подбор параметров для вычисления переменной z.

Значения переменных:

а=2; b=4; с=3; d=65; x=5; y=7, найти значение переменной z.

Введите следующие значения в указанные ячейки: А3: 2, А4: 4, А5: 3, С3: 5, С4: 7.

1) Вызовите окно Подбор параметров.

2) В поле ввода Установить в ячейке введите имя ячейки, содержащей формулу (в данном примере А6), щёлкнув кнопкой по нужной ячейке.

3) В поле ввода Значение введите 65 (значение переменной d).

4) В поле ввода Изменяя значения в ячейке введите имя ячейки, содержащей значения переменной z (в данном примере С5). Нажмите ОК. При данном наборе переменных в результате вычисления уравнения получится 65, если переменная z=9.

1.3. Самостоятельно подберите параметры для вычисления переменной а при значениях переменных:

b=4; с=2; d=84; x=4; y=7; z =9, найти значение переменной а.

2. Использование средства Подбор параметров для выполнения банковских расчётов.

2.1. Кредит на покупку квартиры

Создайте таблицу, содержащую следующие данные (рис. 4):

	А	В
	Кредит на покупку квартиры
	Сумма кредита	2 000 000р.
	Срок кредита (месяцы)
	Процентная ставка	7,00%
	Ежемесячный платёж	=ПЛТ(Ставка;Кпер;Пс)

Рис. 4. Таблица данных для расчёта кредита

В ячейку В5 вводится финансовая функция ПЛТ, вычисляющая платёж. В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В4/12), аргумент Кпер — количество периодов погашения кредита (ячейка В3), аргумент Пс — сумма кредита (ячейка В2).

Вычисление суммы кредита.

Задача: кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова максимальная сумма кредита?

1) В ячейку В3 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В4 введите 5,75%.

2) В диалоговом окне Подбор параметра в поле ввода Установить в ячейке введите В5.

3) В поле ввода Значение введите число -11 000. В поле Значение вводится отрицательное число, что указывает на то, что ежемесячные платежи заемщик отдает, а не получает (как при банковских вкладах)

4) В поле ввода Изменяя значение ячейки введите В2 или щелкните на ячейке В2.

5) Щелкните на кнопке ОК, в открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: максимальная сумма кредита составит 1 324 647 руб.

2.3. Подбор параметра для вычисления срока погашения кредита

Задача: каков срок погашения кредита, если сумма кредита равна 2 250 ООО руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?

1. В ячейку В2 введите число 2 250 000, в ячейку В4 введите 7%.

2. Вызовите окно Подбора параметра.

3. В поле ввода Установить в ячейке введите В5 или щелкните на ячейке В5.

4. В поле ввода Значение введите число -14 230.

5. В поле ввода Изменяя значение ячейки введите В3 или щелкните на ячейке В3.

6. Щелкните на кнопке ОК. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: кредит берется на 439 месяцев (примерно 36,6 года).

2.4. Подбор параметра для вычисления процентной ставки

Задача: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. В ячейку В2 введите число 8 500 000, а в ячейку В3 число 360.

2. В поле ввода Установить в ячейке щёлкните по ячейке В5.

3. В поле ввода Значение введите число -52250.

4. В поле ввода Изменяя значение ячейки щёлкните по ячейке В3.

Ответ: можно согласиться на процентную ставку в размере 6,23%.

3. Расчеты по депозитам

Создайте таблицу, содержащую следующие данные: начальная сумма депозита – 250 000р.; срок депозита (месяцы) – 48; процентная ставка – 9,15%. Конечную сумму вычисляет функция БС(Ставка;Кпер;;-Пс), где аргумент Ставка задает ежемесячную процентную ставку депозита (поэтому в нашей формуле этот аргумент равен процентная ставка/12), аргумент Кпер — срок хранения депозита (48), аргумент Пс — начальная сумма депозита (250 000р.). Эта функция подобна функции ПЛТ из предыдущего примера, но вычисляет не ежемесячные платежи, а конечную накопленную сумму.

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

3.1. Подбор параметра для вычисления начальной суммы депозита

Задача: депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150 ООО руб.?

Ответ: если депозит открывается на 3 года с процентной ставкой 12,75%, то для получения конечной суммы 150 000 руб. начальная сумма должна составлять 102 530 руб.

3.2. Подбор параметра для вычисления срока депозита

Задача: на какой срок необходимо открыть депозит, чтобы при процентной ставке 10,7% годовых из начальной суммы 125 000 руб. получить конечную сумму 200 000 руб.?

Ответ: 53 месяца (примерно 4,4 года).

3.3. Подбор параметра для вычисления процентной ставки

Задача: депозит в размере 250 000 руб. открывается на 2 года с целью получить конечную сумму не менее 300 000 руб. Какова при таких условиях может быть минимальная процентная ставка?

Ответ: не менее 9,15% годовых.

4. Подбор параметра для оптимизации продажи театральных билетов

В этом подразделе рассматриваются задачи определения количества билетов одной из трех категорий таким образом, чтобы стоимость «кассы» составляла определенную сумму. Создайте рабочий лист, показанный на рисунке 5.

	А	В	С
		Цена билета	Кол-во билетов
	детские билеты	60р.
	билеты для взрослых	100р.
	льготные билеты	90р.
	стоимость всех билетов	=В2*С2+В3*С3+В4*С4

Рис. 5. Таблица данных для расчёта стоимости билетов

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

4.1. Вычисление количества билетов для детей

Задача: сколько необходимо продать детских билетов по цене 55 руб. для того, чтобы в кассе было 22 000 руб., если продано 150 «взрослых» билетов по цене 95 руб. и 100 льготных билетов по цене 75 руб.?

Ответ: при выполнении условий задачи необходимо продать 5 (4,54) детских билетов.

4.2. Вычисление количества билетов для взрослых

Задача: сколько необходимо продать билетов для взрослых по цене 90 руб. для того, чтобы в кассе было 21 500 руб., если продано 125 детских билетов по цене 60 руб. и 100 льготных билетов по цене 75 руб.?

Ответ: при выполнении условий задачи необходимо продать 72 билета для взрослых.

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

Задача: какова должна быть цена льготных билетов для того, что­бы в кассе было 20 700 руб., если продано 95 детских билетов по цене 50 руб. и 125 билетов для взрослых по цене 105 руб. и планируется продать 40 льготных билетов?

Ответ: при выполнении условий задачи необходимо установить цену льготных билетов 71 руб.

Ошибки при работе со средством «Подбор параметра»

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

Ячейка должна содержать формулу. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Установить в ячейке диалогового окна Подбор параметра, не содержит формулы. Чаще всего в этом поле введён адрес ячейки, который должен быть указан в поле ввода Изменяя значение ячейки. Закройте сообщение об ошибке, а затем введите в поле Установить в ячейке адрес ячейки, содержащей формулу.

Введено недопустимое значение. Это сообщение появляется тогда, когда в поле ввода Значение кроме числа введены еще какие-то символы, которые Excel не может распознать как числа. Закройте сообщение об ошибке, а затем введите в поле Значение правильное число (целое или десятичное). К числу можно добавить знак денежной единицы или процента

Ячейка должна содержать значение. Это сообщение появляется тогда, когда ячейка, адрес которой указан в поле ввода Изменяя значение ячейки, не содержит числового значения (а содержит, например, текст или формулу). Чтобы исправить эту ошибку, введите в поле Изменяя значение ячейки адрес ячейки, содержащей числовое значение.

Введенный текст не является правильной ссылкой или именем. Это сообщение появляется тогда, когда в поле ввода Установить в ячейке или в поле ввода Изменяя значение ячейки введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда адрес ячейки вводится вручную, а не указывается путем щелчка мыши на нужной ячейке. Чтобы исправить эту ошибку, введите в поле ввода правильный адрес ячейки.

Решение не найдено. Это сообщение появляется в диалоговом окне Результат подбора параметра (а не как сообщение об ошибке) тогда, когда Excel не может подобрать такое значение для изменяемого параметра, чтобы указанная формула возвратила заданное значение. Такое же сообщение появится, если в поле Значение введено экстремально маленькое или экстремально большое число. Чтобы исправить эту ошибку, сначала в диалоговом окне Результат подбора параметра щелкните на кнопке Отмена, данное окно закроется. Затем снова вызовите окно Подбор параметра и в поле Значение введите другое число.

Лабораторная работа №8

Таблицы подстановки

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