Как решать задачи в excel примеры

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

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

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

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

В 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.

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

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

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



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

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

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

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

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

Заполнение аргументов:

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

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

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

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

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

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

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

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

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

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, <, >=, <=). Результат логического выражения – логическое значение ИСТИНА или логическое значение ЛОЖЬ.

Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

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

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

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

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

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

Скачать примеры

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

Цель урока: продолжить формирование
навыков работы с электронными таблицами.

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




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




• воспитательные: формировать и воспитывать
  познавательный интерес; прививать навыки
  самостоятельности в работе.

План урока.

1. Организационный момент.
2. Актуализация знаний учащихся.
3. Проверка домашнего задания.
4. Решение задач.
5. Самостоятельное решение задачи.
6. Подведение итогов. Оценки.
7. Домашнее задание.

1. Организационный момент.

Сообщить тему урока, сформулировать цели и
задачи урока.

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

Сможете ли вы помочь Васе? Сейчас проверим!

• Что такое электронная таблица?
• Какие основные элементы электронной таблицы
  вам известны?
• Как задается имя ячейки (строки, столбца) в
  электронной таблице?
• Что может быть содержимым ячейки?
• Число 1 находится в столбце …, в строке …, в
  ячейке с адресом …
• Число 4 находится в ячейке с адресом …
• Каковы правила записи формул в ячейках?
• Чему равно значение, вычисляемое по формуле, в
  ячейке С1?
• Чему равно значение, вычисляемое по формуле, в
  ячейке D2?

Ответ: 25*4/2=50

• Что означает запись =СУММ(В1:D3)?
• Сколько элементов содержит блок В1:D3? Ответ: 9.
• Содержимое ячейки D3? Ответ: 5+2+1+6+8+3+8+3+4= 40

Результаты соревнований по плаванию

Один ученик рассказывает, как он выполнил
домашнее задание (через проектор).

• Среднее время для каждого спортсмена находится
  как среднее арифметическое трех его заплывов.
• В ячейку «Лучшее время» записывается
  минимальный результат из 3 заплывов.
• В ячейку «Лучший результат соревнований»
  записывается минимальное время из столбца.
• В столбец «Отклонение» записывается
  разность между лучшим временем спортсмена и
  лучшим результатом соревнований.
• В ячейку «Максимальное отклонение»
  записывается максимальное значение столбца.

4) Решение простых задач.

№ 1. Вычислить периметр и площадь
прямоугольника со сторонами:

а) 3 и 5; б) 6 и 8; в) 10 и 7.

№ 2. Маленький великан Вася решил
подсчитать, через сколько дней в его копилке
будет 100 руб., если ежедневно он стал класть туда
на 5 руб. больше, чем в предыдущий день. Помогите
Васе. Сейчас в его копилке 2,02 руб.

№ 3. Посчитайте, используя ЭТ, хватит ли
Васе 150 рублей, чтобы купить все продукты, которые
ему заказала мама, и хватит ли на чипсы за 10
рублей? Сдачу мама разрешила положить в копилку.
Сколько рублей попадет в копилку?

Предполагаемое решение:

5) Самостоятельное решение задачи.

Маленький великан Вася часто бывал в гостях у
жителей Цветочного города.

Собираясь на пляж, веселые человечки решили
запастись прохладительными напитками. Незнайка
взял с собой 2 литра кваса, 1 литр газировки и 1
литр малинового сиропа, Пончик – 3 литра
газировки и 2 литра малинового сиропа, Торопыжка
– 2 литра газировки, доктор Пилюлькин – 1 литр
кваса и 1 литр касторки.

• Сколько литров напитков каждого вида взяли все
  человечки вместе?
• Сколько всего литров напитков взял с собой
  каждый из человечков?
• Сколько всего литров напитков взяли все
  человечки вместе?

Оформите таблицу произвольно и сохраните в
своей личной папке.

Результат работы.

Подумайте и решите эту задачу, если известны
еще следующие величины.

Как изменится таблица? Какие формулы появятся?

Известно, что 1 литр кваса в Цветочном городе
стоит 1 монету, 1 литр газировки – 3 монеты, 1 литр
малинового сиропа – 6 монет, 1 литр касторки – 2
монеты.

• Сколько монет истратил на покупку напитков
  каждый человечек?
• Сколько монет затрачено на покупку напитков
  каждого вида?
• Сколько потрачено денег всеми человечками
  вместе?

1. Информатика. Задачник-практикум в 2 т. /Под ред.
   И.Г.Семакина, Е.К.Хеннера – М.: Лаборатория
   Базовых Знаний, 2010.
2. Ефимова О. Курс компьютерной технологии с
   основами информатики. – М.: ООО “издательство
   АСТ”; АВF, 2005.

Презентация

Эксель можно использовать для решения широкого спектра задач, в том числе, для нахождения наилучшего способа осуществления перевозок от производителя (продавца) к потребителю (покупателю). Давайте посмотрим, каким образом это можно реализовать в программе.

Транспортная задача: описание

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

Транспортные задачи бывают двух типов:

• Закрытая – совокупное предложение продавца равняется общему спросу.
• Открытая – спрос и предложение не равны. Чтобы решить такую задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с недостающим количеством спроса или предложения. Также в таблицу издержек следует внести соответствующую запись (с нулевыми значениями).

Подготовительный этап: включение функции “Поиск решения”

Чтобы решить транспортную задачу в Эксель, нужно воспользоваться функцией “Поиск решения”, которую нужно предварительно активировать, т.к. изначально она не включена. Алгоритм действий следующий:

1. Открываем меню “Файл”.
2. В перечне слева выбираем пункт “Параметры”.
3. В параметрах кликаем по подразделу “Надстройки”. Затем в правой части окна в самом низу, выбрав значение “Надстройки Excel” для параметра “Управление”, щелкаем по кнопке “Перейти”.
4. В открывшемся окне ставим галочку напротив надстройки “Поиск решения” и жмем OK.
5. В результате, если мы перейдем во вкладу “Данные”, то увидим здесь кнопку “Поиск решения” в группе инструментов “Анализ”.

Пример задачи и ее решение

Чтобы лучше понять, как решать транспортные задачи в Excel, давайте рассмотрим конкретный практический пример.

Условия задачи

Допустим, у нас есть 6 продавцов и 7 покупателей. Предложение продавцов составляет 36, 51, 32, 44, 35 и 38 единиц. Спрос покупателей следующий: 33, 48, 30, 36, 33, 24 и 32 единицы. Суммарные количества по спросу и предложению равны, следовательно, это транспортная задача закрытого типа.

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

Алгоритм решения

Итак, приступи к решению нашей задачи:

1. Для начала строим таблицу, количество строк и столбцов в которой соответствует числу продавцов и покупателей, соответственно.
2. Перейдя в любую свободную ячейку щелкаем по кнопке “Вставить функцию” (fx).
3. В открывшемся окне выбираем категорию “Математические”, в списке операторов отмечаем “СУММПРОИЗВ”, после чего щелкаем OK.
4. На экране отобразится окно, в котором нужно заполнить аргументы:
   • в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
   • в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
   • по готовности жмем OK.
5. Щелкаем по ячейке, расположенной слева от самого верхнего левого элемента новой таблицы, после чего снова жмем кнопку “Вставить функцию”.
6. На этот раз нам нужна функция “СУММ”, которая также, находится в категории “Математические”.
7. Теперь нужно заполнить аргументы. В качестве значения аргумента “Число1” указываем верхнюю строку созданной для расчетов таблицы (целиком) – вручную или методом выделения на листе. Жмем кнопку OK, когда все готово.
8. В ячейке с функцией появится результат, равный нулю. Наводим указатель мыши на ее правый нижний угол, и когда появится Маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца таблицы.
9. Это позволит скопировать формулу и получить аналогичные результаты для остальных строк.
10. Выбираем ячейку, которая находится сверху от самого верхнего левого элемента созданной таблицы. Аналогично описанным выше действиям вставляем в нее функцию “СУММ”.
11. В значении аргумента “Число1” теперь указываем (вручную или с помощью выделения на листе) все ячейки первого столбца, после чего кликаем OK.
12. С помощью Маркера заполнения выполняем копирование формулы на оставшиеся ячейки строки.
13. Переключаемся во вкладку “Данные”, где жмем по кнопке функции “Поиск решения” (группа инструментов “Анализ”).
14. Перед нами появится окно с параметрами функции:
    • в качестве значения параметра “Оптимизировать целевую функцию” указываем координаты ячейки, в которую ранее была вставлена функция “СУММПРОИЗВ”.
    • для параметра “До” выбираем вариант – “Минимум”.
    • в области для ввода значений напротив параметра “Изменяя ячейки переменных” указываем диапазон ячеек новой таблицы (без суммирующей строки и столбца).
    • нажимаем кнопку “Добавить” в блоке “В соответствии с ограничениями”.
15. Откроется небольшое окошко, в котором мы можем добавить ограничение – сумма значений первых столбцов исходной и созданной таблицы должны быть равны.
    • становимся в поле “Ссылка на ячейки”, после чего указываем нужный диапазон данных в таблице для расчетов.
    • затем выбираем знак “равно”.
    • в качестве значения для параметра “Ограничение” указываем координаты  аналогичного столбца в исходной таблице.
    • щелкаем OK по готовности.
16. Таким же способом добавляем условие по равенству сумм верхних строк таблиц.
17. Также добавляем следующие условия касательно суммы ячеек в таблице для расчетов (диапазон совпадает с тем, который мы указали для параметра “Изменяя ячейки переменных”):
    • больше или равно нулю;
    • целое число.
18. В итоге получаем следующий список условий в поле “В соответствии с ограничениями”. Проверяем, чтобы обязательно была поставлена галочка напротив опции “Сделать переменные без ограничений неотрицательными”, а также, чтобы в качестве метода решения стояло значение “Поиск решения нелинейных задач методов ОПГ”. Когда все готово, нажимаем “Найти решение”.
19. В результате будет выполнен расчет и отобразится окно с результатами поиска решения. Оцениваем их, и в случае, когда они нас устраивают, нажимаем OK.
20. Все готово, мы получили таблицу с заполненными данными и транспортную задачу можно считать успешно решенной.

Заключение

Таким образом, с помощью программы Эксель достаточно просто решить транспортную задачу. Самое главное – правильно заполнить начальные данные и четко следовать плану действий, и тогда проблем быть не должно, т.к. программа все расчеты выполнит сама.