ほんの一部のセルを変えるだけで計算範囲を簡単に変更

INDIRECT関数をマスターすると、エクセルの生産性が飛躍的に向上します。いちいちマウスで範囲指定しなくても、集計の範囲を簡単に変更することができる、といったパワフルな使い方ができてしまうからです。これを業務に生かさない手はありません。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

当ウェブサイトでは、「基本的にはあまり教科書に載っていない、実務から編み出されたエクセルの超絶便利テクニック」を紹介するように努めている。こうしたなか、実務的にどうしても出てくるニーズが、「SUM関数の集計範囲を簡単に変更してみたい」というものだ。いったいどうすれば良いのか。

当ウェブサイトでは以前から、「INDIRECT関数」を「イチオシ」で紹介している。

なぜなら、この関数、実務では「本当に役に立つ」からだ。

たとえば、普段から当ウェブサイトで紹介している、ごくシンプルなSUM関数を例に挙げてみよう(図表1)。

図表1 一般的なSUM関数

ご覧のとおり、単純に、各支店の売上高を合算するだけの、何の変哲もないSUM関数である(なお、単純なSUM関数であれば、キーボード一発で入力可能なショートカットがあるので、下記記事も是非参照されたい)。

【参考】『簡単で超絶強力!SUM関数をキーボードから一発入力

ただ、実務上は何らかの法則性をもってセルのアドレスを特定し、そのセルを間接的に引っ張って来たい、という需要が生じることもある。たとえば、「ワークシートの2列目の2行目から9行目までを合計する」、というニーズだ。

=SUM(B2:B9)

このSUM関数を見てみると、構成要素は次の4つだ。

  • B…計算開始セルの列
  • 2…計算開始セルの行
  • B…計算終了セルの列
  • 9…計算終了セルの行

エクセルの場合はセルを「タテ(行、ROW)」と「ヨコ(列、COLUMN)」で指定するという特徴を持っているが、ここでは先ほどのSUM関数が入力されているセルの下、すなわちB11セルあたりに、ためしにこんな数式を入れてみてほしい。

=SUM(INDIRECT(“B2:B9”))

するとどうだろうか、B10セルと、まったく同じ計算結果が出てくる(図表2)。

図表2 計算結果

いちおう解説しておくと、この「INDIRECT関数」には、ほかの関数に組み込むことで、「間接的に範囲を指定する」という機能がある。

さきほどの図表1くらいのシンプルな図表なら大して威力を発揮しないが、シートが複雑化するに従い、次第にその威力を発揮し始める。

たとえば、先ほどのシートを、各支店の2018年から2020年までの売上高を示すものの一部だったとしよう(図表3)。

図表3 各年の売上高シート

具体的にはB列の2~9行目に2018年の、C列の2~9行目に2019年の、D列の2~9列目に2020年の、それぞれ支店別売上高を入力しておく。

次に、行と列を自分自身で指定すべく、仮にB12~B15にSUM関数の「開始列・開始行、終了列・終了行」を手動で入力できるようにしてこう。

そのうえで、B17セルあたりにこんな計算式を入力してみてほしい。

=SUM(INDIRECT(B12&B13&”:”&B14&B15))

この数式の意味は、「B12の値」「B13の値」「:(コロン)」「B14の値」「B15の値」を連続させた文字列を生成し、それを「SUM関数」で参照させる、というものである。この例だと「=SUM(B2:B9)」を入力したのと同じ効果をもたらす(図表4図表5)。

図表4 INDIRECT関数の実践

図表5 その計算結果

いかがだろうか。図表5のB17セルが、図表2の計算結果と一致していることが確認できるだろう。

そのうえで、たとえばB14の値を「b」から「d」などに変更してやると、「SUM関数」の範囲が自動的に変更される、というわけだ(図表6)。

図表6 計算範囲の変更

すなわち、これをうまく活用すれば、いちいちSUM関数を入力し直さなくても、可変セル(図表4~6の事例でいえば、B12~B15セル)の設定を変更するだけで、簡単に計算結果を変更することが可能、というわけだ。

こうした基本形をカスタマイズしたうえで、是非、実務でも実践していただきたい。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. より:

    INDIRECT関数はセルの参照を返してくれるのはわかっていましたが、主にMATCH関数と組み合わせて特定行のセル値を取得するのに使用していました。
    こういった使い方もあるのですね。勉強になりました。

    Excelの関数って単独でもそれなりに使用できますが、組み合わせて使うことでいろいろなことが出来ますし、部品として物足りないものはありますが実装されているものはよく考えられていると思いました。関数単体だけでなく組み合わせも考えてマイクロソフト社に要望してみたいと思いました。
    ありがとうございます。。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。