Отметьте значения, которые слишком сильно отклоняются от среднего для данного идентификатора

Question

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

В следующем примере у меня есть идентификаторы точек с присвоенными значениями.

[Point ID][Number]
 1000      5.321
 1001      3.245 // <----- mark cell or put some indication in next column
 1002      2.647
 1002      2.643
 1001      3.286 // <----- mark cell or put some indication in next column
 1000      5.320

В настоящее время я рассчитываю среднее значение для каждого идентификатора с помощью SUM.IF и COUNT.IF . Но если между значением и средним слишком много различий, я бы хотел знать, не сравнивая каждое из них вручную. Может быть, это возможно с поиском INDEX , но я не уверен, как их сравнить.

Итак, как я могу найти эти значения в левом столбце и заметить, когда значения слишком сильно отличаются от среднего?

Answer 1

Вы можете установить правило условного форматирования, чтобы получить то, что вы описали. Просто выберите значения в своей таблице, которые вы хотите отформатировать (B1:B6 в моем примере, используя ваши примерные данные) и нажмите Conditional Formatting > New Rule... на ленте Home.

Затем выберите « Use a formula to choose which cells to format , и введите приведенную ниже формулу, которая, конечно же, скорректирована с учетом размера и местоположения фактических данных.

=ABS(SUMIF($A$1:$A$6,$A1,$B$1:$B$6)/COUNTIF($A$1:$A$6,$A1)-$B1)>0.01

Последнее число (0,01) - это пороговое отклонение для выделения ячейки. Установите это на то, что вам нравится.

Наконец, установите, как вы хотите, чтобы ячейки были отформатированы.