エクセル内に数万件のレコードが書き込まれています。
登録されているデータの品質をチェックしたいため、
特定の列を参照し、類似しているデータは一括で抽出したいと考えています。
エクセルの関数だけに限った際、
下記のようなやり方があるかと思いますが、
一括で抽出することができないためお知恵をいただきたい所存です。
現在のチェック方法
(1)B2のリンゴをA1にコピーし、C列はA1とB列の文字列の適合率を表示する。
(2)C列の適合率が高いもの(仮に90%以上適合した文字列)を目検でチェック
(3)B3以降をA1に順次コピーして(1)~(2)のチェックを行う
|A列 |B列 |C列
1 |リンゴ | |
2 | |リンゴ |100%
3 | |すいか |0%
4 | |もも |0%
5 | |すもも |0%
6 | |メロン |33%
7 | |生ハム |0%
8 | |おリンゴ |100%
■ご質問させていただく内容
・近似した文字列の適合率を一括で調べるもしくは抽出するツールをご存知でしょうか?
・もしくは、上記に記載した内容をマクロで組むことができるでしょうか?
例えば以下のような流れです。
※上図を参考に説明
(1)Sheet1のB2の文字列の適合率をB2:B8まで順次計算してC列に代入
(2)Sheet1のC列を参照し、B2の行以外で適合率が90%以上の場合、Sheet2のA列に代入
(3)B3~B8まで(1)~(2)の処理を繰り替えす。
※参考になるサイトやプログラムなどをご教示いただければ幸いです。
よろしくお願い致します。
No.2
- 回答日時:
C2に↓の式を入れ、表示書式をパーセンテージにして下方向へコピー
=COUNT(INDEX(FIND(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),A$1),0))/LEN(A$1)
これでC列のデータは出来るので、B:C列をSheet2にコピーしてフィルタで90%以上の物だけ表示しましょう。
ただ、この式が数万個あると相当重くなりそうですから、最初からVBAの方が良いかなぁ……。
ご教示くださいましてありがとうございます。
確かに処理が非常に重いようです。
ベストアンサーはVBAのプログラムをご提供いただけたtom04様とさせていただきます。
またよろしくお願いいたします。
No.3
- 回答日時:
【仕様設計】
検索準備:[済]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
チェックするデータ:[リンゴ______]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
類似データ一覧 [2/52300]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
おリンゴ |100%
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
メロン |_33%
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
例えば、[チェックするデータ]に'リンゴ'と入力すると、[類似データ一覧]に類似度を計算し高い順に表示される。こういう仕様設計が先かと思いますよ。
【大まかにアプリケーションの流れを決める】
次に、大まかにアプリケーションの流れを決めます。
1、数万件のデータを変数に取り込む。
↓
2、チェックするデータが入力されたら次の処理を行う。
2.1、数万件のデータとの照合を行う。
2.2、ヒットしたらデータ名と類似度を記憶する。
2.3、記憶したデータを並び替える
2.4、並び替えたデータを表示する。
2.5、ヒット総数を更新する。
【アプリケーションを書く情報を収集する】
・[検索準備]ボタンを押すと対象データを変数に取り込むには?
・取り込んだら[未]を[済]に書き換えるには?
・類似度算出アルゴリズムを書くには?
・・・・・
・・・・・
このように、開発プロセスを細かくWordkか何かで書くこと。そして、徐々に、最終コードへと接近することです。
祈、成功!
ご教示くださいましてありがとうございます。
作成手順など、情報をご提供いただけるだけでも助かります。
またよろしくお願いいたします。
No.4ベストアンサー
- 回答日時:
こんばんは!
ExcelでVBAの一例です。
↓の画像で左側がSheet1・右側がSheet2とします。
Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, cnt As Long, str As String, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
i = wS2.Cells(Rows.Count, "A").End(xlUp).Row
If i > 1 Then
Range(wS2.Cells(2, "A"), wS2.Cells(i, "C")).ClearContents
End If
wS2.Range("B:B").Style = "Percent" '←Sheet2のB列を%表示にしている場合は不要
With wS1
For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1") = .Cells(i, "B")
For j = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
If InStr(.Cells(j, "B"), .Range("A1")) > 0 Then
.Cells(j, "C") = 1
Else
For k = 1 To Len(.Range("A1"))
str = Mid(.Range("A1"), k, 1)
If InStr(.Cells(j, "B"), str) > 0 Then
cnt = cnt + 1
End If
Next k
.Cells(j, "C") = cnt / Len(.Range("A1"))
cnt = 0
End If
If .Range("A1") <> .Cells(j, "B") And .Cells(j, "C") >= 0.9 Then
With wS2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Value = wS1.Cells(j, "B")
.Offset(, 1) = wS1.Cells(j, "C")
.Offset(, 2) = wS1.Range("A1")
End With
End If
Next j
Next i
.Range("C:C").ClearContents
End With
End Sub 'この行まで
※ 余計なお世話かもしれませんが、Sheet2のC列にはSheet1のA1セルのデータの場合で
B列にはそれに対する割合を表示させています。
Sheet2の2行目を説明すると
A1セルが「リンゴ」の時に「おリンゴ」が100%になります
同様に3行目はSheet1のA1セルが「もも」の時に「すもも」が100%
というコトになります。
(Sheet1のC列データは最終的には消しています)
外していたらごめんなさいね。m(_ _)m
プログラム自体、細かいところまでご提供くださいましてありがとうございました。
やりたいこと自体、完璧です。
重ねて御礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- Excel(エクセル) エクセルで2つの表を比較して、文字列が同じだが、その行のある値が違うものを抽出したい 1 2022/10/06 21:48
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- Excel(エクセル) 【VBA】A列にある連続したデータの1番下に文字列を入力したい 1 2023/01/28 04:40
- Excel(エクセル) SUMIF関数について 4 2023/06/14 13:13
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) エクセルの関数を教えてください。② 9 2023/05/25 15:28
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
あいまい検索(文字列一致率)
Visual Basic(VBA)
-
Excel 文字列 一致
Excel(エクセル)
-
最も近い数値のあるセルを探す
Excel(エクセル)
-
-
4
似たような言葉を検索したい
Excel(エクセル)
-
5
Excel countif関数で取り消し線のセルをカウントしないようにしたい countif関数で文
Excel(エクセル)
-
6
エクセルで0を除いて昇順に並べ替えできますか
Excel(エクセル)
-
7
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
8
VBAで「致命的なエラー」が出ました。どのくらい致命的なんでしょうか?
Access(アクセス)
-
9
accessかExcelで類似検索はできますか?
Excel(エクセル)
-
10
テキストファイルから改行コードを削除して読込む方法
Visual Basic(VBA)
-
11
エクセルVBAで画像から文字を抽出出来ますか?
その他(Microsoft Office)
-
12
数式による空白を無視して最終行までコピーするマクロ
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelのマクロについて教えてく...
-
Vba 実数および実数タイプの変...
-
ユーザーフォームに別シートか...
-
VBA レジストリの値の読み方に...
-
エクセルVBAについて
-
VBA listBoxから
-
ExcelのVBAコードについて教え...
-
VBA 複数条件の分岐処理の上手...
-
ExcelのVBAです。フォルダ内の...
-
VBAの計算で@が出てしまう件
-
VB.net(VB)で、フォームにExcel...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
VBAの質問になります Userform内で
-
VBAの質問になります メッセー...
-
Excel マクロについての相談
-
Vba SelStart、SelLen教えてく...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel VBA 定義されたプロージ...
-
Excel-VBAのmsgBox()の不思議
-
【VBA】マクロの入ったファイル...
-
VBA 複数条件の分岐処理の上手...
-
現在のブックを閉じないで、マ...
-
VBAで各列の"+"と"o"の合計数を...
-
VBAに詳しい方教えてください。
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
ユーザーフォームに別シートか...
-
エクセルのマクロについて教え...
-
ExcelVBA シート名を複数セルか...
-
エクセルのマクロについて教え...
-
VBA listBoxから
-
Excelのマクロについて教えてく...
-
エクセルのマクロについて教え...
おすすめ情報