Excel高级功能详细文档03

公式与函数

1.1 基本公式

手动操作：

• 输入公式以=开始
• 引用单元格：=A1+B1
• 引用区域：=SUM(A1:A10)
• 绝对引用：=$A$1（按F4切换）

常用运算符：

• 算术：+-*/^
• 比较：=><>=<=<>
• 文本连接：&

1.2 常用函数

数学函数：

=SUM(A1:A10) # 求和 =AVERAGE(A1:A10) # 平均值 =MAX(A1:A10) # 最大值 =MIN(A1:A10) # 最小值 =COUNT(A1:A10) # 计数 =ROUND(A1, 2) # 四舍五入

文本函数：

=LEFT(A1, 3) # 左边3个字符 =RIGHT(A1, 3) # 右边3个字符 =MID(A1, 2, 3) # 从第2个字符开始取3个 =LEN(A1) # 文本长度 =CONCATENATE(A1, B1) # 连接文本 =UPPER(A1) # 转大写 =LOWER(A1) # 转小写

日期函数：

=TODAY() # 今天日期 =NOW() # 当前日期时间 =YEAR(A1) # 提取年份 =MONTH(A1) # 提取月份 =DAY(A1) # 提取日 =DATE(2024, 1, 1) # 创建日期

逻辑函数：

=IF(A1>60, "及格", "不及格") =AND(A1>0, B1>0) # 全部为真 =OR(A1>0, B1>0) # 任一为真 =IFERROR(A1/B1, 0) # 错误时返回0

查找函数：

=VLOOKUP(A1, A:C, 2, FALSE) # 垂直查找 =INDEX(B:B, MATCH(A1, A:A, 0)) # INDEX+MATCH组合

1.3 Python实现公式

importpandasaspdimportnumpyasnp df=pd.read_excel('数据.xlsx')# SUMdf['总和']=df[['列1','列2','列3']].sum(axis=1)# AVERAGEdf['平均']=df[['列1','列2','列3']].mean(axis=1)# IFdf['结果']=df['分数'].apply(lambdax:'及格'ifx>=60else'不及格')# VLOOKUPlookup_df=pd.read_excel('查找表.xlsx')df=df.merge(lookup_df,left_on='ID',right_on='ID',how='left')# CONCATENATEdf['全名']=df['姓']+df['名']df.to_excel('结果.xlsx',index=False)

条件格式

2.1 突出显示单元格规则

手动操作：

• 开始 → 条件格式 → 突出显示单元格规则

规则类型：

• 大于/小于/等于
• 介于
• 文本包含
• 重复值

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.stylesimportPatternFillfromopenpyxl.formatting.ruleimportCellIsRule wb=Workbook()ws=wb.active# 添加数据foriinrange(1,11):ws.append([i*10])# 大于30的单元格标红red_fill=PatternFill(start_color='FF0000',fill_type='solid')rule=CellIsRule(operator='greaterThan',formula=['30'],fill=red_fill)ws.conditional_formatting.add('A1:A10',rule)wb.save('条件格式.xlsx')

2.2 数据条

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.formatting.ruleimportDataBarRule wb=Workbook()ws=wb.activeforiinrange(1,11):ws.append([i*10])# 添加数据条rule=DataBarRule(start_type='min',end_type='max',color='638EC6')ws.conditional_formatting.add('A1:A10',rule)wb.save('数据条.xlsx')

2.3 色阶

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.formatting.ruleimportColorScaleRule wb=Workbook()ws=wb.activeforiinrange(1,11):ws.append([i*10])# 添加色阶（红-黄-绿）rule=ColorScaleRule(start_type='min',start_color='FF0000',mid_type='percentile',mid_value=50,mid_color='FFFF00',end_type='max',end_color='00FF00')ws.conditional_formatting.add('A1:A10',rule)wb.save('色阶.xlsx')

图表制作

3.1 创建柱形图

手动操作：

• 选中数据 → 插入 → 柱形图

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.chartimportBarChart,Reference wb=Workbook()ws=wb.active# 添加数据data=[['类别','数值'],['A',10],['B',20],['C',30],['D',25]]forrowindata:ws.append(row)# 创建柱形图chart=BarChart()chart.title="销售数据"chart.x_axis.title="类别"chart.y_axis.title="数值"data_ref=Reference(ws,min_col=2,min_row=1,max_row=5)cats_ref=Reference(ws,min_col=1,min_row=2,max_row=5)chart.add_data(data_ref,titles_from_data=True)chart.set_categories(cats_ref)ws.add_chart(chart,"D2")wb.save('柱形图.xlsx')

3.2 折线图

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.chartimportLineChart,Reference wb=Workbook()ws=wb.active data=[['月份','销售额'],['1月',100],['2月',120],['3月',150],['4月',130],['5月',180]]forrowindata:ws.append(row)chart=LineChart()chart.title="月度销售趋势"chart.x_axis.title="月份"chart.y_axis.title="销售额"data_ref=Reference(ws,min_col=2,min_row=1,max_row=6)cats_ref=Reference(ws,min_col=1,min_row=2,max_row=6)chart.add_data(data_ref,titles_from_data=True)chart.set_categories(cats_ref)ws.add_chart(chart,"D2")wb.save('折线图.xlsx')

3.3 饼图

Python操作：

fromopenpyxlimportWorkbookfromopenpyxl.chartimportPieChart,Reference wb=Workbook()ws=wb.active data=[['部门','人数'],['销售',30],['技术',45],['市场',20],['人事',15]]forrowindata:ws.append(row)chart=PieChart()chart.title="部门人数分布"data_ref=Reference(ws,min_col=2,min_row=1,max_row=5)cats_ref=Reference(ws,min_col=1,min_row=2,max_row=5)chart.add_data(data_ref,titles_from_data=True)chart.set_categories(cats_ref)ws.add_chart(chart,"D2")wb.save('饼图.xlsx')

数据分析工具

4.1 单变量求解

手动操作：

• 数据 → 模拟分析 → 单变量求解

Python实现：

fromscipy.optimizeimportfsolvedefequation(x):returnx**2-4result=fsolve(equation,1)print(f"解:{result[0]}")

4.2 规划求解

Python实现：

fromscipy.optimizeimportlinprog# 目标函数系数c=[-1,-2]# 约束条件A=[[1,1],[2,1]]b=[10,15]x_bounds=(0,None)y_bounds=(0,None)result=linprog(c,A_ub=A,b_ub=b,bounds=[x_bounds,y_bounds])print(f"最优解: x={result.x[0]}, y={result.x[1]}")