- •Лабораторная работа №1
- •1. Общие приёмы работы
- •5. Объединение/разбиение ячеек.
- •8.2. Выделение крайних значений
- •8.3. Форматирование с использованием гистограммы
- •8.4. Форматирование с использованием трехцветной шкалы
- •8.5. Форматирование с использованием набора значков
- •8.6. Управление правилами условного форматирования
- •8.7. Поиск ячеек с условным форматированием
- •Лабораторная работа №2
- •1.Ввод формул.
- •2. Ввод формул, содержащих ссылки на ячейки.
- •Лабораторная работа №3.
- •Лабораторная работа №4
- •Изменение типа диаграммы
- •Изменение источника данных
- •Добавление и удаление элементов диаграммы
- •Изменение положения диаграммы и ее элементов Изменение положения всей диаграммы
- •Изменение положения элементов диаграммы
- •Изменение размеров диаграммы и ее элементов Изменение размеров всей диаграммы
- •Выбор стиля оформления элемента
- •Заливка элементов диаграммы
- •Аналогично можно изменить контур (линии) элементов диаграммы, добавить эффекты оформления элементов диаграммы Особенности оформления осей
- •Оформление шрифта элемента
- •Восстановление параметров оформления
- •Лабораторная работа №5. Тема: Функции условия
- •3.2. Начисление налогов с помощью условий
- •Практическая работа № 6
- •Лабораторная работа №7
- •2.2. Вычисление суммы кредита.
- •2.3. Подбор параметра для вычисления срока погашения кредита
- •2.4. Подбор параметра для вычисления процентной ставки
- •3. Расчеты по депозитам
- •4.1. Вычисление количества билетов для детей
- •4.2. Вычисление количества билетов для взрослых
- •Лабораторная работа №8
- •2. Создание таблицы подстановки с двумя входами.
- •3. Удаление и преобразование таблиц подстановки
- •4 . Использование таблиц подстановки для подсчета дивидендов
- •5. Возможные ошибки при работе с Таблицами подстановки.
- •Лабораторная работа №9
- •1. Задача вычисления объёма
- •2. Задача вычисления расстояния
- •3. Расчёты по банковским кредитам
- •4. Структура производства.
- •Лабораторная работа №10
- •1. Создание и работа со сценариями
- •2. Самостоятельная работа. Использование сценариев для прогноза объемов продаж
- •3. Защита сценариев от изменений
- •4. Ошибки при работе со сценариями
- •Лабораторная работа №11
- •Лабораторная работа №12
- •1.1. Автоматически обновляемая текущая дата
- •1.4. День недели произвольной даты
- •4.1. Преобразование регистра текста. Для преобразования регистра текста используются три функции: прописн, пропнач, строч.
- •4.2. Объединение текста
- •4.4. Расчет количества знаков
- •4. Назначение макроса графическому объекту или элементу управления.
- •5. Запуск макроса.
- •7. Удаление макроса.
- •Лабораторная работа №14
Лабораторная работа №7
Тема: Использование средства Подбор параметров
Средство Подбор параметров находит такое значение параметра, которое обеспечит требуемое значение, вычисленное по формуле, зависящей от этого параметра. Средство Подбор параметра применяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающее желаемый результат вычислений. Другими словами, это средство применяете в случае, если на рабочем листе имеется одна ячейка с числовым значением, а другая — с формулой, зависящей от значения в первой ячейке, и необходимо подобрать такое значение в первой ячейке, чтобы в ячейке с формулой получилось заданное вами значение. Например, в ячейке А1 записана оптовая цена некоторого товара (595 руб.), а в ячейке А2 записана формула =ОКРУГЛ(А1*(А1*8,8%);2), которая вычисляет розничную цену этого же товара, увеличивая его оптовую цену на 8,8%. Теперь необходимо узнать, какова оптовая цена другого товара, если его розничная цена равна 1099 руб. Подбор параметра быстро определит, что в этом случае оптовая цена составляет 1010 руб.
Чтобы применить средство Подбор параметра, на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Подбор параметра (рис. 1).
Рис. 1. Открытие средства Подбор параметра
Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Подбор параметра (рис 2).
Рис.2. Окно Подбор параметра
В поле ввода Установить в ячейке введите адрес ячейки, содержащей формулу, для результата вычислений которой вы хотите получить значение.
В поле ввода Значение введите число, которое должно получиться в ячейке, указанной в поле Установить в ячейке.
В поле ввода Изменяя значение ячейки введите адрес ячейки, содержащей числовое значение, которое вы хотите определить
Заполнив все три поля диалогового окна Подбор параметра, щёлкните на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено и покажет два числа: Подбираемое значение (то, которое вы указали) и Текущее значение (то, которое Excel смогла добиться от формулы). Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.
1. Вычисление корней алгебраических уравнений.
Алгебраическое выражение ax+ by+cz=d по значениям переменных a, b, c, x, y и z вычисляет значение переменной d. Имея значения любых шести переменных, с помощью средства Подбор параметра можно вычислить значение седьмой переменной.
Подготовьте рабочий лист, содержащий следующие данные (рис. 3):
-
А
В
С
D
1
Решение корней уравнения ax+ by+cz=d
2
3
3
a
4
x
4
3
b
3
y
5
2
c
5
z
6
=(А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):
-
А
В
1
Кредит на покупку квартиры
2
Сумма кредита
2 000 000р.
3
Срок кредита (месяцы)
480
4
Процентная ставка
7,00%
5
Ежемесячный платёж
=ПЛТ(Ставка;Кпер;Пс)
Рис. 4. Таблица данных для расчёта кредита
В ячейку В5 вводится финансовая функция ПЛТ, вычисляющая платёж. В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В4/12), аргумент Кпер — количество периодов погашения кредита (ячейка В3), аргумент Пс — сумма кредита (ячейка В2).