PartV  攻略 配列数式と関数による合計・件数を求める  その1

関数あらかると

 配列数式? ExcelHelpを参照しても意味不明といった代物。
ここでは、配列数式と配列に関連する関数を使った合計の求め方を例に配列についての基本的な部分をご紹介します。

サンプルは、Excelシートへコピッペすると「キッチシ」確認することができます。


 簡単な合計を求める
使用する関数は、SUM、SUMPRODUCTそして配列数式です。
 
A B C D E
売上月 品目 数量 単価 金額
11 品目1 10 500 5,000
11 品目2 25 350 8,750
12 品目3 40 280 11,200
11 品目4 15 650 9,750
12 品目5 31 250 7,750
=SUM(E2:E6) →SUM関数での合計
=SUMPRODUCT(C2:C6,D2:D6) →SUMPRODUCT関数での合計
{=SUM(C2:C6*D2:D6)} →配列数式での合計
 
※ セルE9に定義する配列数式 {=SUM(C2:C6*D2:D6)} は複写することができません。以下の操作で再定義して下さい。
@セルA1の売上月からセルE9の配列関数を範囲にワークシートへコピッペします。 
AセルE9の配列数式の{}を削除します。この操作は数式バーの中で行います。
B数式バーのカーソル点滅を確認して Ctrl + Shift を押しながら Enter キーを押下します。このキー操作が配列数式の宣言です。

@からBの操作で配列数式の値を求めることができます。
SUM関数、SUMPRODUCT関数、配列数式で求めた合計は、いずれも 42,450 となります。

 配列数式とは?
 数学の行列の考え方と同じで箱を縦(行)と横(列)に必要なだけ配置したものです。その箱には、文字であったり数値などが入ります。
ひとつの同じ要素を持つ配列の束が配列引数と呼ばれ、複数の値のセットで配列数式が実行されます。
 配列数式の条件としては、同じ数の行と列を指定する必要があります。配列数式の作成方法は他の数式とほぼ同じですが、数式の入力後に Ctrl キーと Shift キーを押しながら Enter キーを押下します。 キー操作後に中かっこ { } の間が数式の前後に自動で挿入されます。入力しての{ }では機能しません。
         
項目売上月、品目、数量、単価、金額が最小単位の配列要素となり、各々の項目の配列の大きさは、列が1、行が5です。(緑枠が配列引数の単位)

         
同じ表を横系列にした場合の各々の項目の配列の大きさは、列が5、行が1です。(緑枠が配列引数の単位)


 配列数式で使える関数・使えない関数
 配列数式で扱える関数はすべてではありません。
ExcelHelpを見て記載されているものはごくまれなケースです。WebMambowでは配列数式で定義できる関数を全て熟知しているわけではありません。下表はこれまでに定義してわかっている関数の一覧です。
使える関数
COLUMN, COUNT, FIND, INDEX, IS関数, LARGE, LEFT, MATCH, MAX, MID, MIN, MOD, MODE, RIGHT, ROW, SMALL


 「簡単な合計を求める」 の配列内解剖
まず、SUM関数とSUMPRODUCT関数の数式についてです。
関数式 =SUM(数値1,数値2,...)

 セル範囲に含まれる数値のすべての合計値を返します。数値は1個から30個まで指定することができます。

関数式 =SUMPRODUCT(配列1,配列2,配列3,...)    <配列関数>

 引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。配列は2個から30個まで指定することができます。
指定方法は違いますが考え方は、配列数式と同じです。

  • SUM関数の合計      =SUM(E2:E6)

     配列ではないが数量×単価で求めた金額 「セルE2の値からE6までの値を合計しなさい」 といった命令になります。

  • SUMPRODUCT関数    =SUMPRODUCT(C2:C6,D2:D6)

    説明にある配列に対応する要素間の積とは、 になります。要素間の乗算を実行後に加算ですからSUM関数と同じ結果となりますが、比較すれば金額を計算するプロセスは、余計な処理と時間を生みます。

  • 配列数式           {=SUM(C2:C6*D2:D6)}
     配列に関してはSUMPRODUCT関数と同じ考え方になります。違いは、配列SUM関数で定義している点です。C2:C6*D2:D6 の中の * は「積を求めなさい」の意味になります。全体を通せば、セルC2からC6を加算し....ではなくSUMPRODUCTと同様に配列要素間 C2*D2、C3*D3 .... C6*D6 を最初に計算しその結果を加算します。
    • 配列数式の宣言
      数式を指定する時と同様に数式バーで =SUM(C2:C6*D2:D6) を入力します。
      その後に Ctrl + Shift を押しながらEnterキーを押下 のキー操作で数式の前後に中括弧 {} が付されます。 =SUM(C2:C6*D2:D6) だけでEnterキーを押下すると #VALUE! のエラー値が返されます。
    配列数式は、応用如何によって高度な関数式を組み立てることができますが、その配列のマトリックスを頭の中で思考する必要があります。最初から「配列数式」と固まって思考してもなかなかいい結果が生まれてこない場合も多々あります。それよりもワークセルを使って計算を分割し結果を導き出す方が最良と感じる方が多いかも知れません。
    配列数式の留意として、通常の計算式よりメモリー消費が大きいためCPUに負担がかかり動作に影響する場合があります。


PartVは、配列の基本的な考え方までにとどめます。
続きは、条件式を加えた配列の指定をテーマに PartW でお送り致します。