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

Question

やりたいことは、複数のデータの内、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以上のセルは、それぞれ異なります。
なにか、良い方法はないでしょうか？
教えてください。
よろしくお願いします。

MarcoRossiItaly · Accepted Answer

念のための確認ですが、四分位数なら 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 引数もゼロになることによりエラーが発生するのを回避するための措置です。

tsubuyuki · Answer

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

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

=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)),""))

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

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

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

tsubuyuki · Answer

添付忘れました。

重ねて、すいません。

tom04 · Answer

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

＞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

tom04 · Answer

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

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

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

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

keithin · Answer

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

Ａ３８に
=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

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

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

tsubuyuki · Answer

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

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

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

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個分のセルの合計を出したいです。
の「４」です。５でも１０でも指定できます。

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

tom04 · Answer

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

MarcoRossiItaly · Answer

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

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

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

すいません、作業列を使うやり方しか思いつかなかった上に、

添付忘れました。

こんばんは！

No.３です！

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

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

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

No.3です！

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング