Як користатися розрахунковим блоком
1. Введіть в комірку D3 табельний номер водія.
2. Виберіть у таблиці-відомості номер картки, дані за якою необхідно проаналізувати і скопіюйте його в комірку D4.
3. Виберіть у таблиці-відомості номер автомобіля, дані за який проаналізуйте і скопіюйте його в комірку D5.
4. Введіть в комірку Е3 кінцеву дату аналізованого періоду.
5. Введіть в комірку F3 дату аналізованого дня (при необхідності).
Розрахункові дані з’являться в комірках автоматично.
Стандартні можливості таблиці-відомості
Додаткові аналітичні дані можна одержати, використовуючи стандартні можливості Excel. Найбільш доступний спосіб - це установка фільтрів, що дозволяють групувати дані за визначеними ознаками. Виділите комірки A9:J9 і виберіть команду Автофильтр (меню Данные - Фильтр). Ознакою появи нової можливості по обробці даних буде установка в кожній комірці стрілки автофільтру. Тепер клацніть стрілку, наприклад, в комірці D4, і вкажіть у списку, що з’явився, рядок "Гонтар В.А.". В результаті в таблиці будуть представлені дані тільки по цьому водію (мал.5). Аналогічні вибірки можна робити за будь-яким списком (графою).
Поява помилки #н/д.
Найбільш розповсюджена проблема при використанні формул масиву. Таке повідомлення з’являється в комірці, якщо використовувані у формулах інтервали комірок не рівні і не симетричні. Щоб виправити це, достатньо відкрити Формулы / Диспетчер имен і скорегувати формулу імені. Числові аргументи повинні бути однаковими (мал.6).
Ця ж операція знадобиться вам, якщо обсяг даних, внесених у таблицю-відомість, перевищить використовуваний розмір іменованих інтервалів комірок. Нехай, розглянута таблиця повністю заповнена і дані необхідно розташовувати нижче 25 рядка (див. графу Інтервал, Таблиця 1 і мал.1). Тоді просто збільшить останній числовий аргумент з 25 до потрібного розміру, наприклад, до 110 (поле Формула, мал.6).
Лабораторная работа № 2 (практическое занятие 2) использование средств ms excel для экономических расчетов: подбор параметра, таблица данных, поиск решения
1. Подбор параметра
Подбор параметра — это средство Excel для так называемого анализа «что-если». При этом значения ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равным заданному.
Например. Как превратить нажитые тяжким трудом сто тысяч в миллион? За год-два... Ну, пока не началось...
Составим таблицу данных.
1. Введите в ячейку С2 сумму вклада 100 000 гривен в формате Денежный (или Финансовый — в нем нет отрицательных чисел), число десятичных знаков 0.
2. Введите в ячейку СЗ месячную процентную ставку 1 % (формат Процентный, число десятичных знаков 0).
3. Введите в ячейку С4 срок 12 месяцев.
4. Введите в ячейку С5 формулу сложных процентов =(1+СЗ)^С4 для расчета коэффициента наращивания.
5. Введите в ячейку С7 формулу =С2*С5 для расчета суммы выплат (формат, тот же, что и в ячейке С2).
6. Через год (12 месяцев) остаток вклада (или сумма вместе с полученной прибылью составит всего лишь 112 683 гривны (рис. 1).
Рис. 1. Вычисление суммы выплат
А сколько же нужно ждать, чтобы получить миллион?
При изменении в таблице с исходными данными исходных параметров (суммы вклада, процента и срока) соответственно изменяется и сумма выплат. В случае, когда нужно определить значение исходных данных, исходя из заданной суммы выплат, задача значительно усложняется. Решить такую задачу можно только подбором, изменяя исходные величины, пока не будет получен желаемый результат. Для решения такой задачи можно использовать средство Excel — Подбор параметра.
Примечание: При выполнении вычислений с помощью функции подбора параметра необходимо, чтобы целевая ячейка была прямо или косвенно связана с ячейкой с изменяемым значением,
7. Выберите Данные / Анализ "Что-если" / Подбор параметра. На экране появится диалоговое окно Подбор параметра (рис. 2).
Рис. 2. Подбор параметра
В поле Установить в ячейке укажите целевую ячейку — ту, в которой мы хотим получить желаемое значение. В нашем случае это ячейка С7. Это можно сделать двумя способами: ввести адрес с клавиатуры или выделить (щелчком) ячейку на рабочем листе.
Примечание: Кнопка свертывания диалогового окна справа от поля ввода позволит временно убрать диалоговое окно с экрана, чтобы было удобнее выделять диапазон на листе. Закончив выделение диапазона, для восстановления диалогового окна снова щелкните на этой кнопке.
8. В поле Значение введите числовое значение (в нашем случае 1000000), которое должно быть получено в целевой ячейке. В это поле должно быть введено только число, иначе при запуске появится соответствующее сообщение.
9. В поле Изменяя значение ячейки укажите адрес ячейки, которая должна меняться для получения желаемого результата. Щелкните на кнопке ОК, чтобы запустить поиск нужного значения.
Рис. 3. Результат подбора параметра
10. На экране появится диалоговое окно Результат подбора параметра с сообщением о результате поиска.
11. Щелкните на кнопке ОК, если полученные результаты удовлетворительны, или на кнопке Отмена, если от полученных результатов придется отказаться
Примечание: Если решение не может быть найдено, сообщение об этом выводится в диалоговом окне Результат подбора параметра. Если задача обладает плохой сходимостью и для ее решения требуется очень много итераций, можно воспользоваться кнопками Шаг и Пауза, чтобы проконтролировать процесс.
Средство Подбор параметра помогло убедиться, что удесятерить вклад и достичь заветного миллиона при процентной ставке в 1 % месячных можно всего за 231 месяц, то есть менее чем за 20 лет. Если же пробовать уложиться в год, изменяя значение в ячейке СЗ, то стоит вкладывать деньги только в предприятия, сулящие не менее 21 % прибыли в месяц!