集計グラフは、何かが発生した頻度を表す集計マークの表です。 Microsoft Excel には多数の組み込みグラフの種類が用意されていますが、集計グラフのオプションはありません。幸いなことに、これは Excel の数式を使用して作成できます。
この例では、集計グラフを作成して、リスト上の各人が受け取った投票を視覚化したいと考えています。
集計システムを作成する
集計グラフは通常、4 本の線と、その後に続く 5 番目の集計の斜めの取り消し線で表示されます。これにより、視覚的に優れたグループ化が可能になります。
Excel でこれを再現するのは難しいため、代わりに 4 つのパイプ記号とハイフンを使用して値をグループ化します。パイプ記号は、US または UK キーボードのバックスラッシュ文字の上にある垂直線です。
したがって、5 つの各グループは次のように表示されます。
||||-
そして、単一のオカレンス (1) に対する単一のパイプ シンボルは次のように表示されます。
|
これらの記号をスプレッドシートのセル D1 と E1 に入力します。
数式を使用して集計グラフを作成し、これら 2 つのセルを参照して正しい集計マークを表示します。
5 つのグループを合計します
5 つのグループを合計するには、投票値を最も近い 5 の倍数に切り捨て、結果を 5 で割ります。 FLOOR.MATH という名前の関数を使用して値を丸めることができます。
セル D3 に次の数式を入力します。
=FLOOR.MATH(C3,5)/5
これにより、C3 の値 (23) が最も近い 5 の倍数 (20) に切り捨てられ、その結果が 5 で除算され、答えは 4 になります。
残ったシングルを合計する
次に、5 つのグループの後に何が残るかを計算する必要があります。このために、MOD 関数を使用できます。この関数は、2 つの数値を除算した後の剰余を返します。
セル E3 に次の数式を入力します。
=MOD(C3,5)
数式で集計グラフを作成する
これで、5 人グループの数と、集計グラフに表示するシングルの数がわかりました。これらを 1 行の集計マークに結合するだけです。
これを行うには、REPT 関数を使用して各文字の出現を必要な回数繰り返し、それらを連結します。
セル F3 に次の数式を入力します。
=REPT($D$1,D3)&REPT($E$1,E3)
REPT 関数は、指定された回数だけテキストを繰り返します。この関数を使用して、グループとシングルの式で指定された回数だけタリー文字を繰り返しました。また、アンパサンド (&) を使用してそれらを連結しました。
ヘルパー列を非表示にする
集計グラフを完成させるために、ヘルパー列 D と E を非表示にします。
列 D と E を選択して右クリックし、[非表示] を選択します。
完成した集計グラフは、各人が受け取った投票数を視覚的にわかりやすく表示します。
