Strādājot ar datiem programmā Excel, jums bieži rodas problēmas ar datu kopas noviržu apstrādi.
Ārkārtas ir diezgan izplatītas visu veidu datos, un ir svarīgi identificēt un apstrādāt šīs novirzes, lai pārliecinātos, ka jūsu analīze ir pareiza un jēgpilnāka.
Šajā apmācībā es jums parādīšu kā uzzināt izņēmumus programmā Excel, un dažas metodes, kuras esmu izmantojis savā darbā, lai apstrādātu šīs novirzes.
Kas ir izņēmumi un kāpēc ir svarīgi tos atrast?
Ārējais rādītājs ir datu punkts, kas atrodas krietni ārpus citiem datu kopas datu punktiem. Ja jūsu datos ir novirzes, tas var sagrozīt jūsu datus, kas var novest pie nepareiziem secinājumiem.
Ļaujiet man sniegt jums vienkāršu piemēru.
Pieņemsim, ka 30 cilvēki brauc ar autobusu no galamērķa A līdz galamērķim B. Visi cilvēki ir līdzīgā svara grupā un ienākumu grupā. Šajā apmācībā vidējais svars ir 220 mārciņas, bet vidējie gada ienākumi - 70 000 ASV dolāru.
Tagad kaut kur mūsu maršruta vidū autobuss apstājas, un Bils Geitss ielec iekšā.
Tagad, kā jūs domājat, ko tas darītu ar autobusa cilvēku vidējo svaru un vidējiem ienākumiem.
Lai gan vidējais svars, visticamāk, daudz nemainīsies, autobusa cilvēku vidējie ienākumi strauji pieaugs.
Tas ir tāpēc, ka Bila Geitsa ienākumi ir ārpus mūsu grupas, un tas mums sniedz nepareizu datu interpretāciju. Vidējie ienākumi katrai autobusa personai būtu daži miljardi dolāru, kas ir krietni vairāk par faktisko vērtību.
Strādājot ar faktiskajām datu kopām programmā Excel, jums var būt novirzes jebkurā virzienā (t.i., pozitīva novirze vai negatīva novirze).
Un, lai pārliecinātos, ka jūsu analīze ir pareiza, jums kaut kā ir jāidentificē šīs novirzes un pēc tam jāizlemj, kā tās vislabāk apstrādāt.
Tagad aplūkosim dažus veidus, kā programmā Excel atrast izņēmumus.
Atrodiet novirzes, kārtojot datus
Izmantojot nelielas datu kopas, ātrs veids, kā noteikt novirzes, ir vienkārši kārtot datus un manuāli iziet dažas vērtības šo sakārtoto datu augšdaļā.
Tā kā abos virzienos var būt novirzes, vispirms kārtojiet datus augošā secībā un pēc tam dilstošā secībā un pēc tam pārbaudiet augstākās vērtības.
Ļaujiet man parādīt piemēru.
Zemāk man ir datu kopa, kurā man ir zvanu ilgums (sekundēs) 15 klientu apkalpošanas zvaniem.
Tālāk ir norādīti soļi, lai kārtotu šos datus, lai mēs varētu noteikt datu kopas novirzes.
- Atlasiet kārtojamās kolonnas kolonnas galveni (šādā piemērā B1 šūna)
- Noklikšķiniet uz cilnes Sākums
- Rediģēšanas grupā noklikšķiniet uz ikonas Kārtot un filtrēt.
- Noklikšķiniet uz Pielāgota kārtošana
- Dialoglodziņā Kārtot nolaižamajā izvēlnē Kārtot pēc atlasiet “Ilgums” un nolaižamajā secībā “Lielākais līdz mazākais”
- Noklikšķiniet uz Labi
Iepriekš minētās darbības sakārtos zvana ilguma kolonnu ar augstākajām vērtībām augšpusē. Tagad jūs varat manuāli skenēt datus un redzēt, vai ir kādi novirzes.
Mūsu piemērā es redzu, ka pirmās divas vērtības ir daudz augstākas nekā pārējās vērtības (un divas apakšējās ir daudz zemākas).
Piezīme. Šī metode darbojas ar nelielām datu kopām, kurās varat manuāli skenēt datus. Tā nav zinātniska metode, bet darbojas labi
Ārkārtas noteikšana, izmantojot kvartilfunkcijas
Tagad parunāsim par zinātnisku risinājumu, kas var palīdzēt noteikt, vai ir kādi novirzes vai nav.
Statistikā kvartile ir ceturtā daļa no datu kopas. Piemēram, ja jums ir 12 datu punkti, tad pirmā kvartile būtu trīs apakšējie datu punkti, otrā kvartile - nākamie trīs datu punkti utt.
Zemāk ir datu kopa, kurā es vēlos atrast novirzes. Lai to izdarītu, man būs jāaprēķina pirmā un trešā kvartile, un pēc tam, izmantojot to, jāaprēķina augšējā un apakšējā robeža.
Zemāk ir formula, lai aprēķinātu pirmo kvartili šūnā E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
un šeit var aprēķināt trešo kvartili šūnā E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Tagad es varu izmantot divus iepriekšminētos aprēķinus, lai iegūtu starpkvartilitāšu diapazonu (kas ir 50% no mūsu datiem pirmajā un trešajā kvartilē)
= F3-F2
Tagad mēs izmantosim starpkvartilu diapazonu, lai atrastu apakšējo un augšējo robežu, kurā būtu lielākā daļa mūsu datu.
Viss, kas ir ārpus šīm apakšējām un augšējām robežām, tiks uzskatīts par izņēmumu.
Zemāk ir formula, lai aprēķinātu apakšējo robežu:
= Kvartili1 - 1,5*(starpkvartilu diapazons)
kas mūsu piemērā kļūst:
= F2-1,5*F4
Un formula augšējās robežas aprēķināšanai ir šāda:
= Kvartili3 + 1,5*(starpkvartilu diapazons)
kas mūsu piemērā kļūst:
= F3+1,5*F4
Tagad, kad mūsu datu kopā ir augšējā un apakšējā robeža, mēs varam atgriezties pie sākotnējiem datiem un ātri noteikt tās vērtības, kuras neietilpst šajā diapazonā.
Ātrs veids, kā to izdarīt, būtu pārbaudīt katru vērtību un jaunā slejā atgriezt patiesu vai nepatiesu.
Esmu izmantojis zemāk esošo VAI formulu, lai iegūtu TRUE tām vērtībām, kuras ir ārpus vērtības.
= VAI (B2 $ F $ 6)
Tagad varat filtrēt sleju Ārpus vērtības un parādīt tikai tos ierakstus, kuru vērtība ir PATIESA.
Varat arī izmantot nosacījuma formatējumu, lai iezīmētu visas šūnas, kurās vērtība ir TRUE
Piezīme: Lai gan šī ir vairāk pieņemta metode, lai noskaidrotu novirzes statistikā. Es uzskatu, ka šī metode ir mazliet nelietojama reālās dzīves scenārijos. Iepriekš minētajā piemērā zemākā robeža, kas aprēķināta pēc formulas, ir -103, savukārt mūsu rīcībā esošā datu kopa var būt tikai pozitīva. Tātad šī metode var palīdzēt mums atrast novirzes vienā virzienā (augstas vērtības), tā ir bezjēdzīga, nosakot novirzes citā virzienā.Noviržu atrašana, izmantojot LARGE/SMALL funkcijas
Ja strādājat ar daudziem datiem (vērtības vairākās kolonnās), varat iegūt lielākās un mazākās 5 vai 7 vērtības un pārbaudīt, vai tajā nav noviržu.
Ja ir kādi novirzes, jūs varēsit tos identificēt, neizmantojot visus datus abos virzienos.
Pieņemsim, ka mums ir zemāk esošā datu kopa, un mēs vēlamies uzzināt, vai ir kādas novirzes.
Zemāk ir formula, kas sniegs jums lielāko vērtību datu kopā:
= LIELS ($ B $ 2: $ B $ 16,1)
Līdzīgi otro lielāko vērtību piešķirs
= LIELS ($ B $ 2: $ B $ 16,1)
Ja neizmantojat Microsoft 365, kurai ir dinamiski masīvi, varat izmantot zemāk esošo formulu, un tā sniegs jums piecas lielākās datu kopas vērtības ar vienu formulu:
= LIELS ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Līdzīgi, ja vēlaties vismazākās 5 vērtības, izmantojiet zemāk esošo formulu:
= MAZS ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
vai, ja jums nav dinamisku masīvu:
= MAZS ($ B $ 2: $ B $ 16,1)
Kad esat ieguvis šīs vērtības, ir patiešām viegli uzzināt visus datu kopas novirzes.
Lai gan esmu izvēlējies iegūt lielākās un mazākās 5 vērtības, jūs varat izvēlēties iegūt 7 vai 10, pamatojoties uz jūsu datu kopas lielumu.
Es neesmu pārliecināts, vai šī ir pieņemama metode, lai atrastu Excel novirzes vai nē, taču šo metodi es izmantoju, kad pirms dažiem gadiem savā darbā bija jāstrādā ar daudziem finanšu datiem. Salīdzinot ar visām pārējām šajā apmācībā aplūkotajām metodēm, es atklāju, ka šī ir visefektīvākā.
Kā pareizi rīkoties ar novirzēm
Līdz šim mēs esam redzējuši metodes, kas palīdzēs mums atrast mūsu datu kopas novirzes. Bet ko darīt, kad jūs zināt, ka ir novirzes.
Šeit ir dažas metodes, kuras varat izmantot, lai apstrādātu novirzes, lai datu analīze būtu pareiza.
Izdzēsiet izņēmumus
Vienkāršākais veids, kā no datu kopas noņemt novirzes, ir tās vienkārši izdzēst. Tādā veidā tas nekavēs jūsu analīzi.
Tas ir dzīvotspējīgāks risinājums, ja jums ir lielas datu kopas un izdzēšot pāris novirzes neietekmēs vispārējo analīzi. Un, protams, pirms datu dzēšanas noteikti izveidojiet kopiju un izpētiet, kas izraisa šīs novirzes.
Normalizēt novirzes (pielāgot vērtību)
Ārkārtību normalizēšana ir tas, ko es darīju, strādājot pilna laika darbu. Visām izņēmuma vērtībām es vienkārši tos mainītu uz vērtību, kas ir nedaudz augstāka par datu kopas maksimālo vērtību.
Tas nodrošināja, ka es neizdzēšu datus, bet tajā pašā laikā neļauju tiem sagrozīt savus datus.
Lai sniegtu jums reālu piemēru, ja analizējat uzņēmumu tīro peļņas normu, kur lielākā daļa uzņēmumu ir robežās no -10%līdz 30%, un ir dažas vērtības, kas pārsniedz 100%, es vienkārši mainītu šīs izņēmuma vērtības uz 30% vai 35%.
Tātad šīs ir dažas no metodēm, kuras varat izmantot Excel, lai atrastu novirzes.
Kad esat identificējis novirzes, varat iedziļināties datos un noskaidrot, kas tos izraisa, vienlaikus izvēloties kādu no metodēm, kā rīkoties ar šīm novirzēm (kas varētu tās noņemt vai normalizēt, pielāgojot vērtību)
Es ceru, ka šī apmācība jums šķita noderīga.
