Excel/家計簿77から作る医療費明細書、内容を見直し部分的にマクロ化した

 「家計簿77から作る医療費明細書作成」の最終回を 3月26日AM に予定している。
来年になったらすっかり忘れてしまっている‥‥ かも知れない。で、(1)前提条件を明確にし、(2)マクロで出来るところはマクロ化した。
 完成予想図は、2016.02.17のブログ「家計簿77で「医療費明細書」をExcel で仕上げた‥‥ が、税務署がウンと言うかは別」(2月29日、確定申告は無事終了した)と同じで下図。

作成手順も同じであるが、マクロでは処理し難いところがあるので一部変更した。

≪基本的な考え方≫
  1. 医療費明細書用の Excelファイルは、家計簿77の総収支・シートをコピーして作る。
    • ファイル名は任意であるが、「医療費明細書yyyy」が望ましい。
    • コピー後、マクロ家計簿77を呼び出すボタンを全て削除する。
  2. 医療費明細書専用の「マクロ医療費明細.xlsm」を作る。
    • 医療費明細書yyyy を開いた後、「マクロ医療費明細」を使うためのボタンを作る。
    • 医療費明細書は個人差(家計簿77の入力内容)が大きいく、マクロのみで作ることは困難なので、必要な都度マクロを起動する方式の「マクロ集」とする。

  3. 「マクロ医療費明細」は「マクロ家計簿77」と同じ手順でダウンロード出来る
    • 現在のファイル名は「マクロ医療費明細v00.zip」
§1.医療費明細書ファイルの準備
  1. 家計簿77の総収支・シートをコピーして、「医療費明細書yyyy」ファイルを作る
    1. 家計簿77(通常であれば昨年分)を開く。
    2. 総収支・シートをアクティブにする。
    3. シート見出しを右クリック ⇒[移動またはコピー]をクリックする。
    4. 移動先ブック名:(新しいブック)
      コピーを作成する ‥‥ 必須!!!
    5. [OK]をクリックすると、新しいブック,Book* が出来る。
  2. (裏にある)家計簿77 を閉じる。
    • もし、「変更を保存しますか?」が出たら[保存しない]にする。

  3. 総収支シートの全てのボタンを削除する
    1. [ホーム]タブ/編集Gr.の[検索と選択]⇒[条件を選択してジャンプ]をクリックし、
    2. ◉オブジェクト を選択して[OK]。
    3. すべてのボタンが選択されるので、[Delete]する。
      • この操作により、「マクロ家計簿77」とは縁が切れたことになる。

  4. Book* を[名前を付けて保存]する
    • 保存場所は 家計簿77があるフォルダー(家計簿77)と同じ場所がベスト。
    • ファイル名は任意であるが、「医療費明細書yyyy」がベター。
    • 保存後、タイトルバーで名前を確認する!
§2.明細書1・シート(医療費明細書の原始データ)の作成
  1. 「医療費明細書yyyy」の総収支・シートをアクティブにする。
  2. [フィルター]をオンにする。
  3. 「費目」より医療費明細対象の費目を選択する。
  4. 合計の支出金額を控える...
    • 支出の列を選択すると、ステータスバーに合計が表示される‥‥ かも。
    • 今からデータを加工してゆくが、最後になっても合計金額は変わらないはず。

  5. データを全て選択し、(セルA1を選択し、Ctrl+A)
  6. クリップボードにコピー(Ctrl+C)する。
  7. 新しいシートのセルA1 に貼り付ける。(Ctrl+V)
  8. 貼り付けたら、シート名を “明細書1” に変更する。
    • “明細書1”の“1”は半角とします。以下、同じ。
       ∵マクロで使用しているためで、全角にするとエラーになる。
    • 列幅を調節する。(任意)
ヒント

 初回時のみ、次を行う。

  1. 総収支・シートにマクロを[有効にする]ボタンを作る
    1. 総収支・シートをアクティブにする。
      • フィルターはオフ、選択も解除しておく。
    2. 「マクロ医療費明細.xlsm」を開く。
    3. [マクロの表示(Alt+F8)]する。
    4. [〜マクロを有効にするボタンの作成]を選択し、
    5. [実行]する。
    6. セルB1に[マクロを有効にする]ボタンが出来る。
  2. [上書き保存(Ctrl+S)]する。
§3.明細書2・シート(医療費明細書と同じレイアウト)の作成
  • マクロ名:〜医療費明細レイアウトを作る
    • マクロは「画面が見えない」ので、1列ずつコピペする方法を採っている。


列番号申告項目家計簿77の項目
医療を受けた人D列(費目)
続柄なし(列挿入)
病院・薬局などの所在地・名称C列(品名)
治療内容・医薬品名など備考
備考 中の最初の“。”の前までを切り取って作る。
支払った医療費F列(支出)
生命保険や社会保険などで補填される金額E列(入金)
なし(なし。
列を挿入し、初期値として「すべて1」を入れる。
なし通し番号

    • 明細書1・シートより「家計簿77の項目」で、明細書2・シートを作る。
      • 例えばA列、マクロではD列を移す。(“費目”は探さない)
      • 「通し番号」は“通し番号”という名の列を探して移す。
    • 項目名は家計簿77と同じ(無い場合は申告項目)とする。


  1. 明細書1・シートをコピーし、明細書2・シートを作る。
  2. 不要な列を削除する
    • 必要な列は、「品名」「費目」「入金」「支出」「通し番号」「備考」
       上記以外の列は削除する。
  3. 列を入れ替え、「医療費の明細書」と同じレイアウトにする太字が項目名)
    1. 例えば「費目」列を選択し、
    2. 右クリック ⇒[切り取り]をクリックする。
    3. A列を選択し、
    4. 右クリック ⇒[切り取ったセルの挿入]する。
ヒント
  • 通し番号のセルの書式設定は、下記がお勧め。
    • 表示形式の “0000-000” は変更不可。
    • 配置タブ/横位置は「標準」とし、文字の制御のすべてのチェックを外す。
  • 完成したら、上書き保存する。(任意)
§4.明細書2・シートを集計順に並べ替える
  • マクロ名:費目品名備考通し番号で並べ替える
  1. 明細書2・シートをアクティブにする。
  2. 並べ替える
    • 費目/品名/備考/通し番号
    • 費目(医療を受けた人)は申告者が「上」になる(たぶん)よう昇順/降順を決める。
    • 「◉ふりがなを使わない」がお勧め。

§5.データの整合性を図る

 これが全てを精度を決める重要な作業となる。(マクロでは決して出来ない)

  1. 品名(病院・薬局)の整合性
    • 基本的には、領収書に書かれているとおりがベストであろう。
       まりふのひとの場合、
      • クスリ岩崎チェーン」と「くすり岩崎チェーン」があり、後者に統一した。
      • 「周東病院」は「周東総合病院」に統一した。
  2. 備考(治療内容・医療品名)の整合性
    • 表現を統一する。長いと印刷設定で苦労する。
    • 歯科医院の場合は “治療” でOK?
    • 内容を修正したら、再度、並び替える。‥‥ 必須!!!、手を抜かない!

§6.集計する
  • マクロ名:費目品名備考別に集計する

 集計(合計)は、①医療を受けた人、②病院・薬局など、③治療内容 で行うことになる。

  1. [小計]する(1回目)
    • グループの基準:費目
    • 集計の方法:合計
    • 集計するフィールド:☑支出、☑入金、☑枚
    • ☑現在の小計をすべて置き換える
      ☑集計行をデータの下に挿入する
  2. [OK]

  3. [小計]する(2回目)
    • グループの基準:品名
    • ☐現在の小計をすべて置き換える
  4. [OK]

  5. [小計]する(3回目)
    • グループの基準:備考
  6. [OK]

  7. 列幅を調節する(任意)
§7.集計行に通し番号を貼り付ける
  • マクロ名:〜集計行に通し番号をセットする
    • データの整合性を図るため、再度、集計し直す。

 次の処理で「集計行」のみにする。
そうすると「通し番号」が隠れてしまうので、集計行の通し番号のセルに「通し番号」を貼り付けるもの。手動ではできない...(たぶん)

§8.医療費明細書のヘッダーを作り、集計行を貼り付ける
  • マクロ名:〜明細書Hシート作成
    • 明細書ヘッダーのセルA1には、明細書1・シートのセルB2の「年」を取ってきている。日付は1月1日。

 医療費明細書のヘッダー部分を作り、その下に、明細書2・シートの集計行を貼り付ける。

  1. マクロ医療費明細の明細書H・シートをコピーする
    1. 「マクロ医療費明細」を前に出す。
    2. 明細書H・シートをアクティブにする。
    3. シート見出しを右クリック ⇒[移動またはコピー]する。
    4. 【シートの移動またはコピー】ダイアログボックスで、
      • 移動先ブック名:医療費明細書yyyy
      • 挿入先:(末尾へ移動)
      • Rコピーを作成する(必須
    5. 設定を確認して[OK]する。
  2. 明細書2・シートの集計行のみをコピーし、明細書H・シートのヘッダーの下に貼り付ける
    1. 明細書2・シートをアクティブにする。
    2. 表示レベル[4]をクリックする。
    3. セルA1を選択し、
    4. 全て選択する。(Ctrl+A)
    5. 可視セルのみ選択する。(Alt+;)
      • リボンでは、[ホーム]タブ/編集Gr.の[検索と選択]⇒[条件を選択してジャンプ]⇒8可視セル ⇒[OK]
    6. コピーする。(Ctrl+C)
    7. 明細書H・シートのセルA6に貼り付ける。(Ctrl+V)
    • 明細書ヘッダーと項目の対応を確認し、第6行を行削除する。
§9.医療費明細書を完成させる

 人によりデータの内容、量、主観が異なるので、手でないとできない。

ヒント
  1. ページ設定は、[印刷プレビュー(全画面表示)]⇒[ページ設定]が便利か...
  2. 「医療を受けた人」,「病院・薬局」の“※※※※ 集計” の “集計” を “計” にするには、
    1. 列を選択し、
    2. [検索と置換](Ctrl+H)で
       ①検索する文字列を “集計”
       ⓶置換後の文字列を “計” とすればよい。
  3. 「治療内容」の“※※※※ 集計” の “集計” は不要であろう。
    1. D列を選択し、
    2. [検索と置換]で
       ①検索する文字列を“ 集計” ‥‥ 先頭に半角スペースが入っている。
       ⓶置換後の文字列を“” ‥‥ 空欄(未入力状態)にする,すなわち、右から左に選択して[Delete]がベスト。
      とすればよい。
  4. 領収書番号のセルの書式 ⇒[配置]は、☑折り返して全体を表示する がお勧め。列幅は、プレビューしながら調節する。
  5. 「病院・薬局」と「治療内容」の医療費が同じ場合、

    「病院・薬局」を直上の行に移し、「病院・薬局」の集計行を削除した方が「行の節約」ができる。
  6. 罫線は[格子]で引いておき、[罫線の削除]で消していった方が楽‥‥ かも。