Как рассчитать процентную норму ранга в эксель
Обновлено: 03.07.2024
Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса - другое дело, а для обычных людей мышеловка"деньги за 15 минут, нужен только паспорт" срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это "потом" все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?
Так что очень надеюсь, что изложенный ниже материал вам не пригодится.
Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. "Помассажировать числа" заранее, как я это называю :) Microsoft Excel может сильно помочь в этом вопросе.
Вариант 1. Простой кредитный калькулятор в Excel
Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами - таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:
- Ставка - процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
- Кпер - количество периодов, т.е. срок кредита в месяцах.
- Пс - начальный баланс, т.е. сумма кредита.
- Бс - конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
- Тип - способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.
Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:
Вариант 2. Добавляем детализацию
Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel - ОСПЛТ (PPMT) и ПРПЛТ (IPMT) . Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):
Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:
Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:
Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку ("") в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:
=ЕСЛИ(A18<>""; текущая формула; "")
Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:
Вариант 3. Досрочное погашение с уменьшением срока или выплаты
Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.
В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять - не достигли мы нулевого баланса раньше срока:
А в случае уменьшения выплаты - заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:
Вариант 4. Кредитный калькулятор с нерегулярными выплатами
Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:
В этой статье описаны синтаксис формулы и использование функции РАНГ в Microsoft Excel.
Описание
Возвращает ранг числа в списке чисел, то есть его величину относительно других значений в списке. Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений.
Если отсортировать список, то ранг числа будет его позицией.
Синтаксис
Аргументы функции РАНГ.РВ описаны ниже.
Число Обязательный. Число, для которого определяется ранг.
Ссылка Обязательный. Массив чисел или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.
Порядок Необязательный. Число, определяющее способ упорядочения.
Замечания
Если значение аргумента "порядок" равно 0 (нулю) или опущено, ранг числа определяется в Excel так, как если бы ссылка была списком, отсортированным по убыванию.
Если значение аргумента "порядок" — любое число, кроме нуля, то ранг числа определяется в Excel так, как если бы ссылка была списком, отсортированным по возрастанию.
Функция РАНГ.РВ присваивает повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).
В некоторых случаях может потребоваться определить ранг, учитывая при этом связь. В предыдущем примере необходимо получить измененный ранг числа 5,5 для числа 10. Для этого добавьте следующий коэффициент исправления к значению, возвращаемом функцией РАНГ.ЕQ. Этот коэффициент исправления подходит как для случая, когда ранг вычисляется в порядке убывания (порядок = 0 или опущен) или в порядке возрастания (порядок = нелиценз).
Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ.РВ(число, ссылка, 0) – РАНГ.РВ(число, ссылка, 1)]/2.
В примере, содержащемся в книге, РАНГ.РВ(A3,A2:A6,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей, равен 3 + 0,5 = 3,5. Если число встречается в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ.РВ для связи не будет изменяться.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Процентный ранг - это обычный термин, используемый в статистике в основном в процентах. Percentile (также называемый Centile) - это процентное соотношение баллов в диапазоне от 0 до 100, которое меньше или равно данному набору распределения. Перцентили делят любое распределение на 100 равных частей. Это преимущественно используется для интерпретации оценок с различными диапазонами в различных тестах. Процентный ранг (PR) достигается на основе общего количества рангов и количества рангов ниже и выше процентиля.
Формула для Процентного Ранга определяется как:
Чтобы определить процентильный ранг (PR) для оценки x, из N, где x включен.
Percentile Rank = ((M + (0.5 * R)) / Y) x 100
- M = количество рангов ниже х
- R = количество рангов равно х
- Y = общее количество рангов
Чтобы определить процентильный ранг (на ранг ) для оценки x, из Y, где x не включен.
Percentile Rank = (M / Y) x 100
Процентиль в основном применяется к набору данных, где необходимо определить рейтинг. Кроме того, каждый 25- й процентиль известен как один квартиль. Из 100 25- й процентиль известен как 1- й квартиль. 50- й процентиль известен как 2- й квартиль или медиана, 75- й процентиль известен как 3- й квартиль. Разница между 3- м и 1- м квартилем называется межквартильным диапазоном.
Примеры формулы процентильного ранга
Давайте рассмотрим пример, чтобы лучше понять расчёт Процентного Ранга.
Вы можете скачать этот шаблон Процентильного ранга здесь - Шаблон Процентильного ранга
Пример № 1
Предположим, в классе 20 учеников. Все они посещают экзамен NEET для получения высшего образования. Бабу и Судху - двое из этих 20 учеников, и оба занимают 10- е место среди 20. Рассчитайте Процентное звание Бабу.
Решение:
- Общее количество рангов будет таким же, как и общее количество студентов в этом случае. Так что Y будет 20.
- Процентный ранг должен быть рассчитан для Бабу, который находится на 10- м ранге. Следовательно, х будет 10.
- Идентификация подсчета того же 10- го ранга. В этом случае это будут Бабу и Судха. Так что R будет 2.
- Подсчитайте ранги меньше 10, которые будут считаться M. Следовательно, M будет 9.
Процентильный ранг рассчитывается по формуле, приведенной ниже
Процентильный ранг = ((M + (0, 5 * R)) / Y) х 100
- Процентильный ранг = ((9 + (0, 5 * 2)) / 20) * 100
- Процентный ранг = ((9 + 1) / 20) * 100
- Процентильный ранг = (10/20) * 100
- Процентильный ранг = 0, 5 * 100
- Процентный ранг = 50%
Следовательно, процентиль ранг для Бабу будет 50%. Бабу стоит как 50- й процентиль в своем классе !!
Пример № 2
В организации участвуют 10 студентов. Баллы учеников 5, 9, 4, 12 и 7. Определите процентиль для 9 балла.
Решение:
- Шаг 1: Расположите набор данных в порядке возрастания: 4, 5, 7, 9, 12.
- Шаг 2: Добавить рейтинг для упорядоченного счета:
- Общее количество рангов будет таким же, как и общее количество студентов в этом случае. Так что Y будет 5.
- Процентное звание должно быть рассчитано для оценки 9, которая является 4- м рангом. Следовательно, х будет 4.
- Подсчитайте ранги в 4, которые будут рассматриваться как « М ». Следовательно, М будет 4.
- Идентификация подсчета того же 10- го ранга. В этом случае это будет 0. Поэтому мы будем использовать вторую формулу.
Процентильный ранг рассчитывается по формуле, приведенной ниже
Процентный ранг = (М / Г) х 100
- Процентильный ранг = (4/5) * 100
- Процентильный ранг = 0, 8 * 100
- Процентный ранг = 80%
Это означает, что оценка 9 выше, чем 80% оценок.
объяснение
Ниже приведен пошаговый подход для получения значения Percentile Rank.
Шаг 1: Запишите значение ряда оценок в порядке возрастания (от низшего к наибольшему), а также ранжирование в табличном формате. Подсчитайте количество баллов или последний ранг, который будет считаться « Y - Общее количество рангов».
Шаг 2: Определите балл х, для которого необходимо рассчитать процентиль.
Шаг 3: Определите, есть ли такая же оценка как x, если это так, считайте ту же оценку как «R».
Шаг 4: Подсчитайте оценки, которые меньше x, что будет считаться «М». Таким образом, формула
Процентильный ранг = ((M + (0, 5 * R)) / Y) х 100
Шаг 5: Если значение «R» равно нулю на шаге 3, то формула
Процентный ранг% = (М / Г) х 100%
Актуальность и использование
Не существует универсально определенной формулы для Процентильного и Процентильного рангов. Однако это преимущественно используется для всех ранжирований экзаменов, таких как NEET, GRE, SAT, LSAT и т. Д. Это обычно используется для определения в качестве метода интерпретации позиции в стандартном наборе данных. В современном цифровом мире это также используется для интерпретации медицинских данных, таких как процентильные неудачи лапароскопии во время операций, а также в области науки о данных !!
Калькулятор формулы ранга в процентах
Вы можете использовать следующий калькулятор Percentile Rank
Формула процентного ранга в Excel (с шаблоном Excel)
Здесь мы сделаем пример формулы Процентного ранга в Excel. Это очень легко и просто.
В Excel есть несколько наборов значений в вкладках Пример: 1 и Пример: 2. Пример 1 может быть использован для формулы: 1, а пример: 2 может использоваться для формулы: 2. Эти два основаны на двух различных формулах, как упомянуто выше, и могут использоваться на основе сценариев. Пожалуйста, следуйте приведенным ниже инструкциям для расчета Процентильного Ранга с использованием шаблона Excel:
Для образца А
Процентильный ранг рассчитывается по формуле, приведенной ниже
Процентильный ранг = ((M + (0, 5 * R)) / Y) х 100
- Процентильный ранг = ((12 + (0, 5 * 3)) / 48) * 100
- Процентильный ранг = ((12 + 1, 5) / 48) * 100
- Процентильный ранг = (13, 5 / 48) * 100
- Процентильный ранг = 0.28125 * 100
- Процентный ранг = 28, 125%
Для образца Б
Процентильный ранг рассчитывается по формуле, приведенной ниже
Процентный ранг = (М / Г) х 100
- Процентильный ранг = (4/67) * 100
- Процентильный ранг = 0, 05970149254 * 100
- Процентильный ранг = 5, 970149254%
Рекомендуемые статьи
Это было руководство к формуле Процентильный ранг. Здесь мы обсудим, как рассчитать Процентильный ранг, а также на практических примерах. Мы также предоставляем калькулятор Percentile Rank с загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
Формула для оценки - это вложенная формула IF, которая проверяет определенные критерии и возвращает конкретную оценку, если критерии удовлетворены. Определение оценки ученика, вероятно, является основным примером, который все руководства Excel дают своим ученикам, чтобы объяснить логику логических функций в Excel. IF, AND, OR, FALSE, TRUE и IFERROR - это некоторые из широко используемых логических функций в Excel.
В этой статье мы расскажем о различных способах определения оценки по баллам учащихся, уровня эффективности сотрудников, расчета бонусов и многого другого с помощью формулы для оценки в Excel.
Как использовать формулу для расчета оценок в Excel?
Давайте разберемся, как использовать формулу для расчета оценок на нескольких примерах.
Каждый пример будет пытаться исследовать различные варианты использования, которые реализуются с помощью формулы для оценки в Excel.
Вы можете скачать эту формулу для Grade Excel Template здесь - Формула для Grade Excel Template
Найти оценки студентов: вложенные условия IF
Оценка учащихся - один из лучших примеров, чтобы понять логичность условия IF в excel. Рассмотрим приведенную ниже оценку студентов для этого примера.
Ниже приведены критерии для нахождения оценки.
- Если оценка выше 550, оценка А +
- Если оценка выше 500 баллов - это А.
- Если оценка выше 450, оценка B +
- Если оценка выше 400, оценка B
- Если оценка выше 300, оценка C
- Если оценка не соответствует ни одному из указанных выше условий, оценка будет НЕУДАЧНОЙ.
Чтобы определить индивидуальную оценку учащегося, нам необходимо проверить все эти условия и на основе удовлетворения условий, которые нам необходимы для объявления оценок.
Используя условие IF, мы можем найти оценки здесь. Прежде чем я расскажу вам, как найти оценки, позвольте мне сначала объяснить условие ЕСЛИ.
Логический тест: это аргумент для проверки логики значения.
Значение if True: если логический тест верен, каким должен быть желаемый результат.
Значение if False: если логический тест равен false, каким должен быть желаемый результат.
Теперь взглянем на простой пример.
Формула для оценки в Excel - Пример № 1
Если день равен ВОСКРЕСЕНЬЮ, тогда цена должна быть 35, иначе нам нужна цена 30.
Шаг 1: Откройте условие IF в ячейке B2.
Шаг 2: Теперь нам нужно провести логический тест, т. Е. Равен ли день воскресенью или нет. Выберите ячейку A2 и проверьте, равно ли это воскресенье или нет.
Шаг 3: Если логический тест верен, то результат 35.
Шаг 4: Если логический тест ложный, результат должен быть 30.
Шаг 5: Закройте скобку и нажмите клавишу Enter. У нас есть результат.
Шаг 6: Теперь мы перетащим эту формулу ячейки B2 в ячейку B3. Таким образом, у нас будет результат, как показано ниже.
Формула для оценки в Excel - Пример № 2
Теперь давайте применим эту логику, чтобы найти оценки учеников с помощью формулы для оценки в Excel.
Шаг 1: Откройте условие IF в ячейке C2. Первый логический тест - если результат выше 550. Таким образом, результат теста> 550 или нет. Если для этого ИД теста значение ИСТИНА должно быть «А».
Шаг 2: Если тест ложный, значение должно быть . Здесь нам нужно проверить еще несколько критериев. Итак, откройте новое условие ЕСЛИ.
Шаг 3: Теперь проверьте второй критерий, т.е. если значение> 500. Если этот логический тест верен, результат должен быть «B +».
Шаг 4: Теперь, если оба условия ЛОЖНЫ, нам нужно проверить третий критерий. Итак, откройте еще одно условие IF и проверьте, является ли значение> 400 или нет. Если тест ИСТИНА, результат должен быть «B».
Шаг 5. Подобным образом примените условия ЕСЛИ для всех критериев и закройте скобки. Я уже применил все условия ЕСЛИ и получил результаты.
Шаг 6: Теперь мы перетащим эту формулу ячейки C2 в ячейку C9. Таким образом, у нас будет результат, как показано ниже.
Формула для оценки в Excel - Пример № 3
Найти оценки сотрудников: сочетание условий IF & AND
Мы видели, как использовать условие ЕСЛИ, чтобы найти оценки студентов. Теперь давайте посмотрим комбинацию условий IF & AND.
У меня есть таблица данных сотрудников с указанием их отдела и года службы в компании.
Для того, чтобы найти оценку, необходимо выполнить следующие критерии: «Если сотрудник работает в отделе продаж, а год службы> = 5 лет, то сотрудник должен иметь право на повышение по службе».
Кроме отдела продаж никто не имеет права на Акции.
Здесь нам нужно протестировать два условия одновременно, в отличие от предыдущего, где нам нужно протестировать множество критериев по отдельности. Комбинация условий IF & AND может помочь нам здесь.
Функция AND одновременно проверяет несколько условий, и если все пройденные условия верны, мы получим результат как ИСТИНА или ЛОЖЬ.
Шаг 1: Откройте условие IF в ячейке D2.
Шаг 2: Логический тест здесь не является единственным, нам нужно проверить два условия для одного сотрудника. Так откройте И функцию внутри условия ЕСЛИ.
Шаг 3: Первый критерий - равен ли отдел продажам или нет. И второй тест: год службы> = 5 лет или нет.
Шаг 4: Поскольку у нас есть только два условия для проверки, закройте скобку функции AND. Если логический тест с помощью функции AND равен TRUE, то результат должен соответствовать критериям для повышения, а если логический тест с помощью функции AND равен FALSE, результатом должен быть отказ от участия в акции. Закройте скобку и нажмите клавишу Enter, чтобы завершить формулу.
Шаг 5: Теперь мы перетащим эту формулу ячейки D2 в ячейку D9. Таким образом, у нас будет результат, как показано ниже.
То, что нужно запомнить
- Если вы находите оценку, проверяя несколько условий одновременно, и если все условия выполняются, то нам нужно использовать условие IF & AND для получения точных результатов.
- Если вы находите оценку, проверяя несколько условий одновременно, и если какое-либо из условий является верным, то нам нужно использовать условие IF & OR, чтобы получить точные результаты. Условие ИЛИ возвращает значение ИСТИНА, если выполняется любое из предоставленных условий, и возвращает ЛОЖЬ, если ни одно из условий не выполнено.
- Когда вы находите оценку на основе числовых значений, нам нужно сначала пройти самое высокое значение. Это должно быть в порядке убывания.
- Все текстовые значения должны быть заключены в двойные кавычки, а числовые значения не должны быть заключены в двойные кавычки.
Рекомендуемые статьи
Это было руководство к формуле для оценки в Excel. Здесь мы обсудили, как использовать формулу для расчета оценок в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: