公式与函数
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]}")
)
