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

Обновлено: 05.07.2024

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

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

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

Перед тем как посчитать проценты от суммы определимся с понятием «процент». Оно имеет латинское происхождение и дословно переводится как «из сотни». Это определенная часть из 100 долей целого.

Математическая формула расчета процентов выглядит следующим образом: (искомая часть / целое число) * 100 .

Чтобы найти процент от числа, применяется такой вариант формулы: (число * процент) / 100 . Либо перенести запятую в процентах на 2 знака влево и выполнить только умножение. Например, 10% от 100 – это 0,1 * 100 = 10.

Какую именно формулу применить в Excel, зависит от желаемого результата.

Задача №1: Найти, сколько составит 20% от 400.

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

Как назначить для ячейки процентный формат? Выбирайте любой удобный для вас способ:

ввести сразу число со знаком «%» (ячейка автоматически установит нужный формат);
щелкнуть по ячейке правой кнопкой мыши, выбрать «Формат ячеек» - «Процентный»;
выделить ячейку и нажать комбинацию горячих клавиш CTRL+SHIFT+5.

Без использования процентного формата в ячейку вводится обычная формула: =A2/100*B2.

Такой вариант нахождения процента от числа тоже применяется пользователями.

Задача №2: Заказано 100 изделий. Доставлено – 20. Найти, сколько процентов заказа выполнено.

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

Вводить в отдельную ячейку проценты совсем не обязательно. У нас в одной ячейке может быть число. А во второй – формула нахождения процента от числа (=A2*20%).

Как прибавить проценты к числу в Excel?

В математике мы сначала находим проценты от числа, а потом выполняем сложение. Microsoft Excel выполняет то же самое. Нам нужно правильно ввести формулу.

Задача: Прибавить 20 процентов к числу 100.

Значения вносим в ячейки с соответствующими форматами: число – с числовым (или общим), процент – с процентным.
Вводим формулу: =A2+A2*B2.

Для решения такой же задачи может использоваться и другая формула: =A2*(1+B2).

Разница между числами в процентах в Excel

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

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

(«новое» число – «старое» число) / «старое» число * 100%.

Задача: Найти разницу в процентах между «старыми» и «новыми» ценами поставщика.

Сделаем третий столбец «Динамика в процентах». Назначим для ячеек процентный формат.
Поставим курсор в первую ячейку столбца, введем формулу: =(В2-А2)/В2.
Нажмем Enter. И протянем формулу вниз.

Разница в процентном отношении имеет положительное и отрицательное значение. Установление процентного формата позволило упростить исходную формулу расчета.

Разница в процентах между двумя числами в формате ячеек по умолчанию («Общий») вычисляется по следующей формуле: =(B1-A1)/(B1/100).

Как умножить на проценты в Excel

Задача: 10 кг соленой воды содержит 15% соли. Сколько килограммов соли в воде?

Решение сводится к одному действию: 10 * 15% = 10 * (15/100) = 1,5 (кг).

Как решить эту задачу в Excel:

Ввести в ячейку В2 число 10.
Поставить курсор в ячейку C2 и ввести формулу: =В2 * 15%.
Нажать Enter.

Нам не пришлось преобразовывать проценты в число, т.к. Excel отлично распознает знак «%».

Если числовые значения в одном столбце, а проценты – в другом, то в формуле достаточно сделать ссылки на ячейки. Например, =B9*A9.

Расчет процентов по кредиту в Excel

Задача: В кредит взяли 200 000 рублей на год. Процентная ставка – 19%. Погашать будем в течение всего срока равными платежами. Вопрос: какой размер ежемесячного платежа при данных условиях кредитования?

Важные условия для выбора функции: постоянство процентной ставки и сумм ежемесячных платежей. Подходящий вариант функция – «ПЛТ()». Она находиться в разделе «Формулы»-«Финансовые»-«ПЛТ»

Во время работы с числовыми данными в Эксель нередко перед пользователями встает такая задача, как подсчет среднего значения. Математически данное действие выполняется путем деления суммы всех чисел на их количество. А как это сделать в Excel? Давайте разбираться.

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

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

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

Расчет среднего значения

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

Использование арифметического выражения

Как мы знаем, среднее значение равняется сумме чисел, разделенных на их количество. Данную формулу можно использовать и в Экселе.

Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
=(Число1+Число2+Число3. )/Количество_слагаемых .
Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 .
Когда все готово, жмем Enter, чтобы получить результат.

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

Инструменты на ленте

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

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

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

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

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

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

Альтернативный способ использования “Среднее” на ленте:

Использование функции СРЗНАЧ

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

Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
Получаем результат в выбранной ячейке.
Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
Все готово. Теперь результат выглядит намного привлекательнее.

Инструменты во вкладке “Формулы”

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

Ввод функции в ячейку вручную

Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.

В общем, синтаксис функции СРЗНАЧ выглядит так:

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

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

Когда формула полностью готова, нажимаем клавишу Enter и получаем готовый результат.

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

Определение среднего значения по условию

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

Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.

Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
Откроются аргументы функции, после заполнения которых кликаем OK:
- в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
- в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
- поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.

Заключение

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

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

4 ответа

Вы можете попробовать это в D1

А затем скопируйте / заполните. Позвольте мне знать, если это помогает.

Объяснение

Ваш первый критерий находится в столбце C , а второй критерий находится в столбце B . Обычно сначала подсчитывается количество записей ($ C $ 1: $ C $ 99) , которые больше самой записи ($ C1) . Для первого в рейтинге вы получите ноль , поэтому вам нужно добавить 1 к каждому результату (+1) . До этого момента вы будете получать двойные рейтинги, если у вас дважды будет одно и то же значение.
Следовательно, вам нужно добавить еще один аргумент для выполнения дополнительных вычислений на основе второго критерия:
Чтобы решить проблему с ничьей, вам необходимо sumproduct две формулы массива и добавить результат к предыдущему аргументу, цель состоит в том, чтобы найти количество записей, которые равны этой записи с помощью $ C $ 1: $ C $ 99 = C1 и имеют большее значение во втором столбце критериев $ B $ 1: $ B $ 99> B1 :

вы добавляете -- для преобразования TRUE и FALSE на 0 и 1 , чтобы их можно было умножить:
SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))
первый массив показывает, сколько у вас связей по первому критерию. А второй массив - найти количество значений больше, чем сама запись.

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

Если вам не нравится ограничивать количество строк или числа, используемые в критериях, подход Jeeped можно расширить. Вы можете использовать следующие формулы в ячейках с D2 по L2, предполагая, что существует три критерия: первый в столбце A, второй в столбце B и третий в столбце C:

Формулы необходимо скопировать. Результат находится в столбце L. Связи разрываются по номеру строки.

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

Добавьте новый критерий между столбцами C и D.
Вставьте три новых столбца между столбцами I и J.
Скопируйте столбцы G: I в новые столбцы J: L.
Скопируйте столбец G в столбец M, перезаписав его содержимое.
Измените формулу в столбце L, чтобы она указывала на новый критерий.

Коэффициент 2^27 , используемый в формулах, уравновешивает точность 53 бита, доступную в double -точность чисел. Этого достаточно, чтобы покрыть лимит строк в текущих версиях Excel.

Очень простое (или, по крайней мере, гораздо более простое, чем тот, который дает лучший ответ ) "математическое" решение: выполнить линейную комбинацию с весами. Сделай что-нибудь вроде

Затем отсортируйте взвешенные оценки, используя простую функцию ранжирования.

Это действительно решает вашу проблему, создавая нужный вам рейтинг.

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

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

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

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

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

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

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

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

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

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

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