Как посчитать рейтинг в excel пропорционально доле
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). Расчет будем проводить с помощью надстройки Поиск решения.
Вводная статья про Поиск решения в 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%.
В разделе Техника на вопрос Помогите, как в Экселе сделать формулу пропорция. заданный автором Leonna лучший ответ это Ну пусть 50000 стоит в ячейке А1, а 600 — в ячейке А2. Тогда там, где надо получить 1,2%, надо написать «=A2/A1%». То есть после операции поставить значок % — это и будет указание Экселю, что результат в процентах нужен.
22 ответа
Привет! Вот подборка тем с ответами на Ваш вопрос: Помогите, как в Экселе сделать формулу пропорция.
Ответ от ретроспектива
в ячейке а1 пишешь 50000, в ячейке в1 пишешь 600 в ячейке с1 пишешь =в1/а1 потом выделяешь с1 и формат ячейки % и задаешь количество знаком после запятой (для отображения)
2 ответа
Привет! Вот еще темы с нужными ответами:
Буквально сегодня столкнулся с ошибкой при вычислении разницы двух величин в процентах. Эта ошибка привела к довольно серьезным последствиям, а исправление этих последствий заняла несколько часов. Чтобы в будущем я смог сослаться на эту статью, а не писать пояснения каждый раз, предназначена эта статья. Как посчитать проценты в Excel? Расчет процентов от числа. Разница чисел в процентах и правильное оформление.
Для начала, немного теории. Процент — это, в первую очередь доля, а точнее сотая часть, обозначается знаком «%» и используется для обозначения доли чего-либо по отношению к целому. Если уж совсем углубиться в школьный курс, то десятичная дробь 0,07 равна 7%. В нашей культуре удобно и привычно сравнивать величины в процентах, так например понятие «согласен на 100%» означает полное одобрение.
Как правильно посчитать проценты в Excel?
Как мы сказали выше, процент это всего лишь доля. Поэтому в Excel я не рекомендую использовать деления на 100 при выделении процентной доли, а использовать специальный процентный формат. Как впрочем, и формат числа «Дата» или «Время«. Такой формат доступен на панели Главная отдельной кнопкой:
Ниже я показываю правильное использование этого формата. В ячейке A4 используется Процентный формат.
Дело в том, что показанный в строке 5 способ записи процентов затрудняет последующие вычисления — нужно каждый раз делить данные на 100 в новых формулах. Велика вероятность забыть об этом или принять данные в ячейке за обычное число.
Стоит отметить, что если в ячейке вы введете текст 5%, то программа сама распознает и примет процентный формат. А число в ячейке запишется как 0,05.
Как посчитать процент от числа в Excel?
Чтобы рассчитать проценты (долю) от определенного числа необходимо это число (в нашем примере 5) разделить на число, от которого нужна доля (100).
Соответственно если нам надо понять, какой процент (рентабельность) составляет прибыль 12 тыс. руб. в выручке 70 тыс. руб. нам нужно 12 разделить на 70.
Чаще всего путаница возникает при расчете включенного НДС. Для разъяснения у меня есть отдельная статья.
Пропорция в Excel и расчет числа от процента
Как быть если у вас дано число 250 и вы знаете, что оно составляет 7% от какого-то числа. Как найти это число, т.е. 100%.
Посчитать нам поможет правило пропорции (proportio — соразмерность, определённое соотношение долей между собой) или в обиходе «крест-на-крест».
Чтобы найти вопросительный знак нам нужно ? = 250 * 100% / 7%. Т.е. получится 3 571, это будет 100% для этого задания.
Точно так же можно находить любое неизвестное значение из 4 и легко посчитать проценты в Excel. Например если заданы 100% и не задан процент от числа.
Не судите строго, кто считает такую информацию элементарной — кому-то обязательно пригодиться описание пропорции.
Разница чисел в процентах
Еще один популярный вопрос: «Как найти разницу чисел в процентах?» или «Как найти прирост показателя из года в год в процентах?».
Правило из большого вычитаем меньшее здесь не работает, поскольку разница может быть и отрицательной. Здесь так же частый вопрос, на какое число нужно делить. Все зависит от условий задачи. Если вам нужно найти увеличение показателя по отношению к предыдущему, 2016 году, нужно делить именно на данные 2016 года.
Аналогично можно рассчитать разницу между любыми числами. Не забываем ставить скобки в формуле!
Конечно же, прикладываю файл-пример.
Поделитесь нашей статьей в ваших соцсетях:
(Visited 6 905 times, 1 visits today)
В процессе решения различного рода задач, как учебных, так и практических, пользователи нередко обращаются к программе Excel.
Электронные таблицы позволяет проводить анализ данных, строить диаграммы и графики, а также выполнять разнообразные вычисления. Одной из распространенных операций является вычисление процентов. Умение грамотно производить необходимые расчеты – полезный навык, который находит успешное применение практически во всех сферах жизни. Какие техники помогут посчитать проценты с помощью таблиц Excel?
1 Как посчитать проценты в Excel – основная формула расчета
Прежде, чем приступить к вычислению процентов, необходимо определиться с терминологией. Термин «процент» означает количество долей из всех 100 долей целого. Математическое определение процента – дробь, числитель которой определяет искомое количество частей, а знаменатель – общее. Результат умножается на 100 (т.к. целое – 100%). Работая с электронной таблицей, формула для определения процента выглядит следующим образом:
От привычной в математике интерпретации отличает лишь отсутствие дальнейшего умножения на 100. Получить необходимый формат значения помогут свойства полей таблицы – достаточно активировать Процентный формат ячейки.
Пример 1
Перед вами ряд данных, внесенных, например, в колонку D (D2, D3, D4, D5, …). Необходимо рассчитать, 5% от каждого значения.
- Активируете соседнюю с первым значением (или любую другую) ячейку – в ней будет располагаться результат вычислений.
- В ячейке E2 записываете выражение «=D2/100*5» или «=D2*5%».
- Жмете Enter.
- «Протяните» ячейку E2 на необходимое число строк. Благодаря маркеру автозаполнения по указанной выше формуле будет произведен расчет и для остальных значений.
Пример 2
Перед вами находятся 2 колонки значений – например, реализованные пирожные (D2, D3, D4, D5, …) и общее количество выпечки (E2, E3, E4, E5, …) каждого вида. Необходимо определить, какая часть продукции реализована.
- В ячейке, где будет рассчитан результат (например, F) записываете выражение «=D2/E2».
- Жмете Enter и «протягиваете» ячейку на необходимое число строк. Использование маркера автозаполнения позволит применить данную формулу для всех последующих ячеек и произвести верные расчеты.
- Для перевода результата в формат процентов выделите необходимые ячейки и воспользуйтесь командой Percent Style. Для активации последней можно кликнуть правой клавишей мыши и выбрать в появившемся перечне пункт «Формат ячеек» – «Процентный». При этом вы указываете желаемое число десятичных знаков. Или же перейдите в раздел «Главная» – «Число» и выберите вид «Процентный».
2 Как посчитать проценты в Excel – процент от суммы
Для вычисления доли каждой части относительно общей суммы используйте выражение «=A2/$A$10», где A2 – интересующее значение, общая сумма указана в ячейке A10. Как быть, если интересующая вас позиция встречается в таблице несколько раз? В таком случае воспользуйтесь функцией SUMIF (СУММЕСЛИ) с параметрами:
- Перемещаетесь в ячейку, где будет получен результат.
- Записываете выражение «=СУММЕСЛИ(C2:C10;F1;D2:D10)/$D$14» (или =SUMIF (C2:C10;F1;D2:D10)/$D$14), где
C2:C10, D2:D10 – диапазоны значений, в пределах которых происходят вычисления,
F1 – ячейка, в которой указана исследуемая характеристика,
D14 – ячейка, в которой рассчитана сумма.
3 Как посчитать проценты в Excel – изменение в процентах
Необходимость в таких вычислениях часто возникает в ходе оценки прироста или убыли по результатам деятельности. Итак, объемы продаж по категориям продукции за 2015г. внесены в колонку D, аналогичные данные за 2016г. – в колонку E. Необходимо определить на сколько процентов увеличился или уменьшился объем продаж.
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
Функции МАКС и МИН прекрасно подходят для поиска наибольшего и наименышего значения в диапазоне данных. Но если нужно найти несколько максимальных или минимальных значений для этой задачи Excel предлагает несколько интересных функций: НАИБОЛЬШИЙ, НАИМЕНЬШИЙ, РАНГ и РАНГ.СР.
Пример выборки нескольких наибольших значений в Excel
Ниже на рисунке представлены результаты турниру по боулингу. Участники в турнирной таблице должны быть отсортированы по возрастанию по имени. Сложно проверить, который из них является победителем. Допустим нам необходимо узнать, которые участники заняли первые 3 места и какие их результаты. Ниже указанная формула, которая возвращает третье наибольшее число со списка результатов:
В формуле использованной для поиска имени участника используется комбинация функций ИНДЕКС и ПИСКПОЗ:
Функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ используются для поиска порядкового наибольшего и наименьшего значения в списке данных. В первом аргументе функции НАИБОЛЬШИЙ, как и в функции МАКС указывается ссылка на исходный диапазон просматриваемых значений. Однако отличие между ними заключается в том, что функция НАИБОЛЬШИЙ имеет еще один обязательный для заполнения аргумент «К» в котором можно указать порядковый номер возвращаемого наибольшего значения (второе наибольшее, третье… и т.д.).
Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.
В ячейке E2 используется функция СТРОКА(A1) определяющая значение аргумента «К». Функция строка возвращает номер строки листа для ячейки, указанной в аргументе – в данном примере строка №1. Как второй аргумент функции НАИБОЛЬШИЙ можно было бы просто указать число 1, однако благодаря функции СТРОКА(A1) номер строки листа будет увеличиваться автоматически во время копирования формулы в очередные ячейки в низ от начальной. Ссылка на ячейку A1 является относительной, когда потом формула будет скопирована в ячейку E3, функция будет уже иметь следующий вид: СТРОКА(A2). С таким аргументом функция уже будет возвращать значение 2, а функция НАИБОЛЬШИЙ в ячейке E3 возвратит второе наибольшее значение из столбца «Результат» в исходном списке данных.
Формула ранжирования в Excel для выборки наибольших значений
В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:
Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.
В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.
Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:
Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:
Функция РАНГ.СР для ранжирования списка с повторяющимися значениями
В программе Excel версии 2010 и старше появились две новые функции ранжирования значений: РАНГ.РВ и РАНГ.СР, в которых по-разному решается проблема с одинаковыми значениями в диапазоне ячеек. Формула РАНГ.РВ – это аналог функции РАНГ, она возвращает тот же самый результат при таких же условиях с теми же самыми одинаковыми ячейками. А вот функция РАНГ.СР возвращает средний результат вычисления для всех одинаковых значений.
Допустим в таблице находятся результаты четырех участников с одинаковым вторым наименьшим показателем времени 21:38:
Для наилучшего времени в данной таблице функция РАНГ.СР естественно возвращает номер 1, а для четырех последующих наилучших показателей времени возвращено дробное значение 3,5. Эти четыре участника одновременно занимают места: 2, 3, 4 и 5, в итоге все места разделены между ними поровну, поэтому каждый участник получил по 3,5 балла. То есть 2+3+4+5/4=3,5.
Число — число, для которого определяется ранг.
Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.
Порядок — число, определяющее способ упорядочения.
- Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б о льшие ранги.
- Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б о льшим значениям присваиваются б о льшие ранги.
Примечание : Начиная с MS EXCEL 2010 для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ() . Последняя функция аналогична РАНГ() .
Определяем ранг в списке без повторов
Если список чисел находится в диапазоне A7:A11 , то формула =РАНГ(A7;$A$7:$A$11) определит ранг числа из ячейки А7 (см. файл примера ).
Т.к. аргумент порядок опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) - минимальному (10).
Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;">"&A7)+1
В столбце С приведена формула =РАНГ(A7;$A$7:$A$11;1) с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;"
Если исходный список отсортировать , то ранг числа будет его позицией в списке.
Ранг по условию
Если список состоит из значений, относящихся к разным группам (например, к разным маркам машин), то ранг можно вычислить не только относительно всей совокупности данных, но и относительно данных каждой отдельной группы.
В файле примера ранг по условию (условием является принадлежность значения к групп) вычислен с помощью формулы:
В столбце А содержатся названия группы, в столбце В - значения.
Связь функций НАИБОЛЬШИЙ() / НАИМЕНЬШИЙ() и РАНГ()
Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11 .
Определяем ранг в списке с повторами
Если список содержит повторы , то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция РАНГ() или РАНГ.РВ() ) или среднее значение, если РАНГ.СР() ). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).
Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).
В этом нам поможет формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1
Предполагается, что исходный список с числами находится в диапазоне А37:А44 .
Примечание . В MS EXCEL 2010 добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.
Массив рангов
Для построения некоторых сложных формул массива может потребоваться массив рангов, т.е. тот же набор рангов, но в одной ячейке.
Как видно из картинки выше, значения из диапазона В60:В67 и в ячейке D60 совпадают. Такой массив можно получить с помощью формулы =РАНГ(A60:A67;A60:A67) или с помощью формулы =СЧЁТЕСЛИ(A60:A67;">"&A60:A67)+1
Ранги по возрастанию можно получить с помощью формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;" .
Читайте также: