[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定した氏名と電話番号が一致する人の個人データを抽出するには？』（ミワ）
同じシートに各都道府県まとめて住所録を作成しています。（項目は氏名・住所・電話番号・年齢です）
関数を利用して、指定した氏名と電話番号が一致した人の個人データを抽出する表を別のシートに作りたいのですが、どのようにしたらいいのでしょうか。
また、３件分のデータを残せるようにしたいのですが・・・
SUMPRODUCT((氏名データ範囲=氏名)*((電話番号データ範囲=電話番号)*ROW(データの行範囲)) で該当行が返りますのであとはINDEX関数で参照してください。 うまくいかなければサンプルデータの提示を！
>また、３件分のデータを残せるようにしたいのですが・・・ 意味がわかりません。 指定した氏名と電話番号が変わっても前回,前々回抽出した結果は残しておくという意味ですか？
(kym) (^^)_v
サンプルデータは
Ａ Ｂ Ｃ Ｄ Ｅ
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と:】の順に打つ。
→後に出てくる検索を、登録日のみで処理すると同じ日に登録した場合に不都合が出るため。
同じ時間があると同じ事ですが。（私の入力スピードだと１分以上は掛かるので大丈夫ですが…）
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.