さて、私は金曜日にこのExcelチャー どうしたの? 私はあなたの解決策が私のものよりも優れていると確信しています。 これはExcelフォーラムの質問からの挑戦でした：

“グラフの外れ値を排除する方法

いくつかのグラフを表示するとき、時々私はいくつかの外れ値を無視 外れ値データまたはx軸上の対応するxをクリックすることが可能かどうかを知りたい場合は、この外れ値データなしでグラフが更新されます。 データセットに移動して外れ値を削除できることはわかっていますが、グラフ上で単純化したいと考えています。 本当にどうも。.このユーザーが投稿した画像は次のとおりです。

グラフには2つの外部データポイントがあります。 1月4日と1月7日に開催された。 Excelユーザーがデータポイントを手動で削除すると、画像の下部に最終的なグラフが表示されます。また、トレンドライン式とR値を持つトレンドラインを追加する必要がありますが、これは手動のステップになる可能性があります。 データは、2013年の1月1日から1月10日までの期間に適用されます。 だから、これらの仮定に行くことができます。では、これらのデータポイントを手動で削除せずに、1月4日と1月7日のデータポイントを削除して、最終的なグラフを作成するにはどうすればよいで それを取得することができます！ 以下に、私が解決策を作成した方法についての簡単な内訳を示します。 その下には、このExcelソリューションの詳細なステップバイステップのチュートリアルがあります。 その下には、このExcelのヒントのビデオデモが表示されます。 最後に、その下には、あなたがデータとチャート技術で周りに計画するためにダウンロードすることができます私のスプレッドシートのコピーを見つ

まず、別のユーザーがこの問題にどのように取り組んだかをお見せしましょう。 ピートはこの問題を回避する独創的な方法を持っていました。 彼はデータ点を取り、線の傾きに基づいて式を作成します。 ここに彼自身の言葉で彼の解決策があります：

ピートから：”私はあなたの新しい金曜日の挑戦で遊んでいた、と私は結果に別のスピンを思い付いた。 私のグラフには、元のデータと、outlyersが削除された新しいデータが表示されます。 数式を表示するには、シートのロックを解除する必要があります。

基本的に、元のデータを取得し、SLOPE関数を使用してデータの傾きを見つけ、y=MX+B線の代数式を使用してX値に基づいて理論線と比較しました。この式と計算された傾きを使用すると、Bを解くことができます。今、実際の値と理論値がデータ検証ドロップダウンセルによって選択された%によって異なる場合、#N/Aエラーを返すためにand IF式を使用しました。 このようにして、ユーザーはどのパーセントでoutlyersを削除するかを選択できます。 データポイント「2」が計算された理論値から710％離れているため、ユーザーが元のデータを表示できるようにするには、500％と100％を含める必要がありました。”

ここで彼の解決策をチェックしてください：Petes Outlyer Solution

今ここに外れ値を表示しないExcel行を作成するための私の基本的な解決策があります。 私の高度な解決策を見るために明日戻ってきてください。

内訳

1）チャートデータ範囲を作成します

2）外れ値許容度レベルとしてセルを指定します

3）チャートデータ範囲の外れ値式を作成します

4）Excelで外れ値チャートを作成します

5）チャートにトレンドラインを追加します,トレンドライン式とR二乗値

6）線を滑らかにし、チャートのジャンクをクリーンアップ

ステップバイステップ

1）チャートのデータ範囲を作成

さて、セルA1：b11に元のデータ範囲を作成しましょう。 今、それがどのように見えるかを確認するためにマーカーを使用してExcelで簡単な折れ線グラフを作成することができます。 元の質問から見た最初のグラフとほぼ同じように見えます。

唯一の違いは、私たちの行が平滑化されていないということです。 このチュートリアルの最後のステップでは、この変更を行う方法を説明します。 しかし、元のグラフが平滑化された線でどのように見えるかは次のとおりです:

2）外れ値の許容値としてセルを指定します

さて、私のソリューションは、外れ値の許容値が何であるかを指定するためにスプレッドシートセルを使用しています。 そのため、スプレッドシートのセルD3を許容レベルとして指定しました。 データを見ると、ポイントが約7単位異なる場合、Excelユーザーの例ではデータポイントが外れ値として指定されているようです。だから私はセルD3に7の値を入れました:

外れ値式を作成するときに、次のステップでセルD3を使用します。3）チャートデータ範囲の外れ値式を作成する

最初に、日付をセルE2に入れましょう。 これは、セルE2に=A2を入れるのと同じくらい簡単です。

さて、これはすべてを動作させるステップです。 私たちがする必要があるのは、現在のデータポイントを現在のデータポイントの上下のデータポイントと比較することです。 比較すると、現在のデータポイントがセルD3で設定した許容レベルの外側にあるかどうかがわかります。 基本的には、上の1つのセルのデータポイントから現在のデータポイントを減算し、その差がセルD3の許容値より大きいかどうかを確認します。 次に、現在のデータポイントの下のセルに対してこの手順を繰り返します。 両方のデータポイントが許容レベルより大きい場合、現在のセルにNA（）を配置します。 両方の値が許容範囲外でない場合は、現在のデータポイントの値をこのセルに配置します。 差が負になる可能性があるため、この減算を絶対値関数でラップする必要があります。 セルF2では、この式を入れてみましょう:=IF(AND(ABS(B2-B1)>ABS D$3,ABS(B2-B3)>$d$3),NA(),B2)

>>>

式の内訳は次のとおりです。

最初にIf式から始めるので、セルf2に移動して次のように入力します。

=if(

値を比較する必要があるため、その値に基づいてこのセルに2つの値のいずれかを配置します

上で説明したように、現在のデータポイントを上記のセルと比較する必要があります。以下のセル。 また、両方の比較が許容範囲外の場合は、データポイントを表示したくありません。 したがって、”BOTH”比較を扱っているので、AND関数を使用する必要があるため、次のように入力します。

=IF(AND(

先に述べたように、データポイントを減算すると最終値が負になることがあります。 そして、負の値は常に許容値を下回っているため、許容値レベルが正の単位であるため、差が常に正の単位であることを確認する必要があります。 常に肯定的なものにするには、比較を絶対値関数でラップする必要があるため、次にABS関数を入力してみましょう。

=IF(AND(ABS(

実際に1番目の比較値 これを行うには、現在のデータポイントの上の値で現在の値を減算し、セルD3の許容レベルよりも大きいかどうかを確認します。 したがって、次のように入力します。

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

D3と絶対参照を行ったことに注意してください。 絶対参照が何であるかわからない場合は、この投稿をチェックしてください：

Excelダッシュボードテンプレートの数式の範囲を参照する

これはAND関数の最初の部分であり、and関数はカンマで区切られているので、次の比較を行います。 したがって、カンマの後に次の比較を入力します。 次の比較は、現在のデータポイントと次のデータポイントを比較することです。 だから、それを入力してみましょう:=IF(AND(ABS(B2-B1)>ABS D$3,ABS(B2-B3)>$D$3

これが心配する必要がある最後の比較なので、AND関数を右括弧で終了できます。 今、私たちはカンマを入れて、AND基準の両方が真である場合に何をすべきかを決定することができます。 私たちの場合、データポイントの両方の差が許容誤差よりも大きい場合は、そこにNA（）関数を配置する必要があります。 だから、真の値の場合はそれを入力してみましょう:IF(AND(ABS(B2-B1)>ABS D$3,ABS(B2-B3)>D D$3),NA()

NA()関数の詳細を知りたい場合は、この記事をチェックしてください。

ゼロ円グラフスライスまたは積み重ねられた縦棒グラフを非表示にする方法section

and関数がtrueでない場合、最後の値の準備が整いました。 したがって、カンマを入れて、IF FALSE値を入力します。 この場合、データセルB2の実際のデータポイント値を入力します。 したがって、カンマを入力してからB2)

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

enterキーを押すと、セルF2の値は次のようになります。>$D$3),NA(),B2)

image

式が整ったので、列aとbの最後のデータポイントにコピーすることができます。

1/4と1/7のデータポイント現在、#N/aの値が表示されています。 そして、他の値は、列Bの元のデータセットで見られるのと同じです。 私たちの傾向から外れ値を取り除くためにどのようなクールなexcelのヒント。

今、私たちはすべて私たちのExcelの外れ値チャートを構築するために設定されています。4）Excelで外れ値グラフを作成する

だから、E1：F11のセル範囲を強調表示してグラフを作成しましょう。 次に、挿入リボンに移動し、ラインボタンからマーカー付きの折れ線グラフを選択します。

チャートは次のようになります:

外れ値を持つものとは大きく異なります。

5）チャートにトレンドライン、トレンドライン式、R二乗値を追加

今、私たちはチャートを持っていることを、我々はちょうどいくつかの最後のものが必要です。 グラフにトレンドラインを追加しましょう。 これを行うには、グラフをクリックし、グラフの線をクリックします。 そこから、レイアウトリボンに移動し、トレンドラインボタンを選択し、線形トレンドラインを選択します:

次に、トレンドラインの形式ダイアログボックスから”線形”と”チャート上の式を表示”と”チャート上のR二乗値を表示”を選択します。