Основные статистические метрики для списка в Excel считаются функциями СЧЁТЕСЛИ и СУММ: сколько раз элемент встречается в диапазоне — =СЧЁТЕСЛИ($A$2:$A$7;A2), какой по счёту раз он встречается — =СЧЁТЕСЛИ($A$2:A2;A2), доля числа в общей сумме — =B2/СУММ($B$2:$B$7), доля накопительным итогом — =СУММ($B$2:B2)/СУММ($B$2:$B$7). Те же метрики без формул считают процедуры !SEMTools из меню ИЗВЛЕЧЬ → «Статистические метрики (Посчитать)».
В Excel существует множество инструментов для анализа данных, но не всякий анализ элементов множеств легко произвести.
По этой причине в надстройку !SEMTools для Excel в группу процедур "ИЗВЛЕЧЬ" был добавлен раздел "Статистические метрики (Посчитать)". Эта группа позволяет рассчитывать различные статистические показатели для элементов выбранного диапазона, обеспечивая быстрый анализ данных. Давайте рассмотрим каждый из этих инструментов более подробно.
Количество повторений элемента в диапазоне
Этот инструмент позволяет определить, сколько раз каждый элемент повторяется в выбранном диапазоне. Это полезно для выявления наиболее часто встречающихся значений и анализа распределения данных.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Элемент | Значение | Повторов | Номер вхождения | Доля | Накопл. доля |
| 2 | A | 10 | 3=СЧЁТЕСЛИ($A$2:$A$7;A2) | 1=СЧЁТЕСЛИ($A$2:A2;A2) | 10%=B2/СУММ($B$2:$B$7) | 10%=СУММ($B$2:B2)/СУММ($B$2:$B$7) |
| 3 | B | 20 | 2=СЧЁТЕСЛИ($A$2:$A$7;A3) | 1=СЧЁТЕСЛИ($A$2:A3;A3) | 20%=B3/СУММ($B$2:$B$7) | 30%=СУММ($B$2:B3)/СУММ($B$2:$B$7) |
| 4 | C | 30 | 1=СЧЁТЕСЛИ($A$2:$A$7;A4) | 1=СЧЁТЕСЛИ($A$2:A4;A4) | 30%=B4/СУММ($B$2:$B$7) | 60%=СУММ($B$2:B4)/СУММ($B$2:$B$7) |
| 5 | A | 10 | 3=СЧЁТЕСЛИ($A$2:$A$7;A5) | 2=СЧЁТЕСЛИ($A$2:A5;A5) | 10%=B5/СУММ($B$2:$B$7) | 70%=СУММ($B$2:B5)/СУММ($B$2:$B$7) |
| 6 | B | 20 | 2=СЧЁТЕСЛИ($A$2:$A$7;A6) | 2=СЧЁТЕСЛИ($A$2:A6;A6) | 20%=B6/СУММ($B$2:$B$7) | 90%=СУММ($B$2:B6)/СУММ($B$2:$B$7) |
| 7 | A | 10 | 3=СЧЁТЕСЛИ($A$2:$A$7;A7) | 3=СЧЁТЕСЛИ($A$2:A7;A7) | 10%=B7/СУММ($B$2:$B$7) | 100%=СУММ($B$2:B7)/СУММ($B$2:$B$7) |
Общая сумма значений — 100, поэтому доли легко проверить: 10 — это 10%, 30 — 30%. Ячейкам с долями задан процентный формат.
В английском Excel: =COUNTIF($A$2:$A$7,A2)
Формула количества повторений: диапазон закреплён целиком ($A$2:$A$7), поэтому для каждой строки считаются все вхождения элемента во всём списке.
=СЧЁТЕСЛИ($A$2:$A$7;A2)
Номер встречаемости элемента в списке
Этот инструмент определяет, сколько раз элемент уже встречался выше в диапазоне плюс 1. Это помогает отслеживать порядок появления элементов и их частоту.
Отличие от предыдущей формулы — закреплено только начало диапазона ($A$2:A2), а конец «растёт» вместе со строкой. Поэтому считаются вхождения от начала списка до текущей строки. Номер 1 — первое вхождение, всё больше 1 — повторы: так удобно находить и удалять дубли без смещения.
=СЧЁТЕСЛИ($A$2:A2;A2)
Доля каждого числа в общей сумме
Этот инструмент рассчитывает долю каждого элемента от общей суммы всех чисел в диапазоне. Это полезно для анализа вклада каждого элемента в общую сумму.
=B2/СУММ($B$2:$B$7)
Сумма всех долей всегда равна 100%. Если доли нужно считать по элементу (например, сколько вместе дают все «A»), используйте =СУММЕСЛИ($A$2:$A$7;A2;$B$2:$B$7)/СУММ($B$2:$B$7) — для A это 30%.
Доля от суммы накопительным итогом
Этот инструмент позволяет определить долю каждого элемента от общей суммы с накопительным итогом. Это полезно для анализа изменения вклада элементов по мере их накопления.
=СУММ($B$2:B2)/СУММ($B$2:$B$7)
Последняя строка всегда даёт 100%. Подробнее о нарастающем итоге, варианте для умной таблицы и сводной — в статье доля нарастающим итогом.
ABC-анализ
ABC-анализ помогает классифицировать элементы на три категории (A, B и C) на основе их значимости. Подробнее о методе и процедуре: ABC-анализ в Excel.
Группа "Статистические метрики" постоянно развивается, и в будущем планируется добавление новых инструментов для анализа данных в Excel. Они помогают выявлять ключевые элементы, анализировать их вклад и распределение, а также принимать более обоснованные решения. Использование этих инструментов позволяет существенно повысить эффективность анализа данных и оптимизировать управление ресурсами.
Почему метрики считаются неверно
- Формула не принимается и выдаёт ошибку. В русской версии Excel аргументы разделяются точкой с запятой: СЧЁТЕСЛИ($A$2:$A$7;A2), а не запятой.
- Везде одинаковый результат или 1. Не те знаки $: для количества повторов закрепляется весь диапазон, для номера вхождения — только его начало.
- Доли в сумме не дают 100%. Делитель не закреплён ($B$2:$B$7) или в диапазон попала строка «Итого».
- СЧЁТЕСЛИ считает «A» и «а» одинаковыми. Функция не различает регистр — для точного сравнения используйте СУММПРОИЗВ(--СОВПАД($A$2:$A$7;A2)).
Статистические метрики в Excel — частые вопросы
Как посчитать, сколько раз значение встречается в столбце?
Формулой =СЧЁТЕСЛИ($A$2:$A$100;A2) — она вернёт количество вхождений значения из A2 во всём столбце.
Как пронумеровать повторы значения?
Формулой =СЧЁТЕСЛИ($A$2:A2;A2): первое вхождение получит 1, второе — 2 и так далее.
Как посчитать долю от общей суммы?
=B2/СУММ($B$2:$B$100) и процентный формат ячеек (Ctrl+Shift+5).
Как сделать ABC-анализ?
Посчитать долю накопительным итогом и разбить позиции на группы по порогам 80% и 95% — подробно в статье ABC-анализ в Excel.
Посчитать повторы, номера вхождений, доли и долю накопительным итогом или сделать ABC-анализ любого диапазона можно в пару кликов с !SEMTools — без формул и закреплённых ссылок.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: статистические метрики в Excel
| Метрика | Формула |
|---|---|
| Количество повторений | =СЧЁТЕСЛИ($A$2:$A$7;A2) |
| Номер вхождения | =СЧЁТЕСЛИ($A$2:A2;A2) |
| Доля в общей сумме | =B2/СУММ($B$2:$B$7) |
| Доля накопительным итогом | =СУММ($B$2:B2)/СУММ($B$2:$B$7) |
| ABC-анализ | Процедура !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: ABC-анализ и найти дубликаты.