Поиск решения в эксель 2019: Поиск решения в Экселе

Использование надстройки «Поиск решения» для определения оптимального набора продуктов

Как определить ежемесячный набор продуктов, который будет максимально подключаться к рентабельности?

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

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

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

Теперь рассмотрим следующий пример проблемы с набором продуктов. Вы можете найти решение этой проблемы в файле Продмикс. xlsx, показанном на рисунке 27-1.

Рассмотрим, что мы работаем для фармацевтической компании, которая создает шесть разных продуктов на своем предприятии. Для производства каждого продукта требуется материал и сырье. В строке 4 на рисунке 27-1 показано количество рабочих часов, необходимых для производства килограмма каждого продукта, а в строке 5 — количество килограммов сырья, необходимых для производства килограмма каждого продукта. Например, для производства килограмма продукта 1 требуется 6 часов трудозатрат и 3,2 фунта сырья. Для каждого лекарства Цена за штуку выдается в строке 6, стоимость единицы на килограмм дается в строке 7, а прибыль на килограмм — в строке 9. Например, если товар 2 продает на $11,00 для каждого фунта, то на него возмещаются стоимость за единицу $5,70, а для каждого из них — доход $5,30 долларов за каждый фунт. Потребность в месяце для каждого лекарства указана в строке 8. Например, спрос на товар 3 составляет 1041 фунта. В этом месяце на 4500 часов трудозатрат и 1600 килограмма сырья доступны. Как эта компания может максимально увеличить месячный доход?

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

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

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

(Трудозатраты на килограмм лекарства 1) * (произведенные
килограммы лекарства 1) + (трудозатраты на фунты лекарства 2) * (количество килограммов лекарства 2) *.
(Трудозатраты на килограмм лекарства 6) * (произведенные килограммы для лекарства 6)

Мы могли вычислить использование трудовых ресурсов более утомительно, как D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + h3 * h5 и I2 * I4. Кроме того, использование сырья может быть вычислено как D2 * D5 + E2 *SHIFT+F5 + F2 * F5 + G2 * G5 + h3 * H5 и I2 * I5. Однако ввод этих формул на листе для шести продуктов занимает много времени. Представьте себе, сколько времени потребуется, если вы работали с компанией, которая создала, например, продукты 50 на своем предприятии. Намного проще всего вычислять ресурсы и использование сырья — копирование из D14 в D15 формулы СУММПРОИЗВ ($D $2: $I $2, D4: i4). В этой формуле представлено значение D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + h3 * h5 + I2 * I4 (это наш трудный расход), но это еще проще вводить! Обратите внимание, что я использую знак $ с диапазоном D2: I2, так что при копировании формулы по-прежнему захватывается набор продуктов из строки 2. Формула в ячейке D15 вычисляет необработанное использование сырья.

Аналогичным образом, наш доход определяется с помощью

(Прибыль на лекарства 1 за килограмм) * (произведенные килограммы лекарства 1) +
(стоимость лекарства 2 на фунт) * (произведенные килограммы лекарства 2) *.
(Прибыль на килограмм лекарства 6) * (произведенные килограммы лекарства 6)

Прибыль легко вычисляется в ячейке D12 с помощью формулы СУММПРОИЗВ (D9: I9, $D $2: $I $2).

Теперь мы можем идентифицировать три компонента нашей модели «Поиск решения» для набора продуктов.

• Целевая ячейка.Наша цель – это максимизировать прибыль (вычисленные в ячейках D12).

• Изменяемые ячейки.Количество произведенных единиц продукта (указывается в диапазоне ячеек D2: I2)

• Unique. Существуют указанные ниже ограничения.

  • Не используйте больше трудовых и необработанных материалов, чем доступно. Таким образом, значения в ячейках D14: D15 (используемые ресурсы) должны быть меньше или равны значениям в ячейках F14: F15 (доступные ресурсы).

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

  • Мы не можем выдать отрицательную сумму лекарства.

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

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

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

Появится диалоговое окно Параметры поиска решения, как показано на рисунке 27-2.

Щелкните поле задать целевую ячейку и выберите нашу ячейку дохода (ячейка D12). Щелкните поле изМеняя ячейки и наведите указатель мыши на диапазон D2: I2, который будет содержать произведенные килограммы для каждого лекарства. В диалоговом окне должно появиться представление 27-3.

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

Чтобы добавить ограничения на использование ресурсов, щелкните поле Ссылка на ячейку и выберите диапазон D14: D15. Выберите _Лт_ = из среднего списка. Щелкните поле ограничения и выберите диапазон ячеек F14: F15. Диалоговое окно Добавление ограничения теперь должно выглядеть примерно так, как показано на рисунке 27-5.

Теперь мы доходили, что когда поиск решения попытается использовать различные значения для изменяющихся ячеек, только комбинации, которые удовлетворяют обоим D14< = F14 (использование трудовых и недоступных), и D15< = F15 (использование сырья не меньше или равно необработанные сырье) будут учитываться. Нажмите кнопку Добавить, чтобы ввести ограничения спроса. ЗаПолните диалоговое окно Добавление ограничения, как показано на рисунке 27-6.

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

• D2< = D8 (объем лекарства 1 меньше или равен спросу на лекарство 1)

• E2< = E8 (объем произведенного лекарства 2 меньше или равен спросу на лекарство 2)

• F2< = F8 (произведенная сумма лекарства 3 меньше или равна спросу на лекарство 3)

• G2< = G8 (произведенная сумма лекарства 4 меньше или равна спросу на лекарство 4).

• h3< = H8 (произведенное количество лекарства 5 меньше или равно спросу на лекарство 5)

• I2< = i8 (произведенное количество лекарства 6 меньше или равно спросу на лекарство 6)

Нажмите кнопку «ОК» в диалоговом окне «добавить ограничение». Окно поиска решения должно выглядеть примерно так, как показано на рисунке 27-7.

В диалоговом окне Параметры поиска решения не нужно отрицательно менять ячейки. Нажмите кнопку «Параметры» в диалоговом окне «Параметры поиска решения». Установите флажок Линейная модель и поле предполагать неОтрицательное значение, как показано на рисунке 27-8 на следующей странице. Нажмите кнопку «ОК».

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

• Целевая ячейка вычисляется путем сложения условий формы (изменяемой ячейки) * (константы).

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

Почему эта проблема поиска решения является линейной? Наша целевая ячейка (прибыль) вычисляется как

(Прибыль на лекарства 1 за килограмм) * (произведенные килограммы лекарства 1) +
(стоимость лекарства 2 на фунт) * (произведенные килограммы лекарства 2) *.
(Прибыль на килограмм лекарства 6) * (произведенные килограммы лекарства 6)

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

Наши ограничения на трудовые ресурсы оцениваются за счет сравнения значения Derived («трудозатраты на килограмм» лекарства 1) * (произведенные килограммы лекарства 1) + (трудозатраты на килограмм килограмма лекарства 2) * (количество килограммов лекарства 2) *. (Труд. США ) ED на килограмм лекарства 6) * (произведенные килограммы лекарства 6) * для доступа к вашим трудозатратам.

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

Наши ограничения на спрос принимают форму

(Лекарства 1) _лт_ = (спрос на лекарства 1)
(лекарства 2) _лт_ = (спрос на лекарства 2)
§
(лекарства 6), _лт_ = (спрос на лекарство 6 )

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

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

• Если модель поиска решения является линейной и выбирается линейная модель, надстройка «Поиск решения» гарантирует Поиск оптимального решения для модели «Поиск решения». Если модель поиска решения не является линейной, поиск решения может быть или может не найти оптимальное решение.

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

После нажатия кнопки ОК в диалоговом окне Параметры поиска решения мы вернемся к Главному диалоговому окну Поиск решения, показанному ранее на рисунке 27-7. После нажатия кнопки «решение» Поиск решения вычислит оптимальное решение (если оно существует) для нашей модели номенклатуры продуктов. Как было сказано в главе 26, оптимальным решением для модели «набор продуктов» является набор значений ячеек (произведенных килограммов каждого лекарства), который обеспечивает максимально возможную прибыль по набору всех возможных решений. Опять же, подходящим решением является набор изменяемых значений ячеек, удовлетворяющий всем ограничениям. Значения изменяемых ячеек, показанные на рисунке 27-9, являются подходящими решением, так как все уровни производства не являются отрицательными, но производственные уровни не превосходят требования, а использование ресурсов не превышает доступные ресурсы.

Значения изменяемых ячеек, показанные на рисунке 27-10 на следующей странице, представляют собой неприменимое решение по следующим причинам.

• Мы создаем больше из лекарства 5, чем спрос на него.

• Мы используем больше трудовых затрат, чем доступно.

• Мы используем больше необработанных материалов, чем доступно.

После нажатия кнопки «решить» Поиск решения быстро находит оптимальное решение, показанное на рисунке 27-11. Чтобы сохранить оптимальные значения решений на листе, необходимо выбрать команду Сохранить найденное решение.

Наша компания может максимально увеличить месячный доход на уровне $6 625,20, выполнив 596,67 фунта на лекарства 4, 1084 фунта на лекарства 5, и ни один из других наркотиков! Мы не можем определить, можно ли достичь максимального дохода в $6 625,20 в других случаях. Все, что мы можем сделать, – это то, что с нашими ограниченными ресурсами и спросом не существует способа внести более $6 627,20 в этом месяце.

Надстройка Microsoft Excel «Поиск решения»

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

Краткое описание надстройки

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

Как установить надстройку?

Надстройку «Поиск решения» можно установить двумя способами. Стандартные надстройки, такие как «Поиск решения» и «Пакет анализа» устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки «Поиск решения» на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.

Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».

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

Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.

Как подключить надстройку?

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

Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье «Как установить надстройку для Excel 2003/2007/2010?» со скриншотами для каждой из трех версий приложения Excel,  поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка «Поиск решения») и произвести установку в соответствии с описанием по ссылке выше.

Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?

После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

Расположение

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

1. Нажимаете кнопку Office в верхнем левом углу экрана и переходите к Параметрам.

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

3. Ставите галочку напротив Поиск решения и нажимаете ОК.

4.Программа выдает предупреждение об отсутствии компонента и предлагает его установить. Соглашаетесь.

5. Дожидаетесь окончания установки.

6. Если все сделано правильно, то во вкладке Данные появится блок Анализ с кнопкой Поиск решения.

Структура

Рассмотрим подробнее основные аргументы и принцип работы функции. Основное окно содержит следующие поля:

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

На заметку! Excel может сам выбрать ячейки, которые будут меняться.

Для этого нажимаете кнопку Предположить.

3. Блок добавления ограничений.
4. Кнопка параметров, при нажатии которой, появляется новое окно, где можно настроить количество повторений, время выполнения, погрешность и отклонение, а также обозначить дополнительные настойки.

Использование

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

Перенесем эти сведения на рабочий лист excel.

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

1. Процентная ставка.
2. Период (кпер).
3. Сумма платежа (плт).

Для первого года формула будет выглядеть следующим образом:

Важно! Чтобы расчеты были правильными, необходимо зафиксировать значение суммы и процента, нажав клавишу F4 или добавив значки доллара.

Как видите, число отрицательное – это особенной функции БС. Чтобы этого избежать, ячейку с суммой денег нужно сделать отрицательной. Тогда итоговые результаты будут отображаться корректно.

Воспользуемся автозаполнением и получим сумму средств после 5 лет нахождения на депозите под 4 процента годовых с ежегодным пополнением.

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

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

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

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

Как видите, чтобы достигнуть отметки в 12000$ через пять лет, необходимо найти депозит под 4,03 процента годовых и ежегодно пополнять его на сумму 2214 доллара 01 цент.

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

90000 How to Use the Excel 2019 Solver 90001 90002 90003 90004 90003 Software 90004 90003 Microsoft Office 90004 90003 Excel 90004 90003 How to Use the Excel 2019 Solver 90004 90013 90014 By Greg Harvey 90015 90014 Although Excel’s Data Table and Goal Seek commands work just fine for simple problems that require determining the direct relationship between the inputs and results in a formula, you need to use the Solver add-in when dealing with more complex problems .For example, use the Solver to find the best solution when you need to change multiple input values ​​in your Excel model and you need to impose constraints on these values ​​and / or the output value. 90015 90014 The Solver add-in works by applying iterative methods to find the «best» solution given the inputs, desired solution, and the constraints that you impose. With each iteration, the program applies a trial-and-error method (based on the use of linear or nonlinear equations and inequalities) that attempts to get closer to the optimum solution.90015 90014 When using the Solver add-in, keep in mind that many problems, especially the more complicated ones, have many solutions. Although the Solver returns the optimum solution, given the starting values, the variables that can change, and the constraints that you define, this solution is often not the only one possible and, in fact, may not be the best solution for you. To be sure that you are finding the best solution, you may want to run the Solver more than once, adjusting the initial values ​​each time you solve the problem.90015 90014 When setting up the problem for the Solver add-in in your Excel worksheet, define the following items: 90015 90024 90003 90026 Objective cell: 90027 The target cell in your worksheet whose value is to be maximized, minimized, or made to reach a particular value. Note that this cell must contain a formula. 90004 90003 90026 Variable cells: 90027 The changing cells in your worksheet whose values ​​are to be adjusted until the answer is found. 90004 90003 90026 Constraint cells: 90027 The cells that contain the limits that you impose on the changing values ​​in the variable cells and / or the target cell in the objective cell.90004 90037 90014 After you finish defining the problem with these parameters and have the Solver add-in solve the problem, the program returns the optimum solution by modifying the values ​​in your worksheet. At this point, you can choose to retain the changes in the worksheet or restore the original values ​​to the worksheet. You can also save the solution as a scenario to view later before you restore the original values. 90015 90014 You can use the Solver add-in with the Scenario Manager to help set up a problem to solve or to save a solution so that you can view it at a later date.The changing cells that you define for the Scenario Manager are automatically picked up and used by the Solver when you select this command, and vice versa. Also, you can save the Solver’s solution to a problem as a scenario (by clicking the Save Scenario button in the Solver dialog box) that you can then view with the Scenario Manager. 90015 90042 Setting up and defining the problem in Excel 2019 90043 90014 The first step in setting up a problem for the Solver to work on is to create the worksheet model for which you will define the objective cell, variables cells, and the constraint cells.90015 90014 Keep in mind that the Solver is an add-in utility. This means that, before you can use it, you need to make sure that the Solver add-in program is still loaded, as indicated by the appearance of the Solver button in the Analysis group at the end of the Data tab on the Ribbon. If this button is missing, you can load Solver by opening the Add-Ins tab of the Excel Options dialog box (Alt + FTAA) and then clicking the Go button after making sure that Excel Add-Ins is displayed in the Manage drop-down list box to its immediate left.Then, select the Solver Add-in check box in the Add-Ins dialog box to put a check mark in it before you click OK to close the dialog box and reload the add-in. 90015 90014 To define and solve a problem with the Solver add-in after you’ve loaded the add-in and have created your worksheet model, you follow these steps: 90015 90002 90003 Click the Solver command button in the Analyze group at the end of the Ribbon’s Data tab. 90014 Excel opens the Solver Parameters dialog box. 90015 Specifying the parameters to apply to the model in the Solver Parameters dialog box.90004 90003 Click the target cell in the worksheet or enter its cell reference or range name in the Set Objective text box. 90014 Next, you need to select the To setting. Click the Max option button when you want the target cell’s value to be as large as possible. Click the Min option button when you want the target cell’s value to be as small as possible. Click the Value Of option button and then enter a value in the associated text box when you want the target cell’s value to reach a particular value.90015 90004 90003 Click the appropriate option button option in the To section of the dialog box. If you select the Value Of option button, enter the value to match in the associated text box. 90014 Next, designate the variable cells — that is, the ones Solver can change to reach your Equal To goal. 90015 90004 90003 Click the By Changing Variable Cells text box and then select the cells to change in the worksheet or enter their cell references or range name in the text box. 90014 Remember that to select nonadjacent cells in the worksheet, you need to hold down the Ctrl key as you click each cell in the selection.To have Excel choose the changing cells for you based on the target cell that you selected, click the Guess button to the right of this text box. 90015 90014 Before having Solver adjust your model, you may add constraints for the target cell or any of the changing cells that determine its limits when adjusting the values. 90015 90004 90003 (Optional) Click the Add button to the right of the Subject to the Constraints list box in the Solver Parameters dialog box. 90014 This action opens the Add Constraint dialog box.When defining a constraint, choose the cell whose value you want to constrain or select the cell in the worksheet or enter its cell reference in the Cell Reference text box. Then select the relationship (=, <=,> =, or 90071 int 90072 for integer or 90071 bin 90072 for binary) from the drop-down list box to the right and (unless you chose 90071 int 90072 or 90071 bin 90072) enter the appropriate value or cell reference in the Constraint text box. 90015 90014 To continue adding constraints for other cells used by the Solver, click the Add button to add the constraint and clear the text boxes in the Add Constraint dialog box.Then, repeat Step 5 to add a new constraint. After you finish defining constraints for the target cell and changing values ​​in the model, click OK to close the Add Constraint dialog box and return to the Solver Parameters dialog box (which now lists your constraints in the Subject to the Constraints list box). 90015 90004 90003 (Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values ​​when the variable cells are not subject to constraints.90026 90085 90027 By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7. 90004 90003 (Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.90004 90003 Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box. 90004 90013 90042 Solving the problem with Excel’s Solver 90043 90014 When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial ) solutions as they are tried.To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button. 90015 90014 When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem.To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values ​​to the worksheet, click the Restore Original Values ​​option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values ​​option and OK button. 90015 The Solver Results dialog box showing that Solver found a solution to the problem.90014 Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can not use the Undo command button on the Quick Access toolbar to restore the original values ​​to your worksheet. If you want to be able to switch between the «before» and «after» views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values ​​option button. That way, you can retain the «before» view in the original worksheet and use the Scenario Manager to display the «after» view created by the Solver.90015 90042 Changing Excel’s Solver options 90043 90014 For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes. 90015 Modifying the solution options in the Options dialog box.90105 90106 Excel 2019 Solver Option Settings 90107 90108 90109 90110 Option 90111 90110 Function 90111 90114 90115 90116 90109 90110 Constraint Precision 90111 90110 Specifies the precision of the constraints. The number that you enter in this text box determines whether the value in a constraint cell meets the specified value or the upper or lower limit you have set. Specify a lower number (between 0 and 1) to reduce the time it takes the Solver to return a solution to your problem.90111 90114 90109 90110 Use Automatic Scaling 90111 90110 Select this check box to have the Solver automatically scale the results when solving the problem. 90111 90114 90109 90110 Show Iteration Results 90111 90110 Select this check box to have the Solver show the results for the iterations followed in solving the problem. 90111 90114 90109 90110 Ignore Integer Constraints 90111 90110 Select this check box to have the Solver ignore any constraints you specify that use integers.90111 90114 90109 90110 Integer Optimality (%) 90111 90110 Specifies the percentage of integer optimality criteria that the Solver applies in solving the problem. 90111 90114 90109 90110 Max Time (seconds) 90111 90110 Specifies the maximum number of seconds that the Solver will spend on finding the solution. 90111 90114 90109 90110 Iterations 90111 90110 Specifies the maximum number of times that the Solver will recalculate the worksheet when finding the solution. 90111 90114 90109 90110 Max Subproblems 90111 90110 Specifies the maximum number of subproblems that the Solver takes on when using the Evolutionary method to solve the problem.90111 90114 90109 90110 Max Feasible Solutions 90111 90110 Specifies the maximum number of feasible solutions that the Solver will pursue when you select the Evolutionary method for solving the problem. 90111 90114 90171 90172 90014 After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed. 90015 90014 When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box.These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods. 90015 90042 Saving and loading a model problem in Excel 2019 90043 90014 The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl + S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.90015 90014 When you click the Load / Save button, Excel opens the Load / Save Model dialog box, containing a Select Model Area text box. This text box contains the cell references for a range large enough to hold all the problem’s parameters, starting with the active cell. To save the problem’s parameters in this range, click OK. If this range includes cells with existing data, you need to modify the cell reference in this text box before you click OK to prevent Excel from replacing the existing data.90015 90014 After you click OK, Excel copies the problem’s parameters in the specified range. These values ​​are then saved as part of the worksheet the next time you save the workbook. To reuse these problem parameters when solving a problem, you simply need to open the Solver Options dialog box, click the Load / Save button to open the Load / Save Model dialog box, click the Load button, and then select the range containing the saved problem parameters. When you click OK in the Load Model dialog box, Excel loads the parameters from this cell range into the appropriate text boxes in the Solver Parameters dialog box.You can then close the Solver Options dialog box by clicking OK, and you can solve the problem by using these parameters by clicking the Solve command button. 90015 90014 Remember that you can use the Reset All button whenever you want to clear all the parameters defined for the previous problem and return the Solver options to their defaults. 90015 90042 Creating Solver reports in Excel 2019 90043 90014 You can create three different types of reports with the Solver: 90015 90024 90003 90026 Answer report: 90027 Lists the target cell and changing cells with their original and final values, along with the constraints used in solving the problem.90004 90003 90026 Sensitivity report: 90027 Indicates how sensitive an optimal solution is to changes in the formulas that calculate the target cell and constraints. The report shows the changing cells with their final values ​​and the 90071 reduced gradient 90072 for each cell. (The reduced gradient measures the objective per unit increase in the changing cell.) If you defined constraints, the Sensitivity report lists them with their final values ​​and the 90071 Lagrange multiplier 90072 for each constraint.(The Lagrange multiplier measures the objective per unit increase that appears in the right side of the constraint equation.) 90004 90003 90026 Limits report: 90027 Shows the target cell and the changing cells with their values, lower and upper limits, and target results. The lower limit represents the lowest value that a changing cell can have while fixing the values ​​of all other cells and still satisfying the constraints. The upper limit represents the highest value that will do this. 90004 90037 90014 Excel places each report that you generate for a Solver problem in a separate worksheet in the workbook.To generate one (or all) of these reports, select the report type (Answer, Sensitivity, or Limits) from the Reports list box of the Solver Results dialog box. To select more than one report, just click the name of the report. 90015 90014 When you click OK to close the Solver Results dialog box (after choosing between the Keep Solver Solution and Restore Original Values ​​options), Excel generates the report (or reports) that you selected in a new worksheet that it adds to the beginning of the workbook.(Report sheet tabs are named by report type, as in 90071 Answer Report 1, Sensitivity Report 1, 90072 and 90071 Limits Report 1. 90072) 90015 90217 About the Book Author 90218 90014 90220 Faithe Wempen, M.A., 90221 is a Microsoft Office Master Instructor and the author of over 150 books on computer technology, including 90222 Outlook 2019 for Dummies 90223 and 90222 PowerPoint Bible 90223. She is also a CompTIA A + certified PC technician and an adjunct Computer Technology instructor at Purdue University.Her online courses in Office applications and digital literacy have educated over a quarter of a million students for corporate clients. 90015 .90000 5 Ways to Use AutoFill in Excel 2019 90001 90002 90003 90004 90003 Software 90004 90003 Microsoft Office 90004 90003 Excel 90004 90003 5 Ways to Use AutoFill in Excel 2019 90004 90013 90014 By Greg Harvey 90015 90014 Many of the worksheets that you create with Excel require the entry of a series of sequential dates or numbers. For example, a worksheet may require you to title the columns with the 12 months, from January through December, or to number the rows from 1 to 100.90015 90014 Excel’s AutoFill feature makes short work of this kind of repetitive task. All you have to enter is the initial value in that series. In most cases, AutoFill is smart enough to figure out how to fill out the series for you when you drag the fill handle to the right (to take the series across columns to the right) or down (to extend the series to the rows below ). 90015 90014 The AutoFill (or fill) handle looks like this — + — and appears only when you position the mouse on the lower-right corner of the active cell (or the last cell, when you’ve selected a block of cells).If you drag a cell selection with the white-cross mouse pointer rather than the AutoFill handle, Excel simply extends the cell selection to those cells you drag through. If you drag a cell selection with the arrowhead pointer, Excel moves the cell selection. 90015 90014 On a touchscreen device, you make the Excel fill handle appear by tapping the active cell’s lower selection handle (indicated by a circle) and then tapping the AutoFill item on the mini-toolbar that then appears. You can then fill a series by dragging this fill handle with your finger or a stylus.90015 90014 When creating a series with the fill handle, you can drag in only one direction at a time. For example, you can fill the series or copy the entry to the range to the left or right of the cell that contains the initial values, or you can fill the series or copy to the range above or below the cell containing the initial values. You can not, however, fill or copy the series to two directions at the same time (such as down and to the right by dragging the fill handle diagonally). 90015 90014 As you drag the fill handle, the program keeps you informed of whatever entry will be entered into the last cell selected in the range by displaying that entry next to the mouse pointer (a kind of AutoFill tips, if you will).After extending the range with the fill handle, Excel either creates a series in all of the cells that you select or copies the entire range with the initial value. To the right of the last entry in the filled or copied series, Excel also displays a drop-down button that contains a shortcut menu of options. You can use this shortcut menu to override Excel’s default filling or copying. 90015 90014 For example, when you use the fill handle, Excel copies an initial value (such as 10) into every cell in the range.But, if you wanted a sequential series (such as 10, 11, 12, and so on), you do this by selecting the Fill Series command on the AutoFill Options shortcut menu. 90015 90014 The images below illustrate how to use AutoFill to enter a row of months, starting with January in cell B2 and ending with June in cell G2. To do this, you simply enter 90031 Jan 90032 in cell B2 and then position the mouse pointer (or your finger or stylus) on the fill handle in the lower-right corner of this cell before you drag through to cell G2 on the right.90015 To enter a series of months, enter the first month and then drag the fill handle in a direction to add sequential months. 90014 When you release the mouse button or remove your finger or stylus from the touchscreen, Excel fills in the names of the rest of the months (Feb through Jun) in the selected cells. 90015 Release the mouse button, and Excel fills the cell selection with the missing months. 90014 Excel keeps the cells with the series of months selected, giving you another chance to modify the series.(If you went too far, you can drag the fill handle to the left to cut back on the list of months; if you did not go far enough, you can drag it to the right to extend the list of months farther.) 90015 90014 Also, you can use the options on the AutoFill Options drop-down menu. To display this menu, you click the drop-down button that appears on the fill handle (to the right of Jun) to override the series created by default. To have Excel copy Jan into each of the selected cells, select Copy Cells on this menu.To have the program fill the selected cells with the formatting used in cell B2 (in this case, the cell has had bold applied to it), you select Fill Formatting Only on this menu. To have Excel fill in the series of months in the selected cells without copying the formatting used in cell B2, you select the Fill Without Formatting command from this shortcut menu. 90015 90014 Check out the table below to see different initial values ​​that AutoFill can use and the types of series that Excel can create from them.90015 90042 90043 Samples of Series You Can Create with AutoFill 90044 90045 90046 90047 Value Entered in First Cell 90048 90047 Extended Series Created by AutoFill in the Next Three Cells 90048 90051 90052 90053 90046 90047 June 90048 90047 July, August, September 90048 90051 90046 90047 Jun 90048 90047 Jul, Aug, Sep 90048 90051 90046 90047 Tuesday 90048 90047 Wednesday, Thursday, Friday 90048 90051 90046 90047 Tue 90048 90047 Wed, Thu, Fri 90048 90051 90046 90047 4/1/99 90048 90047 4/2/99, 4/3/99, 4/4/99 90048 90051 90046 90047 Jan-00 90048 90047 Feb-00, Mar-00, Apr-00 90048 90051 90046 90047 15-Feb 90048 90047 16-Feb, 17-Feb, 18-Feb 90048 90051 90046 90047 10:00 PM 90048 90047 11:00 PM, 12:00 AM, 1:00 AM 90048 90051 90046 90047 8:01 90048 90047 9:01, 10:01, 11:01 90048 90051 90046 90047 Quarter 1 90048 90047 Quarter 2, Quarter 3, Quarter 4 90048 90051 90046 90047 Qtr2 90048 90047 Qtr3, Qtr4, Qtr1 90048 90051 90046 90047 Q3 90048 90047 Q4, Q1, Q2 90048 90051 90046 90047 Product 1 90048 90047 Product 2, Product 3, Product 4 90048 90051 90132 90133 90134 Working with a spaced series in Excel 2019 90135 90014 AutoFill uses the initial value that you select (date, time, day, year, and so on) to design the series.All the sample series shown above change by a factor of one (one day, one month, or one number). You can tell AutoFill to create a series that changes by some other value: Enter two sample values ​​in neighboring cells that describe the amount of change you want between each value in the series. Make these two values ​​the initial selection that you extend with the fill handle. 90015 90014 For example, to start a series with Saturday and enter every other day across a row, enter 90031 Saturday 90032 in the first cell and 90031 Monday 90032 in the cell next door.After selecting both cells, drag the fill handle across the cells to the right as far as you need to fill out a series based on these two initial values. When you release the mouse button or remove your finger or stylus from the screen, Excel follows the example set in the first two cells by entering every other day (Wednesday to the right of Monday, Friday to the right of Wednesday, and so on) . 90015 90134 Copying with AutoFill in Excel 2019 90135 90014 You can use AutoFill to copy a text entry throughout a cell range (rather than fill in a series of related entries).To copy a text entry to a cell range, engage the Ctrl key while you click and drag the fill handle. When you do, a plus sign appears to the right of the fill handle — your sign that AutoFill will 90147 copy 90148 the entry in the active cell instead of creating a series using it. 90015 90014 You can also tell because the entry that appears as the AutoFill tip next to the fill handle while you drag contains the same text as the original cell. If you decide after copying an initial label or value to a range that you should have used it to fill in a series, click the drop-down button that appears on the fill handle at the cell with the last copied entry and then select the Fill Series command on the AutoFill Options shortcut menu that appears.90015 90014 Although holding down Ctrl while you drag the fill handle copies a text entry, just the opposite is true when it comes to values! Suppose that you enter the number 17 in a cell and then drag the fill handle across the row — Excel just copies the number 17 in all the cells that you select. If, however, you hold down Ctrl while you drag the fill handle, Excel then fills out the series (17, 18, 19, and so on). If you forget and create a series of numbers when you only need the value copied, rectify this situation by selecting the Copy Cells command on the AutoFill Options shortcut menu.90015 90134 Creating custom lists for AutoFill in Excel 2019 90135 90014 In addition to varying the increment in a series created with AutoFill, you can also create your own custom series. For example, say your company has offices in the following locations and you get tired of typing the sequence in each new spreadsheet that requires them: 90015 90158 90003 New York 90004 90003 Chicago 90004 90003 Atlanta 90004 90003 New Orleans 90004 90003 San Francisco 90004 90003 Los Angeles 90004 90171 90014 After creating a custom list with these locations, you can enter the entire sequence of cities simply by entering New York in the first cell and then dragging the Fill handle to the blank cells where the rest of the cities should appear.90015 90014 To create this kind of custom series using Excel’s AutoFill feature, follow these steps: 90015 90002 90003 Click File → Options → Advanced or press Alt + FTA and then scroll down and click the Edit Custom Lists button in the General section to open the Custom Lists dialog box. Creating a custom company location list from a range of existing cell entries. 90014 If you’ve already gone to the time and trouble of typing the custom list in a range of cells, go to Step 2. If you have not yet typed the series in an open worksheet, go to Step 4.90015 90004 90003 Click in the Import List from Cells text box and then select the range of cells in the worksheet containing the custom list. 90014 As soon as you start selecting the cells in the worksheet by dragging your mouse or Touch pointer, Excel automatically collapses the Options dialog box to the minimum to get out of the way. The moment you release the mouse button or remove your finger or stylus from the screen, Excel automatically restores the Options dialog box to its normal size.90015 90004 90003 Click the Import button to copy this list into the List Entries list box. 90031 90187 90032 Skip to Step 6. 90004 90003 Select the List Entries list box and then type each entry (in the desired order), being sure to press Enter after typing each one. 90014 When all the entries in the custom list appear in the List Entries list box in the order you want them, proceed to Step 5. 90015 90004 90003 Click the Add button to add the list of entries to the Custom Lists list box.90014 Finish creating all the custom lists you need, using the preceding steps. When you’re done, move to Step 6. 90015 90004 90003 Click OK twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box and return to the current worksheet in the active workbook. 90014 After adding a custom list to Excel, from then on you need only enter the first entry in a cell and then use the fill handle to extend it to the cells below or to the right. 90015 90004 90013 90014 If you do not even want to bother with typing the first entry, use the AutoCorrect feature to create an entry that fills in as soon as you type your favorite acronym for it (such as ny for New York).90015 90134 Excel 2019: Doing AutoFill on a touchscreen 90135 90014 To fill out a data series using your finger or stylus when using Excel on a touchscreen tablet without access to a mouse or touchpad, you use the AutoFill button that appears on the mini-toolbar as the fill handle as follows: 90015 90002 90003 Tap the cell containing the initial value in the series you want AutoFill to extend. 90031 90187 90032 Excel selects the cell and displays selection handles (with circles) in the upper-left and lower-right corners.90004 90003 Tap and hold the cell until the mini-toolbar appears. 90031 90187 90032 When summoned by touch, the mini-toolbar appears as a single row of command buttons, from Paste to AutoFill, terminated by a Show Context Menu button (with a black triangle pointing downward). 90004 90003 Tap the AutoFill button on the mini-toolbar. 90031 90187 90032 Excel closes the mini-toolbar and adds an AutoFill button to the currently selected cell (the blue downward-pointing arrow in the square that appears in the lower-right corner of the cell).90004 90003 Drag the AutoFill button through the blank cells in the same column or row into which the data series sequence is to be filled. 90014 As you drag your finger or stylus through blank cells, the Name box on the Formula bar keeps informed of the next entry in the data series. When you release your finger or stylus from the touchscreen after selecting the last blank cell to be filled, Excel fills out the data series in the selected range. 90015 90004 90013 90134 Excel 2019: Using AutoFill with the Fill button on the Home tab 90135 90014 If you’re using Excel 2019 on a touchscreen tablet without the benefit of a mouse or touchpad, you can do AutoFill from the Ribbon (you may also want to use this method if you find that using the fill handle to create a series of data entries with AutoFill is too taxing even with a physical mouse).90015 90014 You simply use the Fill button on the Home tab of the Ribbon to accomplish your AutoFill operations in Excel 2019 as follows: 90015 90002 90003 Enter the first entry (or entries) upon which the series is to be based in the first cell (s) to hold the new data series in your worksheet. 90004 90003 Select the cell range where the series is to be created, across a row or down a column, being sure to include the cell with the initial entry or entries in this range. 90004 90003 Click the Fill button on the Home tab followed by Series on its drop-down menu or press Alt + HFIS.90014 The Fill button is located in the Editing group right below the AutoSum button (the one with the Greek sigma). When you select the Series option, Excel opens the Series dialog box. 90015 90004 90003 Click the AutoFill option button in the Type column followed by the OK button in the Series dialog box. 90031 90187 90032 Excel enters a series of data based on the initial value (s) in your selected cell range just as though you’d selected the range with the fill handle. 90004 90013 90014 Note that the Series dialog box contains a bunch of options that you can use to further refine and control the data series that Excel creates.In a linear data series, if you want the series to increment more than one step value at a time, you can increase it in the Step Value text box. Likewise, if you want your linear or AutoFill series to stop when it reaches a particular value, you enter that into the Stop Value text box. 90015 90014 When you’re entering a series of dates with AutoFill that increment on anything other than the day, remember the Date Unit options in the Series dialog box enable you to specify other parts of the initial date to increment in the series.Your choices include Weekday, Month, or Year. 90015 90255 About the Book Author 90256 90014 90258 Greg Harvey 90259 began training business people to use computers and software back in the days of DOS. He is the author of numerous 90260 For Dummies 90261 books, including all editions of 90260 Excel All-in-One For Dummies 90261, 90260 Excel Workbook For Dummies 90261, and 90260 Excel For Dummies 90261. He has worked as an independent trainer and as an instructor at Golden Gate University.90015 .90000 Excel 2019 Essential Training — Finding and replacing data 90001 90002 Course details 90003 90004 Start mastering Excel, the world-renowned powerful spreadsheet program, with Excel expert Dennis Taylor. Learn how to enter data, organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of data and cells, build charts, and create PivotTables.Find out how to use the IF, VLOOKUP, and COUNTIF family of functions. See how to use data analysis tools, including Goal Seek and Solver. Discover how to automate tasks with macros, manage data, and more. 90005 90002 Instructor 90003 90008 90009 90010 90004 90012 Dennis Taylor 90013 90014 Principal at Taylor Associates 90015 90005 Dennis Taylor is an Excel expert who has 25+ years of experience in spreadsheet authoring and training.90004 Dennis has experience working as an author, speaker, seminar leader, and facilitator. Since the mid-90s, he has been the author / presenter of numerous Excel video and online courses and has traveled throughout the U.S. and Canada presenting over 300 seminars and classes. He has authored or co-authored multiple books on spreadsheet software and has presented over 500 Excel webinars to a diversity of audiences. Dennis has worked with hundreds of different corporations and governmental agencies as well as colleges and universities.He lives in Boulder, Colorado. 90005 See more See less 90019 90020 90002 Skills covered in this course 90003 90002 Viewers of this course 90003 90004 123,363 people watched this course 90005 90004 What they do 90005 90004 Student, Business Strategist, Camp Counselor, and University Professor 90005 90002 Related courses 90003 .90000 Using Excel 2019 Filters — dummies 90001 90002 90003 90004 90003 Software 90004 90003 Microsoft Office 90004 90003 Excel 90004 90003 Using Excel 2019 Filters 90004 90013 90014 By Greg Harvey 90015 90014 Excel’s Filter feature makes it a breeze to hide everything in a data list except the records you want to see. To filter the data list to just those records that contain a particular value, you then click the appropriate field’s AutoFilter button to display a drop-down list containing all the entries made in that field and select the one you want to use as a filter.Excel then displays only those records that contain the value you selected in that field. (All other records are hidden temporarily.) 90015 90014 If the column headings of your data list table do not currently have filter drop-down buttons displayed in their cells after the field names, you can add them simply by clicking Home → Sort & Filter → Filter or pressing Alt + HSF. (Check out these other entry and formatting shortcuts.) 90015 90014 For example, in the image below, the Employee Data List was filtered to display only those records in which the Location is either Boston or San Francisco by clicking the Location field’s AutoFilter button and then clicking the (Select All) check box to remove its check mark.Then, the Boston and San Francisco check boxes were selected to add check marks to them before clicking OK. (It’s as simple as that.) 90015 The Employee Data List after Excel filters out all records except those with Boston or San Francisco in the Location field. 90014 After you filter a data list so that only the records you want to work with are displayed, you can copy those records to another part of the worksheet to the right of the database (or better yet, another Excel sheet in the workbook).Simply select the cells, then click the Copy button on the Home tab or press Ctrl + C, move the cell cursor to the first cell where the copied records are to appear, and then press Enter. After copying the filtered records, you can then redisplay all the records in the database or apply a slightly different filter. 90015 90014 If you find that filtering the data list by selecting a single value in a field drop-down list box gives you more records than you really want to contend with, you can further filter the database by selecting another value in a second field’s drop- down list.90015 90014 For example, suppose that you select Boston as the filter value in the Location field’s drop-down list and end up with hundreds of Boston records displayed in the worksheet. To reduce the number of Boston records to a more manageable number, you could then select a value (such as Human Resources) in the Dept field’s drop-down list to further filter the database and reduce the records you have to work with onscreen. When you finish working with the Boston Human Resources employee records, you can display another set by displaying the Dept field’s drop-down list again and changing the filter value from Human Resources to some other department, such as Accounting.90015 90014 When you’re ready to display all the records in the database again, click the filtered field’s AutoFilter button (indicated by the appearance of a cone filter on its drop-down button) and then click the Clear Filter from (followed by the name of the field in parentheses) option near the middle of its drop-down list. 90015 90014 You can temporarily remove the AutoFilter buttons from the cells in the top row of the data list containing the field names and later redisplay them by clicking the Filter button on the Data tab or by pressing Alt + AT or Ctrl + Shift + L.You can also use Slicer and Timeline filters on your data. 90015 90032 Using Excel’s ready-made number filters: Top 10 90033 90014 Excel contains a number filter option called Top 10. You can use this option on a number field to show only a certain number of records (like the ones with the ten highest or lowest values ​​in that field or those in the ten highest or lowest percent in that field or just those that are above or below average of that field). 90015 90014 To use the Top 10 option in Excel to filter a database, follow these steps: 90015 90002 90003 Click the AutoFilter button on the numeric field you want to filter with the Top 10 option.Then highlight Number Filters in the drop-down list and click Top 10 on its submenu. 90040 90041 90042 Excel opens the Top 10 AutoFilter dialog box. By default, the Top 10 AutoFilter chooses to show the top ten items in the selected field. However, you can change these default settings before filtering the database. 90004 90003 To show only the bottom ten records, change Top to Bottom in the left-most drop-down list box. 90004 90003 To show more or fewer than the top or bottom ten records, enter the new value in the middle text box (that currently holds 10) or select a new value by using the spinner buttons.90004 90003 To show those records that fall into the Top 10 or Bottom 10 (or whatever) percent 90049, 90050 change Items to Percent in the right-most drop-down list box. 90004 90003 Click OK or press Enter to filter the database by using your Top 10 settings. 90014 In the image below, you can see the Employee Data List after using the Top 10 option (with all its default settings) to show only those records with salaries that are in the top ten. David Letterman would be proud! 90015 90004 90013 The Employee Data List after using the Top 10 AutoFilter to filter out all records except for those with the ten highest salaries.90032 Using Excel’s ready-made date filters 90033 90014 When filtering a data list by the entries in a date field, Excel makes available a variety of date filters that you can apply to the list. These ready-made filters include Equals, Before, After, and Between as well as Tomorrow, Today, Yesterday, as well as Next, This, and Last for the Week, Month, Quarter, and Year. Additionally, Excel offers Year to Date and All Dates in the Period filters. When you select the All Dates in the Period filter, Excel enables you to choose between Quarter 1 through 4 or any of the 12 months, January through December, as the period to use in filtering the records.90015 90014 To select any of these date filters, you click the date field’s AutoFilter button, then highlight Date Filters on the drop-down list and click the appropriate date filter option on the continuation menu (s). 90015 90032 Using custom autofilters in Excel 2019 90033 90014 In addition to filtering a data list to records that contain a particular field entry (such as Newark as the City or CA as the State), you can create custom AutoFilters that enable you to filter the list to records that meet less-exacting criteria (such as last names starting with the letter M) or ranges of values ​​(such as salaries between $ 25,000 and $ 75,000 a year).90015 90014 To create a custom filter for a field, you click the field’s AutoFilter button and then highlight Text Filters, Number Filters, or Date Filters (depending on the type of field) on the drop-down list and then click the Custom Filter option at the bottom of the continuation list. When you select the Custom Filter option, Excel displays a Custom AutoFilter dialog box. 90015 Use a custom AutoFilter to display records with entries in the Salary field between $ 25,000 and $ 75,000. 90014 You can also open the Custom AutoFilter dialog box by clicking the initial operator (Equals, Does Not Equal, Greater Than, and so on) on the field’s Text Filters, Number Filters, or Date Filters submenus.90015 90014 In this dialog box, you select the operator that you want to use in the first drop-down list box. Then enter the value (text or numbers) that should be met, exceeded, fallen below, or not found in the records of the database in the text box to the right. 90015 90073 90074 Operators Used in Custom Excel AutoFilters 90075 90076 90077 90078 Operator 90079 90078 Example 90079 90078 What It Locates in the Database 90079 90084 90085 90086 90077 90078 Equals 90079 90078 Salary equals 35000 90079 90078 Records where the value in the Salary field is equal to $ 35,000 90079 90084 90077 90078 Does not equal 90079 90078 State does not equal NY 90079 90078 Records where the entry in the State field is not NY (New York) 90079 90084 90077 90078 Is greater than 90079 90078 Zip is greater than 42500 90079 90078 Records where the number in the Zip field comes after 42500 90079 90084 90077 90078 Is greater than or equal to 90079 90078 Zip is greater than or equal to 42500 90079 90078 Records where the number in the Zip field is equal to 42500 or comes after it 90079 90084 90077 90078 Is less than 90079 90078 Salary is less than 25000 90079 90078 Records where the value in the Salary field is less than $ 25,000 a year 90079 90084 90077 90078 Is less than or equal to 90079 90078 Salary is less than or equal to 25000 90079 90078 Records where the value in the Salary field is equal to $ 25,000 or less than $ 25,000 90079 90084 90077 90078 Begins with 90079 90078 Begins with d 90079 90078 Records with specified fields have entries that start with the letter 90049 d 90050 90079 90084 90077 90078 Does not begin with 90079 90078 Does not begin with d 90079 90078 Records with specified fields have entries that do not start with the letter 90049 d 90050 90079 90084 90077 90078 Ends with 90079 90078 Ends with ey 90079 90078 Records whose specified fields have entries that end with the letters 90049 ey 90050 90079 90084 90077 90078 Does not end with 90079 90078 Does not end with ey 90079 90078 Records with specified fields have entries that do not end with the letters 90049 ey 90050 90079 90084 90077 90078 Contains 90079 90078 Contains Harvey 90079 90078 Records with specified fields have entries that contain the name Harvey 90079 90084 90077 90078 Does not contain 90079 90078 Does not contain Harvey 90079 90078 Records with specified fields have entries that do not contain the name Harvey 90079 90084 90191 90192 90014 If you want to filter records in which only a particular field entry matches, exceeds, falls below, or simply is not the same as the one you enter in the text box, you then click OK or press Enter to apply this Excel filter to the database.However, you can use the Custom AutoFilter dialog box to filter the database to records with field entries that fall within a range of values ​​or meet either one of two criteria. 90015 90014 To set up a range of values, you select the «is greater than» or «is greater than or equal to» operator for the top operator and then enter or select the lowest (or first) value in the range. Then, make sure that the And option is selected, select «is less than» or «is less than or equal to» as the bottom operator, and enter the highest (or last) value in the range.90015 90014 Check out the images above and below to see how Excel filters the records in the Employee Data List so that only those records where Salary amounts are between $ 25,000 and $ 75,000 are displayed. As shown above, you set up this range of values ​​as the filter by selecting «is greater than or equal to» as the operator and 25,000 as the lower value of the range. Then, with the And option selected, you select «is less than or equal to» as the operator and 75,000 as the upper value of the range.The results of applying this filter to the Employee Data List are shown below. 90015 The Employee Data List after applying the custom AutoFilter. 90014 To set up an either / or condition in the Custom AutoFilter dialog box, you normally choose between the «equals» and «does not equal» operators (whichever is appropriate) and then enter or select the first value that must be met or must not be equaled. Then you select the Or option and select whichever operator is appropriate and enter or select the second value that must be met or must not be equaled.90015 90014 For example, if you want Excel to filter the data list so that only records for the Accounting or Human Resources departments in the Employee Data List appear, you select «equals» as the first operator and then select or enter Accounting as the first entry . Next, you click the Or option, select «equals» as the second operator, and then select or enter Human Resources as the second entry. When you then filter the database by clicking OK or pressing Enter, Excel displays only those records with either Accounting or Human Resources as the entry in the Dept field.90015 90203 About the Book Author 90204 90014 90206 Greg Harvey 90207 began training business people to use computers and software back in the days of DOS. He is the author of numerous 90208 For Dummies 90209 books, including all editions of 90208 Excel All-in-One For Dummies 90209, 90208 Excel Workbook For Dummies 90209, and 90208 Excel For Dummies 90209. He has worked as an independent trainer and as an instructor at Golden Gate University.90015 .