MASTER / EXCEL

関数を使った集計

電卓での手計算から、計算方法が表に残る集計へ。SUM・AVERAGE・IF・SUMIF・XLOOKUPを使い分け、結果を検算する

約20分入門演習 5問講義をはじめる

※ みなと商事と受注データ、「3万円以上は要確認」という条件は、説明のための架空の例です。XLOOKUPは利用するExcelのバージョンによって使えない場合があります。

この講義でできるようになること

  • SUMとAVERAGEで合計と平均を求め、空白と0の扱いの違いを説明できる
  • IFで条件に応じた目印を付け、SUMIFで担当者別の合計を求められる
  • XLOOKUPで商品マスターから商品名を参照し、集計結果を手計算で検算できる

前提知識

特になし。はじめての人でも学べます。

Excel・表計算

00:00 / 06:33

1第1章手計算より再現できる集計

Space 再生/停止・← → 10秒移動・N / P で章を移動・C で字幕。右の「文字で読む」で、話している内容を文字で追えます。

演習:手を動かして確かめる

講義を見終えたら取り組みましょう。入力した内容はこの画面の中で使うだけで、どこにも送信されません。

問題 1 / 5

担当者がC列、金額がG列(2〜11行目)にあります。「佐藤」さんの金額だけを合計する数式はどれ?

選択肢

関数による集計の確認リスト

  • 集計の前に、データの範囲と何を集計したいかを確認したか
  • 集計範囲が最終行まで届いているか
  • SUMIFの条件範囲と合計範囲の行が対応しているか
  • SUMIFの条件(担当者名など)が、実データの表記と一致しているか
  • XLOOKUPで「未登録」が出たら、推測で置き換えずにコードとマスターを確認したか
  • 結果を信じる前に、数行を手計算で照合したか
次の講座データの整理

次に学ぶなら

テキストで読む(文字起こし)

第1章 手計算より再現できる集計

前回作成した受注一覧を使い、月次報告に必要な数字を出しましょう 電卓で1行ずつ足す方法では、新しい注文が追加されるたびに 作業をやり直す必要があります 関数を使うと計算方法を表の中に残せます ただし数式が正しくても、元データが不正確なら結果は誤ります まずデータの範囲と何を集計したいかを確認する姿勢が重要です SUMは数値を合計します 売上金額がG2からG11なら=SUM(G2:G11)です AVERAGEは数値の平均を求め=AVERAGE(G2:G11)とします 平均値は受注1件あたり売上げの参考になりますが 空白やゼロの扱いには注意が必要です ゼロは集計対象となる一方 空白セルは平均の計算対象になりません 値の意味を確かめてから使いましょう G列の合計と平均を計算し 電卓で数行検算しましょう 動画を一時停止して手を動かしてください

第2章 IF・SUMIFとXLOOKUP

IFは条件に応じて結果を分けます 例えばIF、G2が3万以上なら要確認 そうでなければ通常と入力すると 3万円以上の受注に確認の目印がつきます これは承認ルールを自動的に決めるものではなく 練習用の仮条件です SUMIFは指定した条件に一致するデータを合計します 担当者がC列、金額がG列ならイコール SUMIF、C2からC11が佐藤の行のG2からG11を合計です 条件範囲と合計範囲の行が対応しているか必ず確認してください SUMIFで一人の担当者だけを集計しましょう 動画を一時停止して手を動かしてください 受注表に商品コードがあっても 商品名や標準単価がないケースを考えます 商品マスターのA列をコード B列を商品名として イコールXLOOKUP、D2を商品マスターのA列から探し B列を返す、見つからなければ未登録とすると 該当する商品名を表示できます XLOOKUPは利用するExcelのバージョンによって 使えない場合があります 古い環境では代替手段が必要です また、コードの重複や文字列・数値の不一致があると 意図した結果にならないことがあります XLOOKUPの検索値を未登録コードへ変えて挙動を見ましょう 動画を一時停止して手を動かしてください

第3章 演習と検算・まとめ

合計売上、平均受注額、担当者別の合計、3万円以上の目印、 商品コードからの商品名参照を作りましょう 結果を信じる前に、3行だけ、手計算で照合します SUMIFの担当者名が実データと一致しているか、XLOOKUPで未登録が出ていないか、 集計範囲が最終行まで届いているかを確認します 正しい関数を知るだけでなく、集計結果を検証することが実務スキルです。 確認問題です。 1問目、SUMIFで重要な範囲の条件は何でしょうか? 2問目、XLOOKUPで未登録が出たらどうしますか? 3問目、AVERAGEで空白とゼロは同じでしょうか? 答え合わせです。 1問目は条件範囲と合計範囲の行が対応していることです 2問目はコードとマスターを確認し 推測で置き換えないことです 3問目は異なります 空白は通常対象外 0は対象となります 次回は関数で計算する前提となる データの品質を整えます