Excel 2021 + BAT 批处理：3步实现千级文件批量重命名与格式转换-平芜编程栈

Excel 2021 + BAT 批处理：3步实现千级文件批量重命名与格式转换

当你的硬盘里堆积着数千张照片、文档或音频文件，文件名杂乱无章得像被猫抓过的毛线球时，手动重命名无疑是场噩梦。我曾接手过一个项目，需要整理5年积累的12000+份客户资料，文件名包含日期、编号、业务员缩写等混乱信息。尝试过各种批量改名工具后，最终发现Excel 2021的Power Query+BAT批处理的组合才是真正的效率杀手——不仅免费、无需安装第三方软件，还能处理最复杂的命名规则。

1. 从混乱到秩序：Power Query自动化提取文件名

传统方法依赖FILES函数或dir命令导出文件名，但遇到特殊字符或长路径时容易崩溃。Power Query的文件夹数据导入引擎能稳定处理上万个文件，就像给文件系统装了SQL查询接口。

1.1 配置Power Query数据源

打开Excel 2021，依次点击：

数据选项卡 →获取数据→自文件→从文件夹
在弹出的对话框中粘贴目标文件夹路径（例如D:\客户资料\2023）
点击打开进入查询编辑器界面

重要提示：路径中若包含[ ]等特殊字符，需在Power Query编辑器中手动修改Source步骤的路径公式，用#号包裹路径字符串

1.2 智能清洗文件列表

在查询编辑器中，你会看到包含文件所有元数据的表格。按Ctrl键选择三列关键数据：

Name（文件名）
Extension（扩展名）
Folder Path（文件夹路径）

右键点击列标题，选择删除其他列精简数据。接着点击转换选项卡，进行关键处理：

添加自定义列：完整路径 = [Folder Path] & "\" & [Name]
筛选掉临时文件：点击Name列筛选按钮，排除~$*开头的文件
处理特殊字符：选择替换值功能，将文件名中的%20替换为空格

点击关闭并上载，数据将作为智能表格载入Excel工作表。不同于静态数据，这个表格能通过右键刷新随时同步文件夹最新状态。

2. 构建可复用的BAT命令模板

当需要同时修改文件名和扩展名时（如将.jpeg统一转为.jpg），传统方法需要复杂公式拼接。这里给出一个带容错机制的万能模板：

="ren """ & A2 & """ """ & IFERROR(REGEXREPLACE(B2,"[\\/:*?""<>|]","_"),"ERROR") & IF(C2="",RIGHT(A2,FIND(".",A2)-1),C2) & """"

将此公式拖拽填充至所有行，将生成如下安全命令：

ren "Old File[1].jpeg" "New_File_1.jpg" ren "A/B:C*.docx" "A_B_C_.docx" # 自动替换非法字符

2.1 特殊字符处理方案

Windows文件名禁止使用\ / : * ? " < > |等字符，建议在Excel中预清洗：

原始字符	替换方案	公式示例
空格	保留或转下划线	`SUBSTITUTE(A2," ","_")`
中文括号	转英文括号	`SUBSTITUTE(SUBSTITUTE(A2,"（","("),"）",")")`
连续符号	合并为单个	`REGEXREPLACE(A2,"[_]{2,}","_")`

2.2 批量格式转换技巧

如需在改名同时转换文件格式，可在BAT命令后追加格式转换命令。例如将PNG转JPG：

@echo off setlocal enabledelayedexpansion for %%i in (*.png) do ( magick "%%i" "%%~ni.jpg" del "%%i" )

注意：需提前安装ImageMagick，其convert命令比系统自带工具支持更多格式

3. 高级实战：动态规则与错误处理

面对需要按规则动态生成新文件名的情况（如客户名_日期_序号.pdf），Excel的TEXTJOIN函数能构建复杂命名逻辑：

=TEXTJOIN("_",TRUE, VLOOKUP(LEFT(A2,FIND("_",A2)-1),客户表!A:B,2,FALSE), TEXT(提取日期(A2),"yyyymmdd"), RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"_","@",2))) )

3.1 错误预防机制

在最终生成BAT文件前，建议添加以下检查步骤：

重复检测：使用COUNTIF函数检查新文件名是否重复
```
=IF(COUNTIF($B$2:$B$1000,B2)>1,"重复","")
```
长度验证：Windows路径限制260字符
```
=IF(LEN(D2)>200,"超长","")
```
模拟运行：在BAT文件首行添加echo命令预览效果
```
@echo off echo 测试模式（实际运行请删除本行）
```

3.2 自动化部署流程

将完整流程封装为Excel宏，实现一键生成BAT：

Sub GenerateBAT() Dim batContent As String batContent = "@echo off" & vbCrLf & "chcp 65001" & vbCrLf For Each rng In Selection batContent = batContent & rng.Value & vbCrLf Next CreateObject("Scripting.FileSystemObject"). _ CreateTextFile("批量处理.bat", True).Write batContent End Sub

我曾用这套方法为律师事务所处理过200GB的扫描件归档，从手动操作预计3周缩短到2小时完成。关键是要像编程一样思考——把命名规则转化为可执行的逻辑链条，让重复劳动变成按按钮的瞬间操作。