[[20020613142527]]　『数式=sheet!a1のシート名をセルから参照２』（マッスル）　ページの最後に飛ぶ

『数式=sheet!a1のシート名をセルから参照２』（マッスル）

数式”=sheet!a1"の”sheet（シート名）”を変数型にしてあるセルから参照したい

ブック中にシートが３つ（Ａ，Ｂ，Ｃ）あるとします。

ＡシートからＢシートのa1セルを参照する場合、数式には

”=Ｂ!a1”のように記述しますが、

次回は同じＡシートからＣシートを参照するようにしたいのです。

Ａシートの中に参照先のシート名（ＢあるいはＣ）を記述して、

数式ではそこを参照するような変数形式にする為にはどのように

記述すればできますか？

 Ａシートの中に参照先のシート名（ＢあるいはＣ）を記述して････

AシートのB1をリスト選択するように設定します。

AシートB1を選択して、メニューの【データ】【入力規則】で入力値の種類を「入力値の種類」、元の値「B!A1,C!A1」とします。

AシートA1に　=INDIRECT(B1)　と式を入力します。

INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセルへの参照を変更することができます。

AシートのA1を表示させない場合は【入力規則】の元の値「B!A1,C!A1, 」と最後に半角Spaceを加えます。

AシートA1に　　=IF(B1="","",INDIRECT(B1))　と式を入力します。B1セルで空白を選択すればA1が表示されません。

B、CシートのA1にデータ未入力であれば、0が返されます。

 （シニア？）

質問が下手だったかもしれません。

やりたいことは、次のようなことです。

Ａシートの複数セルa2～a4に、ＢシートあるいはＣシートのa2～a4を表示させたいのです。

表示内容の対象をＢシートからＣシートに変更にする場合は、

Ａシートのセルa1に”Ｂ”あるいは”Ｃ”と入力することによって

Ａシートのセルa2～a4の表示内容を「Ｂ!a2～Ｂ!a4」から「Ｃ!a2～Ｃ!a4」へ

かえたいのです。

ＢやＣの部分を変数的にあるセルの値を適用するにはどうしたらいいですか？

よろしくお願いします。

 これは難問ですね？

BシートA2の参照式は=B!A2　CシートA2の参照式は='C'!A2で求められますが、Cシートの式はBシートの式と形式が変わっています。

 範囲名で参照すると範囲名の先頭セルの値が返されます。

しかし、Cの範囲名は拒否されますので、BシートのA2:A4の範囲名をBBとし、Cシートの範囲名をCCとします。

これを利用して、次のようにしてみました。

AシートA1にBB,CCとダブルで入力します。

AシートA2に =INDIRECT(A1)　と入力

AシートA3に =INDEX(INDIRECT($A$1),ROW()-1,1)と入力し、この式を下方複写します。

 ここで問題になるのは、A2以降の式が範囲内であれば参照範囲のセルが空白であれば0を返します。

範囲外のセルを参照するとエラー値が返されます。

エラー値を見掛上の非表示にするために条件付き設定をします。

 列番号Aを選択して、【書式】メニューの【条件付き書式】の設定で【数式が】【=iserror(A1)】とし、

次に【書式】をクリックして、フォント色を白に指定します。ok okでエラー値は背景色と同色になり、あたかも非表示になったようになります。

 尚、この場合範囲名で処理していますので、シート名とは関係しません。

範囲名を拡張すれば、A3の式を下方複写すればできます。

B2に =INDEX(INDIRECT($A$1),ROW()-1,2)を入力し下方複写すれば、範囲名の2列目も表示されます。

苦肉の策ですが、確かめて下さい。

 （シニア？）

ありがとうございました。

上記のヒントにより、こうすることにしました。

Ａシート：表示シート、Ｂ／Ｃシート：表示元ネタ（それぞれa2:b5にデータ有り）

Ａシートの設定）

a1選択、データ－＞入力規制、入力値の種類：リスト、元の値：Ａ,Ｂ

a2より下のセル内容：　=INDEX(INDIRECT($A$1&"!"&"a2:b5"),ROW()-1,1)

b2より下のセル内容：　=INDEX(INDIRECT($A$1&"!"&"a2:b5"),ROW()-1,2)

Ｃ列以下、同様

a1をＢ／Ｃに切替ることにより、Ａシートの表示内容がＢ／Ｃの内容に切替る。

感謝！

（マッスル）

 さすが、ヒントで実行できるように改変活用されたのですね！

素晴らしい応用力の持ち主ですね、今後の力量の進展に期待します。

 （シニア？）

追加の質問です。

２回目のシニア？さんの回答にもあるように、参照範囲のセルが空白の場合、

0を返して0を表示します。

 この0を表示ない（ブランク表示させたい）ような方法はありますか？

私の知識では=if(数式=0,"",数式)の形で、えらく長くなります。

 えらく長くなる部分については、[[20020615153730]]で別質問を挙げました。

（マッスル）

 すべての0を表示させたくないのであれば、ツールメニューの【オプション】【表示】「ゼロの値」のチェックをOFFにすればできます。

前回のエラー処理に条件付き書式を利用しましたが、追加を選択して、

【数式が】【=A1=0】とし、次に【書式】をクリックして、フォント色を白に指定します。

 （シニア？）

コメント返信：

[ 一覧(最新更新順) ]

[[20020613142527]] 『数式=sheet!a1のシート名をセルから参照２』（マッスル） ページの最後に飛ぶ

[[20020613142527]]　『数式=sheet!a1のシート名をセルから参照２』（マッスル）　ページの最後に飛ぶ