Excelでデータの各値とその個数を生成するには

質問

Excelでデータの各値とその個数を生成するような機能はないでしょうか?

例えば、Excelの
A列に1,2,3,2,5,3,2という数値が入力されていた場合に、
B列に1,2,3,4,5という値と、
C列に各値の個数である1,3,2,0,1という値が
生成されるような機能はありますでしょうか。
できれば、A列のデータから、
添付図のグラフを生成させたいです。

なお、上記の例のA,B,C列は一例で、データの数も7個だけですが、
実際には、数百あり、桁数も1～4桁の値です。

よろしくお願いします。(Windows10,Excel2016)

回答No.6

HohoPapa · ベストアンサー

VBAを使ってもいいのであれば、
後記のコードはいかがでしょうか？

A列の1行目から開始して数百行のレコードがあり、
その集計結果をB,C列に書き出すコードです。
シート名：Sheet1
最大５万行を想定しています。

Option Explicit

Sub RecCounter()

　Dim SQL As String
　Dim cn As Object
　Dim rs As Object

　'SQL全文を組み立て、実行
　SQL = "SELECT F1,count(F1) as RecCnt" & vbCrLf
　SQL = SQL & "FROM [" & "Sheet1" & "$A1:A50000]" & vbCrLf
　SQL = SQL & "GROUP BY F1" & vbCrLf
　SQL = SQL & "ORDER BY F1" & vbCrLf

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=No;IMEX=1"
　cn.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name
　rs.Open SQL, cn

　'結果セットを出力
　Range("B1").CopyFromRecordset rs

　'後処理
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Sub

お礼

教えていただきましたVBAコードを実行して正しく出力できました。
SQL式の部分を書き換えることによりいろいろ使えそうです。
ありがうございました。

回答No.5

imogasi

#3です。エクセルVBAの初等の勉強にふさわしいものですが、
（４）ソート法
を書いてみます。
＃３と同じA列にデータがあるとして
標準モジュールに
Sub test01()
Range("A2").CurrentRegion.Select
Application.Intersect(Selection, Selection.Offset(1, 0)).Select
Selection.Sort key1:=Columns(1)
'--
k = 1
For Each cl In Selection
If cl = mae Then
cnt = cnt + 1
Else
Cells(k, "D") = cnt
cnt = 1
k = k + 1
Cells(k, "C") = cl
mae = cl
End If
Next
Cells(k, "D") = cnt
End Sub
ーー
実行結果
C1:D８
C列　　D列
数 出現頻度　＜－－この行は、手入力
1 3
2 3
3 1
5 3
8 1
21 3
34 1
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
グラフ作成は、
C1;D８を範囲指定して
挿入ーグラフー散布図
X軸の目盛単位を１にする。

お礼

教えていただきましたVBAコードを実行して正しく出力できました。
いろいろな方法を教えていただきましてありがとうございました。

回答No.4

imogasi

#3です。
もう一つのやり方です。
（３）FREQUENCY法
操作
・データ
・フィルター詳細設定
・指定した範囲ーE1:E16（どの列でもよいが）
・重複するレコードは無視する
＃３回答で使った、データでやると
数値
1
2
3
5
8
34
21
データを小ー＞大に並べ替えておく。
ーー
結果は
数値
1
2
3
5
8
21
34
ーーー
F2:F9を範囲指定して置いて
F2に式　=FREQUENCY(A2:A16,E2:E8)解いててカーソルを
数式バー部において、SHIFT+CTRL+ENTERを同時押しする。
配列数式というものです。FREQUENCY関数は、エクセル関数では、珍しく複数データを返すので
配列になります。
参考
https://kokodane.com/kan47.htm
FREQUENCY関数
結果
数値（E列） 　F列（出現頻度）
1 　3
2 　3
3 　1
5 　3
8 　1
21　　 3
34　　 1
　　　0
＞Excelでデータの各値とその個数を生成するには
「生成」とは大げさすぎる表現。出現行の「カウント」などが適当かと。

お礼

FREQUENCY()関数を使う方法についても確認をしてできました。
ありがうございました。

回答No.3

imogasi

（１）COUNTIF法
　　　A列数値の重複しないリストをフィルタなどで出しておいて
　　　その隣列に関数で出現件数をだす。
（２）ピボットテーブル法
　　　ピボットはエクセルの秀逸な機能です。
例データ
A１：A16
数値
1
2
3
5
8
2
1
34
21
2
5
1
21
21
5
表示形式を「文字列」に設定
A1:A16を範囲指定
挿入ーピボットーー新規ワークシートーOK
「数値」を行にD&D
「値」フィールドの「値フィールドの設定」で、「データの個数」ーOK
結果

行ラベル データの個数 / 数値
1 3
2 3
3 1
5 3
8 1
21 3
34 1
総計 15

お礼

COUNTIF()関数を使用については、No1とNo2の回答者様の回答が参考になりました。
ピボットテーブル法についても確認してできました。
ありがうございました。

回答No.2

msMike

C1: =COUNTIF(A$1:A$500,B1)

お礼

各値はあらかじめ用意しておくということですね。
教えていただきました方法でできました。
ありがうございました。

回答No.1

kkkkkm

1から連番で最大数まで続くとして
B1に
=IF(ROW(A1)<=MAX(A:A),ROW(A1),"")
C1に
=IF(B1="","",COUNTIF(A:A,B1))
として必要なだけ下にコピーして試してみてください。

お礼

1から連番で最大数まで続くことが条件ですね。
教えていただきました方法でできました。
ありがうございました。