Функция ПРОЦЕНТОФ в Excel возвращает процент, который подмножество данных вносит в весь набор данных, избавляя вас от необходимости создавать дополнительные или сложные формулы для получения того же результата.
Синтаксис PERCENTOF
Функция PERCENTOF имеет два аргумента:
=ПРОЦЕНТ(a,b)
в котором
- a (обязательно) — это подмножество данных, которое составляет часть всего набора данных, и
- b (также обязательно) — весь набор данных.
Другими словами, функция PERCENTOF сообщает вам значение подмножества a как процент от общего набора данных b.
Использование PERCENTOF с одним значением
Наиболее простым применением функции PERCENTOF является вычисление процента отдельного значения от общей суммы.
В этом примере, предположим, вас попросили проанализировать июньские показатели 14 магазинов в Англии. В частности, ваша задача — определить вклад каждого магазина в общий объем продаж за этот месяц.
В моем примере я использовал отформатированную таблицу Excel и структурированные ссылки. В результате мои формулы легче анализировать, и если я добавлю больше данных в строку 16 и ниже, новые значения будут автоматически включены в вычисления. Если вы не используете таблицы и структурированные ссылки, вам может потребоваться настроить ваши формулы так, чтобы они содержали абсолютные ссылки, и вам придется обновить ваши формулы, если вы добавите больше строк данных.
Для этого в ячейке E2 введите:
=ПРОЦЕНТ(
Затем щелкните ячейку D2, которая является подмножеством данных, вклад которого в общую сумму вы хотите вычислить. Если вы использовали отформатированную таблицу, это заставит Excel добавить имя столбца в вашу формулу, а символ @ означает, что каждая строка будет рассматриваться индивидуально в результате. После этого добавьте запятую:
=ПРОЦЕНТ([@[Июньские распродажи]],
Наконец, выберите все данные в столбце D, включая подмножество, выбранное на предыдущем шаге, но исключая заголовок столбца, чтобы сообщить Excel, какие ячейки составляют весь набор данных. Это отобразится в вашей формуле как имя столбца в квадратных скобках. Затем закройте исходные круглые скобки.
=PERCENTOF([@[Продажи в июне]],[Июньские распродажи])
При нажатии Enter результат отобразится в виде ряда нулей. Но не волнуйтесь — это происходит потому, что данные в процентном столбце в настоящее время представлены в виде десятичных дробей, а не процентов.
Чтобы изменить эти значения с десятичных дробей на проценты, выберите все затронутые ячейки и щелкните значок «%» в группе «Число» вкладки «Главная» на ленте. Пока вы там, щелкните кнопку «Увеличить десятичную дробь», чтобы добавить десятичные знаки, что поможет вам различать похожие значения.
И вот вам! Отформатированная таблица Excel, содержащая столбец, показывающий вклад каждого магазина в общие показатели продаж.
Использование PERCENTOF с GROUPBY
Хотя PERCENTOF может использоваться сам по себе для отображения индивидуальных процентных вкладов, он был изначально добавлен в Excel для использования с другими функциями. В частности, PERCENTOF отлично работает с GROUPBY для дальнейшего разбиения ваших данных на указанные категории и возврата выходных данных в виде процентов.
Возвращаясь к данным о продажах в магазинах из приведенного выше примера, предположим, что ваша цель — выяснить, какие округа привлекают наибольший процент продаж.
Поскольку GROUPBY создает динамический массив, функция не будет работать в отформатированной таблице Excel. Вот почему вам нужно создать таблицу извлечения данных в области вашей электронной таблицы, которая не отформатирована как таблица.
Для этого в ячейке G2 введите:
=GROUPBY(Продажи[Округ],Продажи[Продажи в июне],PERCENTOF)
в котором
- Продажи[Округ] — это поле в исходной таблице продаж, которое будет определять группировку,
- Продажи[Продажи за июнь] — это поле, содержащее значения, которые будут генерировать проценты, и
- PERCENTOF — это функция, которая преобразует необработанные данные в сравнительные значения.
Функция GROUPBY также позволяет вводить еще пять необязательных полей, таких как заголовки полей, порядок сортировки и массив фильтров, но я не включил их в этот пример, чтобы показать, как использовать ее с PERCENTOF в простейшей форме.
При нажатии Enter Excel сгруппирует продажи по округам (по умолчанию в алфавитном порядке), представив данные в десятичном виде.
Чтобы преобразовать эти десятичные дроби в проценты, выберите цифры и щелкните значок «%» в группе «Число» вкладки «Главная» на ленте. Вы также можете настроить количество десятичных знаков в процентных числах, нажав кнопки «Увеличить десятичную дробь» или «Уменьшить десятичную дробь» в той же группе этой вкладки.
Теперь, даже если ваши данные существенно изменятся, функция GROUPBY отследит эти изменения и соответствующим образом скорректирует таблицу категоризации.
Вам не обязательно останавливаться на достигнутом с вашим PERCENTOF! Например, вы можете встроить эту функцию в PIVOTBY, чтобы разбить ваши данные еще больше, используя более одной переменной, прежде чем отображать вывод в процентах.
