Excelの荒技（あらわざ）
今日は、Excelの荒技についてである。
私は、結構、仕事でExeclを使っているので、Excelについては、関数まわりを中心に相当の使い手である（と思っている。）
その私がお薦めする２つのテクニックを紹介する。
1つ目が「配列数式」、
2つ目が、複数シートでできたブックを１つの文書としてまとめて、割付印刷したり両面印刷する方法である。
これらを選択した観点は2つある。
- 実用的で役に立つ
- まだ、あまり人に使われていない
私の職場では、全員Excelは使えると思うのだが、それでも私以外の人が今回紹介する方法を使っているのを見たことがない。
まず1つ目の配列数式であるが、ひとことで言えば、式の中に配列（A1:A3 など連続した複数のセル参照）を使う方法である。こうすれば、「配列の1つ1つに対して式を適用した結果を最後に合計する」など、通常だと中間結果を一旦書き込むセルなどを用意しなければならないような複雑な計算でも、一気に答えを求めることができる。
配列数式の詳しい情報は、「配列数式」でググれば、たくさん出てくる。
最初にでてくる
に配列数式の書き方や使用例などが相当判りやすく書かれているので、今さら、それ以上の説明をここで書こうとは思わない。
ここでは、とりあえず配列数式を使った例（例自体は小規模なサンプルであるが、使い方は私がよく実務で使っているものから持ってきた）を紹介して便利さをわかってもらい、一人でも多くの人に使おうという気になってもらいたいと思っている。
まず、例で扱うデータである。8名の生徒の国語と算数の点数の表である。
ここでは、後の例で計算式を見やすくするために、性別、クラス、国語、算数の各列を、それぞれの名前で登録している。例えば、「クラス」は「$C$2:$C$9」である。この「名前の登録」は、配列数式とは直接関係ないので、以下の例で「クラス」と書く代わりに「$C$2:$C$9」と書いても何も問題ない。（私自身も通常、名前登録はしていない）
さて、いきなり、使用例を挙げる。
見ればわかるように、性別別、クラス別の両教科の平均点を計算している。ここで、ピンク色のセルの数式は
{=AVERAGE(IF((性別=$A16)*(クラス=$B16),国語))}
である。（他のセルは、ピンク色のセルをコピーすればよい。これ以降の例でも同様である。）
ここで、最初と最後の{}は、キーボードで入力するのでなく、
=AVERAGE(IF((性別=$A16)*(クラス=$B16),国語))
と入力した後に、Control + Shift + Enter （３つのキーを同時に押す）ことで自動的に前後に {} が付く。これをしないと、配列数式は正しく動作しないかエラーとなる。（私も配列数式を最初に知ったときに、この特殊な入力方法に驚いた。）
簡単に式を解説すると、IF関数の中は、『性別が$A16（1行目で言えば「男」）、かつ、クラスが$B16（1行目で言えば「梅」）に等しければ、国語 の値』という意味だ。それを、AVERAGE関数で囲んでいるので、全体の意味としては、各行（A～Hまでの8行）に対して『』の中を試し、各行の結果の AVERAGE を計算するという意味になる。すなわち、
AVERAGE(Aの『』の結果, Bの『』の結果, ・・・・)
= AVERAGE(39, ,72, , , , , )
= 55.5
となる。
配列数式でIF文を使用する時の注意として、複数条件の論理積は AND 関数ではなく * 、論理輪は OR 関数ではなく + を用いる。もちろん、IF関数を入れ子にしてもよい。
2番目の使用例である。
クラス別、国語の点数別の人数の表である。ここで点数が 75 の行は、1つ下のレベルの点に1を足した 51 ～ 75 を意味する。-1 の行は、25 の行を 0 ～ 25 の範囲とするために便宜的に置いているもので、実際の場合は非表示行にすればよい。
ピンク色のセルの数式は
{=SUM((クラス=$A24)*(国語>$B23)*(国語<=$B24))}
または
{=SUM(IF((クラス=$A24)*(国語>$B23)*(国語<=$B24),1,0))}
のどちらでもよいが、私は後者の方が好きである。
後者の式を簡単に解説すると、IF関数の中は、『クラスが$A24（1行目で言えば「梅」）、かつ、国語が$B23 より大きく $B24 以下（1行目で言えば -1 より大きく 25以下）であれば 1 そうでなければ 0』という意味だ。それを、SUM関数で囲んでいるので、全体の意味としては、各行（A～Hまでの8行）に対して『』の中を試し、各行の結果の SUM を計算するという意味になる。すなわち、
SUM(Aの『』の結果, Bの『』の結果, ・・・・)
= SUM(0, 1, 0, 0, 0, 0, 0, 0)
= 1
となる。
3番目の使用例は、国語と算数の点数別人数のマトリクス表である。
ピンク色のセルの数式は
{=SUM(IF((国語>$B36)*(国語<=$B37)*(算数>B$36)*(算数<=C$36),1,0))}
である。
最後の使用例として、IF関数の実行部に 配列を用いた式 を書いた例を示す。
性別別、クラス別の両教科の平均点のマトリクス表である。
ピンク色のセルの数式は
{=SUM(IF((性別=$A44)*(クラス=B$43),国語+算数))/SUM(IF((性別=$A44)*(クラス=B$43),2))}
である。
このように、SUM 関数や IF関数を組み合わせた式も使える。（この例の問題では、AVERAGE関数を使えばもっと簡単にできるが・・・）
また、使える関数としては、SUM や AVERAGE 以外にも色々あるようだが、私の経験上、SUM が使えれば十分である。
配列数式の説明は以上である。
便利さが少しでも伝わっただろうか。
思いの外、記述量がかさんだので、冒頭に書いた2つ目の「複数シートでできたブックを、１つの文書として、まとめて 割付印刷したり両面印刷する方法」は、また次回ということにする。
では。
| 固定リンク
コメント