Excel无法vlookup事件

最近由于工作关系，深入的用了一阵excel，并遭遇和处理了一系列关于excel数据的问题。

其中最有趣的一个，就是一个无法vlookup的问题。

 

问题记录如下：

excel中直接打开csv文件，看到类似如下的数据表：

SN,DATA
A ,Data1
12345789012 ,Data2
C ,Data3

 

在另外一个数据表中，需要对SN号做关联操作。于是使用常用的vlookup进行关联，但是发现全部数据都只得到N/A：

SN  取值  
A   #N/A  
12345789012 #N/A  
C   #N/A  

 

在两个表里使用查询功能查询相应的sn，都可以查到。

于是使用sublime查看csv文件本身，发现这个csv中的sn号，后面统一附加了一个tab字符（为清晰展现，使用16进制显示）：

00000000: 534e 2c44 4154 410d 0a41 092c 4461 7461  SN,DATA..A.,Data                  
00000010: 310d 0a31 3233 3435 3738 3930 3132 092c  1..12345789012.,                                                 
00000020: 4461 7461 340d 0a43 092c 4461 7461 330d  Data4..C.,Data3.                  
00000030: 0a      

 csv文件来自于一个软件系统，咨询研发，得到的答复是，sn里有一些号是纯数字，如果不加tab，会被excel显示为科学计数法，引发更多问题。

使用正则表达式移除掉全部tab之后，用excel直接打开，发现确实部分sn被显示为科学计数法，且无法恢复原始值。

 

经研究，发现excel对于此种情况，其实有处理方法，就是使用csv数据导入功能而不要直接打开csv，经实验，使用文本数据导入功能，并设定sn字段为文本，即可解决科学计数法问题，并且可以正确的vlookup。

由于太忙，研发团队拒绝对系统进行任何修改。同时，各种表格的数据来源非常复杂，大家基本都已经在基于这些excel数据在工作了。

因此，写了一段vba代码针对无法vlookup的excel文档的sn做了处理，解决了这些文档无法vlookup的问题：

 

Public Sub 去掉SN号两端的空格和TAB()  
    ' TODO:首先把字段设置成文本格式  
    Dim Rng1 As Range  
    For Each Rng1 In Range("A2:A9999")  
        ' 处理科学计数法问题  
        On Error Resume Next  
        If CStr(Val(Rng1.Value)) = Rng1.Value Then  
            Rng1.Value = "'" & Rng1.Value  
        End If  
        Rng1.Value = Trim(Rng1.Value)  
        Rng1.Value = Replace(Rng1.Value, vbTab, "")  
        DoEvents  
    Next  
End Sub  

 

 

使用的时候，需要修改range的范围，和sn的实际范围对应。

 

没有使用UsedRange.Rows.count之类的方法，是因为期间发现了很多人的很多表格，数据可能并不多，但rows.count都是一个巨大的数字。（一般这些数据也都是来自于某软件系统导出的数据，看来非常不靠谱），解决这个问题是另外一个话题了，暂且不表。

 

有一个花絮是，曾经在一个同事的表格，发现了部分匹配成XX的值，实际是因为sn匹配失败，导致得到了其他结果。我深深的怀疑，在此前的工作中，有多少数据是刚好经过了这个坑被计算出来的，这又引发了哪些连锁的数据错误，而这些数据如果刚好被用于计算公司的成本收入或者其他重要的用途，导致的判断错误最终会引发多大的后果。