[[20190924113027]]　『不均等間隔の数行ごと平均の出し方』（aaa）　ページの最後に飛ぶ

『不均等間隔の数行ごと平均の出し方』（aaa）

http://get.secret.jp/pt/file/1569292303.PNG

各都道府県の店舗数を集計した表があるとします。
（図でいうと北海道3店舗、青森1店舗、岩手2店舗・・・沖縄2店舗を列Bに集計）

4月、8月、12月の各店舗売上合計額を別の列と行に集計しているとします。
（図でいうと列Dから列Gにそれぞれ集計）
各都道府県ごとの平均値を自動で求めたいのですが、列Bの数ごとに列DからGの各列の平均を出す方法がわかりません。
ウェブで検索しても等間隔平均についての記載はあれど、不等間隔平均の自動算出についてはでてきません。
どなたかご教示ください。よろしくお願いいたします。

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

 図は見ていませんけど　AVERAGEIF、AVERAGEIFS　とかが使えるのでは？
（渡辺ひかる） 2019/09/24(火) 11:45

検索値を手動で振って、その検索値ごとにAVERAGEIFを使って平均を出していますが、データが長いので面倒なのです。手動だと間違いも出ますし。

図でいえば列E-Gを3行、1行、2行、2行・・・という風に自動で平均したいわけです。
（aaa） 2019/09/24(火) 12:07

 名前は知っているけど使ったことない関数で、
 DAVERAGE 関数っていうのがあるんですけど、いかがでしょう

 図は見てません。というか私の環境からは見えませんでした。
（´・ω・`） 2019/09/24(火) 12:17

 行数を指定しなくても　AVERAGE　関数は空白セルを無視するので、最大行を指定しておけばいいのでは？
　 
 もし外しているなら、　レイアウトを　きちんと示してください（リンクではなく）

（渡辺ひかる） 2019/09/24(火) 12:28

店舗名が
北海道A
北海道B
北海道C
青森A
岩手A
岩手B
山形A

のようになっていますが、
エリア列を設けることはできませんか？
例：
エリア　店舗名
北海道　北海道A
北海道　北海道B
北海道　北海道C
青森　　青森A
岩手　　岩手A
岩手　　岩手B
山形　　山形A
その列を条件参照すれば、いちいちAVERAGEIFの条件を手動で打つ必要はなくなると思います。
（mori） 2019/09/24(火) 12:43

元になるデータは店舗名のみです。エリア列を設けることは検索値を振るのと変わりませんし、結局手動になってしまいます。

店舗エリアは店舗数の列と同じ順序で並んでいるので、北海道3店舗ならその3店舗に自動で検索値を振れる仕組みがあればいいのですが、、
（aaa） 2019/09/24(火) 12:49

＞行数を指定しなくても　AVERAGE　関数は空白セルを無視するので、最大行を指定しておけばいいのでは？
　
＞もし外しているなら、　レイアウトを　きちんと示してください（リンクではなく）

すいません。EXCEL初心者なのでおっしゃってることがわかりません。

列Bでしめした数ごとに、列D-Gをその数で示した行ごとに平均したいのです。
例えば北海道の4月の平均なら、北海道は3店舗なのでFx=AVERAGE(F2:F4)、8月はFx=(G2:G4)となります。それらを自動化したいのです。
（aaa） 2019/09/24(火) 12:53

他のアップローダーに画像を上げなおしました。
https://imgur.com/a/gYba7tv
（aaa） 2019/09/24(火) 12:58

失礼。間違えました。
https://imgur.com/a/hd2JMOA
（aaa） 2019/09/24(火) 12:59

元データはいじらずに、というのは理解しました。
となると、関数も複雑になってきます。
店舗などの具体名は出さなくてもよいので、元シートと同じレイアウトを示してください。
上記サンプルでつくっても「元レイアウトと違うので作り直してほしい」というのは無駄になります。

また、みなさんがお願いしているのは、画像URLではなくこの掲示板にレイアウトを示してほしいということです。
渡辺ひかるさんが言っているのは、平均値を出したい最大行数に表の方を揃えて、同じ式で計算してはどうか、ということだろうと思います。
（おそらくaaaさんは「手動処理なのでNG」だろうと思いますが・・・）
（mori） 2019/09/24(火) 13:00

 あ、質問の内容を勘違いしていました。
 Average関数の引数に渡す範囲を、Offset関数で指定するという問題なんですね
　
 リンク先の画像は見てません。見れませんし、見る気もありません。
（´・ω・`） 2019/09/24(火) 13:03

A B C D E F G

都道府県店舗数検索値店舗名 4月 8月 12月
北海道 3 　　 1　北海道A 1033 0.1855 57.7
青森県 1 　　 1 北海道B 905.1 0.1683 132.8
岩手県 2 1 北海道C 1112 0.2072 45.75
山形県 2 2 青森県A 1935 0.1908 133.3
宮城県 5 3 岩手県A 1372 0.2074 65.2
群馬県 2 3 岩手県B 785.1 0.2286 40.9
栃木県 3 4 山形県A 704.9 0.2936 100.7
・
・
・
沖縄県

このようになっております。
（aaa） 2019/09/24(火) 13:14

 mori さん　フォローありがとうございます。おっしゃる通りです

 質問者さん　画像リンク　を張ってもすべての閲覧者が　画像を開けるとは思わないでくださいね
 こちらの環境では、危険なサイト　ということでブロックされました（両方共です）

 >例えば北海道の4月の平均なら、北海道は3店舗なのでFx=AVERAGE(F2:F4)、8月はFx=(G2:G4)となります。それらを自動化したいのです。

 3店舗というデータがあるなら

 Fx=AVERAGE(F2:F4)→Fx==AVERAGE(OFFSET(F2,,,3))

 などではだめですか？

（渡辺ひかる） 2019/09/24(火) 13:15

 掲示されたデータが　A1から始まっているとして

 I2　に　=AVERAGE(OFFSET(INDEX($D$2:$D$8,MATCH(A2& "*",$D$2:$D$8,0)),,1,B2))
 J2　に　=AVERAGE(OFFSET(INDEX($D$2:$D$8,MATCH(A2& "*",$D$2:$D$8,0)),,2,B2))
 K2　に　=AVERAGE(OFFSET(INDEX($D$2:$D$8,MATCH(A2& "*",$D$2:$D$8,0)),,3,B2))

 として　3セルとも下にコピーすれば
 希望の結果になりませんか？
（渡辺ひかる） 2019/09/24(火) 13:26

MATCH関数を使わずに、つまり純粋に店舗数で示した数の行ごとに列E-Gを平均化する方法はあるでしょうか？
（aaa） 2019/09/24(火) 13:50

実際の店舗名には　県名は入っていないということですか・・・

 数式の入力セルは同様

 =AVERAGE(OFFSET($D$2,SUM($B$2:B2)-B2,1,B2))
 =AVERAGE(OFFSET($D$2,SUM($B$2:B2)-B2,2,B2))
 =AVERAGE(OFFSET($D$2,SUM($B$2:B2)-B2,3,B2))

（渡辺ひかる） 2019/09/24(火) 14:02

おお、すばらしい。
完璧です。ご教示ありがとうございました。

（aaa） 2019/09/24(火) 14:30

コメント返信：

[ 一覧(最新更新順) ]

[[20190924113027]] 『不均等間隔の数行ごと平均の出し方』（aaa） ページの最後に飛ぶ

[[20190924113027]]　『不均等間隔の数行ごと平均の出し方』（aaa）　ページの最後に飛ぶ