dobra, opublikowałem to wyzwanie na wykresie Excela w piątek. Jak ci poszło? Jestem pewien, że Twoje rozwiązanie jest lepsze niż moje. To było wyzwanie z pytania na forum Excela:

„Jak wyeliminować wartości odstające w grafie

przeglądając niektóre wykresy, czasami muszę zignorować niektóre wartości odstające. Chcesz wiedzieć, czy można kliknąć na dane odstające lub odpowiednie x na osi x, a wykres zostanie zaktualizowany bez tych danych odstających. Wiem, że mogę przejść do zbioru danych i usunąć wartości odstające, ale chcę uprościć, robiąc to na wykresie. Wielkie dzięki.”

Oto zdjęcie, które umieścił ten użytkownik:

Wykres ma 2 odległe punkty danych. 1 4 stycznia i 1 7 stycznia. Użytkownik programu Excel ręcznie usunął punkty danych, a następnie zobaczysz końcowy wykres na dole obrazu.

musimy również dodać linię trendu z formułą linii trendu i wartością R, ale może to być krok ręczny. Dane dotyczą okresu od 1 stycznia do 10 stycznia 2013 roku. Więc chodźmy z tymi założeniami.

więc jak możemy stworzyć końcowy Wykres, usuwając punkty danych z 4 i 7 stycznia bez ręcznego usuwania tych punktów danych? Do dzieła! Poniżej znajdziesz Krótki opis tego, jak stworzyłem moje rozwiązanie. Poniżej znajduje się szczegółowy samouczek krok po kroku tego rozwiązania Excel. Poniżej zobaczysz film demonstracyjny tej porady programu Excel. Wreszcie, poniżej znajdziesz kopię mojego arkusza kalkulacyjnego, który możesz pobrać, aby zaplanować Dane i technikę tworzenia wykresów.

Po pierwsze, pokażę Ci, jak inny użytkownik poradził sobie z tym problemem. Pete miał genialny sposób na obejście tego problemu. Bierze punkty danych i tworzy wzór na podstawie nachylenia linii. Oto jego rozwiązanie w jego własnych słowach:

od Pete ’ a: „bawiłem się Twoim nowym piątkowym wyzwaniem i wymyśliłem inny obrót wyników. Mój wykres pokazuje oryginalne Dane, i nowe dane z outlyers usunięte. Będziesz musiał odblokować arkusz, aby wyświetlić formuły.

zasadniczo wziąłem oryginalne Dane i użyłem funkcji nachylenia, aby znaleźć nachylenie danych, a następnie porównałem je z teoretyczną linią opartą na wartości X za pomocą wzoru algebraicznego dla linii Y=MX+B. z tym wzorem i obliczonym nachyleniem mógłbym rozwiązać B. teraz użyłem formuły i IF, aby zwrócić błąd #N/A, jeśli rzeczywista wartość i teoretyczna wartość różniły się o dany % wybrany przez rozwijaną komórkę walidacji danych. W ten sposób użytkownik może wybrać, w jakim procentach usunąć zewnętrzne elementy. Musiałem uwzględnić 500% i 100%, aby umożliwić użytkownikowi pokazanie oryginalnych danych, ponieważ punkt danych ” 2 ” jest oddalony o 710% od obliczonej wartości teoretycznej.”

sprawdź jego rozwiązanie tutaj: Petes Outlyer Solution

oto moje podstawowe rozwiązanie do tworzenia linii Excela, która nie wyświetla wartości odstających. Przyjdź jutro, aby zobaczyć moje zaawansowane rozwiązanie.

podział

1) Utwórz zakres danych wykresu

2) Określ komórkę jako poziom tolerancji Odstającej

3) Utwórz formułę odstającą dla zakresu danych wykresu

4) Utwórz wykres odstający w programie Excel

5) Dodaj linię trendu do wykresu, formułę Trendową i wartość kwadratowa

6) wygładzić linię I wyczyścić śmieci wykresu

krok po kroku

1) Utwórz zakres danych wykresu

ok, Stwórz nasz oryginalny zakres danych w komórkach a1:B11. Teraz pozwala utworzyć szybki wykres liniowy w programie Excel ze znacznikami, aby zobaczyć, jak wygląda. Wygląda prawie dokładnie jak pierwszy wykres, który widzieliście z oryginalnego pytania.

jedyną różnicą jest to, że nasza linia nie jest wygładzona. Ostatni krok w tym samouczku pokaże Ci, jak wprowadzić tę zmianę. Ale oto jak wyglądałby oryginalny Wykres z wygładzonymi liniami:

2) wyznacz komórkę jako poziom tolerancji Odstającej

ok, moje rozwiązanie wykorzystuje komórkę arkusza kalkulacyjnego do wyznaczenia tolerancji odstającej. Więc wyznaczyłem komórkę D3 w arkuszu kalkulacyjnym jako mój poziom tolerancji. Patrząc na dane, wydaje się, że punkt danych jest oznaczony jako odstający w przykładzie użytkownika programu Excel, gdy punkty różnią się o jednostki 7.

więc wpisałem wartość 7 w komórce D3:

użyjemy komórki D3 w następnym kroku, gdy stworzymy naszą formułę odstającą.

3) Utwórz formułę odstającą dla zakresu danych wykresu

najpierw umieśćmy nasze daty w komórce E2. Jest to tak proste, jak umieszczenie an =A2 w komórce E2.

To, co musimy zrobić, to porównać bieżący punkt danych z punktami danych powyżej i poniżej bieżącego. Porównanie sprawdzi, czy bieżący punkt danych znajduje się poza poziomem tolerancji ustawionym w komórce D3. Zasadniczo odejmiemy bieżący punkt danych od punktu danych o jednej komórce powyżej i zobaczymy, czy różnica jest większa niż tolerancja w komórce D3. Następnie powtórz ten krok dla komórki poniżej bieżącego punktu danych. Jeśli oba punkty danych są większe niż poziom tolerancji, wtedy umieścimy NA () w bieżącej komórce. Jeżeli obie wartości nie są poza tolerancją, wtedy w tej komórce umieścimy wartość bieżącego punktu danych. Ponieważ różnica może być ujemna, musimy zawinąć to odejmowanie w funkcję wartości bezwzględnej. Więc w komórce F2 umieśćmy ten wzór:

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

oto podział formuły:

najpierw zaczniemy od formuły if, więc przejdź do komórki F2 i wpisz to:

=if(

ponieważ musimy porównać wartość, a następnie na podstawie tej wartości umieścimy jedną z 2 wartości w tej komórce

teraz, jak opisałem powyżej, musisz porównać bieżące dane.wskaż komórkę powyżej i komórkę poniżej. A jeśli oba porównania są poza tolerancją, to nie chcemy pokazywać punktu danych. Tak więc, ponieważ mamy do czynienia z porównaniem „BOTH”, powinniśmy użyć funkcji AND, więc wpisz, że w następnym:

=IF(AND(

teraz, jak powiedziałem wcześniej, czasami odejmowanie punktów danych spowoduje, że końcowa wartość będzie ujemna. A wartość ujemna zawsze będzie poniżej naszej tolerancji, więc musimy się upewnić, że nasza różnica jest zawsze w jednostkach dodatnich, ponieważ nasz poziom tolerancji jest w jednostkach dodatnich. Aby coś było zawsze dodatnie, musimy zawinąć nasze porównanie w funkcję wartości bezwzględnej, więc wpisz funkcję ABS dalej:

= IF(AND (ABS (

teraz zróbmy naszą pierwszą wartość porównania. Robimy to po prostu przez odjęcie bieżącej wartości z wartością nad bieżącym punktem danych i sprawdzić, czy jest ona większa niż nasz poziom tolerancji w komórce D3. Więc wpisz to w:

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

zauważ, że zrobiłem D3 i absolutne odniesienie. Jeśli nie wiesz, co to jest bezwzględne odniesienie, sprawdź ten post:

odwoływanie się do zakresów w formułach dla szablonów Pulpitu Nawigacyjnego programu Excel

To jest pierwsza część naszej funkcji AND, a funkcja and jest oddzielona przecinkiem, a następnie umieścisz następne porównanie. Wpisz następne porównanie po przecinku. Kolejnym porównaniem jest porównanie naszego aktualnego punktu danych z kolejnym punktem danych. Więc wpisujmy to w:

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

ponieważ jest to nasze ostatnie porównanie, o które musimy się martwić, możesz zakończyć funkcję AND prawym nawiasem. Teraz możemy wstawić przecinek i określić, co zrobić, jeśli oba kryteria i są prawdziwe. W naszym przypadku, jeśli obie różnice punktów danych są większe niż tolerancja, to musimy tam umieścić funkcję NA (). Więc wpisujmy, że jeśli wartość TRUE:

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

Jeśli chcesz dowiedzieć się więcej o funkcji NA (), sprawdź ten post:

jak ukryć zerowy wycinek wykresu kołowego lub sekcja wykresu kolumnowego

teraz jesteśmy gotowi na ostateczną wartość, jeśli funkcja and Nie Jest Prawdziwa. Więc umieść przecinek i wpisz wartość if FALSE. W tym przypadku chcemy wprowadzić rzeczywistą wartość punktu danych naszej komórki danych B2. Wpisz przecinek,a następnie B2)

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

Teraz naciśnij enter, a twoja wartość w komórce F2 powinna wyglądać następująco:

teraz, gdy mamy nasze formuły na miejscu, możemy skopiować je do naszego ostatniego punktu danych w kolumnach a i B. skopiuj więc tę formułę, a będzie ona wyglądać następująco:

zauważ, że dane punkty za 1/4 i 1/7 pokazują teraz wartość #N/A. A inne wartości są takie same, jak w naszym oryginalnym zestawie danych w kolumnie B. To jest tak wspaniałe! Co za fajna wskazówka Excela, aby pozbyć się odstających od naszego trendu.

teraz jesteśmy gotowi do zbudowania naszego wykresu Excel outlier.

4) Utwórz wykres odstający w Excelu

więc stwórzmy nasz wykres, podkreślając zakres komórek od E1: F11. Następnie przejdź do wstążki Wstaw i wybierz wykres liniowy ze znacznikami z przycisku linii:

Twój wykres powinien teraz wyglądać tak:

wygląda znacznie inaczej niż ten z wartościami odstającymi:

5) Dodaj linię trendu do wykresu, formułę trendu i wartość R-kwadrat

teraz, gdy mamy nasz wykres, potrzebujemy tylko kilku ostatnich rzeczy. Dodajmy linię trendu do naszego wykresu. Aby to zrobić, kliknij Wykres, a następnie kliknij linię wykresu. Stamtąd przejdź do wstążki układu i wybierz przycisk linia trendu, a następnie wybierz liniową linię trendu:

następnie wybierz „Linear” i „Display Equation on chart” oraz „Display R-squared value on chart” z okna dialogowego Format Trendline:

twój wykres będzie teraz wyglądał tak:

6) wygładź linię I Wyczyść Wykres śmieci

teraz wszystko, co musimy zrobić, to wyczyścić wykres i jesteśmy gotowi! Najpierw wybierz Wykres, a następnie wybierz legendę i naciśnij klawisz delete. Twój wykres powinien teraz wyglądać tak:

teraz musimy utworzyć wygładzony wykres liniowy. Aby to zrobić, wybierz Wykres, a następnie linię na wykresie. Następnie naciśnij klawisz CTRL + 1, aby wyświetlić okno dialogowe Seria formatowania. Stamtąd Wybierz Style linii z lewego menu i wybierz wygładzoną linię z opcji stylów linii:

twój końcowy wykres powinien teraz wyglądać tak:

nie ma dużej różnicy, ale gdy Twoje dane mają większe zmiany, będą wyglądać jeszcze bardziej gładko

.

teraz porównajmy go z pierwotnym żądaniem. Wygląda prawie dokładnie tak, jak chcieliśmy. Widać jednak, że dane od 3 do 5 stycznia są połączone i nie są łamane jak oryginalna próbka. Sam dla punktów danych między 1/6 a 1/8. Nie jestem pewien, jak ważne jest, aby nie łączyć tych linii, ale nie jest to możliwe w przypadku sposobu, w jaki Excel tworzy wykresy liniowe, gdy masz lukę danych wykorzystującą formułę.

jednak wróć jutro, gdzie pokażę Ci, jak zrobić wykres liniowy, aby pokazać lukę za pomocą formuł.

Video Tutorial

zobacz film pokazujący budowanie wykresu linii Excela, który ignoruje wartości odstające: