Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(その他 : その他)
不確定なセルの平均を求める
投稿日時: 22/03/24 20:39:16
投稿者: PICKY_LICKY_123

2010です。
アクセスのクロス集計クエリをエクセルに出力しています。
年で集計されているので2009から、現在は2022までセルがあるのですが、来年には2023までと順次列は増えていきます。その列の直近5年の平均値を求めるセルをエクセルを起動する時に自動で差し込みたいのですがどうしたら良いでしょうか?

回答
投稿日時: 22/03/24 22:12:25
投稿者: WinArrow
投稿者のウェブサイトに移動

コード作成依頼は、禁止されています。
↓参照してください。
https://www.moug.net/faq/kiyaku.html
 
 
ヒントだけ
機能を分けましょう。
  
Accessからデータを取り出し、Excelシートに貼りつける。
クエリはできているようですが、それをマクロの中で実行することになります。
マクロの自動記録でコード作成できるかも?
 
列の最後(最右端)を取得する。
平均値を計算する数式をセルに書きこむ。
  
といった、個別のマクロを作成(マクロの記録でコード作成できるものあり)します。
  
出来上がったマウロを、ブックオープン時に実行するマクロを作成する。
  
なお、
>エクセルを起動する時
ではなく、
当該ブックを開くとき
と、考えましょう。
 
ところで、できあがった表は、印刷しなくてよいのでしょうか?
また、ブックを保存しなくてよいのでしょうか?
毎回、新しくブックを作成するのか?
既存のブックに上書きするのか?
そこまでのストーリーを作成(仕様)しましょう。
 
 
 

投稿日時: 22/03/24 22:51:42
投稿者: PICKY_LICKY_123

ありがとうございます。
 
考えてみると今アクセスでエクセルを吐き出す仕組みは、コマンドボタンをクリックするたびにTRANSFERSPREDSHEETを使用して、「資料YYMMDDHHSSMM.xlsx」というブックを吐き出すというものです。
 
これだと毎回新しいブックができることになるので、エクセル側でマクロを入れることは無理ですよね。
 
そうするとアクセスで吐き出す時にエクセルに仕掛けをするか、まだは、何か違う方法でエクセルを吐き出すしかないのでしょうか?
 
エクセル側に教えていただいたようなマクロを組むとしたら、アクセスからは毎回同じエクセルの中身を置き換える必要が有るということですよね?
 
認識が間違っているでしょうか?

回答
投稿日時: 22/03/25 09:20:49
投稿者: WinArrow
投稿者のウェブサイトに移動

既にExcelブックが作成されているとしたら、
作成されているブックに平均数式を追加する(手作業で)だけでは、ダメでしょうか?
  
列の最右列は、任意のセルにカーソルを置き、
[ctrl]+[→]
で最後のセルが選択されます。
その右セルに↓のような数式で、直近5個の平均が計算可能です。
=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),4)),0,-1,1,-5))
あとは、下へフィルドラッグすれば、数式が複写されます。
 

回答
投稿日時: 22/03/25 20:17:58
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
エクセルを起動する時に自動で

を「資料YYMMDDHHSSMM.xlsx」を開くときと考えると、多分無理です。
理由
このブックはマクロブックではありませんから、このブックを開くときにマクロを動かすことはできません。
都度、異なるファイルが作成されることなので、マクロを仕込むタイミングが無いと思います。
 
対応策
案(1)一番簡単なのは、前レスの通り、手作業で、平均を求める数式を入力する方法。
  前レスで紹介した数式は、どのブックでも共通で使えます。
 
案(2)前レスの数式を代入するだけのマクロブックを作成する方法。
 そのマクロブックで、「資料YYMMDDHHSSMM.xlsx」を選択するダイアログを作成するようにします。
 数式を入力したら、上書き保存します。
 マクロは、これ一つで足りると思います。
 
案(3)AccessVBAに(2)の処理を組み込む方法
 
案(4)Excelで、ADOを使って、TranseFerSQLを実行〜数式代入までの処理を組み込む方法。
 
VBAで対応するとしたら、案(3)がお勧めです。
 

投稿日時: 22/03/25 22:20:09
投稿者: PICKY_LICKY_123

案3について、質問ですが、素人なのでおっしゃっていることが理解できているか確認させてください。
一旦アクセスで「資料YYMMDDHHSSMM.xlsx」を作成して、この後それを開いて式を入れ込むマクロを作るということですか?
アクセスVBAで.エクセルを開いて、式を入れ込むのもアクセス側でするってことでしょうか?
 
その認識であっているのなら、調べてみてやってみます。

回答
投稿日時: 22/03/25 22:45:30
投稿者: よろずや

ACCESS VBA で EXCEL を起動して
ブックを作り
ADO でテーブルを開き
シートに張り付けて
計算式も書き込む
 
https://tsware.jp/tips/tips_686.htm

回答
投稿日時: 22/03/26 09:42:14
投稿者: WinArrow
投稿者のウェブサイトに移動

PICKY_LICKY_123 さんの引用:

案3について、質問ですが、素人なのでおっしゃっていることが理解できているか確認させてください。
一旦アクセスで「資料YYMMDDHHSSMM.xlsx」を作成して、この後それを開いて式を入れ込むマクロを作るということですか?
アクセスVBAで.エクセルを開いて、式を入れ込むのもアクセス側でするってことでしょうか?
 
その認識であっているのなら、調べてみてやってみます。

 
私の提案3は、その通りです。
 
↓のような感じのコードを Docmd.TRANSFERSPREDSHEET メソッドの後に記述すればよいでしょう。
 
Dim xlApp As Object, xlsBook As Object
Dim maxRow As Long, maxcol As Long, toprow As Long
Const xUp = -4162
Const xltoLeft = 0

    Set xlApp = CreateObject("Excel.Application")
    Set xlsBook = xlApp.Workbooks.Open("ブックパス")
    With xlsBook
        With .Sheets(1)
            toprow = 2
            maxRow = .Range("A" & .Rows.Count).End(xlUp).Row
            maxcol = .Cells(toprow - 1, .Columns.Count).End(xltoLeft).Column + 1
            With .Cells(toprow, maxcol)
                .Formula = _
                        "=AVERAGE(" & .Cells(toprow, maxcol - 5).Address(0, 0) & ":" & .Cells(toprow, maxcol - 1).Address(0, 0) & ")"
                .Value = .Value
            End With
        End With
        .Close True
    End With
    Set xlsBook = Nothing
    xlApp.Quit

 
これが実現すれば、「エクセルを起動する時」には、平均値が計算されていることになります。

回答
投稿日時: 22/03/26 10:41:56
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません。
提案のコードに一部間違いがあります、
下記修正します。
 
誤)
            With .Cells(toprow, maxcol)
                .Formula = _
                        "=AVERAGE(" & .Cells(toprow, maxcol - 5).Address(0, 0) & ":" & .Cells(toprow, maxcol - 1).Address(0, 0) & ")"
                .Value = .Value
            End With
 
正)
            SHIKI = "=AVERAGE(" & .Cells(toprow, maxcol - 5).Address(0, 0) & ":" & .Cells(toprow, maxcol - 1).Address(0, 0) & ")"
            With .Range(.Cells(toprow, maxcol), .Cells(maxRow, maxcol))
                .Formula = SHIKI
                .Value = .Value
            End With
※変数追加
Dim SHIKI As String
を追加してください。

投稿日時: 22/03/26 22:36:02
投稿者: PICKY_LICKY_123

ありがとうございます。
おっしゃっている流れは理解できました。
ちょっと教えていただいたコードを確認するまで時間かかりそうなので、一旦お礼させていただきます。

回答
投稿日時: 22/03/27 17:55:28
投稿者: WinArrow
投稿者のウェブサイトに移動

細かい話ですが、
>「資料YYMMDDHHSSMM.xlsx」
の編集について
  
「MMSS」だと思いますが、「SSMM」と書かれています。
  
「SSMM」と「MMSS」では、「MM」の「値」が違ってきます。
  
実際のコードを確認したほうがよいでしょう。
  
↓テストコード

  
Debug.Print Format(Now(), "YYMMDDHHSSMM") & " " & Format(Now(), "YYMMDDHHMMSS")

トピックに返信