[[20170808040548]]　『AVERAGEIFS関数の平均対象範囲の指定について』（milkbear）　ページの最後に飛ぶ

『AVERAGEIFS関数の平均対象範囲の指定について』（milkbear）

AVERAGEIFS関数の平均対象範囲の指定方法についてご教授ください。

「商品」シートに
商品ごとに指定期間内の平均値を求める式を作成したいと思っていますが、
「平均対象範囲」の指定がうまくいかず困っています。

平均対象範囲には、「商品」シートの商品IDから「データ」シートを検索し、
一致した行全体を指定したいです。

現在作成している式と、シートの構成は下記になります。

●作成中の式

=AVERAGEIFS(平均対象範囲,'データ'!$2:$2,">="&DATE(YEAR(商品!$L4),MONTH(商品!$L4),1),'データ'!$2:$2,"<="&DATE(YEAR(商品!$M4),MONTH(商品!$M4),1),'データ'!$3:$3,"PV")

●シート内容
・「商品」シート
　B列：商品ID
　L列：対象期間_開始日
　M列：対象期間_終了日

  AB列：PV平均値（ここに上記の式を設定）

・「データ」シート
　A列：商品ID
　B列以降：月間の各データ
　2行目：集計月の1日の日付
　3行目：項目名

MATCH関数を使用して、
MATCH(B4,'データ'!$A:$A,0)
とすれば、該当行を検索できることは分かるのですが、
平均対象範囲として指定するためには、
どのように記述すれば良いでしょうか？

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

おはようございます。

内容のご確認をさせていただいてもよろしいでしょうか。

「データ」シートの内容なのですが、
B列：2017年1月、C列：2017年2月、D列：2017年3月...
B2：2017/1/1、C2：2017/2/1、D3：2017/3/1...
という理解でよいでしょうか。

また、「データ」シートの3行目、「項目」にはどういうデータが入力されますか？
（「PV」とはなにを表しますか？）

「データ」シートは、商品ごとに（商品IDごとに）複数行を使う...という風に読み取れるのですが、合っているでしょうか。
読解力がないのに口を出そうとして恐れ入りますが...
（きゅうり婦人） 2017/08/08(火) 08:32

きゅうり婦人様

コメントいただきありがとうございます。
説明が分かりづらく失礼いたしました。

「データ」シートについてですが、
2行目から下記のようになっております。

	A	｜	B	｜	C	｜	D	｜	E	｜	F	｜	G	｜	
2行目：		｜	2016/7/1｜	2016/7/1｜	2016/7/1｜	2016/8/1｜	2016/8/1｜	2016/8/1｜	…
3行目：	商品ID	｜	PV	｜	直帰率	｜	CVR	｜	PV	｜	直帰率	｜	CVR	｜	…
4行目：	8375	｜	300	｜	70.0%	｜	1.2%	｜	300	｜	70.0%	｜	1.2%	｜	…
5行目：	8376	｜	300	｜	70.0%	｜	1.2%	｜	300	｜	70.0%	｜	1.2%	｜	…
6行目：	8377	｜	300	｜	70.0%	｜	1.2%	｜	300	｜	70.0%	｜	1.2%	｜	…

B～D列、E～G列・・・と3列ごとに一月分のデータとなっております。
3行目の日付欄は、AVERAGEIFS関数で条件を指定するために便宜上、
同じ日付を入力してあります。
（「商品」シートの対象期間に含まれる月を平均したいため。）

項目名には、「PV（ページビュー）」「直帰率」「CVR（コンバージョン）」の
3種類の項目名が記載されています。

やりたいことは、
商品ごとの対象期間と合致する月の「PV」「直帰率」「CVR」を商品ごとに平均して表示する
ということなのですが・・・

平均対象期間を直接指定すれば、正しく表示されているのですが、
商品数が多いため、関数で自動的に検索できればと思っています。

もしお分かりになりましたら、お知恵をいただけますよう
どうぞよろしくお願いいたします。

（milkbear） 2017/08/08(火) 09:54

 書いてるときに衝突しましたが、たぶん、あってるのでそのままアップします。

 別表作るの面倒だったので、サンプルです。
 例では
 A列が003の行（6行目）で、2行目が文字列のPVだった場合の平均です。

 H1=AVERAGEIFS(INDEX(1:50,MATCH("003",A:A,0),),$3:$3,"PV")
               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑~
 INDEX関数は、行数または列数を指定しなければ、その行・列の参照を返します。
 数式の検証でどのように推移していくか確認してみてください。
http://excel-master.net/worksheet-function/verification-formula/

    |[A]|[B]   |[C]   |[D]   |[E]   |[F]   |[G]|[H]     
 [1]|   |      |      |      |      |      |   |71.66667
 [2]|   |9月1日|9月2日|9月3日|9月4日|9月5日|   |        
 [3]|   |PV    |PV    |      |PV    |      |   |        
 [4]|001|    68|    82|    22|    84|    60|   |        
 [5]|002|    68|    71|    32|    36|    50|   |        
 [6]|003|    87|    48|    14|    80|    41|   |        
 [7]|004|    32|    49|    23|    63|    86|   |        
 [8]|005|    91|    66|    36|    68|    51|   |        
（稲葉） 2017/08/08(火) 09:55

 補足ですが・・・
 ＞商品ごとの対象期間と合致する月の「PV」「直帰率」「CVR」を商品ごとに平均して表示する 
 平均値の平均は、無意味かと思われます。
    |[A]   |[B]  |[C]       |[D]       
 [1]|      |PV   |直帰数    |日別平均  
 [2]|9月1日|10000|      7000|70.0%     
 [3]|9月2日|   10|         7|70.0%     
 [4]|      |     |全体の平均|平均の平均
 [5]|      |     |0.1%      |70.0%     
（稲葉） 2017/08/08(火) 10:02

 間違えました。
 恥ずかしい
    |[A]   |[B]  |[C]       |[D]       
 [1]|      |PV   |直帰数    |日別平均  
 [2]|9月1日|10000|      7000|70.0%     
 [3]|9月2日|   10|         9|90.0%     
 [4]|      |     |全体の平均|平均の平均
 [5]|      |     |70.0%     |80.0%

 正しくはこちらです。
（稲葉） 2017/08/08(火) 10:30

稲葉様

アドバイスいただきありがとうございます。
また、諸々分かりづらい質問の仕方で大変申し訳ありません。

＞商品ごとの対象期間と合致する月の「PV」「直帰率」「CVR」を商品ごとに平均して表示する

 平均値の平均は、無意味かと思われます。

平均値の平均をする訳ではなく、
「データ」シートのPVなどの値がシステム上、
月間（2016/7/1～2016/7/31など）の実測値でしか出力されないため、
対象期間が月をまたいでいる場合に平均値を算出したく、
ご質問させていただいた次第です。

例えば

「商品」シート
A
[A列]商品ID：8375
[L列]対象期間_開始日：2016/7/20
[M列]対象期間_終了日：2016/9/15
[AM列]PV平均値　←ここに式を入力
となっている場合は、

「データ」シートで
商品ID：8375の行の
7月(2016/7/1）と8月(2016/8/1）と9月（2016/9/1）のPVの値を平均したい

という感じです。

一度アドバイスいただいた内容で考えてみます！
ありがとうございます！
（milkbear） 2017/08/08(火) 10:59

コメント返信：

[ 一覧(最新更新順) ]

[[20170808040548]] 『AVERAGEIFS関数の平均対象範囲の指定について』（milkbear） ページの最後に飛ぶ

[[20170808040548]]　『AVERAGEIFS関数の平均対象範囲の指定について』（milkbear）　ページの最後に飛ぶ