Okej, jag publicerade denna Excel-Diagramutmaning på fredagen. Hur gick det? Jag är säker på att din lösning är bättre än min. Detta var utmaningen från en Excel-Forumfråga:

”hur man eliminerar outliers i graph

När man tittar på några grafer måste jag ibland ignorera några outliers. Vilja veta om det är möjligt att klicka på outlier data eller motsvarande x på X-axeln och grafen kommer att uppdateras utan denna outlier data. Jag vet att jag kan gå till datauppsättningen och ta bort outliers men vill förenkla genom att göra det på grafen. Tack.”

här är bilden som den här användaren publicerade:

grafen har 2 avlägsna datapunkter. 1 den 4 januari och 1 den 7 januari. Excel-användaren raderade datapunkterna manuellt och sedan ser du det slutliga diagrammet längst ner på bilden.

Vi måste också lägga till en trendlinje med en trendlinjeformel och R-värde men detta kan vara ett manuellt steg. Uppgifterna går från 1 januari till 10 januari år 2013. Så låt oss gå med dessa antaganden.

Så hur kan vi skapa det slutliga diagrammet genom att ta bort datapunkterna från 4 januari och 7 januari utan att manuellt radera dessa datapunkter? Låt oss komma till det! Nedan hittar du en snabb uppdelning på hur jag skapade min lösning. Nedan är en detaljerad steg-för-steg-handledning av denna Excel-lösning. Nedan ser du en videodemonstration av detta Excel-Tips. Slutligen, nedan hittar du en kopia av mitt kalkylblad som du kan ladda ner för att planera med data-och kartläggningstekniken.

Låt mig först visa dig hur en annan användare hanterade detta problem. Pete hade ett genialt sätt runt detta problem. Han tar datapunkterna och skapar en formel baserad på linjens lutning. Här är hans lösning med sina egna ord:

från Pete: ”jag lekte med din nya fredagsutmaning, och jag kom med en annan snurr på resultaten. Mitt diagram visar originaldata och de nya uppgifterna med outlyers borttagna. Du måste låsa upp arket för att visa formlerna.

i grund och botten tog jag originaldata och använde lutningsfunktionen för att hitta dataens lutning och jämförde den sedan med en teoretisk linje baserad på X-värdet med hjälp av den algebraiska formeln för linjer Y=MX+B. Med denna formel och den beräknade lutningen kunde jag lösa för B. nu använde jag och om formel för att returnera ett #N/A-fel om det verkliga värdet och det teoretiska värdet skilde sig med en given % vald av datavalideringens rullgardinscell. På så sätt kan användaren välja vid vilken procent att ta bort outlyers. Jag var tvungen att inkludera 500% och 100% för att tillåta användaren att visa originaldata eftersom datapunkten ”2” är 710% bort från det beräknade teoretiska värdet.”

kolla in hans lösning här: Petes Outlyer Solution

Nu här är min grundläggande lösning för att skapa en Excel-rad som inte visar avvikare. Kom tillbaka imorgon för att se min avancerade lösning.

uppdelningen

1) Skapa Diagramdataområde

2) beteckna en Cell som Outlier toleransnivå

3) Skapa Outlier formel för Diagramdataområde

4) Skapa Outlier diagram i Excel

5) Lägg till trendlinje till Diagram, Trendlinjeformel och R-kvadratiskt värde

6) släta linjen och städa upp diagram skräp

steg-för-steg

1) Skapa diagram dataområde

okej, låter skapa vårt ursprungliga dataområde i cellerna A1:B11. Nu kan skapa en snabb linjediagram i Excel med markörer för att se hur det ser ut. Ser nästan exakt ut som det första diagrammet som du såg från den ursprungliga frågan.

enda skillnaden är att vår linje inte slätas. Det sista steget i denna handledning visar hur du gör denna förändring. Men här är vad det ursprungliga diagrammet skulle se ut med jämna linjer:

2) Ange en Cell som Outlier toleransnivå

okej, min lösning använder en kalkylarkcell för att ange vad toleransen är för en outlier. Så jag har utsett cell D3 i kalkylbladet som min toleransnivå. När man tittar på data verkar det som om en datapunkt betecknas som en outlier i Excel-användarens exempel när punkterna skiljer sig åt cirka 7 enheter.

så jag har lagt in ett värde på 7 i cell D3:

vi använder cell D3 i nästa steg när vi skapar vår outlier-formel.

3) Skapa Outlier formel för Diagramdataområde

först kan vi sätta våra datum i cell E2. Detta är lika enkelt som att sätta an =A2 i cell E2.

okej, det här är steget som får allt att fungera. Vad vi behöver göra är att jämföra den aktuella datapunkten med datapunkterna ovanför och under den nuvarande. Jämförelsen kommer att se om den aktuella datapunkten ligger utanför toleransnivån vi satt i cell D3. I huvudsak kommer vi att subtrahera den aktuella datapunkten från datapunkten en cell ovan och se om skillnaden är större än toleransen i cell D3. Upprepa sedan detta steg för cellen under den aktuella datapunkten. Om båda datapunkterna är större än toleransnivån sätter vi en NA() i den aktuella cellen. Om båda värdena inte är toleranta kommer vi att sätta värdet på den aktuella datapunkten i den här cellen. Nu eftersom skillnaden kan vara negativ måste vi sätta in denna subtraktion i en absolut värdefunktion. Så i cell F2 låt oss sätta denna formel:

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

här är en uppdelning av formeln:

först ska vi börja med en if-formel, så gå till cell F2 och skriv detta:

=If(

eftersom vi behöver jämföra ett värde och sedan baserat på det värdet kommer vi att sätta ett av 2 värden i denna cell

nu som jag beskrivit ovan måste du jämföra värdet av aktuell datapunkt med cellen ovan och cellen nedan. Och om båda jämförelserna inte är toleranta, vill vi inte visa datapunkten. Så eftersom vi har att göra med en ”båda” jämförelse, bör vi använda och-funktionen, så låt oss skriva det i nästa:

=IF(och(

nu som jag sa tidigare, ibland subtraherar datapunkterna gör det slutliga värdet negativt. Och ett negativt värde kommer alltid att ligga under vår tolerans, så vi måste se till att vår skillnad alltid är i positiva enheter eftersom vår toleransnivå är i positiva enheter. För att göra något alltid positivt måste vi sätta in vår Jämförelse i en absolut Värdefunktion, så låt oss skriva in ABS-funktionen nästa:

=IF(och(ABS(

Nu kan vi faktiskt göra vårt 1: a jämförelsevärde. Vi gör detta helt enkelt genom att subtrahera det aktuella värdet med värdet ovanför den aktuella datapunkten och se om det är större än vår toleransnivå i cell D3. Så skriv in det:

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

Observera att jag gjorde D3 och absolut referens. Om du inte vet vad en absolut referens är, kolla in det här inlägget:

hänvisar till intervall i formler för dina Excel-Instrumentpanelmallar

det är den första delen av vår och funktion och och funktionen separeras med ett komma så lägger du nästa jämförelse. Så låt oss skriva in nästa jämförelse efter ett komma. Nästa jämförelse är att jämföra vår nuvarande datapunkt med nästa datapunkt. Så låt oss skriva in det:

= om (och(ABS (B2-B1)>$D$3,ABS(B2-B3)>$d$3

eftersom det är vår sista jämförelse som vi behöver oroa oss för kan du avsluta och-funktionen med rätt parentes. Nu kan vi lägga ett komma in och bestämma vad vi ska göra om både och kriterierna är sanna. I vårt fall, om båda skillnaderna i datapunkterna är större än toleransen, måste vi sätta en NA () – funktion där inne. Så låt oss skriva in det om sant värde:

=om(och(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

om du vill lära dig mer om NA ()-funktionen, Kolla in det här inlägget:

Hur-döljer du en Nollpaj chart slice eller stacked column chart section

Nu är vi redo för det slutliga värdet om funktionen och inte är sant. Så sätt ett komma in och skriv in om falskt värde. I det här fallet vill vi sätta in det faktiska datapunktvärdet för vår DataCell B2. Så skriv in ett komma och sedan B2)

=om(och(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Tryck nu på enter och ditt värde i Cell F2 ska se ut så här:

Nu när vi har våra formler på plats kan vi kopiera dem ner till vår sista datapunkt i kolumnerna A och B. så kopiera denna formel ner och det kommer att se ut så här:

Lägg märke till att datapunkter för 1/4 och 1/7 visar nu ett värde på #N/A. Och de andra värdena är desamma som du ser i vår ursprungliga datamängd i kolumn B. Det är så underbart! Vilket coolt excel-Tips för att bli av med outliers från vår trend.

Nu är vi redo att bygga vårt Excel outlier-diagram.

4) Skapa Outlier diagram i Excel

så låter skapa vårt diagram genom att markera cellområdet från E1:F11. Gå sedan till Infoga-bandet och välj ett linjediagram med markörer från Radknappen:

ditt diagram ska nu se ut så här:

det ser mycket annorlunda ut än den med outliers:

5) Lägg till trendlinje till Diagram, Trendlinjeformel och R-Kvadratvärde

Nu när vi har vårt diagram behöver vi bara några sista saker. Låt oss lägga till en trendlinje i vårt diagram. För att göra detta, klicka på diagrammet och klicka sedan på diagramraden. Därifrån går du till Layoutbandet och väljer Trendline-knappen och väljer sedan linjär Trendline:

välj sedan ”linjär” och ”visa ekvation på diagram” och ”visa r-kvadratvärde på diagram” från dialogrutan Format Trendline:

ditt diagram kommer nu att se ut så här:

6) släta linjen och städa upp diagrammet skräp

Nu behöver vi bara städa upp diagrammet och vi är alla redo! Välj först ditt diagram och välj sedan legenden och tryck på delete-tangenten. Ditt diagram ska nu se ut så här:

Nu måste vi skapa ett jämnt linjediagram. För att göra det, välj diagrammet och sedan raden i diagrammet. Därifrån trycker du på CTRL + 1-tangenten för att öppna dialogrutan Format Series. Därifrån väljer du Linjestilarna från den vänstra menyn och väljer jämn linje från linjens stilval:

ditt slutliga diagram ska nu se ut så här:

inte mycket skillnad, men när dina data har större förändringar kommer det att se ännu smidigare ut

.

Nu kan vi jämföra det med den ursprungliga begäran. Det ser nästan exakt ut som vad vi ville ha. Du kommer dock att se att datapunkterna från 3 januari till 5: e är förenade och inte brutna som det ursprungliga provet. Sam för datapunkter mellan 1/6 och 1/8. Jag är inte säker på hur viktigt det är att inte ha dessa linjer ansluta, men det är inte möjligt med hur Excel skapar linjediagram när du har ett gap av data som använder en formel.

men kom tillbaka imorgon där jag ska visa dig hur man gör ett linjediagram visar ett gap med formler.

video Tutorial

kolla in den här videodemonstrationen för att bygga ett Excel-linjediagram som ignorerar avvikare: