エクセルで小数の中の最大値を求める関数

質問

W16～W28に数値があります。
整数と小数が混在しています。
小数の中の最大値を求めようと、SUMPRODUCT関数を使ったところVALUEエラーになりました。
今日は（いつも？）頭がはたらきません。

=SUMPRODUCT(MAX(W16:W28*(W16:W28<>INT(W16:W28))))

どこが間違っているのでしょうか？

回答No.5

Wendy02 · ベストアンサー

こんにちは。

私は配列数式は完全には分かりません。ただ、SUMPRODOCUT を真似て作るぐらいのことは出来るつもりでいます。

>=SUMPRODUCT(MAX(W16:W28*(W16:W28<>X16:X28))) で正解が返るのに
>=SUMPRODUCT(MAX(W16:W28*(W16:W28<>INT(W16:W28))))　が通らない

SUMPRODUCT の引数は、そのタイプが、パラメータ配列になっていますから、引数の様式は配列まで受け取ることが出来ます。しかし、一次引数の配列に対する計算は出来るけれども、二次引数の配列引数は計算できません。

具体的には、
W16:W28*(W16:W28<>X16:X28　一次になっています。

W16:W28*(W16:W28<>INT(W16:W28)　こちらは、INT関数の引数になっているから、二次引数になっていますから、SUMPRODUCT では、そのままでは、数値を実体化しません。実体化させるには、二次の部分を一次にしてあげないといけないので、

=SUMPRODUCT(MAX(W16:W28*INDEX(W16:W28<>INT(W16:W28),,)))

としてあげないと出ません。

なお、この数式、全体としては、負の数も入るとすれば、正しい値は取れないと思います。昨今の掲示板の回答の傾向が、配列の確定を避けようとするわけですが、配列の確定(Ctrl + Shift + Enter) をしない方法でというと、ちょっと難しいように思います。

不思議さからいうと、なぜ、MAX が配列を引数として扱えるか、これ自体が分かりません。初期関数には手を付けないといことでもないようです。Office の開発チームの中のそれぞれが決めたことだとは思うのですが、例えば、RANK 関数は、配列を引数として使えません。製作者の恣意的なものなのか、理由は分かりません。

お礼

Wendy02さま、いつもありがとうございます。

> 一次引数の配列に対する計算は出来るけれども、二次引数の配列引数は計算できません。

> INT関数の引数になっているから、二次引数になっていますから、SUMPRODUCT では、そのままでは、数値を実体化しません。実体化させるには、二次の部分を一次にしてあげないといけないので、

なるほど、そのような理由があったのですね、とてもすっきりしました。
ありがとうございます。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＞=SUMPRODUCT(MAX(W16:W28*(W16:W28<>X16:X28))) で正解が返るのに
＞=SUMPRODUCT(MAX(W16:W28*(W16:W28<>INT(W16:W28))))　が通らないのか
＞不思議なので質問させていただきました。
確かに不思議に思います。
以前にもこのような経験があり、SUM関数と[Ctrl]+[Shft] ＋[Enter] で確定にしました。
例
=SUMPRODUCT(IF(W16:W28<>INT(W16:W28),W16:W28))
＞Ctrl+Shift+EnterしなくてよいSUMPRODUCTを使おうと
SUM要素も含むことからその考えは半分○といったところじゃないかと思います
また、もともとヘルプにはそのような使い方を書いていないから、奥深く数式を組み立てると、配列として認識できないのではないかと思います
=MAX(W16:W28*(W16:W28<>X16:X28))
[Ctrl]+[Shft] ＋[Enter] で確定が一番楽だと思うんですけどね。
つまり、MAXやMIN、SUMなど配列を返さない関数にSUMPRODUCTは意味がないと思います

結局、不思議ななぞは解けていないので参考まで

お礼

ありがとうございました。

回答No.3

KURUMITO

次のような式になりますね。
=MAX(IF(W16:W28<1,W16:W28,""))
Ctrl+Shift+Interで式を確定します。

お礼

ありがとうございます。
ただ、これでは１以上の小数が除外されてしまいます。

回答No.2

noname#204879

》 どこが間違っているのでしょうか？

う～む、全部間違ってます。

貴方には多分難解ですが、短い式を示しておきます。ただし、配列数式（勉強してネ）です。
{=MAX(IF(MOD(W16:W28,1),W16:W28,""))}

お礼

はい、配列数式はとても苦手です。
ですからCtrl+Shift+EnterしなくてよいSUMPRODUCTを使おうとしまして・・・。

隣のX列に=INT(W16)を入れてドラッグした場合、
=SUMPRODUCT(MAX(W16:W28*(W16:W28<>X16:X28))) で正解が返るのに
=SUMPRODUCT(MAX(W16:W28*(W16:W28<>INT(W16:W28))))　が通らないのか不思議なので質問させていただきました。

回答No.1

grumpy_the_dwarf

sumproductを使う意図が分かりません。最大値を求めるのだから、
一番外側はmaxでしょう。

=max(index(範囲*(mod(範囲,1)<>0),0))
なんてどうですか。indexは配列としての体裁を整え明示するための
もので、ウチの環境ではこれがないと数式パレットにしか正解が表
示されないのです。

お礼

grumpy_the_dwarfさま、先ほどの質問に引き続きご回答ありがとうございます。教えていただいた関数で求められました。

> sumproductを使う意図が分かりません。

馬鹿の一つ覚えのようなものでして・・・・。
たとえば、隣のX列に=INT(W16)を入れてドラッグした場合、
=SUMPRODUCT(MAX(W16:W28*(W16:W28<>X16:X28))) で正解が返りますよね。
ならば、なぜ
=SUMPRODUCT(MAX(W16:W28*(W16:W28<>INT(W16:W28))))　が通らないのか不思議なのです。

ありがとうございました。