[[20200513173316]] 『AVERAGEの式を下向きにドラッグすると範囲がズレax(yuuka) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『AVERAGEの式を下向きにドラッグすると範囲がズレる』(yuuka)

A列に縦に1月、2月、3月・・・と月が入力されており、B列に数値が入っているテーブルがあります。

このテーブルの真下(B列)に1月〜3月の平均、4〜6月の平均、7〜9月の平均・・とどんどん入れていきたいと思い、まず、=AVERAGE(B1:B3)と入力後、それを下向きにコピーすると、=AVERAGE(B2:B4)、AVERAGE(B3:B5),AVERAGE(B4:B6)・・・となってしまいます。

目指す形は、AVERAGE(B4:B6)AVERAGE(B7:B9),AVERAGE(B10:B12)・・・なのですが、ドラッグするだけでこのようになる方法はありますでしょうか?

< 使用 Excel:Excel2010、使用 OS:Windows10 >


 >目指す形は、AVERAGE(B4:B6)AVERAGE(B7:B9),AVERAGE(B10:B12)・・・

 この通りの数式にはできないと思います。(たぶん)
 ただ、この数式と同じ結果が出る別の数式はあります。

 =AVERAGE(OFFSET($B$1,ROW(A1)*3-3,0,3,1))

 と入力して下にドラッグしてみてください。
 希望通りの結果になると思います。

(tora) 2020/05/13(水) 19:13


ありがとうございます。
OFFSET関数は起点から指定の行数と列数の位置にある値を返す。
ROW関数は知りたい行番号を返す、ということを今調べて知った上で再質問します。

教示いただいた数式の意味がまだわかりません
?@なぜB1を絶対参照にする必要があるのか?
?AROW(A1)となっていますが、B列に答えを出したいのに、なぜA1なのか?
?BROW(A1)*3-3,0,3,1))で、-3というのはどういう意味か?

(yuuka) 2020/05/14(木) 22:14


 =ROW(A1) で 1|*3で  3|-3で  0|なので|$B$1を起点に0行下のセル |から3行分の範囲|つまりB1:B3  |の平均|
 =ROW(A2) で 2|*3で  6|-3で  3|なので|$B$1を起点に3行下のセル |から3行分の範囲|つまりB4:B6  |の平均|
 =ROW(A3) で 3|*3で  9|-3で  6|なので|$B$1を起点に6行下のセル |から3行分の範囲|つまりB7:B9  |の平均|
 =ROW(A4) で 4|*3で 12|-3で  9|なので|$B$1を起点に9行下のセル |から3行分の範囲|つまりB10:B12|の平均|
 =ROW(A5) で 5|*3で 15|-3で 12|なので|$B$1を起点に12行下のセル|から3行分の範囲|つまりB13:B15|の平均|
 =ROW(A6) で 6|*3で 18|-3で 15|なので|$B$1を起点に15行下のセル|から3行分の範囲|つまりB16:B18|の平均|

 という事だと思います。
 従って実際の起点が「$B$1」じゃなかったら、そこは置き換えないとですね。

 >B列に答えを出したいのに、なぜA1なのか?
 行番号を算出したいだけなので、A列かB列かの違いには特に意味はないかと。

 >OFFSET関数は起点から指定の行数と列数の位置にある値を返す。
 結果的に「値」が返ってくるわけですが、
 OFFSET関数が返すのは「値」ではなく「参照」と解釈した方が、理解し易いかと思います。

 (横から失礼しました)

(白茶) 2020/05/14(木) 23:09


 ほとんど白茶さんが回答されているのでいまさらですけど、
 B1を絶対参照にしているのは、yuukaさんの最初の質問で、

 >まず、=AVERAGE(B1:B3)と入力後、それを下向きにコピーする

 とありましたので、集計の基点はB1としました。
(tora) 2020/05/14(木) 23:23

ありがとうございます.

残る疑問点は、OFFSET関数の仕組みです。

 =AVERAGE(OFFSET($B$1,ROW(A1)*3-3,0,3,1))のうち、最後の3,1が理解できませんでした。

OFFSET(基準,行数,列数,高さ,幅)だそうですが、高さ、幅??とは何の為に設定するのでしょうか?

(yuuka) 2020/05/15(金) 20:44


     {A}     {B}
{1}  名前   金額
{2}    A        10
{3}  B        20
{4}  C        30
{5}    D        40
{6}  E        50

 上の表を使って説明します。
 適当なセル(どこでも)に、 =OFFSET(A1,0,1,3,1)  と入力してみます。
 結果は、#VALUE のエラーになりますね。

 この数式は、OFFSET関数で、
 A1は、最初の基準となるセルです。
 次の 0 は、A1から下に 0 、次の 1 は 右に 1 移動した場所(この場合は B1 )の位置をあらわします。
 さらに、次の 3 は高さ、つまりB1 から下に 3 行、次の 1 は横に 1 列、つまり、B1:B3 の範囲を表します。

 それを確認するために、最初に示した数式のセルを選択して、数式バーのなかで F9 キーを押してみてください。
 数式バーの中に、={"金額";10;20} のように表示され、B1:B3のセル範囲の配列が取得できたことが分かります。

 そこで最初のOFFSET関数をAVERAGE関数の中に入れると、=AVERAGE(OFFSET(A1,0,1,3,1)) となり、B1:B3 の平均値 15 が求められます。

 今回は説明のためOFFSETの範囲に見出しの行も含めましたが、実際にはその範囲は省けばいいでしょう。
 (AVERAGE関数では文字列を無視するので今の例では正しい結果が出ましたが・・)

 関数の中の引数を自分で適当に変えて、どうなるかいろいろ確認してみてください。

(tora) 2020/05/16(土) 10:34


とてもわかりやすく、ありがとうございます!助かりました。

しかし、この数式を自分で考えて組み立てるのは大変難しいですね。
がんばります!ありがとうございました。

(yuuka) 2020/05/16(土) 16:06


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.