Как да подчертаете статистически отклонения в Excel

Когато извършвате анализ на данни, обикновено приемате, че вашите стойности се групират около някаква централна точка от данни (медиана). Но понякога някои от стойностите падат твърде далеч от централната точка. Тези стойности се наричат ​​извънредни стойности (те лежат извън очаквания диапазон). Отклоненията могат да изкривят вашите статистически анализи, което да ви доведе до неверни или подвеждащи заключения относно вашите данни.

Можете да използвате няколко прости формули и условно форматиране, за да подчертаете отклоненията във вашите данни.

Първата стъпка в идентифицирането на отклонения е да се определи точно статистическия център на диапазона. За да направите това точно определяне, започвате с намирането на 1-ви и 3-ти квартил. А квартил е статистически участък на набор от данни на четири равни групи, като всяка група, представляваща 25 на сто от данните. Горните 25 процента от колекцията се считат за 1-ви квартил, докато долните 25 процента се считат за 4-ти квартил.

В Excel можете лесно да получите квартилни стойности, като използвате функцията QUARTILE. Тази функция изисква два аргумента: диапазон от данни и номер на квартил, който искате.

В показания пример стойностите в клетки E3 и E4 са 1-ви и 3-ти квартил за данните в диапазона B3:B20.

Като вземете тези два квартила, можете да изчислите статистическите 50 процента от набора от данни, като извадите 3-ия квартил от 1-ия квартил. Тези статистически 50 процента се наричат ​​интерквартилен диапазон (IQR). Фигура 9-18 показва IQR в клетка E5.

Сега въпросът е колко далеч от средните 50 процента може да седи стойност и все още да се счита за „разумна“ стойност? Статистиците обикновено са съгласни, че IQR*1.5 може да се използва за установяване на разумна горна и долна ограда:

Долната ограда е равна на 1-ви квартил – IQR*1,5.

Горната ограда е равна на 3-ти квартил + IQR*1,5.

Както можете да видите, клетки E7 и E8 изчисляват крайните горни и долни огради. Всяка стойност, по-голяма от горната ограда или по-малка от долната ограда, се счита за извънредно.

В този момент правилото за условно форматиране е лесно за изпълнение.

За да създадете това основно правило за форматиране, изпълнете следните стъпки:

Изберете клетките с данни във вашия целеви диапазон (клетки B3:B20 в този пример), щракнете върху раздела Начало на лентата на Excel и след това изберете Условно форматиране→Ново правило.

Това отваря диалоговия прозорец Ново правило за форматиране.

В списъчното поле в горната част на диалоговия прозорец щракнете върху опцията Използване на формула за определяне кои клетки да се форматират.

Този избор оценява стойностите въз основа на формула, която сте посочили. Ако определена стойност се оценява на TRUE, условното форматиране се прилага към тази клетка.

В полето за въвеждане на формула въведете формулата, показана тук.

Имайте предвид, че използвате функцията ИЛИ, за да сравните стойността във вашата целева клетка (B3) както с горната, така и с долната граница, намираща се съответно в клетките $E$7 и $E$8. Ако целевата клетка е по-голяма от горната граница или по-малка от долната граница, тя се счита за извънредно и по този начин ще се оцени на TRUE, задействайки условното форматиране.

=ИЛИ(B3<$e$8,b3>$E$7)

Имайте предвид, че във формулата изключвате абсолютните референтни доларови символи ($) за целевата клетка (B3). Ако щракнете върху клетка B3, вместо да пишете препратката към клетка, Excel автоматично прави препратката към клетката ви абсолютна. Важно е да не включвате абсолютните референтни символи на долари във вашата целева клетка, защото имате нужда от Excel, за да приложите това правило за форматиране въз основа на собствената стойност на всяка клетка.

Щракнете върху бутона Форматиране.

Това отваря диалоговия прозорец Форматиране на клетки, където имате пълен набор от опции за форматиране на шрифта, границата и запълването за вашата целева клетка. След като приключите с избора на вашите опции за форматиране, щракнете върху бутона OK, за да потвърдите промените си и да се върнете към диалоговия прозорец Ново правило за форматиране.

Обратно в диалоговия прозорец Ново правило за форматиране щракнете върху бутона OK, за да потвърдите вашето правило за форматиране.

Ако трябва да редактирате вашето правило за условно форматиране, просто поставете курсора си в някоя от клетките с данни във вашия форматиран диапазон и след това отидете в раздела Начало и изберете Условно форматиране→Управление на правилата. Това отваря диалоговия прозорец Мениджър на правилата за условно форматиране. Щракнете върху правилото, което искате да редактирате, след което щракнете върху бутона Редактиране на правило.