エクセルでの、複数参照のチェックがしたいのです。

質問

先週質問したものから、さらに次の疑問です。

「シート1に任意のデータがあり、シート50からシート60までの10枚のシートのそれぞれ　W1からAW999までの間に、シート1のA1のデータがあれば、シート1のC1に「○」を打つ」という表を作っています。

　ここで、シート50からシート60までの２つ以上のシートが、シート１のデータを参照している場合、シート１のD１に「！」を打ちたいのですが、どうしたらいいでしょうか？

どなたかご回答をよろしくお願いいたします。

回答No.2

squip · ベストアンサー

Sheet1 の内容
C1 の式、=IF(SUM(E1:G1),"○","")
D1 の式、=IF(COUNTIF(E1:G1,">0")>1,"!","")
E1:G1 の式、=COUNTIF(INDIRECT("Sheet"&COLUMN()-3&"!A1:C1"),$A1)

シート名、および、セル範囲は適当に変更してください。
変数として使用しているセル範囲 E1:G1 は、必要なシート数だけ用意してください。

ちなみに、「シート50からシート60までの10枚のシート」とありますが、欠番がないなら11枚のシートですから、11個の変数が必要です。

お礼

ありがとうございます。
#ref!が出てきてしまうので、もう少し修正してみます。

補足

squipさんの回答を参考に
Ｃ１とＤ１をまとめてしまって

Ｃ１の式
IF(SUM(P2:V2)>=2,"！",IF(SUM(P2:V2)=1,"○",""))

Ｄ１：Ｎ１の式をそれぞれ
D1=COUNTIF('50'!$A$1:$AW$999,$F2)
E1=COUNTIF('51'!$A$1:$AW$999,$F2)
↓
　　　　　　↓
　　　　　　　↓
N1=COUNTIF('60'!$A$1:$AW$999,$F2)

とすることで、なんとか解決できました。
（INDIRECTの使い方が良くわからなかったので・・・、
　こちらはまた改めて勉強します）

初歩的な計算式にしてしまいましたが、「考え方」を教えていただき
ありがとうございました。

ほんとうに助かりました。

回答No.1

imogasi

この問題に対しては自信がありませんが、先鞭をつけて感想を述べます。まず問題のニーズが珍奇で実感できない。問題の説明がわかりにくい。
まず２つの問題がある（質問では明確な分けた書き方でない）
（１）１０枚のシート各々を対象に、それぞれにシートのW1:AW999間での範囲で、ある決まったデータがあるかどうかチェック
A.エクセル関数では、MATCH関数があるくらいで、MATCH関数は複数行を対象にはできない。似た関数のVLOOKUPも同じ。他に適当な関数はないと思う。ですから関数ではできない。
B。１０シートを対象にそれぞれのシートを調べて、全体として１つでも、該当セルがあるかどうか、はエクセル関数ではできにくい。
結論的にVBAで（それも熟練したレベルの技量で）やらざるを得ないが、質問にはVBAの言葉も出ていないが、できるのかな。
ーー
（２）A.あるシートがシート１のデータを参照しているかどうか、をチェックするのも難しい
B。２つ以上のシートから参照しているという条件も難しい。
ーー
似たようなことは、手操作だが
ツールーワークシート分析ー参照元のトレース（参照先のトレース）
がある。しかし手操作で見てどうのこうのするのであるから本質問では生かせないでしょう。
VBAでは
Sub Macro1()
　　Range("A1").Select
Selection.ShowDependents
End Sub
Sub Macro2()
Range("C4").Select
Selection.ShowPrecedents
End Sub
ですが,
これ以上参照先のセルの有無の判断をプログラム上で進めるには、VBA情報があるのかわからない。
WEBを探したら
http://www.dailydoseofexcel.com/archives/2004/06/01/cell-precedents/
ガあり、外国のものなので、コメントなど削除して修正したものを載せておく。単純な例ではうまく動いた。
Sub ListPrecedents()

Dim rStart As Range
Dim rPrecCells As Range
Dim cell As Range
Dim sPrecList As String

Set rStart = Sheet1.Range("A1")
On Error Resume Next
Set rPrecCells = rStart.Precedents
On Error GoTo 0

If Not rPrecCells Is Nothing Then

For Each cell In rStart.Precedents.Areas
sPrecList = sPrecList & cell.Address(0, 0) & ","
Next cell

sPrecList = Left(sPrecList, Len(sPrecList) - 1)
Else
sPrecList = "No Precedents Found"
End If

MsgBox sPrecList

End Sub

Sub ListDependents()

Dim rStart As Range
Dim rPrecCells As Range
Dim cell As Range
Dim sPrecList As String

Set rStart = Sheet1.Range("A1")
On Error Resume Next
Set rPrecCells = rStart.Dependents
On Error GoTo 0

If Not rPrecCells Is Nothing Then

For Each cell In rStart.Dependents.Areas
sPrecList = sPrecList & cell.Address(0, 0) & ","
Next cell

sPrecList = Left(sPrecList, Len(sPrecList) - 1)
Else
sPrecList = "No Dependents Found"
End If

MsgBox sPrecList
End Sub
後者の変数名sPrecListなどは、sDepenListなどと変えるべきかもしれないがそのままにしている。
他シート参照の扱いも難しそうだ。
これを使って質問の回答にこぎつけるのは、小生も息切れ。

お礼

質問内容が不明確で、申し訳ございませんでした。

VBAでも難しいとのことですので、断念します。

いろいろとお考え頂き、ありがとうございました。