やりたいことは、複数のデータの内、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列だけですといいのですが、
実際には、72列あります(添付の図は、そのうちの3列分です)。
また、それぞれの列で、値が0以上のセルは、それぞれ異なります。
なにか、良い方法はないでしょうか?
教えてください。
よろしくお願いします。
No.7ベストアンサー
- 回答日時:
念のための確認ですが、四分位数なら 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
- 回答日時:
>理解できたのは、D1の関数だけでしたが…。
難しすぎて、すみません。なかなか全てを理解してもらうには、知っていただかないといけない要素が多くてたいへんかもしれませんが、回答を読んでいて、もしも何か追加のご質問でもありましたら、こちらで分かることは何でもお答えします。こういう QA サイトの本当の使い方は質問・回答なのであって、作業を人に手伝ってもらうことではないのですから…。
『もしも何か追加のご質問でもありましたら、こちらで分かることは何でもお答えします。』
あたたかいお言葉、心にしみました。
『こういう QA サイトの本当の使い方は質問・回答なのであって、作業を人に手伝ってもらうことではないのですから…。』
理解できるまで勉強して、それでもわからなかったら、「自分の理解を深めるために」QAサイトを頼ろうと思います。
うまく文字で表現できませんが、大きな何かをいただいた気がします。
ありがとうございました。
No.8
- 回答日時:
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
- 回答日時:
#5さんの式に、なるほどと感心しつつ。
とりあえず「解決したのかしていないのか」
あるいは「回答を理解できたのか出来なかったのか」
教えて頂けませんか?
実は「お礼」にはあまりこだわっておりません。
質問者さんが「質問をどのように解決したか」の方が気になります。
そんなわけで、マクロ編です。
例えば、
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
これを走らせると、40行目に結果を表示します。
ユーザー定義関数にすると
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個分のセルの合計を出したいです。
の「4」です。5でも10でも指定できます。
多分、もっと良い書き方はあるはずですが、参考までに。
表面的ですが、おかげさまで解決しました。
理解のほどは、と言いますと、ほぼ困難です。
(私が、マクロを日常的に使えていないせいです。)
具体的には、
『そんなわけで、マクロ編です。
例えば、~これを走らせると、40行目に結果を表示します。』
の部分は、コピーペーストで、解を得ることができました。
『ユーザー定義関数にすると~5でも10でも指定できます。』
の部分は、コピーペーストすらできませんでした。
(私自信、どこがどう理解できているのかもよくわかっていないせいです。)
少しずつでも理解しないと、自立に結びつかないです。
がんばります!
ご教授ありがとうございました。
No.5
- 回答日時:
簡単のためセルを分けて計算しますが、その気になれば一個に詰め込んでも出来ます。
お勧めはしませんが。A38に
=IF(A40,IFERROR(SUM(OFFSET(A1,A39-1,0,INT(A40),1)),0)+INDEX(A1:A37,A39+A40)*MOD(A40,1),"")
A39に
=IF(A40,MIN(IF(A1:A37>0,ROW(A1:A37))),"")
必ずコントロールキーとシフトキーを押しながらEnterで入力
A40に
=COUNTIF(A1:A37,">0")/4
#補足
ゼロより大きい数字セルは「必ず連続している」とします
#参考
ゼロより大きい数字セルが4個未満しかなかった場合の処置がお話をちょっとややこしくしています
『A38に
=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は、私にとってなじみのない関数で、理解するのに時間がかかりそうですが、これらの関数が理解できるよう、がんばってみます。
『A39に
=IF(A40,MIN(IF(A1:A37>0,ROW(A1:A37))),"")
必ずコントロールキーとシフトキーを押しながらEnterで入力』
この部分の注意書きの意味を理解するのは、時間がかかりそうです。ROW関数の理解も時間がかかりそうです。
『A40に
=COUNTIF(A1:A37,">0")/4』
この関数は、すぐに理解できました。
私の理解の程度は、上記のような状況です。
そのため、A40に出てきた値が5.25となりました。
お示しいただいたご回答を、活用できるよう、時間をかけてみます。
また、補足や参考を加筆いただき、ありがとうございました。
加筆していただいた内容が、お示しいただいた関数とどのように関係しているのか理解するのには、時間がかかりそうですが、一つずつ、解決したいです。
ありがとうございました。
No.4
- 回答日時:
No.3です!
たびたびごめんなさい。
前回のコードで1行訂正してください。
最後の方の
>Do Until Cells(n, j) > 0
を
>Do Until wS1.Cells(n, j) > 0
のようにしてください。
※ Sheetを指定していなかったので、Sheet2がアクティブな状態でマクロを実行すると
エラーになってしまいます。
尚、前回書き忘れたのですが、Sheet1の1行目で最終列を取得していますので、
元データは1行目からあり、何らかのデータが入っているという前提のコードです。m(_ _)m
No.3
- 回答日時:
こんばんは!
何とか関数で!と頑張ってみたのですが・・・ギブアップです。
>0以上の値を示すいくつかのセル・・・
とありますが、画像を拝見すると「0より大きい」セルのようですので、
0より大きいセルを対象としています。
VBAでやってみました。
Sheet1(お示しの画像)のデータをSheet2の1行目に表示するようにしています。
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.1
- 回答日時:
すいません、作業列を使うやり方しか思いつかなかった上に、
非常に効率が悪い式になってしまいました。
でも、なんとなく勿体無いので、
=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)),""))
これをB列に貼り付けてフィルしたものが添付図です。
あとはこのB列に対してSUMしてやれば合計がでます。
マクロを使えばあるいは・・なんですが・・・
すいません、賢人の登場をお待ち下さいませ。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) VBA オリジナル関数で選択セルの合計を作成したい 3 2023/03/19 19:45
- Excel(エクセル) Excel2019 列と列(2列)の数値の重複を調べたい 1 2023/05/11 13:35
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Excel(エクセル) エクセル 自動計算 1 2023/01/30 13:28
- Excel(エクセル) エクセル関数についてお教えください 3 2023/07/24 12:33
- Excel(エクセル) エクセル VBAでセル内容を別の列の最下行に転記したい 2 2022/11/29 08:47
- Excel(エクセル) 表計算ソフトcalcにおいて、1時間10分を1.1と表記する方法とそれらを集計する方法は? 4 2022/04/06 16:54
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) Excel vbaについて知恵もしくは、コード教えて下さいm(__)m ① 表にあるデータをコピー、 2 2022/09/01 23:57
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで日付の入ったセルの...
-
エクセルでセルに「氏名を入力...
-
Excel ウインドウ枠の固定をす...
-
Excelファイルの「数式」タブ→...
-
関数を教えて下さい。
-
スマートな関数を教えて下さい。
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelのif関数で文字が見えなく...
-
Excel 2019 のピボットテーブル...
-
Excel 2019 は、SPILL機能があ...
-
各ページの1番上の表示について
-
写真のコピー
-
条件付き書式を教えてください
-
INDEX関数やMATCH関数で値を取...
-
エクセル グラフ軸について
-
excelの不要な行の削除ができな...
-
Excelで行をコピー、同じ行内に...
-
エクセル関数に詳しい方、教え...
-
Excel 改ページの文字色の変更...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報