MisaNeco Works ブログ

2026-10-25・フリーランス / Excel

Excelで外れ値を見つける方法｜平均がおかしいときは四分位（QUARTILE.INC）と箱ひげ図で確かめる

この記事には広告（アフィリエイトリンク）を含みます。

月の売上をExcelで集計したら、1日あたりの平均がいつもより2万円も高い。「今月は調子がよかったのかな」と思う前に、一度だけ確かめてほしいことがあります。

0が1つ多い入力ミスが、1件まざっていないかです。

平均は、たった1件の大きな数字で大きく動きます。この記事では、売上や経費をExcelで集計している個人事業主や事務の方に向けて、そんな「おかしな1件」を見つける手順をまとめました。使うのは、四分位（QUARTILE.INC）、条件付き書式、箱ひげ図の3つです。

1. 平均と中央値の違い

例として、架空の20日分の売上を使います。 8日目の売上は本当は46,000円ですが、0を1つ多く打って 460,000円 と入れてしまった、という想定です。

A列に日付、B列に売上を入れます（1行目は見出し）。

行 A（日付） B（売上・円）
2 10/1 52,000
3 10/2 47,000
4 10/3 55,000
5 10/4 49,000
6 10/5 58,000
7 10/6 44,000
8 10/7 51,000
9 10/8 460,000（本当は46,000）
10 10/9 53,000
11 10/10 50,000
12 10/11 57,000
13 10/12 48,000
14 10/13 54,000
15 10/14 42,000
16 10/15 56,000
17 10/16 50,000
18 10/17 45,000
19 10/18 53,000
20 10/19 60,000
21 10/20 49,000

入力ミスがある場合とない場合で、平均と中央値をくらべます。

正しいデータ 入力ミスあり 差
平均（AVERAGE） 50,950円 71,650円 +20,700円
中央値（MEDIAN） 50,500円 51,500円 +1,000円

平均は、20件をすべて足して20で割った数です。1件が41万円多いと、平均は約2万円上がります。中央値は、小さい順に並べたときの真ん中の数です。端の1件がどれだけ大きくても、ほとんど動きません。

平均と中央値が大きく離れていたら、どこかに極端な数字がある、という目安になります。

2. QUARTILE.INCで四分位と上限・下限を出す

次に、「どこからが極端か」を数字で決めます。よく使われるのが、四分位を使う方法です。

下限より小さい、または上限より大きい数を「外れ値の候補」とします。 1.5倍という数は、広く使われている目安です。決まりではありません。

式を入れる

D列に名前、E列に式を入れます。

セル 内容 E列に入れる式 この例の結果
D1 / E1 Q1 =QUARTILE.INC(B2:B21,1) 48,750
D2 / E2 中央値 =MEDIAN(B2:B21) 51,500
D3 / E3 Q3 =QUARTILE.INC(B2:B21,3) 55,250
D4 / E4 IQR =E3-E1 6,500
D5 / E5 下限 =E1-1.5*E4 39,000
D6 / E6 上限 =E3+1.5*E4 65,000
D7 / E7 平均 =AVERAGE(B2:B21) 71,650

上限は65,000円です。460,000円は上限を大きくこえているので、外れ値の候補になります。下限の39,000円より小さい数はありません。

Q1が48,750になる理由

QUARTILE.INCは、小さい順に並べたときの「1 ＋（件数 − 1）× 割合」番目の数を出します。間にあるときは、前後の数の間を比例で分けます。

似た関数に QUARTILE.EXC があります。計算のしかたが少し違うので、同じデータでも値が変わります（この例ではQ1が48,250、Q3が55,750）。表の中では、どちらか一方にそろえてください。この記事では QUARTILE.INC を使います。

日本語版のExcelでも、関数名は英語のまま入力します。Googleスプレッドシートでも、同じ式が使えます。

3. 外れ値に自動で色を付ける（条件付き書式）

20件なら目で探せますが、何百行もあると見落とします。上限・下限の外にあるセルに、自動で色が付くようにします。

使う式はこれです。

=OR(B2<$E$5,B2>$E$6)

「B2が下限（E5）より小さい、または上限（E6）より大きい」という意味です。 $E$5 と $E$6 には $ を付けて、どの行でも同じセルを見るようにします。B2 には付けません。

Excel（Windows）

  1. B2:B21 を選ぶ
  2. 「ホーム」タブ →「条件付き書式」→「新しいルール」
  3. 「数式を使用して、書式設定するセルを決定」を選ぶ
  4. 数式の欄に =OR(B2<$E$5,B2>$E$6) を入れる
  5. 「書式」→「塗りつぶし」で薄い赤などを選び、「OK」→「OK」

Excel（Mac）

  1. B2:B21 を選ぶ
  2. 「ホーム」タブ →「条件付き書式」→「新しいルール」
  3. 「スタイル」で「クラシック」を選び、その下で「数式を使用して、書式設定するセルを決定」を選ぶ
  4. 数式の欄に =OR(B2<$E$5,B2>$E$6) を入れる
  5. 「書式」で「ユーザー設定の書式」を選び、塗りつぶしの色を決めて「OK」

Googleスプレッドシート

  1. B2:B21 を選ぶ
  2. メニューの「表示形式」→「条件付き書式」
  3. 右に出る画面の「書式ルール」で「カスタム数式」を選ぶ
  4. =OR(B2<$E$5,B2>$E$6) を入れる
  5. 「書式設定のスタイル」で塗りつぶしの色を選び、「完了」

この例では、B9（10/8の460,000円）だけに色が付きます。データの行を増やしたときは、式の B2:B21 と、色を付ける範囲の両方を広げてください。

4. 箱ひげ図で見る

数字の表だけでなく、図で見ると「1件だけ飛び出している」のがすぐわかります。そのための図が箱ひげ図です。Excel 2016以降で作れます。

作り方（Excel）

  1. B1:B21（見出しと売上）を選ぶ
  2. 「挿入」タブ →「統計グラフの挿入」→「箱ひげ図」

図の読み方

Excelの箱ひげ図は、設定（データ系列の書式設定の「四分位数の計算」）によって、箱の位置が表の QUARTILE.INC の値と少しずれることがあります。外れ値を見つけるという目的なら、大きな問題にはなりません。正確な数字は、2.の表で確認してください。

なお、460,000円の点があると縦の目盛りが大きく広がり、箱がつぶれて見えます。元の書類を確認して数字を直すと、箱が見やすくなります。

5. 外れ値＝必ず間違い、ではありません

色が付いたセルは、あくまで「確認する候補」です。

消したり直したりする前に、元の書類（請求書・領収書・通帳の記録など）を確認してください。 入力ミスなら正しい数字に直します。本当の取引なら、そのまま残します。必要なら、備考の列に「大口」などと書いておくと、あとで見返したときに迷いません。

外れ値を除いた平均を出したいときも、元のデータは消さないでおくのがおすすめです。

入力ミスそのものを減らしたいときは、入力規則（プルダウン）で入力ミスを減らす方法もあわせてどうぞ。

広告・PR

6. チェックリスト

まとめ

売上と経費の表の作り方は、個人事業主の売上と経費をExcelで管理する最低限の列にまとめています。値付けの計算なら、利益率と上乗せ率の違いも参考になると思います。

※この記事の計算例は検算しています。例の売上は説明用の架空の数字です。四分位の値は、Excelの QUARTILE.INC と同じ計算方法（位置 1＋(n−1)×p の比例配分）で確認しています。

あわせて使える無料ツール帳簿・領収書の保存期間チェッカー
家事按分 計算ツール
支払期日の計算ツール

新しい無料ツールや記事を、LINEでお知らせします
「ツール」「お金」「スマホ」と送ると、合うツールの案内が自動で届きます。

LINEで友だち追加Eternal Philosophy 公式LINE（EQUA・MisaNeco Works を運営）
関連記事利益率（粗利率）と上乗せ率（マークアップ）の違い｜値付けで損しないためのExcel・スプレッドシートの式
Excelで工期（稼働日）を数える｜WORKDAY.INTL・NETWORKDAYS.INTLの使い方と「開始日を1日目にする」コツ
中古車・中古バイクの原価と粗利の出し方｜仕入れ＋整備費＋諸費用、在庫日数と「長く売れていない台」の見つけ方