Экономические задачи в excel примеры с решением

Обновлено: 07.07.2024

Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.

Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.

Решение задач оптимизации в Excel

Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

Количество изделий нам пока неизвестно. Это переменные.
В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
Тип – 0.
БС – сумма, которую мы хотим получить в конце срока вклада.

Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрики в Excel

Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.

Дано 2 диапазона значений:

Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

Делаем таблицу со значениями матрицы А.
Выделяем на этом же листе область для обратной матрицы.
Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
В поле аргумента «Массив» вписываем диапазон матрицы А.
Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

Возможности табличного процессора Excel широко применяются в экономике. С помощью офисной программы можно обрабатывать и анализировать данные, составлять отчеты, бизнес-модели, прогнозировать, определять ценность клиентов и т.д.

В рамках данной статьи рассмотрим использование функции ВПР для решения экономических задач.

Описание и синтаксис функции

ВПР – функция просмотра. Формула находит нужное значение в пределах заданного диапазона. Поиск ведется в вертикальном направлении и начинается в первом столбце рабочей области.

Аргумент «Интервальный просмотр» необязательный. Если указано значение «ИСТИНА» или аргумент опущен, то функция возвращает точное или приблизительное совпадение (меньше искомого, наибольшее в диапазоне).

Для правильной работы функции значения в первом столбце нужно отсортировать по возрастанию.

ВПР в Excel и примеры по экономике

Составим формулу для подбора стоимости в зависимости от даты реализации продукта.

Изменения стоимостного показателя во времени представлены в таблице вида:

Нужно найти, сколько стоил продукт в следующие даты.

Назовем исходную таблицу с данными «Стоимость». В первую ячейку колонки «Цена» введем формулу: =ВПР(B8;Стоимость;2). Размножим на весь столбец.

Функция вертикального просмотра сопоставляет даты из первого столбца с датами таблицы «Стоимость». Для дат между 01.01.2015 и 01.04.2015 формула останавливает поиск на 01.01.2015 и возвращает значение из второго столбца той же строки. То есть 87. И так прорабатывается каждая дата.

Составим формулу для нахождения имени должника с максимальной задолженностью.

В таблице – список должников с данными о задолженности и дате окончания договора займа:

Чтобы решить задачу, применим следующую схему:

Для нахождения максимальной задолженности используем функцию МАКС (=МАКС(B2:B10)). Аргумент – столбец с суммой долга.
Так как функция вертикально просматривает крайний левый столбец диапазона (а суммы находятся во втором столбце), добавим в исходную таблицу столбец с нумерацией.
Чтобы найти номер предприятия с максимальной задолженностью, применим функцию ПОИСКПОЗ (=ПОИСКПОЗ(C12;C2:C10;0)). Тип сопоставления – 0, т.к. к столбцу с долгами не применялась сортировка.
Чтобы вывести имя должника, применим функцию: =ВПР(D12;Должники;2).

Сделаем из трех формул одну: =ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); Должники;2). Она нам выдаст тот же результат.

Функция ВПР в экономических расчетах полезна, когда нужно извлечь определенное значение из огромного диапазона данных. Причем это значение нужно найти по другому значению (цену по идентификатору продукта, налоговую ставку по уровню дохода и т.п.).

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

Читайте также: