Как пропорционально разделить сумму по отношению к числам excel

Как пропорционально разделить сумму по отношению к числам excel

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

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

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

Excel Windows
и
Excel Macintosh

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

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

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

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

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

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

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

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

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

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

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

Процентное распределение по динамической формуле 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

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

СУММПРОИЗВ (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 очень проста:

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

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

Процентное распределение по динамической формуле 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%.

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

• 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! ☝

Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

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

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. ��

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как одной из самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
• справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

�� Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

• первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
2. выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

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

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .

Константа / в формуле ячейка не изменяется

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

Один из самых простых способов сложить все выделенные ячейки — это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку) .

Что нужно сделать, чтобы посчитать сумму определенных ячеек:

1. сначала выделяем ячейки (см. скрин ниже ��) ;
2. далее открываем раздел «Формулы» ;
3. следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
5. таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

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

1. » =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 — это столбик, по которому будет проверяться наше условие;
4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже) .

Сумма с условием

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

• A2:A7 — диапазон, в котором будут проверяться и считаться строки;
• A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

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

�� В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

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

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно ��.

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

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться 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%.

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

• 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! ☝

Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

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

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. ��

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как одной из самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
• справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

�� Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

• первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
2. выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

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

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .

Константа / в формуле ячейка не изменяется

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

Один из самых простых способов сложить все выделенные ячейки — это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку) .

Что нужно сделать, чтобы посчитать сумму определенных ячеек:

1. сначала выделяем ячейки (см. скрин ниже ��) ;
2. далее открываем раздел «Формулы» ;
3. следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
5. таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

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

1. » =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 — это столбик, по которому будет проверяться наше условие;
4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже) .

Сумма с условием

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

• A2:A7 — диапазон, в котором будут проверяться и считаться строки;
• A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

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

�� В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

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

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно ��.

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

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. ��

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

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

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

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

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

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

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

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

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

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

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

Есть три числа, в трех ячейках
Как должна выглядеть формула, которая, допустим, под этими ячейками, в трех дополнительных ячейках укажет процентное соотношение между этими числами?

К примеру, есть числа:

Естественно, что выглядеть будет это как 10%, 40%, 50%, что в сумме будет 100%

Но если эти числа 45, 4, 8. (В сумме 57). То как вычислить процентное соотношение между ними?

RPI.su — самая большая русскоязычная база вопросов и ответов. Наш проект был реализован как продолжение популярного сервиса otvety.google.ru, который был закрыт и удален 30 апреля 2015 года. Мы решили воскресить полезный сервис Ответы Гугл, чтобы любой человек смог публично узнать ответ на свой вопрос у интернет сообщества.

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

Чтобы связаться с нами по любому вопросу О САЙТЕ (реклама, сотрудничество, отзыв о сервисе), пишите на почту [email protected] . Только все общие вопросы размещайте на сайте, на них ответ по почте не предоставляется.

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

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

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

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

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

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

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

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

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

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

Первое слагаемое должно содержать 2 части числа, а второе 3, значит число 50 содержит 5 таких частей (2 + 3 = 5), следовательно каждая такая часть будет равна:

Число 10 – одна часть, теперь надо умножить эту часть на числа, пропорционально которым требовалось разделить число 50:

Пример 2. Разделить число 90 на три слагаемых пропорционально числам 1, 2 и 3.

90 : (1 + 2 + 3) = 90 : 6 = 15

Ответ: 1:2:3 = 15:30:45

Длинные отношения вида 1:2:3 называются сложными. Сложные отношения – это условные записи, показывающие сколько долей содержит каждая часть. Если члены сложного отношения дробные, то, приведя их к общему знаменателю и умножив на него, можно заменить отношение дробных чисел отношением целых.

Пример. Разделить число 66 на такие три части, чтобы первая относилась ко второй, как 3:2, а вторая к третьей, как 5:4.

Решение.Первый способ: обозначим искомые части буквами a, b и c. Так как отношение не изменится, если оба члена умножить на одно и то же число, то умножим члены первого отношения на 5, а второго на 2:

a:b = 3:2 = 15:10	значит a:b:c = 15:10:8
b:c = 5:4 = 10:8

так как 15 + 10 + 8 = 33, то

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

Второй способ: обозначим искомые части буквами a, b и c:

Если первая часть a равна 3, вторая b равна 2, то третью часть c можно определить из пропорции:

следовательно c равно:

c =	2 · 4	=	8
	5		5

Умножив все члены полученного сложного отношения на 5, чтобы избавиться от дробного члена, получим:

так как 15 + 10 + 8 = 33, то

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

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

Итак, площадь двух участков вместе 32 кв. м. Определим теперь, сколько штук рассады приходится на 1 кв. м:

224 : 32 = 7 (штук).

Зная сколько рассады приходится на 1 кв. м, мы легко вычислим число штук рассады на 8 кв. м и на 24 кв.. м, т. е. ответим на вопрос задачи:

7 • 24 = 168 (штук).

Подумаем теперь, какие величины входят в нашу задачу и как они связаны между собой. В условие задачи входят две величины: 1) количество штук рассады, 2) площадь участка. Эти две величины прямо пропорциональны одна другой, потому что, чем больше площадь участка, тем больше на нём можно посадить рассады. Расположим числа, с которыми мы имели дело в задаче, так, чтобы их удобно было сравнивать:

8 кв. м — 56 штук
24 кв. м — 168 штук

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

Итак, в этой задаче мы разделили число штук рассады пропорционально площадям двух участков. Это и есть одна из возможных задач на пропорциональное деление. Как же решаются такие задачи? В задаче требовалось число 224 разделить на две части, пропорциональные числам 8 и 24, т. е. разделить это число на такие две части, которые относились бы между собой так же, как 8 : 24. Обозначим величину первой части буквой х, а второй части — у и напишем отношение этих частей:

Для нахождения этих частей были выполнены следующие действия. Число 224 разделили на сумму чисел 8 и 24 и затем найденное частное последовательно умножили сначала на 8, а потом на 24, т. е.

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

Рассмотрим другую задачу: «За три куска мыла одного и того же сорта заплатили 40 руб, Сколько заплатили за каждый из них, если первый кусок весил 2 кг, второй 3 кг и третий 5 кг?»

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

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

Таким образом, первый кусок мыла стоит 8 руб., второй 12 руб. и третий 20 руб. Найденные числа рублей х, у, z находятся между собой в таких же отношениях, как и данные в задаче числа весовых единиц, т. е.

х : у : z = 8 : 12 : 20 = 2 : 3 : 5.

Рассмотрим теперь задачу с отвлечёнными числами. Разделить число 180 на три части пропорционально числам 3; 5; 7. Иными словами: в этой задаче требуется разложить число 180 на такие три слагаемых, чтобы первое относилось ко второму, как 3 к 5, второе относилось к третьему, как 5 к 7 и, наконец, первое к третьему, как 3 к 7. Сокращённо это можно написать так:

х : у : z = 3 : 5 : 7,

где х, у, z обозначают соответственно первое, второе и третье число.

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

Применяя указанное выше правило, можем написать:

Полученные три числа удовлетворяют условию задачи: они в сумме составляют 180, т. е.

36 + 60 + 84 = 180 и 3 : 5 : 7 = 36 : 60 : 84.

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

Задача 1. Определить квартирную плату за каждую из двух комнат (8 кв. м и 24 кв. м), если за обе вместе нужно заплатить 64 руб.

Обозначим плату за 1 кв. м буквой х; тогда за первую комнату нужно будет заплатить 8x, а за вторую — 24x. Значит, за обе комнаты вместе надо заплатить 8х + 24х, что составляет 64 руб. Следовательно, можно записать равенство:

х = 64 : 32 = 2 (руб.).

Дальше задача решается так:

Задача 2. Найти стоимость каждого из трёх пакетов муки, если все три пакета стоят 40 руб., а вес первого 2 кг, второго 3 кг и третьего 5 кг.

Обозначим цену одного килограмма буквой х, тогда:

2 кг будут стоить 2х ;

а вся мука будет стоить:

10х = 40; х = 40 : 10 = 4 (руб.).

После этого легко определить стоимость каждого пакета;

2х = 2 • 4 = 8 (руб.);

3х = 3 • 4 = 12 (руб.);

5х = 5 • 4 = 20 (руб.).

Задача 3. Разделить число 1 800 на три слагаемых пропорционально числам: 3, 5 и 7.

Рассуждаем так: в первом слагаемом 3 части, во втором 5 и в третьем 7.

Обозначая величину одной части буквой х, можно написать:

3х + 5х + 7х =1 800.

15х = 1 800; х = 1 800 : 15 = 120.

3х = 3 • 120 = 360;

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

Задача. Разделить 968 на четыре части пропорционально числам: 2/3 , 3/4 , 2/5 и 3/8.Это значит, что надо найти четыре таких числа (х, у, z, t), отношения которых были бы равны соответствующим отношениям данных чисел, т. е.

а сумма x + y + z + t = 968.

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

Отбрасывая общий знаменатель 40, получим: 60 : 30 : 16 : 15. Вычислим последовательно каждое из искомых чисел:

§ 139. Деление числа на части обратно пропорционально данным числам.

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

Задача. В двух полевых бригадах 70 колхозников. Каждой бригаде поручено обработать одинаковые участки. Сколько колхозников в каждой бригаде, если первая бригада выполнила работу в 6 дней, а вторая — в 8 дней? (Предполагается, что все колхозники работают с одинаковой производительностью труда.)

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

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

Сравним теперь работу, которую выполняет в день первая бригаду с работой, выполняемой в день второй бригадой. Эти работы выражаются дробями 1/6 и 1/8. Первая дробь больше второй. Значит, первая бригада в один день может делать больше, чем вторая. А так как все колхозники работают с одинаковой производительностью труда, то, значит, в первой бригаде больше колхозников, чем во второй. Таким образом, число колхозников в каждой бригаде пропорционально той работе, которую каждая бригада может выполнить. Значит, данное в задаче число 70 мы должны разделить на две части пропорционально числам: 1/6 и 1/8 . С задачами такого типа мы уже знакомы. Приведя дроби 1/6 и 1/8 к общему знаменателю, мы найдём числа, пропорционально которым следует разделить число 70:

т. е. число 70 нужно разделить на две части пропорционально числам 4 и 3. Обозначим число колхозников первой бригады буквой х, а второй — буквой у и вычислим:

Итак, в первой бригаде было 40 человек, а во второй 30. Рассмотрим теперь метод решения этой задачи. В условие задачи входят три числа: 70 (человек), 6 (дней) и 8 (дней). В процессе решения мы ввели еще два числа: 1/6 и 1/8, и пропорционально этим дробям разделили число 70 на две части. Очевидно, что число 6 и число 1/6 взаимно обратны. Так же взаимно сбратны числа 8 и 1/8 .

Для решения задачи требуется разделить 70 рабочих на две неравные бригады, исходя из количества времени (дней), затраченного ими на работу. Это время выражается числами 6 (дней) и 8 (дней). Вместо этих двух чисел мы берём обратные им числа 1/6 и 1/8 и пропорционально им делим число 70.

Такая замена сделана нами потому, что число работников не прямо, а обратно пропорционально времени, затраченному на работу. О такой задаче принято говорить, что в ней число 70 разделено на две части обратно пропорционально числам 6 и 8, т. е. в ней первая часть относится ко второй не как 6 к 8, а как 8 к 6.

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

Задача. Разделить 65 на три части обратно пропорционально числам: 2, 3, 4.

Мы теперь знаем, что разделить число на части обратно пропорционально нескольким числам — это значит разделить его на столько же частей прямо пропорционально обратным числам.

Напишем числа, обратные данным в задаче:

данные числа 2, 3, 4;

обратные числа 1/2, 1/3 , 1/4.

Пропорционально этим последним и нужно разделить число 65. Приведём дроби к общему знаменателю:

а потом освободимся от него:

Пример 1. Разделить число 50 на части пропорционально числам 2 и 3.

Пример 2. Разделить число 90 на три слагаемых пропорционально числам 1, 2 и 3.

90 : (1 + 2 + 3) = 90 : 6 = 15

Длинные отношения вида 1:2:3 называются сложными. Сложные отношения – это условные записи, показывающие сколько долей содержит каждая часть. Если члены сложного отношения дробные, то, приведя их к общему знаменателю и умножив на него, можно заменить отношение дробных чисел отношением целых.

Пример. Разделить число 66 на такие три части, чтобы первая относилась ко второй, как 3:2, а вторая к третьей, как 5:4.

a:b = 3:2 = 15:10	значит a:b:c = 15:10:8
b:c = 5:4 = 10:8

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

Свежие материалы

Списание стоимости ОС производственным способомСпособ списания стоимости основного средства производственным методом является нелинейным, то есть…

Земельный налог: сроки уплаты авансовых платежей по земельному налогуСроки уплаты авансовых платежей по земельному налогу…

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

Положение о стимулирующих выплатах УТВЕРЖДЕНО Приказом директора МБУК "Карагайский районный дом культуры и досуга"от "15"…

No related posts.

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

Есть три числа, в трех ячейках
Как должна выглядеть формула, которая, допустим, под этими ячейками, в трех дополнительных ячейках укажет процентное соотношение между этими числами?

К примеру, есть числа:

Естественно, что выглядеть будет это как 10%, 40%, 50%, что в сумме будет 100%

Но если эти числа 45, 4, 8. (В сумме 57). То как вычислить процентное соотношение между ними?

RPI.su — самая большая русскоязычная база вопросов и ответов. Наш проект был реализован как продолжение популярного сервиса otvety.google.ru, который был закрыт и удален 30 апреля 2015 года. Мы решили воскресить полезный сервис Ответы Гугл, чтобы любой человек смог публично узнать ответ на свой вопрос у интернет сообщества.

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

Чтобы связаться с нами по любому вопросу О САЙТЕ (реклама, сотрудничество, отзыв о сервисе), пишите на почту [email protected] . Только все общие вопросы размещайте на сайте, на них ответ по почте не предоставляется.

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

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

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

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

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

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

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

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

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

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

Первое слагаемое должно содержать 2 части числа, а второе 3, значит число 50 содержит 5 таких частей (2 + 3 = 5), следовательно каждая такая часть будет равна:

Число 10 – одна часть, теперь надо умножить эту часть на числа, пропорционально которым требовалось разделить число 50:

Пример 2. Разделить число 90 на три слагаемых пропорционально числам 1, 2 и 3.

90 : (1 + 2 + 3) = 90 : 6 = 15

Ответ: 1:2:3 = 15:30:45

Длинные отношения вида 1:2:3 называются сложными. Сложные отношения – это условные записи, показывающие сколько долей содержит каждая часть. Если члены сложного отношения дробные, то, приведя их к общему знаменателю и умножив на него, можно заменить отношение дробных чисел отношением целых.

Пример. Разделить число 66 на такие три части, чтобы первая относилась ко второй, как 3:2, а вторая к третьей, как 5:4.

Решение.Первый способ: обозначим искомые части буквами a, b и c. Так как отношение не изменится, если оба члена умножить на одно и то же число, то умножим члены первого отношения на 5, а второго на 2:

a:b = 3:2 = 15:10	значит a:b:c = 15:10:8
b:c = 5:4 = 10:8

так как 15 + 10 + 8 = 33, то

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

Второй способ: обозначим искомые части буквами a, b и c:

Если первая часть a равна 3, вторая b равна 2, то третью часть c можно определить из пропорции:

следовательно c равно:

c =	2 · 4	=	8
	5		5

Умножив все члены полученного сложного отношения на 5, чтобы избавиться от дробного члена, получим:

так как 15 + 10 + 8 = 33, то

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

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

Итак, площадь двух участков вместе 32 кв. м. Определим теперь, сколько штук рассады приходится на 1 кв. м:

224 : 32 = 7 (штук).

Зная сколько рассады приходится на 1 кв. м, мы легко вычислим число штук рассады на 8 кв. м и на 24 кв.. м, т. е. ответим на вопрос задачи:

7 • 24 = 168 (штук).

Подумаем теперь, какие величины входят в нашу задачу и как они связаны между собой. В условие задачи входят две величины: 1) количество штук рассады, 2) площадь участка. Эти две величины прямо пропорциональны одна другой, потому что, чем больше площадь участка, тем больше на нём можно посадить рассады. Расположим числа, с которыми мы имели дело в задаче, так, чтобы их удобно было сравнивать:

8 кв. м — 56 штук
24 кв. м — 168 штук

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

Итак, в этой задаче мы разделили число штук рассады пропорционально площадям двух участков. Это и есть одна из возможных задач на пропорциональное деление. Как же решаются такие задачи? В задаче требовалось число 224 разделить на две части, пропорциональные числам 8 и 24, т. е. разделить это число на такие две части, которые относились бы между собой так же, как 8 : 24. Обозначим величину первой части буквой х, а второй части — у и напишем отношение этих частей:

Для нахождения этих частей были выполнены следующие действия. Число 224 разделили на сумму чисел 8 и 24 и затем найденное частное последовательно умножили сначала на 8, а потом на 24, т. е.

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

Рассмотрим другую задачу: «За три куска мыла одного и того же сорта заплатили 40 руб, Сколько заплатили за каждый из них, если первый кусок весил 2 кг, второй 3 кг и третий 5 кг?»

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

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

Таким образом, первый кусок мыла стоит 8 руб., второй 12 руб. и третий 20 руб. Найденные числа рублей х, у, z находятся между собой в таких же отношениях, как и данные в задаче числа весовых единиц, т. е.

х : у : z = 8 : 12 : 20 = 2 : 3 : 5.

Рассмотрим теперь задачу с отвлечёнными числами. Разделить число 180 на три части пропорционально числам 3; 5; 7. Иными словами: в этой задаче требуется разложить число 180 на такие три слагаемых, чтобы первое относилось ко второму, как 3 к 5, второе относилось к третьему, как 5 к 7 и, наконец, первое к третьему, как 3 к 7. Сокращённо это можно написать так:

х : у : z = 3 : 5 : 7,

где х, у, z обозначают соответственно первое, второе и третье число.

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

Применяя указанное выше правило, можем написать:

Полученные три числа удовлетворяют условию задачи: они в сумме составляют 180, т. е.

36 + 60 + 84 = 180 и 3 : 5 : 7 = 36 : 60 : 84.

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

Задача 1. Определить квартирную плату за каждую из двух комнат (8 кв. м и 24 кв. м), если за обе вместе нужно заплатить 64 руб.

Обозначим плату за 1 кв. м буквой х; тогда за первую комнату нужно будет заплатить 8x, а за вторую — 24x. Значит, за обе комнаты вместе надо заплатить 8х + 24х, что составляет 64 руб. Следовательно, можно записать равенство:

х = 64 : 32 = 2 (руб.).

Дальше задача решается так:

Задача 2. Найти стоимость каждого из трёх пакетов муки, если все три пакета стоят 40 руб., а вес первого 2 кг, второго 3 кг и третьего 5 кг.

Обозначим цену одного килограмма буквой х, тогда:

2 кг будут стоить 2х ;

а вся мука будет стоить:

10х = 40; х = 40 : 10 = 4 (руб.).

После этого легко определить стоимость каждого пакета;

2х = 2 • 4 = 8 (руб.);

3х = 3 • 4 = 12 (руб.);

5х = 5 • 4 = 20 (руб.).

Задача 3. Разделить число 1 800 на три слагаемых пропорционально числам: 3, 5 и 7.

Рассуждаем так: в первом слагаемом 3 части, во втором 5 и в третьем 7.

Обозначая величину одной части буквой х, можно написать:

3х + 5х + 7х =1 800.

15х = 1 800; х = 1 800 : 15 = 120.

3х = 3 • 120 = 360;

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

Задача. Разделить 968 на четыре части пропорционально числам: 2/3 , 3/4 , 2/5 и 3/8.Это значит, что надо найти четыре таких числа (х, у, z, t), отношения которых были бы равны соответствующим отношениям данных чисел, т. е.

а сумма x + y + z + t = 968.

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

Отбрасывая общий знаменатель 40, получим: 60 : 30 : 16 : 15. Вычислим последовательно каждое из искомых чисел:

§ 139. Деление числа на части обратно пропорционально данным числам.

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

Задача. В двух полевых бригадах 70 колхозников. Каждой бригаде поручено обработать одинаковые участки. Сколько колхозников в каждой бригаде, если первая бригада выполнила работу в 6 дней, а вторая — в 8 дней? (Предполагается, что все колхозники работают с одинаковой производительностью труда.)

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

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

Сравним теперь работу, которую выполняет в день первая бригаду с работой, выполняемой в день второй бригадой. Эти работы выражаются дробями 1/6 и 1/8. Первая дробь больше второй. Значит, первая бригада в один день может делать больше, чем вторая. А так как все колхозники работают с одинаковой производительностью труда, то, значит, в первой бригаде больше колхозников, чем во второй. Таким образом, число колхозников в каждой бригаде пропорционально той работе, которую каждая бригада может выполнить. Значит, данное в задаче число 70 мы должны разделить на две части пропорционально числам: 1/6 и 1/8 . С задачами такого типа мы уже знакомы. Приведя дроби 1/6 и 1/8 к общему знаменателю, мы найдём числа, пропорционально которым следует разделить число 70:

т. е. число 70 нужно разделить на две части пропорционально числам 4 и 3. Обозначим число колхозников первой бригады буквой х, а второй — буквой у и вычислим:

Итак, в первой бригаде было 40 человек, а во второй 30. Рассмотрим теперь метод решения этой задачи. В условие задачи входят три числа: 70 (человек), 6 (дней) и 8 (дней). В процессе решения мы ввели еще два числа: 1/6 и 1/8, и пропорционально этим дробям разделили число 70 на две части. Очевидно, что число 6 и число 1/6 взаимно обратны. Так же взаимно сбратны числа 8 и 1/8 .

Для решения задачи требуется разделить 70 рабочих на две неравные бригады, исходя из количества времени (дней), затраченного ими на работу. Это время выражается числами 6 (дней) и 8 (дней). Вместо этих двух чисел мы берём обратные им числа 1/6 и 1/8 и пропорционально им делим число 70.

Такая замена сделана нами потому, что число работников не прямо, а обратно пропорционально времени, затраченному на работу. О такой задаче принято говорить, что в ней число 70 разделено на две части обратно пропорционально числам 6 и 8, т. е. в ней первая часть относится ко второй не как 6 к 8, а как 8 к 6.

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

Задача. Разделить 65 на три части обратно пропорционально числам: 2, 3, 4.

Мы теперь знаем, что разделить число на части обратно пропорционально нескольким числам — это значит разделить его на столько же частей прямо пропорционально обратным числам.

Напишем числа, обратные данным в задаче:

данные числа 2, 3, 4;

обратные числа 1/2, 1/3 , 1/4.

Пропорционально этим последним и нужно разделить число 65. Приведём дроби к общему знаменателю:

а потом освободимся от него:

Пример 1. Разделить число 50 на части пропорционально числам 2 и 3.

Пример 2. Разделить число 90 на три слагаемых пропорционально числам 1, 2 и 3.

90 : (1 + 2 + 3) = 90 : 6 = 15

Длинные отношения вида 1:2:3 называются сложными. Сложные отношения – это условные записи, показывающие сколько долей содержит каждая часть. Если члены сложного отношения дробные, то, приведя их к общему знаменателю и умножив на него, можно заменить отношение дробных чисел отношением целых.

Пример. Разделить число 66 на такие три части, чтобы первая относилась ко второй, как 3:2, а вторая к третьей, как 5:4.

a:b = 3:2 = 15:10	значит a:b:c = 15:10:8
b:c = 5:4 = 10:8

a = (66 : 33) · 15 = 30

b = (66 : 33) · 10 = 20

c = (66 : 33) · 8 = 16

Свежие материалы

Списание стоимости ОС производственным способомСпособ списания стоимости основного средства производственным методом является нелинейным, то есть…

Земельный налог: сроки уплаты авансовых платежей по земельному налогуСроки уплаты авансовых платежей по земельному налогу…

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

Положение о стимулирующих выплатах УТВЕРЖДЕНО Приказом директора МБУК "Карагайский районный дом культуры и досуга"от "15"…