Ok, ik postte deze Excel Chart Challenge op vrijdag. Hoe heb je het gedaan? Ik weet zeker dat jouw oplossing beter is dan de mijne. Dit was de uitdaging van een Excel Forumvraag:

“hoe uitschieters in grafiek te elimineren

bij het bekijken van sommige grafieken moet ik soms enkele uitschieters negeren. Wilt u weten of het mogelijk is om te klikken op de uitbijter gegevens of de bijbehorende x op de x-as en de grafiek zal worden bijgewerkt zonder deze uitbijter gegevens. Ik weet dat ik kan gaan naar de dataset en verwijder de uitschieters, maar willen vereenvoudigen door het te doen op de grafiek. Bedanken.”

Hier is het beeld dat deze gebruiker heeft geplaatst:

de grafiek heeft 2 outlying datapunten. 1 op 4 januari en 1 op 7 januari. De Excel-gebruiker heeft de gegevenspunten handmatig verwijderd en vervolgens ziet u het definitieve diagram onderaan de afbeelding.

We moeten ook een trendlijn met een trendlijnformule en R-waarde toevoegen, maar dit kan een handmatige stap zijn. De gegevens gaan van 1 januari tot 10 januari in het jaar 2013. Dus laten we gaan met deze veronderstellingen.

dus hoe kunnen we de definitieve grafiek maken door de gegevenspunten van 4 januari en 7 januari te verwijderen zonder deze gegevenspunten handmatig te verwijderen? Laten we beginnen! Hieronder vindt u een snel overzicht over hoe ik mijn oplossing heb gemaakt. Hieronder is een gedetailleerde stap-voor-stap handleiding van deze Excel-oplossing. Hieronder ziet u een Video demonstratie van deze Excel tip. Tot slot, hieronder vindt u een kopie van mijn spreadsheet die u kunt downloaden om te plannen rond met de gegevens en grafieken techniek.

ten eerste laat ik je zien hoe een andere gebruiker dit probleem aanpakte. Pete had een ingenieuze manier om dit probleem op te lossen. Hij neemt de datapunten en maakt een formule gebaseerd op de helling van de lijn. Hier is zijn oplossing in zijn eigen woorden:

van Pete: “I was playing around with your new Friday challenge, and I came up with a different spin on the results. Mijn grafiek toont de originele gegevens, en de nieuwe gegevens met de outlers verwijderd. U moet het blad ontgrendelen om de formules te bekijken.

In Principe nam ik de oorspronkelijke gegevens en gebruikte de helling functie om de helling van de gegevens te vinden, en vergeleek het met een theoretische lijn gebaseerd op de x-waarde met behulp van de algebraïsche formule voor lijnen Y=mx+B. Met deze formule en de berekende helling, kon ik oplossen voor B. nu gebruikte ik en als formule om een #N/A fout te retourneren als de reële waarde en theoretische waarde verschilden met een gegeven % gekozen door de data validation drop down cel. Op deze manier kan de gebruiker kiezen op welk percentage om de outlers te verwijderen. Ik moest 500% en 100% opnemen om de gebruiker in staat te stellen de oorspronkelijke gegevens te tonen als het gegevenspunt “2” 710% verwijderd is van de berekende theoretische waarde.”

Bekijk hier zijn oplossing: Petes Outlyer Solution

Hier is mijn basisoplossing voor het maken van een Excel-regel die geen uitschieters toont. Kom morgen terug om mijn geavanceerde oplossing te zien.

De Uitsplitsing

1) Grafiek Maken van Gegevens Bereik

2) het Aanwijzen van een Cel als de Afwijkende Tolerantie Niveau

3) het Maken van Afwijkende Formule voor de Grafiek Data-Bereik

4) Afwijkende Grafiek in Excel

5) Trendlijn toevoegen aan een Grafiek, Trendline Formule en R-Kwadraat Waarde

6) een Vloeiende lijn en opruimen Grafiek Ongewenste

Stap-voor-Stap

1) Grafiek Maken van Gegevens Bereik

Oké, laten we creëren onze oorspronkelijke bereik voor gegevens in de cellen A1:B11. Laten we nu een snel lijndiagram maken in Excel met markeringen om te zien hoe het eruit ziet. Lijkt bijna precies op de eerste grafiek die je zag van de oorspronkelijke vraag.

Het enige verschil is dat onze regel niet glad is. De laatste stap in deze tutorial laat je zien hoe je deze verandering kunt maken. Maar hier is hoe de originele grafiek eruit zou zien met gladgestreken lijnen:

2) Wijs een cel aan als het tolerantieniveau voor een uitschieter

OK, Mijn oplossing gebruikt een spreadsheetcel om aan te geven wat de tolerantie is voor een uitschieter. Dus ik heb cel D3 in de spreadsheet aangewezen als mijn tolerantieniveau. Kijkend naar de gegevens, lijkt het erop dat een gegevenspunt is aangewezen als een uitschieter in het voorbeeld van de Excel-gebruiker wanneer de punten ongeveer 7 eenheden verschillen.

dus ik heb een waarde van 7 in cel D3 ingevoerd:

We zullen cel D3 gebruiken in de volgende stap wanneer we onze uitbijterformule maken.

3) Maak een Uitbijterformule voor het Diagramgegevensbereik

eerst, laten we onze datums in cel E2 plaatsen. Dit is zo eenvoudig als het plaatsen van een = A2 in cel E2.

OK, dit is de stap die alles laat werken. Wat we moeten doen is het huidige gegevenspunt vergelijken met de gegevenspunten boven en onder de huidige. De vergelijking zal zien of het huidige gegevenspunt buiten het tolerantieniveau ligt dat we in cel D3 hebben ingesteld. In wezen gaan we het huidige gegevenspunt aftrekken van het gegevenspunt één cel hierboven en kijken of het verschil groter is dan de tolerantie in cel D3. Herhaal vervolgens deze stap voor de cel onder het huidige gegevenspunt. Als beide datapunten groter zijn dan het tolerantieniveau, dan zetten we een NA() in de huidige cel. Als beide waarden niet buiten de tolerantie vallen, zetten we de waarde van het huidige gegevenspunt in deze cel. Omdat het verschil negatief kan zijn, moeten we deze aftrekking in een absolute waarde functie wikkelen. Dus in cel F2 zetten we deze formule:

=ALS(EN(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Hier is een overzicht van de formule:

om te beginnen wij gaan beginnen met een ALS formule, dus ga naar cel F2 en typ dit:

=IF(

omdat wij nodig hebben voor het vergelijken van een waarde en daarna op basis van die waarde brengen wij een van de 2 waarden in deze cel

Nu, zoals ik hierboven beschreven, moet u vergelijken met de huidige gegevens wijzen met de cel boven en de cel eronder. En, als beide vergelijkingen buiten de tolerantie vallen, dan willen we het gegevenspunt niet laten zien. Dus, omdat we te maken hebben met een” beide ” vergelijking, moeten we de functie en gebruiken, dus laten we typen dat in volgende:

= als(en (

nu zoals ik eerder zei, soms aftrekken van de data punten zal de uiteindelijke waarde negatief. En een negatieve waarde zal altijd onder onze tolerantie, dus we moeten ervoor zorgen dat ons verschil is altijd in positieve eenheden, omdat ons tolerantieniveau is in positieve eenheden. Om iets altijd positief te maken, moeten we onze vergelijking wikkelen in een Absolute waarde functie, dus laten we typ in de ABS functie volgende:

=IF(en(ABS(

laten nu eigenlijk maken onze 1e vergelijking waarde. We doen dit simpelweg door de huidige waarde af te trekken met de waarde boven het huidige gegevenspunt en te zien of het groter is dan ons tolerantieniveau in cel D3. Dus typ dat in:

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

merk op dat ik de D3 en absolute referentie heb gemaakt. Als u niet weet wat een Absolute referentie is, bekijk dan dit bericht:

verwijzend naar bereiken in formules voor uw Excel Dashboard sjablonen

dat is het eerste deel van onze en functie en de en functie wordt gescheiden door een komma dan zult u de volgende vergelijking plaatsen. Dus laten we typen in de volgende vergelijking na een komma. De volgende vergelijking is om ons huidige gegevenspunt te vergelijken met het volgende gegevenspunt. Dus laten we dat intypen:

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

aangezien dat onze laatste vergelijking is waar we ons zorgen over moeten maken, kunt u de en-functie beëindigen met een haakje. Nu kunnen we een komma plaatsen en bepalen wat we moeten doen als beide criteria waar zijn. In ons geval, als beide verschillen van de datapunten groter zijn dan de tolerantie, dan moeten we daar een na() functie in zetten. Dus laten we typen dat als ware waarde:

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

als u meer wilt weten over de na() functie, bekijk dan dit bericht:

hoe Verberg je een cirkeldiagram Slice of gestapelde kolomdiagram sectie

nu zijn we klaar voor de uiteindelijke waarde als de en-functie niet waar is. Dus zet een komma in en typ de IF FALSE waarde in. In dit geval willen we de werkelijke datapuntwaarde van onze gegevenscel B2 invoeren. Dus typ een komma en vervolgens B2)

=ALS(EN(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

druk Nu op enter en uw waarde in Cel F2 moet er als volgt uitzien:

Nu hebben we onze formules op zijn plaats, kunnen we kopieer ze naar onze laatste gegevens wijzen in de kolommen A en B. Dus kopieer deze formule naar beneden en het zal er als volgt uitzien:

Merk op dat data punten voor 1/4 en 1/7 tonen nu een waarde van #N/A. En de andere waarden zijn hetzelfde als je ziet in onze oorspronkelijke dataset in kolom B. Dat is zo geweldig! Wat een leuke excel tip om zich te ontdoen van uitschieters uit onze trend.

nu zijn we helemaal klaar om onze Excel outlier grafiek te bouwen.

4) Maak een Uitbijterdiagram in Excel

dus laten we onze grafiek maken door het celbereik van E1:F11 te markeren. Ga dan naar het lint invoegen en selecteer een lijndiagram met markeringen van de Lijnknop:

uw grafiek ziet er nu zo uit:

het ziet er heel anders uit dan die met de uitschieters:

5) Voeg trendlijn toe aan het diagram, Trendlijnformule en R-kwadraatwaarde

nu we onze grafiek hebben, hebben we alleen nog een paar laatste dingen nodig. Laten we een trendlijn toevoegen aan onze grafiek. Om dit te doen, klik op de grafiek en klik vervolgens op de grafiek lijn. Van daar, ga naar de lay-out Lint en kies de trendlijn knop en kies vervolgens Lineaire trendlijn:

kies Dan “Lineaire” en “Display Equation on chart” en “Display R-kwadraat in grafiek” van de Indeling van de Trendlijn in het dialoogvenster:

de grafiek wordt nu als volgt uitzien:

6) Een soepele lijn en opruimen Grafiek Ongewenste

wat we Nu nog moeten doen is het schoonmaken van de grafiek en we zijn er helemaal klaar voor! Selecteer eerst uw grafiek en selecteer vervolgens de legende en druk op uw delete-toets. Uw grafiek ziet er nu zo uit:

nu moeten we een glad lijndiagram maken. Om dat te doen, selecteert u de grafiek en vervolgens de lijn in de grafiek. Druk vanaf daar op uw Ctrl + 1 toets om de Format Series dialoogvenster. Selecteer van daaruit de lijnstijlen in het linker menu en kies gladgestreken lijn uit de Lijnstijlkeuzes:

uw uiteindelijke grafiek ziet er nu zo uit:

niet veel verschil, maar wanneer uw gegevens grotere veranderingen hebben, zal het er nog gladder uitzien

.

laten we het nu vergelijken met het oorspronkelijke verzoek. Het lijkt bijna precies op wat we wilden. U zult echter zien dat de gegevenspunten van 3 januari tot 5 januari zijn samengevoegd en niet zijn gebroken zoals het oorspronkelijke monster. Sam voor gegevenspunten tussen 1/6 en 1/8. Ik weet niet zeker hoe belangrijk het is om deze lijnen niet te verbinden, maar het is niet mogelijk met de manier waarop Excel lijndiagrammen maakt wanneer u een gat van gegevens hebt dat een formule gebruikt.

kom morgen echter terug waar ik u zal laten zien hoe u een lijndiagram een gat laat zien met behulp van formules.

Video Tutorial

bekijk deze video demonstratie van het bouwen van een Excel-lijndiagram dat uitschieters negeert: