目次
AVERAGEIF関数について
AVERAGEIFの概要
| 条件に合う数値の平均値を求めるExcel関数/統計 | ||||||||||||||||||||
=AVERAGEIF( 検索条件範囲 , 条件 , 平均対象) 概要 条件に会う数値の平均値を求める | ||||||||||||||||||||
| ||||||||||||||||||||
AVERAGEIFはどのような時に使用するか
- 多数の商品の中から、特定の商品の平均価格を求めたい
AVERAGEIFの使い方
| A | B | C | D | E | |
| 1 | |||||
| 2 | 10 | 100 | |||
| 3 | 10 | 110 | 105 =AVERAGEIF( B2:B4 , 10 , C2:C4 ) ※(100+110)/2 | ||
| 4 | 4 | 200 | |||
| 5 | =AVERAGEIF( C2:C4 , “>100” , B2:B4 ) とすると、100を超える数値となり、110と200が対象となるため、平均値の 7 が出力される。 D2を>100とし、=AVERAGEIF( C2:C4 , D2 , B2:B4 )としてもよい | ||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
AVERAGEIFとAVERAGEIFSの違い
- AVERAGEIFは条件一つ、AVERAGEIFSは複数の条件を設定できる。ただし、これは「かつ」であるANDの条件です。AVERAGEIFSは一つの条件でも構わないことから、AVERAGEIFSに統一するとよいかもしれません。
- 同じ列について、「または」であるORのつもりでAVERAGEIFS(対象,B列,10,B列,30)というように複数条件とすることはできません。#DIV/0エラーとなります。「かつ」、つまりANDであるから、存在せず、0個だからです。
- 同一列について複数条件をORで設定するには、各々を求めた上で、その平均を取ります。(AVERAGEIF1*個数1+AVERAGEIF1*個数2)/(個数1+個数2)です。以下の式は例です。
- (AVERAGEIF(B1:B7,10,C1:C7)*COUNTIF(B1:B7,10)+AVERAGEIF(B1:B7,30,C1:C7)*COUNTIF(B1:B7,30))/(COUNTIF(B1:B7,10)+COUNTIF(B1:B7,30))
- 各々の数の平均を取り、COUNTIFでその個数を計算して重みを計算します。COUNTIFがなければ正しい数値になりません。
AVERAGEIFと離れたセルの平均値
- 上記の説明と同じように、ORで考えることになります。単純に合計して計算をしても個数が異なるため正しい答えにならないため注意してください。
- 別シートや別の場所で平均値をとるための範囲セルを設けてAVERAGEIF計算をするようにしたほうがよいです。
AVERAGEIFで0以外や特定の文字以外とするには
- 以外は<>と不等式を挟んで用います。0以外であれば、”<>0″です。
- 以下について、神奈川以外の項目についての平均を取ります。
| A | B | C | D | E | |
| 1 | 東京 | 1 | |||
| 2 | 神奈川 | 2 | 3.8 =AVERAGEIF( B1:B6 ,“<>神奈川”, C1:C6) B列に15は0個(なし)のため、”なし”と表示される | ||
| 3 | 埼玉 | 3 | |||
| 4 | 千葉 | 4 | |||
| 5 | 栃木 | 5 | |||
| 6 | 群馬 | 6 |
AVERAGEIFと#DIV/0エラー
- 検索条件範囲に該当がない場合、#DIV/0 エラーとなります。これは該当個数が0、つまり0除算をしているためです。
- 平均対象についても、該当するセルの全てが空白や文字などの場合も同様に#DIV/0となります。
- 以下は該当する項目がなく、#DIV/0となりますが、該当なしの場合は「なし」と表示する例です。
| A | B | C | D | E | |
| 1 | 10 | 1 | |||
| 2 | 10 | 2 | なし =IF(COUNTIF(B1:B6,15)=0,“なし”,AVERAGEIF(B1:B6,10,C1:C6)) B列に15は0個(なし)のため、”なし”と表示される | ||
| 3 | 20 | 3 | |||
| 4 | 20 | 4 | |||
| 5 | 30 | 5 | |||
| 6 | 30 | 6 |
AVERAGEIFで以上、以下の条件
- 以上であり、かつ、以下である、という2つの条件になります。以上以下の条件を1つの論理式で書くことはできないため、AVERAGEIFSを用います。
- 以下はB列が10以上、20以下の場合です。以下の場合であれば30以外としてAVERAGEIF(B1:B7,”<>30″,C1:C7)でもよい。
| A | B | C | D | E | |
| 1 | 10 | 1 | |||
| 2 | 10 | 2 | 2.5 =AVERAGEIFS( C1:C7 , B1:B7 ,“>=10” ,B1:B7,”<=20″) 1,2,3,4の平均2.5となる | ||
| 3 | 20 | 3 | |||
| 4 | 20 | 4 | |||
| 5 | 30 | 5 | |||
| 6 | 30 | 6 | |||
| 7 | 30 | 7 |
AVERAGEIFに関する参考解説
Excelの関数を検索する
| 分類 | 関数 | 説明 |
|---|---|---|
| 日付 | DATE | 年月日から日付を求める |
| 日付 | DATEDIF | 開始日から終了日までの期間を求める |
| 日付 | DAY | 日付から日を抽出する |
| 日付 | EDATE | 指定月後の月日を求める |
| 日付 | EOMONTH | 指定月後の月末を求める |
| 日付 | EOMONTH+1 | 指定月後の月初めを求める |
| 日付 | HOUR | 時刻から時間を取り出す |
| 日付 | MINUTE | 時刻から分を取り出す |
| 日付 | MONTH | 日付から付きを抽出する |
| 日付 | NETWORKDAYS | 営業日数を求める(土日休日型) |
| 日付 | NETWORKDAYS.INTL | 営業日数を求める(定休日と休業日指定型) |
| 日付 | NOW | 今日の日付と時刻を求める |
| 日付 | SECOND | 時刻から秒を取り出す |
| 日付 | TIME | 時間を指定して時刻データを作成する |
| 日付 | TODAY | 今日の日付を求める |
| 日付 | WEEKNUM | 1月1日から何週目かを求める |
| 日付 | WEEKDAY | 年月日から曜日番号を求める |
| 日付 | WORKDAY | 営業日について指定した日数後の年月日を求める |
| 日付 | WORKDAY.INTL | 営業日について指定した日数後の年月日を求める(定休日と休業日指定型) |
| 日付 | YEAR | 日付から年を抽出する |
| 文字操作 | & | 文字列を結合する |
| 文字操作 | ASC | 全角文字を半角文字に置き換える |
| 文字操作 | CHAR/CLEAN | 改行を削除する |
| 文字操作 | CONCATENATE | 文字列を結合する |
| 文字操作 | DELTA | 数値が等しいかを判定する |
| 文字操作 | EXACT | 文字列が等しいかを判定する |
| 文字操作 | FIND(B) | セル内の指定した文字の位置を求める |
| 文字操作 | ISODD | 指定した数値が奇数か偶数かを判定する |
| 文字操作 | JIS | 半角文字を全角文字に置き換える |
| 文字操作 | LEFT(B) | 指定した文字数分の文字を先頭から取り出す |
| 文字操作 | LEN(B) | 文字数をカウントする |
| 文字操作 | LOWER | 文字列を小文字にする |
| 文字操作 | MID(B) | 指定した文字以降の文字を取り出す |
| 文字操作 | PHONETIC | フリガナを取り出す |
| 文字操作 | PROPER | 文字列の先頭を大文字にする |
| 文字操作 | REPLACE(B) | 指定した位置にある文字を置き換える |
| 文字操作 | RIGHT(B) | 指定した文字数分の文字を末尾から取り出す |
| 文字操作 | SUBSTITUTE | 指定した文字列を置き換える |
| 文字操作 | SEARCH(B) | セル内の指定した文字の位置を求める |
| 文字操作 | TEXT | 日付などの数値(シリアル値)を文字形式に変換、抽出する |
| 文字操作 | TRIM | 余分な空白を削除する |
| 文字操作 | UPPER | 文字列を大文字にする |
| 文字操作 | VALUE | 文字列を数値(シリアル値など)に変換する |
| 論理 | 論理式 | TRUEとFALSE |
| 論理 | AND | すべての条件を満たす論理式 |
| 論理 | IF | 論理式の結果によって表示を切り替える |
| 論理 | OR | いずれかの条件を満たす論理式 |
| 数学 | AGGREGATE | 指定した19の集計方法で集計値を求める |
| 数学 | CEILING | 基準値の単位で切り上げる |
| 数学 | FLOOR | 基準値の単位で切り下げる |
| 数学 | INT | 小数点以下を切り捨てる |
| 数学 | MOD | 割り算の余りを求める |
| 数学 | MROUND | 基準値の単位で四捨五入する |
| 数学 | PRODUCT | 掛け算を求める |
| 数学 | QUOTIENT | 割り算の商を求める |
| 数学 | RAND | 乱数を発生させる |
| 数学 | ROUND | 指定した桁で四捨五入する |
| 数学 | ROUNDDOWN | 指定した桁で切り捨てる |
| 数学 | ROUNDUP | 指定した桁で切り上げる |
| 数学 | SUBTOTAL | 指定した11の集計方法で集計値を求める |
| 数学 | SUM | 数値の合計を求める |
| 数学 | SUMIF | 特定の条件を満たすセルの合計値を求める |
| 数学 | SUMIFS | 複数の条件を満たすセルの合計値を求める |
| 数学 | SUMPRODUCT | 条件付きセルの乗算数の合計を求める |
| 数学 | SUMPRODUCT | セルの乗算数の合計を求める |
| 数学 | TRUNC | 指定した桁数を切り捨てる |
| 数学 | TRUNC | 小数点以下を切り捨てる |
| 検索 | CHOOSE | インデックス数値に対応する値を表示する |
| 検索 | INDEX | 範囲内の指定したデータを取り出す |
| 検索 | INDEX | 指定した行位置に対応する列データを取り出す |
| 検索 | INDEX | 指定した列位置に対応する行データを取り出す |
| 検索 | LOOKUP | 検索値に一致するデータを検索する |
| 検索 | MATCH | 値に一致する行位置を求める |
| 検索 | MATCH | 値に一致する列位置を求める |
| 検索 | VLOOKUP | 検索値に一致するデータを検索する |
| 検索 | VLOOKUP | 検索値に対応するデータ(範囲型)を求める |
| 統計 | AVERAGE | 平均値を求める |
| 統計 | AVERAGEA | 平均値を求める |
| 統計 | AVERAGEIF | 条件に合う数値の平均値を求める |
| 統計 | AVERAGEIFS | 複数の条件に合う数値の平均値を求める |
| 統計 | COUNT | 指定したデータの個数を求める |
| 統計 | COUNTA | 指定したデータの個数を求める |
| 統計 | COUNTBLANK | 空白セルの個数を求める |
| 統計 | COUNTIF | 条件に合うデータ数を求める |
| 統計 | COUNTIFS | 複数の条件に合うデータ数を求める |
| 統計 | FREQUENCY | データの度数を求める |
| 統計 | LARGE | 大きいほうからの順位の数値を求める |
| 統計 | MAX | データの最大値を求める |
| 統計 | MAXA | データの最大値を求める |
| 統計 | MEDIAN | 中央値を求める |
| 統計 | MIN | データの最大値を求める |
| 統計 | MINA | データの最大値を求める |
| 統計 | MODE.MULT | データの最頻値を求める(こちらを推奨) |
| 統計 | MODE.SNGL | データの最頻値を求める(MODE.MULT推奨) |
| 統計 | PERCENTILE.EXC | 百分位数(パーセント)を求める |
| 統計 | PERCENTILE.INC | 百分位数(パーセント)を求める |
| 統計 | PERCENTRANK.EXC | データの位置を百分率で求める |
| 統計 | PERCENTRANK.INC | データの位置を百分率で求める |
| 統計 | QUARTILE.EXC | 四分位数を求める |
| 統計 | QUARTILE.INC | 四分位数を求める |
| 統計 | RANK.EQ | データの順位を求める |
| 統計 | SMALL | 小さいほうからの順位の数値を求める |
| 統計 | STDEV.P | 標準偏差(母集団型)を求める |
| 統計 | STDEV.S | 標準偏差(標本型)を求める |
| 統計 | STDEVA | 標準偏差(標本型で論理等含む)を求める |
| 統計 | STDEVPA | 標準偏差(母集団型で論理等含む)を求める |
| 統計 | VAR.P | データの分散(標本分散)を求める |
| 統計 | VAR.S | データの分散(不偏分散)を求める |
| 統計 | VARA | データの分散(不偏分散)を求める |
| 統計 | VARPA | データの分散(標本分散)を求める |