エクセル中級 14

Excelの基礎■

■SUBTOTAL関数

SUBTOTAL関数は、リスト(データベース)から集計値を求める関数です。単なる集計はSUM関数でいいのですが、リストでオートフィルターを実行する場合はこのSUBTOTAL関数を使います。

書式で表すと、SUBTOTAL(集計方法, 範囲1, 範囲2, ...)となります。
引数の集計方法には、集計に使用する関数を、1 〜 11 の番号で指定します。範囲1, 範囲2, …は集計するリストの範囲を 1 〜 29 個まで指定することができます。
オートフィルタを実行して、非表示になっている行は無視して集計されます。つまり、表示されているデータだけを集計するわけです。

実はこのSUBTOTAL関数は、One Point編の数式・計算No2 Q6「オートフィルター実行後の集計について」で解説していますので詳細及び使用例等は参照してください。

■SUMPRODUCT関数

SUMPRODUCT関数は、引数として指定した配列の対応する要素間の積（掛算の結果）をまず計算し、さらにその和（足算の結果）を返します。前ページ中級13のPRODUCT関数とSUM関数を合体させた関数ともいえます。

書式で表すと、SUMPRODUCT(配列1, 配列2, 配列3, ...)となります。
引数には、配列を指定します。配列と言う言葉は、これまでの解説の中にも出てきていますが、簡単に言えばデータが入力されている範囲のことです。SUMPRODUCT関数は、この配列で指定しないと正しい答えを求めることができません。また引数のそれぞれの配列は、行数と列数を互いに等しくする必要があります。等しくないと、エラー値 #VALUE! が返されます。引数は2個から30個まで指定できます。

◆使用例 1

下図は、販売数と単価から直接売上合計金額を求めた例です。

販売数の範囲C3:C6を一つ目の配列とし、単価の範囲D3:D6を2つ目の配列として、合計金額を求めています。

このような表の場合、H3以下に「=C3*D3」とし順次計算させて、SUM関数で合計を求めたりしますが、SUMPRODUCT関数を使えばこの例ように一発で掛算と足算の結果を求めることができます。

◆使用例 2

下図の例は、少し複雑な表配置から一発で合計金額を求めている例です。上段の表には、地域別の販売数があり、下段の表には地域別の単価が入力されています。

このような表の場合でも、C3:D6を一つ目の配列とし、C9:D12を二つ目の配列として、合計金額を求めることができます。 SUMPRODUCT関数は、このように上下に表が分かれていても、表配置さえ正しければ、正しい答えを求めることができます。

注意点は、同じ地域の販売数と単価は同じ列に配置すること、上下の表の列と行は同じ数にすることです。これが異なれば正しい答えを求めることができません。

◆配列数式について

ここで、配列数式について簡単に触れておきます。配列数式という言葉はどこかで聞いたことがあるかと思います。実は上記の使用例 1及び2は、SUMPRODUCT関数を使わずSUM関数の配列数式で同じ結果を一発で求めることができるのです。
使用例 1の場合は、=SUM(C3:C6*D3:D6)と数式を入力し、確定する時、Shift＋Ctrl＋Enterキーを押します。そうすると下図のように、数式バーの計算式は｛=SUM(C3:C6*D3:D6)｝となり、同じ結果が求められます。

式が、｛ ｝の括弧で囲まれます。このような式を配列数式といいます。

｛ ｝の括弧は、キーボードに刻まれた括弧から入力しても無効です。必ずShift＋Ctrl＋Enterキーで入力する必要があります。そうしないとエクセルは配列数式として認識できません。

使用例 2の場合は、全く同じ操作で、｛=SUM(C3:D6*C9:D12)｝となります。これで同じ結果を求めることができます。

※配列については、特別編で詳しく解説する予定です。

■RAND関数

RAND関数は、0 以上で １ より小さい乱数を発生させる関数です。ワークシート上で入力などの操作をすると、ワークシートはその都度再計算されますが、その度に新たな乱数を発生させます。（F9キーでも再計算されます。）乱数とは簡単に言えば、意図的でないランダムな数字です。

書式で表すと、RAND()となります。
RAND関数には特定の引数はありません。（ ）のみです。ただし、（ ）は省略できません。

◆使用例

下図はRAND関数で乱数を求めたいろいろな例です。

図から式と答えを比較して意味を理解してください。 いろいろな応用ができます。

この図の例では、整数化するのにINT関数を使っていますが、TRUNCやROUNDUP、ROUNDDOWNなどの関数を使えば自在に桁数の調整が可能です。

■RANDBETWEEN関数

RANDBETWEEN関数も乱数を発生させる関数です。上記のRAND関数と似ていますが、RANDBETWEEN関数の場合は、指定範囲内の整数の乱数を発生させる関数です。
なお、RANDBETWEEN関数はツール（T）のアドイン（I）から分析ツール（アドイン関数）を追加しなければ使うことができません。

書式で表すと、RANDBETWEEN(最小値, 最大値)となります。
引数の最小値は、必ず乱数発生の最小値を整数で指定します。また最大値は、乱数発生の最大値を整数で指定します。引数の指定は、直接入力やセル参照で指定します。なお、最小値と最大値を逆に指定するとエラーになります。

◆使用例

左図のようにRANDBETWEEN関数の使い方は簡単です。

例の引数は、すべて数値を直接入力していますが、当然セル参照でも指定できます。

※RANDやRANDBETWEEN関数は、実務の上ではあまり使うことがないように思えます。しかし、データから無作為な抽出をしたり、何かの当選者を決めることなどに利用することができます。工夫次第でいろいろな場面で使うことができるものです。工夫してみてください。

「中級・特別編トップへ」