Как сделать ограничение целое в экселе

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

Для изготовления различных изделий А и В используются три вида сырья. На производство единицы изделия А требуется затратить сырья первого вида — А1(кг), сырья второго вида — А2(кг), сырья третьего вида — А3(кг). На производство единицы изделия В требуется затратить сырья первого вида — В1(кг), сырья второго вида — В2(кг), сырья третьего вида — В3(кг).

Производство обеспечено сырьем первого вида в количестве Р1(кг), сырьем второго вида в количестве Р2(кг), сырьем третьего вида в количестве Р3(кг).

Прибыль от реализации единицы готового изделия А составляет С1(руб.), а изделия В составляет С2(руб.).

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

Пусть (для примера):
$$
А_1 = 15, A_2 = 4, A_3 = 4 (Затраты на производство изделия A); \\
B_1 = 2, B_2 = 3, B_3 = 14 (Затраты на производство изделия B); \\
P_1 = 285, P_2 = 113, P_3 = 322 (Количество сырья); \\
C_1 = 15, C_2 = 9 (Прибыль от реализации продукции)
$$

Решение:

Сформируем в Excel таблицу исходных значений по варианту:

Принимаем за х1 объем производства изделий А, а х2 – объем производства изделий В.

Целевая функция отражает суммарную прибыль от реализации изделий: f(x) = 15х1+9х2 → mах , где 15*х1 отражает прибыль от реализации изделий А, а 9*х2 — прибыль от реализации изделий В.

Ограничения имеют вид:
1) 15*х1+2*х2 ≤285 – по запасам сырья первого вида, (кг).
2) 4*х1+3*х2≤113 – по запасам сырья второго вида, (кг).
3) 4*х1+14*х2≤322 – по запасам сырья третьего вида, (кг).

х1≥0, х2≥0, параметры управления принимают значения больше либо равны нулю.
Таким образом, формальная постановка данной задачи имеет вид:
$$
15\cdot x_1 + 9 \cdot x_2 \rightarrow max \\
\begin
\begin
15 \cdot x_1 + 2\cdot x_2 \leq 285, \\
4 \cdot x_1 + 3 \cdot x_2 \leq 113, \\
4 \cdot x_1 + 14 \cdot x_2 \leq 322, \\
x_1 \geq 0, \\
x_2 \geq 0
\end
\end
$$

Создание экранной формы и ввод исходных данных

В этой форме каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Так, например, переменным задачи соответствуют ячейки B3 (х1), C3 (х2), коэффициентам ЦФ соответствуют ячейки B5 (с1 = 15), C5 (с2 = 9), правым частям ограничений соответствуют ячейки D15 (р1 = 285), D16 (р2 = 113), D17 (р3 =322) и т.д.

Ввод зависимостей из формальной постановки задачи в экранную форму

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B10, C10 — 1-е ограничение; B11, С11 — 2-е ограничение и B12, С12 — 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в таблице:

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

Задание ячеек переменных и ограничений (граничных условий)

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

Хороший инструмент — надстройка Поиск решения в MS Excel!

Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:

Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти. Отметить флагом Поиск решения, нажать ОК

На ленте на вкладке Данные появился Поиск решения:

в ячейках столбца B указываем 0 или 1 (сейчас неважно, что конкретно)
в ячейке C4 формула =A4*B4
в ячейках C5:C14 — аналогично, с учетом номера строки
в ячейке C3 формула =СУММ(C4:C14)

Теперь запускаем Поиск решения. И заполняем:

Оптимизировать целевую функцию: $C$3
До: Значения: 10
Изменяя ячейки переменных: $B$4:$B$14
В соответствии с ограничениями: — добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть

Не очень частый, но и не экзотический случай. На моих тренингах такой вопрос задавали не один и не два раза 🙂 Суть в том, что мы имеем конечный набор каких-то чисел, из которых надо выбрать те, что дадут в сумме заданное значение.

В реальной жизни эта задача может выглядеть по-разному.

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

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

Способ 1. Надстройка Поиск решения (Solver)

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

в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти (File — Excel Options — Add-ins — Go)
в Excel 2003 и старше — открыть меню Сервис — Надстройки (Tools — Add-ins)

и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.

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

После ввода формулы ее необходимо ввести не как обычную формулу, а как формулу массива, т.е. нажать не Enter, а Ctrl+Shift+Enter. Похожая формула используется в примере о ВПР, выдающей сразу все найденные значения (а не только первое).

Теперь перейдем на вкладку (или в меню) Данные и запустим инструмент Поиск решения (Data — Solver):

В открывшемся окне необходимо:

Задать как целевую функцию (Target Cell) — ячейку вычисления погрешности подбора E5. Чуть ниже выбрать опцию — Минимум, т.к. мы хотим подобрать числа под заданную сумму с минимальной (а лучше даже нулевой) погрешностью.
В качестве изменяемых ячеек переменных (Changing cells) задать диапазон столбца переключателей B1:B20.
С помощью кнопки Добавить (Add) создать дополнительное условие на то, что ячейки диапазона B1:B20 должны быть бинарными (т.е. содержать только 0 или 1):

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

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

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

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

И весьма удобно будет вывести все найденные решения, сохраненные в виде сценариев, в одной сравнительной таблице с помощью кнопки Отчет (Summary):

Способ 2. Макрос подбора

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

Для использования макроса нажмите сочетание Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert — Module и скопируйте туда этот код:

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

в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти
в Excel 2003 и старше — открыть меню Сервис — Надстройки

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

Как отключить округление в Эксель

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

Увеличение разрядности

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

В более новых версиях Excel этот метод будет иметь другой вид:

Настройка формата

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

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

Изменение формата

Как сделать, чтобы данные в таблице Эксель округлялись

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

Кнопки в ленте

Наиболее простой метод, как сделать, чтобы Excel снова округлял, выделить одну / несколько ячеек, а после пройти такие шаги:

Округление через форматирование

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

Задание точности расчетов

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

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

Изменение функции

Дополнительный метод

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

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

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

Первый способ.

Второй способ.

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

Читайте также: