■サンプルデータ
SUMIFS関数のサンプルデータです。
動作の確認と検証に使用してください。ただし、関数によっては未完成のものもあります。
今後バージョンをアップしていきます。定期的にダウンロードしてみてください。
※未完成なので、ご了承ください。少しづつ完成させ、アップロードしていきます。
[wpdm_button_template id=14470 style=”instagram” align=”left”]
■複数の条件を指定して数値を合計したい / =SUMIFS()
【関数名】：SUMIFS（サム・イフ・エス）
【用途】：複数の検索条件に一致したセルを検索し、見つかったセルと同じ行や列にある合計範囲のセルの数値の合計を求めることができる。
【書式】：=SUMIFS(合計対象範囲 , 検索範囲1 , 条件1, 検索範囲2 , 条件2 , …）
【補足】
- 検索条件はいくつでも追加できる。
- 検索条件はすべて「AND（アンド）」とみなされる。
例１ 「2017/6/2」かつ「文房具」の合計値を算出したい場合
=SUMIFS(F5:F17,A5:A17,”2017/6/2″,C5:C17,”文房具”)
上記の方法は最もスタンダードな使用方法になります。
SUMIF関数と異なり、条件の最初に合計対象の範囲が必要になります。
検索条件は「半角 / 全角」を区別するため注意が必要になります。
例２ 「2017/6/1以外」かつ「家具」で「数量が２以上」の合計値を算出したい場合
=SUMIFS(F5:F17,A5:A17,”<>2017/6/2″,C5:C17,”家具”,E5:E17,”>1″)
検索の指定条件に「比較演算子」を追加します。
複数の条件を指定した場合は、すべてANDになるため、すべての条件に合致したものが合計されます。
例３ 「商品名に”花”」が含まれており、かつ「単価が1,000以上」の合計値を算出したい場合
=SUMIFS(F5:F17,B5:B17,”*花*”,D5:D17,”>=1000″)
上記のように、条件にワイルドカードを使用することも可能です。
条件は254個まで指定できるので、詳細に絞り込めるでしょう。
例４ 「2017/6/2～2017/6/3」で、かつ「単価が500円以上」の合計値を算出したい場合
=SUMIFS(F5:F17,A5:A17,”>=2017/6/2″,A5:A17,”<=2017/6/3″,D5:D17,”>=500″)
色々な方法はありますが、2017/6/1～2017/6/2のような特定の期間にするためには、上記のように記述します。
SMUNIFS関数では、日付での指定を使用することが多いため、覚えておいて損はありません。
例５ 空白以外のセルの合計値を算出したい場合
=SUMIFS(F5:F17,A5:A17,”<>”)
空白のセル以外は、上記のように不等号の比較演算子を使います。
こうすることで日付が空白の行が合計に組み込まれることはありません。
例６ 分類の「家具」または「雑貨」のＯＲ条件で合計値を出したい場合
=SUM(SUMIFS(F5:F17,C5:C17,{“家具”,”雑貨”}))
SUMIFS関数は基本的にAND条件でしか合計値を算出できません。
そこで、OR条件にするためには配列数式を使用します。
上記の例では「家具」または「雑貨」の行のみが合計されます。それをSUM関数でまとめることで合計を計算できます。
ちなみに、配列数式はいくつでも設定できます。
例えば、{“家具”,”雑貨”,”文房具”}でも構いません。
配列数式を使うと記述がスッキリするので、分かりやすくなります。
▶ SUMIFS関数でエラーになったら…
もしSUMIFS関数でエラーになったら、下記のようなことが原因かもしれません。
下の表は条件範囲に「日付」「単価」を設定し、合計範囲が「合計」の欄になっています。
よく見てみると、「日付」の範囲は「A5：A17」ですが、合計範囲だけが「F5：F14」になっており、行数が足りません。
SUMIF関数やSUMIFS関数では、必ず「行数」は同じ範囲でなければエラーとなります。
気を付けてください。
■おさらい：SUMIFS関数のスタンダードな記述方法
【例１】=SUMIFS(F5:F17,A5:A17,”2017/6/2″,C5:C17,”文房具”)
【書式】=SUMIFS(合計対象範囲 , 検索範囲1 , 条件1, 検索範囲2 , 条件2 , …）
SUMIFS関数はSUMIF関数と異なり、合計範囲が先にきます。
似たような関数ですが、この点は注意が必要です。
■おさらい：SUMIFS関数で複数条件（OR条件）に対応する方法
SUMIF関数では複数条件が設定できないため、下記のような方法でOR条件にすることができます。
【例６】=SUM(SUMIFS(F5:F17,C5:C17,{“家具”,”雑貨”}))
OR条件にするためには、上記のように配列数式（{“家具”,”雑貨”}）を利用します。
または、SUMIF関数どうしを加算することでも可能です。
■おさらい：SUMIFS関数で特定の文字列の検索に対応する方法
【例３】=SUMIFS(F5:F17,B5:B17,”*花*”,D5:D17,”>=1000″)
SUMIF関数と同じく、ワイルドカードを使用することもできます。
■おさらい：SUMIFS関数で日付を条件にする方法
【例４】=SUMIFS(F5:F17,A5:A17,”>=2017/6/2″,A5:A17,”<=2017/6/3″)
検索条件に日付を入力し、比較演算子で条件を絞り込みます。
これで2017年6月2～2017年6月3日の条件になります。
- 「＞=2017/6/2」・・・2017年6月2日（含む）以降
- 「＜=2017/6/3」・・・2017年6月3日（含む）以前
■おさらい：SUMIFS関数で空白以外のセルを合計する方法
【例５】=SUMIFS(F5:F17,A5:A17,”<>”)
空白のセル以外とする場合は、検索条件に「<>」を付けます。
集計や統計を求める
- 条件を指定して数値を合計したい
- 複数の条件を指定して数値を合計したい
- 集計方法を指定して集計値を求める
- 数値を丸める（桁の切り上げ、切り捨て、四捨五入など）
- 順列を求める
- 組み合わせの数を求める
- 乱数（ランダムな数値）を発生させる
- データの個数を求める
- 空白セルの個数を求める
- 条件に一致するデータの個数を求める
- 複数の条件に一致するデータの個数を求める
- 数値 or データの平均値を求める
- 条件を指定して数値の平均を求める
- 複数の条件を指定して数値の平均を求める
- 数値 or データの最大値を求める
- 数値 or データの最小値を求める
- 特定の範囲に含まれている値の個数を求める
- 数値の中央値を求める
- 数値の最頻値（最も多く表示されている最小の値）を求める
- 大きいほうから何番目かの値を求める
- 小さいほうから何番目かの値を求める
- 特定の範囲から順位（最上位 or 平均値など）を求める
- 四分位数を求める
日付・時刻を求める
- 日付や年、時刻を求める
- 日付から年、月、日、時間などを取り出す
- 日付が何週目かを求める
- 期限（数ヶ月前、数ヶ月後、期間）を求める
条件によって判定させる
- 条件を決めて判定する
- 複数のすべての条件が満たされているかを判定する
- いずれかの条件が満たされているかを判定する
- 条件が満たされていないことを判定する
- エラーだった場合に判定する
セルの内容を調べる
- セルが空白かどうかを調べる
- セルの値がエラーかどうかを調べる
- 文字列かどうか調べる
- 数値かどうか調べる
- 偶数か奇数かを調べる
- 数式かどうかを調べ、取り出す
- エラーの値やデータの種類を調べる
ある特定の値を探す
- 列または行方向に検索する
- 引数のリストから値を検索する
- 配列から特定の値を検索する
- 指定したセルの参照値を検索
- セルの列番号を調べる
- セルの行番号を調べる
- 列数を調べる
- 行数を調べる
- セルの参照を調べる
- 行と列の位置を入れ替える
- ハイパーリンクを作成する
- ピポットテーブルからデータを取り出す
文字列を操作する
- 文字数を求める
- 左端から指定した文字数だけ文字列を取り出す
- 右端から指定した文字数だけ文字列を取り出す
- 指定した位置から文字数だけ文字列を取り出す
- 文字列の位置を調べる
- 指定 or 検索した文字列に置換する
- 文字列を連結する
- 文字列の前後の空白を削除する
- 印刷できない文字を削除する
- ふりがなを取り出す
- 指定回数だけ文字列を繰り返す
- 全角文字 or 半角文字に変換する
- 英字を大文字 or 小文字に変換する
- 数値を表す文字列を数値に変換する
- 英単語の先頭文字を大文字に変換する
- 文字列が等しいかどうか調べる
データベースを制御する
- 条件を満たす数値の個数を求める
- 条件を満たす空白以外のセルの個数を求める
- 条件を満たすセルの合計を求める
- 条件を満たすセルの平均を求める
- 条件を満たす最大値 or 最小値を求める
- 条件を満たすデータを探す