bine, am postat această provocare diagramă Excel vineri. Cum te-ai descurcat? Sunt sigur că soluția ta este mai bună decât a mea. Aceasta a fost provocarea de la o întrebare Excel Forum:

„Cum de a elimina valorile aberante în grafic

când vizualizați unele grafice, uneori am nevoie pentru a ignora unele valori aberante. Ca să știu dacă este posibil să faceți clic pe datele outlier sau X corespunzătoare pe axa x și graficul va fi actualizat fără aceste date outlier. Știu că pot să merg la setul de date și să elimin valorile aberante, dar vreau să simplific făcând-o pe grafic. Multe mulțumiri.”

iată imaginea pe care acest utilizator a postat-o:

graficul are 2 puncte de date periferice. 1 pe 4 ianuarie și 1 pe 7 ianuarie. Utilizatorul Excel a șters manual punctele de date și apoi veți vedea graficul final în partea de jos a imaginii.

trebuie să adăugăm și o linie de tendință cu o formulă de linie de tendință și o valoare R, dar acest lucru poate fi un pas manual. Datele merg de la 1 ianuarie la 10 Ianuarie în anul 2013. Deci, să mergem cu aceste ipoteze.

Deci, cum putem crea graficul final prin eliminarea punctelor de date din 4 ianuarie și 7 ianuarie fără a șterge manual aceste puncte de date? Să ajungem la ea! Mai jos veți găsi o defalcare rapidă a modului în care mi-am creat soluția. Mai jos este un tutorial detaliat pas cu pas al acestei soluții Excel. Mai jos veți vedea o demonstrație Video a acestui sfat Excel. În cele din urmă, mai jos veți găsi o copie a foii mele de calcul pe care o puteți descărca pentru a planifica cu tehnica de date și diagrame.în primul rând, permiteți-mi să vă arăt cum un alt utilizator a abordat această problemă. Pete a avut un mod ingenios în jurul acestei probleme. El ia punctele de date și creează o formulă bazată pe panta liniei. Iată soluția sa în propriile sale cuvinte:

de la Pete: „mă jucam cu noua ta provocare de vineri și am venit cu o altă rotire asupra rezultatelor. Graficul meu arată datele originale, iar noile date cu outlyers eliminate. Va trebui să deblocați foaia pentru a vizualiza formulele.

practic, am luat datele originale și am folosit funcția de pantă pentru a găsi panta datelor și apoi am comparat-o cu o linie teoretică bazată pe valoarea X folosind formula algebrică pentru liniile Y=MX+B. Cu această formulă și panta calculată, aș putea rezolva pentru B. acum am folosit și dacă formula pentru a returna o eroare #N / A dacă valoarea reală și valoarea teoretică diferă cu un % dat ales de celula derulantă de validare a datelor. În acest fel, utilizatorul poate alege la ce procent la care să elimine outlyers. A trebuit să includ 500% și 100% pentru a permite utilizatorului să afișeze datele originale, deoarece punctul de date „2” este la 710% distanță de valoarea teoretică calculată.”

Verificați soluția lui aici: Petes Outlyer Solution

acum, aici este soluția mea de bază pentru crearea unei linii Excel care nu afișează valori aberante. Reveniți mâine pentru a vedea soluția mea avansată.

defalcarea

1) Crearea Diagramă interval de date

2) desemnează o celulă ca nivelul de toleranță Outlier

3) Crearea formulă Outlier pentru intervalul de date diagramă

4) Crearea Diagramă Outlier în Excel

5) Adăugați Trendline la Diagramă, Formula Trendline și R-pătrat valoare

6) neteziți linia și curățați junk-ul graficului

pas cu pas

1) Creați intervalul de date grafic

bine, să creăm intervalul nostru original de date în celulele A1:B11. Acum, vă permite să creați o diagramă linie rapidă în Excel cu markeri pentru a vedea cum arată. Arată aproape exact ca prima diagramă pe care ați văzut-o din întrebarea inițială.

singura diferență este că linia noastră nu este netezită. Ultimul pas din acest tutorial vă va arăta cum să faceți această modificare. Dar iată cum ar arăta graficul original cu linii netezite:

2) desemnează o celulă ca nivel de toleranță Outlier

bine, soluția mea folosește o celulă de foaie de calcul pentru a desemna care este toleranța pentru un outlier. Așa că am desemnat celula D3 din foaia de calcul ca nivel de toleranță. Privind datele, se pare că un punct de date este desemnat ca un outlier în exemplul utilizatorului Excel atunci când punctele diferă aproximativ 7 unități.

așa că am pus o valoare de 7 în celula D3:

vom folosi celula D3 în pasul următor când vom crea formula noastră outlier.

3) Creați Formula Outlier pentru intervalul de date diagramă

în primul rând, să punem datele noastre în celula E2. Acest lucru este la fel de simplu ca punerea unui =A2 în celula E2.

bine, acesta este pasul care face ca totul să funcționeze. Ceea ce trebuie să facem este să comparăm punctul de date curent cu punctele de date de deasupra și dedesubtul celui curent. Comparația va vedea dacă punctul de date curent este în afara nivelului de toleranță pe care l-am stabilit în celula D3. În esență, vom scădea punctul de date curent din punctul de date cu o celulă de mai sus și vom vedea dacă diferența este mai mare decât toleranța din celula D3. Apoi repetați acest pas pentru celula de sub punctul de date curent. Dacă ambele puncte de date sunt mai mari decât nivelul de toleranță, atunci vom pune un NA() în celula curentă. Dacă ambele valori nu sunt în afara toleranței, atunci vom pune valoarea punctului de date curent în această celulă. Acum, deoarece diferența ar putea fi negativă, trebuie să înfășurăm această scădere într-o funcție de valoare absolută. Deci, în celula F2 să punem această formulă:

=dacă(și(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

iată o defalcare a formulei:

Mai întâi vom începe cu o formulă if, așa că mergeți la celula F2 și tastați acest lucru:

=if(

pentru că trebuie să comparăm o valoare și apoi pe baza acelei valori vom pune una din cele 2 valori în această celulă

acum, așa cum am descris mai sus, trebuie să comparați punctul de date cu celula de mai sus și celula de mai jos. Și, dacă ambele comparații sunt în afara toleranței, atunci nu vrem să arătăm punctul de date. Deci, din moment ce avem de-a face cu o comparație „ambele”, ar trebui să folosim funcția și, deci să tastăm asta în următorul:

=IF(and(

Acum, Așa cum am spus mai devreme, uneori scăderea punctelor de date va face valoarea finală negativă. Și o valoare negativă va fi întotdeauna sub toleranța noastră, așa că trebuie să ne asigurăm că diferența noastră este întotdeauna în unități pozitive, deoarece nivelul nostru de toleranță este în unități pozitive. Pentru a face ceva întotdeauna pozitiv, trebuie să ne înfășurăm comparația într-o funcție de valoare absolută, așa că haideți să tastăm funcția ABS următoarea:

=IF(AND(ABS(

acum să facem de fapt valoarea noastră de comparație 1st. Facem acest lucru pur și simplu scăzând valoarea curentă cu valoarea deasupra punctului de date curent și vedem dacă este mai mare decât nivelul nostru de toleranță în celula D3. Deci, tastați că în:

=IF(și(ABS(B2-B1)>$d$3

rețineți că am făcut D3 și referință absolută. Dacă nu știți ce este o referință absolută, consultați acest post:

referindu-se la intervalele din formulele pentru șabloanele Tabloului de bord Excel

aceasta este prima parte a funcției noastre AND și funcția and este separată printr-o virgulă, atunci veți pune următoarea comparație. Deci, vă permite să tastați în următoarea comparație după o virgulă. Următoarea comparație este de a compara punctul nostru de date curent cu următorul punct de date. Deci, vă permite să tastați că în:

=IF (and (ABS(B2-B1)>$D$3,ABS (B2-B3)>$d$3

deoarece aceasta este ultima noastră comparație de care trebuie să ne facem griji, puteți încheia și funcționează cu o paranteză dreaptă. Acum putem pune o virgulă și să determinăm ce să facem dacă ambele criterii și criteriile sunt adevărate. În cazul nostru, dacă ambele diferențe ale punctelor de date sunt mai mari decât toleranța, atunci trebuie să punem o funcție NA() acolo. Deci, vă permite să tastați în faptul că, dacă valoarea adevărată:

=IF(and(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

dacă doriți să aflați mai multe despre funcția NA (), consultați acest post:

cum să ascundeți o plăcintă Zero diagramă felie sau coloană stivuite diagramă secțiune

acum suntem gata pentru valoarea finală în cazul în care funcția și nu este adevărat. Deci, puneți o virgulă și introduceți valoarea IF FALSE. În acest caz, dorim să introducem valoarea reală a punctului de date al celulei noastre de date B2. Deci,tastați o virgulă și apoi B2)

=IF(and(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3), NA (), B2)

Acum apăsați enter și valoarea dvs. în celula F2 ar trebui să arate astfel:

acum, că avem formulele noastre în loc, le putem copia până la ultimul nostru punct de date în coloanele A și B. Deci, copiați această formulă în jos și va arăta astfel:

observați că datele punctele pentru 1/4 și 1/7 arată acum o valoare de #N/A. Iar celelalte valori sunt aceleași pe care le vedeți în setul nostru de date originale din coloana B. este atât de minunat! Ce sfat Excel cool pentru a scăpa de valorile aberante din tendința noastră.

acum suntem cu toții pregătiți să construim graficul Excel outlier.

4) Creați Diagramă Outlier în Excel

deci, vă permite să creați graficul nostru prin evidențierea gama de celule de la E1:F11. Apoi mergeți la panglica Insert și selectați o diagramă de linie cu markeri din butonul Line:

diagrama dvs. ar trebui să arate acum astfel:

arată foarte diferit de cel cu valorile aberante:

5) Adăugați Trendline la Diagramă, Formula Trendline și valoarea R-Squared

acum, că avem graficul nostru, avem nevoie doar de câteva ultime lucruri. Să adăugăm o linie de tendință la graficul nostru. Pentru a face acest lucru, faceți clic pe diagramă și apoi faceți clic pe linia de diagramă. De acolo, accesați panglica Layout și alegeți butonul Trendline, apoi alegeți Linear Trendline:

apoi alegeți „Linear” și „display Ecuation on chart” și „Display R-squared value on chart” din caseta de dialog Format Trendline:

diagrama dvs. va arăta acum astfel:

6) neteziți linia și curățați junk-ul graficului

acum tot ce trebuie să facem este să curățăm Graficul și suntem cu toții pregătiți! Mai întâi selectați graficul, apoi selectați legenda și apăsați tasta ștergere. Diagrama dvs. ar trebui să arate acum astfel:

acum trebuie să creăm o diagramă de linie netezită. Pentru a face acest lucru, selectați diagrama și apoi linia din diagramă. De acolo, apăsați tasta CTRL + 1 pentru a afișa caseta de dialog Format Series. De acolo, selectați stilurile de linie din meniul din stânga și alegeți linia netezită din opțiunile de stil de linie:

graficul dvs. final ar trebui să arate acum astfel:

nu prea mare diferență, dar atunci când datele dvs. are modificări mai mari, se va arata chiar mai buna

.

acum să o comparăm cu cererea inițială. Se pare aproape exact ca ceea ce ne-am dorit. Cu toate acestea, veți vedea că punctele de date din 3 până în 5 ianuarie sunt unite și nu sunt rupte ca eșantionul original. Sam pentru puncte de date între 1/6 și 1/8. Nu sunt sigur cât de important este să nu se conecteze aceste linii, dar nu este posibil cu modul în care Excel creează diagrame de linie atunci când aveți un decalaj de date care utilizează o formulă.

cu toate acestea, reveniți mâine unde vă voi arăta cum să faceți o diagramă de linie arată un decalaj folosind formule.

tutorial Video

Check out această demonstrație video de a construi o diagramă linie Excel care ignoră valorile aberante: