パソコン困りごと掲示板 
◇-?二つのシートから重複していない行を見つけたい。-take(2005/07/15-14:40)No.275297
　┣?~Re:二つのシートから重複してい�...-白茶(2005/07/15-15:24)No.275307
　┃┗?~!Re:二つのシートから重複してい...-take(2005/07/15-15:36)No.275312
　┃　┗?~!!Re:二つのシートから重複して�...-白茶(2005/07/15-16:13)No.275324
　┃　　┗?~!!!Re:二つのシートから重複して...-白茶(2005/07/15-18:16)No.275347
　┃　　　┣?~!!!-Re:二つのシートから重複し�...-take(2005/07/15-20:59)No.275374
　┃　　　┃┗?~!!!-!Re:二つのシートから重複し...-白茶(2005/07/15-21:28)No.275376
　┃　　　┃　┗?~!!!-!!追加説明...-白茶(2005/07/16-11:50)No.275448
　┃　　　┃　　┗?~!!!-!!-Re:追加説明...-take(2005/07/16-14:44)No.275478
　┃　　　┗?~!!!!Re:二つのシートから重複し�...-半端者(2005/07/16-10:03)No.275433
　┗?!Re:二つのシートから重複してい�...-半端者(2005/07/15-15:57)No.275319
　　┗?!!Re:二つのシートから重複してい...-半端者(2005/07/15-18:03)No.275346
　　　┗?!!【ありがとう】Re:二つのシート...-take(2005/07/15-20:39)No.275372
　　　　┗?!!A【解決】遅くなりました。...-take(2005/07/17-17:39)No.275749
▲このページのトップに戻る
275297二つのシートから重複していない行を見つけたい。take 2005/07/15-14:40

メーカー名：富士通
ＯＳ名：Windows2000
パソコン名：C600
ソフト名：EXCEL2003
ソフト関連：ソフトの使い方がわからない
--
二日前に質問させていただいたのですが、解決できなかったので
再度質問させていただきます！
以前お答えくださった方には申し訳ありませんが、
質問方法を詳しく説明しなおしましたのでよろしくお願いします。

一つのエクセルファイルに二つのシートがあります。
「営業把握分」というシートと「登録分」というシートです。
二つのシートにある住所データに差異があるので、

1.「営業把握分」にはあるが「登録分」にない住所
2.「登録分」にはあるが「営業把握分」にない住所

を新シートか、どちらかのシートにでもいいのですが一覧にして表示させたいのです。

二つのシートには1行目にオートフィルタをかけています。
・「営業把握分」のシートはＦ列の2行～219行まであります。
・「登録分」　　のシートは I列の2行～3905行まであります。
（I列に行が多いのは同じ住所が複数あるため）

どのエクセルの機能を使って、どのように検索（抽出）すればいいんでしょうか？
よろしくお願いします。

▲このページのトップに戻る
275307Re:二つのシートから重複していない行を見つけたい。白茶 2005/07/15-15:24

記事番号275297へのコメント

あれ？

何でまた新規にスレ立てをなさったのでしょう？
http://pasokoma.jp/?num=274760&ope=sel&id=
こっちも、まだ生きてますよ。

で、私の紹介した案はどんなでしたか？


▲このページのトップに戻る
275312Re:二つのシートから重複していない行を見つけたい。take 2005/07/15-15:36

記事番号275307へのコメント
白茶さん、すみません。教えてくださったやり方をしてみたのですが、
うまくできませんでした。。
前のスレは生きていたのですが、説明が新たに必要なことと下に行きすぎてしまったので
前の説明とややこしくなってしまいそうなので新たにスレを立てさせていただきました。
せっかく教えてくださって申し訳ないですが、よろしくお願いします。

▲このページのトップに戻る
275324Re:二つのシートから重複していない行を見つけたい。白茶 2005/07/15-16:13

記事番号275312へのコメント
スレッドが生きている間は、先頭へ上げる事が出来ます。
投稿時に

□上げレス(２ページめ以降に消えてしまった場合に１ページめに上げる)

というチェックボックスを見かけませんでした？
今後は上げるか、前のスレッドを「解決」にして閉じてから
新規に質問を立てて下さいね。
でないと、takeさんの気づかぬ間に、前のスレに回答が付いちゃって、
読まれる事なく過去ログへ流れちゃう。
なんて事態も出てきますんで。


>うまくできませんでした。。

の詳細を伺いたかったのですが。

計算式自体に問題があったのであれば、
そこを突っ込んで頂きたいな、と。
私の間違いですから。

あるいは、計算式自体に問題はなかったけど、
想定外の別条件が絡んできて、
代案を所望する場合でも、
その旨を書いて頂かないと、先へ進みません。

とりあえず、今回、
>（I列に行が多いのは同じ住所が複数あるため）
という事は新たに了解しました。

それでも、例の式で問題無いと思うんだけどなぁ・・。

あ！
それとも、私が単に質問の趣旨を読み違えたとか？


何はともあれ、お返事お待ちしておりまする。


▲このページのトップに戻る
275347Re:二つのシートから重複していない行を見つけたい。白茶 2005/07/15-18:16

記事番号275324へのコメント
とりあえず、実シートに沿った形で、
いま一度ご説明をしておきましょう。

解り易くするため、
>1.「営業把握分」にはあるが「登録分」にない住所
の場合だけについて。

営業把握分のZ2(別にZ列でなくとも良し)に
=IF(COUNTIF(登録分!I:I,F2)=0,ROW())
と入力。
Z219までフィルハンドルをつかんで下へ引っ張る。

Z列に「FALSE」が返ってきた住所は
「登録分」のシートと重複している住所です。
数字が返ってきている住所が「登録分」には存在しない住所です。

で、この「登録分」には存在しない住所だけを
抽出します。

場所は新たなシートでも、既存のシートでも、
セル番地すら何処でも良いです。
とにかく、
=INDEX(営業把握分!F:F,SMALL(営業把握分!Z:Z,ROW(A1)))
として、エラー値「#NUM!」が出るまで、
フィルハンドルをつかんで下へ引っ張る。

と、「営業把握分」にはあるが「登録分」にない住所
だけが、ズラーッと出てくるハズなのですが。

実シートでご確認下さいませ。


▲このページのトップに戻る
275374Re:二つのシートから重複していない行を見つけたい。take 2005/07/15-20:59

記事番号275347へのコメント
アドバイスありがとうございます。

営業把握分のI2に
=IF(COUNTIF(登録分!I:I,F2)=0,ROW())　と入力し、
I列のフィルハンドルをI219までをつかんで下へ引っ張りましたが、
2～219までの数字になってしまいました。。

>=INDEX(営業把握分!F:F,SMALL(営業把握分!I:I,ROW(A1)))
の使い方というか、どのセルに入力するものなのかわからないのですが。。

とりあえず、再度考えながら試してみようと思います。



▲このページのトップに戻る
275376Re:二つのシートから重複していない行を見つけたい。白茶 2005/07/15-21:28

記事番号275374へのコメント
やや、
どうも、遅くまでお疲れ様です。


>営業把握分のI2に
>=IF(COUNTIF(登録分!I:I,F2)=0,ROW())　と入力し、
>I列のフィルハンドルをI219までをつかんで下へ引っ張りましたが、
>2～219までの数字になってしまいました。。

ということは、
参照先などが間違っていなければ、
営業把握分の住所は登録分の住所と
１件もダブっていないということになります。

明らかに重複している事が分っているのであれば、
その２つのセル内容をよ～く比較してみて下さい。

住所の文字列が微妙に異なっている事が原因かもしれません。

例えば、
番地が全角だったり半角だったりとか、
不要なスペースが末尾に付いているとか、

この程度なら関数の追加で何とかできると思います。

あるいは、
片方の住所録は県名が無いとか、　(それは無いか)



>
>>=INDEX(営業把握分!F:F,SMALL(営業把握分!I:I,ROW(A1)))
>の使い方というか、どのセルに入力するものなのかわからないのですが。。

何処でも良いのですよ。
オススメは新規シートの
A1="「営業把握分」にはあるが「登録分」にない住所"
として、
A2に式を入力し、
下方向へフィルコピーでしょうかね。

ROW(A1)は何処のセルに入力する場合でも
ROW(A1)のままです。

実際下へフィルコピーしたときに、
1,2,3,4,5,6,7・・・
と言う数字を取り出したいだけの事ですから。


>
>とりあえず、再度考えながら試してみようと思います。
>

はい。がんばって下さいね。
とりあえず、白茶は今夜はもう帰ります。

また、結果を聞かせて下さいね。
では。


▲このページのトップに戻る
275448追加説明白茶 2005/07/16-11:50

記事番号275376へのコメント
>例えば、
>番地が全角だったり半角だったりとか、
>不要なスペースが末尾に付いているとか、
>
>この程度なら関数の追加で何とかできると思います。
>
>あるいは、
>片方の住所録は県名が無いとか、
>

の追加説明です。


>番地が全角だったり半角だったりとか、

番地の表記が全角／半角入り乱れている場合は、
JIS関数で全角に統一できます。


>不要なスペースが末尾に付いているとか、

住所録が元々他のソフトから持ってきているデータだったりすると、
住所の前後や単語間に不要なスペースがくっ付いている場合があります。
TRIM関数で不要なスペースを削除できす。


>片方の住所録は県名が無いとか、

「○○県○○市○○」と「○○市○○」は当然同じとは判断されません。
仮にA2に「○○県○○市○○」とあったら、
=IF(ISERROR(FIND("県",A2)),A2,RIGHT(A2,LEN(A2)-FIND("県",A2)))
で「○○市○○」に書き換えることが出来ます。

ただし、
世の中には、市郡町村字に「県」という漢字を使う地名がありますので、
用心して下さい。

～参考例(旧市町村名ですが)～
○：「広島県山県郡千代田町」→「山県郡千代田町」
×：「山県郡千代田町」→「郡千代田町」


番地の表記に関して。
「○○町３丁目１－２」と「○○町３－１－２」も当然同じとは判断されません。
SUBSTITUTE関数で「丁目」を「－」に置き換えられます。
「○○番地○」と「○○－○」も同様。

複合使用例：
=SUBSTITUTE(JIS(TRIM(A2)),"丁目","－")

これらを組み合わせて、
住所録のデータ表記を可能な限り統一し、
この更にした住所録に対して前述のCOUNTIF式を使用すると、
上手く行くかも知れません。




▲このページのトップに戻る
275478Re:追加説明take 2005/07/16-14:44

記事番号275448へのコメント
白茶さん。度々アドバイスありがとございます。
営業分の住所録の数字が全角で、登録分が半角なので、営業把握分の列を挿入して、
ASC関数を使い半角リストを作れました。
あまり関数のしくみ、使い方を理解できていませんが、これから試してみようと思います。

▲このページのトップに戻る
275433Re:二つのシートから重複していない行を見つけたい。半端者 2005/07/16-10:03

記事番号275347へのコメント
　白茶さんの例示した式
>=INDEX(営業把握分!F:F,SMALL(営業把握分!Z:Z,ROW(A1)))
で、「ROW(A1)」のところが引っかかっているのかな？

　teruさん、ひとつひとつの関数の意味は把握できていますか？！
把握できていれば、回答者の記述式にミス（質問者のBook,Sheet全体が把握できて
いるわけではないので、ミスというよりは、「想定回答上の許容範囲内での記述」
とするべき）があったとしても、関数式の意味が把握できていれば、実際のSheet
上で修正できる筈です。
　私が貼った「VLOOK関数を用いた式」も、考え方を提示し、実務上で修正の上、
応用することを前提に記述してあります。（白茶さん程親切な書き方ではありませ
ん）これは、実際に、解決のために試行錯誤している現場に立ち会っているわけで
はないので、仕方のないことです。

　このサイト↓を参考にして、関数の意味を把握しましょう。
http://t_shun.at.infoseek.co.jp/My_Page/Excel-FNC/fnc_title.htm

恐らく、もう一歩のところまで来ているはず。頑張って下さい。

▲このページのトップに戻る
275319Re:二つのシートから重複していない行を見つけたい。半端者 2005/07/15-15:57

記事番号275297へのコメント
　最も簡単なやり方を

1.エクセルファイルに３つのシートがあります。
　　　「営業」「登録」「一覧」というシートです。
２．「営業」と「登録」の表の構成は、
　［列A］コード、［列B］氏名、［列C］住所となっていて、１行目は項目名です。
　２行目以下にデータが入っていて、コードは昇順に並んでいます。
３．「一覧」の表の構成は、
　［列A］コード、［列B］氏名、［列C］「営業」の住所、［列D］「登録」の住所
　となっていて、１行目は項目名です。
４.（1）「営業」にはあるが「登録」にない住所
　 （2）「登録」にはあるが「営業」にない住所
　という条件で、「一覧」に、［列A］コードにマッチした「営業」と「登録」
の氏名、住所のデータを表示します。

［列A行2］XXXXX 【コードを入力する】
［列B行2］=VLOOKUP(A2,登録!A:C,2) 【［列A行2］にマッチする氏名を表示】
［列C行2］=IF(VLOOKUP(A2,営業!A:C,3)="","",IF(VLOOKUP(A2,登録!A:C,3)<>"","",VLOOKUP(A2,営業!A:C,3)))
　　　　　【［列A行2］にマッチする「営業」の住所を表示】
［列D行2］=IF(VLOOKUP(A2,登録!A:C,3)="","",IF(VLOOKUP(A2,営業!A:C,3)<>"","",VLOOKUP(A2,登録!A:C,3)))
　　　　　【［列A行2］にマッチする「登録」の住所を表示】

５．２行目の計算式を３行目以下にコピー

で、どうだ～！！
　疲れた。（ブランドタッチじゃないので、記述するのが疲れる）
　コードマッチングじゃないやり方や、他、方法はいくらでもありますが、
他の人にも参考式の提示求む！
　ただ、数千行のファイルマッチングは、Excelじゃ重たすぎるよ。
　メモリ容量とＣＰＵの性能次第では、フリーズの危険有。

▲このページのトップに戻る
275346Re:二つのシートから重複していない行を見つけたい。半端者 2005/07/15-18:03

記事番号275319へのコメント
　「takeさん」 試して頂けましたか。
まあ、「上げ」については、ルールが分からなかったのでしょう。
私も知らないことが多いです。お互いに今後、気をつけましょう。
　ただ、
　「白茶」さんの言われることはもっともで、回答者は、質問者が回答を試行して
出た結果が成否のどちらであれ、その結果を見て、自分のスキルを上げる努力をし
たり、自分の認識の誤っていたところを正したりすることが出来るわけです。
　したがって、回答者には、自分の回答によって必ず質問者の問題が解決すること
に責任は負いませんが、解決に向けて「何とかしてあげよう」と、真剣に取り組ん
でいる以上、質問者は、回答を試行した結果に対しての報告をするべきだと思い
ます。（義務とは、言いませんが）
　何人かの回答が、ほぼ同時刻に付いて、その中から、自分の判断でひとつを採用
し、それで解決できれば、「×××さんのアドバイスで解決できました」でも構わ
ないし、いくつか試してみて、どれも解決に至らず、一旦打ち切るなら、
「残念ながら、皆さんのアドバイスを一通り試しましたが、解決できませんでした。
自分なりに、もう少し考えて見ます」でも良いと思います。

　所用があり、外出してそのまま帰ります。
　良い結果が出ますよう、お祈り致します。　

▲このページのトップに戻る
275372Re:二つのシートから重複していない行を見つけたい。take 2005/07/15-20:39

記事番号275346へのコメント
アドバイスありがとうございます。
また、返信遅くなり失礼しました。

したがって、回答者には、自分の回答によって必ず質問者の問題が解決すること
>に責任は負いませんが、解決に向けて「何とかしてあげよう」と、真剣に取り組ん
>でいる以上、質問者は、回答を試行した結果に対しての報告をするべきだと思い
>ます。

全く、半端者さんの言うとおりです。白茶さんもとても丁寧に提示してくださり、
それに対して、試行した結果どうなったかという報告をすべきだと思いました。
これからはその意識を持って質問していこうと思います。

未だ、問題の検索結果が見れませんが、考えながら行っていこうと思います。
成功した際には、どのようにしてできたのかを、ご報告します。
レスが死んだ場合は新レスでお礼という形で報告するつもりです。
よろしくお願いします。

▲このページのトップに戻る
275749遅くなりました。take 2005/07/17-17:39

記事番号275372へのコメント
返信遅くなりました。
二つのシートからそれぞれのどれがないのかを、リストにできました。

結果、登録分にあって営業把握分にないリストは、
=IF(COUNTIF(営業把握分!F:F,G2)=0,ROW())を使って数字がFALSEで帰ってくるものも
あり、どれがリストにない分なのか、というのが見分けられるようになりました。
営業把握分にあって登録分に無いリストも
=IF(COUNTIF(登録分!G:G,F2)=0,ROW())　で数字とFALSEで見分けられるようになりました。

ただ、元のファイルデータが片方は番地が全角や、半角が入り混じっていたり、
～県が入っていたり市から始まっていたりなど、お互いのシートの表示内容がかなり違って
いたのでとても作りにくかったですが。。
半角、全角に関してはアドバイスいただいいた内容の逆の関数でまとめられました。

IF関数の使い方がまだまだわからず、教えてもらった式を
もらいっぱなしで列を修正してできましたが、時間のあるときに関数を
理解していこうと思います。
今回は何度もアドバイスいただき、本当にありがとうございました。