富裕層・投資家の「今と先」を伝えるキュレーションサイト

【エクセル関数】多様な集計に使える“万能関数”をマスターしよう

問題:次の表組の「小計」、「合計」、「平均値」欄にみあう、適切な数値を、1種類の関数を使い自動計算せよ。 難易度:☆☆☆★★ 実用度:☆☆☆☆☆ 目標ステップ数:11 解答: 小計や合計ならSUM関数、平均値はAVERAGE関数というように、求めたい数値の計算方法ごとに関数を使いわけている人も多いと思うが、これらの計算はすべて、指定した集計方法で集計を行ってくれる「AGGREGATE(アグリゲート)」関数だけで行うことができる。さらに、AGGREGATE関数を使えば、列に含まれる「小計」欄の数値を無視して合計を求める、といった計算も可能だ。では、標準解答の手順を見ていこう。 ●STEP1 まず、Aグループの成績の小計を求める。数式を入力したいセル(ここではC5)を選択し、「数式バー」に「=AG」と入力。表示される関数の候補から「AGGREGATE」をクリック。 ●STEP2 「= AGGREGATE (」と、関数の一部が自動入力され、さらに「集計方法」の引数を選択するメニューが表示される。ここでは「9-SUM」を選択。「9」と引数を直接入力しても構わない。 ●STEP3 続けて「,」(半角のカンマ)を入力すると、「オプション」の引数を選択するメニューが表示される。ここでは「0-入れ子になっているSUBTOTAL関数及びAGGREGATE関数を無視…」を選択。ここも、「0」と引数を直接入力しても構わない。 ●STEP4 続いて、計算対象の範囲をドラッグして選択する(ここではC2:C4)。最後にEnterキーを押して数式を確定させる。 ●STEP5 これで、STEP4で選択した範囲の小計(合計)が計算される。計算結果が表示されているセルを選択し、CTRL+Cキーでコピーする。 ●STEP6 Bグループの「小計」欄(ここではC9)を選択し、CTRL+Vキーを押すとコピーした数式が貼り付けされる。この際、計算対象の範囲はC2:C4からC6:C8へと自動的に変更される(相対参照)ため、そのままBグループの小計(合計)が計算結果として表示される。 ●STEP7 次に、AグループとBグループの成績の合計を求める。「合計」欄(ここではC10)を選択し、CTRL+Vキーで先ほどコピーした数式を貼り付ける。 ●STEP8 次に、AグループとBグループの成績の合計を求める。「合計」欄(ここではC10)を選択し、CTRL+Vキーで先ほどコピーした数式を貼り付ける。「数式バー」に表示されている数式の、計算対象の範囲にあたる部分(ここではC7:C9)を選択し、正しい範囲(ここではC2:C9)をドラッグで選びなおす。続けて「F4」キーを押し、「$C$2:$C$9」という絶対参照の形式に変更したらEnterキーを押す。 ●STEP9 これで、「小計」欄(セルC5とC9)を除く、C2からC9までの合計値が求められる。計算結果が表示されているセルを選択し、CTRL+Cキーでコピーする。 ●STEP10 最後に、AグループとBグループの成績の平均値を求める。「平均値」欄(ここではC11)を選択し、CTRL+Vキーで先ほどコピーした数式を貼り付けたら、「数式バー」に表示されている数式の、「集計方法」にあたる部分(ここでは9)を削除。「集計方法」の引数を選択するメニューが表示されるので、「1-AVERAGE」を選択。「1」と引数を直接入力しても構わない。 ●STEP11 これで、「小計」欄(セルC5とC9)を除く、C2からC9までの平均値が求められる。STEP8で計算対象の範囲を絶対参照で指定しているため、数式の場所が移動しても範囲は変更されない。 【今回のまとめ】 今回用いたのは、AGGREGATE関数は「=AGGREGATE(集計方法,オプション,計算対象の範囲(参照または配列))」という基本構文となる。 この関数が特にユニークなのは、「集計方法」の指定により、計算方法が変わる点だ。今回の問題で紹介したように、関数の自動入力機能を使えば、「集計方法」をメニューから選ぶこともできるが、慣れてきたら、引数を直接入力した方が効率的だ。「集計方法」の引数は、以下の通りとなる。 このうち、よく使う引数だけを覚えておけばよいだろう。 また、AGGREGATE関数が便利なのは「オプション」の指定により、計算対象の範囲から特定のセルを除外できる点。今回の問題のように「小計」欄の数値を除外した合計も、簡単に求められるので、集計表の作成には、かなり重宝するはずだ。「オプション」の引数は、以下の通りだ。 こちらは、「0」のオプションを使う機会がもっとも多いはず。「0」は省略することもできるので、難しく感じるようなら、「=AGGREGATE(集計方法,計算対象の範囲)」というように、「オプション」の引数を無視した構文でおぼえておいてもよいだろう。 SUM関数に比べれば使い方は難しいが、そのぶん用途はかなり多様なAGGREGATE関数。エクセルの達人を気取るなら、確実に使いこなしておきたいところだ。