配列数式−予備集計表を使わずに一気に集計

配列に対応する要素間の積をまず計算し、さらにその合計を返します。

問．売り上げ金額を求めなさい。

■SUMPRODUCTを使う方法

■集計関数を使う方法

SUM関数で合計する場合は、集計用の金額フィールドを追加し、単価×数量を計算しておく必要があります。

■配列数式を使う方法

ほとんどSUMPRODUCTと同じです。SUM関数を使用。

方法

１．セルに「 SUM 」を入力して、範囲 G49:G58 を選択、
２．*（乗算） を入力、
３．範囲 H49:H58 を選択して、Ctrl+Shift+Enter を押下。ここがポイント。

式の前後に { } 記号が自動的に挿入されます。これが配列数式を表しています。
なお、この記号は記号としては直接入力することはできません。

また、式を編集しようとしただけで解除されます。
式を編集した場合は、再度、Ctrl+Shift+Enter を押下する必要があります。
入力を取り消す場合は、ESCを押します。

問、商品名と一致する金額の合計を計算しなさい

=SUMPRODUCT($B$3:$B$14=G7,$D$3:$D$14)

とすれば、合計金額が出そうですが、$B$3:$B$14=G7 は TRUE/FALSE の論理値なので、 TRUE → 1、FALSE → 0 とする必要があります。
以降でも述べていますが、数値を掛けることにより、論理値 → 数値に変換することができます。
具体的には、下図のように1を掛けます。

($B$3:$B$14=G7)*1

条件に合致したところだけ1、合致しなかったところは 0 になるので、 条件に合致する商品区分のみが合計対象となります。
データベース関数 SUMIF関数 を使った方法も併記 このレベルでは、どの方法も「どっこいどっこい」ですね。
配列数式では、計算式自体が 論理値×数値 なので、*1 の必要はありません。

問．商品名とサイズに一致する商品の個数を計算しなさい

■SUMPRODUCTを使う方法

■配列数式を使う方法

=SUM(B48:B55=B45)*(C48:C55=C45)

と入力して、Ctrl+Shift+Enter を押します。
SUMPRODUCT と同じく、B48:B55=B45 は論理値になりますが、論理値を乗算した時点で数値になります。
*1 の必要はありません。
それぞれのAND条件の論理値なので、掛け算として処理できます。

SUMPRODUCTより少しだけ簡素。

論理積表

TRUE　× TRUE　-> 1
TRUE　× FALSE -> 0
FALSE × TRUE　-> 0
FALSE × FALSE -> 0

問．テスト結果で、全て80点以上ならば「合格」と表示なさい。

■SUMPRODUCT

■データベース関数

問．指定の性別、かつ、30歳以上の人数を集計しなさい。

■SUMPRODUCT

■配列数式

■DCOUNT(データベース関数)

例1．都道府県が「東京都、神奈川県」のいずれかの人数を求めなさい

■SUMPRODUCT

都道府県の上比較: D4:D23={"東京都","神奈川県"}
{ }で括ることで配列定数として使用できます。
この { } は配列数式と異なって手入力します。
D4:D23={"東京都","神奈川県"} とすることで、「いずれかに一致」としての OR条件として使用可能。
以下、1を掛けて数値化。

=SUMPRODUCT((D4:D23={"東京都","神奈川県"})*1)

■配列数式

同様。

{=SUM((D4:D23={"東京都, 神奈川県})*1)}

■DCOUNT(データベース関数)

DCOUNT関数には条件フィールドが必要

=DCOUNTA(B3:E23,"都道府県",M9:M11)

例2.都道府県が「東京都、神奈川県」のいずれかで、男性の人数を求めなさい

AND条件が追加されましたが、これは今までと同じ。
例1で作成した条件式に、「男性」であるという条件式を掛けた式(論理積)になります。

問、指定の性別または、30歳以上の人数を集計しなさい。

■配列数式

■SUMPRODUCT

配列数式と同じに形式に記述するとOK

=SUMPRODUCT((((C4:E23=J2)+(E4:E23>=J3))>=1)*1)

IFを使って記述すると、何故か結果が0になる・・・ - 保留

=SUMPRODUCT(IF((C4:C23=J2)+(E4:E23>=J3)>=1,1,0))

ちなみに、フリーのオフィスソフト LibreOfficeCalc で開いてみると、下図のように正常「6」に計算されています。


キングソフト( kingsoft-spreadsheets2012) では、エクセルと同じく「0」となります。

■DCOUNT(データベース関数)

下のシフト表から勤務ごとの日数を緑枠内に集計しなさい。

SUMPRODUCT

=SUMPRODUCT(($C13:$AG13=AI$12)*1,($C13:$AG13<>"")*1)

配列数式

{=SUM(($C13:$AG13<>"")*($C13:$AG13=AP$12)*1)}

SUMIF

SUMIFで集計するためには数値表が必要となるので、あらかじめCOUNTIFで勤務数を集計しておきます。

=SUMIF($C13:$AG13,AI$33,$C34:$AG34)

問. シフト表を集計して、勤務を選択したら曜日ごとの勤務日数が表示されるようにしなさい。

　また、ワイルドカード「*」の場合、全勤務が集計対象になるようにしなさい。

■配列数式

■データペース関数

一気には計算できないので、IF関数で集計予備表に曜日番号が表示されるようにして置きます。
この予備表を元にCOUNTIF関数で集計することになります。

=COUNTIF($C34:$AG34,AI$33)

問．中間テストの成績順位を付けなさい。

全体順位とクラス内順位を付けます。

■RANK関数

■COUNTIF関数

■配列数式 - 全体順位

■配列数式 - クラス内順位

問、下図の5つの文字列で、文字数の一番多い文字列を取り出しなさい。

通常は、

�@ それぞれの文字列の長さを求める - LEN関数
�A 一番長い文字列を探す - MAX関数
�B その行位置を調べる - MATCH関数
�C 行位置から求める文字列を出す - INDEX関数

の手順で行うことになるかと思われます。

配列数式を使うと、これらを1行の数式に纏めることが出来ます。
この問題で配列数式を使うべきかは異論があると思いますが、
この手法をマスターすることで、どのような場面でも配列数式を適用することが出来るようになることでしょう。

例１．二つのセル範囲で、等しくないデータの個数を調べる

例2. セル範囲内で、最大値の位置(アドレス)を調べる

セルＤ４６の式

{=ADDRESS(MIN(IF(D36:D44=MAX(D36:D44), ROW(D36:D44),"")), COLUMN(D36:D44))}

注: {=ADDRESS(IF(D36:D44-MAX [D36:D44),ROW(D36:D44),""),COLUMN(D36:D44))} とは書けません。
　配列数式 IF(D36:D44=MAX(D36:D44),ROW(D36:D44),"") は配列で返るため、
　ADDRESS関数では受け取れないためです。

上記の式の意味

・作業用の内部配列に
　指定範囲内のデータがその範囲内の最大値と等しいとき行番号をセット
　等しくなかったら空白をセット
・作業配列をMIN関数に渡す - 返り値は最小の行番号
・行番号と列番号をADDRESS関数に渡して、そのセルのアドレスが返る。

実際には、範囲に名前を付けておいた方が良いでしょう。
範囲 D36:D44 に”データ”と名前を付けておいた場合

{=ADDRESS(MIN(IF(データ=MAX(データ),ROW(データ),"")),COLUMN(データ))}

と、かなり見易くなります。

長所

短所

★ 配列関数で AND 関数とOR関数を直接使用することはできません。

★複数セルの配列数式を構成する個々のセルを上書きすることはできません。