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

444
Aaron Hooper

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

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

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

0

2 ответа на вопрос

1
robinCTS

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

Скриншот рабочего листа

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

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

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

Спасибо за помощь! Я пытался создать новое правило в CF, и я получил эту ошибку: «Вы не можете использовать операторы ссылок (такие как пересечения и диапазоны объединений) или константы массива для критериев условного форматирования». Есть идеи, как мне решить эту проблему? Aaron Hooper 6 лет назад 0
@AaronHooper О, черт! Различные версии Excel позволяют / запрещают разные вещи в формулах условного форматирования. Попробуйте создать определенное имя, скажем, `HighlightMax60`, с параметром` Refers to: `, установленным на ту же формулу (` = B1 = MAX (INDEX (B: B, QUOTIENT (ROW () - 1,60) * 60 + 1) ): ИНДЕКС (В: В, QUOTIENT (СТРОКА () - 1,60) * 60 + 60)) `). Убедитесь, что активная ячейка `B1`, когда вы делаете это. Затем используйте `= HighlightMax60` для формулы условного форматирования. robinCTS 6 лет назад 0
0
JRI

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

Похожие вопросы