Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
近似値の出力
投稿日時: 18/03/30 11:29:24
投稿者: おで

入力した値の近似値を取りたいと思っております。
 
たとえばA列に
A-37.888
B-36.483
と入力した際に、下記マスターシートから数値の近いものをB列に表示したいです。
 
今回の場合は、下記のように表示させたい。
A-37.888  A-38.8
B-36.483  B-36.48
 
<マスターシート>
A-3.999
A-38.8
B-36.48
B-36.9
 ・
 ・
 ・
 
VLOOKUPで検索方法をtrueにすればできるかと思いましたが、
入力値A-37.888に対して、マスターシートのA-3.999が出力されてしまいます・・・
MATCHで、数値の近似値は取れますが、入力値の頭の"A-"や"B-"があるので、うまくいきません・・・
 
 
 

回答
投稿日時: 18/03/30 13:33:01
投稿者: WinArrow
投稿者のウェブサイトに移動

回答ではありませんが、
>近似値
を誤解されているような気がします。
 
>近似値
は、数値だけに適用されるもので、
文字列に対しては、当てはまらないです・・
 
根本から見直すことをお勧めします。

回答
投稿日時: 18/03/30 15:29:17
投稿者: WinArrow
投稿者のウェブサイトに移動

近似値ではなく、
検索値より一番近くて大きい値
ということであれば、
MATCH関数とINDEX関数で取得可能です。
但し、マスタ側を降順に並べておく必要があります。

回答
投稿日時: 18/03/31 00:50:31
投稿者: Mike

ゴリ押し別解です(*^_^*
 
Master
   A  B  C
1 A-3.999 A- 3.999
2 A-38.8 A- 38.8
3 B-36.48 B- 36.48
4 B-36.9 B- 36.9
 
B1: =LEFT(A1,FIND("-",A1))
C1: =MID(A1,FIND("-",A1)+1,99)*1
 
 
Sheet1
   A    B  C D  E   F   G   H   I  J   K  L
1 A-37.888 A-38.8  A- 37.888 0.912 36.976  38.8 #N/A TRUE   0 38.8
2 B-36.483 B-36.48  B- 36.483 0.003 36.48 36.486 TRUE #N/A 36.48  0
 
D1: =LEFT(A1,FIND("-",A1))
E1: =MID(A1,FIND("-",A1)+1,99)*1
F1: =MIN(IF(Master!B$1:B$100=D1,ABS(Master!C$1:C$100-Sheet1!E1),""))
【お断り】F1の式は必ず配列数式として入力のこと
G1: =E1-F1
H1: =E1+F1
I1: =MATCH(G1,IF(Master!$B$1:$B$100=$D1,Master!$C$1:$C$100,""),0)>0
【お断り】I1の式は必ず配列数式として入力のこと
J1: I1 をコピー&ペースト
K1: =IFERROR(G1*I1,0)
L1: K1 をコピー&ペースト
B1: =D1&SUM(K1:L1)

回答
投稿日時: 18/03/31 09:29:17
投稿者: んなっと

こんな方法もあるかもしれません。
ただし、マスターのA列はすべてA-,B-,...,ABC- の形で始まる前提で。
 
     A    B  C    D   E   F
1 A-37.888  A-38.8 A- 37.888 3.999 38.8
2 B-36.483 B-36.48 B- 36.483 36.48 36.9
 
B1
=IFERROR(C1&IF(D1<=AVERAGE(E1:F1),MIN(E1:F1),MAX(E1:F1)),"")
下方向・↓
C1
=IFERROR(LEFT(A1,FIND("-",A1)),"")
下方向・↓
D1
=IF(C1="","",SUBSTITUTE(A1,C1,)*1)
下方向・↓
E1
=IFERROR(AGGREGATE(14,6,TEXT(SUBSTITUTE(マスター!A$1:A$200,C1,),"[<="&D1&"]標準;;;")*1,1),"")
下方向・↓
F1
=IFERROR(AGGREGATE(15,6,TEXT(SUBSTITUTE(マスター!A$1:A$200,C1,),"[>="&D1&"]標準;;;")*1,1),"")
下方向・↓

回答
投稿日時: 18/03/31 09:57:24
投稿者: sy

おで さんの引用:
MATCHで、数値の近似値は取れますが、入力値の頭の"A-"や"B-"があるので、うまくいきません・・・

本当に数値なら上手くいきますか?
MATCH関数などの近似検索は近似値を求めてる訳ではありません。
関数のヘルプをよく読んでみて下さい。
仮に数値だけのリストでも3.999が返る筈ですよ。
 
今回の要件に関しては、以下でも出来ると思います。
B1 =INDEX(マスター!A:A,MATCH(E1,INDEX(ABS(SUBSTITUTE(マスター!A$1:A$100,C1,"")-D1),0),0))
C1 =LEFT(A1,FIND("-",A1))
D1 =SUBSTITUTE(A1,C1,"")
E1=AGGREGATE(15,6,ABS(SUBSTITUTE(マスター!A$1:A$100,C1,"")-D1),1)

 
一発で求めるのは以下のように長くて分かり辛くなるのでお勧めはしません。
使うなら式の修正などが発生した時などに修正依頼はしないで下さい。(無視します。)
=INDEX(マスター!A:A,MATCH(AGGREGATE(15,6,ABS(SUBSTITUTE(マスター!A$1:A$100,LEFT(A1,FIND("-",A1)),"")-SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)),"")),1),INDEX(ABS(SUBSTITUTE(マスター!A$1:A$100,LEFT(A1,FIND("-",A1)),"")-SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)),"")),0),0))

回答
投稿日時: 18/03/31 11:11:32
投稿者: んなっと

syさんの式、完璧ですね。すばらしいです。
  
先の私の書き込みで、「マスターのA列はすべてA-,B-,...,ABC- の形で」
の部分は、「数値だけ」、または「2EA- の形で始まる」ようなことがあると
うまくいかないという意味です。

回答
投稿日時: 18/03/31 13:25:36
投稿者: んなっと

あと、最初から気になっていたのが、
 
A-4.5 を検索するとき
 
マスターシートが
 
A-4.0
A-5.0
 
の場合どっちを選ぶのか、ということです。

回答
投稿日時: 18/04/01 22:57:04
投稿者: WinArrow
投稿者のウェブサイトに移動

別解
 
説明上、マスタ部分とデータ部分を同一シート上という前提にしています。
 
まず、マスタ部分の準備
 
A列は、原形のまま
 
但し、1行目をタイトルとするのでデータは2行目
 
[1行目]のB列、C列
B1:A-
C1:B-
 
[2行目]
B2:=IF(LEFT($A2,LEN(B$1))=B$1,SUBSTITUTE($A2,B$1,"")*1,0)
下へフィルドラッグ
B2〜B5をC列目へコピペ
 
B2〜B5に名前設定:A_
C2〜C5に名前設定:B_
 
※名前設定は、データ側の数式の中で参照部分を簡易化することと、
A-,B-が2つではなく、もっと多いだろうという推測しての対応。
※名前定義の名前には「-」は使えないので「_」で設定してください。
 
データ部分A11〜A12・・原形のまま
B11:=MID($A11,FIND("-",$A11)+1,10)*1
C11:{=MAX(IF(INDIRECT(SUBSTITUTE(LEFT(A11,FIND("-",A11)),"-","_"))<=B11,INDIRECT(SUBSTITUTE(LEFT(A11,FIND("-",A11)),"-","_")),""))}
D11:{=MIN(IF(INDIRECT(SUBSTITUTE(LEFT(A11,FIND("-",A11)),"-","_"))>=B11,INDIRECT(SUBSTITUTE(LEFT(A11,FIND("-",A11)),"-","_")),""))}
E11:=IF(ABS(C11-B11)<ABS(D11-B11),C11,D11)
 
B11〜E11を下へフィルドラッグ
 
考え方
基準値より(大きい値)の最小値(@)と、基準値より(小さい値)の最大値(A)を求め
各々と基準値の差が小さい方(B)を近似値とする。
C11が@、D11がA、E11がB
 
C11、D11は、配列関数にすること。
 
 
 
 
 

投稿日時: 18/04/02 16:59:16
投稿者: おで

みなさん、色々とアドバイスいただきまして、ありがとうございます。
内容確認してすすめたいと思います。
検証に時間がかかりそうですので、取り急ぎ、解決済とさせていただきます。