Mod（row）***

質問

=SUM(IF(MOD(ROW(A1:A20),4)=MOD(ROW(A3),4),A1:A20))

と、ネットに書いてある通り入力しても0になるか最後に,””を入れた場合、Valueになってしまいます。

理解しようと簡単な数字でやっているのですが計算ができません。

最初はMod、Row関数の意味から調べました。

昔、会社の技師が入力した関数を噛み砕こうとしています。

技師の入力したものは。範囲が200ほどあり、4行ごとの1行目の合計、別に2行目の合計を作成されています。

時々、おかしくなったから直してと言われることがあり、取り急ぎコピペで事なきを得ましたが、

理解したいと思っています。

ただ、実際に入ってる場所で同じ形で同じ関数を入れて作成すると、あるセルはちゃんと計算できるのですが、

あるセルだけは39,248,338分足された値が出てきます。

まったく同じ数式なのでなぜそうなるのかわかりません。

どなたかお詳しい方よろしくお願い致します。

回答No.5

kkkkkm · ベストアンサー

No1のお礼に対しての返信です。
配列数式にしない
=SUM(IF(MOD(ROW(A1:A20),4)=MOD(ROW(A3),4),A1:A20))
だと
MOD(ROW(A1:A20),4)
は
MOD(ROW(A1),4)
と同じになり範囲を指定したことにならないのです。
ROW(A1)とROW(A3)が違い計算結果が真とはならないのでSUMでは何も足されずに0になります。
> あるセルだけは39,248,338分足された値が出てきます
これは
MOD(ROW(A1:A20),4)
のセル範囲指定の最初のセル番地と
=MOD(ROW(A3),4)
のセル番地が一致したときにすべてのセルが足されてしまった結果だと思います。
たとえば
MOD(ROW(A1:A20),4)=MOD(ROW(A1),4)
となったとき
ROW(A1)とROW(A1)が同じなので計算結果が真となりSUMでは最後に指定されたセル範囲が全て足されます。
> でもそうすることで何が変わって計算ができるようになるのですか？
=IF(MOD(ROW(A1),4)=MOD(ROW($A$3),4),A1,"")
たとえばC1に上記を入力してそれをC20までコピーして
で合計を出したいセルに
=SUM(C1:C20)
と入力する。
ということを一つの式と一つのセルでできるようになるということです。

お礼

ご丁寧な説明をありがとうございます。
0になったり付加される事は理解したと思います。でも、これらの事を考える方がいらっしゃる事に凄いですと凡人の私には思うだけです。
少しづつですが頑張って理解したいです。出来たら便利ですよね。ありがとうございました。

回答No.4

imogasi

解説・説明用に、参考に書いてみました。
まだるっこしいようなら無視して。
例データ　A1:A20　下記
B列はB1:B20を範囲指定し、B1をアクチブにして=IF(MOD(ROW(A1:A20),4)=3,1,0）と入れてSHIFT+CTRL+ENTERの３キーを同時押し。
原作者がMOD(ROW(A3),4）と書いた意図はよくわからない。３で済むように思った。
C列はC1:C20を範囲指定して、C1をアクチブにして、
=(A1:A20)*(B1:B20）と入れてSHIFT+CTRL+ENTERの３キーを同時押し。
1 0 0
2 0 0
3 1 3
4 0 0
5 0 0
6 0 0
7 1 7
8 0 0
9 0 0
10 0 0
11 1 11
12 0 0
13 0 0
14 0 0
15 1 15
16 0 0
17 0 0
18 0 0
19 1 19
20 0 0
最後にC1:C20のSUMをどこかのセルに出す。
以上を１つの式で済ましたものが質問の式。普通は、B,C列を使わず、質問に出ている１つの式で済ます。しかし配列数式という仕組みを使うので、知らない人は、面くらうかも。
結果の見てくれが、数式バーを見ないと配列数式ということがわかりにくい。
ROW(A1:A20）のような、書き方から配列数式かなと気づく。普通の使い方では、ROW(A1)と言うような書き方をする。「セル番地：セル番地」が来るのはSUM関数などの、複数数値ーー＞１数値（代表値）写像の、集合関数ですから。

お礼

すみません。
まだ噛み砕けていません。
が説明ありがとうございます。
よく勉強したいと思います。
ありがとうございました。

回答No.3

mt2015

誤記訂正
誤：MOD(ROW(A1:A20),4)　これはA1:A20の各列数を4で割った余りですので……
正：MOD(ROW(A1:A20),4)　これはA1:A20の各行数を4で割った余りですので……

お礼

ありがとうございます。
列と行はいつも悩みます。

回答No.2

mt2015

これ、「Ctrl」+「Shift」+「Enter」で配列数式にする式ですね。
A3、A7、A11、A15、A19　と、3行目から始めて3つ置きのセルの合計値を求めています。

ザックリ説明すると、
まず、MOD(ROW(A3),4) これは3です。
MOD(ROW(A1:A20),4)　これはA1:A20の各列数を4で割った余りですので結果は、1,2,3,0,1,2,3,0,……の繰り返しになります。

で、この繰り返しの値が3に等しい時のA列の値を合計しています。
解りにくいですよね。

お礼

はい、関数の意味は理解できたのですが、どうしてそうなるかは理解できていません。外国語のように無理やり納得している状態です。

CtrlとShiftを押してやればできました。
どうしてなのかネットにも載っていますでしょうか？
ありがとうございました。

回答No.1

kkkkkm

多分配列数式だと思います。
CtrlとShiftを押しながらエンターしてみてください。
見た目が以下のようになると思います。
{=SUM(IF(MOD(ROW(A1:A20),4)=MOD(ROW(A3),4),A1:A20))}
配列数式の場合、数式バーをクリックしてそのままエンターとかすると配列数式じゃなくなるのでそれでおかしくなっているのだと思います。
数式のあるところをロックしてシートの保護をしておくといいかもしれません。
また、以下のようにするとその危険性はなくなると思います。
=SUMPRODUCT((MOD(ROW(A1:A20),4)=MOD(ROW(A3),4))*(A1:A20))

お礼

ありがとうございます。
CtrlとShiftを押しながらENTER 押しても、どうすればよいかわからず、
いろいろ試してカーソルを関数に持っていってやればできました。
取り急ぎ、このやり方しか試していません。（すみません。仕事しながらなのでご理解ください）
でもそうすることで何が変わって計算ができるようになるのですか？
ありがとうございます。
お時間がありましたら回答ください。