Excelに関してお願いします!


今、A1~A10000セルに半角英数字の文字列(全て95桁)が入っています。
C1に任意の半角英数字の文字列(95桁)を入力します。

もし、A列に完全一致する文字列があるならば、すぐ横のB列に「0」とフラグをたてたいです。

・2桁目は違うが他の94桁は一致
・89桁目は違うが他の94桁は一致
など、差分がわずか1桁だけのものに対しは、すぐ横のB列に「1」とフラグをたてたいです。

同様に考えていき、全桁が違う場合は、すぐ横のB列に「95」とフラグをたてたいです。

うまくVBAとかでできればよいなと思いますが私には難しいです。
どうぞよろしくお願い致します。

※95桁の文字列には、半角アルファベットの大文字小文字が混在しているため、MATCH関数とかはいまいちかもです。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/09/17 16:51:57
  • 終了:2011/09/20 10:11:16

回答(3件)

id:Lhankor_Mhy No.1

Lhankor_Mhy回答回数779ベストアンサー獲得回数2302011/09/17 17:54:34

ポイント10pt

強引な方法ですが、マクロ使用してません。

 ABCDEF
1 =95-SUM(D1:F1) =(MID(A1,1,1)=MID(B1,1,1))+(MID(A1,2,1)=MID(B1,2,1))+(MID(A1,3,1)=MID(B1,3,1))+(MID(A1,4,1)=MID(B1,4,1))+(MID(A1,5,1)=MID(B1,5,1))+(MID(A1,6,1)=MID(B1,6,1))+(MID(A1,7,1)=MID(B1,7,1))+(MID(A1,8,1)=MID(B1,8,1))+(MID(A1,9,1)=MID(B1,9,1))+(MID(A1,10,1)=MID(B1,10,1))+(MID(A1,11,1)=MID(B1,11,1))+(MID(A1,12,1)=MID(B1,12,1))+(MID(A1,13,1)=MID(B1,13,1))+(MID(A1,14,1)=MID(B1,14,1))+(MID(A1,15,1)=MID(B1,15,1))+(MID(A1,16,1)=MID(B1,16,1))+(MID(A1,17,1)=MID(B1,17,1))+(MID(A1,18,1)=MID(B1,18,1))+(MID(A1,19,1)=MID(B1,19,1))+(MID(A1,20,1)=MID(B1,20,1))+(MID(A1,21,1)=MID(B1,21,1))+(MID(A1,22,1)=MID(B1,22,1))+(MID(A1,23,1)=MID(B1,23,1))+(MID(A1,24,1)=MID(B1,24,1))+(MID(A1,25,1)=MID(B1,25,1))+(MID(A1,26,1)=MID(B1,26,1))+(MID(A1,27,1)=MID(B1,27,1))+(MID(A1,28,1)=MID(B1,28,1))+(MID(A1,29,1)=MID(B1,29,1))+(MID(A1,30,1)=MID(B1,30,1))+(MID(A1,31,1)=MID(B1,31,1))=(MID(A1,32,1)=MID(B1,32,1))+(MID(A1,33,1)=MID(B1,33,1))+(MID(A1,34,1)=MID(B1,34,1))+(MID(A1,35,1)=MID(B1,35,1))+(MID(A1,36,1)=MID(B1,36,1))+(MID(A1,37,1)=MID(B1,37,1))+(MID(A1,38,1)=MID(B1,38,1))+(MID(A1,39,1)=MID(B1,39,1))+(MID(A1,40,1)=MID(B1,40,1))+(MID(A1,41,1)=MID(B1,41,1))+(MID(A1,42,1)=MID(B1,42,1))+(MID(A1,43,1)=MID(B1,43,1))+(MID(A1,44,1)=MID(B1,44,1))+(MID(A1,45,1)=MID(B1,45,1))+(MID(A1,46,1)=MID(B1,46,1))+(MID(A1,47,1)=MID(B1,47,1))+(MID(A1,48,1)=MID(B1,48,1))+(MID(A1,49,1)=MID(B1,49,1))+(MID(A1,50,1)=MID(B1,50,1))+(MID(A1,51,1)=MID(B1,51,1))+(MID(A1,52,1)=MID(B1,52,1))+(MID(A1,53,1)=MID(B1,53,1))+(MID(A1,54,1)=MID(B1,54,1))+(MID(A1,55,1)=MID(B1,55,1))+(MID(A1,56,1)=MID(B1,56,1))+(MID(A1,57,1)=MID(B1,57,1))+(MID(A1,58,1)=MID(B1,58,1))+(MID(A1,59,1)=MID(B1,59,1))+(MID(A1,60,1)=MID(B1,60,1))+(MID(A1,61,1)=MID(B1,61,1))+(MID(A1,62,1)=MID(B1,62,1))=(MID(A1,63,1)=MID(B1,63,1))+(MID(A1,64,1)=MID(B1,64,1))+(MID(A1,65,1)=MID(B1,65,1))+(MID(A1,66,1)=MID(B1,66,1))+(MID(A1,67,1)=MID(B1,67,1))+(MID(A1,68,1)=MID(B1,68,1))+(MID(A1,69,1)=MID(B1,69,1))+(MID(A1,70,1)=MID(B1,70,1))+(MID(A1,71,1)=MID(B1,71,1))+(MID(A1,72,1)=MID(B1,72,1))+(MID(A1,73,1)=MID(B1,73,1))+(MID(A1,74,1)=MID(B1,74,1))+(MID(A1,75,1)=MID(B1,75,1))+(MID(A1,76,1)=MID(B1,76,1))+(MID(A1,77,1)=MID(B1,77,1))+(MID(A1,78,1)=MID(B1,78,1))+(MID(A1,79,1)=MID(B1,79,1))+(MID(A1,80,1)=MID(B1,80,1))+(MID(A1,81,1)=MID(B1,81,1))+(MID(A1,82,1)=MID(B1,82,1))+(MID(A1,83,1)=MID(B1,83,1))+(MID(A1,84,1)=MID(B1,84,1))+(MID(A1,85,1)=MID(B1,85,1))+(MID(A1,86,1)=MID(B1,86,1))+(MID(A1,87,1)=MID(B1,87,1))+(MID(A1,88,1)=MID(B1,88,1))+(MID(A1,89,1)=MID(B1,89,1))+(MID(A1,90,1)=MID(B1,90,1))+(MID(A1,91,1)=MID(B1,91,1))+(MID(A1,92,1)=MID(B1,92,1))+(MID(A1,93,1)=MID(B1,93,1))+(MID(A1,94,1)=MID(B1,94,1))+(MID(A1,95,1)=MID(B1,95,1))

 

D~F列を作業列として使ってます。

id:miku1973

ありがとうございます!

関数なのは魅力的です。

実は例として10000個の文字列としたのですが、実を言いますと本番では100万個以上のものを使いまして、関数を組みまくるとファイルがかなり重くなり計算もかかるかなぁというのが懸念事項です。

2011/09/17 17:59:29
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912011/09/17 22:51:57

ポイント150pt

下記を標準モジュールに置き

B1=MyStrComp(A1,$C$1)

でB2以下にコピーでどうでしょうか。


速度はわかりませんが、数万行となるとやはりそれなりには遅いかもしれません。

こちらで100桁を30000行試してみましたが、10秒くらいはかかりました。


Function MyStrComp(strA, strB)
    Dim minLen As Long
    minLen = Len(strA)
    If Len(strA) >= Len(strB) Then minLen = Len(strB)
        
    MyStrComp = Abs(Len(strA) - Len(strB))
    Dim i As Long
    For i = 1 To minLen
        If Mid(strA, i, 1) <> Mid(strB, i, 1) Then MyStrComp = MyStrComp + 1
    Next
End Function
id:miku1973

ユーザ定義関数が100万件全ての結果を出すのに約10分でした。結果は正しいように思います。ありがとうございます!

2011/09/20 10:09:24
id:ymlab No.3

ymlab回答回数506ベストアンサー獲得回数332011/09/18 12:34:31

ポイント40pt

VBAで組みました。


Option Explicit

Public Sub matches()
    Dim i As Long    '何行目まで検索するか。10000になりますね。
    Dim icnt As Integer  'カウンタ
    
    Dim jcnt As Integer
    
    
    i = 10000               '今回の場合は10000になります。
    
    Dim szA As String   'i行A列のセルの中身
    Dim szC As String   'i行C列のセルの中身
    
    Dim noMatch As Integer
    
    Dim szTemp1 As String
    Dim szTemp2 As String
        
    For icnt = 1 To i Step 1    '今回はnext icntまでをicntの値を1ずつ増やしながらicnt=10000になるまで繰り返すことになります。
        szA = ActiveSheet.Cells(icnt, 1).Value  'szAにicnt行A列を代入
        szC = ActiveSheet.Cells(icnt, 3).Value  'szCにicnt行C列を代入
        
      
        noMatch = 0                             'ヒットしなかった文字の個数を初期化します。
        For jcnt = 1 To Len(Trim(szA)) Step 1   'icnt行A列の文字数分走査していきます.
            If jcnt > Len(Trim(szC)) Then       'でも、A列の文字数が多くてC列の方が少なかった場合、きっとエラーがでるので、その場合は終了します。
                Exit For                        'ここで、fot分を抜けて終了させています。
            End If
            szTemp1 = Mid(szA, jcnt, 1)         'icnt行A列のjcnt番目の文字を抽出しています。
            szTemp2 = Mid(szC, jcnt, 1)         'icnt行C列のjcnt番目の文字を抽出しています。
            
            If szTemp1 <> szTemp2 Then          'szTemp1とszTemp2が違っていたらNoMatchに1加算します。
                noMatch = noMatch + 1
            End If
        Next jcnt
        ActiveSheet.Cells(icnt, 2) = noMatch    '最後にicnt行B列にnoMatchを代入してicnt行のフェーズは終了します。
    Next icnt
End Sub



ただし、誤入力などにより、A列とC列に違いがある場合、A列を優先させています。

本番では、100万行あるとのこと。

業務上難しいかも知れませんが、

個人的には、Excelは、65535行までしか扱えませんので(実は私が知らないだけで、方法があるかも)、

ExcelからCSVに保存して、別の言語系で処理をさせた方が近道な気がします。

例えば、C言語で組めば圧倒的に高速に処理は速いでしょうし、

そこまで速度を求めないのであれば、PHP等で簡単に処理してもよいような気がします。

以前、私も学生時代に40万イベントを何度も処理しないといけない自体になり、

Excelを使うことができませんでしたから・・。

がんばってくださいね。

id:miku1973

約100万件に1分10秒かかりました。 しかし結果は全て「0」となってしまうようでした。でもありがとうございます。

2011/09/20 10:10:23
  • id:Mook
    最大行数 65536行は EXCEL2003以前ですね。
    EXCEL2007以降では 100万(1048576)行まで拡張されています。
  • id:miku1973
    とりあえずいただいた記述をもとに改良を重ねてみます。
    ありがとうございました!
    VBAの質問はよくするので、今後もどうぞよろしくお願い致します。
  • id:Mook
    標準モジュールに回答の関数と下記の関数をおいて下記を実行してみて下さい。
    こちらで試した際は100万行で約30秒でした。

    Sub CompAll()
      Debug.Print Now()
      src = Range("C1").Value
      Dim tbl
      tbl = Range("A1").Resize(Rows.Count)

      Dim res
      res = Range("B1").Resize(Rows.Count)

      For r = 1 To Rows.Count
        res(r, 1) = MyStrComp(tbl(r, 1), src)
      Next
      
      Range("B1").Resize(Rows.Count) = res
      Debug.Print Now()
    End Sub
  • id:Mook
    追伸:メモリの空きが250Mぐらい必要ですので、余裕が無い場合は
    性能がでないかもしれません。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません