［図1］SUMIF 関数で分類別支出合計を自動的に計算
以下の説明では前回のサンプルデータを使いますので、 前回授業時にサンプルデータを保存していない場合は、 以下のリンクからサンプルデータを保存してください。
まず、前回のサンプルで「残高」を計算する式を考えて、収支表を完成させましょう。 ここで、計算式は「支出」と「収入」のどちらになっている行でも機能す るものを考えて下さい。
ところで、同じような計算式を何度も入力するのは面倒です。 考えた計算式を一行分だけ入力して、そのセルをマウスで選択してください。 そしてそのセルの右下端辺りに矢印の先端を持っていってみましょう。 矢印のかたちが変わったはずです。 そうしたらマウスを下の方向にドラッグしてみてください。 Excel には自動的に次のセルに入力すべき値を推測して入力するという便利な機 能があります。 これを「オートフィル」と呼びます。
なお、一番最初の式を考える時には、空白のセルは 0 が入っているのと 同じ扱いになるということがヒントになります。
ちなみに「オートフィル」とは、連続したデータを連続したセルに自動的に入れることができる機能です。 連続した数字を自動的に入れることができるのですが、連続したデータなら月の名前や曜日なども自動的に入れることができます。 詳しくは、「オートフィル」機能で繰り返しのデータ入力を自動化 & 生産性アップ! - Microsoft for businessをご覧ください。
「SUMIF 関数」とは、 SUM=「和」、IF=「もし…ならば」という名が示すように、 指定された範囲内で、ある条件に一致するデータのみの合計を計算する関数です。
［図1］のように、 一番目の引数の範囲（青）の中から二番目の引数（緑）に一致するものを探し、 三番目の引数の範囲（赤）から対応するデータを自動的に取り出して合計します。 この関数を使えば、 ひとつひとつセルを選択しながら足していった計算が一気に出来ることがおわかりかと思います。
［図1］SUMIF 関数で分類別支出合計を自動的に計算
Excel の関数は非常に種類が多く、 また専門的な用途の関数もたくさん含まれているため、 この授業で概要を示すことは無理です。 「関数を挿入」ダイアログの分類別一覧を見ながら、 面白そうな関数を探していろいろと試行錯誤してみることをお勧めします。
また、貼り付け操作や簡単な関数を用いたサンプルファイルも用意しましたので、参考にしてみてください。
上で紹介した SUMIF 関数、 これは便利だということで、 コピーして「食費」以外の項目についても計算してみようとすると［図2］のように、 参照すべき範囲がずれてしまいます （数式バーをクリックすると、参照元の範囲を確認できます）。 今回の例では書籍費の部分で計算が間違ってしまします。 なぜこんなことになってしまうのでしょう?
［図2］SUMIF 関数を単純にコピーしてもうまくいかない
この現象を理解するためには、Excel における参照の仕組みを知る必要があります。 まずはたとえ話から入りましょう。
教室で、出席者全員に何かを書いてもらうとします。 一人一枚紙を配って個別に回収するならば、 書き終わったら教卓に提出してください」と指示すればうまくいきます（［図3］左）。 一方、横方向に同じ列の人は同じ紙に書いてもらうならば、 右端の列に紙を配っておいて「書き終わったら左隣へまわして下さい」と指示すればよいでしょう（［図3］右）。
このように、 作業の目的によっては「教卓」のように教室内の特定の場所を指定した方がうまくいく場合と、 「左隣」のようにそれぞれの座席から見た位置を指定した方がうまくいく場合とがあります。 教室内の特定の場所を指す指定方法を「絶対参照」、 それぞれの座席から見た場所を指す指定方法を「相対参照」といいます。
［図3］セルを参照する二つの方法
たとえ話はこれくらいにして、Excel の話に戻りましょう。 ［図2］を注意深く見ると、 SUMIF 関数を三つ下のセルにコピーすることで、 関数が参照しているセルも三つ下に移動していることがわかります。 つまり、［図2］では「SUMIF 関数が入力されているセルから見て」 参照先がどこにあるかという相対参照がおこなわれているわけです。 このように、Excel の参照は、 何も指定しなければ相対参照として解釈されます。
実は、相対参照の考えは、この授業の中ですでに出てきています。 先程、残高を計算した時、残高の一番上に式を入力してもらい、それをオートフィルで入力してもらいました。 実は、この操作はコピー操作の一種です。 実際、「オートフィル」の機能を使わずに一つずづ貼り付けていっても同じ結果を得ることができます。 では、コピー操作をしているのに、同じ数式が貼り付けられるのではなく、 勝手に数式が変わってしまうのはなぜなのでしょうか。
これは、数式をコピーする時、 コピーされるのは、「F4」のような絶対的なセルの位置ではなく、 例えば「左にいくつ、上にいくつ」というように相対的な位置なのです。 ですので、コピー操作を行うと「F4」という絶対的な位置の情報は変更され、 相対的な「左にいくつ、上にいくつ」という相対的な位置の情報は変更されずに残ることになります。 残高の式もコピーをしても、相対的な位置は変わりませんね。
数式の中に「F4」と書かれているので、 絶対的な「F4」という位置がコピーされるような気がしますが、 実は、相対的な位置の情報がコピーされるのです。
では、Excel で絶対参照を用いたいときはどうすればよいのでしょうか。 これは、「$」という記号を使う、というのがその答えです。 前回の授業で、セルを参照するときは「C17」のように列と行を指定するということを説明しました。 この書き方では、行と列ともに相対参照として解釈されます。 列を指定する「C」、行を指定する「17」にそれぞれ「$」をつけると、 「$」がついてある行/列に対する絶対参照として解釈されます。
文章で説明してもわかりにくいと思いますので、具体例を図で説明しましょう。 「$C17」のように、列を指定する「C」に「$」を付けたとします［図4］。 そうすると、この参照は常に「列C」を指しますので、 ［図4］で「$C17」を参照しているセル1 を 2、3 の位置へコピーすると、 それぞれ「列C の中で対応する行のセル」に対する参照と解釈されます。
［図4］列を絶対参照する
続いて「C$17」のように、行を指定する「17」に「$」を付けてみましょう［図5］。 この場合は参照先が常に「行17」から選ばれますので、 ［図5］のように「C$17」を参照しているセル1 を 2、3 の位置へコピーすると、 「行17 の中で対応する列のセル」への参照と解釈されます。 なお、セル1 の参照が、列方向に「ひとつ左隣の列」を参照しているので、 コピー先 2、3 でも（行17 で）ひとつ左隣の列を参照していることに注意してください。
［図5］行を絶対参照する
［図2］の SUMIF 関数の例では、 「支出の分類」と「支出」のデータ範囲は変化しませんから、 行列ともに絶対参照したいところです。
このように行列ともに絶対参照したい場合は「$C$17」のように両方に「$」を付けます。 この「$」を付ける作業は、キーボードから「$」を直接入力してもよいのですが、 数式バー上で参照を変更したい部分をクリックして、 キー入力出来る状態にしてからキーボードの「F4」キーを押すという方法も使えます。 「F4」を続けて押すと「行列とも絶対参照」→「行だけ絶対参照」→ 「列だけ絶対参照」→「行列とも相対参照(最初の状態)」と変化します。
［図6］SUMIF の引数を行列ともに絶対参照にする
絶対参照を使って、SUMIF 関数の引数を書き直した例が［図7］です。 ［図2］のように三つ下へコピーしても、意図したとおりに正しく参照されています。
［図7］正しく参照がコピーされた SUMIF 関数
この相対参照と絶対参照という考えはなかなか分かりにくいと思います。 よく分からなかったら、ひとまず以下のように覚えておいて下さい。