こんにちは。アベです。
配列の出現回数の率で順位をだし、率と内容を表示したい。
特定の範囲から
・一番出現回数の多い値とその値が占める率を取りたい
・次に、二番目に出現回数の多い値とその値が占める率を取りたい
・次に、三番目に出現回数の多い値とその値が占める率を取りたい
・・・という風に式を作りたいです。
イメージ
-----------------------------------------------------------
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.1
- 回答日時:
式ではちょっと無理ですね。
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つをセル上に表示、ということは確かにできます。
このプログラミングには、ループと分岐が必要になり、関数のネストに制限数があるの「式」では、難しすぎます。
No.2ベストアンサー
- 回答日時:
こんばんは!
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
- 回答日時:
>配列の出現回数の率で順位をだし、率と内容を表示したい。
>式のイメージ、ヒントを教えて頂けないでしょうか?
ご提示の模擬データのみで検証してみました。
条件として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以前のバージョンでも再現できると思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 統計学 確率統計の問題です。 3 2022/04/07 04:39
- その他(データベース) Accessフォームにて指定のフィールドの平均値を小数点第一位で表示できない 2 2022/08/30 17:19
- 数学 確率について 事象Aが起こる確率が0.25である独立行列において、試行回数を5回とした時Aの起こった 2 2022/06/06 19:46
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- 数学 株価予想の一致率(?)の求め方について 5 2022/12/27 04:44
- 統計学 統計検定2級の過去問について 1 2023/01/04 16:40
- 数学 数学の問題です。 問1: ある(人数の非常に多い)集団から無作為に6名を選んで身長を測ったところ、そ 2 2022/12/09 12:03
- 統計学 統計学の問題です。教えてください(_ _) 数万人の有権者がいる選挙区で, 無作為に400人の標本を 2 2023/02/03 15:27
- 統計学 統計学の問題です よろしくお願いします 区間推定 母集団は正規分布に従い,母分散は σ2 = 112 1 2023/01/31 18:57
- Java Java 南京錠 2 2023/02/04 11:46
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
首吊りどこ締めるの
-
至急!尿検査前日にオナニーし...
-
尿検査前日に自慰行為した時の...
-
白血球が多いとどんな心配があ...
-
尿検査の前日は自慰控えたほう...
-
検便についてです。 便は取れた...
-
納豆食べた後の尿の納豆臭は何故?
-
腕を見たら黄色くなってる部分...
-
彼女のことが好きすぎて彼女の...
-
EXCELで条件付き書式で空白セル...
-
2つの数値のうち、数値が小さい...
-
精子が黄色?
-
変な話しになります。尿検査で...
-
勃起する時って痛いんですか? ...
-
エクセル指定した範囲からラン...
-
小数点以下を繰り上げたものを...
-
excelでsin二乗のやり方を教え...
-
エクセルのラベルの値(文字列...
-
リンク先のファイルを開かなく...
-
1日前の検尿
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
至急!尿検査前日にオナニーし...
-
首吊りどこ締めるの
-
尿検査の前日は自慰控えたほう...
-
尿検査前日に自慰行為した時の...
-
検便についてです。 便は取れた...
-
白血球が多いとどんな心配があ...
-
中出しをするとお腹が痛い・・・。
-
射精をして1週間以内に尿検査を...
-
彼女のことが好きすぎて彼女の...
-
腕を見たら黄色くなってる部分...
-
勃起する時って痛いんですか? ...
-
変な話しになります。尿検査で...
-
これって喉仏ですか? 私は女性...
-
EXCELで条件付き書式で空白セル...
-
男です。昨日の午後3時くらいに...
-
今朝、毎朝の習慣でオナニーし...
-
納豆食べた後の尿の納豆臭は何故?
-
1日前の検尿
-
値が入っているときだけ計算結...
-
精子が黄色?
おすすめ情報