Обобщение
За да създадете текущ брой в таблица на 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 със структурирана справка решава проблема.
