=COUNTIFS(範囲1,検索条件1,範囲2,検索条件2,…)
■すべての条件に合うデータを数える。条件は複数指定でき、「範囲1」に対する条件を「検索条件1」、「範囲2」に対する条件を「検索条件2」…と指定する
社員一覧表で、性別が「男」で、所属部署が「営業部」の人数を集計します。複数の条件に合致するデータの個数を数えるには、COUNTIFS関数を使います。
今回の例では、「性別」のセル全体を対象に「男」、「所属部署」のセル全体を対象に「営業部」、という２つの条件を指定します。
COUNTIFS関数では、引数に複数の条件を並べて書けば、１２７個まで条件指定できます。
また2-04.COUNTIF関数で紹介した、検索条件に「〜以上」「〜を含む」といった条件での指定もできます。
=COUNTIF(範囲,検索条件)
■「範囲」に指定したセル範囲内で、「検索条件」に合ったセルの個数を数える。
数学の得点をまとめた表で、80点以上の人数を数えます。COUNTIF関数で、「〜以上」「〜未満」といった条件を指定します。
引数「検索条件」に、「〜以上」「〜未満」といった数値の大小を指定するには、「＝」や「＞」などの記号を使います。今回は「80以上」なので、「”>=80″」のように記載します。
このように直接条件式を設定する場合は「”」ダブルクォーテーションで囲む必要があります。
検索条件の書き方は下記参照
| ≪検索内容≫ | ≪検索条件≫ |
|---|---|
| 80と等しい | “=80” |
| 80以上 | “>=80” |
| 80より大きい | “>80” |
| 80以下 | “<=80” |
| 80未満 | “<80” |
| 80以外 | “<>80” |
=COUNTIF(範囲,検索条件)
■「範囲」に指定したセル範囲内で、「検索条件」に合ったセルの個数を数える。
住所が「東京都」の人数を調べます。
社員一覧表から、住所の都道府県ごとの件数を数えるために、COUNTIF関数で数えます。
一部一致の検索条件の場合は、検索条件を「東京都」だけでは、一致しないので数えられません。
そのために「＊」アスタリスクという記号を使うと、「〜で始まる」「〜を含む」といった条件も指定できます。
東京都で始まるなら「東京都＊」と指定します。
ここでは、D2セルの「東京都」の文字と「＊」を、「＆」を使って結合した「範囲」を絶対参照にしておけば、コピーするだけでほかも数えられます。
検索条件の書き方は下記参照
| ≪検索内容≫ | ≪検索条件≫ |
|---|---|
| 「ABC」と等しい | ”ABC” |
| 「ABC」で始まる | ”ABC＊” |
| 「ABC」を含む | ”＊ABC＊” |
| 「ABC」で終わる | ”＊ABC” |
| 「セル指定の場合」～で始まる | D2 ＆ ”＊” |
| 「セル指定の場合」～を含む | ”＊” ＆ D2 ＆ ”＊” |
| 「セル指定の場合」～で終わる | ”＊” ＆ D2 |
=COUNTIF(範囲,検索条件)
■「範囲」に指定したセル範囲内で、「検索条件」に合ったセルの個数を数えます。
条件に合ったデータの個数を調べる
図のような担当者一覧表で、特定の文字が入力されているセルで部署の人数を数える場合に、検索範囲内に条件に合うデータを数えるCOUNTIF関数を使うと便利です。
上図の部署人数には「営業部」「総務部」が入力されています。
種別が「営業部」の人数は、COUNTIF関数の引数「範囲」に種別のセル（C3〜C12）を、「検索条件」に「営業部」と入力されているF2セルを指定すれば求められます。
このとき、「範囲」を絶対参照(＄)にしておくと、関数式をドラッグでコピーすれば、「総務部」の人数も計算できる
検索条件の指定の仕方は大きく分けて2種類あり、文字で直接指定する場合は、”営業部”のように、文字列を「”」（ダブルクォーテーション）で囲んで数える方法もあります。今回のように、他のセルに検索条件の文字列が入力されているときは、そのセル番地を指定します。
=COUNT(セル範囲1,セル範囲2,…)
■引数「セル範囲1」「セル範囲2」…に指定したセルやセル範囲で、数値の入ったセルの個数を数えます
=COUNTA(セル範囲1,セル範囲2,…)
■引数「セル範囲1」「セル範囲2」…に指定したセルやセル範囲で、文字・数値・数式などデータ入力済みのセルの個数を数えます
売り上げた商品数リストを作成しているが、まだ集計が完了していない場合と返品があった場合はそれぞれのセルに「未確定」と「返品」と入力することにしました。このとき、確定している金額と売り上げに関するすべての商品数を知りたい場合の計算式になります。
簡単な説明ですと数値データを数えるのはＣＯＵＮＴ関数、空欄以外のデータを数えるのはＣＯＵＮＴＡ関数となります。
=MIN(数値1,数値2,…)
■引数「数値1」「数値2」…に指定した数値やセル範囲の中で、最も小さい数値を求めます。
定められた上限額で切り捨てます。
交通費支給や家賃補助支給などで、あらかじめ設定された上限額を限度に支給したい場合に、複数の値の最小値を求めるMIN関数を使うと便利です。
実費と上限額を比較し、小さいほうを支給額とする関数になります。
「数値1」に金額のセル、「数値2」に上限額のF1セルを指定します。関数をコピーする時に基準となるF1セルがずれないよう、「$」を付けた絶対参照にしておきます。最後に、ドラッグでほかのセルにコピーします。
MIN関数は、引数に指定した数値の中の最小値を調べるものです。
=MROUND(数値,基準値)
■「数値」の値に最も近い、「基準値」の倍数を計算する。「数値」が2つの倍数のちょうど中央だった場合は、０から遠いほう（正の数なら大きいほう）の倍数になります。
在庫数に応じて発注箱数を変更します。
箱単位で発注する場合で、必要個数と実際の個数の差（不足分）が1箱の個数の半分以上なら、1箱増やして多めに注文したい場合に、MROUND関数を使用します。
帳票を箱単位で注文するとき、必要個数との差が、１箱の個数の半数以上なら１箱増やして発注したい。MROUND関数を使うと、発注個数がすぐにわかる。それを1箱の個数で割れば、箱数になります。
ＭＲＯＵＮＤ関数は、「基準値」の倍数で、「数値」に最も近いものを求めます。
例えば「＝MROUND（17,6）」なら、17の前後にある６の倍数「12」「18」のうち、近いほうの「18」が計算結果になる。
一方「＝MROUND（13,6）」なら、13に近い「12」が結果になる
=FLOOR(数値,基準値)
■「数値」を、「基準値」の倍数のうち最も近い値に切り捨てる（０に近いほうの倍数にする）
端数を単品で発注するときの箱数を求める
帳票を箱単位で発注して、端数があった場合は単品で発注する場合に、この関数を使えばすぐに計算できます。
端数部分を単品で注文する場合は、ＦＬＯＯＲ関数を使って計算します。
帳票Ａの場合、最低在庫個数は15個で、1箱は10個入りになるので、箱単位で注文する数は、必要個数を超えない最大の「10個」になります。単品も交えて注文する場合、箱単位で発注する個数は、「1箱の個数」の倍数で、「必要個数」以下の最も大きな値（「換算個数」と呼ぶ）になります。これをFLOOR関数で求める場合は、「換算個数」÷「1箱の個数」、単品での発注数は「必要個数」−「換算個数」で計算できます。
=CEILING(数値,基準値)
■「数値」を、「基準値」の倍数のうち最も近い値に切り上げる（０から遠いほうの倍数にする）
箱単位での必要箱数を求めます
「1箱10個入り」のように、箱単位で発注する場合がありますが、このような場合に必要な帳票が不足しないような箱数単位で計算したい場合に、CEILING関数が役立ちます。
例えば、上図の「帳票A」の最低個数は15個で、１箱は10個入り。ＣＥＩＬＩＮＧ関数を使うと、なるべく余剰分を少なくするには、全部で「20個」注文すればよいので、この場合の必要な箱数は「2」になります。
ＣＥＩＬＩＮＧ関数は、「基準値」の倍数のうち、「数値」以上で最も小さい値を求めるになります。
=ROUNDUP(数値,桁数)
■「数値」を、指定した「桁数」で切り上げる。小数第一位で切り上げるときの「桁数」は「０」。これを基準に、小数方向は正の数、整数方向は負の数で指定します
=ROUNDDOWN(数値,桁数)
■「数値」を、指定した「桁数」で切り捨てる。小数第一位で切り捨てるときの「桁数」は「０」。これを基準に、小数方向は正の数、整数方向は負の数で指定します
数値を切り上げる／切り捨てる
ROUND関数、ROUNDUP関数、ROUNDDOWN関数も使い方はほぼ同じですので、処理の対象となる位を指定する引数「桁数」の書き方は下記となります。
| ≪処理する桁≫ | ≪桁数≫ |
|---|---|
| 百の位 | －3 |
| 十の位 | －２ |
| 一の位 | －１ |
| 小数点第一位 | ０ |
| 小数点第二位 | １ |
| 小数点第三位 | ２ |
数値の切り上げには、ＲＯＵＮＤＵＰ関数を使う。
数値の切り捨ては、ROUNDDOWN関数を使う。