Как пропорционально распределить сумму из двух таблиц

КАК РАСПРЕДЕЛИТЬ СУММУ ПО СУММАМ В EXCEL

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

Возьмем следующий пример. У нас есть общая сумма в ячейке A1, а в ячейках A2, A3 и A4 находятся значения, к которым нужно распределить эту сумму.

Процесс распределения будет следующим:

1. В ячейке B2 введите формулу =A1*A2/SUM($A$2:$A$4) . Это позволит распределить сумму на основе отношения значения A2 к сумме значений A2:A4.
2. Скопируйте формулу из ячейки B2 в ячейки B3 и B4, чтобы распределить сумму по другим значениям.

Теперь общая сумма будет распределена пропорционально значениям в ячейках A2, A3 и A4.

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

Как сложить числа в Excel Функция СУММ

1 Функция СУММЕСЛИ в excel (SUMIF)

ТЕСТ! Выберите подарок, а я расскажу что ждет Вас в 2024 году!

Excel:Как посчитать сумму чисел в столбце или строке

Сводные таблицы Excel ➤ Подробная инструкция для начинающих

3 лучших способа расчёта суммы в excel

Умная таблица в Excel ➤ Секреты эффективной работы

Михаил Захаров

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

Вам также может понравиться:

Как разбить сумму пропорционально на другие суммы excel

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

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

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

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

Например, если у вас есть простые табличные данные, вы можете использовать простую формулу SUM (СУММ), но если у вас есть таблица Excel, лучше всего использовать структурированные ссылки. Для этого также можно использовать Power Query.

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

Расчет промежуточной суммы с табличными данными

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

Использование оператора сложения

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

Ниже приведены шаги для этого.

Шаг 1. В ячейке C2, которая является первой ячейкой, в которой вы хотите получить промежуточную сумму, введите

Это просто получит те же значения продаж в ячейке B2.

Это даст вам результат, как показано ниже.

Это действительно простой метод, который хорошо работает в большинстве случаев.

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

Использование SUM с частично заблокированной ссылкой на ячейку

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

Ниже приведена формула СУММ, которая даст вам промежуточную сумму.

Позвольте мне объяснить, как работает эта формула.

В приведенной выше формуле SUM я использовал ссылку для добавления как $ B $ 2: B2

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

Расчет промежуточной суммы в таблице Excel

При работе с табличными данными в Excel рекомендуется преобразовать их в таблицу Excel. Это значительно упрощает управление данными, а также упрощает использование таких инструментов, как Power Query и Power Pivot.

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

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

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

Ниже приведена формула, которая сделает это:

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

А [@Sale] относится к значению в ячейке в той же строке в столбце Sale.

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

Ниже приведены шаги для этого:

• В ячейке C2 введите = SUM (
• Выберите ячейку B1, которая является заголовком столбца со стоимостью продажи. Вы можете использовать мышь или клавиши со стрелками. Вы заметите, что Excel автоматически вводит структурированную ссылку для этой ячейки.
• Добавьте: (символ двоеточия)
• Выберите ячейку B2. Excel снова автоматически вставит структурированную ссылку для ячейки
• Закройте скобку и нажмите Enter.

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

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

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

Расчет промежуточной суммы с помощью Power Query

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

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

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

Ниже приведены шаги для этого:

Вышеупомянутые шаги вставят в вашу книгу новый лист с таблицей с промежуточными итогами.

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

Если у вас уже есть набор данных, и все, что вам нужно сделать, это добавить промежуточные итоги, лучше не использовать Power Query.

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

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

Как это работает?

Теперь позвольте мне быстро объяснить, что происходит в этом методе.

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

Затем мы вставляем настраиваемый столбец и используем формулу ниже

Это формула List.Sum, которая даст вам сумму диапазона, указанного в ней.

И этот диапазон указывается с помощью функции List.Range.

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

Расчет промежуточной суммы на основе критериев

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

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

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

Это можно сделать с помощью формулы СУММЕСЛИ, которая вычисляет промежуточную сумму, обеспечивая при этом выполнение указанного условия.

Ниже приведена формула, которая сделает это для столбцов «Принтер»:

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

В приведенных выше формулах я использовал SUMIF (СУММЕСЛИ), которая дает мне сумму в диапазоне при соблюдении указанных критериев.

Формула принимает три аргумента:

• диапазон : это диапазон критериев, который будет проверяться на соответствие указанным критериям.
• критерии : это критерии, которые будут проверяться, только если этот критерий выполняется, тогда значения в третьем аргументе, который является диапазоном сумм, будут добавлены
• [диапазон_суммы] : это диапазон сумм, из которого будут добавлены значения, если критерии соблюдены.

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

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

Если у вас есть несколько условий, которые вам нужно проверить, вы можете использовать формулу SUMIF.

Промежуточная сумма в сводных таблицах

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

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

Ниже приведены шаги по добавлению дополнительного столбца, в котором будет отображаться промежуточная сумма продаж по дате:

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

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

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

Разделить число 100 на две части прямо пропорционально числам 2 и 3 .

Эту задачу следует понимать так : разделить 100 на две части, чтобы первая относилась ко второй, как 2 и 3 . Если обозначить искомые числа буквами х ₁ и х ₂ , то эту задачу можно сформулировать и так. Найти х ₁ и х ₂ такие, чтоб

Разделить 780 на четыре части пропорционально числам

Эту задачу следует понимать так: если обозначить искомые числа через х ₁ , х ₂ , х ₃ и х ₄ , то :

Умножив каждый из знаменателей на 100 и воспользовавшись свойством равных отношений, получим :

Деление числа на части обратно пропорционально данным числам.

Чтобы разделить число на части обратно пропорционально данным числам, надо разделить его прямо пропорционально числам, обратным данным.

Разделить число 52 на три части обратно пропорционально числам

Комментариев нет:

Уроки математики и физики (RU + UA)

• I. НАТУРАЛЬНЫЕ ЧИСЛА И ДРОБИ (RU + UA + EN)
• II. ПРОПОРЦИИ ПРОЦЕНТЫ МАСШТАБ (RU + UA)
• III. РАЦИОНАЛЬНЫЕ ЧИСЛА (RU + UA)
• IV. АЛГЕБРАИЧЕСКИЕ ВЫРАЖЕНИЯ (RU + UA)
• V. КОРНИ (RU + UA)
• VI. ФУНКЦИИ И ГРАФИКИ (RU + UA + EN)
• VII. УРАВНЕНИЯ И СИСТЕМЫ УРАВНЕНИЙ (RU + UA)
• VIII. ПОКАЗАТЕЛЬНАЯ И ЛОГАРИФМИЧЕСКАЯ ФУНКЦИИ
• IX. НЕРАВЕНСТВА (RU + UA)
• X. ТРИГОНОМЕТРИЧЕСКИЕ ФУНКЦИИ (RU + UA)
• XI. ТРИГОНОМЕТРИЧЕСКИЕ УРАВНЕНИЯ И НЕРАВЕНСТВА (RU + UA)
• XII. ПЛАНИМЕТРИЯ (1) (RU + UA)
• XIII. ПЛАНИМЕТРИЯ (площади фигур) (RU + UA)
• XIV. СТЕРЕОМЕТРИЯ (1) (RU + UA)
• XV. СТЕРЕОМЕТРИЯ (2) (RU + UA)
• XVI. КОМБИНАТОРИКА (RU + UA)
• XVII. ЧИСЛОВЫЕ ПОСЛЕДОВАТЕЛЬНОСТИ (RU + UA)
• XVIII. ВЕКТОРЫ (RU + UA)
• XIX. ЭЛЕМЕНТЫ ТЕОРИИ ВЕРОЯТНОСТЕЙ И СТАТИСТИКИ (RU + UA)
• XX. ЭЛЕМЕНТЫ ВЫСШЕЙ МАТЕМАТИКИ (RU + UA)
• КИНЕМАТИКА
• ДИНАМИКА
• WATCH YOUR MONEY!

О сайте

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

Каждый урок содержит краткие сведения по теоретической части и три практических задания по 12 примеров или задач в каждом задании. При желании Вы можете написать ответы заданий для проверки в комментариях. Сайт находится в постоянной доработке. Возможны методические и математические ошибки.

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

Щелкните в начальной ячейке диапазона, который находится под номером первого месяца, введите эту формулу

B1 — ячейка суммы, B3 — номер монтирования, B2 — месяц начала, D1 — первое монтирование в диапазоне месяцев.

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

Быстрое разделение нескольких листов на отдельные книги в Excel

Прочие операции (статьи)

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

Как разбить или разбить число на отдельные цифры в Excel?
Предположим, вам нужно разбить номер на отдельные цифры, как показано на скриншоте ниже, что вы можете сделать для этого? Эта статья предоставит вам два метода.

Как извлечь отчество только из полного имени в Excel?
Как мы знаем, у многих людей есть отчество, и мы обычно не используем их отчество. Теперь у вас есть список полных имен в Excel, и все они имеют отчество, и вы хотите извлечь только отчество.

Как быстро разбить ячейку в таблицу в Excel?
Вот несколько ячеек на листе, каждая из которых содержит несколько значений, и теперь я хочу разбить ячейки на диапазон в виде таблицы, как показано ниже. Есть какие-нибудь хитрости для решения этой задачи в Excel?

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

• Как распечатать письмо из outlook
• Программа песни под гитару для компьютера
• Ошибка е782 в программе старт
• Как добавить модуль в visual studio
• Как установить алису без яндекс браузера

Суммирование значений с учетом нескольких условий

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

Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).

Вот формула, с помощью которая можно сопровождать эту формулу:

=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.

=СУММЕСЛИМН(D2:D11,

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

Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:

=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)

Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.

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

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

Что следует ввести

Как пропорционально разделить сумму на другие суммы в Excel

Если вам когда-либо приходилось распределять сумму по времени и разбивать ее на месячные столбцы в зависимости от количества дней, вы быстро поймете, что реализовать эту простую идею сложно. Как и во всем в 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. Взгляните на скриншот ниже.

Наша существующая формула, которая вычисляет сегмент 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%
Любые идеи.

Удачи
Я не претендую на то, чтобы знать, что я делаю, однако, как и вы, я тоже с чего-то начинал.
Один день, одна проблема за раз.
Если вы считаете, что я вам помог, нажмите на звездочку слева от сообщения [Добавить репутацию]
Также. добавьте комментарий, если хотите.
И помните. Отметить тему как решенную.
Форум 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 и спросите его/ее, какой из них лучше всего отражает его/ее ожидания.

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

На приведенном выше рисунке показаны значения в столбце A, и они равномерно распределены по 9 столбцам, от столбцов D до M.

Теперь скопируйте ячейку D5 и вставьте в ячейки ниже и справа.

Объяснение формулы в ячейке D5

Шаг 1. Отслеживание списков

Функция СТОЛБЦЫ работает так же, как функция СТРОКИ, за исключением того, что она подсчитывает столбцы, а не строки в ссылке на ячейку.

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

и возвращает ИСТИНА. Это заставляет формулу возвращать пустые ячейки, когда количество списков превышает значение в ячейке E2.

Шаг 2. Возвращает пустые значения, если заполнены все списки

Функция ЕСЛИ позволяет указать значение, если логическое выражение возвращает ИСТИНА (аргумент 2), и другое значение, если ЛОЖЬ (аргумент 3).

Шаг 3. Возврат номера на основе расширяющейся ссылки на ячейку

Функция СТРОКИ возвращает количество строк в ссылке на ячейку, эта ссылка на ячейку особенная. Он расширяется по мере того, как формула копируется в ячейки ниже.

Шаг 4. Умножение с ячейкой E2

Шаг 5. Вычтите $E$2 из столбцов

Шаг 6. Вычисление номера строки

Шаг 6. Получение значения на основе номера строки

Функция ИНДЕКС возвращает значение в диапазоне ячеек на основе номера строки и столбца. Это ссылка на ячейку одного столбца, поэтому в этом случае номер столбца не требуется.

и возвращает «ГМ» в ячейке D5.

Получить файл Excel

разделить значения на группы с помощью excel-formulav3.xlsx

Пользовательская функция

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

На анимированном изображении выше показан диапазон ячеек с 5 столбцами.

В этом посте показано, как классифицировать значения по рабочим листам:

и в этом посте показано, как классифицировать значения по столбцам на основе условия:

Как ввести формулу массива

1. Выберите диапазон ячеек C2:G11.
2. Вставьте приведенную выше формулу массива в строку формул.
3. Одновременно нажмите и удерживайте клавиши CTRL и SHIFT.
4. Нажмите Enter один раз

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

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

Как скопировать код в книгу?

Обратите внимание: убедитесь, что вы сохранили свою книгу как файл *.xlsm, иначе ваша книга потеряет весь свой код vba в следующий раз, когда вы ее закроете.

Получить файл Excel

Еженедельный блог 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)

Если это не то, что вы хотели, опишите проблему более подробно.

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

• Программа синтезатора Casio для ПК
• ПО для синхронизации Lumia
• Открыть файл djvu онлайн
• Не удается удалить программу установки msi
• Как отменить сохранение в Word