Выполнение расчетов с накоплением. Счет

Memory Dump
Выполнение расчетов с накоплением. Счет
Содержимое страницы

Что, если имеется последовательный массив городов, и требуется найти вхождение определенного города по счету? Например, массиву “Москва, Питер, Москва, Питер, Омск” должен соответствовать массив очереди “1, 1, 2, 2, 1”.

Что не так с расчетами

Расчеты с накоплением имеют широкое применение в контроле и оформлении данных в Таблицах. Основной сложностью является борьба с унификацией и упрощением (а чаще додумыванием за пользователя) функций основного назначение. Яркий пример тому, нерегистрозависимый поиск в формуле COUNT, COUNTIF, COUNTIFS и т.д.

Обычная формула не учитывает регистр

ARRAYFORMULA не спасает

COUNTIFS не учитывает регистр

Кажется очевидным, что где-то тут кроется решение. Но на самом деле это не так, т.к. даже комбинируя VLOOKUP, MATCH и QUERY можно попасть в эту ловушку.

Решение в деталях

Формула FIND учитывает регист. А еще точное совпадение можно проверить с помощью EXACT. Второе, правда, требует двух аргументов, и тут кроется очередная проблема универсальности. Временно отложим эту формулу и вернемся к FIND. Как работает FIND, я опишу в какой-нибудь другой статье, а сейчас сразу треш

Поиск количества с учетом регистра FIND

Да, все верно, это более-менее внятная формула для подсчета без ошибок точного вхождения элементов в массиве

=COUNT(ARRAYFORMULA(IFERROR(FIND(A9;$A$2:$A9);)))

Собственно говоря, вот и все решение задачи.

Формулы для диапазонов и настоящих аналитиков

Ясно, что никому и в голову не придет в боевой Таблице использовать формулу, которую поймет каждый. Надо все завернуть в непрекращающийся поток обновляемых и пересчитываемых данных, причиной которым одна, ну, максимум две ячейки.

MMULT, Ё

Если вы еще не знакомы с расчетами через MMULT, то лучше всего начать со статьи “Выполнение расчетов с накоплением. Сумма ”.

Краткие рассуждения

Строим матрицу (ака “массив”) точных совпадений элементов самих в себе

Точное совпадение элементов в массиве

=ARRAYFORMULA(EXACT(A2:A9;TRANSPOSE(A2:A9))^1)

Строим единичную матрицу, соответствующую элементам массива

=ARRAYFORMULA(ROW(A2:A9)^0)

Перемножение матриц дает нам количество вхождений каждого элемента

Регистрозависимый поиск вхождений каждого элемента

=ARRAYFORMULA(MMULT(
  EXACT(A2:A9;TRANSPOSE(A2:A9))^1;
  ROW(A2:A9)^0
))

Очевидным является предположение “урезать” основную матрицу наполовину для “накопительного” эффекта

Регистрозависимый счет с накоплением

=ARRAYFORMULA(IF(A2:A12<>"";MMULT(
  (ROW(A2:A12)>=TRANSPOSE(ROW(A2:A12))) *
    EXACT(A2:A12;TRANSPOSE(A2:A12))^1;
  ROW(A2:A12)^0
);))

Это очень “правильная” формула.

Итоговая формула

Приятным бонусом будет формула нерегистрозависимого счета, когда данные уже нормализованы (или не очень)

Счет с накоплением

=ARRAYFORMULA(IF(A2:A12<>"";MMULT(
  (ROW(A2:A12)>=TRANSPOSE(ROW(A2:A12))) *
    (A2:A12=TRANSPOSE(A2:A12))^1;
  ROW(A2:A12)^0
);))

Ссылки