[[20191205131528]]　『あるセルが参照された回数を表示したい』（みいき）　ページの最後に飛ぶ

『あるセルが参照された回数を表示したい』（みいき）

お世話になります。
分かりにくいかもしれませんが、よろしくお願いします。

あるセルが参照された回数をカウントしたいです。

マスタシートが1枚と
サブシートが複数枚存在するブックを想定しています。

(マスタシート)

 A-1 犬
 A-2 猫
 A-3 兎

(サブシート)

 わんわん (マスタシートA-1を参照) 犬(vlookupで出力)
 ぴょんぴょん (マスタシートA-3を参照) 兎(vlookupで出力)
 にゃあ (マスタシートA-2を参照) 猫(vlookupで出力)
 にゃん (マスタシートA-2を参照) 猫(vlookupで出力)

このようなシート構成で、似たようなサブシートが複数枚あります
このとき、マスタシート上のA-1からA-3が、全てのサブシートにおいて
何回参照されたかを、マスタシート上にそれぞれ出力したいです

 それぞれのサブシートで、A-1からA-3までカウントイフで数を数え
それを合計することも考えましたが

 A-1からA-3までの行は状況に応じて増減する予定で
 さらにサブシートも適宜増えていきます

 何か他に手立てがありましたら、教えていただけると幸いです

よろしくお願いします

＜使用 Excel：Office365、使用 OS：Windows10 ＞

参照とは、数式で指定した範囲に該当セルが含まれているか、でしょうか？それは、VLOOKUP関数だけを対象に調べるのでしょうか？（単純イコールで代入とか、他の関数は無い？）

マクロを使って全てのセルに書かれている数式を抜き出して、VLOOKUPの範囲指定内に含まれている場合にカウントアップするようなコードを考えるしかないように思いますよ。

実際の数式を見ないと難易度は判りませんが、VLOOKUPの引数を抜き出すだけでもかなり面倒そうに思います。例えば、検索文字列として「A-1」という文字列が固定で埋め込まれているのか、セル参照になっていて、数式とは違うセルに「A-1」と書いてあるのか、とか。すべての数式を見ないと、どうやるのがベストか判断が付かないので、ご自身で頑張るしかないでしょう。
（具体的には、SpecialCellsを使えば、シート内の数式のあるセル全てを選択できるので、後は該当セルを1つずつ調べて、Formulaプロパティに書かれている文字列を調べれば良さそう）
（???） 2019/12/05(木) 14:08

Sub main()

    '新規ブックの標準モジュール
    Dim ms As String, fname As String, FSO As Object, wb As Workbook, sht As Worksheet, myfile As String, x As String, ctr As Long, i As Long, tot As Long
    ms = "マスタシート" 'マスターシートのシート名を指定
    myfile = Application.GetOpenFilename(FileFilter:="Excel ファイル (*.xls; *.xlsx; *.xlsm),*.xls; *.xlsx; *.xlsm", FilterIndex:=1, Title:="調査対象ファイルを選択してください", MultiSelect:=False)
    If Dir(myfile) = "" Then Exit Sub
    Cells.ClearContents
    Set FSO = CreateObject("Scripting.FileSystemObject")
    fname = ThisWorkbook.Path & "\temp." & FSO.GetExtensionName(myfile)
    FSO.GetFile(myfile).Copy fname
    Set wb = Workbooks.Open(Filename:=fname, ReadOnly:=True)
    Do While wb.Sheets(ms).Range("A" & i + 1).Value <> ""
        x = wb.Sheets(ms).Range("A" & i + 1).Value
        ctr = 0
        wb.Sheets(ms).Range("A" & i + 1).Delete Shift:=xlToLeft
        For Each sht In wb.Worksheets
            If sht.Name <> ms Then
                ctr = ctr + WorksheetFunction.CountIf(sht.Cells, "#REF!")
            End If
        Next sht
        ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Array(x, ctr - tot & "回")
        tot = ctr
        i = i + 1
    Loop
    wb.Close False
    FSO.DeleteFile fname
End Sub

（mm） 2019/12/05(木) 16:24

 例：Sheet1～Sheet3 の A1:A20 の間の「犬」をカウント。

 =SUMPRODUCT((T(OFFSET(INDIRECT("Sheet"&{1,2,3}&"!A1"),ROW($1:$20),0))="犬")*1)
（GobGob） 2019/12/05(木) 18:48

 OFFSETいらんかｗ

 =SUMPRODUCT((T(INDIRECT("Sheet"&{1,2,3}&"!A"&ROW($1:$20)))="犬")*1)
（GobGob） 2019/12/05(木) 19:00

例がおかしくないですか？

「にゃあ」と入力されていて、なんでA2セルだと判断できるかがわかりません。

あと、結果を数えるなら、猫の数を数えとか、にゃあの数を数えればいいだけかと。
セルが参照された回数とかなんか回りくどいいいかたで、
読んでわかるようなわからないような表現かなと思います。

ホントの実例でビフォーアフターを書いた方が、
解決が早い気がします。
（まっつわん） 2019/12/06(金) 08:44

コメント返信：

[ 一覧(最新更新順) ]

[[20191205131528]] 『あるセルが参照された回数を表示したい』（みいき） ページの最後に飛ぶ

[[20191205131528]]　『あるセルが参照された回数を表示したい』（みいき）　ページの最後に飛ぶ