C列の3行目から360行目くらいまでのセルに半角英数の名前が入っています。
この中で最も多く出てくる名前をmsgboxで表示したいんです。
ただ、或る人物(例えばU.chief)は1位でも除外します。
Satou_M
Yoshida
Kusakabe
U.chief
U.chief
Satou_M
U.chief
上の例ではmsgboxにSatou_Mと出るようにしたいんです。
Worksheet関数のmode関数を利用して
MsgBox Application.WorksheetFunction.Mode([c3:c367])
でやってみたんですが、modeは対象が数値でないとエラーになるみたいです。
半角英数の文字列で上のような事が出来る方法は無いでしょうか?
予め決まった一人を除外して最多者を出す方法、のほうは全然分かりません。 (^_^;
No.1ベストアンサー
- 回答日時:
以前の質問で、文字列の最頻出値を求める関数を作ったことがあります。
http://okweb.jp/kotaeru.php3?qid=1143416
の#3
U.chiefを除外するのは、
最初のfor each で登録しカウントする部分をスキップする部分を追加すればいいです。
初めのfor eachの中身を
if x.value <> "U.chief" then
…
end if
のようにくるめばいいと思います。
No.4
- 回答日時:
データがA列にあるとする。
Sub test01()
d = Range("A65356").End(xlUp).Row
startrow = 2 'データ開始行
workc = "x" 'ワーク列
jyogai = "df" ' 除外者氏名1名
'--- copy sort
Range(Cells(startrow, "A"), Cells(d, "A")).Copy
Range(workc & startrow).PasteSpecial
Selection.Sort Key1:=Range(workc & startrow), Order1:=xlAscending
'-----
hmax = 1
smax = Cells(startrow, workc)
s = Cells(startrow, workc)
h = 1
'-----
For i = 3 To d
If Cells(i, workc) = jyogai Then Exit For
If Cells(i, workc) = s Then
h = h + 1
Else
If hmax < h Then
hmax = h
h = 1
smax = s
Else
h = 1
End If
End If
s = Cells(i, workc)
Next i
'----
MsgBox smax & "が最頻出" & hmax & "回"
End Sub
エクセルソートを使っているので、ワーク列を使います。
長いコードを短くならないか考えた結果です。Dimなどは省いてますのでよろしく。
startrow = 2 'データ開始行
workc = "x" 'ワーク列X列
jyogai = "df" ' 除外者氏名1名
は質問者が適当に変えてください。
簡単テスト例。
a
a
a
c
c
c
d
df
df
e
e
f
q
r
s
s
s
w
z
c
df
df
df
でcが4件
最初が除外者だとおかしくなるかもしれないと気づいたが、そのままにしました。
No.3
- 回答日時:
選択範囲 ( C3行目からC400行目くらいまで ) の中で
除外対象 (例えばU.chief 複数設定可) を考慮して
最も多く出てくる名前を 抽出
同順の場合 AA+bb と列挙
↓ モジュール ↓
Sub 指定範囲を結合して最多頻出項目を抽出する()
除外対象 = "太郎,U.chief" ' 半角 "," 区切り で 複数設定可
Set 選択範囲 = Range("C3:C400").SpecialCells(xlCellTypeConstants)
要素数 = 選択範囲.Count
文字列 = "" '文字列に結合
For Each 要素 In 選択範囲
文字列 = 文字列 + Trim(要素.Value) + ":"
Next
For Each 語句 In Split(除外対象, ",")
文字列 = Replace(文字列, 語句 + ":", "") '対象外 語句を削除
Next
最多対象 = ""
最多数 = 0
Do While 1 < Len(文字列)
計数対象 = Split(文字列, ":")(0)
新文字列 = Replace(文字列, 計数対象 + ":", "") '対象文字列削除
'ダミー1文字に置き換えたものとの差=対象数
対象数 = Len(Replace(文字列, 計数対象 + ":", "@")) - Len(新文字列)
文字列 = 新文字列
If 最多数 < 対象数 Then '最多 頻出
最多対象 = 計数対象
最多数 = 対象数
ElseIf 対象数0 = 対象数 Then ' 同順位 頻出
最多対象 = 計数対象 & "+" & 最多対象
End If
Loop
MsgBox "最多項目 = " & 最多対象 & "/最多頻出数=" & 最多数
End Sub
返事が遅れすみません。
変数が内容を表す漢字になっていて、とても分かり易かったです。
始め単なる解説文かと思ったんですが、立派なマクロなんですね。
文字列の最頻値を返す関数等は用意されてないようで、LOOPを作って出さなきゃいけないんですね。
難しいマクロをとても見易く書いて頂き有り難うございました。
もちろんエラー無く動きました。 m(_ _)m
No.2
- 回答日時:
こんばんは。
>MsgBox Application.WorksheetFunction.Mode([c3:c367])
あえて、こうしようと思った理由は、何かありますか?
例えば、
補助列を設けて
D3:
=IF(C3="","",IF((MATCH(C3,$C$3:$C$367,0)=ROW(A1)),COUNTIF($C$3:$C$367,C3)+ROW(A1)/1000,""))
E3:
=IF(COUNT($D$3:$D$367)>=ROW(A1),INDEX($C$3:$C$3671,MOD(LARGE($D$3:$D$367,ROW(A1)),1)*1000,1),"")
こんな風にすれば、並び替えをしなくても、順位が取れますから、必要なものを抜き出せばよいと思います。
しかし、こういう式を、そのまま、VBAに持ち込んでも、メリットが少ないのです。そこで以下のようにはなりますが、これは、全て、並べ替えた順序を配列の中に用意してあります。ですから、上から、順に取り出すことも可能です。
Option Explicit
Option Compare Text
Sub 検出リスト()
Dim myDic As Object, rng As Range
Dim i As Long, j As Long, k As String, t As Variant, n As Long
Dim a() As Variant
'データ範囲
Set rng = Range("C3", Range("C65536").End(xlUp))
'除外データ
Const Exception As String = "U.chief"
'
Set myDic = CreateObject("Scripting.Dictionary")
For i = 1 To rng.Rows.Count
k = rng.Cells(i, 1).Value
'ディクショナリに確保
If myDic.Exists(k) = False Then
myDic.Add k, Application.CountIf(rng, k)
End If
Next i
ReDim a(0 To myDic.Count - 1, 0 To 1)
For Each t In myDic.keys
a(j, 0) = myDic.Item(t)
a(j, 1) = t
j = j + 1
Next t
BB_Sort a()
For n = LBound(a(), 1) To UBound(a(), 1)
'除外項目
If Not a(n, 1) Like "*" & Exception & "*" Then
MsgBox a(n, 1)
Exit Sub
End If
Next n
Set myDic = Nothing
Set rng = Nothing
End Sub
Private Function BB_Sort(a() As Variant)
'バブルソート
Dim u As Long
Dim i As Long
Dim j As Long
Dim t1 As Variant, t2 As Variant
u = UBound(a(), 1)
i = LBound(a(), 1)
Do While i < u
j = u
Do While j > i
If a(j, 0) > a(i, 0) Then '降順
t1 = a(j, 0)
t2 = a(j, 1)
a(j, 0) = a(i, 0)
a(j, 1) = a(i, 1)
a(i, 0) = t1
a(i, 1) = t2
End If
j = j - 1
Loop
i = i + 1
Loop
End Function
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) エクセルの数式で教えてください。 1 2023/07/31 15:49
- Excel(エクセル) マクロでボタンにつける名前がどこに設定されているかわからないケースがありました。 1 2023/06/19 19:37
- Excel(エクセル) VBAで “:” を含むセルの特定 2 2023/05/11 16:30
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/07/06 17:46
- Excel(エクセル) エクセルのマクロについて教えてください。 1 2023/02/25 16:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報