回答 受付中

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

  • 困ってます
  • 質問No.9590816
  • 閲覧数151
  • ありがとう数2
  • 気になる数0
  • 回答数11

お礼率 13% (9/66)

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

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

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

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

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

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

回答 (全11件)

  • 回答No.11

ベストアンサー率 64% (11/17)

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

ベストアンサー率 64% (11/17)

こういう質問には空気を読まず「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.よくわからない何かが出てくるけど落ち着いて、画面の上側の「挿入(i)」をクリックする。
3.その下に表示される「標準モジュール(M)」をクリックする。
4.それまで灰色だった部分が白くなるので、そこに上の『ここから』『ここまで』の間をコピーして貼り付ける
5.手順2で開いた良く分からないヤツを閉じる。
6.作業用のブックに戻るので、キーボードの「Alt」と「F8」を同時に押す
7.「不一致検出」を選択した状態で「実行」をクリックする

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

この辺りも念のため手順に追加しておくとベストかも。
  • 回答No.9

ベストアンサー率 43% (189/433)

今晩は
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

ベストアンサー率 22% (239/1078)

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

ベストアンサー率 20% (946/4520)

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

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

ベストアンサー率 20% (946/4520)

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

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

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

>面白い。試してみよう!

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

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

ベストアンサー率 22% (239/1078)

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))),"")
【お断り】上式は必ず配列数式として入力のこと
補足コメント
Cyber-r

お礼率 13% (9/66)

よくこんな関数かけますね。ちょっとこのまま使ってみます。
ありがとうございす。
投稿日時 - 2019-02-23 21:56:51
  • 回答No.4

ベストアンサー率 28% (4468/15889)

Excel(エクセル) カテゴリマスター
VLOOKUP関数の第1引数にA列の1セルデータを、第2引数の参照表をC列($C$1:$C$xxx。絶対番地範囲にすること。)、第3引数は、普通は隣列のデータをもってきたりするが、本件は存在のチェックのために表を引くので、1、第4引数をFALSEとして(間隔に存在でなく、一致したものを探すのでFALSE)して、
D1に式を入れて、下方向に式を複写する。
D列に、見つからないという内容の、エラーが出た行をチェックする。
VLOOKUP関数は、エクセルの有名な筆頭関数です。WEBを調べるとすぐ例も見つかる。
http://www.excel-list.com/vlookup.HTML他多数
ISERRORを使いたければ、Googleで「vlookup関数 iserror」で照会のこと。
http://kokoro.kir.jp/excel/vlookup-iserror.html
エクセル使いでVLOOKUP関数を思いつかなようではね。
MATCH関数もエラー検出というところは同じ働きをさせられる。
VLOOKUP関数の第3引数のような余分なことを、考えなくてよい。
ーー
質問者が、実際やってみて、思うところがあれば別質問する。
他のチェック点はプログラムの作成のスキルが要るので、質問者には無理だろう。
====
お勧めの、意外に良い方法は、(コピーを別に取っておいて)A列をソートして、人間の目視(教養)で、チェックすることだ。
間違いや、変なものは、別の行ブロック(カタマリ)の間に、紛れて出てくるので、一見してよくわかる。数千行ならチェックはすぐ終わるよ。
補足コメント
Cyber-r

お礼率 13% (9/66)

ありがとうございます。たすかります。
投稿日時 - 2019-02-23 21:54:53
  • 回答No.3

ベストアンサー率 54% (462/850)

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

お礼率 13% (9/66)

=IF(ISERROR(MATCH(A2,$C:$C,0)),"間違い","")
B2にこれを入れて、下にコピーですね。
あ!できました!ありがとうございます。
投稿日時 - 2019-02-23 21:11:34
  • 回答No.2

ベストアンサー率 55% (47/84)

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

お礼率 13% (9/66)

ありがとうございます。初心者の私には難しいページですが何回か読んでみたいとおもいます。
私が求めてたのは
https://www.excelspeedup.com/syougou/
これが近いんですが、うまくいきません。
投稿日時 - 2019-02-23 21:03:20
11件中 1~10件目を表示
AIエージェント「あい」

こんにちは。AIエージェントの「あい」です。
あなたの悩みに、OKWAVE 3,500万件のQ&Aを分析して最適な回答をご提案します。

関連するQ&A

その他の関連するQ&A、テーマをキーワードで探す

キーワードでQ&A、テーマを検索する

特集


OKWAVE若者応援スペシャル企画

ピックアップ

ページ先頭へ