一些有关Excel的小技巧

就像有些人认为WinForm或WebForm里面拖拉一下控件就能生成界面不高级那样，所以遇到什么问题首先想到的是写代码，尤其是有了诸如LINQPad这类POC工具之后更是方便。但在日常工作中的有些需求，比如数据分析和预处理，通过其它工具比如Excel点点鼠标也能解决，而且可能比写代码还快。Excel有很多强大的功能，这里只简单介绍一下我在Excel里面用到的一些小功能。

▲ 什么？需要编程？Excel里直接点点就出来了，费这事干嘛？

这些功能可以分为三个方面：基本功能、函数和数据透视表。

基本功能

基本功能里常用的有数据分列、去重、“高级”复制粘贴。

数据分列

在Excel中，数据通常是以列来展示的，但是需要处理的数据源可能是以逗号、Tab键、空格或者其它分隔符号分隔的文本或者csv文件，如果直接把内容复制粘贴进来，这些数据都会在一列里面，首先需要做的就是把这些数据按列分开，然后才能进一步的进行分析和处理。

假设我从某个行情网站上复制了交易额前15的股票的信息到txt文件里，现在需要把它导入到Excel里面进行进一步分析。

如果直接把数据复制粘贴到Excel里，在某些低版本的Excel里，文本文件里的每一行数据会挤在Excel里的一个单元格里。需要把这些数据分开到对应的列里。当然，可以使用Excel里面的导入txt/csv文件来实现。

▲在某些低版本的Excel里，直接复制粘贴数据会使得所有的数据都在一列里。

对于已经复制进来的数据，可以选中需要处理的列，点击“分列”按钮，在弹出的配置框里，首先要选择以何种方式进行划分，可以是分隔符或是等宽。分隔符分隔就是按照某些特殊符号对文本进行划分，等宽就是每列按照特定的宽度进行划分，使用这种方式分隔可以直接对着表头单击并拖动来改变宽度。这里使用分隔符分割，这也是最常用的方法。

下一步就是设置分隔符，比如可以以Tab键、空格、逗号、分号或者自定义的分隔符来进行分割，选好分隔符后窗体的分割结果是可以实时预览的。

设置完分隔符之后，还可以进一步设置每一列的数据类型。在某些情况下，如果文本的内容的形式是数字的，那么Excel就自动把他转为数字，如果这一列是文本，那么就非常有必要手动的将这些列设置为文本形式，比如这里的股票代码，应该是文本形式，如果我们不选中设置它的类型为文本，系统就会判断它为数字，就会移除前面的前导0。

点击确认，就可以看到按照预定的分隔操作将数据从单一列分割到不同的列了。

上面的数据仍然有一些瑕疵，比如“相关链接”这一列的数据实际上是包含“股吧资金流数据”这三列的数据，因为是按照Tab键进行分割的，所以这本来应该是一列的数据被划分到了三列，导致数据整体往右移了两位。要处理这种方式很简单，直接删除“相关链接”这一列，然后将“最新价”和“涨跌幅”下面的所有数据删除，删除的时候选择右侧数据往左移即可。整理后的数据如下：

也可以直接就采用导入的方式将txt文件里的内容导入到Excel里面来，在导入的过程中，也会弹出与上述分列的步骤中类似的操作。如下：

在随后的窗体中，Excel 2023非常智能的能够识别列。

但直接采用复制然后粘贴进来的方式，在一些比较高的版本Excel中，它会自动识别剪贴板的内容并完成分列，在Excel 2023中，直接复制粘贴，它会自动分好列。

但这里面其实也有不理想的部分，比如股票代码，我们不希望省略前面的0，很明显Excel将股票代码识别为了数字类型，所以它这里显示为了数字，要恢复前面的0，还需要一些操作。但在分列里面，我们可以很方便的控制每一列的数据类型，从而可以避免出现这种情况。

去重

有些时候，数据里可能有些重复的数据，要将这些重复的数据找出来，如果使用肉眼是很困难的。比如假设上面的数据里面，代码这里面可能不小心输入了重复的数据，需要找出来。

这时，可以选择需要识别出重复内容的列，然后点击“条件格式->突出显示单元格规则->重复值”，在弹出的显示框里选择填充颜色，那么列中存在重复的值就会以选中的样式显示。

上面只是标注出来那些是重复的数据，要删除这些重复的数据，可以选择需要的列，然后点击“去重”

在这里，我故意复制了两行数据粘贴在了末尾。这里只选中了代码这一列，在弹出的去重对话框中，问是否需要扩充选中的列，如果要删除所有行的数据都重复的话，就选择扩充。

这里列出了所有的列，选择所有，点击完成。

可以看到，它找到了2行重复的记录，然后把最后的重复的数据删除了。

“高级”复制粘贴

在Excel中，单元格有时候会包含公式，如果简单的Ctrl-C+Ctrl-V那么就会把这个单元格里面的所有内容都粘贴到了目标单元格里。而在某些情况下，这并不是我们想要的。

能够看到的是当复制内容之后，在单元格内点击鼠标右键，在粘贴部分可以看到有很多选项。

这里面可以粘贴值，粘贴函数，转置函数等等。

快速简单统计

有些时候，我们希望能快速知道选中的数据的简单统计信息，比如最大值，最小值，个数，求和等，当然这些都可以使用函数实现，但在Excel的Sheet中有状态栏，在状态栏上可以快速显示这些统计信息。

状态栏的信息可以调整，方法是在状态栏上右键单击，就可以选择需要统计显示的项。

函数

Excel最强大的地方莫过于它内置了一系列的函数，除了内置的函数之外，我们也可以进行二次开发自己领域内的自定义函数，包括实时函数。相关的信息可以查看浅谈Excel开发：四 Excel 自定义函数、五 Excel RTD函数、六 Excel 异步自定义函数。这里只是介绍在Excel里面内置的一些我平常会用到的一些函数。

统计函数

统计函数包括Min，Max，Avg，Sum等等这些，当然这些简单的统计根本不需要手打公式，如果只是查看的话，上面讲的工具栏即可满足要求。但在有些时候计算的时候还是很有用的。

逻辑函数

包括IF等，在有些情况下还是挺有用。

查找函数

查找函数在Excel中是非常重要的，在有些情况下，它能达到类似数据库里的join的功能。这里我用的非常多的是VLookUp函数。

比如有两张表，一张表有股票代码基本信息，一张表有股票代码对应的其它信息，如何将第二张表里的额外信息，添加到第一张表里的后面，或者查找第一张表中，那些数据不在第一张表里。这些都涉及到将两张表通过一个key来进行拼接。比如下面这个数据，我们要将股票的拼音首字母添加到表中，股票对应的拼音首字母在另外一张表中，或者在同一张表的不同列中，并且它的股票代码的排序跟主表里面的排序不一致。

首先，在“名称”后面添加名为“简称”的一列，然后在第一个单元格里面输入以下函数：

=VLOOKUP(B2,S:T,2,FALSE)

这个函数有4个参数：

第一个参数表示要查找匹配的源单元格地址，在这里就是左边的代码表，它的单元格代号是B2
第二个参数是要查找的表，需要注意的是，这个要查找的列必须是这个表的第一列，我们的查找目标是第S列到第T列，第S列就是代码。
第三个参数是如果查找到了数据，需要返回查找表的第几列，这里，如果匹配上了代码，那么就返回“简称”，因为简称属于目标表的第2列，所以这个参数为2
第四个参数表示匹配的准确度，如果是TRUE表示模糊匹配，FALSE表示精确匹配，一般使用精确匹配。

公式输入完整之后，敲击回车，即可看到结果。接下来双击第一个单元格右下角，即可将所有的单元格都填充上函数。

可以看到，结果非常完美。这里有一点需要注意的是，单元格地址的表示形式，比如相对地址和绝对地址，这里目标表使用的是"S:T"表示从S列到T列所有的数据。

拼接函数

还有一些其它函数，比如重复函数，拼接函数，这里演示两个例子。

比如在第一部分的导入股票数据时，如果利用默认的数据导入，这有可能将股票代码当作数据类型来处理了，这样它的前导0可能就丢失了，比如股票“000063”，就直接变成了“63”，那么如何从“63”恢复为“000063呢”？

这里需要用到3个函数，第一个是重复函数REPT，第二个是长度函数LEN，第三个是拼接符，或者CONCAT。

所以在最后一列的第一个单元格输入以下公式：

=REPT(0,6-LEN(B2))&B2

或者

=CONCAT(REPT(0,6-LEN(B2)),B2)

这个公式的基本思路是：计算出需要补足的0的个数，然后重复这么多个0，然后在后面跟上原来的值。

然后直接双击右下角即可填充所有单元格：

拼接还有一个功能功能是生成SQL语句，这是一个有点常见的场景。比如上面这张表，如何将它导入到SQLServer数据库中，假设数据库中有一张存储股票日线数据的表，名为StockDailyData，这里面存储了这支股票的每天的高开低收成交量成交额涨跌幅等信息。

一种做法就是生成SQL语句，然后在SQLServer里面去执行。这里面就用到了拼接符或者拼接函数。比如可以在最后添加名为“SQL”的一列，并在第一个单元格里面输入：

="INSERT INTO [StockDailyData]"&"([Symbol],[StockName],[Price],[High],[Low]"&",[Open],[Close],[Volume] ,[Day]) VALUES
('"&B2&"','"&C2&"' ,"&E2&","&K2&","&L2&","&M2&" ,"&N2&","&H2&",'2024-03-24')"

如下：