エクセルの作成（関数）について

質問

D5に担当組、F5、F6、F7、F8に氏名を入力する表を作成します。

D5は、組名の１、２、３のリストが設定されています。

各組は４０名ほどおり、1班～4班の１０名ずつの4班に分けております。

例えば、D5にリストで１を選んだ場合、F5にリストで1組1班の１０名、F6にリストで1組2班の１０名、F7にリストで1組３班の１０名、F8にリストで1組４班の１０名の名前だけ表示させたいのです。

また、２組を入力した場合も、同じく2組の各班の氏名をリストに出したいと思います。

わかりづらいと思いますが、よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
一案です。

↓の画像のように別Sheet（今回はSheet2）に表を作成しておきます。
（各組4列10行に収めています）
Sheet2のA1セルは
=IF(Sheet1!D5="","",Sheet1!D5)
という数式を入れておき、

Sheet2のB2セルに
=IF($A$1="","",INDEX(G:G,($A$1-1)*10+ROW()))
という数式を入れ、右方向に4列分・行方向に10行分オートフィルでコピーしておきます。
これでSheet1のD5セルに入力された組がこの表に表示されます。

Excel2010以降であれば入力規則のリストで別Sheetが直接指定できますので、
Sheet1のF5セル　→　入力規則　→　リスト　→　元の値の欄に
=Sheet2!$B$2:$B$11
としてOK！

同様にF6セルも
=Sheet2!$C$2:$C$11
といった具合にF8セルまで設定して完了です。

※　Excel2007以前のバージョンでは別Sheetを直接範囲指定できなかったと思いますので
Sheet2の各列を名前定義します。

Sheet2のB2～B11セルを範囲指定　→　名前ボックス（画面左上のB2とセル番地が表示されているところ）の
B2を消して　仮に　1班　と入力しEnter
これでSheet2のB2～B11セルが「1班」と名前定義あれました。
同様に各列を　2班・3班・4班　と名前定義しておきます。
そしてSheet1のF5セル　→　リスト　→　元の値の欄に
=_1班
としてOK
同様に各列を　_2班　_3班　_4班
と名前定義して完了です。

※　注意点　※　
名前定義の名前の中に英数字がある場合はエラーとなることがありますので、
敢えて、1班　の前に　アンダーバーを入れています。

長々と失礼しました。m(_ _)m

お礼

ありがとうございます。
完璧です！助かりました。

もう一つお聞きしたいのですが、氏名などのデータは、Sheet３に作るとして、Sheet２もSheet１と同じようにするには、どのようにしたら良いのでしょうか。
よろしくお願いいたします。

回答No.3

tom04

No.1です。

＞氏名などのデータは、Sheet３に作るとして、Sheet２もSheet１と同じようにするには、
＞どのようにしたら良いのでしょうか。

に関してですが、Sheet2を画像のSheet1のようにして組・班のデータをSheet3にしたい！
という解釈でよろしいでしょうか？
もしそうであれば前回の数式のSheet1・Sheet2の部分だけの変更で大丈夫だろうと思います。

ただ、No.2のkeithinさんが回答されていらっしゃるように
全ての組・班を「名前定義」しておけば、
Sheet名に関係なく同じブック内であればどのSheetでも対応できます。

keithinさんの方法で名前定義を設定すれば、F5～F8それぞれのセルでリスト設定する必要はなく、
一括でリスト設定が可能です。

F5～F8セルを範囲指定　→　データの入力規則　→　リスト　→　元の値の欄に
=INDIRECT("_"&D$5&"_"&ROW(A1))

これでF5～F8セルが一度でリスト設定されると思います。m(_ _)m

回答No.2

keithin

１組１班の１０名を記入したセル範囲を選択
添付図のように、名前ボックスの中に
_1_1
と必ずこの通りに記入してEnterし、名前を付けます
_だとか、数字の半角と全角だとか、間違えないようによく気を付けて作業します

１組２班のセル範囲には同様にして　_1_2　と名前を付け
１組３班のセル範囲には同様にして　_1_3　と名前を付け
２組１班のセル範囲には同様にして　_2_1　のように、それぞれ名前を付けておきます
イチイチ全部書きませんが、他の組他の班のセル範囲にも全部名前を付けます
添付図は見やすいように並べただけなので、どこにどう名前が置いてあっても別に構いません。

簡単のためＤ５に仮に１を記入しておき、
Ｆ５セルの入力規則は
　種類　リスト
　元の値に　=INDIRECT("_"&D5&"_1")
Ｆ６の入力規則は
　種類　リスト
　元の値に　=INDIRECT("_"&D5&"_2")
と設定
他のセルも同様に設定します。

お礼

ありがとうございました。
表を作ることができました。
感謝します。