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