Резюме
Щоб створити поточний підрахунок у таблиці Excel, ви можете використовувати функцію INDEX зі структурованим посиланням для створення розширюваного діапазону. У наведеному прикладі формула в F5 має вигляд:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Під час копіювання в стовпець ця формула поверне поточний рахунок для кожного кольору у стовпці Колір.
У деяких версіях Excel це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
По суті, ця формула використовує INDEX для створення розширюваного посилання, як це:
INDEX((Color),1):(@Color) // expanding range
У лівій частині двокрапки (:) функція INDEX повертає посилання на першу комірку стовпця стовпця.
INDEX((Color),1) // first cell in color
Це працює, оскільки функція INDEX повертає посилання на першу комірку, а не фактичне значення. Праворуч від ободової кишки ми отримуємо посилання на поточний рядок стовпця кольорів таким чином:
(@Color) // current row of Color
Це стандартний структурований довідковий синтаксис для "цього рядка". Ці два посилання, об’єднані з двокрапкою, створюють діапазон, який розширюється під час копіювання формули в таблицю. Отже, ми замінюємо ці посилання на функцію SUM, маємо:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Кожен із наведених вище виразів генерує масив значень TRUE / FALSE, а подвійний від’ємник (-) використовується для перетворення цих значень в 1s і 0s. Отже, в останньому рядку ми закінчуємо:
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 зі структурованим посиланням вирішує проблему.
