■ マクロ残高証明
出納帳を完成させます。
- データを揃えるため、
§１．出納帳サンプルデータのダウンロード
-
- ［麻里府発］4まりふのひとの「公開」SkyDrive に入り、
- フォルダー「麻里府パソコン同好会」4「iPSマクロ研」をクリックして開く。
- 「2013-1122_出納帳サンプルデータ」にチェックを入れ、
- ［ダウンロード］をクリックする。
- ［名前を付けて保存］する。
このサンプルデータには
- ［出納帳］シートと［名前］シートがある。
- ［名前］シートには、「前月繰越金」が入っている前提。
- 各シートの1行目（項目行）のみ 塗りつぶし，斜体、罫線等最低限の設定がしてある。
§２．出納帳を設定する
- ウィンドウ枠の固定
- 「月日」セルの書式設定
- 例えば、ユーザー定義で“mm/dd (aaa)”
- 例えば、ユーザー定義で“mm/dd (aaa)”
- 「月日」セルの条件付き書式
- 例えば右図。
- 例えば右図。
- ［出納帳］シートの「名前」は、［名前］シートの「名前」しか入力できないようにする。
- ［名前］シートの名前のセルを選択する。（例えば、C1：C20）
- 名前ボックスに“名前リスト”と入力する。
- ［出納帳］シートの「名前」を列選択し、（例えば、C列）
- ［データの入力規制］を行う。（例えば、右図）
§３．集計用のシートを作成する
残金額等の計算をするため、中間データを保存するシートを作成する。
- シート見出しを、例えば“集計用”とする。
§４．出納帳を集計する
- 「名前」で並ベ替える。
- ［データ］タブ/アウトラインGr.の［小計］をクリックする。
- 【集計の設定】ダイアログボックスで下記を設定し、［OK］をクリックする。
- グループの基準： 名前
- 集計の方法： 合計
- 集計するフィールド：R入金額， R出金額
- R現在の小計をすべて置き換える
- □グループごとに改ページを挿入する
- R集計行をデータの下に挿入する
- アウトライン記号の［2］をクリックする。
- 集計行と総計のみ表示される。
- 見出しおよび集計行のデータを選択する。
- 例えば、C1〜F124 まで。
- 「可視セル」のみを選択する。（Alt＋;）
- ［ホーム］タブ/編集Gr.の［検索と選択］をクリックし、
- ［条件を選択してジャンプ］をクリックする。
- 【選択オプション】ダイアログボックスで、「可視セル」のみを選択し、［OK］する。
- コピーする。（Ctrl＋C）
- 集計用シートの セル A1に貼り付ける。
§５．名前から“ 集計”を取るマクロを作る。
- ［開発］タブ/コードGr.の［マクロの記録］をクリックする。
- 【マクロの記録】ダイアログボックスで下記を入力し［OK］をクリックする。
- マクロ名： “選択したセル内の集計という文字を取り去る”
- マクロの保存先： 作業中のブック
- マクロの記録を終了する。
- ［開発］タブ/コードGr.の［マクロ］をクリックする。
- ［選択したセル内の集計という文字を取り去る］を選択し、［編集］をクリックする。
- 下記を入力する。
- Sub 選択したセル内の集計という文字を取り去る()
- Dim rCell As Range
- Dim sString As String
- For Each rCell In Selection
- sString = rCell.Value
- sString = Replace(sString, "集計", "")
- rCell = Trim(sString)
- Next
- End Sub
- 例えば、セル A2〜A12 を選択する。
§６．残金額を求める
- ［作業用］シートのデータの範囲を選択し、
- 例えば、セル A2〜D12
- 名前ボックスに“集計リスト”と入力する。
- ［名前］シートの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
- 以下のセルは、フィルハンドルでコピーする。
■ よくわかる Microsoft Excel2010 マクロ/VBA （FOM出版）
