1. 为什么我要整理这份数据从“下个表”到“造个表”1.1 需求远比“找一个xlsx”复杂做数据分析这几年我整理过不少乱七八糟的Excel表格但印象最深的还是2000年到2024年企业员工数、裁员数这份数据。起因是要写一份跨年度的企业用工趋势分析需要把员工总数、裁员规模、裁员率这几个数字从2000年一路拉到2024年。那时候我第一时间打开搜索引擎看到一堆“xxx数据xlsx”的下载链接以为下载下来就能用。事实是真正可用的现成表格几乎没有能找到的都是散落在年报、年鉴、数据库里的原始片段。类似鸢尾花数据集那种一打包就整整齐齐的xlsx在企业员工数据领域根本不存在。这也是第一版方案失败的根因我在网上找来三四个年份不齐、字段混乱的Excel用一个小时拼在一起结果做出来的趋势图前一年还是平稳的后一年突然断崖式下跌。后来排查发现不是数据出了问题而是2001年用的“员工人数”口径是年末在册数2002年用的却是“全年平均人数”两个数值差了一大截。从那一刻起我就意识到这份活儿不是“下个表”而是“造个表”。“造表”意味着要做几个关键决策数据覆盖范围多大年份是否每年都有行业怎么分类员工数和裁员数的定义到底是什么数据来源能不能追溯整张表要支持哪些分析场景先把这些问题回答完后面才能动手。当时也有人劝我数据量又不大直接用CSV存数据更简单。但CSV有三个硬伤没有多sheet能力数据字典和汇总表没地方放字段增减时所有协作者都要手工同步发给别人后还得让人重新设置列宽、筛选、格式。对于一份要经常更新和分发的数据来说xlsx是更稳妥的载体。1.2 数据来源与口径的取舍宁可少一个数不能错一个口径既然现成xlsx不存在我就把目标定成“自建一张可持续更新的标准表”。数据来源上我结合了四类渠道。一是上市公司年报和招股说明书。这类数据披露相对规范每年有固定审计流程员工数在“员工情况”章节能直接查到部分公司还会披露劳务派遣人数。缺点是只覆盖上市公司行业分布偏制造业和互联网中小型企业很少。二是统计年鉴和宏观经济数据库。这类数据胜在时间长、口径统一能拿到从2000年至今连续二十多年的就业总量。但年鉴里的“城镇单位就业人员”和“企业员工数”并不是一个概念很多年份还包含机关单位需要自己拆分。三是行业协会和公开招聘信息。行业协会会发布细分行业的用工数据招聘平台通过投递量、offer量、入离职数也能看出趋势但覆盖面有限只能作为补充。四是研究机构整理的面板数据。有些机构会发布已经清洗过的跨年度数据格式和字段都比较接近需求适合拿来做基准和交叉验证。但要确认数据来源是否允许二次处理避免发布时踩授权坑。数据来源确定后口径成了最头疼的问题。我踩过的坑主要有三个。第一个是员工总数的口径用“期末员工人数”还是“全年平均人数”。很多年报写的是“在职员工数量”但企业会在年末突击调整不同公司可比性不高。我规定能拿到期末数就用期末数拿不到就用年报里的“全年平均人数”并且单独加一列记录口径类型。第二个是裁员数的定义。“裁员”两个字太模糊了。接到辞退通知、合同到期不续签、业务线整体裁撤、协商解除劳动补偿在实操里都会被称为裁员但统计渠道完全不一样。有些企业年报里写“人员优化”“组织架构调整”“转岗”根本不会直接出现“裁员”二字。用文本匹配很难抓全必须结合新闻稿、年报上下文一起判断。我当时的方法是先设定关键词库再人工抽查宁可漏掉部分也要保证已统计的数值来源可靠。最终我选择“公司决策导致的主动解除劳动关系人数”作为主口径同时保留原始字段值。第三个是行业分类。2000年用的是旧国民经济行业分类2011年和2017年又各调整过一次直接拼接会导致行业名称对不上。我采用“行业代码历史行业名称现行行业名称”的三段式结构让每条数据都能在现行标准与历史标准之间切换。这些口径决策如果不提前做后面所有年份的合并都是白干活。这也是为什么遇到跨年度数据我会建议先花一天时间写数据字典再开始采集。2. 表格结构设计xlsx不是“一格一个数”2.1 多表设计一次规划不返工很多人在整理数据时喜欢“一张sheet塞满所有东西”结果就是列数几十个、行数上万、不同年份的字段还混在最右边。这种表用起来非常痛苦你根本不知道哪些列是真实的、哪些列只是某个年份临时加的。我这次采用经典的多sheet结构一共四张主表明细表一行一条记录记录“年份行业企业规模分层员工数裁员数裁员率数据来源口径说明”汇总表按年份和行业口径汇总方便快速做趋势图数据字典说明每一个字段名称、取值、单位、缺失值标注方式版本记录记录每次更新的时间、范围和改动内容明细表是主体所有原始数据都在这里。汇总表不要手工填我用脚本自动从明细表计算生成避免两边数字对不上。数据字典是很多人不写的但跨几十年、多人协作时它比数据本身还重要不然过两个月你自己都得猜“emp_cnt”到底有没有除以1000。除了四张主表我还在明细表前面加了一张“README”表把核心口径用一句话写在顶部。比如“员工数默认期末人数使用年均人数时会在员工数口径列标注”“裁员数仅统计公司决策导致的主动解除不含辞职、退休”等。这张表的存在能解决大部分合作方拿到表格却不知道我做了什么手脚的问题。2.2 字段设计让每一条数据都能“自解释”设计字段时我重点考虑三件事这条数据属于谁数字是怎么来的能不能跟其他表对齐当时定下的核心字段如下字段名说明示例备注year年份2020必填用于时间序列industry_code行业代码C27沿用统计用行业分类industry_name行业名称医药制造业使用现行国标名称company_scale企业规模分层大型/中型/小微型部分年份没有分规模emp_count员工数125000单位人emp_count_type员工数口径期末数/全年平均关键字段layoff_count裁员数3500单位人layoff_definition裁员定义公司主动解除关键字段layoff_rate裁员率2.8计算列单位%data_source数据来源某年年报/年鉴可追溯remark备注含子公司合并口径自由文本有两个地方特别容易翻车。一个是员工数和裁员数的逻辑矛盾裁员率如果超过30%或为负数一定是数据有问题。另一个是单位混乱有些来源给的是“千人”有些给的是“人”我明确规定统一成“人”不保留第二种单位。另外我还加了一个“年报披露月份”字段。因为企业年报基本在次年上半年发布不同公司披露时间不一样直接使用“报告年度”作为year字段时可能会把不同披露时间的数值放混。加了这个字段后做分析时可以按披露月份做二次校正。2.3 数据字典与口径注释写给未来的自己数据字典表一般四列就够字段名、中文解释、单位/取值、备注。下面是我挂在汇总表旁边的真实写法字段名说明取值备注layoff_definition裁员定义公司主动解除不含辞职、退休、内部调动emp_count_type员工数口径期末数优先期末数缺失时用年均layoff_rate裁员率百分比layoff_count / emp_count * 100data_source数据来源年报/年鉴/协会主页写明具体出处需要特别强调“缺失值标注”问题我用空值表示“未知”用0表示“真实为0”这两个含义绝对不能混。很多Excel函数会把空值和0都当成0处理但当你做筛选排序时空值会被放在一边0会被正常参与计算两者的语义完全不同。我会把所有“数据缺失”明确写成文本“NA”凡是数值列不填数字避免它和真正的0混在一起。数据字典做完后整个表的结构骨架就有了之后往里填数据再乱都能回到统一格式。3. 实操过程从原始数据到干净xlsx3.1 清洗链路不靠肉眼靠脚本很多人清洗数据的方法是打开Excel肉眼盯着改这种方式在几十行的表里没问题但遇到跨度二十多年、来源四五种的数据就会改到怀疑人生。我的流程分为四步。第一步把纸质年报、PDF表格统一转成结构化csv。无论用OCR、手动录入还是PDF提取工具最终都要落到“一列一个字段”的规范格式。这里最大的坑是PDF表格经常合并单元格提取后出现大量空值需要先做横向填充。第二步统一列名和单位。原始数据有“员工总人数”“在职员工”“雇员数量”等十几种叫法我先做一个列名映射表把它们统一成字段设计里的规范名。常见映射长得像这样原始列名规范字段员工总人数emp_count在职员工emp_count平均从业人数emp_count裁员人数/裁减员工layoff_count离职人数需人工判断是否为裁员第三步处理缺失值和异常值。员工数明显小于裁员数、裁员率为负数、年份不在2000到2024年范围内这些记录单独标记出来不做自动删除。第四步去重和逻辑校验。同一个来源、同一年、同一行业出现两条不一致数据时以数据质量更高的一条为准并在备注里写明冲突原因。清洗脚本一定要写注释并保存中间文件。不要一个脚本从头处理到尾每处理一个来源就保存一个“clean_xxx.csv”这样万一某个来源需要重新更新不用把之前所有处理都重跑一遍。3.2 Python生成多表xlsx几行代码解决重复劳动数据清洗成规范csv后下一步就是生成多sheet的xlsx。这一步我强烈推荐用pandas和openpyxl不要手动复制粘贴更不要在Excel里逐个设置格式。下面是生成xlsx的基础框架包含读取明细、计算汇总、生成表格和设置自动筛选import pandas as pd from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows df pd.read_csv(clean_detail.csv) # 计算裁员率 df[layoff_rate] (df[layoff_count] / df[emp_count] * 100).round(2) # 按年行业汇总 summary df.groupby([year, industry_name]).agg( emp_sum(emp_count, sum), layoff_sum(layoff_count, sum) ).reset_index() summary[layoff_rate] (summary[layoff_sum] / summary[emp_sum] * 100).round(2) wb Workbook() ws_detail wb.active ws_detail.title 明细表 for row in dataframe_to_rows(df, indexFalse, headerTrue): ws_detail.append(row) ws_detail.freeze_panes A3 ws_detail.auto_filter.ref ws_detail.dimensions ws_summary wb.create_sheet(汇总表) for row in dataframe_to_rows(summary, indexFalse, headerTrue): ws_summary.append(row) ws_summary.freeze_panes A3 ws_summary.auto_filter.ref ws_summary.dimensions # 加一个简单的使用说明sheet ws_readme wb.create_sheet(README) ws_readme.append([字段说明详见‘数据字典’sheet]) ws_readme.append([员工数默认期末人数使用年均人数时会在员工数口径列标注]) ws_readme.append([裁员数仅统计公司决策导致的主动解除不含辞职、退休]) wb.save(企业员工数_裁员数_2000-2024.xlsx)除了自动筛选和冻结窗格我还会用openpyxl单独给表头设置浅灰色背景、加粗字体、列宽自适应。这些格式可以让文件一打开就有“值得看”的第一印象。比较稳妥的做法是写一个函数把所有格式设置集中放在里面而不是在生成表后手工返工。因为这份表要反复更新人工步骤越少越不容易遗漏。3.3 发布前校验清单不跑一遍心里不踏实发布xlsx之前我会跑一遍脚本把下面几类问题一次性查出来避免发出去后被对方发现低级错误。年份覆盖是否完整2000到2024年之间某一年某行业整年没数据到底是有意空出还是漏了明细表和汇总表数字是否一致加总明细应与汇总表一致不一致一定是脚本或清洗环节有问题。裁员率是否异常超过50%或为负数的记录必须逐条查看。来源覆盖比例如果某年80%的数据来自同一个来源说明该年份的代表性存疑需要在README里提示。我还会检查单元格格式数字列必须是数字类型不能有隐藏为文本的数字。比如员工数这一列如果有一部分是文本格式做合计时会被跳过直接导致汇总数错误。这类问题肉眼很难发现但用脚本判断value_if_string很容易暴露。校验不通过就先不要发布。这也是为什么我坚持加“版本记录”sheet每次做完一轮清洗、生成、校验就把“改了哪些、删了哪些、哪些来源有更新”写进去后面追问题能省大量时间。4. xlsx格式坑存储膨胀、xlsm跳变、打开乱掉4.1 文件越来越大不是数据多的错我第一版xlsx只有8MB后来只是调整几次格式、加了几列公式体积直接飙到50MB以上。很多人以为这是数据量大其实不是这是xlsx“存储膨胀”的典型症状。xlsx本质上是一个zip压缩包里面是xml和资源文件。当你整列整行设置格式哪怕这个区域根本没有数据这些空单元格的样式也会被记录在styles.xml里文件体积就会不正常增加。条件格式叠加过多、隐藏图表没清理、旧版本缓存数据没删除都会让文件越来越臃肿。有的表明明只有几万行数据却能撑到200MB多半是这种原因。检查方法很直接用Python直接打开xlsx看它内部装了些什么。import zipfile zf zipfile.ZipFile(企业员工数_裁员数_2000-2024.xlsx, r) for name in zf.namelist(): size zf.getinfo(name).file_size if size 100_000: print(name, size) zf.close()如果发现styles.xml、sharedStrings.xml异常大基本可以断定是格式冗余。解决思路有三个。第一只给有数据的区域设置格式不要全选整列设置字体边框。第二删掉没用的条件格式规则和隐藏sheet。第三用openpyxl打开文件后检查sheet的max_row和max_column有没有膨胀到几万甚至几十万如果max_column是几百列但实际只有几列那要手动清除多余列。最后还有一招把内容复制到一个新建工作簿用“值格式”粘贴后重新保存往往文件体积能缩小一半以上。4.2 一打开就变成xlsm到底哪出了问题有段时间我把生成好的xlsx发给同事对方回我一句“你是不是发成xlsm了”我先是一愣后来发现是WPS的问题。“xlsm”通常是两种来源。一种是我在Excel文件里写了宏模块哪怕没真正使用宏只要文件里存在VBA项目另存或重新保存时WPS和Excel都可能提示另存为xlsm格式。另一种是WPS打开xlsx后在保存时自动添加了宏项目结构把扩展名悄悄改成了xlsm。排查方法不复杂。点击“开发工具”或“宏”按钮查看VBAProject是否存在如果不需要宏打开VBA编辑器删除模块再另存为xlsx。如果只是WPS自动转换没有真正的宏更稳妥的办法是在发送文件前用Excel打开另存一次确保扩展名确实是.xlsx再检查文件大小和格式是否正常。xlsm和xlsx的核心区别就是能不能包含宏xlsx不支持宏xlsm支持。安全方面别人发来的xlsm文件不要随便启用宏这不是效率问题是安全底线。尤其是我这种会把数据分享给多个协作方的人更要防止有脚本被误触。4.3 别人打开后格式乱了的三大原因自己电脑上看着好好的表发到别人那边字体变了、数字变成科学计数法、小数位丢了这是xlsx分发最常见的坑。大概率是下面三个原因之一。第一字体兼容性。用了某些系统专有字体对方电脑没有这个字体时会被替换成默认字体。第二数字格式依赖区域设置。对方系统的区域设置和你不同小数点、千位分隔符规则不一样显示就会乱。第三条件格式和筛选依赖区域引用如果文件复制时引用区域乱了筛选和格式就会失效。解决方法是发布前统一字体为常见字体把数字格式设置成固定的小数位不要依赖全球通用区域设置条件格式规则重新整理一遍清除没用的引用区域。还有个容易被忽略的点不要把整个工作表设置为“适合内容列宽”。这个设置会随着字体和分辨率变化。发布前最好手动设定固定列宽比如8到12这样的宽度确保在别人的屏幕上看起来跟你自己这边基本一致。4.4 数据口径对不齐全在散点图上露馅数据清洗时最容易漏掉的问题不是“缺数”而是“单位错位”。当我把2000年和2015年的数据放在同一张表里发现“员工数”这一列的量级差得离谱检查后才发现2000年用的是“千人”2015年用的是“人”。这种单位混乱如果不及时发现趋势图基本就是断崖式突变。排查异常最有效的工具不是Excel筛选而是画一张简单的散点图。把每年员工总数画出来凡是出现不合理突变的年份基本都能一眼看出来。再用“按年和行业分组求均值”把均值明显偏离的趋势点标出来逐条查。不要只盯着最大值和最小值单位错误往往隐藏在中间几年。应对措施其实就一条数据字典里把单位约定写得清清楚楚清洗脚本里再强制校验每个来源的数值范围。如果某个来源的数据平均在10万附近突然来了个9位数的记录先停下来查清楚再继续往下跑。5. 实操总结与个人体会5.1 长跨度数据表的核心经验把2000到2024年的企业员工数、裁员数xlsx从无到有做出来我最想分享的经验不是某个具体函数而是流程。第一先写数据字典再碰原始数据。我能少走一半弯路就是靠这份字典兜底。第二所有清洗处理保留原始文件和中间文件不要只留最终结果谁也不知道哪个来源需要重新处理。第三发布表格前再跑一遍校验清单远比一遍遍解释“这里为什么是这样的”有效。有一段时间我图省事直接用Excel手工把几列数据拼在一起当时看起来很高效结果每次更新都要手动重做最后熬不住还是回去写了Python脚本。长跨度数据更新频率高流程化建设比一次性完成重要得多。5.2 这份数据还能怎么扩展如果后续要再往上走我会做几件事。一是把产业结构数据和员工数做联动看看哪些行业的用工变化趋势联动性更强。二是补充企业注册数据和裁员数形成更完整的对比视角。三是将xlsx与BI报表结合做成自动刷新的可视化看板。技术上其实不难核心还是前面说的把数据源、口径、字段设计先搞清楚再前进。在这次整理过程中我还有一个体会越是看起来简单的Excel表格越需要投入精力做设计。数据本身不会说话整理数据的方式决定了它能不能被信任。你要做类似的长跨度数据表时我的建议是不要急着下载和堆数据先花时间把口径和结构定下来。这个收益会在你后面的每一次更新和查询中持续放大短期内可能看不出差别长期看能把你的数据习惯带到另一个水平。 1. 为什么我要整理这份数据从“下个表”到“造个表”1.1 需求远比“找一个xlsx”复杂做数据分析这几年我整理过不少乱七八糟的Excel表格但印象最深的还是2000年到2024年企业员工数、裁员数这份数据。起因是要写一份跨年度的企业用工趋势分析需要把员工总数、裁员规模、裁员率这几个数字从2000年一路拉到2024年。那时候我第一时间打开搜索引擎看到一堆“xxx数据xlsx”的下载链接以为下载下来就能用。事实是真正可用的现成表格几乎没有能找到的都是散落在年报、年鉴、数据库里的原始片段。类似鸢尾花数据集那种一打包就整整齐齐的xlsx在企业员工数据领域根本不存在。这也是第一版方案失败的根因我在网上找来三四个年份不齐、字段混乱的Excel用一个小时拼在一起结果做出来的趋势图前一年还是平稳的后一年突然断崖式下跌。后来排查发现不是数据出了问题而是2001年用的“员工人数”口径是年末在册数2002年用的却是“全年平均人数”两个数值差了一大截。从那一刻起我就意识到这份活儿不是“下个表”而是“造个表”。“造表”意味着要做几个关键决策数据覆盖范围多大年份是否每年都有行业怎么分类员工数和裁员数的定义到底是什么数据来源能不能追溯整张表要支持哪些分析场景先把这些问题回答完后面才能动手。当时也有人劝我数据量又不大直接用CSV存数据更简单。但CSV有三个硬伤没有多sheet能力数据字典和汇总表没地方放字段增减时所有协作者都要手工同步发给别人后还得让人重新设置列宽、筛选、格式。对于一份要经常更新和分发的数据来说xlsx是更稳妥的载体。1.2 数据来源与口径的取舍宁可少一个数不能错一个口径既然现成xlsx不存在我就把目标定成“自建一张可持续更新的标准表”。数据来源上我结合了四类渠道。一是上市公司年报和招股说明书。这类数据披露相对规范每年有固定审计流程员工数在“员工情况”章节能直接查到部分公司还会披露劳务派遣人数。缺点是只覆盖上市公司行业分布偏制造业和互联网中小型企业很少。二是统计年鉴和宏观经济数据库。这类数据胜在时间长、口径统一能拿到从2000年至今连续二十多年的就业总量。但年鉴里的“城镇单位就业人员”和“企业员工数”并不是一个概念很多年份还包含机关单位需要自己拆分。三是行业协会和公开招聘信息。行业协会会发布细分行业的用工数据招聘平台通过投递量、offer量、入离职数也能看出趋势但覆盖面有限只能作为补充。四是研究机构整理的面板数据。有些机构会发布已经清洗过的跨年度数据格式和字段都比较接近需求适合拿来做基准和交叉验证。但要确认数据来源是否允许二次处理避免发布时踩授权坑。数据来源确定后口径成了最头疼的问题。我踩过的坑主要有三个。第一个是员工总数的口径用“期末员工人数”还是“全年平均人数”。很多年报写的是“在职员工数量”但企业会在年末突击调整不同公司可比性不高。我规定能拿到期末数就用期末数拿不到就用年报里的“全年平均人数”并且单独加一列记录口径类型。第二个是裁员数的定义。“裁员”两个字太模糊了。接到辞退通知、合同到期不续签、业务线整体裁撤、协商解除劳动补偿在实操里都会被称为裁员但统计渠道完全不一样。有些企业年报里写“人员优化”“组织架构调整”“转岗”根本不会直接出现“裁员”二字。用文本匹配很难抓全必须结合新闻稿、年报上下文一起判断。我当时的方法是先设定关键词库再人工抽查宁可漏掉部分也要保证已统计的数值来源可靠。最终我选择“公司决策导致的主动解除劳动关系人数”作为主口径同时保留原始字段值。第三个是行业分类。2000年用的是旧国民经济行业分类2011年和2017年又各调整过一次直接拼接会导致行业名称对不上。我采用“行业代码历史行业名称现行行业名称”的三段式结构让每条数据都能在现行标准与历史标准之间切换。这些口径决策如果不提前做后面所有年份的合并都是白干活。这也是为什么遇到跨年度数据我会建议先花一天时间写数据字典再开始采集。2. 表格结构设计xlsx不是“一格一个数”2.1 多表设计一次规划不返工很多人在整理数据时喜欢“一张sheet塞满所有东西”结果就是列数几十个、行数上万、不同年份的字段还混在最右边。这种表用起来非常痛苦你根本不知道哪些列是真实的、哪些列只是某个年份临时加的。我这次采用经典的多sheet结构一共四张主表明细表一行一条记录记录“年份行业企业规模分层员工数裁员数裁员率数据来源口径说明”汇总表按年份和行业口径汇总方便快速做趋势图数据字典说明每一个字段名称、取值、单位、缺失值标注方式版本记录记录每次更新的时间、范围和改动内容明细表是主体所有原始数据都在这里。汇总表不要手工填我用脚本自动从明细表计算生成避免两边数字对不上。数据字典是很多人不写的但跨几十年、多人协作时它比数据本身还重要不然过两个月你自己都得猜“emp_cnt”到底有没有除以1000。除了四张主表我还在明细表前面加了一张“README”表把核心口径用一句话写在顶部。比如“员工数默认期末人数使用年均人数时会在员工数口径列标注”“裁员数仅统计公司决策导致的主动解除不含辞职、退休”等。这张表的存在能解决大部分合作方拿到表格却不知道我做了什么手脚的问题。2.2 字段设计让每一条数据都能“自解释”设计字段时我重点考虑三件事这条数据属于谁数字是怎么来的能不能跟其他表对齐当时定下的核心字段如下字段名说明示例备注year年份2020必填用于时间序列industry_code行业代码C27沿用统计用行业分类industry_name行业名称医药制造业使用现行国标名称company_scale企业规模分层大型/中型/小微型部分年份没有分规模emp_count员工数125000单位人emp_count_type员工数口径期末数/全年平均关键字段layoff_count裁员数3500单位人layoff_definition裁员定义公司主动解除关键字段layoff_rate裁员率2.8计算列单位%data_source数据来源某年年报/年鉴可追溯remark备注含子公司合并口径自由文本有两个地方特别容易翻车。一个是员工数和裁员数的逻辑矛盾裁员率如果超过30%或为负数一定是数据有问题。另一个是单位混乱有些来源给的是“千人”有些给的是“人”我明确规定统一成“人”不保留第二种单位。另外我还加了一个“年报披露月份”字段。因为企业年报基本在次年上半年发布不同公司披露时间不一样直接使用“报告年度”作为year字段时可能会把不同披露时间的数值放混。加了这个字段后做分析时可以按披露月份做二次校正。2.3 数据字典与口径注释写给未来的自己数据字典表一般四列就够字段名、中文解释、单位/取值、备注。下面是我挂在汇总表旁边的真实写法字段名说明取值备注layoff_definition裁员定义公司主动解除不含辞职、退休、内部调动emp_count_type员工数口径期末数优先期末数缺失时用年均layoff_rate裁员率百分比layoff_count / emp_count * 100data_source数据来源年报/年鉴/协会主页写明具体出处需要特别强调“缺失值标注”问题我用空值表示“未知”用0表示“真实为0”这两个含义绝对不能混。很多Excel函数会把空值和0都当成0处理但当你做筛选排序时空值会被放在一边0会被正常参与计算两者的语义完全不同。我会把所有“数据缺失”明确写成文本“NA”凡是数值列不填数字避免它和真正的0混在一起。数据字典做完后整个表的结构骨架就有了之后往里填数据再乱都能回到统一格式。3. 实操过程从原始数据到干净xlsx3.1 清洗链路不靠肉眼靠脚本很多人清洗数据的方法是打开Excel肉眼盯着改这种方式在几十行的表里没问题但遇到跨度二十多年、来源四五种的数据就会改到怀疑人生。我的流程分为四步。第一步把纸质年报、PDF表格统一转成结构化csv。无论用OCR、手动录入还是PDF提取工具最终都要落到“一列一个字段”的规范格式。这里最大的坑是PDF表格经常合并单元格提取后出现大量空值需要先做横向填充。第二步统一列名和单位。原始数据有“员工总人数”“在职员工”“雇员数量”等十几种叫法我先做一个列名映射表把它们统一成字段设计里的规范名。常见映射长得像这样原始列名规范字段员工总人数emp_count在职员工emp_count平均从业人数emp_count裁员人数/裁减员工layoff_count离职人数需人工判断是否为裁员第三步处理缺失值和异常值。员工数明显小于裁员数、裁员率为负数、年份不在2000到2024年范围内这些记录单独标记出来不做自动删除。第四步去重和逻辑校验。同一个来源、同一年、同一行业出现两条不一致数据时以数据质量更高的一条为准并在备注里写明冲突原因。清洗脚本一定要写注释并保存中间文件。不要一个脚本从头处理到尾每处理一个来源就保存一个“clean_xxx.csv”这样万一某个来源需要重新更新不用把之前所有处理都重跑一遍。3.2 Python生成多表xlsx几行代码解决重复劳动数据清洗成规范csv后下一步就是生成多sheet的xlsx。这一步我强烈推荐用pandas和openpyxl不要手动复制粘贴更不要在Excel里逐个设置格式。下面是生成xlsx的基础框架包含读取明细、计算汇总、生成表格和设置自动筛选import pandas as pd from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows df pd.read_csv(clean_detail.csv) # 计算裁员率 df[layoff_rate] (df[layoff_count] / df[emp_count] * 100).round(2) # 按年行业汇总 summary df.groupby([year, industry_name]).agg( emp_sum(emp_count, sum), layoff_sum(layoff_count, sum) ).reset_index() summary[layoff_rate] (summary[layoff_sum] / summary[emp_sum] * 100).round(2) wb Workbook() ws_detail wb.active ws_detail.title 明细表 for row in dataframe_to_rows(df, indexFalse, headerTrue): ws_detail.append(row) ws_detail.freeze_panes A3 ws_detail.auto_filter.ref ws_detail.dimensions ws_summary wb.create_sheet(汇总表) for row in dataframe_to_rows(summary, indexFalse, headerTrue): ws_summary.append(row) ws_summary.freeze_panes A3 ws_summary.auto_filter.ref ws_summary.dimensions # 加一个简单的使用说明sheet ws_readme wb.create_sheet(README) ws_readme.append([字段说明详见‘数据字典’sheet]) ws_readme.append([员工数默认期末人数使用年均人数时会在员工数口径列标注]) ws_readme.append([裁员数仅统计公司决策导致的主动解除不含辞职、退休]) wb.save(企业员工数_裁员数_2000-2024.xlsx)除了自动筛选和冻结窗格我还会用openpyxl单独给表头设置浅灰色背景、加粗字体、列宽自适应。这些格式可以让文件一打开就有“值得看”的第一印象。比较稳妥的做法是写一个函数把所有格式设置集中放在里面而不是在生成表后手工返工。因为这份表要反复更新人工步骤越少越不容易遗漏。3.3 发布前校验清单不跑一遍心里不踏实发布xlsx之前我会跑一遍脚本把下面几类问题一次性查出来避免发出去后被对方发现低级错误。年份覆盖是否完整2000到2024年之间某一年某行业整年没数据到底是有意空出还是漏了明细表和汇总表数字是否一致加总明细应与汇总表一致不一致一定是脚本或清洗环节有问题。裁员率是否异常超过50%或为负数的记录必须逐条查看。来源覆盖比例如果某年80%的数据来自同一个来源说明该年份的代表性存疑需要在README里提示。我还会检查单元格格式数字列必须是数字类型不能有隐藏为文本的数字。比如员工数这一列如果有一部分是文本格式做合计时会被跳过直接导致汇总数错误。这类问题肉眼很难发现但用脚本判断value_if_string很容易暴露。校验不通过就先不要发布。这也是为什么我坚持加“版本记录”sheet每次做完一轮清洗、生成、校验就把“改了哪些、删了哪些、哪些来源有更新”写进去后面追问题能省大量时间。4. xlsx格式坑存储膨胀、xlsm跳变、打开乱掉4.1 文件越来越大不是数据多的错我第一版xlsx只有8MB后来只是调整几次格式、加了几列公式体积直接飙到50MB以上。很多人以为这是数据量大其实不是这是xlsx“存储膨胀”的典型症状。xlsx本质上是一个zip压缩包里面是xml和资源文件。当你整列整行设置格式哪怕这个区域根本没有数据这些空单元格的样式也会被记录在styles.xml里文件体积就会不正常增加。条件格式叠加过多、隐藏图表没清理、旧版本缓存数据没删除都会让文件越来越臃肿。有的表明明只有几万行数据却能撑到200MB多半是这种原因。检查方法很直接用Python直接打开xlsx看它内部装了些什么。import zipfile zf zipfile.ZipFile(企业员工数_裁员数_2000-2024.xlsx, r) for name in zf.namelist(): size zf.getinfo(name).file_size if size 100_000: print(name, size) zf.close()如果发现styles.xml、sharedStrings.xml异常大基本可以断定是格式冗余。解决思路有三个。第一只给有数据的区域设置格式不要全选整列设置字体边框。第二删掉没用的条件格式规则和隐藏sheet。第三用openpyxl打开文件后检查sheet的max_row和max_column有没有膨胀到几万甚至几十万如果max_column是几百列但实际只有几列那要手动清除多余列。最后还有一招把内容复制到一个新建工作簿用“值格式”粘贴后重新保存往往文件体积能缩小一半以上。4.2 一打开就变成xlsm到底哪出了问题有段时间我把生成好的xlsx发给同事对方回我一句“你是不是发成xlsm了”我先是一愣后来发现是WPS的问题。“xlsm”通常是两种来源。一种是我在Excel文件里写了宏模块哪怕没真正使用宏只要文件里存在VBA项目另存或重新保存时WPS和Excel都可能提示另存为xlsm格式。另一种是WPS打开xlsx后在保存时自动添加了宏项目结构把扩展名悄悄改成了xlsm。排查方法不复杂。点击“开发工具”或“宏”按钮查看VBAProject是否存在如果不需要宏打开VBA编辑器删除模块再另存为xlsx。如果只是WPS自动转换没有真正的宏更稳妥的办法是在发送文件前用Excel打开另存一次确保扩展名确实是.xlsx再检查文件大小和格式是否正常。xlsm和xlsx的核心区别就是能不能包含宏xlsx不支持宏xlsm支持。安全方面别人发来的xlsm文件不要随便启用宏这不是效率问题是安全底线。尤其是我这种会把数据分享给多个协作方的人更要防止有脚本被误触。4.3 别人打开后格式乱了的三大原因自己电脑上看着好好的表发到别人那边字体变了、数字变成科学计数法、小数位丢了这是xlsx分发最常见的坑。大概率是下面三个原因之一。第一字体兼容性。用了某些系统专有字体对方电脑没有这个字体时会被替换成默认字体。第二数字格式依赖区域设置。对方系统的区域设置和你不同小数点、千位分隔符规则不一样显示就会乱。第三条件格式和筛选依赖区域引用如果文件复制时引用区域乱了筛选和格式就会失效。解决方法是发布前统一字体为常见字体把数字格式设置成固定的小数位不要依赖全球通用区域设置条件格式规则重新整理一遍清除没用的引用区域。还有个容易被忽略的点不要把整个工作表设置为“适合内容列宽”。这个设置会随着字体和分辨率变化。发布前最好手动设定固定列宽比如8到12这样的宽度确保在别人的屏幕上看起来跟你自己这边基本一致。4.4 数据口径对不齐全在散点图上露馅数据清洗时最容易漏掉的问题不是“缺数”而是“单位错位”。当我把2000年和2015年的数据放在同一张表里发现“员工数”这一列的量级差得离谱检查后才发现2000年用的是“千人”2015年用的是“人”。这种单位混乱如果不及时发现趋势图基本就是断崖式突变。排查异常最有效的工具不是Excel筛选而是画一张简单的散点图。把每年员工总数画出来凡是出现不合理突变的年份基本都能一眼看出来。再用“按年和行业分组求均值”把均值明显偏离的趋势点标出来逐条查。不要只盯着最大值和最小值单位错误往往隐藏在中间几年。应对措施其实就一条数据字典里把单位约定写得清清楚楚清洗脚本里再强制校验每个来源的数值范围。如果某个来源的数据平均在10万附近突然来了个9位数的记录先停下来查清楚再继续往下跑。5. 实操总结与个人体会5.1 长跨度数据表的核心经验把2000到2024年的企业员工数、裁员数xlsx从无到有做出来我最想分享的经验不是某个具体函数而是流程。第一先写数据字典再碰原始数据。我能少走一半弯路就是靠这份字典兜底。第二所有清洗处理保留原始文件和中间文件不要只留最终结果谁也不知道哪个来源需要重新处理。第三发布表格前再跑一遍校验清单远比一遍遍解释“这里为什么是这样的”有效。有一段时间我图省事直接用Excel手工把几列数据拼在一起当时看起来很高效结果每次更新都要手动重做最后熬不住还是回去写了Python脚本。长跨度数据更新频率高流程化建设比一次性完成重要得多。5.2 这份数据还能怎么扩展如果后续要再往上走我会做几件事。一是把产业结构数据和员工数做联动看看哪些行业的用工变化趋势联动性更强。二是补充企业注册数据和裁员数形成更完整的对比视角。三是将xlsx与BI报表结合做成自动刷新的可视化看板。技术上其实不难核心还是前面说的把数据源、口径、字段设计先搞清楚再前进。在这次整理过程中我还有一个体会越是看起来简单的Excel表格越需要投入精力做设计。数据本身不会说话整理数据的方式决定了它能不能被信任。你要做类似的长跨度数据表时我的建议是不要急着下载和堆数据先花时间把口径和结构定下来。这个收益会在你后面的每一次更新和查询中持续放大短期内可能看不出差别长期看能把你的数据习惯带到另一个水平。