今会社で直近6ヶ月の売上平均が出せる表を作ってと言われ、困っています。
例えば、今が3月なので昨年の9月~今年の2月までの売上平均を出すといったものです。これの難しいところは、6ヶ月という期間がこれからも移り変わるところです。つまり、来月になると昨年の10月~今年の3月というふうになるのです。
どうすれば直近6ヶ月間の平均値を出す式ができるのでしょうか?
是非教えてください。

このQ&Aに関連する最新のQ&A

A 回答 (3件)

こんな表が有るとして、


---------------------------------------------------------
| A | B  |  C | D  |  E |・・・
---------------------------------------------------------
1|   | 東京 | 名古屋 | 大阪 | 合計 |
---------------------------------------------------------
2| 8月|    |    |    | (2)  |
---------------------------------------------------------
3| 9月|    |    |    |    |
---------------------------------------------------------
4|10月|    |    |    |    |
---------------------------------------------------------
5|11月|    |    |    |    |
---------------------------------------------------------
6|12月|    |    |    |    |
---------------------------------------------------------
7| 1月|    |    |    |    |
---------------------------------------------------------
8| 平均| (1)  |    |    |    |
---------------------------------------------------------

1.計算式の入力
  (1)へ、=AVERAGE(B2:B7)を入れます。
  (2)は、=SUM(B2:D2)とします。
  (3)各月(8月はいらない)の売上高入力して計算が正しいか確認して下さい。
   (注)平均は小数1位で以下四捨五入されて表示されています。
      表示だけなので小数点以下の数値も持っている為念。
2.マクロを記憶させる
  「ツール」「マクロ」「新しいマクロの記録」でマクロ名に"月のシフト"と
   でもし、OKをクリックします。小さいウィンドが開きますがそのままに
   します。
3.月のシフトをする作業
  1)A3~E7の間をドラッグして反転さた上で、「編集」-「コピー」をクリックする。
  2)A2のセルをクリックして「編集」-「貼り付け」をクリックする。
4.マクロの記録を中止
  先ほどの小さいウィンドにある「記録終了」をクリックすると記録が終了します。
5.2月のデータを入力して保存をする。

では、翌月はどうするかと言うと、2~4で記録させたマクロを実行させると
一ヶ月分ずれてくれますから最後の行を更新するだけで良い訳です。

マクロの実行方法は、「ツール」「マクロ」「マクロ」「月のシフト」を選択
して、実行をクリックすれば、2~4で記録した作業を自動でやってくれます。

マクロを実行すると元に戻す事が出来ません。戻す場合は保存しないでシートを
閉じ、新たにファイルを開いて実行させてください。

この回答への補足

HAL007さん。すみません、わざわざ図まで書いていただいて!
本当にありがとうございます。
でもまだ試していません。
今からためしにやってみます。

補足日時:2001/03/23 22:41
    • good
    • 0

kensakuさんすみません。

勝手に補足します。

例をあげて説明します。
A1:9月の売上、B1:10月....G1:3月といった具合に売上が横に並んでいるとします。
G2に=ave(A1:G1)という数式を入力します。結果は9~3月の平均です。
G2の式をコピーしてE2,F2,...に貼り付けます.
結果は、E2に10~4月平均、F2に11~5月平均、…といった具合に各月の売上の下に
直近6ヶ月の平均が表示されます。

5月になったら4月以前の旧平均を表示したくない。あるいは未来の平均は無意味だから表示したくない。そのような場合は、表示したくない列(=月)を選んで表示_
表示しないを選べば直近6ヶ月だけ見えるようになります。

全ての月の売上は表示したい。未来についても予算をれておきたい。だけど特定の平均(例えば未来の分)のセルだけ見えなくしたいというのであれば、そのセルの書式から文字色を白にしてしまえば見えなくなります.

以上 参考になりましたでしょうか?
    • good
    • 0

9月から2月までの平均値を出したら、その式を右に12回コピーします。

すると、以後毎月における過去半年の平均値が出せます。これをその都度、必要な月を表示させ、他の月は非表示にさせる、ということではいかがでしょうか?
    • good
    • 0
この回答へのお礼

回答ありがとうございます。こんなに早く帰ってくるとは思っていませんでした。
でもエクセル初めてまだ日が浅いこともあって、kensakuさんがせっかく教えてくれのに、いまいちわかりません。「12回コピー」「非表示」などがわかりません。
あとぜいたくをいうようですが、その都度何かする、ということがないほうがありがたいです。わがまま言って申し訳ないです・・・・。
もしよろしければまたご指導よろしくお願いします。

お礼日時:2001/03/18 09:58

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで直近1ヶ月と3ヶ月の平均を求める

1  2015/1/5 10   直近1ヶ月の月平均
2  2015/1/11 7    直近3ヶ月の月平均
3  2015/2/7 1
4  2015/2/24 21
5  2015/1/7 9
6  2014/12/23 13

上記のようなデータがあります。
C1セルに直近1ヶ月の月平均
C2セルに直近3ヶ月の月平均
を表示させたいのです。

良い方法はないでしょうか?

Aベストアンサー

No.1です。

>①は、22
>②は、20.3・・・

というコトですので、AVERAGEIFSではなくSUMIFS関数で大丈夫だと思います。
配置は前回と同じだとすると

E1セルに
=SUMIFS(B:B,A:A,">"&EDATE(TODAY(),-1),A:A,"<="&TODAY())
E2セルに
=SUMIFS(B:B,A:A,">"&EDATE(TODAY(),-3),A:A,"<="&TODAY())/3

という数式を入れてみてください。m(_ _)m

Q月ごとに自動で平均を出したい

vistaでエクセル2007を使用しています
たとえば縦軸に
9月
1日 33 44 55
2日 63 24 75
3日 43 64 35


とあります
最終行に月平均を出しているのですが
毎月30日の時はシートの平均を
AVERAGE(D4:D33)で出して
31日の時はD33を34にしたり
また翌月は33に変更したりしているのですが
これを自動で30日の時、あるいは10月なら30日とか
計算してくれるようにすることは
可能でしょうか?
入力データから
毎月複数のシートを変更しなくてはならず
面倒といったら面倒なんです。
30日用のデータと31日用のデータに分けて作って
月によって変更しようかとも
思ったのですが毎月些細な変更があり
そうもできないのです

出来ないのでしたらしょうがないのですが
なんとかうまくできる方法があれば
お教え願えますでしょうか

Aベストアンサー

回答No4です。
A4セルへの入力の式は次のようにしてください。
=IF(MONTH(DATE(YEAR($A$1),MONTH($A$1),ROW(A1)))<>MONTH($A$1),"",DATE(YEAR($A$1),MONTH($A$1),ROW(A1)))
<>の前に)が一つ足りませんでした。大変失礼いたしまいた。
式の意味はA1セルのデータを使って年や月は決めていますが日にちは1日から始まるのでROW(A1)としてA4セルでは1となります。それが下の行に移るにつれて、例えばROW(A32)となった場合には自動的に月の値が次の月に変わって日にちも変わることになります。その月がA1セルの月と変わらない間は日付を表示していきますが、A1セルの日付の月とROW関数を入れた日付の月が違う(<>)場合にはセルを空白にしなさいということになります。
A1セルに2009/10/1と入力すれば10月31日までは10月で表示されますが11月になれば空白のセルにすることになります。

Q前年比の%の計算式を教えてください

例えば前年比115%とかよくいいますよね?

その計算の仕方が分かりません・・・
例えば 前年度の売り上げ2.301.452円
    今年度の売り上げ2.756.553円
の場合前年比何%アップになるのでしょうか?計算式とその答えを
解りやすく教えて下さい・・・
バカな質問でゴメンなさい(><)

よろしくお願いします。

Aベストアンサー

 
(今年の売上÷前年の売上×100)-100=19.8%の売上増加

 

Q勤続年数の平均を求めたい時の関数

エクセルで会社の従業員の勤続年数とその平均を求めたいです。
勤続年数のセルに
=DATEDIF(A1,TODAY(),"y")&"年"&DATEDIF(A1,TODAY(),"ym")&"ケ月"
といれて勤続年数を出してます。
それで次に従業員の勤続年数の平均を出したいのですが、AVERAGEを使うとエラー#DIV/0!が出ます。
どうすれば勤続年数とその平均が求められますか?
どんな方法でも良いので教えて下さい!!非常に困ってます(ー_ー)!!

Aベストアンサー

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で、出てくるのは勤続延べ何ヶ月ですから、こいつを12で割って「年」
余りを「月」にする必要があります。平均月が出たセルをB1として、

="平均"&INT(B1/12)&"年"&(B1-INT(B1/12)*12)&"ケ月"

こんな感じで表示できるはずです。
キモは「計算に使うセルと表示するセルを分ける」ということです。

計算に使うセルが表示上邪魔な場合は「書式」「列」「表示しない」
などで表示上隠す方法と、AA1~などの極端に離れたセルに記入して
おく方法などがあります。

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で...続きを読む

Qパーセンテージの平均の出し方は?

1月:90%
2月:90%
3月:86%

1月~3月までの平均のパーセンテージは?
という時に、(90+90+86)÷3
という計算方法が間違いである理由がどうしてもわからないのですが、わかりやすく教えていただけませんか?

宜しくお願いいたします。

Aベストアンサー

はじめまして。
単純に割合の平均は求めてはいけません。
割合は全体に対するものです。
例えば
1月
本が100冊売れた。 漫画本はその内50冊。
(漫画本の割合は50%ですよね)
2月
本が10冊売れた。 漫画本はその内10冊。
(漫画本の割合は100%ですよね)
3月
本が1000冊売れた。 漫画本はその内0冊。
(漫画本の割合は0%ですよね)

さて、1・2・3月トータルで漫画本の割合は
(50+100+0)/3=50でいいのでしょうか?
本当にトータルで50%も漫画本は売れたでしょうか?
本当は
1110冊売れて、内60冊が漫画本ですから、
60/1110*100=約5.4%になります

ですから、割合を出すときは
とにかく分母は全体です。
(この場合1~3月の全体が分母になります)


似た問題でAからBまで150kmを1時間、
BからCまで150kmは2時間
かかりました。
問題1.AからBの時速は
  2.BからCの時速は
  3.AからCの時速は

ここで3を求めるとき同じ距離だから、
1と2の平均を出す人がいるのですが・・・・
答えは300kmを3時間なので・・・・。
と言うことになり、単純に平均を取ればよいと言う問題ではないのです。

はじめまして。
単純に割合の平均は求めてはいけません。
割合は全体に対するものです。
例えば
1月
本が100冊売れた。 漫画本はその内50冊。
(漫画本の割合は50%ですよね)
2月
本が10冊売れた。 漫画本はその内10冊。
(漫画本の割合は100%ですよね)
3月
本が1000冊売れた。 漫画本はその内0冊。
(漫画本の割合は0%ですよね)

さて、1・2・3月トータルで漫画本の割合は
(50+100+0)/3=50でいいのでしょうか?
本当にトータルで50%も漫画...続きを読む

QEXCELで別のシートのデータを参照して返す方法

ちょっとしたことなのですが、うまく式が書けなくて悩んでいます。

作業用のシート1と参照用のシート2があり、
シート1のA列には
 あおき
 あべ
 いぐち
 いはら
などと文字列(名前)が入力されています。
こちらは今度の野球のスタメンだと思ってください。

シート2にはA列とB列があって、
わたなべ 55
いぐち  43
あおやま 67
いはら  41
などと、名前:背番号が羅列されています。こちらが参照用の全選手の背番号リストだと思ってください。

ここで、 シート1のB列に、A列の名前に対応した背番号を返したいのです。
なので、シート1のB列に
IF(シート1のA列の値=シート2のA列のいずれかの値)だったとき、
適合したシート2の行のB列の値を表示する
という式を入れたいのですが、どのように式を書けばよいでしょうか。

すみませんがどなたか教えてください。

Aベストアンサー

私もその場合はVLOOKUP関数を使用しています。

「B1」=VLOOKUP(A1,Sheet2!$A:$B,2,0)

Qエクセル 直近入力データの平均・合計

セルA5からA6 A7…と下方向にデータを入力しています。A1に直近5つのデータの平均、A2に直近5つのデータの合計を表示させたいです。例えばA1~A100までデータを入力したとすると、直近5つのデータ平均とはA96~A100の平均ということです。どんどん下方向にデータを入力していくのですが、A1に直近5つのデータの平均、A2に直近5つのデータの合計を表示させたいです。これを実行させるマクロ、関数式を知りたいです。関数式でできるのかはわかりませんが・・・。よろしくお願いします。

Aベストアンサー

(#2コメントへのレスです)

#5:vizzarさんが書いてらっしゃいますね。
『関数で出来るものをわざわざマクロで実現するのはお勧めしません。』
私もそう思います。
不具合があった時に自分でメンテできなければ実用化は難しいと考えてください。

VBAの場合、一般機能でできない事をできるようになるという事ではなく、
一般機能や手作業では煩雑な手続きを自動化できる、程度に
捉えておいたほうが良いと思います。
#知っていれば便利なのは間違いないです。がんばってください^ ^

以下2例。今後の参考になれば幸いです。
'----------------------------------------------------------
いわゆる[ユーザー定義関数]です。標準モジュールに置いて使います。
Application.Volatileを使っているので、
多用すると一般数式より重くなるかもしれません。
'----------------------------------------------------------
Option Explicit

Function SUMAVE(SR As Range, _
        Optional flg As Boolean = True, _
        Optional n As Long = 5)
  'SUM計算は、セルに =SUMAVE(起点セルを指定) と入力。
  'AVERAGEは =SUMAVE(起点セルを指定,0)と入力。
  '第3引数で計算範囲を指定できる。指定なしは5
  
  Dim TR As Range
  Dim x
  
  Application.Volatile
  Set TR = Cells(Rows.Count, SR.Column).End(xlUp)
  If TR.Row < SR.Row Then
    Set TR = SR
  Else
    If n > TR.Row Then n = TR.Row
    Set TR = Intersect(Range(SR, TR), _
              Range(TR.Offset(-n + 1), TR))
  End If
  If flg Then
    x = Application.Sum(TR)
  Else
    x = Application.Average(TR)
  End If
  SUMAVE = x
  Set TR = Nothing
End Function
'----------------------------------------------------------
Worksheetの値を変更する度に実行するイベントプロシージャです。
目的シートのシートモジュールに置いて使います。
改造して、任意に実行するようにすれば少し簡単になります。
'----------------------------------------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  'これはWorksheetイベントプロシージャなので _
   目的シート右クリック[コードの表示]のコードペインに置く。
  
  Const rn As Long = 5 '起点セルの行
  Const cn As Long = 1 '起点セルの列
  Const n As Long = 5 '計算範囲
  Dim TR As Range
  Dim nn As Long
  
  If Target.Column <> cn Then Exit Sub
  Set TR = Cells(Rows.Count, cn).End(xlUp)
  If TR.Row < rn Then
    Set TR = Cells(rn, cn)
  Else
    nn = IIf(n > TR.Row, TR.Row, n)
    Set TR = Intersect(Range(Cells(rn, cn), TR), _
              Range(TR.Offset(-nn + 1), TR))
  End If
  With Application
    .EnableEvents = False
    .Calculation = xlCalculationManual
    Cells(1, cn).Value = .Average(TR)
    Cells(2, cn).Value = .Sum(TR)
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
  End With
  Set TR = Nothing
End Sub
'----------------------------------------------------------

(#2コメントへのレスです)

#5:vizzarさんが書いてらっしゃいますね。
『関数で出来るものをわざわざマクロで実現するのはお勧めしません。』
私もそう思います。
不具合があった時に自分でメンテできなければ実用化は難しいと考えてください。

VBAの場合、一般機能でできない事をできるようになるという事ではなく、
一般機能や手作業では煩雑な手続きを自動化できる、程度に
捉えておいたほうが良いと思います。
#知っていれば便利なのは間違いないです。がんばってください^ ^

以下2例。今後...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcelで日別に入力されたデータを月別に集計するにはどうしたらいいのでしょうか。

Excelで月別の集計をしたいのですが
例えば
  日付    数量
10月25日  200
10月27日  150
10月30日  120
11月 1日  200
11月 3日  150
のように、日付が飛び飛びで連続していなくい日々の数量を、上の行から順番に入力していった場合に(月が替わる行が決まっていない)月別の集計を取る方法はないでしょうか。


 

Aベストアンサー

どのように集計する月を指定するのかが書かれていませんが、例えば11月分の合計を出したいなら

1)11月までの合計から10月までの合計を引く方法
 =SUMIF(A:A,"<=11/30",B:B)-SUMIF(A:A,"<=10/31",B:B)

2)月が「11」のB列を合計する方法
 =SUMPRODUCT((MONTH(A2:A100)=11)*(B2:B100))

なども考えられます。

でもエクセルなら一番オーソドックスな方法はピボットテーブルでしょう。C列に
 =TEXT(A1,"YYYY/MM")
で「2008/11」のような文字列を生成しておいてピボットテーブルで集計すれば、シート上にある全ての月の合計が一発で求められます。
ピボットテーブルの使い方はGoogleなどで検索したらたくさん見つかります
http://www.viplt.ne.jp/tomy/pibot.html

Qエクセル-過去6カ月分の合計を関数で計算したい

エクセルで下記のようなテーブルがあります。
4/1 500
4/1 300
4/2 600
.
.
12/31 1000


日付ごとに数字が入っています。1日に1つの数字とは限りません。
これを今日遡って半年分だけ合計した数を、表示したいのですがどのような関数を書いたらよいでしょうか。

ファイルはどのタイミングで誰が開くかわからないため、常に今日から半年分(180日分)合計したいと思っています。

よろしくお願いいたします。

Aベストアンサー

No.2です。

私は未来日付のデータがあるとは想定していませんでしたが
もし存在するならNo.3さんの回答のように
今日の日付より後の分を引いてやらなければいけません。
私がNo.2で書いた式はあくまで
「A列の日付が今日の日付の180日前の日付より後になっている行の
B列の数値を集計する」という式ですから。
なお、質問は「今日を含めて180日分」ということでしたので
「ちょうど180日前の日付」は含めないようにしています。
今日を含めて3日分、と言う場合、2日前・1日前・今日で
3日分になりますよね。それと同じ考え方です。

余談ですが、No.3さんの回答の
>DATE(YEAR(TODAY()),MONTH(TODAY()-6),DAY(TODAY()))
の部分は
DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
の間違いではないかと…


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング