- Home »
- Excelの基本操作の目次 »
- 関数・数式を入力する方法 »
- SUMIF関数・SUMIFS関数でテーブルを参照する構造化参照を使った数式を作成する
- SUMIF関数を使って、商品ごとの売上合計を集計する例です。下のデータリストはテーブルには設定していません。
SUMIF関数の構文は =SUMIFP(範囲,検索条件,[合計範囲]) です。
通常の数式ではI3セルは =SUMIF($C$3:$C$13,$H3,$F$3:$F$13) となります。
- この数式だと、売上表に14行目にデータが追加されたら、=SUMIF($C$3:$C$13,$H5,$F$3:$F$13)のままでは14行目が合計されません。
- I3セルの数式は =SUMIF($C$3:$C$14,$H3,$F$3:$F$14) と修正する必要があります。
テーブルを使う方法
- 売上リストのB2:F13セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジン、テーブルスタイル」を選択して、テーブルに変換します。
- テーブル内のセルを1つ選択して、テーブルツールを表示します。
テーブルツールの[デザイン]タブのテーブル名で「売上」とテーブルの名前を変更しました。
- I3セルの数式は =SUMIF(売上[商品名],H3,売上[金額]) となります。
- テーブル名[列見出し] という形式でテーブルの列を指定することができます。
下図では $H3 と列を絶対参照にしていますが、これは H3 でOKです。
- テーブル「売上」にデータが追加されました。すると、テーブルが自動で拡張されます。
I5セルの数式は 何も修正することなく、正しい答えを返しています。
- 複数条件で合計値を求めるケースではSUMIFS関数を使うことができます。
- SUMIFS関数の構文は =SUMIFS(合計対象範囲,条件範囲1,条件1,[条件範囲2,条件2],[条件範囲3,条件3],・・・) です。
- 開始日から終了日までの各商品の金額の合計を求めたいと思います。
K3セルの数式は =SUMIFS(売上[金額],売上[日付],">="&H3,売上[日付],"<="&I3,売上[商品名],J3) となります。
日付の条件は ">="&H3 と以上を ""(ダブルクォーテーション)でくくり、セル参照とを & でつなげて書きます。
下図では $H3 のように列を絶対参照にしていますが、これは H3 と相対参照でOKです。
- 上記のSUMIFS関数を使った数式を手順を追って入力してみます。
数式を入力するセル K3セルを選択して、数式バーの左隣の[関数の挿入]ボタンをクリックします。
- 関数の挿入ダイアログボックスが表示されます。
関数の分類で「すべて表示」を選択して、関数名で「SUMIFS」を選択します。
[OK]ボタンをクリックします。
- SUMIFS関数の合計対象範囲は シートのF3:F14セルを選択すると、ダイアログの合計対象範囲に 売上[金額] と入力されます。
これは、テーブル名が「売上」の「金額」の列を意味しています。
- 条件範囲を入力するボックス内をクリックしてカーソルを表示します。
B3:B14セルを選択すると、売上[日付] と表示されます。テーブル名が「売上」の「日付」の列を意味しています。
- 条件1を入力するボックス内をクリックしてカーソルを表示します。
">="& と入力して H3 セルをクリックして、 ">="&H3 とします。
- 右側にあるスクロールバーを使って、条件範囲2、条件2、条件範囲3、条件3を表示します。
条件範囲2に B3:B14セルを選択して、 売上[日付] と入力します。
条件2に "<="&I3 と入力します。
条件範囲3に C3:C14セルを選択して、 売上[商品名] と入力します。
条件3に J3セルを選択して J3 と入力します。
[OK]ボタンをクリックします。
- K3セルに =SUMIFS(売上[金額],売上[日付],">="&H3,売上[日付],"<="&I3,売上[商品名],J3) と入力されました。
- K3セルを選択して、フィルハンドルをK5セルまでドラッグして数式をコピーします。
- 数式を作成しているときにテーブル名が表示されないケースがあります。
例えば、下図のように関数の引数のダイアログボックスで、F3:F14セルの範囲を選択しています。
入力するボックスにはセル番地がそのまま表示されて、テーブル名が表示されません。
- このような場合は、[ファイル]タブをクリックして、バックステージで[オプション]を選択します。
Excelのオプションで[数式]タブを選択して、「数式でテーブル名を使用する」のチェックが外れているときはチェックを入れてください。
- なお、「数式でテーブル名を使用する」のチェックが外れているときでも、下図のようにキーボードから手入力することができます。
つまり、自動でセル範囲を テーブル名[列見出し] に変換してくれなくなるだけです。
Home｜Excelの基本操作の目次｜SUMIF関数・SUMIFS関数でテーブルを参照する構造化参照を使った数式を作成する