【自动化办公】用Python在Excel中查找并替换数据_excel如何用代码实现替换数据

本文链接：https://blog.youkuaiyun.com/python03012/article/details/136498580

本文介绍了如何使用Python及其相关库如pandas、openpyxl和xlwings在Excel中高效查找和替换数据，以及如何结合其他工具如jupyternotebook进行开发。着重讲解了如何使用Python自动完成查找替换任务，提高工作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、Python处理Excel

二、用Python在Excel中查找并替换数据

三、往期推荐

四、文末推荐与福利

一、Python处理Excel

Python处理Excel的好处

1.批量操作：当要处理众多Excel文件时，例如出现重复性的手工劳动，那么使用Python就可以实现批量扫描文件、自动化进行处理，利用代码代替手工重复劳动，实现自动化，是Python第一个比Excel强大的地方

2.大型文件，当Excel文件超过几十兆、甚至上百兆时，打开文件很慢、处理文件更加慢，这时候若使用Python，会发现处理几十兆、几百兆甚至几GB都是没有问题的

3.当使用Excel进行复杂的计算时，会使用VBA，但是VBA本身是过时并且复杂的语言，Python是当前最简单且容易实现的一门语言，用Python能够处理比VBA难度更高的业务逻辑

4.Python是通用语言，不仅可以处理Excel，使用Python就可以得到很多额外的功能，例如：爬虫、发布网页的Web服务、与数据库进行连接、同时结合word和PPT进行处理、加入定时任务处理、人工智能分析等，各种额外的功能，这是Excel和VBA所不具备的

Python处理Excel主要有三大类库

1.pandas：是Python领域非常重要的，用于数据分析和可视化的类库，在处理Excel中，90%可以利用pandas类库就可以搞掂，利用pandas就可以读取Excel、处理Excel和输出Excel，但是pandas也有缺点，就是无法做到格式类，例如Excel中合并单元、大量复杂的样式（看起来很精美）的时候，用pandas无法搞掂，此时，依然是使用pandas结合openyxl、xlwings来搞掂需求

2.openpyxl：若电脑上未安装office时，也可以使用openpyxl，这个类型可以运行在linux上，并且也可以实现操作大部分Excel格式和样式的功能，使用它配合pandas，也可以完成大部分场景的需求

3.xlwings：比openyxl更加强大，只能运行在Windows或者Mac系统，并且该系统中必须安装了office才能运行，xlwings的原理，就是基于当前系统已经安装好的office软件，来进行功能的拓展来操作Excel

使用pandas的时候，经常会结合其他类库，来完成更加复杂的功能
- requests, bs4：可以完成爬虫的功能
- flask：可以做网页，把表格展示在网页上
- Matplotlib：读取表格后，进行可视化
- sklearn：进行复杂的数据分析时，也可以结合机器学习Sklearn把读取的Excel数据，进行数据分析和机器学习
- Python-docx：也可以结合Python-docx类库，实现Excel和word的互通
- smtplib：也可以使用smtplib，讲Excel数据发送邮件出去
开发环境

操作系统：使用windows, mac都可以

Python版本：系统中需要安装Python3.6以上的版本，Python2已经过期不建议使用，Python3.6以前的版本功能相对弱，最好就是采用Python3.6以上的版本

开发工具：有两个可以选择，jupyter notebook，是个网页编辑器，可以运行Python，常常用于交互性、探索性的开发；pycharm，用于成熟脚本，或者web服务的一些开发；这两个工具可以随意选择。

二、用Python在Excel中查找并替换数据

技术工具：

Python版本:3.9

代码编辑器：jupyter notebook

    随着项目的进展，需要经常在Excel业务表格中查找及替换数据，已保证数据与实际项目进度一致。手动一个一个查找，然后替换，效率太低，还容易遗漏。现在我们来试试用Python自动完成查找及替换吧。具体要求如下。

首先，我们先将左边表格中的数据提取，并存入字典data，其键为“查找内容”中的数据，值为“替换内容”中的数据。

from openpyxl import load_workbook #用于读取Excel中的信息
#获取Excel表格中的数据
wb = load_workbook('查找替换.xlsx')#读取工作簿
ws = wb.active #读取活动工作表
data={} #新建字典，用于储存数据

for row in range(2,ws.max_row+1):
    chazhao = str(ws['A' + str(row)].value)  #转换成字符串，以免后续比对时出现数据类型冲突
    tihuan = str(ws['B' + str(row)].value) #转换成字符串，以免后续比对时出现数据类型冲突
    data[chazhao]=tihuan #键值对应存入字典

data

     然后，读取目标表格，将D列中的所有数据提取出来，以便后续比对及替换。通过\`for\`循环遍历“原表”，将D列每个单元格的值提取并存入\`ID\_list\`。然后通过切片\`ID\_list\[:10\]\`查看前10个数据是否OK。结果显示相当正常。

wb = load_workbook('原表.xlsx') #读取目标工作簿
ws = wb.active
ID_list = [] #新建一个列表，用于储存原表D列的信息
for row in range(2,ws.max_row+1):
    ID = ws['D' + str(row)].value #遍历整个工作表，将D列的数据逐个存入ID变量
    ID_list.append(ID) #将读取到的结果存入列表
ID_list[:10] #查看列表中前10个数据

type("")

    为了比对数据，我们需要将\`'说明码：77601'\`中的“说明码：”字符拿掉，只保留“77601”。于是调用\`split\`函数来进行分割，并将分割好的数字部分存入新建的列表\`code\`。不好，居然报错了，说\`ID\_list\`列表中有"None"（空）类型的数据，而"None"类型的数据是不能使用\`split\`函数的。目测了一下，\`ID\_list\`列表中除了\`'说明码：77601'\`和\`''\`这样的空字符串，没看到None啊。再回来“原表”侦察一下，发现最下面还有一些单元格很有嫌疑。原来是表尾有一些“供应商”和仓位信息，这些信息所在位置对应的D列都是空单元格，其值为"None"。用\`ID\_list\[-10:\]\`查看最后10个元素，果然都是"None"。

code = [i.split("：")[-1] for i in ID_list]
code

ID_list[-10:]

    这样，我们就知道\`ID\_list\`中有三种数据，即含内容的字符串（比如'说明码：77601'），空字符串（比如''）和空值None。因此，需要修改一下字符串分割代码如下。加入了\`if\`判断语句，如果\`ID\_list\`中的值是None，则放入None占位，以保持列表的值的顺序与原表一致；值不是None，则按"："符号分割，并放分割后的最后一个值\`\[-1\]\`进入新列表code。空字符串在这里也要经过\`split\`分割，但其中没有“：”符号，所以就分割不了，只得直接跳过，最后放入新列表的还是空字符串。

code = []
for i in ID_list:
    if i == None:# 如果是None，则放入None占位，以保持列表的值的顺序与原表一致
        code.append(None)
    else:
        code.append(i.split("：")[-1]) #不是None,则按"："符号分割，并放分割后的最后一个值进入新列表code
code[:10]

    处理完数据后，即可开始查找并替换目标数据了。用\`for\`循环遍历列表\`code\`，即原表D列中的数字部分。如果其中的值也存在于data的键中，即语句\`if code\[i\] in data\`，则将原表中D列(\`column=4\`)对应的行中的数据改写成新的值。新的值由两部分组成，一部分是“说明码：”这样的，即\`ID\_list\[i\].split("：")\[0\]\`，另一部分则是要替换的数字，即\`data\[code\[i\]\]\`。这样保证只替换了需要替换的数字部分，而保留中文和冒号部分。最后保存为新的文件，替换完成。

for i in range(len(code)):
    if code[i] in data:
        ws.cell(row=i+2,column=4).value = ID_list[i].split("：")[0] +"："+ data[code[i]]
wb.save('原表-替换.xlsx')

    如果以上不能通过观察原表，发现空值问题，还可以用\`enumerate\`函数给列表里的所有元素加上索引，以便精确定位\`ID\_list\`中的空值。加上索引后，在转换成列表，并存入新的列表\`ID\_list\_idx\`中。观察其中前10个数据，可见索引已加好了。然后遍历新列表，判断其中的值是否为空值，若是则打印其对应的索引编号，这样就能精准定位哪些是空值了，再回到原Excel表，就容易弄清楚发生了什么事啦。其中，新列表中的元素的结构是一个元组，像这样\`(2, '说明码：77601')\`，\`i\[0\]\`是索引\`2\`，\`i\[1\]\`是索引对应的值\`说明码：77601\`。

ID_list_idx = list(enumerate(ID_list)) #加索引
ID_list_idx[:10]

for i in ID_list_idx: #遍历列表
    if i[1] == None: #判断索引对应的值是否为空值。i的结构是一个元组，像这样(2, '说明码：77601')，i[0]是索引，i[1]是索引对应的值
        print(i[0]) #打印索引编号

三、往期推荐

Python提取pdf中的表格数据（附实战案例）

使用Python一键提取PDF中的表格到Excel

使用Python批量生成PPT版荣誉证书

使用Python批量处理Excel文件并转为csv文件

四、文末福利（2024最新全套Python资料文末领取）

Python经验分享

学好 Python 不论是用于就业还是做副业赚钱都不错，而且学好Python还能契合未来发展趋势——人工智能、机器学习、深度学习等。
小编是一名Python开发工程师，自己整理了一套最新的Python系统学习教程，包括从基础的python脚本到web开发、爬虫、数据分析、数据可视化、机器学习等。如果你也喜欢编程，想通过学习Python转行、做副业或者提升工作效率，这份【最新全套Python学习资料】一定对你有用！

包括：Python激活码+安装包、Python web开发，Python爬虫，Python数据分析，人工智能、机器学习、Python量化交易等学习教程。带你从零基础系统性的学好Python！