Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4050 / 13644 ツリー ←次へ | 前へ→

【58651】同じ文字列の検索 板さん 08/11/2(日) 23:32 質問[未読]
【58652】Re:同じ文字列の検索 kanabun 08/11/2(日) 23:42 発言[未読]
【58653】Re:同じ文字列の検索 板さん 08/11/3(月) 7:25 質問[未読]
【58657】Re:同じ文字列の検索 kanabun 08/11/3(月) 11:36 発言[未読]
【58658】Re:同じ文字列の検索 kanabun 08/11/3(月) 15:45 発言[未読]
【58660】Re:同じ文字列の検索 板さん 08/11/3(月) 18:14 お礼[未読]
【58661】Re:同じ文字列の検索 kanabun 08/11/3(月) 18:20 発言[未読]
【58662】Re:同じ文字列の検索 kanabun 08/11/3(月) 18:35 発言[未読]
【58663】Re:同じ文字列の検索 kanabun 08/11/3(月) 22:33 発言[未読]
【58664】Re:同じ文字列の検索 板さん 08/11/4(火) 0:14 お礼[未読]

【58651】同じ文字列の検索
質問  板さん E-MAIL  - 08/11/2(日) 23:32 -

引用なし
パスワード
   あるデータファイルがあります。
A列に文字列、B列にその文字列の数値
※例
[A列] [B列]
「 12345」「15」
「 012345a」「 1」
「 987a654」「11」
「1987a65400」「 3」


集計結果
「 12345」「15」
「 12345a」「 1」
合計 16
「 987a654」「11」
「 987a65400」「 3」
合計 14

「集計結果」というシートを作成しそのシートに集計結果を貼り付けしたい
集計したいデータ量はn個です
オートフィルタでn個の行まで同じ数値を含む文字列を検索したい
(オートフィルタでなくても他に方法があれば教えてください)
A列はの書式は「標準」で右寄せしたい
文字列が左詰めになっているものは頭の数字を削除して右詰めにしたい
(左詰めになっているものは先頭文字が0か1の場合)
同じ数値を含む文字列ごとにB列の数値を合計したい


というVBAを作成したいのです。
文章にするとわかりにくいところがあるかもしれませんがよろしくお願いします。

【58652】Re:同じ文字列の検索
発言  kanabun  - 08/11/2(日) 23:42 -

引用なし
パスワード
   ▼板さん さん:

こんばんは。

>オートフィルタでn個の行まで同じ数値を含む文字列を検索したい

ということですが、その「同じ数値を含む文字列」 ということを
プログラムにどういうルールで判定させますか?


> [A列]     [B列]
>「 12345」   「15」
>「 012345a」  「 1」
>「 987a654」  「11」
>「1987a65400」 「 3」

につづいて、
  0123
  345a987
  87a65

というデータがあったら、これらはどう判定しますか?

【58653】Re:同じ文字列の検索
質問  板さん E-MAIL  - 08/11/3(月) 7:25 -

引用なし
パスワード
   ▼kanabun さん:
>▼板さん さん:
>
>こんばんは。
>
>>オートフィルタでn個の行まで同じ数値を含む文字列を検索したい
>
>ということですが、その「同じ数値を含む文字列」 ということを
>プログラムにどういうルールで判定させますか?
>
>
>> [A列]     [B列]
>>「 12345」   「15」
>>「 012345a」  「 1」
>>「 987a654」  「11」
>>「1987a65400」 「 3」
>
>につづいて、
>  0123
>  345a987
>  87a65
>
>というデータがあったら、これらはどう判定しますか?

返信ありがとうございます
説明の補足ですが取得したい文字列は5〜10桁とします
つまり
>  0123   は文字列データとしてはありませんので考える必要なしです
>  345a987  は345a987です
>  87a65   は87a65です

>> [A列]   [B列]
「  23456」「10」  
「023456 a」「32」
「023456aa」 「153」

というデータがあれば同じものと判定します
左詰めとなっているデータには必ず0か1が頭に来ます
結果として
「  23456」 「10」
「  23456aa」「32」
「  23456a」「153」
   合計  「195」 としたいのです

こんな説明で申し訳ありません。不明点あればまたお願いします

【58657】Re:同じ文字列の検索
発言  kanabun  - 08/11/3(月) 11:36 -

引用なし
パスワード
   ▼板さん さん:

何か非常に面白い問題だけれど、同時に、
非常に難しい問題ですね(-_-)

手始めに、A列のデータ(コード)について、どうグループ化するかを
VBA風に考えてみます。

A列データを1次元配列に格納し、
検索条件として
(1)A列のコードが文字列で、かつ、先頭1文字が「0か1」のばあい
  先頭1文字を取り除き、
(2)文字数が5〜7のとき、
という条件で、配列全体からこれを含むコードを
 VBA.Filter関数で抽出してみますと、

Sub Try1()
  Dim ColA As Range
  Dim v, w
  Dim ss As String
  Dim i As Long
  
  Set ColA = Range("A2", Cells(Rows.Count, 1).End(xlUp))
  v = Application.Transpose(ColA)  '------- A列を配列に
  For i = 1 To UBound(v)
    ss = v(i)
    If Not IsNumeric(ss) Then
      If InStr("01", Left$(ss, 1)) Then ss = Mid$(ss, 2)
    End If
    Select Case Len(ss)
      Case 5 To 7
        w = Filter(v, ss)
        If IsArray(w) Then
          Debug.Print "[" & ss & "]", Join(w, ",")
        End If
    End Select
  Next
  
End Sub

結果、こうなります。
--------------------------------------
'[12345]    12345,012345a
'[12345a]   012345a
'[987a654]   987a654,1987a65400
'[23456]    23456,23456aa,23456a
'[23456aa]   23456aa
'[23456a]   23456aa,23456a


'重複しているコードをグループ化する
Sub Try2()
  Dim ColA As Range
  Dim v, w, key
  Dim ss As String, sss As String
  Dim i As Long, j As Long, k As Long, ok As Long
  
  Set ColA = Range("A2", Cells(Rows.Count, 1).End(xlUp))
  v = Application.Transpose(ColA)
  For i = 1 To UBound(v)
    ss = v(i)
    If Not IsNumeric(ss) Then
      If InStr("01", Left$(ss, 1)) Then ss = Mid$(ss, 2)
    End If
    Select Case Len(ss)
      Case 5 To 7
        ok = 1
        For j = 5 To Len(ss)
          If InStr(sss, Left$(ss, j)) Then ok = 0: Exit For
        Next
        If ok Then sss = sss & "|" & ss
    End Select
  Next i
  For Each key In Split(Mid$(sss, 2), "|")
    w = Filter(v, key)
    Debug.Print "[" & key & "]", Join(w, ",")
  Next
  
End Sub

すると、このサンプルでは 結果はこうなります。
-----------------------------------
'[12345]    12345,012345a
'[987a654]   987a654,1987a65400
'[23456]    23456,23456aa,23456a

しかしこれは keyとなるコードが文字列数の少ないほうから
出現していたからです

つぎのような並び順の場合、
---------------------------
 コード
 1987a65400
 987a654
 23456aa
 23456a
 12345a
 23456
 12345

結果はこうなります。

------------------------------------
[987a654]   1987a65400,987a654
[23456aa]   23456aa
[12345a]   012345a

'これは失敗です。
検索コードグループ文字列は 最短一致するkeyに置き換えねばなりません。

Sub Try3()
  Dim ColA As Range
  Dim v, w, key
  Dim s As String, ss As String, sss As String
  Dim i As Long, k As Long
  
  Set ColA = Range("A2", Cells(Rows.Count, 1).End(xlUp))
  v = Application.Transpose(ColA)
  For i = 1 To UBound(v)
    ss = v(i)
    If Not IsNumeric(ss) Then
      If InStr("01", Left$(ss, 1)) Then ss = Mid$(ss, 2)
    End If
    Select Case Len(ss)
      Case 5 To 7
        Distribute sss, ss
    End Select
  Next i
  For Each key In Split(Left$(sss, Len(sss) - 1), "|")
    w = Filter(v, key)
    Debug.Print "[" & key & "]", Join(w, ",")
  Next
  
End Sub

Private Sub Distribute(sss$, ss$)
  Dim ok%, j&, j1&, j2&, s$, zz$
  ok = 1
  For j = 5 To Len(ss)
    s = Left$(ss, j)
    j1 = InStr(sss, s)
    If j1 Then
      j2 = InStr(j1 + Len(s), sss, "|")
      zz = Mid$(sss, j1, j2 - j1)
      sss = Replace(sss, zz, s)
      ok = 0
      Exit For
    End If
  Next
  If ok Then sss = sss & ss & "|"
End Sub

これを同じデータで実行すると、こうなります。
-------------------------------------
'[987a654]   1987a65400,987a654
'[23456]    23456aa,23456a,23456
'[12345]    012345a,12345

ここまでの思考過程を把握するには、提示のような簡単なデータをシートに
おいて、ステップ実行してみて、1行づつ実行しながら、変数が変わっていく
状態をトレースするといいです。

分からない点がございましたら、レスください。

【58658】Re:同じ文字列の検索
発言  kanabun  - 08/11/3(月) 15:45 -

引用なし
パスワード
   さて、話が「グループ別集計作業」に戻りますが、
グループ別に集計するには Dictioanryオブジェクト
というものを使うといいです。

いま、以下のようなテーブルがあったとします。
'---------------
 A     B
'コード   数量
'123     10
'456     10
'123     10
'789     10
'123     10

Sub Try_Dic()
  Dim ColA As Range
  Dim v
  Dim ss As String
  Dim i As Long
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set ColA = Range("A2", Cells(Rows.Count, 1).End(xlUp))
  v = ColA.Resize(, 2).Value 'A,B列のデータ
  For i = 1 To UBound(v)
    ss = v(i, 1)  '同じKey(コード)に Item(数量)を加えていく
    dic(ss) = dic(ss) + v(i, 2)
  Next

  '集計結果を新しいシートに書き出す
  With Worksheets.Add(After:=ActiveSheet)
    .Range("A2").Resize(dic.Count, 2).Value = _
      Application.Transpose(Array(dic.Keys, dic.Items))
  End With
 
End Sub

' ------------ 結果はこうなります
'123  30
'456  10
'789  10


キー(グループ名)がはじめから分かっているばあい、
キー別集計作業は 上のように Dictionaryを使うと簡単にできます。

【58660】Re:同じ文字列の検索
お礼  板さん E-MAIL  - 08/11/3(月) 18:14 -

引用なし
パスワード
   ▼kanabun さん:
>さて、話が「グループ別集計作業」に戻りますが、
>グループ別に集計するには Dictioanryオブジェクト
>というものを使うといいです。
>
>いま、以下のようなテーブルがあったとします。
>'---------------
> A     B
>'コード   数量
>'123     10
>'456     10
>'123     10
>'789     10
>'123     10
>
>Sub Try_Dic()
>  Dim ColA As Range
>  Dim v
>  Dim ss As String
>  Dim i As Long
>  Dim dic As Object
>  
>  Set dic = CreateObject("Scripting.Dictionary")
>  Set ColA = Range("A2", Cells(Rows.Count, 1).End(xlUp))
>  v = ColA.Resize(, 2).Value 'A,B列のデータ
>  For i = 1 To UBound(v)
>    ss = v(i, 1)  '同じKey(コード)に Item(数量)を加えていく
>    dic(ss) = dic(ss) + v(i, 2)
>  Next
>
>  '集計結果を新しいシートに書き出す
>  With Worksheets.Add(After:=ActiveSheet)
>    .Range("A2").Resize(dic.Count, 2).Value = _
>      Application.Transpose(Array(dic.Keys, dic.Items))
>  End With
> 
>End Sub
>
>' ------------ 結果はこうなります
>'123  30
>'456  10
>'789  10
>
>
>キー(グループ名)がはじめから分かっているばあい、
>キー別集計作業は 上のように Dictionaryを使うと簡単にできます。


ありがとうございました。
あまりにレベルが高すぎて(質問した私のレベルが低いのがいけないのですが)
もう一度じっくり考え直してひとつずつ質問していきたいと思います

【58661】Re:同じ文字列の検索
発言  kanabun  - 08/11/3(月) 18:20 -

引用なし
パスワード
   ▼板さん さん:
こんにちは。

>ありがとうございました。
>あまりにレベルが高すぎて(質問した私のレベルが低いのがいけないのですが)
>もう一度じっくり考え直してひとつずつ質問していきたいと思います

そうですね。
これまでの説明がかなり端折ってしまっているので、
順番に分からない点を質問してください。

なお、当初、テストに使った簡単な表は 以下のようなものです。

-----------------------------------
コード    数量
12345    15
012345a    1
987a654    11
1987a65400    3
23456    10
23456aa    32
23456a    153

【58662】Re:同じ文字列の検索
発言  kanabun  - 08/11/3(月) 18:35 -

引用なし
パスワード
   なお、
>ステップ実行
の基本については、たとえば

ht tp://hp.vector.co.jp/authors/VA016119/step/step01.html

を参照ください。
Step実行するときは、画面に ワークシートとVBEコード画面が両方みえる
ようにしてコードをトレース実行していくと、理解しやすいですよ

【58663】Re:同じ文字列の検索
発言  kanabun  - 08/11/3(月) 22:33 -

引用なし
パスワード
   なんだかマルチポストをされると、回答する気が失せますね

Sub Try3() と Sub Try_Dic()のグループ別集計作業を
ドッキングさせたものを投稿して、ぼくの発言はこれにて
終了させていただきます。


Sub Try4c()
  Dim WS1 As Worksheet
  Dim ColA As Range, c As Range
  Dim v, u, w, key
  Dim ss As String, sss As String
  Dim i As Long, ii As Long, j As Long, n As Long
  Dim total As Double
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set WS1 = Worksheets("Anoth") '------- 集計元データシート
  Set ColA = WS1.Range("A2", WS1.Cells(Rows.Count, 1).End(xlUp))
  WS1.Columns("C:D").Insert
  With ColA.Columns(3)
    .Formula = "=ROW()"
    .Value = .Value
  End With
  ColA.Columns(4).FormulaR1C1 = "=LEN(RC[-3])"
  With ColA.Resize(, 4)
    .Sort Key1:=.Columns(4), Header:=xlNo
    v = Application.Transpose(.Columns(1))
    u = Application.Transpose(.Columns(2))
    .Sort Key1:=.Columns(3), Header:=xlNo
  End With
  WS1.Columns("C:D").Delete
  
  For i = 1 To UBound(v)
    ss = v(i)
    If dic.Exists(ss) Then
      Do
        ss = ss & " "
      Loop While dic.Exists(ss)
      v(i) = ss
    End If
    dic(ss) = u(i)
    If Not IsNumeric(ss) Then
      If InStr("01", Left$(ss, 1)) Then ss = Mid$(ss, 2)
    End If
    
    If Len(ss) > 10 Then ss = Left$(ss, 10)
    Distribute sss, ss
  Next
  
  u = Split(Left$(sss, Len(sss) - 1), "|")
  n = dic.Count + UBound(u) + 1
  ReDim tbl(n, 1 To 3)
  i = 0
  tbl(i, 1) = "種別"
  tbl(i, 2) = "コード"
  tbl(i, 3) = "数量"
  For Each key In u
    w = Filter(v, key)
    total = 0
    ii = 0
    For j = 0 To UBound(w)
      If dic(w(j)) > 0 Then
        i = i + 1: ii = ii + 1
        tbl(i, 2) = RTrim$(w(j))
        tbl(i, 3) = dic(w(j))
        total = total + dic(w(j))
        dic(w(j)) = 0
      End If
    Next
    If ii Then
      i = i + 1
      tbl(i, 1) = key
      tbl(i, 2) = "合計"
      tbl(i, 3) = total
    End If
  Next
  With Worksheets.Add(After:=WS1)
    With .Range("A1").Resize(n + 1, 3)
      .Value = tbl
      .Columns(3).NumberFormat = "#,##0"
      For Each c In .Columns(1).SpecialCells( _
              xlConstants, xlTextValues Or xlNumbers)
        c.Resize(, 3).Interior.ColorIndex = 34
      Next
    End With
    .Columns("A:C").AutoFit
  End With
End Sub


マルチポスト先のデータにも対応できるようにしてあります。

A列が邪魔でしたら、削除するコードを付け加えてください。

 A     B       C
種別    コード      数量
     636233030     21
     1636233030 90    4
63623   合計        25
     4561410021     10
     4561410021  400,000
     04561410021G0   15
     04561410021G0   53
45614   合計     400,078
     4166110010   38,000
     04166110010D6  4,000
4166110010 合計     42,000
     1175955026    100
     1175955026     12
11759   合計       112
     04517610060G0   11
4517610060 合計       11

【58664】Re:同じ文字列の検索
お礼  板さん  - 08/11/4(火) 0:14 -

引用なし
パスワード
   申し訳ありませんでした。
全然そんなつもりはなかったのですが
気分を悪くされたこと誤ります。

4050 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free