| |
つんさん、みなさん、こんばんは
配列数式を使う方法を最近おぼえたので作ってみました。
結果を書き出す範囲を選択して、数式バーに
=mySub(A5:A8,A15:A17)
のようにキーインして、Ctrl+Shift+Enter で配列数式として入力して下さい。
Option Explicit
'配列数式で与える
Function mySub(argR1 As Range, argR2 As Range) As Variant
'argR1 : 数えたいカテゴリの数字あるいは「N」
'argR2 : カウントする範囲
Dim kensakuti As Variant
Dim hanni As Variant
Dim ans As Variant
Dim tbl() As String
Dim e3 As Variant
Dim i As Long, j As Long, k As Long
Dim n As Long
Dim ncount As Long, q As String
kensakuti = argR1.Value
hanni = argR2.Value
If Right$(TypeName(kensakuti), 1) = ")" Then '複数セル
ReDim ans(LBound(kensakuti, 1) To UBound(kensakuti, 1), _
LBound(kensakuti, 2) To UBound(kensakuti, 2))
End If
'カウントする範囲のデータをバラバラにして、tbl()に記憶
n = 0
For i = LBound(hanni, 1) To UBound(hanni, 1)
For j = LBound(hanni, 2) To UBound(hanni, 2)
e3 = Split(CStr(hanni(i, j)), ",")
ReDim Preserve tbl(n + UBound(e3) + 1)
For k = 0 To UBound(e3)
tbl(n + k) = CStr(e3(k))
Next
n = n + UBound(e3) + 1
Next
Next
For i = LBound(kensakuti, 1) To UBound(kensakuti, 1)
For j = LBound(kensakuti, 2) To UBound(kensakuti, 2)
q = kensakuti(i, j)
ncount = 0
For k = 0 To n
ncount = ncount + IIf(tbl(k) = q, 1, 0)
Next
ans(i, j) = ncount
Next
Next
mySub = ans
End Function
セル参照は２回＋書き出しに１回だけなので、ひょっとすると速いかも。
|
|
ｎｉ
- 04/4/2(金) 21:47 -
