エクセル OFFSET関数での行数指定の方法

質問

EXCELでA2からE6の5X5のテーブルに数値が入っていた時に、50以上の数が１つでも含まれる行の数カウントするという質問（実際にはもう少し複雑な質問でしたが本質だけに絞りました）を以前にした時に、「=SUM(IF(COUNTIF(OFFSET(A1:E1,{1,2,3,4,5},0),">=50")>0,1,0)) 」　という回答をいただきました。ここでOFFSET(A1:E1,{1,2,3,4,5},0)という表記がありますが、これはテーブルが5行の時には良いのですが、テーブルを縦方向に増やしていった時に対応ができません。例えばテーブルの行数（可変）がF1セルに入っていたとして、{}部分を置き換える表記はあるのでしょうか。

また、色々Webで探したのですが、そもそもOFFSET関数の中でで{}で複数行を指定（というか順番に演算していく？）するというものが見当りませんでした。どこか参考にするサイトはありますでしょうか。

回答No.6

bunjii · ベストアンサー

>A君～E君の5×5の行列にF列として1列×5行の有効無効フラグ列を追加して、そこの対応する行のセルが1である行の数のみをカウントするというようにする必要がある事がわかりました。
その条件は質問の文言の下記要件と食い違いがあります。
「例えばテーブルの行数（可変）がF1セルに入っていたとして、{}部分を置き換える表記はあるのでしょうか。」
COUNTIF(OFFSET(A1:E1,{1,2,3,4,5},0),">=50")の結果は5列×1行の配列値になります。
従って、F2:F6のフラグ（0と1の数列？）は1列×5行なので1つのCOUNTIFS関数で纏めることはできません。
SUM関数の配列内でIF(COUNTIF(OFFSET([省略]),[条件])>0,1,0)の戻り値とTRANSPOSE(F2:F6)の積を求めれば良いでしょう。
=SUM(IF(COUNTIF(OFFSET(A1:E1,TRANSPOSE(ROW(A1:A5)),0),">=50")>0,1,0)*TRANSPOSE(F2:F6))
数式の確定時にShift+Ctrl+Enterの打鍵が必要です。
添付画像は5列×10行を対象に50以上の値でF列のFlagはA列からE列に10未満が無い行に1を立てたものです。
解1=SUM(IF(COUNTIF(OFFSET(A1:E1,TRANSPOSE(ROW(A1:A10)),0),">=50")>0,1,0)*TRANSPOSE(F2:F11))
解2=SUM((MMULT(INDEX((A2:E11>=50)*1,0),TRANSPOSE(COLUMN(A1:E1)/COLUMN(A1:E1)))>0)*(F2:F11))
解3=SUM(IF(COUNTIFS(OFFSET(A1:E1,TRANSPOSE(ROW(A1:A10)),0),">=50")>0,1,0)*TRANSPOSE(F2:F11))
解4=SUMPRODUCT((MMULT(INDEX((A2:E11>=50)*1,0),ROW(A2:A6)/ROW(A2:A6))>0)*F2:F11)
解1、解2、解3の確定時にはShift+Ctrl+Enterキーの打鍵が必要です。
解4のみEnterキーで確定可能です。

お礼

BUNJIさん　ありがとうございます。サンプルを5×10にしてもらったので式の意味の取り違えを排除できました。私の5×5サンプルは自分で分かり難くしていましたね。解4はエレガントですね。まだ理解しきっていない所もあるのでこの式で色々とstudyします。丁寧な説明、ありがとうございました。

回答No.5

Chiquilin

> 今回はOFFSET関数を使った別の人の回答の方法の一般解を知りたくて
配列計算に理解がない人はそれしか思いつかないのかもしれませんが この手の
計算に OFFSETや INDIRECTを組み込むのは止めた方がいいと思います。

回答No.4

bunjii

>例えばテーブルの行数（可変）がF1セルに入っていたとして、{}部分を置き換える表記はあるのでしょうか。
「行数がF1セルに」という考え方に拘ると更に分かり難い論理を組み立てなければならなくなります。
{1,2,3,4,5}は1行×5列の数列を表しています。
セル番地から導くと COLUMN(A1:E1) → {1,2,3,4,5} となりますので、7行分にするためには COLUMN(A1:G1) → {1,2,3,4,5,6,7} のようにしなければなりません。
代案といて次のような案がえ方をする分かり易いかもしれません。
TRANSPOSE(ROW(A1:A7)) → TRANSPOSE({1;2;3;4;5;6;7}) → {1,2,3,4,5,6,7}
TRANSPOSE関数は行と列を入れ替えるためのもので行数を増減するときに引数の列数を増減する場合に考え易くするために使えます。
元の質問である「A2からE6の5X5のテーブルに数値が入っていた時に、50以上の数が１つでも含まれる行の数カウントする」に対して別解の数式を提言しましたが、それは参考にされたのでしょうか？
=SUMPRODUCT((MMULT(INDEX((A2:E6>=50)*1,0),ROW(A2:A6)/ROW(A2:A6))>0)*1)
この数式では中間の配列値が見えるので分かり易いと思います。
比較対象の行数が増減したときはINDEX関数で前処理した配列数の範囲を変更するだけで良いことになります。
但し、列数が増減したときはMMULT関数の第2引数である数列 ROW(A2:A6)/ROW(A2:A6) の範囲も変更しなければなりません。これは今回の質問で固定配列値の置き換えと類似する疑問点になるかも知れません。
しかし、OFFSET関数を含む数式では計算途中が「可変」となって様子が分からないので内包する関数での中間結果が見える数式の方が理解し易くなると思います。

お礼

詳しいご説明、ありがとうございます。

前回質問ではbunjiさんのSUMPRODUCT(MMULT())方式でうまく言ったのでBestアンサーにさせていただきました。一番わかりやすい数式でした。ただ質問のあとで、A君～E君の5×5の行列にF列として1列×5行の有効無効フラグ列を追加して、そこの対応する行のセルが1である行の数のみをカウントするというようにする必要がある事がわかりました。この時にSUM(IF(COUNTIFS())方式のほうが複数条件を並べられるので良いのかなと思ってこの質問をしたのです。

しかしながら、みなさんから回答はもらったのですが、COUNTIFS関数を使って
｛=SUM(IF(COUNTIFS(OFFSET(A1:E1,ROW(A1:A5),0),">=50",OFFSET(F1,ROW(A1:A5),0),1)>0,1,0))｝とやっても＃VALUE!となってしまう事がわかり、結局うまく行っていません。

SUMPRODUCT(MMULT())系のほうで同じような事（複数の条件をつける）はできますでしょうか。

なにかだらだらした追加質問になってしまい申し訳ありません。

回答No.3

Chiquilin

前回 最後に回答しておいたのに。

=SUM(IF(MMULT(N($A$1:$E$★>=$A12),{1;1;1;1;1}),1))

★のところを多めにとっておけばいいです。

お礼

前回はありがとうございました。前回のchiquilinさんの回答でこの方法で出来るのはわかったのですが、今回はOFFSET関数を使った別の人の回答の方法の一般解を知りたくて質問しました。

回答No.2

ushi2015

こんにちは
No1さんの式をお借りして、
=SUM(IF(COUNTIF(OFFSET(A1:E1,ROW(INDIRECT("A1:E"&$F$1)),0),">=50")>0,1,0))
と入力してCtrl+Shift+Enterで確定で。

お礼

ありがとうございます。

回答No.1

mshr1962

配列数式（数式入力後にCtrl+Shift+Enterで確定）になりますけど。。。
=SUM(IF(COUNTIF(OFFSET(A1:E1,ROW(A1:E5),0),">=50")>0,1,0))

お礼

ありがとうございました。ROW(A1:E5)は、ROW(A1:A5)でも同じなのですね。