Решение задач с помощью программы ms excel

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

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

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

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

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

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

Решение задачи о распределении ресурсов с помощью Поиска решений

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

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

Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

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

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

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

Урок 1.Поиск решения, оптимизация, оптимальный план производства

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Менее – не сдан.

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

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

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

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

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

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

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Карта сайта
• Скачать примеры

Источник: exceltable.com

Решение задач с помощью программы Excel. Исследовательская работа

МУНИЦИПАЛЬНОЕ БЮДЖЕТНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ЖИРНОВСКАЯ СРЕДНЯЯ ОБЩЕОБРАЗОВАТЕЛЬНАЯ
ШКОЛА
Исследовательская работа
Решение задач с
помощью программы
Excel
Работу выполнил ученик
11 класса
Сергиенко Александр
Учитель: Скрынникова О.П.
2022г

2.

КАК ЧАСТО ЧЕЛОВЕК
ИСПОЛЬЗУЕТ
ТАБЛИЦЫ В ЖИЗНИ?
30
30
50
50
20
20
Кто чаще всего
использует
программу Excel?
20
10
50
20
бухгалтер
часто
часто
ученик, студент
редко
редко
банкир
затрудняюсь
затрудняюсь ответить
ответить
затрудняюсь ответить
В повседневной жизни человек постоянно использует
таблицы: дневник в школе, расписание электричек,
расписание занятий и т.д.

3.

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

4.

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

5.

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

6.

Цель работы: рассмотреть возможности табличного процессора,
не изучаемые в школьной программе, на примерах решения
задач с использованием элементов управления и решения систем
уравнений.
Задачи:
Рассмотреть программу Excel.
Выявить дополнительные возможности табличного процессора
Excel, которые мы еще не изучали.
Решить задачу с использованием элементов управления.
Решить систему уравнений графическим способом.
Провести анализ проделанной работы, сделать выводы.
Методы исследования:
Анализ литературы по проблеме исследования.
Практическая работа.
Анализ результатов.

7.

Первая электронная таблица, явившаяся прототипом программы
Excel, появилась в 1979 году благодаря студенту из Гарварда Дэну
Бриклину . Как известно, студентам приходится делать множество
математических расчетов, и Бриклин счел это занятие слишком
утомительным и трудозатратным. Придумав первую в мире
электронную таблицу, Брикман, тем не менее, доказал, что
студенты Гарварда – действительно лучшие из лучших.
Электронная
таблица
получила
название VisiCalc и сразу обрела популярность.
Ее первая версия предназначалась для
компьютеров Apple. Да – да, оказывается, они
уже существовали в то время и, кстати, именно
появлению VisiCalc Apple обязан значительным
скачком продаж.
Следующим этапом развития Excel стала
электронная таблица, представленная
компанией Lotus и предназначавшаяся для
компьютеров IBM. И снова, благодаря
электронной таблице, оборот компании вскоре
достиг 50 миллионов долларов в год.

8.

Версия Excel, которая в большей степени уже знакома
пользователям, была представлена в 1987 году
компанией Майкрософт. Программа имела
усовершенствованный интерфейс и широкий набор
функций.

9.

«Простые задачи должны решаться просто».
Этому постулату как нельзя лучше отвечают вычислительные
возможности программы MS Excel, которые без оговорки можно
назвать безграничными.
Программа MS Excel (электронные таблицы) предназначена
для работы с таблицами данных, преимущественно числовых.
Особенность электронных таблиц заключается в возможности
применения формул для описания связи между значениями
различных ячеек.
Расчёт по заданным формулам выполняется автоматически.
Изменение содержимого какой-либо ячейки приводит к
пересчёту значений всех ячеек, которые с ней связаны
формульными отношениями и, тем самым, к обновлению всей
таблицы в соответствии с изменившимися данными.

10.

Электронные таблицы можно использовать эффективно для:
проведения однотипных расчётов над большими наборами данных;
итоговых вычислений;
решения задач путём подбора значений параметров;
табулирования формул (функций);
обработки результатов экспериментов;
проведение поиска оптимальных значений параметров;
подготовки табличных документов;
построения диаграмм и графиков по имеющимся данным.
Возможности табличного процессора
MS Excel позволяют
создавать электронные пособия (учебники), состоящие из
многих станиц (листов), переходы в которых можно создавать
с помощью гиперссылок и кнопок.

11.

ИССЛЕДОВАНИЕ
ВОЗМОЖНОСТЕЙ MS EXCEL
Для чего нужен Excel?
Приведем жизненные примеры применения формул,
функций и графиков в решении важных задач.
Задача 1. Найти корни функции у=х*х+5*х-6 в
диапазоне значений аргумента [0;5] с точностью 0,5.

12.

1 ЭТАП: ПОСТРОИМ ТАБЛИЦУ ЗНАЧЕНИЙ
ФУНКЦИИ

13.

2 ЭТАП: ПО ТАБЛИЦЕ ЗНАЧЕНИЙ АРГУМЕНТА
И ФУНКЦИИ СТРОИМ ГРАФИК ФУНКЦИИ.

14.

ИЗ
ГРАФИКА
ВИДНО,
ЧТО
ФУНКЦИЯ
ПЕРЕСЕКАЕТ ОСЬ АБСЦИСС НА УКАЗАННОМ
ОТРЕЗКЕ
ИМЕЕТ
ДВА РАЗА. СЛЕДОВАТЕЛЬНО, ОНА
ДВА
КОРНЯ:
Х=2,
Х=3.

15.

2 ЗАДАЧА: РЕШИТЬ СИСТЕМУ УРАВНЕНИЙ
8 x 5 y 10
5x 2 y 4
7x 4 y 8
Выразим в каждом уравнении х относительно у,
получим:
y 1,6 x 2
y 2,5 x 2
y 1,75 x 2

16.

1 ЭТАП: ПОСТРОИМ ТАБЛИЦУ
ЗНАЧЕНИЙ ФУНКЦИИ

17.

РЕШЕНИЕ ЗАДАЧИ: НАХОДИМ ИХ ОБЩУЮ ТОЧКУ ПЕРЕСЕЧЕНИЯ,
НАВОДИМ УКАЗАТЕЛЬ МЫШИ НА ТОЧКУ ПЕРЕСЕЧЕНИЯ, ПРИ ЭТОМ
ВСПЛЫВАЕТ ПОДСКАЗКА, ЧТО ТОЧКА ПЕРЕСЕЧЕНИЯ С КООРДИНАТОЙ (0;
2).

18.

3 ЗАДАЧА. ВЫ РЕШИЛИ ВЗЯТЬ КРЕДИТ В
БАНКЕ. ПОСТРОИМ КАЛЬКУЛЯТОР .
1 способ

19.

ДАЛЕЕ В ТАБЛИЦЕ ВЫПОЛНЯЕМ РАСЧЕТ ВЫПЛАТ
ПО КРЕДИТУ, ПРОЦЕНТОВ ЗА КАЖДЫЙ МЕСЯЦ,
ИТОГОВЫЕ СУММЫ
Вывод: Таким образом,
можно не только
проверить правильность
финансовых расчетов, но
и понять как выгоднее
отдать долг в банк.

20.

4 ЗАДАЧА. ВЫ ЗАДУМАЛИСЬ КУПИТЬ МАШИНУ, НА
КОТОРОЙ БУДЕТЕ ЕЗДИТЬ НА РАБОТУ. ВОПРОС:
ВЫГОДНО ЛИ ЭТО?
Формулировка
условий:
•На общественном транспорте вы
тратите на дорогу 24*4=96 руб в день.
•Стоимость бензина равна 38,9 руб за
литр.
•Расход на вашем автомобиле 7
литров на 100 км.

21.

Можно решить задачу,
используя ПОДБОР
ПАРАМЕТРА:
1.До какого значения
может повышаться
цена на бензин, чтобы
было выгодно ездить
на работу на машине.

22.

Получаем
ответ: 45, 71
рублей

23.

Таким же образом, при
исходных данных можно
расчитать на каком
максимальном удалении
может находиться ваша
работа, чтобы поездка на
машине была выгодной

24.

Получим
ответ: 35,26
км.

25.

Подведем итог
Преимущества, которыми вы сможете обладать, если решите
изучить программу Excel:
1. Вместо того чтобы делать математические расчеты устно или при
помощи калькулятора, за нас это с легкостью сделает Excel.
2. Программе каким-то образом известны все правила алгебры,
которые мы так не любим в школе;
3. Мы можем сортировать и фильтровать данные по любым
параметрам;
4. Мы можем составлять диаграммы и графики и использовать их в
презентациях;
5. Excel может выявить максимум и минимум, а также подсчитать
среднее значение даже очень большого диапазона чисел
(представьте, сколько времени нам понадобиться делать это
вручную!)
6. Excel умеет даже применить условие «а что если», действуя
одновременно как психолог и финансовый аналитик, особенно
если речь идет о курсе валют, размере прибыли или убытков.

26.

автоматизация всех итоговых вычислений;
над большими наборами данных можно производить
однотипные расчёты;
можно разрешать задачи с помощью подбора значений с
различными параметрами;
можно обрабатывать результаты экспериментов;
производить табулирование функций и формул;
подготавливать табличные документы;
проводить поиск наиболее оптимальных значений для
выбранных параметров;
строить графики и диаграммы по уже введённым данным.

27.

Человек постоянно использует таблицы:
дневник в школе,
расписание транспорта,
расписание занятий
наличие мощного аппарата инженерных и
математических функций позволяет решать большой
спектр задач в области технических и многих
естественных наук.
Знать базовые возможности электронных
таблиц необходимо каждому из нас!

Источник: ppt-online.org

проект «Решение практических задач с помощью электронных таблиц»