SUM 数値を合計する
SUM()関数をキー入力で使ってみよう
②、合計を表示したいセル（B8）にセルポイントを置く。=SUM(B3:B7)とキー入力します。
③、Enterキーで式が入力され、計算結果（121）が表示される。
SUM()関数は、カッコの中で指定したセル範囲のデータを合計します。
関数の合計ですので、入力時には先頭の「=」を必ず入力します。
SUM()関数のカッコ内では、
始点セル:終点
というように半角の「:」（コロン記号）を「～から～」という意味です。
④、横合計をする。表示したいセル（D3）にセルポイントを置く。=SUM(B3:C3)とキー入力します。
⑤、Enterキーで式が入力され、計算結果（44）が表示される。
⑥、縦合計をコピーする。フィルハンドルをドラッグしてコピーする
⑦、縦合計完了
⑧、横合計をコピーする。フィルハンドルをドラッグしてコピーする
⑨、横合計完了
⑩、全体を整える
条件を指定して数値を合計する
SUMIF()関数を使ってみよう
SUMIF関数は、［範囲］から［検索条件］に一致するセルを検索し、見つかったセルと同じ行（または列）にある、［合計範囲］のセルの数値の合計を求めます。
SUMIF(範囲, 検索条件, 合計範囲)
①、土曜日の利用人数を求める
=SUMIF(C4:C10,"土",D4:D10)
②、セル（D12）に数式を入力後、「ENTER」キーで計算結果が表示、数式（編集）バーには数式が表示されます。
③、セルD12をセルE12にオートフィルでコピーすると、以下のように参照すべき曜日列（C4:C10）が一般の列（D4:D10）に移動しました。
これは、相対参照によりC列がD列に移動した結果です
絶対列参照（列だけ固定する）
絶対列参照は列だけを固定する参照方式です。「=$A1」のように、列のアルファベットの前に「$」を付けて使用します。
絶対行参照（行だけ固定する）
絶対行参照は行だけを固定して参照する方式です。「=A$1」のように、行の数字の前に「$」を付けると固定できます。
絶対参照のショートカット
絶対行参照は行だけを固定して参照する方式です。「=A$1」のように、行の数字の前に「$」を付けると固定できます。
入力したい都度「$」を入力するのは、手間なので、数式の中の参照部分を選択し「F4」を入力すると、参照方式を切り替えることができることを知っておいてください。
・F4を1回：$B$1（絶対参照）
・F4を2回：B$1（絶対行参照）
・F4を3回：$B1（絶対列参照）
・F4を4回：B1（元の状態：相対参照
④、曜日列「C4:C10」は、絶対列参照（列だけ固定する）ですので、列のアルファベットの前に「$」を付けます。
⑤、セルD12をセルE12にオートフィルでコピーしても参照すべき曜日列（$C4:$C10）はそのままです。
これは、「$」を付けることで列が絶対列参照（固定）となったためです。
⑥、セルE12をセルF12にオートフィルでコピーしても参照すべき曜日列（$C4:$C10）はそのままです。
複数の条件を指定して数値を合計する
SUMIFS()関数を使ってみよう
SUMIFS関数は、複数の条件に一致するセルを検索し、見つかったセルと同じ行（または列）にある、［合計対象範囲］のセルの数値の合計を求めます。
SUMIFS（合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...）
①、平日のの利用人数を求める
=SUMIFS(D4:D10,$C4:$C10,"<>土",$C4:$C10,"<>日")
⑤、セルD13をセルE13にオートフィルでコピーしても参照すべき曜日列（$C4:$C10）はそのままです。
これは、「$」を付けることで列が絶対列参照（固定）となったためです。
⑥、セルE13をセルF13にオートフィルでコピーしても参照すべき曜日列（$C4:$C10）はそのままです。
割り算の剰余（余り）を求める
MOD()関数を使ってみよう
MOD関数は割り算した「余り」の数値を返します。
MOD(数値, 除数)
※もし、除数に「0」が入り込む可能性があるなら、予めIF関数にMOD関数を入れ子して「#DIV/0」を非表示にする必要があります。→「=IF(C2=0,"",MOD(A2,C2))」
①、使用例1（数値、関数を入力）
=MOD(A2,C2)
A2セルに「9」、C2セルに「2」を入力します。D2セルに関数「=MOD(A2,C2)」を入力します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 割られる数 | 割る数 | 剰余 | ||
| 2 | 9 | ÷ | 2 | =MOD(A2,C2) | |
| 3 | 数値 | 除数 | |||
| 4 |
②、使用例1（計算結果）
9÷2を計算した余りが、セルD2に「1」が表示されます。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 割られる数 | 割る数 | 剰余 | ||
| 2 | 9 | ÷ | 2 | 1 | |
| 3 | 数値 | 除数 | |||
| 4 |
③、使用例2（直接数値を計算する）
| 式 | =MOD(9,-2) | 結果 |
| 説明 | 9÷-2を計算し余りを求めます。符号は除数と同じ「－」になります。 | -1 |
| 式 | =MOD(-9,2) | 結果 |
| 説明 | -9÷2を計算し余りを求めます。符号は除数と同じ「+」になります。 | 1 |
MOD関数,ROW関数
MOD,ROW関数を使って1行ごとに色付けをしてみましょう
条件付き書式でMOD,ROW,COLUMN関数を組み合わせて使用することで、 1行ごと又は、1列ごとに色を設定することができます。
①、1行ごとに色を設定してみます。
最初にデータ部分の「A2～D11」を全て選択します。次に「ホーム」タブをクリックします。
②、「条件付き書式」を選択します。
リボンが表示されたらスタイルグループの中にある「条件付き書式」を選択してクリックします。
③、表示されたメニューの中から「新しいルール」をクリックします。
④、「新しい書式ルール」が表示されます。ルールの種類の中から「数式を使用して、書式設定するセルを決定」を選択します。
⑤、ROW 関数を使って取得した行数を MOD 関数の引数に 2 を指定し余りが 0 かどうかを調べることで偶数の行数に色を設定します。 ROW 関数の引数は省略します。
=MOD(ROW(),2)=0
⑥、セルに設定する書式を定義します。「書式」をクリックします。
⑦、「セルの書式設定」が表示されます。今回はセル行ごとに色を設定するので「塗りつぶし」と「設定する色」をクリックして選択します。そのあとで「OK」をクリックします。
⑧、「一つ前のダイアログに戻りますので、確認して「OK」をクリックします。
⑨、対象のデータの中で、行数が偶数のセルに対して色が表示されました。
MOD関数,COLUMN関数
MOD,COLUMN関数を使って1列ごとに色付けをしてみましょう
条件付き書式でMOD,ROW,COLUMN関数を組み合わせて使用することで、 1行ごと又は、1列ごとに色を設定することができます。
①、1列ごとに色を設定してみます。
最初にデータ部分の「A2～D11」を全て選択します。次に「ホーム」タブをクリックします。
②、「条件付き書式」を選択します。
リボンが表示されたらスタイルグループの中にある「条件付き書式」を選択してクリックします。
③、表示されたメニューの中から「新しいルール」をクリックします。
④、「新しい書式ルール」が表示されます。ルールの種類の中から「数式を使用して、書式設定するセルを決定」を選択します。
⑤、COLUMN 関数を使って取得した行数を MOD 関数の引数に 2 を指定し余りが 0 かどうかを調べることで偶数の行数に色を設定します。COLUMN 関数の引数は省略します。
=MOD(COLUMN(),2)=0
⑥、セルに設定する書式を定義します。「書式」をクリックします。
⑦、「セルの書式設定」が表示されます。今回はセル行ごとに色を設定するので「塗りつぶし」と「設定する色」をクリックして選択します。そのあとで「OK」をクリックします。
⑧、「一つ前のダイアログに戻りますので、確認して「OK」をクリックします。
⑨、対象のデータの中で、列数が偶数のセルに対して色が表示されました。
SUMPRODUCT関数
SUMPRODUCT関数で、複数の掛け算の合計を求める
SUMPRODUCT関数は、その名の通りSUM（合計をする）とPRODUCT（掛け算の積）から構成され、複数の掛け算をした後に合計値を出します。
＝SUMPRODUCT(配列1，[配列2]，[配列3]・・・)
①、複数の掛け算の和を求める
=SUMPRODUCT(C2:C11,D2:D11)
まず最初に、セル「C13」「＝SUMPRODUCT(」と入力します。
②、「配列1」を指定しします。
数量の配列1「B2：B11」を指定、カンマ「,」を入力します。
③、「配列21」を指定しします。
単価の配列2「C2：C11」を指定、カッコ「)」を入力します。
④、「数式を確定させるためにEnterを押すと、計算が完了します。
=SUMPRODUCT(C2:C11,D2:D11)この計算の内訳は2行(5 * 120) +3行(6 * 120) ．．．+10行(10 * 120) + 11行(7 * 130) =7940となります。
COUNTIF/INDEX関数で、重複データを削除して1つだけ残す
UNIQUE関数を使えば簡単に求めることができますが、Excel2019以前のバージョンでは使用できないため、COUNTIF/INDEX関数を使います。
①、重複するデータを削除
まず最初に、セル「G2」に
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")
と入力します。
②、G11セルまでフィルコピーします。
これは、A列の最初のメーカー名だけ行番号を表示し、そうでなければ何も表示しない」という意味の式です。
③、重複を除いたメーカー名を表示する
A列からその行番号のメーカー名を抽出します。
セル「A16」に
=INDEX(A:A,SMALL(G$2:G$11,ROW(A1)))
と入力します。
④、A25セルまでフィルコピーします。
「SMALL(G$2:G$11,ROW(A1)」で、G列の数字のうち1番目に小さい値（2）、次に2番目に小さい値（4)がとすなわち昇順に抽出されました。
⑤、エラー表示「#NUM!」を空白にする
IFERROR関数でエラーを空文字列（""）に置き換えています。
セル「A16」に
=IFERROR(INDEX(A:A,SMALL(G$2:G$11,ROW(A1))),"")
と入力して、フィルコピーします。
⑥、フィルコピー結果
IFERROR関数でエラーを空文字列（""）に置き換えられました。
SUMPRODUCT関数で、メーカー別の「数量」と「売上」を求めます。
UNSUMPRODUCT関数は、SUM関数や「*」と異なり、一つの数式で複数の掛け算の和を求めることができます。
①、メーカー別の「数量」を求めます。
まず最初に、セル「B16」に
=SUMPRODUCT((A$2:A$11=A16)*E$2:E$11)
と入力します。
②、メーカー別の売上（数量×単価）を求めます。
次に、セル「C16」に
=SUMPRODUCT((A$2:A$11=A16)*E$2:E$11,F$2:F$11)
と入力します。
③、フィルコピーします。
２つのセル(B16～C16)を選択して、下方25行までフィルコピーします。
④、IF関数で0の非表示する
数量を求める書式を
=IF(SUMPRODUCT((A$2:A$11=A16)*E$2:E$11)=0,"",SUMPRODUCT((A$2:A$11=A16)*E$2:E$11))
売上を求める書式を
=IF(SUMPRODUCT((A$2:A$11=A16)*E$2:E$11,F$2:F$11)=0,"",SUMPRODUCT((A$2:A$11=A16)*E$2:E$11,F$2:F$11))
に置き替えます。
SUMPRODUCT、MOD、ROW、関数を利用して、偶数行、あるいは奇数行のを合計する
偶数行データを合計する
①、大人、男子の市内の「人数」を求めます。
まず最初に、セル「D16」に
=SUMPRODUCT((MOD(ROW(B3:B14),2)=0)*B3:B14)
と入力します。
②、「Enter」キーを押します。
偶数行の大人、男子の市内の「人数」が求められました。
奇数行データを合計する
①、大人、男子の市外の「人数」を求めます。
まず最初に、セル「D17」に
=SUMPRODUCT((MOD(ROW(B3:B14),2)=1)*B3:B14)
と入力します。
②、「Enter」キーを押します。
奇数行の大人、男子の市外の「人数」が求められました。
③、入力し数式(セルD16～D17)をI列までコピーします。
セルD16～D17をクリックすると、セルの右下に小さな四角が表示されます。この「フィルハンドル」にマウスポインタを合わせ黒い十字の表示に変わったことを確認してドラッグします。
④、フィルハンドルをI列までドラッグします。
合計欄が表示されました。
1列おきにデータを合計する
①、大人、男子と小人、男子の「人数」を合計します。
大人、男子は４列、小人、男子は６列なので偶数列を計算します。
=SUMPRODUCT((MOD(COLUMN(D4:G4),2)=0)*(D4:G4))
と入力します。
②、「Enter」キーを押します。
大人、男子と小人、男子の「人数」を合計が求められました。
③、大人、女子と小人、女子の「人数」を合計します。
大人、男子は４列、小人、男子は６列なので偶数列を計算します。
=SUMPRODUCT((MOD(COLUMN(D4:G4),2)=1)*(D4:G4))
と入力します。
④、「Enter」キーを押します。
大人、女子と小人、女子の「人数」を合計が求められました。
③、入力し数式(セルH4～I4)を15行までコピーします。
セルH4～I4をクリックすると、セルの右下に小さな四角が表示されます。この「フィルハンドル」にマウスポインタを合わせ黒い十字の表示に変わったことを確認してドラッグします。
④、フィルハンドルをI5行までドラッグします。
合計欄が表示されました。
12行／2列飛ばしで合計する
①、数式の応用で「2つ飛ばし」や「3つ飛ばし」の合計も計算できます。
各数式
①、2行飛ばし：=SUMPRODUCT((MOD(ROW(A1:A10),3)=0)*A1:A10)
②、3行飛ばし：=SUMPRODUCT((MOD(ROW(B1:B10),4)=0)*B1:B10)
③、2列飛ばし：=SUMPRODUCT((MOD(COLUMN(A5:J5),3)=0)*(A5:J5))
④、3列飛ばし：=SUMPRODUCT((MOD(COLUMN(A7:J7),4)=0)*(A7:J7))
②、飛ばしのポイントは MOD 関数の2番目の数値（除数）です。
・1つ飛ばし (2つごと): MOD(..., 2)
・2つ飛ばし (3つごと): MOD(..., 3)
・3つ飛ばし (4つごと): MOD(..., 4)
FILTER関数との違いと使い分け
FILTER関数でも同様のことができますが、Excel 2019以前にはFILTER関数が搭載されていません。
また、自分のパソコンは、FILTER関数が使える場合でも、SUMPRODUCTは「古いExcel」や「業務用の互換性が求められる場合」で広く利用されています。