[[20041015135059]]　『すべてのシートから検索したい』（TK）　ページの最後に飛ぶ

『すべてのシートから検索したい』（TK）

以前ここで教えていただいて作成した予定表で、
1つのシートにすべて書き込んでいたのですが、
多くなってきたので、シートを月ごとにわけて作り直しました.

作り直したので、当日の予定を検索していたシートが、他のシートを検索範囲にならない為
当日予定が表示されません。数式をどのように変えれば、今までどおり検索できますか？

シート１（当日予定表)

　A　　　　　B　　　　　　C　　　　　　　　　　　D　　　E　　　　　　F　　　　G　　　　　　H 　　　　　I　　　　J

	発行日	 発行番号 	 拠点地 	時間	 住所 	 POI 	 担当者 	 連絡先 	 備考 
１　日付		  	  		  	  	  	  	  
２　＝TODAY()		  	  		  	  	  	  	  
３　曜日

でB2～J13までのセルすべてに
　　　=IF(COUNTIF(予定表一覧4月!$B:$B,$A$3)=0,"",IF(COUNTA(OFFSET(予定表一覧4月!$B$3,MATCH($A$3,予定表一覧4月!$B$3:$B$8541,0)-1,0,ROW()-1,1))=1,OFFSET(予定表一覧4月!$B$3,MATCH($A$3,予定表一覧4月!$B$3:$B$8541,0)+ROW()-3,COLUMN()-1),""))

と入力されています。この式だと予定表一覧4月の中からしか検索できないので
すべてのシートから検索できるようにしたいのです。
この式を変えてすべてのシートから検索するにはどのようにしたらよいですか？
お願いします.

 せっかく一つのシートへ収めていたのだから、分割する必要はなかったのでは？
検索を目的とするデータであれば、やはりデータは一つのシートへ収めるべき。
さて、本題。
上記の式でA3セルにTODAY関数、
シート名はTODAY関数の返す月のモノを参照する仕様とするには。
COUNTIF(予定表一覧4月!$B:$B,$A$3)であれば、
COUNTIF(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B:$B"),$A$3)
のように書き換え。
”参照を表現する文字列”を作成してINDIRECT関数で参照に変換します。
（みやほりん）

さっそくありがとうございます。
ところで置き換えは数式の"予定表一覧4月"となっているところすべてになるのでしょうか？
（TK)

 当然そうなります。
ですが、いきなり本番の数式で試さずに、
練習として適当なセルへ
COUNTIF(予定表一覧4月!$B:$B,$A$3)
や
MATCH($A$3,予定表一覧4月!$B$3:$B$8541,0)
などにINDIRECT関数を適用してみてA3へいろいろな日付入力で
INDIRECT関数がどんな動作をしているのか確認してみることを
お勧めします。
（みやほりん）

むむむ…　　よくわかりません　できれば数式をすべて教えていただきたいのですが…
若葉マークなもんで…（TK)　

 A3セルに入力されているTODAY関数が
「何月」を返しているか求めるのには
MONTH関数を使用します。
MONTH($A$3)
この点は理解できると思います。
さて、ここで、
「MONTH($A$3)を利用して参照するシートを
切り替えることが出来たら楽だなぁ～」なんて思ったりします。
　
そこで、
COUNTIF(予定表一覧4月!$B:$B,$A$3)
この数式を例に取ります。
　
予定表一覧4月!$B:$B
　
の部分が他のシートへの参照部分です。
これにMONTH($A$3)を組み込んでみます。
　
"予定表一覧"&MONTH($A$3)&"月!$B:$B"
　
なんて書くことは出来ないだろうか。
でも、これは「文字列」なので、そのままもとの式に放り込んで、
　
COUNTIF("予定表一覧"&MONTH($A$3)&"月!$B:$B",$A$3)
　
としたのではエラーがでます。
そこで、INDIRECT関数の出番です。
INDIRECT関数は”参照を表現する文字列”を元に実際の参照を行う関数です。
先に例示したように次のように書き換えることで目的の式となります。
　
COUNTIF(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B:$B"),$A$3)
　
同様に、数式中の
「予定表一覧4月!セル番地」　を
「INDIRECT("予定表一覧"&MONTH($A$3)&"月!セル番地")」へ
書き換えることで所期の動作を得られると思います。
（みやほりん）

ただいま格闘中ですがうまくいきません。
どこがおかしいのでしょうか？

=IF(COUNTIF(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B:$B"),$A$3)=0,"",IF(COUNTA(OFFSET(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B$3"),MATCH($A$3,(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B$3:$B$8541"),0)-1,0,ROW()-1,1))=1,OFFSET(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B$3"),MATCH($A$3,(INDIRECT("予定表一覧"&MONTH($A$3)&"月!$B$3:$B$8541"),0)+ROW()-3,COLUMN()-1),""))

となっています。

 ほぼ正解です。
カッコの数が合っていないだけ。2箇所の
MATCH($A$3,(INDIRECT
           ↑このカッコが不要。
（みやほりん）

できました。
ありがとうございます。
みやほりんさんからのレスがはいるまで、()の数を数えていました.
やはり()の数があっていなかったのですね。
本当にありがとうございました。
（TK)

 解決して何よりデス。
もう見てないかもしれませんが、多少手直しをするともっとすっきりします。
IF関数の部分は多少処理のダブリがあり、
OFFSET関数の部分がどうしても冗長ぎみ。
？月のレイアウトがいまいちわかっていませんが、
「たぶん」下記の数式でも機能するのではないかと推測します。

=IF(ISNA(MATCH($A$3,INDIRECT("予定表一覧"&MONTH($A$3)&"月!B:B"),0)),"",INDEX(INDIRECT("予定表一覧"&MONTH($A$3)&"月!B:K"),MATCH($A$3,INDIRECT("予定表一覧"&MONTH($A$3)&"月!B:B"),0),COLUMN()))

 （みやほりん）

ためしてみました。
いろいろあるんですね。
同じようにコピーしてみましたが、
Jの行だけ＃REF！になりました。
（TK)

 そっか、K列まであるんですな。
修正しときました。
（みやほりん）

コメント返信：

[ 一覧(最新更新順) ]

[[20041015135059]] 『すべてのシートから検索したい』（TK） ページの最後に飛ぶ

[[20041015135059]]　『すべてのシートから検索したい』（TK）　ページの最後に飛ぶ