[[20050826024901]] 『指定した氏名と電話番号が一致する人の個人データ』(ミワ) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『指定した氏名と電話番号が一致する人の個人データを抽出するには?』(ミワ)

同じシートに各都道府県まとめて住所録を作成しています。(項目は氏名・住所・電話番号・年齢です)
関数を利用して、指定した氏名と電話番号が一致した人の個人データを抽出する表を別のシートに作りたいのですが、どのようにしたらいいのでしょうか。
また、3件分のデータを残せるようにしたいのですが・・・


 SUMPRODUCT((氏名データ範囲=氏名)*((電話番号データ範囲=電話番号)*ROW(データの行範囲))
 で該当行が返りますのであとはINDEX関数で参照してください。
  うまくいかなければサンプルデータの提示を!

 >また、3件分のデータを残せるようにしたいのですが・・・ 
 意味がわかりません。
 指定した氏名と電話番号が変わっても前回,前々回抽出した結果は残しておくという意味ですか?

                    (kym)  (^^)_v


ご回答、ありがとうございます。
データを残すという件は、おっしゃる通りの意味です。

サンプルデータは

    A     B     C    D    E

1  登録日  氏名   性別  住所  電話番号

2   1/1   ○○    男   ××   △△

3   2/1   ○○    女   ××   △△

4   3/3   ○○    女   ××   △△

5   5/5   ○○    男   ××   △△

という感じです。
宜しくお願いします。


    A       B      C      D
 1 氏名   ○○  電話番号 △△
 2 今回
 3 前回
 4 前々回
 このように一般機能で抽出するのは私のスキルでは無理です。
 拙いコードでよければ考えてみます。

                            (kym)  

 VBAには疎く自動記録で作ってみました。
     A     B        C        D        E        F
 1		佐藤太郎			123-4567
 2	登録日	氏名	性別	住所	電話番号
 3 今回					
 4 前回					
 5 前々回					
 C1・F1に検索条件を入れて以下のマクロを実行します。

 Sub Macro1()

    Range("B4:F4").Select
    Selection.Copy
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B3:F3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.FormulaArray = _
        "=INDEX(Sheet1!C1:C5,MAX((Sheet1!R1C2:R99C2=R1C3)*(Sheet1!R1C5:R99C5=R1C6)*ROW(R1C[-1]:R99C[-1])),COLUMN(R[-2]C[-1]))"
    'Selection.AutoFill Destination:=Range("B3:F3"), Type:=xlFillDefault
    Selection.Copy
    Range("C3:F3").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B3:F3").Select
    Selection.Copy
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
 End Sub

 無駄が多く突っ込みどころ満載のコードです(^^ゞ
 該当行が存在しないときはタイトル行が入ります。
                                   (kym)

 関数で処理する場合の一例です。 
 Sheet1
     A	  B	 C	 D	 E	 F	 G
 1 登録日	 氏名	性別	住所	電話番号	年齢	検索用
 2 1月1日  aaa	男	zzz	△△	100	aaa_△△
 3 2月1日 bbb	女	yyy	××	101	bbb_××
 4 3月3日 ccc	女	xxx	○○	102	ccc_○○
 5 5月5日 aaa	男	zzz	△△	100	aaa_△△
 6 6月6日 aaa	男	zzz	△△	100	aaa_△△
  G列に検索用の作業列を用意します。G2セルに =B2&"_"&E2  として下方コピー。

 Sheet2
     A	    B	 C	  D	   E
 1 氏名	   aaa	前回	前々回	前々々回
 2 電話番号 △△	6月6日	5月5日	1月1日
 3 住所	   zzz			
 4 年齢	   100			
B2,B3は、指定するものを手入力。
B3セルに、 =INDEX(Sheet1!$D$1:$D$6,MATCH($B$1&"_"&$B$2,Sheet1!$G$1:$G$6,FALSE))
B4セルに、 =INDEX(Sheet1!$F$1:$F$6,MATCH($B$1&"_"&$B$2,Sheet1!$G$1:$G$6,FALSE))
C2セルに、
=LARGE(IF($B$1&"_"&$B$2=Sheet1!$G$1:$G$6,Sheet1!$A$1:$A$6+ROW(Sheet1!$A$1:$A$6)/10000),COLUMN(A1))
ここは、CtrlとShiftとEnterキーを一緒に打って【配列数式】として確定し、D2,E2にコピー。
日付が同日の場合は、より下の行を先に表示します。見た目は同じですけど...
(sin) 意図に合ってますでしょうか? 

 読み返してみました。上記の私の書き込みは、意図されてるものと違っています。
よって、無視して下さい。
対処するには、マクロかオートフィルターとなるでしょうが、既にここと別レスで
kymさんがご回答下さってますね。
(sin) 登録した日を出したいのだと思い込んでました。

 自己満足のために、マクロもオートフィルターも使わない方法をちょっと考えてみました。
二、三約束事が存在しますが...
 Sheet名:入力Sheet  ←データベースとなる入力用シート			
      A	           B	C     D	        E       F	       G
 1 登録日時         氏名   性別 住所	     電話番号  年齢	     検索用
 2 2005/1/1_15:55	 aaa     男   aaa_住所    111   aaa_年齢  aaa_111
 3 2005/2/1_15:56	 bbb     女   bbb_住所    222   bbb_年齢  bbb_222
 4 2005/3/1_15:57	 ccc     女   ccc_住所    333   ccc_年齢  ccc_333
 5 2005/3/1_15:58	 ddd     男   ddd_住所    444   ddd_年齢  ddd_444
 6 2005/5/1_15:59	 eee     男   eee_住所    555   eee_年齢  eee_555
 登録日は時間まで入力:【Ctrlと;】【_】【Ctrlと:】の順に打つ。
 →後に出てくる検索を、登録日のみで処理すると同じ日に登録した場合に不都合が出るため。
  同じ時間があると同じ事ですが。(私の入力スピードだと1分以上は掛かるので大丈夫ですが…)
G2セルは、=B2&"_"&E2 で下方コピー。

 Sheet名:検索Sheet  ←氏名・電話番号における検索および履歴のシート
      A	    B	         C        D    E	     F	  G       H	
 1   氏名:  bbb	       電話番号: 222
 2  検索順  登録日時        氏名    性別  住所	  電話番号 年齢	 *
 3    1	 2005/1/1_15:55	aaa     男  aaa_住所   111	 aaa_年齢	 *
 4    2	 2005/3/1_15:57	ccc     女  ccc_住所   333	 ccc_年齢	 *
 5    3	 2005/5/1_15:59	eee     男  eee_住所   555	 eee_年齢	 *
 6    4	 2005/2/1_15:56	bbb     女  bbb_住所   222	 bbb_年齢	
B1,D1セルに検索する氏名、電話番号を手入力。 またH2の『*』も最初に手入力。
 A3セル =IF($H2="*",SUM(A2,1),"")
 B3セル ◇該当する氏名・電話番号が無い場合には表示します。
=IF(AND($A3<>"",$B$1<>"",$D$1<>""),IF(ISNUMBER(MATCH($B$1,入力Sheet!$B$2:$B$1000,FALSE)),IF(ISNUMBER(MATCH($B$1&"_"&$D$1,入力Sheet!$G$2:$G$1000,FALSE)),INDEX(入力Sheet!$A$2:$A$1000,MATCH($B$1&"_"&$D$1,入力Sheet!$G$2:$G$1000,FALSE)),"電話番号が違います!"),"該当する氏名がありません!"),"")
 C3セル 
=IF($B3="","",VLOOKUP($B3,入力Sheet!$A$2:$G$1000,COLUMN()-1,FALSE))
これをD3からG3までコピペ。 B3で該当なしの場合は#N/Aが返ります。
で、A3からG3をコピーし、必要なとこまで貼付け。
◆ここで、検索結果(入力した結果)がOKであれば、コピーして値のみを貼付け、数式を消し、
 H列に*を入力(上にあるものをコピペでもいいし)
 →ここは面倒でも、手作業になってしまいます。

 Sheet名:過去3回表示Sheet
     A	   B      	C    D     E	   F	  G
 1 検索順	 登録日時	        氏名 性別	 住所	電話番号	 年齢
 2   4	 2005/2/1_15:56   bbb  女	bbb_住所	  222	bbb_年齢
 3   3	 2005/5/1_15:59   eee  男	eee_住所	  555	eee_年齢
 4   2	 2005/3/1_15:57   ccc  女	ccc_住所	  333	ccc_年齢
 A2セルに =IF(COUNT(検索Sheet!A:A)>=ROW()-1,LARGE(検索Sheet!A:A,ROW()-1),"")
 B2セルに =IF($A2<>"",VLOOKUP($A2,検索Sheet!$A$3:$G$81,COLUMN(),FALSE),"")
これをC2からG2までコピペ。A2からG2をコピーし、A3からG4にこぴぺ。
過去5回までとする場合は、6行目まで範囲拡大です。
(sin) 以上、私が運用するなら って感じで考えたものです。


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.