如何找到打印在Excel中的文字之间的匹配？ [英] How to find the match between text that typo in Excel?
问题描述
问题是第二列的名称与第一列不完全相同。难以匹配他们与一百个名字。
有没有任何公式的excel至少给予宽容与数据像Setyadi与Setiadi或 Tak Jelan与Tak Lejan。
感谢解决方案，它的工作原理，我编辑这是因为我想问更多，
我有另一个问题，你能帮我一下吗？
我有一个这样的数据，我想填写列C与一个数字，根据列D中的名称与列A。另一个问题是，我想匹配只有在一个组中提到的名称在列E中，甚至不在同一行。
从
A | B | c | D | E |
Setyadi | 1 | | Setiadi | 11 |
Tak Jelan | 2 | |德雷安| 11 |
Gordon | 3 | | Herik | 12 |
Alex | 4 | | Goerdon | 12 |
Heri | 5 | |亚历克斯| 12 |
进入
A | B | c | D | E |
Setyadi | 1 | 1 | Setiadi | 11 |
Tak Jelan | 2 | 2 |德雷安| 11 |
Gordon | 3 | 5 | Herik | 12 |
Alex | 4 | 3 | Goerdon | 12 |
Heri | 5 | 4 |亚历克斯| 12 |
我需要的是，我们如何比较列D中提到的组中的名称，然后我们可以自动给出从列B中耦合的列C中的数字。
提前感谢
您可以使用John Walkenbach的SOUNDEX（）函数：
http://spreadsheetpage.com/index.php/tip/searching_using_soundex_codes/
然后把Excel中的代码：
http：// www。 contextures.com/xlvba01.html
现在，如果您有以下数据：
A | B
Setyadi | Setiadi
Tak Jelan | Tak Tak jan jan b b b b b b b b b b>>>>>>>>>>>>>>>>>>>>>>> b b b b b b b b
= SOUNDEX（A1）
将该公式复制到C2，D1和D2。
现在看看你的数据：
A | B | SOUNDEX（ColumnA）| SOUNDEX（ColumnB）
Setyadi | Setiadi | S330 | S330
Tak Jelan |德雷安| T245 | T242
请注意Setyadi和Setiadi是否完全一样，那是因为他们声音同样的，这就是为什么来自SOUNDEX函数的代码回到这样。
现在，当你看到德克兰条目时，你会看到有一个区别为3（从T245到T242）。现在，我会做的是创建一个新的公式，如果第一个字母相同，然后拉出数字，并比较它们的接近程度：
= IF（LEFT（C1,1）= LEFT（D1,1），STDEV.P（MID（C1,2,3），MID（D1,2,3）））
然后你可以比较std偏差。
I have two columns of data with an hundred names on and I need to find the matches.
The problem is when names on the second column are not exactly the same as the first column. Its hard to match them with a hundred names.
Is there any formula in excel for at least give tolerance with the data like "Setyadi" with "Setiadi", or "Tak Jelan" with "Tak Lejan".
Thanks for the solution, it works, I edited this because I want to ask more,
I have another problem, could you help me again?
I have a data like this, I want to fill column C with a number according to match the name in column D with column A. The other problem is, I want to match the name that only in a group that mentioned in column E, even not in the same row.
From
A | B | c | D | E |
Setyadi | 1 | | Setiadi | 11 |
Tak Jelan | 2 | | Tak Lejan | 11 |
Gordon | 3 | | Herik | 12 |
Alex | 4 | | Goerdon | 12 |
Heri | 5 | | Alex | 12 |
Into
A | B | c | D | E |
Setyadi | 1 | 1 | Setiadi | 11 |
Tak Jelan | 2 | 2 | Tak Lejan | 11 |
Gordon | 3 | 5 | Herik | 12 |
Alex | 4 | 3 | Goerdon | 12 |
Heri | 5 | 4 | Alex | 12 |
What I need is, how can we compare the name in group that mentioned in column D, then We can automatically give the number in column C that coupling from column B.
Thanks in advance
解决方案 You can use something like John Walkenbach's SOUNDEX() function:
http://spreadsheetpage.com/index.php/tip/searching_using_soundex_codes/
Then put the code in Excel:
http://www.contextures.com/xlvba01.html
Now, if you had this data:
A | B
Setyadi | Setiadi
Tak Jelan | Tak Lejan
Now you want to add a formula in C1 like:
=SOUNDEX(A1)
And copy that formula to C2, D1, and D2.
Now take a look at your data:
A | B | SOUNDEX(ColumnA) | SOUNDEX(ColumnB)
Setyadi | Setiadi | S330 | S330
Tak Jelan | Tak Lejan | T245 | T242
Notice how Setyadi and Setiadi are exactly the same, that's because they sound the same, which is why the code from the SOUNDEX function comes back like this.
Now when you look at the Tak Jelan entry, you see that there is a difference of 3 (from T245 to T242). Now, what I would do is creat a new formula where if the first letter is the same, then pull out only the number and compare how close they are like:
=IF(LEFT(C1,1)=LEFT(D1,1),STDEV.P(MID(C1,2,3),MID(D1,2,3)))
Then you can compare the std deviation.
这篇关于如何找到打印在Excel中的文字之间的匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
