今回は、項目と範囲を指定して合計(SUM)や平均(AVERAGE)を求めるテクニックを紹介していこう。この仕組みは「交差演算子」を活用すると実現できる。Excelに慣れている方でも「まだ交差演算子を使ったことがない・・・」という方は多いかもしれない。便利に活用できるケースもあるので、この機会に使い方を学んでおくとよいだろう。
交差演算子とは?
今回は「交差演算子」の具体的な活用方法を紹介していこう。交差演算子は少し特殊な演算子となるが、使い方次第で便利に活用できるケースもある。もちろん、「関数SUM」や「関数AVERAGE」の引数を交差演算子で指定することも可能だ。
いきなり「交差演算子」と言われても、「どういった話なのか?」を理解できない方が多いだろう。そこで、まずは簡単な例を紹介しておこう。
以下の図は、ある路線の4つの駅について「時間帯別の利用者数」をまとめた表だ。今回は、この表を使って交差演算子の使い方を紹介していこう。
Excelに慣れている方なら、関数SUMの引数に「複数のセル範囲」を指定できることをご存じだろう。たとえば、以下のように「,」(カンマ)で区切って2つのセル範囲を指定すると、「C4:C21」と「E4:E21」にある数値データの合計を求めることができる。
このように関数SUMを記述することで、「梅芝」と「錫見」の2つの駅を合算して「利用者数の合計」を求めることが可能となる。
では、以下の図のように「半角スペース」で2つのセル範囲を区切った場合はどうなるだろうか?
この場合は、2つのセル範囲が「重なる部分」だけが計算の対象になる。上図の場合、「E5:E7」のセル範囲についてのみ合計が算出される。
つまり、「錫見駅」の「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でセル範囲に変換する。
続けて、交差演算子となる「半角スペース」を入力し、2つ目のセル範囲(時間帯に対応するセル範囲)を指定する。こちらは、J11セル(開始セル)とJ12セル(終了セル)で表現できる。これらの間に「:」を挿入するように文字を連結して、関数INDIRECTでセル範囲に変換する。
このように関数SUMを記述すると、2つのセル範囲が「重なる部分」だけを計算の対象にして合計を算出できる。
・1つ目のセル範囲・・・・錫見(E4:E21)
・2つ目のセル範囲・・・・7時~10時(C5:F7)
つまり、錫見駅の7時~10時について「利用者数の合計」を計算できたことになる。
念のため、検算しておこう。E4:E7のセル範囲を選択し、ウィンドウ下部に表示される「合計」の数値を見ると、関数SUMの結果と一致していることを確認できるだろう。
同様の手順で「平均」を求めることも可能だ。こちらは関数名がAVERAGEに変わるだけで、カッコ内の記述は全く同じになる。
それぞれの条件を変更した例も紹介しておこう。たとえば、駅名に「秋姫橋」、時間帯に「17時から20時まで」を指定すると、以下のように「合計」と「平均」が再計算される。
今度は、駅名に「植沢」、時間帯に「11時から16時まで」を指定した例だ。こちらも条件に合致するデータについてのみ、「合計」と「平均」が算出されていることを確認できる。
このように交差演算子を応用すると、「計算するセル範囲」を自在に変更できる計算ツールを作成できる。少し難しく感じるかもしれないが、「交差演算子」や「関数INDIRECT」の基本を踏まえながら手順を追っていけば、その仕組みを理解できるはずだ。色々な用途に応用できるユニークなテクニックとして、覚えておいても損はないだろう。