エクセルの関数について

質問

エクセルで部署コートが同じ人をまとめて一枚の用紙に出したいとおもっています。

シート1
部署コード　　　　名前欄
　1　　　　　田中　原田　中野　宮本
　2　　　　　大木　松下　伊藤　
　3　　　　　石川　神崎　乾　　斉藤　　鈴木
といった風にコードの横に人の名前が入っています。
（部署は7.8箇所あります。人数のばらつきもかなりあります）
シート2
部署コードを入れると
該当する人の名前が表示されるセル*10行

といった風に表示させるには
名前が表示されるセルの関数をどのようにすればいいのでしょうか？

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。
↓の画像で説明させていただきます。

左側がSheet1で右側がSheet2になります。

Sheet2のA2セルに
=IF($A$1="","",IF(COUNTA(OFFSET(Sheet1!$B$1:$K$1,MATCH($A$1,Sheet1!$A$2:$A$10,0),,1))<ROW(A1),"",INDEX(OFFSET(Sheet1!$B$1:$K$1,MATCH($A$1,Sheet1!$A$2:$A$10,0),,1),,ROW(A1))))

という数式を入れ、オートフィルで下へコピーしています。
これでSheet2のA1セルに部署コードを入力すれば
その部署の氏名が表示されると思います。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ありがとうございます＞＜
やりたかったのはまさにこれなんですが
数式を読んでも今一理解ができておりません＾＾；

Sheet1!$B$1:$K$1　 は　名前見出し部分で横範囲を指定（固定）
Sheet1!$A$2:$A$10　は　部署コード範囲を指定（固定）
$A$1　　　　　　　 は　部署コード入力欄（固定）
だと思うのですが
ROW(A1)の意味が理解できていないのですが
これは何をやっているのでしょうか？

回答No.2

tom04

こんにちは！
No．1です！
たびたびお邪魔します。

>ROW(A1)の意味が理解できていないのですが
これは何をやっているのでしょうか？

とあるので再び顔を出しました。

数式内の　ROW(A1)=1　になります。
ためしにどこのセルでも良いですのでアクティブにして
そのセルの数式を　=ROW()　と入力して確定してみてください。
仮にB4セルにその数式を入れると　「4」が表示されると思います。
結局オートフィルで下へコピーするにしたがって、そのセルの行番号を参照するようにしています。
すなわち無理にA列でなくても他の列でも　ROW（C1)＝1 となります。

そして数式を解説すると
IF(COUNTA(OFFSET(Sheet1!$B$1:$K$1,MATCH($A$1,Sheet1!$A$2:$A$10,0),,1))<ROW(A1),"",・・・
の部分は
$A$1セルに入力したものがSheet1の$A$1～$A$10のどの行にあるか？
すなわちSheet1の$A$1～$K$1を基準に何行下にあるか範囲を指定し、
その行に入力されているセル数よりオートフィルした行番号の方が大きければそれ以降の行は空白にしなさい！という意味になります。
これはエラー処理のためです。

オートフィルでコピーした下の行のセルをクリックして数式バーを確認してみてください。
(A1)　の部分がその行のセル番地に変わっていると思います。

後半の数式
INDEX(OFFSET(Sheet1!$B$1:$K$1,MATCH($A$1,Sheet1!$A$2:$A$10,0),,1),,ROW(A1))))
の部分の説明は
OFFSET・・・に関しては前述の意味と一緒ですのでここでは割愛します。
その範囲の行の配列の中で　ROW(A1)　は　1列目を表示しなさい！　
ROW（A2）は　2列目を表示しなさい！・・・とオートフィルで下へコピーするたびに
列番号が増えていきますので、結局指定した行の左の列から順番に表示させる数式になっています。

以上、長々と書きましたが
この程度でよろしかったでしょうか？

どうも何度も失礼しました。m(__)m

お礼

ありがとうございます＞＜
ROW(A1)の意味が理解できていなかったので
コードを入力するセルと同じにしていたため
ROW(A5)となっていて5番目の人が一番目に表示されていて
どうしてそうなるのかと困っていました。
1に直してやってみたところうまくいきました＾＾

縦方向に情報を入力して横の列の何番目かの情報を指定して
取り出す方法は載っていたのですが
こちらの方法がみつからず悩んでいました。
これで作業が楽になります。本当にありがとうございました＾＾