Excelで表を切り替えて表示する

よくある運賃表などでの大人と子供の参照元の切り替えではなく、
表示する表そものもを切り替えたく思っています。

単一セルの場合には、
「あ」にa、「い」にb と名前を定義して
任意のセルに =indirect(A1) と入力し A1 に b などと記入すれば任意セルには「い」と表示されるはずです。

これの複数セルバージョンを行いたいということです。つまり表を切り替えたいということでして、
={"あ";"い";"う";"え";"お"} これにαと名付け
={"や";"";"ゆ";"";"よ"} これにβと名付け

単一セルの場合のA1への入力の様に簡単操作で表αとβを切り替える術はあるのでしょうか。

ワークシート上にはあまりαとかβを作成したくないので、名前や数式内で完結できればと悩んでいます。ちなみに「よ」に相当する文字列が256文字を超えているので文字列の途中で&でつないでいます。

No.2 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2015/06/28 03:00

={"あ";"い";"う";"え";"お"} などは、ワークシート上に置いておいてもいいし、名前のダイアログの中だけで定義する形でもいいです。

どちらでも構いません。後者の場合、「名前の管理」ダイアログの「参照範囲」を配列定数で指定すると、「値」欄には {...} と表示されると思いますが、気にしません。


ワークシート上のどこかに、表そのものではなく、「α」などの名前であれば表示してもいいという場合は、例えば

D1　=index(indirect(a1),3)

といった数式で「切替えが可能な名前を付けた参照」をすることができます。ワークシート上のどこにもαと表示してはならないのであれば、INDIRECT 関数を使わず

D2　=index(α,3)
　　あるいは
　　=index({"あ";"い";"う";"え";"お"},3)

といった記述でオッケーです。数式中での指定なので、これを切替え可能と呼ぶのがふさわしいかは分かりませんが。B1 セルに 0、1、2 といった番号を記入しても構わない場合は、

D3　=index(if(b1,α,β),3)　　　　…… B1 には数値を記入
　　あるいは
　　=index(choose(b1,α,β),3)　　…… B1 には 1 以上の整数を記入

で「切替えが可能な名前を付けた参照」になりますね。

この回答へのお礼

ありがとうございます。お陰で野望を実現できました。

セル範囲に名前を定義すると、名前の管理で確認した際に値の項目が配列定数的に表示されているので、そこで勘違いしindirect関数の引数に直接配列定数や配列定数に定義した名前を指定していたのがエラーの原因でした。
（そしてそのindirectをセル範囲に配列数式として入力して配列定数を表の形に表そうとしていました）

恐らくindirectはご紹介頂いたindexと異なり配列を引数にとれない関数だったのですね。
名前はOKだが配列定数はダメということは、「値」の項目では配列定数に見えなくもない「名前」中の個々の要素は、そう見えるだけで両者は実際には全然異なるということなのですね。たぶん。

ともかくご教授頂いた方法でうまくいき助かりました。ありがとうございました。

お礼日時：2015/06/28 20:29

No.1 回答者： esupuresso 回答日時： 2015/06/27 20:23

＞表示する『表そのもの』を切り替えたく思っています。

σ(￣、￣=)ﾝｰﾄ･･･、OFFSET関数を入れて名前の定義をして、VLOOKUP+INDIRECTで「大人」「子ども」の表をそっくりそのまま切り替えることが出来ます。

画像添付しますので参照して戴いて、大体こんな感じだよ～という事で説明します。
取り敢えずシート数は一応3枚用意しておいて・・・
Sheet2の表は「大人」用、Sheet3の表は「子ども」用として、予め表を作成しておきます。（シート見出しに「大人」とか「子ども」とか書き換えてもいいです。）
Sheet1に見出し列と見出し行のみを入力しておきます。

Sheet2のセルA2~E5までを選択し、「数式」タブより「定義された名前」グループ内の「名前の定義」をクリックすると「新しい名前」と書かれた表示画面が出ますので名前の所に「大人」と入力し「OK」で一度表示画面を閉じます。
次にSheet3のセルA2~E5までを選択し、同じように名前の定義の名前の所に「子ども」と入力して「OK」で閉じます。

空いている列（邪魔にならない列）に「大人」「子ども」と入力しておきます。（添付画像ではG2とG3のセル）
Sheet1のセルA1をクリックしておき、「データ」タブより「データの入力規則」をクリック→「入力値の種類」を「リスト」に変更し、「元の値」の枠内に　=OFFSET(G2,0,0,COUNTA(G2:G50))　の式を入れます。（ドロップダウンリストの設定をします、G2～G50までに名前を入力する事が出来ます）

Sheet1のセルA1にドロップダウンリストが出来てると思いますので、「大人」か「子ども」を選択しておきます。
同じくSheet1のセルB3に　=VLOOKUP($A3,INDIRECT($A$1),COLUMN(B1),0)　の式を入れて右方向と下方向に式をオートフィルコピーして完成です。

※表を増やす場合も手順は同じ要領なので、新しく表を作成したら範囲を選択して「名前の定義」から名前を付けてください。（必須）
新たに名前を作ったらドロップダウンリストにも名前が載るようにセルG4にも同じ名前を入力しておきます。（必須）

この回答へのお礼

ありがとうございます。確かにできますね。表そのものが切り替わりました。
自分は配列で一気に処理することにこだわりすぎていたようです。
お教え頂いたように 表をコピー＝含まれるセルを個々にコピー
といったわかりやすい考え方に立ち返る必要があるようです。
ありがとうございました。

お礼日時：2015/06/28 20:38