- ベストアンサー
他のブックを参照するCOUNTIFマクロを作りたい
- みんなの回答 (7)
- 専門家の回答
質問者が選んだベストアンサー
>book2が開いていてCOUNTIFで書くと、 =COUNTIF(book2.xlsm!$A$2:$A$8,A1) (book1のA3に入力する関数) こうなるのですが、教えていただいたSUMとIFの計算式で どのように自分にあてはめて書けば良いのかがわかりません… 私の回答に一部ミスがあり、 >=SUM(IF([ファイル名]シート名!範囲と条件式,範囲,ゼロ)) は =SUM(IF([ファイル名]シート名!範囲と条件式,イチ,ゼロ)) でした。 当てはめると、 =SUM(IF('[book2.xlsm]シート名'!$A$2:$A$8=A1,1,0)) です。
その他の回答 (6)
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
- imogasi
- ベストアンサー率27% (4737/17070)
小生が個人的に興味からやってみたものを参考に上げてみます。 小生は府県名のリストで、テストしましたが、下記で「府県サンプル1.xlsm」に当たるブックを作り、そのSheet1のA列に、電話番号のリストを作ってください。 そして下記VBAコードで、そのブックを指定している該当行を、フルパスで、書き直してください。 そして次に、このVBAコードを入れるブックのSheet1のA1セルに府県名(質問者の場合は電話番号)を1つ入れて指定します。 下記コードは、1件の府県名をチェックして、該当するかどうかを表示して、終わりですが、For nextで繰り返し、多数の府県(質問者の場合は電話番号)のチェックに変えるのは簡単です。 ーーー 盛り込んだ機能は、 (1)考えているブックがすでに開かれているかどうかの判別 (2)(別ブックデータを)検索する、 その方法の、数種のコードを書き連ねています。 方法の選択は、 GoTo p4 ' <========================================処理分岐 の現状のP4をP1,p2,P3などに変えて方法を選び、実行します。 ほかにSQLを使う方法がありますが、省略してます。 少数例で動くことを確認したら、Msgbox行は、適当な時宜処理にかえてください。または削除してください。 === VBAコードは、電話番号リストがあるブックではない方の、ブックの 標準モジュールに Sub test02() '---- Set wb1 = ActiveWorkbook '--moug WEB 記事から bn = "C:\Users\XXX\YYYY\Documents\府県サンプル1.xlsm" On Error GoTo ErrHdl Set myChkBook = Workbooks("bn") MsgBox "開かれています。" GoTo p0 ErrHdl: MsgBox "開かれていません。" '--開く Set wb2 = Workbooks.Open("C:\Users\惇\ドキュメント\Documents\府県サンプル1.xlsm") '()が要る p0: Set sh2 = wb2.Worksheets("Sheet1") 'MsgBox sh2.Cells(2, "A") lr2 = sh2.Cells(1000000, "A").End(xlUp).Row '-- Set sh1 = wb1.Worksheets("Sheet1") S = sh1.Cells(1, "A") MsgBox "検索語=" & S '---Dictionaryを作成 Set ofso = CreateObject("Scripting.FileSystemObject") '---Dictionaryオブジェクトの宣言 Set dic = CreateObject("Scripting.Dictionary") '--Dic登録 For i = 2 To lr2 MsgBox sh2.Cells(i, "A") dic.Add Key:=sh2.Cells(i, "A").Value, Item:=sh2.Cells(i, "A").Value Next i '--確認 MsgBox dic.Count '-- '// 全キー取得 arKeys = dic.Keys '// 全キーを1つずつループ For Each vKey In arKeys MsgBox "Item=" & dic.Item(vKey) Next GoTo p4 ' <=============================方法によって==処理分岐 '=================CountIf炉用法========================== '==============VLOOKUP利用法 もあると思う。ただしここでは省略 p1: x = WorksheetFunction.CountIf(sh2.Range("a2:A" & lr2), S) If x = 1 Then MsgBox S & " あり" Else MsgBox S & " なし" End If GoTo ext '================Find法============ p2: Set x = sh2.Range("a2:A2" & lr2).Find(S) If Not x Is Nothing Then MsgBox S & " あり" Else MsgBox S & " なし" End If GoTo ext '=================総当たりチェック法================ p3: For i = 2 To lr2 If sh2.Cells(i, "A") = S Then MsgBox S & " あり" GoTo ext Else End If Next i MsgBox S & " なし" GoTo ext 'wb2.Close 'Workbooks(wb1).close は不可 '=================Dictionary利用法========================== p4: MsgBox "検索語 " & S If dic.Exists(S) Then MsgBox S & "は登録済みです。" Else MsgBox S & "は登録されていません" End If '---最終 ext: wb2.Close Set fso = Nothing Set dic = Nothing End Sub
- HohoPapa
- ベストアンサー率65% (455/693)
たびたびゴメンナサイ 誤 .Cells(3, 1).Value = "複数しない" 正 .Cells(3, 1).Value = "存在しない" です。
- HohoPapa
- ベストアンサー率65% (455/693)
ごめんなさい、訂正して再ポストします。 列名を見誤っていました。 電話番号 から 電話番号リスト に変更しました。 >ただ、book2は常時ブックを開いているわけではなく、 >閉じた状態であることもあります。 book2が開いていて、 画面上の方がより新しい状態で、かつ未保存の時 どちら(開いている側/保存されている側)をみればいいでしょうか? 開いている場合は開いている側を参照してよければ、 以下のコードでいかがでしょうか。 なお、 COUNTIFではなく SQL文を使いますので、若干見慣れないコードです。 Option Explicit Sub Sample() 'ボタンのイベントで呼び出してください Dim HitCnt As Long With ThisWorkbook.Sheets("sheet1") HitCnt = TelHitCount(.Cells(1, 1).Value, "Book2.xlsx", "Sheet1") If HitCnt = 1 Then .Cells(3, 1).Value = "存在する" ElseIf HitCnt > 1 Then .Cells(3, 1).Value = "複数存在する" Else .Cells(3, 1).Value = "複数しない" End If End With End Sub Function TelHitCount(TelNum As String, BName As String, SName As String) As Long Dim cn As Object Dim rs As Object Dim wkSQL As String 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=YES;IMEX=1" cn.Open ThisWorkbook.Path & "\" & BName 'SQL文組み立て wkSQL = "" wkSQL = wkSQL & "SELECT count([電話番号リスト]) as HitCnt " & vbCrLf wkSQL = wkSQL & "FROM [" & SName & "$A1:A65000]" & vbCrLf wkSQL = wkSQL & "Where [電話番号リスト] = '" & TelNum & "'" & vbCrLf 'SQL文実行 rs.Open wkSQL, cn '結果セットを取得 TelHitCount = rs("HitCnt") '後処理 rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Function
- HohoPapa
- ベストアンサー率65% (455/693)
>ただ、book2は常時ブックを開いているわけではなく、 >閉じた状態であることもあります。 book2が開いていて、 画面上の方がより新しい状態で、かつ未保存の時 どちら(開いている側/保存されている側)をみればいいでしょうか? 開いている場合は開いている側を参照してよければ、 以下のコードでいかがでしょうか。 なお、 COUNTIFではなく SQL文を使いますので、若干見慣れないコードです。 Option Explicit Sub Sample() 'ボタンのイベントで呼び出してください Dim HitCnt As Long With ThisWorkbook.Sheets("sheet1") HitCnt = TelHitCount(.Cells(1, 1).Value, "Book2.xlsx", "Sheet1") If HitCnt = 1 Then .Cells(3, 1).Value = "存在する" ElseIf HitCnt > 1 Then .Cells(3, 1).Value = "複数存在する" Else .Cells(3, 1).Value = "複数しない" End If End With End Sub Function TelHitCount(TelNum As String, BName As String, SName As String) As Long Dim cn As Object Dim rs As Object Dim wkSQL As String 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=YES;IMEX=1" cn.Open ThisWorkbook.Path & "\" & BName 'SQL文組み立て wkSQL = "" wkSQL = wkSQL & "SELECT count([電話番号]) as HitCnt " & vbCrLf wkSQL = wkSQL & "FROM [" & SName & "$A1:A65000]" & vbCrLf wkSQL = wkSQL & "Where [電話番号] = '" & TelNum & "'" & vbCrLf 'SQL文実行 rs.Open wkSQL, cn '結果セットを取得 TelHitCount = rs("HitCnt") '後処理 rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Function
- unokwave
- ベストアンサー率58% (966/1654)
マクロを書かなくても、sumとifで代わりができます。 =SUM(IF([ファイル名]シート名!範囲と条件式,範囲,ゼロ)) 開いていないブックを開いて処理するcountif代替マクロの概ねを書くと function MyCountIF(filename as string, sheetname as string, area as string, condition as variant) という関数入出力定義とします。 最初にExcelブックを設定します。 MyCountIFでファイル名に""を指定すると dim wb as workbook if fikename = "" then set wb = ThisWorkbook elseif dir(filename) <> "" then Workbooks.Open(filename) dim wb2 as workbook for each wb2 in Workbooks if wb2.FullName = filename then set wb = wb2 exit for end if next end if if wb is nothing then MyCountIF = "" & filename & """が見つからないか開けません。" exit function end if この後はwbが持つシートから指定のシート名を探します。 dim ws as Worksheet, ws2 as Worksheet if sheetname = "" then set ws = wb.activesheet else for each ws2 in wb if ws2.name = sheetname then set ws = ws2 exit for end if next end if if ws is nothing then MyCountIF = "" & sheetname & """が見つかりません" exit function end if この後は範囲の処理と条件式処理です。 dim xcell as range, i as integer, result as long, cond() as string cond = Split("<>,>=,<=,>,<,="), ",") '←比較演算子のカンマ区切りリストを配列にする。演算子は文字数が長い方を先に書く。 for each xcell in ws.range(area) for i = lbound(cond) to ubound(cond) select case left(condition, len(cond(i))) case ">": if xcell.value > right(condition, len(condition) - len(cond(i)) then result = result +1 他の条件式処理を並べていく。caseの条件式">"とif の中の>を他の比較演算子に変えていけば良い end select next next 残りは後始末です。 if not wb2 is nothing then wb2.close MyCountIF = result これでCountifの代わりに使える物ができるので、後はボタン用のサブルーチンと処理を書けば良いです。
補足
ご回答いただきありがとうございます。 >マクロを書かなくても、sumとifで代わりができます。 >=SUM(IF([ファイル名]シート名!範囲と条件式,範囲,ゼロ)) book2が開いていてCOUNTIFで書くと、 =COUNTIF(book2.xlsm!$A$2:$A$8,A1) (book1のA3に入力する関数) こうなるのですが、教えていただいたSUMとIFの計算式で どのように自分にあてはめて書けば良いのかがわかりません… 引き続きご教授いただければ幸いです。 (関数で済むならマクロを使わずに関数を使いたいです)
お礼
ご丁寧にご説明いただきありがとうございました。 こちらをベストアンサーにさせていただきます。 御礼が遅くなりまして失礼致しました。