1 / 6
医療費の入力と集計
まえがき
医療費は一年間の合計を計算し、10 万円を超えていれば税務申告に際して医療費控除を受けることが できます。そこで、医療費を記入するたびに自動集計される仕組みを考えてみましょう。 ここで紹介する「医療費の入力と集計」は、税務申告で必要となる医療費のデータを作成するのに使う ものです。特徴は、ドロップダウンリストから簡便に入力ができ、入力と同時に自動集計されるように してあることです。この資料は Excel 2013 で説明しています。Excel 2007 などでも使えますが、メニ ュー名などは適宜読み替えてください。 このエクセルには、「医療費支出」、「設定」、「集計表」という３つのシートがあります。「医療費支出」 シートと「設定」シートは必須です。「集計表」シートには、Excel 2007 以降のエクセルで導入された 関数が使われており、興味のある方のみ読み進めてください。医療費支出シートの構造
このシートに医療費の支出を記入していくことにします。番号、月日、氏名、病院・薬局、内容、金額 をタイトルとする表を作成することにします。タイトル行は、太字、中央寄せ、背景色を黄色に設定し ておきます。タイトル行のすぐ下 5 行目をクリックしておいて、「表示」タブにある「ウィンドウ枠の 固定」をクリックし、スクロールしてもタイトル行が動かないようにしておきます。 番号 欄は、中央寄せに設定してから、1, 2, 3 と入力したのち、ドラッグして 100 までの数字を入れて おきます。 月日 欄（B5）は、中央寄せに設定してから、セルのプロパティを「日付」に設定し、1 月 20 日なら 1/20 で入力が済むようにします。 氏名 欄は、医療費を使った人の名前 を入力します。"太郎" と "花子" と いう２つの何れかしか入力すること はないと仮定します。このような場 合、「入力規則」を使うのが便利です。 C5 をクリックしておき、「データ」 タブにある「データの入力規則」を クリックします。すると、右に示す ような画面が表示されますから、「設 定」タブにある「入力値の種類」に 「リスト」を選び、「元の値」に「太 郎, 花子」と入力します。 これで、このセルはドロップダウン リストから "太郎" または "花子" を入力できるようになります。この 欄も中央寄せに設定しておきましょ う。2 / 6 次の 病院・薬局 欄は、「氏名」欄に入力された値によって入力すべき値が変わります。太郎の行く病院 と花子の行く病院が必ずしも同じではないからです。 このような違いを「設定」シートで定 義しておきましょう。太郎の行く病 院のリストを「太郎」、花子の行く病 院のリストを「花子」として２つのリ ストが定義されています。このよう に定義しておいて、「医療費支出」シ ートの「病院・薬局」の項（D5）に 「データの入力規則」を設定し、「入 力値の種類」に「リスト」を選び、「元 の値」に =INDIRECT(C5) としてお きます。INDIRECT 関数は、リスト 名に読み替えるのに使います。すな わち、C5 が "太郎" という文字であ れば、「太郎」というリスト名と解釈 する関数です。このようにして、「医 療費支出」シートで「病院・薬局」を 入力するときのドロップダウンリス トが、「太郎」というリストか、「花子」 というリストかが切り替わることに なります。 次の 内容 欄（E5）は、病院でどんな治療を受けたのか、または薬局でどんな薬を購入したのかを記入 する場所です。 「設定」シートで、病院または薬局でどのような処置を受けるかを横書きのリストとして夫々定義して います。リストの定義は、一列に並べようと一行に並べようと同じように扱われます。このようにして おき、上述と同様に、「医療費支出」シートの「内容」の項（E5）に「データの入力規則」を設定し、 「入力の種類」に「リスト」を選び、 「元の値」を =INDIRECT(D5) とし ておきます。こうすることによって、 入力するときドロップダウンリスト が使えることになります。 ただし、この「内容」には、「設定」 シートで定義した以外の文字を記入 したい場合があります。このため、右 に示したように「エラーメッセージ」 タブで「無効なデータが入力された らエラーメッセージを表示する」の チェックを外しておきます。こうす ることで、一旦ドロップダウンリス トから入力した後に手書き修正が可 能になります。通常の「データの入力 規則」では、ここにチェックが入って いるため、設定値以外の入力は受け 付けません。 次に、金額 欄について説明します。
3 / 6 使 っ た 金 額 を 記 入 する場所です。セル の プ ロ パ テ ィ を 右 に 示 し た よ う に 、 「通貨」に設定し、 「記号」に「なし」 を選んでおきます。 また、月日、金額 欄 は、「データの入力 規則」から「日本語 入力」タブを開き、 「 日 本 語 入 力 」 を 「 オ フ （ 英 語 モ ー ド）」に設定してお きましょう。 以上の設定が済ん だら、B5 ～ F5 を ま と め て 下 方 に ド ラッグし、番号 100 ま で の 表 を 完 成 し ます。
集計について
以上で入力個所の準備ができたので、次に集計箇所をセル F2 に作ります。セル F2 のプロパティは、 前述のように「通貨」に設定し、「記号」に「なし」を選んでおきます。 計算式は金額の合計ですから、SUM 関数を使います。セル F2 をクリックしておいて、「数値」タブか ら「オートSUM」の「合計」をクリックすると、下図のように、セル F2 には =SUM() と入ります。 ここで「関数の挿入 fx」をクリックします。4 / 6 すると、右図のよう な 関 数 の 引 数 を 設 定 す る 画 面 が 表 示 されます。 こ の 画 面 は 大 き す ぎるため、図に示し た 箇 所 を ク リ ッ ク し て 引 数 部 分 の み を別画面にします。 右に示したのが、引 数 部 分 の み を 別 画 面 に し た 細 長 い 画 面です。この画面を開いておいて、F5 から F104 までをドラッグすると、図のように F5:F104 とい う引数が入ります。この画面の右端をクリックすると元の画面に戻ります。「OK」をクリックしてこの 画面を閉じると、セル F2 に =SUM(F5:F104) という式が入り、金額欄の集計が求められます。 これで医療費の入力と集計の表が完成しました。余力のある方は、次の集計表シートをお読みください。
集計表シートについて
「集計表」シートでは、「氏名」、「病院・薬局」ごとに集計値を計算しています。「集計表」シートのセ ル D4 について考えてみましょう。ここには、「氏名」が "太郎"、「病院・薬局」が "○○大学病院" と いう２つの条件を満たす「金額」の合計を計算する式が入らねばなりません。 計 算 に 使 う 関 数 は SUMIFS です。こ の SUMIFS 関 数 は Excel 2007 で初 めて導入されたもの で 、 従 っ て Excel 2003 では使うこと ができません。 最初に、「医療費支 出」シートの領域を 定義しておきましょ う。「氏名」欄の入力 個 所 (C5:C104) を 「氏名」、「病院・薬 局 」 欄 の 入 力 個 所 (D5:D104) を 「 病5 / 6 院」、「金額」欄の入力個所 (F5:F104) を「金額」と名前を付けておきます。 次いで「集計表」シートのセル D4 をクリックしておいて、「数値」タブから「数学/三角」の ▼ をク リックして SUMIFS 関数を選びます。すると、セル D4 には =SUMIFS() という式が入り、上図の ような引数を設定する画面が表示されます。 「合計対象範囲」には、「医療費支出」シートの F5:F104、すなわち上で名前を付けた「金額」領域を 指定します。 「条件範囲１」には、「医療費支出」シートの C5:C104、すなわち上で名前を付けた「氏名」領域を指 定します。 結局、すべての引数 を指定すると、右図 に示すようになりま す。 条 件 １ に $B$4 と 指定されていること に 注 意 し て く だ さ い。セルを単に B4 と指定することを相 対 参 照 、$B$4 と $ を 付 け て 参 照 す る方法を絶対参照と 言います。このセル を他のセルにコピー する場合、相対参照 の場合はセルの位置 によって変化しますが、絶対参照の場合は変化しません。 「OK」をクリックすると、セル D4 の式は =SUMIFS(金額,氏名,$B$4,病院,C4) となります。セル D4 を D10 までドラッグして上半分を完成させます。 セル D13 は D4 の式をコピーしておいて、少し修正します。式は、=SUMIFS(金額,氏名,$B$13,病 院,C13) となります。D13 を D22 までドラッグして下半分を完成させます。
シートの保護について
式の書いてあるセルは、迂闊に書き換えられることがないように保護しておきましょう。セルのプロパ ティ画面で「保護」タブを開きます。「ロック」と「表示しない」という２つの項目があるので、その両 方にチェックを付けておきます。 こうしておいて「校閲」タブにある「シートの保護」をクリックすると、小さな条件設定の画面が開か れますが、そのまま「OK」ボタンを押すと、これらのセルはロックされ、数式は非表示になります。 「シート保護の解除」をクリックすると、ロックされていたセルの編集が可能になります。「集計表」シ ートなどは、通常操作することはないので、シートを保護した状態で使ってください。6 / 6
