[[20141202053610]]　『セル間で25文字以上連続重複しているセルを特定す』（nick）　ページの最後に飛ぶ

『セル間で25文字以上連続重複しているセルを特定する。』（nick）

約40,000件のセル（約100文字のセル2行×20,000列)間で、「25文字以上連続で重複している」セルを見つけ出す必要があります。

＜例１＞
次の２つのセルは35文字連続重複（私はいつも夕食を食べるのが遅くなってしまい、体重は増えるばかりです。そ）しており、今回見つけ出したいセルに該当します。

セル１：こんにちは。私はいつも夕食を食べるのが遅くなってしまい、体重は増えるばかりです。そんな私が困っているのは・・・
セル２：こんばんは。私はいつも夕食を食べるのが遅くなってしまい、体重は増えるばかりです。そこで私は毎日運動を・・・

＜例２＞
次の２つのセルは例1と似ていますが、最大20文字連続重複（私はいつも夕食を食べるのが遅くなってしま）のため、今回見つけ出したいセルには該当しません。

セル１：こんにちは。私はいつも夕食を食べるのが遅くなってしまうため、体重は増えるばかりです。そんな私が困っているのは・・・
セル２：こんばんは。私はいつも夕食を食べるのが遅くなってしまい、体重は増えるばかりです。そこで私は毎日運動を・・・

例１のようなセルを特定することはエクセルで可能でしょうか。
可能であれば、その方法をご教授いただけますと幸いです。

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 UDF
 使用例:
 セルに =IsSimiler(A1,B1,30)
 A1,B1はそれぞれ比較文字列のセルアドレス
 30は最少比較文字列数

 標準モジュールへ

 Function IsSimiler(ByVal txt1 As String, ByVal txt2 As String, myLength As Long) As Boolean
    With CreateObject("VBScript.RegExp")
        .Pattern = "(.{" & myLength & "}).*" & Chr(2) & ".*\1"
        IsSimiler = .test(txt1 & Chr(2) & txt2)
    End With
End Function

（seiya） 2014/12/02(火) 06:20

 >約40,000件のセル（約100文字のセル2行×20,000列)間で、「25文字以上連続で重複している」セルを見つけ出す必要があります。
 これはその組み合わせを知りたいのか、相手はわからなくてもいいのか？
 (例えばA1セルとB3セルが一致しているということが判ればいいのか、A1セルは相手セルはわからないが重複があるということが判ればいいのか)
（ねむねむ） 2014/12/02(火) 09:19

 質問者じゃないんですが、教えてください。

 Patternの 
 １）"(.{n})" は、任意の一文字がn回連続する文字列で
 ２）"\1"     は、↑の"()"の後方参照で、↑に合致したパターンの文字を指す
 と認識しています。

 それで、以下のような場合結果からいえばTrueになるのですが
    Sub 勉強1()
        Dim a As String, b As String
        a = "あいうえお"
        b = "かいうえお"
        Debug.Print IsSimiler(a, b, 3)
    End Sub

 １）から順に見ていくと、１）では「あいう」が検索され、
 ２）でも「あいう」に合致したパターンになる
 ・・・のではないでしょうか？

 なんで「いうえ」が該当するのか、それらしい解説を探せなかったのでぜひ教えていただければ
 と思います。
（稲葉） 2014/12/02(火) 09:31

 caret等を使用して「合致検索の開始位置」を限定していないから。
（seiya） 2014/12/02(火) 09:52

 そうなると、
 「あいう」「いうえ」「うえお」も対象になると思いますが、
 そこで「いうえ」が合致するというのは、どのように考えればいいのでしょうか？
 以下のようにしても、「あいう」しかヒットしません。
    Sub 勉強2()
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.{3})"
            If .test("あいうえお") Then
                Debug.Print .Execute("あいうえお").Count
                Debug.Print .Execute("あいうえお")(0)
            End If
        End With
    End Sub

 イメージとしてはPatternが以下のように変化していると思っていたのですが・・・
 (.{3}).*\1
 あいう.*\1
 あいう.*あいう　←これが最終的なパターンになる？

 後方参照は↓のような処理を内部で行っているのでしょうか・・・？
    Sub 勉強3()
        Dim a As String, b As String
        Dim n As Long
        Dim i As Long, j As Long
        a = "あいうえお"
        b = "かいうえお"
        n = 3
        For i = 1 To Len(a) - n
            For j = 1 To Len(b) - n
                If Mid(a, i, n) = Mid(b, j, n) Then
                    Debug.Print Mid(a, i, n) & " : " & Mid(b, j, n)
                    Debug.Print True
                    Exit Sub
                End If
            Next j
        Next i
    End Sub
（稲葉） 2014/12/02(火) 10:19

 正規表現のマッチングが理解できていないね。

 >.Pattern = "(.{" & myLength & "}).*" & Chr(2) & ".*\1"
 と
 >.Pattern = "(.{3})"
 の 括弧内のマッチング対象はまるっきり別なもの。

 上は Chr(2) を挟んで前後で任意の文字数の文字列が繰り返されているものにマッチング。
 下は任意の3文字、ということは常に最初の3文字にマッチングして終了。
（seiya） 2014/12/02(火) 10:36

 seiyaさんの説明と
http://takenaka-akio.org/doc/perl_kiso/match2.html
 こちらの「同じパターンが再現する'というパターンは後方参照で 」の中の失敗例で
 理解できたと・・思います。

 トータルでマッチングするものと、単体でマッチングするものはまったく別もので、
 あくまでChr(2)を挟んだ左右がマッチングしないものは、Falseということですね。

 すみません、長々とありがとうございました。
（稲葉） 2014/12/02(火) 11:27

ご返信遅くなり、申し訳ございません。
ご丁寧にご返信いただき、ありがとうございました。

＞seiya様
今回のケースですが、特定データとそれ以外のデータ（例：A1とA1以外のデータ39,999件）で重複チェックを行う場合、どのようにすればよろしいでしょうか。
知識が乏しく、度々ご質問してしまい恐縮ですが、お知恵をお借りできますと幸いです。

＞ねむねむ様
可能であれば組み合わせまで知りたく思っております。
（nick） 2014/12/08(月) 18:42

 重複チェックしてその結果の期待値は？
（seiya） 2014/12/08(月) 22:11

ご返信ありがとうございます。
ここでいう期待値とは、40.000件のチェックの結果、どれだけの重複が発生するかを示したものという認識でお間違えございませんでしょうか。想定ですが、全体の10%強重複が見られると考えております。
（nick） 2014/12/09(火) 08:15

 こういうこと？

 =CountSimiler(A1,A1:A1000,25)
 A1: 検索文字列のあるセル
 A1:A1000: 検索範囲
 25: 文字数

 Function CountSimiler(ByVal txt As String, ByVal rng As Range, myLength As Long) As Long
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Pattern = "(.{" & myLength & "}).*" & Chr(2) & ".*\1"
        For Each r In rng
            If .test(txt & Chr(2) & r.Value) Then CountSimiler = CountSimiler + 1
        Next
    End With
End Function

（seiya） 2014/12/09(火) 08:36

 回答じゃないけど言いたいことがあったので。
 ↓にも回答付いてたのにお礼のコメントなしですか。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13138911678
 エクセルの学校はマルチポスト容認してるから別のサイトで質問してもいいけど放置するのはいただけないですね。
（bi） 2014/12/09(火) 10:07

 私の場合は関数で考えていて、二つのセルで25文字以上一致しているかどうかまでしかできなかったため離脱しているが本筋から離れた場所で気になることがあるので。

 >約100文字のセル2行×20,000列
 Excelでは行は縦に1行、2行と増えていき、2007では1,048,576行まで、列は横にA列(1列)、B列(2列)と増えていき2007ではXFD列(16,384列)まで。
 列と行が逆になっていないか？
（ねむねむ） 2014/12/09(火) 10:20

コメント返信：

[ 一覧(最新更新順) ]

[[20141202053610]] 『セル間で25文字以上連続重複しているセルを特定す』（nick） ページの最後に飛ぶ

[[20141202053610]]　『セル間で25文字以上連続重複しているセルを特定す』（nick）　ページの最後に飛ぶ