最大値MAXを、最小値MINと（ABS?)で作りたい。

質問

最大値MAXを、最小値MINと（ABS?)で式を作りたい。

下記のデータのときに、
L123＞０のとき　　MAX(L123:L147)　　最大値は1.181787
L123＜０のとき　　MIN((L123:L147))　最小値は-2.725490です。

そこで、質問です。
下記のようにMINとABSを使い擬似最大値の式を作りました。
=MIN(ABS(L123:L147))
擬似最大値は0.029886の結果を出したいのです。
しかし、「=MIN(ABS(L123:L147))」では「0.368062 」となります。
これはABS（L123)の値です。
上記「MINとABSの擬似最大値は0.029886」 となる式を教えてください。

（L１２３からL１４７のデータは下記のとおりです。
上記「MINとABSの擬似最大値は0.029886」 となる式を教えてください。）
-0.368062 ,0.207454 ,0.085564 ,0.432480 ,1.099316 ,0.971129 ,1.181787,0.815206 ,1.118215 ,0.494807 ,0.602503 ,0.550321 ,0.029886 ,0.081306 ,-0.792841 ,-0.606078 ,-0.194655 ,-0.164800 ,-0.673720 ,-1.137632 ,-0.852141 ,-0.951941 ,-2.333839 ,-2.469809 ,-2.725490

回答No.1

NCU · ベストアンサー

>しかし、「=MIN(ABS(L123:L147))」では「0.368062 」となります。
それは式が123行目に入力されているからです。
たとえば次行にあれば 0.029886 を返すはずです。

>上記「MINとABSの擬似最大値は0.029886」 となる式を教えてください。
Shift+Ctrl+Enterで配列数式として入力して入力して下さい。

ところで「擬似最大値」という言葉はどこで使われる用語なのでしょう？
また、「L123＞０のとき」、「L123＜０のとき」は意味不明です。
ついでにいえば、回答者にとっては「L１２３からL１４７」である必要は全くないのでは？

お礼

NCUさん、書き込みありがとうございます。
（長文のため続きを投稿しようとしたら質問者は書き込めないとのことでここでかかせていただきました。失礼しました。）
＞＞>しかし、「=MIN(ABS(L123:L147))」では「0.368062 」となります。
＞それは式が123行目に入力されているからです。
＞たとえば次行にあれば 0.029886 を返すはずです。
えーと。よくわからないんのですが。
=MIN(ABS(L123:L147))では、まずABS（L123からL147の範囲で）マイナスの数値はプラスに変えて、そのうちで一番小さい数をだす（ゼロにより近い数をだす）と思って書いたのです。
間違っていますか？

＞>上記「MINとABSの擬似最大値は0.029886」 となる式を教えてください。
＞Shift+Ctrl+Enterで配列数式として入力して入力して下さい。
配列数式ですね。本を見てまた改めて返答させていただきます。
取り急ぎお礼のみ書かせていただきました。

＞、「L123＞０のとき」、「L123＜０のとき」は意味不明
１つの式を作ったらその式をコピペで使います。
その場合に必要なのです。

＞「擬似最大値」という言葉
私の造語です。
掲示板で「MINとABSの…」と繰り返し書くより短文になるので書きました。
失礼しました。それでも投稿は８００字を超えて一度ではかけませんでした。
掲示板のための字数を少なくする工夫としてご了承ください。

＞回答者にとっては「L１２３からL１４７」である必要は全くない
おっしゃるとおりです。ただ、MAXやMIN関数は３０個までという制限があると聞きましたので今回の数字が２５個の範囲内との意味から書かせていただきました。

書き込みありがとうございました。

補足

書き込みありがとうございます。
（長文のため：質問の続きを投稿できなかったので続きを書きます。）

（以下、未投稿）
なお、現時点で作成中なのは下記の式です。
ややこしいのでポイントのみお伺いしました。
ご了承ください。
当初は「IF（論理、真、偽）を「＝IF(L123＞０、式１、式２）」と書きました。しかし、これではエラーになりました。
IF文の中にさらに（式１という）IF文をつくったからでしょうか。
そこで、下記式１と式２を１つの式にすることを試みています。
しかし、上記「MINとABSの擬似最大値は0.029886」というのがうまくできません。

L123＞０のとき
=IF(((L123-P123)-(P123*(MAX(L123:L147))))>0,L123)　　式１
L123＜０のとき
=IF(((L123-P123)-(P123*(MIN((L123:L147)))))<0,L123)　式２
L123=0のときは偽とする。　　　　　　　　　　　　　　　式３

この３つをひとつの式にまめたく下記のようにしました。
P123=SIGN(L123)
=IF(P123^2*((L123-P123)-(P123*(MIN(ABS(L123:L147))))*P123)>0,L123)

なお、このMAXの代わりにMINとABSとすると次のようなことが起こることは了承済みです。
それは、2、1、0、-１、-2、-3のときに本来はMAX２です。しかし、MINとABSでは結果が０となることです。
そこはデータの特性ゆえクリアーできるのです。

よろしくお願いします。
（データ：L１２３からL１４７のデータは下記のとおりです。：上記と同じ数字です。)
-0.368062
0.207454
0.085564
0.432480
1.099316
0.971129
1.181787
0.815206
1.118215
0.494807
0.602503
0.550321
0.029886
0.081306
-0.792841
-0.606078
-0.194655
-0.164800
-0.673720
-1.137632
-0.852141
-0.951941
-2.333839
-2.469809
-2.725490

回答No.4

zap35

=MIN(ABS(A1:A25))　を配列関数として入力したら、あっさり最小の絶対値である0.029886になりましたね。

＃０３さんの補足を見ましたが、数式バーに、=MIN(ABS(L123:L147)　を入力してEnterの代わりにShift＋Ctrl＋Enter　を入力すればよいのです。範囲指定の時にShift＋Ctrlは押す必要はありませんよ

お礼

ありがとうございます！！！
できました。

はずかしながら、できなかった理由を欠かせていただきます。
「Shift＋Ctrl＋Enter」を「Shift＋Space＋Enter」と思い違いをしていました。
1時間以上3つのボタンを押し続け「うまくいかない！」。
本をひっくり返し、ネットで検索し「途方にくれていました。」
皆様、ありがとうございました。
心よりお礼申し上げます。

回答No.3

NoBi18

「疑似最大値」などという造語を持ち出すまでもなく「絶対値が最小である値」、もしくは「最も0に近い値」で十分だと思います。

解答としては、NCUさんの
「Shift+Ctrl+Enterで配列数式として入力して下さい。」
これが全てですね。数式バーの表示は
{=MIN(ABS(L123:L147))}
になります。{}は自分で入力するものではなく、Shift+Ctrl+Enterを押すことで自動で入力されるものです。

お礼

書き込みありがとうございます。今、書籍で確認したところ配列数式はEXCEL2000以降のようです。
私のは古くてEXCEL97です（涙）。

補足

私の勘違いでした。EXCEL97でもヘルプに配列数式「Shift+Ctrl+Enter」の記述がありました。
とりいそぎ、お詫びとお礼を申し上げます。
いま本を見ながら「Shift+Ctrl+Enter」をしていますがうまくいきません。範囲をドラッグし「Shift+Ctrl+Enter」をするとSHIFT+CTRLで範囲がやたら広くなり失敗。ひとつセルだけをして「Shift+Ctrl+Enter」を押すと行ごと指定範囲が変わってしまいます。数式入力用に「＝」のところで押しても変わらず。う～ん。困っています。

回答No.2

NCU

訂正します。

（誤）たとえば次行にあれば 0.029886 を返すはずです。
（正）たとえば次行にあれば 0.207454 を返すはずです。