Поиск решения в эксель 2019: Загрузка надстройки «Поиск решения» в Excel

сервис / поиск решения.

Лабораторная работа №4

Решение задач линейного программирования

Цель работы: Изучение возможностей пакета Ms Excel при решении задач линейного программирования. Приобретение навыков решения задач линейного программирования.

В задачах линейного программирования всегда необходимо найти минимум (или максимум) линейной функции многих переменных при линейных ограничениях в виде равенств или неравенств.

В задачи целочисленного программирования добавляется ограничение, что всеxi должны быть целыми.

1. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

Рис. 3. Параметры Excel

Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

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

В окне «Надстройки» установите флажок «Поиск решения» и нажмите Ok (рис. 5). (Если «Поиск решения» отсутствует в списке поля «Надстройки», чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.)

Рис. 5. Активация надстройки «Поиск решения»

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

2. Пример.Решить задачу линейного программирования:

L = 5×1 — 2x3min
— 5×1 — x2 + 2×3 ? 2
— x 1+x3 + x4 ? 5
— 3×1 + 5×4 ? 7

Пусть значения x1, x2, x3, x4 хранятся в ячейках A1:A4, a значение функции L — в ячейке С1 = =5*A1-2*A3.

Введем ограничения:

С2 = -5*A1 — A2 + 2*A3
С3 = -А1 +А3 + А4
С4 = -3*А1 + 5*А4.

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

Выполним команду из главного вкладка «Данные»Поиск решения (рис. 6.1).

В Open Office Calc: Сервис / Поиск решения.

Назначение основных кнопок и окон диалогового окна Поиск решения:

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

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

Устремим целевую функцию в ячейке C1 к минимуму. Для этого введем в поле Установить целевую функцию значение С1 и установим опцию равной минимальному значению.

В поле Изменяя ячейки необходимо указать адреса ячеек, в которых хранятся изменяемые значения. В нашем случае это ячейки А1:А4.

Для добавления ограничений необходимо щелкнуть по кнопке Добавить, появится диалоговое окно Добавить ограничение (рис. 6.2).

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

Щелчок по кнопке OK означает ввод очередного ограничения и возврат к диалоговому окну Поиск решения.

Щелчок по кнопке Добавить вводить очередное ограничение, находясь в окне Добавить ограничение.

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

Щелчок по кнопке OK приведет к появлению в ячейке С1 значения целевой функции L, а в ячейках A1:A4 — значений переменных x1-x4, при которых целевая функция достигает минимального значения.

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

Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». И еще одна маленькая хитрость. Попробуйте уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис.) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение. Можно так же увеличить предельное число итераций.

Увеличение погрешности вычислений

В Open Office Calc:

Статьи к прочтению:

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

Похожие статьи:

Методические указания к выполнению практической работы № 12 на тему «ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)»

Практическая работа № 12

Тема: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)

Цель: изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

Задание 1. Минимизация фонда заработной платы фирмы.

Пусть известно, что для работы фирмы требуется 5-7 курьеров, 8-10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.

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

В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид

N₁ * A₁ * x + N₂ * (A₂ * x + B₂) + …+ N₈ * (A₈ * x + B₈) = Минимум, где N_i — количество работников данной специальности; x зарплата курьера; A_i и В_i – коэффициенты заработной платы сотрудников фирмы.

Порядок работы

1. Запустите редактор электронных таблиц MS Excel и откройте созданный в предыдущей практической работе файл «Штатное расписание».

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

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

Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) «Поиск решения».

Загрузка надстройки «Поиск решения»

1. 1. Щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки.

   2. В поле Управление выберите значение

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

Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный – Минимальному значению.

В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6:$E$7:$D$3 (при задании ячеек E6, Е7 и D3 держите нажатой клавишу Ctrl).

Рис. 1. Задание условий для минимизации фонда заработной

платы

Рис. 2. Добавление ограничений для минимизации фонда

заработной платы

Используя кнопку Добавить в окнах Поиск решения и Добавление ограничений, опишите все ограничения задачи: количество курьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400 (рис. 2). Ограничения наберите в виде

$D$3 > = 1400

$E$6 > = 5

$E$6 < = 7

$E$7 > = 8

$E$7 < = 10.

Активизировав кнопку Параметры, введите параметры поиска, как показано на рис. 3.

Рис. 3. Задание параметров поиска решения по минимизации

фонда заработной платы

Окончательный вид окна Поиск решения приведен на рис. 1.

Запустите процесс поиска решения нажатием кнопки Выполнить. В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение (рис. 4.)

Рис. 4. Сохранение найденного при поиске решения

Решение задачи приведено на рис. 5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

Рис. 5. Минимизация фонда заработной платы

Задание 2. Составление плана выгодного производства

Фирма производит несколько видов продукции из одного и того же сырья – A, B, и C. Реализация продукции А дает прибыль 10 р., В – 15 р., С – 20 р. на единицу изделия.

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

Нормы расхода сырья на производство продукции каждого вида приведены в табл. 1.

Таблица 1

Порядок работы

1. Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу.

2. Создайте расчетную таблицу как на рис. 6. Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).

Значит в ячейку F5 нужно ввести формулу = B5 * $B$9 + C5 * $C$9 + D5 * $D$9.

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

(Общая прибыль по А) = (прибыль на ед.изделий А) * (количество А),

следовательно, в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С),

значит в ячейку Е10 следует ввести формулу = СУММ (В10:D10).

Рис. 6. Исходные данные для задания 2

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

Рис. 7 Задание условий и ограничений для поиска решений

Рис. 8. Задание параметров поиска решений

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек – ячейки количества сырья – (B9:D9).

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

Расход сырья 1< = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А, В, С > = 0.

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

4. Кнопкой Выполнить запустите Поиск решения. Если вы сделали все верно, то решение будет как на рис.9.

5. Сохраните созданный документ под именем «План производства».

Выводы: из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг

продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 р.

Рис. 9. Найденное решение максимизации прибыли при

заданных ограничениях

Контрольные вопросы:

1. Для чего в MS Excel используется надстройка «Поиск решения»?

2. Что такое целевая ячейка?

3. Как задать максимальное из возможных значение целевой ячейки?

4. Для чего при поиске решений используются ограничения?

Как использовать поиск и решение целей в Excel 2019

Когда бы вы использовали Solver в Excel?

С помощью инструмента поиска цели вы нашли значения, которые можно использовать для определения результата. Результатом было заданное значение, которое Excel затем использовал для вычисления другого значения, которое поможет вам определить ваше решение. Это простой инструмент анализа «что-если», который можно использовать для базовых прогнозов, но у вас может быть более сложный сценарий, когда вы не уверены в нескольких переменных, включая вашу цель. Здесь вам пригодится инструмент Solver.

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

Добавление решателя в Excel

Поскольку Solver — это надстройка Microsoft, вы можете загрузить и добавить его прямо из интерфейса Excel 2019. Щелкните вкладку «Файл», и в нижней части этого окна вы найдете кнопку «Параметры».

(кнопка параметров)

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

(конфигурации надстроек)

В этом окне вы можете увидеть список надстроек, установленных в Excel.Надстройки доступны для всех приложений Microsoft Office при условии, что их можно использовать в рабочем процессе приложения. В этом списке найдите «Надстройку Solver». Если найдете, значит, он уже установлен. Если вы не видите его в списке, нажмите «Перейти» рядом с раскрывающимся списком «Управление» с выбранными «Надстройками Excel». Откроется другое окно конфигурации.

(установка надстроек)

Установите флажок рядом с надстройками, которые вы хотите установить. В этом примере отметьте «Надстройка решателя» и нажмите «ОК».«После того, как вы нажмете« ОК », Excel 2019 установит надстройку« Решатель », и теперь ее можно будет использовать с инструментом« Поиск цели ». Excel добавляет кнопку инструмента« Решатель »в раздел« Анализ »вкладки меню« Данные ».

(кнопка решателя)

Работа с Solver Tool

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

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

(ячейка формулы)

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

(конфигурация решателя)

В окне конфигурации есть несколько параметров, в которых вы устанавливаете значения вашего сценария. Первое поле ввода — это значение «Установить цель». В этом примере ячейка формулы расположена в ячейке I4, поэтому она вводится в текстовое поле ввода. Любые данные должны быть на одном листе, чтобы быть допустимыми параметрами для инструмента Solver.

Следующий набор параметров — это раздел «Кому». Этот раздел позволяет вам определить, хотите ли вы создать сценарий с максимальным значением, минимальным значением или точным значением («Value Of»).В этом примере будет использоваться минимальное число доходов, потому что вопрос заключается в том, какая часть комиссии может быть применена до того, как показатели продаж станут слишком низкими для обеспечения требуемого чистого дохода.

В разделе «Изменяя ячейки переменных» вы устанавливаете ячейки для принятия решений. У вас может быть до 200 ячеек решений с помощью инструмента Solver. Этот раздел делает инструмент Solver более гибким и мощным, чем простой расчет поиска цели. С этими значениями Excel определит, какие значения можно использовать для достижения цели, установленной в разделе «Установить цель».В этом примере будут использоваться десять значений решения. Значения будут от 1 до 10.

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

Чтобы добавить ограничение, нажмите кнопку «Добавить» в разделе «Субъект ограничений».

(Добавить ограничение)

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

Прежде чем вы закончите настройку инструмента Solver, вы должны выбрать метод решения. Это раскрывающееся меню находится в разделе «Выберите метод решения» в разделе ограничений. Тип решающего метода, который вы выберете, определит результат.

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

GRG Нелинейный : «GRG» в названии этого алгоритма означает «Обобщенный уменьшенный градиент». Это самый быстрый из всех трех алгоритмов, поэтому, если у вас сложные ограничения и требования, этот алгоритм может быть лучшим решением. Он также наименее оптимален, чем нелинейный алгоритм GRG, поэтому имеет обратную сторону.GRG Nonlinear не найдет глобальных оптимальных решений, если начальные факторы не оптимальны. Любые функции ЕСЛИ, ВПР или абсолютные функции также могут вызвать проблемы для этого метода.

Evolutionary : алгоритм Excel Evolutionary намного точнее и надежнее, чем алгоритм GRG Nonlinear. Эволюционный алгоритм создает набор случайных чисел. Эти численность населения затем сузились до другого набора численности населения. Каждый суженный набор численности населения называется потомком, и каждое потомство сокращается до тех пор, пока Excel не сможет найти лучшее решение.Это решение намного более точное, но опять же недостатком этого алгоритма является то, что он занимает гораздо больше времени, чем другие. Если у вас есть много времени, чтобы дождаться решения, это алгоритм, который вы должны использовать с инструментом Solver.

Simplex LP : алгоритм Simplex LP очень ограничен, но он полезен, если вы ищете только линейные решения. Это быстрый метод из-за его ограничений, но его преимущество также в том, что он находит глобальное решение, которое, вероятно, приведет к желаемому результату.Когда у вас есть очень простые решения, основанные на линейных значениях, вам следует использовать этот, поскольку он является наиболее точным из всех других алгоритмов.

С помощью этих основных пояснений вы можете выбрать метод расчета. Если вы просто изучаете, как работает Solver, вы можете оставить его как вариант GRG Nonlinear по умолчанию, так как он найдет решение, и он самый быстрый без линейных ограничений, которые Simplex LP имеет со своими требованиями.

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

(окно Решающих решений)

Вверху окна результатов сообщается, найдено ли решение. Если он не был найден, вы можете установить флажок «Вернуться в диалоговое окно параметров решателя», чтобы вернуться в окно конфигурации и ввести другие значения.Решающая программа может не найти решение из-за введенных вами параметров, или решение, которое вы хотите, невозможно. В этом преимущество Решателя — он может помочь вам определить, возможен ли результат, и если он возможен, то как вы можете достичь его с помощью алгоритмов и чисел.

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

В разделе «Отчеты» показано, что предлагает Solver при распечатке отчетов. Выберите один из отчетов, который хотите просмотреть. Для этого примера выбран отчет «Ответ». Когда вы закончите выбирать параметры, нажмите кнопку «ОК», чтобы увидеть результаты. Когда выбран отчет, создается новый рабочий лист, на котором вы можете увидеть результаты своего решения Solver. Рабочий лист называется «Отчет об ответах 1». Щелкните вкладку, чтобы просмотреть свой отчет.

(Отчет об ответе)

В отчете «Ответ» вы можете увидеть «Slack», который дает вам ответ о том, сколько комиссионных вы можете заплатить, прежде чем достигнете порога, установленного в окне конфигурации Solver.Этот отчет полезен, когда у вас есть несколько сценариев и вам необходимо просмотреть каждый в отдельных отчетах. В отчете об ответах показаны параметры, ограничения и целевые значения ячеек, которые вы можете просмотреть по сравнению с решением, предложенным Решателем.

Если вы хотите увидеть другой отчет, нажмите кнопку «Решатель» еще раз, чтобы открыть окно конфигурации. Нажмите «Решить», и откроется окно результатов. На этот раз выберите параметр «Чувствительность» в разделе «Отчеты» и нажмите «ОК».«Excel закроет окно и сгенерирует отчет, который отобразится на следующей вкладке. Вкладка автоматически получит имя« Отчет о чувствительности 1 ». Щелкните вкладку, чтобы просмотреть этот отчет.

(Отчет о чувствительности)

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

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

(отчет о лимитах)

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

Инструмент Solver — это продвинутый способ поиска решений ваших вопросов. Вы можете использовать простой инструмент Excel Goal Seek, но когда вам нужны сложные решения с многочисленными ограничениями и факторами, которые могут дать результат, вы используете инструмент Solver.В Excel 2019 есть этот инструмент в качестве бесплатной надстройки, которую вы можете использовать.

Определите и решите проблему с помощью Solver

Примечание. В версиях Solver до Excel 2007 целевая ячейка называлась «целевой ячейкой», а ячейки переменной решения — «изменяемыми ячейками» или «регулируемыми ячейками». В надстройку Solver для Excel 2010 было внесено множество улучшений, поэтому, если вы используете Excel 2007, ваш опыт будет немного другим.

В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму выручки от продаж, связанные с этим расходы и прибыль. Решатель может изменять квартальные бюджеты на рекламу (ячейки переменной решения B5: C5) до общего бюджетного ограничения в 20 000 долларов (ячейка F5), пока общая прибыль (ячейка цели F7) не достигнет максимально возможной суммы.Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы F7, = СУММ (Прибыль за 1 квартал: Прибыль за 2 квартал).

1. Ячейки переменных

2. Ячейка с ограничениями

3. Ячейка цели

После запуска Solver новые значения будут следующими.

1. На вкладке Data в группе Analysis щелкните Solver .
   

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

3. org/ListItem»>

   Выполните одно из следующих действий:

   • Если вы хотите, чтобы значение целевой ячейки было как можно большим, щелкните Макс. .

   • Если вы хотите, чтобы значение целевой ячейки было как можно меньше, щелкните Мин .

   • Если вы хотите, чтобы целевой ячейкой было определенное значение, щелкните Значение , а затем введите значение в поле.

   • В поле путем изменения ячеек переменной введите имя или ссылку для каждого диапазона ячеек переменной решения.Разделите несмежные ссылки запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Вы можете указать до 200 ячеек переменных.

4. В поле Subject to the Constraints введите любые ограничения, которые вы хотите применить, выполнив следующие действия:

   1. В диалоговом окне Solver Parameters нажмите Add .

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

   3. org/ListItem»>

      Щелкните отношение ( <= , = , > = , int , bin или dif ), которое вы хотите между указанной ячейкой и ограничением.Если вы щелкните int , целое число появится в поле Constraint . Если вы щелкните bin , binary появится в поле Constraint . Если вы щелкните diff , alldifferent появится в поле Constraint .

   4. Если вы выбрали <=, = или> = для отношения в поле Ограничение , введите число, ссылку на ячейку или имя или формулу.

   5. Выполните одно из следующих действий:

      • Чтобы принять ограничение и добавить другое, нажмите Добавить .

      • Чтобы принять ограничение и вернуться в диалоговое окно Solver Parameter s, нажмите OK .
        Примечание Вы можете применить отношения int , bin и diff только в ограничениях на ячейки переменных решения.

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

   6. В диалоговом окне Solver Parameters щелкните ограничение, которое вы хотите изменить или удалить.

   7. Щелкните Изменить и затем внесите изменения или щелкните Удалить .

5. Щелкните Решите и выполните одно из следующих действий:

   • Чтобы сохранить значения решения на листе, в диалоговом окне Solver Results щелкните Keep Solver Solution .

   • Чтобы восстановить исходные значения до того, как вы нажали Решить , нажмите Восстановить исходные значения .

   • Вы можете прервать процесс решения, нажав Esc. Excel пересчитывает лист с последними значениями, найденными для ячеек переменной решения.

   • Чтобы создать отчет на основе вашего решения после того, как Solver найдет решение, вы можете щелкнуть тип отчета в поле Reports , а затем нажать OK . Отчет создается на новом листе в вашей книге. Если Solver не находит решения, доступны только определенные отчеты или нет отчетов.

   • Чтобы сохранить значения ячеек переменной решения в виде сценария, который можно отобразить позже, щелкните Сохранить сценарий в диалоговом окне Solver Results , а затем введите имя сценария в поле Имя сценария .

1. После определения проблемы щелкните Параметры в диалоговом окне Параметры решателя .

2. org/ListItem»>

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

3. В диалоговом окне Solver Parameters нажмите Solve .

4. В диалоговом окне Show Trial Solution выполните одно из следующих действий:

   • Чтобы остановить процесс решения и отобразить диалоговое окно Solver Results , щелкните Stop .

   • Чтобы продолжить процесс решения и отобразить следующее пробное решение, щелкните Продолжить .

1. В диалоговом окне Solver Parameters щелкните Options .

2. Выберите или введите значения для любого из параметров на вкладках All Methods , GRG Nonlinear и Evolutionary в диалоговом окне.

1. org/ListItem»>

   В диалоговом окне Solver Parameters нажмите Load / Save .

2. Введите диапазон ячеек для области модели и нажмите Сохранить или Загрузить .

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

   Совет: Вы можете сохранить последние выборы в диалоговом окне Solver Parameters с рабочим листом, сохранив книгу.Каждый рабочий лист в книге может иметь свой собственный выбор Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для рабочего листа, нажав Загрузить / сохранить , чтобы сохранить проблемы по отдельности.

Вы можете выбрать любой из следующих трех алгоритмов или методов решения в диалоговом окне Solver Parameters :

• Обобщенный приведенный градиент (GRG) Нелинейный Используется для задач с гладкой нелинейностью.

• LP Simplex Используется для линейных задач.

• Evolutionary Используется для негладких задач.

В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму выручки от продаж, связанные с этим расходы и прибыль. Решатель может изменять квартальные бюджеты на рекламу (ячейки переменной решения B5: C5) до общего бюджетного ограничения в 20 000 долларов (ячейка D5), пока общая прибыль (ячейка цели D7) не достигнет максимально возможной суммы. Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы D7, = СУММ (Прибыль за 1 квартал: Прибыль за 2 квартал).

Переменные ячейки

Ограниченная ячейка

Объективная ячейка

После запуска Solver новые значения будут следующими.

1. В Excel 2016 для Mac: щелкните Data > Solver .

   В Excel для Mac 2011: щелкните вкладку Data , в разделе Analysis щелкните Solver .

2. В Установить цель введите ссылку на ячейку или имя для целевой ячейки.

   Примечание. Целевая ячейка должна содержать формулу.

3. Выполните одно из следующих действий:

   С по	Сделай это
   Сделайте значение целевой ячейки как можно большим	Нажмите Макс .
   Сделайте значение целевой ячейки как можно меньше	Щелкните Min .
   Установить целевую ячейку на определенное значение	Щелкните Value Of , а затем введите значение в поле.

4. В поле путем изменения ячеек переменной введите имя или ссылку для каждого диапазона ячеек переменной решения. Разделите несмежные ссылки запятыми.

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

5. В поле Subject to the Constraints добавьте любые ограничения, которые вы хотите применить.

   Чтобы добавить ограничение, выполните следующие действия:

   1. В диалоговом окне Solver Parameters нажмите Add .

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

   3. Во всплывающем меню взаимосвязи <= выберите нужную взаимосвязь между ячейкой, на которую указывает ссылка, и ограничением.Если вы выбрали <= , = или > = , в поле Ограничение введите число, ссылку на ячейку или имя или формулу.

      Примечание. В ограничениях для ячеек переменных решения можно применять только отношения int, bin и dif.

   4. Выполните одно из следующих действий:

   С по	Сделай это
   Примите ограничение и добавьте еще	Нажмите Добавить .
   Примите ограничение и вернитесь в диалоговое окно Solver Parameters	Нажмите ОК .

6. Щелкните Решить , а затем выполните одно из следующих действий:

   С по	Сделай это
   Сохраните значения решения на листе	Щелкните Keep Solver Solution в диалоговом окне Solver Results .
   Восстановить исходные данные	Нажмите Восстановить исходные значения .

Примечания:

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

2. Чтобы создать отчет на основе вашего решения после того, как Solver найдет решение, вы можете щелкнуть тип отчета в поле Reports , а затем нажать OK . Отчет создается на новом листе в вашей книге. Если Solver не находит решения, возможность создания отчета недоступна.

3. Чтобы сохранить значения настраиваемых ячеек в качестве сценария, который можно отобразить позже, щелкните Сохранить сценарий в диалоговом окне Solver Results , а затем введите имя сценария в поле Имя сценария .

1. В Excel 2016 для Mac: щелкните Data > Solver .

   В Excel для Mac 2011: щелкните вкладку Data , в разделе Analysis щелкните Solver .

2. После определения проблемы в диалоговом окне Solver Parameters щелкните Options .

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

4. В диалоговом окне Solver Parameters нажмите Solve .

5. В диалоговом окне Show Trial Solution выполните одно из следующих действий:

   С по	Сделай это
   Остановите процесс решения и отобразите диалоговое окно Solver Results	Нажмите Остановить .
   Продолжить процесс решения и отобразить следующее пробное решение	Нажмите Продолжить .

1. В Excel 2016 для Mac: щелкните Data > Solver .

   В Excel для Mac 2011: щелкните вкладку Data , в разделе Analysis щелкните Solver .

2. Щелкните Options , а затем в диалоговом окне Options или Solver Options выберите один или несколько из следующих параметров:

   С по	Сделай это
   Установить время решения и количество итераций	На вкладке Все методы в разделе Пределы решения в поле Максимальное время (секунды) введите количество секунд, которое вы хотите отвести на время решения.Затем в поле Iterations введите максимальное количество итераций, которое вы хотите разрешить. Примечание: Если процесс решения достигает максимального времени или количества итераций, прежде чем Solver найдет решение, Solver отображает диалоговое окно Показать пробное решение .
   Установить степень точности	На вкладке Все методы в поле Constraint Precision введите желаемую степень точности.Чем меньше число, тем выше точность.
   Установить степень схождения	На вкладке GRG Nonlinear или Evolutionary в поле Convergence введите величину относительного изменения, которое вы хотите допустить в последних пяти итерациях, прежде чем Поиск решения остановится с решением.Чем меньше число, тем меньше допустимое относительное изменение.

3. Нажмите ОК .

4. В диалоговом окне Solver Parameters нажмите Solve или Close .

1. В Excel 2016 для Mac: щелкните Data > Solver .

   В Excel для Mac 2011: щелкните вкладку Data , в разделе Analysis щелкните Solver .

2. Щелкните Загрузить / сохранить , введите диапазон ячеек для области модели, а затем щелкните Сохранить или Загрузить .

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

   Совет: Вы можете сохранить последний выбор в диалоговом окне Solver Parameters с листом, сохранив книгу. Каждый лист в книге может иметь свой собственный выбор Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для листа, нажав Загрузить / сохранить , чтобы сохранить проблемы по отдельности.

1. В Excel 2016 для Mac: щелкните Data > Solver .

   В Excel для Mac 2011: щелкните вкладку Data , в разделе Analysis щелкните Solver .

2. Во всплывающем меню «Выбор метода решения » выберите один из следующих вариантов:

Примечание: Права на части программного кода Solver принадлежат компании Frontline Systems, Inc., 1990-2010 гг., Авторское право на части принадлежит компании Optimal Methods, Inc., 1989 г.

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

Если у вас есть классическое приложение Excel, вы можете использовать кнопку Открыть в Excel , чтобы открыть книгу и использовать надстройку Solver.

в Excel — Easy Excel Tutorial

Загрузите надстройку Solver | Сформулируйте модель | Метод проб и ошибок | Решите Модель

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

Загрузите надстройку Solver

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

1. На вкладке Файл щелкните Параметры.

2. В разделе «Надстройки» выберите «Надстройка решателя» и нажмите кнопку «Перейти».

3. Отметьте Надстройку Solver и нажмите OK.

4. Вы можете найти Решатель на вкладке «Данные» в группе «Анализ».

Сформулируйте модель

Модель , которую мы собираемся решить, выглядит в Excel следующим образом.

1. Чтобы сформулировать эту модель линейного программирования, ответьте на следующие три вопроса.

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

б. Что сдерживает эти решения? Ограничения здесь заключаются в том, что объем капитала и хранилища, используемых продуктами, не может превышать ограниченный объем доступного капитала и хранилища (ресурсов).Например, на каждый велосипед используется 300 единиц капитала и 0,5 единицы хранения.

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

2. Чтобы облегчить понимание модели, создайте следующие именованные диапазоны.

3.Вставьте следующие три функции СУММПРОИЗВ.

Объяснение: Сумма использованного капитала равна сумме из диапазона C7: E7 и OrderSize. Объем используемой памяти равен сумме из диапазона C8: E8 и OrderSize. Общая прибыль равна произведению UnitProfit и OrderSize.

Метод проб и ошибок

С этой формулой становится легко анализировать любой пробный раствор.

Например, если мы заказываем 20 велосипедов, 40 мопедов и 100 детских сидений, общее количество используемых ресурсов не превышает количества доступных ресурсов.Это решение имеет общую прибыль 19000.

Необязательно использовать метод проб и ошибок. Далее мы опишем, как можно использовать Excel Solver для быстрого поиска оптимального решения.

Решите модель

Чтобы найти оптимальное решение , выполните следующие шаги.

1. На вкладке «Данные» в группе «Анализ» щелкните Решатель .

Введите параметры решателя (читайте дальше).Результат должен соответствовать изображенному ниже.

У вас есть выбор: ввести имена диапазонов или щелкнуть ячейки в электронной таблице.

2. Введите TotalProfit для цели.

3. Щелкните Макс.

4. Введите размер заказа для изменяющихся ячеек переменных.

5. Щелкните Добавить, чтобы ввести следующее ограничение.

6. Отметьте «Сделать неограниченные переменные неотрицательными» и выберите «Simplex LP».

7. Наконец, нажмите «Решить».

Результат:

Оптимальное решение:

Вывод: оптимально заказать 94 велосипеда и 54 мопеда. Это решение дает максимальную прибыль 25600. Это решение использует все доступные ресурсы.

Решение задач с линейным программированием и Excel

Подтверждение стоимости

Линейное программирование, продемонстрированное применением функции Excel Solver, представляет собой жизнеспособный и экономичный инструмент для анализа финансовых и операционных проблем с множеством переменных.

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

Ограничения линейного программирования

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

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

Кларенс Го, Калифорния (Сингапур), доктор философии , доцент кафедры бухгалтерского учета (практика) и директор по профессиональному развитию Школы бухгалтерского учета Сингапурского университета менеджмента. Чтобы прокомментировать эту статью или предложить идею для другой статьи, свяжитесь с Джеффом Дрю, старшим редактором журнала FM , по адресу [email protected].

Excel 2016 и шпаргалка 2019

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

Excel, конечно же, является частью пакета инструментов Microsoft Office для повышения производительности. Microsoft продает Office по двум моделям: физические и юридические лица могут заплатить за лицензию на программное обеспечение заранее и владеть ею навсегда (компания называет это «бессрочной» версией пакета) или они могут приобрести подписку на Office 365, что означает, что они доступ к программному обеспечению только до тех пор, пока они продолжают платить абонентскую плату.

Когда вы покупаете бессрочную версию пакета, скажем, Office 2016 или Office 2019, его приложения никогда не получат новые функции, тогда как приложения Office 365 постоянно обновляются новыми функциями.(Дополнительные сведения см. В разделе «В чем разница между Microsoft Office 2019 и Office 365?»)

Эта шпаргалка познакомит вас с функциями, которые были представлены в Excel 2016 и Excel 2019, версиях с бессрочной лицензией. Excel входит в состав Office 2016 и Office 2019 соответственно. В Office 365 Excel имеет все эти функции, а также несколько других. Если у вас или вашей организации есть подписка на Office 365, ознакомьтесь с нашей отдельной памяткой по Excel для Office 365, чтобы узнать обо всех последних функциях.

Большинство советов в этой статье применимы как к Excel 2016, так и к 2019 для Windows. Ближе к концу находится раздел только для Excel 2019.

Поделитесь этой историей: ИТ-специалисты, мы надеемся, что вы передадите это руководство своим пользователям, чтобы помочь им научиться максимально эффективно использовать Excel 2016 и 2019.

Используйте ленту

Лента Интерфейс, который вы знали и любили (или, возможно, ненавидели) в более ранних версиях Excel, не сильно изменился в Excel 2016 или 2019.Поскольку лента включена в приложения пакета Office начиная с Office 2007, мы предполагаем, что вы уже знакомы с тем, как она работает. Если вам нужно что-то напомнить, см. Нашу шпаргалку по Excel 2010.

Как и в Excel 2013, лента в Excel 2016 и 2019 имеет плоский вид, более чистый и менее загроможденный, чем в Excel 2010 и 2007. Лента 2016 и 2019 меньше, чем в Excel 2013, строка заголовка сплошного зеленого цвета. вместо белого, а текст на вкладках ленты («Файл», «Главная», «Вставка» и т. д.) представляет собой сочетание верхнего и нижнего регистра, а не только заглавных.Но он по-прежнему работает так же, и вы найдете большинство команд в тех же местах, что и в более ранних версиях.

Лента не сильно изменилась по сравнению с Excel 2013. (Щелкните изображение, чтобы увеличить.)

Чтобы узнать, какие команды находятся на каких вкладках ленты, загрузите наш краткий справочник по лентам Excel 2016 и 2019. Также ознакомьтесь с изящной новой функцией Tell Me, описанной ниже.

Как и в предыдущих версиях Excel, если вы хотите, чтобы лента исчезла, нажмите Ctrl-F1.Чтобы он снова появился, нажмите Ctrl-F1, и он вернется.

У вас есть и другие варианты отображения ленты. Чтобы перейти к ним, щелкните значок «Параметры отображения ленты» в правом верхнем углу экрана, слева от значков, чтобы свернуть и развернуть PowerPoint. Появится раскрывающееся меню с тремя параметрами:

• Автоматически скрывать ленту: Это скрывает всю ленту, как вкладки, так и команды под ними. Чтобы снова отобразить ленту, щелкните в верхней части PowerPoint.
• Показать вкладки: Показывает вкладки, но скрывает команды под ними. Это то же самое, что и нажатие Ctrl-F1. Чтобы отобразить команды под вкладками, когда они скрыты, нажмите Ctrl-F1, щелкните вкладку или щелкните значок отображения ленты и выберите «Показать вкладки и команды».
• Показать вкладки и команды: При выборе этого параметра отображаются как вкладки, так и команды.

И если по какой-то причине этот приятный зеленый цвет в строке заголовка вам не по вкусу, вы можете сделать его белым или серым.(В Excel 2019 есть также черный вариант.) Для этого выберите Файл> Параметры> Общие . В разделе «Персонализируйте свою копию Microsoft Office» щелкните стрелку вниз рядом с темой Office и выберите Темно-серый или Белый (или Черный) в раскрывающемся меню. Чтобы строка заголовка снова стала зеленой, выберите вариант «Цветной» из раскрывающегося списка. Прямо над меню темы Office находится раскрывающееся меню «Фон Office» — здесь вы можете выбрать отображение шаблона, например печатной платы или кругов и полос в строке заголовка.

Вы можете изменить зеленую строку заголовка Excel: в разделе «Персонализируйте свою копию Microsoft Office» щелкните стрелку вниз рядом с темой Office и выберите цвет. (Щелкните изображение, чтобы увеличить.)

Есть очень полезная функция в том, что Microsoft называет закулисной областью, которая появляется при нажатии на кнопку «Файл» на ленте: если вы нажмете «Открыть» или «Сохранить как» в меню слева, вы увидите облачные сервисы, к которым вы подключились. ваша учетная запись Office, например SharePoint и OneDrive.Теперь под каждым местоположением отображается связанный с ним адрес электронной почты. Это очень полезно, если вы используете облачную службу с несколькими учетными записями, например, если у вас есть одна учетная запись OneDrive для личного использования, а другая — для бизнеса. Вы сможете сразу увидеть, что есть что.

Область за кулисами показывает, какие облачные службы вы подключили к своей учетной записи Office. (Щелкните изображение, чтобы увеличить.)

Выполняйте задачи быстро с Tell Me

Excel никогда не был самым удобным для пользователя приложением, и в нем так много мощных функций, что использовать его может быть непросто.Microsoft упростила эту задачу с помощью функции Tell Me в Excel 2016 и 2019, которая позволяет легко добраться даже до скрытых инструментов.

Чтобы использовать его, щелкните текст «Скажите, что вы хотите сделать» справа от вкладки «Просмотр» на ленте. (Поклонники клавиатуры могут вместо этого нажать Alt-Q.) Затем введите задачу, которую хотите выполнить, например «создать сводную таблицу». Вы получите меню, в котором показаны возможные варианты выполнения задачи. В этом случае основным результатом является прямая ссылка на форму для создания сводной таблицы — выберите ее, и вы сразу же начнете создавать сводную таблицу, не переходя сначала на вкладку «Вставка» ленты.

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

Функция Tell Me упрощает выполнение практически любых задач. (Щелкните изображение, чтобы увеличить.)

Даже если вы считаете себя жокеем электронных таблиц, вам стоит попробовать Tell Me. Это значительно экономит время и намного эффективнее, чем поиск команды через ленту.Также полезно то, что он запоминает функции, которые вы ранее нажимали в поле, поэтому, когда вы нажимаете на него, вы сначала видите список предыдущих задач, которые вы искали. Это гарантирует, что задачи, которые вы часто выполняете, всегда будут под рукой. Кроме того, он делает задачи, которые вы редко выполняете, в пределах легкой досягаемости.

Используйте Smart Lookup для онлайн-исследований

Еще одна новая функция, Smart Lookup, позволяет вам проводить исследования, пока вы работаете с электронной таблицей. Щелкните правой кнопкой мыши ячейку со словом или группой слов и в появившемся меню выберите Smart Lookup.

При этом Excel использует поисковую систему Microsoft Bing для поиска слова или слов в Интернете, а затем отображает определения, любые связанные записи Википедии и другие результаты из Интернета на панели интеллектуального поиска, которая появляется справа. Щелкните любую ссылку результата, чтобы открыть в браузере всю страницу. Если вам просто нужно определение слова, щелкните вкладку «Определить» на панели. Если вам нужна дополнительная информация, щелкните вкладку «Обзор» на панели.

Smart Lookup удобен для поиска общей информации, такой как определения финансовых терминов.(Щелкните изображение, чтобы увеличить.)

Для общих терминов, таких как «срок окупаемости» или «рентабельность инвестиций», он работает хорошо. Но не ожидайте, что Smart Lookup будет исследовать финансовую информацию, которую вы, возможно, захотите поместить в свою электронную таблицу, по крайней мере, исходя из моего опыта. Например, когда я провел интеллектуальный поиск по «уровню инфляции во Франции в 2016 году», я получил результаты футбольного турнира УЕФА Евро-2016 и другую информацию, говорящую мне, что 2016 год был високосным. И когда я поискал по запросу «Производство стали в США», Smart Lookup нашел в Википедии запись о Соединенных Штатах.

Обратите внимание, что для использования Smart Lookup в Excel или любом другом приложении Office вам может потребоваться сначала включить функцию интеллектуальных служб Microsoft, которая собирает ваши условия поиска и некоторый контент из ваших электронных таблиц и других документов. (Если вас беспокоит конфиденциальность, вам нужно взвесить, стоит ли этот удар о конфиденциальности удобства проведения исследования прямо в приложении.) Если вы не включили его, вы увидите экран, когда нажмете Smart Lookup просит вас включить его.Как только вы это сделаете, он будет включен во всех ваших приложениях Office.

Диаграммы новые типы диаграмм

Таблицы — это не только необработанные данные, но и диаграммы. Диаграммы отлично подходят для визуализации и представления данных, а также для получения из них аналитической информации. С этой целью в Excel 2016 есть шесть новых типов диаграмм, в том числе, в первую очередь, гистограмма (часто используется в статистике), «водопад», который эффективно показывает текущие финансовые итоги, и иерархическую древовидную карту, которая помогает находить закономерности в данных.(В Excel 2019 есть еще два новых типа диаграмм, о которых мы поговорим позже.) Обратите внимание, что новые диаграммы доступны только в том случае, если вы работаете с документом .xlsx. Если вы используете старый формат .xls, вы их не найдете.

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

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

Это шесть новых типов диаграмм:

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

Древовидная диаграмма позволяет легко сравнивать категории и подкатегории верхнего уровня в одном представлении. (Щелкните изображение, чтобы увеличить.)

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

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

Диаграмма солнечных лучей показывает иерархические данные, такие как категории и подкатегории книг, в виде многоуровневой круговой диаграммы. (Щелкните изображение, чтобы увеличить.)

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

Каскадная диаграмма показывает промежуточную сумму положительных и отрицательных вкладов, таких как доходы и расходы, в окончательную чистую стоимость.(Щелкните изображение, чтобы увеличить.)

Гистограмма. Этот вид диаграммы показывает частоты в наборе данных. Например, он может показать количество книг, проданных в определенном ценовом диапазоне в книжном магазине.

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

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

Обратите внимание, что диаграмма Парето не отображается при выборе Вставить> Рекомендуемые диаграммы> Все диаграммы . Чтобы использовать его, сначала выберите данные, которые вы хотите построить в диаграмме, затем выберите Вставить> Вставить статистическую диаграмму и в разделе «Гистограмма» выберите Парето.

На диаграмме Парето или отсортированной гистограмме восходящая линия представляет совокупный общий процент измеряемых элементов. В этом примере легко увидеть, что более 80% прибыли книжного магазина связано с тремя проблемами. (Щелкните изображение, чтобы увеличить.)

Коробка и ус. Эта диаграмма, как гистограмма, показывает частоты в наборе данных, но обеспечивает более глубокий анализ, чем гистограмма. Например, в книжном магазине он может показывать распределение цен на книги разных жанров.В показанном здесь примере каждый «прямоугольник» представляет собой от первого до третьего квартиля цен на книги этого жанра, а «усы» (линии, идущие вверх и вниз от прямоугольника) показывают верхний и нижний диапазон цен. Выбросы, которые оцениваются за пределами усов, показаны точками, средняя цена для каждого жанра показана горизонтальной линией в рамке, а средняя цена показана знаком x.

Box & Whisker могут отображать подробности о диапазонах данных, такие как от первого до третьего квартиля в «прямоугольниках», медиана и среднее значение внутри прямоугольников, верхний и нижний диапазон с «усами» и выбросы с точками.(Щелкните изображение, чтобы увеличить.)

Дополнительные сведения о новых типах диаграмм см. В статьях PCWorld «Что делать с новыми стилями диаграмм в Excel 2016: Treemap, Sunburst и Box & Whisker» и «Диаграммы Excel 2016: как использовать новые форматы Парето, гистограммы и водопада» . »

Совместная работа в реальном времени с помощью Excel Online

Когда был выпущен Office 2016, самой громкой новой функцией была совместная работа в режиме реального времени, которая позволяла людям одновременно работать над документами, где бы они ни находились, при условии, что у них есть подключение к Интернету.(Microsoft называет это «совместным редактированием».) Когда вы сотрудничаете с другими вживую, каждый, у кого есть доступ к документу, может работать над ним одновременно, и каждый видит, что делают все остальные, когда они редактируют.

Но Excel остался в стороне для совместной работы. Только Word, PowerPoint и OneNote имели эту функцию, при этом Microsoft заявила, что в неопределенное время Excel получит возможность совместной работы в реальном времени.

В июле 2017 года Microsoft наконец-то развернула совместную работу в реальном времени для настольного клиента Excel, но только для подписчиков Office 365.(См. «Как использовать новые функции совместной работы Excel в реальном времени», где подробно описано, как их использовать.)

Многие люди (включая меня) ожидали, что Excel 2019 получит функции совместного редактирования, но это не так. Чтобы получить возможность совместной работы в настольном клиенте Excel, вы должны быть подписчиком Office 365.

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

Для совместной работы с помощью онлайн-версии Excel файл, которым вы хотите поделиться, должен находиться в OneDrive, OneDrive для бизнеса, SharePoint или Dropbox. Для начала перейдите в Excel Online на сайте office.com; затем войдите в систему, используя свой Microsoft ID, и нажмите кнопку Excel. Когда Excel запустится, откройте файл, которым хотите поделиться.

Затем нажмите кнопку «Поделиться» в правом верхнем углу экрана. Экран всплывает над Excel. В нем введите адрес электронной почты человека, с которым хотите поделиться.Если вы хотите поделиться с несколькими людьми, введите несколько адресов электронной почты. Затем введите заметку, если хотите.

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

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

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

Excel теперь отправляет электронное письмо всем людям, с которыми вы хотите сотрудничать.Когда они нажимают кнопку «Просмотреть в OneDrive» или «Открыть», они открывают электронную таблицу. На этом этапе они могут просматривать электронную таблицу, но не могут ее редактировать. Чтобы отредактировать его, им нужно нажать кнопку «Редактировать в браузере» в верхней части экрана или щелкнуть меню «Редактировать книгу» и выбрать «Редактировать в браузере». Затем они могут редактировать документ прямо в окне браузера.

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

Когда люди совместно работают над электронной таблицей в Excel Online, каждый может видеть изменения, вносимые всеми остальными. (Щелкните изображение, чтобы увеличить.)

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

Чат недоступен. Но если вы нажмете значок Skype в правом верхнем углу экрана, вы сможете запустить Skype, посмотреть, подключены ли они к службе, и таким образом общаться с ними.

Обратите внимание, что даже владелец книги должен использовать Excel Online для совместной работы в режиме реального времени. Если у вас открыта книга в постоянной версии настольного клиента Excel 2016 или 2019, никто другой не сможет вносить изменения в свой браузер; они увидят сообщение о том, что файл заблокирован.Если вы используете Excel Online, каждый может вносить изменения (при условии, что вы предоставили им права на редактирование). Когда все внесут изменения и книга больше не будет открыта в браузере, вы можете повторно открыть файл в настольном клиенте Excel 2016 или 2019.

Excel Data Analysis — Solver Add-in

Solver Add-in

Это дополнительная надстройка, которую можно загрузить при необходимости. По умолчанию он не установлен.
Надстройка Solver позволяет выполнять вычисления «что, если» на основе нескольких переменных.
Эту надстройку можно использовать для решения формул, содержащих несколько переменных, и она является расширением встроенной функции Goal Seek (одна переменная).
Команду Goal Seek можно использовать для поиска простых целевых значений, но эту надстройку можно использовать для более сложных проблем.
Эта надстройка также позволяет указать одно или несколько ограничений (* т. Е. Условий), которые должны выполняться для решение быть действительным.
У этой надстройки есть несколько применений, одно из которых — решение обоих линейных уравнений.
Важно помнить, что найти решение для ваших формул не всегда возможно .

Установлено?

Вкладка данных, группа анализа, решатель

Различия между решателем и поиском цели

1) Решатель может решать формулы (или уравнения), которые используют несколько переменных, тогда как поиск цели может быть используется с одной переменной.
2) Solver позволит вам изменять значения до 200 ячеек, тогда как Goal Seek позволяет изменять значение только в одной ячейке.
3) С помощью Solver можно сохранить одну (или несколько) моделей.
4) Solver позволяет добавлять ограничения, которые должны выполняться, чтобы решение было действительным.
5) Решатель может использоваться для поиска значений переменных, которые дают формуле максимальное или минимальное значение, а также конкретное значение.

Новые возможности в 2010 году

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

Местоположение надстройки

Microsoft 365 — C: \ Program Files \ Microsoft Office \ root \ Office16 \ Library \ SOLVER.XLAM | SOLVER32.DLL
Excel 2019 — C: \ Program Files \ Microsoft Office \ Office15 \ Library \ Solver \ SOLVER.XLAM | SOLVER32.DLL
Excel 2016 — C: \ Program Files \ Microsoft Office \ root \ Office16 \ Library \ SOLVER.XLAM | SOLVER32.DLL
Excel 2013 — C: \ Program Files \ Microsoft Office \ Office15 \ Library \ Solver \ SOLVER.XLAM | SOLVER32.DLL

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

Использование надстройки

SS — диалоговое окно параметров решателя
Предоставьте 4 элемента информации
1) Цель
2) Целевая ячейка — решатель корректирует значения на основе этой формулы.
установить целевую ячейку (F9), имитирующую общие расходы
3) Переменные / регулируемые ячейки — решающая программа изменяет эти ячейки на новые значения.
количество объявлений в каждой публикации (E3: E8)
4) Ограничения — решатель корректирует значения в соответствии с этими условиями.
общие расходы должны быть меньше доступного бюджета (F8 <= G13)

Определить целевую ячейку и ее значение
Добавить / указать ограничения
SS — Добавить ограничение

SS — Результаты решателя
Сохранить решение для решателя —
Восстановить оригинал Значение —

Важно

Если вы хотите ограничить изменения, которые Excel может внести в значение ячейки или целевое значение, вы можете добавить ограничение.
Это также можно использовать для поиска комбинаций переменных, которые могут дать максимальный или минимальный результат.
Лицензия на эту надстройку принадлежит Microsoft компанией Frontline Systems Inc. (solver.com), и вы можете получить дополнительную информацию об этой надстройке на их веб-сайте.
Вам нужно только добавить ограничения к ячейкам, которые вы собираетесь изменить.
Эта надстройка чрезвычайно полезна для поиска корней уравнений.

Microsoft Excel: что нового в Excel 2019

2.Автоматизированный рабочий процесс: Excel 2019 теперь лучше подключается к Microsoft Flow, что позволяет создавать автоматизированные рабочие процессы, в которых данные извлекаются из других приложений, а затем сохраняются и / или обрабатываются в Excel. Поток данных можно дополнительно запрограммировать для автоматической отправки или обмена этими данными с другими людьми или сторонними приложениями. Например, Flow можно использовать для сбора положительных отзывов клиентов о ваших товарах или услугах в вашей учетной записи Twitter, а затем сохранить их в вашей книге Excel (на платформе OneDrive или SharePoint).Затем эти данные Twitter можно было бы периодически отправлять (например, каждый понедельник утром) выбранным получателям по электронной почте или текстовым сообщениям или публиковать непосредственно на веб-сайтах или в других местах назначения данных.

Flow может получать ваши данные практически из любого места — из вашей учетной системы, ERP-системы, CRM-системы, баз данных курсов акций, баз данных прогнозов погоды, сообщений электронной почты, уведомлений с напоминаниями, Salesforce.com и т. Д. В качестве примеров вы можете использовать эту технологию для отслеживания ваше рабочее время, места поездок, сообщения в Facebook, сообщения в Instagram, командировочные расходы, дебиторская задолженность и т. д.Чтобы создать новый поток данных, начните с веб-страницы My Flows по адресу login.microsoftonline.com, войдите в свою учетную запись, используя свой Microsoft ID, и нажмите кнопку Начать работу .

Нажмите Создайте поток из пустого , как показано на рисунке ниже в правом нижнем углу, а затем выберите один из более чем 200 триггеров и укажите событие, которое вы хотите запустить для автоматического процесса потока. Например, получение электронного письма с определенным вложением может вызвать автоматический процесс обработки.Продолжите, указав действия, которые вы хотите предпринять, например, открытие вложения электронной почты в Excel и запуск определенного макроса для создания сводной таблицы или сводной диаграммы, а затем укажите соединитель для обмена результатами через другие приложения Microsoft и сторонних производителей, такие как как Outlook или SharePoint.