3 章データの集計に役立つ関数 この後、キーを押すと関数の入力は完了し、 30 歳 以上 40 歳未満の「 30 代」が数えられます。 =OOlJNTIFS($B$3 $ 日 $ 1 4 : 〉 = 20 を $ 日 $ 3 : $ 日 $ 1 4 : ・く 30 " ) 1 来店者記録 2 3 5 7 1 3 1 4 23 店者 7 5 5 000 4 300 年齡層別 1 5 000 1 0 は、 1 2 1 00 25 000 20 30 代 1 5 200 40 代 日 08 ー 2 50 〕 50 3 300 9 800 1 0600 1 9 日 00 「 10 代以下」「 40 代」「 50 代」についても同様に修正しま す。それぞれの数式の修正結果は以下のようになります。 =COUNTIFS($B$3:$B$14 , k20 り 40 代 ~ , セル、 F =COUNTIFS($B$3:$B$14 , " > = 40 ” , $ B $ 3 : $ B $ 1 4 , k50 ” ) = 50 、以。 ( セル 0 ) 餐 =COUNTIFS($B$3:$B$14,">=50") 最初の「 10 代以下」は、「 0 以上」ということで下限が 不要です。そのため、 1 つ目の条件を削除しています。ま た、最後の「 50 代以上」は、「 50 以上」ということで上限 が不要です。そのため、 2 つ目の条件を削除しています。 さて、ここで問題です。「 20 代の男性の購入金額の合計」 を求めるにはどうすればよいでしようか。もうおわかりで すね。 2 つ前の項目で学んだ SUMIFS 関数を使い、条件 が 3 つの場合を考えればよいのです。この場合、条件とは 「男」と「 20 歳以上」「 30 歳未満」の 3 つです。 30 代の来店者数が 表示される 97

3 章 データの集計に役立つ関数 男 男 店男男 12 ~ 3 ( 4 5 一「 6 ~ 7 ( 日 80 ( 123 4 ~ 来店者 7 5 5 2 日 4 3 6 5 3 9 2 3 っ凵 4 ・ ( 0 〔 0 1 ー 0 」 4 ・に 0 ィー 0 」のこ 4 300 年層月 1 5 〔 )O( 〕 10 代 1 、 1 2 1 00 25 ) 0 20 30 代 1 5 2C0 日 80 40 代 50 代 2 5CCJ 3 3(Xl 9 80 〕 1 060 〕 1 9 800 数式をコピーして他の年代も求める セル F7 に「 20 代」の来店者数が数えられましたので、 他の年代の来店者数も求めてみます。セル F7 を選択し、 ハンドルをドラッグしてセル F6 とセル F8 ~ F10 へ数式 をコピーします ( ① ) 。 = COIJNTI 円 ( $ 日 $ 3 $ 日 $ 1 4 : 〉 = 20 " $ 3 $ 日 $ 1 4 : く 30 " ) 1 来店者記録 2 ~ 第片 3 35 4 女 42 5 男 3 日 24 33 8 25 43 20 代の来店者数が 表示される 4 ①セル F7 を選択して、 ハンドルをドラッグ する 来店者 5 0 〔 4 300 年齢層別 1 5 COD 10 代 1 、 1 2 1 〔旧 25 0 〔 20 1 5 2 〔旧 30 8 08 40 代 50 代、 2 5 〔 7 5 = COUNTI 円 ( $ 3 ・ $ 1 4 " 〉 = 20 " $ 日 $ 3 ・ $ 1 4 : く 30 " ) 記 阿要者 店 イ ! 4 ( 0 4 「 5 : 0 7 日 9 ~ 5 000 4 3 〔 年齢層別 1 5 10 代 1 、 1 2 1 〕 25 20 1 5 30 8 CO) 40 250 〕 50 3 3 〔 7 5 4 1 0 95

キーを押すごとに、「 $ B $ 4 : $ B $ 14 」「 B $ 4 : B $ 14 」「 $ B4 : $ B14 」「 B4 : B14 」の順で他の参照形式に切り替わります。 他の参照形式になってしまった場合は、「 B4 : B14 」を選択 し直してからキーを押します。 ⑤引数の区切りを意味する「 , 」の入力 カーソルが「 $ B $ 14 」の直後にあることを確認してから、 匚」 ( カンマ ) を入力します。 ⑥検索条件の指定 次に、データを調べる際の検索条件を指定します。この 場合、「インスタントコーヒー」を調べるので、セル BI 6 を選択します。このとき「 , 」に続けて「 B16 」と表示され ます。 ⑦引数の区切りを意味する「 , 」の入力 カーソルが「 B16 」の直後にあることを確認してから、 区切りを意味する「 , 」を入力します。① ~ ⑦の操作で、 「 =SUMIF($B$4:$B$14,B16, 」と表示されています。 =SLJMIF($8$4 $ 日 $ 14. 日 16. AVERAGE 売上げ記録表 2 3 201 0 / 2 / 1 ンスタンコーヒー 5 - 三 201 0 / 2 / 1 覊 201 0 / 2 / 2 レッドテ 201 0 / 2 / 2 緑茶 2010 / 2 / 2 インスタントコーヒー 8 201 0 / 2 / 3 ンスタン コーヒー 9 1 0 ま 201 0 / 2 / 3 吊、 1 1 2010 / 2 / 3 レッドティー 12 : 201 0 / 2 / 4 インスタン コーヒー 13 201 0 / 2 / 4 芦 14 201 0 / 2 / 5 ンスタン コーヒー 15 1 17 20 ⑤「 , 」を入力する O 〔ロ 4 ・のこ 9 1 3 C-O つ」 600 450 日 00 450 600 600 450 B00 600 450 600 ⑥セル B16 を 選択する 1 6 = SIJMI 气 4 ・ $ 日 $ 14. 引ら . ー第を心蠍を域 . レぃ " テ ②「 , 」を入力する 68

必すお読みくたさい 本書では、以下の環境を使って、そのすべての機能を確 認して執筆しています。 OS ・ Windows 7 Home Premium Windows XP Professional SP3 ソフト : Microsoft Office Excel 2007 Microsoft Office Excel 2003 Microsoft Office Excel 2010 べータ版 本書に掲載されている情報は、 2010 年 2 月現在のもの です。ご利用時は変更されている場合もありますので、あ らかじめご了承ください。また、コンピュータソフトとい う性格上、本書はご紹介しているソフトの安全性を保証す るものではありません。本書で紹介している内容をご利用 いただく際は、すべて自己責任の原則で行ってください。 なお、本書は、 Excel の基本的な操作が一通りできる方 を対象にしております。そのため、表の作り方などの基本 的な操作については、操作手順に含めておりません。本書 の内容が難しいと思われた方は、 Excel の入門書もあわせ てお読みいただくことをお勧めします。 本書の中で記載されている氏名、会社名、住所、電話番 号等は、すべて架空のものです。実在したとしても偶然の 一致で、無関係です。 著者、ならびに講談社は、電話による質問にはいっさい お答えできません。 Mic 「 0S0 れ、 Windows 、 0 新 ce 、 ExceI は米国マイクロソフトコーポレ ーションの米国およびその他の国における登録商標です。 本書では TM 、⑨マークは明記していません。 ・カバーイラスト / 浅賀行雄 ・カバー装幀 / 芦澤泰偉・児崎雅淑

数式をコピーして他の年代も求める セル F4 に「 20 代男性」の平均購入金額が表示されまし たので、数式をコピーして他の年代の平均購入金額も求め てみます。セル F4 を選択し、ハンドルをドラッグしてセ ル F3 とセル F5 ~ F7 へ数式をコピーします。 =AVERAGEIFS($C$3 $C$1 4. $ 3 : $ 14. $ 2 , $ 日 $ 3 $ 日 $ 14. 朝 L = 。町三 0 L 、、上朝 1 来店者記録 平均購人、 . 額 性別齡購入金額 2 3 女 10 代 . 、き 22 3500 4 51 4 300 20 30 代 3 日 755 〕 40 代 6 24 6 0 〔旧 50 は、 435 〕 8 女 1 55 〕 1 6 1 日 33 〔旧 1 0 男 41 2500 55 3 500 20 1 00 43 9 00 1 4 23 1 00 セル F4 を選択して、 ハンドルをドラッグ する =AVERAG 日田($0$3$0$14 , $ 23 : $ 14. $ 2 , $ 日 $ 3 $ 日 $ 14. 平均購入金額 1 0 イは、 3 500 20 代 4 300 30 イ 7 500 40 代 6 80 50 弋以上 4 300 1 500 3 300 2 500 コピーされた数式は、どれも「 20 代男性」の平均購入 金額が表示されたままです。年代別に異なる条件を設定す る論理式は、それぞれ修整する必要があります。本書の COUNTIFS 関数の説明 ( 96 ~ 97 ページ ) でも行いまし たが、コピーされた数式のセルをダブルクリックして、各 年代別に 1 っ 1 つ編集します。修整した結果は、それぞれ 次のようになります。 つ」 1 , 03 4 ・ ( 0 ( 0 〔 0 1 ・ 記 店女男男女女男男 1 ~ り三 3 ( 4 5 6 フ 00 」 ( リ 3900 3900 三 114

3 章データの集計に役立つ関数 ⑩参照するセル範囲を絶対参照へ切り替え そのままカーソルを移動させずに、キーを 1 回押して 絶対参照にします。先ほどは「 B3 : B14 」だった表示が 「 $ B $ 3 : $ B $ 14 」に変わります。こまでで、「 =AVERAGE IFS ( $ C $ 3 : $ C $ 14 , $ A $ 3 : $ A $ 14 , $ F $ 2 , $ B $ 3 : $ B $ 14 」と表示さ れています。 ⑩引数の区切りを意味する「 , 」の入力 カーソルが「 $ B $ 14 」の直後にあることを確認してから、 「 , 」を入力します。 ⑩ 2 つ目の検索条件の指定 次に、データを調べる際の検索条件を指定します。この 場合、「 20 歳以上」を調べるので、「 " > = 20 " 」を入力しま す。「 > = 」は「 ~ 以上」を表す論理式です。論理式を条件 とする場合「 " 」 ( ダブルクオーテーション ) で挟んで入力 します。 =AVERAGEIFS($C$3 $ 0 $ 1 $ 3 : $ A $ 1 4 , $ 円 2 , $ 日 $ 3 ・ $ 日 $ 1 4 , AVERAGE 来店者記録 平均購、 . 額 2 性別齡 購 金額 10 代 . 、 3 500 4 300 20 5 3 日 7 500 30 6 80 40 イ 6 24 50 代↓、 7 33 4 300 1 50 〕 3 3 〔 25d ー 55 3 0 〕 20 1 0 〕 13 43 9 80 ) 23 1 5 1 7 1 9 20 21 22 23 24 25 入す > カ の 6 を E # - ロいを 。・望キーを 1 回押す =AVERAGEIFS($O$3$C$1 4 $ 3 を $ 14. $ 日 2 , $ 3 $ 畔動可「件 1 コ条件重己幻条 、「 R G ま十均対象 「 $ B $ 3 : $ B $ 14 」 となる $ 日 $ 3 : $ 日 $ 14. " 〉 = 20 " 111

3 章データの集計に役立つ関数 使ら関 Excel 2003 では使えません 複数の条件が満たされるデータの 個数を数える = COUNT 旧 s ( 検索条件範囲 1 , 検索条件 1 , [ 検索条件範囲 2 Ⅱ検索条件 2 ト ) 0 0 0 0 0 ・・・条件が満たされているか調べるセル範囲を指定する 0 ・条件にする文字列や数値、論理式などを指定する こでは、はじめに 20 代の来店者数を求めます。 COUNTIFS 関数は、「 =COUNTIFS 」の後に続く「 ( に指定した複数の条件が満たされるセルの数を数えます。 段階を追って説明していきましよう。 ①入力するセルの選択 20 代の来店者数を表示したいセル F7 を選択します。 ②関数名の入力 「 =COUNTIFS( 」を入力します。 ③ 1 つ目の条件のデータを調べる範囲の指定 次に、 1 つ目の条件が満たされているデータがあるかを ーこでは、年齢が入力され 調べるセル範囲を指定します。 ているセル B3 ~ BI 4 をドラッグします。 =COlJNTIFS()3 日 1 4 AVERAGE 1 来店者記録 年 2 3 ・ 3 引 4 5 男 ・ 3 日 6 24 ・ 33 9 ・ 25 ・ 5 物 カウントイフス COUNTIFS き一第第一①セル F7 を選択する ②「 =COUNTIFS( 」 を入力する 額 5 000 4 3 〔 年齢層別 1 5 00 〕 1 0 は、 1 2 1 00 25 C03 20 1 5 00 30 8 0 〕 0 40 2 5 〔 )O 50 3 3 〔 106 〔 1 9 8 〔 7 5 =COUNTIFS()3 日 1 4 1 をっ 4 3 セル B3 ~ B14 をドラッグする 2

3 章データの集計に役立つ関数 ④参照するセル範囲を絶対参照へ切り替え そのままカーソルを移動させずに、キーを 1 回押しま す。先ほどは「 B4 : B14 」だった表示が「 $ B $ 4 : $ B $ 14 」に 変わります。 このように、セルの列番号と行番号それぞれに「 $ 」が 付くと、セルの参照方法が「絶対参照」という方法にな り、データを調べる範囲は「 B4 : B14 」で固定されます。 こうするには理由があります。現在、セル DI 6 に入力し ている数式は、あとでセル DI 7 とセル DI 8 にコピーする ことが確実です。なぜなら、「インスタントコーヒー」の 合計個数を求めたあとは、当然、「緑茶」と「レッドティ ー」の合計個数も求めることになるからです。このとき、 「絶対参照」になっていないと、コピーした数式では、「 B4 : B14 」であるべきセル範囲が「 B5 : B15 」「 B6 : B16 」とずれ てしまいます。「絶対参照」にすると、コピーした数式内 でも「 B4 : B14 」で固定されます。 =SLJMIF($8$4 $ 1 4 AVERAGE 売上げ記録表 2 201 0 / 2 / 1 ンスタン コーヒー 4 5 201 0 / 2 / 1 201 0 / 2 / 2 レッドテ 6 7 201 0 / 2 / 2 緑 , 201 0 / 2 / 2 ンスタン コーヒー 日 201 0 / 2 / 3 ンスタン コーヒー 9 1 0 201 0 / 2 / 3 覊 201 0 / 2 / 3 レ、、 " テ 201 0 / 2 / 4 ンスタン コーヒー 1 2 1 3 201 0 / 2 / 4 糸 1 4 201 0 / 2 / 5 ンスタン コーヒー インスタン コーヒー レい・・テ 0 〔 0 4 ・の」 9 600 450 450 600 600 450 日 00 6m 450 6 〔 「 $ B $ 4 : $ B $ 14 」 となる 1 3 っ凵つ」 ( 0 ( 0 〔 0 1 ・イー 1-- ィー =SUMIF($8$4 $ 日 $ 1 4 っ , 日 9 ~ 0 ~ 1 。 2 ィー 1 . ーク第ク」 ~ ク一 $ 日 $ 4 : $ 日 $ 1 4 67

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

7 章金利の計算に役立つ関数 29 月々の返済額を調べる PMT ・ 30 月々の返済額の元金と金利がどれくらいかを調べる PMT 、 PPMT 、 IPMT ・ おわりに 付録 1 付録 2 付録 3 付録 4 付録 5 表示されるエラー値とその原因・対処方法 相対参照と絶対参照 セルと表示形式 日付・時刻とシリアル値 分析ツールアドインのインストール・ さくいん・ ・・ 236 ・・ 257 ・・ 241 ・・ 268 ・・ 266 ・・ 264 ・・ 262 ・ 260 ・・ 258