関数で数行を抜き出したい

質問

いつも大変お世話になっております。データベースから抜き出す関数ですがちょっと問題があります。Ａ列に日付Ｂ列に名前Ｃ列に納入品目Ｄ列に値段が入っているデータベースがあります。そのデータベースから抜き出すのですが、一人で数品目買っている方は名前が一番上だけしか入っていません。それを抜き出したいのですが、名前を入れると、その名前が入っている行を含めて下へ５行（日付・品目・金額すべてを）抜き出すことの出来る関数は作れますでしょうか。最高６品目以上購入している方はいないので５行にしました。
よろしくお願い致します。

回答No.3

noname#262398 · ベストアンサー

名前は、同姓同名がいる可能性を考えて、キーにしない方が良いのですが、
それはさておき、
F1に名前を入力、H1～H5に日付、I1～I5品目、J1～J5に値段を出すとします。
G1（作業セル）=MATCH(F1,B:B,0)
H1=INDEX(A:A,$G$1)
I1=INDEX(C:C,$G$1)
J1=INDEX(D:D,$G$1)
H2=IF(AND(INDEX(B:B,$G$1+ROW(A1))="",H1<>""),INDEX(A:A,$G$1+ROW(A1)),"")
I2=IF(AND(INDEX(B:B,$G$1+ROW(A1))="",I1<>""),INDEX(C:C,$G$1+ROW(A1)),"")
J2=IF(AND(INDEX(B:B,$G$1+ROW(A1))="",J1<>""),INDEX(D:D,$G$1+ROW(A1)),"")
H2～J2の式をH5～J5までコピー。

というような感じでしょうか？

お礼

ありがとうございました。やりたかったことが出来ました。ただ、回答にもありましたが、同姓同名（同じ方が）２つ以上あった場合はどの様になるのでしょうか。それに対応させる方法などあるのでしょうか。（２つあった場合、両方表示させる方法）
よろしくお願いします。

回答No.4

noname#262398

#3のNNAQです。

> ＋ハンドルで複写したりする手間を惜しむため
その手間を惜しむと、集計する時にかえって手間がかかることになります。

やはり、B列の名前は全て埋めておきましょう。
（埋めなくてもやってやれないことは無いが、単なるメモ代わりではなくデータベ－スとして使うなら埋めておかなければならない、という意味です。）

空いている列（仮にE列）に、
E2=IF(ISBLANK(B2),E1,B2)
として下までドラッグすれば名前が全て入るはずなので、
そうしたらE列をコピー、B列に[形式を選択して貼り付け]-[値]。

B列が空白無く埋まれば、#1,#2のご回答のような方法とか、
#3のように、F1に名前を入力、G列を作業列とすると
G1=MATCH(F1,B:B,0)
G2=MATCH(F$1,OFFSET(B$1,G1,0):B$65536,0)+G1
適当に下へコピー。
これで、G列には、"F1に入力した名前がB列の何行目にあるか"が分かるので、
あとはINDEX関数で。

あるいは、関数を使わずフィルタオプションを使う手もあります。
そのほうが良いかもしれません。

回答No.2

imogasi

氏名も上行と同じ場合も値を入れるようにします。または＋ハンドルで複写します。そのほうがなにかと良い。
条件付書式でA2:A100を指定して、数式がー=A2=A1　　でフォントs色を白色にします。
これで重複氏名は見えなくなります。
ーーー
氏名を指定し抜き出しする方法（imogasi方式）
第１行に行挿入して、
例データA2:B１１　　C2に選択する氏名を入れるとする
a 1 a
a 2
a 3
a 4
c
c
c
c
d
d
B2は　=IF(A2=$C$2,MAX($B$1:B1)+1,"")
と入れて下方向に式を複写する。
これをSheet2に行って
=INDEX(Sheet1!$A$2:$A$11,MATCH(ROW()-1,Sheet1!$B$2:$B$11,0),1)
の式を入れて下方向に式を複写する。
氏名以外の列あり
＃N/Aを出さない方法
については、自称「imogasi方式」でOKWAVEを照会していただければ
沢山（毎日のように）同じような質問と回答が出ますので、省略。

お礼

ご回答ありがとうございます。実は、＋ハンドルで複写したりする手間を惜しむために、今回のご質問をさせて頂きました。お手数かけました。

補足

ご回答ありがとうございます。実は、＋ハンドルで複写したりする手間を惜しむために、今回のご質問をさせて頂きました。お手数かけました。

回答No.1

zap35

関数は戻り値が一つしかないため、一つの関数で複数のセルに値をセットすることはできません

従って関数では単純にはできませんね。

ただしこんな方法はどうでしょう。Ｅ列に（ここではE4セルとします）
=COUNTIF($B$1:$B4,B4)
と入れると、顧客のその行までの出現回数が得られますから、Ｆ列に顧客名と出現回数をバインドした値をセットします（ =B4 & E4 ）

検索では顧客名がセルH1に入るとして、５つのセルに
=MATCH(H1 & "1" ,$F$1:$F$65536,0)
=MATCH(H1 & "2" ,$F$1:$F$65536,0)
=MATCH(H1 & "3" ,$F$1:$F$65536,0)
=MATCH(H1 & "4" ,$F$1:$F$65536,0)
=MATCH(H1 & "5" ,$F$1:$F$65536,0)
とすれば５件分の情報が得られます。（実際の情報はINDEX関数と組み合わせて取得する）

ここではMATCHを使いましたがＦ列の値がＡ列にあればVLOOKUP関数を使用する方が簡単でしょう