Oké, pénteken közzétettem ezt az Excel diagram kihívást. Hogy ment? Biztos vagyok benne, hogy a megoldás jobb, mint az enyém. Ez volt a kihívás egy Excel fórum kérdés:

” hogyan lehet kiküszöbölni kiugró gráf

amikor megtekinti néhány grafikonok, néha meg kell figyelmen kívül hagyni néhány kiugró. Tudni szeretnénk, hogy a kiugró adatokra vagy a megfelelő X-re lehet-e kattintani az x tengelyen, a grafikon pedig ezen kiugró adatok nélkül frissül. Tudom, hogy elmehetek az adatkészletbe, eltávolíthatom a kiugró értékeket, de egyszerűsíteni akarok a grafikonon. Nagyon köszönöm.”

itt van a kép, amit a felhasználó közzétett:

a grafikonnak 2 külső adatpontja van. Január 4-én 1, január 7-én 1. Az Excel felhasználó manuálisan törölte az adatpontokat, majd a kép alján megjelenik a végső diagram.

hozzá kell adnunk egy trendvonalat trendvonal-képlettel és R értékkel, de ez lehet manuális lépés is. Az Adatok 2013.január 1-jétől január 10-ig terjednek. Tehát menjünk ezekkel a feltételezésekkel.

tehát hogyan hozhatjuk létre a végleges diagramot az adatpontok január 4-től január 7-ig történő eltávolításával anélkül, hogy manuálisan törölnénk ezeket az adatpontokat? Menjünk oda! Az alábbiakban talál egy gyors bontást arról, hogyan hoztam létre a megoldást. Az alábbiakban részletesen bemutatjuk ezt az Excel megoldást. Az alábbiakban egy videó bemutató látható az Excel tippről. Végül, az alábbiakban talál egy példányt a táblázatkezelő, hogy lehet letölteni, hogy tervezzen körül az adatok, valamint a térképezés technika.

először hadd mutassam meg, hogy egy másik felhasználó hogyan kezelte ezt a problémát. Pete volt egy ötletes módon körül ezt a problémát. Ő veszi az adatpontokat, és létrehoz egy képletet a vonal meredeksége alapján. Itt van a megoldása a saját szavaival:

Pete-től: “az új pénteki kihívásoddal játszottam, és egy másik pörgetést találtam ki az eredményekre. A diagramom az eredeti adatokat mutatja, az új adatokat pedig az outlyers eltávolításával. A képletek megtekintéséhez ki kell nyitnia a lapot.

Tulajdonképpen nem került az eredeti adatokat használt a LEJTŐN funkció, hogy megtalálja a lejtőn az adatokat, majd képest, hogy egy elméleti vonal alapján az X érték használata az algebrai képlete vonalak Y=MX+B. ez A képlet, valamint a számított lejtőn, meg tudom oldani a B. Már régen meg, HA a képlet, hogy visszatérjen a #N/A hiba, ha a valós érték, valamint az elméleti érték eltérő által megadott % által választott az adat-ellenőrzési legördülő cella. Így a felhasználó kiválaszthatja, hogy milyen százalékban távolítsa el az outlyers-t. 500% – ot és 100% – ot kellett beleírnom, hogy a felhasználó az eredeti adatokat megjelenítse, mivel a “2” adatpont 710% – kal van távol a kiszámított elméleti értéktől.”

nézze meg a megoldást itt: Petes Outlyer Solution

most itt van az alapvető megoldás egy olyan Excel sor létrehozásához, amely nem jeleníti meg a Kiugrókat. Jöjjön vissza holnap, hogy megnézze a fejlett megoldásomat.

A Bontás

1) Létrehozása Diagram Adatait Tartomány

2) Kijelöli a Sejt, mint a Kiugró Tolerancia Szint

3) hozzon Létre Kiugró Képlet a Diagram Adatait Tartomány

4) Létrehozása Kiugró Táblázat az Excel

5) Add Trendline, hogy Diagram, Trendline Képlet, R-Négyzet Érték

6) Sima a sort, rendet Diagram Szemét

Step-by-Step

1) Létrehozása Diagram Adatait Tartomány

Oké, lehetővé teszi, hogy hozzon létre az eredeti adatok körét a sejtek A1:B11. Most lehetővé teszi, hogy hozzon létre egy gyors vonaldiagramot az Excel-ben markerekkel, hogy megnézze, hogyan néz ki. Majdnem pontosan úgy néz ki, mint az első diagram, amelyet az eredeti kérdésből láttál.

csak a különbség az, hogy a vonal nem simított. A bemutató utolsó lépése megmutatja, hogyan kell ezt a változást végrehajtani. De itt van, amit az eredeti diagram nézne ki, mint a simított vonalak:

2) Kijelöli a Sejt, mint a Kiugró Tolerancia Szint

Oké, a megoldás használ egy táblázat cellájában, hogy kijelölje, mi a tolerancia egy kiugró. Tehát a táblázatban a D3 cellát a tolerancia szintemnek jelöltem. Az adatokat tekintve úgy tűnik, hogy egy adatpontot az Excel felhasználó példáján kívülinek jelölnek, amikor a pontok körülbelül 7 egységben különböznek egymástól.

tehát 7 értéket tettem be a D3 cellába:

a következő lépésben a D3 cellát fogjuk használni, amikor létrehozzuk a kiugró képletet.

3) Hozzon létre Outlier képletet a diagram adattartományához

először tegye a dátumokat az E2 cellába. Ez olyan egyszerű, mint az an =A2 beillesztése az E2 cellába.

Oké, ez az a lépés, amely mindent működésbe hoz. Ehhez össze kell hasonlítanunk az aktuális adatpontot a jelenlegi adatpont felett és alatt található adatpontokkal. Az összehasonlítás látni fogja, hogy az aktuális adatpont kívül esik-e a D3 cellában beállított toleranciaszinten. Lényegében kivonjuk az aktuális adatpontot a fenti adatpontból, és megnézzük, hogy a különbség nagyobb-e, mint a D3 cellában lévő tolerancia. Ezután ismételje meg ezt a lépést az aktuális adatpont alatti cellához. Ha mindkét adatpont nagyobb, mint a tolerancia szintje, akkor egy NA-t() helyezünk az aktuális cellába. Ha mindkét érték nem tolerálható, akkor az aktuális adatpont értékét ebben a cellában helyezzük el. Most, mert a különbség negatív lehet, ezt a kivonást abszolút értékfüggvénybe kell csomagolni. Tehát az F2 cellában tegyük ezt a képletet:

a=HA(ÉS(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Itt egy részletes leírás a képlet:

Első kezdünk azzal, HA egy képlet, hogy sejt az F2 billentyűt, majd írja be ezt:

a=HA(

mert össze kell hasonlítanunk egy értéket, majd ez alapján érték tesszük az egyik 2 értékek ebben a cella

Most, mint a fent leírt, meg kell összehasonlítani az aktuális adatot a cella felett, valamint a cella alatt. És ha mindkét összehasonlítás nem tolerálható, akkor nem akarjuk megmutatni az adatpontot. Tehát, mivel “mindkettő” összehasonlítással foglalkozunk, használnunk kell az AND függvényt, tehát írja be ezt a következőbe:

=Ha(és(

most, ahogy korábban említettem, néha az adatpontok kivonása negatívvá teszi a végső értéket. A negatív érték mindig a tolerancia alatt lesz, ezért meg kell győződnünk arról, hogy különbségünk mindig pozitív egységekben van, mivel toleranciaszintünk pozitív egységekben van. Ahhoz, hogy valami mindig pozitív legyen, az összehasonlításunkat abszolút Értékfüggvénybe kell csomagolnunk, ezért írja be az ABS függvényt következő:

=IF(and(ABS(

most lehetővé teszi az 1.összehasonlító értékünk tényleges elkészítését. Ezt egyszerűen úgy tesszük, hogy kivonjuk az aktuális értéket az aktuális adatpont feletti értékkel, és megnézzük, hogy nagyobb-e, mint a D3 cellában lévő toleranciaszintünk. Tehát írja be:

=IF(és(ABS(B2-B1)>$D$3

vegye figyelembe, hogy a D3-at és abszolút hivatkozást készítettem. Ha nem tudod, mi az abszolút referencia, nézd meg ezt a bejegyzést:

utalva tartományok képletekben az Excel műszerfal sablonok

Ez az első része a mi és a funkció elválasztjuk egy vesszővel, akkor tegye a következő összehasonlítás. Tehát lehetővé teszi, hogy vessző után írja be a következő összehasonlítást. A következő összehasonlítás a jelenlegi adatpontunk összehasonlítása a következő adatponttal. Így lehetővé teszi, hogy írja be:

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

mivel ez az utolsó összehasonlításunk, amely miatt aggódnunk kell, jobb zárójelben fejezheti be a funkciót. Most egy vesszőt helyezhetünk el, és meghatározhatjuk, hogy mi a teendő, ha mind a kritériumok, mind a kritériumok igazak. Esetünkben, ha mindkét adatpont különbsége nagyobb, mint a tolerancia, akkor be kell helyeznünk egy NA() funkciót. Így írja be, hogy ha igaz érték:

=Ha(és(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

Ha többet szeretne megtudni a NA() funkcióról, nézd meg ezt a bejegyzést:

hogyan-elrejteni egy nulla pite diagram szelet vagy halmozott oszlop diagram szakasz

most készen állunk a végső értékre, ha az AND funkció nem igaz. Tehát tegyen egy vesszőt, majd írja be az IF FALSE értéket. Ebben az esetben a B2 adatcellánk tényleges adatpontértékét szeretnénk beilleszteni. Szóval írja be a vesszőt, aztán B2)

a=HA(ÉS(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Most nyomja meg az enter billentyűt az érték az F2 Cellában kell kinéznie:

Most, hogy megvan a képletek a helyén, akkor másolja le őket, hogy az utolsó adatok értelme, oszlopok, valamint B. Szóval másolja a képletet le fog kinézni:

Figyeljük meg, hogy az adatok pontot 1/4 pedig 1/7 most mutató értéke az #N/A. A többi érték ugyanaz, mint amit a B oszlopban található eredeti adatkészletünkben lát. Milyen jó excel tipp, hogy megszabaduljon a kiugróktól a trendünktől.

most mindannyian készen állunk az Excel outlier diagram felépítésére.

4) hozzon létre Outlier Chart Excel

így lehetővé teszi, hogy hozzon létre a chart kiemelve a cellatartomány E1: F11. Ezután lépjen a szalag beszúrása elemre, majd válasszon ki egy Sordiagramot jelölőkkel a sor gombjából:

a diagramnak most így kell kinéznie:

úgy néz ki, egy csomó más, mint az egyik a kiugró:

5) Add Trendline diagram, Trendline Formula és R-négyzet értéke

most, hogy megvan a diagram, csak kell egy pár utolsó dolog. Adjunk hozzá egy trendvonalat a diagramunkhoz. Ehhez kattintson a diagramra, majd kattintson a diagram sorra. Innen LÉPJEN az elrendezés szalagra, válassza ki a Trendline gombot, majd válassza a lineáris Trendline lehetőséget:

Majd válassza ki a “Lineáris”, meg a “Display Egyenlet az ábrán a”, valamint a “Display R-négyzet érték a táblázat” a Formátum Trendline párbeszédpanelen:

A táblázat most így néz ki:

6) Sima a sort, rendet Diagram Szemét

Most, annyit kell tenned, hogy tisztítsák meg a diagram minden készen áll! Először válassza ki a diagramot, majd válassza ki a legendát, majd nyomja meg a Törlés gombot. A diagramnak most így kell kinéznie:

most létre kell hoznunk egy simított vonaldiagramot. Ehhez válassza ki a diagramot, majd a sort a diagramban. Innen nyomja meg a CTRL + 1 gombot a Formátum sorozat párbeszédpanel megjelenítéséhez. Innen válassza ki a vonalstílusokat a bal oldali menüből, majd válassza a simított sort a Vonalstílusválasztásból:

a végső diagramnak így kell kinéznie:

nem sok különbség, de ha az adatok nagyobb változások, akkor meg még sima

.

most összehasonlíthatja az eredeti kéréssel. Szinte pontosan úgy néz ki, mint amit akartunk. Látni fogja azonban, hogy a január 3-tól 5-ig terjedő adatpontok összekapcsolódnak, és nem törnek meg, mint az eredeti minta. Sam az 1/6 és 1/8 közötti adatpontokra. Nem vagyok biztos benne, hogy mennyire kritikus az, ha ezek a vonalak nem kapcsolódnak egymáshoz, de nem lehetséges az Excel vonaldiagramok létrehozásának módjával, ha van egy adathézag, amely képletet használ.

azonban gyere vissza holnap, ahol megmutatom, hogyan lehet egy vonaldiagramot megjeleníteni a képletek segítségével.

Video Tutorial

nézd meg ezt a videót bemutató épület egy Excel vonal diagram, amely figyelmen kívül hagyja a kiugró: