関数とワールドカード+複数シートに跨る指定

質問

任意の行中にある文字列形式で記載された日付風データを
ワイルドカードでフックして
SUBSTITUTE関数で置換しようと思ったのですが
ワイルドカードの指定法が解らなくなってしまいました

例
　　　　　A列
１　　1987/10/05日
２　　　　・
３　　　　・
４　　　　・
５　　1972/07/27日
６　　　　・
７　　　　・
　　　　　…
　　　　　…
　　　　　…

=SUBSTITUTE(A:A,"*/??/*","0")

あともう一つ
関数で参照する対象シートを切り替えたいのですが
どうも良いアイディアが浮かびません。

Offset構文での第1引数にIndex構文を置き
例えばシート1のA3にAN、シート2のA3にBen、シート3のA3にCanと名付けて、シート4の適当なところに
=Offset(Index((AN,Ben,Can),1，1，A1),0,0,247,1)
としてみたのですが、2007では駄目でした

でもそもそも
=index((Sheet1!A3:A250,Sheet2!A3:A250,Sheet3!A3:A250),247,0,Sheet4!A1)
とかできたら良いだけなのですが
此も駄目っぽくて…

代案として
=Choose(Sheet4!A1,Sheet1!A3:A250,Sheet2!A3:A250,Sheet3!A3:A250)
とかも考えたのですが
今一美的にどうかと…

2007だから駄目なのでしょうか?

何か他に良いアイディアがありましたら
どうぞ宜しくお願い致します。

回答No.2

abe_onesel · ベストアンサー

#1です。
初めに、私は正しい間違いを論じる専門家ではありません。
Nouble様は難しい言葉も多くご存じですので、私よりも詳しい方だと思います。
私が普段使用していない高度な内容も多く、私に答えられない事がほとんどです。
あまり参考になる回答ができないかと思いますが、私なりの意見を書かせてもらいます。

FIND関数については、ヘルプに書いてあるのであれば使えないのでしょう。
私はこういった形でFIND関数を使う事が無いのでお役に立てそうにありません。
配列数式については、私は全然使う機会が無いのでわかりません。
2003と2007の違いについても、私はわからないです。

INDIRECT関数の一例です。
Sheet1、Sheet2、Sheet3の３つのうち１つのシートのA3:A250の合計を出す場合です。

Sheet4のA1セルに文字列でSheet1と入力。
同じくA2セルに数式で、=SUM(INDIRECT($A$1&"!A3:A250"))
A1セルをSheet2、Sheet3に変更すると、それぞれのシートの合計を出します。
INDIRECT関数は文字列を使用するので、CHAR関数と組み合わせれば違う列の指定もできます。
=SUM(INDIRECT($A$1&"!"&CHAR(65)&"3:"&CHAR(65)&"250"))
CHRA(65)は半角のAですので、65～90で変更すればA列～Z列まで指定できます。
CHAR(COLUMN(A2)+64)とすれば、B列以降にコピーして使う事もできます。
AA列以降は式が複雑になるので、私は別シートにリストを作ってVLOOKUPで参照しています。

参考までに下記のように長くなります。
IF(COLUMN(A2)>26,CHAR(INT((COLUMN(A2)-1)/26)+64)&CHAR(MOD(COLUMN(A2)-1,26)+65),CHAR(COLUMN(A2)+64))
２００７だとZZ列の702列目まではこれで対応ができます。

参考ななるのが少しでもあれば幸いです。

お礼

度重なるご訪問の上大変有意義な御指南有り難う御座います

私自身今回ご回答頂いた内容により正直目から鱗が落ちました

というのも、
以前INDIRECT文の他セル参照に泣かされた経験がありまして
他シート参照の場合参照対象となる領域に名前を定義しておいて、
その名前によりアクセスするようにしないと
てっきり出来ないものという先入観を持ってました

ですが御指南を受けて
改めて今一度試してみたところちゃんと動作しました

ADDRESS文なども有用でしょうし
御陰様で又新たな可能性・新たなステージに進めそうです

本当に助かりました

有り難う御座います。

回答No.1

abe_onesel

参考までに、SUBSTITUTE関数ではワイルドカードを使えません。
そして、
=SUBSTITUTE(A2,"*/??/*","0")
というようにＡ列全体を選択するのではなく、一つのセルを選択して使うのが一般的です。
関数で置換するのであれば、FIND関数やSEARCH関数との組み合わせが必要です。
関数でなくても、Ctrl＋Hの置換であればワイルドカードを使えますので、そちらが便利かと思います。

もう一つの方は、INDIRECT関数を使えばどうにかなるものではないでしょうか？

お礼

ご教授有り難う御座います
記憶が定かでないので確認したいのですが
findでは正規表記が使えたでしょうか？

現在私の手元のパソには２００７しかないのですが
このヘルプによると使えないとされています

確かに２００３では使えたような気が私もするのですが
記憶が曖昧なうえ
確認することも叶わない状態なのです

あと
検索系の関数は
ファーストヒットしか対応していないように思うのですが
これについて何か対策をお持ちでしたら
お教え頂けないでしょうか

substituteは配列数式が扱える関数だと思ったのですが
間違いなのでしょうか

もう一点
indirectで参照するシートを可変的に切り替える
具体的な実例を示して頂ければ有り難いのですが
如何でしょうか

無理を言って申し訳ありませんが宜しくお願いします