[[20100329192844]] 『重複データの検索方法を教えていただきたいです』(ゆき夫) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『重複データの検索方法を教えていただきたいです』(ゆき夫)
 下記の図で重複データのセルの結果を出したいのです。
 A列:商品コード(数字のみ)
 B列:本支店区分(本店=100)
 C,E,G,I,K列:商品名(Null)あり
 D,F,H,J列:他のデータ(今回の処理には使いません)

 ・重複を見る範囲は各商品CD内(A列が同じ)でB列=100以外の行です
 ・各行内における商品の重複は対象外です

    [A]   [B]   [C]  [D]  [E]  [F]  [G]  [H]  [I]
 [1]  商品CD  本支店   商品1 他項目 商品2  他項目 商品3 他項目  結果
 [2]     123     100     ○
 [3]     123      10     △            ×      ○
 [4]     123      35     ×                                         0
 [5]     223     100     △            ○
 [6]     223      22     ○            △
 [7]     223      43     ×
 [8]     223       6     △            ×                           0
 [9]     323     100     ○            ×            △
 [10]    323      32     △            ○
 [11]    323       2     △                                         0
 [12]    423     100     ×            ○
 [13]    423      23     △            ×
 [14]    423      56     ×                                         0
 [15]    423       8     ×                                         0

 言葉にするとこのような処理がしたいです。
 A列が同じコード内でB列が100以外の行同士の商品1と商品2と商品3をみます。
 2行目はB列が100なので対象外です。
 3行目の商品名を3つdictionaryに代入。
 4行目は商品1が既に3行目にあるので結果に0を入力。
 5行目はB列が100なのでdictionaryをクリア
 6行目の商品名を2つdictionaryに代入。
 7行目は6行目には無い商品名なのでdictionaryに代入。
 8行目は既にdictionaryに入っている商品名なので結果に0を入力

 というような処理をしたいのです。
 同じ行内で重複データがあってもそれは対象外で、異なる行同士での重複チェックです。

 お力を貸してください。どうぞよろしくお願いいたします。

 >C,E,G,I,K列:商品名(Null)あり
 と、表とはちゃうみたいですけど(結果表示のI列は商品名が入っているはず)。
 まぁ、とりあえず表のほうが正しいとして。
     (弥太郎)
 '-----------------------------
Sub Yukio()
    Dim dic As Object, i As Long, n As Integer, tbl, x, y
    Set dic = CreateObject("scripting.dictionary")
    tbl = Range("a2").Resize(Range("a" & Rows.Count).End(xlUp).Row - 1, 7)
    ReDim x(1 To UBound(tbl, 1), 1 To 1)
    For i = 2 To UBound(tbl, 1)
        If tbl(i, 2) <> 100 Then
            If Not dic.exists(tbl(i, 1)) Then
                dic(tbl(i, 1)) = Array(tbl(i, 3), tbl(i, 5), tbl(i, 7))
            Else
                y = dic(tbl(i, 1))
                For n = 3 To 7 Step 2
                    If tbl(i, n) <> "" Then
                        If Not IsError(Application.Match(tbl(i, n), y, 0)) And IsEmpty(x(i, 1)) Then
                            x(i, 1) = 0
                        Else
                            ReDim Preserve y(UBound(y) + 1)
                            y(UBound(y)) = tbl(i, n)
                            dic(tbl(i, 1)) = y
                        End If
                    End If
                Next n
            End If
        End If
    Next i
    Range("i2").Resize(UBound(tbl, 1)) = x
End Sub


 数式を使うなら、J列を作業列にして
 J2に =IF(B2=100,"",J1&C2&E2&G2)
 I2に =IF(B2=100,"",IF(ISERROR(FIND(C2,J1))+ISERROR(FIND(E2,J1))+ISERROR(FIND(G2,J1)),"",0))

 J列は100が出てきたらこれまでの商品名をリセットして、& で結合しながら表示。
 I列は、一つ上の行までに 商品名が有ったかどうかを確認しています。

 同じ様な(?)考え方でマクロを作ると。。。
 '------
Sub 商品CD_商品を登録()
    Dim dic As Object, i As Long, ii As Long
    Dim tbl, x
    Set dic = CreateObject("scripting.dictionary")
    tbl = Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row, 9).Value
    ReDim x(1 To UBound(tbl, 1), 1 To 1)
    x(1, 1) = tbl(1, 9)
    For i = 2 To UBound(tbl, 1)
        If tbl(i, 2) <> 100 Then
            For ii = 3 To UBound(tbl, 2) - 1 Step 2
                If tbl(i, ii) <> "" Then
                    If dic.exists(tbl(i, 1) & "_" & tbl(i, ii)) Then
                        x(i, 1) = 0
                        Exit For
                    End If
                End If
            Next
            For ii = 3 To UBound(tbl, 2) - 1 Step 2
                If tbl(i, ii) <> "" Then
                    dic(tbl(i, 1) & "_" & tbl(i, ii)) = Empty
                End If
            Next
        End If
    Next
    Range("I1").Resize(UBound(tbl, 1)).Value = x
    Set dic = Nothing
End Sub
 '------

 >For ii = 3 To UBound(tbl, 2) - 1 Step 2
 が二回出ていますが、
  上側がI列に入れた式の様な役割・・・・過去に出てきているか確認
  下側がJ列に入れた式の様な役割・・・・次の行の為に過去に出てきた物を登録
 しています。

 (HANA)

 >弥太郎さん
 どうもありがとうございました!
 関数の入れ子(VBAでも『入れ子』というのでしょうか)がまだ習得できていないので
 とても助かりました。辞書を見ながら解読してみます。

 >HANAさん
 以前にもお世話になりましたが、違う方向からの見方がある事を気づかされます。
 数式もバッチリ結果が出ました。本当に助かります。ありがとうございました!

 教えてください。以前、教えていただいた関数を使用してみました。

 J2に =IF(B2=100,"",J1&C2&E2&G2)
 I2に =IF(B2=100,"",IF(ISERROR(FIND(C2,J1))+ISERROR(FIND(E2,J1))+ISERROR(FIND(G2,J1)),"",0))

 この方法で試したみたところ、一番上の表でセルC15は重複データなのですが、E15に○が入った場合には
 重複データと認識しなくなってしまうのです。
 自分の予想は『整数+#VALUE!』という式になり、答えがエラー値になってしまうため
 重複データではないという結果になってしまうからなのかなと思うのです。
 これを回避する方法は無いかと思い質問いたしました。

 VBAで試しましたが、変数や配列の関数(?)が完全には理解できず、
 会社で使用している表に合わせることができませんでした。

 どうぞよろしくお願いいたします。
(ゆき夫)

 >自分の予想は『整数+#VALUE!』という式になり、答えがエラー値になってしまうため
 >重複データではないという結果になってしまうからなのかなと思うのです。

 確かにそうですね。
 実際は、ISERROR関数に入っているので、FALSE + TRUE + FALSE = 1
 になってしまうため、重複データではないという結果になります。

 現在の方法では、FALSE + FALSE + FALSE = 0 になった時だけ
 ・・・3つのセルの値をそれぞれ J列の中で探した時に
    すべてのセルで値が見つかった場合・・・
 重複データと言う事に成っています。

 少し変更して、
 J2に =IF(B2=100,"★",J1&C2&E2&G2)
 I2に =IF(B2=100,"",IF(ISERROR(1/(FIND(C2,J1)-1))*ISERROR(1/(FIND(E2,J1)-1))*ISERROR(1/(FIND(G2,J1)-1)),"",0))
 としてみるとどうでしょう?

 J2用の式の「★」は、C,E,G列に使われない文字にしてもらえれば何でも良いです。

 最初の式では、検索するセルに入力が無かった場合も、検索値が最初にあった場合も
 FIND関数で調べるとどちらも1に成っていたのを
 未入力なのか、検索値があったのか、検索値がなかったのか
 3つに分けることにしてみました。

 一つだけ取り出すと、数式内の
            __________1
 ISERROR(1/(FIND(C2,J1)-1)) の部分です。
            ~~~~~~~~~~~~~2
         ^^^^^^^^^^^^^^^^3

 FIND(C2,J1) ・・・・・・・・・・・・・・・・・・・・★1★
  C2セルに入力が無い場合   1
  C2セルの値がJ1にある場合 2以上
  C2セルの値がJ1にない場合 #VALUE!

 ★1★−1・・・・・・・・・・・・・・・・・・・・・★2★
  C2セルに入力が無い場合   1−1     =0
  C2セルの値がJ1にある場合 2以上−1 =1以上
  C2セルの値がJ1にない場合 #VALUE!−1=#VALUE

 1/★2★・・・・・・・・・・・・・・・・・・・・・★3★
  C2セルに入力が無い場合   1/0     =#DIV/0!
  C2セルの値がJ1にある場合 1/1以上 =数値
  C2セルの値がJ1にない場合 1/#VALUE!=#VALUE

 ISERROR(★3★)
  C2セルに入力が無い場合   ISERROR(#DIV/0!)=TRUE
  C2セルの値がJ1にある場合 ISERROR(数値)   =FALSE
  C2セルの値がJ1にない場合 ISERROR(#VALUE!)=TRUE

 になるので、3つのセルのうちどこかに一つでもFALSEがあったら
 「重複がある」と判断出来る様になりそうです。

 (HANA)

 HANAさんどうもありがとうございました!
 おかげさまで希望どおりの結果が出ました。

 わかりやすい説明付きで本当に助かりました。
 遅い時間にもかかわらずありがとうございました。
 (ゆき夫)

 コードは、↑で載せた物から考えると
 データがA1セルから入っている事にかわりが無い場合

 x(1, 1) = tbl(1, 9)
   結果を書き出す列がI列 = 9列目

 If tbl(i, 2) <> 100 Then
   本支店コードが入っているのがB列 = 2列目

 For ii = 3 To UBound(tbl, 2) - 1 Step 2
   商品が入っているのが C列 = 3列目から
            Step 2 = 2列おき
     UBound(tbl, 2) - 1 の部分は 直接値を入れると
     今回の場合 H列 = 8列目(或いは G列 = 7列目)
           C列から2列セットで3つあるので、H列までと考える。
       ただ、実際にはH列は確認しないので、G列にしても良い。

 Range("I1").Resize(UBound(tbl, 1)).Value = x
   結果を書き出す先頭セルが I1 セル

 ループ処理は2箇所に有るので、列の移動が有っただけなら
 5行の変更になると思います。

 どの位置に有った物が何処へ行ったのか
 置き換えてもらうと動くかもしれません。

 また、こちらのコードの改造なので
[[20100327135924]] 『同じ商品番号同士の金額を比較するVBA』(ゆき夫)
 ↑で載せた物も、基本的には同じです。
 両方のコードを見比べて、どの列がどの列に移動した時に
 何処がどう変わっているか確認してもらって
 同じように変更してみてもらうと良いかもしれません。

 (HANA)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.