エクセル：複雑な検索をしたい

質問

お世話になります。
Ａ列に数字が並んでいます。
C1:C10に数字のリストがあります。

Ｂ１にはＡ１の数字からＣ１～Ｃ１０のそれぞれの数字を引いた場合の
絶対値が最小値になるＣ列の数字を入れる。
この場合Ｂ１にはどのような計算式を入れておけばよいのでしょうか。

例
仮にＣ１が１００、Ｃ２が２００・・・Ｃ１０が１０００の場合で、
Ａ１の数字が１７０の場合、Ｃ１は１７０－１００で７０、
Ｃ２は１７０－２００で－３０（絶対値なので３０）、Ｃ３は１７０－３００で－１３０・・・となり引き算の結果の最小値となるのはＣ２なので、Ｂ２はＣ２の数字２００を入れる。

Ａ列の数字は整数とは限らず、小数点がつくものが多い。
Ｃ列は整数だが、例のように単純に１００ずつ増えるわけではない。
仮に上の例でＡ１が１５０の場合、Ｃ１とＣ２から引いた数は共に５０で同じになるが、その場合は小さい数のＣ１の数字１００をＢ１に入れる。

さらに、
実際には引き算の結果の最小値は１前後の小さな数になることを想定しています。
引き算の結果（絶対値）が１５を超えるような場合は、イレギュラーなものなのでＢ列には「エラー」と表示させたい。

今回の質問の例はかなり簡略化していて、実際は列番号や、リストの数などは違ってきます。自分でカスタマイズしやすいように計算式でできれば理想的ですがかなり複雑になりそうです。マクロでやる場合は、素人なので「列を変えるときはこの部分を修正など」詳しく教えてくると助かります。
アドバイスよろしくお願いします。

回答No.6

ham_kamo · ベストアンサー

上記の例の場合にB1に入れる式は、以下でいかがでしょうか。
配列数式なので、B1に入力したあと、Ctrl+Shift+Enterで確定します。その後そのセルをマウスで下にコピーしたら、各行の式になります。

=IF(MIN(ABS($A1-$C$1:C$10))>15,"エラー",INDEX($C$1:$C$10,MIN(IF($C$1:$C$10=MIN(IF(ABS($A1-$C$1:$C$10)=MIN(ABS($A1-$C$1:$C$10)),$C$1:$C$10,MAX($C$1:$C$10))),ROW($C$1:$C$10),ROW($C$10)+1))))

実際のデータでは、

$A　→　質問文に出てくるA列に相当する列名
$C　→　質問文に出てくるC列に相当する列名
$A1や$C$1の数字　→　実際にデータが入る最初の行番号
$10　→　実際にデータが入る最後の行番号より大きな数字

に変えてください。

お礼

ありがとうございました。
配列数式のことを知らず恥ずかしい限りです。
参考になりました。

回答No.10

Cupper

なにか複雑なことを考えていらっしゃるようですが・・・

ぶっちゃけ A1セルの値に一番近い C列の数字を B1セルに表示させたいと言うことですよね。
完璧な回答ではありませんが参考までにどうぞ

　=LOOKUP(A1,C1:C10,C1:C10)

お礼

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

回答No.9

zap35

＃０４です。B1に入れる式が冗長だったので見直しました

=IF(MIN(ABS(A1-C1:C10))>15,"エラー",INDEX(C1:C10,MAX((ABS(A1-C1:C10)=MIN(ABS(A1-C1:C10)))*ROW(C1:C10))))

お礼

ありがとうございます。
随分すっきりして助かります。

回答No.8

xaxyaxe

=IF(ABS(A1-INDEX(リスト,MATCH(A1,リスト,1),1))>ABS(A1-INDEX(リスト,MATCH(A1,リスト,1)+1,1)),IF(ABS(A1-INDEX(リスト,MATCH(A1,リスト,1)+1,1))<15,ABS(A1-INDEX(リスト,MATCH(A1,リスト,1)+1,1)),"エラー"),IF(ABS(A1-INDEX(リスト,MATCH(A1,リスト,1),1))<15,ABS(A1-INDEX(リスト,MATCH(A1,リスト,1),1)),"エラー"))

は、いかがでしょうか？
設定の再現が難しく、数式の検証が出来ていません。
また、Ｃ列にＡ列より小さな数値がないと、＃Ｎ／Ａが出てしまいます。

お礼

ありがとうございます。
参考にします。

回答No.7

imogasi

回答者がダーと配列数式などの複雑な式の回答になびいていますが
このタイプの問題だとVBAで考えたほうが、素直なやり方でできるように思います。
Sub test01()
d1 = Range("A65536").End(xlUp).Row
d2 = Range("C65536").End(xlUp).Row
For i = 1 To d1 'A列のデータ数だけ繰り返し
m = 10000 '仮の最大値　適当に見繕うこと
k = 1
For j = 1 To d2 'C列のデータ数だけ繰り返し
n = Abs(Int(Cells(i, "A")) - Cells(j, "C"))
If n < m Then
m = n
k = j
End If
Next j
Cells(i, "B") = Cells(k, "C")
Next i
End Sub
のようになるのかな。
質問の意図を反映しているか不安ですが。

お礼

ありがとうございました。
参考にします。

回答No.5

mshr1962

最初のだけなら
=MIN(IF(MIN(ABS(C1:C10-A1))=ABS(C1:C10-A1),C1:C10,MAX(C1:C10)+1))
上記を入力して、Ctrl+Shift+Enterで確定して配列数式にしてください。

15を超えたらエラーだと
=IF(MIN(IF((MIN(ABS(C1:C10-A1))=ABS(C1:C10-A1))*(ABS(C1:C10-A1)<=15),C1:C10,MAX(C1:C10)+1))=MAX(C1:C10)+1,"エラー",MIN(IF((MIN(ABS(C1:C10-A1))=ABS(C1:C10-A1))*(ABS(C1:C10-A1)<=15),C1:C10,MAX(C1:C10)+1)))
上記を入力して、Ctrl+Shift+Enterで確定して配列数式にしてください。

これ一個だけならたいしたことありませんが、同じ計算を複数のセルで行う場合は
結構処理が重くなりますのでご注意ください。

お礼

ありがとうございました。
配列数式のことを知らず恥ずかしい限りです。
参考になりました。

回答No.4

zap35

B1セルは数式バーに以下の式を貼り付けてShift＋Ctrl＋Enterで確定します。（配列数式になります）

=IF(MIN(INDEX(ABS(($A$1-$C$1:$C$10)),))>15,"エラー",INDEX($C$1:$C$10,MAX((ABS($A$1-$C$1:$C$10)=MIN(INDEX(ABS(($A$1-$C$1:$C$10)),)))*ROW($C$1:$C$10))))

MIN(INDEX(ABS(($A$1-$C$1:$C$10)),))は絶対値の最小値を表します。15以上なら「エラー」を表示しています。

MAX以降の式で「差の絶対値が最小になる行番号」を求めますので、式の「前略…,"エラー",INDEX($C$1:$C$10,…後略」の$C$1:$C$10は必ず１行目を開始範囲にすることがポイントです。その他の箇所の$C$1:$C$10は実際のデータ範囲に置き換えます。

なお「差の絶対値が最小となる」行が２行以上ある場合は「大きい方の値」を選択しています。（その方が簡単だったので。ご承知おきください）

列を変えたいなら$A,$Cを、$Bでも$Hでも好きなように変更してください

お礼

ありがとうございました。
配列数式のことを知らず恥ずかしい限りです。
参考になりました。

回答No.3

134

=min(abs(a1-c1:c10))
と入力して、Ctrl+Shiftの状態でEnterを押すと、最初の課題はクリアできそうに思います。

１５を超えるようなら　というなら、
=IF(MIN(ABS(A1-C1:C10))>15,"エラー",MIN(ABS(A1-C1:C10)))
と入力して、Ctrl+ShiftでEnterを押して、数式を入力してみてはいかがでしょうか

お礼

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

補足

ご回答ありがとうございます。
理想に近づいていますが、B列に表示したいのは引き算の結果ではなく、引き算で最小値をはじきだした「C列」の数字なのです。

回答No.2

Tasuke22

>Ａ列がＡ１のみなら問題ないのですが、Ａ列には数字が並んでいて
>Ａ列の各行に対し今回の質問の結果をＢ列の各行に表示させたいのです。

(?_?) 一行作ったらそれを全行コピーすればいいはなしでは
ないのですか？

回答No.1

Tasuke22

列を分けて整理したほうが良さそうです。
A列C列　D列にA-Cの絶対値　B列にA,C,DのMIN
単純になります。

お礼

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

補足

早速回答ありがとうございます。
Ａ列がＡ１のみなら問題ないのですが、Ａ列には数字が並んでいてＡ列の各行に対し今回の質問の結果をＢ列の各行に表示させたいのです。