【Python脚本】20个必学的Excel表格操作

本文将介绍使用 Python 处理 Excel 文件的多种方法，涵盖从基本的读写操作到高级的数据分析与可视化。通过这些示例，你可以学习如何高效地管理和分析 Excel 数据。

---

• 包含编程资料、学习路线图、源代码、软件安装包等！【[点击这里]】！

---

1.安装必要的库

• 在开始之前，确保安装了 pandas 和 openpyxl 这两个库。这两个库是处理 Excel 文件的基础。

pip install pandas openpyxl  

---

2.读取 Excel 文件

• 首先，让我们看看如何读取一个 Excel 文件。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx', engine='openpyxl')    
# 显示前五行数据  
print(df.head())  

• 输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

---

3.写入 Excel 文件

• 接下来，我们将创建一个新的 DataFrame 并将其写入新的 Excel 文件。

import pandas as pd    
# 创建一个 DataFrame  
data = {  
    'A': [1, 2, 3, 4, 5],  
    'B': [100, 200, 300, 400, 500],  
    'C': [1000, 2000, 3000, 4000, 5000]}  
df = pd.DataFrame(data)    
# 将 DataFrame 写入 Excel 文件  
df.to_excel('output.xlsx', index=False)  

---

4.选择特定列

• 有时候我们只需要 Excel 文件中的某些列。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx', usecols=['A', 'C'])    
# 显示前五行数据  
print(df.head())  

• 输出：

   A     C  
0  1  1000  
1  2  2000  
2  3  3000  
3  4  4000  
4  5  5000

---

5.过滤数据

• 过滤数据可以帮助我们找到特定条件下的记录。

import pandas as pd  
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 过滤出 A 列大于 3 的行  
filtered_df = df[df['A'] > 3]    
# 显示过滤后的数据  
print(filtered_df)  

• 输出：

  A    B     C  
3  4  400  4000  
4  5  500  5000

---

6.数据排序

• 排序数据可以让我们更容易地查看数据的趋势。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 按照 A 列降序排列  
sorted_df = df.sort_values(by='A', ascending=False)    
# 显示排序后的数据  
print(sorted_df)  

• 输出：

   A    B     C  
4  5  500  5000  
3  4  400  4000  
2  3  300  3000  
1  2  200  2000  
0  1  100  1000

---

7.数据分组

• 数据分组可以帮助我们分析不同类别的数据。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 按照 B 列分组并计算平均值  
grouped_df = df.groupby('B').mean()    
# 显示分组后的数据  
print(grouped_df)  

• 输出：

          A        C  
B                      
100  1.000000  1000.0  
200  2.000000  2000.0  
300  3.000000  3000.0  
400  4.000000  4000.0  
500  5.000000  5000.0

---

8.添加新列

• 我们可以根据现有列的数据轻松添加新列。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 添加新列 D  
df['D'] = df['A'] * df['B']    
# 显示更新后的数据  
print(df)  

• 输出：

  A    B     C     D  
0  1  100  1000   100  
1  2  200  2000   400  
2  3  300  3000   900  
3  4  400  4000  1600  
4  5  500  5000  2500

---

9.更新单元格

• 有时候我们需要更新特定单元格的值。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 更新 A 列第 2 行的值  
df.at[1, 'A'] = 10    
# 显示更新后的数据  
print(df)  

• 输出：

 A    B     C  
0  1  100  1000  
1 10  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

---

10.删除列

• 删除不需要的列可以简化数据集。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 删除 C 列  
del df['C']    
# 显示更新后的数据  
print(df)  

• 输出：

   A    B  
0  1  100  
1  2  200  
2  3  300  
3  4  400  
4  5  500

---

11.合并多个 Excel 文件

• 合并多个 Excel 文件可以方便地将数据集中到一起。

import pandas as pd    
# 读取多个 Excel 文件  
df1 = pd.read_excel('example1.xlsx')  
df2 = pd.read_excel('example2.xlsx')    
# 合并两个 DataFrame  
merged_df = pd.concat([df1, df2], ignore_index=True)    
# 显示合并后的数据  
print(merged_df)  

• 输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

---

12.数据透视表

• 数据透视表是一种强大的工具，可以快速汇总和分析数据。

import pandas as pd   
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 创建数据透视表  
pivot_table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc=sum)    
# 显示数据透视表  
print(pivot_table)  

• 输出：

B       100   200   300   400   500  
A                          
1       1000  NaN   NaN   NaN   NaN  
2       NaN   2000  NaN   NaN   NaN  
3       NaN   NaN   3000  NaN   NaN  
4       NaN   NaN   NaN   4000  NaN  
5       NaN   NaN   NaN   NaN   5000  

---

13.数据合并

• 合并多个数据集可以让你更好地分析数据之间的关系。

import pandas as pd    
# 读取两个 Excel 文件  
df1 = pd.read_excel('example1.xlsx')  
df2 = pd.read_excel('example2.xlsx')    
# 使用内连接合并两个数据集  
merged_df = pd.merge(df1, df2, on='A', how='inner')    
# 显示合并后的数据  
print(merged_df)  

• 输出：

   A    B_x    C_x    B_y    C_y  
0  1   100  1000    10    100  
1  2   200  2000    20    200  
2  3   300  3000    30    300  
3  4   400  4000    40    400  
4  5   500  5000    50    500

---

14.数据清洗

• 数据清洗是数据分析的重要步骤，可以去除无效或错误的数据。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 去除空值  
df.dropna(inplace=True)    
# 去除重复行  
df.drop_duplicates(inplace=True)    
# 显示清洗后的数据  
print(df)  

• 输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

---

15.数据类型转换

• 正确设置数据类型有助于节省内存并提高性能。

import pandas as pd    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  
# 将 A 列转换为整型  
df['A'] = df['A'].astype(int)    
# 将 B 列转换为浮点型  
df['B'] = df['B'].astype(float)    
# 显示转换后的数据  
print(df.dtypes)  

• 输出：

A         int64  
B        float64  
C        float64  
dtype: object

---

16.数据可视化

• 使用 matplotlib库可以方便地绘制图表。

import pandas as pd  
import matplotlib.pyplot as plt    
# 读取 Excel 文件  
df = pd.read_excel('example.xlsx')    
# 绘制柱状图  
plt.bar(df['A'], df['B'])  
plt.xlabel('A')  
plt.ylabel('B')  
plt.title('A vs B')  
plt.show()  

---

17.多页 Excel 文件操作

• 处理多页 Excel 文件时，可以使用openpyxl库。

from openpyxl 
import load_workbook  
# 加载 Excel 文件  
wb = load_workbook('example.xlsx')    
# 获取所有工作表名称  
sheet_names = wb.sheetnames  
print(sheet_names)    
# 选择特定工作表  
sheet = wb['Sheet1']    
# 读取特定单元格的值  
cell_value = sheet.cell(row=1, column=1).value  
print(cell_value)  

• 输出：

['Sheet1', 'Sheet2', 'Sheet3']  
1  

---

18.条件格式化

• 条件格式化可以帮助你突出显示特定数据。

import pandas as pd  
from openpyxl import Workbook  
from openpyxl.styles import PatternFill  
  
# 创建一个新的 DataFrame  
data = {  
    'A': [1, 2, 3, 4, 5],  
    'B': [100, 200, 300, 400, 500],  
    'C': [1000, 2000, 3000, 4000, 5000]  
}  
df = pd.DataFrame(data)  
  
# 将 DataFrame 写入 Excel 文件  
wb = Workbook()  
ws = wb.active  
df.to_excel(ws, index=False)  
  
# 设置条件格式化  
for row in ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=2):  
    for cell in row:  
        if cell.value > 300:  
            cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")  
  
# 保存 Excel 文件  
wb.save('condition.xlsx')  

---

19.自定义样式

• 自定义样式可以让你的 Excel 文件更加美观。

import pandas as pd  
from openpyxl import Workbook  
from openpyxl.styles import Font, Border, Side, Alignment  
  
# 创建一个新的 DataFrame  
data = {  
    'A': [1, 2, 3, 4, 5],  
    'B': [100, 200, 300, 400, 500],  
    'C': [1000, 2000, 3000, 4000, 5000]  
}  
df = pd.DataFrame(data)  
  
# 将 DataFrame 写入 Excel 文件  
wb = Workbook()  
ws = wb.active  
df.to_excel(ws, index=False)  
  
# 设置字体样式  
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=3):  
    for cell in row:  
        cell.font = Font(bold=True, color="FFFFFF")  
  
# 设置边框样式  
thin_border = Border(left=Side(style='thin'),   
                     right=Side(style='thin'),   
                     top=Side(style='thin'),   
                     bottom=Side(style='thin'))  
  
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3):  
    for cell in row:  
        cell.border = thin_border  
  
# 设置居中对齐  
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3):  
    for cell in row:  
        cell.alignment = Alignment(horizontal='center', vertical='center')  
  
# 保存 Excel 文件  
wb.save('styled.xlsx')  

---

20.批量处理文件

• 批量处理多个 Excel 文件可以大大提高效率。

import os  
import pandas as pd  
  
# 获取目录中的所有 Excel 文件  
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]  
  
# 循环处理每个文件  
for file in files:  
    # 读取 Excel 文件  
    df = pd.read_excel(file)  
      
    # 进行数据处理  
    df['D'] = df['A'] * df['B']  
      
    # 保存处理后的文件  
    df.to_excel(f'processed_{file}', index=False)  

---

21.实战案例：员工绩效分析

• 假设你有一个包含员工绩效数据的 Excel 文件，需要分析每位员工的绩效。

import pandas as pd  
  
# 读取 Excel 文件  
performance_data = pd.read_excel('employee_performance.xlsx')  
  
# 计算每位员工的总销售额  
performance_data['Total Sales'] = performance_data['Quantity'] * performance_data['Price']  
  
# 分析每位员工的平均销售额  
average_sales = performance_data.groupby('Employee')['Total Sales'].mean()  
  
# 显示平均销售额  
print(average_sales)  

• 输出：

Employee  
Alice    5000.0  
Bob      6000.0  
Charlie  7000.0  
Dave     8000.0  
Eve      9000.0  
dtype: float64  

---

总结

• 本文介绍了使用 Python 处理 Excel 文件的各种方法，包括读取、写入、筛选、排序、分组、添加新列、更新单元格、删除列、合并多个文件、数据透视表、数据合并、数据清洗、数据类型转换、数据可视化、多页文件操作、条件格式化、自定义样式以及批量处理等。
• 通过这些示例，你可以掌握如何高效地管理和分析 Excel 数据。
  

总结

• 最后希望你编程学习上不急不躁,按照计划有条不紊推进,把任何一件事做到极致,都是不容易的,加油,努力！相信自己！

文末福利

• 最后这里免费分享给大家一份Python全套学习资料，希望能帮到那些不满现状，想提升自己却又没有方向的朋友，也可以和我一起来学习交流呀。

包含编程资料、学习路线图、源代码、软件安装包等！【[点击这里]】领取！

• ① Python所有方向的学习路线图，清楚各个方向要学什么东西
• ② 100多节Python课程视频，涵盖必备基础、爬虫和数据分析
• ③ 100多个Python实战案例，学习不再是只会理论
• ④ 华为出品独家Python漫画教程，手机也能学习

可以扫描下方二维码领取【保证100%免费】