Как пропорционально разделить сумму на другие суммы в Excel
Обновлено: 21.11.2024
Если вам когда-либо приходилось распределять сумму по времени и разбивать ее на месячные столбцы в зависимости от количества дней, вы быстро поймете, что реализовать эту простую идею сложно. Как и во всем в Excel, есть много способов выполнить эту задачу, и в этом посте рассматривается один из таких способов. Если вы предпочитаете другой метод, оставьте комментарий со своей формулой.
Цель
Цель — взять сумму, скажем, 1000 долл. США, и распределить ее по ежемесячным столбцам между датами начала и окончания распределения, например, с 1 января по 31 марта. Мы ожидаем увидеть около трети суммы в каждом ежемесячном столбце за январь, февраль и март. Мы будем распределять его по количеству дней в каждом месяце, поэтому он не будет равен трети (в январе больше дней, чем в феврале). В дополнение к этой основной цели наша формула должна быть последовательной, чтобы мы могли заполнить ее вниз и вправо, и она должна продолжать работать. Кроме того, наша формула должна обрабатывать отрицательные суммы в случае наличия каких-либо кредитовых сальдо. И наша формула должна возвращать ноль, если период распределения выходит за пределы месяцев, отраженных в нашей таблице распределения. Наконец, наша формула должна обрабатывать неполные месячные периоды и поддерживать периоды распределения, скажем, с 15.01.14 по 15.04.14.
Несмотря на то, что в Excel есть несколько встроенных функций, которые приближают нас к цели, я не нашел ни одной, которая удовлетворяла бы всем вышеперечисленным целям. Одна из таких функций, которая сближает нас, — это функция VDB, однако она имеет встроенное предположение о том, что распределение производится в равные периоды, и не позволяет нам распределять сумму на основе количества дней в месяце. Если ваша книга не нуждается в этом ежедневном распределении, а просто требует целых месяцев, то функция VDB значительно упростит вашу книгу, поэтому определенно стоит потратить время на ее проверку.
Примечание: в следующих нескольких абзацах рассматривается логика формулы, однако, если вам просто нужен ответ, пропустите сообщение до конца и загрузите образец файла.
Прохождение
Вместо того, чтобы просто опубликовать формулу, я подумал, что было бы полезно создать формулу вместе, по частям, чтобы, если вы решите использовать ее в своих книгах, вы могли адаптировать ее и поддерживать ее с течением времени. .
Во-первых, давайте взглянем на образец рабочей тетради, чтобы наша цель была кристально ясной.
Идея довольно проста. Мы берем сумму, делим ее на количество дней в периоде распределения, а затем умножаем эту дневную сумму на количество дней, представленных в каждом столбце таблицы распределения. Связанные формулы в Excel также довольно просты, за исключением той части, которая вычисляет количество дней, представленных в каждом столбце месяца.
Начнем с легко подсчитываемых сумм, количества дней (Days) и дневной суммы (DailyAmt). Количество дней можно быстро определить, вычитая даты Alloc End и Alloc Start. В ячейке F9 формула будет выглядеть так:
Эта формула вычисляет количество дней между двумя датами, что в большинстве случаев вполне приемлемо. Однако в нашем случае это создает проблему. В простом случае, если мы хотим выделить значение с 01.01.14 по 31.03.14, нам нужно выделить 90 дней. То есть 31 день на январь, 28 дней на февраль и 31 день на март. Однако простое вычитание дает всего 89 дней, поскольку оно не включает обе конечные точки дат. Эту проблему легко решить, так как мы можем просто добавить 1 к результату. Наша модифицированная формула, показанная ниже, прекрасно заполнит столбец «Дни».
Далее идет столбец DailyAmt. Это представляет собой сумму, которую нужно выделять ежедневно, так что это просто общая сумма, деленная на количество дней. Формула в G9:
Мы можем заполнить каждую из этих формул, и результат показан ниже.
Пока все хорошо. Теперь давайте перейдем к самой сложной части: вычислению количества дней, представленных в каждом столбце.
Мы знаем, что если мы можем вычислить количество дней распределения, представленное в каждом столбце, мы можем просто умножить его на дневную сумму (DailyAmt). Поэтому сейчас мы сосредоточимся только на подсчете количества дней, представленных в каждом столбце.
Давайте сделаем предположение, которое поможет в этом упражнении. Предположим, что заголовки столбцов в ежемесячной таблице распределения представляют собой значения дат, представляющие последний день месяца. Таким образом, заголовок столбца «Ян» на самом деле является значением даты, представляющим 31.01.14 и т. д.
Вместо того, чтобы просто опубликовать формулу для расчета дней, я хотел предоставить более наглядный способ понять механизм. Поэтому я создал серию снимков экрана, чтобы проиллюстрировать, что делает каждая часть формулы.
Если подумать, есть несколько случаев, когда нам нужна наша причудливая формула. Нам нужно, чтобы он обрабатывал случай, когда текущий заголовок столбца, назовем его текущим периодом, попадает между датами начала и окончания распределения. Наша формула также должна обрабатывать случай, когда заголовок ежемесячного столбца меньше даты начала. Он также должен работать, когда текущий период больше, чем дата окончания. Итак, мы будем двигаться медленно и шаг за шагом улучшать формулу.
Давайте сначала рассмотрим простой случай, когда текущая метка столбца соответствует одному месяцу периода распределения. Для серии снимков экрана ниже все они предполагают, что период распределения — это трехмесячный период с 1 января (дата начала) до 31 марта (дата окончания). Если текущий период (CurPer) равен 1/31, то количество дней можно рассматривать как отрезок линии A, показанный ниже.
Количество дней в сегменте A легко вычислить путем вычитания конечных точек даты.
Пока все хорошо? Хорошо, давайте рассмотрим другой случай. Случай, когда дата месячного столбца предшествует дате начала распределения. Например, текущая месячная дата столбца 30 ноября, как показано ниже.
В этом случае наша формула (CurPer – StartDate) даст отрицательное количество дней. Это легко исправить. Функция MAX возвращает наибольшее значение аргумента. Например, MAX(100,0) возвращает 100, а MAX(-200,0) возвращает 0. Таким образом, мы можем легко обновить формулу, чтобы предотвратить возврат отрицательного числа, обернув вокруг нее функцию MAX.
Еще один случай, который должна обрабатывать наша формула, — это случай, когда текущий период превышает один месяц периода распределения. Например, когда CurPer = 2/28. Взгляните на скриншот ниже.
Наша существующая формула (CurPer – StartDate) возвращает совокупное количество дней между текущим периодом и датой начала. Поскольку нам нужно только количество дней в текущем месячном столбце, нам нужно вычесть количество дней до текущего периода. Это представлено сегментом C выше. Сегмент C можно вычислить путем вычитания сегмента B из сегмента A.
Значение PriorPer легко вычисляется с помощью функции КОНМЕСЯЦА. Если вы еще не изучили функцию КОНМЕСЯЦА, вот статья, в которой она рассматривается. Чтобы избежать отрицательных значений дня, мы снова воспользуемся функцией MAX.
Последний случай, который нам нужно обработать, — это случай, когда текущий период приходится на дату окончания, например, когда CurPer = 5/31. Это показано ниже.
Наша существующая формула, которая вычисляет сегмент C, вернет ненулевое значение, даже если распределения не должно быть, поскольку дата текущего периода наступает после последнего дня периода распределения. Таким образом, мы вычитаем из него количество дней в текущем периоде, которые превышают EndDate, представленный выше сегментом F. Желаемый результат формулы C-F возвращает ноль дней.
Опять же, чтобы предотвратить возврат отрицательных дней, мы оборачиваем их в функцию MAX и снова вычисляем дату PriorPer с помощью функции КОНМЕСЯЦА.
Если мы поместим все это в формулу, а затем заполним формулу вниз и вправо, получится результат количества дней, рассчитанных для каждого столбца в таблице распределения.
Формула Excel, используемая в ячейке H9, выглядит следующим образом:
Хотя это может показаться запутанным, это просто вычисление шагов, которые мы сделали выше. Мы вычисляем сегмент C (первая строка в приведенной выше формуле) и вычитаем из него сегмент F (вторая строка).
Функции MAX нужны только для того, чтобы гарантировать, что мы не вернем отрицательное число дней, а функции КОНМЕСЯЦА нужны только для вычисления значений даты предыдущего периода.
Вы заметите, что нам по-прежнему нужно учитывать тот факт, что мы отстаем на один день в первом столбце периода распределения. То есть столбец 1/31 неверно отражает 30 дней, тогда как должен быть 31 день.Это связано с тем, что 31 января — 1 января составляет 30 дней, но нам нужно сделать распределение на основе 31 дня. Нам нужно обновить формулы нашей таблицы распределения, чтобы добавить единицу к количеству дней для первого периода распределения. Есть много способов добиться этого, один из них — использовать формулу сравнения, которая определяет, равен ли период CurPer периоду StartDate. Формула сравнения возвращает ИСТИНА, а ИСТИНА обрабатывается механизмом вычислений Excel как 1. Итак, если мы добавим следующее в конец нашей формулы, все будет хорошо:
Преобразование приведенной выше логики в реальную формулу Excel приводит к обновленной формуле ниже.
Приведенная выше формула берет сегмент C минус сегмент F, а затем добавляет 1, если последний день текущего периода столбца равен начальной дате периода. Обновленная таблица распределения показана ниже.
Теперь, когда количество дней точное, все, что нам нужно сделать, это умножить их на значение DailyAmt, хранящееся в ячейке $G9. Полученный рабочий лист показан на снимке экрана ниже.
Формула Excel в H9 выглядит следующим образом:
И что, мой друг, это один из способов распределить сумму по ежемесячным столбцам. Есть так много других способов, я хотел бы услышать ваш предпочтительный метод… опубликуйте свою формулу в поле для комментариев ниже.
Forum Expert Дата регистрации 04.12.2015 Местоположение Кейптаун MS-Off Ver 2013 | 2016 | Сообщений за 2019 г. – 10 609
Формула пропорционального разделения
В прикрепленном файле. У меня есть 9 процентов, которые составляют 100%
Мне нужно удалить значение 25% и разделить его пропорционально с другими 8 оставшимися значениями, чтобы вернуться к моим 100%
Любые идеи. < /p>
Удачи
Я не претендую на то, чтобы знать, что я делаю, однако, как и вы, я тоже с чего-то начинал.
Один день, одна проблема за раз.
Если вы считаете, что я вам помог, нажмите на звездочку слева от сообщения [Добавить репутацию]
Также. добавьте комментарий, если хотите.
И помните. Отметить тему как решенную.
Форум Excel рулит.
Forum Expert Дата регистрации 04.12.2015 Местоположение Кейптаун MS-Off Ver 2013 | 2016 | Сообщений за 2019 г. – 10 609
Гуру форума Дата регистрации 13 апреля 2005 г. Местоположение Северная Америка MS-Off Ver 2002/XP и 2007 Сообщений 14 045
Это может зависеть от того, что именно вы подразумеваете под пропорциональным. В качестве первого предположения:
Сначала алгебра: Текущая формула 100=сумма(Ai)+B
Есть 8 Ai, поэтому разделите B на 8 и прибавьте это к каждому Ai Ci=Ai+B/8
Новая сумма 100 =Sum(Ci) Нужно показать, что это то же самое, что и начальная формула:
100=Sum(Ci)=Sum(Ai+B/8)=Sum(Ai)+8*B/ 8=Сумма(Ai)+B
Если пропорциональность означает что-то иное, чем равномерное распределение, вам нужно выяснить, на что делить B для каждой записи. Опять же, я бы начал с задачи по алгебре, но с подходящим определением деления.
Первоначальное сообщение от shg
Forum Expert Дата регистрации 04.12.2015 Местоположение Кейптаун MS-Off Ver 2013 | 2016 | Сообщений за 2019 г. – 10 609
Нет . Над моей головой, лол
MrShorty. Любой способ добавления формулы в образец книги. Буду очень признателен.
Гуру форума Дата регистрации 13 апреля 2005 г. Местоположение Северная Америка MS-Off Ver 2002/XP и 2007 Сообщений 14 045
Я считаю, что понимание алгебры, лежащей в основе проблемы, действительно помогает мне программировать решение. Если эта простая алгебра не укладывается у вас в голове, могу предложить вам уделить ей немного времени.
Я бы использовал следующую формулу:
B2 =A2+A$10/8 -- копируется в B9
B11 является копией A11
Это очень похоже на то, что вы видите в столбце B на новом снимке экрана. Но я не понял ничего из того, что вы сделали сверх этого, и не понимаю, откуда взялись 33 1/3. На данный момент я предполагаю, что не понимаю, чего вы хотите - что вы подразумеваете под «пропорциональностью».
Обратите внимание, что результат, который вы получили в Q, — это то, что я получаю, когда нормализую A2:A9 и получаю «долю от суммы» для всех записей, кроме 25, а затем использую эту дробь в качестве множителя:
D2 =A2/SUM(A$2:A$9) копируется в D9
E2 =A2+D2*A$10
E11 копия A11
Это то, что вы подразумеваете под пропорциональным?
Forum Expert Дата регистрации 04.12.2015 Местоположение Кейптаун MS-Off Ver 2013 | 2016 | Сообщений за 2019 г. – 10 609
Честно говоря, я не знаю, какого результата я ожидал.
Я предположил, что процентные значения в A2:A10 имеют определенную пропорцию.
Затем я хотел вычесть 25 и вычислить пропорциональное значение 25 и добавить его обратно ко всем значениям в A2:A9, чтобы получить ту же пропорцию. Если это имеет смысл.
Какими значениями, по вашему мнению, должны стать A2:A9.
Клиент говорит, что это текущий пропорциональный процент его инвестиций.
удалить 25% и добавить пропорционально к остальным 8
Гуру форума Дата регистрации 13 апреля 2005 г. Местоположение Северная Америка MS-Off Ver 2002/XP и 2007 Сообщений 14 045
Это первые два способа пропорционального распределения, которые я вижу. Один - распределите 1/8 от 25 каждому из остальных, или два - распределите его таким образом, чтобы более высокие проценты получали больше 25, чем более низкие проценты (с использованием той доли нормализации, которую я использовал). Если вы не уверены, что имел в виду клиент (или если клиент имел в виду совсем другое), то я не знаю, что еще предложить. Предложите клиенту эти два способа распределения 25 и спросите его/ее, какой из них лучше всего отражает его/ее ожидания.
Если абсолютное число недостаточно точно описывает сценарий, пропорции помогают заполнить пробел, описывая число по отношению к общему количеству. Например, знание того, что 30 заказов были предназначены для конкретной модели продаж, относительно бессмысленно без знания общего количества заказов. Без этой жизненно важной информации вы не знаете, на долю этой модели приходится весь объем продаж или лишь незначительное меньшинство. Однако если вы знаете, что 0,35%, или 35% заказов приходится на эту модель, то вы лучше понимаете ее популярность. Если у вас есть эти данные в Excel 2013, вы можете удобно подсчитать количество заказов на этот продукт, общее количество заказов в целом и их долю.
Используйте функцию «CountIf» в пустой ячейке, чтобы подсчитать количество записей, содержащих определенный термин. Используйте формат «=CountIf(диапазон, критерии)» и поместите критерии в кавычки, если только вы не ссылаетесь на другую ячейку, содержащую текстовые или числовые критерии. Диапазон может указывать определенный диапазон ячеек, например «B1:B15», или весь столбец, например «B:B». Последний вариант хорош, если столбец содержит только те данные, которые вы хотите найти. Например, для поиска в столбце B слова «Продукт A» введите в ячейку C1 следующую формулу (без кавычек здесь и далее): =CountIf(B:B», «Product A»)
Используйте функцию «CountA» в другой ячейке, чтобы подсчитать количество непустых ячеек, чтобы использовать их как итог. Используйте формат «=CountA(диапазон)» и используйте тот же диапазон, что и в предыдущей формуле. Например, введите «=CountA(B:B)» в ячейку C2.
Поделите первый результат на второй, чтобы вычислить десятичную пропорцию. Продолжая пример, введите «=C1/C2» в ячейку C3, чтобы рассчитать долю общего объема продаж, относящуюся к продукту A.
Нажмите ячейку, содержащую пропорцию, и щелкните значок «Процент» в группе «Числа» на вкладке «Главная», чтобы отформатировать пропорцию в процентах.
В этом посте показаны два разных подхода: формула массива и определяемая пользователем функция. Вы найдете UDF позже в этом посте.
На приведенном выше рисунке показаны значения в столбце A, и они равномерно распределены по 9 столбцам, от столбцов D до M.
Теперь скопируйте ячейку D5 и вставьте в ячейки ниже и справа.
Объяснение формулы в ячейке D5
Шаг 1. Отслеживание списков
Функция СТОЛБЦЫ работает так же, как функция СТРОКИ, за исключением того, что она подсчитывает столбцы, а не строки в ссылке на ячейку.
| Ячейка | Функция СТОЛБЦЫ | Результат |
| СТОЛБЦЫ($A$1:A1) | 1 | |
| E5 | СТОЛБЦЫ($A$1:A2) | 2 |
| F5 | СТОЛБЦЫ($A$1:A3) | 3 |
Ячейка E2 содержит количество списков, в которые нужно поместить значения.
и возвращает ИСТИНА. Это заставляет формулу возвращать пустые ячейки, когда количество списков превышает значение в ячейке E2.
Шаг 2. Возвращает пустые значения, если заполнены все списки
Функция ЕСЛИ позволяет указать значение, если логическое выражение возвращает ИСТИНА (аргумент 2), и другое значение, если ЛОЖЬ (аргумент 3).
Шаг 3. Возврат номера на основе расширяющейся ссылки на ячейку
Функция СТРОКИ возвращает количество строк в ссылке на ячейку, эта ссылка на ячейку особенная. Он расширяется по мере того, как формула копируется в ячейки ниже.
| Ячейка | Функция ROWS | Результат |
| ROWS($A$1:A1) | 1 | |
| B4 | ROWS($A$1:A2) | 2 |
| B5 | СТРОКИ($A$1:A3) | 3 |
Шаг 4. Умножение с ячейкой E2
Шаг 5. Вычтите $E$2 из столбцов
Шаг 6. Вычисление номера строки
Шаг 6. Получение значения на основе номера строки
Функция ИНДЕКС возвращает значение в диапазоне ячеек на основе номера строки и столбца. Это ссылка на ячейку одного столбца, поэтому в этом случае номер столбца не требуется.
и возвращает "ГМ" в ячейке D5.
Получить файл Excel
разделить значения на группы с помощью excel-formulav3.xlsx
Пользовательская функция
Вы решаете, сколько групп вам нужно, выбирая диапазон ячеек с таким количеством столбцов, сколько вы хотите групп, а затем вводите пользовательскую функцию. Он предназначен для группировки значений в зависимости от того, сколько столбцов вы выбрали перед вводом.
На анимированном изображении выше показан диапазон ячеек с 5 столбцами.
В этом посте показано, как классифицировать значения по рабочим листам:
и в этом посте показано, как классифицировать значения по столбцам на основе условия:
Как ввести формулу массива
- Выберите диапазон ячеек C2:G11.
- Вставьте приведенную выше формулу массива в строку формул.
- Одновременно нажмите и удерживайте клавиши CTRL и SHIFT.
- Нажмите Enter один раз
Формула в строке формул теперь выглядит следующим образом:
Не вводите эти фигурные скобки самостоятельно, они появятся автоматически, если вы правильно выполнили вышеуказанные шаги.
Формулы массива позволяют выполнять сложные вычисления, недоступные для обычных формул.
Код VBA
Как скопировать код в книгу?
Обратите внимание: убедитесь, что вы сохранили свою книгу как файл *.xlsm, иначе ваша книга потеряет весь свой код vba в следующий раз, когда вы ее закроете.
Получить файл Excel
Групповые значения.xlsm
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
В этом сообщении описывается, как разделить слова в диапазоне ячеек на ячейки, каждая из которых использует пользовательскую функцию. я […]
В этом посте я покажу, как создать новый лист для каждого самолета с помощью vba. […]
В этой статье блога описывается, как разделить строки в ячейке с помощью пробела в качестве символа-разделителя, например, текст на […]
Эта определяемая пользователем функция создает уникальный список слов и определяет, сколько раз они встречаются в выбранном […]
В этой статье демонстрируются две формулы, которые извлекают отдельные значения из отфильтрованной таблицы Excel, одна формула для подписчиков Excel 365 […]
В этой статье демонстрируется определяемая пользователем функция, которая выводит список файлов в заданной папке и вложенных папках. Пользовательская функция — это […]
На изображении выше показана определяемая пользователем функция в диапазоне ячеек B6:D7, которая позволяет выполнять поиск в папке и вложенных папках […]
14 ответов на вопрос «Разделить значения поровну на группы»
я попытался добавить список номер 4 с помощью этой формулы = ЕСЛИ (D1> 3, ИНДЕКС ($ A $ 2: $ A $ 21, (СТРОКА ($ A $ 2: $ A $ 21) - МИН (СТРОКА ( $ A $ 2) :$A$21))+1)*D1-(D1-4)),"")
значение на f4 равно DD, но f5, f6 и т.д. просто повторяем результат
Привет, Оскар! Спасибо за отличный урок.
Я надеялся использовать то, что вы описали выше, но для данных, которые занимают одну строку.
Например, если у меня есть данные, которые простираются от A1:J1, я хотел бы разделить их на 5 строк, чтобы значения отображались в A2:B2, A3:B3, A4:B4,A5: B5, A6:B6.
Я попытался манипулировать предоставленными вами формулами, но безуспешно. Надеялся, что вы поможете!
Формула массива в диапазоне ячеек: A2:E3:
Как создать формулу массива
1. Выберите диапазон ячеек A2:E3
2. Вставьте формулу в строку формул
3. Нажмите и удерживайте Ctrl + Shift
4. Нажмите Enter
Настройте диапазоны ячеек, выделенные жирным шрифтом, если вы вводите формулу массива в другом диапазоне ячеек.
Привет, Оскар! Мне понадобится ваша помощь, чтобы растянуть столбец A до 500 строк с 8 списками. Спасибо!
Эй, быстрый вопрос, можно ли расширить его, скажем, до списка из 100 значений и 10 групп. Я пробовал редактировать код, но не смог заставить формулу работать правильно
Вот ссылка на мою электронную таблицу на Google Диске (я использую Excel 2013 — просто использую Google Диск для размещения файла!) Я был бы очень признателен, если бы вы могли взглянуть и сообщить мне, куда я иду неправильно.
Ссылка
Я создал определяемую пользователем функцию, которую проще использовать, прочитайте этот пост еще раз.
Привет, Оскар! Большое спасибо, это намного проще, спасибо!
Привет, Оскар! Большое спасибо за этот урок. Я пытался воспроизвести эту точную книгу для 10 групп, а также использовать функции «ВЫБРАТЬ» И «СЛУЧАЙНЫЙ МЕЖДУ» для рандомизации значений столбца A в массив, но у меня возникли проблемы с этим. Можно ли это настроить? Очень ценю вашу помощь!
Я пытаюсь использовать вашу формулу (файл электронной таблицы), чтобы разделить 253 учащихся (значение) на 15 групп поровну. Пробовал редактировать код. но я получаю сообщение об ошибке при попытке настроить массив.
Если вы пытаетесь расширить формулу массива и получаете сообщение об ошибке, попробуйте следующее:
Формула массива в столбце C вводится в диапазоне ячеек C4:C25.
Выберите ячейку нового диапазона ячеек, например C4:C27
Нажмите левую кнопку мыши в строке формул.
Нажмите и удерживайте CTRL + SHIFT. Нажмите Enter, чтобы создать новую формулу массива для нового диапазона.
Я рекомендую вам использовать определяемую пользователем функцию, с ней намного проще работать.
Я очень ценю ваш блог, он очень полезен.
однако моя проблема в том, что я не хочу перечислять имена. Я хочу знать, сколько ЛЮДЕЙ должно быть в каждой группе. поэтому у меня есть уровни, и на каждом уровне есть определенное количество людей.
и в зависимости от количества ЛЮДЕЙ они будут разделены поровну на N групп (1 группа, 2 группы, иногда до 8 групп)
поэтому таблица будет отображаться горизонтально с количеством людей в каждой группе.
как я могу это сделать?
Оставить ответ
Как комментировать
Как добавить формулу в комментарий
Вставьте сюда формулу.
Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >
Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]
В одной из предыдущих статей мы обсуждали три основные функции для расчета среднего значения в Excel, которые очень просты и удобны в использовании. Но что, если некоторые значения имеют больший «вес», чем другие, и, следовательно, вносят больший вклад в итоговое среднее значение? В таких ситуациях вам потребуется рассчитать средневзвешенное значение.
Хотя в Microsoft Excel нет специальной функции средневзвешенного значения, в нем есть несколько других функций, которые окажутся полезными в ваших расчетах, как показано в следующих примерах формул.
Что такое средневзвешенное значение?
Среднее взвешенное значение – это своего рода среднее арифметическое, при котором некоторые элементы набора данных имеют большее значение, чем другие. Другими словами, каждому усредняемому значению присваивается определенный вес.
Оценки учащихся часто рассчитываются с использованием средневзвешенного значения, как показано на следующем снимке экрана. Обычное среднее значение легко вычисляется с помощью функции Excel AVERAGE. Однако мы хотим, чтобы формула среднего учитывала вес каждого действия, указанного в столбце C.
В математике и статистике средневзвешенное значение рассчитывается путем умножения каждого значения в наборе на его вес, затем вы складываете произведения и делите сумму произведений на сумму всех весов.
В этом примере, чтобы рассчитать средневзвешенную оценку (общую оценку), вы умножаете каждую оценку на соответствующий процент (преобразованный в десятичную дробь), складываете 5 произведений и делите это число на сумму 5. веса:
Как видите, нормальная средняя оценка (75,4) и средневзвешенная оценка (73,5) — разные значения.
Вычисление средневзвешенного значения в Excel
В Microsoft Excel средневзвешенное значение рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel сделают большую часть работы за вас.
Пример 1. Вычисление средневзвешенного значения с помощью функции СУММ
Если у вас есть базовые знания о функции СУММ в Excel, приведенная ниже формула вряд ли потребует каких-либо объяснений:
=СУММ(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6)/СУММ(C2:C6)
По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы указываете ссылки на ячейки вместо чисел.
Как видно на снимке экрана, формула возвращает точно такой же результат, как и вычисление, которое мы сделали минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым функцией СРЗНАЧ (C8), и средневзвешенным значением (C9).
Несмотря на то, что формула СУММ очень проста и понятна, она не подходит, если нужно усреднить большое количество элементов. В этом случае лучше использовать функцию СУММПРОИЗВ, как показано в следующем примере.
Пример 2. Нахождение средневзвешенного значения с помощью функции СУММПРОИЗВ
Функция СУММПРОИЗВ в Excel идеально подходит для этой задачи, поскольку она предназначена для суммирования произведений, что нам и нужно. Итак, вместо того, чтобы умножать каждое значение на его вес по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в данном контексте массив — это непрерывный диапазон ячеек), а затем делите результат на сумму весов:
Предполагая, что значения для усреднения находятся в ячейках B2:B6, а веса — в ячейках C2:C6, наша формула Sumproduct Weighted Average примет следующий вид:
=СУММПРОИЗВ(B2:B6, C2:C6) / СУММ(C2:C6)
Чтобы увидеть фактические значения массива, выберите его в строке формул и нажмите клавишу F9. Результат будет примерно таким:
Итак, функция СУММПРОИЗВ умножает 1-е значение в массиве1 на 1-е значение в массиве2 (91*0,1 в этом примере), а затем умножает 2-е значение в массиве1 на 2-е значение в массиве2 ( 65*0,15 в этом примере) и так далее. Когда все умножения выполнены, функция складывает произведения и возвращает эту сумму.
Чтобы убедиться, что функция СУММПРОИЗВ дает правильный результат, сравните ее с формулой СУММ из предыдущего примера, и вы увидите, что числа идентичны.
При использовании функции СУММ или СУММПРОИЗВ для нахождения среднего веса в Excel сумма весов не обязательно должна составлять 100 %. Их также не нужно выражать в процентах. Например, вы можете составить шкалу приоритет/важность и присвоить каждому элементу определенное количество баллов, как показано на следующем снимке экрана:
Ну вот и все, что касается расчета средневзвешенного значения в Excel. Вы можете скачать электронную таблицу взвешенных средних и попробовать формулы на своих данных. В следующем уроке мы подробно рассмотрим расчет скользящей средней. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе!
Вас также может заинтересовать
37 комментариев к "Как рассчитать средневзвешенное значение в Excel"
Привет,
Можете ли вы объяснить мне эту ситуацию, когда у меня есть годовые данные для каждого конкретного блюда с 21 января по 21 декабря на ежедневном уровне.
Теперь каждое блюдо доступно по разным ценам. дней в неделю.Например, курица в масле будет доступна все 7 дней в неделю, но, возможно, сэндвич с индейкой будет доступен только два раза в неделю.
Как рассчитать средневзвешенное дневное значение для каждого блюда.
Здравствуйте!
Формула расчета средневзвешенного значения может быть совершенно разной в зависимости от того, как выглядят ваши данные. Если в столбце B указано количество, а в столбце C – цена, формула может выглядеть так:
=СУММПРОИЗВ(B2:B53, C2:C53) / СУММ(C2:C53)
Если это не то, что вы хотели, опишите проблему более подробно.
Читайте также: