Как условно отформатировать несмежные ячейки в Microsoft Excel на основе второго по величине значения
Я готовлю оценочную карточку для викторины, в которой будут участвовать 6 команд. Часть которого выглядит следующим образом:
Ячейка с наивысшим баллом выделена зеленым цветом. Я добился этого, используя встроенную формулу в разделе «Условное форматирование» для ячеек D4, D10, D16, D22, D28 и D34. Больше всего мне хотелось бы выделить также второй и третий наивысшие баллы. Я столкнулся с большими трудностями в достижении этого из-за несмежности ячеек.
Я много раз пробовал использовать функцию LARGE, но она отказывается принимать массив в качестве входных данных и вместо этого ожидает диапазон.
Думаю, здесь мне следует упомянуть, что я использую Microsoft® Excel для Mac по подписке 365. Моя машина работает под управлением macOS Sonoma. В своих неудачных попытках я подозревал, что LARGE обычно принимает массивы в качестве входных данных на машинах с Windows.
Еще один момент, который следует подчеркнуть: я не хочу отбрасывать повторяющиеся значения в упомянутых ячейках. У меня есть правило тай-брейка в викторине, если несколько общих баллов становятся равными.
В случае равенства следует отформатировать все связанные ячейки с одинаковым форматированием. Моя основная попытка: выберите упомянутые ячейки -> Условное форматирование -> Новое правило -> Используйте формулу, чтобы определить, какие ячейки форматировать -> =D4=large({D4;D10;D16;D22;D28;D34},2). При этом появляется сообщение «Проблема с формулой».
Следуя по тому же пути, «Используйте формулу, чтобы определить, какие ячейки форматировать» -> ваша формула выдает `Вы не можете использовать ссылочные операторы (такие как объединения, пересечения и диапазоны), константы массива или функцию LAMBDA для критериев условного форматирования. .
В отдельной ячейке ваша формула выдает результат «ЛОЖЬ».
=D4=large(vstack(D4,D10,D16,D22,D28,D34),2) ?
Пока не повезло. Если для всех значений установлено значение 0, форматируются все ячейки, однако с нетривиальными значениями это не работает!
Но ты сказал, что связи должны подчеркивать все
Нужно ли нам отображать каждый ранг разным цветом?
@P.b, пожалуйста, дайте мне знать, если мой ответ нуждается в исправлении или улучшении. Спасибо.
@P.b Да, но он делает это только со значениями, установленными на 0. Со значениями 15, 10, 10, 5, 0, 0 он ничего не подсвечивает.
Ответы 2
Если среди значений есть 0, похоже, что и RANK.EQ, и LARGE рассматривают пустые ячейки как содержащие 0.
Итак, не могли бы вы попробовать
- Выберите диапазон, в примере D1:D20.
- Мы будем искать по строкам с помощью
INDEX(RANK.EQ($D$1:$D$20,$D$1:$D$20),ROW(D1)-ROW($D$1)+1)
отрегулируйте$D$1в соответствии с первой ячейкой диапазона - Для проверки рейтинга вы можете использовать следующее, например, в
J1=RANK.EQ($D$1:$D$20,$D$1:$D$20)
как видно на скриншоте. - Вы можете видеть, что ячейкам с нулями и пустым ячейкам присвоен одинаковый ранг; хотя пустым ячейкам присваивается тот же ранг, что и ячейкам с нулем, на последующий ранжирование это не влияет.
- Выделив D1, очистите условное форматирование и установите его в качестве нового правила.
Для каждого ранга разный цвет,
=IF(LEN(TRIM($D1)),INDEX(RANK.EQ($D$1:$D$20,$D$1:$D$20),ROW(D1)-ROW($D$1)+1) = <1, 2 or 3>)
Спасибо за ваши усилия. Однако он учитывает положительные и отрицательные значения, а не 0. Прежде всего, позвольте мне упомянуть о небольшом несоответствии в ваших формулах между текстовым и графическим представлениями. В тексте вы написали IF(...$E$20) <= 3), однако на втором изображении упоминается IF(...$E$20) = 3). В любом случае, я попробовал оба из них, получив одинаковые неполные результаты. Со значениями 10, 10, -5, -5, -10, -10; он правильно выделяет 4 ячейки зеленым и красным цветом. Тем не менее, для набора 10, 10, 0, -5, -10, -10; он останавливается только на двух зеленых клетках.
У меня это сработало (просто набрал 10,10,0,-5,-10,-10, а не всю таблицу, и установил три правила)
@SubhajitPaul, ты прав насчет того, что результат неверен с 0s. Пожалуйста, дайте мне знать, если обновленный ответ работает.
Спасибо @TomSharpe за тестирование. Я не знал, как RANK.EQ и LARGE работают с диапазоном, содержащим как нули, так и пробелы.
Условное форматирование несмежных ячеек в соответствии с рангами
Выберите
D4:D34и добавьте следующие 3 условия:=IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=1) =IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=2) =IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=3)Что с
ISNUMBER()?Он заботится о том, чтобы не форматировать все ненужные ячейки, когда
0входит в тройку лучших.
- На снимке экрана показан способ построения и проверки формул. В ячейках
E4:G4записаны те же 3 формулы. - Вы можете скопировать/вставить все это по горизонтали (вправо или влево), поскольку заблокированы только строки («$»).
- Поскольку вы не можете избежать блокировки строк, для копирования по вертикали (вниз или вверх) вам необходимо скопировать/вставить вправо, затем вырезать/вставить вниз или вверх и скопировать горизонтально по своему усмотрению.
Могут ли быть связи? Как себя вести тогда? Что вы пробовали? Непроверено:
=let(n,{4;10;16;22},d,index(D:D,n),XLOOKUP(LARGE(d,2),d,n)=row())