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

<スポンサーリンク>  
 

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

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

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

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

SUMIF以上未満等_14
参考

参考 support office;SUMIF関数


<スポンサーリンク>    
 

SUMIF関数の基本

SUMIF関数の記述方法

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

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

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

◆ 範囲

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

◆ 検索条件

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

◆ 合計範囲

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

省略等の引数の詳細は、support office;SUMIF関数を参考にしてください。

SUMIF関数の使用例

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

SUMIF以上未満等_11

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

「=SUMIFB2:B11,B16,C2:11)」

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

『範囲』

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

『検索条件』

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

『合計範囲』

C2セルからC11セルを指定しています。

SUMIF関数の検索条件の例

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

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

SUMIF以上未満等_12

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

「SUMIF(C2:C11,“<200000”,C2:C11)」

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

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

(support office;SUMIF関数より)

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

 

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

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

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

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

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

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

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

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

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

「SUMIF(C2:C11,“<“&A16,C2:C11)」

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

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

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

SUMIF以上未満等_13

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

「SUMIF(A2:A11,“<=”&A16,C2:11)」

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

おまけ COUNTIF関数

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

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

「COUNTIF(C2:C11,“<200000”)」

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

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

● 半角の疑問符『?』・・・任意の1文字

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

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

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

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

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

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

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

(support office;SUMIF関数より)

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


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

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

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

 


<スポンサーリンク>


お知らせ等

<このブログの管理人>
勤務先 ⇒ UI総合事務所
 
<お問い合わせ先>
 税務顧問契約等をご希望の方は、UI総合事務所のお問い合わせページよりお問い合わせください。

 税理士かわべと直接、顧問契約等をご希望の方はお問い合わせページよりお問い合わせください。

<スポンサーリンク>


<スポンサーリンク>








Pocket
このエントリーを Google ブックマーク に追加
LINEで送る