Excelでテスト結果、売上金額、作業時間などの数値データを集計すると、平均値だけでは分布の偏りや山の位置をつかみにくいことがあります。
そのような場面で役立つグラフがヒストグラムです。
ヒストグラムを使うと、数値がどの範囲に多く集まっているか、ばらつきは大きいか、極端に小さい値や大きい値があるかを視覚的に確認できます。
Excelでは、グラフ機能を使う方法と、分析ツールのヒストグラム機能を使う方法の二通りを選べます。
データの形や必要な集計表に応じて使い分けることが、見やすく正確な度数分布の作成につながります。
この記事で確認するポイント
・元データからヒストグラムをすばやく作る方法
・階級幅とビンの設定によって分布を読みやすくする方法
・データ分析アドインで度数分布表とグラフを同時に出力する方法
ここでは、1行目に見出しがある表を例に、Excelの操作手順、数式による集計、グラフの調整方法を順番に解説します。
なお、ヒストグラムは数値の連続性を扱うグラフであり、都道府県名や商品名のような文字列分類には通常の縦棒グラフが向いています。
エクセルでヒストグラムを作る方法【グラフ機能】
それではまず、Excelの標準グラフ機能を使ってヒストグラムを作る方法について解説していきます。
元データを選択して挿入タブから作成するため、度数分布表を先に用意しなくても分布の全体像を確認できます。
| A列 | B列 |
|---|---|
| 1 | テスト得点 |
| 2 | 58 |
| 3 | 63 |
| 4 | 71 |
| 5 | 76 |
| 6 | 82 |
この例では、A1セルに見出し、A2セル以降に得点を入力しています。
元データの選択
ヒストグラムを作る前に、数値が同じ単位で入力されているかを確認しましょう。
得点、金額、身長、処理時間のように、比較したい数値だけを一列または一行へ連続して配置するのが基本です。
空白セルやエラー値、文字列が途中に混ざると、意図した集計にならない場合があります。
A1セルに項目名があり、A2セルから最終行まで実数値が続く形にすると、後のグラフ選択や数式作成も行いやすくなります。
たとえばA1からA31までが対象なら、見出しを含めてA1:A31を選択します。
複数列のデータを選択すると系列ごとに処理されることがあるため、初めて作る場合は一列だけを対象にすると理解しやすいでしょう。
元データをコピーして別シートに加工する場合も、数値が文字列として保存されていないか確認が必要です。
セルの左上に緑色の三角形が出ている数値は、文字列として認識されている可能性があります。
該当セルを選択して警告アイコンから数値に変換するか、VALUE関数で数値化してください。
データ範囲に見出しを含めるかどうかは、選んだ作成方法によって確認画面が異なります。
標準グラフでは見出しを含めても問題ありませんが、分析ツールではラベルのチェック項目を正しく設定します。
挿入タブからのグラフ作成
続いて、選択済みのデータからヒストグラムを挿入していきます。
Excel上部のリボンで挿入タブを開き、グラフグループにある統計グラフの挿入を選択します。
表示されたグラフ種類の中からヒストグラムをクリックすると、現在のワークシートへグラフが配置されます。
棒が隙間なく横に並ぶ形が、ヒストグラムの見た目です。
各棒は階級と呼ばれる数値の区間を示し、棒の高さはその区間に入ったデータ数、つまり度数を表します。
たとえば60から70の棒が高ければ、その範囲の得点者が多いと判断できます。
縦棒グラフと似ていますが、ヒストグラムでは横軸の数値範囲に意味があり、棒と棒の間を原則として空けない点が重要です。
グラフが作成されたら、タイトルをクリックして「テスト得点の分布」など、内容がわかる名称に変更します。
タイトルには対象期間や単位を含めると、後から見返したときにも誤解が生じにくくなります。
横軸が連続した数値に見えず、項目名が一つずつ並んでいる場合は、通常の縦棒グラフを挿入している可能性があります。
グラフの種類の変更から統計グラフのヒストグラムを選び直してください。
グラフの配置と初期確認
続いて、作成直後のグラフで確認したい表示項目を見ていきます。
グラフ枠をドラッグすると位置を移動でき、四隅や辺のハンドルをドラッグすると大きさを変えられます。
棒の本数が少なすぎる場合は特徴が見えにくく、反対に多すぎる場合は細かな変動だけが目立つことがあります。
最初に表示された状態で、山が一つか複数か、左右どちらかへ偏っていないか、外れた位置に棒がないかを確認しましょう。
ヒストグラムは平均値だけでは見落としやすい分布の形を把握するための道具です。
平均点が同じでも、全員が平均付近に集まるケースと、高得点者と低得点者に分かれるケースでは改善策が変わります。
グラフの横幅を十分に取り、横軸のラベルが読める状態に調整すると、階級ごとの比較がしやすくなります。
【操作のポイント】最初は標準設定でグラフを作成し、次に横軸の階級幅を調整すると、設定ミスを切り分けやすくなります。
ヒストグラムの階級幅とビンの設定
続いては、ヒストグラムの見え方を左右する階級幅とビンの設定を確認していきます。
Excelでは横軸の書式設定から、階級幅、ビンの数、アンダーフロービン、オーバーフロービンを変更できます。
| 階級 | 範囲 | 度数の例 |
|---|---|---|
| 50点台 | 50以上60未満 | 3 |
| 60点台 | 60以上70未満 | 7 |
| 70点台 | 70以上80未満 | 11 |
| 80点台 | 80以上90未満 | 6 |
階級幅を10点にすれば、得点データは10点ごとの区間にまとめられます。
横軸の書式設定
続いて、横軸の設定画面を開く操作について解説していきます。
ヒストグラムの横軸に表示されている数値をクリックし、右クリックメニューから軸の書式設定を選択します。
画面右側に作業ウィンドウが表示されたら、軸のオプションを開きます。
ここには自動、ビンの幅、ビンの数などの設定項目が表示されます。
自動のままでもグラフは作成できますが、報告書や資料で区間を統一したい場合には手動設定が便利です。
階級幅は、グラフから何を読み取りたいかに合わせて決める値です。
得点なら5点または10点、売上なら1万円または10万円、時間なら5分または10分といったように、業務で意味を持つ単位を基準に考えるとよいでしょう。
設定を変更すると棒の本数と高さがすぐに再計算されるため、数値を入力した後にグラフの変化を確認できます。
横軸の境界値が意図と異なるときは、データの最小値と最大値を確認してから設定値を見直します。
階級幅とビンの数
続いて、階級幅とビンの数の違いを確認していきます。
階級幅は一つの区間の広さを直接指定する方法です。
たとえば得点の範囲が40点から100点で、階級幅を10にすると、おおむね40点台、50点台、60点台のような区分になります。
一方のビンの数は、横軸全体を何分割するかを指定する方法です。
最小値と最大値の幅が同じでも、データを追加すると一つの区間の広さが変わることがあります。
階級幅の考え方
階級幅 ＝ 隣り合う階級の境界値の差
例として、60以上70未満と70以上80未満なら、階級幅は10です。
定例資料で毎月の分布を比較する場合は、階級幅を固定する方法が向いています。
比較対象ごとに階級幅が変わると、棒の高さや山の位置を単純に比較しにくくなります。
反対に、一度だけデータの概況を探索したい場合は、ビンの数を変えながら見やすい状態を探す方法も有効です。
データ数が少ないのに区間を細かくしすぎると、空の棒が多くなって傾向を判断しづらくなります。
データ数が多い場合に区間を大きくしすぎると、複数の山が一つに見えるかもしれません。
外れ値をまとめるビン
続いて、極端な数値を含むデータで使えるアンダーフロービンとオーバーフロービンを確認していきます。
アンダーフロービンは指定値以下のデータを一つの階級にまとめる機能です。
オーバーフロービンは指定値以上のデータを一つの階級にまとめます。
たとえば通常の売上が1万円から10万円に集中している一方で、特別受注の100万円が数件ある場合、そのまま表示すると横軸が広がりすぎることがあります。
このようなとき、オーバーフロービンを20万円に設定すると、20万円以上の値を一つの棒に集約できます。
外れ値を消すのではなく、別の階級としてまとめて分布の中心を読みやすくする操作です。
ただし、外れ値が業務上の重要な異常値である場合は、まとめた後も元データを必ず確認してください。
品質管理では、極端な測定値が入力ミスなのか、工程上の問題なのかを判断する必要があります。
【操作のポイント】継続的に比較するグラフでは、ビンの数ではなく階級幅を固定し、外れ値の扱いも同じ基準にそろえましょう。
データ分析アドインによる度数分布表
続いては、分析ツールを使って度数分布表とヒストグラムを出力する方法を確認していきます。
この方法は、各階級の度数をワークシート上の表として残したい場合や、集計根拠を確認したい場合に便利です。
| A列 | C列 | |
|---|---|---|
| 1 | 作業時間 | 階級上限 |
| 2 | 18 | 20 |
| 3 | 24 | 30 |
| 4 | 31 | 40 |
| 5 | 37 | 50 |
この例ではA列に元データ、C列に各階級の上限値を入力します。
分析ツールの有効化
それではまず、データ分析コマンドを表示するためのアドイン設定について解説していきます。
Excelのファイルタブからオプションを開き、左側のメニューでアドインを選択します。
画面下部の管理でExcelアドインを選び、設定ボタンをクリックします。
アドインの一覧で分析ツールにチェックを入れ、OKを押すと機能が追加されます。
設定後はデータタブの右側にデータ分析というコマンドが表示されます。
分析ツールはExcelの標準機能に含まれますが、初期状態ではリボンに表示されていないことがあります。
会社の端末などで設定変更が制限されている場合は、管理者のルールに従ってください。
Microsoft 365版、Excel 2021、Excel 2019などでは画面構成に多少の差がありますが、ファイル、オプション、アドインという流れは共通しています。
有効化済みなのにデータ分析が見つからない場合は、Excelを再起動すると表示が更新されることがあります。
入力範囲とデータ区間
続いて、分析ツールで指定する入力範囲とデータ区間について解説していきます。
データタブのデータ分析をクリックし、一覧からヒストグラムを選んでOKをクリックします。
入力範囲には元データの範囲を指定します。
見出しを含めた場合は、ラベルにチェックを入れます。
データ区間には、階級の上限値を入力したセル範囲を指定します。
データ区間へ20、30、40、50と入力した場合、Excelは20以下、20超30以下、30超40以下という順で度数を集計します。
分析ツールのデータ区間は階級の上限値として扱われる点が、標準グラフの階級幅設定との大きな違いです。
たとえば0から10、10から20という区分を作りたい場合は、10、20、30のように境界の上側をデータ区間へ入力します。
上限値は昇順に並べてください。
降順だったり重複があったりすると、期待どおりの度数分布表になりません。
出力先と累積比率
続いて、結果の出力先と累積比率の使い方を確認していきます。
ヒストグラムの設定画面では、出力先を新規ワークシート、または出力範囲から選べます。
作業中のデータを残したい場合は、新規ワークシートを選ぶと安全です。
グラフ出力にチェックを入れると、度数分布表とヒストグラムが同時に作成されます。
累積比率にチェックを入れると、各階級までに含まれるデータの割合も出力できます。
累積比率の計算
累積比率 ＝ その階級までの累積度数 ÷ データ全体の件数
100件中80件が80点以下なら、80点までの累積比率は80パーセントです。
累積比率を見れば、全体の何パーセントが基準値以下または基準値以内なのかを確認できます。
納期、検査値、対応時間のように、基準を満たす割合を確認したいデータでは特に役立ちます。
出力後のグラフは通常のグラフと同様に、タイトル、軸ラベル、色、表示位置を編集できます。
【操作のポイント】分析ツールでは、入力範囲とデータ区間の指定先を取り違えないようにし、出力先は元データを上書きしない位置に設定しましょう。
COUNTIFS関数による度数分布表
続いては、関数を使って自分で度数分布表を作成する方法を確認していきます。
数式を残しておけば元データが増減したときに再計算されるため、定期更新する集計表に向いています。
| C列 | D列 | E列 | |
|---|---|---|---|
| 1 | 下限 | 上限 | 度数 |
| 2 | 50 | 59 | 数式を入力 |
| 3 | 60 | 69 | 数式をコピー |
| 4 | 70 | 79 | 数式をコピー |
ここでは元データがA2:A31にあり、C列とD列に各階級の下限と上限を入力したものとします。
下限と上限の入力
それではまず、集計の基準になる階級表について解説していきます。
C1に下限、D1に上限、E1に度数と入力します。
C2に50、D2に59、C3に60、D3に69というように、重複も空白もない区間を並べます。
整数の得点を集計するなら50から59、60から69の表記で問題ありません。
小数を含む測定値では、50以上60未満、60以上70未満のような境界ルールをあらかじめ決める必要があります。
隣り合う階級で同じ境界値を二重に数えないことが、度数分布表を正しく作る基本です。
50以上60未満と60以上70未満なら、60は後ろの階級だけに含められます。
表の見出しには単位も記載すると、数値の意味を読み間違えにくくなります。
階級の例
50以上60未満
60以上70未満
70以上80未満
このように下限を含め、上限を含めないルールを統一すると計算が明確になります。
COUNTIFS関数の入力
続いて、最初の度数を求めるCOUNTIFS関数について解説していきます。
E2セルへ次の数式を入力します。
=COUNTIFS($A$2:$A$31,”>=”&C2,$A$2:$A$31,”<=”&D2)
COUNTIFS関数は複数の条件を同時に満たすセル数を数える関数です。
最初の条件では、A2:A31のうちC2セルの下限以上である値を数えます。
二つ目の条件では、同じ範囲のうちD2セルの上限以下である値を数えます。
この二条件を両方満たすデータだけがE2セルの度数になります。
データ範囲のA2:A31にはドル記号を付けて絶対参照にし、階級セルのC2とD2は相対参照のままにします。
この設定により、数式を下方向へコピーしても元データ範囲は固定され、階級だけがC3とD3、C4とD4へ変化します。
小数データで上限を含めないルールを使うなら、二つ目の条件を「<」&D2へ変更してください。
最終階級だけ最大値を含めたい場合は、最後の行だけ上限以下の条件にするなど、データに合わせて調整します。
オートフィルとグラフ化
続いて、数式を下の行へコピーし、作成した表をグラフ化する方法を確認していきます。
E2セルを選択すると、セル右下に小さな四角形のフィルハンドルが表示されます。
フィルハンドルを下方向へドラッグすると、数式が各階級の行にコピーされます。
元データ数と度数の合計が一致するかを確認すると、集計漏れや重複計上を見つけやすくなります。
確認用の数式
=SUM(E2:E6)
この合計が =COUNT(A2:A31) の結果と一致するかを確認します。
度数の合計と元データ件数が一致しない場合は、階級の空白、境界値の重複、対象範囲の誤りを確認しましょう。
グラフ化するときは、階級名と度数の列を選択し、挿入タブから集合縦棒グラフを作成します。
作成後に棒を右クリックしてデータ系列の書式設定を開き、要素の間隔を0パーセントに近づけると、ヒストグラムに近い見た目になります。
ただしこの方法は度数分布表を基にした縦棒グラフであるため、横軸を数値軸として自動再計算する標準ヒストグラムとは性質が異なります。
【操作のポイント】関数で作った表は、階級の定義と数式を確認できるため、報告書の根拠を残したい場面で便利です。
ヒストグラムの見方と分布の確認
続いては、完成したヒストグラムから分布を読み取る視点を確認していきます。
棒の高さだけを見るのではなく、山の位置、広がり、左右の偏り、離れた棒の有無を合わせて確認しましょう。
| 分布の特徴 | 読み取りの例 |
|---|---|
| 中央に山がある | 多くの値が標準的な範囲に集中 |
| 横に広い | ばらつきが大きい可能性 |
| 片側に長い尾 | 偏りや外れ値の可能性 |
| 山が二つある | 異なる集団が混在している可能性 |
山の位置と最頻階級
それではまず、最も高い棒が示す最頻階級について解説していきます。
最頻階級とは、データ数が最も多い階級です。
テスト得点なら受験者が最も多く集まった得点帯、作業時間なら最も多く発生した時間帯を示します。
最頻階級は、平均値とは異なり、実際に最も多く出現した範囲を直接示します。
たとえば平均が70点でも、70点台の棒が最も高いとは限りません。
高得点と低得点に分かれた集団では、平均付近にデータが少ないこともあります。
最頻階級を確認したら、その階級が業務上の目標範囲や許容範囲に入っているかを比較します。
標準値から離れている場合は、データの発生条件、担当者、製品ロット、時期などで分けて追加分析すると原因を探りやすくなります。
ばらつきと偏り
続いて、棒の広がりと左右の偏りを見る方法を確認していきます。
棒が狭い範囲に集中していれば、数値のばらつきは比較的小さいと考えられます。
横軸の広い範囲へ棒が分散していれば、同じ種類のデータでも結果に差が出ている状態です。
右側だけに長く棒が続く分布は、右に裾が長い分布と呼ばれます。
たとえば通常は短時間で終わる作業でも、一部で非常に長い処理時間が発生する場合に見られます。
偏りがあるデータでは、平均値だけで代表値を判断すると実態とずれることがあります。
中央値や最頻値も併せて確認すると、典型的な状態を把握しやすくなります。
ExcelではAVERAGE関数で平均値、MEDIAN関数で中央値、MODE.SNGL関数で最頻値を求められます。
代表値を求める数式の例
=AVERAGE(A2:A31)
=MEDIAN(A2:A31)
=MODE.SNGL(A2:A31)
これらの数値とヒストグラムを並べて確認すると、分布の特徴を説明しやすくなります。
外れ値と複数の山
続いて、外れ値や複数の山が見える場合の考え方を確認していきます。
ほかの棒から大きく離れた場所に少数の棒がある場合は、外れ値の可能性があります。
ただし、外れ値は必ずしも削除すべきデータではありません。
入力ミス、測定ミス、特殊な条件、重大な異常など、原因を確認するきっかけになります。
外れ値を処理する前に、元データの記録内容と発生理由を確認することが大切です。
また、山が二つ以上ある分布は、異なる条件のデータが混在している可能性を示します。
午前と午後、経験者と未経験者、機械Aと機械Bのデータを一つにまとめた結果かもしれません。
この場合は条件ごとに列を分け、ヒストグラムを別々に作成すると差が明確になります。
【操作のポイント】ヒストグラムで異常らしい棒を見つけたら、削除を急がず、元データの条件や入力内容を確認して原因を切り分けましょう。
ヒストグラム作成時のエラー対処
続いては、ヒストグラムの作成時によくある問題と対処方法を確認していきます。
グラフが作れない、棒の本数がおかしい、期待した範囲で集計されないときは、元データ、階級設定、Excelの機能状態を順に確認します。
| 症状 | 確認する内容 |
|---|---|
| グラフが作成できない | 数値が文字列になっていないか |
| 棒が極端に多い | 階級幅またはビンの数 |
| 度数の合計が合わない | 境界値と対象範囲 |
| データ分析がない | 分析ツールアドイン |
数値が文字列になっている場合
それではまず、数値が文字列として扱われる場合の対処を解説していきます。
セル内に見た目は数値でも、先頭にアポストロフィが付いていたり、全角スペースが混ざったりすると、Excelは文字列として扱うことがあります。
文字列の数値はグラフやCOUNTIFS関数の条件集計から外れることがあります。
対象範囲を選択して警告アイコンから数値に変換するか、区切り位置機能を使って変換します。
別のセルにVALUE関数を入力して数値化する方法もあります。
セルの表示形式を数値に変えただけでは、文字列そのものが数値へ変換されない場合があります。
変換後は、SUM関数で合計できるか、セルが右寄せで表示されるかなどを確認しましょう。
CSVから取り込んだデータでは、桁区切りや単位文字が混在していることもあります。
その場合は置換機能で不要な文字を除去してから、数値化するのが確実です。
階級設定が意図と異なる場合
続いて、棒の本数や境界が意図と異なる場合の確認方法を解説していきます。
標準ヒストグラムでは横軸を右クリックし、軸の書式設定で階級幅を見直します。
分析ツールではデータ区間に入力した値が上限値になるため、階級表を再確認してください。
たとえば20、30、40を入力した場合、20以下、20超30以下、30超40以下という集計になります。
0から20未満にしたい場合は、元データの最小値と境界値の扱いを踏まえて表を作る必要があります。
境界値をどちらの階級へ含めるかを統一しないと、度数の合計がずれたり、同じ値が二重に集計されたりします。
特に小数を含む品質測定データでは、上限以下と未満の違いが重要です。
階級幅を途中で変更した後は、以前に作成した報告資料と比較できるかも確認しましょう。
グラフの種類と更新
続いて、グラフの種類やデータ更新に関する問題を確認していきます。
棒と棒の間に大きな隙間があり、横軸が商品名などの項目として並ぶ場合は、通常の縦棒グラフになっている可能性があります。
グラフデザインタブのグラフの種類の変更から、統計グラフのヒストグラムを選択してください。
元データへ新しい行を追加してもグラフに反映されない場合は、参照範囲が固定されていることがあります。
元データをテーブルとして書式設定しておくと、行の追加に合わせて範囲を拡張しやすくなります。
テーブル化の基本操作
元データ範囲を選択し、挿入タブからテーブルを選択します。
先頭行をテーブルの見出しとして使用するにチェックを入れると、A1の項目名を維持できます。
テーブルを基にグラフを作ると、定期的なデータ追加に対応しやすくなります。
【操作のポイント】エラーが出たときは、まず元データが本当の数値かを確認し、次に階級の境界、最後にグラフ種類と参照範囲を確認しましょう。
まとめ エクセルでヒストグラムを作る方法
Excelでヒストグラムを作る方法は、元データを選択して挿入タブの統計グラフから作成する方法が最も手軽です。
グラフ作成後に横軸の書式設定を開けば、階級幅、ビンの数、アンダーフロービン、オーバーフロービンを調整できます。
継続的な比較では階級幅を固定し、同じ基準でグラフを作成することが重要です。
度数分布表も必要な場合は、分析ツールのヒストグラム機能を利用すると、集計表とグラフを同時に出力できます。
分析ツールでは、データ区間が階級の上限値になる点を理解しておきましょう。
元データが更新される集計では、COUNTIFS関数で度数を計算する方法も便利です。
数式をコピーして使う場合は、元データ範囲を絶対参照にし、階級セルは相対参照にする設定が基本になります。
ヒストグラムからは最頻階級、ばらつき、偏り、外れ値、複数の山といった特徴を確認できます。
平均値だけでは分からないデータの姿を把握し、業務の改善や説明資料の作成に役立てましょう。
