Excelで「正」とするデータとの比較。

質問

Excelで「正」とするデータとの比較。
■添付資料あり（例として簡単にしたものです）

わかる方、教えてください。

やりたいこと：「正しい市町村名」と「入力ミス、存在しない市町村名」のチェック

・Ａ列は、変換ミスや、存在しない市町村名が記載されたデータが約10000件くらいあります。
・Ｃ列は総務省のページから正しい市町村名を並べています。
・Ｃ列を「正」としてＡ列の間違ったデータをＢ列に何らかの形で検出したいです。

「その他条件」（あまり関係ないかもしれませんが）
・Ａ列は重複したデータは沢山ありますが、それはＯＫです。
・Ａ列もＣ列も県名と市町村の間に全角スペースが入っています。

私、関数が苦手でLookUPでやるのか、EXACTでやるのか、何をどうすればいいのか
非常に困ってます。よろしくお願いします。

回答No.11

kon555

No.10ですが、タブのインデントが消えているのでそのままでは使えませんな。
これでどうかなー

'ここから
Sub 不一致検出()
Dim i, α1, α2
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
　α1 = Cells(i, 1).Value
　Set α2 = Range("C:C").Find(What:=α1)
　If α2 Is Nothing Then Cells(i, 2).Value = "不一致"
Next i
MsgBox "完了しました"
End Sub
'ここまで

回答No.10

kon555

こういう質問には空気を読まず「VBAでやればいいのに」と回答するのがお約束なのだな。やればいいのにー。
関数でやるとかなり大変そうだけど、試しに組んだらシンプルに出来たので一応置いときます。

'ここから
Sub 不一致検出()
Dim i, α1, α2
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
α1 = Cells(i, 1).Value
Set α2 = Range("C:C").Find(What:=α1)
If α2 Is Nothing Then Cells(i, 2).Value = "不一致"
Next i
MsgBox "完了しました"
End Sub
'ここまで

【使い方】
1.この作業をしたいファイル開いた状態で、キーボードの「Alt」と「F11」を同時に押す。
(他に開いてるファイルがあったら一旦閉じて)
2.よくわからない何かが出てくるけど落ち着いて、画面の上側の「挿入(ｉ)」をクリックする。
3.その下に表示される「標準モジュール(M)」をクリックする。
4.それまで灰色だった部分が白くなるので、そこに上の『ここから』『ここまで』の間をコピーして貼り付ける
5.手順2で開いた良く分からないヤツを閉じる。
6.作業用のブックに戻るので、キーボードの「Alt」と「F8」を同時に押す
7.「不一致検出」を選択した状態で「実行」をクリックする

動作は一応確認済みです。一致していないセルの横、B列に「不一致」と入ります。
なお
8.頑張って仕事しているフリをしておく
9.適当な頃合で、出来ました、と報告してサッサと帰る。

この辺りも念のため手順に追加しておくとベストかも。

回答No.9

chayamati

今晩は
If関数にISNA関数とVLOOKUP関数を入れ子にしました
添付図中B2の式は
=IF(ISNA(VLOOKUP(A2,$C$2:$C$1900,1,FALSE)),"",VLOOKUP(A2,$C$2:$C$1900,1,FALSE))
です。
B2をB列の他のセルにコピペしました。
次に,BIにフィルタ(添付図の赤枠）をかけて、空白行のみ表示して同じ行のA列正しい市区町村名にするとそれがB列に表示されます。

回答No.8

msMike

http://www.e-stat.go.jp/municipalities/number-of-municipalities
に依れば、全国の市町村数は2,000弱のようなので、[No.5]で提示した式中の「C:C」は「C$2:C$2000」に変更した方が計算が早くなるかも。

回答No.7

山田 太郎（@f_a_007）

【念の為に】データが約10000件くらいあります。

もしかして、比較するとは添付図のようでは・・・。例示のデータだと、ソートして目で見たら判る筈と思うが・・・。それに、１万件というのも・・・。

回答No.6

山田 太郎（@f_a_007）

【参考までに】Excelの関数もVBAも知らないド素人のアイデア

=DLookup("SELECT 'OK' AS 判定 FROM [住所正誤表$B1:C15000] WHERE 正しいデータ='"& B2 & "'",,,"X")

　この式をA2に書いてズズーッと下に。でも、多分、OK!

＞面白い。試してみよう！

なら、DLookup() を紹介します。

《難点》SQL文の綴りをミスなくが最初はシンドイ！が、難しいExcelの関数を書く必要はない。

回答No.5

msMike

B2: =IFERROR(INDEX(A:A,SMALL(IF(A$2:A$11000="","",IF(COUNTIF(C:C,A$2:A$11000),"",ROW(A$2:A$11000))),ROW(A1))),"")
【お断り】上式は必ず配列数式として入力のこと

補足

よくこんな関数かけますね。ちょっとこのまま使ってみます。
ありがとうございす。

回答No.4

imogasi

ＶＬＯＯＫＵＰ関数の第1引数にＡ列の１セルデータを、第2引数の参照表をＣ列（＄Ｃ＄１：＄Ｃ＄ｘｘｘ。絶対番地範囲にすること。）、第３引数は、普通は隣列のデータをもってきたりするが、本件は存在のチェックのために表を引くので、１、第4引数をＦＡＬＳＥとして（間隔に存在でなく、一致したものを探すのでＦＡＬＳＥ）して、
Ｄ１に式を入れて、下方向に式を複写する。
Ｄ列に、見つからないという内容の、エラーが出た行をチェックする。
ＶＬＯＯＫＵＰ関数は、エクセルの有名な筆頭関数です。ＷＥＢを調べるとすぐ例も見つかる。
http://www.excel-list.com/vlookup.HTML他多数
ＩＳＥＲＲＯＲを使いたければ、Ｇｏｏｇｌｅで「vlookup関数 iserror」で照会のこと。
http://kokoro.kir.jp/excel/vlookup-iserror.html
エクセル使いでＶＬＯＯＫＵＰ関数を思いつかなようではね。
ＭＡＴＣＨ関数もエラー検出というところは同じ働きをさせられる。
ＶＬＯＯＫＵＰ関数の第3引数のような余分なことを、考えなくてよい。
ーー
質問者が、実際やってみて、思うところがあれば別質問する。
他のチェック点はプログラムの作成のスキルが要るので、質問者には無理だろう。
＝＝＝＝
お勧めの、意外に良い方法は、（コピーを別に取っておいて）Ａ列をソートして、人間の目視（教養）で、チェックすることだ。
間違いや、変なものは、別の行ブロック（カタマリ）の間に、紛れて出てくるので、一見してよくわかる。数千行ならチェックはすぐ終わるよ。

補足

ありがとうございます。たすかります。

回答No.3

kkkkkm

No1への補足コメントへの返信です。
> 横のB列に何かしら印が検出できれば良いです
No1の式をB2に入れて下にコピー(フィル)すれば画像でのB7とB8に間違いと表示されると思います。

補足

=IF(ISERROR(MATCH(A2,$C:$C,0)),"間違い","")
B2にこれを入れて、下にコピーですね。
あ！できました！ありがとうございます。

回答No.2

mabyos

vlookupを使用して検索ヒットするものはOKとみなし、それ以外はエラーということが最善策な気がしますが、一般的に元データの表記揺れというものがあり、これはエクセルでは難しいことがよくありますので、上記のチェックであぶり出された
エラーデータを最終目で見て判断する必要があると思います。
vlookupの使用上の注意が下記にまとまっています。
https://global-wing.com/activity/excel_vlookup_error.html

補足

ありがとうございます。初心者の私には難しいページですが何回か読んでみたいとおもいます。
私が求めてたのは
https://www.excelspeedup.com/syougou/
これが近いんですが、うまくいきません。