オートSUM
001合計を簡単に求める
使用関数：SUM
表の集計の中で、最も頻繁に使用する計算は「合計」でしょう。合計を求めるには、「オートS∪ M」機能を使用するのが便利です。ボタンをクリックするだけで、合計計算用のS∪ M関数を入力できます。合計対象のセルも自動認識されます。
■「オートSuM」機能で売上数の合計を求める
田合計を表示するセルB6を選択して、[数式]タブの[関数ライブラリ]グループにある[オートS∪ M]ボタンをクリックする。もしくは、[ホーム]タブの[編集]グループにある[合計]ボタンをクリックしてもよい。
ロセルにS∪ M関数が入力され、合計対象のセル範囲が点線で回まれる。自動認識された範囲が正しいことを確認したら、もう一度[オートS∪ M]ボタンをクリックするか、[Enter]キーを押す。
OS∪M関数の数式が確定され、セルに合計値が表示された。
memo
=SUMI数値1[,数値2]・……〕
数値…数値、またはセル範囲を指定。空自セルや文字列は無視される
指定した「数値」の合計を返します。「数値」は255個まで指定できます。
002誤認識された合計対象のセル範囲を修正する
使用関数：SUM
[オートSUM]ボタンをクリックすると合計対象のセル範囲が自動認識されますが、目的どおりのセル範囲が正しく認識されるとは限りません。きちんと確認し、間違っている場合は正しいセル範囲をドラッグして指定し直します。
■「オ… 卜SUM」実行時に合計対象のセルを手動で指定する
口[オートS∪ M]ボタンをクリックしたときに、合計したいセル範囲が正しく認識されないことがある。
0正しいセル範囲B3:B5をドラツグして指定し直す。ドラツグした範囲が点線で囲まれたら、[オートS∪ M]ボタンをクリックするか、[Enter]キーを押す。
OS∪M関数の数式が確定され、セルに合計値が表示された。
=SUMI数値1[,数値2]・……〕→
>自動認識される合計対象のセルは、上または左に隣接する数値が入力されているセル範
囲です。上にも左にも数値が入力されている場合は、上のセル範囲が優先されます。
003離れたセルを合計する
使用関数：SUM
離れたセル範囲にあるデータも、オートSUMの機能で簡単に合計できます。それには、[オートSUM]ボタンをクリックしたあと、[CtrL]キーを使用して複数のセルを合計対象に加えます。ここでは東日本の売上(セル範囲B3:B5)と西日本の売上(セル範囲E3:E5)をまとめて合計します。
■東日本の売上と西日本の売上をまとめて合計する
田合計を表示するセルE7を選択して、[数式]タブの[オートS∪ M]ボタンをクリックする。
a自動認識されたセル範囲を無視し、日的のセル範囲B3:B5をドラツグして選択する。続いて、[Ct‖ ]キーを押しながらセル範囲E3:E5をドラツグして選択し、[オートS∪ M]ボタンをクリックする。
口S∪ M関数の数式が確定され、セルに合計値が表示された。
004表の縦横の合計値を一発で求める
SUM
右端と下端に合計欄があるクロス集計表の場合、表全体を選択してオートS∪ Mを実行すると、縦計と横計をまとめて一気に計算できます。大変便利な機能なので、ぜひ試してみてください。
■表の縦横の合計値を―発で求める
口数値データと合計欄のセル範囲B3:E6をまとめて選択し、[数式]タプの[オートS∪M]ボタンをクリツクする。0合計欄のセルにS∪ M関数が入力され、縦横の合計値がそれぞれ正しく表示された。
memo
>オートS∪ Mを実行するためのボタンは、2つ用意されています。1つは手順1で使用した[数式]タプの[関数ライブラリ]グループにあるボタンで、もう1つは[ホーム]タプの[編集]グループにある「Σ」記号のボタンです。どちらも正式名称は「[合計]ボタン」です。
[ホーム]タブが表示されている場合は、[数式]タブに切り替える必要はありません。[ホーム]タプの[合計]ボタンを使用しましょう。
005複数のシートの表を串刺じ演算で集計する
sum
各支店のデータが、それぞれ異なるシートに入力されているようなケースで、全支店の集計を行いたいことがあります。データが各シートの同じ位置に入力されているのであれば、オートSUMで簡単に合計できます。このような計算は、シートを東ねて各セルに串を刺すようにセルごとの集計を行うので、「串刺し演算」と呼ばれます。
■3つのシ… 卜にある売上を集計する
口[渋谷店][有楽町店][上野店]の3つのシートには、それぞれ店舗ごとの来客数が入力されている。これらのデータを[全店合計]シートに集計したい。
0[全店合計]シートにも、他の3つのシートと同じ形の表を用意しておく。合計値を表示するセル範囲B3:D6を選択して、[数式]タブの[オートS∪M]ボタンをクリツクする。
ロアクティブセルであるセルB3にS∪M関数が入力され、引数の入力待ちの状態になる。集計対象のシートのうち、いちばん左にある[渋谷店]のシート見出しをクリツクする。
● [渋谷店]シートに切り替わったら、セルB3をクリックする。続いて[Shlft]キーを押しながら、いちばん右にある[上野店]のシート見出しをクリックする。
口集計対象のシートがすべて選択された状態になる。最後に、もう一度[オートS∪M]ボタンをクリックする。
● [全店合計]シートに、各シートの合計値が
表示された。入力されたSUM関数は、「渋谷店:上野店!B3」のように「最初のシート名:最後のシート名!セル番号」の形式で引数が指定される。
難b齢む
>手順5では[オートS∪ M]ボタンをクリックして数式を確定しましたが、代わりに[Ctrl]キーを押しながら[Enter]キーを押しても、選択したセル範囲ヘー気にSUM関数を入力できます
006表に追加したデータも自動的に合計する
経費帳のように、最下行に日々のデータを追加していくタイプの表では、S∪ M関数の引数にどこまでのセルを指定すべきか迷うところです。そのようなときは、引数に列全体を指定しましょう。そうすれば、あとから追加したデータも自動的に合計値に加わります。
田引数に列全体を指定して自動的に合計する
口合計欄にS∪ M関数を入力し、引数に「金額」の列全体を指定する。
●新しいデータを追加すると、追加したデータが自動的に合計値に加えられる。
memo
>サンプルのC列には列見出しとして「金額」という文字が入力されていますが、SUM関数では文字列データを無視して合計するので問題ありません。ただし、同じ列に日付が入力されていると、シリアル値として合計に加えられてしまいます。列全体をS∪ M関数の引数に指定する場合は、計算対象以外の数値や日付を同じ列に入力しないようにしてください。
0074行ごとに合計を表示する
sum
四半期ごとにデータを合計したいときなど、「合計」列の決まった行数ごとにSUM関数を入力したいことがあります。ここでは「合計」列の4行ごとに合計を表示します。最初のSUM関数を入力したあと、空白セルと合計欄の合わせて4つのセルを選択して、オートフィルすることがポイントです。
鵬四半期ごとの売上を1年分ずつ合計する
口最初の合計欄のセルD6にS∪M関数を入力して、合計を求めておく。先頭の4行分のセル範囲D3:D6を選択して、フイルハンドルを表の最下行までドラッグする。
24行ごとに合計値を計算できた。S∪ M関数を入力したセルの左上隅には、エラーインジケータが表示される。そのままにしておいて差し支えないが、気になるようなら口E正口を参考に非表示にすればよい。
008データの平均/個数/最大値/最小値を簡単に求める
average
[オートS∪ M]ボタンの右横にある[▼]ボタンを使用すると、データの平均、個数、最大値、最小値を簡単に求めることができます。個数は数値データだけがカウントの対象です。ここでは、例として「得点」の平均値を求めます。
田[オートSUM]ボタンを使用して平均を求める口平均値を求めるセルC8を選択する。[数式]タブの[オートS∪ M]ボタンの右横にある[▼ ]ボタンをクリックして、[平均]を選択する。
口「得点」欄のセル範囲C3:C7が正しく認識されたことを確認する。次に[オートS∪ M]ボタンをクリックするか、[Enter]キーを押して確定すると、「得点」の平均が表示される。
memo
>[オートSUM]ボタンから入力できる集計の種類と関数の対応は右表のとおりです。
合計
009条件に合致するデータを合計する
sumif
「会員/非会員の購入データのうち、会員のデータだけを合計したい」といったとき
に役に立つのがSUMIF関数です。この関数を使えば、指定した条件に合致するデ
ータだけを合計できます。ここでは、「会員」という条件で「ご購入額」を合計します。
田「会員」の購入額を合計する
入カセル E3 入力式 =SUMIF(B3:B9「 ‘会員“,C3:C9〕
check
■SUMiF〔条件範囲,条件[,合計範囲1) .
条件籠田…条件判定の対象となるデータが入力されているセル範囲を指定
条件…合計対象のデータを検察するための条件を指定
合計範囲…合計対象の数値データが入力されているセル範囲を指定。指定を省略すると、「条件範囲」の
データが合計対象となる
指定した「条件」に合致するデータを「条件範囲」から探し、条件に合致した行の「合計範囲」のデータを合
計します。
memo
>セルE3に入力したS∪ MlF関数の引数の指定内容は次のとおりです。
=SUM:FIB3:B9,“ 会員“,C3:C9〕
条件範囲 条件 合計範囲
「区分」欄「会員」「ご購入額」欄
)S∪ MIF関数の引数「条件」に文字列や日付を指定する場合は、「・会員・」「・2016/6/17・」
のように半角ダブルクォーテーション「‖
」で囲みます。数値の場合は、そのまま「1234」
のように指定します。
010「〇以上」のデータを合計する
sumif
SUMIF関数の引数「条件」に「比較演算子」を組み合わせると、「〇以上」や「○未
満」など、範囲を条件に合計を求めることができます。ここでは年齢が「40歳以上」
という条件で、「ご購入額」を合計します。「以上」を表す比較演算子は「>=」で、
条件は「・>=40″」となります。
■「40歳以上」の顧客の購入額を合計する
入カセル E4 入力式 =SUM:FIB3:B91・ 〕>=40“ ,C3:C9〕
check
=SUMIFI条件範囲,条件[,合計範囲]〕→ ■■エロ
memo
>比較演算子には、次表の種類があります。比較演算子を含む条件は、半角ダブルクォーテーション「‖」で囲む必要があります。なお、「40に等しい」という条件は、「・=40・」としても、単に「40」としてもどちらでも構いません。
> より大きい ・>40″ 40より大きい
>= 以上・>=40・40以上
< より小さしヽ‘く40・40より小さしヽ
<= 以下
‘.<=40’ 40以下
等しい”=40” 40に等しい
<> 等しくない ‘`<>40” 40に等しくない
011セルに入力した数値以上のデータを合計する
sumif
■巨ロコでSUMIF関数の引数「条件」に「“>=40″」を指定して、「40歳以上」とい
う条件で合計を求めましたが、条件を「30歳以上」に変更したい場合、数式を修正
しなければならないので面倒です。そこでここでは、「セルに入力した年齢以上」と
いう条件を指定することにします。そうすれば、セルの数値を変更するだけで、即座
にその条件に応じた合計が求められます。
ロセルE3の年齢以上の顧客の購入額を合計する
check
=SuMiF(条件範囲,条件[=合計範囲]l→ ■■Eロ
memo
>セルと比較演算子を組み合わせた条件を指定するときは、比較演算子をダブルクォーテーション「‖」で囲み、条件のデータと「&」演算子で文字列結合します。
‘`>'(&E3 セルE3のデータより大きい
”>=・&E3 セルE3のデータ以上
・<・&E3 セルE3のデータより小さい
く=・&E3 セルE3のデータ以下
・
=・&E3 セルE3のデータに等しい
・<>”&E3 セルE3のデータに等しくない
012「〇〇でない」データを合計する
sumif
S∪ MIF関数を使用して、「○○でない」という条件でデータを合計するには、比較演算子「<>」を使用します。例えば「東京都でない」という条件なら「¨<>東京都“」と指定し、「セルE3のデータではない」という条件なら「”<>”&E3」と指定します。
ここでは後者の条件を使用したサンプルを紹介します。
翻セルE3とは異なる顧客の購入額を合計する
check
=SUM:Fl条件範囲,条件[,合計範囲]〕→ ■■Eロ
memo
レ条件に応じて集計を行うCO∪ NTIF関数とA∨ ERAGEIF関数は[統計関数]に分類されていますが、S∪ MIF関数の分類は[数学/三角]です。[関数の挿入]ダイアログや[関数ライブラリ]から関数を入力する際は、注意してください。
013「〇〇を含む」データを合計する
sumif
SUMIF関数で、「○○を含む」「○○で始まる」「○○で終わる」などのあいまいな条件を指定するには、「ワイルドカード文字」を使用します。ここではワイルドカード文字「*」を使用して、「“*“ &E3&“ *¨」という条件を指定し、セルE3に入力した文字列を含むデータの「ご購入額」を合計します。
■ セルE3の文字列を含むデータの購入額を合計する
check
=SUM:F:条件範囲=条件[,合計範囲]〕→ |■匝コ|
memo
トワイルドカード文字には以下の種類があります。文字列の前に付けるか、後ろに付けるかによって、「〇〇を含む」「○○で始まる」「○○で終わる」という条件を使い分けます。
なお、「“」や「’」を通常の文字として検索したいときは、ワイルドカード文字の前に「・~・」のように半角のチルダ「～」を付けます。
一致例
0文字以上の任意の文字列
”*山ネ” 山、登山、富士山、山頂、山岳地帯、登山道(山を含む文字列)
・*山山、登山、富士山(山で終わる文字列)
山P’ 山、山頂、山岳地帯(山で始まる文字列)
任意のl文字
りつ山“ 富士山(山で終わる3文字)
山つ・山頂(山で始まる2文字)
014AND条件でデータを合計する①
SuMPRODUCT
「性別が女、かつ、区分が会員」というように、すべてを満たす場合の条件設定をAND条件と呼びます。AND条件でデータを合計するにはSUMIFS関数(回□ロロ参照)を使用するのが一般的ですが、SUMPRODUCT関数を使用する方法もあります。
考え方は複雑ですが、使い方を覚えておくと、いろいろな場面で応用できます。ここでは、セルF4に入力した性別と、セルG4に入力した区分を条件に「ご購入額」を合計します。
セルF4の性別、セルG4の区分を条件に購入額を合計する
check
=SUMPRODUCTl配列1[,配列2]・“・¨卜)■■フロ
¬nl●ぃ●‐
>SUMPROD∪ CT関数を使用してAND条件でデータを合計する方法は、考え方が難しいのが難点ですが、検索条件を指定するためのセルがなくても計算できることはメリットです。この他に、SUMIFS関数を使用(■■国日参照)、DSUM関数を使用(■■□日参照)と、AND条件でデータを合計する方法は複数あります。いろいろ試して、使いやすい方法を選んでください。
卜S∪ MPROD∪ CT関数は配列の要素同士の積を合計するための関数ですが、次の書式にしたがって数式を作成すると、条件1と条件2を同時に満たすデータの合計を求めるために使用できます。
=SUMPRODUCTIIセル範囲1=条件11*1セル範囲2=条件2L合計範囲〕
この計算は、論理値の「TR∪ E」と「FALSE」がそれぞれ数値の「1」と「0」として扱えることを利用しています。計算の過程を、順を追って説明しましょう。
①「性別」が「女」であれば「1」、そうでなければ「0」の配列を作成
②「区分」が「会員」であれば「1」、そうでなければ「0」の配列を作成
③ ①と②の配列の要素同士を掛け合わせる
④「性別」が「女」かつ「区分」が「会員」であれば「1」、そうでなければ「0」の配列ができる
⑤ ④の配列と「ご購入額」の数値を掛け合わせる
⑥「性別」が「女」かつ「区分」が「会員」であれば購入額、そうでなければ「0」の配列ができる
⑦ ⑥の配列の要素を合計する
=SUMPRODUCTI B3:B9=F41 * iC3:C9=G4〕
i D3:D91
015AND条件でデータを合計する②
sumifs
S∪ MIFS関数を使用すると、簡単にAND条件でデータを合計できます。ここでは、
セルF4に入力した性別と、セルG4に入力した区分を条件に「ご購入額」を合計し
ます。
ロセルF4の性別、セル04の区分を条件に購入額を合計する
臓瑕吻鯰
=SUM:FSI合計範囲,条件範囲1,条件1[,条件範囲2,条件2]・…・1
合計範囲…合計対彙の数値データが入力されているセル範囲を指定
条件範囲…条件判定の対象となるデータが入力されているセル範囲を指定
条件・¨合計対象のデータを検索するための条件を指定
指定した「条件」に合致するデータを「条件範囲」から探し、条件に合致した行の「合計範囲」のデータを合
計します。「条件範囲」と「条件」は必ずペアで指定します。最大127組のベアを指定できます。
、|,memo‐
>セルF6に入力したSUMIFS関数の引数の指定内容は次のとおりです。
=SUiV‖ FS(D3:D9,B3:B9!F4,C3:C9,G41
合計範囲
「ご購入額」欄
条件範囲1条件1条件範囲2 条件2
「性別」欄 「女」「区分」欄 「会員」~¨~¨~…
1===三
===コ
~‐~~~
AND会イ牛
>SUMIF関数とS∪ MIFS関数では、引数の「合計範囲」と「条件範囲」「条件」の順序が異な
るので注意してください。
016「〇以上△未満」のデータを合計する
sumifs
「○以上△未満」という条件は、一見すると1つの条件のようですが、実は「〇以上、かつ、△未満」というAND条件です。このような条件で合計するには、SU MIFS関数を使用します。引数の「条件範囲1」と「条件範囲2」には同じセル範囲を指定します。
囲セルE4以上、セルF4未満を条件に購入額を合計する
入カセル E6 入力式 =SUM:FS(C3:C9,B3:B9,”>=”&E4,B3:B9,“ <“&F4)
0170R条件でデータを合計する①
sumif
「都道府県が埼玉県、または、都道府県が千葉県」というように、いずれかを満たす場合の条件設定を「OR条件」と呼びます。Excetには、OR条件による合計を求めるための専用の関数がありません。S∪ MIF関数で埼玉県の合計と千葉県の合計をそれぞれ求め、足し算することで「埼玉県または千葉県」の合計がわかります。
■ セルE4、またはセルF4を条件に購入額を合計する
入カセル E6 入力式 =SUM!FIB3:B9,E4,C3:C9)+St’MiFIB3:B9,F4,C3:C9〕
check
=SuMIFl条件範囲,条件[,合計範囲]〕→ |■匝コ
memo
>セルE6に入力した数式の内容は次のとおりです。
=SUヽ11F(B3:i39,E4,C3:C9〕+St’MIFIB3:B9,F4iC3:C91
「埼玉県」の合計 プラス 「千葉県」の合計
>OR条件による合計の方法は、この他にSUM関数とS∪ MIF関数を組み合わせる方法
(■■Eコ参照)、DS∪ M関数を使用する方法(■■Eヨ参照)があります。
0180R条件でデータを合計する②
SUM//SUMiF
ここでは、SUM関数とSUMIF関数を組み合わせて、OR条件による合計を求める方法を紹介します。■■コロで紹介した方法は条件の数が増えると数式が長くなりがちですが、こちらの方法なら1つの数式でスマートに複数の条件を指定できます。
□「埼玉県または千葉県」の顧客の購入額を合計する
入カセル E6 入力式 =SUMISUMIFIB3:B9,{‘1埼 玉県 ‘∵ `千葉 県 tC3:C9‖
memo
>セルに入力したデータを条件としたい場合は、数式を配列数式として入力します。例え
ば、セルE3に「埼玉県」、セルE4に「千葉県」と入力してある場合、次の数式を配列数式
(|■匝ヨ参照)として入力すると、「埼玉県または千葉県」の顧客の購入額の合計を求め
られます。
{=SUMISUMIFIB3:B9,E3:E4,C3:C9〕〕
0191行おきの数値を合計する①
MOD//ROW//SUMIF
ここでは作業列を使用する方法で、シートの偶数行に入力された数値の合計を求めます。作業列に、合計対象なら「0」、そうでなければ「1」を表示しておき、「0」を条件にS∪ MIF関数で合計します。条件となる「0」と「1」は、行番号を2で割った余りで求めます。使用するのは、余りを求めるMOD関数と、行番号を求めるROW関数です。
偶数行に入力された「今期目標」を合計する
入力式 =MODIROWi),2〕
入力式 =SUM!FIID3:D810,C3:C81
辟□□□
=MODl数値,除数〕→|■ロヨ|
=ROWi[参照11→ |■匝ロ
=SUMiF〔条件範囲,条件[,合計範囲]〕→■EEロ
‐l|“.豊i ‐ m19.
>シートの奇数行に入力された数値を合計したい場合は、S∪ MIF関数の引数「条件」に「1」を指定します。
=SUM:FlD3:D8,1,C3:C8)
>数式が作成できたら、■■□]を参考に作業列を非表示にしておきましょう。
0201行おきの数値を合計する②
SUM/fF/MOD/ROW
:SUM/1F/MOD/ROW
ここでは配列数式を使用して、偶数行に入力された数値の合計を求めます。
■□回口で紹介した作業列を使用する場合に比べて難易度は上がりますが、1つの数
式でスマートに合計計算できるのはメリットです。
圏偶数行に入力された「売上」を合計する
入カセル C9 入力式 {=SUMI:FlMODIROWIC3:C81,2)=0,C3:C8,0〕 )}
圏圏副雷
=SUMI数値1[,数値2]・……〕→ |■匝■|
=:F〔論理式,真の場合,偽の場合〕→ ■EEロ
=MODl数値l除数〕→ ■■翼ロ
=ROWI[参照]〕→■■塑ロ
‐輛
)S∪`mM●関数の引数に指定した「IF(MOD(ROW(C3iC8)′ 2)=0,C3iC8,0)」 の結果は、奇数行
を0に置き換えた「目標」欄の数値からなる配列「[Oi5000000,Oi4000000,0,300000
0〕」です。数式バーでこの部分をドラッグし、[F9]キーを押すと、配列になっていること
を確認できます。確認したら、[Esc]キーを押してください。
021商品ごとの集計表を作成する
sumif
日々の売上を記録した表から、商品ごとの売上の集計表を作成してみましょう。集計表に商品名を入力しておき、それを条件にS∪ MIF関数で合計値を求めます。コピーしたときに条件範囲や合計範囲のセル番号がずれないように、絶対参照を使用することがポイントです。
疇商品ごとの集計表を作成する
入カセル F3 入力式 =SUMIF($BS3:$B$10,E3,SC$3:$C$10〕
〓SUMiF〔条件範囲,条件[,合計範囲]〕→ ■E亜ロ
>“Se∪lMmI9F関数の1番 目の引数「条件範囲」と3番目の引数「合計範囲」はどの商品の場合も
同じなので、絶対参照で指定します。2番目の引数「条件」は商品ごとに異なるので、相対
参照で指定します。
>|口EE3や回■Eコのテクニックを使用すると、「売上記録」の表から商品名を1つずつ自
動で抜き出せます。
022月ごとの集計表を作成する
month sumif
日々の売上を記録した表から、月ごとの売上の集計表を作成してみましょう。「月ごと」を実現するために、MONTH関数で日付から月を取り出し、作業列に表示します。
これを条件範囲として、S∪ MIF関数で合計値を求めます。コピーしたときに条件範囲や合計範囲のセル番号がずれないように、絶対参照を使用します。
回月ごとの集計表を作成する
入カセル C3 入力式 =MONTH(A3)
入 カセル F3 入力式 =SUM:FISCS3:SCS10,E3,SB$3:$B$10〕
団口回鷹
=MONTHlシリアル値l→ |■匝コ
=SUMIFl条件範囲,条件[,合計範囲]〕→ ■EEロ
‐m‐●ml●‐
)SUMIF関数の1番目の引数「条件範囲」と3番目の引数「合計範囲」はどの月の場合も同じなので、絶対参照で指定します。2番目の引数「条件」は月ごとに異なるので、相対参照で指定します。
>数式が作成できたら、■■□]を参考に作業列を非表示にしておきましょう。
023月ごとの最終行に合計値を表示する
month if sumif
売上表の中に「月合計」の列を設けて、月ごとの最終行に合計値を表示してみましょう。まず、MONTH関数で日付から月を取り出し、作業列に表示します。現在行の月が下の行の月と異なる場合だけ、月ごとの合計値をSUMIF関数で求めます。
幽月ごとの最終行に合計値を表示する
入カセル D3 入力式 =MONTH(A3)
入カセル C3 =lF(D3=D4,“
°‘,SUMIFl$D$3:$D$101D3,$B$3:SBS10〕
memo
>セルC3に入力した数式の内容は次のとおりです。
=!FID3=D4, =”‘, SUM!Fl$D$3:$DS10,D3,$B$3:$B$1011
024クロス集計表を作成する
sumproduct
日々の売上を記録した表を元に、商品ごと、担当者ごとに売上を集計するクロス集計表を作成してみましょう。それにはS∪ MPRODUCT関数を使用して、クロス集計表の行見出しと列見出しの文字を条件に合計を計算します。数式をコピーしたときに、行見出しと列見出しを正しく参照できるように、複合参照を使用することがポイントです。
口商品ごと、担当者ごとに売上をクロス集計する
入カセルG3
入力式 =SUMPRODUCTI($B$3:$B$11=SF31*I$CS3:$CSll=G$2〕 ,$D$3:$DSll〕
memo
>S∪ MPRODUCT関数は配列の要素同士の積を合計するための関数ですが、次の書式にしたがって数式を作成すると、条件1と条件2を同時に満たすデータの合計を求めるために使用できます。詳しくは、■Eコロを参照してください。
=SUMPRODUCT11セル範囲1=条件1〕*〔セル範囲2=条件2),合計範囲〕
025小計と総計を求める
subtotal
小計行と総計行のある表を作成するときは、S∪ BTOTAL関数が便利です。
SU BTOTAL関数は、引数に指定したセル範囲の中にS∪ BTOTAL関数で求めた小計が含まれていると、自動的に小計を除外した数値だけを集計します。飛び飛びに入力された数値のセル範囲を指定しなくても、小計を含むセル範囲を一括で指定すれば済むので効率的です。
儘小計と総計を求める
入カセル C6 入力式 =SUBTOTAL19,C3:C51
入カセル C10 入力式 =SUBTOTAL19,C7:C9〕
入カセル C11 入力式 =SUBTOTAL19,C3:C10〕
check
=SuBTOTALl集計方法,範囲1[,範囲2]・……〕
集計方法…集針のために使用する関数を次表の数値で指定。非表示の値も含める場合は1～ 11、非表示
の値を無視する場合は101～ 111を指定
範囲…集計対象のセル範囲を指定
「集計方法」で指定した関数を使用して、「範囲」のデータを集計します。「範囲」は254個まで指定できます。
memo
>列番号を右クリックして[非表示]を選択すると、行が非表示になりますが、そのような非表示の行のデータを集計対象とするかどうかを指定できます。引数「集計方法」に1～11を指定した場合は集計対象となり、101～ 111を指定した場合は集計対象から除外します。詳しくは|■■亜口を参照してください。
>オートフィルターで抽出を実行した場合、S∪ BTOTAL関数では抽出されているデータのみが集計の対象になります。抽出されなかった非表示のデータは、引数「集計方法」の指定にかかわらず、集計対象から除外されます。詳しくは■■□]を参照してください。
>SU BTOTAL関数は、引数「範囲」に指定したセル範囲内にS∪ BTOTAL関数で求めた集計値が挿入されている場合、集計の重複を防ぐために、それらの集計値を無視して集計を行います。
>Exce1 201 6/2013/2010では、AGGREGATE関数を使用すると、SU BTOTAL関数よりも集計対象の条件を詳しく指定できます。詳しくは日■ロコを参照してください。
026フィルターで抽出されたデータのみを合計する
SUBTOTAL
Excelには「オートフィルター」と呼ばれる便利な抽出機能があり、表の列見出しの[▼]ボタンをクリックして、表示されるメニューから条件を選ぶだけで抽出を実行できます。S∪ BTOTAL関数を使用すると、オートフィルターで抽出されたデータだけを対象に集計を行えます。
田抽出されたデータのみを対象に合計する
入カセル C13 入力式 =SUBTOTAL19,C4:Clll
check
=SUBTOTAL〔集計方法,範囲1[,範囲2]・¨¨1→ ■■塑日
memo
>表の列見出しにオートフィルターの[▼ ]ボタンを表示するには、[データ]タプの[並ベ替えとフィルター]グループにある[フィルター]ボタンをクリックします。
027非表示の行を除外して合計する
SUBTOTIAL
SU BTOTAL関数の引数「集計方法」に101～ 111を指定すると、非表示の行を除外して集計を行えます。ここでは、非表示の行を除外して、売上の合計を求めます。
目非表示の行を除外して合計する
入 カセル C13 入 力式 =SUBTOTALl109,C4:Cll)
check
=SUBTOTALi集計方法,範囲1[,範囲2]・¨¨1→ ■■厖ロ
memo
>行を非表示にするには、行番号を右クリックして[非表示]を選択します。また、行の非表示を解除するには、非表示の行を含むように隣接する行番号をドラッグして選択し、右クリックして[再表示]を選択します。
028エラー値を除外して小計と総計を求める
ACGRECATE
ACGRECATE関数を使用すると、さまざまな条件を指定して集計を行えます。ここではエラー値を無視して、小計と総計を求めます。いくつかの過程を経て計算された結果を集計する場合、大元のデータが一部未入力のために集計対象のデータに工ラーが含まれてしまうことがあります。SUBTOTAL関数を使用した場合、大元のデータが入力されるまで集計結果はエラーのままですが、AGGRECATE関数ならエラー値を無視して、常に暫定的な集計結果を表示できます。なお、Excet 2007では、AGGRECATE関数を使用できません。
ロエラーを無視して小計と総計を求める
入カセル D6 入力式 =ACGRECATE19,2,D3:D5〕
入カセル 入力式 =ACGRECATE19,2,D7:D9)
入カセル Dll 入力式 =AGGRECATE19,2,D3:D10)
memo
=ACGRECATEI集計方法,除外条f牛=範囲1[,範囲2]・・¨¨〕
[Exce1 2016′ 2013′ 20101
集計方法…集計のために使用する関数を次表の数値で指定
除外条件…集計対象のデータのうち無視するデータの条件を次表の数値で指定
範囲…集計対象のセル範囲を指定
「集計方法」で指定した関数を使用して、「除外条件」のデータを除いた「範囲」のデータを集計します。「範囲」
を253個まで指定できます。
memo
)AGGREGATE関数は、従来からあるSUBTOTAL関数を発展させたものです。SUBTOTAL関数と比較すると、使用できる関数の種類は、11種類から19種類へと大幅に増えています。また、S∪ BTOTAL関数では非表示のデータを無視するかどうかしか選べませんでしたが、AGGREGATE関数ではニラー値や小計を無視するかどうかも選べるようになっています。
>ここでは、エラー値と小計データを無視して総計を求めたかったので、セルDllのAGGREGATE関数の引数「除外条件」に「2」を使用しました。セルD6とD10については、引数のセル範囲の中に小計が含まれていないので、引数「除外条件」に、「2」の代わりに「6」を指定しても同じ結果になります。
カウント
029数値データのセルをカウントする
count
Excetには、データをカウントするための関数が豊富に用意されており、カウント対象のデータの種類に応じて使い分けます。カウントしたいデータが数値データの場合は、COUNT関数でカウントします。ここでは、セル範囲C3:C12に入力されたデータのうち、数値データのみをカウントします。
■ 数値データをカウントする
入カセル E3 入力式 =COUNTIC3:C12〕
check
=couNT(● 1[.:|コ¨¨¨
1
●…Ⅲ■の口蠍壷●べ0■やセル●□■■定
指定した「●Uに含まれる数管0菫t返します。「●」は255曇まで描定できます。
memo
>数式の戻り値として数値が表示されているセルは、カウントの対象です。また、日付や時刻も「シリアル値」と呼ばれる数値データの一種なので、カウントの対象になります。
>空白セルや、セルに文字列として入力された数値、論理値はカウントされません。なお、引数に直接指定した文字列の数値や論理値はカウントされます。例えば「=COUNT(・1’′TR∪ E)」の結果は2です。
030データが入力されたセルをカウントする
counta
何らかのデータが入力されているセルをカウントするには、COUNTA関数を使用します。ここでは、セル範囲C3:C9の「申請書類」欄にデータが入力されているセルを、「手続き完了者」とみなしてカウントします。
■入力済みのセルをカウントする
入カセル E4 入力式 =COU NTA〔 C3:C9)
check
=COUNTA〔値1[,1直2]・・・・・・〕
値…データの個数を調べる値やセル範囲を指定
指定した「値」に含まれるデータの数を返します。未入力のセルはカウントされません。「値」は255個まで指定できます。
memo
)COUNT関数が数値と日付だけをカウントするのに対して、CO∪ NTA関数は数値、日付、文字列、論理値などすべてのデータをカウントします。
>CO∪ NTA関数は、数式が入力されているセルもカウントします。数式の戻り値として
「・・」が返されたセルは、見た目は空白ですが、カウントの対象になります。
031見た目が空自のセルをカウントする
00UNTBLANK
セル範囲に含まれる空白セルをカウントするには、COUNTBLANK関数を使用します。ここで言う「空白」とは、見た目が空自のセルです。未入力のセルと、数式の戻り値として「¨“」が返されたセルはカウント対象です。ここでは、セル範囲D3:D8から空白セルをカウントします。
■空自のセルをカウントする
入カセル F3 入力式 =COUNTBLANKlD3:D8〕
check
=COUNTBLANKIセル範囲〕
セル範囲…空自セルの個数を調べるセル範囲を指定
指定した「セル範囲」に含まれる空白セルの数を返します。空白文字列「…」が入力されているセルもカウントの対象になります。数値の0はカウントされません。
.節●面お
>サンプルのセルD3:D8には「=IF(B3=01”支給無
‖
」F(C3く>m′・済み1∵ l・
))」という数式が入力されており、「給与」が0のセルに「支給無」、「支払日」が入力されているセルに「済み」、それ以外のセルに空白文字列「山」を表示しています。つまり、セルF3に入力したCO∪ NTBLANK関数で、見た目が空白のセル(実際には数式が入力されているセル)をカウントしています。
>全角や半角のスペースが入力されているセルは、COU NTBLANK関数のカウントの対象になりません。
032見た目も中身も空自のセルをカウントする
rows columns counta
■■コ■で紹介したCOUNTBLANK関数は、未入力のセルも、数式の結果が何も表示されていないセルも、区別せずにカウントします。未入力のセルだけをカウントしたいときは、CO∪ NTBLANK関数を使わずに、すべてのセルの個数からデータが入力されているセルの個数を引き算します。指定したセル範囲に含まれるセルの個数は、行数と列数の積から求めます。データが入力されているセルの個数は、CO∪ NTA関数で求めます。
■見た日も中身も空自のセルをカウントする
入カセル F4 入力式 =ROWStA2:D51*COLUMNSiA2:D5〕 ―COUNTAIA2:D5〕
団雷目□
=ROWSi配列〕→|■匝ヨ
=COLUMNSI配列〕→ |■亜ロ
=COUNTAl値1[,値2]……〕→ ■■ED
memo
づ1.
>セルD3には「=IF(B3=m∵ ‘・′DATEDlF(B3,TODAY()メY・))」という数式が入力されており、セルB3に生年月日が入力されるまで年齢が表示されない仕掛けになっています。
>ROWS関数は引数のセル範囲の行数を返し、COLUMNS関数は引数のセル範囲の列数を返します。
=ROヽへ′SiA2:D51■ COLUMNSIA2:D51-COUNTAIA2:D51
A2:D5の行数 X A2:D5の列数 ―A2:D5の入力済みのセル数
A2:A5のセル数
コメント