Excel関数式についてご教授お願いします

Question

お世話になります。

自分では表現したい関数式が思いつかなかったため相談させていただきました。

ご指導の方よろしくお願いします。

A23~A25の最大値がA20~A22の最大値以下＆A26~A28の最大値以下ならA23~A25の最大値とする、そうでなければ3とする。

上記の式は   

①　=IF(AND(MAX(A23:A25)>=MAX(A20:A22),MAX(A23:A25)>=MAX(A26:A28)),MAX(A23:A25),3) 　　　

となりますが、

この式①をB23~B25にそれぞれ入力してから、B23~B25を選択→下にオートフィルすると、B26~B28は

②　=IF(AND(MAX(A26:A28)>=MAX(A23:D25),MAX(A26:A28)>=MAX(A29:A31)),MAX(A26:A28),3)

がそれぞれ入力され、B23~B25には3行同じ式①、B26~B28にもB23~B25を選択してオートフィルした式②が3行それぞれに入力され、以降下にずっとオートフィルしていけば同じように3行ずつ同じ式が続いていきますが、

これを例えばB1セルに3と入力してB1=3の時は、

B23~B25に同じ式をそれぞれ入力するのではなく、B23だけに式(仮にX)を入力してB23だけを選択してから下にオートフィルしていけば、

上記と同じようなことが出来る式Xは関数式で表現可能でしょうか？

(例えばB1セルが4ならB23~B26、B27~B30、B31~B34のように4行ずつ同じ式が続いていくようにする。）

自分ではなかなかいい方法が思い浮かばず相談させていただいたのですが、

もし可能であればご教授いただければ幸いです。

よろしくお願い致します。

Answer 1

B1セルに入力された数字の行数だけ同じ範囲の数式が入力できるようにするには以下のようなOFFSET関数に変更します。

元の数式：MAX(A23:A25)

変更後の数式：MAX(OFFSET($A$23,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1))

すなわち、提示された数式全体の数式を以下のような数式に変更します(オートフィルコピー）。

=IF(AND(MAX(OFFSET($A$23,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1))>=MAX(OFFSET($A$20,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1)),MAX(OFFSET($A$23,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1))>=MAX(OFFSET($A$26,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1))),MAX(OFFSET($A$23,$B$1*INT((ROW(1:1)-1)/$B$1),0,$B$1,1)),3)

Answer 2

こんにちは。

どうしても気になったもので、横合いから失礼します。

ご希望の動作の確認からで恐縮ですが、

• A 列の数値を 3つのグループに分け、そのグループの計算上の行数を B1 セルの値によって可変させ、グループ単位の比較結果を B1 セルの値の行数分 B 列に表示させる。

ということではなく、

• A 列の数値を 3つのグループに分け、そのグループの計算上の行数は 3行ずつで固定しておき、グループ単位の比較結果を B1 セルの値の行数分 B 列に表示させる。

ということでしょうか？。

前回（5月13日）の返信を拝見するまでは、上段のような動作をする数式を作成し検証していたのですが、下段のような動作をする数式を改めて考えてみました。一例です。

・数式例１

=IF(COUNT(OFFSET(A$26,INT((ROW(A1)-1)/B$1)*3,,3,))=0,"",IF(MAX(OFFSET(A$23,INT((ROW(A1)-1)/B$1)*3,,3,))>=MAX(MAX(OFFSET(A$20,INT((ROW(A1)-1)/B$1)*3,,3,)),MAX(OFFSET(A$26,INT((ROW(A1)-1)/B$1)*3,,3,))),MAX(OFFSET(A$23,INT((ROW(A1)-1)/B$1)*3,,3,)),3))

・数式例２

=IF(INDIRECT("A"&ROW())="","",IF(MAX(OFFSET(A$23,INT((ROW(A1)-1)/B$1)*3,,3,))>=MAX(MAX(OFFSET(A$20,INT((ROW(A1)-1)/B$1)*3,,3,)),MAX(OFFSET(A$26,INT((ROW(A1)-1)/B$1)*3,,3,))),MAX(OFFSET(A$23,INT((ROW(A1)-1)/B$1)*3,,3,)),3))

どちらの数式も基本的な動作は同じです。

違うのは、A 列に比較すべき数値が無くなった時の処理です。

数式例１は、A 列で比較対象の 3つのグループの内の 3つ目のグループが見つからない場合に空白を表示します。

数式例２は、A 列のデータが空白の場合に空白を表示します。

両方の数式を適用した具体例は下図のようになります。

※ A 列と行位置を合わせるため、数式は B 列、C 列共に 20行目に入れ、これを下方向に必要なだけ（この場合は適当に 50行目まで）コピーしています。（これらの数式は、23行目から入れ始めても同様の結果を得られます。）

※ 2つの数式それぞれで表示の仕方に特徴がありますので、ご自身の環境でお試しになってみてください。

尚、前回（5月13日）の返信で提示されている A 列の数値を使い、B1=4 として 23行目から数式を入れた結果は下図のようになります。（念のため記載。）

ご参考になれば幸いです。

Answer 3

オートフィルでは無理があると思います。最初の３行だけ作ってしまってから３行分をコピペぐらいしか思いつきませんが。

Answer 4

私の説明不足ですね、すみません。

$を使って固定したいわけではなく、本来3つのセルB23~B25を選択→下にオートフィルすれば、

下図のように3セルずつ同じ式が続いていくことになりますが、

B23　　=IF(AND(MAX(A23:A25)>=MAX(A20:A22),MAX(A23:A25)>=MAX(A26:A28)),MAX(A23:A25),3)
B24 　   =IF(AND(MAX(A23:A25)>=MAX(A20:A22),MAX(A23:A25)>=MAX(A26:A28)),MAX(A23:A25),3)
B25  　  =IF(AND(MAX(A23:A25)>=MAX(A20:A22),MAX(A23:A25)>=MAX(A26:A28)),MAX(A23:A25),3)
B27　　=IF(AND(MAX(A26:A28)>=MAX(A23:A25),MAX(A26:A28)>=MAX(A29:A31)),MAX(A26:A28),3)
B28　　=IF(AND(MAX(A26:A28)>=MAX(A23:A25),MAX(A26:A28)>=MAX(A29:A31)),MAX(A26:A28),3)
B29　　=IF(AND(MAX(A26:A28)>=MAX(A23:A25),MAX(A26:A28)>=MAX(A29:A31)),MAX(A26:A28),3)
B30　　=IF(AND(MAX(A29:A31)>=MAX(A26:A28),MAX(A29:A31)>=MAX(A32:A34)),MAX(A29:A31),3)
B31　　=IF(AND(MAX(A29:A31)>=MAX(A26:A28),MAX(A29:A31)>=MAX(A32:A34)),MAX(A29:A31),3)
B32　　=IF(AND(MAX(A29:A31)>=MAX(A26:A28),MAX(A29:A31)>=MAX(A32:A34)),MAX(A29:A31),3)

これと同じことをB23だけを選択→下にオートフィルすることで実現できないかなということです。

さらに先ほどの場合はB23~B25のように3セルずつでしたが、これを4セルずつ（B23~26）、5セルずつ（B23~27）と変化させれるようにしたいのですが、それをB1に数字を入力してB1を参照することで変化させれるようにしたいのです。

このようなことは実際関数式で可能なのでしょうか？

B1　　3　(B1の数字を4にした場合はB23~26を選択して下にオートフィルしていくのと同じようにしたい)
B23　　新しい式（B1=3の時、B23を選択→下にオートフィルするだけで上図と同じようにB23~25に同じ式が入力されるようにしたい)
B25
B26
B27
B28

Answer 5

セルの一部を固定した状態で関数式を作成したいのでしょうか？固定したいセルを”＄”で囲む（例えば、$A$1では完全固定、$A1では横方向のみ固定、A$1では縦方向のみ固定）方法が有りますが。