お店屋さんごっこで駄菓子屋さんやったの
お友達が色々と買ってくれたんだけど
全体で何が何個売れたのかと
合計金額がすぐわかる一覧を作りたいんだ
範囲内を検索して条件に該当したセル値の合計って感じかな・・・
「SUMIF関数」で対応できるよ
どうも!アメビンです。
今日も娘がExcelについて悩んでいる様子です。
Excel 「 SUMIF関数 」って何?
条件に一致するセルの値の合計の値を返す場合は
「SUMIF関数」を使用します。
式の書き方は「=SUMIF(範囲,検索条件,合計範囲)」となります。
早速ですが、使い方を紹介させて頂きます!
Excel 「 SUMIF関数 」使い方:作成プロセス
「SUMIF関数」は何かの結果に対して使用する関数なので
今回の駄菓子屋さんを例として、作成プロセスを説明させて頂きます。
まずは、駄菓子の商品と値段のリストを作成します。
左から「商品リスト」「単価」「販売数」「小計金額」
その下に「総販売数」「合計金額」としてみました。
「単価」「小計金額」「合計金額」には、数値のみを入力して「*円」と表示
「総販売数」も同様に、数値のみを入力して「*個」と表示させる為
このセルには「セルの書式設定」を使用します。
Excel機能について、もっと詳しく知りたい方は
↑コチラをご覧ください
次に「小計」の列に、商品ごとの金額の合計をする計算式を入力します。
商品の「単価」*「販売数」=「小計」となりますので
セル名に当てはめて式を作成すると「まいう棒」の行の場合は「=C3*D3」となります。
したの行も同様に式を入力するか、セルのコピー&ペーストでもOKです。
次に、「総販売数」と「合計金額」の計算です。
範囲の合計なので「SUM」を使用します。
手入力で「=SUM(D3:D8)」と入力するか、
「=SUM()」の()内をマウスでドラッグして範囲指定
Excel関数について、もっと詳しく知りたい方は
↑コチラをご覧ください
計算する範囲は商品の数に応じて調整して下さい。
「合計金額」も同様な作業をするか、コピー&ペーストも可能ですが
「*個」という「セルの書式設定」もコピーされる為、「*円」に修正する必要があります。
次に、お友達の誰が「何」を「いくつ」買ったかのリストを作成します。
左から「商品名」「買った数」としてみました。
自分で入力するセルは色を付けておくと判別しやすいです。
以上で「SUMIF関数」を使う準備が出来ました。
Excel 「 SUMIF関数 」使い方:式の書き方
ようやく本題の「SUMIF関数」ですが、まずは「=SUMIF(」までを入力し
「何」の部分となる「商品」が記載されたセルの範囲を指定します。
範囲の終わりには「,」(カンマ)を入力するのがルールとなっているので
ここまでをセル名に当てはめて式を作成すると「=SUMIF(G3:G20,」となります。
次は、検索条件の指定となりますので、「商品リスト」から選択します。
検索条件の終わりにも「,」(カンマ)を入力するのがルールとなっているので
ここまでをセル名に当てはめて式を作成すると「=SUMIF(G3:G20,B3,」となります。
これにより、青いセル範囲から赤のセル「まいう棒」を検索する条件が設定されました。
最後に、「何個」の部分となる「買った数」が記載されたセルの範囲を指定します。
基本的には、青いセル範囲と紫のセル範囲は同じ数にして下さい。
最後は「)」を入力するのがルールとなっているので、ここまで入力して完成です。
式は「=SUMIF(G3:G20,B3,H3:H20)」となります。
尚、この図では2種類のセル範囲が隣同士になっていますが
セルの数が合っていれば、レイアウトが離れたりするのは問題ありません。
又、下のセルも同様に作業をして下さい。
※コピー&ペーストする場合は注意点があるので後程、補足の説明をさせて頂きます。
それでは、正しく機能しているか確認してみしょう。
「まいう棒」を買ったのは、佐藤くんと田中さんが2個で
販売数の合計は4個という結果が表示されたので、正しく表示されました。
他の部分も同様に、商品ごとの合計個数の表示と、総販売数と合計金額も正しく表示されました。
以上で、完成となります。
Excel 「 SUMIF関数 」使い方:補足
コピー&ペーストする場合の注意点ですが
貼付け元から、貼り付け先のセル移動量が
関数式で指定した、セルやセル範囲に影響してしまう事です。
商品リストは良いのですが、範囲が移動してしまうのは困りますね・・・
そこで、セルのアドレスを固定する技を使います。
関数式で入力したセル名の固定させたい列(縦)と
行(横)の前に「$」を挿入します
式は「=SUMIF($G$3:$G$20,B3,$H$3:$H$20)」となります。
修正後にセルをコピー&ペーストすると
商品リストの指定セルは移動し、範囲指定したセルは移動しませんでした。
ちなみに「$」を付加する部分は行だけ、列だけというのも可能です。
役に立つ場面が今後も出てくると思うので、覚えておきましょう。
Excel 「 SUMIF関数 」使い方:応用編
今回は、販売数を合計する方法の紹介でしたが、応用するとこんな事も出来ます。
右側に「単価」「小計」「合計」を追加しました。
お友達が買った商品の単価商品リストから抽出して
それぞれの購入金額の合計を計算する、なんて事も可能です。
範囲,検索条件,合計範囲の場所を変えるだけなので
式の仕組みを理解していれば、難しくないと思います。
情報の抽出といえば「VLOOKUP関数」という関数があります。
「SUMIF関数」は情報列が2列ですが、「VLOOKUP関数」は情報列が多数ある場合に使用します。
Excel関数について、もっと詳しく知りたい方は
↑コチラをご覧ください
Excel 「 SUMIF関数 」まとめ
「SUMIF関数」を使う事で、人が結果を見て合計の計算をしていた部分を
Excelさんが代わりに判定して、合計を計算をするという仕組みを作る事が出来ます。
又、今回の例の様に「SUM」と組み合わせる事で
何が何個と、全体の合計も同時に処理できるので、作業効率も上がります。
これで数と金額の管理が楽になるね!
最後に先生に提出するから
見やすいレイアウトにアレンジしてみるね
ありがとう!
これで悩みが解決し、一つ成長しました。
最後までご観覧頂きありがとうございました!
コメント