Kā programmā Excel izcelt statistiskās novirzes

Veicot datu analīzi, jūs parasti pieņemat, ka jūsu vērtības grupējas ap kādu centrālo datu punktu (mediānu). Bet dažreiz dažas vērtības ir pārāk tālu no centrālā punkta. Šīs vērtības sauc par novirzēm (tās atrodas ārpus paredzamā diapazona). Novirzes var sagrozīt jūsu statistiskās analīzes, liekot jums izdarīt nepatiesus vai maldinošus secinājumus par jūsu datiem.

Varat izmantot dažas vienkāršas formulas un nosacījumu formatējumu, lai izceltu nobīdes savos datos.

Pirmais solis, lai identificētu novirzes, ir precīzi noteikt diapazona statistisko centru. Lai to izdarītu, sāciet ar 1. un 3. kvartiles atrašanu. Kvartiles ir statistikas sadalījums no datu kopas četrās vienādās grupās, ar katru grupu veido 25 procentus no datiem. Kolekcijas augšējie 25 procenti tiek uzskatīti par 1. kvartili, savukārt apakšējie 25 procenti tiek uzskatīti par 4. kvartili.

Programmā Excel varat viegli iegūt kvartiles vērtības, izmantojot funkciju QUARTILE. Šai funkcijai ir nepieciešami divi argumenti: datu diapazons un vēlamais kvartiles skaitlis.

Parādītajā piemērā vērtības šūnās E3 un E4 ir 1. un 3. kvartile datiem diapazonā B3:B20.

Ņemot šīs divas kvartiles, varat aprēķināt statistiskos 50 procentus no datu kopas, no 1. kvartiles atņemot 3. kvartili. Šos statistiskos 50 procentus sauc par interkvartiļu diapazonu (IQR). Attēlā 9-18 parādīts IQR šūnā E5.

Tagad jautājums ir, cik tālu no vidējiem 50 procentiem vērtība var atrasties un joprojām tikt uzskatīta par “saprātīgu” vērtību? Statistiķi parasti piekrīt, ka IQR*1.5 var izmantot, lai izveidotu saprātīgu augšējo un apakšējo žogu:

Apakšējais žogs ir vienāds ar 1. kvartili – IQR*1,5.

Augšējais žogs ir vienāds ar 3. kvartili + IQR*1,5.

Kā redzat, šūnas E7 un E8 aprēķina galīgo augšējo un apakšējo žogu. Jebkura vērtība, kas ir lielāka par augšējo žogu vai mazāka par apakšējo žogu, tiek uzskatīta par novirzi.

Šobrīd nosacījumformatēšanas kārtulu ir viegli ieviest.

Lai izveidotu šo pamata formatēšanas noteikumu, rīkojieties šādi:

Atlasiet datu šūnas savā mērķa diapazonā (šajā piemērā šūnas B3:B20), noklikšķiniet uz Excel lentes cilnes Sākums un pēc tam atlasiet Nosacītā formatēšana → Jauns noteikums.

Tiek atvērts dialoglodziņš Jauns formatēšanas kārtula.

Saraksta lodziņā dialoglodziņa augšdaļā noklikšķiniet uz opcijas Izmantot formulu, lai noteiktu, kuras šūnas formatēt.

Šī atlase novērtē vērtības, pamatojoties uz jūsu norādīto formulu. Ja noteikta vērtība tiek novērtēta kā TRUE, šai šūnai tiek piemērots nosacījumformatējums.

Formulas ievades lodziņā ievadiet šeit parādīto formulu.

Ņemiet vērā, ka izmantojat funkciju VAI, lai salīdzinātu vērtību savā mērķa šūnā (B3) ar augšējo un apakšējo žogu, kas atrodas attiecīgi šūnās $E$7 un $E$8. Ja mērķa šūna ir lielāka par augšējo nožogojumu vai mazāka par apakšējo nožogojumu, tā tiek uzskatīta par nobīdi un tādējādi tiks novērtēta kā TRUE, aktivizējot nosacīto formatējumu.

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

Ņemiet vērā, ka formulā jūs izslēdzat absolūtos atsauces dolāru simbolus ($) mērķa šūnai (B3). Ja noklikšķināt uz šūnas B3, nevis ierakstāt šūnas atsauci, programma Excel automātiski padara jūsu šūnas atsauci absolūtu. Ir svarīgi mērķa šūnā neiekļaut absolūtos atsauces dolāru simbolus, jo jums ir nepieciešams Excel, lai lietotu šo formatēšanas kārtulu, pamatojoties uz katras šūnas vērtību.

Noklikšķiniet uz pogas Formatēt.

Tiek atvērts dialoglodziņš Šūnu formatēšana, kurā ir pieejams pilns opciju kopums mērķa šūnas fonta, apmales un aizpildījuma formatēšanai. Kad esat pabeidzis formatēšanas opciju izvēli, noklikšķiniet uz pogas Labi, lai apstiprinātu izmaiņas un atgrieztos dialoglodziņā Jauna formatēšanas kārtula.

Atgriežoties dialoglodziņā Jauna formatēšanas kārtula, noklikšķiniet uz pogas Labi, lai apstiprinātu formatēšanas kārtulu.

Ja jums ir jārediģē nosacījumformatēšanas kārtula, vienkārši novietojiet kursoru jebkurā datu šūnā formatētajā diapazonā un pēc tam dodieties uz cilni Sākums un atlasiet Nosacītā formatēšana → Pārvaldīt kārtulas. Tiek atvērts dialoglodziņš Nosacījuma formatēšanas noteikumu pārvaldnieks. Noklikšķiniet uz kārtulas, kuru vēlaties rediģēt, un pēc tam noklikšķiniet uz pogas Rediģēt kārtulu.