goo blog サービス終了のお知らせ 

半角チルダ

ExcelVBA、その他。
覚え書きや、補足資料などのスクラップブック。
end-u(1037781)

■ExcelVBAで複数条件で抽出して表を作成

2014-02-03 19:00:00 | scrap
ExcelVBAで複数条件で抽出して表を作成 - 教えて!goo
一応、次のステップ書いておいたんだけど〆られたのでscrapいき。 :)

Option Explicit

Sub pre() 'とりあえずサンプルデータBookを作ってみる
  Const x = 10
  Const y = 10000

  With Workbooks.Add.Sheets(1)
    .Range("A1:D1").Value = [{"広域","地域","性別","年代"}]
    .Range("A2").Resize(y).Formula = "=INT(B2/100)"
    .Range("B2").Resize(y).Formula = "=INT(RAND()*1000)+100"
    .Range("C2").Resize(y).Formula = "=INT(RAND()*2)+1"
    .Range("D2").Resize(y).Formula = "=INT(RAND()*13)*10/2+40"
    .Range("E1").Resize(, x).Formula = "=COLUMN(A1)"
    .Range("E2").Resize(y, x).Formula = "=INT(RAND()*10)"
    .Range("A1").CurrentRegion.Copy
    .Range("A1").PasteSpecial xlPasteValues
  End With
  Application.CutCopyMode = False
End Sub

(pre実行後)


'新規にできたBook.Sheet1がActiveになっている事を確認してそのまま下のコードを実行。

Sub try() 'サンプルデータBookをActiveにして実行
  Dim w As Worksheet
  Dim r As Range
  Dim p As PivotTable
  Dim s As String
  Dim i As Long

  Application.ScreenUpdating = False
  With ActiveWorkbook
    Set r = .ActiveSheet.Range("A1").CurrentRegion
    Set p = .PivotCaches.Add(SourceType:=xlDatabase, _
                 SourceData:=r).CreatePivotTable("")
  End With
  p.AddFields RowFields:=Array("地域", "性別", "年代"), _
        ColumnFields:="data", _
        PageFields:="広域"
  For i = 5 To r.Columns.Count
    p.AddDataField p.PivotFields(i), , xlSum
    s = s & "+'" & p.PivotFields(i).Name & "'"
  Next
  p.CalculatedFields.Add("k", "=" & Mid(s, 2)).Orientation = xlDataField
  p.PivotFields("年代").ShowAllItems = True
  With p.PivotFields("性別")
    .PivotItems("1").Caption = "男性"
    .PivotItems("2").Caption = "女性"
    .ShowAllItems = True
  End With
  p.NullString = "0"

  'Pivotを新規Bookへ移動
  ActiveSheet.Move
  Set p = ActiveSheet.PivotTables(1)

  'Pagesを各Sheetに展開
  p.ShowPages PageField:="広域"
  For Each w In ActiveWorkbook.Worksheets
    For Each p In w.PivotTables
      Set r = Intersect(p.RowRange, w.Columns("A:B"))
      'Pivot解除
      With p.TableRange2
        .Copy
        .PasteSpecial xlPasteValues
        .ClearFormats
      End With
      r.SpecialCells(xlCellTypeBlanks) _
       .FormulaR1C1 = "=if(right(r[-1]c,1)=""計"","""",r[-1]c)"
      r.Value = r.Value
    Next
  Next
  Application.ScreenUpdating = True
End Sub

(try実行後)


'要は、面倒な作業をExcelの基本機能に任せて
'仕上げとしてVBAで加工、体裁を整えるようにしたらいかがでしょう、という案でした。
Comment
  • X
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする

■XPエクスプローラ風「数値順」ソート

2013-03-29 22:00:00 | scrap
# ちと古い話題ですが

ファイル名「1.jpg ~10.jpg~」のソート
ここで教えて頂きました。:)

しばらく前のmoug.net給湯室でも出てたお題です。
「数値混じり文字列を文字部分と数値部分に分けてソートしたい」

Sub test1()
  Dim i  As Long
  Dim j  As Long
  Dim mx As Long
  Dim tmp As String
  Dim ary

  ary = VBA.Array("X10Y1", "X10Y10", "X10Y2", "X1Y1", _
          "X1Y10", "X1Y2", "X2Y1", "X2Y10", "X2Y2")
  mx = UBound(ary)
  For i = 0 To mx - 1
    For j = i + 1 To mx
      If ary(j) < ary(i) Then
        tmp = ary(i)
        ary(i) = ary(j)
        ary(j) = tmp
      End If
    Next
  Next
  Debug.Print Join(ary, vbLf)
End Sub

普通にSortすると

 X10Y1
 X10Y10
 X10Y2
 X1Y1
 X1Y10
 X1Y2
 X2Y1
 X2Y10
 X2Y2

こうなので

Option Explicit

Private Declare Function StrCmpLogicalW Lib "SHLWAPI.DLL" ( _
                    ByVal lpStr1 As Long, _
                    ByVal lpStr2 As Long) As Long

Sub test2()
  Dim i  As Long
  Dim j  As Long
  Dim mx As Long
  Dim tmp As String
  Dim ary

  ary = VBA.Array("X10Y1", "X10Y10", "X10Y2", "X1Y1", _
          "X1Y10", "X1Y2", "X2Y1", "X2Y10", "X2Y2")
  mx = UBound(ary)
  For i = 0 To mx - 1
    For j = i + 1 To mx
      If StrCmpLogicalW(StrPtr(ary(j)), _
               StrPtr(ary(i))) < 0 Then
        tmp = ary(i)
        ary(i) = ary(j)
        ary(j) = tmp
      End If
    Next
  Next
  Debug.Print Join(ary, vbLf)
End Sub

結果。

 X1Y1
 X1Y2
 X1Y10
 X2Y1
 X2Y2
 X2Y10
 X10Y1
 X10Y2
 X10Y10

参考:
『ファイル名の表示順序を変更する』
http://www.atmarkit.co.jp/fwin2k/win2ktips/342xpsort/xpsort.html
『StrCmpLogicalW function』
http://msdn.microsoft.com/en-us/library/bb759947%28VS.85%29.aspx
Comment
  • X
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする

■非再帰QuickSort(カンニング編

2012-10-24 20:00:00 | scrap
と、いう事でここは先人の経験と技術を素直に学ばせていただこう。
#決してカンニングではない..と思う(多分 X)

残念ながら出典サイトはなくなってしまいました。
確か「Excel VBA 実用サンプルコレクション」著者 渡辺ひかる氏のサイト(www.vbasekai.com)に、
以前mougでよく回答してらっしゃった工藤氏のコードが載ってたと思う。
それを参考にさせて頂きました。感謝。

Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private QS() As String    'ソートデータ用
Private QL() As Long     'ソートインデックス用
'-------------------------------------------------
Private Sub test()      'テストプロシージャ選択用
  Const cnt As Long = 65536 'テストデータ量
  Dim ret() As String    '書出し用配列
  Dim t(1) As Long
  Dim i   As Long
  Dim p   As Long
  Dim v

  ReDim QS(1 To cnt)
  ReDim QL(1 To cnt)
  ReDim ret(1 To cnt, 0)

  For Each v In Range("A1").Resize(cnt).Value
    i = i + 1
    QL(i) = i
    QS(i) = v
  Next

  p = 1
  t(0) = timeGetTime
  Select Case p
  Case 1
    Call LQuickSort(1, cnt)
  Case 2
    Call LQuickSortS(1, cnt)
  Case 3
    Call LQuickLoop4
  End Select

  For i = 1 To cnt
    ret(i, 0) = QS(QL(i))
  Next

  t(1) = timeGetTime
  'Sheets.Add.Range("A1").Resize(cnt).Value = ret
  Erase QS, QL, ret
  Debug.Print cnt, t(1) - t(0)
End Sub
'-------------------------------------------------
Private Sub LQuickSort(n As Long, x As Long) '再帰
  Dim tmp As String
  Dim i  As Long
  Dim j  As Long
  Dim k  As Long

  i = n
  j = x
  tmp = QS(QL((i + j) ¥ 2))

  Do
    Do While QS(QL(i)) < tmp
      i = i + 1
    Loop
    Do While tmp < QS(QL(j))
      j = j - 1
    Loop

    If j <= i Then
      Exit Do
    End If
    k = QL(i)
    QL(i) = QL(j)
    QL(j) = k
    i = i + 1
    j = j - 1
  Loop

  If n < (i - 1) Then
    LQuickSort n, i - 1
  End If
  If (j + 1) < x Then
    LQuickSort j + 1, x
  End If
End Sub
'-------------------------------------------------
Private Sub LQuickSortS(n As Long, x As Long) '再帰
  Dim tmp As String
  Dim i  As Long
  Dim j  As Long
  Dim k  As Long

  tmp = QS(QL((n + x) ¥ 2))
  i = n - 1
  j = x + 1

  Do
    Do
      i = i + 1
    Loop While QS(QL(i)) < tmp
    Do
      j = j - 1
    Loop While tmp < QS(QL(j))

    If j <= i Then
      Exit Do
    End If
    k = QL(i)
    QL(i) = QL(j)
    QL(j) = k
  Loop

  If n < (i - 1) Then
    LQuickSortS n, i - 1
  End If
  If (j + 1) < x Then
    LQuickSortS j + 1, x
  End If
End Sub
'-------------------------------------------------
Private Sub LQuickLoop4()   '非再帰
  Const p   As Long = 100 '記憶用配列サイズ
  Dim idxi(p) As Long    'Index記憶用
  Dim idxj(p) As Long    'Index記憶用
  Dim Lv   As Long    '深さレベル
  Dim mn   As Long
  Dim mx   As Long
  Dim i    As Long
  Dim j    As Long
  Dim k    As Long
  Dim tmp   As String

  Lv = 1
  idxi(Lv) = LBound(QL)
  idxj(Lv) = UBound(QL)

  Do While Lv > 0
    mn = idxi(Lv)
    mx = idxj(Lv)
    Lv = Lv - 1
    tmp = QS(QL((mn + mx) ¥ 2))
    i = mn - 1
    j = mx + 1

    Do
      Do
        i = i + 1
      Loop While QS(QL(i)) < tmp
      Do
        j = j - 1
      Loop While tmp < QS(QL(j))
      If j <= i Then
        Exit Do
      End If
      k = QL(i)
      QL(i) = QL(j)
      QL(j) = k
    Loop

    If mn < (i - 1) Then
      Lv = Lv + 1
      idxi(Lv) = mn
      idxj(Lv) = i - 1
    End If
    If (j + 1) < mx Then
      Lv = Lv + 1
      idxi(Lv) = j + 1
      idxj(Lv) = mx
    End If
  Loop
End Sub

[LQuickSort]
65536     329
65536     329
65536     328

[LQuickSortS]
65536     312
65536     297
65536     297

[LQuickLoop4]
65536     312
65536     297
65536     314

ver2010。
[LQuickSort]
1000000    6907
1000000    7032
1000000    7017

[LQuickSortS]
1000000    6766
1000000    6800
1000000    6781

[LQuickLoop4]
1000000    6719
1000000    6767
1000000    6735

再帰版と同等の速さです。しかも簡潔。
達人の方が書くとこうも違うものなのですね..
他にも色々なサイトで非再帰版のコードを見て勉強させて頂きましたが、
[LQuickLoop4]の元になった工藤氏のコードが一番勉強になりました。有難うございます。

また、再帰版も再掲してますが、[LQuickSortS]は今回Loop版に合わせて比較の意味で載せました。
以前はこっち使ってたんですが、いつの間にか変わってました。[LQuickSort]のコードの方が馴染みやすいのかな..
#師匠の教えもあってちょっと修正してますが :)
それにしても、ちょっとしたコーディングの違いで差が出るもんなんですね-

あとは余談。
コードを見てもらうとわかる事なので今さらですが、今回の配列ソートは配列そのものを入れ替えずに
ソートインデックス用のLong型配列を作ってこれを入れ替えてます。
これは、セル範囲を配列に取得したVariant型2次元配列をそのままQuickSortする場合と比較して、
ソートデータ用のString型1次元配列、ソートインデックス用Long型1次元配列、
それに書出し用2次元配列にLoop処理で格納し直す処理を入れても、より高速に処理できます。
Comment
  • X
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする

■非再帰QuickSort(反省編

2012-10-23 20:00:00 | scrap
>結果。やっぱ再帰のほうが記述が簡単で速い。
ぃゃ、私が書くと..って事なのだが。
>#ぃゃもっと効率良く書けるのかもしれないが..力ないです..orz
とは言うものの、少し足掻いてみた。

>A列に6桁ランダム数値を全角文字列にしたデータでテスト。重複あり。
Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private QS() As String    'ソートデータ用
Private QL() As Long     'ソートインデックス用
'-------------------------------------------------
Private Sub test()      'テストプロシージャ選択用
  Const cnt As Long = 65536 'テストデータ量
  Dim ret() As String    '書出し用配列
  Dim t(1) As Long
  Dim i   As Long
  Dim v

  ReDim QS(1 To cnt)
  ReDim QL(1 To cnt)
  ReDim ret(1 To cnt, 0)

  For Each v In Range("A1").Resize(cnt).Value
    i = i + 1
    QL(i) = i
    QS(i) = v
  Next

  t(0) = timeGetTime

  Call LQuickLoop3

  For i = 1 To cnt
    ret(i, 0) = QS(QL(i))
  Next

  t(1) = timeGetTime

  'Sheets.Add.Range("A1").Resize(cnt).Value = ret
  Erase QS, QL, ret
  Debug.Print cnt, t(1) - t(0)
End Sub
'-------------------------------------------------
Private Sub LQuickLoop3()   '非再帰
  Const p   As Long = 100 '記憶用配列サイズ
  Dim flg0(p) As Boolean  'Lo処理記憶用
  Dim flg1(p) As Boolean  'Up処理記憶用
  Dim idxi(p) As Long    'Index記憶用
  Dim idxj(p) As Long    'Index記憶用
  Dim jj(p)  As Long    'Loopカウンタ記憶用
  Dim Lv   As Long    '深さレベル
  Dim i    As Long
  Dim j    As Long
  Dim k    As Long
  Dim tmp   As String

  Lv = 1
  idxi(Lv) = LBound(QL)
  idxj(Lv) = UBound(QL)

  Do While Lv > 0

    Do
      If flg0(Lv) Then Exit Do
      flg0(Lv) = True
      i = idxi(Lv)
      j = idxj(Lv)
      tmp = QS(QL((i + j) ¥ 2))

      Do
        Do While tmp > QS(QL(i))
          i = i + 1
        Loop
        Do While tmp < QS(QL(j))
          j = j - 1
        Loop
        If i >= j Then
          Exit Do
        End If
        k = QL(i)
        QL(i) = QL(j)
        QL(j) = k
        i = i + 1
        j = j - 1
      Loop

      jj(Lv) = j
      If (i - 1) <= idxi(Lv) Then
        Exit Do
      End If
      Lv = Lv + 1
      idxi(Lv) = idxi(Lv - 1)
      idxj(Lv) = i - 1
      flg0(Lv) = False
      flg1(Lv) = False
    Loop

    If Not flg1(Lv) Then
      flg1(Lv) = True
      If (jj(Lv) + 1) < idxj(Lv) Then
        Lv = Lv + 1
        idxi(Lv) = jj(Lv - 1) + 1
        idxj(Lv) = idxj(Lv - 1)
        flg0(Lv) = False
        flg1(Lv) = False
      Else
        Do
          Lv = Lv - 1
        Loop While flg1(Lv)
      End If
    End If

  Loop
End Sub

ちょっとは見通し良くなったけど..ジリキではここが限界?
再帰版の処理順をトレースして作るとこんな感じからどうも抜け出せない...

..っというわけでカンニング編に続く..(ぇ
Comment
  • X
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする

■QuickSort再帰|非再帰

2012-10-19 21:00:00 | scrap
QuickSort非再帰を試してみる。
A列に6桁ランダム数値を全角文字列にしたデータでテスト。重複あり。

Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private QS() As String    'ソートデータ用
Private QL() As Long     'ソートインデックス用
'-------------------------------------------------
Private Sub test()      'テストプロシージャ選択用
  Const cnt As Long = 65536 'テストデータ量
  Dim ret() As String    '書出し用配列
  Dim i   As Long
  Dim p   As Long
  Dim t   As Long
  Dim v

  ReDim QS(1 To cnt)
  ReDim QL(1 To cnt)
  ReDim ret(1 To cnt, 0)

  For Each v In Range("A1").Resize(cnt).Value
    i = i + 1
    QL(i) = i
    QS(i) = v
  Next

  p = 1 '0 or 1

  t = timeGetTime

  Select Case p
  Case 0
    Call LQuickSort(1, cnt)
  Case 1
    Call LQuickLoop
  End Select

  For i = 1 To cnt
    ret(i, 0) = QS(QL(i))
  Next

  Debug.Print cnt, timeGetTime - t
  'Sheets.Add.Range("A1").Resize(cnt).Value = ret
  Erase QS, QL, ret
End Sub
'-------------------------------------------------
Sub LQuickSort(n As Long, x As Long) '再帰
  Dim tmp As String
  Dim i  As Long
  Dim j  As Long
  Dim k  As Long

  i = n
  j = x
  tmp = QS(QL((i + j) ¥ 2))

  Do
    Do While tmp > QS(QL(i))
      i = i + 1
    Loop
    Do While tmp < QS(QL(j))
      j = j - 1
    Loop

    If i >= j Then
      Exit Do
    End If
    k = QL(i)
    QL(i) = QL(j)
    QL(j) = k

    i = i + 1
    j = j - 1
  Loop

  If n < (i - 1) Then
    LQuickSort n, i - 1
  End If
  If x > (j + 1) Then
    LQuickSort j + 1, x
  End If
End Sub
'-------------------------------------------------
Sub LQuickLoop()      '非再帰
  Const p  As Long = 100 '記憶用配列サイズ・漸増用兼
  Dim tmp  As String
  Dim flg() As Boolean  'Lo|Up処理記憶用
  Dim ix() As Long    'Index記憶用
  Dim ii() As Long    'Loopカウンタ記憶用
  Dim jj() As Long    'Loopカウンタ記憶用
  Dim Lv  As Long    '深さレベル
  Dim Mx  As Long    '配列漸増用(今回未使用)
  Dim i   As Long
  Dim j   As Long
  Dim k   As Long

  ReDim flg(1, p)
  ReDim ix(1, p)
  ReDim ii(p)
  ReDim jj(p)

  Mx = p
  Lv = 1
  ix(0, Lv) = LBound(QL)
  ix(1, Lv) = UBound(QL)

  Do

    Do
      If flg(1, Lv) Then
        Lv = Lv - 1
        Exit Do
      End If

      Do
        If flg(0, Lv) Then
          Exit Do
        End If

        i = ix(0, Lv)
        j = ix(1, Lv)
        tmp = QS(QL((i + j) ¥ 2))

        Do
          Do While tmp > QS(QL(i))
            i = i + 1
          Loop
          Do While tmp < QS(QL(j))
            j = j - 1
          Loop

          If i >= j Then
            Exit Do
          End If
          k = QL(i)
          QL(i) = QL(j)
          QL(j) = k

          i = i + 1
          j = j - 1
        Loop

        flg(0, Lv) = True
        ii(Lv) = i
        jj(Lv) = j

        If ix(0, Lv) >= (i - 1) Then
          Exit Do
        End If

        Lv = Lv + 1
        ix(0, Lv) = ix(0, Lv - 1)
        ix(1, Lv) = i - 1
        flg(0, Lv) = False
        flg(1, Lv) = False
      Loop

      flg(1, Lv) = True

      If ix(1, Lv) <= (jj(Lv) + 1) Then
        Lv = Lv - 1
        Exit Do
      End If

      Lv = Lv + 1
      ix(0, Lv) = jj(Lv - 1) + 1
      ix(1, Lv) = ix(1, Lv - 1)
      flg(0, Lv) = False
      flg(1, Lv) = False
    Loop
  Loop Until Lv < 1
End Sub


結果。やっぱ再帰のほうが記述が簡単で速い。
#ぃゃもっと効率良く書けるのかもしれないが..力ないです..orz

[LQuickSort]
65536     328
65536     329
65536     328

[LQuickLoop]
65536     360
65536     344
65536     344

一応2010でも試してみた。
[LQuickSort]
1000000    6783
1000000    7188
1000000    7204

[LQuickLoop]
1000000    7610
1000000    7719
1000000    7703
Comment
  • X
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする