まりふのひと

トップ > MAPS-MK

午後/iPSマクロ研の予定…マクロ残高証明の作成

MAPS-MK


■　お知らせ

  • 今回で最終回となります。


■　マクロ残高証明

  1. サンプルデータをダウンロードする
    1. ［麻里府発］の まりふのひとの「公開」SkyDrive に移動し、
    2. フォルダー「麻里府パソコン同好会」をクリックし、（開く）
    3. フォルダ「iPSマクロ研」をクリックする。 （開く）
    4. 「残高証明v026」（圧縮（zip形式）フォルダ）にチェックを入れ、
    5. ［ダウンロード］をクリックする。
    6. ［ファイルを開く］。
    7. 残高証明v026（フォルダ）を右クリック ⇒［コピー］する。
    8. 自分のPCの保存先フォルダーを開き‥‥ 貼り付ける。
  2. ダウンロード後の最初に開く時は、警告が出るかもしれません。
  3. 使い方
    1. 平成25年10月分出納帳を開く。
      • データを更新する...
    2. マクロ残高証明.xlsm を開く。
    3. マクロを実行する。
      1. 出納帳編集
      2. 残高証明書作成
  4. 評価する...

事後放言

  • 「印刷」が残ったが、何れマクロを作って配付することとした。
  • マクロは難しい。その前に「Excelをもっと知る必要がある」というのが大半の意見であった。

午後/iPSマクロ研の予定…マクロ残高証明の作成

MAPS-MK


■　お知らせ


■　マクロ残高証明

§１．データを揃えるため、サンプルデータをダウンロードする
  1. ［麻里府発］の まりふのひとの「公開」SkyDrive に移動し、
  2. フォルダー「麻里府パソコン同好会」をクリックし、（開く）
  3. フォルダ「iPSマクロ研」をクリックする。 （開く）
  4. 「2014-0117_出納帳サンプルデータ」（圧縮（zip形式）フォルダ）にチェックを入れ、
  5. ［ダウンロード］をクリックする。
  6. ［ファイルを開く］。
  7. 「2014-0117_出納帳サンプルデータ.xlsm」を右クリック ⇒［コピー］する。
  8. 保存先フォルダーを開き‥‥ 貼り付ける。

§２．サブ「残金を集計する」を解析する
≪コード≫
  1. Sub 残金を集計する()
  2. Dim i As Long
  3. Dim LastRow As Long
  4. Dim sRange As String
  5. 　
  6. Sheets("作業用").Select
  7. ActiveCell.SpecialCells(xlLastCell).Select
  8. sRange = "$A$2:" & ActiveCell.Address
  9. 　
  10. Sheets("名前").Select
  11. Range("C1").Select
  12. Selection.End(xlDown).Select
  13. LastRow = ActiveCell.Row
  14. 　
  15. For i = 2 To LastRow
  16. If IsError(Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 3, False)) Then
  17. Cells(i, 5) = ""
  18. Else
  19. Cells(i, 5) = Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 3, False)
  20. End If
  21. 　
  22. If IsError(Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 4, False)) Then
  23. Cells(i, 6) = ""
  24. Else
  25. Cells(i, 6) = Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 4, False)
  26. End If
  27. 　
  28. Cells(i, 7) = Cells(i, 4) + Cells(i, 5) - Cells(i, 6)
  29. Next
  30. Range("A1").Select
  31. 　
  32. End Sub
≪補足説明≫
  • 7行目：Ctrl＋End（［作業用］シートの表の右下隅のセルをアクティブにする）
  • 8行目：VLookup で参照する表の範囲（A2〜D12）を sRange にセットする。（$A$2:$D$12）
  • 12行目：Ctrl＋↓
  • 13行目：C列の最終行を LastRow にセットする。
  • 15行目〜29行目：For〜Nextステートメント ‥‥ テキスト P.126 STEP6 処理を繰り返す
  • 16行目〜20行目：If〜Then〜Else〜End If ‥‥ テキスト P.102 STEP3 制御構造を使用する
  • 16行目〜17行目：VLookup関数で作業用シート $A$2:$D$12 を検索した時、見つからなかった（エラーになった）時、"" とする。
  • 19行目：見つかった時は、預り金を返す。
  • 22行目〜25行目：同様に、出金額を返す。
  • 28行目：残金額を計算する。
  • 30行目：終わったら、セルA1 をアクティブにしておく。
§３．画面の更新を禁止する

動作確認が終わったら、仕上げとして、画面の更新を禁止します。
［名前］シートを選択しておき、下記を実行して、確認します。

  1. Sub aa出納帳編集()
  2. 　
  3. Application.ScreenUpdating = False
  4. Call 集計シートのクリア
  5. Call 作業用シートのクリア
  6. Call 出納帳を名前で並べ替える
  7. Call 出納帳を集計する
  8. Call 名前の集計を取り去る
  9. Call 残金を集計する
  10. Application.ScreenUpdating = True
  11. 　
  12. End Sub

午後/iPSマクロ研の予定…マクロ残高証明の作成

MAPS-MK VBA


■　マクロ残高証明

§１．データを揃えるため、サンプルデータをダウンロードする
  1. ［麻里府発］の まりふのひとの「公開」SkyDrive に入り、
  2. フォルダー「麻里府パソコン同好会」をクリックし、（開く）
  3. フォルダ「iPSマクロ研」をクリックする。 （開く）
  4. 「2013-1220_出納帳サンプルデータ」（圧縮（zip形式）フォルダ）にチェックを入れ、
  5. ［ダウンロード］をクリックする。
  6. ［ファイルを開く］。
  7. 「2013-1220_出納帳サンプルデータ.xlsm」を右クリック ⇒［コピー］する。
  8. 保存先フォルダーを開き‥‥ 貼り付ける。


このサンプルデータには、3っのシートがある。

  • ［出納帳］シート
  • ［名前］シート
    • 「前月繰越金」は、入っている前提。
  • ［作業用］シート

マクロは、下記が入っている。

  • 前回作成したマクロ
    　修正箇所
    1. 出納帳を集計する ‥‥ 書式はクリアしないようにした。
    2. 作業用シートのクリア ‥‥ ［作業用］シートは無ければ作る。あればクリアするようにした。
  • 選択したセル内の集計と云う文字を取り去る


§２．出納帳を集計する
  1. 前回作成したマクロ（その後、一部改修）の動作確認を行う。
  2. 前回の続きのマクロを作成する。（バグがあるかも...）
§2.6. マクロ名「名前の集計を取り去る」

作成済みマクロを使います。

１）操作
  1. ［作業用］シートを選択する。
  2. セル A1 をクリックし、
  3. Shift＋Ctrl＋↓
  4. マクロ「選択したセル内の集計という文字を取り去る」を実行する。
  5. セル A1 をクリックし、選択を解除しておく。

２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 名前の集計を取り去る()
  2. 　
  3. Sheets("作業用").Select
  4. Range("A1").Select
  5. Range(Selection, Selection.End(xlDown)).Select
  6. Application.Run "'2013-1220_出納帳サンプルデータ.xlsm'!選択したセル内の集計という文字を取り去る"
  7. Range("A1").Select
  8. 　
  9. End Sub

３）補完後マクロ
    1. Sub 名前の集計を取り去る()
    2. 　
    3. Sheets("作業用").Select
    4. Range("A1").Select
    5. Range(Selection, Selection.End(xlDown)).Select
    6. Call 選択したセル内の集計という文字を取り去る
    7. Range("A1").Select
    8. 　
    9. End Sub

§2.7. ここまで作成したマクロを連続して動かす
  1. マクロ「aa出納帳編集」を下記のように改修後、実行する。
    1. Sub aa出納帳編集()
    3. Call 集計シートのクリア
    4. Call 作業用シートのクリア
    5. Call 出納帳を名前で並べ替える
    6. Call 出納帳を集計する
    7. Call 名前の集計を取り去る
    9. End Sub
§2.8. 名前「金額リスト」を作る

次に VLookup 関数を使うため、［作業用］シートの表に「金額リスト」という名前を付ける。
但し、名前はマクロに反映されないため、マクロは作らない。

１）操作
  1. ［作業用］シートを選択する。
  2. 表（A2〜D12）を選択する。
    1. セル A1 をクリックし、
    2. Shift＋Ctrl＋End
  3. 名前ボックスに“金額リスト”を入力する。

§2.8. マクロ名「残金を計算する」

［名前］シートを完成させる。

１）操作
  1. ［作業用］シートの表の右下のアドレスの取得
    1. ［作業用］シートを選択する。
    2. Ctrl＋End
  2. ［名前］シートを選択する。
  3. セル E2 に次の式を入力する。
    =IF(ISERROR(VLOOKUP(C2,金額リスト,3,FALSE)),0,VLOOKUP(C2,金額リスト,3,FALSE))
  4. セル F2 に次の式を入力する。
    =IF(ISERROR(VLOOKUP(C2,金額リスト,4,FALSE)),0,VLOOKUP(C2,金額リスト,4,FALSE))
  5. セル G2 に次の式を入力する。
    =D2+E2-F2
  6. 3行目以降は、計算式をコピーする。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 残金を集計する()
  2. 　
  3. Sheets("作業用").Select
  4. ActiveCell.SpecialCells(xlLastCell).Select
  5. Sheets("名前").Select
  6. Range("E2").Select
  7. ActiveCell.FormulaR1C1 = _
  8. "=IF(ISERROR(VLOOKUP(RC[-2],金額リスト,3,FALSE)),0,VLOOKUP(RC[-2],金額リスト,3,FALSE))"
  9. Range("F2").Select
  10. ActiveCell.FormulaR1C1 = _
  11. "=IF(ISERROR(VLOOKUP(RC[-3],金額リスト,4,FALSE)),0,VLOOKUP(RC[-3],金額リスト,4,FALSE))"
  12. Range("G2").Select
  13. ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]-RC[-1]"
  14. Range("E2:G2").Select
  15. Selection.AutoFill Destination:=Range("E2:G21")
  16. Range("E2:G21").Select
  17. Range("A1").Select
  18. 　
  19. End Sub
３）補完後マクロ

得られたマクロは難解です。全面的に作り変えます。

  1. Sub 残金を集計する()
  2. Dim i As Long
  3. Dim LastRow As Long
  4. Dim sRange As String
  5. 　
  6. Sheets("作業用").Select
  7. ActiveCell.SpecialCells(xlLastCell).Select
  8. sRange = "$A$2:" & ActiveCell.Address
  9. 　
  10. Sheets("名前").Select
  11. Range("C1").Select
  12. Selection.End(xlDown).Select
  13. LastRow = ActiveCell.Row
  14. 　
  15. For i = 2 To LastRow
  16. If IsError(Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 3, False)) Then
  17. Cells(i, 5) = ""
  18. Else
  19. Cells(i, 5) = Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 3, False)
  20. End If
  21. 　
  22. If IsError(Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 4, False)) Then
  23. Cells(i, 6) = ""
  24. Else
  25. Cells(i, 6) = Application.VLookup(Cells(i, 3), Worksheets("作業用").Range(sRange), 4, False)
  26. End If
  27. 　
  28. Cells(i, 7) = Cells(i, 4) + Cells(i, 5) - Cells(i, 6)
  29. Next
  30. Range("A1").Select
  31. 　
  32. End Sub
≪補足説明≫
  • 7行目：Ctrl＋End（［作業用］シートの表の右下隅のセルをアクティブにする）
  • 8行目：VLookup で参照する表の範囲（A2〜D12）を sRange にセットする。（$A$2:$D$12）
  • 12行目：Ctrl＋↓
  • 13行目：C列の最終行を LastRow にセットする。
  • 15行目〜29行目：For〜Nextステートメント ‥‥ テキスト P.126 STEP6 処理を繰り返す
  • 16行目〜20行目：If〜Then〜Else〜End If ‥‥ テキスト P.102 STEP3 制御構造を使用する
  • 16行目〜17行目：VLookup関数で作業用シート $A$2:$D$12 を検索した時、見つからなかった（エラーになった）時、"" とする。
  • 19行目：見つかった時は、預り金を返す。
  • 22行目〜25行目：同様に、出金額を返す。
  • 28行目：残金額を計算する。
  • 30行目：終わったら、セルA1 をアクティブにしておく。

§2.9. マクロを完成させる

これまで作成したマクロを順に実行させるマクロを作成する。

完成したマクロ
  1. Sub aa出納帳編集()
  2. 　
  3. Call 集計シートのクリア
  4. Call 作業用シートのクリア
  5. Call 出納帳を名前で並べ替える
  6. Call 出納帳を集計する
  7. Call 名前の集計を取り去る
  8. Call 残金を集計する
  9. 　
  10. End Sub

事後放言

  1. 初めに「Excel/フィルターで「を含む」で絞り込むマクロ（2条件）」を行ったので、出だしが遅れた。
    • しかもバグがあるなんて‥‥ （Excel2007と Excel2010の違いかも知れないが未確認）
  2. 「2013-1220_出納帳サンプルデータ」（圧縮（zip形式）がアップロードしてなくて遅れた。（確認不足）
  3. 「完成したマクロ」まで走ったが、「残金を計算する」のコードは次回に持ち越した。
    • インデントの付け方がバラバラ。この辺も次回に...

午後/iPSマクロ研の予定…マクロ残高証明の作成

MAPS-MK VBA


■　マクロ残高証明
前回行った手順をマクロ化してみます。

§１．データを揃えるため、サンプルデータをダウンロードする
  1. ［麻里府発］の まりふのひとの「公開」SkyDrive に入り、
  2. フォルダー「麻里府パソコン同好会」をクリックし、（開く）
  3. フォルダ「iPSマクロ研」をクリックする。 （開く）
  4. 「2013-1213_出納帳サンプルデータ」（圧縮（zip形式）フォルダ）にチェックを入れ、
  5. ［ダウンロード］をクリックする。
  6. ［ファイルを開く］。
  7. 「2013-1213_出納帳サンプルデータ.xlsm」を右クリック ⇒［コピー］する。
  8. 保存先フォルダーを開き‥‥ 貼り付ける。


このサンプルデータには、3っのシートがある。

  • ［出納帳］シート
  • ［名前］シート
    • 「前月繰越金」は、入っている前提。
  • ［作業用］シート

更に、マクロ名「選択したセル内の集計と云う文字を取り去る 」が入っている。（前回作成）


§２．出納帳を集計する

手順を確認しながら進めます。（バグがあるかも...）

§2.1. マクロ名「集計シートのクリア」

［名前］シートの集計場所 Ｅ〜Ｇ列をクリアします。

１）操作
  1. ［名前］シートを選択する。
  2. セル E2 をクリックし、
  3. Shift＋Ctrl＋End を押下する。
  4. ［ホーム］タブ/編集Gr.の［クリア］⇒［数式と値のクリア］［すべてクリア］をクリックする。
  5. セル A1 をクリックし、選択を解除しておく。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 集計シートのクリア()
  2. Sheets("名前").Select
  3. Range("E2").Select
  4. Range(Selection, ActiveCell.SpecialCells(xlLastCell).Select
  5. Selection.Clear
  6. Range("A1").Select
  7. End Sub
３）補完後マクロ
  • 書式は残してクリアする。
  1. Sub 集計シートのクリア()
  2. 　
  3. Sheets("名前").Select
  4. Range("E2").Select
  5. Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  6. Selection.ClearContents
  7. Range("A1").Select
  8. 　
  9. End Sub

§2.2. マクロ名「作業用シートのクリア」
１）操作
  1. ［作業用］シートを選択する。
  2. セル A1 を選択し、
  3. Shift＋Ctrl＋End を押下する。
  4. ［ホーム］タブ/編集Gr.の［クリア］⇒［すべてクリア］をクリックする。
  5. セル A1 をクリックし、選択を解除しておく。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 作業用シートのクリア()
  2. Sheets("作業用").Select
  3. Range("A1").Select
  4. Range(Selection, ActiveCell.SpecialCells(xlLastCell).Select
  5. Selection.Clear
  6. Range("A1").Select
  7. End Sub
３）補完後マクロ
  • ［作業用］シートは無ければ作る。あればクリアする。
  1. Sub 作業用シートのクリア()
  2. Dim wWorksheet As Worksheet
  3. Dim bFlag As Boolean
  4. 　
  5. bFlag = False
  6. For Each wWorksheet In Worksheets
  7. If wWorksheet.Name = "作業用" Then bFlag = True
  8. Next wWorksheet
  9. 　
  10. If bFlag = True Then
  11. Sheets("作業用").Select
  12. Range("A1").Select
  13. Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  14. Selection.Clear
  15. Else
  16. With Worksheets.Add(after:=Worksheets(Worksheets.Count))
  17. .Name = "作業用"
  18. End With
  19. End If
  20. 　
  21. Range("A1").Select
  22. 　
  23. End Sub

§2.3. マクロ名「出納帳を名前で並べ替える」

今回の目的からすれば「名前」のみの並び替えでよいが、先々のことを考慮し「名前／月日」を昇順に並べる。

１）操作
  1. ［出納帳］シートを選択する。
  2. Ctrl＋End ‥‥ これにより、表の右下隅のセルがアクティブになる。
    • これは、マクロの修整に必要な操作で、並べ替えには不要。
  3. セル A1 を選択し、
  4. ［並べ替え］をクリックする。
  5. 【並べ替え】ダイアログボックスで
    • 名前／月日を昇順とする。
    • R先頭行をデータの見出しとして使用する。
    • 8ふりがなを使わない。
  6. ［OK］で並べ替える。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 出納帳を名前で並べ替える()
  2. Sheets("出納帳").Select
  3. ActiveCell.SpecialCells(xlLastCell).Select
  4. Range("A1").Select
  5. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Clear
  6. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("C2:C113"), _
  7. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  8. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("B2:B113"), _
  9. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  10. With ActiveWorkbook.Worksheets("出納帳").Sort
  11. .SetRange Range("A1:G113")
  12. .Header = xlYes
  13. .MatchCase = False
  14. .Orientation = xlTopToBottom
  15. .SortMethod = xlStroke
  16. .Apply
  17. End With
  18. End Sub

３）補完後マクロ
  • “113”という行番号が出てくるが、これはデータ量で変わるため固定できない。
  1. Sub 出納帳を名前で並べ替える()
  2. Dim LastRow As Long
  3. 　
  4. Sheets("出納帳").Select
  5. ActiveCell.SpecialCells(xlLastCell).Select
  6. LastRow = ActiveCell.Row
  7. Range("A1").Select
  8. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Clear
  9. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("C2:C" & LastRow), _
  10. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  11. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
  12. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  13. With ActiveWorkbook.Worksheets("出納帳").Sort
  14. .SetRange Range("A1:G" & LastRow)
  15. .Header = xlYes
  16. .MatchCase = False
  17. .Orientation = xlTopToBottom
  18. .SortMethod = xlStroke
  19. .Apply
  20. End With
  21. 　
  22. End Sub

§2.4. マクロ名「出納帳を集計する」

出納帳を集計し、名前別の金額合計を［作業用］シートに貼り付ける。

１）操作
  1. ［出納帳］シートを選択する。
  2. セル A1 を選択し、
  3. ［データ］タブ/アウトラインGr.の［小計］をクリックする。
  4. 【集計の設定】ダイアログボックスで下記を設定し、［OK］をクリックする。
    • グループの基準： 名前
    • 集計の方法： 合計
    • 集計するフィールド：R入金額， R出金額
    • R現在の小計をすべて置き換える
    • □グループごとに改ページを挿入する
    • R集計行をデータの下に挿入する
  5. アウトライン記号の［2］をクリックする。
    　→ 集計行と総計のみ表示される。
  6. 次の操作はマクロではできないので、
    1. セルC1をクリックし、
    2. Ctrl＋↓　しておく。
  7. 集計されたデータ（C1〜F124）を選択する。
  8. 「可視セル」のみを選択する。（Alt＋;（セミコロン））
  9. コピーする。（Ctrl＋C）
  10. 作業用シートのセル A1 に値のみ 貼り付ける。
  11. セル A1 をクリックし、選択を解除しておく。
  12. ［出納帳］シートを選択し、
  13. セル A1 をクリックし、
  14. 小計を［すべて削除］する。

２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 出納帳を集計する()
  2. Sheets("出納帳").Select
  3. Range("A1").Select
  4. Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 6), _
  5. Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  6. ActiveSheet.Outline.ShowLevels RowLevels:=2
  7. Range("C1").Select
  8. Selection.End(xlDown).Select
  9. Range("C1:F124").Select
  10. Selection.SpecialCells(xlCellTypeVisible).Select
  11. Selection.Copy
  12. Sheets("作業用").Select
  13. Range("A1").Select
  14. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15. :=False, Transpose:=False
  16. Range("A1").Select
  17. Sheets("出納帳").Select
  18. Range("A1").Select
  19. Application.CutCopyMode = False
  20. Selection.RemoveSubtotal
  21. End Sub

３）補完後マクロ
  • “124”という行番号が出てくるが、これはデータ量で変わるため固定できない。
  1. Sub 出納帳を集計する()
  2. Dim LastRow As Long
  3. 　
  4. Sheets("出納帳").Select
  5. Range("A1").Select
  6. Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 6), _
  7. Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  8. ActiveSheet.Outline.ShowLevels RowLevels:=2
  9. Range("C1").Select
  10. Selection.End(xlDown).Select
  11. LastRow = ActiveCell.Row - 1
  12. Range("C1:F" & LastRow).Select
  13. Selection.SpecialCells(xlCellTypeVisible).Select
  14. Selection.Copy
  15. Sheets("作業用").Select
  16. Range("A1").Select
  17. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  18. :=False, Transpose:=False
  19. Range("A1").Select
  20. Sheets("出納帳").Select
  21. Range("A1").Select
  22. Application.CutCopyMode = False
  23. Selection.RemoveSubtotal
  24. 　
  25. End Sub


§2.5. マクロ名「aa出納帳集計」

今日 作成したマクロを、連続して動かすマクロを作成する。

    • 先頭の“aa”は、［マクロの表示］ボタンをクリックした時の表示の順番の問題で、他意はない。
１）操作
  1. ［マクロの記録］を開始し、
  2. マクロ名を入力し、［OK］する。
  3. マクロの記録を終了する。
２）得られたマクロ

コメント行のみで、中身は無い。

  1. Sub aa出納帳編集()
  2. 　
  3. End Sub
３）補完後マクロ
  1. Sub aa出納帳編集()
  2. 　
  3. Call 集計シートのクリア
  4. Call 作業用シートのクリア
  5. Call 出納帳を名前で並べ替える
  6. Call 出納帳を集計する
  7. 　
  8. End Sub

事後放言

  • 今日行った内容と、帰宅後 気が付いた点で更新した。（次回、確認する）
  • 次回送りになった内容は、冗長になるので削除した。
  • 次回、20日には完成させるッ!!!
  • bootさんから「Excelデータベースの検索」をしたい旨の要望が出たので、次回までに検討する。

平成25年11月22日(金/PM) いきいきパソコン談話室（iPSマクロ研)の予定

MAPS-MK


■　マクロ残高証明
出納帳を完成させます。

  • データを揃えるため、
§１．出納帳サンプルデータのダウンロード
    1. ［麻里府発］4まりふのひとの「公開」SkyDrive に入り、
    2. フォルダー「麻里府パソコン同好会」4「iPSマクロ研」をクリックして開く。
    3. 「2013-1122_出納帳サンプルデータ」にチェックを入れ、
    4. ［ダウンロード］をクリックする。
    5. ［名前を付けて保存］する。

このサンプルデータには

  • ［出納帳］シートと［名前］シートがある。
  • ［名前］シートには、「前月繰越金」が入っている前提。
  • 各シートの1行目（項目行）のみ 塗りつぶし，斜体、罫線等最低限の設定がしてある。
§２．出納帳を設定する
  1. ウィンドウ枠の固定
  2. 「月日」セルの書式設定
    • 例えば、ユーザー定義で“mm/dd (aaa)”
  3. 「月日」セルの条件付き書式
    • 例えば右図。
  4. ［出納帳］シートの「名前」は、［名前］シートの「名前」しか入力できないようにする。
    1. ［名前］シートの名前のセルを選択する。（例えば、C1：C20）
    2. 名前ボックスに“名前リスト”と入力する。
    3. ［出納帳］シートの「名前」を列選択し、（例えば、C列）
    4. ［データの入力規制］を行う。（例えば、右図）
§３．集計用のシートを作成する

残金額等の計算をするため、中間データを保存するシートを作成する。

  • シート見出しを、例えば“集計用”とする。
§４．出納帳を集計する
  1. 「名前」で並ベ替える。
  2. ［データ］タブ/アウトラインGr.の［小計］をクリックする。
  3. 【集計の設定】ダイアログボックスで下記を設定し、［OK］をクリックする。
    • グループの基準： 名前
    • 集計の方法： 合計
    • 集計するフィールド：R入金額， R出金額
    • R現在の小計をすべて置き換える
    • □グループごとに改ページを挿入する
    • R集計行をデータの下に挿入する
  4. アウトライン記号の［2］をクリックする。
    • 集計行と総計のみ表示される。
  5. 見出しおよび集計行のデータを選択する。
    • 例えば、C1〜F124 まで。
  6. 「可視セル」のみを選択する。（Alt＋;）
    1. ［ホーム］タブ/編集Gr.の［検索と選択］をクリックし、
    2. ［条件を選択してジャンプ］をクリックする。
    3. 【選択オプション】ダイアログボックスで、「可視セル」のみを選択し、［OK］する。
  7. コピーする。（Ctrl＋C）
  8. 集計用シートの セル A1に貼り付ける。


§５．名前から“ 集計”を取るマクロを作る。
  1. ［開発］タブ/コードGr.の［マクロの記録］をクリックする。
  2. 【マクロの記録】ダイアログボックスで下記を入力し［OK］をクリックする。
    1. マクロ名： “選択したセル内の集計という文字を取り去る”
    2. マクロの保存先： 作業中のブック
  3. マクロの記録を終了する。
  4. ［開発］タブ/コードGr.の［マクロ］をクリックする。
  5. ［選択したセル内の集計という文字を取り去る］を選択し、［編集］をクリックする。
  6. 下記を入力する。
    1. Sub 選択したセル内の集計という文字を取り去る()
    2. Dim rCell As Range
    3. Dim sString As String
    4. 　
    5. For Each rCell In Selection
    6. sString = rCell.Value
    7. sString = Replace(sString, "集計", "")
    8. rCell = Trim(sString)
    9. Next
    10. 　
    11. End Sub
  7. コンパイルし、文法エラーが無いことを確認する。
  8. VBAを閉じる。
  9. ［作業用］シートの名前を選択し、
    • 例えば、セル A2〜A12 を選択する。
  10. マクロ「選択したセル内の集計という文字を取り去る」を［実行］する。
§６．残金額を求める
  1. ［作業用］シートのデータの範囲を選択し、
    • 例えば、セル A2〜D12
  2. 名前ボックスに“集計リスト”と入力する。
  3. ［名前］シートの2行目に計算式を入力し、
    • セル E2：“=if(iserror(vlookup(c2,集計リスト,3,false)),0,vlookup(c2,集計リスト,3,false))”
    • セル F2：“=if(iserror(vlookup(c2,集計リスト,4,false)),0,vlookup(c2,集計リスト,4,false))”
    • セル G2：“=d2+e2-f2
  4. 以下のセルは、フィルハンドルでコピーする。





■　よくわかる Microsoft Excel2010 マクロ/VBA （FOM出版）

§第４章　モジュールとプロシージャ

平成25年11月8日(金/PM) いきいきパソコン談話室（iPSマクロ研)の予定

MAPS-MK


■　お知らせ

  • 12月の「iPSマクロ研」は、12/13（金）と 12/20（金）、何れも午後（13:00-16:00）を予定しています。


■　よくわかる Microsoft Excel2010 マクロ/VBA （FOM出版）

§第４章　モジュールとプロシージャ
  • とりあえずテキストを離れ、「マクロ残高証明書」で実践します。


■　マクロ残高証明

平成25年10月25日(金/PM) いきいきパソコン談話室（iPSマクロ研)の予定

MAPS-MK


■　お知らせ


■　よくわかる Microsoft Excel2010 マクロ/VBA （FOM出版）

  • VBEの画面構成と基本設定
    • VBE の画面構成（第３章　P.43）
    • 変数の宣言の強制（第５章 P.101）
    • Dim ステートメントとデータ型（第５章 P.95〜P.96）
  • 第４章　モジュールとプロシージャ
    • Step1　モジュールの概要



■　マクロ残高証明
　Excel機能を確認します。

  1. フィルターの結果を別のシートにコピペする
    • Office Tanaka によれば、Excel2007 以降変わったようだ。
      　Excel2003 以前を使っている方には参考にならないかも知れない‥‥ を考える。