MASTER / DIGITAL SKILLS
Excelの関数と集計
関数は、何を知りたいかに合わせて選びます。SUM・AVERAGE・COUNT、IF、SUMIF、XLOOKUPで、総売上・担当別売上・目標達成の判定・所属の照合を作り、全件の合計と担当別の合計が一致するかを検算します。
約25分入門演習 5問講義をはじめる
※ みらい商事と、画面内のファイル・受注データ・担当者は、説明のための架空の例です。画面はWindows版Microsoft 365の例で、バージョンやMacでは表示や操作の名前が異なる場合があります。
この講義でできるようになること
- SUM・AVERAGE・COUNTで、合計・平均・件数を求められる
- IFで条件ごとに結果を変え、SUMIFで担当別に集計できる
- XLOOKUPで別表から情報を引き、見つからない場合を隠さずに表示できる
00:00 / 06:57
1第1章何を知りたいかで関数を選ぶ
Space 再生/停止・← → 10秒移動・N / P で章を移動・C で字幕。右の「文字で読む」で、話している内容を文字で追えます。
演習:手を動かして確かめる
講義を見終えたら取り組みましょう。入力した内容はこの画面の中で使うだけで、どこにも送信されません。
総売上(SUM)、件数(COUNT)、担当S01の売上合計(SUMIF)を求めてください。
関数で集計するときの確認リスト
- 何を知りたいか(総売上・担当別・判定・照合)を先に決めたか
- 関数の範囲(例:G2:G7)が、集計したいデータ全体を指しているか
- SUMIFの条件の列と集計の列が、同じ行どうしで対応しているか
- XLOOKUPで見つからないときに「未登録」などと表示し、欠損を隠していないか
- 全件の合計と、担当別の合計を足したものが一致するか検算したか
- 式が動いたことと、結果が正しいことを分けて確認したか
次に学ぶなら
テキストで読む(文字起こし)
第1章 何を知りたいかで関数を選ぶ
数十件の売上明細から、担当別の売上と目標の達成状況を急いで報告することになりました。 この講座では、サム・アベレージ・イフ・サムイフ・エックスルックアップを使って、 業務の集計ができるようになります。 練習では、みらい商事の10月の受注データ6件と、G列で計算した売上金額を使います。 数値を足し算するだけではなく 何を知りたいかに合わせて関数を選びます 今回の問いは4つです 全体の売上げはいくらか 担当ごとの売上げはいくらか 目標を達成したかどうか そして担当者がどの課に所属しているかです
第2章 合計・平均・件数と判定
まずは全体の売上です G2からG7の合計はイコール サムG2からG7 平均はイコールアベレージG2からG7 数値が入ったセルの件数はイコールカウントG2からG7です 結果は合計が20,500円、平均が約3,416.67円、件数が6件です カウントが数えるのは数値のセルなので、文字が入ったセルは件数に入りません 目標金額を別セルに置き、イコールイフG2大なりイコールH2達成、未達と書くと 条件ごとに結果を変えられます G2の売上がH2の目標以上なら達成 そうでなければ未達と表示されます この式を下の行にコピーすれば 6件すべての判定ができます 目標を式の中に直接書かずに別のセルに置いておけば 目標が変わってもそのセルを直すだけで済みます
第3章 条件で集計し、マスターと照合する
次は、担当ごとの売上です。 担当IDがS01の売上合計は、イコール、サムイフ、C2からC7、S01、 G2からG7。 条件の列と集計の列が対応しているか確認します。 1つ目のC2からC7は、条件を探す担当IDの列です 2つ目は探す条件のS01 3つ目のG2からG7は、合計する売上金額の列です 結果はS01が7000円、S02が5400円、S03が8100円になります 最後は担当者の所属です 担当マスターにはS01の佐藤さんが営業1課 S02の田中さんが営業2課 S03の鈴木さんが営業1課と登録されています 担当マスターから所属を引くなら イコールXルックアップC2マスターのA2からA4 マスターのC2からC4未登録 見つからない場合は、未登録と表示し、無言で欠損を隠しません 1つ目のC2は、探す担当IDです 2つ目は、マスターで担当IDが並ぶ範囲 3つ目は、取り出したい所属が並ぶ範囲です 行の番号の前にドル記号を付けて範囲を固定しておくと 式を下にコピーしてもマスターの範囲がずれません 画面ではマスターにない担当IDを入れて 未登録と表示されることも確かめます
第4章 演習と検算、まとめ
式の範囲を確認し全件の合計と担当別合計が一致するか検算しましょう 式が動いたことと結果が正しいことは別です 今回なら担当別の7000円、5400円、8100円を足すと20500円になり 全件の合計と一致いたします では演習です 売上表から総売上、平均、担当別売上、目標達成判定を作り 担当マスターから所属を取得してください 動画を一時停止して実際に式を入れてみてください 確認するポイントは3つです 結果が手計算と合うか、式の範囲を説明できるか そして検索で見つからないものを隠していないかです 総売上が20,500円、平均が約3,416.67円、担当別が7,000円、 5,400円、8,100円になっていれば計算は合っています 合計、平均、条件分岐、条件集計、照合 関数名より使う目的を覚えましょう
