Excel公式函数运用

有用的excel函数公式

项目检验重复公式

公式IF(MATCH($A2,$A$2:$A$18,)=ROW()-1,"","重复")

生日7日提醒公式

IF(DATEDIF(U13-7,TODAY(),"YD")<=7,"提醒","")

对大于100的产量进行求和

SUMPRODUCT((B2:B11>100)*B2:B11)

对大于100或者小于110的数据 SUMPRODUCT((B12:B27>100)*(B12:B27<110)*B12:B27) 求和 对一车间男性职工的工资求和 SUMPRODUCT((B2:B10="一车间")*(C2:C10="男")*D2:D10) 对姓赵的女职工工资求和 求前三名产量之和 偏移3位求和 SUMPRODUCT((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10) SUM(LARGE(B2:B10,{1,2,3})) SUM(OFFSET($B$1,MATCH(A15,A2:A11,0),,,3))

求所有工作表相同区域数据之 SUM(A组:E组!B2:B9) 和 用SUM函数计数 SUMPRODUCT((B2:B9="男")*1)

多个工作表不同区域求前三名 SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C 产量和 组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)) 大写转化 小写转化 显示某位置对应的内容 显示某位置对应的内容 C2:C7中满足A10和B10的多条 件查找 在C列查找含有C1字段的第一 个位置 满足A10和B10的位置 创建下拉列表 求固定间隔n列的单元格之 和，例如求第2行B、D、F、H 列之和 间隔2列，求A、D、G、J列 A2至A49每隔3行相加 NumUpper(A2) NumLower(B2) VLOOKUP(B3,IF({1,0},E2:E7,D2:D7),2,0) INDEX($D$2:$D$7,MATCH($B$3,$E$2:$E$7,0)) LOOKUP(1,0/(A2:A7=A10)*(B2:B7=B10),C2:C7) MATCH("*"&C1&"*",$C:$C,0) MATCH(A10&"|"&B10,A2:A7&"|"&B2:B7,0) 数据-有效性\设置\有效性条件的"允许"中选序列,下面的来源框 中,输入要创建的下拉列表内容,注意每条目间用逗号隔开 SUMPRODUCT((MOD(COLUMN(B:H),2)=0)*B2:H2) SUMPRODUCT((MOD(COLUMN(A:J),3)=1)*A2:J2) SUMPRODUCT((MOD(ROW(A2:A49),4)=2)*A2:A49)

对行号除以4余数为1的单元格 sumproduct((mod(row(a1:a100,4)=1)*a1:a100), 求和(中间没文字) 从C4到C18每间隔10行相加(中 SUMPRODUCT((MOD(ROW(C4:C18),10)=4)*1,C4:C18) 间有文字) C2为零值时显示为空 IF(ISERROR(B2/C2),"",B2/C2)

有用的excel函数公式

数值范围取对应数值 $E$4:$E$26出现D5的次数

LOOKUP(C4,{1,2500.01,4000.01,7000.01,22000.01},{"0.05", "0.10","0.15","0.20","0.25"}) COUNTIF($E$4:$E$26,D5)

A1在A1-A18中是否重复，重复 COUNTIF($A$1:$A$18,A1)>1 返回TURE

根据查找结果建立超级链接

HYPERLINK("#清 单!E"&MATCH($B$3,$D$1:$D$16,0),VLOOKUP(B3,D2:J16, 2,0)) 条件格式内输入公式：当MCO(COUNTA($A$2,$A2,2)=1时设 置底纹；当MCO(COUNTA($A$2,$A2,2)=0时设置另一个底纹

设置不同函数底纹

把需要同时打开的文件保存为 同一个工作簿把每个文件分别打开，然后保存为工作区 一个工作区文件 把工作簿文档设置为只读文件 通过设置修改权限密码实现 检查唯一数值是否重复 为数字自动添加单位 通过数据有效性功能实现

把符合条件的数据挑选出来并 利用“高级筛选”实现 保存到其他区域

保存文档过程中设置密码

保存\工具\常规选项

利用F4键快速切换“相对引用 选中单

元格，涂选公式区域，按F4键切换 ”、“绝对引用”

有用的excel函数公式

公式说明“MATCH($A2,$A$2:$A$18,)”:A2在A2至A18出现次 数；“ROW()”:行号

公式备注

“MATCH”:返回指定数值在指定数组区域中的位置； “ROW()”:返回引用的行号 “DATEDIF”:计算两个日期之间的天数、月数、年数 。“Y”为时间段中的整年数；“M”为时间段中的整月 “DATEDIF($A2-7,TODAY(),"YD")”:A2日期减7天与当前 数；“D”为为时间段中的天数；“MD”为时间段中的天 日期间隔天数 数的差，可忽略日期中的月和年；“YM”为时间段中的 月数的差，可忽略日期中的日和年；“YD”为时间段中 的天数的差，可忽略日期中的年； “SUMPRODUCT”:在给定的几组数组中，将数组间 对应的元素相乘，并返回乘积之和。

"女")*D2:D10) “LARGE”:返回数据集中的第K个最大值 "MATCH(A15,A2:A11,0)":定位A15在A2:A11中的位 置； 工作表分别为“A组”、“B组”、“C组”、“D组”、 “E组” OFFSET函数的功能为以指定的引用为参照系，通过 给定偏移量得到新的引用

“{1,2,3,4,5}”:表现从五个工作表(A、B、C、D、E)选数 “CHOOSE”:从参数列表中选择并返回一个值

MATCH($B$3,$E$2:$E$7,0):B3在E2:E7的位置；

VLOOKUP:横向查找函数，VLOOKUP是按列查找 的，HLOOKUP是按行查找的 INDEX:返回数组中指定的单元格或单元格数组的数 值。 LOOKUP返回向量或数组中的数值

ROW:返回引用的行号；mod函数是一个求余函数，其 格式为： mod(nExp1,nExp2),即是两个数值表达式作 除法运算后的余数。

IsError 函数 返回 Boolean 值，指出表达式是否为一 个错误值

"女