[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『並べ替えた先頭ｺｰﾄﾞ群の頭行と終端行の検索』（伊那）
初めての投稿ですが、宜しくお願いします。
A B C D E F 製品名 単価 販売個数 販売日 A-ｶﾒﾗN 85000 10 2006/12/25 B-週刊誌 230 300 2006/12/20 E-TV-P 298000 5 2006/12/15 E-ｴｱｺﾝ-M 118000 20 2006/12/3 C-ｹｰｷ 1800 150 2006/11/23 B-新聞 120 650 2006/11/22 A-ﾚﾝｽﾞN 58000 8 2006/11/12 A-ｶﾒﾗC 98000 25 2006/11/10 A-ｶﾒﾗS 95000 30 2006/11/2 E-TV-S 358000 8 2006/10/25 C-ﾁｮｺﾚｰﾄ 450 250 2006/10/10 C-お茶-C 130 1800 2006/7/30 C-ｺｰﾗ-C 120 350 2006/8/5 D-ﾄﾞﾚｽ-F 25000 6 2006/8/15 D-着物-M 185000 2 2006/7/20 B-文庫本 530 12 2006/6/20 C-缶詰-N 185 378 2006/6/5 E-TV-S 198000 23 2006/6/10 E-ｽﾄｰﾌﾞ-P 39800 170 2006/12/10
この様な感じで毎日ｱｯﾌﾟﾃﾞｰﾄされるﾃﾞｰﾀﾍﾞｰｽの週、月単位での集計作業のVBA化を目指して お勉強中です。
1:売上金額算出(E列) 2:先頭ｺｰﾄﾞ記述(F列) 3:先頭ｺｰﾄﾞ群別並べ替え の順にマクロ化したのが下記のｺｰﾄﾞです。
Sub 製品群別分類()
'************ 売上金額算出(E列）***********
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-2]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E20"), Type:=xlFillDefault
Range("E2:E20").Select
'************ 先頭ｺｰﾄﾞ記述(F列) ***********
Range("F2").Select
ActiveCell.FormulaR1C1 = "=LEFT(C[-5])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F20"), Type:=xlFillDefault
Range("F2:F20").Select
Range("F2").Select
'************ 先頭ｺｰﾄﾞ群別並べ替え ************
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End Sub
この結果が下記の様になっています。
製品名 単価 販売個数 販売日 売上金額 先頭ｺｰﾄﾞ A-ｶﾒﾗN 85000 10 2006/12/25 850000 A A-ﾚﾝｽﾞN 58000 8 2006/11/12 464000 A A-ｶﾒﾗC 98000 25 2006/11/10 2450000 A A-ｶﾒﾗS 95000 30 2006/11/2 2 850000 A B-週刊誌 230 300 2006/12/20 69000 B B-新聞 120 650 2006/11/22 78000 B B-文庫本 530 12 2006/6/20 6360 B C-ｹｰｷ 1800 150 2006/11/23 270000 C C-ﾁｮｺﾚｰﾄ 450 250 2006/10/10 112500 C C-お茶-C 130 1800 2006/7/30 234000 C C-ｺｰﾗ-C 120 350 2006/8/5 42000 C C-缶詰-N 185 378 2006/6/5 69930 C D-ﾄﾞﾚｽ-F 25000 6 2006/8/15 150000 D D-着物-M 185000 2 2006/7/20 370000 D E-TV-P 298000 5 2006/12/15 1490000 E E-ｴｱｺﾝ-M 118000 20 2006/12/3 2360000 E E-TV-S 358000 8 2006/10/25 2864000 E E-TV-S 198000 23 2006/6/10 4554000 E E-ｽﾄｰﾌﾞ-P 39800 170 2006/12/10 6766000 E
先端ｺｰﾄﾞ（例えばＢ群）の最初の行、最後の行を検索してその値を変数として取り扱い、 その群の平均値、最大値、最小値を別ｼｰﾄに記述する方法を考えているのですが、その部分で つまずいています。これが解決すれば万歳なのですが・・・
ちなみに信州さんの投稿を参考にさせて頂きました。信州さんも同じ事を考えているのでは？ [[20060712165352]],[[20061225151826]]
すみません。少し的外れだった見たいで 自己削除しました。 考え直してみます （れいまま）
ぬおおっ、同じ事を考えていたんだ！感激～。お互いに頑張ろーね。伊那さん 信州でした
参考までに、ワークシート関数ですと、このようにするのですが。。 (6UP)
=MATCH("A",F:F,0) ←最初の行
=MATCH("A",F:F,1) ←最後の行
明けましておめでとうございます。今年も宜しくお願いしま～す。 MATCH関数を変数化して記述する方法を試みているのですが、うまくいきません。
Sub 変数テスト() Dim ka As Integer Dim ow As Integer
ka = Match("A", "F:F", 0)
ow = Match("A", "F:F", 1)
MsgBox ka, ow End Sub
記述のどこが間違っているのでしょうか？ 伊那
参考までに。。 (6UP)
Sub Macro1()
Dim KK As Long
Dim OO As Long
Dim AA As String
Do
On Error GoTo XX
AA = InputBox("ALPHABET 1")
KK = Application.Match(AA, Range("F:F"), 0)
OO = Application.Match(AA, Range("F:F"), 1)
MsgBox KK & "," & OO
Loop
XX:
End Sub
すでにあるコードには触れませんが...
Sub test()
Dim dic As Object, a, i As Long, w(), y
Set dic = CreateObject("Scripting.Dictionary)
a = Range("a1").CurrentRegion.Resize(,6).Value
For i = 2 To UBound(a,1)
If Not dic.exists(a(i,6)) Then
ReDim w(1 To 5)
w(1) = a(i,6) : w(2) = a(i,5) : w(3) = a(i,5) : w(4) = a(i,4) : w(5) = 1
dic.add a(i,6), w
Else
w = dic.(a(i,6))
w(2) = w(2) + a(i,5) : w(3) = WorksheetFunction.Max(w(3),a(i,5))
w(4) = WorksheetFunction.Min(w(4), a(i,5)) : w(5) = w(5) + 1
dic(a(i,6)) = w
End If
Next
y = dic.items : Set dic = Nothing : Erase a
With Sheets("sheet2").Range("a1")
.CurrentRegion.ClearContents
.Resize(,4).Value = [{"製品名","平均","最大","最小"}]
For i = 0 To UBound(y)
.Offset(i + 1, 1).Value = y(i)(1) '<--ここから
.Offset(i + 1, 2).Value = y(i)(2) / y(i)(5)
.Offset(i + 1, 3).Value = y(i)(3)
.Offset(i + 1, 4).Value = y(4) '<--ここまで変更
Next
End With
End Sub
(seiya)
6UPさん、SEIYAさん有難うございます。 6UPさんへ・・・Loopから抜け出せず、タスクマネージャでEXCELを強制終了させて抜け出しました。 (-.-;) Loopから抜け出す方法あります？
seiyaさんへ・・・sheet2で下記の様に出ました。 製品名 平均 最大 最小 A 1653500 2850000 E B 51120 78000 E C 145686 270000 E D 260000 370000 E E 3606800 6766000 E
平均、最大、最小が1列ずれているので直そうとしたのですが、どこをいじればいいかわかりません。 もしかしてVBスクリプト手法による記述ですか？私はVBスクリプトはよーわからないのでこれからも教えて下さいね。
>Loopから抜け出す方法あります？ F列にマッチしない文字列、例えば、XXなどを入力すると終了します。 (6UP)
久久の投稿です。桜がボツボツ咲くこの頃ですね。SEIYAさんが作ってくれた配列変数の意味がよーわからないまま ここに至っていますが、何とかしたいのでどなたか解説してくださいませ。m(_ _)m
a, i As Long, w(), y それぞれの変数の意味がよく判らないのです。それとSEIYAさんが作成したコードをｺﾋﾟﾍﾟして
動かしたところ、下記のような画面になってしまいます。
*記述ミス2箇所を修正しました。
①Set dic = CreateObject("Scripting.Dictionary)
↓
Set dic = CreateObject("Scripting.Dictionary")
② w = dic.(a(i,6))
↓
w = dic(a(i,6))
A B C D E 1 製品名 平均 最大 最小 2 A 1653500 2850000 E 3 B 51120 78000 E 4 C 145686 270000 E 5 D 260000 370000 E 6 E 3606800 6766000 E
おーーーっと、見逃していました。すみません！
.Offset(i + 1,1) の ,1 の値を1づつ減じてください。 .Offset(i + 1,0) .Offset(i + 1,1) . . . のように。 (seiya)
D列の最小値が出てこないのですが、どこをいじればいいでしょうか？ 伊那
y(4) が y(i)(4) ですね。 (seiya)
修正したら、うまくいきました。 .Offset(i + 1, 0).Value = y(i)(1) --->製品名 .Offset(i + 1, 1).Value = y(i)(2) / y(i)(5) --->平均 .Offset(i + 1, 2).Value = y(i)(3) --->最大 .Offset(i + 1, 3).Value = y(4) --->最小 と理解していいですね。
その場合のｙ()()はdic.itemsでしょうか？ 伊那
そのとおりです。 変数yに dic.Itemsを格納していますので、 考え方は y(i) -> dic.Item(i) (このような参照はできませんが） 各 item に配列を格納してありますので、必要な要素を 取り出すのに y(i)(1), y(i)(2), y(i)(3)... 1 = 先頭コード 2 = 売上金額累計 3 = 売上金額最大値 4 = 売上金最小値 5 = 出現回数（平均値を割り出すため) のような記述で取り出しています。 (seiya)
有難うございます！ ところでseiya先生、お願いがありますの。
For i = 2 To UBound(a,1)
If Not dic.exists(a(i,6)) Then
ReDim w(1 To 5)
w(1) = a(i,6) : w(2) = a(i,5) : w(3) = a(i,5) : w(4) = a(i,4) : w(5) = 1
dic.add a(i,6), w
Else
w = dic.(a(i,6))
w(2) = w(2) + a(i,5) : w(3) = WorksheetFunction.Max(w(3),a(i,5))
w(4) = WorksheetFunction.Min(w(4), a(i,5)) : w(5) = w(5) + 1
dic(a(i,6)) = w
End If
Next
の解説もしてくださいませんか？少しずつで結構ですので・・・・
①For i = 2 To UBound(a,1) ～ Next のﾙｰﾌﾟのところで i = 2 To UBound(a,1)の部分でiの初値を何を元にして２と設定し、 UBound(a,1) までをﾙｰﾌﾟしているのか？ ②UBound(a,1):ｲﾝﾃﾞｯｸｽ番号の最大値を取得？と思われますが、(a,1)の意味は？
がちんぷんかんぷです。
高速にするために範囲を変数に格納してから処理していますので、 理解がしにくいと思います。
Step debugすることをお勧めします。 VB editorで [表示]-[ローカルウィンドウ]を表示させ コードをクリックしてF8を押すと、一行毎実行しますので、 各変数の変化をローカルウィンドウ内で視認することができます。
a = Range("a1").CurrentRegion.Resize(,6).Value
変数 a に A1:Fx の範囲の値を格納した1Base(最小要素Indexが1)の２次元配列を生成
ローカルウィンドウで a を開いてください a+ の + をクリックすると展開します。 これで、a(i,1), a(i,2)...の正体が判明すると思います。
For i = 1 To UBound(a,1) UBound(a,1)で２次元配列の１次元目（行)の最大値を取得
あとは、Step debugして確認してください。 そして、またわからなかったら質問してください。 (seiya)
ローカルウインドウ、初めて見ました。奥が深いですね～。しばし、ローカルウインドウで確認しながら勉強させて頂きます。 とりあえず、お礼まで 伊那
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.