-
よねさんのExcelとWordの使い方 »
-
エクセル2013基本講座：目次 »
-
テンプレート・アドイン »
-
確定申告の医療費控除の集計表をExcelで作成する
確定申告の医療費控除の集計表をExcelで作成する Topへ
- このページは古くなりました。
令和3年分の医療費集計フォームに関しては
令和3年分確定申告に医療費集計フォーム(平成29年以降分用)を利用する
をご覧ください。
このページは平成27年分の申告用に書いたもので、ちょっと古い記事になりました。
令和元年用は 令和元年分確定申告に医療費集計フォーム(平成29年以降分用)を利用する をご覧ください。
平成29年度分は形式が変更されています。入力項目が少なくなっています。ここに書いている一部で済みます
平成29年分以降用の「医療費集計フォーム」を利用する をご覧ください。
- 医療費集計フォームを国税庁のページからダウンロードして利用することができます。→ 確定申告の医療費控除の計算に集計フォームを利用する
しかし、入力してみるとちょっと使いづらく感じます。具体的に書くと日付がバラバラに入力する必要がある とか病院名と住所はセットで入力できないといった感じになります。
- 確定申告で医療費控除を受けるには医療費の明細書を作成する必要があります。医療費の明細は下図のような形式になっています。
医療を受けた人、病院や薬局の所在地と名称、医療費の内訳、生命保険や健康保険で補てんされる金額で1行となっています。
- つまり、これらの項目が入力できるシートをExcelで作成すれば良いことになります。
下図のように作成し、あとは入力が容易にできるように機能を加えればよいことになります。
- 医療を受けた人や病院名称などはリストから選択できるようにすると便利かと思われます。
入力規則のリストを利用してみます。
- Sheet2に医療を受けた人と病院などのリストを作成します。
- 病院名と家族のセルを選択します。
[数式]タブの定義された名前グループの[選択範囲から作成]を実行します。
- 「上端行」にチェックを入れて[OK]ボタンをクリックします。
病院名と家族の名前を定義しました。
- C5セルを選択して、[データ]タブの[データの入力規則]→[データの入力規則]を実行します。
- 「設定」タブで入力値の種類で「リスト」を選択します。
元の値に「=家族」と入力します。
[OK]ボタンをクリックします。
- 同様に、F5セルを選択して、入力規則を設定します。
入力値の種類を「リスト」にし、元の値には「=病院名」とします。
- D5セルの続柄はSheet2を参照すればよいので、
=IFERROR(VLOOKUP(C5,Sheet2!$D$2:$E$5,2,FALSE),"") と入力します。
- E5セルの病院の所在地についてもSheet2の表を参照すればよいので、
=IFERROR(VLOOKUP(E5,Sheet2!$B$2:$C$7,2,FALSE),"") と入力します。
- H2セルとI2セルの合計はそれぞれ =SUM(H5:H200) と =SUM(I5:I200) としました。
- 病院名を追加するときは？
- 病院名及び住所をSheet2に入力します。
- [数式]タブの[名前の管理]を実行します。
- 名前の「病院名」を選択して、[編集]ボタンをクリックします。
- 参照範囲を入力した範囲と一致するように修正します。
[OK]ボタンをクリックします。
- 名前を定義したセル範囲を自動的に変化させる方法を利用することもできます。
すると、上記のように手動で病院名のセル範囲を変更する必要がなくなります。
参照範囲を =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B$2:$B$100),1) とします。
- 病院名を追加すると、VLOOKUP関数を使って参照しているセル範囲を修正する必要が出てきます。
F5セルは =IFERROR(VLOOKUP(E5,Sheet2!$B$2:$C$9,2,FALSE),"") といった具合になります。
これは名前を追加するたびに数式を変更するのは面倒・・・という方は数式を検討してみましょう。
病院名のセル範囲は修正したのですから、数式を下のように変更することで修正の必要がなくなります。
=IFERROR(OFFSET(Sheet2!$C$1,MATCH(E5,病院名,0),0),"")
よねさんのExcelとWordの使い方｜エクセル2013基本講座：目次｜テンプレート・アドインなど｜確定申告の医療費控除の集計表をExcelで作成する