Excel 疑难杂症
Excel解决不了或者解决起来比较复杂的问题,可以借助插件及简单编程来解决,具体实例分享。
LuckJudy
这个作者很懒,什么都没留下…
展开
-
在全表查找字符串
要求查找每个表格的每个单元格,只要匹配指定的字符串比如foo,就列出该行完整数据。Excel有两个sheet,sheet里有结构相同的表格。符号 | 用于合并两个序列。原创 2024-09-13 17:39:31 · 223 阅读 · 0 评论 -
拆分文字中的字符
函数 E@1 将片区转为一维序列,~ 表示当前成员,split 拆分文字,conj 合并。原创 2024-09-12 15:49:49 · 121 阅读 · 0 评论 -
计算一批集合中包含指定成员的次数
上面公式只算出了一格,需要通过拖拽算出其他格,并避开对角线(自己和自己同桌无意义)。注意整个矩阵会重复算两遍,所以只要拖拽出三角形区域就可以了。函数 group 用于分组,# 表示当前成员的序号,~ 表示当前成员,函数 contain 计算指定的多个项目是否都是某个序列的成员。有一个用Excel表格表示的赛事分组图,从C1:V13,每6列表示四个选手的一桌比赛,1-16表示16位选手的编号。请计算出任意两名选手在同一桌的次数,用矩阵图表示。原创 2024-09-11 17:06:04 · 396 阅读 · 0 评论 -
拆分 IP 地址后再分类
函数 E@p 对表格进行横纵转置,E@1 将表格转为一维,group 分组,~ 表示当前成员,\表示除法取整数部分。请将IP的第2节均分为四份,即0-63、64-127、128-191、192-256,分别写入四列。原创 2024-09-10 15:18:02 · 305 阅读 · 0 评论 -
根据规则将一行拆成多行
函数 E@b 去掉表格的标题,news 根据规则将一行拆为多行,100.iterate 循环迭代 100 次,符合条件时停止迭代(这里假设最多拆分 100 行,可根据需要扩大)。~ 表示序列的当前成员,~1 表示当前成员的第 1 个子成员,符号 | 用于合并序列。要求将一行拆成多行,规则是:如果Amount小于50000则不拆分;如果Amount大于50000,则每30000拆成一条记录。某Excel表格如下。原创 2024-09-05 17:22:29 · 734 阅读 · 0 评论 -
把空格对应的表头拼接起来
函数 pselect 找到符合条件的成员的位置,默认返回第 1 个成员的位置,@a 表示返回所有成员的位置。to(2,) 表示取序列的第 2 到最后一个成员。要求处理每行,把空数据格对应的表头用斜线拼起来。某Excel表格有表头,部分数据格为空。原创 2024-09-04 15:26:25 · 333 阅读 · 0 评论 -
挑出重复的行
函数 group 对表格进行分组,@u 表示保持原有顺序。~ 表示当前成员,to(2,) 表示从第 2 到最后一个子成员。Excel表格有4列。第2-4 列作为一个整体,存在重复的情况。请在保持原有顺序的情况下,挑出重复的行,形成新表格。原创 2024-09-03 16:18:53 · 306 阅读 · 0 评论 -
删除重复的行
函数 group 对行分组,select 筛选符合条件的组,conj 合并各组。Excel的有7列数字,有些数字整行重复。原创 2024-09-02 14:24:25 · 192 阅读 · 0 评论 -
将每一列的每 3 行的格值拼进一个格子
Excel的表格不规范,每三行对应一条记录,纵向每三列对应一格。请规范化表格,把每一列的每3行的格值拼进一个格子,用空格分隔。函数 E@2p用于二维表转置,group 分组,# 是行号。原创 2024-08-30 13:54:17 · 272 阅读 · 0 评论 -
从每 N 行找出需要数据拼成一行
Excel某表格不规范,每两行6列对应规范表格的一行3列,分别是:第1行第1列或第2行第1列(两者重复,取其一即可)、第2行第2列、第1行第3列。函数 group 按规则对表格分组,符号 #是行号,符号 \ 表示除法取整数部分,~1(1) 表示当前组的第 1 行第 1 列。使用 SPL XLL。原创 2024-08-29 14:08:33 · 344 阅读 · 0 评论 -
取出每组最后一行
函数 E 用于解析 Excel 片区、Excel 日期格式,groups 用于分组汇总,maxp 找到最大值所在的行。要求:找出每组数据中DATE列最大(日期最晚)的一行,取出ACTION列。Excel某表格有3列,其中ID是分组列。使用 SPL XLL。原创 2024-08-28 14:04:48 · 193 阅读 · 0 评论 -
Excel 保持原序时计算组内排名
函数 E 将片区转为表格,derive 增加新列,rank 计算排序后的组内记录的排名,# 是行号,#1 是第 1 列。Excel某表格第1列是分组,第2列是日期,未排序。使用 SPL XLL。原创 2024-08-27 13:34:22 · 537 阅读 · 0 评论 -
EXCEL 分组后找出满足条件的行拼接起来
函数 group 执行分组并处理每组数据,~2 表示当前成员的第 2 个子成员,$[] 是字符串标志,# 是当前成员的序号,#1 是表格的第 1 列,concat@c 用逗号合并成员。现在要按第2列分组,找到每组第4列等于"done"的行,将这些行的第3列用逗号拼起来,再与分组名、行号组成新的表格。Excel某表格有四列,其中第2列是分组列。使用 SPL XLL。原创 2024-08-23 16:48:58 · 501 阅读 · 0 评论 -
Excel 中找出每列第一个和最后一个非空格对应的行--Excel难题#87
函数 E@2p 对二维序列进行转置。pselect 找到符合条件的第 1 个成员的位置,@z 表示从后往前找。to(2,) 取第 2 至最后一个成员,(1) 第 1 个成员。要求从第2-6列的每列,找出第一个和最后一个非空格,把它们对应的第1列的格值用"to"拼起来。Excel表格的第2-6列有空格。使用 SPL XLL。原创 2024-08-22 17:36:28 · 363 阅读 · 0 评论 -
EXCEL 分段排序--Excel难题#86
现在要求对表格按照第3列进行分段排序,由小到大排列。第1段:第3列小于等于50;第2段:第3列介于700和720之间;第3列是其他情况,即第3列大于50并小于700,或大于720。函数 enum 用于枚举分组,分组依据是字符串表达式,$[] 是字符串标志,@n 表示其他情况单独分一组。~ 表示序列的当前成员,~3 表示序列的第 3 个成员。Excel某表格有3列。使用 SPL XLL。原创 2024-08-21 13:46:44 · 535 阅读 · 0 评论 -
找出满足条件的分类--Excel难题#85
函数 groups用于分组汇总,~1 表示成员的第 1 个子成员,#1 表示表格的第 1 列。Excel表格的第1列是分类,第2列是明细,有ture、false两种值。要求找出明细里不含true的分类,即Andy、Tim。使用 SPL XLL。原创 2024-08-20 14:00:23 · 188 阅读 · 0 评论 -
根据格值把行复制多遍--Excel难题#84
函数 conj 用来合并序列成员,run 修改序列,"整数 N* 序列" 表示把序列成员复制 N 遍。请根据第3列的格值把每一行都复制多遍,原来的第3列显示为空格。Excel表格的每行代表一种产品,第3列表示该产品数量。使用 SPL XLL。原创 2024-08-19 17:15:15 · 196 阅读 · 0 评论 -
分组并合并其它列的非空值 --Excel难题#83
函数 group 用于分组并处理每组,ifn返回序列中第一个非空成员,每个成员都是空则返回空。符号~表示当前组,~1表示当前组的第 1行,# 表示当前成员的序号。Excel第1列是分类,第2-42列是平行的多个数据项列,下表用部分列示例。数据有X或null两种情况,同一个分类的同一列数据偶尔有重复。要求按分组合并每列的数据,如果有重复数据X,则只显示一个。使用 SPL XLL。原创 2024-08-18 15:00:00 · 210 阅读 · 0 评论 -
计算两个字串的差异
符号 ~1 表示当前成员(行)的第 1 个子成员,\ 用于求差集,$[] 是字符串标志。请找出两者的差异,即在A列但不在B列的项目。使用 SPL XLL。原创 2024-08-17 14:30:00 · 149 阅读 · 0 评论 -
跳过空格做排序
函数 E@1 将片区转为单层序列。pselect@a 选出符合条件的所有成员的位置。"序列 1(位置集合)= 序列 2" 可将序列 1 的指定位置的成员修改成序列 2。Excel用A、B两列组成了目录结构,其中A列的日期没有按顺序排序。要求对A列的日期按顺序排序,保持空格的位置不变。使用 SPL XLL。原创 2024-08-16 14:23:18 · 143 阅读 · 0 评论 -
分组汇总时保留不变列
要求:低版本的Excel(不支持groupby函数),按ID分组,对Value1和Value2求和,保留Descr 1和Descr 2。Excel表格的ID列是分类,Value1和Value2是数值,ID相同时Descr 1和Descr 2保持不变。函数 E 将片区转为表格,groups 对表格分组汇总,计算出的新列可以用冒号改名。使用 SPL XLL。原创 2024-08-14 16:22:29 · 393 阅读 · 0 评论 -
分组汇总树状结构表
函数 group@i 用于条件分组,~ 表示当前组,函数 new 新建表格,ifn 返回第一个非 null 的成员(当前组汇总结果为 null 时可返回 0)。现在要按最高层Epic分组,对Hour列汇总,同时保留Code列。有一个树状结构的Excel表,Epic列是最高层。使用 SPL XLL。原创 2024-08-13 15:44:17 · 251 阅读 · 0 评论 -
列方向区间筛选并聚合
函数 pselect 可获取符合条件的成员的位置。(N)表示第 N 个成员,N 是整数序列时表示相应位置上的多个成员。to(2,) 表示取第 2 到最末的成员,~ 表示当前成员。Excel表格的列由按顺序排列的月份组成,月份是日期反显而成,比如Jan实际是1/1/2023。现在要根据G1、H1里的月份参数,对这两个起止区间内的数据进行求和。使用 SPL XLL。原创 2024-08-12 15:24:25 · 107 阅读 · 0 评论 -
列方向分类聚合
函数 E 用于表格转换,@p 表示转置,@b 表示不含标题,@2 表示两层序列。groups 分组汇总,${} 把字符串当表达式执行,eval@s() 循环替换序列里的字符串,并当作表达式执行。concat@c 用逗号合并序列。Excel表格有多个重复的列。要求按列进行分类求和。使用 SPL XLL。原创 2024-08-11 16:00:00 · 163 阅读 · 0 评论 -
将重复项替换指定文字
函数 E 将片区转为二维表,group@1分组并取各组第 1 行,运算符 \ 计算两个集合的差集。要求在第2列中保留第一次出现的项,并将重复项替换成字符串"cont.".Excel表格第1列是序号,第2列有重复项.原创 2024-08-10 16:30:00 · 159 阅读 · 0 评论 -
匹配格值的前半部分
函数 transpose@n 用于序列转置,E@1 将多层序列转为单层。substr@l在一个字符串中搜索符号并返回符号之前的子串,注意是字母 l。pos判断序列是否包含某个成员,~ 表示当前成员。Excel有多列含空格的源数据,如C3:D19;还有若干用于比较的数据项,由"-"隔为前后两部分,如F3:F7。上面用一个公式处理了所有列,也可以用下面公式先在 C21 处理 C 列,再将公式拖动到其他列。要求用源数据的每列与数据项的前半部分进行比较,将匹配上的数据项填在该列下面。使用 SPL XLL。原创 2024-08-09 16:49:47 · 231 阅读 · 0 评论 -
每组中随机选一行
函数 E 将片区解析为表格,sort(rand()) 随机排序,group@1 分组并取每组第 1 条记录。Excel的A列是分组,B列是明细。要求从每组中随机选出一行。使用 SPL XLL。原创 2024-08-06 16:12:31 · 307 阅读 · 0 评论 -
去除重复项后再求交集
函数 isect 求交集,group 分组但不汇总,select 过滤,~ 表示当前成员。Excel有两列数字,列内有重复项。要求先去除两列内的重复项,再求交集。使用 SPL XLL。原创 2024-08-05 14:09:05 · 193 阅读 · 0 评论 -
计算两个单元格内容之间的差异
符号 \ 可计算差集,即集合 A 有而 B 没有的成员。符号 / 用于连接 2 个字符串。$[] 是字符串标志。使用 SPL XLL。原创 2024-08-04 14:30:00 · 199 阅读 · 0 评论 -
找到第一个满足条件的格值
函数 select@1 选出第一个符合条件的成员,m(2:) 表示取第 2 个直到最后一个成员,~1 表示第 1 个成员,~ 表示当前成员。要求根据指定的科目和成绩,找到该科目中大于等于该成绩的第1个格值,比如参数是Maths、6.5时,应当返回6.6。表格第1列是科目,之后几列是每次的考试成绩,顺序排列。使用 SPL XLL。原创 2024-08-03 14:00:00 · 347 阅读 · 0 评论 -
将一批 csv 的第一行抄进 Excel
函数directory@p按通配符列出文件名,带全路径。函数T通过文件名后缀按特定格式读取或写入文件。~表示当前成员,(1)表示第1行。请将每个 csv 的第 1 行(不含列名),写入一个 Excel 文件。原创 2024-08-02 13:13:04 · 321 阅读 · 0 评论 -
将各行数据向指定列对齐
表格A-C有多行数据,部分行为空,C列有重复值。列E的值是唯一的,对应着C列的部分数据,但两者大小写不一致。函数 align@a 将一个序列按另一个序列对齐并分组,conj 合并各组成员,E@1 将多层序列变为单层。需要将表格A-C向列E对齐,忽略大小写。使用 SPL XLL。原创 2024-08-02 13:11:29 · 189 阅读 · 0 评论 -
将 Excel 按部分格值分类存成多个文件
函数 T 从文件读取表格或将表格写入文件,@b 表示不含列名。group 按规则分组并保留组内数据。~ 表示序列的当前成员。#1 表示表格的第 1 列。请将该表格按分类存成多个tab分隔的txt文件,名字形如:Group_分类.txt。Excel表格按A列排序,且A列是"-"分隔的字符串,其中第1部分是分类。使用 SPL IDE,执行代码。原创 2024-08-01 14:52:57 · 610 阅读 · 0 评论 -
将日志文件转换成标准 csv
A2:用计算列新建二维表,#1 表示第 1 列,t(1)(2) 表示序列的第 1 个成员的第 2 个子成员。A3:将二维表写入文本文件,@c 表示分隔符为逗号,@t 表示带标题。A1:将文本文件读为二维表,分隔符为空格。log 文件有 3 列,分隔符是空格。Java 集成 SPL 可参考。原创 2024-07-31 16:02:50 · 432 阅读 · 0 评论 -
合并两个列头不同的表格
有两个Excel的"named ranges",Events1和Events2。从第2行开始,它们都可以看做带表头的表格,并有部分表头相同。要求:通过引用"named ranges"的名字,将两个表格合并,字段不同时用空白表示。使用 SPL XLL。原创 2024-07-30 16:12:13 · 218 阅读 · 0 评论 -
N 行一片的表格按列做分组汇总
函数 group 分组并保留各组数据,groups 分组汇总。# 表示序列的当前序号,~ 表示序列的当前成员。E@pb 对序列转置,不带列头。Excel表格每2行一片,每片的上下两格可以看做一条数据,分别是客户、工时,有时工时为空。需要统计每个客户的工时。使用 SPL XLL。原创 2024-07-29 14:16:29 · 350 阅读 · 0 评论 -
从两组数中各找两个使总和等于给定值
从每行找出2个数(可以相同),使4个数的和等于定值26216692,第1行的两个数放入A、B列,第2行的两个数放入C、D列。函数 xjoin 进行叉乘,E@1 将多层序列转为单层,E@b去掉标题,#1表示表格第 1 列。使用 SPL XLL。原创 2024-07-15 13:25:34 · 377 阅读 · 0 评论 -
将一串数字中的连续数字写成区间
split@pc表示将字符串拆分成序列,自动解析数据类型,分隔符是逗号。group@i 表示符合条件时分组。~[-1] 表示上一个成员,m(-1) 表示最后一个成员。要求找出每个连续的序列,用短横写成区间的形式,区间之间用逗号分隔。使用 SPL XLL。原创 2024-07-12 14:45:16 · 633 阅读 · 0 评论 -
每格内容按相邻格数值重复多次
函数 conj 合并序列的成员,~2 表示当前成员的第 2 个子成员,"整数 N* 序列 " 表示把序列成员复制 N 遍。需要将A格的内容按B格的数值重复多次,并拼成一列。表格有两列,其中B列是数值。使用 SPL XLL。原创 2024-07-11 15:53:30 · 618 阅读 · 0 评论 -
将格内多行文字展开成多格
函数 news@q 用序列的成员生成新序列,~2 是当前变量的第 2 个成员,import@si将字符串按回车解析成单行字符串组成的序列,get 在多层循环中按层数返回循环变量。表格的A列是分类,B列由多行文字组成,即分隔符是换行符。请将B列的每格展开成多个单元格,并复制A列。使用 SPL XLL。原创 2024-07-10 17:06:37 · 590 阅读 · 0 评论