複数のデータの内、0以上の値のセル1/4個分の合計

質問

やりたいことは、複数のデータの内、0以上の値を示すいくつかのセルの、その1/4個分のセルの合計を出すことです。

図で具体的に説明します。
A列に37個のデータがあります。
このうち、値が0以上のセルは、A7からA27までの21個です。
このうち、上1/4個分のセルの合計を出したいです。
計算するセルは、5.25個（セル21個/4）のセルなので、
A列の場合は、
A7＋A8＋A9＋A10＋A11＋(A12*0.25)
で計算することになります。
ただ、A列だけですといいのですが、
実際には、７２列あります（添付の図は、そのうちの３列分です）。
また、それぞれの列で、値が0以上のセルは、それぞれ異なります。
なにか、良い方法はないでしょうか？
教えてください。
よろしくお願いします。

回答No.7

MarcoRossiItaly · ベストアンサー

念のための確認ですが、四分位数なら QUARTILE 関数というのがあるのですが、そうではなくて、個数で 1/4 だけ合計ということでいいのですね？

0 以上ではなく、「0 より大きい」あるいは「正の数（正数）」ということですね。データ処理するときは文学的な表現でなく、数学の言い回しをなるべく使うよう心がけましょう。今回は図もあって明白なので意味は通じますが、紛れが生じる場合も多々あるかと思います。

D1　=countif(a:a,">0")/4
D2　=min(index(row(a1:a1000)+10^15*(a1:a1000<=0),))
D3　=if(d1>=1,sum(offset(a1,d2-1,0,int(d1),1)),)+mod(d1,1)*offset(a1,int(d1)+d2-1,0)

データ量が 1,000 行を超えるときは、2 箇所の「a1000」を必要な行数に合わせてください。D1:D3 の数式を A1002:1004 とか別シートに入力しても構いません。別シートの場合は数式中に「sheet1!」などの追記が必要になりますが。「10^15」というのは、Excel が扱える最大の数値ということです。「+10^15*(a1:a1000<=0)」というのは、0 以下の行だけ 10^15 を足すという意味です。D2 セルでは配列を使っていますが、INDEX を混ぜているので、Ctrl+Shift+Enter の操作は不要です。IF で場合分けしているのは、D1 がゼロのときに OFFSET の第 4 引数もゼロになることによりエラーが発生するのを回避するための措置です。

お礼

『四分位数なら QUARTILE 関数というのがあるのですが、そうではなくて、個数で 1/4 だけ合計ということでいい』
『「0 より大きい」あるいは「正の数（正数）」』
これらの補足ありがとうございます。気がつきませんでした。

ご提示いただきました関数で、結果をだすことができました。
理解できたのは、D1の関数だけでしたが…。

ありがとうございました。

回答No.9

MarcoRossiItaly

＞理解できたのは、D1の関数だけでしたが…。

難しすぎて、すみません。なかなか全てを理解してもらうには、知っていただかないといけない要素が多くてたいへんかもしれませんが、回答を読んでいて、もしも何か追加のご質問でもありましたら、こちらで分かることは何でもお答えします。こういう QA サイトの本当の使い方は質問・回答なのであって、作業を人に手伝ってもらうことではないのですから…。

お礼

『もしも何か追加のご質問でもありましたら、こちらで分かることは何でもお答えします。』
あたたかいお言葉、心にしみました。
『こういう QA サイトの本当の使い方は質問・回答なのであって、作業を人に手伝ってもらうことではないのですから…。』
理解できるまで勉強して、それでもわからなかったら、「自分の理解を深めるために」QAサイトを頼ろうと思います。

うまく文字で表現できませんが、大きな何かをいただいた気がします。
ありがとうございました。

回答No.8

tom04

No.3です！

解決しているようですが・・・関数で無理やりやってみました。
とりあえず40行までの数式です。

A列の41行目以降のセルに
=SUM(INDIRECT(ADDRESS(MIN(IF(A1:A40>0,ROW(A1:A40))),COLUMN())&":"&ADDRESS(SMALL(IF(A1:A40>0,ROW(A1:A40)),INT(COUNTIF(A1:A40,">0")/4)),COLUMN())))+INDIRECT(ADDRESS(SMALL(IF(A1:A40>0,ROW(A1:A40)),CEILING(COUNTIF(A1:A40,">0")/4,1)),COLUMN()))*MOD(COUNTIF(A1:A40,">0")/4,1)

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面から数式をコピー＆ペーストする場合は、
上記数式をコピー　→　貼り付けたいセル（A41以降）を選択　→　数式バーを一度クリック　→　貼り付け
→　そのまま（編集可能のまま）Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これを列方向にオートフィルでコピーしてみてください。
尚、「0より大きい」データ数が4個未満の場合はエラーとなります。

※　「0より大きい」データが連続していなくても対応できるようにしてみました。m(_ _)m

お礼

『この画面から数式をコピー＆ペーストする場合は、
上記数式をコピー　→　貼り付けたいセル（A41以降）を選択　→　数式バーを一度クリック　→　貼り付け
→　そのまま（編集可能のまま）Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。』

具体的なご説明で作業がわかりやすく、すぐできました。
ありがとうございました。

回答No.6

tsubuyuki

＃５さんの式に、なるほどと感心しつつ。

とりあえず「解決したのかしていないのか」
あるいは「回答を理解できたのか出来なかったのか」
教えて頂けませんか？
実は「お礼」にはあまりこだわっておりません。
質問者さんが「質問をどのように解決したか」の方が気になります。

そんなわけで、マクロ編です。
例えば、

Sub Sample()
Dim XVal As Long, YVal As Long, Zval As Double
Dim myCount As Long, myVal As Double
Dim myRow As Long, myCol As Long

　　　　For myCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
　　　　　　　　XVal = Application.CountIf(Range(Cells(1, myCol), Cells(1, myCol).End(xlDown)), ">0")
　　　　　　　　YVal = Int(XVal / 4)
　　　　　　　　Zval = XVal / 4 - YVal

　　　　　　　　myCount = 0: myVal = 0
　　　　　　　　For myRow = 1 To Cells(1, myCol).End(xlDown).Row
　　　　　　　　　　　　If Cells(myRow, myCol) > 0 Then
　　　　　　　　　　　　　　　　myVal = myVal + Cells(myRow, myCol)
　　　　　　　　　　　　　　　　myCount = myCount + 1
　　　　　　　　　　　　　　　　If myCount >= YVal Then
　　　　　　　　　　　　　　　　　　　　myVal = myVal + Cells(myRow + 1, myCol) * Zval
　　　　　　　　　　　　　　　　　　　　Exit For
　　　　　　　　　　　　　　　　End If
　　　　　　　　　　　　End If
　　　　　　　　Next myRow
　　　　　　　　Cells(40, myCol) = myVal
　　　　Next myCol
End Sub

これを走らせると、４０行目に結果を表示します。

ユーザー定義関数にすると

Function mySumIf(myrange As Range, myInd As Long) As Double
Dim XVal As Long, YVal As Long, Zval As Double
Dim myCount As Long, myVal As Double
Dim myRow As Long

　　　　XVal = Application.CountIf(myrange, ">0")
　　　　YVal = Int(XVal / myInd)
　　　　Zval = XVal / myInd - YVal

　　　　For myRow = 1 To myrange.Count
　　　　　　　　If Cells(myRow, myrange.Column) > 0 Then
　　　　　　　　　　　　myVal = myVal + Cells(myRow, myrange.Column)
　　　　　　　　　　　　myCount = myCount + 1
　　　　　　　　　　　　If myCount >= YVal Then
　　　　　　　　　　　　　　　　myVal = myVal + Cells(myRow + 1, myrange.Column) * Zval
　　　　　　　　　　　　　　　　Exit For
　　　　　　　　　　　　End If
　　　　　　　　End If
　　　　Next myRow

　　　　mySumIf = myVal
End Function

これを標準モジュールに貼り付けると、
ワークシート上で
　　=mySumIf(A1:A37,4)
などのように使えます。
後ろの「,4」は
> このうち、上1/4個分のセルの合計を出したいです。
の「４」です。５でも１０でも指定できます。

多分、もっと良い書き方はあるはずですが、参考までに。

お礼

表面的ですが、おかげさまで解決しました。
理解のほどは、と言いますと、ほぼ困難です。
（私が、マクロを日常的に使えていないせいです。）

具体的には、
『そんなわけで、マクロ編です。
例えば、～これを走らせると、４０行目に結果を表示します。』
の部分は、コピーペーストで、解を得ることができました。

『ユーザー定義関数にすると～５でも１０でも指定できます。』
の部分は、コピーペーストすらできませんでした。
（私自信、どこがどう理解できているのかもよくわかっていないせいです。）

少しずつでも理解しないと、自立に結びつかないです。
がんばります！

ご教授ありがとうございました。

回答No.5

keithin

簡単のためセルを分けて計算しますが、その気になれば一個に詰め込んでも出来ます。お勧めはしませんが。

Ａ３８に
=IF(A40,IFERROR(SUM(OFFSET(A1,A39-1,0,INT(A40),1)),0)+INDEX(A1:A37,A39+A40)*MOD(A40,1),"")

Ａ３９に
=IF(A40,MIN(IF(A1:A37>0,ROW(A1:A37))),"")
必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力

Ａ４０に
=COUNTIF(A1:A37,">0")/4

＃補足
ゼロより大きい数字セルは「必ず連続している」とします

＃参考
ゼロより大きい数字セルが４個未満しかなかった場合の処置がお話をちょっとややこしくしています

お礼

『Ａ３８に
=IF(A40,IFERROR(SUM(OFFSET(A1,A39-1,0,INT(A40),1)),0)+INDEX(A1:A37,A39+A40)*MOD(A40,1),"")　』
この部分は、お示しいただいた関数をコピーペーストしました。
IFERROR、OFFSET、INT、MODは、私にとってなじみのない関数で、理解するのに時間がかかりそうですが、これらの関数が理解できるよう、がんばってみます。

『Ａ３９に
=IF(A40,MIN(IF(A1:A37>0,ROW(A1:A37))),"")
必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力』
この部分の注意書きの意味を理解するのは、時間がかかりそうです。ROW関数の理解も時間がかかりそうです。

『Ａ４０に
=COUNTIF(A1:A37,">0")/4』
この関数は、すぐに理解できました。

私の理解の程度は、上記のような状況です。
そのため、A40に出てきた値が5.25となりました。
お示しいただいたご回答を、活用できるよう、時間をかけてみます。

また、補足や参考を加筆いただき、ありがとうございました。
加筆していただいた内容が、お示しいただいた関数とどのように関係しているのか理解するのには、時間がかかりそうですが、一つずつ、解決したいです。

ありがとうございました。

回答No.4

tom04

No.３です！
たびたびごめんなさい。

前回のコードで１行訂正してください。
最後の方の
＞Do Until Cells(n, j) > 0
を
＞Do Until wS1.Cells(n, j) > 0
のようにしてください。

※　Sheetを指定していなかったので、Sheet2がアクティブな状態でマクロを実行すると
エラーになってしまいます。

尚、前回書き忘れたのですが、Sheet1の１行目で最終列を取得していますので、
元データは１行目からあり、何らかのデータが入っているという前提のコードです。m(_ _)m

お礼

ありがとうございました。

回答No.3

tom04

こんばんは！
何とか関数で！と頑張ってみたのですが・・・ギブアップです。

＞0以上の値を示すいくつかのセル・・・
とありますが、画像を拝見すると「0より大きい」セルのようですので、
０より大きいセルを対象としています。

VBAでやってみました。
Sheet１（お示しの画像）のデータをSheet2の１行目に表示するようにしています。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, endCol As Long, n As Long, cnt As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS2.Cells.ClearContents
endCol = wS1.Cells(1, Columns.Count).End(xlToLeft).Column
With wS2.Cells(2, 1).Resize(1, endCol)
.Formula = "=INT(COUNTIF(Sheet1!A:A,"">0"")/4)"
.Value = .Value
End With
With wS2.Cells(3, 1).Resize(1, endCol)
.Formula = "=MOD(COUNTIF(Sheet1!A:A,"">0"")/4,1)"
.Value = .Value
End With

For j = 1 To endCol
cnt = 0
For i = 1 To wS1.Cells(Rows.Count, j).End(xlUp).Row
If wS1.Cells(i, j) > 0 Then
cnt = cnt + 1
wS2.Cells(1, j) = wS2.Cells(1, j) + wS1.Cells(i, j)
End If
If cnt = wS2.Cells(2, j) Then
Exit For
End If
Next i
n = i + 1
If wS1.Cells(n, j) <= 0 Then
Do Until Cells(n, j) > 0
n = n + 1
Loop
End If
wS2.Cells(1, j) = wS2.Cells(1, j) + wS1.Cells(n, j) * wS2.Cells(3, j)
Next j
wS2.Rows(2 & ":" & 3).Clear
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数で簡単にできる方法があればごめんなさいね。m(_ _)m

お礼

ありがとうございました。

回答No.2

tsubuyuki

添付忘れました。

重ねて、すいません。

お礼

丁寧なご返答ありがとうございました。

回答No.1

tsubuyuki

すいません、作業列を使うやり方しか思いつかなかった上に、
非常に効率が悪い式になってしまいました。

でも、なんとなく勿体無いので、

=IF(AND(A1>0,COUNTIF($A$1:$A$37,">0")/4>=COUNTIF(A$1:A1,">0")),A1,IF(COUNTIF(A$1:A1,">0")=ROUNDUP(COUNTIF($A$1:$A$37,">0")/4,0),A1*(COUNTIF($A$1:$A$37,">0")/4-INT(COUNTIF($A$1:$A$37,">0")/4)),""))

これをＢ列に貼り付けてフィルしたものが添付図です。
あとはこのＢ列に対してＳＵＭしてやれば合計がでます。

マクロを使えばあるいは・・なんですが・・・

すいません、賢人の登場をお待ち下さいませ。

お礼

ありがとうございました。