Специальное Excel условное форматирование

Question

У меня есть данные Excel. Имена в строках и данные об именах в колонках:

Name Data1 Data2 Data3 ...
AA     1     7     5   ...
BB     8     5     3   ...
CC     5     9     7   ...
...   ...   ...   ...  ...

Теперь представьте условное форматирование в каждом столбце - выделите 10% лучших значений в каждом столбце. Это простое условное форматирование, что я могу сделать.

Но мой вопрос: как выделить строки, у которых есть, скажем, 3 и более выделенных ячеек путем условного форматирования в столбцах?

Answer 1

За исключением использования VBA, вам нужно будет "Использовать формулу, чтобы определить, какие ячейки форматировать". В этой формуле добавьте каждое условие, найденное в строке, и отформатируйте ячейку, если сумма>= 3.

В следующем примере данные форматируются условно, если они находятся в верхнем значении 20% для столбца:

Нам нужна формула, которая определяет, находятся ли 3 или более точек данных в верхних 20% на столбец.

Если число находится в верхних 20% набора данных, оно находится в 80-м процентиле. (100% - 20% = 80%). Excel имеет PERCENTILE функция, которая как раз то, что нам нужно.

=PERCENTILE(A$1:A$15,0.8) возвращает число 8. Таким образом, любое значение 8 и выше должно быть выделено, что мы видим в примере.

Мы получили бы ИСТИНА или ЛОЖЬ, если бы мы сравнили каждое значение в столбце А с процентилем. Я сделал то же самое для всех столбцов ниже, дав нам сетку ИСТИНА и ЛОЖЬ.

Теперь нам нужно определить, есть ли 3 или более ИСТИНЫ. Это легко сделать, потому что ИСТИНА представлена ​​как 1 в Excel, а ЛОЖЬ - как 0. Таким образом, мы можем добавить ИСТИНЫ и ЛОЖИ в строке:

Объедините всю эту работу в формулу условного формата, и вот что вы получите:

=($A1>=PERCENTILE($A$1:$A$15,0.8))+
 ($B1>=PERCENTILE($B$1:$B$15,0.8))+
 ($C1>=PERCENTILE($C$1:$C$15,0.8))+
 ($D1>=PERCENTILE($D$1:$D$15,0.8))+
 ($E1>=PERCENTILE($E$1:$E$15,0.8)) >= 3

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

Answer 2

Выберите столбцы B:F (здесь предполагаются пять столбцов данных, формуле потребуются дополнительные термины для более чем этого), и в качестве правила формулы условного форматирования применяются:

=IF($B1>=LARGE($B:$B,COUNT($B:$B)/10),1)+IF($C1>=LARGE($C:$C,COUNT($C:$C)/10),1)+IF($D1>=LARGE($D:$D,COUNT($D:$D)/10),1)+IF($E1>=LARGE($E:$E,COUNT($E:$E)/10),1)+IF($F1>=LARGE($F:$F,COUNT($F:$F)/10),1)>2  

с форматированием по вашему выбору (предположительно, это не противоречит таковому для 10% форматирования). Метки столбцов также будут отформатированы, если диапазон не сокращен.