- ベストアンサー
エクセル 直近入力データの平均・合計
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
(#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 '----------------------------------------------------------
その他の回答 (5)
> すいませんができたらマクロでの方法(コード)も教えていただきたいのですが・・・ 多分、参考の為にお知りになりたいのだとは思いますが、 関数で出来るものをわざわざマクロで実現するのはお勧めしません。 1.何でも安易にマクロに走るクセがつく。 2.職場などでは、マクロのメンテや同僚からの要望に忙殺されてしまう。 3.マクロ入りファイルを開くと表示される「ウイルス云々」の警告メッセージに過剰反応する人が必ずいる。 余計なお世話かと思いますが、老婆心ながら...
お礼
アドバイスありがとうございます。マクロの勉強はしているのですが、なかなか上達しないもので・・。
- imogasi
- ベストアンサー率27% (4737/17068)
この質問はイメージするのは簡単ですが、関数としては、超難問でしょう。 取り合えず、配列数式を織り込んで 例データ A2:A9 1 2 3 4 5 6 8 1 最下行は(質問には無いが) =MAX((A1:A100<>"")*(ROW(A1:A100))) と入れてSHIFT+CTRL+ENTER(3つのキーを同時に押す) 結果 9 ーー 5セルの合計は =SUM(OFFSET(A1,MAX((A1:A100<>"")*(ROW(A1:A100)))-5,0,5,1)) といえてSHIFT+CTRL+ENTER(3つのキーを同時に押す) 結果 24 平均はSUMをAVERAGEに変えるとでる。 入力最下行をA100までを対象とした式です。 ーーーー 他の方法 最下行を求める式が、思いつきにくいが、VBAを使う方なら、下記は 良く使うので ユーザー関数 Function lr(a) lr = Range(a).End(xlUp).Row End Function を標準モジュールに作り =SUM(OFFSET(A1,lr("A100")-5,0,5,1)) とするとやや簡単になり、配列数式で無くなる。 平均もSUMをAVERAGEで置き換えると良い。
お礼
関数・マクロと教えていただきありがとうございます。 いろいろなテクニックがあり非常に参考になります。
データ入力範囲が最大で A5:A1000 と仮定すると、 A1: =AVERAGE(OFFSET(INDIRECT("A"&(4+COUNT(A5:A1000))),,,-5)) A2: =SUM(OFFSET(INDIRECT("A"&(4+COUNT(A5:A1000))),,,-5))
お礼
こういう関数式でもできるのですね。 参考になりました、ありがとうございます。
- pauNed
- ベストアンサー率74% (129/173)
ぁ。失礼m(_ _)m 『セルA5から』なので =AVERAGE(OFFSET(A4,COUNTA(A5:A65536),,-5)) =SUM(OFFSET(A4,COUNTA(A5:A65536),,-5))
補足
レスありがとうございます。 関数でできたのですね。 すいませんができたらマクロでの方法(コード)も教えていただきたいのですが・・・。図々しくてすいませんが、よろしくお願いします。
- pauNed
- ベストアンサー率74% (129/173)
こんにちは。 A1に=AVERAGE(OFFSET(A2,COUNTA(A3:A65536),,-5)) A2に=SUM(OFFSET(A2,COUNTA(A3:A65536),,-5)) とか。 途中未入力セルがない事が前提ですが。 それに、最下行がA7以上でないとだめです^ ^;
関連するQ&A
- エクセル 直近入力データの最大値
セルE1からF1 G1…と右方向にデータを入力しています。A1に直近5つのデータの最大値を表示させたいです。例えばE1~Z1までデータを入力したとすると、直近5つ最大値とはV1~Z1の最大値ということです。どんどん右方向にデータを入力していくのですが、A1に直近5つのデータの最大値を表示させたいです。これを実行させる関数式を知りたいです。関数式でできるのかはわかりませんが・・・。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルのデータの合計
教えてください。 sheet1のセル、A1~A5のデータの合計を、sheet2のセルA1にSUM関数を使って、表示させてます。 次に、sheet1のセルA6~A10に入力されているデータの 合計をsheet2のセルA2に表示させたいのですが、 この際、sheet2のセルA1から、計算式を下にコピーすると、 sheet1のセルA2~A6の合計データが表示されます。 sheet1の5つのセルの各合計を、sheet2のセルに順番にコピーを使って表示させるには、どうしたらいいのでしょうか? うまく質問できていないかもしれませんが、 教えてください。
- 締切済み
- オフィス系ソフト
- 入力されるまで合計セルを空白にしたいのですが
A5:A120にデータ入力し、A121に「Sum」で合計を だしています。B列からM列も同様になっています。 列のデータは1~0の数字だけです。 当該列のセルが未入力だと当然合計欄は「0」に表示 されています。 この場合、「0」が目障りなので、いずれかのセルにデータ が入力されない限り、空白表示にしておきたいのです。 どんな関数をどう使えば良いのでしょうか?
- 締切済み
- その他(Windows)
- エクセル2000 合計するセルの個数を任意に変更
2つの疑問が生じました。ご存知の方がいらっしゃいましたら、お時間の許す方にご回答をお願いします。 【1】 B1からB1000、C1からC1000までにデータが入力されているとします。 A1に3を入力するとD1にB1以下3個のセルの合計 A1に4を入力するとD1にB1以下4個のセルの合計 ・・・・・・・・・・ A1にxを入力するとD1にB1以下x個のセルの合計を表示したいのですが、このときのD1の関数式。 【2】 A1に3を入力するとE1に〔B1以下3個のセル〕と〔C1以下3個のセル〕の合計 A1に4を入力するとE1に〔B1以下4個のセル〕と〔C1以下4個のセル〕の合計 ・・・・・・・・・・・・ A1にxを入力するとE1に〔B1以下x個のセル〕と〔C1以下x個のセル〕の合計を表示したいのですが、このときのE1の関数式。 以上似たような質問なのですが、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセルのマクロ 並んだ数字の幾つかを抜き出してその合計を計算させたいのですが・・
エクセルのマクロ(VBA)について、教えてください。 セルのA1からA2・・・A1000までに「01,03,06,08,20,」のように5つの数字がカンマで区切られて入力されています。 1.この5つの数字をb.c.d.e.f.の1から1000のそれぞれのセルに配置するマクロ 2.この5つの数字の数字の合計をb1から1000のセルに表示させるマクロは難しいでしょうか? MID関数をひとつづつのセルに入れていたのですが、マクロでできれば効率が良いと思い手引書等で考えたのですがよく分からないのです。
- ベストアンサー
- その他(プログラミング・開発)
- 入力した合計数値を振り分けたい
関数とかマクロなど理解していない初心者でエクセル2003を使用しいます。 例えば 1Aのセルに1~10個(単価50円)と入力済 2Aのセルに11~20個(単価40円)と入力済 3Aのセルに21~30個(単価30円)と入力済 1B~3Bのセルに個数欄未入力 1C~3Cのセルに金額自動計算式入力済 4Bのセルに合計個数欄未入力 4Cのセルに合計金額自動計算式入力済 とした場合、例えば4Bの合計個数に29個と入力すると、自動的に1Bに10個、2Bに10個、3Bに9個と個数が振り分けられる様にしたいのですが、できるのでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセルの合計と平均
すみません、エクセルの合計/平均に関する質問です。 例えば、1月から12月までの売り上げの平均値をA2からL2に入力し、M2に1月から12月までの合計の平均を出したいと思います。 ですが、M2に=AVERAGE(A2:L2)と入力すると平均値の平均が出てしまいます。 1月~12月の合計÷365をM2に出したい場合どのようにしたら良いでしょうか。 また、例えば、7月まで入力されている場合、8月、9月の平均を入力していったら、自動的にM2に9月までの平均を出すにはどのようにしたら良いでしょうか。 (例:2月まで入力されている場合。A1、B1には1月の平均、2月の平均が入力されている。M2には、1月の平均×31+2月の平均×28÷59の計算が出るようににしたい) 分かりにくくて申し訳ございませんが、宜しくお願い致します。
- 締切済み
- オフィス系ソフト
- オートフィルタ後の直近Nの合計
Excelでオートフィルタされたあとの、直近のデータNコの合計を右列(図でD列)に出したいのです。 毎日出力されたデータ(C列)が届き、そのデータはカテゴリ(B列)が決まっています。ここではAかBとします。 カテゴリは毎日ユニークに分類するので、AかBのどちらか(同じ日にAとBの混在はない)。 やりたいことは、ExcelのD列に、次の数字を表示したい ・最新の日のカテゴリがAだった場合、直近NコのAのデータの合計を ・最新の日のカテゴリがBだった場合、直近NコのBのデータの合計を NはG1に変数として入力。ただし1~9の整数のみ。 B列に現れるカテゴリは、同じものは通常30日以内に最大9は必ず現れる。 (B列を過去31[=本日+過去30日]検索すれば、Nが最大の9でも必ずサンプリングできる) D列のセルには、どのような計算式を入れて下へオートフィルすればよいでしょうか。 SUBTOTAL関数だと過去Nコの指定ができないようです。 Excelは2000または2002です。
- ベストアンサー
- その他MS Office製品
- 【Excel】決まった行数ごとの合計と平均値
費目別に合計と平均値をだしたいのですが、合計と平均値の算出結果を表1のように費目と費目の間に挟むと、費目数が多い場合大変です。 またひとつの月ごとに一度、費目に対する合計と平均値がださればよいので、日々の入力は不要です。 そのため表2のようにまとめたいのですが、離れたセルの合計数を出す場合はオートサムを使えばよいらしいですが、決まった行数ごとの合計と平均値を特定の場所(つまり表2)に表記させる関数式(に問いませんが)を教えてください。
- ベストアンサー
- Excel(エクセル)
- Excelで選択範囲の合計をマクロで
Excel2003を使用しています。 ステータスバーに、選択範囲の合計、平均、データの個数などが 表示されますが、同じ機能をマクロを使って、 固定のセルに表示できないでしょうか? 例えば、任意のセルを選択すると、 A1に選択範囲の合計、A2に選択範囲の平均が自動で表示される というような機能です。 Excel2007では、同様の機能がステータスバーにあるようなのですが。 よろしくお願い申し上げます。
- ベストアンサー
- オフィス系ソフト
お礼
度重なる質問にレスしていただき、ありがとうございます。 マクロについては、標準モジュール・シートモジュールと2パターン提示していただき非常に参考になりました。 私もいつかここで回答者になれるよう、マクロの勉強に精進します・・・。