エクセル数式・等間隔ごとの範囲にしたい（再）

数日前にココに質問させていただいて、回答をもらえたのですが
私の説明不足で、よい結果が得られなかったので、もう一度質問させていただきます

countif関数で範囲を等間隔にしたいです、と質問したところ

=SUMPRODUCT((A1:A1000="あ")*(MOD(ROW(A1:A1000),10)=1))
との回答をいただきました

残念ながら、都合でその数式が書かれているセルが
範囲の中に入ってしまっていて、循環になってしまいます

範囲を等間隔に出来れば循環にならないと思い質問させていただいたのですが
そこを書かなかったので、望みの数式になりませんでした

循環にならない方法で何とかなりませんでしょうか？

No.5 回答者： bunjii 回答日時： 2014/09/10 09:54

>循環にならない方法で何とかなりませんでしょうか？

一般的には範囲指定を連続したセルが対象になるようなデータの配置にします。
あなたの設問では対象範囲の中に結果を求めようとしていますので無理が生じます。
数式を入力するセルのみを除外した範囲を2分割して処理することで対応できます。
A2=SUMPRODUCT((A$1:A1="あ")*(MOD(ROW(A$1:A1),10)=1))+SUMPRODUCT((A3:A$1000="あ")*(MOD(ROW(A3:A$1000),10)=1))
この式をA12セルへコピーすると次のようになります。
A12=SUMPRODUCT((A$1:A11="あ")*(MOD(ROW(A$1:A11),10)=1))+SUMPRODUCT((A13:A$1000="あ")*(MOD(ROW(A13:A$1000),10)=1))
A2セルの式が単独であれば循環を避けられますがA12の数式がA2セルの値に影響を及ぼす可能性があると判断されて循環数式となります。
敢えてSUBSTITUTE関数を強行するには計算方法を変更すれば良いでしょう。
「ツール」→「オプション」→「計算方法」→「反復計算」にチェック→「OK」ボタンをクリック
「最大反復回数」と「変化の最大値」はデータの状況で異なる値を指定する必要があるかも知れません。

この回答へのお礼

回答有難う御座います
計算方法の変更は、したことが無いのでちょっと遠慮させていただきます

お礼日時：2014/09/10 11:18

No.4 ベストアンサー 回答者： cj_mover 回答日時： 2014/09/10 09:40

こんにちは。

お邪魔します。

> 範囲の中に入ってしまっていて、循環になってしまいます
、、、"なってしまう"というよりは、敢えて循環参照になる位置に数式を置きたい、
という話のようにも思えますが、
基本的にはやはりシートの設計を見直すことを勧めるべきなのでしょうね、、、。

それでも敢えて循環参照を回避しながら参照先と同じ列に求める結果が得られる数式を設定する為には、
飛び飛びのセル範囲を参照先に指定することになりますが、まともにやるとすると、
=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で配列数式として確定するとかでしょうか。
数式を作成する為の数式活用が重要になりますが、
これなら、参照先のセルが他のセルの挿入や切り貼り等で移動されたとしても、
トレースを崩すことがないという意味での堅牢さ、を長所として多少は評価出来るものの、
参照先を変更する等の新たな編集の困難さは大きな短所となってしまいます。
そもそも、こんな冗長な数式を求めている訳ではないですよね？

飛び飛びのセル範囲を参照先に、その飛び飛びのセル範囲の間にあるセルを参照元に、
というのは、かなり特殊な扱い方な訳ですから、対症療法的な対策で済ますのが現実的かも、です。
添付画像のようにＡ２に数式を置くのなら、
=(A1="あ")+SUMPRODUCT((A11:A1001="あ")*(MOD(ROW(A11:A1001),10)=1))
みたいにするのが状況に対する素直な対応なんだと思います。
=(A1="あ")+SUMPRODUCT((A11:A1001="あ")*(MOD(ROW(A11:A1001),10)=MOD(ROW(A11),10)))
と書くと、上部の行挿入や対象セル以外の行削除、に対応して、ほんの少しだけ保守寄りな感じになりますか、、、。
もし課題の数式を多数のセルに設定したい場合は、却って手数が増えて難しいです。

でも、これまでのやりとりを見ていると、"等間隔"というのが、
"例えば"仮の話としての１０行間隔ということだったり、（もしかして行の間隔が増減することもあるとか）
もっと編集が容易な数式を求めているとか、スマートなものを求めているとか、
そんな雰囲気を感じていたりもするのですが、、、。

少し設計の面からの対応にも触れてみますが、
今回の質問文でご提示の元の数式をそのまま活用する手立てとして、ですが、
一旦、Ａ列以外のセル（どこかテキトーな、邪魔にならない、通常見える範囲の外）
にその数式を設定してから、そのセルをコピーして、
Ａ２セルに［リンクされた図］（旧バージョンＵＩでは［図のリンク貼付け］）
として貼付けるのはどうでしょう？
表示上（見た目）は添付画像のように、そして循環参照とは無縁な形で、
求める値を配置することが出来ます。
手当てとしては最も簡単ですが、
表示するだけでいいのかどうか、
計算結果を他の数式から参照する場合は、どこに計算結果があるのか解り難いけど構わないか、
とか、これも条件次第、です。
循環参照を回避するような制限の中で難しい数式を考える、必要がないというのはメリットではあります。

最後に、私なら、という話。
循環参照の問題が無かったとしても、私ならユーザー定義関数で対応する場合が多いです。
とりあえず書いてみたので上げておきます。
特長は、一度設定してしまえば制約が少ないこと、数式の書き易さ、条件変更に伴う書換えのし易さ、です。
少しオプションを加えて、ワイルドカードで部分一致等も使えたりします。
設定の仕方他説明が必要でしたら遠慮なく訊いてください。
手順　Alt + F11 → Alt + i → Alt + m
下にあるＶＢＡコードをModule 1（大きな空のシート）に貼り付けて、
Let_UDF_Descriptionを一度だけ実行。
シート上での数式の書き方としては、Ａ２に
=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 & "　省略または１を指定した場合はすべての行を検査します")
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

この回答へのお礼

VBA初心者ですが頑張ってみます

お礼日時：2014/09/10 11:14

No.3 回答者： MackyNo1 回答日時： 2014/09/09 23:29

わざわざデータ対象範囲の中に数式を入れることはないと思うのですが、どうしてもそのような位置に数式を入力したいなら、配列定数を利用した数式にします。

具体的には以下の式を入力し、数式バーで「OFFSET($A$1,ROW($1:$100)*10-10,0)」を選択し、Ｆ９キーを押して配列定数にすれば、ご希望のデータが表示できます（1000行までのデータの場合）。

=SUMPRODUCT((OFFSET($A$1,ROW($1:$100)*10-10,0)="あ")*1)

ただし、配列定数を利用した場合は、データが変更されると自動的には対応できませんので、上記の数式をどこかのセルに入力しておき、値を変更した場合は、このセルをコピーして上記のＦ９キーの操作を行う必要があります。

この回答へのお礼

頻繁に値が変わるので無理ですね
関数式を範囲外に置くことにします
残念です

お礼日時：2014/09/10 11:10

No.2 回答者： tom04 回答日時： 2014/09/09 22:46

こんばんは！

どうしても数式の範囲内に結果を表示させたい場合、循環参照になりますので
関数では無理だと思います。

お示しの画像のような配置（A2セル）に結果を表示させたい場合
VBAになってしまいますが、一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, cnt As Long
For i = 1 To 1000 Step 10
If Cells(i, "A") = "あ" Then
cnt = cnt + 1
End If
Next i
Range("A2") = cnt
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

この回答へのお礼

VBAはさけたいです
関数式を範囲外に置くことにします
残念です

お礼日時：2014/09/10 11:09

No.1 回答者： Cupper-2 回答日時： 2014/09/09 21:23

　

　　　循環関数にならないよう、関数式を範囲外に置く

のが正解と思うんです。
循環関数にならないような関数式を示すのは無理ですから。
（だってどこに関数式を置くのか示されていませんからね）

でなければ関数式を置く場所と範囲を設定したい場所を具体的に示しましょう。

この回答へのお礼

やっぱり関数式を範囲外に置くことにします
残念です

お礼日時：2014/09/10 11:08