Excel爬天气数据翻车实录：从编码乱码到URL构造，我的避坑指南全在这了

Excel爬取天气数据实战从乱码修复到动态URL处理的完整解决方案最近在帮朋友整理一份历史天气数据集时我决定用Excel直接爬取网页数据。本以为是个简单的任务结果却遭遇了编码乱码、动态URL构造、访问限制等一系列问题。经过三天调试和反复尝试终于找到了稳定可靠的解决方案。本文将分享我的完整踩坑经历和最终验证有效的技术方案。1. 环境准备与基础概念在开始爬取天气数据前我们需要明确几个关键概念和工具准备。Excel的数据获取功能主要依赖于Power Query在Mac上称为Get Transform这是一个强大的ETL工具内置了从网页抓取数据的能力。必备工具检查清单Excel 2016或更高版本包含Power Query目标网站的分析权限确保不违反robots.txt基础的网页结构理解能力能使用浏览器开发者工具提示在开始前建议先在浏览器中手动访问目标天气网站确认所需数据确实以静态形式存在于网页源码中。许多现代网站采用动态加载这种情况下传统爬取方法可能失效。常见的天气数据网站通常采用两种数据组织形式表格形式直接展示在HTML中通过API接口返回JSON数据我们需要先通过浏览器开发者工具F12确认数据类型。以国内某天气历史数据网站为例按F12打开开发者工具切换到网络选项卡刷新页面后观察XHR请求发现实际数据是通过API接口返回的JSON格式。2. 编码问题深度解析与解决方案第一次尝试使用Web.Contents函数获取数据时我遇到了令人头疼的乱码问题。返回的中文全部显示为问号或乱码字符。经过排查发现这是字符编码不匹配导致的典型问题。编码问题排查流程确认网页原始编码通过查看HTML的meta charset标签或HTTP响应头匹配Excel对应的编码标识符在数据获取流程中正确指定编码大多数现代网站使用UTF-8编码对应的Excel编码标识符是65001。但有些老旧天气网站可能仍在使用GB2312或GBK编码这时就需要使用对应的代码页标识符。// UTF-8编码的正确使用方式 Text.FromBinary(Web.Contents(http://example.com/weather), 65001)常见中文编码对应表编码类型Excel代码页适用场景UTF-865001绝大多数现代网站GB2312936一些政府或老牌气象网站GBK936GB2312的扩展BIG5950港澳台地区网站当不确定编码类型时可以尝试以下调试方法先用65001(UTF-8)尝试如果乱码尝试936(GBK)观察乱码模式全角乱码通常是GBK/UTF-8混淆半角问号可能是ASCII强制转换3. 动态URL构造与参数化请求天气数据通常需要按城市和日期进行查询这就要求我们动态构造请求URL。以某天气历史数据网站为例观察其URL模式http://www.tianqihoubao.com/lishi/beijing/month/202101.html可以看出URL中包含三个变量元素城市拼音beijing年份2021月份01在Excel中实现参数化请求的步骤如下3.1 创建参数表首先在工作表中创建参数表格城市拼音参数年份月份北京beijing202101上海shanghai2021023.2 在Power Query中建立参数化查询let 城市参数 Excel.CurrentWorkbook(){[Name参数表]}[Content]{0}[拼音参数], 年份参数 Text.From(Excel.CurrentWorkbook(){[Name参数表]}[Content]{0}[年份]), 月份参数 Text.PadStart(Text.From(Excel.CurrentWorkbook(){[Name参数表]}[Content]{0}[月份]), 2, 0), 基础URL http://www.tianqihoubao.com/lishi/, 完整URL 基础URL 城市参数 /month/ 年份参数 月份参数 .html, 源 Text.FromBinary(Web.Contents(完整URL), 65001), 数据 Web.Page(源){0}[Data] in 数据3.3 处理分页和大量请求当需要获取多个月份或多个城市数据时需要注意添加延迟防止被封Function.Sleep(1000)在每个请求间暂停1秒错误处理使用try...otherwise处理单个请求失败分批获取每次获取3-6个月数据后暂停2分钟4. 反爬机制应对策略在连续请求天气数据时我很快遇到了403禁止访问错误。这是网站的反爬机制在起作用。经过测试发现以下策略可以有效降低被封风险IP限制规避方案控制请求频率每30秒不超过10次请求模拟真实浏览器请求头使用会话保持Cookie在Power Query中设置请求头的方法Web.Contents(http://example.com, [ Headers [ #User-AgentMozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36, #Accepttext/html,application/xhtmlxml, #Refererhttp://example.com ], ManualStatusHandling {400, 403, 404} ])常见反爬现象及应对现象可能原因解决方案403错误IP被封降低频率更换代理IP验证码行为异常添加人工延迟模拟人类操作空数据返回参数错误检查URL构造逻辑连接重置请求过快增加请求间隔5. 数据清洗与结构化处理获取到原始天气数据后通常需要进行大量清洗工作才能得到规整的表格。常见问题包括多余的空格和换行符不统一的单位如℃有的带符号有的不带合并单元格导致的结构问题异常值和缺失值处理Power Query清洗技巧// 去除多余空格 Table.TransformColumns(源, {{温度, Text.Trim, type text}}) // 统一温度单位 Table.ReplaceValue( #上一步, ℃, , Replacer.ReplaceText, {最高温度, 最低温度} ) // 处理缺失值 Table.ReplaceValue( #上一步, null, N/A, Replacer.ReplaceValue, {降水量, 风速} )完整的数据处理流程去除HTML标签和特殊字符拆分合并的日期时间字段统一数值单位和格式处理空值和异常值类型转换文本→数值/日期最终质量检查6. 自动化与调度更新对于需要定期更新的天气数据我们可以设置自动化流程参数化日期范围自动获取最近N天的数据增量更新只获取数据库中不存在的新数据定时刷新利用Windows任务计划或Mac自动化定期执行在Power Query中设置动态日期范围let 结束日期 DateTime.Date(DateTime.LocalNow()), 开始日期 Date.AddDays(结束日期, -30), // 获取最近30天数据 日期列表 List.Dates(开始日期, Duration.Days(结束日期 - 开始日期)1, #duration(1,0,0,0)), 转换为文本 List.Transform(日期列表, each Date.ToText(_, yyyyMMdd)), 构造URL List.Transform(转换为文本, each http://example.com/daily/ _ .html) in 构造URL实际项目中我建立了一个包含20个城市、自动更新最近3个月天气数据的解决方案每天通过任务计划自动运行将处理好的数据输出到共享文件夹供团队使用。整个过程从最初的编码乱码问题到最终稳定运行前后经历了十余次重大调整。