Таблицы и данные на практике
Средний процент в Excel: учитываем размер групп
Сначала определите, чему дать одинаковый вес: каждой записи или каждой встрече. На одном примере разберём два вида среднего и вычислим общую долю в Excel.
Начните с вопроса
Чтобы узнать, какая доля всех записей закончилась посещением, сложите состоявшиеся посещения и разделите на общее число записей. В нашем вымышленном примере получится 32%. Среднее трёх процентов по встречам — 45%: оно отвечает на другой вопрос.
Представим три мастер-класса. Одна запись означает, что один человек записался на одну встречу; посещение — что он по этой записи пришёл. Человек, записавшийся на две встречи и посетивший обе, даёт две записи и два посещения. Считаем случаи посещения, а не уникальных людей.
Три встречи, два полезных показателя
- A: 8 посещений из 10 записей → 80%.
- B: 9 посещений из 30 записей → 30%.
- C: 15 посещений из 60 записей → 25%.
Объединяем исходные количества
text
Всего посещений = 8 + 9 + 15 = 32
Всего записей = 10 + 30 + 60 = 100
Общая доля посещений = 32 / 100 = 32%
Средняя доля по встречам = (80% + 30% + 25%) / 3 = 45%В этих двух показателях используются разные веса.
Что означает каждый результат?
В показателе 32% каждая запись имеет одинаковый вес. На встречу C приходится 60 из 100 записей, поэтому её влияние на общий результат больше, чем у встречи A. Умножение доли на число записей восстанавливает число посещений: 80% × 10 = 8. Аналогично работают остальные строки.
В показателе 45% каждая встреча имеет одинаковый вес. Это среднее арифметическое трёх долей. Используйте его, если нужен именно такой показатель, и явно его подпишите. При одинаковом числе записей на каждую встречу оба способа дадут один результат.
Перенесите пример в Excel
Вставьте блок с разделителями табуляции в A1 пустого листа. Он должен занять A1:C4. Если всё оказалось в одном столбце, разделите данные по символу табуляции. В D1 напишите «Доля», в D2 введите =B2/C2 и протяните формулу до D4. Задайте D2:D4 процентный формат.
Данные для A1:C4
text
Встреча Посещения Записи
A 8 10
B 9 30
C 15 60Данные придуманы специально для этого примера.
Общая доля из исходных количеств
text
=СУММ(B2:B4)/СУММ(C2:C4)Введите в свободную ячейку и задайте процентный формат: получится 32%.
Тот же результат из долей и их знаменателей
text
=СУММПРОИЗВ(D2:D4;C2:C4)/СУММ(C2:C4)В D2:D4 должны оставаться формулы без округления. Средневзвешенная доля — 32%.
Названия функций и процентный формат
В англоязычном Excel соответствующие формулы: =SUM(B2:B4)/SUM(C2:C4) и =SUMPRODUCT(D2:D4,C2:C4)/SUM(C2:C4). Разделитель аргументов зависит от региональных настроек; здесь для русской версии показана точка с запятой, для английской — запятая.
Число 0,32 при процентном формате отображается как 32%. Дополнительно умножать результат на 100 не нужно. В справке Microsoft по ссылкам ниже описаны процентный формат и формула со СУММПРОИЗВ и СУММ.
Что проверить перед расчётом
- Сохранены ли знаменатели? По одним процентам обычно нельзя восстановить общую долю. Не считайте группы одинаковыми по размеру без основания.
- Есть ли исходные количества? Восстановление посещений из округлённых процентов может изменить ответ. Округлять отображение можно, если в ячейках сохраняются точные исходные формулы.
- Действительно ли в ячейках числа? Импортированный текст «80%» может выглядеть как число. СУММПРОИЗВ считает нечисловые элементы аргументов-массивов нулями. Диапазоны должны соответствовать друг другу и иметь одинаковый размер.
- При нуле записей доля для встречи не определена: не вычисляйте её как 0/0. Формула по исходным количествам работает, если общий знаменатель положителен и сами количества корректны. Если общий знаменатель равен нулю, укажите «не определено», а не 0%. Пропущенные данные нужно выяснить, а не заменить нулями.
- Одинаково ли определён показатель во всех строках? Здесь это посещения по записям, делённые на записи. Каждую запись учитывайте один раз; не смешивайте регистрации, просмотры страниц и уникальных людей.
Попробуйте на другой паре
Разбор находится в следующем разделе. Задачу можно решить без JavaScript.
Ответы и пояснения
- Примерно 53,3%: Это среднее от 60% и примерно 46,7%: вес каждой встречи одинаков. В вопросе одинаковый вес имеет каждая запись.
- 50%: Верно: (3 + 7) / (5 + 15) = 10 / 20 = 50%.
- 10%: Десять — число состоявшихся посещений. Разделите его на 20 записей и выразите результат в процентах.
Ответ на задачу
Всего у D и E — 10 посещений из 20 записей: 50%. Среднее долей с одинаковым весом каждой встречи — примерно 53,3%. До выбора формулы закончите фразу «процент от всех…» и точно назовите, что считает знаменатель.