入力規則に使用するOffset関数とリストの作成方法について

Question

Sheet1に入力規則からデータ入力するリストの作成について教えてください。

　A　/　　B　　/　C

　区/　エリア/担当者　

A列：　入力規則から選択

B列：　入力規則で区のエリアが選択できるようにする

C列：　AとBの組み合わせで担当者が選択できるようにする

例

A列　：千代田区

B列　：表示されるリストは神田・麹町・霞ヶ関・五番町

C列　：B列で麹町を選択した場合は、小川・南・川越(担当者）が選択可能

Sheet2

現在あるリスト

　　A / 　　B / 　　C

区 / エリア / 担当

千代田区 / 神田 / 池田

千代田区 / 神田 / 小川

千代田区 / 神田 / 西田

千代田区 / 神田 / 梅岡

千代田区 / 麹町 / 小川

千代田区 / 麹町 / 南

千代田区 / 麹町 / 川越

千代田区 / 霞ヶ関 / 留岡

千代田区 / 霞ヶ関 / 西中島

千代田区 / 霞ヶ関 / 矢上

千代田区 / 霞ヶ関 / 木村

千代田区 / 五番町 / 留岡

千代田区 / 五番町 / 柳

千代田区 / 五番町 / 柳生

新宿区 / 歌舞伎町 / 真田

新宿区 / 歌舞伎町 / 手塚

新宿区 / 歌舞伎町 / 北村

新宿区 / 牛込 / 広田

新宿区 / 牛込 / ギャバン

新宿区 / 牛込 / 大久保

新宿区 / 戸山 / 米山

新宿区 / 戸山 / 馬場

新宿区 / 戸山 / 鐘崎

新宿区 / 戸山 / 土方

新宿区 / 戸山 / 四谷

練馬区 / 富士見台 / 三和

練馬区 / 富士見台 / 細川

練馬区 / 富士見台 / 古河

練馬区 / 高野台 / 赤坂

練馬区 / 高野台 / 斉藤

練馬区 / 高野台 / 藤原

練馬区 / 中村橋 / 菊川

練馬区 / 中村橋 / 鉄河

練馬区 / 中村橋 / 羽田

縦横可変範囲の名前定義でリストする方法を掲載しているページをみつけました。

Sheet2のリストが以下の場合

=OFFSET(Sheet1!$A$2,0,MATCH(!$A2,区,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH(!$A2,区,0)-1))-1,1)

　　A / 　　B / 　　C

千代田区 / 新宿区 / 練馬区

神田 / 歌舞伎長 / 富士見台

麹町 / 牛込 / 高野台

霞ヶ関 / 戸山 / 中村橋

五番町    

で2つの範囲が入力規則に設定できますが、それ以上の場合の設定は可能でしょうか？

参考にしながらリストのシートを分けて作成しようとしたのですが、うまく設定できませんでした。

条件(リストのデータ）が複数の場合はどうすればいいでしょうか？

リストを作り変えることで選択可能であれば、リストの作成方法を教えてていただけますようお願いします。

Answer 1

複雑な数式を使う必要があるので、以下のような関数をそのまま入力して、うまく作動するかチェックしてください（こちらではうまく作動することを確認済みです）。

Ｓｈｅｅｔ２にリストの元データがある場合、まずＳｈｅet3のＡ１セルに入力して右方向にオートフィルして重複のない区名が表示できるか確認してください。

=INDEX(Sheet2!$A:$A,SMALL(INDEX((MATCH(Sheet2!$A$2:$A$1000&"",Sheet2!$A$2:$A$1000&"",0)<>ROW($A$2:$A$1000)-1)*1000+ROW($A$2:$A$1000),),COLUMN(A1)))&""

同様にＡ２セルに以下の式を入力し、右方向および下方向にオートフィルしてエリア名を表示してください。

=INDEX(Sheet2!$B:$B,SMALL(INDEX(((MATCH(Sheet2!$B$2:$B$1000&"",Sheet2!$B$2:$B$1000&"",0)<>ROW($A$2:$A$1000)-1)+(Sheet2!$A$2:$A$1000<>A$1))*1000+ROW($A$2:$A$1000),),ROW(A1)))&""

これでうまく表示できたら、次にＳｈｅｅｔ１のB１セルを選択し、名前定義で以下のように設定してください。

区：=OFFSET(Sheet3!$A$1,0,0,1,COUNTIF(Sheet3!$1:$1,">"""))

エリア：=OFFSET(Sheet3!$A$2,0,MATCH(Sheet1!$A1,区,0)-1,SUMPRODUCT((OFFSET(Sheet3!$A$1:$A100,0,MATCH(Sheet1!$A1,区,0)-1)&""<>"")*1)-1,1)

これでA1セルに「=区」B1セルに「=エリア」と入力規則のリストを付ければ表示することができます。

この入力規則が成功したら、上記と全く同じ設定でSheet4のA1セルに横方向にSheet2のB列のデータを表示させ、担当者をその下のセルに表示するリストを作成し、この横方向のエリアを「エリア２」として名前定義し、同様の入力規則をB列とC列の間で設定してください。

Answer 2

確認が大変遅くなりましたが、教えていただいたそのままコピーすればできました。

動作は確認できたのですが、式の意味が理解できていないので時間をかけて式をばらして

キチンと考えて次に生かせるようにしたいです。

また助けていただくことがあるかと思いますが、よろしくお願いいたします。

本当に、ありがとうございました。感謝です。

Answer 3

本当に説明下手ですみません。

できたというのは、このような表を別シートに作成したときに

区を選択して、エリアが出てくるような入力規則の設定はできたという状態です。

　　A / 　　B / 　　C

千代田区 / 新宿区 / 練馬区

神田 / 歌舞伎長 / 富士見台

麹町 / 牛込 / 高野台

霞ヶ関 / 戸山 / 中村橋

五番町

名前の定義で作成

区：=OFFSET(Sheet2!$A$1,0,0,1,COUNTA(Sheet2!$1:$1))

エリア：=OFFSET(Sheet2!$A$2,0,MATCH(!$A1,区,0)-1,COUNTA(OFFSET(Sheet2!$A:$A,0,MATCH(!$A1,区,0)-1))-1,1)

別の入力シートに入力規則として

A列：区・B列：エリアを設定すれば、A列で選択したエリアがB列に出てくるようにはできましたが

この方法ではC列の担当者を入力規則から選択させることができません。

そのために、リスト表を作り変えようと考えていますが、どのように作ればいいのか困っています。

教えていただいた、INDEXを使った式で、現在あるリストと同一シートに

区とエリアの表と、エリアと担当者の表が作成できましが、リストは今後も更新されるのですが

どうやって名前をつければいいのでしょうか？

今後もデータ量が増えていったり、削除したりとリストが更新されているので

今のリストと同一シートに表をつくるよりは別シートにしたほうがいいのかなぁと考えているのですが。

表をいちから作り直していいので、入力規則でA/B/Cと複数条件で表示される内容が変わるようにできないでしょうか？

そのためには、どのようにリストの表をつくって名前を定義すればいいでしょうか？

Answer 4

>A1で新宿区を選択した場合

>B1セルの入力規則で表示されるのは歌舞伎町/牛込/戸山と新宿区のエリア

>B1セルでエリア歌舞伎町と選択した場合、C1セルには歌舞伎町を担当している人の名前

>真田/手塚/北村が選択できるような入力規則の設定はできないでしょうか？

A1セルで区名を選択したときにＢ１セルで該当する町名を表示する入力規則を設定する部分はクリアできているのですよね。

もし、それができているなら、上に提示した式を用いて、町名を横方向に、同様に参照範囲を変更した数式でその担当者名をその下に表示したリストを作成すれば、区名から町名を絞り込む場合と同様の入力規則を設定すれば、町名から担当者を絞り込むことができます。

Answer 5

マックちゃん 様

お世話になります。

今あるデータの表をまとめるのが複雑であれば表は手入力で作り変えてもいいのですが

名前の入力規則で設定するために、どのようなリスト表を作成すればいいのかわからないのです。

元表の例をだしてしまったのでわかりにくい質問になってしまって申し訳ないです。

A　B　C　列に入力規則から値を選択したい表があります。

A1で入力規則から選択した後、B列の入力規則にはA列で選択したものによって表示される内容が変わるように

同様にC列もB列で選択した内容によって表示内容が変わるように設定することはできないでしょうか？

A1で新宿区を選択した場合

B1セルの入力規則で表示されるのは歌舞伎町/牛込/戸山と新宿区のエリア

B1セルでエリア歌舞伎町と選択した場合、C1セルには歌舞伎町を担当している人の名前

真田/手塚/北村が選択できるような入力規則の設定はできないでしょうか？

担当は何箇所かのエリアを受け持っている場合があります。

名前の定義でOffset関数をつかって設定

入力規則で名前をつかって設定できるかな？と思ったのですが、担当者を表示することができませんでした。

なにかいい方法がありましたら、ご教示いただけますようお願いいたします。