Ok, jeg postet Denne Excel-Diagramutfordringen på fredag. Hvordan gikk det? Jeg er sikker på at din løsning er bedre enn min. Dette var utfordringen fra Et Excel Forum spørsmål:

«hvordan eliminere outliers i grafen

når du ser på noen grafer, noen ganger må jeg ignorere noen outliers. Liker å vite om det er mulig å klikke på outlier data eller tilsvarende x på x-aksen og grafen vil bli oppdatert uten denne outlier data. Jeg vet at jeg kan gå til datasettet og fjerne outliers, men vil forenkle ved å gjøre det på grafen. Takk.»

Her er bildet som denne brukeren postet:

grafen har 2 avsidesliggende datapunkter. 1. januar 4. og 1.januar 7. Excel-brukeren slettet datapunktene manuelt, og da vil du se det endelige diagrammet nederst på bildet.

Vi må også legge til en trendlinje med en trendlinjeformel og r-verdi, men dette kan være et manuell trinn. Dataene går fra 1. januar til 10. januar i 2013. Så la oss gå med disse antagelsene.

så hvordan kan vi lage det endelige diagrammet ved å fjerne datapunktene fra 4. januar og 7. januar uten å manuelt slette disse datapunktene? Kan få til det! Nedenfor finner du en rask oversikt over hvordan jeg opprettet løsningen min. Nedenfor er en detaljert trinnvis opplæring av Denne Excel-løsningen. Nedenfor vil du se En video demonstrasjon av Denne Excel tips. Til slutt, under det finner du en kopi av regnearket mitt som du kan laste ned for å planlegge rundt med data-og kartleggingsteknikken.

Først av, la meg vise deg hvordan en annen bruker taklet dette problemet. Pete hadde en genial måte rundt dette problemet. Han tar datapunktene og lager en formel basert på linjens helling. Her er hans løsning i sine egne ord:

Fra Pete: «jeg spilte rundt med din nye fredagsutfordring, og jeg kom opp med en annen vri på resultatene . Diagrammet mitt viser de opprinnelige dataene, og de nye dataene med outlyers fjernet. Du må låse opp arket for å vise formlene.I Utgangspunktet tok jeg de opprinnelige dataene og brukte HELLINGSFUNKSJONEN til å finne hellingen til dataene, og sammenlignet den med en teoretisk linje basert På x-verdien ved hjelp Av algebraisk formel FOR linjer Y=MX + B. med denne formelen og den beregnede hellingen kunne jeg løse For B. nå brukte jeg og hvis formel for å returnere en # I / A-feil hvis den virkelige verdien og teoretiske verdien varierte med en gitt % valgt av datavaliderings-rullegardincellen. På denne måten kan brukeren velge på hvilken prosent som å fjerne outlyers. Jeg måtte inkludere 500% og 100% for å tillate brukeren å vise de opprinnelige dataene da datapunktet » 2 » er 710% unna den beregnede teoretiske verdien.»

Sjekk ut løsningen hans her: Petes Outlyer Solution

Nå er min grunnleggende løsning for å lage En Excel-Linje som ikke viser Avvik. Kom tilbake i morgen for å se min avanserte løsning.

Nedbrytingen

1) Lag Diagramdataområde

2) Angi En Celle som Outlier Toleransenivå

3) Lag Outlier Formel for Diagramdataområde

4) Lag Outlier Diagram I Excel

5) Legg Til Trendlinje I Diagram, Trendlinjeformel og R-squared value

6) glatt linjen og rydde opp diagram junk

trinn-For-Trinn

1) lag diagram data range

Ok, La Oss Lage vårt opprinnelige dataområde i celler a1:b11. Nå kan du lage et hurtiglinjediagram I Excel med markører for å se hvordan det ser ut. Ser nesten ut som det første diagrammet du så fra det opprinnelige spørsmålet.

eneste forskjellen er at vår linje ikke er glattet. Det siste trinnet i denne opplæringen vil vise deg hvordan du gjør denne endringen. Men her er hva det opprinnelige diagrammet ville se ut Med Glatte Linjer:

2) Angi En Celle Som Outlier Toleransenivå

Ok, min løsning bruker en regnearkcelle for å angi hva toleransen er for en outlier. Så jeg har utpekt celle D3 I regnearket som toleransenivå. Når man ser på dataene, ser det ut til at et datapunkt er betegnet som en outlier i Excel-brukerens eksempel når punktene er forskjellige om 7-enheter.

Så jeg har satt inn en verdi på 7 I celle D3:

Vi vil bruke celle D3 i neste trinn når vi lager vår outlier formel.

3) Lag Outlier Formel For Diagram Data Range

Først, kan sette våre datoer i celle E2. Dette er så enkelt som å sette en =a2 i celle E2.

Ok, dette er trinnet som gjør at alt fungerer. Det vi trenger å gjøre er å sammenligne dagens datapunkt med datapunktene over og under den nåværende. Sammenligningen vil se om gjeldende datapunkt er utenfor toleransenivået vi angir I celle D3. I hovedsak skal vi trekke det nåværende datapunktet fra datapunktet en celle over og se om forskjellen er større enn toleransen I celle D3. Gjenta deretter dette trinnet for cellen under gjeldende datapunkt. Hvis begge datapunktene er storre enn toleransenivået, vil VI sette EN NA () i den aktuelle cellen. Hvis begge verdiene ikke er ute av toleranse, vil vi sette verdien av gjeldende datapunkt i denne cellen. Nå fordi forskjellen kan være negativ, må vi pakke denne subtraksjonen i en absolutt verdifunksjon. Så i celle F2 la oss sette denne formelen:

=HVIS(OG(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

HER er en sammenbrudd av formelen:

først Skal VI Starte med en if-formel, så gå til celle f2 og skriv inn dette:

=hvis(

fordi vi må sammenligne en verdi og deretter basert på den verdien vil vi sette en av 2 verdier i denne cellen

nå som jeg beskrev ovenfor, må du sammenligne verdien.gjeldende datapunkt med cellen over OG CELLEN NEDENFOR. Og hvis begge sammenligningene er ute av toleranse, vil vi ikke vise datapunktet. Så, siden vi har å gjøre med en» BEGGE » sammenligning, bør VI bruke OG-funksjonen, så la oss skrive det i neste:

=HVIS(OG(

Nå Som jeg nevnte tidligere, vil noen ganger trekke datapunktene Den endelige verdien Negativ. Og en negativ verdi vil alltid være under vår toleranse, så vi må sørge for at forskjellen vår alltid er i positive enheter siden toleransenivået vårt er i positive enheter. For å gjøre noe alltid positivt, må vi pakke vår sammenligning I En Absoluttverdifunksjon, så la oss skrive INN ABS-funksjonen neste:

=HVIS (OG (ABS (

nå kan vi faktisk gjøre vår 1.sammenligningsverdi. Vi gjør dette ganske enkelt ved å trekke den nåværende verdien med verdien over gjeldende datapunkt og se om den er større enn vårt toleransenivå I celle D3. Så skriv det inn:

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

Merk at Jeg gjorde D3 og absolutt referanse. Hvis du ikke vet Hva En Absolutt Referanse er, sjekk ut dette innlegget:

Refererer Til Områder i Formler for Excel-Dashbordmaler

Det er den første delen av VÅR og funksjon og og-funksjonen er skilt av et komma, så vil du sette neste sammenligning. Så kan du skrive inn neste sammenligning etter et komma. Den neste sammenligningen er å sammenligne vårt nåværende datapunkt med det neste datapunktet. Så kan skrive det inn:

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

siden det er vår siste sammenligning som vi trenger å bekymre OSS for, kan DU avslutte OG-funksjonen med en riktig parentes. Nå kan vi sette et komma inn og bestemme hva du skal gjøre hvis BEGGE OG kriteriene er sanne. I vårt tilfelle, hvis begge forskjellene i datapunktene er større enn toleransen, må vi sette EN NA () – funksjon der inne. Så la oss skrive inn DET HVIS SANN verdi:

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

hvis du vil lære mer OM NA ()-funksjonen, sjekk ut dette innlegget:

Hvordan Skjule En Nullpai diagram slice eller stablet kolonne diagram seksjon

Nå Er Vi Klare for den endelige verdien hvis og funksjonen ikke er sant. Så legg inn et komma og skriv INN HVIS FALSK verdi. I dette tilfellet ønsker vi å sette inn den faktiske datapunktverdien til vår datacelle B2. Så skriv inn et komma og Deretter B2)

=HVIS(OG(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

trykk nå enter og verdien I Celle F2 skal se slik ut:

Nå som vi har våre formler på plass, kan vi kopiere dem ned til vårt siste datapunkt i kolonne a og b. så kopier DENNE FORMELEN NED Og DET Vil Se Slik Ut:

poeng for 1/4 og 1/7 viser nå en verdi på #n / A. Og de andre verdiene er de samme som du ser i vårt opprinnelige datasett I Kolonne B. Det er SÅ FANTASTISK! Hva en kul excel tips for å bli kvitt uteliggere fra vår trend.

Nå er vi alle satt til å bygge Vår Excel outlier diagram.4) Lag Outlier Diagram I Excel

Så kan vi lage vårt diagram ved å markere celleområdet Fra E1: F11. Deretter går Du Til Sett Inn Båndet og velger Et Linjediagram Med Markører fra Linjeknappen:

diagrammet ditt skal nå se slik ut:

Det ser mye annerledes ut enn den med outliers:

5) Legg Til Trendlinje Til Diagram, Trendlinjeformel Og R-Kvadrert Verdi

Nå som vi har vårt diagram, trenger vi bare noen få siste ting. La oss legge til en trendlinje i diagrammet vårt. For å gjøre dette, klikk på diagrammet og klikk deretter på diagramlinjen. Derfra går du til Layoutbåndet og velger Trendlinje-knappen og velger Deretter Lineær Trendlinje:

velg Deretter «Lineær» Og «Vis Ligning på diagram» og «Vis r-kvadrert verdi på diagram» fra Dialogboksen Format Trendlinje:

diagrammet ditt vil nå se slik ut:

6) glatt linjen og rydd opp chart junk

nå Er alt vi Trenger Å Gjøre, Rydde Opp Diagrammet, Og Vi er alle klare! Velg først diagrammet ditt, og velg deretter forklaringen og trykk på slett-tasten. Diagrammet ditt skal nå se slik ut:

nå må Vi lage et glatt linjediagram. For å gjøre det, velg diagrammet og deretter linjen i diagrammet. Derfra trykker DU CTRL+1-tasten for Å få opp Dialogboksen Formater Serie. Derfra velger Du Linjestiler fra venstre meny og velger Glatt Linje fra Linjestilvalgene:

ditt endelige diagram skal nå se slik ut:

Ikke mye forskjell, men når dataene dine har større endringer, vil det se enda jevnere ut

.

nå kan sammenligne den med den opprinnelige forespørselen. Det ser nesten akkurat ut som det vi onsket. Du vil imidlertid se at datapunktene fra 3. januar til 5. er sammenføyet og ikke ødelagt som den opprinnelige prøven. Sam for datapunkter mellom 1/6 og 1/8. Jeg er ikke sikker på hvor viktig det er Å ikke ha disse linjene koble, men Det er ikke mulig med Måten Excel oppretter linjediagrammer når du har et gap med data som bruker en formel.Men kom tilbake i morgen hvor jeg vil vise deg hvordan du lager et linjediagram, vis et gap ved hjelp av formler.

Video Tutorial

Sjekk ut denne videoen demonstrasjon av å bygge En Excel Linjediagram Som Ignorerer Outliers: