[[20040522200408]]　『３行毎にあるセルの値を合計』（Taka）　ページの最後に飛ぶ

『３行毎にあるセルの値を合計』（Taka）

初めまして、教えていただきたいことがあります。
以前にも似たような質問があったかと思いますが、列ではなく行方向に３つおきのセルの値を
合計したいのですが。

現在は、例えばA1+A4+A7+A10+A13・・・と言った感じで希望のセルを１つずつ足していって
いますが、余りにも長くなってしまい、式が長すぎますと言ったようなエラーメッセージが
出てしまいます。

何か良い関数はないでしょうか。

Excelは、2002と2000です。
OSが、2000とXPです。

MOD 関数を使ってはどうでしょうか?

  =SUMPRODUCT(A1:A300*(MOD(ROW(1:300),ア)=イ))

  A1:A300 は集計範囲、したがって ROW のカッコ内は これに対応して 1:300
  3行ごとの集計では ア は 3、イ は 0,1,2
  4行ごとの集計では ア は 4、イ は 0,1,2,3

(列方向の場合の例)

  =SUMPRODUCT(A1:AA1*(MOD(COLUMN(A:AA),ア)=イ))

  A1:AA1 は集計範囲、したがって COLUMN のカッコ内は これに対応して A:AA
  3列ごとの集計では ア は 3、イ は 0,1,2
  4列ごとの集計では ア は 4、イ は 0,1,2,3

  これだと何行/列ごとでも　また 行/列が増えても問題ないかと思いますが。 
　(q9)

ありがとうございます、ｑ９さん。
上手く計算できました。
社内の人間に頼まれ棚卸の集計表を作っていたのですが、その部分で止まって
いたので助ったのですが、他の場所で確認し理解した上で改めて入力してみた
のですが、VALUEとエラーメッセージが帰ってきたので表の確認をしてみた
ところ、数字だけでなく文字が含まれている事がわかりました。
文字が入っているセルのみを回避して、同様の計算をするような関数または
そう言った内容のユーザー定義を作る方法はないでしょうか。
ついでと言っては申し訳ないのですが、教えていただけませんでしょうか。

 横から失礼します
 ユーザー関数やったらこんな塩梅になりますワ。
 下のコードを標準モジュールをコピペして
 トータルを出したいせるに　=taka(a1:a300)といった具合に範囲を入力してくだはい。
         （弥太郎）

 '-------------------------
 Function taka(ByVal data As Variant)
    Dim i As Long, totl As Long

    Set tbl = Range(data.Address)
        For i = 1 To tbl.Rows.Count Step 3
            If Application.WorksheetFunction.IsNumber(tbl.Cells(i, 1)) Then
                totl = totl + tbl.Cells(i, 1)
            End If
        Next i
    taka = totl
    Set tbl = Nothing

 End Function

すみませんでした、文字のことを加味するのであれば数式を次のようにして
配列入力(入力時に ctrl+shift+enter) すると解決します。

   =SUMPRODUCT(A1:A300*(MOD(ROW(1:300),ア)=イ)) のところを

   =SUM(IF(ISNUMBER(A1:A300),A1:A300,0)*(MOD(ROW(1:300),ア)=イ))
　　　ctrl + shift + enter で入力
(q9)

弥太郎さん、モジュールの件ありがとうございました。
この内容で、計算したい行数を今の３行おきから２行おきや、４行おき等に変更
したい場合は、Step 3をStep 4やStep 2とこの部分の数字だけ変えていけば良かった
のでしょうか？

q9さん、追記ありがとうございます。
ただ、ctrl + shift + enter で入力がいまいち理解できません。
ctrl + shift + enterを押したままで、=SUM(IF・・・と入力していくのでしょうか？
押したままだと、セルに文字が入っていかないので．．．
笑われてしまうかもしれませんが、教えていただけますでしょうか。
御願いします。

 勿論Step を変更すればOKですけど、
 もし頻繁変更するようだったらいちいちマクロを触るん面倒でっしゃろ。
 下のコードやったら=taka(a1:a300,4)とすればstep が４にa300,3)ならStep　が３に
 なりますけどなぁ。
 そのトータルがどの分（Step数）か分かりまっか？
 どっかに記述しとかないけまへんなぁ。
 　　　　（弥太郎）
 '-------------------
 Function taka(ByVal data As Variant, ByVal num As Integer)
    Dim i As Long, totl As Long

    Set tbl = Range(data.Address)
        For i = 1 To tbl.Rows.Count Step num
            If Application.WorksheetFunction.IsNumber(tbl.Cells(i, 1)) Then
                totl = totl + tbl.Cells(i, 1)
            End If
        Next i
    taka = totl
    Set tbl = Nothing

 End Function

   セルに式を入力するとき、通常は 式 enter とキーを押しますが 
   配列式入力では  式 ctrl shift enter の順にキーを押します。

　　(そうするとセル内は {=SUM(IF(ISNUMBER(A1:A300),A1:A300,0)*(MOD(ROW(1:300),ア)=イ))} のような表示になると思います。)

  説明がつたなくてごめんなさい｡
  (q9)

弥太郎さん、q9さん、度々すみません。
早速に試してみたのですが、結果の合計数がいまいち上手く合いません。
例えば、Y4のセルに2と入っており、そこから3つ下がったセルに5と言った感じで
Y560まで続いているものを合計し、別表に表示させると言ったものなのですが。
実際の表のイメージを書いてみたのですが、上手く表示されなかったのでこんな風に
書いてみました。
理解力が無くてすみません、宜しく御願いします。

次の表をもとに説明します(基本的には前回の説明と変わっていませんが、式のコピー貼り付け時を考慮して見ました)。

  データの行範囲は 3:560 としました。列 Y Z AA それぞれの 3行ごとの計を求めめす。

Y561 に次の式を『配列数式』として入れ、これを Y561:AA563 にコピー・貼り付けをします。

        (列 Y Z AA に自動的に対応します)。
                                                           この 3 は 3行ごとの計を意味します。
                                                             ↓ 5行ごとの計の場合は 5 とします。
  =SUM(IF(ISNUMBER(Y$3:Y$560),Y$3:Y$560,0)*(MOD(ROW($3:$560),3)=ROW()-ROW($561:$561))) に続けて
  ctrl と shift を同時に押したままで enter を押します、これで 配列数式になります。

            Y    Z   AA
    1
    2
  ----------------------------------------
    3            5   12
    4       2    3    8
    5            7    2
  - - - - - - - - - - - - - - - - - - - - 
    6            2    4
    7       5    8    3
    8           11   AAA
  - - - - - - - - - - - - - - - - - - - - 
    9

  556
  557
  - - - - - - - - - - - - - - - - - - - - 
  558            4   BBB
  559            6
  560            5    7
  ----------------------------------------
  561       0   11   16
  562       7   17   11
  563       0   23    9

上記 Y561:AA563 にはそれぞれ以下の計が計算されるはずです(文字は対象になりません)。

   　[Y561:Y3,Y6,----,Y558の計]  [Z561:Z3,Z6,----,Z558の計]  [AA561:AA3,AA6,----,AA558の計]
     [Y562:Y4,Y7,----,Y559の計]  [Z562:Z4,Z7,----,Z559の計]  [AA562:AA4,AA7,----,AA559の計]
     [Y563:Y5,Y8,----,Y560の計]  [Z563:Z5,Z8,----,Z560の計]  [AA563:AA5,AA8,----,AA560の計]

うまく説明できませんが、これで特に問題ないはずです･･･････お試しください。

  (q9)

 ？？？？
 どうしてでせう？
 どっちのコードでッか？
 上のコードやったら=taka(y4:y560)
 下のコードやったら=taka(y4:y560,3)
 で、ちゃんと作業してくれまっせ。
 非表示とか、結合セルなんかおまへんわなぁ。
 そうでなかったら絶対に計算してくれますワ。
 　　　ほな...（弥太郎）

コメント返信：

[ 一覧(最新更新順) ]

[[20040522200408]] 『３行毎にあるセルの値を合計』（Taka） ページの最後に飛ぶ

[[20040522200408]]　『３行毎にあるセルの値を合計』（Taka）　ページの最後に飛ぶ