为什么写这篇文章
实用到爆的10个EXCEL技巧 一文展示了几个很高端的 Excel 数据处理技巧,看上去很强大,但是我作为一个程序员,觉得这么搞简直就是给自己找麻烦,明明用写几行程序就能简单搞定的事,为什么非要到 Excel 里搞得那么复杂呢?而且因为受限于 Excel 提供的那几个函数,还得用非最优解法来做,计算复杂度远不如直接编程实现,遇到大数据无疑会浪费大量计算时间。
因此,决定写篇文章,展示一下怎么用 LINQ 编程处理 Excel 数据。很多人会害怕编程,其实现在很多编程语言的门槛已经是非常低了,入门是非常简单的事,写一些简单的数据处理,从零开始学基础,到写出程序,可能也就是几天的时间,但是受益确绝对不小,之后能用在很多地方。比如,下图就是一个完整的 LINQ 程序示例,是不是足够简单易懂?所以,不要局限于在 Excel 里处理数据了,赶快学一下用编程处理 Excel 数据吧.
简单但完整LINQ示例
为什么选 LINQ
毫无疑问,现在几乎所有主流语言都能比较轻松的处理 Excel 数据,我这里选择用 LINQ 做展示。LINQ 的直观理解是一种较为通用的数据查询语言,把不同来源的数据转为抽象模型相同的数据对象序列,然后提供通用的数据操作符,来在数据对象系列上执行各种数据查询操作,详细的介绍可以参考微软官方文档。LINQ 的使用是相当简单直观的,但是却有着不少优点:
LINQ 是一种类 SQL 语言(数据库操作语言),如果有相关知识一学就会,如果不懂学起来也很简单。
LINQ 对于扩展非常友好,因此虽然 LINQ 的核心类库操作并不多(大约40个查询操作符,其实也足够用),但是第三方类库提供的额外操作可以说可以满足你的绝大部分需要,比如 MoreLINQ 提供了超过 150 个扩展操作,基本上你想得到的基本操作,都能在里面找到。
LINQ 的数据源设计非常灵活,不仅可以处理 Excel 文件(本质是处理 XML 文件),更可以处理传统的关系型数据库、非关系型数据库等等,当然也可以直接处理内存中的数据序列。这意味着如果用 LINQ 写处理代码,之后把数据从 Excel 迁移到了数据库,所有的操作代码都可以一字不动的沿用,仅仅需要改几行代码,切换数据源就可以了。
LINQ 的执行效率很高,这涉及到其核心机制:不是简单的执行函数,而是从根据你调用的函数生成表达式,再生成执行代码,这提供了大量的性能优化机会。同时,Parallel LINQ 的存在,可以让你不费一点力气,将操作并行化,进一步缩短计算时间。
LINQ 可以在包括 C++、VB 的任意 .Net 语言族语言上运行,可选择的编程语言非常多。
LINQ可以使用多种数据源和上层语言
LINQ 的编程模型
编程模型听起来很高深,其实并不难,举个例子,对于 Excel 表格,编程模型就是一张二维表,基本操作是单元格(Cell),在这之上可以对行、列、或者多个单元格组成的区域做操作。在对行列做操作的时候,其实相当于是在对多个单元格组成的序列做操作。
相比之下,LINQ 的编程模型基于面相对象编程,操作的是一个数据对象的序列,当数据源是 Excel 时,每个数据对象就是 Excel 中的一行,也就说一行中的所有单元格,都会成为对应数据对象的子数据(属性)。
举个例子,如果处理的是用户数据表,表中每一行都对应一个用户,同时有 Id,Name,Password,Account 四个数据列。那么,到了 LINQ 里,就会被转换为一个由用户对象组成的序列,每个用户对象对应表中的一行,有 Id,Name,Password,Account 四个属性,分别对应一行中的四个单元格。
这样的模型,相比表格模型有了简化,可以使用各种各样的针对序列的函数,比如:
Slice 截取序列中的一段元素
Select 提取数据项的内容,比如将用户对象序列,转化为用户Id的序列
Where 筛选符合给定条件的对象
Distinct 去除重复元素
GroupBy 根据给定条件,将数据分为若干组
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 灵活?有以下几点:
不必指定连续区域,而是可以通过列名(数组 columns)指定多个离散的列,一方面提高效率,另一方面可以减少因为表格修改而出错的概率。
cellValueSelector 可以对表格的值做一些预处理,比如将大写形式的数值转为数字,然后就可以和纯数字的数值比较;或者将文本中的无用信息删去。
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做面向对象编程一样,不是不可以,但是大抵等于是在折腾自己的生命。
大多数人不是程序员,excel函数对大多数人更友好
为什么要用复杂的函数/编程语言来解决这几个问题,Excel里面一两分钟点几下鼠标结果不就有了: 统计不重复元素个数:1. 转换为表格;2. 通过数据表汇总,加入数据模型;3. UserName非重复计数。 连续RANK:1. 转换为表格;2. 复制出一张Helper表;3. Helper表Length列删除重复值并排序;4. 新建Rank列无脑填充1-5;5. 原表根据Length列的值vlookup出Rank; 根据多个数据项RANK:1. 自定义排序,Type降序&Length降序&HP降序;2. Rank列无脑填充1-7;
很复杂 不如写python
行政和会计宁愿等剧也不会看一眼的,放心吧。你这还不麻烦?关键Excel她们能看懂啊。。。。
真的需要做复杂数据处理的自己都会去研究用什么合适的工具,不需要的明显excel就够了 硬推linq是个什么想法。重点不对吧
最关键的是excel给别人看的时候,没有函数计算只有数据结果的话毫无说服力啊,用自己写的程序跑数据对别人来说和和造假没区别的。就比如年会抽奖,你用c#自己写个不开放源码的抽奖程序,请问哪个会承认这样的结果;除非你给别人看数据的同时先提交源码然后当场编译。
用excel内置的vba也能解决,也可以是面向对象,为什么选择LINQ
能用函数尽量函数,函数无法实现才上vba,函数的美感是vba不可比拟的,至于这种外挂不作评价
这样直接怼人家真的好吗?通篇说人家不好,再怎么不好人家自己觉得好用的愿意分享出来就值得尊重
写代码,仅这一条就足以吓退99.999%的excel用户,功能再强也没意义。
这里都是大佬,顺便请教一下,想学一门语言用来做工程上的计算,可能需要结合cad,同时具有一定的大量数据处理能力,学哪个比较适合????
我比较关心的是,Excel里面直接嵌套LINQ来运行?还是要这个程序?怎么做?6年没写代码了,找回C#的难度不大
我一直以为Linq是一个C#的库,敢情现在独立出来了?
不是有个power bi吗?
所以为了处理个excel还需要先装一个Visual Studio?用python+pandas+jupyter不是更好的选择吗?
复杂一些的我都是导入到oracle,一顿select操作,excel函数整的我头大
下班后,我在什么值得买上看代码
楼主的教程本身没毛病。Excel用的多的人无非是财务,销售,运营占大头。财务销售用的EXCEL表数据量通常不会太大。不管是用函数还是ACCESS都能应付。运营同学的数据量会大一些,好学的,会去学Python,R,SQL,甚至.net java这类。安于现状的,自然是函数到底。处理数据方法千万条,就看处理数据的人有多懒了
我感觉这篇文章好像是喷了另外一个文章