Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1532 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【73781】マクロでの平均値計算について shouw 13/2/14(木) 10:50 質問[未読]
【73787】Re:マクロでの平均値計算について kanabun 13/2/15(金) 9:12 発言[未読]
【73810】Re:マクロでの平均値計算について shouw 13/2/17(日) 10:28 質問[未読]
【73811】Re:マクロでの平均値計算について kanabun 13/2/17(日) 10:44 発言[未読]
【73813】Re:マクロでの平均値計算について shouw 13/2/17(日) 12:10 質問[未読]
【73814】Re:マクロでの平均値計算について kanabun 13/2/17(日) 12:36 発言[未読]
【73815】Re:マクロでの平均値計算について shouw 13/2/17(日) 12:44 お礼[未読]
【73816】Re:マクロでの平均値計算について kanabun 13/2/17(日) 12:53 発言[未読]
【73817】Re:マクロでの平均値計算について shouw 13/2/17(日) 14:26 お礼[未読]
【73788】Re:マクロでの平均値計算について 佳 13/2/15(金) 9:32 回答[未読]
【73781】マクロでの平均値計算について
質問  shouw  - 13/2/14(木) 10:50 -
引用なし
パスワード
   いつもこちらのサイトをいろいろ参考にさせて頂きお世話になっております。

平均値の計算についての質問ですが、あるデータベース上の数値の平均をだしたいと考えております。

そのデータベースの値はマクロで更新ボタンを作り、ボタンを押すことによって他のシートの記録用紙から必要な数値のみの最新のデータが一行挿入され記録されるように組んであります。（例：2月13日の数値がA1：G1ある。本日のデータを更新する際はA1：G1に2月14日分の数値が入り、2月13日分はA2:G2になる。）

そこでA列の平均値を出す際に、通常の計算式で組むと更新ボタンを押し一行挿入されると平均値の計算式も一行下がってしまい、最新のデータを含む平均値の自動計算ができません。

どなたかどのようにすればよいか教えて頂けますか。

よろしくお願い致します。
【73787】Re:マクロでの平均値計算について
発言  kanabun  - 13/2/15(金) 9:12 -
引用なし
パスワード
   ▼shouw さん：こんにちは

>そこでA列の平均値を出す際に、通常の計算式で組むと更新ボタンを押し一行挿入されると平均値の計算式も一行下がってしまい、最新のデータを含む平均値の自動計算ができません。

平均値の式が書いてあるセルはどこですか？
仮にそのセルの名前が「平均値A」と定義してあるとすると、
当該シートのモジュールに
こんなんでどうですか？

'------------------- シートのコード ---------------

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r As Range
　　If Target.Count = 1 Then Exit Sub
　　Set r = Intersect(Target, Rows(1))　
　　If r Is Nothing Then Exit Sub
　　
　　Application.EnableEvents = False
　　Set r = Range("A1", Cells(Rows.Count, 1).End(xlUp))
　　Range("平均値A").Formula = "=Average(" & r.Address & ")"
　　Application.EnableEvents = True
End Sub

※ Range("平均値A") のところをそちらの実際のセルに変更してから
試してみてください。
【73788】Re:マクロでの平均値計算について
回答  佳  - 13/2/15(金) 9:32 -
引用なし
パスワード
   こんにちは。

そのボタンのコードを、現在の「一行挿入」から
「数式削除→一行挿入→数式入力」に変更すればいいかと思います。
平均を出す数式がH1セルに入っているとして、こんな感じで
　Dim myFormula As String
　myFormula = Range("H1").Formula
　Range("H1").ClearContents
　'一行挿入のコード
　Range("H1").Formula = myFormula


▼shouw さん：
>いつもこちらのサイトをいろいろ参考にさせて頂きお世話になっております。
>
>平均値の計算についての質問ですが、あるデータベース上の数値の平均をだしたいと考えております。
>
>そのデータベースの値はマクロで更新ボタンを作り、ボタンを押すことによって他のシートの記録用紙から必要な数値のみの最新のデータが一行挿入され記録されるように組んであります。（例：2月13日の数値がA1：G1ある。本日のデータを更新する際はA1：G1に2月14日分の数値が入り、2月13日分はA2:G2になる。）
>
>そこでA列の平均値を出す際に、通常の計算式で組むと更新ボタンを押し一行挿入されると平均値の計算式も一行下がってしまい、最新のデータを含む平均値の自動計算ができません。
>
>どなたかどのようにすればよいか教えて頂けますか。
>
>よろしくお願い致します。
【73810】Re:マクロでの平均値計算について
質問  shouw  - 13/2/17(日) 10:28 -
引用なし
パスワード
   ▼kanabun さん：

丁寧なご回答ありがとうございます。

参考にさせて頂きます。

そこで質問なのですが、B6〜B200までの平均の答えをB4セルに出すようにしたいのですが、どのように変更すればよろしいでしょうか？

大変申し訳ありませんがご指導願います。
【73811】Re:マクロでの平均値計算について
発言  kanabun  - 13/2/17(日) 10:44 -
引用なし
パスワード
   ▼shouw さん：こんにちは〜

>そこで質問なのですが、B6〜B200までの平均の答えをB4セルに出す
> ようにしたいのですが、どのように変更すればよろしいでしょうか？

セル範囲の関係がよく分かりません。
1行目が挿入されて更新されるんですよね？
そしたら、[B4]セルは 新しいデータで上書きされることになりませんか？
【73813】Re:マクロでの平均値計算について
質問  shouw  - 13/2/17(日) 12:10 -
引用なし
パスワード
   ▼kanabun さん：

説明不足で申し訳ありません。

B6セル以降がデータが入るようになっており、行挿入及び最新データはB6セルに入るようになっています。

B6〜B200と表現してしまいましたが、B7以降は最新前のデータが蓄積されるようになっております。ですので、最新のデータを本日分とするとボタンクリックで昨日以降のデータがB7以降にずれ、B6に新たなデータとして本日分、B7に昨日分、B8は昨々日分・・・となります。
その新たに挿入された最新データ及び今までのデータの平均値をB4セル内に表示するようにしたいのです。

ご指導よろしくお願い致します。
【73814】Re:マクロでの平均値計算について
発言  kanabun  - 13/2/17(日) 12:36 -
引用なし
パスワード
   ▼shouw さん：

>
>B6セル以降がデータが入るようになっており、行挿入及び最新データはB6セルに入るようになっています。

>その新たに挿入された最新データ及び今までのデータの平均値をB4セル内に表示するようにしたいのです。

なるほど、それなら分かります。
[B4]セルは固定なのですね

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r As Range
　　If Target.Count = 1 Then Exit Sub
　　Set r = Intersect(Target, Rows(6)) '6行目が挿入されたら
　　If r Is Nothing Then Exit Sub
　　
　　Application.EnableEvents = False
　　Set r = Range("B6", Cells(Rows.Count, "B").End(xlUp))
　　Range("B4").Formula = "=Average(" & r.Address & ")"
　　Application.EnableEvents = True
End Sub
【73815】Re:マクロでの平均値計算について
お礼  shouw  - 13/2/17(日) 12:44 -
引用なし
パスワード
   ▼kanabun さん：

できました！！！！

本当にありがとうございます。

ものすごく感動しました。

最後にひとつだけ教えてください。

最高、最低をだすのはこのマクロのAVERAGEを「MAX」、「MIN」にするだけでよろしいでしょうか？？

よろしくお願い致します。
【73816】Re:マクロでの平均値計算について
発言  kanabun  - 13/2/17(日) 12:53 -
引用なし
パスワード
   ▼shouw さん：

>最後にひとつだけ教えてください。
>
>最高、最低をだすのはこのマクロのAVERAGEを「MAX」、「MIN」にするだけでよろしいでしょうか？？

ハイ、それでいいと思います。
Try It!
【73817】Re:マクロでの平均値計算について
お礼  shouw  - 13/2/17(日) 14:26 -
引用なし
パスワード
   ▼kanabun さん：

丁寧に教えて頂き、ありがとうございました。

また、壁にぶち当たったらお教授願います。

度々の回答、本当にありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1532 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free