スプレッドシートの入力規則

Question

googleスプレッドシートでA列に名前、B列に区分、C列に備考、D列に結果を出したいと思っています。
別のリストに
カテゴリAの区分1から10(文字列です)
カテゴリBの区分1から10
カテゴリCの区分1から10
カテゴリDの区分1から10
カテゴリEの区分1から10
を作る(変動があり、カテゴリ毎に1から10の値は違います)

A1のセル(どこでもいい)にカテゴリA-Eを選択
A1で選択したカテゴリによりB列(区分)の列にプルダウンでリストを作る
A1のカテゴリとB列(区分)の条件でD列に結果を表示する。

以上の内容がE列-H列、I列-L列、、×10くらい繰り返し
というものを作りたいのですが、可能でしょうか？？

カテゴリから区分を絞り込んで結果を表示するイメージです。

=====追記======
お早いお返事ありがとうございます！！！！

雑ですがサンプルを作ってみました。
https://docs.google.com/spreadsheets/d/143NJdAt-1GMYHN_aVUoQ7HCdVQ8GSCz_DcdYDgktSAo/edit?usp=sharing

金額のリストは合計で50近くあり、変動があります。
個別リストは各担当者に1ファイルづつ、一人につき最大で15クラスを持っています。
わかりにくいようでしたら申し訳ございません。。

Comments

[sawa]

ご質問の細かい要件がわかりづらいので、ダミーのサンプルでいいので、元になるリストの配列イメージと、こういう結果が欲しいというイメージがあった方がいいです。

[Cabe]

お返事ありがとうございます。
追記してみました。ご確認いただけたら幸いです。

Answer 1

サンプル拝見しました。
クラスが違っても区分が結構重複するようなので、選択肢を変動させずに クラスと区分にマッチした金額を返し、クラスによって選択できない区分を選ぶとエラー「選べません」を金額の欄に返す形にしました。

ご希望と違う場合はすいません。

前提と下準備：
・金額リストのデータがある行のA列の空欄箇所は全て埋めてください。
（例 A2:A5にも 一般クラス と入れる）
・金額リスト1行目は、わかりやすくする為にもタイトル行とした方がいいです。
（1行挿入し A1にクラス、B1に区分、C1に料金 といった感じ）
・金額リストは、とりあえず100行まで対応としています。（とりあえず上限区切ってます）
・このようにした上で、金額リストのE列、F列あたりに 選択肢の列を用意します。
以下のように数式を入れる
E2 → =UNIQUE(A2:A100)
F2 → =UNIQUE(B2:B100)

添付の画像のような感じになります。

あとは、個別リストの方で A3のクラス選択とC列の区分選択で、
データの入力規制で 金額シートのE列、F列の選択肢 を範囲として指定。

D5に数式

=ARRAYFORMULA(IFERROR(IF(C5:C34="","",VLOOKUP(A$3&"_"&C5:C34,{ARRAYFORMULA('金額リスト'!$A$2:$A$100&"_"&'金額リスト'!$B$2:$B$100),'金額リスト'!$C$2:$C$100},2,FALSE)),"選択できません"))

を貼付け。
Arrayformula使いたかったので 古典的な2条件をVLOOKUPする為にクラスと区分を"_"で繋いだ、仮想の行を用意して検索させ金額を引っ張ってきてます。IFで区分が空白時の処理、IFERROで クラスによって選べないものを選択したら「選択できません」を返しています。

A~E列をコピペすれば 横に増やしていけます。

もし、実際はクラスごとに区分が大きく変わるって場合は、やはり区分の選択肢を変動させた方が良いのですが、その場合は数式よりもGASでの処理になってきます。
ある程度ご自身で勉強された上で、Google Apps Script のタグをつけて質問されると良いと思います。

Comments

[Cabe]

ありがとうございます！！
実際試してみましたが求めていた内容ですごく使いやすくなりました！！

[Cabe]

ちなみにこちらの内容でフリーで入力も可能にすることはできますでしょうか？

[sawa]

フリーで入力というのは、個別リストの区分とか金額の欄でしょうか？
区分でしたらリストから選ばないと警告は出ますが入力は可能です。
金額の方は Arrayformula で一括で式を入れちゃってるので、このままだとフリー入力すると金額列の他の箇所がエラーになっちゃいます。
金額をフリー入力する可能性があるなら、

=IFERROR(IF(C5="","",VLOOKUP(A$3&"_"&C5,{ARRAYFORMULA('金額リスト'!$A$2:$A$100&"_"&'金額リスト'!$B$2:$B$100),'金額リスト'!$C$2:$C$100},2,FALSE)),"選択できません"))

という感じでArrayformulaを外す必要があります。（Arrayformula使わないなら、違う数式使った方がすっきりしますが。。）

これをD5に貼り付けてからあとは下にコピペといった感じで、1個ずつ数式を入れた形にしてあげれば部分的に数式をつぶしてフリー入力しても他には影響がありません。

ただフリー入力をOKとしちゃうと、誤って数式を消してしまったり、一度フリー入力したところは数式が無い状態になるので、その点を運用上気を付ける必要があります。

例えば 条件付き書式で =NOT(ISFORMULA(D5)) として、数式じゃないところは色付けしてわかるようにする等

[Cabe]

なるほどですね！！！
ご丁寧にありがとうございます！！！！
やってみます！！！！