質問: エクセル数式・等間隔ごとの範囲にしたい(再) 数日前にココに質問させていただいて、回答をもらえたのですが 私の説明不足で、よい結果が得られなかったので、もう一度質問させていただきます countif関数で範囲を等間隔にしたいです、と質問したところ =sumproduct((a1:a1000="あ")*(mod(row(a1:a1000),10)=1)) との回答をいただきました 残念ながら、都合でその数式が書かれているセルが 範囲の中に入ってしまっていて、循環になってしまいます 範囲を等間隔に出来れば循環にならないと思い質問させていただいたのですが そこを書かなかったので、望みの数式になりませんでした 循環にならない方法で何とかなりませんでしょうか?
回答: こんにちは。お邪魔します。 > 範囲の中に入ってしまっていて、循環になってしまいます 、、、"なってしまう"というよりは、敢えて循環参照になる位置に数式を置きたい、 という話のようにも思えますが、 基本的にはやはりシートの設計を見直すことを勧めるべきなのでしょうね、、、。 それでも敢えて循環参照を回避しながら参照先と同じ列に求める結果が得られる数式を設定する為には、 飛び飛びのセル範囲を参照先に指定することになりますが、まともにやるとすると、 =sum(--(choose(row(1:101),a1,a11,a21,a31,a41,a51,a61,a71,a81,a91,a101,a111,a121,a131,a141,a151,a161,a171,a181,a191,a201,a211,a221,a231,a241,a251,a261,a271,a281,a291,a301,a311,a321,a331,a341,a351,a361,a371,a381,a391,a401,a411,a421,a431,a441,a451,a461,a471,a481,a491,a501,a511,a521,a531,a541,a551,a561,a571,a581,a591,a601,a611,a621,a631,a641,a651,a661,a671,a681,a691,a701,a711,a721,a731,a741,a751,a761,a771,a781,a791,a801,a811,a821,a831,a841,a851,a861,a871,a881,a891,a901,a911,a921,a931,a941,a951,a961,a971,a981,a991,a1001)="あ")) を入力して、ctrl+shift+enterで配列数式として確定するとかでしょうか。 数式を作成する為の数式活用が重要になりますが、 これなら、参照先のセルが他のセルの挿入や切り貼り等で移動されたとしても、 トレースを崩すことがないという意味での堅牢さ、を長所として多少は評価出来るものの、 参照先を変更する等の新たな編集の困難さは大きな短所となってしまいます。 そもそも、こんな冗長な数式を求めている訳ではないですよね? 飛び飛びのセル範囲を参照先に、その飛び飛びのセル範囲の間にあるセルを参照元に、 というのは、かなり特殊な扱い方な訳ですから、対症療法的な対策で済ますのが現実的かも、です。 添付画像のようにa2に数式を置くのなら、 =(a1="あ")+sumproduct((a11:a1001="あ")*(mod(row(a11:a1001),10)=1)) みたいにするのが状況に対する素直な対応なんだと思います。 =(a1="あ")+sumproduct((a11:a1001="あ")*(mod(row(a11:a1001),10)=mod(row(a11),10))) と書くと、上部の行挿入や対象セル以外の行削除、に対応して、ほんの少しだけ保守寄りな感じになりますか、、、。 もし課題の数式を多数のセルに設定したい場合は、却って手数が増えて難しいです。 でも、これまでのやりとりを見ていると、"等間隔"というのが、 "例えば"仮の話としての10行間隔ということだったり、(もしかして行の間隔が増減することもあるとか) もっと編集が容易な数式を求めているとか、スマートなものを求めているとか、 そんな雰囲気を感じていたりもするのですが、、、。 少し設計の面からの対応にも触れてみますが、 今回の質問文でご提示の元の数式をそのまま活用する手立てとして、ですが、 一旦、a列以外のセル(どこかテキトーな、邪魔にならない、通常見える範囲の外) にその数式を設定してから、そのセルをコピーして、 a2セルにリンクされた図 として貼付けるのはどうでしょう? 表示上(見た目)は添付画像のように、そして循環参照とは無縁な形で、 求める値を配置することが出来ます。 手当てとしては最も簡単ですが、 表示するだけでいいのかどうか、 計算結果を他の数式から参照する場合は、どこに計算結果があるのか解り難いけど構わないか、 とか、これも条件次第、です。 循環参照を回避するような制限の中で難しい数式を考える、必要がないというのはメリットではあります。 最後に、私なら、という話。 循環参照の問題が無かったとしても、私ならユーザー定義関数で対応する場合が多いです。 とりあえず書いてみたので上げておきます。 特長は、一度設定してしまえば制約が少ないこと、数式の書き易さ、条件変更に伴う書換えのし易さ、です。 少しオプションを加えて、ワイルドカードで部分一致等も使えたりします。 設定の仕方他説明が必要でしたら遠慮なく訊いてください。 手順 alt + f11 → alt + i → alt + m 下にあるvbaコードをmodule 1(大きな空のシート)に貼り付けて、 let_udf_descriptionを一度だけ実行。 シート上での数式の書き方としては、a2に =matchcount_rowskip("あ",a1:a1001,10) です。循環参照にはなりません。 private sub let_udf_description() application.macrooptions _ macro:="matchcount_rowskip", _ description:="検査範囲の先頭から 行間隔で指定した等間隔の行位置にある検査範囲内のセル" _ & vblf & "すべてを対象に 検査値と一致するセルの数(カウント)を返します", _ category:=14, _ argumentdescriptions:=array( _ "には カウント対象として 数値 文字列値 を指定します" _ & vblf & " 文字列値を指定する場合は" _ & vblf & " ワイルドカードとして ? * # が使用可能です", _ _ "には 検査値が入力されている【連続したセル範囲】" _ & vblf & " 【単列】を指定します", _ _ "には 検査範囲の先頭行を起点として" _ & vblf & " 【何行おきに】検査するかを指定します" _ & vblf & " 省略または1を指定した場合はすべての行を検査します") end sub public function matchcount_rowskip(byval 検査値, byval 検査範囲, optional byval 行間隔 as long = 1) dim nescadrs as string dim nrows as long dim nbottom as long dim nlastrow as long dim cnt as long dim i as long dim blnistext as boolean if ucase(typename(検査範囲)) <> "range" then matchcount_rowskip = cverr(xlerrref) exit function end if if 行間隔 < 1 then matchcount_rowskip = cverr(xlerrvalue) exit function end if nrows = 検査範囲.rows.count nbottom = 検査範囲(nrows).row nlastrow = 検査範囲(1).entirecolumn.cells(rows.count).end(xlup).row if nbottom > nlastrow then nrows = nrows - nbottom + nlastrow set 検査範囲 = 検査範囲.resize(nrows) end if blnistext = vartype(検査値) = vbstring nescadrs = application.thiscell.address if blnistext then for i = 1 to nrows step 行間隔 if 検査範囲(i, 1).address <> nescadrs then if 検査範囲(i, 1).text like 検査値 then cnt = cnt + 1 end if next i else for i = 1 to nrows step 行間隔 if 検査範囲(i, 1).address <> nescadrs then if 検査範囲(i, 1) = 検査値 then cnt = cnt + 1 end if next i end if matchcount_rowskip = cnt end function