エクセルに用意されてますよく使う関数の、入力方法から利用例を一部動画でご紹介します。

 

いろいろな合計関数のまとめ

関数の構成と入力方法

関数の構成


ガクガク

記号、アルファベット、数字はすべて半角で入力、間にスペースを入れないようにします。

タマタマ

引数ってのは、関数の計算対象となるセル範囲や関数の利用に必要なデータのことを言うのでありますニャー

釈和釈和

文字（ここでは”ワイン”)は、””（ダブルクォーテーション）マークで囲むんですね。

入力方法(直接入力）

１.関数式の先頭は、「＝」（イコール）からはじまります。

２.次に、関数名を入力。

３.続けて、かっこの間に引数を入力します。

４.2つ以上の引数をカンマで区切って入力します。

５.最後に「Enter」キーを押して完了です。

 

入力方法（関数の挿入）

１．数式バーの「関数の挿入」をクリックします。

２．関数の挿入ダイアログボックスが開きます。

３．関数を選択してOKをクリックします。

４．関数の引数を入力します。

５．それぞれの引数を入力しまう。

６．OKをクリックして関数の式がセル内に入力されます。

ガクガク

「関数の挿入」を使えば、それぞれの関数の引数の配置や内容が分かりやすく便利です！

商品ごとに売上の合計を求める

=SUM（数値1,数値2,・・・・）

■引数「数値1」「数値2」・・・に指定した数値、またはセルやセル範囲内の数値を合計する

  • 売上を合計するには、SUM関数を使います。
  • SUM関数の引数に「B３:D３」などと計算対象となるセル範囲を指定します。
  • セル範囲は視点と終点のセルを「：」（コロン）で区切ります。
  • Enterキーを押してSUM関数の式を入力し計算結果がもとめられます。
  • SUM関数を入力したセルをドラッグしてコピーすれば各製品の合計結果がもとめられます。

関数式をドラッグしてコピーすると、それに応じて指定した範囲も自動的にずれるため、
いちいちセル範囲を書き換えることなく計算式もコピーされて正しく計算ができます。

ガクガク

今回はヨコ並びのセルにある数値の合計を縦にドラッグしてのコピーをご紹介しましたが、タテ並びの列のセルの合計も横にドラッグして同様にして計算式のコピーができます！

 

条件にあった売上の合計を求める

=SUMIF（範囲,検索条件,合計範囲）

■「範囲」内で、「検索条件」に合致した「合計範囲」内の数値を合計する。

  • 「検索条件」には条件を入力したセルを指定する。
  • または、探す文字列などを「”」で囲んで指定する。
  • Enterキーを押してSUMIF関数の式を入力し計算結果が求められます。
  • SUMIF関数を入力したセルをドラッグしてコピーすれば各製品の合計結果がもとめられます。


タマタマ

何を検索するのか（検索条件）、それはどの列に並んでいるのか（範囲）、合計したいのはどの列か（合計範囲）の3つを整理することがポイントですね！

ガクガク

単純にドラッグしてコピーをするとセル範囲の指定がずれてしまいます。範囲は絶対値にすることも忘れずに！

 

月ごとの合計金額を求める

MONTH関数とSUMIF関数を使って合計金額を求めます。

=MONTH（シリアル値）

=SUMIF（範囲,検索条件,合計範囲）

■「範囲」内で、「検索条件」に合致した「合計範囲」内の数値を合計する。

  • まずは、MONTH関数を使って日付から「月」の値を抜き出します。
  • MONTH関数の()内の「シリアル値」とは「日付データ」のことです。
  • 次に、条件に合ったセルを合計するSUMIF関数を使って月ごとの合計金額を求めます。

 

複数条件に合ったセルの合計を求める

=SUMIFS（合計対象範囲,検索条件範囲①,条件①,検索条件範囲②,条件②）

■「範囲」内で、「検索条件」に合致した「合計範囲」内の数値を合計する。

  • １つの条件に合ったセルの合計を求めるには「SUMIF」を使いましたが、複数条件の場合は「SUMIFS」を使います。（関数にSがつくのでSUMIFとの区別がつきます）
  • 複数条件は「条件１」、「条件２」・・・、と条件を複数選ぶことができます。
  • 合計する対象範囲を選択するだけで、複数条件を満たす合計を求めることができます。
  • 条件は対象のセルを選択するか、ダブルクォーテーションで囲んだ”文字”でも続けて指定できます。

ガクガク

条件①と②はセルを選択しましたが、それぞれダブルクォーテションでかこみ、条件①を”吟醸酒”、条件②を”A店舗”としても同じ結果が得られます。

タマタマ

条件に文字を使う場合は、=SUMIFS($C$3:$C$9,$A$3:$A$9,”吟醸酒”,$B$3:$B$9,”A店舗”）という式になるというわけですね！

 

ところで通常、集計は集計表にまとめます。

この時、条件としてセルを指定する際はドラッグして式をコピーする際に、
指定セルがずれないように絶対参照と複合参照を組合せて使います。

例：条件①、列は絶対参照、行は相対参照＝$E3（カーソルを入れてF4キーを２回押します）

例：条件②、列は相対参照、行は絶対参照＝F$2（カーソルを入れてF4キーを３回おします）

ドラッグしてコピーをすると条件範囲は絶対値でズレませんが、
条件①と条件②は複合参照としますのでひとつずつ自動的に縦と横にズレてくれます。

 

ガクガク

F4キーを１回押すと絶対値、２回押すと複合参照（縦）、３回押すと複合参照（横）、４回押すと元にもどる。慣れるが勝ちであります。

絶対参照と複合参照の使用例を動画で　⇒　絶対参照と複合参照

 

ある期間内のセルの合計を求める

=SUMIFS（合計対象範囲,検索条件範囲①,（日付）条件①,検索条件範囲②,（日付）条件②）

  • 日付ごとの売上を集計するなどで「〇月〇日から〇月〇にちまで」など、
    ある特定の期間の合計を求めたい時にも複数条件に合ったセルを合計するSUMIFS関数を使います。
  • 日付の範囲は、「8月1日以降」、「8月10日以前」という2つの条件を指定します。
  • なお、日付の条件は「“>=2019/8/1”」、「“<=2019/8/10”」のように、
    ダブルクォーテーションマークで囲む必要があります。

シートに記載した条件に合致したセルの合計を求める

=DSUM(データベース,フィールド,Criteria）

  • 別表の検索条件に合致するデータを探し、指定した項目（列）を合計する。
  • データベースに表全体、フィールドに合計する列の項目名、Criteriaには検索条件の別表全体に指定する。
  • 検索条件の別表の条件を横に並べると「かつ」、縦に並べると「または」の意味になります。

 

個数と単価から一気に総売上金額を求める

=SUMPRODUCT(範囲1,範囲2,・・・）

  • おなじ大きさの複数の「範囲」を指定することで、
    対応する位置にある値をそれぞれ計算しその結果を合計する。
  • 2つの表の対応する位置にある数値を掛け合わせた結果を一気に合計することができる。

 

オートフィルターで抽出したデータだけを合計する

=SUBTOTAL(集計方法,範囲1,・・・）

エクセルの「オートフィルター」を使って条件に合致したデータの、
表示データのみを対象に合計を計算する。

SUM関数では非表示のデータまで計算してしまうので、
この場合はSUBTOTAL関数を使えばいい。

オートフィルター機能は表内にカーソルを置き「データ」タブ>>「フィルター」で、
▼マークが項目に付き抽出したい項目を選択することで抽出や絞り込みができます。


（集計方法）・・・SUBTOTAL関数では集計方法を番号で指定します。

集計方法 計算内容 同じ計算の関数
１ 平均 AVERAGE
２ 数値の個数 COUNT
３ 空白以外の個数 COUNTA
４ 最大値 MAX
５ 最小値 MIN
６ 合計 SUM

 

複数シートの同じ位置のセルを合計する

=SUM(数値1,数値2・・・）

  • 複数シートの同じ位置にあるセルの数値を合計したい時に使います。
  • SUM関数を使うと、複数シートの同じ位置にあるセルの合計も計算できます。
  • その際、引数のセル範囲の書き方がポイントになります。
  • まず、先頭のシート名と最後のシート名を「：」（コロン）でつなぎます。
  • 次に、「！」（エクスクラメーション）を書き、セル番地の入力すればＯＫ。

（作業グループでの入力方法がカンタン！）

シート（合計Ａ）のB3にＳＵＭ関数を「=SUM（）」と入力します。

シート（千代田）のB3をクリックします。

次に、「shift」キーを押しながらシート（中央）のタブをクリックします。

自動的に、=SUM（千代田：中央!,B3)と入力され串刺し計算の合計が求められます。

ガクガク

よく言う「串刺し計算」ですね。そそう、各シート内の表が同じであれば「グループ設定」での作業が超便利です。連続シートの選択は「shift」キー、離れたシートの選択は「ctrl」キーを押しながらシートをクリックすることでグループ設定ができます。


タマタマ

グループ設定の解除はシートを右クリックで「作業グループ解除」で解除できます。

条件関数・その他の関数

IF関数

IF関数を使うと、指定した条件を満たしている場合とそうでない場合の結果を表示させることができます。

いろいろな条件でのIF関数の設定を動画で！

1.条件がひとつの場合を動画で　⇒　1.条件がひとつのIF関数

2.条件がふたつの場合を動画で　⇒　2.条件がふたつのIF関数

3.AND関数との組み合わせを動画で　⇒　3.AND関数との組み合わせ

4.OR関数との組み合わせを動画で　⇒　4.OR関数との組み合わせ

 

VLOOKUP関数

Vlookup関数を使うと、コードや番号をもとに参照用の表から該当するデータを検索し表示できます。

Vlookup関数の基礎編と応用編を動画で！

1.Vlookup関数（基礎編）　⇒　1.vlookup関数（基本）

2.Vlookup関数（応用編）　⇒　2.vlookup関数（応用）

（補足説明）

  • 動画の中で絶対値設定を行う場面があります。
  • 絶対値はキーボードのF4を押すことで設定ができます。

 

COUNTIF関数

COUNTIF関数を使うと、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。

COUNTIF関数の入力を動画で！

COUNTIF関数　⇒ COUNTIF関数

 

COUNTIFS関数

COUNTIFS関数を使うと、指定された範囲に含まれるセルのうち、複数の検索条件に一致するセルの個数を返します。

COUNTIFS関数の入力を動画で！　

COUNTIFS関数　⇒ COUNTIFS関数

 

SUMIF関数

SUMIF関数を使うと、指定された検索条件に一致するセルの値を合計します。

SUMIF関数を動画で！

SUMIF関数　⇒ SUMIF関数

 

SUMIFS関数

COUNTIFS関数を使うと、指定された範囲に含まれるセルのうち、複数の検索条件に一致するセルの個数を返します。

COUNTIFS関数の入力を動画で！　

COUNTIFS関数　⇒ SUMIFS関数

絶対参照と複合参照

計算式の入力時に参照先のセルが相対的か絶対的か、
その関係によって絶対値の設定が必要となります。

絶対値を設定するにはF4キーを使います。

F4キーの設定は4つ押すことでそれぞれの設定ができ、
4つのサイクルで設定が変わることなど操作方法を知る必要があります。

F4キーを使って「絶対参照」と「複合参照」を設定し、
複雑な関数計算のコピペなど一度の式の入力設定で計算結果を得られるようにすることができます。

絶対参照と相対参照の入力設定の方法と使用例を動画で　⇒　絶対参照と複合参照