Question about Excel

jasonliu234

原帖由 bats 于 2012-7-23 19:19 发表

从后往前，根据最长suburb名字match，所以要字典

请问如何实现？

jasonliu234

原帖由 bats 于 2012-7-23 19:21 发表

的确如此。这个属于raw data问题没办法。但是如果经过观察，或许可以全部replace再match，因为可能mt都可以改成mount，或者设立简写词条加入进去

这只是一个案例，可能还有更多缩写，防不慎防的。

而且这和空格方法比，对raw data更改的要更多。lz 的初衷就是尽量少的 做更改。

jasonliu234

这是两种思路

根据内容分割

根据格式分割。

如果能看到更多的原始数据，可能可以就知道哪个方法更好了

kaixin123

谢谢楼上的同志们，真是好同志啊，先感谢大家！鞠躬！
其次，相信你们也注意到了，地址内容是不规则的，不止是空格，两个空格出现的区域可能在street，也可能在suburb 名字里，或者干脆就在street 和suburb之间一个空格或者没空格，反正是不大规律，=RIGHT(a1,LEN(a1)-FIND("  ",a1)-1)这个是不错的formular，在这里的作用和9楼的结果上差不多

其实还有一个问题，我是为了和另外一个表格里的地址内容做比较才抽取的suburb，有的地址在这个表格里是CNR A ST & B ST BURWOOD在另外一个表格里写1 A ST BURWOOD,如果我用公式校对，结果就不match,如果查google map，两个地方位置基本一样，那我怎么办啊？不能随意说见到是同一个suburb又是提到的street 就说它俩可以视为一个地址吧？所以才先比较的suburb，state问题不大

大饼

原帖由 jasonliu234 于 2012-7-23 19:15 发表
而且 lz 的 suburb名字，本身并不严谨

比如第一行 mt gravatt

但是suburb，正式名字 却是 Mount Gravatt

vlookup根本找不到

找不到的高亮，人工改

jasonliu234

原帖由 大饼 于 2012-7-23 19:41 发表

找不到的高亮，人工改

人工改的话，那就什么都可以改了

空格方法 也可以改的

目标就是尽可能少的改动原始数据

而且，用vlookup 找suburb的方法，我还是没有想出如何实现。

因为suburb 的名字可能一个单词，可能几个单词。如何和前面的street区分，也是问题

bats

原帖由 jasonliu234 于 2012-7-23 19:23 发表


请问如何实现？

一系列公式，假设h to j could be suburb names，if column j match the last word of surburb then continue to try column i&column j...till h,loop here.once found quit loop
不难实现if dictionary is sorted correctly
不loop也可以但是公式可能很复杂

[ 本帖最后由 bats 于 2012-7-23 19:56 编辑 ]

jasonliu234

原帖由 bats 于 2012-7-23 19:51 发表

一系列公式，假设h to j could be suburb names，if column j match the full surburb then ok, if not try column i&column j...till h
不难实现if dictionary is sorted correctly

这个假设就不成立

街道名字 有长有短， suburb 也是有长有短。

分成一系列单独的单词后，如何知道哪些是street 哪些是suburb？

而且如果j match 了整个suburb，但是j 和  i，合起来才是suburn的名字，
比如J 是burwood, i 是north。
north burwood 才是suburb

有如何区分？

内容上的问题：

1. 缩写问题
2. surburb 单个单词和数个单词的组合。
3. 如何区分哪些单词属于street，哪些单词属于suburb

尤其是第三个问题，并不好解决。

这与格式划分比：

1.难以实现，
2.复杂的公式
3.要更多的 手工更改原始数据

[ 本帖最后由 jasonliu234 于 2012-7-23 20:36 编辑 ]

bats

原帖由 jasonliu234 于 2012-7-23 19:55 发表


这个假设就不成立

街道名字 有长有短， suburb 也是有长有短。

分成一系列单独的单词后，如何知道哪些是street 哪些是suburb？

从后往前穷举。从最长suburb开始尝试，match就退出

jasonliu234

原帖由 bats 于 2012-7-23 19:59 发表

从后往前穷举。从最长suburb开始尝试，match就退出

总要让lz知道如何操作吧，

这样吧，麻烦写个公式，不用多，先把lz列出来的那些数据，区分开来就行。

kaixin123

同求！谢谢bats先 (paopaobing(79))

kaixin123

也要感谢jasonliu234，帮我把想说的，没说的展开了，才容易理解~ (paopaobing(78))

jasonliu234

我也在想如何写公式

真的不好写

难为那位同学了

[ 本帖最后由 jasonliu234 于 2012-7-23 20:36 编辑 ]

kaixin123

是啊，从后往前找，没试过 ：（
辛苦了bats童鞋

bats

如附件，基本的都在那了，其他的靠你自己推导，写着累(这个有错误，fix在下面）

[ 本帖最后由 bats 于 2012-7-24 09:17 编辑 ]

bats

column t-z是我推导过程的实验，没用的可以删除

bats

其他要你自己弄的：1，最长suburb namein字典；2.根据最长suburb name,o与p之间还要多几个column,还有
3.适当排序避免名字干扰

[ 本帖最后由 bats 于 2012-7-23 21:37 编辑 ]

Mia888

好多EXCEL高手呀，MARK一下！

kaixin123

是啊，而且愿意分享知识帮我们，不止是牛人，还是好人！(paopaobing(85))

elephant

膜拜+学习

bats

昨晚没测试，公式有一些错误，对不起哈
见附件，经测试应该符合要求
其实还挺复杂的，盖了几次了才符合要求。完全找不到suburb会显示错误

[ 本帖最后由 bats 于 2012-7-24 14:47 编辑 ]

dingding.ann

高手真心多，想想自己好惭愧！

chychychy

围观高手中。。。

tvc889

我觉得我的解决方案挺好的。

在楼主的数据上测试，100%正确率。

请见附件。

原帖由 kaixin123 于 2012-7-23 12:50 发表
感谢bats童鞋的帮助，感谢你特意帮忙写公式，谢谢！

Best answer so far in level 9! Thanks Jasonliu234 ^_^

I would like to split the suburb name and street/rd/ave from the address given. Would anyone have an  ...

tvc889

怎么才能发excel附件？总说不能。

bats

原帖由 tvc889 于 2012-7-24 10:42 发表
我觉得我的解决方案挺好的。

在楼主的数据上测试，100%正确率。

请见附件。

这个也是个很好的办法，根据路和空格一起来区分。不过可能问题是空格和路名的标准，如果有一些地址同时没有ave，rd之类的，空格又不对，就不行了。例如row9如果没有way。就是“1 WOOLWORTHS BELLA        VISTA”，你的公式就会得到vista为suburb，不过lz数据也可能没有这个问题，难说。
还有一个问题是除了lz列的，还有没有别的路的写法。例如“1 WOOLWORTHS STREET BELLA        VISTA”，
而用suburb的话，澳洲最多才5个words的suburb名字,比较肯定。当然前提是suburb输入准确阿，呵呵，哪种好取决于源数据

[ 本帖最后由 bats 于 2012-7-24 11:12 编辑 ]

tvc889

楼主可以扩展我的路名的例子。澳洲的suburb很多，路名的类型有限的多了。

原帖由 bats 于 2012-7-24 11:08 发表

这个也是个很好的办法，根据路和空格一起来区分。不过可能问题是空格和路名的标准，如果有一些地址同时没有ave，rd之类的，空格又不对，就不行了。例如row9如果没有way。就是“1 WOOLWORTHS BELLA        VISTA”，你的公式就会 ...

bats

原帖由 tvc889 于 2012-7-24 11:27 发表
楼主可以扩展我的路名的例子。澳洲的suburb很多，路名的类型有限的多了。

suburb一共15612个,post office网站可以下载做成字典，保证正确。你的路名例子如果源之中的路名，空格之一不规则，例如我上面说的，就只能增加column增加条件。
如果路名，空格同时不和你设定的规则，例如171 CAMBERWELL CAMBERWELL。这样SUBURB和路名之间只有一个空格，你的方案似乎没有区分开来的途径-你可以试验下。
偶说了，取舍取决于源数据，纯技术探讨哈

jasonliu234

原帖由 bats 于 2012-7-24 09:06 发表
昨晚没测试，公式有一些错误，对不起哈
见附件，经测试应该符合要求
注意row17的例子。如果raw data有错误，是没有办法的哦。如果是简写如nth，可以用whole cell match replace

牛b阿

还在消化中。

tvc889

的确是要看楼主的数据。你的方法也很好。

原帖由 bats 于 2012-7-24 11:34 发表

suburb一共15612个,post office网站可以下载做成字典，保证正确。你的路名例子如果源之中的路名，空格之一不规则，例如我上面说的，就只能增加column增加条件。
如果路名，空格同时不和你设定的规则，例如171 CAMBERWELL CAMBER ...