Excel (一般機能)

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

 
(Windows 10 Pro : その他)
Vlookup関数(?)にて、、、
投稿日時: 20/11/12 19:13:16
投稿者: mattuwan44

エクセル2019です。
 
P1-0+10
P1-0+10R3
P1-0+10R7
P1-0+10L3
P1-1
P1-1R3
P1-1R7
P1-1L3
 
というような文字列がセルに入っているとして、
 
一覧表が
 

     [H]        [I]          [K]
	P1-0	    0	763.09
	P1-0+5	    5	762.9914423
	P1-0+10	    10	762.8928846
	P1-1	    20	762.6957692
	P1-1+10	    30	762.4986538
	P1-1+15.6	35.6	762.3882692
	P1-2	    40	762.3015385
	P1-2+1.6	41.6	762.27

 
のようにあり、
 
P1-0+10R3やP1-0+10L3 も、
P1-0+10
として検索して3列目の762.4986538を
返してほしいですが、
何かいい関数がないでしょうか?

投稿日時: 20/11/12 20:40:42
投稿者: mattuwan44

ルールとしては、
文字列中の数字の後の「R」と「L」以降は無視したいと考えてます。
 
一応以下で解決しましたが、
=IF(ISERROR(FIND("L",A1)),IF(ISERROR(FIND("R",A1)),A1,MID(A1,1,FIND("R",A1)-1)),MID(A1,1,FIND("L",A1)-1))
 
数式だと、こんなわけわかんない感じになるのはしょうがないのですかねぇ。。。。
 
別案があればと思うので、しばし閉じないでおきます。

回答
投稿日時: 20/11/12 22:09:35
投稿者: んなっと

削除するパターンは[RかL&なんとか]しかないんですね。それなら簡単。
 
      A  B      C D E F G     H   I     J
1  P1-0+10 10 762.8928846          P1-0   0  763.09
2 P1-0+10R3 10 762.8928846         P1-0+5   5 762.9914
3 P1-0+10R7 10 762.8928846        P1-0+10  10 762.8929
4 P1-0+10L3 10 762.8928846          P1-1  20 762.6958
5    P1-1 20 762.6957692        P1-1+10  30 762.4987
6   P1-1R3 20 762.6957692       P1-1+15.6 35.6 762.3883
7   P1-1R7 20 762.6957692          P1-2  40 762.3015
8   P1-1L3 20 762.6957692        P1-2+1.6 41.6  762.27
9     P1  0     111           P1       111
 
B1
=INDEX(I:I,MATCH(IFERROR(LEFT($A1,AGGREGATE(15,6,FIND({"R","L"},$A1),1)-1),$A1),$H:$H,0))
右方向・→下方向・↓
 
もちろんVLOOKUPでも可能です。

回答
投稿日時: 20/11/12 22:13:00
投稿者: 半平太

データの在り様が不明瞭ですからねぇ。。
 
例えば、こんなのは絶無なのかどうか回答側は分からないです。
 
L1-1L1(初っ端にLが存在したら)
P1-L1 (前が数字じゃないLの取り扱いが不明)
 

投稿日時: 20/11/13 18:17:57
投稿者: mattuwan44

半平太 さんの引用:
データの在り様が不明瞭ですからねぇ。。
 
例えば、こんなのは絶無なのかどうか回答側は分からないです。
 
L1-1L1(初っ端にLが存在したら)
P1-L1 (前が数字じゃないLの取り扱いが不明)
 

そうですかぁ。。。。
 
L1-1L1(初っ端にLが存在したら)←これはありますが数字の後ろでないので、2番目のL以降が対象です。
P1-L1 (前が数字じゃないLの取り扱いが不明)←これは絶対にないです。
0R-1+10R3 ←先頭の数字の後にアルファベットが来て数字が出てアルファベットがまた出てくるもなしです。
0R3 ← これはあります。
R3 ←これはありですが、前に数字がないのでそのままです。
R3R3 ←これはありですが、2番目のRが対象です。
R3R ←これはないです。
 
あと、特殊な例でいうと
R1-BC.1R3
R1-EC.3R5
R1-KA.1-1R5
KE.1-2L3
R1-100+19.999R7.123
というような文字列が出てくる可能性がありますが、
いずれも数字の後のLかR以降を無視したいです。
 
例を書いてみて思ったのは、
数字に挟まれたLかRがあれば、そのLかR以降を無視して表引きしたいです。
 
んなっとさんの式は後程、試させていただきます。

回答
投稿日時: 20/11/13 20:25:08
投稿者: んなっと

この書き込みは何だったのでしょうか。

引用:
一応以下で解決しましたが、
=IF(ISERROR(FIND("L",A1)),IF(ISERROR(FIND("R",A1)),A1,MID(A1,1,FIND("R",A1)-1)),MID(A1,1,FIND("L",A1)-1))

 
削除するところだけ。
 
           A       B
 1       P1-0+10R    P1-0+10R
 2      P1-0+10R3    P1-0+10
 3      P1-0+10R7    P1-0+10
 4      P1-0+10L3    P1-0+10
 5         P1-1      P1-1
 6        P1-1R3      P1-1
 7        P1-1R7      P1-1
 8        P1-1L3      P1-1
 9          P1       P1
10        L1-1L1      L1-1
11         0R3       0
12          R3       R3
13         R3R3       R3
14      R1-BC.1R3    R1-BC.1
15      R1-EC.3R5    R1-EC.3
16     R1-KA.1-1R5   R1-KA.1-1
17       KE.1-2L3     KE.1-2
18 R1-100+19.999R7.123 R1-100+19.999
 
B1
=IFERROR(LEFT($A1,FIND("0R0",SUBSTITUTE(CONCAT(TEXT(MID($A1,ROW($1:$50),1),"!0")),"L","R"))),$A1)
下方向・↓

投稿日時: 20/11/17 19:08:14
投稿者: mattuwan44

 >この書き込みは何だったのでしょうか。
 
う〜ん。なんていえばいいんでしょうか。。。
とりあえず、汎用的ではなくても、今、出てきている文字に対して最低限場当たり的に対応して、
自分で思いついた関数を使って曲りなりにでも処理できたという感じでしょうか。。。
 
別の関数での案をいくつか知りたかったです。
IFERROR
は、思いつかなかったなぁ。
 
AGGREGATE
は全く知りませんでした。ヘルプ読んでも使いようを思いつかないですけど^^;
 
数式見させてもらって、疑問が出てきました。
 
FIND({"R","L"},$A1)
とか、
MID($A1,ROW($1:$50),1)
とか配列を返していると思うんですけど、
昔から配列返す仕様でした?
いつの間にかエクセルが進化したのでしょうか?
あるいは、僕が知らなかっただけでしょうか?
 

回答
投稿日時: 20/11/17 19:26:39
投稿者: んなっと

最初の条件の簡単なものは
=LEFT($A1,MIN(FIND({"R","L"},$A1&"RL"))-1)
でもOKです。

回答
投稿日時: 20/11/17 21:20:52
投稿者: んなっと

MINやSMALL関数だと、配列の要素にエラーが含まれる場合にその「エラーの除外」に工夫がいります。
AGGREGATEは配列の中の「エラーを無視する」こともできるので、非常に強力。

投稿日時: 20/11/21 18:12:08
投稿者: mattuwan44

咀嚼に時間がかかりましたが、
勉強になりました。
 
ありがとうございました。