エクセル 関数 マクロ

質問

エクセルの関数で5項目の文字（仮に【あ～お】）が合ったらカウントする関数、但しプルダウンで絞るとその分カウントに比例すること。
で下記関数を作成しましたが、
=SUMPRODUCT((SUBTOTAL(103,INDIRECT("AA"&ROW(AA10:AA10000))))*(AA10:AA10000="あ"))
AA列10行～10000行にあがあればカウントする。動作確認済み
この関数を右列にコピーしていきたいんですが、自動でABに変更にならない箇所があります。対策教えていただきたく。

回答No.10

SI299792 · ベストアンサー

私が頼んだ情報は出してくれないのですね。私の言っていることが理解できないのか、情報を出したくないのか判りませんが、情報がないとできないので勝手に決めます。
　プルダウンと書いてありますが、フィルターですよね。私はINDIRECTが出てきたらフィルターと刷り込まれているので、フィルターだと思い込んでいました。他の方は、文字通りプルダウンだと思い、フィルターの考慮をしていません。

以下の条件です。
・検索文字は「あ」だけ（質問にあった数式と同じ）
・Ｊ列に連番又は項目が入っている（必ず何か入っていて空白が無い）
・K17～FZ10000が計算対象
・計算結果はK1～FZ1 に表示
・ワークエリアはGZ列を使う。（本当はＡ列に作った方がいいのですが、空いてないと思うので）

GZ17 =SUBTOTAL(103,J17)
GZ10000 迄コピペ。
K1: =COUNTIFS($GZ17:$GZ10000,1,K17:K10000,"あ")
FZ1 迄コピペ。
（これなら短いので、７種類加算しても大した長さになりません）

　Ｊ列が必ず何か入っているとは限らない場合、別の列にして下さい。とこにもない場合、１列作って下さい
　画像を貼りたい場合、新しい質問を立てればいいです。どうせタダだし。

補足

いろいろありがとうございます。
正直この関数も見よう見まねでなにも理解しておりません。
新たな質問で今の形の一部を写真貼ります。
ご確認ください。
表題、同じで新たに質問します。
すみませんが画像目を通してください。

回答No.12

HohoPapa

ごめんなさい、誤解していました。
SUBTOTALとあるので、非表示行は数えないんですね？

ならば、

Option Explicit

Function GetMyCount(tgRng As Range, KeyRng As Range) As Long

Dim LastRow As Long
Dim c As Long
Dim i As Long

c = 0
'行末を取得
LastRow = Cells(Rows.Count, tgRng.Column).End(xlUp).Row

For i = 16 To LastRow

If ((Rows(i).Hidden = False) And _
(Cells(i, tgRng.Column).Value = KeyRng.Value)) Then
c = c + 1
End If
Next i

GetMyCount = c

End Function

という関数にして、

J4=GetMyCount(J:J,$I4)　と埋め
下方向と横方向に必要数複写する対応はいかがでしょうか。

お礼

いろいろありがとうございました。
感謝です

回答No.11

HohoPapa

https://okwave.jp/qa/q10040651.html
の画像を確認しました。
ならば、後記コードを貼り付け、
J4=GetMyCount(J:J,$I4)
を埋め、下方向と横方向に必要数複写する対応はいかがでしょうか。

Option Explicit

Function GetMyCount(tgRng As Range, KeyRng As Range) As Long

　Const SCol = 10　'J列の列番号
　Const DataRng = "[Sheet1$J15:FZ10000]" 'データのシート名と範囲

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

　'SQL用環境設定
　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

　 'SQL全文を組み立て
　SQL = "SELECT" & vbCrLf
　SQL = SQL & "count(*) as MyCnt" & vbCrLf
　SQL = SQL & "FROM " & DataRng & vbCrLf
　SQL = SQL & _
　　　　"Where [F" & tgRng.Column - SCol + 1 & "] = '" & _
　　　　KeyRng.Value & "'" & vbCrLf

　'SQL文を実行して結果を取得
　rs.Open SQL, cn
　GetMyCount = rs("MyCnt")

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

End Function

回答No.9

HohoPapa

VBAで課題カウントを行う関数を作り、
この関数式をセルに埋める対応はいかがでしょうか。

添付画像の右半分に後記VBAのコードを貼り、
K15セルに
=GetMyCount(K:K,K13)　を埋め
必要数右方向に複写します。

計算式埋めているK13は、数える対象の文字列の埋まったセルです。

Option Explicit

Function GetMyCount(tgRng As Range, KeyRng As Range) As Long

Const SCol = 11 'K列の列番号
Const DataRng = "[Sheet1$K17:FZ10000]" 'データのシート名と範囲

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

'SQL用環境設定
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

'SQL全文を組み立て
SQL = "SELECT" & vbCrLf
SQL = SQL & "count(*) as MyCnt" & vbCrLf
SQL = SQL & "FROM " & DataRng & vbCrLf
SQL = SQL & _
　　　　"Where [F" & tgRng.Column - SCol + 1 & "] = '" & _
　　　　KeyRng.Value & "'" & vbCrLf

'SQL文を実行して結果を取得
rs.Open SQL, cn
GetMyCount = rs("MyCnt")

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

End Function

回答No.8

SI299792

ワークエリアを使えば、FZ列まであっても１瞬で計算できる数式が可能です。VBA よりそっちの方がいいと思うのですがどうでしょうか。

回答No.7

SI299792

それは大変ですね。FZ迄入れてみましたが、１ヶ所入力する毎にしばらく止まりました。

VBA にすると、リアルタイムでなく、ボタンを押すたびに計算になります。
いくつか補足をお願いします。
・文字７つはどこに入っているか。（例えばA1～A7の様に、入っていないなら場所を決めて入れて下さい）
・計算結果をどの列に出力するか（１列目でいいか）
・開始位置はK17 、終了行・終了列共に決まっていないという解釈でいいか。
・17～10000 なのになぜ 3万カウントなのか。１列づつのカウントではなく、全体カウントなのか。

補足

いろいろご検討ありがとうございます。
列に1マス 1WEEK か1Day これが今年度来年度と追加になっていく感じ
行に1イベント単位で増えていきます
1イベントにあ～き（7ショップ分）があり、日程間にあ～きを入力して各ショップのボリューム感を表す感じにしたいです。
行はイベントごと随時増えていきますので
数千数万になっていきます。
画像貼れれば伝わるかと思いましたが・・・。

回答No.6

bunjii

>この関数を右列にコピーしていきたいんですが、自動でABに変更にならない箇所があります。
提示の数式でINDIRECT関数を使っている理由を補足してください。
Excelに組み込まれた関数はセル番地を指定するとき絶対番地と相対番地を自在に扱うことができます。
例えば提示の数式をAA2に組み込んだとします。
それをAB2セルへコピーするとINDIRECT関数の"AA"は文字列なので"AB"に変化しませんがSUMPRODUCT関数の(AA10:AA10000＝”あ")は相対番地指定なので(AB10:AB10000＝”あ")に変化します。
このことから対策方法をご自身で探せると思います。

回答No.5

SI299792

INDIRECTのR1C1形式です。
R10C27の様に列も数字で指定します。列の数式指定が可能になります。
第二パラメータでFALSE を指定するのですが, だけで省略しています。（, もつけないとA1形式になります）

VBA にするのは可能ですが、簡単に修正はできないので、確認します。
・実際にAA10:AA10000なのか、それとももっと多いのか
・その数式は何処からどこまで入れるのか
（というのは、私が数式を入れたら一瞬で出ました、もしかしたら実際もっと多いのではないかと思いました。件数を把握しておかないと、スピードの検証ができません）
・「あ」だけでいいのか「あ」～「お」か。それとも「あ」「い」「か」「き」の様に自由指定にするのか

お礼

丁寧な回答ありがとうございます。
実際は文字は７つ
実際には行はK17～K10000で 随時増えていきます。
列もありまして、とりあえずFZまで設けてますが、これも随時増えていきます。
現在この範囲で3万カウントになっております。

回答No.4

imogasi

#2です。
配列数式ですが
=SUM(IF(A2:A11=D1:F1,1,0))を入れて、SHIFT＋CTRL+ENTERでも
D1,E１，F１のどれか１文字と一致したら件数を加える、が出せます。
D1,E１，F１が、あ、え、お
ーー
A2:A11が
あ
い
う
え
お
あ
い
か
え
お
の時、関数の結果は６です。
セルデータが１文字でなくても、完全一致で良いなら同じ式でOK。

回答No.3

imogasi

#2です。
すみません。COUNTIFSでは、OR条件で複数条件指定はできません、でした。
=COUNTIF(A2:A11,D1)+COUNTIF(A2:A11,E1)+COUNTIF(A2:A11,F1)
条件は、例えば、D1:F1に、「あ、う、お」とある場合
のような長々した式になりそうです。
＝＝
この質問の意図、状況が十分文章で、説明されていない。
＝＝
ＶＢＡを使うに、処理方法をVBAに、決めて、WorksheetFunctionを使ってCountIf関数の、条件数分の繰り返しで、各々を加え、出せば簡単なように思った。
　＞SUMPRODUCT((SUBTOTALを使うのは思い付きか、WEB記事の真似では。

お礼

関数、VBAとも全くの無知識です。
おっしゃる通りネットでこんなのできないかを探した結果です。ただ重すぎて計算時間がかかっております。
今後もデータ量は随時増えていくため改良したいと考えてます

回答No.2

imogasi

>5項目の文字　5つの文字の１文字にについて、該当が何セルあるかカウントしたい、であれば、＞但しプルダウンで絞ると＝５文字のどれか（複数指定在り）を指定するのは、難しいのでは。
VBAを使えば、できる範囲の課題でしょう。質問表題の「マクロ」が添えられているが、どういう意図か？だいたいはマクロはできないという質問者はここでは、多いが。
＞質問の「プルダウン」は、エクセルの入力規則の「リスト」を指す、のでしょうが、５セルに、その入力規則（同じ内容）を設定しておくことになると思うが、そんなことを考えているのか？
プルダウンで（例えば）４回別の文字を指定したら、別セルに４文字設定できるのは、難しいのでは。
関数では複数条件的な情報も、複数セルに個別にその値が実現している
か式の中に使用者が埋め込むか）ことを、要求するのが普通。
＞複数条件的な情報をＡＮＤ条件でカウントするなら、ＣＯＵＮＴＩＦＳ関数などの利用を考え直してはどうか。
それでも関数の条件に、指定文字のどれか、の条件を組み立てるのは難しそうだ（複雑になりそう）。

お礼

ご回答ありがとうございます。
VBA=マクロと勘違いしておりました。
VBA勉強したいと考えてますが、なかなか覚えられません。