エクセル2013基本講座：医療費控除のデータを手早く入力するためのシートを作成する

医療費控除用集計シートを作成する	テーブルのセルの設定を行う
テーブルにデータを入力する	手早い入力の手順
入力したデータを集計する	データを並べ替えます
ピボットテーブルを作成します	医療費の明細書を作成する

医療費控除用集計シートを作成する　　　　　 Topへ

このページは平成27年分の申告用に書いたもので、ちょっと古い記事になりました。
令和3年分の医療費集計フォームに関しては
令和3年分確定申告に医療費集計フォーム(平成29年以降分用)を利用する
をご覧ください。
~~令和元年用は　令和元年分確定申告に医療費集計フォーム(平成29年以降分用)を利用する　をご覧ください。~~
確定申告で医療控除を申請する際に1年分の医療費を集計する必要があります。
ある程度の領収書がたまってきたら入力するようにしているのですが、結構手間がかかってしまいます。
そこで、入力が簡単にできるようなExcelのシートを作成しようと思います。
Excelのテーブル機能を利用することでかなり手早く入力ができるのではないかと考え、この姿になりました。
サンプルはExcel2016で作成したものです。ダウンロードして使い勝手を試すことができます。 2017iryou.xlsx　←32kBほどの容量になっています。
平成29年度分は形式が変更されています。入力項目が少なくなっています。ここに書いている一部で済みます
平成29年分以降用の「医療費集計フォーム」を利用する　をご覧ください。

Excelのシートに下図のように、データの見出しなどを入力します。
1～2行目は医療費や補填費の合計を表示する欄になります。途中経過を見ることができます。
1行空けて、4行目からがデータを入力する部分になります。
4行目に、No、支払年月日、医療を受けた人、続柄、病院などの所在地、]病院などの名称、治療内容医薬品名など、支払った医療費交通費、補填される金額・・・といった各見出しを入力します。
5行目以降が入力したデータになります。2行ほど入力しています。
入力するセル範囲をテーブルにします。
A4:I6を選択して、[ホーム]タブの[テーブルとして書式設定]→テーブルのスタイルを選択します。
テーブルとして書式設定　が表示されます。
データ範囲が正しく選択されているのを確認し、
「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して[OK]ボタンをクリックします。
入力する部分がテーブルになりました。(見出し部分にフィルターボタンが表示されます。)
H2:I2セルに計算式を入力します。
H2セルに　=SUM(H5:H6) と入力します。　I2セルには =SUM(I5:I6)　と入力します。
G1:I2セルは見やすいように書式を設定します。
名前ボックスをクリックすると、「テーブル1」という名前がテーブルに設定されているのが確認できます。

テーブルのセルの設定を行う　　　　　 Topへ

C列の「医療を受けた人」はリストから選択して入力するように設定します。
1. Sheet2のB2:C6に医療控除を受ける人の名前と申告する人との続柄を入力して、[ホーム]タブの[テーブルとして書式設定]→テーブルのスタイルを選択します。
  「テーブル2」と名前が定義されました。
2. B2:B6セルを選択します。(見出し部分(家族)を含めて選択します)
  [数式]タブの定義されて名前グループの[選択範囲から作成]を実行します。
  「上端行」にチェックを入れて、[OK]ボタンをクリックします。
3. B3:B6セルに「家族」と名前が定義されました。名前ボックスで「家族」を選択すると確認することができます。
4. Sheet1の　C5:C6セルを選択します。
  [データ]タブのデータツールグループの[データの入力規則]をクリックします。
5. データの入力規則の条件設定の入力値の種類で「リスト」を選択します。
  元の値に　=家族　と入力します。(ここで定義した名前を利用します)
6. C列に入力規則のリストが設定できました。
  C6セルを選択すると、ドロップダウンリストを表示する▼ボタンが表示されます。このボタンをクリックして、リストを表示してもよいのですが、手早く操作するには　[Alt] + [↓]キーを押すとドロップダウンリストが展開されて、[↓]キーで項目を選択することができます。[Enter]キーで選択している項目が入力されます。
D列の続柄は　テーブル2から表引きすることができます。
- D5セルを選択して、　=VLOOKUP(C5,テーブル2,2,FALSE)　と数式を入力します。D6セルへ数式をコピーします。
  - D5:D6セルを選択して、数式を入力して、[Ctrl] + [Enter]で確定すると両セルに入力することもできます。
F列の病院の名称もテーブルにして、リストから入力できるようにします。
1. Sheet2 の　E2:F6セルに病院の名称と所在地を入力します。
  E2:F6セルを選択して、[ホーム]タブの[テーブルとして書式設定]→テーブルのスタイルを選択します。
  テーブル3　と定義されました。
2. E2:E6セルを選択して、 [数式]タブの定義されて名前グループの[選択範囲から作成]を実行します。
  「上端行」にチェックを入れて、[OK]ボタンをクリックします。
3. E3:E6に「病院名」と名前が定義されました。
4. F5:F6セルを選択して、[データ]タブのデータツールグループの[データの入力規則]をクリックします。
  データの入力規則の条件設定の入力値の種類で「リスト」を選択します。
  元の値に　=病院名　と入力します。(ここで定義した名前を利用します)
5. F5:F6セルに入力規則のリストが設定されました。
  「Alt]+[↓] でリストを表示して、[↓]キーで病院名を選択して、[Enter]キーで入力します。
E列の病院などの所在地はSheet2に作成した　テーブル3 から表引きで検索することができます。
1. E5セルに　=VLOOKUP(F5,テーブル3,2,FALSE)　と数式を入力し、E6セルにコピーします。
  病院名のテーブルに「菅原整骨院」が登録されていないため、E6セルには　#N/A エラーが表示されています。
2. 病院名のテーブルに「菅原整骨院」など、必要となった病院名や所在地を追加して入力します。
  テーブルは自動的に拡張されます。テーブルの書式が拡張されるのでわかります。
3. 先ほどの　E6セルのエラーは解消されました。(テーブルの機能で参照範囲が拡張されるため数式の変更は必要ありません。)

テーブルにデータを入力する　　　　　 Topへ

入力するデータ以外で数式で求めるセルが混在しています。
数式の入力されている列は素早く入力するときには邪魔になりますので、グループ化して折りたたんでおきます。
1. D:E列を選択します。(列番号部分をドラッグして選択します)
2. [データ]タブのアウトライングルぷにある[グループ化]を実行します。
3. D:E列がグループ化されました。
  折りたたみのボタンをクリックして、D:E列を折りたたむことができます。
4. D:E列を折りたたみました。この状態で入力すると、手早く操作ができると思います。

手早い入力の手順

まず「支払い年月日」を入力します。1/9　と入力して[Tab]キーを押します。
テーブルが拡張されます。オレンジの枠線が7行目まで伸びたのでわかります。
医療を受けた人の欄がアクティブになりましたので、[Alt]+[↓]キーを押してリストを展開します。[↓]キーを押して「山田一郎」を選択します。
[Enter]キーを押してセルに「山田一郎」を入力します。[Tab]キーを押して右のセルをアクティブにします。
同様に、[Alt]+[↓]、[↓][↓]　とキーを押して、リストの「黒川医院」を選択します。
[Enter][Tab]とキーを押して、右のセルをアクティブにします。
G:H:I列に入力して、[Tab]キーでアクティブセルを右に移動して入力します。
最後に入力したときは[Enter]キーで確定すると、次の行がアクティブになりますので、入力がキーボードだけでスムーズに進みます。
ちなみに折りたたんでいる列もちゃんと計算されていますので、ご心配なく・・・

入力したデータを集計する　　　　　 Topへ

確定申告で医療費控除を受けるには医療費の明細書を作成する必要があります。医療費の明細は下図のような形式になっています。
医療を受けた人、病院や薬局の所在地と名称、医療費の内訳、生命保険や健康保険で補てんされる金額で1行となっています。
つまり、データを入力したら、上記のような構成で集計をする必要があります。
データを並べ替えます
- ピボットテーブルで集計しますので、データを並べ替える必要はありません。この項の操作は不要です。
  結構な労力を使って作成してしまいましたので、残しておきたいと思います。
- この項のデータの支払年月日は1917年となっていますが、2017年の間違いです。もともとダミーの日付なのですが変換を間違って今いました。この項の日付は2017年と脳内変換してご覧ください。申し訳ありません。
- 並べ替えがうまくできない場合は、データの振り仮名情報の有無が考えられますので、次項に書いています[並べ替え]コマンドから実行してみてください。
1. テーブル内のA列「支払い年月日」のセルを選択します。
  [データ]タブの並べ替えとフィルターグループにある[昇順]ボタンをクリックします。
2. テーブル内のF列「病院などの名称」のセルを選択します。
  [データ]タブの並べ替えとフィルターグループにある[昇順]ボタンをクリックします。
3. 医療を受けた人の順番でテーブル内のセルを選択します。
  [データ]タブの並べ替えとフィルターグループにある[昇順]ボタンをクリックします。
- 並べ替えがうまくできない場合はデータの入力時に付け加えられるふりがな情報の有無の違いがあることがあります。
  そのような場合は、[データ]タブの[並べ替え]をクリックして、 [並べ替え]から実行してください。
  [レベルの追加]ボタンを使って最優先されるキー～次に優先されるキーの3つを作成します。
  [オプション]ボタンをクリックして、並べ替えのオプションで「ふりがなを使わない」にチェックを入れます。
  並べ替えのオプションのダイアログボックスの[OK]ボタンをクリックして、並べ替えのオプションのダイアログボックスを閉じます。
  並べ替えのダイアログボックスの[OK]ボタンをクリックして、並べ替えを実行します。

ピボットテーブルを作成します

テーブル内のセルを1つ選択して、[挿入]タブの[ピボットテーブル]をクリックします。
テーブル/範囲が　「テーブル1」になっているのを確認します。
ピボットテーブルレポートを配置する場所を選択してください。で「新規ワークシート」を選択します。
[OK]ボタンをクリックします。
ピボットテーブルのフィールド　で下図のように各項目をドラッグして配置します。
行に「医療を受けた人」「続柄」「病院などの名称」「病院などの所在地」「治療内容、医薬品など」を配置します。
Σ値に「支払った医療費」「補填される金額」の合計を配置します。
- Σ値に配置したとき、合計でなかった場合はクリックして、「値フィールドの設定」を選択します。
  
  ↓
  選択したフィールドのデータで「合計」を選択します。
下図のように集計ができました。
- なお、上図のピボットテーブルでは、ピボットテーブルツールの[デザイン]タブで[小計]→[小計を表示しない]を選択しています。
- また、ピボットテーブルツールの[デザイン]タブで[レポートのレイアウト]→[表形式で表示]を選択しています。

医療費の明細書を作成する　　　　　 Topへ

国税庁のページから医療費の明細書のPDFがダウンロードできます。
https://www.nta.go.jp/tetsuzuki/shinkoku/shotoku/yoshiki02/pdf/003.pdf
ただし、入力ができないのでExcelで似た形式のものを作成してみました。
かなり似ていますので、これで提出してもOK・・・となるのでは・・・といった甘い気持ちがあります。
心配でしたら、正式なものに転記すればよいと思います。
実際は、私は青色申告ソフトで作成していますので、ここで作ったデータ(ピボットテーブル)からコピペしています。
下図では平成27年となっていますが、実は今は平成29年とすべきところでした。間違ってしまっていますがご勘弁ください。

医療を受けた人～補填視される金額の表はピボットテーブルをコピーして貼り付けています。
貼り付けるとき貼り付けオプションで「値の貼り付け」を選択しています。
控除額の計算部分は、下表のような数式を入力しています。
たいして難しい関数を使用しているわけではありません。
強いてあげればD28セルの数式は　=MAX(MIN(C27,100000),0)　として、最小値が 0 なるようにしていますが、=MIN(C27,100000) でよさそうな気がします。

よねさんのExcelとWordの使い方｜エクセル2013基本講座：目次｜テンプレート・アドインなど｜医療費控除のデータを手早く入力するためのシートを作成する