[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数の別シートからドロップダウンリスト選択』（kazu）
Sheet1（A1）の先頭行：果物（テーブルとしての見出し項目）
A2:りんご
A3:みかん
Sheet2（A1）の先頭行：果物（テーブルとしての見出し項目）
A2:ぶどう
A3:いちご
任意のセルにデータの入力規則でリスト（ドロップダウンリスト）として
すべての果物（りんご、みかん、ぶどう、いちご）を表示したいのですが
不可能でしょうか？
同一シート内であれば可能ですが複数のシートになるとできません。
どうぞよろしくお願いいたします。
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
りんご,みかん,ぶどう,いちご
ってやりゃいいじゃん。
（ベタ） 2022/05/18(水) 01:06
個数が少なければそれでもいいのですが
もっと種類やシート数が増えた時に対応できるやり方が
ないかと悩んでおります。
（kazu） 2022/05/18(水) 07:04
（γ） 2022/05/18(水) 07:33
>個数が少なければそれでもいいのですが >もっと種類やシート数が増えた時に対応できるやり方
どっちにしろ自分で手を加えなきゃならないなら、 同じじゃん。 数ってどれくらいか解らないけど、文字数制限以上？ 言っている意味がわからない。 上記でも書かれているように、それ専用のリスト範囲を作っておくべきでは。 何が不満なんだか。 （ベタ） 2022/05/18(水) 12:03
みなさんと回答をまとめると
Sheet1 が
[A]
1 果物
2 りんご
3 みかん
Sheet2 が
[A]
1 果物
2 ぶどう
3 いちご
のときに、
Sheet3 に
[A]
1 果物
2 りんご
3 みかん
4 ぶどう
5 いちご
とする式は？という問題に帰着しますね。・・・・(1) (当然ながらSheet1,Sheet2に行追加されたらSheet3も追従）
あと、Sheet3を入力規則のリストにするときに、余計な空白を含まない様にする方法・・・(2)
(1)は、If関数とかIndex関数とかCountA関数とかごにょごにょすれば出来そう（やってません） (2)はリスト範囲は名前を定義して名前の範囲が可変になるようにすれば出来そう（昔やったことある） （´・ω・`） 2022/05/18(水) 12:39
＞ とする式は？という問題に帰着しますね。・・・・(1) ＞ (当然ながらSheet1,Sheet2に行追加されたらSheet3も追従）
うーん、、シートが増えた時にも対応しないとならないので、数式はかなり面倒だ思います。 VBAで対応した方が楽ではないですかねぇ。
※ VBAがダメな事情があったら意味ない話ですけど。
（半平太） 2022/05/18(水) 12:54
半平太さん >シートが増えた時にも対応しないとならないので 確かに最初の質問では「複数のシート」とあるので、 3シート以上の場合もあるかもしれませんが まず2シートの場合から考えればいいのでは？
>VBAで対応した方が楽ではないですかねぇ 私にとってはVBAの方が楽ですが、質問者さんにとってどうかはわかりませんので保留 PowerQueryでも出来そうですけど。。。 と考えはじめると PowerQueryが一番楽かもしれない （´・ω・`） 2022/05/18(水) 13:07
いえ、私は、シート数の多寡の問題じゃなく、 途中から増えるのが面倒だなぁと思ったと言うことです。
これにレスは不要ですけど。
（半平太） 2022/05/18(水) 13:19
（閲覧者） 2022/05/18(水) 21:02
自分自身、皆さまと違いまだまだexcelに関して未熟ですので
色々と不快にさせてしまい申し訳ありませんでした。
シートを複数ではなく１つにまとめる等能率の良い方法はあると思いますが
今回はどうしても複数という条件下でデータの入力規則に反映される方法を模索しておりました。
γ様がおっしゃられた
＞ちなみに、入力規則のリストでは、ユーザー定義関数や配列定数も使用することはできません。
かなり自由度は低いです。
（ついでに言えば、条件付き書式は、ユーザー定義関数は使えます。これと対照的です。）
↑
ありがとうございます。やはり自由度が低かったのですね。納得できました。
そうなりますとやはり抽出用のシートを１枚作製する必要があるので
（´・ω・`）様がおっしゃられた
＞みなさんと回答をまとめると
Sheet1 が
[A]
1 果物
2 りんご
3 みかん
Sheet2 が
[A]
1 果物
2 ぶどう
3 いちご
のときに、
Sheet3 に
[A]
1 果物
2 りんご
3 みかん
4 ぶどう
5 いちご
とする式は？という問題に帰着しますね。・・・・(1)
(当然ながらSheet1,Sheet2に行追加されたらSheet3も追従）
上記を表す式（関数の組み合わせ）が必要となりますが、
いろいろ考えましたが自分の力では答えに行き着く事ができません。
ちなみにVBAを使用すれば楽にできるとは思いますが恥ずかしながら
自分はVBAの知識はほとんどありませんので手間がかかったとしても
何かしらの関数を使用してご教授して下さる方がいましたら幸いです。
（kazu） 2022/05/18(水) 21:06
こんばんは〜（kazu）さんへ
外してたらごめんなさい。スルーしてください。^^; 果物（ テーブルとしての見出し項目 ）の選択が、果物で分けるなら果物1と、 果物2が多分必要かと思われます。
（´・ω・`）さんと多分ですが、似たりよったりの事を言うと思います。
リストを下記のように作成します。 果物、野菜、飲料、を名前定義しといて ←内容はサンプルなのでご了承を
リストは、メインではないので一番右側のシートが よいと思います。
●仮にシート3とします。（リストです）
|[A] |[B] |[C] |[D]
[1]|区別|果物 |野菜 |飲料
[2]|果物|りんご|白菜 |日本酒
[3]|野菜|みかん|椎茸 |焼酎
[4]|飲料|ぶどう|玉ねぎ|ビール
[5]| |いちご|人参 |ワイン
各シートの何枚でも設定可能で、例として Sheet1（A1）の先頭行：区別（テーブルとしての見出し項目）を ドロップダウンにて、リストから、果物、野菜、飲料と好きな項目を選択します。
そして（A2）を選択して入力規則に、=INDIRECT($A$1) と入力規則で設定します。 で、下に好きな範囲までフィルします。
すると、（ A2〜最終行はお好きなだけ設定した範囲に ） 見出し項目に、果物を選択したら リスト範囲の（B列）の品名全てを選択可能になります。
他の項目、野菜や飲料を選択した時は、リスト範囲の （C列）,（D列）の登録してある品名全てを選択が可能になります。
将来的に、シートが増える事が考えられる場合は 予め余分にシートを作成しておいて、使わないときは非表示に すればよいと思いますです。
こんなかんじで？伝わりますか？
（あみな） 2022/05/18(水) 21:44
=IFERROR(IF(ROW()<=COUNTA(テーブル1[#データ]),INDEX(テーブル1,ROW()),INDEX(テーブル2,ROW()-COUNTA(テーブル1[#データ]))),"") とか （´・ω・`） 2022/05/19(木) 09:26
あみな様、こんな自分のわがままに長文で返信してくださり恐縮です。
今回の自分の要望は
Sheet1 が
[A]
1 果物
2 りんご
3 みかん
Sheet2 が
[A]
1 果物
2 ぶどう
3 いちご
のときに(Sheet1,2ともに果物が見出しのテーブル）
Sheet3 (果物が見出しのテーブル)
[A]
1 果物
2
3
4
5
のA2セルになにかしらの関数を組み込むと
Sheet3
[A]
1 果物
2 りんご
3 みかん
4 ぶどう
5 いちご
上記の表記にならないかと模索しておりました。
今回のあみな様のやり方を参考にさせていただこうとおもったのですが
=INDIRECT($A$1)のところでエラーが出てしまい先へ進めませんでした。
完全に自分の読解力不足です。
本来であればあみな様のやり方の方が能率がよく正しいやり方なのだと思いますが
今回はどうしても上記の縛りでのやり方になってしまうので少し趣旨がずれてしまうかもしれません。
自分もいずれあみな様のような応用力が身につけられるように精進して参ります。
本当にありがとうございました。
（´・ω・`）様、自分にとってはとても複雑な関係式をありがとうございます。
（´・ω・`）様の関数をそのまま参考にさせていただくと、A2セルにみかんとしか出てきませんでした。
自分の力量では今回の関数の組み合わせの意図自体
理解できておりませんので修正することもできませんでした。
折角提示してくださったにも関わらず活かし切れず申し訳ありませんでした。
もう一度勉強し直して何かしらの答えがないか模索してみようと思います。
（kazu） 2022/05/19(木) 23:29
Sheet1のA2〜A3、Sheet2のA2〜A3範囲が、リストとはちょっとびっくりです。^^; では、ここを基準にして( Sheet3 )以降にドロップダウンで選択出来ればいいのですね？
可能ですよ。但しですが、果物1、果物2 のように名前定義で別々に指定しなければ できません。名前定義の方法は、わかりますか？
Sheet3（A1）選択で、果物1の時は、りんご、みかん…が選択可能 果物2の時は、ぶどう、いちご…が選択可能です。終わったら果物に戻す。
入力規則でリスト（ドロップダウンリスト）として利用するなら これ以外は、私には今すぐ思いつかないです。
（あみな） 2022/05/20(金) 06:02
（´・ω・`）さんの式は、テーブルの「構造化参照」というものを使っています。 ヘルプ(*)で検索して読んでみて下さい #データは、ヘルプにある"特殊項目指定子"というもので、 テーブルの見出しを除いた本体部分を指しています。
(1)その式を必要数だけ下にコピーする必要があります。
(2)なお、１行目から出力する前提の式になっているので、もし２行目から出力するのであれば、
Row()をRow()-1 とする必要があるでしょう。
(*)【余談】 翻訳が中途半端でとても読みにくいですな。テーブルの例と説明で使う用語が不一致だし、 必要な翻訳が手抜きされているので分かりにくい。 例えば"特殊項目指定子"であれば、 #All は #すべて としないと使えません。 #Data は #データ 〃 #Headers は、#見出し 〃 MS社は、これくらいのローカライズにもっと手間をかけて欲しいですよ。 地の説明文ではなく、数式の要素なんですから。
---------------- ところで、 =Sheet1!A2 =Sheet1!A3 =Sheet2!A2 =Sheet2!A3 として、追加削除の都度、手入れするのではそれほどまずいですか？ （´・ω・`）さんの式も、3つ4つと数が増えるとメインテナンスしにくくなると思います。 上の方式のほうが直接的で分かりやすくないですか？
（γ） 2022/05/20(金) 07:26
あみな様、とても分かりやすく簡単な方法を教えていただきありがとうございます。
名前定義のやり方は今回の件以外でも色々と応用できそうなので今後も使用させていただきます。
γ様、とても分かりやすく丁寧な解説をしていただきありがとうございます。
おかげで式の意図も理解することができました。
自分自身テーブルを多用しますので構造化参照（指定子）は必ず覚えておかなければなりませんね。
Sheet3のA2セルに数式を入れて下までフィルした瞬間すべての果物名が表記された時は感動しました。
こんな式を即座に思いつける（´・ω・`）様も、この式をすぐに理解して分かりやすく解説できる
γ様も凄いとしか言えません。
もしシートが３枚になった場合の式は・・・一度自分で考えてみます。
＞ところで、
=Sheet1!A2 =Sheet1!A3 =Sheet2!A2 =Sheet2!A3 として、追加削除の都度、手入れするのではそれほどまずいですか？
恥ずかしながら上記の発想自体ありませんでした汗
難しく考えすぎてましたね。
ただ、（´・ω・`）様の式は一度完成させればシートが増えない限りは自然に対応してくれるので
自分の力をつけるという意味でも使用してみたい感じはあります。
（kazu） 2022/05/22(日) 22:36
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.