[[20100327135924]] 『同じ商品番号同士の金額を比較するVBA』(ゆき夫) ページの最後に飛ぶ

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

 

『同じ商品番号同士の金額を比較するVBA』(ゆき夫)

[同じ商品番号同士の金額を比較するVBAを教えていただきたいです]

各商品番号同士の金額を比較し、同じか違うかをVBAでできればと思っています。

OS:XP Excel2007使用です。

自分のレベルはVBAの勉強を始めたばかりでマクロの記録に本を見ながらほんの少しだけ加工できる程度です。

初心者向けに命令にコメントをいただけたらとてもうれしいです。

どうぞよろしくお願いいたします。

A列:商品番号(連続した行に昇順になっています。桁数はすべて必ず同じです。)

B列:各商品番号の親(基準・比較元)となる行には100、子となる行(比較する行)は100以外が入っています。

  (親(100)は必ず各商品番号の先頭行にあります。子は100以外の整数で桁数はバラバラです。)

C列:金額(各商品番号ごとの先頭行(B列が100の行)が基準の金額です。)

D列:結果を出したい列です。他の項目でも区別するために、すでに結果が1となるIF関数が入っているので、

   この作業では親とは違う金額の子の行に"0"を直接入力することを希望します

行   A    B    C     D

1   123   100   1000    1

2   123    5    1050    0

3   123    4    1000    1

4   231   100     90    1

5   231    8     95    0

6   231    6     90    1

7   231    5     92    0

8   312   100    3000    1

9   312    4    3000    1

10   422   100     20    1

11   422    6     20    1

12   422    7     25    0

13   431   100    300    1

14   431    5     315    0

15   431    4     300    1

商品番号(A列)が同じ行を一つの塊とします。

 例)商品番号が123である1〜3行目を一つの塊とする

   1行目はB列が100なので親とする

   2行目の金額(C列)を1行目の金額(C列)と比較し、違うのでD列に0を入力

   3行目の金額(C列)を1行目の金額(C列)と比較し、同じなので何もしない

   商品番号が231である4〜7行目を一つの塊とする

   4行目はB列が100なので親とする

   5行目の金額(C列)を4行目の金額(C列)と比較し、違うのでD列に"0"を入力

   6行目の金額(C列)を4行目の金額(C列)と比較し、同じなので何もしない

   7行目の金額(C列)を4行目の金額(C列)と比較し、違うのでD列に"0"を入力

どうぞよろしくお願い致します。


 Sub test20100327()
     Dim myRng As Range, myDic As Object
     Set myDic = CreateObject("Scripting.Dictionary")
     With Range("A1").CurrentRegion
         For Each myRng In .Resize(, 3)
             If myRng.Offset(0, 1) = 100 Then
                 myDic.Add myRng.Value, myRng.Offset(0, 2).Value
             End If
         Next myRng

         For Each myRng In .Resize(, 1)
             If myRng.Offset(0, 2).Value = myDic.Item(myRng.Value) Then
                 myRng.Offset(0, 3).Value = 1
             Else
                 myRng.Offset(0, 3).Value = 0
             End If
         Next myRng
     End With
 End Sub
 
dictionaryの習作。
処理内容。
最初のfor each - nextループで「B列が100であるA列とC列の値」のマスタ辞書
(マスターリスト)を作ります。
key      item
123	1000
231	90
312	3000
422	20
431	300  内容はこんな感じ。 
次のループで、マスタ辞書と金額が一致するかしないかでD列の値を決めています。
例えば、A列が「231」のセルをチェックしている場合、
myDic.Item(231) = 90 という答えが返ります。
myRng.Offset(0, 2).Value = 90 が成立するかしないかで1,0の結果を分岐して
います。
(みやほりん)(-_∂)b

みやほりんさん、的確な回答をありがとうございました!

一つひとつ動作を確認すると、B列が100の時の値を
見事に辞書に記憶しているかのような動きをしてくれています。

本当に助かりました。
今後とも参考にさせていただきます。

(ゆき夫)


 解決済みの様ですが、せっかく書いたので載せておきます。

 >他の項目でも区別するために、すでに結果が1となるIF関数が入っているので
 他の所は関数で処理をしているのに、今回はVBAを限定なのは何故でしょう?

 作業列を一つ作って数式で処理する案です。
 データが2行目から有る場合の式です。
	[A]	[B]	[C]	[D]	[E]
[1]	商品番号	区分	金額	フラグ	作業列
[2]	123	100	1000	1	1000
[3]	123	5	1050	0	1000
[4]	123	4	1000	1	1000
[5]	231	100	90	1	90
[6]	231	8	95	0	90
[7]	231	6	90	1	90
[8]	231	5	92	0	90
[9]	312	100	3000	1	3000
[10]	312	4	3000	1	3000
[11]	422	100	20	1	20
[12]	422	6	20	1	20
[13]	422	7	25	0	20
[14]	431	100	300	1	300
[15]	431	5	315	0	300
[16]	431	4	300	1	300
 D2セルに
=IF(C2=E2,1,0)
 E2セルに
=IF(B2=100,C2,E1)
 必要行フィルドラッグ。

 「親番号の金額を、次の親が出てくるまで保持しておく」
 と、同じ様な考えで 保持する変数を使ってVBAにすると
 以下のように出来ると思います。

 '------
Sub 親の金額を保持()
Dim i As Long, 親の金額 As Long
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("B" & i).Value = 100 Then
            親の金額 = Range("C" & i).Value
        End If
        If 親の金額 = Range("C" & i).Value Then
            Range("D" & i).Value = 1
        Else
            Range("D" & i).Value = 0
        End If
    Next
End Sub
 '------
 こちらも、ご説明とは違って
 2行目からデータが入っている事を想定したコードです。

 親であるかの確認は、B列が100かどうかでも良いと思いますが
 A列の値が変わったかどうかでも 良いかもしれません。
        If Range("A" & i - 1).Value <> Range("A" & i).Value Then

 (HANA)

HANAさん、どうもありがとうございます!
求めている回答がしっかりと出ました。

A列の値が変わったかどうかという発想は全くありませんでした。

かといって、そのような発想があっても自分の力では解決できませんが
違った見方をするということでも勉強になりました。

本当にありがとうございました。

(ゆき夫)


 何度も申し訳ありません。

 結果はしっかり出たのですが、扱っている元データが約10000行40列ほどに関数が入っており
 "0"が入力されるとその都度で再計算が行なわれるため処理に10分以上かかってしまいます。
 関数で出す方法があれば教えていただければと思っています。

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

 >関数で出す方法
 でしたら、載せてありますが?

 ちなみに、↓の表と同じ表でしょうか?
[[20100329192844]] 『重複データの検索方法を教えていただきたいです』(ゆき夫) 
 でしたら(・・・でなくてもですが・・・)
 毎回セルに結果を書くのではなく
 ↑の様に一回で書き出せば良いと思いますし

 でしたら
 処理自体もまとめて行えばよいと思いますが。

 (HANA)

 大変失礼いたしました。

 表は上の表で重複データを探すのではなく、C列の親子の相違を比較する作業なのです。
 [[20100329192844]] で教えていただいた関数を応用できるのではと試しましたが
 子(B列が100でないもの)の数が毎回違うので、親と子、親と子の比較方法が思い浮かばず
 VBAでは Range("I1").Resize(UBound(tbl, 1)).Value = x
 が一度に書きだせるものだと思うのですが、まだ勉強を始めたばかりで
 どこにどのように使ったらよいのかわからず
 教えていただけないかと思い再度質問させていただきました。

 初歩的な質問だったようで大変申し訳ございません。

 途中でデバッグモードになったり途中終了するコードだと面倒ですが
 簡単に済ませるならこんな方法もあります。

 Application.Calculation = xlCalculationManual '自動計算停止(手動計算モード)

 Application.Calculation = xlCalculationAutomatic '自動計算モード

 (momo)

 私が言っている数式は、[[20100329192844]] の数式の事ではなく
 このスレ内にある

 >>  D2セルに
 >> =IF(C2=E2,1,0)
 >>  E2セルに
 >> =IF(B2=100,C2,E1)
 >>  必要行フィルドラッグ。

 の事ですが、うまくいきませんか?
 或いは、やりたい事が当初と変わってきているのですか?

 >初歩的な質問だったようで
 なぜこの様に思われたのでしょう?

 コードを載せていないのは、同じ表に対する処理だった場合
 レイアウトが分からないからです。
  [[20100329192844]]の表はD列は金額では無いので。
 そして、同じ表に対する処理だった場合 可能であれば
 一つの処理にしてしまうのが良いと思うからです。

 違う表であれば、想定しておられる通り
 >Range("I1").Resize(UBound(tbl, 1)).Value = x
 の様なコードにするだけなのですが。。。

 まずは、どちらなのか教えてください。
 また、最初に乗せておられる表は1行目からデータが入っています。
 1行目が見出しで始まっている場合は、項目を教えてください。

 (HANA)


 説明不足と名無しで、しかも、数式を見落としておりまして重ね重ねお詫びいたします。
 自分が初歩的質問だったのではないかと思ったのは、簡単に説明すればわかると
 お思いになられたのではないかと判断したからです。

 関数ではうまく結果が出ました。ありがとうございます。
 どうしても理解できないのが、
 =IF(B2=100,C2,E1)
 なのですが、親子、親子…を見たいのに、
 なぜ『親でない場合は上の行を見る』となるのでしょうか。

 そして、HANAさんがおっしゃる通り、この表と[[20100329192844]]は同じ表で
 やりたいことは当初と変わりませんが、作業的には分けて処理になるとではないかと思っています。

 	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]	
 1	商品CD	本支店	金額	←結果	仕入先1	他項目	仕入先2	他項目	仕入先3	他項目	←結果
 2	123	100	1000		○		×				
 3	123	5	1050	0	△		×				
 4	123	4	1000		×						0
 5	223	100	90		○		△				
 6	223	8	95	0	○						
 7	223	6	90		×		×		△		
 8	223	5	92	0	○		△				0
 9	323	100	3000		○						
 10	323	4	3000		△		△		○		
 11	423	100	20		○						
 12	423	6	20		○		×		△				
 13	423	7	25	0	△						0
 14	423	100	300		×		○					
 15	523	5	315	0	○		×				
 16	523	4	300		△						

 まずは同じ商品CD同士で本支店区分が100と100以外の金額、100と100以外の金額…と比較します。
 相違があった場合はD列に0を入力するようにします。
 次に同じ商品CD同士で本支店区分が100以外の中で重複確認をして、あればK列に0を入力します。
 本支店区分が100以外の中での重複確認なので各商品CDの1行目と2行目には絶対に0は発生しません。
 結果の列を分けるのは、最終的には0が入る全て物を抽出するのですが、
 金額相違の結果と仕入れ先重複の結果とを更に別々に他の処理に使うからです。

 何度も申し訳ございません。
 (ゆき夫)

 >自分が初歩的質問だったのではないかと思ったのは、簡単に説明すればわかると
 >お思いになられたのではないかと判断したからです。
 いや、違うんですよ。既に書きましたが
 >>ちなみに、↓の表と同じ表でしょうか?
 のご返答待ちだったのです。

 さて、コードの話ですが
 別々に処理をすることになっても
 ループ処理は一回にまとめる事が出来ます。

 ☆の行が、D列を出すための処理
 ★の行が、K列を出すための処理 に成っています。

 [[20100329192844]]のコードの改造なので
 違いを確認してもらえればと思います。

 '------
Sub 結果を表示()
    Dim dic As Object, i As Long, ii As Long
    Dim tbl As Variant, x As Variant, y As Variant
    Set dic = CreateObject("scripting.dictionary")
    tbl = Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row, 11).Value
    ReDim x(1 To UBound(tbl, 1), 1 To 1)            '☆D列用
    ReDim y(1 To UBound(tbl, 1), 1 To 1)                '★K列用
    x(1, 1) = tbl(1, 4)                             '☆
    y(1, 1) = tbl(1, 11)                                '★
    For i = 2 To UBound(tbl, 1)
        If tbl(i, 2) <> 100 Then
            If dic(tbl(i, 1)) <> tbl(i, 3) Then     '☆
                x(i, 1) = 0                         '☆
            End If                                  '☆
            For ii = 5 To UBound(tbl, 2) - 1 Step 2     '★〜Next ii
                If tbl(i, ii) <> "" Then
                    If dic.exists(tbl(i, 1) & "_" & tbl(i, ii)) Then
                        y(i, 1) = 0
                        Exit For
                    End If
                End If
            Next ii
            For ii = 5 To UBound(tbl, 2) - 1 Step 2     '★〜Next ii
                If tbl(i, ii) <> "" Then
                    dic(tbl(i, 1) & "_" & tbl(i, ii)) = Empty
                End If
            Next ii
        Else                                        '☆
            dic(tbl(i, 1)) = tbl(i, 3)              '☆
        End If
    Next i
  Application.Calculation = xlCalculationManual
    Range("D1").Resize(UBound(tbl, 1)).Value = x    '☆
    Range("K1").Resize(UBound(tbl, 1)).Value = y        '★
  Application.Calculation = xlCalculationAutomatic
    Set dic = Nothing
End Sub
 '------

 それと、D15,D16は 親番号が無いので
 ご提示の結果と異なる結果に成っています。

 >=IF(B2=100,C2,E1)
 >なぜ『親でない場合は上の行を見る』となるのでしょうか。
 E列の結果を見てください。

 親の場合は、同じ行のC列の金額(親の金額)をE列に入れます。
 子の場合は、対応する親の金額が何行上のC列にあるか分かりませんが
 E列の一つ上のセルには、必ず 親の金額が入っています。

 なぜなら、ここの金額は 親の場合しか変わらないので。
 そして、前提条件として子よりも親が先に来るので。
  ・・・というか、それを意図して、E列の式を作っています。
     親の金額をC列から探さなくても良い様に。

 ((親の金額を保持))のコードで、親の金額を変数に入れていますが
 この変数と同じ役割をしているのがE列という事になります。
 親が出て来た時だけ、値が変わりますよね?

 ちなみに・・・

 最初にみやほりんさんが載せておられるコードは
 親の番号がどこにあっても問題なくご希望の結果を導けます。

 私が載せたものは、数式もコードも
 子よりも前に親が無いと希望する結果には成りません。
  (数式の場合はさらに、グループ毎に成っている必要があります。)

 (HANA)


 HANAさん、ご丁寧にご説明ありがとうございました。

 =IF(B2=100,C2,E1)
 の意味はやっと理解ができました。確かに親の場合しか変わらないです。

 コードに関してはまだまだ解読に時間がかかりそうですが
 読めるように努力します。

 本当にありがとうございました。
 (ゆき夫)

コメント返信:

[ 一覧(最新更新順) ]


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