セル範囲を変更しながら「合計」や「平均」を求める

交差演算子とは？

今回は「交差演算子」の具体的な活用方法を紹介していこう。交差演算子は少し特殊な演算子となるが、使い方次第で便利に活用できるケースもある。もちろん、「関数SUM」や「関数AVERAGE」の引数を交差演算子で指定することも可能だ。

• 

  交差演算子を活用した合計（SUM）と平均（AVERAGE）

いきなり「交差演算子」と言われても、「どういった話なのか？」を理解できない方が多いだろう。そこで、まずは簡単な例を紹介しておこう。

以下の図は、ある路線の4つの駅について「時間帯別の利用者数」をまとめた表だ。今回は、この表を使って交差演算子の使い方を紹介していこう。

• 

  各駅の時間別の利用者数をまとめた表

Excelに慣れている方なら、関数SUMの引数に「複数のセル範囲」を指定できることをご存じだろう。たとえば、以下のように「,」（カンマ）で区切って2つのセル範囲を指定すると、「C4:C21」と「E4:E21」にある数値データの合計を求めることができる。

• 

  離れたセル範囲の合計

このように関数SUMを記述することで、「梅芝」と「錫見」の2つの駅を合算して「利用者数の合計」を求めることが可能となる。

では、以下の図のように「半角スペース」で2つのセル範囲を区切った場合はどうなるだろうか？

• 

  2つのセル範囲を「半角スペース」で区切って記述

この場合は、2つのセル範囲が「重なる部分」だけが計算の対象になる。上図の場合、「E5:E7」のセル範囲についてのみ合計が算出される。

• 

  関数SUMの計算結果

つまり、「錫見駅」の「7時代から9時代まで」に限定して利用者数の合計を求める、ということになる。「7時代から9時代まで」というのは少し理解しにくい表現なので、「7時～10時の利用者数」と考えてもよいだろう。

このように2つのセル範囲を「半角スペース」で区切って記述すると、それぞれのセル範囲が「重なる部分」だけを計算の対象に指定できる。そして、この半角スペースのことを「交差演算子」と呼ぶ。

なお、先ほどの例を見たときに、「わざわざ2つのセル範囲を指定するのは面倒では？」とか、「もっと単純に、E5:E7とセル範囲を指定したほうが簡単！」と感じた方もいるだろう。確かにその通りである。

交差演算子はそのまま利用するものではなく、「関数INDIRECT」と組み合わせて利用するのが一般的だ。こうすることで「絞り込み条件」を指定できる計算ツールを作成できる。具体的な例で紹介していこう。

2つのセル範囲を手軽に指定する方法

交差演算子を利用するときは、「2つのセル範囲」を効率よく指定できるように工夫しておく必要がある。

ということで、ここでは条件に「駅名」と「時間帯」を指定して合計（SUM）と平均（AVERAGE）を求める計算ツールを作成してみよう。以下の図のように「条件を入力するセル」を用意する（背景が黄色のセル）。

• 

  計算条件の指定

続いて、それぞれの「条件に対応するセル範囲」を自動指定できるように準備を進めていく。

まずは、それぞれの「駅名に対応するセル範囲」を簡略化していこう。このような場合は、「各駅のデータ」が記録されているセル範囲に「名前」を付けてあげるとよい。たとえば「梅芝駅のデータ」のセル範囲に名前を付けるときは、以下のように操作する。

（1）「梅芝駅のデータ」が記録されているセル範囲を選択する。

• 

  セル範囲の選択

（2）セル範囲を選択できたら「名前ボックス」をクリックし、「駅名」と同じ名前を入力する。

• 

  セル範囲に名前を定義

上記のように操作すると、C4:C21のセル範囲を「梅芝」という文字で指定できるようになる。同様の手順で、D4:D21のセル範囲に「秋姫橋」、E4:E21のセル範囲に「錫見」、F4:F21のセル範囲に「植沢」という名前を付けていく。

• 

  それぞれのセル範囲に「駅名」の名前を定義

これで各駅のデータ範囲を「駅名」で指定できるようになった。次は「時間帯に対応するセル範囲」について準備を進めていこう。こちらは少し複雑なので、セル範囲の「開始セル」と「終了セル」を個別に求めていく。

今回の例では、「6時～」のデータが4行目に入力されている。同様に、「7時～」のデータは5行目、「8時～」のデータは6行目、・・・という具合に「時刻」と「N行目」が2行ずれて配置されている。

つまり、「時刻」から2を引き算すると、該当するデータの「行番号」に変換できる訳だ。データは「C列目」から入力されているので、時間帯を示すセル範囲の「開始セル」は「="C"&(H7-2)」で指定できる。

• 

  「時間帯のセル範囲」の開始セル

念のため、「&」（アンド）について補足しておこう。「&」は前後の文字を結合する演算子となる。上図の場合、"C"の文字に(H7-2)の数字を結合する、という処理になる。現時点では開始時刻（H7セル）に7が入力されているので、(H7-2)＝5となる。よって、"C5"という文字列が出力されることになる。

「終了セル」の考え方も基本的に同じだ。ただし、「X時まで」の範囲に「X時～」のデータは含まれない、ということに注意する必要がある。

たとえば、条件が「10時まで」の場合、「9時～」の行までをセル範囲に指定する必要がある。よって、行番号を示す数式は(J7-3)になる。あとは、この前に"F"（データの末尾の列）の文字を「&」（アンド）で結合するだけ。これで「終了セル」を求められる。

• 

  「時間帯のセル範囲」の終了セル

それぞれの結果を見てみると、現時点の条件（7時から10時まで）について、セル範囲の「開始セル」と「終了セル」を正しく指定できていることを確認できるだろう。

• 

  時間指定のセル範囲

あとは、これら「2つのセル範囲」を交差演算子（半角スペース）で関数に指定するだけ。すると、条件に合致するデータについてのみ「合計」や「平均」を求めることが可能となる。

交差演算子を活用した関数SUMと関数AVERAGEの入力

K5セルに指定した「駅名」、ならびに「時間帯のセル範囲」を示すJ11、J12のセルは、いずれも文字列としてデータが記録されている。よって、そのまま関数SUMの引数に指定することはできない。

前回の連載で紹介したように、「文字列」を「セル範囲」として扱うには関数INDIRECTで変換しておく必要がある。こちらも順番に説明していこう。

1つ目のセル範囲（駅名に対応するセル範囲）は、「K5セルの文字」で指定できる。ただし、そのままでは文字列として扱われてしまうので、関数INDIRECTでセル範囲に変換する。

• 

  「1つ目のセル範囲」の指定

続けて、交差演算子となる「半角スペース」を入力し、2つ目のセル範囲（時間帯に対応するセル範囲）を指定する。こちらは、J11セル（開始セル）とJ12セル（終了セル）で表現できる。これらの間に「:」を挿入するように文字を連結して、関数INDIRECTでセル範囲に変換する。

• 

  「2つ目のセル範囲」の指定

このように関数SUMを記述すると、2つのセル範囲が「重なる部分」だけを計算の対象にして合計を算出できる。

　　・1つ目のセル範囲・・・・錫見（E4:E21）
　　・2つ目のセル範囲・・・・7時～10時（C5:F7）

つまり、錫見駅の7時～10時について「利用者数の合計」を計算できたことになる。

• 

  関数SUMにより算出された合計

念のため、検算しておこう。E4:E7のセル範囲を選択し、ウィンドウ下部に表示される「合計」の数値を見ると、関数SUMの結果と一致していることを確認できるだろう。

同様の手順で「平均」を求めることも可能だ。こちらは関数名がAVERAGEに変わるだけで、カッコ内の記述は全く同じになる。

• 

  関数AVERAGEの入力

• 

  関数AVERAGEにより算出された平均

それぞれの条件を変更した例も紹介しておこう。たとえば、駅名に「秋姫橋」、時間帯に「17時から20時まで」を指定すると、以下のように「合計」と「平均」が再計算される。

• 

  「秋姫橋駅、17～20時」の合計と平均

今度は、駅名に「植沢」、時間帯に「11時から16時まで」を指定した例だ。こちらも条件に合致するデータについてのみ、「合計」と「平均」が算出されていることを確認できる。

• 

  「植沢駅、11～16時」の合計と平均

このように交差演算子を応用すると、「計算するセル範囲」を自在に変更できる計算ツールを作成できる。少し難しく感じるかもしれないが、「交差演算子」や「関数INDIRECT」の基本を踏まえながら手順を追っていけば、その仕組みを理解できるはずだ。色々な用途に応用できるユニークなテクニックとして、覚えておいても損はないだろう。