エクセル 複雑な表の参照

質問

参照値に１つのデータを入れ、それに対応した複数のデータを検索結果として表示したいです。
しかも「複数のデータ」の数は一定ではありません。

例）
準備：『セルＥ1』以下に『都道府県名』と『各都道府県の市区町村名』の一覧表を作成
『セルA1』に『都道府県名』を入力することで、『セルＣ1』以下に『各都道府県の市区町村』を表示させたい

vlookupを使用すればよいのでしょうか？
それとも、他の方法があるのでしょうか？

よろしくおねがいします。

回答No.3

MackyNo1 · ベストアンサー

>準備：『セルＥ1』以下に『都道府県名』と『各都道府県の市区町村名』の一覧表を作成

この意味が添付画像のようなイメージなら、C1セルに以下の式を入力して下方向にオートフィルコピーするのが簡単です。

=INDEX($E:$L,ROW(A2),MATCH($A$1,$E$1:$L$1,0))&""

No1の回答者の例示された同じ都道府県は連続して入力されているレイアウトなら以下のような数式が計算負荷が少ないのでお勧めです。

=IF(COUNTIF(E:E,$A$1)<ROW(A1),"",INDEX(F:F,MATCH($A$1,E:E,0)+ROW(A1)-1))

都道府県がバラバラの行に入力されているなら以下のような数式になります。

=INDEX(F:F,SMALL(INDEX(($E$1:$E$100<>$A$1)*1000+ROW($E$1:$E$100),),ROW(A1)))&""

回答No.5

MackyNo1

>たとえば、Ａ行の上に5行追加すると…
=IF(OR(A$6="",COUNTIF(G:G,A$6)<ROW(A6)),"",INDEX(H$6:H$5005,SMALL(IF(G$6:G$5005=A$6,ROW(A$6:A$5005)),ROW(A6))))
の数式になり、#NUM!の表示が出てしまいます。

私の提示した数式を含めて、ROW(A1)のような関数部分は、セル範囲を取得しているのではなく、単純に1、2、3、・・・のような連続する数値を取得するための数式です。
したがって、セルを挿入すると自動的にセル番地が変わる（＝数値が変わる）ために期待した結果が得られないことになるわけです。
解決法は、どのセルに入力する場合もROW(A1)やROW(A2)の部分は固定して（この部分を修正して）入力してください。

＃このようなご質問では、リストのレイアウトや使用目的によって、より合理的な関数や処理も考えられますので、具体的なリストのレイアウト（セル番地や入力順）や使用目的（たとえばセルの値によって可変の入力規則のリストを作成したいなど）を例示されたほうが良いと思います。

お礼

みなさま回答ありがとうございました。

NO3の方法で、行挿入後ROWの部分を修正して思い通りの表が完成しました。

回答No.4

tom04

No.1・2です。
たびたびごめんなさい。

No.2は無視してください。
全く見当違いな回答をしていました。

本来すでに数式が入っているSheetに行（または列）を挿入・削除するのは好ましくありません。
お示しのような問題が出てきますので、
どうしてもデータを追加したい場合は
No.1で書いたようにデータはバラバラでも問題ありませんので
最終行の下へ追加していきます。
これでちゃんとデータが追加されても対応できると思います。

どうしても都道府県別にまとめたい場合は
別列に都道府県別に番号を付加しておき（北海道=1・青森=2・・・沖縄=47）のような感じで
行挿入せずにデータの最終行以降に追加していきます。
そしてその番号の昇順で並び替えを行うのはどうでしょうか？m(_ _)m

回答No.2

tom04

No.1です。

＞実はこの表の上に何行か追加するとうまくいきません。

今回の数式に限らず、すでに数式が入っているSheetに行を挿入すると
他の数式でもそのような問題が出てきます。
今回の場合、列は全く関係ないので列挿入は問題ないのですが、
列方向に関しての数式が入っていて（列方向にフィル＆コピーするような場合）は同様の問題が出てきます。

どうしても行挿入しなけらばならない場合の対処方法としては
挿入した行には作業列の数式が入っていませんので、一つ上の作業列のセルのフィルハンドルで数式をコピーしてみてください。
（一旦挿入した行に数式が入れば、フィル＆コピーした最後のセルのフィルハンドルでダブルクリックします）
これで作業列の最初に数式が入っていた最終行まで数式が新たにコピーされます。

結果の数式はそのままで大丈夫のはずです。m(_ _)m

回答No.1

tom04

こんばんは！
具体的な表のレイアウトが判らないのですが、
＞準備：『セルＥ1』以下に『都道府県名』と『各都道府県の市区町村名』の一覧表を作成
とありますので、↓の画像のように「都道府県名」の列と「市町村名」の列は別だというコトでの一例です。

↓の画像でC1セルに
=IF(OR(A$1="",COUNTIF(E:E,A$1)<ROW(A1)),"",INDEX(F$1:F$5000,SMALL(IF(E$1:E$5000=A$1,ROW(A$1:A$5000)),ROW(A1))))
これは配列数式になりますので、Ctrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　C1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
これをフィルハンドルで下へコピーすると、画像のような感じになります。
（E列のデータはバラバラでも構いません）

※　実際の表のレイアウトが判ればもっと具体的なアドバイスができると思います。
まずはこの程度で・・・m(_ _)m

補足

ありがとうございます！

自分の思っているイメージ通りのことができました。

ですが、実はこの表の上に何行か追加するとうまくいきません。
たとえば、Ａ行の上に5行追加すると…
=IF(OR(A$6="",COUNTIF(G:G,A$6)<ROW(A6)),"",INDEX(H$6:H$5005,SMALL(IF(G$6:G$5005=A$6,ROW(A$6:A$5005)),ROW(A6))))
の数式になり、#NUM!の表示が出てしまいます。

列の追加は問題ないようです。

数式の意味もわからず使わせていただいているのでいけないのですが、改善方法を教えていただけるとありがたいです。

よろしくおねがいします。