Okay, I posted this Excel Chart Challenge on Friday. Miten meni? Olen varma, että teidän ratkaisunne on parempi kuin minun. Tämä oli haaste Excel-keskustelupalstan kysymyksestä:

”Miten poistaa poikkeamat kuvaajasta

, kun katselen joitakin graafeja, joskus minun täytyy sivuuttaa joitakin poikkeamia. Haluan tietää, onko mahdollista klikata poikkeavaa tietoa tai vastaavaa x: ää X-akselilla ja kuvaaja päivitetään ilman tätä poikkeavaa tietoa. Tiedän, että voin mennä datajoukkoon ja poistaa poikkeamat, mutta haluan yksinkertaistaa tekemällä sen graafissa. Kiitokset.”

tässä on tämän käyttäjän julkaisema kuva:

kuvaajassa on 2 poikkeavaa datapistettä. 1. Tammikuuta 4. ja 1.Tammikuuta 7. Excel-käyttäjä poisti datapisteet manuaalisesti ja sitten näet lopullisen kaavion kuvan alareunassa.

tarvitaan myös trendiviiva, jossa on trendiviiva kaava ja R-arvo, mutta tämä voi olla manuaalinen vaihe. Tiedot ulottuvat vuoden 2013 tammikuun 1.päivästä tammikuun 10. päivään. Joten antaa mennä nämä oletukset.

Joten miten voimme luoda lopullisen kaavion poistamalla datapisteet 4. ja 7. tammikuuta poistamatta niitä manuaalisesti? Ryhdytään hommiin! Alla on nopea erittely siitä, miten loin ratkaisuni. Alla on yksityiskohtainen askel-askeleelta opetusohjelma tämän Excel-ratkaisu. Alla näet videonäytöksen tästä Excel-vinkistä. Lopuksi, alla, että löydät kopion minun laskentataulukko, jonka voit ladata suunnitella tietoja ja kartoitus tekniikka.

aluksi näytän, miten toinen käyttäjä tarttui tähän ongelmaan. Petellä oli nerokas tapa kiertää tämä ongelma. Hän ottaa datapisteet ja luo kaavan, joka perustuu linjan kaltevuuteen. Tässä on hänen ratkaisunsa omin sanoin:

Peteltä: ”leikittelin sinun Uuden perjantaihaasteesi kanssa, ja keksin toisenlaisen pyörityksen tuloksista. Kaaviossani näkyvät alkuperäiset tiedot, ja uudet tiedot, joissa outlyerit on poistettu. Sinun täytyy avata arkki tarkastella kaavoja.

periaatteessa otin alkuperäisen datan ja käytin KALTEVUUSFUNKTIOTA löytääkseni datan kaltevuuden, ja sitten vertasin sitä x-arvoon perustuvaan teoreettiseen linjaan käyttäen algebrallista kaavaa linjoille Y=mx+B. tällä kaavalla ja lasketulla kaltevuudella voisin ratkaista B: lle.nyt käytin ja jos kaavaa palauttaakseni #N / A-virheen, jos todellinen arvo ja teoreettinen arvo erosivat tiedon validoinnin drop down-solun valitsemalla annetulla prosentilla. Näin käyttäjä voi valita, millä prosentilla hän poistaa outlyerit. Minun oli sisällytettävä 500% ja 100%, jotta käyttäjä voi näyttää alkuperäiset tiedot, koska datapiste ”2” on 710%: n päässä lasketusta teoreettisesta arvosta.”

katso hänen ratkaisunsa täältä: Petes Outlyer-ratkaisu

nyt tässä on perusratkaisuni Excel-rivin luomiseen, jossa ei näy poikkeamia. Tule huomenna katsomaan edistyksellistä ratkaisuani.

The Breakdown

1) Create Chart Data Range

2) Designate a Cell as the Outier Tolerance Level

3) Create Outier Formula for Chart Data Range

4) Create Outlier Chart in Excel

5) Add Trendline to Chart, Trendline Formula and R-squared value

6) smooth the line and clean up chart Junk

step-by-step

1) create chart data range

Okay, lets create our original data range in cells A1:B11. Nyt lets luoda nopea viiva kaavio Excel markkereita nähdä, miltä se näyttää. Näyttää melkein samalta kuin ensimmäisessä kaaviossa, jonka näit alkuperäisestä kysymyksestä.

ainoa ero on se, että linjamme ei ole siloteltu. Viimeinen vaihe tässä opetusohjelmassa näyttää, miten tämä muutos tehdään. Mutta tältä alkuperäinen kaavio näyttäisi Silotelluilla viivoilla:

2 Joten olen määrittänyt solun D3 laskentataulukossa sietokykyni tasoksi. Tarkasteltaessa tietoja, näyttää siltä, että datapiste on nimetty outlier Excel-käyttäjän esimerkissä, kun pisteet eroavat noin 7 yksikköä.

joten olen antanut arvoksi 7 solussa D3:

käytämme solua D3 seuraavassa vaiheessa luodessamme outlier-kaavaa.

3) Luo Outlier kaava Karttatietoalueelle

ensimmäinen, laitetaan päivämäärät soluun E2. Tämä on niinkin yksinkertaista kuin laittaa an =A2 soluun E2.

Okei, tämä on se vaihe, joka saa kaiken toimimaan. Meidän on verrattava nykyistä datapistettä nykyisen pisteen ylä-ja alapuolella oleviin datapisteisiin. Vertailussa nähdään, onko nykyinen datapiste solun D3 toleranssitason ulkopuolella. Pohjimmiltaan aiomme vähentää nykyisen datapisteen datapisteestä yhden solun yläpuolella ja nähdä, onko ero suurempi kuin toleranssi solussa D3. Toista tämä vaihe solulle nykyisen datapisteen alapuolella. Jos molemmat datapisteet ovat toleranssitasoa suurempia, laitetaan nykyiseen soluun NA (). Jos molemmat arvot eivät ole pois toleranssista, niin laitamme nykyisen datapisteen arvon tähän soluun. Nyt koska ero voi olla negatiivinen, meidän täytyy kääriä tämä vähennyslasku itseisarvofunktioon. Joten solussa F2 laitetaan tämä kaava:

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

tässä on jaottelu kaavasta:

ensin aloitamme if-kaavalla, joten mene soluun F2 ja kirjoita tämä:

=Jos (

koska meidän täytyy verrata arvoa ja sitten tuon arvon perusteella laitamme yhden 2 arvosta tähän soluun

nyt kuten edellä kuvasin, sinun täytyy vertailla nykyinen datapiste solun yläpuolella ja solun alla. Ja, jos molemmat vertailut ovat pois toleranssi, niin emme halua näyttää datapiste. Joten, koska olemme tekemisissä” molemmat ” vertailu, meidän pitäisi käyttää ja funktio, joten lets kirjoittaa, että seuraava:

=Jos(ja(

nyt kuten aiemmin totesin, joskus vähentämällä data pistettä tekee lopullinen arvo negatiivinen. Ja negatiivinen arvo on aina toleranssimme alapuolella, joten meidän on varmistettava, että eromme on aina positiivisissa yksiköissä, koska toleranssimme on positiivisissa yksiköissä. Jotta jotain aina positiivista, meidän täytyy kääriä meidän vertailu on Itseisarvofunktio, joten kirjoittakaamme ABS-funktio seuraava:

=Jos(ja(ABS(

nyt lets todella tehdä meidän 1st vertailuarvo. Teemme tämän yksinkertaisesti vähentämällä nykyinen arvo arvo yläpuolella nykyisen datapisteen ja katso, jos se on suurempi kuin meidän toleranssi solussa D3. Kirjoita siis, että:

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

huomaa, että tein D3: n ja absoluuttisen viitteen. Jos et tiedä, mikä absoluuttinen viite on, tutustu tähän viestiin:

viitaten Excel-Kojelautapohjasi kaavojen Vaihteluväleihin

, joka on ensimmäinen osa meidän ja toimintoamme ja funktio on erotettu pilkulla, niin laitat seuraavan vertailun. Joten lets kirjoita seuraava vertailu jälkeen pilkku. Seuraava vertailu on verrata nykyistä datapistettä seuraavaan datapisteeseen. Joten lets kirjoittaa, että:

=Jos(ja(ABS(B2-B1)>$d$3,ABS(B2-B3)>$D$3

koska tämä on viimeinen vertailumme, josta meidän tarvitsee huolehtia, voit lopettaa ja funktion oikeilla sulkeilla. Nyt voimme laittaa pilkun sisään ja päättää, mitä tehdään, jos molemmat kriteerit ja kriteerit pitävät paikkansa. Meidän tapauksessamme, jos molemmat erot datapisteiden ovat suurempia kuin toleranssi, niin meidän täytyy laittaa NA () funktio sinne. Joten antaa kirjoittaa, että jos todellinen arvo:

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

Jos haluat oppia lisää NA ()-funktiosta, Katso tästä postauksesta:

miten piilotat Nollapiirakan kaavion viipale tai pinottu sarakeosa

nyt ollaan valmiita lopulliseen arvoon, jos ja-funktio ei pidä paikkaansa. Laita siis pilkku sisään ja kirjoita IF FALSE-arvo. Tässä tapauksessa, haluamme laittaa todellinen datapiste arvo meidän datasolu B2. Kirjoita siis pilkku ja sitten B2)

=jos(ja(ABS(B2-B1)>$d$3,ABS(B2-B3)>$d$3),NA(),B2)

nyt paina enter ja arvosi solussa F2 pitäisi näyttää tältä:

nyt kun meillä on kaavamme valmiina, voimme kopioida ne viimeiseen datapisteeseemme sarakkeisiin A ja B. kopioi siis tämä kaava alas ja se näyttää tältä:

ilmoitus että datapisteet 1/4 ja 1/7 näyttävät nyt arvoa #N/A. Ja muut arvot ovat samat kuin alkuperäisessä aineistossamme sarakkeessa B. Se on niin ihanaa! Hieno excel-vinkki, jolla pääset eroon trendistämme.

nyt olemme kaikki valmiita rakentamaan Excel-outlier-kaavion.

4) Luo Outlier-kaavio Excelissä

, joten luodaan kaaviomme korostamalla kennoalue kohteesta E1:F11. Siirry sitten Insert-nauhaan ja valitse Viivakaavio Viivapainikkeella:

kaavion pitäisi nyt näyttää tältä:

se näyttää paljon erilaiselta kuin se, jossa on poikkeamia:

5) Lisää Trendline kaavioon, Trendline-kaava ja R-Potenssiarvo

nyt kun meillä on kaaviomme, tarvitsemme vain muutaman viimeisen asian. Olkaamme lisätä trendline meidän kaavio. Voit tehdä tämän, klikkaa kaaviota ja napsauta kaavion linjaa. Sieltä, mene Layout Nauha ja valitse Trendline-painiketta ja valitse Lineaarinen Trendline:

valitse Format Trendline-valintaikkunasta ”lineaarinen” ja ”Näyttöyhtälö kaaviossa” ja ”näytä R-neliöarvo kaaviossa”:

kaaviosi näyttää nyt tältä:

6) silottakaa viivaa ja siivotkaa kaaviota Junk

nyt meidän tarvitsee vain siivota kaavio ja kaikki on valmista! Valitse ensin kaavio ja valitse sitten legenda ja paina Poista-näppäintä. Kaavion pitäisi nyt näyttää tältä:

nyt on luotava tasoitettu viivakaavio. Voit tehdä sen valitsemalla kaavion ja sitten kaavion rivin. Sieltä, paina CTRL + 1 näppäintä esiin Format Series valintaikkuna. Valitse sieltä Rivityylit vasemmasta valikosta ja valitse tasoitettu rivi Rivityylivalinnoista:

lopullinen kaaviosi pitäisi nyt näyttää tältä:

ei paljon eroa, mutta kun tiedoissa on suurempia muutoksia, se näyttää entistä sileämmältä

.

nyt verrataan sitä alkuperäiseen pyyntöön. Se näyttää melkein samalta kuin halusimme. Kuitenkin, näet, että datapisteet tammikuun 3 ja 5th on liittynyt eikä rikki kuin alkuperäinen näyte. Sam datapisteille välillä 1/6 – 1/8. En ole varma, kuinka kriittistä on olla nämä linjat yhteyden, mutta se ei ole mahdollista, miten Excel luo viiva kaavioita, kun sinulla on aukko tietoja, jotka käyttävät kaavaa.

tule kuitenkin huomenna takaisin, missä näytän, miten viivakaavio näyttää aukon kaavojen avulla.

videotutoriaali

Katso tästä Videonäytös Excel-Rivikaavion rakentamisesta, joka jättää poikkeamat huomiotta: