Функция PERCENTOF и преимущества динамических массивов в Excel
Функция PERCENTOF и динамические массивы — это мощные инструменты Excel, которые кардинально упрощают анализ данных и работу с формулами. В этой статье мы разберём, как использовать PERCENTOF для расчёта доли от общей суммы и как динамические массивы заменяют громоздкие формулы, требующие Ctrl + Shift + Enter.
Для подсчёта пятниц, 13-го, введите целые даты без времени в B1 и B2, где B2≥B1. Обе границы включаются: =СУММ((ДЕНЬ(ПОСЛЕДОВ(B2-B1+1;1;B1))=13)*(ДЕНЬНЕД(ПОСЛЕДОВ(B2-B1+1;1;B1);2)=5)) / =SUM((DAY(SEQUENCE(B2-B1+1,1,B1))=13)*(WEEKDAY(SEQUENCE(B2-B1+1,1,B1),2)=5)). При типе 2 пятница имеет номер 5.
ПРОЦЕНТ / PERCENTOF требует актуальной Microsoft 365 и вычисляет СУММ(часть)/СУММ(целое). Функции разлива ПОСЛЕДОВ и УНИК доступны также в Excel 2021/2024 для Windows/Mac и веб-версии; их нет в Excel 2016/2019. Эти возможности имеют разные требования к версии.
Материал поможет, если вам нужны понятные формулы в Excel, разбор функций на русском и английском и практические приемы для расчетов без ошибок.
Расчёт процента от общей суммы с помощью PERCENTOF
Функция PERCENTOF используется в ГРУПППО / GROUPBY и СВОДПО / PIVOTBY, но её можно использовать и самостоятельно. Синтаксис прост: Русская версия: =ПРОЦЕНТ(часть; целое)English version: =PERCENTOF(часть, целое).
Для доли продажи из C6: =ПРОЦЕНТ(C6;$C$4:$C$9) / =PERCENTOF(C6,$C$4:$C$9). Примените процентный формат. Число 3809 на иллюстрации — пример, результат зависит от данных; при нулевой сумме знаменателя будет ошибка деления на ноль.
Замена Ctrl+Shift+Enter на динамические массивы
Некоторые старые формулы массивов требовали ввода с помощью комбинации клавиш Ctrl + Shift + Enter (CSE). Рассмотрим пример: подсчёт количества пятниц, 13-го, между двумя датами.
- Старая формула: Использовала
СУММПРОИЗВ / SUMPRODUCT,СТРОКА / ROW,ДВССЫЛ / INDIRECTи была громоздкой. СУММПРОИЗВ обрабатывает массивы обычным Enter; сочетание Ctrl+Shift+Enter нужно не всем старым формулам.
- Новая формула с динамическими массивами: Использует функцию
ПОСЛЕДОВ / SEQUENCEдля создания массива дат, что делает формулу нагляднее. Ввод — обычный (Enter).
Получение уникального списка: от сложного к простому
Ещё один яркий пример — извлечение уникальных значений из списка. Раньше для этого требовалась невероятно сложная формула.
Рабочий пример для Excel 2016/2019: введите формулу в D5 через Ctrl+Shift+Enter и копируйте вниз. D4 оставьте пустой; B2:B146 содержит текстовые значения и хотя бы одно непустое, без ошибок и символов * или ?. Формула пропускает пустые ячейки:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$146;ПОИСКПОЗ(0;ЕСЛИ($B$2:$B$146<>"";СЧЁТЕСЛИ($D$4:D4;$B$2:$B$146);1);0));"")=IFERROR(INDEX($B$2:$B$146,MATCH(0,IF($B$2:$B$146<>"",COUNTIF($D$4:D4,$B$2:$B$146),1),0)),"")
С динамическими массивами та же задача решается короткой формулой: Русская версия: =УНИК(ФИЛЬТР(B2:B146;B2:B146<>""))English version: =UNIQUE(FILTER(B2:B146,B2:B146<>"")).
Заключение
Функция PERCENTOF и динамические массивы — это эволюция Excel в сторону простоты и мощности. Они избавляют от необходимости использовать устаревшие и сложные методы, делая анализ данных более интуитивным и эффективным. Освоив эти инструменты, вы значительно повысите свою продуктивность.