日経PC21 / 配列数式でSUMPRODUCTとFREQUENCYを使いこなす

「表計算の極意 '03-'04」のエクセル(Excel)“配列数式”基礎講座で完全理解！

◆“達人”芳坂和行氏に学ぶ、エクセル(Excel)「配列数式」講座

第６回　配列を扱う関数の利用

―― SUMPRODUCTとFREQUENCYを使いこなす

この講座もいよいよ最終回です。SUM 関数の代わりに利用されることの多い SUMPRODUCT 関数と、配列を返す関数の代表として FREQUENCY 関数を取り上げます。最後に、配列数式のデメリットと対策について勉強します。

ポー: 「先輩、こんにちは！」
エリカ: 「あれ？ポーくん、今日は何か約束があるんじゃなかったの？」
ポー: 「ああ、それ、なくなりました。まだ仕事ですか？手伝います。何をすればいいですか？」
エリカ: 「これは私の仕事で、あなたには関係ないでしょう？さっさと行きなさい！」
ポー: 「忙しいときはお互い様ですよ。あ、これ 50 部、コピーですね」
エリカ: 「しょうがないなぁ… あ、それはまだだめ！データとグラフが古いままなの。今、新しいデータで集計し直してるところなんだけど…」
ポー: 「どうかしたんですか？」
エリカ: 「他の人が作ったエクセルのシートだから、よくわからなくて…」
ポー: 「えーっと… SUMPRODUCT（サムプロダクト）関数と FREQUENCY（フリークエンシー）関数ですね」

◆SUMPRODUCT関数

エリカ: 「SUMPRODUCT 関数は掛け算して合計する関数だよね？」
ポー: 「はい。例えば『A3:A7』に単価、『B3:B7』に数量があるとき、合計金額は『=SUMPRODUCT(A3:A7,B3:B7)』で計算できます」

エリカ: 「それはわかるんだけど、『=SUMPRODUCT((A3:A12="男")*1)』という式では何を合計しているの？」
ポー: 「男性の人数を数えています。つまり、『{=SUM((A3:A12="男")*1)}』という配列数式と同じ計算になります」
エリカ: 「え？これ、配列数式なの？」
ポー: 「配列数式とは言えないかもしれませんが、計算内容はまったく同じです。まず『A3:A12="男"』の部分が 10 行× 1 列の配列になります。この配列の中身は『TRUE』または『FALSE』という論理値なので、合計の対象にするためには、『1』『0』という数値に変換する必要があります。1 を掛けているのがそれです。『TRUE』を『1』、『FALSE』を『0』に変換して合計したいときは、1 を掛けばいいって、前に教えましたよね。そうすると『男』であれば 1、そうでなければ 0 という配列ができます。この配列を合計すれば『男』の人数をカウントできるわけです」

エリカ: 「つまり SUM 関数の代わりに SUMPRODUCT 関数を使っているってこと？」
ポー: 「はい。そうです」
エリカ: 「どうしてそんなことをするのかな？」
ポー: 「たぶん、数式を『Ctrl』+『Shift』+『Enter』で確定しなくてもいいからでしょうね」
エリカ: 「え？そうなの？」
ポー: 「数式の内容にもよりますが、この式は普通に『Enter』で確定するだけでも計算できます」
エリカ: 「『Ctrl』+『Shift』+『Enter』で確定しなくても、配列の計算ができるの？」
ポー: 「『Ctrl』+『Shift』+『Enter』はセル範囲に配列数式を入力するための操作です。配列の計算とは基本的には関係がないんです」
エリカ: 「でも、SUM 関数は『Ctrl』+『Shift』+『Enter』で確定しないとだめでしょう？」
ポー: 「それは SUM 関数の引数では『共通部分参照』という機能が働くからなんです」
エリカ: 「共通部分参照？」
ポー: 「共通部分参照というのは、指定されたセル範囲と、数式が入力されている行または列との共通部分だけが使用されるという機能です」
エリカ: 「えーっと… どういうこと？」
ポー: 「例えば、C3 に『=A3:A7*B3:B7』という数式を入力します」
エリカ: 「これは配列数式？」
ポー: 「いえ、通常の数式です。『Enter』で確定します。そうすると『A3:A7』『B3:B7』と、数式が入力されている 3 行目との共通部分である『A3』と『B3』だけが使われて、『=A3*B3』という計算になるんです」

ポー: 「C4 にも同じ『=A3:A7*B3:B7』という数式を入力してみますね。すると、数式が入力されている 4 行目との共通部分である『A4』と『B4』だけが使われて、『=A4*B4』という計算が行われます」
エリカ: 「あ、本当だ！」
ポー: 「共通部分参照というのは、こんなふうに自動的にセル範囲を限定する機能なんです」
エリカ: 「ふーん… 共通部分参照なんて知らなかった」
ポー: 「SUM 関数や IF 関数では共通部分参照が有効です。そのため、セル範囲を指定しても自動的に範囲が限定されるだけで、そこから配列が作られないことがあるんです。例えば『 =SUM((A3:A12="男")*1)』という数式が 3 行目に入力されていれば、『A3:A12』と 3 行目の共通部分の『A3』だけが使われて、『=SUM((A3="男")*1)』という計算になります」

エリカ: 「配列数式にするのを忘れると変な答えが出ることがあるけど、こんな計算をしてたのか」
ポー: 「『Ctrl』+『Shift』+『Enter』で確定して配列数式にすると、共通部分参照は無効になります。だから SUM 関数や IF 関数でセル範囲から配列を作って計算したいときには、『Ctrl』+『Shift』+『Enter』で確定するんです」
エリカ: 「そうなんだ」
ポー: 「SUMPRODUCT 関数では共通部分参照は最初から無効です。そのため『Ctrl』+『Shift』+『Enter』で確定しなくても、セル範囲から配列が作られるんです」
エリカ: 「そうか…『Ctrl』+『Shift』+『Enter』で確定するから配列の計算になると思っていたんだけど、そういうわけじゃなかったのか」
ポー: 「SUMPRODUCT 関数でも『=SUMPRODUCT(IF(B3:B12="男",1,0))』のように IF 関数と組み合わせるときには『Ctrl』+『Shift』+『Enter』が必要になります」
エリカ: 「それなら SUM 関数と同じだね」
ポー: 「そうですね。なので、SUM 関数の代わりに SUMPRODUCT 関数を使うときは、IF 関数を使わずに、論理値の計算を使うことがほとんどです。SUM 関数と同じように複数条件の集計もできます。例えば、30歳から39歳の男性の人数は『=SUMPRODUCT((A3:A12="男")*(B3:B12>=30)*(B3:B12<=39))』という式で計算できます」
エリカ: 「この式も普通に『Enter』で確定すればいいの？」
ポー: 「はい。共通部分参照が有効になる関数を使わなければ、通常の数式で大丈夫です」

◆FREQUENCY関数

ポー: 「FREQUENCY 関数は数値の範囲ごとの個数を計算する関数ですね。年齢別の人数のカウントとか、テストの点数別のカウントなどに使います」
エリカ: 「うん」
ポー: 「FREQUENCY 関数の一番の特徴は、結果を配列で返すことです」
エリカ: 「関数の結果が配列になるの？」
ポー: 「はい。このシートでは『～29』『30～39』『40～49』『50～』という範囲ごとにカウントするんですが、FREQUENCY 関数を使うと一つの式で各範囲の人数をすべてカウントできます」
エリカ: 「全部まとめて計算できるってこと？」
ポー: 「そうです。範囲は複数あるので結果も複数になります。その複数の結果を配列で返すわけです。配列をセルに表示するにはどうすればいいか覚えていますか？」
エリカ: 「えーっと… 配列と同じ大きさのセル範囲を選択してから、数式を入力して、『Ctrl』+『Shift』+『Enter』で確定する」
ポー: 「正解です！つまり、FREQUENCY 関数は、集計の範囲の個数と同じ数のセルを選択して、配列数式として入力する、という使い方をします」
エリカ: 「そうなんだ」
ポー: 「では、この『{=FREQUENCY(B3:B12,{29,39,49})}』という数式の入力操作をやってみますね。『～29』『30～39』『40～49』『50～』の 4 個の範囲でカウントしたいので、まず、4 行×1 列のセル範囲、『F8:F11』を選択します」
エリカ: 「うん」
ポー: 「数式パレットを使ってみましょうか」
エリカ: 「数式パレット？」
ポー: 「メニューの [挿入]-[関数] を実行します。ツールバーの『関数貼り付け』ボタンでもいいですし、『Shift』+『F3』のショートカットキーでもいいです。数式が入力済みなら、すぐに数式パレットが表示されます。数式を新しく入力するときは『関数の貼り付け』ダイアログボックスが表示されるので、『FREQUENCY』を見つけて選択し、[OK] をクリックすると数式パレットが表示されます。」
エリカ: 「あ、これが数式パレットか」

ポー: 「『データ配列』には集計したいセル範囲を指定します。ここでは年齢データのある『B3:B12』ですね」
エリカ: 「『区間配列』って何？」
ポー: 「集計の範囲の指定です。各範囲の最後の数を配列で指定します。『～29』の範囲は『29』、『30～39』の範囲は『39』、『40～49』の範囲は『49』です。『50～』の範囲には最後の数はないので指定しません。つまり『{29,39,49}』という配列定数を指定します」
エリカ: 「範囲は 4 個あるけど、指定するのは『{29,39,49}』の 3 個だけでいいの？」
ポー: 「はい。各範囲の区切りを指定するわけです。範囲が 4 個なら、その区切りは 3 つあればいいですよね」
エリカ: 「ああ、そうか」
ポー: 「『{29,39,49}』という配列定数で指定しましたが、この数値をセルに入力しておいて、そのセル範囲を指定することもできます」
エリカ: 「うん」
ポー: 「引数が指定できたら、配列数式にするために『Ctrl』+『Shift』を押しながら [OK] ボタンをクリックします」
エリカ: 「ここでもやっぱり『Ctrl』+『Shift』なんだ」
ポー: 「はい。『Ctrl』+『Shift』+『Enter』でもいいです。それから、配列数式がすでに入力されている場合には、単に [OK] をクリックするだけでも大丈夫です」
エリカ: 「うん、わかった。これで数式の書き換えもできそう！」

◆配列数式のデメリット

ポー: 「そうだ、配列数式のデメリットについて、まだお話ししていませんでしたね」
エリカ: 「配列数式のデメリット？」
ポー: 「主なものに二つあります。一つは先輩が今、体験したことです」
エリカ: 「え？何のこと？」
ポー: 「仕事を引き継ぐときに困るんです。引き継いだ人が配列数式を知っていればいいですが、知らない人もたくさんいます」
エリカ: 「私みたいに？」
ポー: 「そ、そうです。入門書には載っていないし、ヘルプを調べても、なかなか説明が見つからないことがあります。FREQUENCY 関数についてはヘルプを読めば、ある程度はわかると思いますが、SUM 関数や SUMPRODUCT 関数の場合、ヘルプを読んでも配列の計算についてはほとんど何もわかりません」
エリカ: 「そうなんだよね。泣けてきちゃうよ」
ポー: 「配列数式を使う場合は、配列数式の説明書も一緒に引き継ぐようにすると良いと思います」
エリカ: 「うん、そうする。で、配列数式の説明書はポーくんが作ってくれるんだよね？」
ポー: 「え？わかりました… 作っておきます」
エリカ: 「ありがとう。よろしくね！」
ポー: 「それから、一つの数式で済むからといって複雑な式を作るのは避けてください。配列数式にも単純でわかりやすいものもあれば、複雑なものもあります。作業セルと組み合わせて、できるだけ、わかりやすい計算表にしてください」
エリカ: 「うん、わかった」
ポー: 「もう一つのデメリットは、他の計算方法に比べて、計算に時間がかかることです」
エリカ: 「そうなの？今のところ、そんなふうに感じたことはないけど」
ポー: 「配列の大きさ、数式の量、計算の内容、それからパソコンの性能にもよります。セルの値を直接使うのではなく、一度、配列にコピーしてから使うので、そのための時間が余分にかかってしまうんです。最近のパソコンは性能も高いですし、利用できるメモリーも大きいので、気にならないことが多いでしょうけど」
エリカ: 「そうなんだ」
ポー: 「配列数式を使って複数の条件で集計する方法を勉強しましたよね」
エリカ: 「『=SUMPRODUCT((A3:A12="男")*(B3:B12>=30)*(B3:B12<=39))』とか？」
ポー: 「そうです。この方法では条件の数が増えると計算時間も増えるんです。データベース関数の DSUM 関数を使うと、同じ計算がずっと速くできることがあります。計算時間が気になるようなら、データベース関数も試してみてください」
エリカ: 「うん、そうする」
ポー: 「それから、配列数式では作業セルを使わずに計算できることがメリットなんですが、作業セルを使わないと、データの一部を書き換えたときにすべての計算をやり直すことになります。作業セルを使って段階的に計算するようにしておけば、一部の計算をやり直すだけで済みます。データ入力時の再計算時間が気になるようなら、作業セルを使って段階的に計算することも考えてください」
エリカ: 「メリットは同時にデメリットでもあるってことね」

◆配列の大きさの制限

ポー: 「配列の大きさの制限についてもお話ししておきますすね」
エリカ: 「制限って、どれくらいまでなの？」
ポー: 「エクセルのバージョンによって違うんです。エクセル2000以降では無制限です。ただし A:A のような列全体からセルの値の配列を作ることはできません。エクセル97 では配列の要素の最大数は 6553 まで、エクセル95 では 5458 まで、エクセル5.0 では 6553 までです。複数のバージョンでファイルを使う場合には気をつけてください」
エリカ: 「以前のバージョンでは大きな配列は使えないんだ」
ポー: 「関数にも配列の要素数に制限のあるものがあります。MMULT 関数の結果の配列は 5458 まで、MINVERSE 関数で扱える配列は 3249 までです」
エリカ: 「最近のバージョンでも使う関数によっては注意が要るってことね」
ポー: 「そうです… あ、すみません！話し込んでしまって」
エリカ: 「ううん。もう急がない… 本当にいいよ。私、自分でやるから」
ポー: 「先輩、今思い出したんですけど、今日はお母さんの誕生日で、ご家族が集まるという日じゃなかったですか？」
エリカ: 「そうだけど… でも、もう間に合いそうもないし、妹も仕事で来れないって言ってた」
ポー: 「それでも、早く帰った方がいいですよ。こういうときこそ、出来のいい後輩を使ってやってください！」
エリカ: 「出来がいいのはパソコンだけのくせに…… しょうがないなぁ」
ポー: 「ここを書き換えて印刷すればいいんですよね。よし、頑張ろう！」