Резюме
Чтобы создать текущий счетчик в таблице Excel, вы можете использовать функцию ИНДЕКС со структурированной ссылкой для создания расширяющегося диапазона. В показанном примере формула в F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
При копировании вниз по столбцу эта формула вернет текущий счетчик для каждого цвета в столбце «Цвет».
В некоторых версиях Excel это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
По сути, эта формула использует ИНДЕКС для создания такой расширяющейся ссылки:
INDEX((Color),1):(@Color) // expanding range
В левой части двоеточия (:) функция ИНДЕКС возвращает ссылку на первую ячейку в столбце столбца.
INDEX((Color),1) // first cell in color
Это работает, потому что функция ИНДЕКС возвращает ссылку на первую ячейку, а не на фактическое значение. В правой части двоеточия мы получаем ссылку на текущую строку цветового столбца следующим образом:
(@Color) // current row of Color
Это стандартный синтаксис структурированной ссылки для «этой строки». Эти две ссылки, соединенные двоеточием, создают диапазон, который расширяется по мере копирования формулы вниз по таблице. Итак, мы меняем эти ссылки в функцию SUM, у нас есть:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Каждое из приведенных выше выражений генерирует массив значений ИСТИНА / ЛОЖЬ, а двойной отрицательный знак (-) используется для преобразования этих значений в единицы и нули. Итак, в последней строке мы получаем:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Остальная часть формулы просто объединяет цвет из текущей строки со счетчиком, возвращаемым SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Простое расширение ассортимента?
Почему бы не использовать такой простой расширяющийся диапазон?
SUM(--($B$5:B5=(@Color)))
По какой-то причине такая смешанная ссылка повреждается в таблице Excel при добавлении строк. Использование INDEX со структурированной ссылкой решает проблему.
