入力規則に使用する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

元データから以下のようにまとめる式はかなり複雑な式を駆使する必要がありますがOKなのでしょうか？

（この部分ができればその応用で担当者名も絞り込み表示できます）

　　A / 　　B / 　　C

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

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

麹町 / 牛込 / 高野台

霞ヶ関 / 戸山 / 中村橋

五番町    

たとえばA列の重複のない区名を表示させるなら、Sheet2のF1セルに以下の式を入力し。右方向にオートフィルしてください。

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

同様にF2セル以下に１行目の区名に対応する町名を表示するなら以下のような関数を入力し、右方向および下方向にオートフィルします。

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

上記の部分がOKでA1セルの区名とB1セルの町名を選択したときの担当者の名前を自動に表示させたいということなら、以下のような設定をします。

町名を上記の式と同様な式で右方向に列挙します。

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

同様に担当者名を上記の区名から町名一覧を作成した方法と同様にしてその下のセルに表示し、町名から担当者リストが選択できるようにします。

Answer 3

y sakuda　様

ありがとうございます。

もともとある表を使えればベストなのですが、重複する値がズラズラでてきてしまうので

表を作り変えてもいいのですが、どのように表を作りかえればいいのかわからないのです。

Sheet1

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

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

麹町 / 牛込 / 高野台

霞ヶ関 / 戸山 / 中村橋

五番町 /  / 

Sheet2

神田 /麹町 / 霞ヶ関 / 五番町 / 歌舞伎長 / 牛込

池田 /小川 / 留岡 / 留岡 / 真田 / 広田

小川 /南 / 西中島 / 柳 / 手塚 / ギャバン

西田 /川越 / 矢上 / 柳生 / 北村 / 大久保

と区と担当者を分けてシートを作成してもうまくいきませんでした。

今見本で書いている表は区/エリアと重複することがないのですが

別表で項目１・項目２・項目３とあり重複するケースでも使える式が

あればベストなのですが。

よろしくお願いいたします。

Answer 4

全く質問内容を勘違いしておかしなレスをいれました。申し訳ありません。

確認なんですが、

　　A / 　　B / 　　C

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

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

麹町 / 牛込 / 高野台

霞ヶ関 / 戸山 / 中村橋

五番町    

のような表を別に作成してあると言う前提での質問なのか、あくまでもSheet2にあると言う

↓の表だけからやりたいというのかどちらなんでしょうか？

　　A / 　　B / 　　C

区 / エリア / 担当

千代田区 / 神田 / 池田

千代田区 / 神田 / 小川

千代田区 / 神田 / 西田

千代田区 / 神田 / 梅岡

Answer 5

要するに"現在あるリスト"の方で実現したいということでしょうか？

極めて手抜きですが、こんな事はできます。

条件として、違う区に同じ地区名がないという前提（千代田区麹町と新宿区麹町　なんてことはないと言う意味です）

面倒なので、同一シートでの例を示します。

A列に区、B列に地区、C列に担当者

E1に区名を入力、

F1を地区名入力エリアとして、入力規則の式に

=OFFSET(A1,MATCH(E1,A:A,0)-1,1,COUNTIF(A:A,E1))

G1を担当者入力エリアとして、入力規則の式に

=OFFSET(B1,MATCH(F1,B:B,0)-1,1,COUNTIF(B:B,F1))

まあ、こう言う表の作りですと、地区名のところでは同じ名前が沢山ならんじゃいますが・・・・・

参考まで