还在用 Excel 函数做数据处理？赶快跳出苦海吧

为什么写这篇文章

实用到爆的10个EXCEL技巧 一文展示了几个很高端的 Excel 数据处理技巧，看上去很强大，但是我作为一个程序员，觉得这么搞简直就是给自己找麻烦，明明用写几行程序就能简单搞定的事，为什么非要到 Excel 里搞得那么复杂呢？而且因为受限于 Excel 提供的那几个函数，还得用非最优解法来做，计算复杂度远不如直接编程实现，遇到大数据无疑会浪费大量计算时间。

因此，决定写篇文章，展示一下怎么用 LINQ 编程处理 Excel 数据。很多人会害怕编程，其实现在很多编程语言的门槛已经是非常低了，入门是非常简单的事，写一些简单的数据处理，从零开始学基础，到写出程序，可能也就是几天的时间，但是受益确绝对不小，之后能用在很多地方。比如，下图就是一个完整的 LINQ 程序示例，是不是足够简单易懂？所以，不要局限于在 Excel 里处理数据了，赶快学一下用编程处理 Excel 数据吧.

简单但完整LINQ示例

为什么选 LINQ

毫无疑问，现在几乎所有主流语言都能比较轻松的处理 Excel 数据，我这里选择用 LINQ 做展示。LINQ 的直观理解是一种较为通用的数据查询语言，把不同来源的数据转为抽象模型相同的数据对象序列，然后提供通用的数据操作符，来在数据对象系列上执行各种数据查询操作，详细的介绍可以参考微软官方文档。LINQ 的使用是相当简单直观的，但是却有着不少优点：

1. LINQ 是一种类 SQL 语言（数据库操作语言），如果有相关知识一学就会，如果不懂学起来也很简单。

2. LINQ 对于扩展非常友好，因此虽然 LINQ 的核心类库操作并不多（大约40个查询操作符，其实也足够用），但是第三方类库提供的额外操作可以说可以满足你的绝大部分需要，比如 MoreLINQ 提供了超过 150 个扩展操作，基本上你想得到的基本操作，都能在里面找到。

3. LINQ 的数据源设计非常灵活，不仅可以处理 Excel 文件（本质是处理 XML 文件），更可以处理传统的关系型数据库、非关系型数据库等等，当然也可以直接处理内存中的数据序列。这意味着如果用 LINQ 写处理代码，之后把数据从 Excel 迁移到了数据库，所有的操作代码都可以一字不动的沿用，仅仅需要改几行代码，切换数据源就可以了。
   

4. LINQ 的执行效率很高，这涉及到其核心机制：不是简单的执行函数，而是从根据你调用的函数生成表达式，再生成执行代码，这提供了大量的性能优化机会。同时，Parallel LINQ 的存在，可以让你不费一点力气，将操作并行化，进一步缩短计算时间。

5. LINQ 可以在包括 C++、VB 的任意 .Net 语言族语言上运行，可选择的编程语言非常多。

   
   

LINQ可以使用多种数据源和上层语言

LINQ 的编程模型

编程模型听起来很高深，其实并不难，举个例子，对于 Excel 表格，编程模型就是一张二维表，基本操作是单元格（Cell），在这之上可以对行、列、或者多个单元格组成的区域做操作。在对行列做操作的时候，其实相当于是在对多个单元格组成的序列做操作。

相比之下，LINQ 的编程模型基于面相对象编程，操作的是一个数据对象的序列，当数据源是 Excel 时，每个数据对象就是 Excel 中的一行，也就说一行中的所有单元格，都会成为对应数据对象的子数据（属性）。

举个例子，如果处理的是用户数据表，表中每一行都对应一个用户，同时有 Id，Name，Password，Account 四个数据列。那么，到了 LINQ 里，就会被转换为一个由用户对象组成的序列，每个用户对象对应表中的一行，有 Id，Name，Password，Account 四个属性，分别对应一行中的四个单元格。

这样的模型，相比表格模型有了简化，可以使用各种各样的针对序列的函数，比如：

1. Slice 截取序列中的一段元素

2. Select 提取数据项的内容，比如将用户对象序列，转化为用户Id的序列

3. Where 筛选符合给定条件的对象

4. Distinct 去除重复元素

5. GroupBy 根据给定条件，将数据分为若干组

6. Except 从序列A中剔除在序列中出现的元素

   
   

毫无疑问，LINQ 提供的函数（包括第三方扩展提供的函数），其实现的功能远远不是 Excel 提供的那些函数 (Excel函数列表)可比的。也因此，基于 LINQ 实现比较复杂的功能时，可以选择更合理、更高效的函数来组合，而不需要去迁就于 Excel 的函数，做出更繁杂、效率又低的实现。

当然，由于 LINQ 使用基于序列的模型，因此，对于一行不是对应一个数据项的表格，比如一些杂乱的统计表格，或者是导出的透视表，是没法处理的。不过，依然可以用 OpenXML 相关工具，来将这类表格当作单纯的二维表处理。而 LINQ 生成的数据，也同样可以使用 OpenXML 相关工具存为所需的格式，比如 Word 文档或者 Excel 表。

实例

接下来结合 实用到爆的10个EXCEL技巧 一文中的例子，简单展示一下怎么使用 LINQ 做数据操作，以及 LINQ 的优势。选用的语言是 C#，有一些编程基础的应该都能看懂，没有编程基础的，看函数名什么的，大概也能理解做了什么。

统计不重复元素个数

实用到爆的10个EXCEL技巧 文中用的公式是 =SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))，然后花了一大段解释原理。

用 LINQ 怎么做？一个链式调用搞定：

sheet.Slice(1，3)
.DistinctBy(e => (double)e["Length"].Value)
.Count()

基本不用解释，Slice 取范围为 [1, 3) 的数据行 (左开右闭，也就是从第1行开始，到3行为止，但不包括第3行) ，DistinctBy 根据 Length 列的值剔除重复元素，Count 统计个数。

顺便说一句，两者的复杂度是天上地下，前者复杂度是 O（N^2）（由 COUNTIF 的复杂度决定，因为 N 个元素亮需要两两比较），后者是 O（N）（ DistinctBy 是基于 Hash 去重的，Hash 操作是常量级，所以整个涉及 N 个元素 Hash 操作的 DistinctBy 操作复杂度只有 O （N），这也是整个方法的复杂度）。

这意味着，遇上大数据量，用基于 COUNTIF 的方法做，如果要处理的数据足够多，比如数据量 N 达到百万的级别，用 Excel 的 COUNTIF 做，需要的时间，从量级上来看大约会是 LINQ 方法的 N 倍。 换言之，用基于 COUNTIF 的方法做，性能问题非常严重。

如果非要在 Excel 里做，可以考虑升级 Excel 版本，Office 365 （Office 2019 好像也有了）已经有了 Unique 函数（Beta），直接一步搞定。实在要用，可以用使用基于 FREQUENCY 函数的版本，其不存在 COUNTIF 版本的性能问题，参考链接 。

连续RANK

问题是：如果我们要让并列的人不占用名次，或者说不管并列多少名，不让排名数字有空档呢？比如100个人里，99个都考了100分，则考了98分的人，是第100名，还是第2名？

文中给的解法是 =SUMPRODUCT(($B$2:$B$7>B7)/COUNTIF($B$2:$B$7,$B$2:$B$7))+1，大概思路和去重一样，把大于X的元素映射为 1/X的重复数，然后求和加1。

方法没错，但是逻辑好不好？反正我觉得学过算法的人，没人会这么干。首先方法依然很不直观，同时因为 COUNTIF 的存在直接把复杂度拉到了 O（N^2），而排序的复杂度只有 O（NlogN），也就是说上到大数据性能问题严重。

那么，用 LINQ 怎么做？很简单，GroupBy 将相同值的元素分组，然后对组排序，组的序号也就是我们所要求的序号了。复杂度和排序一样是 O（NlogN）。代码如下：

var groups = sheetRows.GroupBy(e => (double)(e["Length"].Value)); // group items by length
var groupsRank = groups.RankBy(group => group.Key); // rank groups
var groupsRankEnumerator = groupsRank.GetEnumerator();
foreach (IGrouping<double, Row> group in groups) {
groupsRankEnumerator.MoveNext();
var groupRank = groupsRankEnumerator.Current;
foreach (Row row in group) { rowRankHandler(row, groupRank); }

}

核心代码只有两行，第一行对数据进行分组，分组后所有 Length 相同的数据在一个 Group 里。第二行依据 Group 里数据的 Length，对 Group 排序。后面的5行代码仅仅负责遍历所有 Group 中数据和其对应的 Rank（也就是 Group 的 Rank），调用 rowRankHandler 进行用户定义的处理，比如进行输出。

简单的例子执行效果如下：

根据多个数据项RANK

问题是：如果有多个数值，在并列的时候需要做第二次排序呢？

实用到爆的10个EXCEL技巧 文中给解法是 =RANK(B2,$B$2:$B$7)+SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7>C2))

这个方法的本质，其是在根据第一个排序项 RANK 后，自己手动对相同 RANK 的数据项，也就是子序列，再根据第二个排序项做一次 RANK ( 通过 SUMPRODUCT 统计对应元素个数 )。但是由于不是直接对子序列做 RANK，而是对每个元素分别统计，引入了大量的重复工作，即对单个元素 SUMPRODUCT 复杂度 O（N），整体对 N 个元素操作，复杂度 O（N^2）；相比 RANK 自身的复杂度 O（NlogN），性能问题依旧严重。

此外，如果要根据三个排序项排序怎么办？继续这么叠加写？如果更多呢？写出来估计没啥人看得懂了，也很难改吧？

那么，正确的解法是什么？很简单，根据多个排序项，生成一个比较函数，这个比较函数很简单，依次比较排序项，如果第一项比出了结果（不相等），则返回比较结果；否则，继续比较下一项，直到某一项有结果，或者所有比较项都比完。然后直接用这个比较函数，对序列进行 RANK 即可。用 LINQ 实现，代码如下：

var comparer = KeyComparer<Row>.OrderBy(e => e["Type"].Value.ToString())
.ThenBy(e => (double)e["Length"].Value)
.ThenBy(e => (double)e["HP"].Value);
var rowsRank = sheet.Rank(comparer);

var rowsRankEnumerator = rowsRank.GetEnumerator();
foreach (var row in sheet.ToArray()) {
rowsRankEnumerator.MoveNext();
var rowRank = rowsRankEnumerator.Current;
rowRankHandler(row, rowRank);
}

核心是前两个语句，第一个语句生成所需的比较函数，其实现只需要简单申明需要依次比较那些数据列即可；第二个函数直接简单调用RANK即可完成排序。后面的代码只负责输出。输出效果如下：

显然，比起 实用到爆的10个EXCEL技巧 文中的方法，更为简洁明了，可以易于扩展和修改，多加比较项只要加一行 ThenBy 即可；同时比较的内容也不限于一列，可以提取一列中的某些内容，也可以结合多列内容进行比较。同时，和使用 RANK 对单个数据项排序相比，仅仅是比较两个数据时，要多若干常数级别操作，复杂度依然维持在了 O（NlogN），完全不存在性能问题。

实现VLOOKUP

VLOOKUP是很好用的函数，LINQ 里没有，那么怎么办？自己写一个更灵活的呗。函数体如下：

var selectRowOrNull = rows.Slice(rowStart, rowEnd)
.FirstOrDefault(row => columns.Any(col => valueComparator(cellValueSelector(row[col]), lookUpValue)));

if (selectRowOrNull != null) { return cellValueSelector(selectRowOrNull[columns[selectColumnIndex]]); }
else { return default; }

实现非常简单，首先和之前一样，Slice 截取指定的数据行；然后 FirstOrDefault 晒选数据行，获取第一个满足条件的数据行，如果没有则返回空。筛选条件也很简单，columns 是所有需查找的数据行名称，Any 函数测试数据行对在这些行上的数据，是否有和待查找值 lookUpValue 相等的。之后就是看是否找到（返回结果是否为空），如果找到则返回 selectColumnIndex 列上对应的值，或者在没有找到时返回空值。

为什么说这个实现比 VLOOKUP 灵活？有以下几点：

1. 不必指定连续区域，而是可以通过列名（数组 columns）指定多个离散的列，一方面提高效率，另一方面可以减少因为表格修改而出错的概率。

2. cellValueSelector 可以对表格的值做一些预处理，比如将大写形式的数值转为数字，然后就可以和纯数字的数值比较；或者将文本中的无用信息删去。

3. valueComparator 可以指定如何对两个元素做比较，比如，对于字符串，可以在调用 String 的 Compare方法是设置 ignoreCase 为 true，实现不区分大小写的比较。
   

使用时很简单，比如要查找的值为 "自由高达"，在 [2, 7) 行以及 "Id", "Length" 两列查找，比较元素时使用 String.Compare 做大小写不敏感的比较，最后返回序号为1的 "Length" 列的内容，代码如下：

var findValue = Vlookup("自由高达", 2, 7, new string[]{ "Id", "Length" }, 1, (str1, str2) => String.Compare(str1, str2, true));

不比 VLOOKUP 代码长多少，但是更灵活，而且要做什么一目了然，可读性更好。

示例项目

目前，我建了个项目，演示了一下怎么在 C# 用 Linq 做同样的事，包括了文中的几个例子。项目链接 ，Readme有安装说明，Program.cs是例子，后面还会继续添加例子。有兴趣的可以一试。

总结

建议有复杂数据操作需求的，能编程就编程，比起在Excel里折腾，实现简单、执行效率高（Linq还可以行发数据操作），也便于修改和维护。说到底，Excel 更多的是一个数据可视化软件，用内置公式来拿来搞复杂的数据操作和分析，本身就不是其擅长的事，就和拿C做面向对象编程一样，不是不可以，但是大抵等于是在折腾自己的生命。