列を挿入しても式の一部を固定するには

Excelで列を挿入しても式の一部を固定するには
毎日発生する数値が一つの列に並んで、当日はA列です。
G列には当日を含む過去最大値を求めています。
添付の画像で、上段の3行はAからFになっています。

翌日のためA列の左に列挿入を行うと、BからGに変わります。
挿入しても、AからGに出来ませんか。

即ち、h8を=MAX($B3:G3)ではなく=MAX($A3:G3)に出来ませんか。
列番号をAに固定する方法を教えて下さい。
以上

No.5 ベストアンサー 回答者： goomania 回答日時： 2023/06/02 00:13

No.4さんの回答に

>CU3セルに
>　=MAX(OFFSET(CU3,,9-COLUMN(),1,COLUMN()-9))
という説明が登場しますが、数式を記述したセルの右側に列を挿入することがないのであれば、CU3セルに

=MAX(OFFSET(DD3,,-COLUMN(),,COLUMN()-9))･･･①

という数式でも良いように思います。

また、No.1さんの回答にINDIRECT関数が登場しますが、ADDRESS関数を使わずに書くと、CU3セルに

=MAX(INDIRECT("R"&ROW()&"C9:R"&ROW()&"C"&COLUMN()-1,0))･･･②

という数式を記述することになります。

なお、①②の数式は下方向へコピーすることを前提に作成されていますが、CU3、CU4、CU5にそれぞれ数式を書く前提ならCU3に

=MAX(INDIRECT("R3C9:R3C"&COLUMN()-1,0))･･･③

とする方法もあります。(③の数式は下方向へコピーして使用できません)

この回答へのお礼

＞CU3セルに
　=MAX(OFFSET(CU3,,9-COLUMN(),1,COLUMN()-9))
この式に関して確認させて下さい。

参照範囲はI～CT列なのにA列～CT列のように思われます。
この式にある「9-」と「-9」の9はABC...のHが9番目です。何か関係ありませんか。

お礼日時：2023/06/02 00:22

No.6 回答者： goomania 回答日時： 2023/06/02 02:24

No.5です。

本来No.4さんにお尋ねになるべき質問ですが、当方に質問されたので代わりに回答します。

>この式にある「9-」と「-9」の9はABC...のHが9番目です。
>何か関係ありませんか。

着眼点はGOODですが、残念ながらご質問者は勘違いをされているようです。「ABC...のHが9番目です。」は誤りです。正しくは「ABC...のHが8番目です」。従って、「ABC...のIが9番目です。」が正しい意味となります。

OFFSET関数の構文はOFFSET(基準,行数,列数,高さ,幅)という形で、[基準]は基準となるセルを指定する引数、[行数],[列数]参照範囲が基準からどれだけシフトするかを指定する引数、[高さ],[幅]は参照範囲の大きさを指定する引数となります。

つまり、=MAX(OFFSET(CU3,,9-COLUMN(),1,COLUMN()-9))･･･①という数式は行数指定が省略されています。この数式のOFFSET(CU3,,9-COLUMN(),1,COLUMN()-9)･･･②という部分を解説すると、数式をCU3に記述するので、このとき「COLUMN()」は「99」を意味します。ですから②はOFFSET(CU3,0,9-99,1,99-9)⇒OFFSET(CU3,0,-90,1,90)･･･③となります。

[行数],[列数]のシフトは下方向、右方向が「正」なので、「CU3から行を0行(つまり動かさず)、列を右に-90セル(つまり左に90セル)シフトしたセルから、高さ1セル分、幅90セル分を参照する」という意味になり、「CU3から行は動かさず、列を左に90セルシフトしたセル」は【I3】セル、そこから「高さ1セル分、幅90セル分参照」だと最終セルが【CT3】セルになります。

従って①は結果として=MAX(I3:CT3)と同じだということになります。

次に列を挿入した場合を考えてみましょう。
列を挿入すると、もともと数式が書かれたCU3セルはCV3セルになっているはずです。このとき「COLUMN()」は「100」です。すると②はOFFSET(CV3,0,9-100,1,100-9)⇒OFFSET(CV3,0,-91,1,91)･･･④となります。
「CV3から行は動かさず、列を左に91セルシフトしたセル」は【I3】セル、そこから「高さ1セル分、幅91セル分参照」だと最終セルが【CU3】セルになります。
つまり、①が=MAX(I3:CU3)に変化したことになり、常にI列から数式直列までの範囲で最大値を求めることができるという訳です。

この回答へのお礼

＞CU3セルに
　=MAX(OFFSET(CU3,,9-COLUMN(),1,COLUMN()-9))･･･①
この式が正しく機能することを確認しました。

ただ、その後気付きました。
参照範囲がI～CT列であるので、列挿入をI列の左側ではなく右側即ち、J列の左側に列挿入すれば、I列はずれることなく=MAX(I:CU)で済ますこと出来ること分かりました。
ご支援をありがとうございました。

お礼日時：2023/06/02 14:19

No.4 回答者： fujillin 回答日時： 2023/06/01 09:50

No2です。

投稿してから気付きましたが、No2の式だとG列の直前に「列挿入」を行うと上手くいきません。
理由は、隣のF列を基準にしてしまったためで、計算列自身を基準にして
　=MAX(OFFSET(G3,,1-COLUMN(),1,COLUMN()-1))
としておくべきところでした。

それはさておき、
＞範囲をI～CT列に変更したく思います。どうすればよいでしょうか。
計算値を表示する欄がどの列なのかによりますけれど、仮に、CU列だとするなら、
CU3セルに
　=MAX(OFFSET(CU3,,9-COLUMN(),1,COLUMN()-9))
となると思います。

No.3 回答者： banzaiA 回答日時： 2023/06/01 08:59

>式の中身を読み解く気力が失われました。

よろしくお願いします。
そう言わずに頑張りましょう。
提示された式を、理解せずに、ただcopyするだけなら、いつまでたっても上達しませんよ。ご自身で解決するよう勉強しましょう。
#2　fujilin　さんの提示された式はそれほど難解では無いと思います。
MAX関数の意味はご存じですね。
OFFSETとCOLUMNの意味を調べましょう。
たったこれだけで、質問者さんの問題は解決するのだから、
そう難しいことではないでしょう。

No.2 回答者： fujillin 回答日時： 2023/05/31 22:08

こんばんは

ご提示の添付図の最初の状態で、G3セルに
　=MAX(OFFSET(F3,,2-COLUMN(),1,COLUMN()-1))
とかでは、ダメでしょうか？

この回答へのお礼

早速のコメントありがとうございます。
この式、希望のものであること確認しました。
ただ、分かり易くA～H列を対象にしました。
実際はI～CT列です。
質問の時点では、式の中にAやHが出て来るだろうと思っていました。

しかしお示しいただいた式には出てきておりません。
範囲をI～CT列に変更したく思います。どうすればよいでしょうか。
式の中身を読み解く気力が失われました。よろしくお願いします。

お礼日時：2023/06/01 08:38

No.1 回答者： zongai 回答日時： 2023/05/31 22:06

=MAX(INDIRECT(ADDRESS(ROW(),1)&":"&ADDRESS(ROW(),COLUMN()-1)))

これで、A列から、式の入ったセルの前の列までのMAXを求めます。