 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
|
ここでは、配列定数や配列数式の具体的な活用例を紹介していきます。
なお、数式を表記している部分で、数式全体が「{ }」で囲まれている場合は、その数式がCtrl＋Shift＋Enterで確定された配列数式であることを表します。
キーボードから直接「{ }」を入力するわけではないことにご注意ください。
STEP 1の配列数式の説明の中で、COUNTIF関数の検索条件に単一の値ではなくセル範囲を指定し、その結果をセル範囲と同じ形（1列×3行）の配列として求める数式と、さらにその結果をSUM関数で合計する数式を紹介しました。
ここでは、検索条件としてセル範囲ではなく配列定数を指定し、複数の種類の検索条件で、該当するセルの数をカウントする数式を紹介しましょう。
検索条件がAとBの2つぐらいであれば「=COUNTIF(範囲,A)+COUNTIF(範囲,B)」でもいいでしょうが、さらに多くの検索条件に該当するセルの数の合計を求めたい場合は、この方法が便利です。
セル範囲を配列と見なさせるわけでもないので、配列数式にする必要はありません。
=SUM(COUNTIF(A1:A10,{"A","C","E"}))
A1:A10のセル範囲の中で、「A」か「C」か「E」が入力されているセルの数の合計を求める。
たとえば、1以上10未満はA、11以上100未満はB、101以上500未満はC、501以上1000未満はD、というように、不規則な間隔で設定された範囲に対応する値を返したい、というケースはよくあります。
このような範囲を表す条件をIF関数で表現するのは、結構面倒です。また、条件が多くなれば数式も長くなり、ネストできる限界（7レベル）もあります。
このようなケースでは、LOOKUPやVLOOKUPを使用するために、参照用のテーブルをどこかに作成するという手もあります。さらに配列定数を利用することで、この「参照テーブル」を数式の中に埋め込んでしまうことが可能です。
=LOOKUP(A2,{1,"A";10,"B";100,"C";500,"D";1000,"E"})
参照用テーブルを用意した例。
参照用テーブルのデータを数式中に埋め込んだ例。
ワークシートをシンプルにできる。
たとえば、ある団体では、1年をちょっと変則的に、3月1日〜7月31日、8月1日〜10月31日、11月1日〜翌年2月末日の3期に分けているとします。このケースで、A2セルに入力されている日付データが属している期間の開始日を求めてみましょう。
最初から参照テーブルを用意しておく場合、問題となるのは対象となる日付の「年」が特定できないことです。対象の日付データを参照してその年の日付データを生成させるにしても、その参照テーブルが同じ年の日付にしか対応できなくなります。
この参照テーブルの部分を数式化することで、対象の日付に応じた日付データの配列が作成できます。
=LOOKUP(A2,DATE(YEAR(A2),{-1,3,8,11},1))
DATE関数の月の部分を配列定数で指定し、戻り値の日付を配列化している。
「-1」というのは前年の11月を表す。
開始日は昇順に並んだ日付データの配列を普通にLOOKUPで調べればよかったのですが、終了日を求めたい場合は、配列の各日付以下の日付という形で検索する必要があります。
このような場合には、数式研究室で紹介した降順のリストでLOOKUP検索するテクニックが利用できます。
また、「○月1日」という場合と違って末日というのは月によって異なります。2月が絡まなければ月と同様に日も配列定数で指定する手もありますが、ここでは次の月の0日（1日の前の日）を指定するという方法を使用しています。
=-LOOKUP(-A2,-DATE(YEAR(A2),{15,11,8,3},0))
DATE関数の月の部分を配列定数で指定し、戻り値の日付を正負反転させた配列にしている。
「15」というのは翌年の3月を表しており、その0日で、すなわち2月の末日を表す。
※上記2つの例題は、Yahoo!掲示板のZugon氏にご紹介いただいたものをアレンジしました。
※Zugonさん、ありがとうございました。
通常、単価と数量はそれぞれ（行ごとに）乗算してからその合計を求めますが、配列数式を使用することで、直接乗算した結果の合計が求められます。
ここではSUM関数を用いてCtrl＋Shift＋Enterで配列数式にしていますが、SUMPRODUCT関数を利用すれば配列数式にする必要はありません。
{=SUM(A2:A6*B2:B6)}
「A2*B2」、「A3*B3」という計算を各行ごとに行って、その合計を求めている。
ある列の条件を満たす項目だけを対象として、その中での最大値や最小値を求める数式を紹介します。
次のような果物の価格表があるとして、等級が「A」の品の中で、最も安い価格を求めてみましょう。
ここではIF関数を使用して、B列が「A」だった場合だけ同じ行のC列の値を返し、その配列の中の最小値をMIN関数で求めています。
なお、IF関数の引数で偽の場合（B列が「A」でなかった場合）については指定していませんが、省略された場合はFALSEが返るため、MIN関数の処理対象とはなりません。
{=MIN(IF(B2:B10="A",C2:C10))}
最も安い価格というだけならみかんの「200」があるが、等級がAという条件があるため、レモンの「300」が返っている。
次のようなやや変則的な表で、奇数行には「消費税」が入力されているため、税別の金額のみを合計したいとします。
この例では、ROW関数で行番号を求め、MOD関数でそれを2で割った余りを求めて、答えが0の行のみの合計を求めています。
{=SUM(IF(MOD(ROW(B2:B11),2)=0,B2:B11))}
奇数行には消費税が入力されているため、合計には加えない。
下のような表で、所属に関係なく売り上げ件数の多いほうから順位を付けるとしたら、普通にRANKを使えば問題ありません。
ただ、ここでは、営業一課、営業二課の各課それぞれに順位を表示させたいとします。
次の数式では、対象の範囲の中で、自分と同じ所属で、かつ自分よりも売り上げ件数が多い行の数を合計し、それに1を加えることで順位を求めています。
{=SUM(($B$2:$B$11=B2)*($C$2:$C$11>C2))+1}
D2セルに数式を入力後、D11セルまでオートフィルでコピーしている。