ある条件を満たした最も近い数値を求める配列数式

質問

ある条件を満たした最も近い数値を求める配列数式について質問します。

A列には，C列の数値と比較する元になる数値が並んでいます。
B列には，D列の数値と比較する元になる数値が並んでいます。
C列には，A列の数値と比較する対象となる数値が並んでいます。
D列には，B列の数値と比較する対象となる数値が並んでいます。
E列には，計算式が入ります。

すなわち，例えば，E20には，
C$1:C20の範囲内の数値で，
右となりにあるD$1:D20の数値が0でなく，かつ，B$1:B20の数値以下である数値の中で，
最もA20の数値に近い数値を求めます。もし，この条件に合う最も近い数値が複数あった場合には，最も小さい数値を求めます。

行番号　　　A　　 　B 　　 C　　 D　　 E
1　　　　　　 100　 　10　 89 　　0　　　0
2　　　　　　 101　　 8　　 90　　10　　　0
3　　　　　　 102　　 11　 92　　11　　92　　
4　　　　　　　 99　　 7　　 77　　0　　　0
5　　　　　　　 87　　 11　 68　　12　　92
6　　　　　　 105　 14　 100　　7　　　100
7　　　　　　 102　　 9　　 78　　8　　　100
8　　　　　　 88　　 15　 102　　0　　　　90
9　　　　　　 98　　 14　　 99　　10　　　　99
10　　　　　 150　　 13　 　78　　9　　　100
11　　　　　 103　　　 8　 　79　　0　　　100
12　　　　　 87　　　　 9　　 68　　8　　　78
13　　　　　　77　　　10　　70　　10　　　78
14　　　　　　89　　　12　　71　　　5　　　90
15　　　　　　65　　　14　　102　　3　　　68
16　　　　　　88　　　13　　105　　0　　　90
17　　　　　123　　　15　　　98　　8　　　102
18　　　　　145　　　17　　　99　　10　　　102
19　　　　　　85　　　　8　　100　　0　　　　90
20　　　　　　84　　　　9　　　87　　5　　　　78

一定の条件を満たした配列の合計を計算する関数として，SUMPRODUCT()という便利な関数がありますが，
この関数に相当する関数がないので困っています。
MIN関数が配列数式に対応していて，かつ，MIN関数をうまく利用して，最も近い数値を求めることがでるき方法があれば，私の希望が解決できそうなのです。
以上，よろしくアドバイス願います。
なお，エクセルのバージョンは，2010です。
本日，同じ表題の質問をしましたが，その際の質問の本文に掲載した数列が崩れていましたので，再度投稿します。
質問番号：6449070

回答No.4

MackyNo1

=MAX((MIN(ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))=ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))*C$1:C1)

数式を分解して説明すると、

IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10）・・・（１）

この部分はD列が０以外かつB列がD列以上ならC列の値を返し、それ以外の場合は大きな数字を返しています。

MIN(ABS(A1-上記の（１）の配列）)・・・（２）

上記の配列からA1セルを引いた絶対値の配列の中で最小値を取得しています。

MIN(ABS(A1-上記の（１）の配列）)=ABS(A1-上記の（１）の配列）)・・・（３）

この配列の中の最小値とその配列が等しい配列を取得しています。

上記の配列にC列の配列を掛け算して最小値のセルのC列の値の配列を取得しています（該当しないデータは０、該当するセルはC列の値）。

この中で最大値（すなわち０以外のデータ）を求めています。

ただしこの数式では、差の絶対値が上下に同じ数字があった場合は、大きいほうの数字を拾ってきます。

しかし、ご質問をよく見たら、複数の該当データがある場合は、小さい方の数字を抽出したいということなので、該当しないデータに０ではなくFALSEを返す以下のような数式にする必要がありますね。

=MIN(IF((MIN(ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))=ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10))),C$1:C1))

回答No.3

MackyNo1

No2の回答の数式を間違えていました。

正しいE1セルに入力する式は以下の数式です。

=MAX((MIN(ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))=ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))*C$1:C1)

補足

回答ありがとうございました。
示していただいた計算式を入力したところ，満足な結果が得られました。
私が，質問した際に例示していた計算に何か所か誤りがあったこともわかりました。
感謝しております。

ところで，示していただいた計算式を機械的に入力しただけで，私としては，ほとんど，理解困難な計算式です。この計算式には，カッコ書きが多いので，関数ごとに色分けして，分解して理解しようと試みましたが，残念ながら，構造の不明な個所があります。
「=ABS」という個所ですが，なぜ，イコールとしているのでしょうか。
また，計算式の最初の関数は，MAX関数なのですが，なぜ，これを用いるのでしょうか。
分かりやすく，説明していただければ，今後の応用に役立ちます。
　よろしくお願いします。

回答No.2

MackyNo1

例示のデータでは、４行目が０となていますが3行目の値を返すのが正しいのではないでしょうか？
また12行目も78ではなく９２ではないでしょうか？

もし間違いなら以下のような配列数式で、最初の0以外のデータを表示することができます。

=IF(D1=0,0,MAX((MIN(ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))=ABS(A1-IF((D$1:D1<>0)*(B$1:B1>=D$1:D1),C$1:C1,10^10)))*C$1:C1))

これに０を判定する式を追加すれば、すべてのセルに共通する数式にすることができますが、数式を重くしないためにも、行の最初の部分の必要最小限のセルに入力するようにしてください。

=IF(SUMPRODUCT((D$1:D1<>0)*(B$1:B1>=D$1:D1)),上記の式,0)

補足

回答，ありがとうございました。
　さて，ご指摘のありました
>例示のデータでは、４行目が０となていますが3行目の値を返すのが正しいのではないでしょうか？
>また12行目も78ではなく９２ではないでしょうか？
について，私の誤りでした。ご指摘のとおりです。

回答No.1

imogasi

>E20には，C$1:C20の範囲内の数値で，右となりにあるD$1:D20の数値が0でなく，かつ，B$1:B20の数値以下である数値の中で，最もA20の数値に近い数値を求めます。
この意味がわからない。
Ｃ悦やＢ列で対応行の１行で、考えるのか、Ｃ列やＢ列１列全体を集合として考えるのか。
どうも対応行で考えるらしいが。
質問にに挙げた例で、Ｅ２０の７８が出る計算や比較対象を明記して、プロセスを文章化してもらえませんか。
＞B$1:B20
はＡ１：Ａ２０の誤りではないですか。　　　＜＝＝＝注目依頼
以下そういう考えで推測してやってみた
例
Ｇ列　　　　　Ｉ列　　　　　Ｈ列=G4-I4 ( Ｇ列で-や空白はＤ列対応行が０やＢ列対応行より大きい場合です）
A1:A20以下 A20 差

-
90 84 6
92 84 8
-

100 84 16
78 84 -6
-

78 84 -6
-
68 84 -16
70 84 -14
71 84 -13
3 84 -81
-
98 84 14
99 84 15
-
Ｈ列で差の絶対値が最小はー６に対応する７８。これがＥ２０にあると言う理解で間違いかな？
ーーーー
こういうのは配列数式で苦労するより、ＶＢＡで処理したら。
ＶＢＡの学習は一般には短期には難しいがこの質問に限れば、そんなに難しい事項は必要なかろうと思う
各行でのくり返し
その他のくり返し（列のペアでくり返し？）
のネスト
比較（引き算）
配列に差の絶対値を収納（シートの列のセルへ一時保有でも出来るかと）
配列データ内でＭＩＮ関数適用
ーー
データ変更に対する即時結果変更性はＶＢＡでは出来ない（イベント化すれば出来るが）ものの、素直なロジックでコードが書けるタイプだと思う。
ーーー
前質問回答途中で締め切りに会いました。
この質問でも、前の質問と表現は変わっていないようなのでこれを上げます。

補足

非常に素早い回答ありがとうございます。
　補足説明します。
　B$1:B20は，A1:A20の誤りではありません。
　詳しく説明します。
　まず，E20の数値を求めるプロセスですが，非常に説明が長くなるので，E6の数値を求めるプロセスについて説明します。
　E6の数値を求めるには，対象となる行が1行から6行までの6つの行になります。
　まず，D列の値が0でない行を求めます。
　すると，2行，3行，5行,6行が残ります。
　次に，D列の値が，B列の値以下である行を求めます。
　すると，1行，3行，4行，6行が残ります。
　以上の2つの条件を満たす行は，3行，6行になります。
　これらの列のC列の数値は，C3の92とC6の100になります。
　このC3 92 とC6 100の2つの値のうちでA6の105に最も近い値を探します。
　すると，C6の100がA6の105に最も近いことになり，求める数値が得られます。
　この結果がE6セルに表示されるようにしたいのです。

　以上，くどい説明になりましたが，よろしくお願いします。
　また，以上のようなプロセスでE列の各セルに計算式をいれていく関係上，作業列ないしは作業セルを使用することはできません。
　ですから，なんとか，関数を使用して計算させたいのです。
　私が現在考えている方法は，
　{=関数((D$1:D1<>0)*(D$1:D1<=B$1:B1),A1,C$1:C1)}
　という配列数式です。
以上，よろしくお願いします。
　なお，VBAで処理する方がいいというアドバイスですが，その方法も時間があれば勉強したいと思います。