ВПР(искомое значение; таблица; номер столбца; интервальный просмотр)
ГПР(искомое значение; таблица; номер строки; интервальный просмотр)
Таблица -- таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.
Номер столбца(номер строки) -- это номер столбца (строки) в массиве «таблица», в котором должно быть найдено соответствующее значение.
Искомое значение -- это значение, которое должно быть найдено в первом столбце (первой строке) массива. Искомое значение может быть значением, ссылкой или текстовой строкой.
Интервальный просмотр -- это логическое значение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие.
Функция ГПР() ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже. Если сравниваемые значения расположены в столбце слева от искомых данных, то следует использовать функцию ВПР.
3.3 Индивидуальное задание
Сформулировать основную и вспомогательную таблицы. Используя данные вспомогательной таблицы, вычислить сумму продаж различных групп товаров.
3.4 Схема алгоритма выполнения индивидуального задания
1. Создать таблицу с исходными данными.
2. Создать вспомогательную таблицу, с данными, которые необходимо перенести в основную таблицу.
3. Вычислить недостающие данные основной таблицы, используя вспомогательную таблицу и функцию ВПР.
3.5 Вывод
С помощью функции просмотра ссылок в программе MicrosoftExcel была рассчитана сумма продаж различных групп товаров с использованием данных из вспомогательной таблицы (Приложение 5).
4. Решение задач оптимизации с помощью надстройки Поиск решения
4.1 Цель работы
Целью данной работы является выявление оптимального объема потребляемых благ для максимизации полезности потребителя, с помощью надстройки Поиск решений в программе Microsoft Excel.
4.2 Краткая теоретическая часть
Многие экономические задачи (максимизация выпуска при ограниченных ресурсах, максимизация полезности при ограниченном доходе и т.д.) сводятся к широкому классу задач оптимизации, для решения которых применяются математические методы.
Существует возможность с помощью надстройки Поиск решения найти оптимальное при данных ограничениях решение. Для этого выберите команду Поиск решения.
Для версий старше Excel 2007 через команду меню Сервис -->Надстройки;
начиная с Excel 2007 через диалоговое окно Параметры Excel.
Начиная с версии Excel 2007 кнопка для запуска Поиска решения появится на вкладке Данные.
В версиях до Excel 2007 аналогичная команда появится в меню Сервис.
В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу. Установите переключатель в положение максимального, минимального или конкретного значению;
В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
В поле Ограничения введите все ограничения, накладываемые на поиск решения.
4.3 Индивидуальное задание
Решить задачу максимизации полезности по исходным данным (Таблица 3).
Таблица 3. Исходные данные
|
Вариант |
a |
b |
px |
py |
I |
|
|
5 |
1/2 |
1/4 |
15 |
3 |
90 |
4.4 Схема алгоритма выполнения индивидуального задания
1. Построить таблицу с исходными данными, взятыми из Таблицы 1.
2. Ввести целевую формулу, которая имеет вид функции полезности: U(x,y)=xayb.
3. Ввести формулу ограничения, которая имеет вид Pxx + Pyy = I.
4. Вычислить значения параметров Х и У, используя надстройку Поиск решения.
4.5 Вывод
С помощью надстройки Поиск решений в программе MicrosoftExcel был выявлен оптимальный объем потребляемых благ для максимизации полезности потребителя (Приложение 6).
5. Подбор параметра. Таблица подстановки
5.1 Цель работы
Целью данной работы является изучение команд Подбор параметра и Таблица подстановки в программе MicrosoftExcel, а также выявление процентной ставки, при которой будет выгодна сделка и расчет различных комбинация процентной ставки и срока вклада с использованием таблицы подстановки.
5.2 Краткая теоретическая часть
При решении задачи следует использовать функцию ЧПС и команду Сервис/Подбор параметра.
Функция ЧПС возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).
ЧПС (ставка;значение1;значение2;...)
Ставка -- ставка дисконтирования за один период.
Значение 1, значение2,... -- от 1 до 29 аргументов, представляющих расходы и доходы.
Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных.
Расчет сложных процентов осуществляется по формуле:
,
где F - будущая стоимость вклада, P - первоначальная стоимость вклада, r - годовая номинальная ставка, n - количество лет.
5.3 Индивидуальное задание 1
Вас просят дать в долг Р рублей и обещают вернуть через год F1 руб., через два - F2 руб., через три - F3. Рассчитайте процентную ставку, при которой эта сделка выгодна. Данные для расчета брать из Таблицы 4.
Таблица 4. Данные для расчета
|
Р |
F1 |
F2 |
F3 |
|
|
50 000 |
30 000 |
20 000 |
20 000 |
5.4 Схема алгоритма выполнения индивидуального задания 1
1. Создать таблицу с исходными данными, взятыми из Таблицы 1.
2. Записать формулу нахождения чистого текущего объема вклада с помощью функции ЧПС.
3. Рассчитать годовую учетную ставку, используя команду Подбор параметра.
5.5 Вывод 1
С помощью команды Подбор параметра в программе MicrosoftExcel была рассчитана годовая учетная ставка, при которой будет выгодна сделка, условиями которой являются исходные данные (Приложение 7).
5.6 Индивидуальное задание 2
Рассчитайте сумму вклада с помощью таблицы подстановки в зависимости от сроков (от 1 до 10 лет) и процентных ставок (от 5% до 15%). Первоначальную сумму вклада взять из Таблицы 4.
5.7 Схема алгоритма выполнения индивидуального задания 2
1. Создать таблицу с исходными данными (таблицу подстановки).
2. Ввести в таблицу подстановки произвольное значение срока вклада и процентной ставки, в пределах условия задания (п. 5.6).
3. Рассчитать значение наращенной суммы по формуле, взятой из пункта 5.2.
4. Создать вторую таблицу, в первом столбце которой будут указаны размеры процентной ставки, в соответствии с условием задания (п.5.6) (от 5% до 15%), а в ее первой строке будут указаны значения возможного срока вклада, также в соответствии с условием задания (п.5.6) (от 1 до 10).
5. Рассчитать данные для второй таблицы с использованием данных таблицы подстановки и команды Таблица подстановки.
5.8 Вывод 2
С помощью команды Таблица подстановки в программе MicrosoftExcel были рассчитаны наращенная сумма для различных комбинаций процентной ставки и срока вклада (Приложение 8).
6. Управление списками в MicrosoftExcel. Сортировка и отбор данных
6.1 Цель работы
Целью данной работы является изучение управления списками, в программе Microsoft Excel, а также, используя сортировку и отбор данных, выбрать из таблицы постояльцев, сделавших заказ на определенную сумму, выполнить сортировку данных по алфавиту и выявить людей, сделавших заказ на определенную сумму.
6.2 Краткая теоретическая часть
Различные экономические, финансовые, учетные и многие другие задачи требуют представления электронных таблиц в виде так называемых списков. Списки - это таблицы, строки которых содержат однородную информацию. Строки таблицы называют записями, а столбцы - полями записей. Столбцам присваиваются уникальные имена полей, которые заносят в первую строку списка - строку заголовка.
Работа с подготовленным списком MS Excel может осуществляться по трем направлениям:
§ Сортировка - выстраивание данных в нужном порядке;
§ Отбор данных - извлечение записей из списка в соответствии с некоторыми критериями;
§ Анализ данных - обработка различными средствами информации, находящейся в списке или отфильтрованных данных.
Сортировка данных осуществляется с помощью команды Данные / Сортировка.
Отбор данных осуществляется следующими средствами:
1) Поиск с помощью формы данных:
Форма данных представляет собой средство для поиска и редактирования записей, которые удовлетворяют простому или множественному критерию сравнения. В форме данных условия в критерии должны соответствовать логической операции И. Здесь нельзя использовать логическую операцию ИЛИ и конструировать вычисляемые критерии.
Для поиска необходимо выбрать команду Данные/Форма, затем нажать кнопку Критерии. После ввода критерия для перехода к нужной записи следует нажать кнопку Далее или Назад.
2) Поиск с помощью автофильтра
Автофильтр позволяет вывести на рабочий лист все записи, удовлетворяющие заданному критерию. Поиск с помощью автофильтра производится с помощью команды Данные/Фильтр/Автофильтр.
3) Поиск с помощью расширенного фильтра.
Расширенный фильтр позволяет одновременно или по отдельности применять операции И, ИЛИ и составлять вычисляемые критерии.
6.3 Индивидуальное задание
Создать таблицу со списком постояльцев гостиницы.
1. Используя поиск с помощью формы данных, удалить из таблицы постояльцев, остановившихся в номере 3 класса.
2. Используя поиск с помощью автофильтра, выполнить сортировку данных по дате заезда от самой ранней до самой поздней.
3. Используя поиск с помощью расширенного фильтра, выявить постояльцев, забронировавших заранее номер 1 класса.
6.4 Схема алгоритма выполнения индивидуального задания
1. Создать таблицу с исходными данными.
2. Удалить из таблицы постояльцев, остановившихся в номере 3 класса, используя поиск с помощью формы данных.
3. Выполнить сортировку данных по дате заезда от самой ранней до самой поздней, используя поиск с помощью автофильтра.
4. Выявить постояльцев, забронировавших заранее номер 1 класса, используя поиск с помощью расширенного фильтра.
6.5 Вывод
С помощью различных средств отбора данных в программе Microsoft Excel были выявлены люди, сделавшие заказ на сумму меньше 4500 рублей, из таблицы, выполнена сортировка данных по алфавиту, а также выбраны люди, сделавшие заказ на сумму больше 4500 рублей, забронировавшие заранее номер 1 класса (Приложение 9).
7. Анализ данных. Структуризация рабочих листов. Автоматическое подведение итогов
7.1 Цель работы
Целью данной работы является выполнение автоматически и «вручную» структуризации данных, полученных по итогам экзаменационной сессии студентов, а также автоматическое подведение промежуточных итогов продажи товаров, различных категорий в программе Microsoft Excel.
7.2 Краткая теоретическая часть
MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся:
– Обработка списка с помощью различных формул и функций;
– Построение диаграмм:
– Проверка данных рабочих листов на наличие ошибок;
– Структуризация рабочих листов;
– Автоматическое подведение итогов (включая мастер частичных сумм);
– Консолидация данных:
– Сводные таблицы;
– Специальные средства анализа выбранных данных - подбор параметра, поиск решения, сценарии, пакет анализа и др.
Структуризация рабочих листов
Цель структуризации заключается в разбиении данных, содержащихся на рабочем листе, на определенные уровни детализации.
Выполните структуризацию данных автоматически и «вручную».
Для автоматического создания структуры следует:
– Удостовериться, что в итоговых формулах, содержатся ссылки на детальные данные, расположенные в одном направлении относительно итоговых;
– Выделить нужный диапазон ячеек - для структуризации части рабочего листа или выбрать одну ячейку - для структуризации всего рабочего листа;
– Выполните команду Данные/Группа и структура/Создание структуры