 |
Текст (значение; Формат). Примеры выполнения заданий. 5. Анализ и распределение данных. Подбор параметра. Примеры выполнения заданий
|
|
|
|
ТЕКСТ (значение; формат)
− значение – это числовое значение или ссылка на ячейку;
− формат – это числовой формат с вкладки Число диалогового окна Формат ячеек.
Далее следует выделить диапазон ячеек А7: F7 и с помощью маркера автозаполнения протащить его вниз до тех пор, пока в столбце F не появится сообщение о том, что корень уравнения найден.
Примеры выполнения заданий
Построить графики функции y = x2, используя внедренные диаграммы типа " График" и " Точечная диаграмма". Результаты сравнить.
Порядок выполнения задания:
● Составить таблицу значений функции:
| Значение аргумента | Значение функции |
| -6 | |
| -5 | |
| -4 | |
| -3 | |
| -2 | |
| -1 | |
● Выделить второй столбец таблицы.
● Вызвать Мастер диаграмм.
● В первом диалоговом окне выбрать тип диаграммы График, подтип График с маркерами, помечающими точки данных.
● Во втором диалоговом окне включить опцию Ряды в столбцах. На вкладке Ряд в качестве подписи меток по оси категорий задать значения первого столбца данных, выделив их в таблице.
● На третьем шаге работы Мастера диаграмм ввести название диаграммы " Квадратичная функция", ввести название оси категорий " Значение аргумента", ввести название оси значений " Значение функции".
● На четвертом шаге задать способ помещения диаграммы на листе, выбрав опцию имеющемся.
● Щелкнуть по кнопке Готово.
График будет иметь следующий вид:
● Вновь выделить второй столбец таблицы.
● Вызвать Мастер диаграмм.
● В первом диалоговом окне выбрать тип диаграммы Точечная диаграмма, подтип Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.
|
|
|
● Во втором диалоговом окне включить опцию Ряды в столбцах. На вкладке Ряд в качестве подписи меток по оси категорий задать значения первого столбца данных, выделив их в таблице.
● На третьем шаге работы Мастера диаграмм ввести название диаграммы " Функция x^2", ввести название оси категорий " Значение аргумента", ввести название оси значений " Значение функции".
● На четвертом шаге задать способ помещения диаграммы на листе, выбрав опцию имеющемся.
● Щелкнуть по кнопке Готово.
График будет иметь следующий вид:
5. АНАЛИЗ И РАСПРЕДЕЛЕНИЕ ДАННЫХ.
ПОДБОР ПАРАМЕТРА
В процессе обработки данных Вас могут заинтересовать вопросы: " Что будет, если размер взносов за кредит уменьшится на 50 000 тыс. рублей? " или " Что будет, если изменится процентная ставка? " или " Что будет, если платежный период сократится с трех лет до одного года? ". Выполнение подобного анализа вручную потребует больших затрат сил и времени. С помощью Microsoft Excel можно легко провести исследование и ответить на вопросы " Что будет, если".
Определить, насколько должна измениться исследуемая величина, чтобы результатом формулы, в которой она использована, было бы требуемое значение, можно с помощью команды Подбор параметра, а для просмотра списка возможных значений в зависимости от изменения одного или двух параметров используются таблицы подстановки данных.
Пусть на листе " Размеры платежей" рабочей книги Excel размещены следующие данные:
Подбор параметра
Составим формулу, вычисляющую размер платежей по кредиту фиксированного размера в зависимости от величины процентной ставки, а затем рассчитаем необходимый размер кредита в соответствии с максимально допустимым значением ежемесячных выплат.
|
|
|
Для вычисления выплат по кредиту необходимо выполнить следующие действия:
➢ На листе Размеры платежей с помощью команды Создать имена присвоить ячейкам D8: D10 имена, соответствующие заголовкам в ячейкам С8: С10.
➢ Выделить ячейку D13. В эту ячейку будет введена формула.
➢ Вызвать Мастер функций.
➢ В окне списка Категория выделить строку Финансовые.
➢ В окне списка Функция выделить ППЛАТ (ПЛТ). Это стандартная функция, вычисляющая платежи по кредитам. Щелкнуть по кнопке ОК. Откроется окно Палитры формул.
➢ Щелкнуть в поле Норма (Ставка), а затем на рабочем листе на ячейке с именем Проценты (ячейка D8). Для определения месячной процентной ставки следует поделить годовую на 12.
➢ В поле Норма после имени Проценты набрать /12.
➢ Щелкнуть в поле Кпер, а затем щелкнуть на ячейке с именем Срок_кредита (ячейка D9).
➢ Щелкнуть в поле Нз (ПС), ввести знак минус (-) и затем щелкнуть на рабочем листе на ячейке с именем Кредит (ячейка D10).
➢ Так как еще не сделано никаких выплат, величина Кредит имеет отрицательный знак. Окно диалога должно выглядеть следующим образом:
➢ Щелкнуть по кнопке ОК. Функция ППЛАТ введена в ячейку D13. Она помещает в эту ячейку величину ежемесячных выплат по заданному кредиту при процентной ставке 8, 5 % годовых.
➢ Применить к ячейке D13 формат ###0" тыс. руб. ". Рабочий лист должен выглядеть следующим образом:
Для определения максимально допустимого размера кредита по заданной величине выплат и при фиксированном проценте необходимо выполнить следующие действия:
➢ На листе Размеры платежей выделить ячейку D13 и выполнить команду Сервис – Подбор параметра. Откроется диалоговое окно Подбор параметра. В поле Установить в ячейке находится адрес D13. Ячейка D13 содержит формулу, вычисляющую выплаты по кредиту.
➢ Щелкнуть в поле Значение и ввести максимальную сумму, которую фирма готова выплачивать каждый месяц по кредиту, например, 900 при процентной ставке 8, 5 %.
➢ Нажать клавишу Tab или щелкнуть в поле Изменяя значение ячейки, а затем выделить ячейку D10.
➢ Щелкнуть по кнопке ОК.
Появится диалоговое окно Результат подбора параметра, сообщающее о том, что решение найдено. Искомое значение кредита при этом можно увидеть в ячейке D10, однако оно еще не введено в ячейку.
|
|
|
➢ Щелкнуть по кнопке ОК.
Новые значения теперь введены в ячейки. Пока диалоговое окно Результат подбора параметра не закрыто, ввод в ячейки рабочего листа новых значений можно отменить.
Замечание. Восстановить исходные значения данных после подбора параметра можно с помощью команды Отменить в меню Правка или кнопки Отменить стандартной панели инструментов.
Таблицы подстановки данных
Пусть, например, имеется несколько допустимых комбинаций данных, которые необходимо сравнить. Может потребоваться сравнить размеры выплат по кредиту для различных процентных ставок или для различных сроков кредита. Или вы захотите оценить влияние роста различных показателей торговли на текущий доход. Вместо того чтобы подбирать параметры и поочередно следить за изменением соответствующих величин, можно составить таблицу данных и сравнить сразу несколько результатов.
Существует два типа таблиц подстановки данных: таблицы подстановки с одной переменной и таблицы подстановки с двумя переменными. Первые таблицы позволяют исследовать влияние различных значений одной переменной на результат одной или нескольких формул. В таблицах с двумя переменными анализируется зависимость результата одной формулы от изменения двух входящих в нее переменных.
Таблица подстановки с одной переменной
Пусть требуется создать таблицу подстановки данных, использующую одну переменную, для определения размеров платежей по кредиту в зависимости от годовой процентной ставки, меняющейся от 6 % до 9 %. Для этого необходимо выполнить следующие действия на листе Размеры платежей:
➢ Выделить блок ячеек C13: D20.
➢ Выполнить команду Данные – Таблица подстановки. Откроется диалоговое окно Таблица подстановки.
➢ Щелкнуть в поле Подставлять значения по строкам в и выделить ячейку D8. Ячейка D8 содержит исходное значение размера процентной ставки.
|
|
|
Замечание. Используется поле Подставлять значения по строкам в, так как значения подстановки расположены в столбце и при обращении к каждому из них нужно переходить на одну строку ниже.
➢ Щелкнуть по кнопке ОК. В ячейках D14: D20 будут находиться результаты заданной формулы для различных аргументов.
Рабочий лист при этом должен выглядеть следующим образом:
Добавление формулы в таблицы подстановки данных
Добавим в таблицу новую формулу для вычисления процентов, выплаченных за весь срок кредита. Для этого необходимо выполнить следующие действия:
➢ В ячейку Е13 ввести формулу: =($D$13*Срок_кредита-Кредит).
➢ Эта формула рассчитывает полный размер процентов, которые предстоит выплачивать за период погашения кредита.
➢ Нажать клавишу Enter. В эту ячейку Excel поместит результат вычислений по введенной формуле для значения процентной ставки 8, 5 %.
➢ Выделить диапазон ячеек C13: E20 и выполнить команду Данные – Таблица подстановки.
➢ Щелкнуть в поле Подставлять значения по строкам в и выделить ячейку D8.
➢ Щелкнуть по кнопке ОК. Каждое значение из столбца С будет подставлено в формулу в качестве величины процентной ставки, а результаты формулы будут занесены в таблицу подстановки данных в столбец под заголовком Уплаченный процент.
Рабочий лист должен выглядеть следующим образом:
Таблица подстановки с двумя переменными
Пусть требуется проследить, как будет зависеть размер взносов от изменения сразу двух параметров: процентной ставки и срока погашения кредита. Для этого необходимо выполнить следующие действия:
➢ На листе Размеры платежей, начиная с ячейки D24, с помощью функции Автозаполнение ввести в строке последовательность сроков кредитования.
➢ Начиная с ячейки С25, ввести в столбце процентные ставки. Указанные действия показаны на следующем рисунке:
➢ Выделить ячейку D13 и выполнить команду Правка – Копировать.
➢ Правой кнопкой мыши щелкнуть на ячейке С24, выбрать в контекстном меню команду Специальная вставка и вставить в ячейку только формулу.
Замечание: Для того чтобы вычислить размер платежей по кредиту в зависимости от величины процентной ставки и сроков кредита, нужно составить таблицу, в которой значения меняющейся процентной ставки, занесенные в столбец С, подставляются в одну ячейку ввода – D8, а значения сроков кредита, расположенные в строке 24, подставляются в другую ячейку ввода – D9.
При создании таблицы подстановки данных с двумя переменными необходимо задать значения одной переменной в отдельном столбце, а значения другой переменной в отдельной строке. Формула должна быть одна, причем она должна быть введена в ячейку, находящуюся на пересечении строки и столбца значений подстановки. Мы поместили формулу в ячейку С24. Произведя вычисления с помощью команды Таблица подстановки, поместим результаты формулы в ячейки, расположенные справа от столбца С и под строкой 24.
|
|
|
➢ Выделить диапазон ячеек C24: I31.
➢ Выполнить команду Данные – Таблица подстановки.
➢ Щелкнуть в поле Подставлять значения по столбцам в и выделить ячейку D9.
Замечание. Так как данные подстановки, определяющие различные сроки кредита, располагаются в строке, то для перехода от одного из них к другому нужно двигаться по столбцам. Поэтому в данном случае заполняется поле Подставлять значения по столбцам в. Адресация к ячейке D9 объясняется тем, что именно эта ячейка носит имя Срок_кредита.
➢ Щелкнуть в поле Подставлять значения по строкам в и выделить ячейку D8.
|
|
|
