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

Question

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

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

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

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

0

worksheet-function microsoft-excel-2016 conditional-formatting cell-format

Источник

Aaron Hooper 21 июн '18 в 20:36

2 ответа

Другие вопросы по тегам worksheet-function microsoft-excel-2016 conditional-formatting cell-format

robinCTS 22 июн '18 в 17:42 2018-06-22 17:42 · Answer 1 · 2018-06-22 17:42

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

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

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

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

JRI 22 июн '18 в 00:22 2018-06-22 00:22 · Answer 2 · 2018-06-22 00:22

Поместите свою формулу, =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, этот метод выделил бы только первое.