v Pořádku, jsem vyslán tento Graf aplikace Excel Výzvu na pátek. Jak jste si vedl? Jsem si jistý, že vaše řešení je lepší než moje. To byla výzva z Otázky fóra Excel:

“ Jak odstranit odlehlé hodnoty v grafu

při prohlížení některých grafů někdy musím některé odlehlé hodnoty ignorovat. Chcete vědět, zda je možné kliknout na odlehlá data nebo odpovídající x na ose x a graf bude aktualizován bez těchto odlehlých dat. Vím, že můžu jít do datové sady a odstranit odlehlé hodnoty, ale chci to zjednodušit tím, že to udělám v grafu. Dík.“

zde je obrázek, který tento uživatel zveřejnil:

graf má 2 odlehlé datové body. 1 4. ledna a 1 7. ledna. Uživatel aplikace Excel ručně odstranil datové body a poté uvidíte konečný graf ve spodní části obrázku.

musíme také přidat trendovou linii se vzorcem trendové linie a hodnotou R, ale může to být ruční krok. Údaje pocházejí od 1. ledna do 10. ledna v roce 2013. Takže pojďme s těmito předpoklady.

Tak jak můžeme vytvořit konečný graf odstraněním datových bodů od 4. ledna a 7. ledna bez ruční odstranění těchto datových bodů? Pojďme na to! Níže najdete rychlý rozpis o tom, jak jsem vytvořil své řešení. Níže je podrobný návod krok za krokem tohoto řešení aplikace Excel. Níže uvidíte Video ukázku tohoto tipu aplikace Excel. Konečně, níže najdete kopii mé tabulky, kterou si můžete stáhnout a naplánovat s technikou dat a grafů.

nejprve vám ukážu, jak tento problém řešil jiný uživatel. Pete měl geniální způsob, jak tento problém vyřešit. Vezme datové body a vytvoří vzorec založený na sklonu čáry. Zde je jeho řešení podle jeho vlastních slov:

od Pete: „hrál jsem si s vaší novou páteční výzvou a přišel jsem s jiným otočením výsledků. Můj graf ukazuje původní data, a nová data s odstraněnými outlyers. Chcete-li zobrazit vzorce, budete muset list odemknout.

v Podstatě jsem vzal originální data a používá SKLON funkce najít sklon dat, a pak to přirovnal k teoretické linii založenou na hodnotu X pomocí algebraické vzorce pro řádky Y=MX+B. tento vzorec a vypočítá svahu, mohl bych to vyřešit pro B. Teď jsem používal a POKUD vzorec vrátí #N/A chybu, pokud skutečné hodnoty a teoretické hodnoty se lišily o dané % zvolené ověření dat rozevírací mobil. Tímto způsobem si uživatel může vybrat, v jakém procentu odstraní outlyers. Já měl obsahovat 500% a 100%, které umožní uživateli zobrazit původní data jako datový bod „2“ je 710% od vypočtené teoretické hodnoty.“

podívejte se na jeho řešení zde: Petes Outlyer Solution

nyní je moje základní řešení pro vytvoření řádku aplikace Excel, který nezobrazuje odlehlé hodnoty. Vraťte se zítra a podívejte se na mé pokročilé řešení.

Rozdělení

1) Vytvoření Grafu, Rozsah Dat,

2) Určit Buňky jako Hodnota Tolerance Úrovni,

3) Vytvořit Outlier Vzorec pro Grafu, Rozsah Dat,

4) Vytvořte Odlehlých hodnot Grafu v aplikaci Excel

5) Přidat spojnice trendu do Grafu, trendová Linie Formule a Hodnotu spolehlivosti R,

6) Hladké linie a vyčistit Graf Nevyžádané

Krok-za-Krokem

1) Vytvoření Grafu, Rozsah Dat,

Dobře, pojďme vytvořit naši původní rozsah dat v buňkách A1:B11. Nyní umožňuje vytvořit rychlý řádek grafu v aplikaci Excel se značkami vidět, jak to vypadá. Vypadá téměř přesně jako první graf, který jste viděli z původní otázky.

Jediný rozdíl je, že naše linka není vyhlazené. Poslední krok v tomto tutoriálu vám ukáže, jak tuto změnu provést. Ale tady je to, jak by vypadal původní graf s vyhlazenými čarami:

2) Určit Buňky jako Hodnota Tolerance Úrovni,

Dobře, moje řešení využívá buňky tabulky určit, co tolerance je pro odlehlá hodnota. Takže jsem v tabulce označil buňku D3 jako úroveň tolerance. Při pohledu na data se zdá, že datový bod je v příkladu uživatele aplikace Excel označen jako odlehlá hodnota, pokud se body liší o jednotkách 7.

takže jsem dal v buňce D3 hodnotu 7:

použijeme buňku D3 v dalším kroku, když vytvoříme náš odlehlý vzorec.

3) Vytvořte odlehlý vzorec pro rozsah dat grafu

nejprve dejte naše data do buňky E2. To je stejně jednoduché jako vložení = A2 do buňky E2 .

Dobře, toto je krok, který dělá všechno práci. Co musíme udělat, je porovnat aktuální datový bod s datovými body nad a pod aktuálním. Porovnání zjistí, zda je aktuální datový bod mimo úroveň tolerance, kterou jsme nastavili v buňce D3. V podstatě odečteme aktuální datový bod od datového bodu o jednu buňku výše a uvidíme, zda je rozdíl větší než tolerance v buňce D3. Poté tento krok opakujte pro buňku pod aktuálním datovým bodem. Pokud jsou oba datové body větší než úroveň tolerance, vložíme do aktuální buňky NA (). Pokud obě hodnoty nejsou mimo toleranci, vložíme do této buňky hodnotu aktuálního datového bodu. Protože rozdíl může být záporný, musíme toto odčítání zabalit do funkce absolutní hodnoty. Takže do buňky F2 dáme tento vzorec:

=KDYŽ(A(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Zde je rozpis vzorec:

Nejdříve začneme s if formule, tak jdi do buňky F2 a zadejte toto:

=IF (a

protože potřebujeme porovnat hodnoty a následně na základě této hodnoty jsme se dát jednu z 2 hodnoty v této buňce

Teď, jak jsem popsal výše, je třeba porovnat aktuální datový bod s mobilním výše a mobilní níže. A pokud jsou obě srovnání mimo toleranci, pak Nechceme ukázat datový bod. Takže, protože máme co do činění s „JAK“ srovnání, bychom měli použít A funkce, takže umožňuje typu, že v příští:

=KDYŽ(A(

Teď, jak jsem uvedl dříve, někdy odečtením dat bodů, bude konečná hodnota Negativní. A záporná hodnota bude vždy pod naší tolerancí, takže se musíme ujistit, že náš rozdíl je vždy v kladných jednotkách, protože naše úroveň tolerance je v kladných jednotkách. Aby bylo něco vždy pozitivní, musíme zabalit naše Srovnání do funkce absolutní hodnoty, takže zadejte následující funkci ABS:

= IF (AND (ABS (

nyní umožňuje skutečně provést naši 1.srovnávací hodnotu. Děláme to jednoduše odečtením aktuální hodnoty s hodnotou nad aktuálním datovým bodem a uvidíme, jestli je větší než naše úroveň tolerance v buňce D3. Takže psát, že:

=KDYŽ(A(ABS(B2-B1)>$D$3

Všimněte si, že jsem udělal D3 a absolutní odkaz. Pokud nevíte, co je to Absolutní Odkaz, podívejte se na tento příspěvek:

s Odkazem na Rozsahy ve Vzorcích pro váš Excel Dashboard Šablony

To je první část z našich A funkce a a funkce, které jsou odděleny čárkou, pak si bude klást další srovnání. Takže umožňuje zadat další srovnání za čárkou. Dalším porovnáním je porovnání našeho aktuálního datového bodu s dalším datovým bodem. Takže umožňuje zadat, že v:

=KDYŽ(A(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3

Vzhledem k tomu, že je naše poslední srovnání, které se musíme starat o to, můžete ukončit A funkce se pravá závorka. Nyní můžeme vložit čárku a určit, co dělat, pokud jsou obě kritéria a kritéria pravdivá. V našem případě, pokud jsou oba rozdíly datových bodů větší než tolerance, musíme tam Vložit funkci NA (). Takže umožňuje zadat, že pokud je skutečná hodnota:

=KDYŽ(A(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

Pokud se chcete dozvědět více o NA() funkce, podívejte se na tento příspěvek:

Jak-chcete-li Skrýt Nulové Koláčový Graf Plátek nebo Skládaný sloupcový Graf Sekce

Nyní jsme připraveni na poslední hodnotu, pokud A funkce NENÍ PRAVDA. Vložte čárku a zadejte hodnotu IF FALSE. V tomto případě chceme vložit skutečnou hodnotu datového bodu naší datové buňky B2. Takže zadejte čárku a pak B2)

=KDYŽ(A(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Nyní stiskněte enter a hodnota v Buňce F2 by měl vypadat takto:

Nyní, že máme vzorce, můžeme je zkopírovat dolů na naše poslední bod dat ve sloupcích a a B. Tak zkopírujte tento vzorec dolů, a to bude vypadat takto:

Všimněte si, že datové body pro 1/4 a 1/7 se nyní ukazuje hodnotu #N/A. A ostatní hodnoty jsou stejné, jak vidíte v naší původní datové sadě ve sloupci B. To je tak úžasné! Jaký skvělý excel tip, jak se zbavit odlehlých hodnot z našeho trendu.

nyní jsme všichni nastaveni na sestavení našeho Excel outlier chart.

4) Vytvořte odlehlý graf v aplikaci Excel

takže umožňuje vytvořit náš graf zvýrazněním rozsahu buněk od E1: F11. Poté přejděte na stuhu Vložit a vyberte řádek graf se značkami z tlačítka řádek:

váš graf by nyní měl vypadat takto:

To vypadá hodně jinak, pak s tím, odlehlé hodnoty:

5) Přidat spojnice trendu do Grafu, trendová Linie Formule a Hodnotu spolehlivosti R,

Nyní, že máme graf, potřebujeme jen pár věcí. Přidejme do našeho grafu trendovou linii. Chcete-li to provést, klikněte na graf a poté na řádek grafu. Odtud přejděte na pásku rozvržení a vyberte tlačítko Trendline a poté vyberte Lineární Trendline:

vyberte „Lineární“ a „Zobrazit Rovnici v grafu“ a „Zobrazit hodnotu spolehlivosti R na grafu“ od Formátu trendová Linie dialogové okno:

Váš graf bude nyní vypadat takto:

6) Hladké linie a vyčistit Graf Nevyžádané

Nyní vše, co musíme udělat, je vyčistit graf a vše je hotovo! Nejprve vyberte graf a poté vyberte legendu a stiskněte klávesu delete. Váš graf by nyní měl vypadat takto:

nyní musíme vytvořit vyhlazený graf čáry. Chcete-li to provést, vyberte graf a poté řádek v grafu. Odtud stisknutím klávesy CTRL+1 vyvolejte dialogové okno Formát série. Odtud, vyberte Styly čar z levého menu a zvolte Vyhlazené Linie od Linie Styl volby:

Vaše poslední graf by měl nyní vypadat takto:

Není moc rozdíl, ale když vaše data má větší změny, bude to vypadat ještě více hladké

.

nyní umožňuje porovnat ji s původním požadavkem. Vypadá téměř přesně jako to, co jsme chtěli. Uvidíte však, že datové body od 3. do 5. ledna jsou spojeny a nejsou rozbité jako původní vzorek. Sam pro datové body mezi 1/6 a 1/8. Nejsem si jistý, jak důležité je, aby se tyto řádky nepřipojily, ale není to možné s tím, jak Excel vytváří čárové grafy, když máte mezeru dat, která používá vzorec.

vraťte se však zítra, kde vám ukážu, jak vytvořit řádkový graf, který zobrazuje mezeru pomocí vzorců.

Video Tutorial

podívejte se na toto video demonstrace budování Excel Line Chart, který ignoruje odlehlé hodnoty: