excel 中数据变化后按照原有对应关系进行查询.

Question

表情况如下:(内容很多.只举五条记录.)
地县名称 原名称
a1 k1-0
a1 k2-0
b1 tt3-1
b1 at5-2
c1 hg-0
其中,地县名称和原有名称对应.为一一对应的关系.
由于数据升级.原名称改变.给出了新的数据.新名称内包含原名称.
形式为xxx_原名称_xx_xx.新名称的次序是混乱的.要求按照以前的
对应关系把新名称与地县名称对应排列出来.该如何去做?
新名称形式: 求对应地县:
k2_tt3-1_1_1
er2_hg-0_3_1
u3_k1-0_3_1
x3_at5_2_3_2
n4_k2-0_2_0

Answer 1

新名称内包含原名称，那么，在你上面的提问中，从最后一行往上数，倒数第二行的内容是：x3_at5_2_3_2，应该改为：x3_at5-2_3_2， 【at5_2，中的是“中横”，不是“下横”，写错了将影响函数的结果】

根据你的描述，我建立了一张新的Excel表(模拟Excle表)如下：

行 A列 B列 C列 D列 E列 F列
1 原名称 地县名称 新名称形式 求对应地县
2 k1-0 a1 k2_tt3-1_1_1 VLOOKUP 函数
3 k2-0 a1 er2_hg-0_3_1
4 tt3-1 b1 u3_k1-0_3_1
5 at5-2 b1 x3_at5-2_3_2
6 hg-0 c1 n4_k2-0_2_0

在F列的F2单元格中输入以下函数：
=VLOOKUP(MID(TRIM(E2),FIND("_",TRIM(E2))+1,FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1),$A$2:$B$100,2,FALSE)
然后向下复制填充函数。

函数公式说明：
1）TRIM(E2)：
表示去除E2中文本前面、后面的空格（TRIM，不能去掉文本字符之间的空格）

2）FIND("_",TRIM(E2))：
表示查找“_”在除去空格后的单元格E2中第一次出现的位置序号。

3）FIND("_",TRIM(E2))+1
表示查找“_”在除去空格后的单元格E2中第一次出现的位置序号，后面一个字符的位置序号。也就是说：第一个“_”出现位置，其后面一个字符的位置序号。

4）LEN(TRIM(E2))：
表示除去空格后的单元格E2共有有效字符的总数（包括，字符之间的空格）。

5）LEN(TRIM(E2))-FIND("_",TRIM(E2))：
表示除去空格后的单元格E2有效字符总数，减去“_”在单元格E2中第一次出现的位置序号，得到的结果为：第一次出现“_”时，“_”后面的所有的字符个数。

6）MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2)))
表示除去空格后的单元格E2中，从第一个“_”出现位置的后面一个位置的开始，提出第一次出现“_”时，“_”后面的所有的字符个数的字符，也就是第一个“_”出现位置时，“_”后面所有的字符，即：
从除去空格后的单元格E2中，提出文本字符串：tt3-1_1_1

7）FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))
表示，除去空格后单元格E2中第一个“_”出现位置时，“_”后面所有的字符中，“_”第一次出现的位置序号，即：
“_”在 tt3-1_1_1 这个文本字符串中出现的位置序号。

8）FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1
表示，除去空格后单元格E2中第一个“_”出现位置时，“_”后面所有的字符中，“_”第一次出现的位置的前面的字符个数，换句话说：就是E2单元格中包含的“原名称”有多少个字符。即：
除去空格后单元格E2中第一、二次出现“_”之间的字符串的个数，也就是从除去空格后单元格E2中提出的“原名称”字符串的个数，也就是 tt3-1 的字符串个数。

9）MID(TRIM(E2),FIND("_",TRIM(E2))+1,FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1)
表示，在除去空格后单元格E2中，从第一个“_”出现位置的后面一位开始，提出第一、二次出现“_”之间的字符串的个数的文本字符串，即：
除去空格后单元格E2中的文本字符串：tt3-1 ，也就是“原名称”字符串。

10）VLOOKUP(MID(TRIM(E2),FIND("_",TRIM(E2))+1,FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1),$A$2:$B$1000,2,FALSE)
表示，
① 此VLOOKUP函数中搜索条件为：
MID(TRIM(E2),FIND("_",TRIM(E2))+1,FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1)
也就是从除去空格后单元格E2中提出的“原名称”字符串，即：tt3-1，然后在$A$2:$B$1000这个区域中查找对应的值。
因为新命名中，在原名称前面字符个数：有的有3个，有的有4个，所以用提出函数提出“原名称”
② 此VLOOKUP函数的查找区域为：
$A$2:$B$1000，
【区域一定是绝对位置，要不然在复制函数时，区域发生变化，例如：第1行中的A1:B2区域，向下复制时到第2行时，第2行的函数公式查找的区域是A2:B3，就不是原来区域了。】
③ 此VLOOKUP函数中返回查找区的第几列的值：2
④ 此VLOOKUP函数中查找类型：FALSE，精确查找

.................................................................................................................................................
【注意】
1、你的表格每行、每列必须与我给的模拟Excle表中每行每列保持一一对应的位置，如果位置不对，那么就得去修改函数公式，怎么改，那就要你理解这个函数公式，否则你自己就不会改。

2、如果这些数据有很多行，那么函数公式中的【$A$2:$B$100】就需要更改。例如：有1000行，那么，改为$A$2:$B$1000，则修改后的函数为：
=VLOOKUP(MID(TRIM(E2),FIND("_",TRIM(E2))+1,FIND("_",MID(TRIM(E2),FIND("_",TRIM(E2))+1,LEN(TRIM(E2))-FIND("_",TRIM(E2))))-1),$A$2:$B$1000,2,FALSE)

3、原名称这一列的文本字符串，前后不能有空格。

Answer 2

照图中做吧，实际引用范围自己改。

D2公式：

=INDEX($A$2:$A$6,MATCH(TRIM(MID(SUBSTITUTE(C2,"_","      "),5,10)),$B$2:$B$6,))

下拉填充。

Answer 3

提取两个下划线中间的原名称的公式可以是
=MID(A1,FIND("_",A1,1)+1,FIND("_",A1,FIND("_",A1,1)+1)-FIND("_",A1,1)-1)
加上查找对应县，公式就是
设原来的数据在sheet2!A:B
=lookup(MID(A1,FIND("_",A1,1)+1,FIND("_",A1,FIND("_",A1,1)+1)-FIND("_",A1,1)-1),sheet2!A:A,sheet2!B:B)
即可
具体引用位置请自行举一反三

Answer 4

=INDIRECT("A"&MATCH(LEFT(RIGHT(D3,LEN(D3)-FIND("_",D3,8)),FIND("_",RIGHT(D3,LEN(D3)-FIND("_",D3,8)))-1),B:B,0))