Как загрузить смету в Excel в общую смету

Обновлено: 22.11.2024

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

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

Скачать шаблон и калькулятор

Все два метода доступны в книге Excel, которую я загрузил. Первый метод находится на листе 1, а другой — на листе 3. Вы можете использовать два вышеуказанных метода в Excel в качестве калькулятора. Для вашего удобства я сделал два калькулятора для двух методов. Он доступен на листе 2 и листе 4 соответственно.

Основные сведения о процентах

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

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

Средние основы

В наборе чисел среднее значение — это значение, рассчитанное путем деления суммы на количество чисел. Например, среднее значение 5, 6, 8, 10 и 11 будет равно 8, где сумма общих значений равна 5+6+8+10+11=40, а количество значений равно 5.

Основы средних процентов

Средний процент — это, по сути, среднее значение процентов. Количество процентов может быть два или даже больше, это зависит от набора данных. Например, в образовательном учреждении предпочтение спорту у преподавателей составляет 40%, а у студентов - 80%. Теперь, каков будет средний процент? На самом деле это 64%, и в этой статье речь пойдет о том, как рассчитывается этот процент.

Распространенные ошибки при расчете среднего процента в Excel

Обычно мы используем формулу СРЗНАЧ в MS Excel для определения среднего значения любых величин. Если мы будем рассчитывать средний процент таким образом, то это, несомненно, будет неправильно. Так что будьте осторожны и избегайте этой ошибки. Как показано на следующем рисунке, средний процент определяется в ячейке D6 с помощью формулы СРЗНАЧ, хотя это неверно.

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

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

я. Расчет среднего процента с использованием данных опроса

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

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

Шаг 1. Определите число, которое обозначает каждый процент предпочтения

Для этого вы должны выполнить:

<р>1. Выберите ячейку D2

<р>2. Щелкните правой кнопкой мыши на кнопке мыши

<р>3. Выберите параметр «Вставить»

<р>4. Нажмите на столбец таблицы вправо

<р>5. Не волнуйтесь, если вы не создали таблицу, как я. В этом случае нажмите Insert > Shift Cells Right

<р>6. Теперь вам предстоит определить процентное умножение и количество участников. Итак, введите символ равенства (=). Затем поместите C3*D3

Здесь C3 – это количество участников для учителей, а D3 – процент предпочтения учителей.

<р>7. Теперь используйте инструмент Fill Handle в Excel. Просто перетащите курсор из нижней правой части E3 в E4. И вы можете увидеть, например, следующее-

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

<р>1. Выберите ячейку D3

<р>2. Выберите команду «Число»

<р>3. Выберите команду «Общие» в разделе «Формат ячеек».

<р>4. Введите ОК

<р>5. Теперь вы можете легко использовать параметры автозаполнения из Excel и выбрать «Заполнить только форматирование» для заполнения ячейки D4.

Шаг 2. Найдите общее значение

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

Шаг 3. Определите средний процент

<р>1. Выберите ячейку D6

<р>2. Введите знак равенства

<р>3. Запишите формулу E5/C5, где E5 — общее количество предпочтений, а C5 — общее количество участников.

<р>4. Нажмите Enter

<р>5. Средний процент составляет 64%

ii. Вычисление среднего процента с помощью определенной формулы

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

Формула среднего процента:

(((Процент1*число1)+(процент2*число2))/общее число))*100

Здесь учителя — номер 1, а ученики — номер 2. Проценты также даны соответственно. Общее количество — это общее количество учителей и учеников.

Затем вы можете выполнить следующие шаги

Шаг 1. Преобразуйте процентное значение в десятичное число (например, 40% = 0,4) или используйте существующее процентное значение

Шаг 2. Умножьте процентное значение на указанное число (или размер выборки) (например, 800*0,4 для учителей)

Шаг 3. Сложите два продукта вместе (например, 800 * 0,4 + 1200 * 0,8)

Шаг 4. Разделите агрегированное значение на целое число (или общий размер выборки), в данном случае на 2000.

Шаг 5. Преобразуйте значение в процентах

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

Выберите ячейку D6

Введите формулу =(((C3*D3+C4*D4)/C5))*100, где C3 и D3 — количество и процент учителей соответственно. Точно так же C4 и D4 предназначены для студентов. А C5 — это общее количество учителей и учеников.

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

Заключение

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

Итог: узнайте, как рассчитать комиссионные в Excel для базового многоуровневого плана и тарифной таблицы с помощью функций ЕСЛИ, ВПР или ССПР.

Уровень квалификации: средний

Видеоруководство

Смотреть на YouTube

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

В этой статье объясняется, как использовать функцию ВПР, чтобы упростить этот процесс. Секрет в том, чтобы установить для последнего аргумента vlookup значение TRUE, чтобы найти ближайшее совпадение.

Пожалуйста, ознакомьтесь с моей статьей о том, как использовать функцию ВПР для поиска ближайшего совпадения, чтобы получить подробное объяснение установки последнего аргумента в значение ИСТИНА.

Обновление: сообщение было обновлено, чтобы включить инструкции по расчету комиссий с помощью новой функции XLOOKUP. Вот статья и видео о XLOOKUP.

Скачать файл

Загрузите файл, чтобы следовать дальше.

Расчет комиссионных — IF, VLOOKUP и XLOOKUP.xlsx (31,5 КБ)

Расчет комиссий с функцией ВПР

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

Ставка выплат может быть фиксированной суммой в долларах, процентом от дохода, процентом от квоты и т. д. Задача функции ВПР – найти сумму продаж представителя в таблице ставок и вернуть соответствующую ставку выплаты.

Для этого примера наш план комиссий выглядит следующим образом:

Представитель продает от 0 до 50 000 долларов США и зарабатывает 5 %.
Представитель продает на сумму от 51 000 до 100 000 долларов США и зарабатывает 7 %.
Представитель продает на сумму от 100 001 до 150 000 долларов США и зарабатывает 10 %.
Представитель продает более 150 001 долл. США и зарабатывает 15 %.

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

Мы можем использовать формулу ВПР, чтобы рассчитать ставку выплат для заданной суммы продаж (значение поиска). Чтобы это работало, нам нужно установить последний аргумент в vlookup [range_lookup] равным TRUE.

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

Поэтому последний аргумент функции ВПР называется диапазон_просмотра. Если этот аргумент имеет значение TRUE, функция ВПР просматривает диапазоны значений в столбце минимального уровня, чтобы найти точное совпадение или значение, меньшее значения поиска.

Как настроить таблицу ставок

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

В этом примере для таблицы ставок комиссий первая строка в диапазоне поиска должна быть нулевой. Это связано с тем, что торговый представитель потенциально может иметь продажи в размере 0 долларов США, а значение поиска будет равно нулю. Если бы искомое значение (сумма продаж) было отрицательным числом, то виртуальный поиск вернул бы ошибку.

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

Если сумма продаж больше, чем последняя строка в диапазоне поиска, то виртуальный поиск вернет последнюю строку. Например, если торговый представитель продал 175 000 долл. США, функция vlookup вернет 15 %.

Рассчитать комиссию для возврата стоимости в долларах

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

Это означает, что выплаты будут одинаковыми, независимо от того, какая сумма продаж находится на уровне. В приведенном ниже примере, если выплата составит 1000 долларов США, если сумма продаж составляет 55 000 долларов США или 95 000 долларов США.

Выплата НЕ осуществляется по скользящей шкале. Это фиксированная ставка для каждого уровня.

Если вам нужен расчет по скользящей шкале, см. мою статью о расчете комиссии с многоуровневой структурой ставок с использованием СУММПРОИЗВ.

Расчет комиссии с XLOOKUP

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

Преимущества XLOOKUP

XLOOKUP имеет два основных отличия и преимущества:

Недостаток XLOOKUP

Основным недостатком XLOOKUP является совместимость. И вы, и все пользователи вашего файла должны использовать версию Microsoft/Office 365 с функцией XLOOKUP. Он НЕ совместим с предыдущими версиями и не доступен в более ранних версиях Excel.

Поэтому ВПР отлично работает в этом сценарии, и ее гораздо проще написать, чем вложенную формулу ЕСЛИ.

Избегайте вложенных формул ЕСЛИ

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

Затем вам нужно объединить все операторы ЕСЛИ в одну длинную и уродливую формулу. Они называются вложенными операторами IF.

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

Формулу ЕСЛИ гораздо сложнее поддерживать. Требуется модификация при добавлении/удалении строк из таблицы. ВПР и КСПР НЕ требуют такого обслуживания.

Если вы используете ЕСЛИ, убедитесь, что каждая ячейка ссылается на прейскурантную таблицу как абсолютная ссылка (F4 на клавиатуре). В противном случае вы получите неправильные результаты при копировании формулы вниз.

Использовать функцию ВПР намного проще и понятнее, чем использовать вложенные формулы ЕСЛИ. Как видно из этого примера, функция vlookup позволяет использовать одну формулу для расчета ставки комиссионных выплат для любой заданной суммы продаж.

Дополнительные ресурсы

Как вы используете функцию ВПР с ближайшим совпадением?

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

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

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

Поскольку Excel предлагает несколько способов написания формул диапазона в соответствии с вашими индивидуальными потребностями, вот три варианта формул диапазона, которые помогут вам начать работу!

1. Формулы минимума и максимума

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

Вам нужно выяснить, какие товары пользуются наименьшим и наибольшим спросом. Это небольшой список, но если вы продаете или перепродаете много товаров, следующая формула может оказаться бесценной.Вы можете легко найти минимальные и максимальные единицы измерения с помощью функций MIN() и MAX().

В ячейке B15 введите "=MIN(C2:C13)".
В ячейке B16 введите "=MAX(C2:C13)".

Теперь у вас есть краткий отчет о наименьшем количестве проданных устройств в штате (102 планшета в Айове) и о самом большом количестве проданных устройств (450 ноутбуков в Иллинойсе).

2. Первые k и последние k формулы

Предположим, вам нужно больше данных — не только о самом дешевом товаре, но и о трех самых дешевых. Вы можете найти их с помощью функции МАЛЕНЬКИЙ(). Чтобы использовать SMALL(), вам понадобятся два параметра:

Тот же диапазон или список значений, которые вы использовали для MIN().

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

Значение k, которое является желаемой позицией в нижней части списка. Если вы ищете наименьшее значение, то k = 1. Чтобы найти второе наименьшее значение, k = 2 и т. д.

Чтобы найти три самых больших значения, используйте функцию НАИБОЛЬШИЙ() с теми же параметрами, что и МАЛЕНЬКИЙ().

Обратите внимание, что SMALL() с k = 1 дает точно такой же результат, как MIN(). Точно так же LARGE() с k = 1 дает то же самое, что и MAX().

3. Формулы условного минимума и максимума

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

Excel поддерживает СУММЕСЛИ(), СЧЁТЕСЛИ() и другие полезные условные формулы. К сожалению, функций MINIF() или MAXIF() нет, но вы можете создать тот же эффект с помощью чуть более сложного метода, называемого формулой массива. Формула массива оценивает диапазон ячеек, а не одну ячейку.

Найдем минимальное значение для настольных компьютеров. Сначала введите соответствующее значение (рабочий стол) в ячейку, с которой вы сравниваете свою функцию. Если вы ищете значения рабочего стола, введите «рабочий стол» в B18. Приведенная ниже формула сравнивает эту ссылку на ячейку с тестируемым диапазоном. Вложите операторы MIN() и IF() следующим образом: «=MIN(IF(B2:B13=B18,C2:C13))» и нажмите - - .

Три формулы в строках 18, 19 и 20 рассчитывают минимальное количество проданных настольных компьютеров, ноутбуков и планшетов. Вы можете написать идентичные формулы с помощью MAX(), чтобы найти наибольшее количество проданных товаров.

Дальнейшие шаги

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

В области аналитики данных Microsoft Excel позволяет профессионалам лучше визуализировать данные и взаимодействовать с ними. M S Excel может предложить несколько функций на ходу.

Основы вычисления процентов

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

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

Представим, что вы набрали 475 баллов из 600. Теперь рассчитайте среднее значение ваших баллов.

Среднее значение рассчитывается путем деления вашего балла на общее количество баллов и умножения дроби на 100.

Из ответа видно, что у вас средний балл 79,16.

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

Последипломная программа по бизнес-анализу

Автоматический расчет процентов в Excel

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

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

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

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

Теперь на следующем этапе вы рассчитаете процент. Формула для расчета процента выглядит следующим образом.

Процент = полученные баллы / общее количество баллов

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

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

Финальный стол выглядит следующим образом:

Дальше вы попробуете сценарий расчета процентного отклонения в Excel.

Бесплатный курс: основы бизнес-анализа

Рассчитать процент вариации в Excel

Теперь рассмотрим пример с топливом. Каждый день цены на топливо меняются. Учтите, что цена топлива на сегодня составляет 97 рупий за литр, а вчера такая же цена топлива была 91 рупия за литр. Цена на топливо подорожала на 6 рупий за литр, а цена на топливо выросла на 0,06%.

Процент отклонения = (новая цена – старая цена) / старая цена

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

Рассчитайте новую сумму, используя вариацию в процентах

Вы всегда сталкиваетесь с множеством предложений по разным продуктам. Представьте себе, что вы купили мобильный телефон за 27 000 рупий после того, как получили скидку 23% от фактической стоимости телефона. Теперь вопрос в том, какова была реальная цена телефона?

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

Цена со скидкой * процент = уменьшенная сумма

Цена со скидкой + сниженная сумма = первоначальная цена

Взгляните на этот практический пример с использованием листа Excel.

Давайте включим в список несколько телефонов с процентом скидки.

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

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

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

Финальная таблица будет такой, как показано ниже.

Далее вы узнаете, как рассчитать приращение или уменьшение числа на основе процентных значений.

Бесплатный курс бизнес-аналитики с Excel

Увеличение или уменьшение числа с использованием процентов

Предположим, что вы управляете компанией и хотите сделать надбавку на 15 % для всех своих сотрудников.

Давайте введем процент повышения в новый столбец рядом со столбцом оклада.

Теперь примените следующую формулу, чтобы получить обновленную зарплату.

Обновленная заработная плата = существующая заработная плата * (1 + процент повышения)

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

Последняя таблица будет выглядеть следующим образом.

Аналогичным образом, если вам нужно вычислить уменьшение, вы можете внести небольшие изменения в формулу, как показано ниже.

Обновленная заработная плата = действующая заработная плата * (1 – уменьшение в процентах)

На этом вы достигли конца этого руководства "Универсальное решение для расчета процентов в Excel".

Ждете карьеры бизнес-аналитика? Ознакомьтесь с сертификационным курсом по бизнес-аналитике с помощью Excel и пройдите сертификацию уже сегодня.

Дальнейшие шаги

Вашей следующей остановкой может стать "Параметры страницы в Excel". Вы узнаете, как Microsoft Excel позволит вам настроить свою страницу, прежде чем представить свою информацию в таблицах EXCEL.

Этот сертификационный курс по бизнес-аналитике, предлагаемый Simplilearn, знакомит вас с основными понятиями анализа данных и статистики, помогая принимать решения на основе данных. В этом обучении вы знакомитесь с Power BI и изучаете статистические концепции, которые помогут вам извлекать ценные сведения из доступных данных для представления результатов с помощью панелей управления на уровне руководителей.

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

Об авторе

Равикиран А С

Равикиран А.С. работает в Simplilearn в качестве аналитика-исследователя. Он увлеченный компьютерщик, всегда стремящийся изучить новейшие технологии. Он владеет языком программирования Java, большими данными и мощными платформами больших данных, такими как Apache Hadoop и Apache Spark.

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