ExcelのSUMIF関数の条件で「以上、未満」を指定する

この記事には広告を含む場合があります。

記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

こんにちは。かわべです。

ExcelのSUMIF関数は「もし○○だったら、合計する」という条件を設定し合計値を求める関数です。

今日はこの関数で「○○以上だったら」あるいは、「○○未満だったら」という条件を指定するケースを中心にいくつかの検索条件について記載してみます。

関連記事 SUMIF関数とSUMIFS関数で条件を指定した合計値を表示する

SUMIF以上未満等_14
参考

参考 support office;SUMIF関数


SUMIF関数の基本

SUMIF関数の記述方法

SUMIF関数は次のように記述します。
SUMIF関数_32
SUMIF関数とは、簡単に記載すると、「もし○○だったら合計しましょう」という関数です。

例えば、「商品がいくつも記載されている売上表から『おにぎり』の販売数量の合計値を表示したい」というような場合に使います。

関数の引数は次のように設定します。

[aside type=”boader”]◆ 範囲

検索条件の対照となる範囲、つまり「○○だったら」の○○が記載されている範囲を設定します。

◆ 検索条件

検索したい文字等を入力します。

◆ 合計範囲

合計したい数値が入力されているセルの範囲を設定します。[/aside]

[aside type=”yellow”]省略等の引数の詳細は、support office;SUMIF関数を参考にしてください。[/aside]

SUMIF関数の使用例

例えば次の画像のような表を作成したときに使用します。

SUMIF以上未満等_11

上の画像のC16セルには、SUMIF関数を使って「B16セルに入力した文字と同じ文字が入力されている行のC列の数値を合計する」という式が入力されています。

[aside type=”boader”]「=SUMIFB2:B11,B16,C2:11)」[/aside]

引数は次のように設定しました。

[aside type=”boader”]『範囲』

B2セルからB11セルが検索条件の対象が入力されているセルとなります。

『検索条件』

B16に入力した値が条件となります。上記の画像では「A」です。

『合計範囲』

C2セルからC11セルを指定しています。[/aside]

SUMIF関数の検索条件の例

「未満、以上」を指定して検索

SUMIF関数の検索条件に「20万円未満」など、一定の数値と比較した条件を指定してみます。

SUMIF以上未満等_12

上の画像のC16セルには、SUMIF関数を使って「C列の数値のうち20万円未満の数値を合計するという式が入力されています。

[aside type=”boader”]「SUMIF(C2:C11,“<200000”,C2:C11)」[/aside]

「検索条件」で以上、未満を指定する場合は、ダブルクォーテーションマーク「”」を前後に入力して不等号(等号)マークと数値を入力します。

[aside type=”boader”]

  • 20万円未満 「”<200000″」
  • 20万円以下 「”<=200000″」
  • 20万円以上 「”>=200000″」
  • 20万円超  「”>200000″」

[/aside]

(support office;SUMIF関数より)

重要: 文字列条件や、論理記号または数学記号を含む条件は、二重引用符 () で囲む必要があります。条件が数値の場合、二重引用符は不要です。

 

検索条件にセルを指定する場合

SUMIF関数で検索条件に「一定のセルに入力されている数値未満」を指定してみました。

上の画像ではA16セルに「200,000」を入力してありますので、「セルA16に入力されている数値未満」と指定してみます。

《セル指定前の式》「SUMIF(C2:C11,“<200000”,C2:C11)」

上記の式の「“<200000”」を次のように変更するとうまくいきません。

[aside type=”yellow”]《誤り①》「SUMIF(C2:C11,“<A16”,C2:C11)」

《誤り②》「SUMIF(C2:C11,“<“A16”,C2:C11)」

《誤り③》「SUMIF(C2:C11,<A16,C2:C11)」[/aside]

次のように入力したらうまくいきました。

[aside type=”boader”]「SUMIF(C2:C11,“<“&A16,C2:C11)」[/aside]

不等号(等号)マークをダブルクォーテーションマーク『””』で囲み、その後に「&」と「セル番号」を入力すると希望どおりの検索条件を指定することができました。

SUMIF関数の検索条件に「日付」を指定

SUMIF関数では「6/30以前」のように日付を指定することもできます。

SUMIF以上未満等_13

上の画像の集計基準のA16セルには、日付を入力しました。また、C16セルにはSUMIF関数を使って「6/30以前の数値を合計する」という式を入力しました。

[aside type=”boader”]「SUMIF(A2:A11,“<=”&A16,C2:11)」[/aside]

検索条件を「6/30以前」としたいため、6/30の数値も集計するように検索条件に「”<=”&A16」と入力しています。(上記の以上、未満の例と違い(検索の)『範囲』を日付の列である「A2セル~A11セル」を指定しました。)

おまけ COUNTIF関数

SUMIF関数と似た関数にCOUNTIF関数があります。COUNTIF関数は「もし○○だったら個数を数えましょう」という関数です。

上記の画像のセルD16には、COUNTIF関数を使って、20万円未満の値の個数を表示するように、次の式を入力しています。

[aside type=”boader”]「COUNTIF(C2:C11,“<200000”)」[/aside]

ワイルドカード文字による検索

SUMIF関数の検索条件にワイルドカード文字(半角の疑問符『?』か半角のアスタリスク『*』)を使うと次のような「任意の文字」を検索条件に加えることができます。

[aside type=”boader”]● 半角の疑問符『?』・・・任意の1文字

● 半角のアスタリスク『*』・・・1文字以上の任意の文字列[/aside]

ワイルドカード文字を使うと次の画像のように集計されます。

上記の画像の緑色の集計表では、「A*」や「A1?」という検索条件をしていしています。

「A*」を検索条件に指定すると頭に「A」のつく商品名「A11、A12、A22、A23」の金額を集計します。(上の画像のC23セル)

「A1?」を検索条件に指定すると「A1と任意の1文字」の商品名「A11、A12」の金額を集計します。

半角の疑問符「?」(B28セル)を検索条件とした場合は、商品名に1文字の名称がないため、集計結果は「0」となります。

半角のアスタリスク「*」(B29セル)を検索条件とした場合は、「1文字以上の任意の文字列」の商品名が検索条件となるので、すべての商品名の金額を集計することになります。

(support office;SUMIF関数より)

  • 検索条件” には、半角の疑問符 (?) または半角のアスタリスク (*) をワイルドカード文字として使うことができます。ワイルドカード文字の疑問符は任意の 1 文字を表し、アスタリスクは 1 文字以上の任意の文字列を表します。ワイルドカード文字ではなく、通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に、”~*” のように半角のチルダ (~) を付けます。

プログラミングを学ぶならTechAcademy [テックアカデミー]


■□◆◇ 編集後記 ◇◆□■

昨日は東陽町のお客様のところへ。

Excelで支払調書を作成してみましたが、うまくいきました。

[aside type=”boader”]最新のExcelを使っていますか?

ビジネスで使う関数は、Excelに登録されているもののうちごく一部かと思いますが、最新のExcelには古いバージョンのExcelにはない[emphasis]新しい関数[/emphasis]があります。より快適にExcelを使うなら最新のExcelは欠かせないでしょう。

大塚商会のたよれーる Office 365は、最新のOfficeを1ユーザー最大15台までインストールが可能で、月額制のため初期コストを抑えることができます。

月額1,080円(税別)から利用できます。(いかにも税込っぽい価格ですが、税別です。)

[/aside]