Как определить исходное местоположение ячейки, содержащее информацию о максимуме, которую дает мне формула?

Question

То, что я пытаюсь сделать, это определить максимум B1:B60, B60:B120, B120:B180 и так далее по всему столбцу. Я также хотел бы определить местоположение этих ячеек и (в идеале) сделать ячейку визуально легко идентифицируемой, то есть изменить цвет этой ячейки или текста внутри этих ячеек.

Я уже знаю, как определить значение старших чисел в диапазоне =MAX(INDIRECT("$B"&(61*ROW()-60)):INDIRECT("$B"&(61*ROW()))) но это не говорит мне место, откуда этот номер пришел. Без знания места, откуда взято значение Max, само по себе не очень полезно, так как мне нужно знать информацию о соответствующих строках и столбцах.

Любые идеи или помощь по этому вопросу будет принята с благодарностью.

Answer 1

Вот решение, чтобы выделить соответствующие максимальные значения без использования вспомогательных столбцов. Все, что вам нужно, это условное форматирование:

Создайте новое правило условного форматирования и задайте следующую формулу:

=B1=MAX(INDEX(B:B,QUOTIENT(ROW()-1,60)*60+1):INDEX(B:B,QUOTIENT(ROW()-1,60)*60+60))

Убедитесь, что для параметра Applies to диапазону установлено значение =$B:$E

Answer 2

Поместите формулу =MAX(INDIRECT("$B"&(61*ROW()-60)):INDIRECT("$B"&(61*ROW()))) в ячейку F1 и скопируйте ее как насколько вам нужно, чтобы он отображал максимальное значение для каждой партии из 60 строк.

Чтобы узнать, где находятся максимальные значения, используйте эту формулу:

=MATCH(F1,INDIRECT("$B"&(61*ROW()-60)):INDIRECT("$B"&(61*ROW())),0) + (60*(ROW()-1))

Опять же, скопируйте его так, как вам нужно. Это должно дать вам номер строки каждого максимального значения. Если вы пропустите член + (60*(ROW()-1)) , он даст вам позицию каждого максимума в пакете, а не абсолютное число строк.

Чтобы выделить максимальные значения, примените условный формат со следующей формулой к столбцу B:

=(B1=OFFSET($F$1, QUOTIENT(ROW()-1, 60),0))

Это полагается на то, что максимумы уже рассчитаны в столбце F. Если несколько ячеек в пакете имеют максимальное значение, все они будут выделены.

В качестве альтернативы вы можете использовать формулу условного форматирования, например =(ROW()=OFFSET($G$1, QUOTIENT(ROW()-1, 60),0)) , где столбец G содержит вычисленные номера строк. Если бы в пакете было несколько значений max, этот метод выделил бы только первое.