еще один вопрос относительно косвенной формулы Excel.
У меня есть столбцы E1:J202, заполненные одним из следующих:
- Зеленый
- Янтарь
- Красный
Как можно использовать косвенную формулу для подсчета каждого из вышеперечисленных показателей по отдельности, если заполняется новая строка или столбец?
Примечание: каждый раз, когда я добавляю новый столбец в Excel (например, если я вставляю новый столбец [K]), я хочу, чтобы система подсчитывала зеленые, янтарные и красные ячейки из F1:K202 вместо E1:J202.
решение1
Используйте ИНДЕКС:
=COUNTIFS(INDEX(1:202,1,MATCH(1E+99,1:1)-5):INDEX(1:202,202,MATCH(1E+99,1:1)),"green")
Он найдет последний столбец с истинной датой, а затем создаст диапазон, включающий его и 5 столбцов слева.
Если «дата» на самом деле текст, а не настоящая дата, то используйте это:
=COUNTIFS(INDEX(1:202,1,MATCH("zzz",1:1)-5):INDEX(1:202,202,MATCH("zzz",1:1)),"green")
решение2
извините, я не прочитал вопрос в своем первом ответе.
Попробуйте использовать следующую формулу:
=COUNTIFS(INDIRECT(ADDRESS(1,5)&":"&ADDRESS(202,(COLUMNS($A$1:WZG1)-16221))),"green")
это сломается, если вы добавите столбец слева column Aили справа отcolumn WZF
решение3
Если заполнены все ячейки диапазона, то используйте следующую формулу для получения последней ячейки в заполненном диапазоне:
=ADDRESS(COUNTA(E:E),COUNTA(1:1)-COUNTA(A1:D1)+4)
Если все ячейки в диапазоне A1:D1 также заполнены, формула может быть
=ADDRESS(COUNTA(E:E),COUNTA(1:1))
Используйте этот адрес в вашем КОСВЕННОМ:
=COUNTIFS(INDIRECT("E1:"&ADDRESS(COUNTA(E:E),COUNTA(1:1)-COUNTA(A1:D1)+4)),"Green")
или, если все ячейки в диапазоне A1:D1 заполнены,
=COUNTIFS(INDIRECT("E1:"&ADDRESS(COUNTA(E:E),COUNTA(1:1))),"Green")
Конечно, вы можете использовать любую другую строку и/или столбец для определения последней ячейки.