Обща формула
=COUNTIFS(INDEX(Table,0,MATCH(name,Table(#Headers),0)),criteria))
Обобщение
За да използвате COUNTIFS с колона с променлива таблица, можете да използвате INDEX и MATCH, за да намерите и извлечете колоната за COUNTIFS. В показания пример формулата в H5 е:
=COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1(#Headers),0)),"x")
Обяснение
Първо, за контекста е важно да се отбележи, че можете да използвате COUNTIFS с обикновена структурирана препратка като тази:
=COUNTIFS(Table1(Swim),"x")
Това е много по-опростена формула, но не можете да я копирате надолу в колона H, защото препратката към колоната няма да се промени.
Следователно примерът на тази страница има за цел да покаже един начин за създаване на формула, която препраща към таблица с променлива препратка към колона.
Работейки отвътре навън, функцията MATCH се използва за намиране на позицията на името на колоната, посочена в колона G:
MATCH(G5,Table1(#Headers),0)
MATCH използва стойността в G5 като стойност за търсене, хедърите в Таблица1 за масив и 0 за типа на съвпадението, за да принуди точно съвпадение. Резултатът за G5 е 2, което влиза в INDEX като номер на колоната:
INDEX(Table1,0,2,0))
Забележете, че номерът на реда е зададен на нула, което кара INDEX да връща цялата колона, което е C5: C13 в този пример.
Тази справка обикновено влиза в COUNTIFS:
=COUNTIFS(C5:C13,"x")
COUNTIFS брои клетки, които съдържат "x", и връща резултата, 5 в този случай.
Когато формулата се копира надолу в колона H, INDEX и MATCH връщат правилната препратка към колона на COUNTIFS на всеки ред.
Алтернатива с INDIRECT
Функцията INDIRECT може също да се използва за настройка на променлива референция на колона като тази:
=COUNTIFS(INDIRECT("Table1("&G5&")"),"x")
Тук структурираната препратка се сглобява като текст и INDIRECT оценява текста като правилна препратка към клетка.
Забележка: INDIRECT е променлива функция и може да причини проблеми с производителността в по-големи или по-сложни работни книги.
