Как сделать задачу в excel?

Поиск решения MS EXCEL. Знакомство

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

Поиск решения будем рассматривать в MS EXCEL 2010 (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в MS EXCEL 2007).
В этой статье рассмотрим:

  • создание оптимизационной модели на листе MS EXCEL
  • настройку Поиска решения;
  • простой пример (линейная модель).

Установка Поиска решения

Команда Поиск решения находится в группе Анализ на вкладке Данные.

Если команда Поиск решения в группе Анализ недоступна, то необходимо включить одноименную надстройку.
Для этого:

  • На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки;
  • В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
  • В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Примечание. Окно Надстройки также доступно на вкладке Разработчик. Как включить эту вкладку читайте здесь.

После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно.

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

Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

Совет. Перед использованием Поиска решения настоятельно рекомендуем изучить литературу по решению оптимизационных задач и построению моделей.

Ниже приведен небольшой ликбез по этой теме.

Надстройка Поиск решения помогает определить лучший способ сделать что-то:

  • «Что-то» может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
  • «Лучший способ» или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.

Вот некоторые типичные примеры оптимизационных задач:

  • Определить план производства, при котором доход от реализации произведенной продукции максимальный;
  • Определить схему перевозок, при которой общие затраты на перевозку были бы минимальными;
  • Найти распределение нескольких станков по разным видам работ, чтобы общие затраты на производство продукции были бы минимальными;
  • Определить минимальный срок исполнения всех работ проекта (критический путь).

Для формализации поставленной задачи требуется создать модель, которая бы отражала существенные характеристики предметной области (и не включала бы незначительные детали). Следует учесть, что модель оптимизируется Поиском решения только по одному показателю (этот оптимизируемый показатель называется целевой функцией).
В MS EXCEL модель представляет собой совокупность связанных между собой формул, которые в качестве аргументов используют переменные. Как правило, эти переменные могут принимать только допустимые значения с учетом заданных пользователем ограничений.
Поиск решения подбирает такие значения этих переменных (с учетом заданных ограничений), чтобы целевая функция была максимальной (минимальной) или была равна заданному числовому значению.

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

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

Подготовка оптимизационной модели в MS EXCEL

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

Совет. Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).

Приведем алгоритм работы с Поиском решения, который советуют сами разработчики ( www.solver.com ):

  • Определите ячейки с переменными модели (decision variables);
  • Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
  • Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
  • С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
  • Запустите Поиск решения для нахождения оптимального решения.
Читать еще:  Как сделать книгу в excel?

Проделаем все эти шаги на простом примере.

Простой пример использования Поиска решения

Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик — 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.

Данные модели организуем следующим образом (см. файл примера ).

Переменные модели (количество каждого вида тары) выделены зеленым.
Целевая функция (общий вес всех коробок и ящиков) – красным.
Ограничения модели: по минимальному количеству тары (>=110) и по общему объему ( =) или граничного значения.
Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения не найдет решения.

При ограничении 17 м3 Поиск решения найдет решение.

Некоторые настройки Поиска решения

Метод решения
Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение — Максимальное количество тары (n) – также линейно x1+x2 Похожие задачи

Транспортная задача в Microsoft Excel

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

Общее описание транспортной задачи

Главной целью транспортной задачи является поиск оптимального плана перевозок от поставщика к потребителю при минимальных затратах. Условия такой задачи записываются в виде схемы или матрицы. Для программы Excel используется матричный тип.

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

Инструменты для решения транспортной задачи в Эксель

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

    Делаем перемещение во вкладку «Файл».

Кликаем по подразделу «Параметры».

В блоке «Управление», который находится внизу открывшегося окна, в выпадающем списке останавливаем выбор на пункте «Надстройки Excel». Делаем клик по кнопке «Перейти…».

Запускается окно активации надстроек. Устанавливаем флажок возле пункта «Поиск решения». Кликаем по кнопке «OK».

  • Вследствие этих действий во вкладке «Данные» в блоке настроек «Анализ» на ленте появится кнопка «Поиск решения». Она нам и понадобится при поиске решения транспортной задачи.
  • Пример решения транспортной задачи в Excel

    Теперь давайте разберем конкретный пример решения транспортной задачи.

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

    Имеем 5 поставщиков и 6 покупателей. Объёмы производства этих поставщиков составляют 48, 65, 51, 61, 53 единиц. Потребность покупателей: 43, 47, 42, 46, 41, 59 единиц. Таким образом, общий объем предложения равен величине спроса, то есть, мы имеем дело с закрытой транспортной задачей.

    Кроме того, по условию дана матрица затрат перевозок из одного пункта в другой, которая отображена на иллюстрации ниже зеленым цветом.

    Решение задачи

    Перед нами стоит задача при условиях, о которых было сказано выше, свести транспортные расходы к минимуму.

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

    Выделяем любую пустую ячейку на листе. Кликаем по значку «Вставить функцию», размещенному слева от строки формул.

    Открывается «Мастер функций». В списке, который предлагает он, нам следует отыскать функцию СУММПРОИЗВ. Выделяем её и жмем на кнопку «OK».

    Открывается окно ввода аргументов функции СУММПРОИЗВ. В качестве первого аргумента внесем диапазон ячеек матрицы затрат. Для этого достаточно выделить курсором данные ячейки. Вторым аргументом выступит диапазон ячеек таблицы, которая была приготовлена для расчетов. Затем, жмем на кнопку «OK».

    Кликаем по ячейке, которая расположена слева от верхней левой ячейки таблицы для расчетов. Как и в прошлый раз вызываем Мастер функций, открываем в нём аргументы функции СУММ. Кликнув по полю первого аргумента, выделяем весь верхний ряд ячеек таблицы для расчетов. После того, как их координаты занесены в соответствующее поле, кликаем по кнопке «OK».

    Становимся в нижний правый угол ячейки с функцией СУММ. Появляется маркер заполнения. Жмем на левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы для расчета. Таким образом мы скопировали формулу.

    Читать еще:  Как сделать проверку текста в excel?

    Кликаем по ячейке размещенной сверху от верхней левой ячейки таблицы для расчетов. Как и в предыдущий раз вызываем функцию СУММ, но на этот раз в качестве аргумента используем первый столбец таблицы для расчетов. Жмем на кнопку «OK».

    Копируем маркером заполнения формулу на всю строку.

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

    Открываются параметры поиска решения. В поле «Оптимизировать целевую функцию» указываем ячейку, содержащую функцию СУММПРОИЗВ. В блоке «До» устанавливаем значение «Минимум». В поле «Изменяя ячейки переменных» указываем весь диапазон таблицы для расчета. В блоке настроек «В соответствии с ограничениями» жмем на кнопку «Добавить», чтобы добавить несколько важных ограничений.

    Запускается окно добавления ограничения. Прежде всего, нам нужно добавить условие того, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле «Ссылка на ячейки» указываем диапазон суммы в строках таблицы расчетов. Затем выставляем знак равно (=). В поле «Ограничение» указываем диапазон сумм в строках таблицы с условием. После этого, жмем на кнопку «OK».

    Аналогичным образом добавляем условие, что столбцы двух таблиц должны быть равны между собой. Добавляем ограничение, что сумма диапазона всех ячеек в таблице для расчета должна быть большей или равной 0, а также условие, что она должна быть целым числом. Общий вид ограничений должен быть таким, как представлен на изображении ниже. Обязательно проследите, чтобы около пункта «Сделать переменные без ограничений неотрицательными» стояла галочка, а методом решения был выбран «Поиск решения нелинейных задач методом ОПГ». После того, как все настройки указаны, жмем на кнопку «Найти решение».

  • После этого происходит расчет. Данные выводятся в ячейки таблицы для расчета. Открывается окно результатов поиска решения. Если результаты вас удовлетворяют, жмите на кнопку «OK».
  • Как видим, решение транспортной задачи в Excel сводится к правильному формированию вводных данных. Сами расчеты выполняет вместо пользователя программа.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Решение простых задач с помощью Excel

    Классы: 6 , 7

    Ключевые слова: Excel , функции в Excel , ячейка , адрес ячейки , таблица

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

    Задачи:

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

    План урока.

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

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

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

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

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

    2. Актуализация знаний учащихся.

    1) Устно ответить на вопросы.

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

    2) Какой результат будет получен в ячейках с формулами?

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

    3) Проверка домашнего задания

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

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

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

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

    Маленький великан Вася решил отремонтировать забор вокруг своего огорода и вскопать его под посадку овощей (наступила очередная весна), разметить грядки прямоугольной формы. Для работы ему потребовалось найти длину забора и площадь участка. Но ведь в школе он никогда не учился. Поможем Васе.

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

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

    Эту задачу обсуждаем совместно с детьми:

    • Как оформить таблицу?
    • Какие формулы использовать?
    • Как использовать уже записанные формулы для следующего прямоугольника?

    Оформление таблицы – на доске и в тетрадях.

    В то же время другой ученик самостоятельно решает следующую задачу и представляет свое решение учащимся (через проектор).

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

    Обсудив решение задачи № 2, переходим к решению следующей.

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

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

    Поиск решения задач в 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.

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

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

    Оптимальный вариант – сконцентрироваться на выпуске йогурта «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. Это условие, при котором логическое значение – ИСТИНА.

  • Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
  • Решение математических задач в Excel

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

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

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

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

    Ссылка на основную публикацию
    Adblock
    detector