配列の出現回数の率で順位をだし、率と値を表示したい

質問

こんにちは。アベです。

配列の出現回数の率で順位をだし、率と内容を表示したい。

特定の範囲から
・一番出現回数の多い値とその値が占める率を取りたい
・次に、二番目に出現回数の多い値とその値が占める率を取りたい
・次に、三番目に出現回数の多い値とその値が占める率を取りたい
・・・という風に式を作りたいです。

イメージ
-----------------------------------------------------------
80% 1 15% 3 5% 5 1 1 1 3 5 1 3 5・・
75% 2 25% 1 2 2 2 1 2 1 2 2・・・
80% 1 15% 3 5% 5 1 1 1 3 5 1 3 5・・・
80% 1 15% 3 5% 5 1 1 1 3 5 1 3 5・・・
80% 1 15% 3 5% 5 1 1 1 3 5 1 3 5・・・

式のイメージ、ヒントを教えて頂けないでしょうか？

回答No.2

tom04 · ベストアンサー

こんばんは！
VBAでの一例です。

↓の画像のように元データがSheet2（下側）の2行目、A列以降にあるとし、
上側のSheet1に表示するとします。
尚、Sheet3を作業用のSheetとして使用していますので、
Sheet3は全く使用していない状態にしておいてください。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, lastRow As Long, lastCol As Long
Dim cnt As Long, wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Cells.Clear
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
If lastRow > 1 Then
Range(.Cells(2, "A"), .Cells(lastRow, lastCol)).ClearContents
End If
For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
lastCol = wS2.Cells(i, Columns.Count).End(xlToLeft).Column
Range(wS2.Cells(i, "A"), wS2.Cells(i, lastCol)).Copy
wS3.Range("A2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
lastRow = wS3.Cells(Rows.Count, "A").End(xlUp).Row
wS3.Range("A1") = "ダミー"
Range(wS3.Cells(1, "A"), wS3.Cells(lastRow, "A")).AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("C1"), unique:=True
lastRow = wS3.Cells(Rows.Count, "C").End(xlUp).Row
Range(wS3.Cells(2, "D"), wS3.Cells(lastRow, "D")).Formula = "=COUNTIF(A:A,C2)"
With Range(wS3.Cells(2, "B"), wS3.Cells(lastRow, "B"))
.Formula = "=D2/(COUNTA(A:A)-1)"
.Value = .Value
.Style = "Percent"
End With
Range(wS3.Cells(2, "B"), wS3.Cells(lastRow, "D")).Sort key1:=wS3.Range("B2"), order1:=xlDescending, Header:=xlNo
For k = 2 To lastRow
wS3.Cells(k, "B").Resize(, 2).Copy .Cells(i, (k - 1) * 2 - 1)
Next k
wS3.Range("A:D").Clear
Next i
For i = 1 To .UsedRange.Columns.Count Step 2
cnt = cnt + 1
With .Cells(1, i)
.Value = "出現率"
.Offset(, 1) = cnt & "位"
End With
Next i
.Columns.AutoFit
.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　じっくり考えればもっと簡単に出来るかもしれませんが、
とりあえずはこの程度で・・・m(_ _)m

回答No.3

bunjii

>配列の出現回数の率で順位をだし、率と内容を表示したい。
>式のイメージ、ヒントを教えて頂けないでしょうか？
ご提示の模擬データのみで検証してみました。
条件としてExcel 2013を使用し、作業用テーブルを使います。
処理対象のセル内には1～5までの正数が入力されているものとします。
貼付画像で数式を入力するセルと処理範囲のセルを確認してください。
作業用テーブルはQ1:U5です。
Q1=COUNTIF($G1:$O1,COLUMNS($Q1:Q1))
Q1セルをQ1:U5セルへコピーします。
各行の出現数の大きい順にG～O列に出現する数値の多い順にB列、D列、F列に数値を抽出します。
B1=IF(LARGE(INDEX(($Q1:$U1),0),COLUMN()/2),100-MOD(LARGE(INDEX(($Q1:$U1+{0.99,0.98,0.97,0.96,0.95}),0),COLUMN()/2),1)*100,"")
B1セルを下へB5セルまでコピーしました。
B1の値が出現した比率をA1セルに算出します。
A1=IF(B1="","",COUNTIF($G1:$O1,ROUND(B1,0))/COUNT($G1:$O1))
比率は数値入力のセル数が分母です。セルの表示形式を％にしてあります。
A1セルを下へA5セルまでコピーしました。
A1:B5セルを纏めてコピーし、C1セルとE1セルへ貼り付けると添付画像のようになります。
Excel 2003以前のバージョンでも再現できると思います。

お礼

有難うございます。

回答No.1

asciiz

式ではちょっと無理ですね。VBAプログラミングすることになるでしょう。
ただ、表現方法が変わりますが、式でも似たようなことは可能です。

例えば、
各列、G～XA 以前までで、任意長で値が入っているとします。
データの個数＝conuta(G□:XA□)
データが1である個数＝countif(G□:XA□, 1)
データが2である個数＝countif(G□:XA□, 2)
データが3である個数＝countif(G□:XA□, 3)
…と、数えられます。

それならば、
A1=countif(G1:XA1,1)/conuta(G1:XA1) ←その行で1が含まれる割合
B1=countif(G1:XA1,2)/conuta(G1:XA1) ←その行で2が含まれる割合
C1=countif(G1:XA1,3)/conuta(G1:XA1) ←その行で3が含まれる割合
:
のように、表示できます。(表示形式で%表示にすること)
どれが何位かは、目で見てください。

まあ、値の種類が数種類であれば、これでもいいですかね。(ご質問の例では、そのように見えたんですが)
値が0～100まであったりするようだと、ちょっとやってられません。

----

VBAプログラミングする場合も、上記と同じように、まずは各行において、各値の出現数を、配列に入れて行ってカウントします。
そして、出現数の多い順に並べ替え、上位3つをセル上に表示、ということは確かにできます。
このプログラミングには、ループと分岐が必要になり、関数のネストに制限数があるの「式」では、難しすぎます。

お礼

VBAを作成してやってみます。
それで出来そうです。