「家計簿77から作る医療費明細書作成」の最終回を 3月26日AM に予定している。
来年になったらすっかり忘れてしまっている‥‥ かも知れない。で、(1)前提条件を明確にし、(2)マクロで出来るところはマクロ化した。
完成予想図は、2016.02.17のブログ「家計簿77で「医療費明細書」をExcel で仕上げた‥‥ が、税務署がウンと言うかは別」(2月29日、確定申告は無事終了した)と同じで下図。
≪基本的な考え方≫
- 医療費明細書用の Excelファイルは、家計簿77の総収支・シートをコピーして作る。
- ファイル名は任意であるが、「医療費明細書yyyy」が望ましい。
- コピー後、マクロ家計簿77を呼び出すボタンを全て削除する。
- 医療費明細書専用の「マクロ医療費明細.xlsm」を作る。
- 医療費明細書yyyy を開いた後、「マクロ医療費明細」を使うためのボタンを作る。
- 医療費明細書は個人差(家計簿77の入力内容)が大きいく、マクロのみで作ることは困難なので、必要な都度マクロを起動する方式の「マクロ集」とする。
- 「マクロ医療費明細」は「マクロ家計簿77」と同じ手順でダウンロード出来る。
- 現在のファイル名は「マクロ医療費明細v00.zip」
§1.医療費明細書ファイルの準備
- 家計簿77の総収支・シートをコピーして、「医療費明細書yyyy」ファイルを作る
- 家計簿77(通常であれば昨年分)を開く。
- 総収支・シートをアクティブにする。
- シート見出しを右クリック ⇒[移動またはコピー]をクリックする。
- 移動先ブック名:(新しいブック)
☑コピーを作成する ‥‥ 必須!!! - [OK]をクリックすると、新しいブック,Book* が出来る。
- (裏にある)家計簿77 を閉じる。
- もし、「変更を保存しますか?」が出たら[保存しない]にする。
- 総収支シートの全てのボタンを削除する
- [ホーム]タブ/編集Gr.の[検索と選択]⇒[条件を選択してジャンプ]をクリックし、
- ◉オブジェクト を選択して[OK]。
- すべてのボタンが選択されるので、[Delete]する。
- この操作により、「マクロ家計簿77」とは縁が切れたことになる。
- Book* を[名前を付けて保存]する
- 保存場所は 家計簿77があるフォルダー(家計簿77)と同じ場所がベスト。
- ファイル名は任意であるが、「医療費明細書yyyy」がベター。
- 保存後、タイトルバーで名前を確認する!
§2.明細書1・シート(医療費明細書の原始データ)の作成
- 「医療費明細書yyyy」の総収支・シートをアクティブにする。
- [フィルター]をオンにする。
- 「費目」より医療費明細対象の費目を選択する。
- 合計の支出金額を控える...
- 支出の列を選択すると、ステータスバーに合計が表示される‥‥ かも。
- 今からデータを加工してゆくが、最後になっても合計金額は変わらないはず。
- データを全て選択し、(セルA1を選択し、Ctrl+A)
- クリップボードにコピー(Ctrl+C)する。
- 新しいシートのセルA1 に貼り付ける。(Ctrl+V)
- 貼り付けたら、シート名を “明細書1” に変更する。
- “明細書1”の“1”は半角とします。以下、同じ。
∵マクロで使用しているためで、全角にするとエラーになる。 - 列幅を調節する。(任意)
- “明細書1”の“1”は半角とします。以下、同じ。
ヒント
初回時のみ、次を行う。
- 総収支・シートにマクロを[有効にする]ボタンを作る
- 総収支・シートをアクティブにする。
- フィルターはオフ、選択も解除しておく。
- 「マクロ医療費明細.xlsm」を開く。
- [マクロの表示(Alt+F8)]する。
- [〜マクロを有効にするボタンの作成]を選択し、
- [実行]する。
- セルB1に[マクロを有効にする]ボタンが出来る。
- 総収支・シートをアクティブにする。
- [上書き保存(Ctrl+S)]する。
§3.明細書2・シート(医療費明細書と同じレイアウト)の作成
- マクロ名:〜医療費明細レイアウトを作る
- マクロは「画面が見えない」ので、1列ずつコピペする方法を採っている。
列番号 | 申告項目 | 家計簿77の項目 |
---|---|---|
A | 医療を受けた人 | D列(費目) |
B | 続柄 | なし(列挿入) |
C | 病院・薬局などの所在地・名称 | C列(品名) |
D | 治療内容・医薬品名など | 備考 備考 中の最初の“。”の前までを切り取って作る。 |
E | 支払った医療費 | F列(支出) |
F | 生命保険や社会保険などで補填される金額 | E列(入金) |
G | なし(枚) | なし。 列を挿入し、初期値として「すべて1」を入れる。 |
H | なし | 通し番号 |
-
- 明細書1・シートより「家計簿77の項目」で、明細書2・シートを作る。
- 例えばA列、マクロではD列を移す。(“費目”は探さない)
- 「通し番号」は“通し番号”という名の列を探して移す。
- 項目名は家計簿77と同じ(無い場合は申告項目)とする。
- 明細書1・シートより「家計簿77の項目」で、明細書2・シートを作る。
- 明細書1・シートをコピーし、明細書2・シートを作る。
- 不要な列を削除する
- 必要な列は、「品名」「費目」「入金」「支出」「通し番号」「備考」
上記以外の列は削除する。
- 必要な列は、「品名」「費目」「入金」「支出」「通し番号」「備考」
- 列を入れ替え、「医療費の明細書」と同じレイアウトにする(太字が項目名)
- 例えば「費目」列を選択し、
- 右クリック ⇒[切り取り]をクリックする。
- A列を選択し、
- 右クリック ⇒[切り取ったセルの挿入]する。
ヒント
- 通し番号のセルの書式設定は、下記がお勧め。
- 表示形式の “0000-000” は変更不可。
- 配置タブ/横位置は「標準」とし、文字の制御のすべてのチェックを外す。
- 完成したら、上書き保存する。(任意)
§4.明細書2・シートを集計順に並べ替える
- マクロ名:費目品名備考通し番号で並べ替える
§5.データの整合性を図る
これが全てを精度を決める重要な作業となる。(マクロでは決して出来ない)
- 品名(病院・薬局)の整合性
- 基本的には、領収書に書かれているとおりがベストであろう。
まりふのひとの場合、- 「クスリ岩崎チェーン」と「くすり岩崎チェーン」があり、後者に統一した。
- 「周東病院」は「周東総合病院」に統一した。
- 基本的には、領収書に書かれているとおりがベストであろう。
- 備考(治療内容・医療品名)の整合性
- 表現を統一する。長いと印刷設定で苦労する。
- 歯科医院の場合は “治療” でOK?
- 内容を修正したら、再度、並び替える。‥‥ 必須!!!、手を抜かない!
§6.集計する
- マクロ名:費目品名備考別に集計する
集計(合計)は、①医療を受けた人、②病院・薬局など、③治療内容 で行うことになる。
- [小計]する(1回目)
- グループの基準:費目
- 集計の方法:合計
- 集計するフィールド:☑支出、☑入金、☑枚
- ☑現在の小計をすべて置き換える
☑集計行をデータの下に挿入する
- [OK]
- [小計]する(2回目)
- グループの基準:品名
- ☐現在の小計をすべて置き換える
- [OK]
- [小計]する(3回目)
- グループの基準:備考
- [OK]
- 列幅を調節する(任意)
§7.集計行に通し番号を貼り付ける
- マクロ名:〜集計行に通し番号をセットする
- データの整合性を図るため、再度、集計し直す。
次の処理で「集計行」のみにする。
そうすると「通し番号」が隠れてしまうので、集計行の通し番号のセルに「通し番号」を貼り付けるもの。手動ではできない...(たぶん)
§8.医療費明細書のヘッダーを作り、集計行を貼り付ける
- マクロ名:〜明細書Hシート作成
- 明細書ヘッダーのセルA1には、明細書1・シートのセルB2の「年」を取ってきている。日付は1月1日。
医療費明細書のヘッダー部分を作り、その下に、明細書2・シートの集計行を貼り付ける。
- マクロ医療費明細の明細書H・シートをコピーする
- 「マクロ医療費明細」を前に出す。
- 明細書H・シートをアクティブにする。
- シート見出しを右クリック ⇒[移動またはコピー]する。
- 【シートの移動またはコピー】ダイアログボックスで、
- 移動先ブック名:医療費明細書yyyy
- 挿入先:(末尾へ移動)
- Rコピーを作成する(必須)
- 設定を確認して[OK]する。
- 明細書2・シートの集計行のみをコピーし、明細書H・シートのヘッダーの下に貼り付ける
- 明細書2・シートをアクティブにする。
- 表示レベル[4]をクリックする。
- セルA1を選択し、
- 全て選択する。(Ctrl+A)
- 可視セルのみ選択する。(Alt+;)
- リボンでは、[ホーム]タブ/編集Gr.の[検索と選択]⇒[条件を選択してジャンプ]⇒8可視セル ⇒[OK]
- コピーする。(Ctrl+C)
- 明細書H・シートのセルA6に貼り付ける。(Ctrl+V)
- 明細書ヘッダーと項目の対応を確認し、第6行を行削除する。
§9.医療費明細書を完成させる
人によりデータの内容、量、主観が異なるので、手でないとできない。
ヒント
- ページ設定は、[印刷プレビュー(全画面表示)]⇒[ページ設定]が便利か...
- 「医療を受けた人」,「病院・薬局」の“※※※※ 集計” の “集計” を “計” にするには、
- 「治療内容」の“※※※※ 集計” の “集計” は不要であろう。
- D列を選択し、
- [検索と置換]で
①検索する文字列を“ 集計” ‥‥ 先頭に半角スペースが入っている。
⓶置換後の文字列を“” ‥‥ 空欄(未入力状態)にする,すなわち、右から左に選択して[Delete]がベスト。
とすればよい。
- 領収書番号のセルの書式 ⇒[配置]は、☑折り返して全体を表示する がお勧め。列幅は、プレビューしながら調節する。
- 「病院・薬局」と「治療内容」の医療費が同じ場合、
「病院・薬局」を直上の行に移し、「病院・薬局」の集計行を削除した方が「行の節約」ができる。 - 罫線は[格子]で引いておき、[罫線の削除]で消していった方が楽‥‥ かも。