エクセルである表を作成したいのですが、、、

質問

エクセルで、　
例えば、A1に
　「関東」　と入力すると、B1からB7までに茨城、栃木、群馬、千葉、埼玉、東京、神奈川」と自動的に表示され、
同じようにA1に
　「東北」と入力すると、B1からB6に青森、秋田、岩手、宮城、山形、福島と、表示される方法はないでしょうか。

回答No.7

ham_kamo · ベストアンサー

No4、6です。補足拝見しました。

> OFFSET関数の基準がSHEET2のA2とあるのですが

えっと、これはこれでいいのですが、式全体の中で整合性がとれてないのでちょっと理解しにくくなってますね。OFFSETの起点をA2にして、次のパラメータで-1しているなら、はじめから起点をA1にするべきでした。

=IF(COUNTIF(Sheet2!$1:$1,$A$1)=0,"",IF(ROW(A1)>COUNTA(OFFSET(Sheet2!$A$1,1,MATCH($A$1,Sheet2!$1:$1,0)-1,100)),"",OFFSET(Sheet2!$A$1,ROW(A1),MATCH($A$1,Sheet2!$1:$1,0)-1)))

としても同じです。こちらの方がよかったかもしれません。

この式の本体は、
OFFSET(Sheet2!$A$1,ROW(A1),MATCH($A$1,Sheet2!$1:$1,0)-1)
の部分だけです。他の部分は、エラーが出ないようにするための条件判断です。まずそこだけ理解できればいいかと思います。

お礼

またの回答ありがとうございましたｍ（＿＿）ｍ
全部分かるようになりました（＾０＾）♪
エラーがでないよう関数を組み合わせてくださって、感謝です。

返信が遅くなってしまったのですが、表の作成も無事出来ました↑

どうもありがとうございました。

回答No.8

Cupper

＃１　Cupperです

解決したようですね。
でも、VLOOKUP関数でできませんでしたか。
簡単な例を乗せてみます。参考にしてください。

　　A　　 B　　 C　　 D　　 E　　 F　　 G　　 H　　 I　　 J
1　　　　　　　関東　茨城　栃木　群馬　千葉　埼玉　東京　神奈川
2　　　　　　　東北　青森　秋田　岩手　宮城　山形　福島
3
：

A1　入力
B1　=VLOOKUP(A1,C1:J2,2)
B2　=VLOOKUP(A1,C1:J2,3)
：
：
B7　=VLOOKUP(A1,C1:J2,8)

地方によっては7件に満たない都道府県のことがあるので空白の場合の処理を考えておく必要があります。
　対策1
参照先が空白の場合は空白を表示
B7　=IF(VLOOKUP(A1,C1:J2,8)="","",VLOOKUP(A1,C1:J2,8))
　対策2
参照先の空白セルに”－”などを入力しておく

お礼

なるほど～！！
Vlookupでも出来ますね！凄いですね！

回答、本当にどうもありがとうございましたｍ（＿＿）ｍ

とても役に立ちました（＾－＾）

回答No.6

ham_kamo

No.4です。
すみません、式に誤りがありました。（先の式では先頭の県名が表示されてませんでした）
以下に訂正します。

=IF(COUNTIF(Sheet2!$1:$1,$A$1)=0,"",IF(ROW(A1)>COUNTA(OFFSET(Sheet2!$A$1,1,MATCH($A$1,Sheet2!$1:$1,0)-1,100)),"",OFFSET(Sheet2!$A$2,ROW(A1)-1,MATCH($A$1,Sheet2!$1:$1,0)-1)))

補足

回答ありがとうございました。
初めて知る関数があり調べていたので、理解するのに時間がかかってしまいました。返信が遅くなってしまって、ごめんなさい。
じつは難しくて、最初のIF関数の偽の場合の部分が理解できたところです。
OFFSET関数の基準がSHEET2のA2とあるのですが、「茨城」ですよね。関東でなくてもいいのでしょうか？SHEET１のA1に関東など、地域名を入力するのですよね。

回答No.5

noname#204879

A　　 B　　　　C　 D　　　 E　　 F　　　 G
1　関東　茨城　　　　 関東　　東北　九州　　沖縄
2　　　　栃木　　　　 茨城　　青森　福岡　　沖縄
3　　　　群馬　　　　 栃木　　秋田　佐賀
4　　　　千葉　　　　 群馬　　岩手　長崎
5　　　　埼玉　　　　 千葉　　宮城　大分
6　　　　東京　　　　 埼玉　　山形　熊本
7　　　　神奈川　　　 東京　　福島　宮崎
8　　　　　　　　　　 神奈川　　　　鹿児島

１．セル A1 をアクティブにして、[データ]→[入力規則]→[設定]を実行
２．[入力値の種類]に“リスト”を選択
３．[元の値]ボックス内にマウスカーソルを置いて、範囲 D1:G1 をドラッグ指定
４．[OK]をクリック
５．範囲 B1:B46 をドラッグ指定して F2キーを一発叩く
６．マウスカーソルがセル B1 内で点滅していることを確認して、
　　式 =INDIRECT(A1)&"" を入力後、Ctrl+Shift+Enterキーをパシーッ
７．[書式]→[条件付き書式]にて次の設定を行う
　　数式が　　=ISNA(B1)
　　書式　　　フォント色を白

補足

回答どうもありがとうございました。
知識不足で、返信が遅くなってしまいました。ごめんなさい。
式 =INDIRECT(A1)&"" の意味なのですが、ヘルプ機能で調べたのですが、分かりませんでした。（＞＿＜）
栃木と表示されました。

回答No.4

ham_kamo

Sheet2に、以下のような一覧を作っておきます。
　　Ａ　　　　Ｂ
１　関東　　東北
２　茨城　　青森
３　栃木　　秋田
４　群馬　　岩手
５　千葉　　宮城
６　埼玉　　山形
７　東京　　福島
８　神奈川

元のシートのB1に、以下の数式を入力し、下にコピーします。入力するのはB1でなくてもいいですが、「関東」などの地域名が入るセルがA1以外のときは、最初のCOUNTIFの中の$A$1と、その後に２箇所出てくる「MATCH($A$1,…」の箇所をそのセル番地に書き換えてください。

=IF(COUNTIF(Sheet2!$1:$1,$A$1)=0,"",IF(ROW(A2)>COUNTA(OFFSET(Sheet2!$A$1,1,MATCH($A$1,Sheet2!$1:$1,0)-1,100)),"",OFFSET(Sheet2!$A$2,ROW(A1),MATCH($A$1,Sheet2!$1:$1,0)-1)))

回答No.3

nobu555

#2です。補足ですが。

あらかじめ作った表の行位置に対応して
表示されますので、
例えば、表の茨城がセルC2に入っていると
セルB2に表示されます。

ちょっと、使いづらいですね。

お礼

回答ありがとうございました！
名前を定義して、INDIRECT関数を使うのですね。
1つ勉強になりました。
使えそうです（＾＾）
知らない方法で理解するのに時間がかかり、お礼が遅くなってしまいました。ごめんなさい　ｍ(__)ｍ

回答No.2

nobu555

あらかじめ、各地方に対応する表を作り
メニューバー「挿入」から、「名前」「定義」で設定し
セルB1に下記の数式を入力する
=INDIRECT($A$1)
後は必要分コピーする。

エラー表示が嫌なら、
=IF(ISERR(INDIRECT($A$1)),"",INDIRECT($A$1))
で出来ます。

回答No.1

Cupper

あらかじめ対応する表を作成しておき、その表に対してVLOOKUP関数を使い抽出してはどうでしょうか。

VLOOKUP関数についてはヘルプに詳細が記載されているので参考にすると良いでしょう。

補足

返信ありがとうございました。
対応する表を下のようにして、D１に地域名を入力します。
　　　A 　　　　　B　　　　　C　　　　　　D　　　　　E
1　　関東　　　　茨城　　　　　　　　　　関東　
2　　関東　　　　栃木　　　　
3　　関東　　　　群馬　　　　
4　　東北　　　　青森
5　　東北　　　　秋田
6　　東北　　　　岩手

E1にVlookupを入力すると、
検索値をD1にし、範囲をA1～B6にし、列番号を２にすると、
茨城が表示され、栃木や群馬は表示されないと思うのですが・・。

他にやり方があるのでしょうか？