[[20250910102034]]　『名前の管理を利用してシンプルに？』（APM）　ページの最後に飛ぶ

『名前の管理を利用してシンプルに？』（APM）

エクセルのデータを計算する数式で、
名前で管理して式を簡略化する方法を知りました。
AVERAGEIFSで条件付きの平均を求めるのですが、
条件の一部が変わるものの多くのセルに同じ式を入力するため、
この数式を名前で管理できれば、と思っていますがうまくいきません。

そもそも私の認識が違うのか、教えていただけないでしょうか。

元の数式
=IFERROR(ROUND(AVERAGEIFS($T:$T,$S:$S,$AJ13,$AH:$AH,"<="&$AP$1,$R:$R,">"&$U$1-$AM$1),2),"")
T列にある数値を、
〇S列のデータがAJ13と同じもの
□AH列のデータがAP1以下のもの
△R列のデータがU1からAM1を引いた値より大きいもの
この3つで絞って平均を出しているのですが、
入力するセルによって〇のみ変化するため、
=IFERROR(ROUND(AVERAGEIFS($T:$T,$S:$S,$AJ13,平均条件),2),"")
このような形で名前を付けて簡略化できれば、と思っています。
実際に試すと
$AH:$AH,"<="&$AP$1,$R:$R,">"&$U$1-$AM$1
これに名前を付けることができないのですが、どうにか解決する方法はあるでしょうか。
それとも名前の管理、という機能の範疇を超えているでしょうか。

＜使用 Excel：Excel2021、使用 OS：Windows11 ＞

 =IFERROR(ROUND(INDIRECT("AVERAGEIFS($T:$T,$S:$S,$AJ13,"&平均条件),2),"")

こんなんでいかんですか？

ただし、$AH:$AH,"<="&$AP$1,$R:$R,">"&$U$1-$AM$1はどこかのセルにおいて、
そのセルに平均条件という名前をつけてください。

こちらではデータもないので確認できないですけれども。
（ゆたか） 2025/09/10(水) 12:00:03

ゆたか様
早速の回答あり瓦当ございます。
試してみた所エラー発生、IFERRORを外してみると#REF!と表示されました。

数式の検証で見てみると、条件の中の不等号が"" ""で囲まれています。
これが原因でしょうか。
""<=""&$AP$1といった感じです。
正直INDIRECTの使い方が曖昧です。
（APM） 2025/09/10(水) 12:16:46

IFERRORをつけていたらエラーでも空白になってしまうので、原因がわからなかったですね。
また、上記の式をそのまま展開すると)が一つ足らなかったかな。

検証で"が""になるのは、"をエスケープしているからで、
"を表現するのに""となっているわけなので、それは問題ないと思います。
"で囲まれた部分などで"を表現する方法です。

どちらにしても、indirectで参照すると#REFになってしまいますね。
（ゆたか） 2025/09/10(水) 14:50:31

 別のアプローチはどうでしょうか。

 S列の値だけが可変ということであれば、
 以下のような方法で、ユーザー定義関数を使ってはどうでしょう。

 標準モジュールに以下をコピーして下さい。
 'S列を指定値に限定したデータの平均値
 Function func_S(r As Range) As Variant
     Dim s As String
     s = "IFERROR(ROUND(AVERAGEIFS($T:$T,$S:$S,●,$AH:$AH,""<=""&$AP$1,$R:$R,"">""&$U$1-$AM$1),2),"""")"
     s = Replace(s, "●", r.Address(False, False))
     func_S = Application.Evaluate(s)
 End Function

 セルには
 =func_S($AJ13)
 などとします。S列が一致する値を指定する引数とするだけです。
 他の引数は既定のものが使われます。

 名は体を表すということで、もっと説明的なユーザー関数名にすることも考えられます。
 一方では、入力負荷もありますから、両者のバランスは使用者の工夫次第です。
（xyz） 2025/09/10(水) 22:27:23

 FILTER関数を使って

 条件 で名前定義 =($S:$S=$AJ13)*($AH:$AH<=&$AP$1)*($R:$R>&$U$1-$AM$1)
 =AVERAGE(FILTER($T:$T,条件))
　
 みたいな感じでどうでしょう
（´・ω・`） 2025/09/11(木) 09:04:39

 ユーザー定義を使う案に対する留意点です。（書き洩らしました）

 ・式を代入した当初は正当計算されるが、
   再計算は直接の引数である$AJ3が変更になったときだけ実行される。
 ・これを避けようとすれば、マクロの最初の行に Application.Volatile と挿入する方法がある。
   ただし、こうすれば再計算はされるが、
   元の式の引数に無関係のセルが変更されても再計算がされてしまう(非効率の元）、という難点がある。

 従って、
 現在のデータをもとに計算して、直ちにその値を固定し、
 以後再計算しないといったときに有効かもしれない。
 まあ、一言で言って"アイデア倒れ"ということかも知れない。

 ■
 （´・ω・`）さんの案をもとに、AJ13を可変にしようとすれば、
 条件はAH列とR列に関するものだけにして、
 =LET(
     t,FILTER($T:$T,条件),
     s,FILTER($S:$S,条件),
     AVERAGE(FILTER(t,s=$AJ13))
 )
 などとするとよいかもしれません（未確認です）

 ■
 尤も、冷静に考えてみると、元の式
 =IFERROR(ROUND(AVERAGEIFS($T:$T,$S:$S,$AJ13,$AH:$AH,"<="&$AP$1,$R:$R,">"&$U$1-$AM$1),2),"")
 で$T:$T,$S:$S,$AJ13だけに注目して、他は見ないことにするという脳内変換を施す、
 というのが実際的な対応かもしれません。
（xyz） 2025/09/11(木) 14:42:55

 名前の定義のときに相対参照にすると、
 その名前を使う場所によって参照位置が相対的に変化しませんか？

 LET関数は2021でも使えるようなので、名前の定義はやめて、
 LET関数で可読性の向上を図る方がいいかもしれません
（´・ω・`） 2025/09/11(木) 16:47:40

コメント返信：

[ 一覧(最新更新順) ]

[[20250910102034]] 『名前の管理を利用してシンプルに？』（APM） ページの最後に飛ぶ

[[20250910102034]]　『名前の管理を利用してシンプルに？』（APM）　ページの最後に飛ぶ