セルの合計の出し方、複数条件指定の集計の仕方／not条件、or条件、ワイルドカード

合計の出し方/SUM,SUBTOTAL、複数列の合計

各セルの数値を合計するには、以下のSUM関数、SUBTOTAL関数を利用する方法があります。

=SUM(数値1,　[数値2・・・])

[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。

関数の説明	指定した範囲にある、数値の合計を求める関数
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=SUBTOTAL(集計方法,　参照1,　[参照2・・・])

[集計方法] = 1～11、または、101～111 の数字で指定します。それぞれの数字の意味は、下記「集計方法について・・」で解説いたします。
[参照1] = 集計するセルの範囲を指定します。
[参照2・・・] = 省略可です。他に集計するセルの範囲が有る場合に指定します。

関数の説明	指定した範囲にある数値を、集計方法の指定に従って集計する関数
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

 

 

使い方例2）指定した複数の列の合計を求める/SUM関数

同じ表（企業の年間売り上げ実績表）を使用し、今回はセルC7に「年間売上実績の合計」をSUM関数を用いて求めてみましょう。

①　SUM関数を使用して、上半期分の売上金額の合計を求める。

(1)[数値1] ： 「上半期の売上合計」が入力されているセル範囲を[E4:E5]を指定します。
(2)[数値2] ： 「上半期の消費税合計」が入力されているセル範囲[I4:I5]を指定します。
=SUM(E4:E5,I4:I5)

 

①　SUBTOTAL関数を使用して、年間売上実績金額の合計を求める。

(1)[集計方法] ： 合計の関数である、SUM関数を使用するため、「9」を指定します。
(2)[参照1] ： 1～6月のそれぞれの合計が入力されている、セル範囲[B4:D4]を指定します。
=SUBTOTAL(9,B4:D4)

 

SUBTOTAL関数の醍醐味は、同じくSUBTOTAL関数を用いた合計のセルが[参照1]（合計範囲）に含まれる場合、小計として認識してくれるという点です。
つまり、以下のような表で、それぞれセル「E4」・「I4」にはすでにSUBTOTAL関数が入力されています。
その上で、セル「J4」に「総合計」を求める場合には、すでに求めているセル「E4」・「I4」を「小計」として認識し、その部分だけを合計してくれるようになっています。

セル「J4」の数式

=SUBTOTAL(9,B4:I4)

 

一見数字に見えるデータでも、SUMで集計対象外となってしまうデータがあります。「文字列」データとして表記されている数字です。どういうことか、以下で説明していきます。

下の表は、ある学生の中間テスト結果です。それぞれ点数のセルに結果を入力して、セルC8で、SUM関数を使用して合計を出すように入力しました。

しかし、よく見ると合計値は「0」となって、正常に点数の合計が出ていません。
実はこれは、一見して数字が入っているセル範囲[C4：C7]が、エクセルによって、

数字ではなく、文字である

と、誤って認識されていることに問題があるのです。（※SUM関数は「数値」しか合計できません）
では、実際に以下の操作で問題点を確認してみましょう。

セルの書式設定の確認方法）

セルの書式設定とは・・・

セルに入力されたデータの表示形式を変更したり、セル内の文字の配置、フォントの種類やサイズ、罫線や塗りつぶしなどの設定を確認、変更できるツールです。

 

①　確認したいセルの上にマウスを重ねて右クリック、表示されたメニューから「セルの書式設定」をクリックします。

②　左側の一覧を見てみると、「文字列」が青く選択されているのが分かります。

 

つまり、現在選択されているセルは、見た目は数値が入力されていたとしても、エクセルにとっては数値ではなく「文字列」であると認識されている状態なのです。
あまり多くはありませんが、このようにエクセルでは、何かの拍子に誤ったセルの書式設定となっている場合があります。
こういった場合は慌てず、次の操作で解決するようにいたしましょう。

セルの書式設定の修正例）

①　修正したいセルの上にマウスを重ねて右クリックし、表示された表示されたメニューから「セルの書式設定」をクリックします。

 

②　左側の一覧から、「数値」を選択して[OK]ボタンをクリックしてください。

以上で修正が完了しました。
SUM関数では、あくまで「数値」だけが合計される為注意が必要ですが、文字などを表にあえて入力するなど、上手に使えば便利な機能ともいえますので、活用してみてください。

 

条件を指定した合計の出し方
／SUMIF→文字列指定、～を含む（ワイルドカード）、not条件

条件にあったセルの数値のみを合計したい場合、以下のSUMIF関数を利用します。
=SUMIF(範囲,　検索条件,　合計範囲)

[範囲] = 条件を検索する対象のセル範囲を指定します。
[検索条件] = 条件となる文字や数値を指定します。
[合計範囲] = 合計したい値が入力されているセル範囲を指定します。

関数の説明	[範囲］の中から［検索条件］に一致するセルを検索し、見つかったセルと同じ行（または列）にある、［合計範囲］の数値の合計を求めます。
EXCELバージョン	Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例1）文字列を指定して合計を求める

下の表は、ある会社の給与一覧表です。セルD13に、営業一課の給与合計を求めてみましょう。

①　SUMIF関数を使用して、「営業1課」を条件として給与合計を求める。

(1)[範囲] ： 文字列を検索するセル範囲[B4:B9]を指定します。
(2)[検索条件] ： 条件となる文字列「営業1課」を、「”」（ダブルクォーテーション）で囲んで指定します。　→“営業1課”
(3)[合計範囲] : 実際に合計するセル範囲[D4:D9]を指定します。
=SUMIF(B4:B9,”営業1課”,D4:D9)

 

使い方例2）「～を含む」（ワイルドカード）条件で文字列を指定して合計を求める

今度は、営業1課と営業2課、両方とも含めた合計をしたいのですが、こういった場合は「ワイルドカード」と呼ばれる便利な文字を活用して検索しましょう。

ワイルドカード（あいまい指定）とは・・・

抽出したい文字の前か後、もしくは前後両方に付けることで、対象の文字列を含んだ文字が検索対象となる方法で、付け加える文字としては、以下の通りになっています。

 

では実際に、「営業1課」と「営業2課」の給与合計を求めてみましょう。

①　SUMIF関数を使用して、「営業1課」及び「営業2課」を条件として給与合計を求める。

(1)[範囲] ： 文字列を検索するセル範囲[B4:B9]を指定します。
(2)[検索条件] ： 「営業1課」「営業2課」両方に共通する文字列「営業」の後ろにワイルドカード「*」を付け加え、「”」（ダブルクォーテーション）で囲んで指定します。　→“営業*”
(3)[合計範囲] : 実際に合計するセル範囲[D4:D9]を指定します。
=SUMIF(B4:B9,”営業*”,D4:D9)

ワイルドカード「*」によって、「営業～」（「営業」という文字の後ろに任意の文字列がある）の意味にしている事に注意してください。

 

使い方例3）指定条件以外を合計させる/not条件

同じ給与一覧表を利用して、今回は「営業1課」以外の合計給与を求めてみることにしましょう。

①　SUMIF関数を使用して、「営業1課」以外を条件として給与合計を求める。

(1)[範囲] ： 条件を検索するセル範囲[B4:B9]を指定します。
(2)[検索条件] ： ～以外（not）をあらわす「<>」を「営業1課」の文字の前に付け加え、「”」（ダブルクォーテーション）で囲んで指定します。　→“<>営業1課”
(3)[合計範囲] : 実際に合計するセル範囲[D4:D9]を指定します。
=SUMIF(B4:B9,”<>営業1課”,D4:D9)

検索条件に使用する不等号について・・・

今回使用した「<>」以外に検索条件には、以下の等号・不等号を用いることが出来ます。

SUMIF関数は、[範囲]の中から検索条件に見合った値を探して合計する性質上から、[範囲]と[合計範囲]はそれぞれ同じ行（または列）、同じ行（または列）でなければいけないので、注意してください。

 

SUMIFS/SUMIF関数の複数条件、or条件

条件を指定して、条件を満たすデータのみを合計したい場合はSUMIF関数を利用しましたが、条件を複数指定したい場合は、以下のSUMIFS関数を利用します。
=SUMIFS(合計対象範囲,　条件範囲1,　条件1,　[条件範囲2,　条件2・・・])

[合計対象範囲] = 合計したい値が入力されているセル範囲を指定します。
[条件範囲1] = 条件を検索する対象のセル範囲を指定します。
[条件1] =条件となる文字や数値を指定します。
[条件範囲2,条件2・・・] = 追加で「条件」やそれを検索する「条件範囲」を指定します。

関数の説明	それぞれの[条件範囲1,2・・・］の中から対応する各［条件1,2・・・］に一致するセルを検索し、見つかったセルと同じ行（または列）にある、［合計対象範囲］の数値の合計を求めます。
EXCELバージョン	Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例1）複数条件を指定して合計させる

下の表は、SUMIF関数でも使用した、ある会社の社員給与一覧表です。新たに勤続年数を追加しておりますが、ここで、セルE13に「営業1課」でかつ、「勤続年数が5年以上」である社員のみの合計を求めてみましょう。

①　SUMIFS関数を使用して、条件1=「営業1課」かつ、条件2=「勤続5年以上」の2つを条件として給与合計を求める。

(1)[合計対象範囲] ： 実際に合計するセル範囲[E4:E9]を指定します。
(2)[条件範囲1] ： 1つ目の条件を検索するセル範囲[B4:B9]を指定します。
(3)[条件1] : 「営業1課」の文字を、「”」（ダブルクォーテーション）で囲んで指定します。　→”営業1課”
(4)[条件範囲2] ： 2つ目の条件を検索するセル範囲[C4:C9]を指定します。
(5)[条件2] : 「勤続5年以上」を不等号「>=」を使用し、「”」（ダブルクォーテーション）で囲んで指定します。　→”>=5″
=SUMIFS(E4:E9,B4:B9,”営業1課”,C4:C9,”>=5″)

 

使い方例2）複数条件を指定して、どちらかに一致する数値を合計させる/or条件

SUMIFS関数では、「複数の条件に一致する場合」のみ使用することが出来ます。では、「どちらかの条件に一致する場合」に合計させる事は出来るのでしょうか。
これは少し難しいですが、「{}」（波かっこ）と、SUM関数を併用することで可能となります。

同じ社員給与一覧表を使用して、条件1=「営業1課もしくは営業2課」かつ、条件2=「勤続5年以上」の場合に給与合計を求めるよう数式を組み立ててみましょう。

①　SUMIFS関数を使用して、条件1=「営業1課または、営業2課」かつ、条件2=「勤続5年以上」の2つを条件として給与合計を求める。

(1)「 =SUM(SUMIFS( 」と数式の導入部分にSUM関数を追加して入力します。
(2)[合計対象範囲] ： 実際に合計するセル範囲[E4:E9]を指定します。
(2)[条件範囲1] ： 1つ目の条件を検索するセル範囲[B4:B9]を指定します。
(3)[条件1] : 「営業1課または営業2課」を表現するために、「”」（ダブルクォーテーション）で囲んだ2つの条件を「,」（カンマ）で区切り、かつ全ての文字を「{}」波かっこで囲んで指定します。　→{“営業1課”,”営業2課”}
(4)[条件範囲2] ： 2つ目の条件を検索するセル範囲[C4:C9]を指定します。
(5)[条件2] : 「勤続5年以上」を不等号「>=」を使用し、「”」（ダブルクォーテーション）で囲んで指定します。　→”>=5″
(6)SUMIFS関数の最後の「)」（かっこ）と、SUM関数の最後の「)」を入力します。　→「))」
=SUM(SUMIFS(E4:E9,B4:B9,{“営業1課”,”営業2課”},C4:C9,”>=5″))

 

「{}」（波かっこ）とSUM関数を使った意味について・・・

「{}」で条件を囲むことによって、「どちらかの条件」（左側から優先）という意味になります。
そこで、SUM関数の無い

=SUMIFS(E4:E9,B4:B9,{“営業1課”,”営業2課”},C4:C9,”>=5″)

とすると、表示される合計結果は、「営業1課」のみが優先された「250,000」となります。
これを解決する為に、上記の全ての数式を「SUM()」で囲み、

=SUM( SUMIFS(E4:E9,B4:B9,{“営業1課”,”営業2課”},C4:C9,”>=5″) )

と修正することで、「営業1課で勤続5年以上」の場合と、「営業2課で勤続5年以上」の場合が合計されるという仕組みになっています。
ただし、使用できない関数もありますので注意が必要です。

 

オートsumとは

 

使い方例1）基本的な使い方

①　合計を出したいセル「F4」を選択します。

 

②　エクセル上部にある「数式」タブをクリックして選択します。

③　「数式」のメニューの一覧から「∑」ボタンをクリックします。

 

④　自動的にSUM関数が入力され、合計する範囲が表示されます。

 

⑤　式に問題が無ければキーボードの「ENTER」を押して、決定してください。

オートSUM結果）

 

 

オートSUMは便利な機能ですが、表の状態によって時折合計範囲が違っている場合が発生します。
下記の例を使用して、オートSUMの合計範囲の修正方法も一緒に覚えておきましょう。

オートSUMで、合計範囲が違っている場合の例）

下の表は、ある学校の生徒別中間テストの成績一覧表です。セルH4・H5にてそれぞれオートSUMで合計が正常に入力されたのですが、セルH6で同じようにオートSUM機能を使用すると、合計の範囲が以下のようになりました。　→　=SUM(H4:H5)

これは、オートSUM機能が横の合計よりも、「縦の合計」を優先するために起こる間違いで、上にデータが並んでいるのをエクセルが認識した事によるものです。
こういった時にも「ENTER」を押して決定しなければ、次の方法で修正が出来ます。

オートSUMの合計範囲修正方法）

①　セルが点滅している状態で、正しい合計範囲[C6:G6]を範囲選択してください。

②　キーボードの「ENTER」を押して、決定してください。

これで、修正が完了します。慌てずに対処するようにしましょう。

 

使い方例2）一括で表内の合計を求める

下のような表の場合は、オートSUMを使用すれば一括で合計を求めることが出来ますので、その方法を覚えておきましょう。

①　合計範囲と合計を入力するセルを含む全てのセル範囲を選択します。

②　「∑」（オートSUM）ボタンをクリックして決定します。

一括オートSUM結果）

オートSUMは入力されているセルと、合計欄が隣り合わせの場合、範囲選択してから利用すると複数の合計が一度に出来るため、非常に手間が省ける機能です。