④ 1 つ目の検索条件の変更 セル F4 からコピーした数式が表示されたら、 2 つある 検索条件のうち、営業所の条件のみ変更します。検索条件 となる「豊島」はセル E8 にあります。「 $ E $ 3 」の「 3 」を 「 8 」に変更します。 ⑤キーを押して確定 最後にキーを押すと入力が完了します。セル F9 に 豊島営業所の消耗品費の合計金額が表示されます。 AVERAGE =SUMIFS($C$4 $ 0 $ 1 6 , $ 24 $ 216. $ 日 , $ 日 $ 4 $ 日 $ 16. 田 ) 経費管表 営業所勘宀 4 。文京 6 。豊島 新聞図書 7 新 、耗品費 第文ー 告運賃 10 を練 , 、 耗品費 1 1 文 賃 1 2 13 14 賃 新聞図書 「 1 6 20 変 △計 1 00 荷造運賃費 1 600 日 80 聞ッ費 3 2 C00 500 を受島 日 00 、肖耗品費 = SLJMI 田 ( $ 0 $ 4 。 $ 0 $ 1 63 邸 4 $ 1 6 E. $ 8 。 $ 日 $ 4 $ 日 $ 1 6 第 900 荷造運賃費 日 00 300 練馬 日 00 耗品費 3 80 荷造運賃費 聞ッ書 6 4 5 ー Enter F10 経費管理表 3 1 00 消耗品 1 00 5 日 00 荷造運賃費 1 600 6 島 新聞図書費 日 80 3 〔 0 7 練馬 新聞図書 2 〔〕 日 ,. 豊島 消耗品費 500 9 800 98 荷造運賃 1 1 文京 苛、買費 88 新聞図書 賃 1 58 38 馬 荷造運賃費 消耗品費 14 早 新聞図書費 3 08 荷造連賃費 15 馬 新聞図書 6 08 聞、、書 16 1 7 豊島営業所の消耗 品費の合計金額が 表示される 82

数式をコピーして他の科目の合計を求める 入力した数式をコピーします。セル F4 を選択し、 ノ、ン ドルを下向きにセル F6 までドラッグします。 F4 = SLJ Ⅷ円 ( $ c $ 4 - $ 0 $ 1 6 , $ 4 $ 216. $ 3. $ 日 $ 4 : $ 16. E4 ) 経査管理表 2 3 営業 1 耗品費 1 5 日荷造連賃費 新聞図書 日 80 新聞、、書 6 7 練馬 新聞図書 2 00 〕 耗品費 8 58 ー 苛造運賃 800 、肖耗品費 9 1 0 練馬 消耗品費 900 荷造運賃費 11 文 荷造連賃 日 00 聞図書 豊島 1 2 荷造連賃費 1 500 消耗ロ費 1 3 豊島 300 練馬 1 4 練馬 荷、上、賃 日 00 消耗品費 1 5 文京 新聞ッ 3 000 荷、運賃 1 7 8 すると、数式がコピーされ「荷造運賃費」と「新聞図書 費」の合計金額が表示されます。 コピーした数式を確認するため、セル F5 を選択して数 式バーを見ると、引数として指定したセル範囲が、コピー した数式でも正しく参照されていることがわかります。 = SLJMI 田 ( $ 0 $ 4 : $ 0 $ 16. $ 4 $ A $ 16. $ 3 , $ 日 $ 4 ・ $ 日 $ 16. ) =SUMIFS($C$4$C$16 $A$4 $ 1 6 3 ′ $ 日 $ 4 $ 日 $ 1 田 ) 1 経査管理表 2 業 勘定科目 支額 3 消耗品費 4 1 00 荷造連買 日 00 荷造運賃費 6 豊島 新聞図書 日 80 新聞図書費 3 000 7 練馬 新聞メ 2 C03 豊島 消耗ロ費 5CJO 9 文京 荷告運賃費 日 00 冫肖耗品聳 10 」練馬 ′肖耗品費 荷造運賃 900 1 1 文 告運賃費 日 00 新聞 造運賃 12 1 00 消耗品費 300 荷造運賃 耗品費 14 日 00 早 新聞図書 ロ告運賃費 15 3 〔 0 16 練馬 図書費 新聞図書 引兀 0 セル F4 を選択して、 ハンドルをドラッグ する セル範囲が正しく参照 されている 80

3 章データの集計に役立つ関数 複数の条件に合う数値を 合計する 各営業所の経費を科目ごとに合計する なんらかの一覧表において、 2 つの条件で絞り込んで、 その数値を合計したい場合があります。たとえば、図 A の一覧表で、各営業所の経費を科目ごとに合計するような ときです。この場合、どの営業所か、どの科目かという複 数の条件で合計する必要があります。 条件が 1 つの場合は、 SUMIF 関数を使いましたが、 のように条件が複数の場合は SUM 旧 S 関数を使います ( 図 0 =SLJMIFS($C$4C$1 6.24 216 , $ 3. $ 日 $ 4 $ 日 $ 1 6 上 4 ) SUMIFS 表 1 っ← 3 ) 4 ー 5 ~ 6 ~ 7 , 日 ~ 9 一 図 A 額 文京 1 耗品費 00 荷造運賃費 1 600 新聞 日 OOO 新聞図書費 3 000 2 OOO 50 ) 。造賃 88 冫、耗品費 800 10 練 . 、、 耗品費 900 荷造運賃費 1 00 荷造運賃費 800 新聞メ 8 000 12 豊 荷造運賃 13 豊島 耗品費 300 練馬 荷造連賃費 1 日 00 900 1 5 文 3 08 荷浩運買 新聞図書費 1 600 6 OOO 新聞図書費 1 6 練馬 新聞図書費 日 000 1 8 1 9 1 各営業所の経費を科目 ごとに合計できる それでは、 SUMIFS 関数を使って、列 C に入力されて いる経費を、各営業所別に勘定科目を分けて合計してみま す。はじめに文京営業所の消耗品費を合計します。 SUMIFS 関数は、「 =SUMIFS 」の後に続く「 ( ) 」に指 定した複数の条件が満たされるデータの数値を合計しま す。段階を追って説明していきましよう。 20 73

2 章必ず押さえておきたい基本の関数 数式をコヒ。ーする セル C3 を選択して、ハンドルをセル C7 までドラッグ し、数式をコピーします。 = 旧日 3 〉 = 60 : 合格・ . " 不合格 " ) 03 成績表 2 4 加藤 5 6 木内 7 9 O O 0 つ」っ」 セル C3 を選択して、 ハンドルをドラッグ する セル C4 ~ C7 に、合否の判定結果が表示されます。 = 旧田〉 = 60 " 合格 " " 不合格 " ) 合否の判定結果 が表示される 表 績青加木 ~ 1 2 4 5 6 よメ ( 日 9 0 0 5 2 2 「論理式」で「 = 」や「 > 」「く」を使い分けると、様々な 条件を指定できます。指定できる条件は次の表のとおりで す。 記号 条件 例 AI = 1 00 ( セル AI の値は 100 と等しい ) 等しい AI く 100 ( セル AI の値は 100 より小さい ) より小さい AI > 1 00 ( セル AI の値は 100 より大きい ) より大きい AI く = 100 ( セル AI の値は 100 以下 ) 以下 AI > = 100 ( セル AI の値は 100 以上 ) 以上 く > 等しくない AI く > 100 ( セル AI の値は 100 ではない )

6 章日付と時刻の処理に役立つ関数 = I 气 04 く日 4.04 + 1 一日 4 SLIM ④「 , 」を入力する 1 作業時間記録 2 社時刻、社時刻 3 3 : 00 = I 04 く日 4 04 + 1 一日 4 4 22 : 00 3 : 20 諞↓ 5 21 : 50 6 1 日 : 20 9 : 50 7 8 9 ~ 0 1 0 を 0 4 5 ⑥「 , 」の入力 引数の区切りを意味する「 , 」を入力します。 ⑦③の条件が満たされないときに行う計算式の入力 「 C4 ー B4 」を入力します。条件が満たされない場合、「退 社時刻ー出社時刻」の計算をします。 ⑧ D 」の入力 引数の指定が完了したので「 ) 」を入力します。 ⑨キーを押して確定 最後にキーを押すと入力が完了します。 = I 04 く日 4.04 + 1 一日 4 04 一日 4 ) SLIM 1 作業時間記録 3 日出社時刻退時刻 3 ・ 00 = I 气 04 く日 4 C4 + 1 一日 4 , C4 一日 4 ) 4 22 : 00 5 3 ・ 20 21 : 50 1 日 20 6 9 : 50 7 8 9 別入キ ⑥「 , 」を入力する 9 Enter 195

⑥「 , 」の入力 引数の区切りを意味する「 , 」を入力します。 ⑦一致するデータが見つからないときの対処方法の指定 セル A5 ~ A10 に「 20kg 」と一致するデータが見つか らない場合の対処方法を数値で指定します。 こでは、 致しないのであれば「 #N/A 」というエラー値が表示され るように「 0 」を入力します。 ⑧ D 」の入力 これで引数の指定がすべて完了しましたので「 ) 」を入 力します。 ⑨キーを押して確定 最後にキーを押すと入力が完了します。 SLIM 配達料金表 =MATCH()I . A5 : AI 0 , 0 ) ⑥「 , 」を入力する 2 3 4 5 8 9 10 1 2 1 1 4 15 ~ 1 7 1 日 20 21 22 23 24 25 26 東北 1500 2000 2500 3000 358 4000 関東 1000 1500 2000 2500 3000 3500 量 達 中部・ 北陸 1000 1500 208 258 3000 3500 =MATOH(F 1 5 0 0 Okg 関西・中国 ②「 0 」を入力する 2k 1 Ok 20k 三 50k 100k 北海道 2000 2500 3000 3500 4000 5000 関西 - 中国 1500 2000 2500 3500 4000 4500 四国 2000 2500 3000 3500 4000 5000 20 ( 2500 3000 3500 408 5000 2500 3000 3500 4000 4500 5000 ① [) 」を入力する ⑨キーを押す セル GI に「 4 」が表示されます。これは、「 20kg 」かセ 174 ル A5 から数えて 4 行目にあることを意味します。

⑥「 , 」の入力 引数の区切りを意味する「 , 」を入力します。 ⑦列番号の指定 「 MATCH(F2,B4:14,0) 」を入力します。こまでで、 「 =INDEX(B5:110,MATCH(F1,A5:A10,0),MATCH(F2,B4: 14 , 0 ) 」と表示されています。 ⑧ D 」の入力 これで引数の指定がすべて完了しましたので「 ) 」を入 力します。 ⑨キーを押して確定 最後にキーを押すと入力が完了します。セル II に 「 3500 」が表示されます。 =NDEX( 日 5 ll O,MATOH()I 譌 5 : AI 0.0 ) , MATOH ( F2 お 4 : 14 , 0 ) ) AVE RAGE ユ 配達科金表 重量 = 扣 EX ( 日 5110,MATOH(F 1 達先 3 中部・ 関西 - 北海道 東北 関東 四国九州 沖縄 北陸 中国 4 5 2000 1500 108 1000 1500 2000 280 2500 6 5k 2500 2000 1500 1500 280 2500 2500 3000 7 10k 3000 2500 208 2000 2500 308 3000 3500 日 20k 3500 3000 2500 2500 3500 358 3500 4000 9 50k 4000 3500 3000 3000 480 408 4000 458 1 0 100 5000 4000 3500 3500 4500 508 5000 5000 1 2 入力する ②「 MATCH(F2,B4:14,0) 」 ①「 ) 」を ⑨キー を入力する 入力するを押す 12 る れ さ 示 沖 8 輌カ 州 8 8 8 8 5 中 1 2 2 3 4 4 2 1 1 2 2 3 3 東 1 2 2 3 3 4 0 0 0 0 0 0 2 5 0 0 0 0 180

7 章金利の計算に役立つ関数 IPMT 関数で第 1 期目分の金利を計算する 続いて、 IPMT 関数で第 1 期目分の金利を求めます。 IPMT 関数は、「 =IPMT 」の後に続く「 ( ) 」内に指定し た利率、返済期間、借入金額の情報を使って、各期の金利 分の金額を求めます。 入力方法は、元金額を求める PPMT 関数と同じです。 セル D8 を選択し、「 =IPMT( 」を入力します。以降の入力 内容は、先ほどの PPMT 関数の③ ~ ⑩までとまったく同 じです。入力する内容は、「 =IPMT($C$3/12,A8,$C$4,$C$5, 0 , 0 ) 」となります。最後にキーを押すと入力が完了し ます。 SLIM 1 ローン返済シミュレーシン ( 元利均等 ) 2 年 3 4 6 7 9 = IPMT ( $ 0 $ 3 / 12 , , $ c $ 4 , $ 0 $ 500 ) 2.1 5 1 2 1 000000 学一 5 =IPMT($C$3/12 , . $ Q $ 4. $ 0 $ 5.0.0 ) ・キ 30 ? 2 セル D8 を選択し、「 = 旧 MT ( $ C $ 3 / 12 , A8 , $ C $ 4 , $ C $ 5 , 0 , 0 ) 」を入力しキーを押す セル D8 に第 1 期目分の金利額が表示されます。 D9 1 ローン返済シミュレション ( 元利均等 ) 2 3 返済期間 4 6 7 8 9 第 1 期目分の金利 額が表示される 2.15 % 1 05 〕 000 済都 ¥ 430 ? 4 ーっこ ( 0 4 ・ LD ¥ー 82 5 10 255

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

コピーされた数式の 1 つを確認します。「 30 代」の来店 者数を求めたいセル F8 を選択して数式バーを見てみると、 絶対参照にしたセル B3 ~ B14 の範囲は正しいのですが、 「 30 以上、 40 未満」とあるべき部分が「 20 以上、 30 未満」 のままになっています。これでは「 20 代」を数えてしま います。年代別に異なる条件を設定する論理式は、それぞ れ修正する必要があります。数式を編集できるように、セ ル F8 をダブルクリックします ( ② ) 。 = COIJNTI 円 ( $ 日 $ 3 : $ 日 $ 1 4 : > = 20 $ 3 - $ 日 $ 1 4 ! : k30 ~ ) 1 来店者記録 来店者故 2 女 「 " > = 20 " 」「 " く 30 " 」と 4 4 00 年齢層別 1 5 〔 20 4 なっている 1 0 、要 6 1 2 1 00 20 代 7 4 25 000 30 代 1 5 00 4 日 000 40 4 2 500 50 4 3 35 〕 1 1 0 600 23 1 9 日 00 数式を編集できるようになったら、「 30 代」を調べられ るように、「 " > = 20 " 」の「 20 」を選択して「 30 」に 「 " く 30 " 」の「 30 」を選択して「 40 」に修正します ( ③ ) 。 = COUNTI 円 ( $ 日 $ 3 $ 日 $ 1 4 " 〉ニ 30 ~ , $ 日 $ 3 $ 日 $ 1 4 " く 40 " ) AVERAGE 1 来店者記録 別年齡 購入金額 男 2 女 35 5 80 4 42 4 300 年齢層別 3 日 1 5 000 10 代 1 、 1 2 1 00 7 33 25 000 20 3 女 1 5 00 30 1 6 40 代 25 8 000 2 00 50 要 10 43 1 1 女 51 3300 12 女 9 日 00 1 9 22 1 0 600 13 1 9 日 00 14 23 ' く 30 " 7 5 ②セル F8 をダブル クリックする 「 " く 30 " 」の「 30 」を「 40 」に変更 する 3 7 5 4 4 = COUNTI 円 ( $ 日 $ 3 $ 日 $ 14. " > = 30 ". $ 3 $ 日 $ 14.. " く 40 " ) 4 ・・く 40 " 96