Как решать в excel задачи – Поиск решения задач в Excel с примерами

Решение транспортной задачи в Excel

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

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

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

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

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

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

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



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



3. В новом окне переходим по надписи «Надстройки».



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



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



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

Урок: функция «Поиск решения» в Экселе

Пример решения транспортной задачи в Excel

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

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

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

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

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

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

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



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



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



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



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



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



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



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



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



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



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



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



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

Как видим, решение транспортной задачи в Excel сводится к правильному формированию вводных данных. Сами расчеты выполняет вместо пользователя программа.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

lumpics.ru

Основные задачи в Excel — Excel

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

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

Документы Excel называются книгами. Каждая книга содержит листы. Можно добавить в книгу любое количество листов или создать новую книгу для хранения данных в отдельном месте.

1. Откройте вкладку Файл и выберите команду Создать.

2. В разделе Доступные шаблоны выберите пункт Новая книга.

1. Щелкните пустую ячейку.

   Например, щелкните ячейку A1 на новом листе. Ячейки определяются на листе по строке и столбцу, и A1 — это ячейка, которая находится в месте пересечения первой строки и столбца A.

2. Введите в ячейке текст или число.

3. Чтобы перейти к следующей ячейке, нажмите клавишу ВВОД или TAB.

1. Выделите ячейку или диапазон ячеек, для которых требуется создать границы.

2. На вкладке

   Главная в группе шрифт щелкните стрелку рядом с кнопкой границы и выберите необходимый стиль границы.

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

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

2. На вкладке Главная в группе Шрифт щелкните стрелку рядом с кнопкой Цвет заливки и затем в разделе Цвета темы или Стандартные цвета выберите нужный цвет.

Дополнительные сведения о том, как применить форматирование к на листе ознакомьтесь со статьей Форматирование листа.

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

1. Выберите ячейку, расположенную справа или снизу от чисел, которые необходимо добавить.

2. На вкладке Главная в группе Редактирование нажмите кнопку Автосумма.

   Функция «Сумма» выполнит сложение чисел и отобразит результат в выделенной ячейке.

Использование функции «Автосумма» для суммирования чисел можно найти дополнительные сведения

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

1. Выберите ячейку, а затем введите знак равенства (=).

   Он указывает Excel, что эта ячейка будет содержать формулу.

2. Введите сочетание чисел и знаков операций, таких как «плюс» (+) для сложения, «минус» (-) для вычитания, «звездочка» (*) для умножения и «наклонная черта» (/) для деления.

   Например, введите =2+4, =4-2, =2*4 или =4/2.

3. Нажмите клавишу ВВОД.

   Будет выполнено вычисление.

   Можно также нажать клавиши CTRL+ВВОД, если нужно оставить указатель в активной ячейке.

Дополнительные сведения в статье Создание простой формулы.

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

1. Выделите ячейки с числами, которые необходимо отформатировать.

2. На вкладке Главная щелкните стрелку в поле Общий.

3. Выберите числовой формат.

   

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

Для более полного использования полезных функций Excel можно поместить данные в таблицу. Это позволяет быстро фильтровать и сортировать данные.

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

   Для выделения данных с использованием клавиатуры нажмите клавишу SHIFT и расширьте область выделения с помощью клавиш со стрелками.

2. Нажмите кнопку Экспресс-анализ  в правом нижнем углу выделенного диапазона.

   

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

4. Щелкните стрелку в заголовке одного из столбцов таблицы.

5. Чтобы отфильтровать данные, снимите флажок Выделить все и выберите данные, которые должны отображаться в таблице.

   

6. Для сортировки данных выберите пункт Сортировка от А до Я или Сортировка от Я до А.

   

7. Нажмите кнопку ОК.

Дополнительные сведения читайте в статье Создание или удаление таблицы Excel

Разрешить (функция недоступна в Excel 2016 и Excel 2013 только) средство экспресс-анализ быстро всего номеров. Будь то сумму, среднее значение или подсчет нужный, Excel отображает результаты вычислений рядом с самим или рядом с числа.

1. Выделите ячейки с числами, которые нужно сложить или сосчитать.

2. Нажмите кнопку Экспресс-анализ  в правом нижнем углу выделенного диапазона.

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

Условное форматирование и спарклайны можно выделить наиболее важные данные или отображения тенденций изменения данных. Попробовать, с помощью средства экспресс-анализа (функция недоступна в Excel 2016 и Excel 2013 только) для динамического просмотра.

1. Выделите данные, которые нужно подробно изучить.

2. Нажмите кнопку Экспресс-анализ  в правом нижнем углу выделенного диапазона.

3. Изучите параметры на вкладках Форматирование и Спарклайны, чтобы узнать, как они влияют на данные.

   Например, выберите в коллекции Форматирование цветовую шкалу, чтобы провести различие между высокой, средней и низкой температурами.

4. Если результат предварительного просмотра вас устраивает, щелкните этот вариант.

Узнайте подробнее, как анализировать тенденции в данных с помощью спарклайнов.

Средство экспресс-анализа (функция недоступна в Excel 2016 и Excel 2013 только) рекомендует подходящей диаграммы для имеющихся данных и предоставляет визуальное представление всего несколькими щелчками мышью.

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

2. Нажмите кнопку Экспресс-анализ  в правом нижнем углу выделенного диапазона.

3. Откройте вкладку Диаграммы, проведите указателем мыши по рекомендуемым диаграммам, чтобы просмотреть их, и выберите наиболее подходящую.

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

Узнайте о других способах создания диаграмм.

Чтобы быстро отсортировать данные

1. Выделите диапазон данных, например A1:L5 (несколько строк и столбцов) или C1:C80 (один столбец). В этот диапазон могут входить и заголовки, созданные для обозначения столбцов или строк.

2. Выделите одну ячейку в столбце, в котором нужно выполнить сортировку.

3. Нажмите кнопку для выполнения по возрастанию (от а до я) или от наименьшего числа к наибольшему.

4. Нажмите кнопку , чтобы выполнить сортировку по убыванию (от я до А или от наибольшего числа к наименьшему).

Чтобы выполнить сортировку по определенным условиям

1. Выделите одну любую ячейку в диапазоне, для которого требуется выполнить сортировку.

2. На вкладке » данные » в группе » Сортировка и фильтр » выберите команду Сортировка.

3. Откроется диалоговое окно Сортировка.

4. В списке Сортировать по выберите первый столбец, по которому нужно выполнить сортировку.

5. В списке Сортировка выберите пункт Значения, Цвет ячейки, Цвет шрифта или Значок ячейки.

6. В списке Порядок выберите порядок сортировки. Можно сортировать текст или числа по возрастанию и по убыванию (соответственно от А до Я и от Я до А для текста или от меньшего к большему и от большего к меньшему для чисел).

   Дополнительные сведения о том, как сортировать данные увидеть Сортировка данных в диапазоне или таблице.

1. Выберите данные, которые нужно отфильтровать.

2. На вкладке » данные » в группе Сортировка и фильтр щелкните Фильтр.

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

4. Для выбора значения из списка, снимите флажок (Выделить все). Флажки будет удалена из все флажки. Затем выберите значения, которые нужно просмотреть и нажмите кнопку ОК, чтобы увидеть результаты.

Дополнительные сведения о том, как фильтровать данные увидеть фильтрации данных в диапазоне или таблице.

1. Нажмите кнопку Сохранить на панели быстрого доступа или сочетание клавиш CTRL+S.

   Если документ уже сохранялся, никаких дополнительных действий выполнять не нужно.

2. Если вы сохраняете файл впервые, сделайте следующее.

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

   2. В поле Имя файла введите имя книги.

   3. Нажмите кнопку Сохранить.

1. Откройте вкладку Файл и выберите команду Печать или нажмите клавиши CTRL+P.

2. Для предварительного просмотра страниц нажимайте стрелки Следующая страница и Предыдущая страница.

   Окно предварительного просмотра отображает страницы в черно-белом или цветном виде в зависимости от настроек принтера.

   Если вас не устраивает вид страниц, можно изменить их поля или добавить разрывы страниц.

3. Нажмите кнопку Печать.

1. На вкладке файл нажмите кнопку Параметры и выберите категорию Надстройки.

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

3. В диалоговом окне Надстройки установите флажки надстроек, которые вы хотите использовать и нажмите кнопку ОК.

   Если отображается сообщение о том, он не удается выполнить этой надстройке и предложит по установке, нажмите кнопку Да для установки надстройки в Excel.

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

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

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

support.office.com

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

Решение различных математических задач, используя надстройки «Подбор параметра» и «Поиск решения» в MS Excel.

Цель работы. Изучить:

• надстройку «Подбор параметра» для нахождения корней нелинейных уравнений;

• надстройку «Поиск решения» для нахождения корней систем уравнений.

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

Подбор параметра

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

F(x) = 0,

где функция F(x) определена и непрерывна на интервале [a, b]. Таким образом, можно сказать, что инструмент Подбор параметра служит для нахождения корня уравнения x. В этой надстройке реализован алгоритм метода половинного деления.

Пример 1. Решим уравнение x² – 3 = 0, используя надстройку Подбор параметра.

В ячейку А1 вводится начальное приближение для поиска одного из корней уравнения. Лучше найти его графически, хотя можно подставить и произвольное значение (например, ноль). В ячейку В2 записывается в виде формулы левая часть решаемого уравнения. Диалоговое окно данного инструмента вызывается через меню Данные / Что-если / Подбор параметра и имеет следующий вид (рис. 2.7.1, 2.7.2):

Рис. 2.7.1. Надстройка Подбор параметра

В поле Установить в ячейке вводится ссылка на ячейку, содержащую левую часть уравнения. В поле Значение непосредственно (т.е. без ссылок на ячейки) вводится правая часть уравнения. Причем правая часть уравнения должна обязательно представлять собой конкретное числовое значение. Если правая часть уравнения содержит переменную или какое-либо выражение, то такое уравнение должно быть предварительно преобразовано к равносильному виду (в общем случае, к каноническому виду F(x) = 0). Нажав кнопку ОК, получаем в ячейке А1 значение искомого корня: 1,731856.

Рис. 2.7.2. Надстройка Подбор параметра

Поиск решения

Нелинейные уравнения также можно решать, используя надстройку Поиск решения. Для того чтобы ее подключить, следует в меню Office (рис. 2.7.3) выбрать пункт Параметры Excel (рис. 2.7.4) и в раскрывшемся списке войти в меню Надстройки, далее активировать Поиск решения, установив флажок против пункта Поиск решения (рис. 2.7.5).

Рис. 2.7.3 Кнопка Office

Рис. 2.7.4. Меню Office

Рис. 2.7.5. Надстройки

После нажатия кнопки ОК соответствующий значок появится во вкладке Данные (рис. 2.7.6).

Рис. 2.7.6. значок Поиск решения

Пример 2. Решим уравнение x² – 3 = 0, используя надстройку Поиск решения.

В ячейку А1 заносится начальное приближение корня, в ячейку В1 – левая часть уравнения в виде формулы. Для предыдущего примера она имеет вид =А1*А1-3.

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

В открывшемся диалоговом окне Поиск решения устанавливается целевая ячейка $B$1, равная нулевому значению. В текстовом поле Изменяя ячейки устанавливается адрес $А$1 и нажимается кнопка Выполнить (рис. 2.7.7).

Рис. 2.7.7. Надстройка Поиск решения

В ячейке А1 получается значение корня 1,732051 (рис. 2.7.8).

Рис. 2.7.8. Результаты работы надстройки Поиск решения

Как видим, оно совпало с точностью до 0,001 с найденным ранее значением.

Обращает на себя внимание неточность решения. Мы получаем очень близко приближающиеся к точным, но все же неточные корни уравнения. Это происходит потому, что решение уравнений на вычислительной технике происходит не аналитическими методами, как это делает человек, а специально разработанными методами, получившими название численных. В отличие от аналитических (точных) методов численные методы обладают определенной погрешностью. В Excel с целью повышения точности решения пользователь может уменьшить погрешность вычислений, но при этом может потребоваться увеличение количества итераций. При этом надо помнить, что тем самым увеличивается время на поиск решения. Установленные по умолчанию значения подходят для большинства практических задач, относительная погрешность вычислений составляет 0,001 (рис. 2.7.9).

Рис. 2.7.9. Изменение погрешности

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

Пример 3. Решим систему уравнений, используя надстройку Поиск решения.

Для того, чтобы использовать рассматриваемую надстройку Поиск решения для нахождения решения системы линейных алгебраических уравнений, следует ввести в столбец А начальное приближение для значений всех неизвестных. Пусть это будут нули. В столбец В ввести формулы, описывающие левые части уравнений. В столбец С вводят значения правых частей уравнений. Курсор ставят на ячейку В1 и запускают надстройку Поиск решения. Значение целевой ячейки $B$1устанавливают равным значению ячейки С1. Изменяют значения ячеек столбца А. К ограничениям добавляют все уравнения, кроме первого. Для системы уравнений:

настроенный на показ формул лист Excel с диалоговым окном Поиск решения будут выглядеть так, как это показано на рисунках 2.7.10, 2.7.11.

Рис. 2.7.10. добавление ограничения

Рис. 2.7.11. Поиск решения системы уравнений

Нажав кнопку Выполнить, получается в столбце А значение неизвестных (рис. 2.7.12):

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

.

Рис. 2.7.12. Результаты работы с надстройкой Поиск решения

Задания для выполнения

Варианты заданий для работы приведены в таблице 2.7.1, 2.7.2.

Задание1.

1. Используя надстройку «Подбор параметра», найти все корни уравнения (по вариантам) на отрезке [-2; +2] (табл. 2.7.1).

Таблица 2.7.1

Варианты заданий

№ варианта

Задание

№ варианта

Задание

1

11

2

12

3

13

4

14

5

15

6

16

7

17

8

18

9

19

10

20

Задание 2.

1. Используя надстройку «Поиск решения», решить систему линейных уравнений AX = B (по вариантам) (табл. 2.7.2) и проверить правильность решения в Excel, подставив найденные значения неизвестных в систему уравнений. A – матрица коэффициентов при x1, x2, x3, x4. В- матрица свободных членов уравнений.

Таблица 2.7.2

Варианты заданий

№ варианта

Задание

№ варианта

Задание

1

2

3

4

1

9

2

10

3

11

4

12

5

13

6

14

7

15

8

16

infourok.ru

Решение транспортной задачи в Excel (сбалансированная задача)

• Курсы
• Новости
• Статьи
  • Excel
  • PowerPoint
  • Windows
  • Word
• Заметки
  • Excel
  • PowerPoint
  • Windows
  • Word
  • Другие заметки
• Видео
  • Excel
  • PowerPoint
  • Windows
  • Word
  • Другие видео
• Блог
• Shop
  • Доступ к курсам
  • Услуги

Поиск

• Главная
• Центр обучения
• Справочник
• Поддержка
• Контакт

MSoffice-Prowork.com

• Курсы
• Новости
• Статьи
  • ВсеExcelPowerPointWindowsWord Windows

    Windows 10 19h2 (версия 1903) все изменения и новые функции

    Windows

    Лучшие в октябрьском обновлении Windows 10 (версия 1809)

    Excel

    Горизонтальная сортировка в Excel

    Excel

    Автонумерация внутри составной записи в Excel

• Заметки
  • ВсеExcelPowerPointWindowsWordДругие заметки Excel

    Анимация минигана в MS Office

    Другие заметки

    Как установить шрифты в систему Windows

    Excel

msoffice-prowork.com

Простое решение транспортной задачи в Excel на примере

В этом материале попробуем разобраться, как решить транспортную задачу в Excel. Среда решения – Excel. Данный материал подходит для версий программы: 2007, 2010, 2013, 2016.

Постановка задачи и подготовка таблиц

Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата. Рассмотрим решение на конкретном примере.

Задача

В хозяйстве имеются 5 складов минеральных удобрений и 4 пункта, в которые необходимо доставить удобрения. Потребность каждого пункта в удобрениях различна, а так же запасы на каждом складе ограничены. Требуется определить, с какого склада, в какой пункт поставлять, сколько удобрений для минимализации грузооборота перевозок.

Исходные данные:

Наличие минеральных удобрений (либо иной продукции)  на складах.

Склады	Наличие удобрений, т.
Склад № 1	200
Склад № 2	190
Склад № 3	220
Склад № 4	145
Склад № 5	280

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

Пункты	Потребность в удобрениях
1 пункт	200
2 пункт	150
3 пункт	220
4 пункт	330

Расстояние между складами и пунктами доставки

	Пункт 1	Пункт 2	Пункт 3	Пункт 4
Склад № 1	6	4	5	11
Склад № 2	12	6	4
Склад № 3	15	7	10	4
Склад № 4	9	5	12	5
Склад № 5	3	7	12	11

Данные в таблицах. На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстоянии между этим пунктом доставки и складом. Например, расстояние между 3 пунктом и складом № 3 равно 10 километрам.

Пошаговое решение в Excel

Подготовим таблицы для решения задачи.

Рисунок 1. Изменяемые ячейки.

Значения ячеек в столбце B с третьей по седьмую определяют сумму значения соответствующих строк со столбца C до столбца F.

Например, значение ячейки B3=СУММ(C4:F4)

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

Рисунок 2. Исходная информация в Excel

 

В строке 16 по столбцам C-F определим грузооборот по каждому пункты доставки. Например, для пункта 1 (ячейка С16) это рассчитывается по формуле:

C16==C3*C11+C4*C12+C5*C13+C6*C14+C7*C15

Либо, это можно рассчитать с помощью функции СУММПРОИЗВ:

C16 =СУММПРОИЗВ(C3:C7;C11:C15)

В ячейке B4 находится количество минеральных удобрений, перевозимых со склада № 1 в 1 пункт доставки, а в ячейке C11 — расстояние от склада №1 до 1 пункта доставки. Соответственно первое слагаемое в формуле означает полный грузооборот  по данному маршруту. Вся же формула вычисляет полный грузооборот перевозок минеральных удобрений в 1 пункт доставки.

В ячейке B16 по формуле =СУММ(C16:F16) будет вычисляться общий объем грузооборота минеральных удобрений. Рабочий лист примет следующий вид.

Рисунок 3. Рабочий лист, приготовленный для решения транспортной задачи.

Для решения транспортной задачи воспользуемся процедурой Поиск решения, которая находится на вкладке Данные. Если у вас нет процедуры Поиск решения, необходимо зайти в Параметры Excel -> Надстройки — > Поиск решения.

После выбора данной процедуры на вкладке Данные откроется диалоговое окно.

Рисунок 4. Диалоговое окно Поиск решения.

Выберем целевую ячейку $B$16, установим ее равной минимальному значению, что бы минимизировать значение конечной ячейки, путем изменения влияющих ячеек, изменяя ячейки, выберем диапазон с единицами $C$3:$F$7.

Рисунок 5. Условия для решения транспортной задачи.

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

1. $B$3:$B$7 <= $B$11:$B$15
2. $C$3:$F$7 >= 0
3. $C$8:$F$8 >= $C$10:$F$10

После всех установок нажмем «Выполнить» и получаем результат.

abuzov.ru

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

2.2. Процессоры электронных таблиц

2.2.7. Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое променение находят различные средства Excel.

В этом разделе рассмотрим команды:

1. Подбор параметров для нахождения значения, приводящего к требуемому результату.
2. Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;
3. Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.

Подбор параметров

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

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

• формула для расчета;
• пустая ячейка для искомого значения;
• другие величины, которые используются в формуле.

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

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

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью  команды Сервис/Параметры, вкладка Вычисления)

Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.

Рис. 1.

2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле «Установить в ячейке» в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.

Рис. 2.

3. Введите в текстовое поле Значение число, соответствующее объему продаж — 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.

Рис. 3.

После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу  по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.

Команда Поиск решения

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

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

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

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

1. Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.
2. Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке.
3. Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

Постановка задачи

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

Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе:

1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения (Рис. 3.). Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».
3. Перейдите к полю «Изменяя ячейки» и введите переменные ячейки листа.
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель «Сохранить найденное решение» и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

Рис. 4.

Диспетчер сценариев «что – если»

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

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.

Создание сценария

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

Создание сценариев происходит следующим образом:

1. Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
2. Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
3. Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
4. Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
5. Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

Рис. 5.

Рис. 6.

Просмотр сценария

Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:

1. Выполните команду Сервис/Сценарии. Открывается окно диалога.
2. Выберите из списка сценарий для просмотра.
3. Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
4. Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчетов по сценарию

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

Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчета по сценарию происходит следующим образом:

1. Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
2. Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.

Далее …>>> Тема: 2.3.1. Современные способы организации презентаций средствами PowerPoіnt

www.lessons-tva.info

Какие математические задачи можно решать в Excel?

Excel для математиков Основополагающий вопрос: Какие математические задачи можно решать в Excel? Проблемные вопросы: Как решать в Excel задачи на арифметическую прогрессию? Можно ли в Excel строить графики? Можно ли в Excel решать задачи на движение? Темы презентации: Темы презентации: Арифметическая прогрессия Графика Решение задач на движение Табличный процессор (ТП) — это прикладная программа, предназначенная для организации табличных вычислений на компьютере. Вычислительная таблица, которая создается с помощью ТП, называется электронной таблицей. Первый табличный процессор был создан в 1979 году, предназначался для компьютеров типа Арр1е II и назывался VisiCalc. В 1982 году появился табличный процессор Lotus 1-2-3, предназначенный для IBM РС. Lotus объединил в себе возможности электронных таблиц, деловую графику и некоторые функции реляционной СУБД. Практически все последующие табличные процессоры (Multiplan, QuattroPro, SuperСаlс и другие) поддерживали эти три функции. Одним из самых популярных табличных процессоров сегодня является MS Ехсеl, входящий в состав пакета Microsoft Office. Электронная таблица (ЭТ) — это своеобразная компьютерная технология организации табличных расчетов. Арифметическая прогрессия Цель работы: Получение и представление о арифметической прогрессии. Используемые средства: Табличный процессор MS Excel; Макарычев Ю. Н. и др. Алгебра 9 класс. – М.: Просвещение, 1990. – 272.: ил. Арифметическая прогрессия Пример1. Последовательность задана формулой . Найдите: а) b5; б) b10; в) b50. Решение: Пример2. Вычислить второй, третий, четвертый и пятый члены последовательности (bn), если известно, что Пример2. Вычислить второй, третий, четвертый и пятый члены последовательности (bn), если известно, что а) первый член равен 10, а каждый следующий на 3 больше предыдущего, т. е. b1=10 и bn+1=bn+3; б) первый член равен 40, а каждый следующий равен предыдущему, деленному на 2, т. е. b1=40 и bn+1=bn/2. Решение: а) б) b1=40; bn+1=bn/2. Пример3. Найдем сумму первых сорока членов последовательности (an), заданной формулой an=5n-4. Пример3. Найдем сумму первых сорока членов последовательности (an), заданной формулой an=5n-4. Решение: Последовательность (an) является арифметической прогрессией, так как она задана формулой вида an=kn+b, где k=5 и b=-4. Найдем первый и сороковой члены этой арифметической прогрессии по формуле an=kn+b. Вычислим сумму S40 по формуле: Цель: освоение основных приемов работы с Мастером диаграмм MS Excel. Диаграмма — это средство графического представления количественной информации, предназначенное для сравне­ния значений величин или нескольких значений одной величины, слежения за изменением их значений и так далее. Диаграмма — это средство графического представления количественной информации, предназначенное для сравне­ния значений величин или нескольких значений одной величины, слежения за изменением их значений и так далее. Как правило, диаграммы строятся в прямоугольной системе координат, где по горизонтальной оси ОX откладываются значения независимой переменной (аргумента), а по вертикальной оси ОY — значения зависимой переменной (функции). На один рисунок может быть выведено одновременно несколько диаграмм. При графической обработке числовой информации с помощью табличного процессора следует: 1) указать область данных (блок ячеек), по которым будет строиться диаграмма; 2) определить последовательность выбора данных (по строкам или по столбцам) из выбранного блока. При выборе по столбцам x-координаты берутся из крайнего левого столбца выделенного блока. Остальные столбцы содержат y-координаты диаграмм. По количеству столбцов определяется количество строящихся диаграмм. При выборе по строкам самая верхняя строка выделенного блока является строкой x-координат, остальные строки содержат y-координаты. Постройте, пользуясь Мастером диаграмм 1. График функции y=50*sin(x) 2. Гистограмма (столбчатая диаграмма) графика функции y=50*sin(x) 3. Круговая диаграмма графика функции y=50*sin(x) Круговая диаграмма служит для сравнения значений нескольких величин в одной точке Цель работы: освоение решения задач на движение в среде MS EXCEL Цель работы: освоение решения задач на движение в среде MS EXCEL Используемые средства: Табличный процессор MS Excel; В.П.Смирнов.- задачник по математике для 6-7 классов. М.-1997г. Пример 1. Две машины выехали из пункта А одновременно в противоположные стороны. Первая машина ехала со скоростью 65 км/ч, а вторая – 73 км/ч. Какое расстояние между машинами будет через три часа? Пример 1. Две машины выехали из пункта А одновременно в противоположные стороны. Первая машина ехала со скоростью 65 км/ч, а вторая – 73 км/ч. Какое расстояние между машинами будет через три часа? Решение: Пример 2. Велосипедист и пешеход отправились из пункта А в пункт В одновременно. Скорость первого- 14 км/ч, а второго – 4.5 км/ч. Какое расстояние между ними будет через 4 часа? Пример 2. Велосипедист и пешеход отправились из пункта А в пункт В одновременно. Скорость первого- 14 км/ч, а второго – 4.5 км/ч. Какое расстояние между ними будет через 4 часа? Решение: Скорость теплохода по течению 22,7 км/ч. Против течения- 17,4 км/ч. Найдите: Скорость теплохода по течению 22,7 км/ч. Против течения- 17,4 км/ч. Найдите: скорость течения скорость теплохода ВЫВОД Система Excel мощное средство для решения математических задач и для построения графиков функций.

rpp.nashaucheba.ru

No related posts.