1 つ目の検索条件の「男」を「女」に変更するには、数 式の「 $ F $ 2 」を「 $ G $ 2 」に変更します。 ーこも先ほどと 同様に、コピーされた数式のセルをダブルクリックして、 数式を 1 つ 1 つ編集します。修整した結果は、それぞれ次 のようになります。 1 = セル G =AVERAGEIFS($C$3:$C$14,$A$3:$A$14 , $ G $ 2 , $ B $ 3 : $ B $ 14 , "<2 の ル 04 =AVERAGEIFS($C$3:$C$14,$A$3:$A$14 , $ G $ 2 , $ B $ 3 : $ B $ 14 , " > = 30 卩 , $ B $ 3 : $ B $ 1 4 , "<4 の 。 T30 女ル 0 印 =. =AVERAGElFS($C$3:$C$14,$A$3:$A$14 , $ G $ 2 , $ B $ 3 : $ B $ 14 》 = 20 ” , $ B $ 3 : $ B $ 1 4 , k30 り =AVERAGEIFS($C$3:$C$14,$A$3:$A$14 , $ G $ 2 , $ B $ 3 : $ B $ 1 4 》 = 40 B $ 3 : $ B $ 14 , k50 ” ) 1 1 6 していけば、どんな関数も使いこなせるからです。 「共通の構造」があります。それをしつかり見極めて応用 それる必要はありません。何度も言いますが、関数には さまざまな関数を取り上げていきますが、もはや関数をお はないでしようか。次の 4 章から、実践的な学びとして、 これだけでもいかに多くのことができるか実感されたので ーこまで 15 種類の関数を紹介してきました。 本書では、 =AVERAGElFS($C$3:$C$14 , $ A $ 3 : $ A $ 1 4 , $ G $ 2 , $ B $ 3 : $ B $ 1 4 》 = 5 の

さくいん PMT ・ PPMT ・ PROPER RANK ROUND ROUNDDOWN ROUNDUP ROWS SMALL ・ STDEVP SUM SUMIF ・ SUMIFS ・ UPPER ・ VARP ・ VLOOKUP WORKDAY ・ YEAR ・ ・・ 236 、 242 ・・ 241 ・・ 150 ・・ 43 ・・・ 38 ・・ 43 ・・ 46 ・・ 123 ・ 139 ・・ 1 6 、 19 、 28 ・・ 64 ・・ 73 、 97 ・・ 1 50 ・・ 139 ・・ 1 59 ・・ 228 ・・ 1 82 【関数】 ASC ・ AVERAGE AVERAGEIF AVERAGEIFS CE 旧 NG COLUMNS COUNT COUNTA ・ COUNTIF ・ COUNTIFS ・ DATE ・・ DAY EDATE ・ FLOOR FREQUENCY INDEX ・ IPMT LARGE LEFT LOWER MATCH MEDIAN MODE ・ MONTH NETWORKDAYS ・ PHONET ℃ ・・ 1 47 ・・ 22 、 36 ・・ 100 ・ 98 、 106 ・・ 206 ・・・ 52 ・・ 46 ・・ 46 ・・ 84 ・・ 90 ・・ 207 ・・ 186 ・・ 1 87 、 266 ・・ 200 ・・ 134 ・・ 53 、 58 、 151 、 166 、 193 、 207 ・・ 1 70 ・・ 241 ・・ 150 ・・ 123 ・・ 150 ・・ 170 ・・ 139 ・・ 182 ・・ 207 ・・ 143 ・・ 56 ・・ 55 ・・ 259 ・・ 258 ・・ 163 、 258 ・・ 258 ・・ 258 ・・ 258 ・・ 258 ・・ 189 、 259 、 266 ・・ 67 、 261 #DIV/O! ・ #NAME? ・ #NULL! ・ #NUM! ・ #REF! ・ #VALUE! ・ 268

複数の条件に合う 数値を平均する 男女別に 20 代の平均購入金額を求める AVERAGEIFS 先ほどは、「男」か「女」かを条件にして AVERAGEIF 関数を使って平均購入金額を求めました。今度は、「 20 代 の男の平均購入金額」という具合に、年齢層を条件に加え る場合を説明します。 2 つ以上の条件で平均を求めるとき は、 AVERAGEIFS 関数を使います ( 図 A)O 図 A 1 来店記録 7 ミ女 切第女 10 12 13 1 5 1 6 18 20 21 22 23 24 22 51 3 日 24 33 1 6 1 8 41 55 20 43 23 3 500 4 300 7 500 6 000 4 300 1 500 3 00 2 500 3 500 1 00 9 日 00 1 日 00 平物購入 . 額 1 0 は、 20 イ、 30 イ、 40 代 50 代以 20 代男性の平均購入金額 を求める ここでは、 20 代男性の平均購入金額を求めます。入力 106 歳未満」の 2 つの条件が必要になります。 つです。「 20 代」を条件とする場合、「 20 歳以上」と「 30 設定する条件は、「男」と「 20 歳以上」「 30 歳未満」の 3 条件に合う数値を合計する ) とほとんど同じです。 方法は、この章ですでに紹介した SUMIFS 関数 ( 複数の

3 章データの集計に役立つ関数 8 条件に合う数値を合計する SLJMIF ・ 9 複数の条件に合う数値を合計する SUMIFS ・ 10 条件に合うデータの個数を数える COUNTIF ・ 11 複数の条件に合うデータの個数を数える COUNTIFS ・ 12 条件に合う数値を平均する AVERAGEIF ・・ 13 複数の条件に合う数値を平均する AVERAGEIFS ・・・・ 106 4 章データの分析に役立つ関数 14 数値を比較して順位を付ける RANK ・ 15 指定した順位の数値を求める LARGE 、 SMALL ・ 16 最も頻繁に登場している数値を調べる MODE ・・ 17 データの分布を調べる FREQUENCY ・ 6 7 8 ・ 90 ・・ 100 ・・ 1 18 ・・ 123 ・・ 131 ・・ 134 9

3 章データの集計に役立つ関数 少し先取りしますが、入力する数式とその構造は次のよう になります。 「 =AVERAGEIFS($C$3:$C$14,$A$3:$A$14,E2,$B$3:$B$14 , " > = 20 " , $ B $ 3 : $ B $ 14 , k30 " ) 」 =AVERAGElFS($C$3:$C$14 , E2, $B$3:$B$14 , k30 り ・・平均対象範囲のセル C3 ~ C14 ・・ 1 つ目の条件の検索範囲である セル A3 ~ AI 4 ・ 1 つ目の検索条件である「男」 が入力されているセル E2 ・・ 2 つ目の条件の検索範囲である セル B3 ~ BI 4 ・・ 2 つ目の条件である「 20 歳以上」 を表す論理式 ・・ 3 つ目の条件の検索範囲である セル B3 ~ BI 4 ・・ 3 つ目の条件である「 30 歳未満」 を表す論理式 見ての通り、 SUMIFS 関数と AVERAGEIFS 関数では、 最初の引数が「集計の対象となる範囲」で、その後は「検 索する範囲」と「条件」を繰り返しています。つまり、ど ちらか 1 つを覚えれば、もう 1 つに応用するのは簡単で す。条件付きで集計する「〇〇 IF 」や「〇〇 IFS 」とい う関数は、本書で扱う以外にも数種類ありますが、基本的 な構造は共通です。 働きがまったく違う関数では、引数が大きく変わります が、この章で扱った関数のように、働きが似ているもので はある程度共通した構造になっています。ですから Excel の関数が 300 種類以上あるといっても、いくつかの構造を 覚えれば、大半を使いこなせるようになるのです。 99

3 章データの集計に役立つ関数 O " ク 。 > す < カセラ 「入 3 ド で使う関 Excel 2003 では使えません 複数の条件が満たされるデータと同 じ行にある数値の平均値を求める = AVERAG 日 FS ( 平均対象範囲 , 条件範囲 1 , 条件 1 條件範囲 2 ] , [ 条件 2 ト・ ) 0 0 ・ 0 0 ・・平均を求める数値が入力されているセル範囲を指定する 0 ・・条件が満たされているか調べるセル範囲を指定する ・・・条件にする文字列や数値、論理式などを指定する AVERAGEIFS 関数は、「 =AVERAGEIFS 」の後に続く ) 」に指定した複数の条件が満たされるデータを平均し ます。段階を追って説明していきましよう。 ①入力するセルの選択 平均購入金額を表示したいセル F4 を選択します。 ②関数名の入力 「 =AVERAGEIFS( 」を入力します。 ③平均範囲の指定 平均したい数値が入力されているセル範囲を指定しま す。セル C3 ~ CI 4 をドラッグします。このとき「 ( 」に 続けて「 C3 : C14 」と表示されます。 =AVERAGEIFS(C3:C1 4 AVERAGE 1 来店者記録 平均購入、 . 購入金額 2 10 イは、 3 ・ 3 500 20 イ . 4 300 ・ 7 5 5 30 6 40 50 は、 7 8 9 3 ・ 2 5 3 アベレージイフズ AVERAGEIFS 1 =AVERAGEIFS(C301 4 2 8 107

3 章データの集計に役立つ関数 =AVERAGEIFS($C$3:$C$14 , $ A $ 3 : $ A $ 1 4 , $ F $ 2 , $ B $ 3 : $ B $ 1 4 , "<2 の ル虧 30 代 =AVERAGEIFS($C$3:$C$14 , $ A $ 3 : $ A $ 14 , $ F $ 2 , $ B $ 3 : $ B $ 14 》 = 30 卩 , $ B $ 3 : $ B $ 14 , k40 り 40 ゾにセルル =AVERAGEIFS($C$3:$C$14,$A$3:$A$14,$F$2,$B$3:$B$14 》 = 40 " , $ B $ 3 : $ B $ 1 4 , "<5 の 0 、以セル =AVERAGEIFS($C$3:$C$14,$A$3:$A$14 , $ F $ 2 , $ B $ 3 : $ B $ 1 4 , " > = 5 の 数式をコピーして女性の平均購入金額を求める 女性の各年代の平均購入金額もコピーして求めます。セ ル F3 ~ F7 を選択し、ハンドルをドラッグしてセル G3 ~ G7 へ数式をコピーします。 =AVERAG 日円($C$3 $ 0 $ 1 4 , $ A $ 3 : $ A $ 14. $ 2 , $ 日 $ 3 : $ 日 $ 14. 平均購人額 、店記録 2 10 、 . l: 、 3 22 3 500 51 4 300 20 3 日 7 500 40 代 24 6 OOO 33 8 1 500 9 男 1 日 335 〕 1 0 55 3 500 表示された平均購入金額は、男性のものです。 1 つ目の 検索条件として「男」を指すセル F2 を絶対参照にしてい この条件を「女」に変更します。 たためです。今度は、 =AVERAGEIFS($C$3 $ 0 $ 1 4 $ 23 $ 214. $ 円 2 $ 日 $ 3 ・ $ 14. 男性の平均購 半均入、・ 1 ~ 来店者記 入金額が表示 2 1 0 3 3 500 される 4 4 30 ) 20 5 30 6 6 000 40 50 は、 4 300 8 1 500 9 3 300 2 500 、以 セル F3 ~ F7 を選 択して、ハンドル をドラッグする 3300 3 25 ( 3300 33 〔旧 3900 3900 7500 7500 2500 2500 4300 4 2 0 っ凵 CXJ っ凵 1 1 4 ・一 1 0 1 15

3 章データの集計に役立つ関数 ここでは男性の平均購入金額を求めます。 AVERAGEIF 関数では、「 =AVERAGEIF 」の後に続く 「 ( ) 」内に指定した条件が満たされるデータを平均しま す。段階を追って説明していきましよう。 ①入力するセルの選択 男性の平均購入金額を表示したいセル F2 を選択します。 ②関数名の入力 「 =AVERAGEIF( 」を入力します。 ③データを調べる範囲の指定 次に、条件が満たされているデータがあるかを調べるセ ル範囲を指定します。 こでは、性別が入力されているセ ル範囲になるので、セル A3 ~ A14 をドラッグします。 このとき「 ( 」に続けて「 A3 : A14 」と表示されます。 =AVERAGEIF()3 AI 4 AVERAGE 1 来店者記録 平均購人 1 5 1 6 1 7 20 22 : 23 24 25 26 ①セル F2 を選択する =AVERAGäF()3 AI 4 5 000 4 00 1 5 000 1 2 1 00 25 000 1 5 00 日 000 2 5 〔旧 3 38 ー 9 日 00 1 9 日 00 ②「 =AVERAGEIF( 」 を入力する 22 23 101

3 章データの集計に役立つ関数 =AVERAGEIF($A$3 $ 14. 鷲 ' 均購 =AVERAGElF($A$3 $A$1 4. 田 . AVERAGE 1 广者記録 2 4 6 7 8 9 3 「 , 」を入力する 5 000 4 300 1 5 000 1 2 1 Cn3 25 000 1 5 200 8 000 2 50 〕 3 30 ) 9 日〕 1 06 ( 1 9 日 00 ロ 0 ク一 4 ・つ凵 ( 0 0 7 ・つつ」 ( 0 ⑥セル E2 を選択する ②「 , 」を入力する 女男 ⑧平均範囲の指定 平均したい数値が入力されているセル範囲を指定しま す。このとき注意すべきことは、指定する平均範囲は、デ ータを調べる範囲 ( ③ ) と平行する同じ行数でなければい けないことです。この場合は、購入金額が入力されている セル C3 ~ CI 4 をドラッグします。このとき「 , 」に続い て「 C3 : C14 」と表示されます。 AVERAGE =AVERAGEIF($A$3$A$14 , , 03 : 01 4 1 来店者 : 均購人・ =AVERAGEIF($Å$3$AS14 , E2.03014 3 4 5 6 7 8 ー盟廻町し亠ニ ! ッ 35 5 ) 0 4 300 38 1 5 000 24 1 2 1 00 33 25 000 1 6 1 5 00 25 日 000 2 500 33 800 22 1 0600 23 ~ コ意ー ⑧セル C3 ~ C14 を ドラッグする 103

④参照するセル範囲を絶対参照へ切り替え そのままカーソルを移動させずに、キーを 1 回押して 絶対参照にします。先ほどは「 C3 : C14 」だった表示が 「 $ C $ 3 : $ C $ 14 」に変わります。ここまでで、「 =AVERAGEIFS ( $ C $ 3 : $ C $ 14 」と表示されています。 ⑤引数の区切りを意味する「 , 」の入力 カーソルが「 $ C $ 14 」の直後にあることを確認してから、 「 , 」 ( カンマ ) を入力します。 ⑥ 1 つ目の条件となるデータを調べる範囲の指定 次に、 1 つ目の条件が満たされているデータがあるかを 調べるセル範囲を指定します。この場合、「男」か「女」 が入力されているセル範囲になるので、セル A3 ~ AI 4 をドラッグします。 表示されます。 AVERAGE 1 来店者記録 このとき「 , 」に続けて「 A3 : A14 」と =AVERAG 日 FS($C$3 $C$1 4 , A3 : AI 4 平均鰤入金額 ④朝キーを 1 回押す 3 5 6 7 9 1 6 108 女・ 女・ 22 51 3 日 24 33 1 6 41 20 4 23 3 500 4 300 7 500 6 000 4 300 1 500 3300 9 日〔旧 10 イは、 20 30 イ 40 50 は、 =AVERAGEIFS($C$3SD$1 4. A3 : AI 4 $ 0 $ 3 : $ 0 $ 1 4 「 $ C $ 3 : $ C $ 14 」 となる ⑥セル A3 ~ A14 を ドラッグする