Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2021)
ふたつのデーターベースを比較して必要なレコードだけ残したい
投稿日時: 22/06/04 22:04:41
投稿者: ももいろのことり

こんばんは。いつもお世話になっております。
表題の件でよろしくお願いします。
 
名簿がふたつあります。
仮にA、Bとします。
 
両方とも列ラベルは同じで列数も同じです。
レコード数はAが2500件、Bには2000件ほどです。
微妙に違うものがあり、大変よく似ているので目視でチェックするのは大変です。
 
AとBを比較して、
「Bにないもの」と
「IDは同じだがその他の列に相違があるレコード(住所や姓が変更になっているもの)」を
Aから削除したいと思います。
 
類似した例をネットで検索するとVLOOKUPを使用する方法を見つけましたが、複数の列を比較する場合にどのように応用すればよいかわからなくなりました。
 
他の情報が必要な時は追記しますので、お力添えをお願いします。
 
なお、削除は手作業でも構いません

回答
投稿日時: 22/06/04 22:21:58
投稿者: WinArrow
投稿者のウェブサイトに移動

2段階の作業をお勧めします。
 
まず、IDで検索して不一致を削除します。
検索は、
作業列にCOUNTIF関数で「0」を纏めて削除(オートフィルタ)できます。
 
次にやはり作業列の検索したい項目を文字列結合して検索し、、
A,B両方とも、作業列を使います。

回答
投稿日時: 22/06/04 22:26:18
投稿者: WinArrow
投稿者のウェブサイトに移動

追加
住所には、番地など数字が混在していますが、半角、全角を同じ使いとするかしないかで
文字列結合の数式が変わります。
 

投稿日時: 22/06/04 23:20:00
投稿者: ももいろのことり

早速のお返事ありがとうございます!
 

WinArrow さんの引用:
追加
住所には、番地など数字が混在していますが、半角、全角を同じ使いとするかしないかで
文字列結合の数式が変わります。
 

 
!!!
確認したところ、確かに混在していました。
どちらかに統一したほうが美しいですね。
 
先に回答してくださったところを試してみます。
(トロいのでちょっと時間がかかります。見捨てないでお待ちください。)

回答
投稿日時: 22/06/05 09:13:04
投稿者: WinArrow
投稿者のウェブサイトに移動

>どちらかに統一したほうが美しいですね
美しい
ということではなく、
比較するのですから、統一しなければ、話になりません。
人名ですから、異体字にも気を使わないといけないと思いますよ。
 
ところで、1つの名簿の中には、同一人物が別のIDで登録されている可能性はないのでしょうか?
もし、そのような場合、IDがアンマッチだけで、削除してしまってよいものか?
 
健闘を祈る。

回答
投稿日時: 22/06/05 11:22:05
投稿者: WinArrow
投稿者のウェブサイトに移動

AとBで、不整合があるということは理解できいますが、
どちらかが新しいことも分かります。
 
例えば、Aが新しいとすると、
A:有、B:無・・は、
Aに新規登録されれば、当たり前ですから、「Aを削除」はまずいよね・・・・
逆に、Bが新しいとすると、
Aをメンテナンスすることの意図がわかりません。
 
というように
いまいち、何をしようとしているのか理解できません。
 

投稿日時: 22/06/05 12:17:54
投稿者: ももいろのことり

WinArrow さんの引用:
>
人名ですから、異体字にも気を使わないといけないと思いますよ。

 
ID検索で0と1での並べ替えが完了しました!
人名は、いい具合にカナ列がありましたのでそれを利用したいと思います。
 
カナ列、住所、郵便番号、等々を作業列にCONCATENATEを使って結合する…ということでよろしいでしょうか。
それともCONCATでしょうか。
 
なお、名簿A、B作成時以降の新規登録者は別ファイルがあるので大丈夫です。
 
引き続き、ご教示の程よろしくお願いいたします。

回答
投稿日時: 22/06/05 19:58:04
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
カナ列、住所、郵便番号、等々を作業列にCONCATENATEを使って結合する…ということでよろしいでしょうか。
それともCONCATでしょうか。

どちらでもよいが、引数の指定方法を見て、使ったらよいです。
この他に「&」を使用することも可能です。
 
余計な心配かもしれませんが、
異体字に対する心配は、
フリガナを使っても解決するのでしょうか?
 
例えば
「沢」と「澤」のように、読みが同一でも、漢字が異なるものをいいます。
これは、同一人物であるかは、人間でしか判別できないと思います。

投稿日時: 22/06/05 23:10:04
投稿者: ももいろのことり

直前の書き込みに誤字があったので削除しました。
 

WinArrow さんの引用:

余計な心配かもしれませんが、
異体字に対する心配は、
フリガナを使っても解決するのでしょうか?
 
例えば
「沢」と「澤」のように、読みが同一でも、漢字が異なるものをいいます。
これは、同一人物であるかは、人間でしか判別できないと思います。

 
異体字に関してですが、漢字の姓名をCONCATからはずせば大丈夫ですか。
  
同一人物が別IDで重複登録されているおそれは「ない」と聞いていますが、人間の作業なので、ないとは言えないかもしれません。
というのも、漢字の姓名住所など他のデーターは全く一緒なのにカナの欄に「ヤマザキ」と「ヤマサキ」という様に、別人として登録されているのを発見しました。(PHONETICを使わず手作業で入力されています。)
  
最終的にフリガナで並べ替えて目視でチェック…でしょうか…。
  
(小声)
お偉いさんの年賀状や招待状を出すための名簿なのですが、複数の団体でお偉いさんなので、各団体ごとに名簿があります。
  
それぞれの団体の職員が名簿を入力しているのですが、AとBの両方の団体に所属している人がいるので、Aには所属していない人、特定のエリア外に住所を移した人(姓の変わった人も含む)などを省くのが目的です。
  
この後に、同一住所で親子や夫婦などでそれぞれ登録のある場合、世帯主だけ残すという作業もあります。
こちらでは世帯主が誰なのかわからないので、年齢で判断できない場合は各団体の役員に聞いて確認します。

回答
投稿日時: 22/06/06 08:21:57
投稿者: WinArrow
投稿者のウェブサイトに移動

お疲れ様です。

引用:

異体字に関してですが、漢字の姓名をCONCATからはずせば大丈夫ですか。

引用:

最終的にフリガナで並べ替えて目視でチェック…でしょうか…。

引用:

最終的にフリガナで並べ替えて目視でチェック…でしょうか…。

そういう問題ではないと思います。
例ですが、
「山崎」を「ヤマザキ」「ヤマサキ」
「山嵜」を「ヤマザキ」「ヤマサキ」
 
「神戸」を「カンベ」「コウベ」「ゴウド」
これをカナで並べると、どの様になるかは、想像できますよね。
PHONETICを使う・使わない・・ということでもありません。
PHONETICは、手入力した平仮名を表示しているだけです。
 
人名ですから、全て正解なんです。
 
各団体それぞれに、非同期で取得した情報で登録/更新しているものと思います。
それらを「自動で削除(更新)する」なんて、とても考えられない。
アラームをだして、人手で対応したほうが無難と思います。
その時、情報の入手者を表示して、
「勝手に誰かに削除されてしまった」なんてことが無いようにしましょうね。
「各団体の役員に聞いて確認します。」でも構いませんが・・・

回答
投稿日時: 22/06/06 15:31:22
投稿者: んなっと

必ず次のような具体例を添えて質問してください。時間の無駄が省けます。
    
質問例
名簿Bの方が新しく作成されたので、住所などが変更になっている場合は名簿Bが正しいデータです。
    
  名簿A                    名簿B
1  ID 名前   カナ  住所 郵便 電話    ID 名前   カナ  住所 郵便 電話
2 004 青木  アオキ あ124 1235  568   002 木村  キムラ い123 6812  201
3 015 高橋 タカハシ あ125 1236  569   004 青木  アオキ あ124 1235  568
4 016 山本 ヤマモト あ126 2154  456   016 山本 ヤマモト い789 3287  456 ★
5 101 山埼 ヤマザキ あ123 1234  567   101 山崎 ヤマサキ あ123 1234  567 ★
    
【作業2】
まず名簿Bに存在しないIDの高橋さんを名簿Aから削除する。
次にIDは同じでも住所が異なる山本さんを、古い名簿Aから削除する。
最後にどちらかが入力ミスである山埼(山崎)さんも、信頼度の低い名簿Aから削除する。
    
最終的に名簿Aは青木さんだけにしたい。
    
  名簿A                
1  ID 名前   カナ  住所 郵便 電話
2 004 青木  アオキ あ124 1235  568
    
質問例ここまで。
    
これでいいんですよね。
山埼(山崎)さんがどちらが正しいかわからないことに気づいたからからといって、
方針がぶれてはいけません。
もしも名簿Bのほうが信頼度が高いとは限らないなら、
【作業2】の前段階として次の【作業1】をしておくだけです。
    
【作業1】
   A   B     C   D   E   F G  H   I     J   K   L   M  N
1  ID 名前   カナ  住所 郵便 電話   ID 名前   カナ  住所 郵便 電話  
2 004 青木  アオキ あ124 1235  568   002 木村  キムラ い123 6812  201  
3 015 高橋 タカハシ あ125 1236  569   004 青木  アオキ あ124 1235  568  
4 016 山本 ヤマモト あ126 2154  456   016 山本 ヤマモト い789 3287  456 ★
5 101 山埼 ヤマザキ あ123 1234  567   101 山崎 ヤマサキ あ123 1234  567 ★
    
N2
=IFERROR(IF(OR(INDEX(B:F,MATCH(H2,A:A,0),)<>I2:M2),"★",""),"")
下方向・↓
B名簿に★が付いているIDをA名簿と比較して、入力ミスなら名簿Bの方を必要に応じて修正し、名簿Aのデータは削除する。
  
    
これで名簿Bが「新しい」かつ「信頼ができる」名簿になります。それから【作業2】
   
   
   
   
   
最初から表形式の具体例があれば、
    
1  ID 名前   カナ  住所 郵便 電話
2 004 青木  アオキ あ124 1235  568
    
意図が伝わりやすいですよね。
さらに、表形式の具体例を作成しているうちに質問者自身も状況の整理ができるようになり、
問題の本質に気が付きやすくなります。よく「データは出せない」という方もいますが、
それは嘘です。名前や住所を架空のものに作り替えるのが面倒なだけ。

回答
投稿日時: 22/06/07 18:21:59
投稿者: WinArrow
投稿者のウェブサイトに移動

Bあり、Aなしのパターンの説明はなかったのですが、
んなっと さんのレスを拝見して、
そうか?Bが最新ということならばそういう考え方もあるな・・・と思いました。
 
でも、よく考えると
それならば、
Aをメンテナンスせずに、BをAに複写すれば済む話ではないかと思います。
Bが複数あるならば、B1〜Bnを統合することになるでしょう。
但し、IDがB1〜Bnで重複するならば、話は別です。
 
質問者さんから具体的な説明がないまま、外野でいろいろ想像してもね〜〜〜

回答
投稿日時: 22/06/07 18:31:43
投稿者: んなっと

↓これは重要だと思いますよ。
IDは同じでも住所が異なる山本さんを、古い名簿Aから削除する
 
余計な情報が追加される前、一番最初の書き込みにほぼ同じ内容が書かれていますから。
最初の質問文を素直に読めば、名簿Aのメンテナンスは必要ですね。

回答
投稿日時: 22/06/07 21:17:06
投稿者: んなっと

一応最初の質問に対しての回答です。
 
   A   B     C   D   E   F  G  H   I     J   K   L   M
1  ID 名前   カナ  住所 郵便 電話    ID 名前   カナ  住所 郵便 電話
2 004 青木  アオキ あ124 1235  568   002 木村  キムラ い123 6812  201
3 015 高橋 タカハシ あ125 1236  569 ☆ 004 青木  アオキ あ124 1235  568
4 016 山本 ヤマモト あ126 2154  456 ★ 016 山本 ヤマモト い789 3287  456
5 101 山崎 ヤマサキ あ123 1234  567 ★ 101 山崎 ヤマサキ あ123 1234  888
 
 
G2
=IFERROR(IF(OR(INDEX(I:M,MATCH(A2,H:H,0),)<>B2:F2),"★",""),"☆")
下方向・↓
→G列で並べ替え・削除
 または G列でフィルター(空白セル)

投稿日時: 22/06/07 21:27:14
投稿者: ももいろのことり

WinArrowさま んなっとさま
 
お返事ありがとうございます。
手が離せなくて、こちらのチェックが遅くなり申し訳ありません。
 

引用:
最初から表形式の具体例があれば、
     
1  ID 名前   カナ  住所 郵便 電話
2 004 青木  アオキ あ124 1235  568
     
意図が伝わりやすいですよね。

そもそもの質問の仕方が悪くていろいろお手を煩わせてしまい、重ねて申し訳ありません。
 
ファイルの作成日はAの方が古いのですが、各団体による最終更新日はA、Bほぼ同じ日です。(こちらでファイルをコピーした日ではありません)
AをもとにBを作ったのではなく、名簿のテンプレートが同じなだけでまったく別の物のようです。
なので、どちらが新しくてどちらが古いかというのは気にしなくてもよいみたいです。
 
上の者から口頭で
 
AとBを比較して、
「Bにないもの」と
「IDは同じだがその他の列に相違があるレコード(住所や姓が変更になっているもの)」を
Aから削除するように。
 
とだけ仰せつかっているので、作業の理由はわかりません。
 
別の新しい団体を起こし、今回の整理で出来た新しい名簿でメンバーとして仮登録をするのかなと想像しています。(勝手に想像しているだけです。)
 
今回の作業で完成した名簿はAを上書きするのではなく、全く別のものとして保存するので、A、Bそれぞれの団体において修正することはないそうです。
 
ご指摘いただいた名前(カナ・漢字)の問題ですが、ここに質問を書き込んだ時には気づいていませんでした。
いろいろ考えが足りなくて情けないです。
 
住所も、都道府県名があったりなかったり、1丁目1番23号だったり、1-1-23だったりして、途方に暮れていましたが、本日「住所は無視して郵便番号が同じであればよい」ということになりました。
 
引用:
IDは同じでも住所が異なる山本さんを、古い名簿Aから削除する

削除してもいいようです。(エリア外になるらしいので)
 
ご提示いただいた数式を試してみたいと思います。
報告にやってきますので、よろしくお願いします。

投稿日時: 22/06/07 21:36:41
投稿者: ももいろのことり

IDが同じで住所が違う理由はエリア外への転居もありますが、Aには住所が団体や所属先になっているものが多く含まれ、それらは新しい名簿には必要がないそうです。

回答
投稿日時: 22/06/07 21:41:19
投稿者: んなっと

Excelが2021より古いバージョンの場合は、数式を入力してから
Ctrl+Shift+Enterキー同時押し。

回答
投稿日時: 22/06/08 08:15:08
投稿者: んなっと

引用:
本日「住所は無視して郵便番号が同じであればよい」ということになりました

↑この部分は完全に無視します。
 
↓G列がうまくいったら、
 
   A   B     C   D   E   F  G
1  ID 名前   カナ  住所 郵便 電話  
2 004 青木  アオキ あ124 1235  568  
3 015 高橋 タカハシ あ125 1236  569 ☆
4 016 山本 ヤマモト あ126 2154  456 ★
5 101 山崎 ヤマサキ あ123 1234  567 ★
 
どこかのセルに
=FILTER(A1:F500,(A1:A500<>"")*(G1:G500=""),"")
でもいいかもしれません。

投稿日時: 22/06/22 21:58:28
投稿者: ももいろのことり

こんばんは。
 
平常の業務に追われていて、こちらへお邪魔するのが遅くなって申し訳ありません。
 
都合により封書が出せない週間になってしまい、この作業が先送りになってしまいました。
今回お助けいただいた件は、必ず再開しますが、日があきますのでいったん解決済みとして示させていただきます。
 
作業再開時にわからないことが出てきましたら改めてご協力をお願いしますので、よろしくお願いします。
 
いろいろご親切にしていただき、ありがとうございました。