[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『RangeのネストとWorksheetfunction.Average』（Ali）
Ver.2002 xp
縦方向の平均値をAverageで連続して出力したく、このように書いてみました。 Dim i as integer i = 3 Do While i < 7 Application.WorksheetFunction.Average Range(Range(Cells _ (myRow - 1,i)).End(xlUp), Range(Cells(myRow - 1, i))) i = i + 1 Loop
平均値の元データは縦に並んでおり、それらの1行下のmyRowは常に 平均値を出したい行（セル）にあります。さらに元データの上は常に 空行なので、Rangeの開始セルRange(Cells(myRow - 1, i)).End(xlUp)、 （iは列番号を表します）。 終わりをRange(Cells(myRow - 1, i)としました。 この式でコンパイルエラーは出ませんが、Application.を実行すると"'Range' メソッドは失敗しました。'_Global'オブジェクト"。と出ます。 Rangeのネストの仕方が悪いのか、Rangeはネストができないのか、お知恵拝借 いたしたく存じます
myRowがきちんと取得できているものと仮定します。
Cells(myRow, "c").Resize(, 3).FormulaR1C1 = _ "=average(r" & Cells(myRow, "c").End(xlUp).Row & "c:r[-1]c)"
で数式が入りませんか？ (seiya)
早速のお教えありがとうございます。 頂戴したコードでは=AVERAGE(E$62:E62)のようになってしまい、範囲がうまく 取得できませんでした。myRowの取得とは具体的はどうなっていればよろしい のでしょうかまた、平均値は5列ほしいところ、3列までしか入りませんでした。 いただいたものは私には高度すぎて理解ができませんが、教えて君で恐縮ですが、 これもご解説お願いできますでしょうか。
おっと、質問者さんと衝突してしまいましたが そのまま載せておきます。
Cellsが単独でRangeの中に入っているのと
Average Range
~関数の括弧が無いのを解消すると
動くようになると思いますが
Application.WorksheetFunction.Average(Range(Cells _
(myRow - 1, i).End(xlUp), Cells(myRow - 1, i)))
seiyaさんが書いて居られるように、範囲のセルに 一度で数式を埋め込むのが良いと思います。
それにしても ＞myRowの取得とは具体的はどうなっていればよろしいのでしょうか ってことは、myRowは取得していないのですか？
ここは、Aliさんが平均の結果を出したい行を設定する事になると思いますが 何処に平均の結果を出したいのですか？
既にシートが出来ている状態で決められた位置に平均を出したいなら シートの状態を説明してみられるのが良いように思います。
(HANA)
こんにちは。かみちゃん です。
横から失礼します。
> お知恵拝借いたしたく存じます
以下のご質問と何か関係がありますか？
(かみちゃん) 2009-01-18 17:40
まとめresですみません。 市販のアンチョコ片手にやっているので、まだ概念が身に付いていないものでして。
HANAさん ありがとうございます。頂戴したコードでは"アプリケーション定義またはオブジェクト 定義のエラーです"と怒られました。ご指摘ごもっともで、泥縄ながらRowとか今読んで いる次第です。 かみちゃん >以下のご質問と何か関係がありますか？ 今書いているものの書き出しで、伺ったものです。
えっと・・・そうですね。 例えば で話をしますので、同じ状況のシートを そちらで作成して動かしてみてください。 データは↓の配置です。 [A] [B] [C] [D] [E] [F] [G] [1] [2] [3] 1 2 3 4 5 [4] 1 2 3 4 5 [5] 1 2 3 4 5 [6] 1 2 3 4 5 [7] 平均を入れるのは7行目です。 以下のコードは、C7:G7の範囲に平均を入れます。
'------
Sub 平均を7行目に()
Dim i As Long, myRow As Long
myRow = 7
i = 3
Do While i < 8
Cells(myRow, i).Value = Application.WorksheetFunction.Average(Range(Cells _
(myRow - 1, i).End(xlUp), Cells(myRow - 1, i)))
i = i + 1
Loop
End Sub
'------
ただし、実際はmyRowを次々に変えていく必要がありますし 一つずつ計算して入れていくよりも、よい方法があると思いますので WorksheetFunction.Averageを使ったサンプルコード程度でご覧ください。
(HANA)
確かに前のご質問時に ＞平均などを出力しようと思っています と書いておられましたね。
平均だけを出すのなら、前回の続きとして ループしないバージョン。
'------
Sub 行挿入と平均()
Dim mrn As Long
Range("A:A").Insert
With Range("B3", Range("B" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=if(C2<>C3,1,"""")"
On Error Resume Next
.SpecialCells(-4123, 1).EntireRow.Insert
On Error GoTo 0
End With
Range("A:A").Delete
mrn = Range("C1").End(xlDown).Row + 1
With Range("C1", Range("G" & Rows.Count).End(xlUp).Offset(1))
With .SpecialCells(4)
.Formula = "=SUMIF($B$2:$B" & mrn & ",$B" & mrn - 1 & _
",C$2:C" & mrn & ")/COUNTIF($B$2:$B" & mrn & ",$B" & mrn - 1 & ")"
End With
.Value = .Value
End With
End Sub
'------
1行目は見出しがある。 すべてのデータは、隙間なく最終行まで入力されている。 場合を想定しています。
平均以外にも何か計算を行いますか？
(HANA)
HANAさん本当にありがとうございます。
お言葉に甘えて申し上げますと データは8列より成り、このうち平均を計算したい列は5-7列で、最後の8列目は合計を出力 したいです。データはところにより空白があります。具体的なフィールドは順に [薬剤No] [薬量] [処理日の季節][イネ最大薬害] [イネ薬害] [ヒエ] [ホタルイ] [イネのカウント]です。 1薬剤1シートで、薬量で降順にソートされてます。現時点でできているところは、薬剤量が 変わるところに、4行空行を挿入し薬剤Noと薬量をその直下にコピーする事で、これから先 上述の平均と合計の出力するところが目下の課題です。丸投げしてしまいましたすみません。
済みませんが、イメージがよく分からないので 実際のデータの様なサンプルデータを使用した 希望結果図を載せてもらえませんか？
また ＞4行空行を挿入し薬剤Noと薬量をその直下にコピー の所のイメージが良く分からないので このコードを載せてもらえると良いのですが。
1行目に見出しがあって、「薬量」毎に そのグループの最後の行に平均と合計があって 3行空いた後、次のグループが始まる ・見出しはあくまでも1行目のみ ・平均・合計の行のA,B列には「薬剤No」と「薬量」を表示 って事ですかね・・・。
もう少しお伺いしてみますが １．その表内で既にどこかに数式を使っていますか？ ２．行の挿入時には、書式もシフトさせる必要がありますか？
２の質問の具体例としては、例えば 9行目を挿入した場合、C10→C11に変わりますが イ．C10セルには色が付けて有るので、セルの色も一緒に移動させたい ロ．特有の書式が設定されている所は無いので、数字だけが移動すれば良い 前者（イ）のご希望が有りますか？
(HANA)
最終的にはこんな表を作りたいのですが....
ソースはaccessが出力したテーブルです。Accessだけでは無理と思いexcelの マクロをつかっております。「処理日の季節」は グループ化されています。
[薬剤No] [薬量] [処理日の季節] [イネ最大] [イネ薬害] [ヒエ] [ホタルイ] [カウント]
22 100 夏 2 1 6 5 1
22 100 夏 2 2 5 7 1
22 100 夏 3 2 8 7 1
22 100 夏 平均 平均 平均 平均 合計
空行(2-3行程度)
22 50 秋 1 2 7 6 1
22 50 秋 1 3 7 5 1
22 50 秋 平均 平均 平均 平均 合計
>見出しはあくまでも1行目のみ・平均・合計の行のA,B列には「薬剤No」と「薬量」 >を表示って事ですかね・・・。 その通りです。書き忘れましたが、季節も表示させたいです。 表の中で数式は使っておりません。
>前者（イ）のご希望が有りますか？ ありません。
>>4行空行を挿入し薬剤Noと薬量をその直下にコピー >の所のイメージが良く分からないので >このコードを載せてもらえると良いのですが。
拙いコードですが、以下のようにしてみました。
For Line = 1 To 4 Step 1
Rows(myRow).Insert '4行挿入
If Line = 4 Then
Range(Cells(myRow - 1, 1), Cells(myRow - 1, 3)).Copy '化合物番号のコピー
Cells(myRow, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
End If
Next Line
お忙しいところお手を煩わせて申し訳ありません。
えっと、 ＞現時点でできているところは、薬剤量が変わるところに、 ＞4行空行を挿入し薬剤Noと薬量をその直下にコピーする事 ということですが、そのコードではそのようにならないと思います。
myRowを取得していませんし。
もしかして、これはのコードから呼び出されるコードで myRowはそちらから渡されるからコード内で取得していない ということですかね？
せっかくですので、ワークする状態で載せてもらえませんか？
(HANA)
お見苦しいところで恐縮ですが......
Sub RowInsert()
Dim myRow As Long Dim myCol As Integer
myCol = 2
For myRow = Cells(Rows.Count, myCol).End(xlUp).Row To 3 Step -1
'For myRow = Cells("A65536").End(xlUp).Row To 3 Step -1
If Cells(myRow, myCol) <> Cells(myRow - 1, myCol) Then
Dim Line As Integer
For Line = 1 To 4 Step 1
Rows(myRow).Insert '4行挿入
If Line = 4 Then
Range(Cells(myRow - 1, 1), Cells(myRow - 1, 3)).Copy '化合物番号のコピー
Cells(myRow, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
End If
Next Line
'Application.WorksheetFunction.Average (Range(Cells _
'(myRow - 1, i).End(xlUp), Cells(myRow - 1, i)))
End If
Next myRow
End Sub
全然見苦しくないですよ。 せっかく作ったのですから、活用出来た方が良いと思いませんか？
ということで、載せてくださったコードから変更したものが以下です。
Sub RowInsertAndCalculation3()
Dim myRow As Long Dim myCol As Integer Dim eRow As Long '★計算範囲の最後の行
myCol = 2
eRow = Cells(Rows.Count, myCol).End(xlUp).Row '★
For myRow = Cells(Rows.Count, myCol).End(xlUp).Row To 3 Step -1
'For myRow = Cells("A65536").End(xlUp).Row To 3 Step -1
If Cells(myRow, myCol) <> Cells(myRow - 1, myCol) Then
'★↓↓↓★
Cells(eRow + 1, 4).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 4), Cells(eRow, 4)))
Cells(eRow + 1, 5).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 5), Cells(eRow, 5)))
Cells(eRow + 1, 6).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 6), Cells(eRow, 6)))
Cells(eRow + 1, 7).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 7), Cells(eRow, 7)))
Cells(eRow + 1, 8).Value = Application.WorksheetFunction.Sum(Range(Cells(myRow, 8), Cells(eRow, 8)))
eRow = myRow - 1
'★↑↑↑★
Dim Line As Integer
For Line = 1 To 4 Step 1
Rows(myRow).Insert '4行挿入
If Line = 4 Then
Range(Cells(myRow - 1, 1), Cells(myRow - 1, 3)).Copy '化合物番号のコピー
Cells(myRow, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
End If
Next Line
'Application.WorksheetFunction.Average (Range(Cells _
'(myRow - 1, i).End(xlUp), Cells(myRow - 1, i)))
End If
Next myRow
'★↓最終行のコピーと、最初のグループの計算↓★
myRow = Cells(Rows.Count, myCol).End(xlUp).Row
Range(Cells(myRow, 1), Cells(myRow, 3)).Copy '化合物番号のコピー
Cells(myRow + 1, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
Cells(eRow + 1, 4).Value = Application.WorksheetFunction.Average(Range(Cells(2, 4), Cells(eRow, 4)))
Cells(eRow + 1, 5).Value = Application.WorksheetFunction.Average(Range(Cells(2, 5), Cells(eRow, 5)))
Cells(eRow + 1, 6).Value = Application.WorksheetFunction.Average(Range(Cells(2, 6), Cells(eRow, 6)))
Cells(eRow + 1, 7).Value = Application.WorksheetFunction.Average(Range(Cells(2, 7), Cells(eRow, 7)))
Cells(eRow + 1, 8).Value = Application.WorksheetFunction.Sum(Range(Cells(2, 8), Cells(eRow, 8)))
'★↑間違っていたので訂正（汗）↑★
End Sub
'★部分を追加しましたが、下の塊は上からのコピーですし 計算部分は、基本的に列番号が違うだけです。
処理の流れとしては １．一番下の行を覚えておく（eRow） ２．データが変わる行を見つける（If ・・・ <> ・・・ Then） その時の行がmyRowに入っているので、平均等を求めるのは myRowとeRowの範囲のセルに関してです。 ３．計算をして、次の計算のための下側の行を覚えておく（eRow = myRow - 1） ４．行の挿入。A:C列のコピー ５．次のデータが変わる行を見つける ・・・・・・ 3行目まで済んだ場合、一番下の行のA:C列のコピーがまだなのと 最初のグループの平均等が出ていないので、 個別にコピー＆計算を行う。 です。
一応、以下は配列を使用したコードです。 こちらはご参考程度に。
'------
Sub 平均と合計の計算()
Dim i As Long, ii As Long, xr As Long
Dim k(1 To 6), tbl, x
tbl = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, 8).Value
ReDim x(1 To UBound(tbl, 1) * 4, 1 To 8)
For i = 1 To UBound(tbl, 1) - 1
xr = xr + 1
For ii = 1 To 5
x(xr, ii) = tbl(i, ii)
k(ii) = k(ii) + tbl(i, ii + 3)
Next
For ii = 6 To 8
x(xr, ii) = tbl(i, ii)
Next
k(6) = k(6) + 1
If tbl(i, 2) & "_" & tbl(i, 3) <> tbl(i + 1, 2) & "_" & tbl(i + 1, 3) Then
For ii = 1 To 3
x(xr + 1, ii) = tbl(i - 1, ii)
Next
For ii = 4 To 7
x(xr + 1, ii) = Format(k(ii - 3) / k(6), "0.00") '←平均値の丸め
Next
x(xr + 1, 8) = k(5)
For ii = 1 To 6
k(ii) = 0
Next
xr = xr + 3 '←間の行（xrに +3 の時 2行空きます。）
End If
Next
Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 8).ClearContents
Range("A2").Resize(xr, 8) = x
End Sub
'------
データがA1セルから始っていること。 また、行挿入の条件は「薬量&季節」にしてあります。 （違う季節だけど、たまたま薬量が同じものが続く可能性って無いのですかね？ あるいは、その場合 同じグループで計算するのですかね？）
(HANA)
別案で、
Sub test()
Dim WholeArea As Areas, OneArea As Range
With Columns("a")
On Error Resume Next
Set WholeArea = .SpecialCells(2).Areas
On Error GoTo 0
If WholeArea Is Nothing Then Exit Sub
For Each OneArea In WholeArea
With OneArea
With .Cells(.Cells.Count)
.Offset(, 3).Resize(, 4).FormulaR1C1 = _
"=average(r" & OneArea.Row & "c:r[-1]c)"
.Offset(, 7).FormulaR1C1 = _
"=sum(" & r" & OneArea.Row & "c:r[-1])"
End With
End With
Next
End With
End Sub
(seiya)
HANAさん、本当にありがとうございます。人手でやっていたことを電子的にしようと思い立ち 以来約一ヶ月、おかげさまでようやく完成です。 多謝。 最終行で[薬剤No] [薬量] [処理日の季節]の貼り付けができてないのですが、どこを 直せばいいのでしょう？ 配列を使用したものや、seiyaさんのコードは高度でじっくり勉強させていただき ます。
>違う季節だけど、たまたま薬量が同じものが続く可能性って無いのですかね？ 現在の試験の仕方ではないと思います。
＞最終行で[薬剤No] [薬量] [処理日の季節]の貼り付けができてないのですが 昨夜一応動かしてみて問題ないと思ったのですが。。。。 済みません、駄目ですね。
For myRow ～ Next が終わった時点で シートの状態は以下の様に成っています。 [A] [B] [C] [D] [E] [F] [G] [H] [1] 薬剤No 薬量 季節 イネ最大 イネ薬害 ヒエ ホタルイ カウント [2] 22 100 夏 2 1 6 5 1 [3] 22 100 夏 2 2 5 7 1 [4] 22 100 夏 3 2 8 7 1 [5] 22 100 夏 [6] [7] [8] [9] 22 50 秋 1 2 7 6 1 [10] 22 50 秋 1 3 7 5 1 [11] 1 2.5 7 5.5 2
その次の
myRow = Cells(Rows.Count, myCol).End(xlUp).Row
で、myRow は10になります。その後コピーするのは
Range(Cells(myRow - 1, 1), Cells(myRow - 1, 3)).Copy '化合物番号のコピー
一つ上の行ではなく、同じ行ですね。
→Range(Cells(myRow, 1), Cells(myRow, 3)).Copy '化合物番号のコピー
それから、貼り付けるのは 一つ下の行なので
Cells(myRow, 1).Select → Cells(myRow + 1, 1).Select
でした。
失礼しました。（謝）
(HANA)
ほんとうにすみませんながら。
教えてくださった変更を行いましたところ、私の環境では、今度は逆に 最終行「以外」に[化合物No] [薬量] [処理日の季節]が入力されない状態に なってしまいます。 私が入力したものは以下ですが、これで間違いないでしょうか。
'★↓最終行のコピーと、最初のグループの計算↓★
myRow = Cells(Rows.Count, myCol).End(xlUp).Row ' myRow=10
Range(Cells(myRow, 1), Cells(myRow, 3)).Copy '化合物番号のコピー
Cells(myRow + 1, 1).Select
ActiveSheet.Paste
加えてはじめの薬量（1000）で平均値と合計がありえない形となりました。具体的には、平均値は 低く、カウントの合計はありえないほど大きな数字、例えば873のようになってしまいます。
変更コードはそれで良いはずですが。。。 何ででしょうね、ご提示の表をA1セルから作成して 動かしてみているのですが・・・・。
取り敢えず、上記コードを変更してみました。 現在の上手く行かないコードはそのまま残しておいて もう一度コードを貼り付けてやってみて下さい。
コードの変更に伴い マクロ名も「RowInsertAndCalculation2」に変更しました。
合計等がおかしいのが、最初からだったら これで解決するような問題では無さそうですが。 （加えて って事は、最初のでは大丈夫だったのかな？）
(HANA)
HANAさん、大変お世話になっております。
RowInsertAndCalculation2で化合物Noなどの貼り付けはうまくいくようになりました。 見る限り違いはないようですが。どうも....
しかし薬量が1000で一番上（出だし）の平均とカウントがおかしな値のままです。 解析しようにもそうウデがあるわけではないし....困ってます。
済みません（涙）間違えてました。。。。
一番最初のグループの平均・合計を行う際の 上側の行は「myRow」ではなくて「2」ですね。
再度変更しましたのでやってみて下さい。 「RowInsertAndCalculation3」です。
(HANA)
HANAさんこの教えて君にお付き合いいただきありがとうございます。
完璧に動作いたしました。私もいただいたコードで勉強致します。 本当にありがとうございました（涙）。
出来ましたか、よかったです。 詰めが甘くて済みませんでした。。。。
＞一番最初のグループの平均・合計を行う際の
＞上側の行は「myRow」ではなくて「2」ですね。
という事で、コード内のmyRow部分を「2」に変更しましたが
そこはmyRowのままで、最終行を再取得する
myRow = Cells(Rows.Count, myCol).End(xlUp).Row
の上に持っていっておいてもよかったかもしれません。
myRowは「3」までループするので Next myRow の次の行へ行ったときは この値は「2」になっていますので。
(HANA)
すみません再び。前回いただいたコードですが、フィールドを追加しようと思い、単純 に以下の通り修正したところ Cells(eRow + 1, 8).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 8), Cells(eRow, 8)))で 「WorksheetFunctionクラスのAverageプロパティを取得できません。」と怒られました。
また教えて君で恐縮ですがご示唆お願いできますでしょうか。
変更は以下の通りです。
Cells(eRow + 1, 8).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 8), Cells(eRow, 8))) Cells(eRow + 1, 9).Value = Application.WorksheetFunction.Sum(Range(Cells(myRow, 9), Cells(eRow, 9)))
Cells(eRow + 1, 8).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 8), Cells(eRow, 8))) Cells(eRow + 1, 9).Value = Application.WorksheetFunction.Sum(Range(Cells(myRow, 9), Cells(eRow, 9)))
恐れ入りながらよろしくお願い申し上げます。Ali
恐れ入りながらですか。＾＾ 普通で良いですよ。。。。
＞フィールドを追加しようと思い って言うのは、どういう事になったって事ですか？ D:Hが平均で、Iが合計？
あ～、たぶんですが 全くデータの無い範囲の平均を出そうとしてませんか？ 平均って =合計/個数 なので、 個数が0なのに計算させようとすると エラーになりますよね。 （0で割る事になるので。）
この「たぶん」は合っていそうですか？ また、 実際も データが無い範囲が出来ることが有りますか？
(HANA)
ありがとうございます。
>D:Hが平均で、Iが合計？ その通りです。
>この「たぶん」は合っていそうですか？ >また、 実際も データが無い範囲が出来ることが有りますか？
データを再び見直したところ、データのない範囲が見つかりました。 どうしたものでしょうか。
そうでしたか。どうしましょうかね。（笑）
IFで場合分けをすれば良いのでしょうけど。
例えば、D列だったら
If Application.WorksheetFunction.Sum(Range(Cells(myRow, 4), Cells(eRow, 4))) > 0 Then
Cells(eRow + 1, 4).Value = Application.WorksheetFunction.Average(Range(Cells(myRow, 4), Cells(eRow, 4)))
End If
こんな感じで、先に範囲の合計（Countでも良いですが）をして
0より大きかった（1以上の）時に、平均を計算する。
平均１つにつき２行追加になるので 場所もとりますし 平均１つにつき２回計算が必要になるので 時間も掛かります。
Application.WorksheetFunction.Averageに見切りを付けて
数式を入れるのはどうでしょう。
Range(Cells(eRow + 1, 4), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'D:H列の範囲に「=IF(SUM(D2:D5)>0,AVERAGE(D2:D5),"")」の様な関数を入れる
Cells(eRow + 1, 9).Formula = _
"=IF(SUM(I" & myRow & ":I" & eRow & ")>0,SUM(I" & myRow & ":I" & eRow & "),"""")"
'I列に 「=IF(SUM(I2:I5)>0,SUM(I2:I5),"")」の様な関数を入れる
こんな書き方だったら、少しは分かりやすいですか？ コメントとして書いている関数の「2」や「5」の部分を 変数に変えています。
(HANA)
Ifなんて基本中の基本を試しそびれるなんてどうかしてました。どうも人に甘えてしまうと 思考停止してしまっていけません。
結果をお知らせしますと、Ifを使ったものは相変わらず同じエラーが出ました。 次にApplication.WorksheetFunction.Averageを使わない方法は算出した平均が出力 されるべきセルが空白のままです。コードが正しいか否か全文載せますので、ご高覧 お願いいたします。
Sub RowInsertAndCalculation5変更1()
Dim myRow As Long Dim myCol As Integer Dim eRow As Long '★計算範囲の最後の行
myCol = 2
eRow = Cells(Rows.Count, myCol).End(xlUp).Row '★
For myRow = Cells(Rows.Count, myCol).End(xlUp).Row To 3 Step -1
'For myRow = Cells("A65536").End(xlUp).Row To 3 Step -1
If Cells(myRow, myCol) <> Cells(myRow - 1, myCol) Then
'★↓↓↓★
Range(Cells(eRow + 1, 4), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 5), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 6), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 7), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 8), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'D:H列の範囲に「=IF(SUM(D2:D5)>0,AVERAGE(D2:D5),"")」の様な関数を入れる
Cells(eRow + 1, 9).Formula = _
"=IF(SUM(I" & myRow & ":I" & eRow & ")>0,SUM(I" & myRow & ":I" & eRow & "),"""")"
'I列に 「=IF(SUM(I2:I5)>0,SUM(I2:I5),"")」の様な関数を入れる
eRow = myRow - 1
'★↑↑↑★
Dim Line As Integer
For Line = 1 To 4 Step 1
Rows(myRow).Insert '4行挿入
If Line = 4 Then
Range(Cells(myRow - 1, 1), Cells(myRow - 1, 3)).Copy '化合物番号のコピー
Cells(myRow, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
End If
Next Line
End If
Next myRow
'★↓最終行のコピーと、最初のグループの計算↓★
'◆↓下から移動して来ました↓◆
Range(Cells(eRow + 1, 4), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'D:H列の範囲に「=IF(SUM(D2:D5)>0,AVERAGE(D2:D5),"")」の様な関数を入れる
Cells(eRow + 1, 9).Formula = _
"=IF(SUM(I" & myRow & ":I" & eRow & ")>0,SUM(I" & myRow & ":I" & eRow & "),"""")"
'I列に 「=IF(SUM(I2:I5)>0,SUM(I2:I5),"")」の様な関数を入れる
'◆↑下から移動して来ました↑◆
myRow = Cells(Rows.Count, myCol).End(xlUp).Row
Range(Cells(myRow, 1), Cells(myRow, 3)).Copy '化合物番号のコピー
Cells(myRow + 1, 1).Select
ActiveSheet.Paste '化合物番号の貼り付け
'◆ Range(Cells(eRow + 1, 4), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 5), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 6), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 7), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'◆ Range(Cells(eRow + 1, 8), Cells(eRow + 1, 8)).Formula = _
"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")"
'D:H列の範囲に「=IF(SUM(D2:D5)>0,AVERAGE(D2:D5),"")」の様な関数を入れる
'◆ Cells(eRow + 1, 9).Formula = _
"=IF(SUM(I" & myRow & ":I" & eRow & ")>0,SUM(I" & myRow & ":I" & eRow & "),"""")"
'I列に 「=IF(SUM(I2:I5)>0,SUM(I2:I5),"")」の様な関数を入れる
End Sub
循環参照が発生していると怒られました。
まず ＞Range(Cells(eRow + 1, 4), Cells(eRow + 1, 8)).Formula = _ ＞"=IF(SUM(D" & myRow & ":D" & eRow & ")>0,AVERAGE(D" & myRow & ":D" & eRow & "),"""")" だけで、8列目までに式を入れるので、その下の5,6,7,8に関しては不要です。
循環参照は、たぶん 最後の部分でおきていると思います。 ＞一番最初のグループの平均・合計を行う際の ＞上側の行は「myRow」ではなくて「2」ですね。 です。
誠に勝手ながら、上記２点について、御提示のコードを 直接編集させてもらいました。 （不要部分は先頭に「'◆」、追加部分は「'◆」の間に挟みました。）
＞算出した平均が出力されるべきセルが空白のままです。 このセルには、数式がそのままセットされているはずですので どの様な式が入っているか、またその式が妥当かどうか 参照先がずれている場合は、どれだけずれているか 確認して下さい。
(HANA)
お世話になります。 早速ご修正くださいましたコードを試してみました。 意図したとおりに動作しました。ありがとうございました。
私はどうも鈍くて申し訳ありませんでした。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.