Приветствую читателей нашего канала.
В данной статье мы рассмотрим как быстро можно разбить массив данных на заданные сегменты.
Рассмотрим на примере, в котором надо разбить на сегменты массив данных клиентов по возрасту.
Создаем таблицу, в которой задаем диапазоны возраста клиентов:
Встаем в первую строчку столбца "Кол-во" и нажимаем кнопку "Формулы" (Раздел "Главная"), далее выбираем "Другие функции".
В появившемся меню, в разделе "Категория" выбираем значение "Полный алфавитный перечень".
В разделе "Поиск функции" набираем наименование функции "СЧЕТЕСЛИМН" и нажимаем кнопку "Найти".
Видим, что нужная функция нашлась, нажимаем кнопку "OK".
Появляется окно с аргументами функции.
Встаем в поле "Диапазон_условия1" и выделяем значения столбца "Возраст (Мин.)".
Далее встаем в поле "Условие1" и пишем условие для сегмента: ">="&K6, где:
">=" - это условие больше или равно
& - символ который указывает, что далее будет не значение (например, "0"), а ссылка на ячейку.
K6 - это ячейка из которой мы берем значение диапазона, в данном случае мы нажимаем на ячейку в первой строке, в столбце "Возраст", в подстолбце "Мин.".
Таким образом, мы указали условие для отбора для минимального значения сегмента, теперь укажем диапазон и условия для максимального значения сегмента возраста:
"Диапазон условия2" - выбираем тот же диапазон значений в столбце "Возраст": H5:H19
"Условие2" - указываем значение подстолбца "Макс.", столбца "Возраст": "<="&L6, где:
"<=" - условие меньше или равно
& - символ который указывает, что далее будет не значение (например, "9"), а ссылка на ячейку.
L6 - это ячейка из которой мы берем значение диапазона, в данном случае мы нажимаем на ячейку в первой строке в столбце "Возраст", в подстолбце "Макс.".
Получили значение для первого диапазона возраста: от 0 до 9 лет.
Теперь скопируем формулу по всем строкам столбца "Кол-во", протянув ячейку за правый нижний угол.
Получили следующие значения, просуммировав их получим 12, а в таблице 15 клиентов, значит что-то не так.
Посмотрим какое значение в скопированной ячейке.
Обнаруживаем, что при копировании по строкам, у нас произошел сдвиг диапазона который мы анализируем. Чтобы этого не происходило, перед копированием надо зафиксировать диапазон в столбце "Возраст", поставив перед и после букв, обозначающих столбец, знак "$": $H$5:$H$19. Итоговая формула получается такой: =СЧЁТЕСЛИМН($H$5:$H$19;">="&K6;$H$5:$H$19;"<="&L6).
Теперь можем копировать ячейку с формулой по строкам.
Получаем следующие значения по сегментам:
Проверим общее количество, просуммировав значения в столбце "Кол-во":
Видим, что теперь все корректно.
Теперь заполняем столбец "Доля". Делим кол-во значений по отдельному диапазону (0) на общее кол-во значений (15).
И не забываем зафиксировать ячейку, в которой указана сумма значений по столбцу (т.к. мы эту ячейку будем копировать по строкам), поставив знак "$" перед буквой , обозначающей столбец =M6/$M$.
Копируем ячейку по строкам.
Получаем следующие значения:
Теперь значения в данном столбце надо отформатировать указав %.
Выделяете диапазон значений столбца "Доля" и нажимаете правую клавишу мышки.
Выбираете: формат "Процентный" и "Число десятичных знаков" - 1
Ура, мы получили значения каждого сегмента в %.
Спасибо за внимание!
Будем благодарны Вам за отметку 👍 данной статьи, а также за комментарии 🤏 и оценку🖐.
Предложения по тематике статей приветствуются!
Вам будет интересно:
Сводная таблица? Проще простого!
Условное форматирование (форматирование ячейки в зависимости от её значения).
Отбор и удаление дубликатов в таблице.
Волшебная функция Excel - ВПР.
Excel. А как сделать диаграмму, чтобы на ней были и точечки и столбики?