Как распределить сумму пропорционально в экселе

Как в excel распределить сумму пропорционально другим суммам

Чтобы распределить сумму пропорционально другим суммам в Excel, нужно выполнить следующие шаги:

1. Введите все суммы в столбец или строку. Например, это может быть столбец «A» с заголовком «Суммы» и рядом с ним другие столбцы с заголовками «Сумма 1», «Сумма 2» и т. д.

2. Выберите итоговую сумму, которую вы хотите распределить пропорционально другим суммам. Например, это может быть значение в ячейке «A10» с заголовком «Итого».

3. Находясь в ячейке, где должно произойти распределение итоговой суммы, введите формулу, которая будет использовать функцию «ДОЛУЧИСЛ» (или «PROPORTIONAL» в английской версии) для распределения суммы пропорционально другим суммам. Например, формула может выглядеть так:

где «A10» — это итоговая сумма, которую нужно распределить, а «Сумма1:Сумма4» — это диапазон, содержащий другие суммы, пропорционально которым нужно распределить итоговую сумму.

4. Нажмите клавишу «Enter», чтобы применить формулу и выполнить распределение.

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

— Используйте функцию «СУММ» (или «SUM» в английской версии) для быстрого подсчета всех сумм в диапазоне. Например, формула может выглядеть так: =ДОЛУЧИСЛ(A10,СУММ(Сумма1),СУММ(Сумма2),СУММ(Сумма3),СУММ(Сумма4)).

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

Как распределить сумму пропорционально в экселе

alt=»Поиск по форуму» width=»16″ height=»16″ />

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Как распределить сумму пропорционально между др. ячейками — Мир MS Excel

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

Всем привет !
Подскажите как распределить сумму из одной ячейки пропорционально между суммами в других ячейках ?

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Здравствуйте.
Например, так

К сообщению приложен файл:

2711820.xls
(42.0 Kb)

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

К сообщению приложен файл:

3966414.xls
(43.5 Kb)

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Замечаний:
0% ±

Excel 2013

Да именно так. Спасибо большое !

Автор:
Robert Simon

Дата создания:
21 Июнь 2021

Дата обновления:
10 Апрель 2023

Сортировка данных в MS Excel

Видео: Сортировка данных в MS Excel

Содержание

Шаг 1
Шаг 2
Шаг 3
Шаг 4
Шаг 5
Шаг 6

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

Шаг 1

Откройте новую электронную таблицу Excel 2010.

Шаг 2

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

Шаг 3

Щелкните ячейку «B1», которая находится справа от первой ячейки. Введите общее количество подпериодов в общем периоде. Например, если вы распределяете ежемесячный счет пропорционально на несколько дней, укажите количество дней в месяце в этой ячейке.

Шаг 4

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

Шаг 5

Щелкните ячейку «B2», которая находится чуть ниже общего количества подпериодов. Введите «= A1 / B1» без кавычек, чтобы получить стоимость для каждого подпериода.

Щелкните ячейку «C3» и введите «= B2 * C1» без кавычек, чтобы получить желаемую пропорциональную сумму.

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

Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:

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

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

Процентное распределение по динамической формуле Excel

Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.

Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.

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

1С:Предприятие 8.2 /
Бухгалтерия для Украины /
Учет НДС

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

Например, организация одновременно продает фармацевтические препараты (освобожденные от НДС) и косметические средства (облагаемые НДС по ставке 20%). Другой пример, организация производит различную печатную продукцию — периодические издания и обычную художественную литературу (освобожденные от НДС), а также учебники и учебные пособия (облагаемые НДС по ставке 20%).

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

Если закупаемый актив можно четко отнести к использованию в облагаемой НДС хозяйственной деятельности, то при наличии входящего НДС (ставка 20%) вся сумма НДС включается в налоговый кредит. Налоговая и бухгалтерская стоимость актива определяется по стоимости закупки без НДС. Налоговое назначение НДС при закупке указывается «Обл. НДС»,

Если закупаемый актив можно четко отнести к использованию в не облагаемой НДС хозяйственной деятельности, то независимо от наличия входящего НДС (ставка 20% или без НДС) вся стоимость закупки включается в налоговую и бухгалтерскую стоимость актива. Сумма НДС не включается в налоговый кредит. Налоговое назначение закупки указывается «Необл. НДС, любая хоз. «,

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

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

Для пропорционального распределения НДС следует в начале года или первого месяца, когда появились два вида деятельности с точки зрения обложения НДС — зафиксировать коэффициент распределения. Операция отражается документом «Установка коэффициента пропорционального отнесения НДС на кредит».

Рис.1. Установка коэффициентов пропорционального распределения НДС

Коэффициент можно рассчитать автоматически, если в ИБ есть данные прошлого года (или первого месяца в 2011 году, когда появились два вида деятельности НДС). Такой расчет производится по кнопке «Заполнить и рассчитать». При этом выбираются данные в зависимости от установленного переключателя — объемы продаж или прошлого года, или указанного месяца. Если такой информации в ИБ нет, то коэффициент можно рассчитать вручную и ввести в поле «Коэфф. (доля использования)». Если весь объем продаж взять за единицу, то коэффициент показывает — какова доля облагаемых НДС продаж.

В документах приобретения, оплат поставщикам (для договоров с упрощенным налоговым учетом) и в регламентном документе » Регистрация авансов в налоговом учете » (для договоров со сложным налоговым учетом) указываются такие налоговые параметры операций:

Сумма НДС — полная в соответствие с документами поставщика,

Налоговое назначение НДС = «Пропорц. обл. НДС».

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

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

При отражении покупки запасов с налоговым назначением НДС «Пропорц. обл. НДС» при проведении документа автоматически рассчитывается сумма налогового кредита в соответствие с установленным коэффициентом (см. Рис. 6.35).

Рис.2. Покупка запасов на распределяемую по НДС деятельность

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

При покупке необоротного актива часть НДС включается в налоговый кредит, а остальная сумма — в стоимость будущего необоротного актива,

При получении услуг часть НДС включается в налоговый кредит, а остальная сумма — в затраты, на которые отнесены полученные услуги,

В платежных документах и при регистрации авансов —» рассчитывается только; сумма НДС, включаемая в налоговый кредит — для формирования проводки по НДС; налоговая стоимость актива не формируется по причине отсутствия в операции актива.

Для регистрации подтвержденного налогового кредита вводится документ » Регистрация входящего налогового документа «.

Рис.3. Регистрация входящего налогового документа

При этом сумма операции разбивается по двум строкам документа:

База и НДС, включенный в налоговый кредит — с видом «облагаемая НДС деятельность» и соответствующей строкой налоговой декларации,

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

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

Такое разбиение на две строки производится:

Либо автоматически при создании налогового документа вводом на основании первичного документа с указанными налоговыми параметрами и зафиксированным «событием» по НДС,

Либо полуавтоматически — по кнопке «Добавить строки по пропорциональному НДС» — при наличии в документе одной строки с общей суммой операции и флагом «Пропорционально». Второй вариант используется, когда налоговый документ:

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

Такое заполнение документа «Регистрация входящего налогового документа» обеспечивает заполнение отчета «Реестр налоговых документов» в соответствии с п. 11.10 Порядка 1002, а также расшифровку сумм, включаемых и не включаемых в налоговый кредит, в Приложении 5 к Декларации.

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

Формула процентного распределения в Excel

Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:

Статическая формула.

Процентное распределение по динамической формуле Excel

Динамическая формула.

Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.

1 337 978 665,76/2643 — значение всех ячеек, кроме одной. В этой избранной — остаток.

Не так? Файла-то нет.

1 337 978 665,76/2643 — значение всех ячеек, кроме одной. В этой избранной — остаток.

Не так? Файла-то нет. vikttur

Сообщение 1 337 978 665,76/2643 — значение всех ячеек, кроме одной. В этой избранной — остаток.

Не так? Файла-то нет. Автор — vikttur
Дата добавления — 18.06.2013 в 17:06

Как пропорционально распределить в Excel (за 6 шагов)

Видео: Сортировка данных в MS Excel

Содержание

Шаг 1
Шаг 2
Шаг 3
Шаг 4
Шаг 5
Шаг 6

Шаг 1

Откройте новую электронную таблицу Excel 2010.

Шаг 2

Шаг 3

Шаг 4

Шаг 5

Шаг 6

Щелкните ячейку «C3» и введите «= B2 * C1» без кавычек, чтобы получить желаемую пропорциональную сумму.

Как распределить сумму пропорционально в экселе

СУММПРОИЗВ (SUMPRODUCT) в Excel. Гораздо больше, чем сумма произведений

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

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

Как и в первой варианте, нам нужно зафиксировать цифру по итоговым продажам, однако, так как в расчетах не принимает участие отдельная ячейка с нужным значением, нам нужно проставить знаки “$” перед обозначениями строк и столбцов в адресах ячеек диапазона суммы: =D2/СУММ($D1500:$D$15) .

Как посчитать процент от числа и долю в Эксель

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

Вариант №1: просматриваем всю сумму

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

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

EXCEL — как распределить сумму X на N месяцев — CodeRoad

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

Находим процент от числа

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

Математическая формула для расчета выглядит следующим образом:

Например, давайте узнаем, какое число составляет 15% от 90.

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

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

Расчет пропорций при распределении расходов | Такском

Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

1 ответ

Возможный Дубликат : Пропорционально распределить (пропорционально распределить) значение по набору значений Я искал алгоритм, чтобы распределить общую сумму ежемесячно на основе количества дней в году в C# так, чтобы сумма всех пропортонов была равна общей стоимости. Диапазон дат в течение года и.

B1 , B2 и B3 являются входными ячейками. B1 должна быть датой, а не строкой.

D1 — O1 — это месяцы. Значения должны быть датами, а не строками, но затем могут быть отформатированы так, чтобы показывать только месяц и год. Например, формат MMM YYYY .

Вам нужно только ввести D1 и E1 в качестве дат 2017-01-01 и 2017-02-01 , затем выберите D1:E1 и заполните справа. Затем будет создана серия , имеющая от шага к шагу разницу в E1 — D1 , что в данном примере составляет 1 месяц.

и может быть заполнен справа по мере необходимости. В примере до O2 .

Теперь, если вы изменяете какую-либо из входных ячеек, значения в D2 на O2 также изменятся из-за формулы.

7 полезных формул для тех, кто считает деньги в эксель-таблице

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

Как посчитать процентное распределение в Excel по формуле

Формула процентного распределения в Excel

Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:

Процентное распределение по динамической формуле Excel

Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.

Как раскидать сумму пропорционально в excel

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

Итак, классика!

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

Таким образом все сводится к такому вот методу:

Здесь базой является количество, сумма базы = 6, распределяемая сумма = 100. Коэффициент = распределяемая сумма / сумма базы = 100 / 6 = 16,(6) («Шесть в скобках» — это то, как нас учили записывать периодичские дроби. Если кого-то учили иначе — проьба иметь это ввиду). Далее в каждой строке я округляю результат до копеек.

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

Давайте рассмотрим случай, когда тот парень был к нам не так благосклонен, а именно — давайте распределим 10 на 3:

В итоге у нас не хватило одной копейки. Для того, чтобы решить эту проблему, необходимо учесть остаточек в конце. У нас распределенная сумма получилась равна 9,99, а сумма, которую нужно распределить — 10. Разницу, обычно, добавляют к последней строке. Т.е. в последней строке у нас будет 3,34, «чтобы не нарушать отчетности» (с).

Все хрошо, пока потерянная в ходе округления сумма мала и не играет большой роли. Но если мы попытаемся таким же образом распределить 10 на 30 строк, то внезапно окажется, что к последней строке нам нужно прибавить уже не 1 копейку, а 10. Можно, конечно, прибавить сумму остатка к последней строке:

В последней строке в итоге будет сумма 0,33 + 0,10 = 0,43. Если мы распределяем какие-нибудь ксвенные затраты на количество выпуска, то для каждой статьи затрат может набраться весьма большое отклонение, которое все целиком упадет на последнюю строчку. Таким образом продукт, выпущенный нами в последнюю очередь, вберет в свою себестоимость все те отклонения и станет «золотым» )))

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

Новое решение!

Давным-давно, кажется в позапрошлую работу, меня попросили создать обработку, которая бы перекраивала контуры полей, перераспределяя на их новую площадь какие-то старые остатки на счетах учета затрат на дату распределения. Там как раз сумма распределялась между новыми площадями пропорционально новому метражу. Звучит пространно, но примите на веру (как древние греки), что это относится к обсуждаемой нами задаче распределения суммы по базе. И тогда я как раз «родил» (ага, прям как Авраам Исаака) алгоритм распределения, после которого нет остатка. Странно, но тогдашний мой руководитель так и не понял суть алгоритма, хотя после теста сказал, что все работает и оставил как есть. Западные программисты в таких случаях просто стараются не использовать подобные алгоритмы, так что честь и хвала программистам российским, которые используют и то, в чем не понимают )))

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

Пример 1. Распределение премии

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

Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат — это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).

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

Начиная с Excel 2010

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

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

3. Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение

4. Ограничения задаются с помощью кнопки Добавить. Задание ограничений, пожалуй, не менее важный и сложный этап, чем построение формул. Именно ограничения обеспечивают получение правильного результата. Ограничения можно задавать как для отдельных ячеек, так и для диапазонов. Помимо всем понятных знаков =, >=,

5. Кнопка, включающая итеративные вычисления с заданными параметрами.

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

Начиная с Excel 2010

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

Решение данной задачи выглядит так

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

Разберём еще одну задачу оптимизации (получение максимальной прибыли)

Пример 2. Мебельное производство (максимизация прибыли)

Фирма производит две модели А и В сборных книжных полок.

Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.

Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В — 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.

Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В — 30 мин. в неделю можно использовать 160 ч машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В — 120 руб. прибыли?

Порядок действий нам уже известен.

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

Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры

1. Целевая ячейка F7 содержит формулу для расчёта прибыли

2. Параметр оптимизации — максимум

3. Изменяемые ячейки F3:G3

4. Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D9); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D8). Здесь вместо ссылок на ячейки D8 и D9 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице

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

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

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

Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае — целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.

Пример 3. Транспортная задача (минимизация затрат)

На заказ строительной компании песок перевозиться от трех поставщиков (карьеров) пяти потребителям (строительным площадкам). Стоимость на доставку включается в себестоимость объекта, поэтому строительная компания заинтересована обеспечить потребности своих стройплощадок в песке самым дешевым способом.

Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».

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

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

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

Запускаем Поиск решения и устанавливаем необходимые параметры (см. рисунок)

Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже

Иногда транспортные задачи усложняются с помощью дополнительных ограничений. Например, по каким-то причинам невозможно возить песок с карьера 2 на стройплощадку №3. Добавляем ещё одно ограничение $D$13=0. И после запуска Поиска решения получаем другой результат

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

Поиск решения EXCEL (1.3). Распределение ресурсов (ограничение по количеству оборудования, несколько периодов)

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

Задача оптимального распределения ресурсов (распределительная задача) заключается в отыскании наилучшего распределения ресурсов, при котором либо максимизируется результат, либо минимизируются затраты. Задача, в которой минимизируются затраты, понесенные в одном периоде решена в статье Поиск решения MS EXCEL (1.2). Распределение ресурсов (ограничение по количеству оборудования) , и имеет смысл предварительно познакомиться с изложенным там материалом. В этой статье мы решим аналогичную задачу, но для случая работы оборудования в нескольких периодах (пример с сайта www.solver.com ).

Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .

Задача

Предприятие выпускает монопродукт (только один вид изделия и ничего более) и ему необходимо выполнить заказ клиента. Выпуск продукции осуществляется в течение 5 дней. Отгрузка заказа ежедневная. На предприятии 3 типа оборудования. Каждый тип оборудования выпускает один и тот же продукт. Производительность каждого типа оборудования разная. Каждый тип оборудования имеет постоянную и переменную часть расходов. Переменная часть расходов пропорциональна количеству произведенных изделий. Имеется ограниченное количество единиц оборудования каждого типа (но общее количество оборудования избыточно для выполнения заказа). Требуется минимизировать расходы на оборудование при условии выполнения заказа.

Создание модели

На рисунке ниже приведена модель, созданная для решения задачи (см. файл примера ).

Предприятие несет расходы в зависимости от типа оборудования: использование оборудования типа Alpha-3000 самое дорогое в эксплуатации, но оно и самое производительное. Оборудование типа Alpha-1000 самое дешевое в эксплуатации, но оно и менее производительное. Задача Поиска решения выбрать наиболее дешевое оборудование, так чтобы заказ был выполнен (мощностей Alpha-1000 не хватит для выполнения заказа). Казалось бы, решение очевидно (взять по максимуму дешевое оборудование, остальную производительность обеспечить более дорогим). Однако, если учесть, что из-за низкой производительности дешевых машин приходится их брать больше, неся существенные постоянные расходы, то решение уже не кажется очевидным.

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

Ограничения (выделено синим) . Количество задействованных машин должно быть целым числом. Количество задействованных машин каждого типа должно быть не больше, чем имеется в наличии (используются именованные диапазоны Alpha XXXX _Задействовано и Alpha XXXX _в_наличии ). Всего должно быть выпущено продукции не меньше чем величина заказа (используется именованный диапазон Продукции выпущено_Итого ). В день возможно производить больше продукции, чем требуется в день заказа, излишек переносится на следующий день. Также необходимо ограничить производительность задействованного оборудования. Производительность задается не для каждой единицы, а для типа в целом (используются именованные диапазоны Продукции выпущено и Макс_производительность_задейств_машин ).

Целевая функция (выделено красным) . Целевая функция – это сумма операционных расходов за 5 дней. Операционные расходы, понесенные за день, задается формулой =СУММПРОИЗВ(B19:B21; Расходы_переменные)+ СУММПРОИЗВ(B13:B15; Расходы_постоянные) B19:B21 – количество продукции, выпущенной в определенный день. B13:B15 — количество задействованных машин в определенный день.

Это суммарные операционные расходы (переменная и постоянные части). Сумма операционных расходов за 5 дней должна быть минимизирована.

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

Теперь в диалоговом окне можно нажать кнопку Найти решение .

Результаты расчетов

Поиск решения подберет оптимальный набор единиц оборудования по типам и их производительность, при котором операционные расходы будут минимальные, а заказ выполнен. В нашей задаче было установлено целочисленное ограничение, что существенно усложняет задачу поиска и, соответственно, сказывается на скорости расчета. Как показано на рисунке выше, Целочисленная оптимальность была выбрана 0% ( Целочисленная оптимальность (Integer Optimality) позволяет Поиску решения остановить поиск, в случае, если он найдет целочисленное решение, в пределах указанного процента от оптимального). В нашем случае (0%), требуется найти лучшее из известных Поиску решения решений. Поиск в этом случае занял 8 секунд, результат 23 311,50. Установив Целочисленную оптимальность 1%, поиск займет 0,2 сек, результат 23 370,50 (отличие на 0,3%). Это информация к размышлению: стоит ли увеличение точности на 0,3% уменьшения скорости расчетов более чем на порядок? Решать Вам. В любом случае, первые расчеты модели лучше проводить при Целочисленной оптимальности не равной 0%.

Распределение суммы по базе

Афиняне! Повсему вижу я, что Вы как-то по-особеному набожны, ибо проходя и осматривая Ваши святыни, я наткнулся и на жертвенник неведомому богу.

Где-то в библии в адрес древних греков.