★ FEATURED ARTICLE 每日一练·伍是我做的这套实操训练里改版最大的一期。我把训练目标从“练几个函数”改成了“完成一整套数据交付”从一张乱糟糟的销售明细到能自动刷新的汇总看板中间穿插Excel透视表、Python清洗和复盘模板。整套练习适合每天能抽出40到60分钟的运营、财务、数据分析师也适合正在准备办公技能考核的职场新人。我的建议是尽量按顺序做别跳题因为你在后面踩到的坑多半是前面没做干净的扫尾工作埋下来的。1. 整体设计思路为什么第五期我坚持“交付优先”1.1 练习不是背公式而是还原真实工作流很多人练Excel和Python的方式是“刷题”今天学一个VLOOKUP明天学一个数据透视表后天再抄一段Pandas代码。这种方式有个很隐蔽的问题每个知识点都认识但把它们串在一起处理一份真实数据时完全不知道从哪里下手。真正的工作场景里数据不会自动变成干净的表给你更不会告诉你“这题用透视表、那题用pandas”。数据往往带着重复行、缺失值、异常格式、区域归属混乱等问题一起出现你必须自己决定每一步做什么。所以“每日一练·伍”的设计不是按函数或语法点来切题而是按一个完整任务流来切题。整期练习围绕一条虚拟的“销售明细表”展开五道题分别对应五个真实工作环节快速生成可汇报的文本摘要、用透视表做同比环比、用Python清洗脏数据、搭建可自动刷新的看板、最后沉淀成自己的模板库。做完这一轮以后你得到的不是五个碎片技巧而是一条能直接复制到下周真实工作里的完整流水线。1.2 三条主线和两条辅线合起来才是完整闭环整期五道题里我刻意把难度和注意力做了分配。前三道是“主线”解决数据进入分析环节前必须经历的三道关文本摘要给领导看、统计口径给自己看、数据清洗给电脑看。第四道是“交付线”把前面处理完的数据变成可交互、可刷新的看板。第五道是“沉淀线”把这次练习中用到的函数、代码、表结构整理成可复用的模板确保下次不再从零开始。这个结构对应的是现实工作流拿到数据 → 看一下总量和异常 → 按维度汇总 → 做成可视化 → 复盘并留档。如果你只做其中一两道题很容易陷入“会做但用不起来”的尴尬。举个典型的例子透视表很多人都学过但真到了周一上午开周会前数据里混着文本型数字和合并单元格透视表立刻失灵。这时候真正能救你的反而是第三题里那些不起眼的清洗动作。1.3 为什么是“伍”而不是“五”这一期标题里的“伍”字面上是第五期的意思但在编排上我借用了“队伍、成组”的含义。全期五道题里第一、二题属于“热身级”只需要Excel基础函数第三题开始进入Python需要一点代码基础第四题把Excel和自动化连接起来第五题则完全站在“复盘者”视角。难度不是平均分配的而是前两题建立信心中间两题攻坚最后一题收口。建议按“221”的节奏来做前两题可以放在同一天各花20分钟左右第三、四题各安排独立的一天因为需要调试和思考第五题适合放在周末和整周的工作复盘一起做。如果你长期坚持这个小循环可以固定在每周逐步积累成月度、季度效率工具包。2. 核心任务拆解五道题的全量实操过程2.1 第一题用TEXTJOIN和IFS拼出可直接汇报的文本摘要日常工作中最常被问到的问题不是“你能做个图表吗”而是“这个月哪个区域卖得好和前两个月比怎么样”。如果每次都要临时看表格再打字回复既慢又容易漏数据。第一题就是训练“把数据直接转成一句话”的能力。假设手头有一张原始表字段包括订单编号、区域、客户名称、销售日期、销售额、成本。其中区域有华东、华南、华北三块日期是2025年1月到3月。目标是生成一段文本“2025年3月华东区域销售额合计128.5万元环比增长6.2%占全公司业绩的34.7%。”实际做法分为两步。第一步用SUMIFS把三个数算出来SUMIFS(销售明细!销售额, 销售明细!区域, 华东, 销售明细!销售日期, 2025-03-01, 销售明细!销售日期, 2025-03-31)第二步用TEXTJOIN把计算好的单元格拼成句子中间加上顿号、冒号等分隔符TEXTJOIN(, TRUE, 2025年3月华东区域销售额合计TEXT(C2,0.0万元), 环比增长TEXT(D2,0.0%), 占全公司业绩的TEXT(E2,0.0%))这里有几个细节要提醒。TEXTJOIN第二个参数写TRUE表示忽略空单元格如果你拼接的某个单元格是公式返回的空文本它也会被忽略这一点在批量做周报时非常好用。另外TEXT函数里的格式代码要注意保留几位小数、是否需要千分位、百分比是否带两位小数都要和公司汇报口径一致。我第一次做的时候直接把“0.0%”写成了“0%”结果所有比率都显示成整数看起来特别奇怪。如果你用的是旧版Excel而没有TEXTJOIN可以用连接符替换但要注意每个条件片段后面都加上分号或逗号否则会把多个数字糊在一起。边做边养成“把计算和文案分开”的习惯计算放上面几行文案只负责引用计算结果后续改口径只改一处不用通篇重写。2.2 第二题用数据透视表完成同比环比分析第一题解决了“怎么描述”第二题解决“怎么分析”。分析里最常用到的动作是按区域、按月份看销售额的同比和环比。我在练习里要求大家先做一张数据透视表行字段放“区域”列字段放“销售日期”并且把销售日期自动按“月”分组。这个分组操作在Excel里非常容易被忽视选中日期字段 → 右键 → 组合 → 选择“月”和“年”。做完整后透视表里会出现“年”和“月”两个层级。接下来是重点也是很多人卡住的地方新建计算字段算环比。透视表“分析”选项卡里有“字段、项目和集 → 计算字段”很多教材会教你在这里直接写公式。但我强烈不建议在计算字段里写“本月-上月”因为透视表计算字段是在汇总结果之上再计算的它自己无法精准引用上一列的单元格很容易得到错误结果。更稳妥的做法是先把透视表按区域和月份汇总好用GETPIVOTDATA或者直接复制粘贴值把结果放到旁边的工作表在普通单元格里写环比公式例如(B2-C2)/C2。这样做的原因是透视表区域作为数据源并不稳定如果后续动维度、加筛选器套在里面的公式很容易错乱。把它转成静态数值表后再做计算虽然多了一步但结果可控。想要自动化时可以配合Power Query做数据源更新而不是在透视表内部写复杂公式。第二题做完后你应该得到一张能回答“哪个区域涨、哪个区域跌、涨跌幅是多少”的二维汇总表。这张表就是后续看板的底座所以建议直接另存为一个数据模型文件别和原始明细混在一起。2.3 第三题用Pandas做一次彻底的数据清洗前两题做完你会发现如果原始表很乱Excel透视表会直接发疯。因此第三题换到Python用Pandas把这张“销售明细”彻底洗干净。这一题设定的练习环境是Jupyter Notebook数据文件是同一张原始表但里面被埋进了几种常见问题重复的订单号、有缺失的客户名称、日期列混入了文本格式、销售额里有空格和千分位符号。先看完整代码再逐行解释import pandas as pd df pd.read_excel(销售明细.xlsx, dtype{客户编号: str}) print(原始规模:, df.shape) print(重复订单数:, df.duplicated(subset[订单号]).sum()) df df.drop_duplicates(subset[订单号], keeplast) df[销售日期] pd.to_datetime(df[销售日期], errorscoerce) df[销售额] df[销售额].astype(str).str.replace(,, ).str.strip().astype(float) df df.dropna(subset[销售日期, 销售额]) df[客户名称] df[客户名称].fillna(待补充) code_mapping {E: 华东, S: 华南, N: 华北} df[区域] df[区域代码].map(code_mapping).fillna(未知) df.to_excel(销售明细_清洗后.xlsx, indexFalse) print(清洗后规模:, df.shape)代码里每个参数都有用意。dtype{客户编号: str}是为了防止像“00123”这种编号在读入时被当成数值Excel里写0123不会保留前面的0但在Python里我们完全掌控类型一开始就用字符串读入后续要跟客户表关联时才不会丢。pd.to_datetime(... errorscoerce)是处理日期类脏数据的神器遇到解析不了的日期不会报错中断而是变成NaT也就是“缺失时间”。然后用dropna(subset[销售日期,销售额])把带NaT和缺失金额的行整体删掉。这里我特意只对关键字段删行其他字段用fillna补全。比如客户名称虽然缺失但订单、区域、金额都是好的删掉整行太可惜补一个“待补充”标签等后续人工认领更合理。区域字段的清洗是这类练习的经典陷阱原始表里存的是“E、S、N”这种代码而不是中文区域。如果你在Excel透视表里直接看只能得到一个神秘字母必须自己做映射。代码里的map(code_mapping)就是完成代码到中文的翻译翻译不到的统一记为“未知”。很多新手在这一题会犯一个错误把缺失值全部删掉。这听起来干净但实际业务里数据量本来就不大滥用dropna会造成严重偏差。正确思路是分字段判断关键字段缺失该删就删非关键字段缺失能补就补。所谓“干净的数据”前提是业务上解释得通而不是机械地把空值清零。2.4 第四题搭建一个每天打开就能用的自动刷新看板数据清洗后第四题的目标是做成品一张业务看板要求打开文件点一下刷新所有汇总数字和排名自动更新。最简单的实现方式是把清洗后的Excel表先转化为“Excel表格”CtrlT再做透视表并使用切片器做筛选。具体步骤是打开“销售明细_清洗后.xlsx”选中数据区域按CtrlT创建表格插入数据透视表数据源选择“表/区域”而不是一整列随机区域在透视表里放“区域”“销售日期”按月分组作为行字段放“销售额”作为值插入切片器连接“区域”字段这样点按钮就能筛选不同大区再加两个图表一个柱状图看趋势一个饼图看区域占比。用“表格”作为透视表数据源是这一步最关键的设计。普通Excel区域在追加数据后透视表不会知道范围变了但Excel表格有结构化引用当你在下方新增行表自动扩张透视表刷新时就能把新数据包含进来。我见过太多人做了好看的看板结果下个月数据一多透视表范围不更新数字怎么都不对最后只能手动改数据源非常狼狈。如果希望看板更自动化可以配合Power Query。把原始文件放进一个“数据输入”文件夹Power Query从文件夹读取数据做几次清洗转换后加载到Excel表格再看板引用这个表格。这样每天替换文件夹里的原始文件后只需点“全部刷新”几十个查询会按顺序刷新比手动复制数据省出大量时间。在这个环节还有一类场景适合用动态数组函数。比如要看“销售额Top 3的客户”用旧版公式需要输入数组公式并按CtrlShiftEnter新版Excel里直接写SORT(UNIQUE(FILTER(客户名称, 销售额100000)), , -1)这个公式会返回一个自动扩展结果不用手动拖拽填充。把结果放在看板角落配合条件格式就能实现“自动点名”效果。建议看板尽量由三个部分组成顶部是指标卡总销售额、总订单量、目标完成率中部是趋势图底部是明细排名这样从上往下看信息密度递减汇报时按顺序讲就行。2.5 第五题把这套流程沉淀成自己的模板库很多练习做完就算完了第五题是用来解决“下次还得重来”这个问题的。复盘时我要求把这次练习中用到的三样东西提炼出来第一Excel模板文件。把空白透视表、固定格式的汇总表、已经写好的TEXTJOIN公式放进一个工作簿只保留格式和函数删掉具体数据。以后来新数据在这个模板上刷新并替换源数据即可。关键的细节是把数据源放Sheet1汇总表放Sheet2看板放Sheet3保持顺序稳定。第二Python脚本。把清洗代码中写死的部分参数化比如源文件路径、日期列名、区域映射字典放到脚本开头统一维护。这样切换业务场景时只改头部的配置区不用动处理逻辑。第三一份检查清单。至少包含字段类型是否读对、重复是否处理、缺失值是否按列处理、日期是否有无效值、输出文件是否保存为指定版本。这份清单就是第五题的最终产物下次直接照着勾。模板库的组织方式不需要很复杂一个文件夹加一套命名规则就够数据工作区/ 00_原始数据/ 01_清洗脚本/ 02_模板看板/ 03_输出报表/ 04_复盘记录/文件名建议统一使用“日期_业务_版本号”格式。比如“20250315_销售看板_v2.xlsx”。版本号每次更新加一旧文件不要覆盖避免出现“最终版”“最终版2”“最终版打死不改版”这种混乱。3. 实操中的参数选择与关键环节实现3.1 Excel公式里的引用、嵌套上限和错误定位前面第一题里用到的TEXTJOIN、IFS都属于新版本Excel中的动态数组函数它们对引用方式特别敏感。我建议所有练习都遵循一个原则原始数据写在左侧计算和拼接写在右侧公式中指向原始数据一律用绝对引用或者直接用表名和字段名引用。例如把数据区域定义成表“销售明细”后SUMIFS可以这么写SUMIFS(销售明细[销售额], 销售明细[区域], A2, 销售明细[销售日期], B2)这种方式叫结构化引用好处是表扩大时公式范围自动跟着变不用改了数据源再去改公式区域。如果用的是旧版本Excel则需要用绝对引用锁死区域例如$A$2:$A$10000但手工维护起来很累。嵌套公式是另一个容易出错的地方。旧版Excel公式最多嵌套7层新版改善了限制但嵌套太多仍然等于给自己挖坑。我踩过最大的坑是在一个单元格里写了八层IF表面看着很厉害实际第二天自己都看不懂改一个条件要翻半天。正确的做法是拆出中间辅助列把复杂判断分成两三列最后再用TEXTJOIN汇总。虽然表格多几列但可维护性大幅提升。公式报错时我建议直接用三条路线排查。一是看括号是否完整一个长公式可能有五六对括号点编辑栏时Excel会高亮匹配的括号二是看区域是否混入了文本单元格比如金额列里有“1,234”这种带千分位的文本直接求和会错三是利用“公式求值”按钮一步一步查看计算顺序。逐行求值看起来慢但往往一眼就能揪出哪个位置的数据类型不对。3.2 Pandas代码里的数据流、类型和性能参数第三题的清洗脚本看起来短但里面有几个容易被忽略的数据流细节直接关系到后面能不能跑通。读文件时我强烈建议显式写出dtype参数尤其对编号类、长数字类字段。自动推断的一个典型问题是把客户编号认成float然后“00123”变成“123.0”后续如果要和客户表做匹配永远匹配不上。订单号如果超过15位也会出现末尾变0的情况所以一律用字符串读入这是减少脏数据的第一个闸口。drop_duplicates里的subset参数要明确指定否则Pandas会按照整行是否重复来判断。实际业务中同一个订单号可能因为补录原因出现两行其他字段并不完全一致如果你按整行去重一个重复都发现不了。keeplast表示保留最后一条通常代表着最新的修正记录。但要注意这个语义要结合业务判断如果最后一条可能是误操作就应该改保留第一条。我在练习里用“保留最后一条”是为了契合“数据多次覆盖更新”的现实场景。当数据量变大时读Excel会比较慢。这时可以改用read_csv加指定编码比如encodingutf-8-sig或者使用read_excel的sheet_name参数只读指定工作表不要把所有Sheet都读进内存。如果需要过滤大量数据chunksize参数可以分块处理避免内存爆炸。第四题看板如果直接连接超大Excel文件也会卡顿这种情况建议把数据先导入Power BI或数据库Excel只负责最终展示。3.3 看板自动刷新的三种实现路径选哪种不纠结第四题里提到了“点一下刷新”实际操作中有三种路径我分别试用后给出结论。第一种是透视表自带的刷新。右键透视表选择“刷新”数据源如果是Excel表格新增行会被纳入。这个方案最简单不需要任何代码适合绝大多数中小型数据。缺点是你必须记得每次手动点刷新而且多张透视表要逐一点。第二种是Power Query刷新的方式。所有清洗转换在查询编辑器中完成加载到Excel表格后右键查询结果选“刷新”一条查询刷新完下游透视表也会跟着更新。如果你要把多个文件合在一起Power Query是最舒服的因为它天然支持“从文件夹”读取新增文件不用改连接。第三种是写一小段VBA一键刷新所有对象。比如在开发者工具里新建一个按钮映射宏Sub 刷新看板() ThisWorkbook.RefreshAll ActiveWorkbook.RefreshAll Application.CalculateFull End Sub这段代码就是把所有数据连接刷新一遍再重算所有公式。Power Query查询、透视表、普通公式都会被触发。如果你的文件存在外部数据库连接RefreshAll也能一并刷新。选择标准也很简单只有一张表选第一种多张表要合并清洗选第二种需要傻瓜式操作、给别人用就加一个按钮选第三种。我非常不推荐你一开始就追求全自动先把一种方案跑熟再逐步加自动化贪多嚼不烂。3.4 版本、备份、文件占用先立规矩再做内容文件处理习惯看起来不是技术问题却最能决定练习成果能不能保住。我给自己定过几条硬规矩每天开始练习前复制一份当天文件到“00_原始数据”文件夹保证原始数据不被污染所有输出文件带上当天的日期前缀存放在“03_输出报表”每次修改模板前先另存一个带版本号的副本避免改坏后回不去。文件占用是Windows下最常见的坑。打开一个Excel工作簿后系统会自动生成一个以“~$”开头的临时锁文件存在同一目录里。如果你在其他程序里或第二次打开同一个文件会提示“文件正由他人/另一进程使用”。导致这个问题的原因往往不是真有同事在用而是某个Excel进程没有完全退出或者Power Query引用的文件仍处于打开状态。遇到这种情况可以先看任务管理器里有没有多个Excel进程全部结束掉再重新打开。4. 常见问题与排查技巧实录4.1 Excel公式报错速查表第五期所有题目踩过的坑我整理成了一张速查表贴在最顺手的位置。它在排查时可以省很多时间。报错信息常见原因排查与解决#VALUE!参与计算的单元格里有文本检查目标列是否使用分列/清除格式转为数值#REF!引用的单元格区域被删除编辑公式重新框选数据源区域#NAME?函数名拼错或缺少引号确认函数拼写文本参数要加英文引号#DIV/0!分母为0或空用IFERROR包裹或先过滤分母为空的行#SPILL!动态数组结果无处扩展清除目标区域右侧/下侧的合并单元格或占用内容#N/AVLOOKUP/XLOOKUP找不到值检查查找值是否存在前导/尾随空格检查查找列为文本类型4.2 透视表日期分组失效与“文本型数字”的坑透视表日期分组功能失效是最容易打击新手的现象。明明列里都是2025/3/15这种日期右键组合时却没有“月、季度、年”选项。原因往往是“日期列”实际是文本。可以从单元格左上角是否有绿色小三角判断。如果全是文本日期用分列功能转换选中日期列 → 数据 → 分列 → 下一步 → 选择“日期”格式 → 完成这一招能解决80%的问题。数字字段显示成文本也会导致透视表求和错误。症状是数值列能显示但拖进“值”区默认是“计数”而不是“求和”。解决方法是先清洗数据选中列 → 分列 → 完成或使用查找替换将空格全部替换为空再用“分列”强制转数值。这个动作在“每日一练·伍”第五题里已经被沉淀成模板所以后续遇到了直接拷贝模板里的公式和操作步骤不要现想。4.3 Pandas清洗时的几个“隐形坑”Pandas好用但它的默认行为里藏着几个坑。第一NaN在比较时不会等于它自己不能用np.nan去判断缺失值必须用isna()或notna()新手在这一步几乎都会踩一次。第二read_excel读取无表头Excel时默认把第一行当作列名如果源文件第一行是标题需要增加header0或headerNone并手动指定列名否则首行数据会被吞掉。第三列名里有空格、中文、百分号等字符时用点操作符访问会失败建议用方括号访问方式例如df[销售额]。还有一类隐蔽问题发生在保存和再次读取之后。如果用df.to_csv保存数据日期列会自动被Pandas转成ISO格式区域里的中文可能会在Excel打开时出现乱码。解决方法是保存时指定encodingutf-8-sig这样Excel打开时能正确识别中文。这个细节我在练习里反复强调因为工作交付不是只在Jupyter里看一下而是要交给不懂代码的人用Excel打开乱码会让自己显得很不专业。4.4 看板打不开、刷新报错的排查顺序看板搭建完成后最尴尬的情况是第二天打开文件提示“数据连接未刷新”或者“我们找不到文件”。排查顺序三个词路径、权限、占用。先检查原始数据文件的路径是否被移动过。Power Query里如果记录的是绝对路径一旦文件被挪到别的文件夹连接就会断开。解决办法是尽量用相对路径或者把原始数据和看板放到同一个文件夹。再看权限和只读属性。从网盘同步下来的文件有时只读打开后连刷新按钮都是灰的需要右键文件 → 属性 → 取消“只读”。最后看进程占用之前说过如果Excel进程没退出文件会显示被锁定这时去任务管理器结束所有Excel进程再打开。如果刷新时提示“查询步骤错误”问题多半出在Power Query清洗步骤中。比如某一个列名在源文件里改了名或新增的一列导致M语言里的结构变化。定位方法是打开“查询编辑器”逐个点击右侧的“已应用步骤”哪一步报错就改哪一步。不要试图一口气修改所有步骤从后往前删错误步骤找到问题再修正效率最高。4.5 练到一半坚持不下去我用“最小动作清单”救场如果你以前练过类似的每日一练一定经历过“练到第三天就断掉”的情况。判断标准如果觉得每天40分钟压力大不是意志力问题而是目标拆得太大。我把“每日一练”改成“最小动作清单”之后坚持率明显提高。最小动作清单允许你在状态不好时只做十分钟但必须完成三件事打开文件、记录当前卡点、做一次15分钟的局部修复。不要小看这个动作。记录卡点比解决卡点更重要因为很多问题是同一类型反复出现。我复盘时发现自己的卡点有80%集中在“日期格式转换”“文本型数字清洗”“文件路径失效”这三类有了记录之后后续遇到可以直接跳到踩坑笔记里查答案不再从零排查。这个记录本身就是第五道题要求沉淀的个人工具箱最核心的组成部分。5. 从练习到日常生产力复盘、沉淀、再扩展5.1 每周复盘不只是“做完了”而是“变化了什么”一个完整的“每日一练”周期结束后我会专门留出30分钟做整体复盘。复盘不是记录“第几天完成了第几题”而是对比练习前后自己在处理真实数据时的速度变化。复盘清单分四项一哪一步花费时间最长二哪个错误重复出现三哪一种方案最终被采纳四下一个周期优先解决哪个场景。用第四题看板举例练习前你可能还在用最笨的办法每个月从系统导出数据、手动改表头、复制到汇总表练习后你把整个过程压缩成“替换文件 → 一键刷新 → 截图发群”省出的时间可以用来做更有价值的波动分析。这个改变要写进复盘里它才是练习真正的产出。如果你只写“学会了透视表”过两周再看就很难感受到价值。5.2 打造自己的“个人效率工具包”所有练习如果没有沉淀就只是练了个寂寞。我的建议是在电脑里建一个“个人效率工具包”的文件夹专门存放三类东西函数模板、脚本片段、检查清单。每次练习、每次工作遇到问题解决后第一时间更新这些文件。工具包不追求代码库级别的规范追求的是“打开就能用”。比如把Pandas清洗脚本做成几个可配置块头部留下路径和字段名后面预留几个# TODO注释再比如把常用Excel函数整理成一页说明每个函数配一个真实场景的例子。我在第五题里就是这么做的函数档案不仅写公式还写适用前提“此公式适用于Excel 365和Excel 2021及以上版本旧版请换用SUMPRODUCT”。有了这个备注半年后再看也不会用错。5.3 组队练习要不要做我的结论是分阶段练习类的项目很容易出现“建群打卡三天后群里只剩下广告”的场景。组队本身不是坏事但要分阶段。早期单人练习足够了因为你需要的是独立的调试过程很多错误只有自己踩过才会有肌肉记忆。到了第三轮、第四轮再选择组队比较合适同一个题目不同人做出来往往会有完全不同的解法把差异拿出来讨论才能打开思路。组队时建议约定一个输出格式比如每次练习后交出一份“结论摘要模板文件”而不是只发一句“打卡”。摘要里至少写清数据来源、做了几步、最终产出、遗留问题。这样的协作才可持续。如果只是互相监督式的打卡很难对能力提升有实质帮助。我个人在实际操作中的体会是每日一练最宝贵的部分从来不是题目本身而是做完之后那些能留在你电脑里的文件、脚本和清单。它们不占地方但一到用的时候就是老本。专业的方法是“慢即是快”每次只写下一分钟能拿出的交付物坚持下去它总有一天会长成一套很稳的工作系统。再好的教材和课程都不如自己踩过的坑和留下的模板更可靠。
阅读完成 · 觉得有帮助？
