Excel2007函数公式实例集
Office 2007 常用函数
=HYPERLINK(link_location，friendly_name)
创建快捷方式，以便打开文档或网络驱动器或连接Internet
COUNT
=COUNT（value1，value2……）
计算参数表中的数字参数和包含数字的单元格的个数
MAX
= MAX（number1，number2，……）
返回一组参数的最大值，忽略逻辑值及文本字符
SIN
=SIN（number）
返回给定角度的正弦值
SUMIF
=SUMIF（range，criteria，sum_range）
根据指定条件对若干单元格求和
PMT
=PMT（tate，nper，pv，fv，type）
返回在固定利率下，投资或贷款的等额分期偿还额
STDEV
=STDEV(number1，number2，……）
Office 2007
在Excel提供的众多函数中，只有一小部分是经常用到的。在【插入函数】对话框中，单击【或选择类别】下拉按钮，选择【常用函数】类别，即可在【选择函数】列表框中显示这些函数，如表7-9所示为常用函数的格式及功能。
表7-9常用函数格式及功能
函数
格式
功能
SUM
=SUM（number1，number2，……）
返回单元格区域中所有数字的和
AVERAGE
=AVERAGE（number1，number2，……）
计算所有参数的算术平均值
IF
=IF（logical_test，value_if_true，value_if_falst）
执行真假值判断，根据对指定条件进行逻辑评价的真假，而返回不同结果
HYPERLINK
Excel2007函数大全 excel公式计算
excel公式计算 Excel2007函数大全一、函数应用基础 1.函数和公式 (1)什么是函数 Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。
以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。
其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。
函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。
使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。
按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。
前者只要启动了Excel，用户就可以使用它们；而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。
(2)什么是公式函数与公式既有区别又互相联系。
如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。
以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。
上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。
2.函数的参数函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。
参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。
参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。
Excel2007常用函数
Excel2007常用函数Excel2007常用函数的使用认识公式各种运算符的含义及示例运算符及含类别含义示例义+(加号) 加 1+2–(减号) 减 2–1–(负号) 负数–1 算术 *(星号) 乘 2*3/(斜杠) 除 4/2%(百分比) 百分比 10%(乘方) 乘幂 3?2=(等号) 等于 A1=A2>(大于号) 大于 A1>A2<(小于号) 小于 A1<A2>=(大于等于比较大于等于 A1>=A2 号)<=(小于等于小于等于 A1<=A2 号)<>(不等号) 不等于 A1<>A2将两个文本连接起来产生连续“2009年”&“”(结果为文本 &(连字符) 的文本“”)区域运算符，对两个引用之间A1:D4(引用A1到D4范围内的:(冒号) 包括这两个引用在内的所有单所有单元格) 元格进行引用联合运算符，将多个引用合并SUM(A1:D1,A2:C2)将A1:D2引用 ,(逗号) 为一个引用和A2:C2两个区域合并为一个A1:D 1:B4(引用A1:D1和交集运算符，生成对两个引用(空格) A1:B4两个区域的交集即中共有的单元格的引用 A1:B1)各种运算符的优先级运算符(优先级从高到低) 说明 :(冒号) 区域运算符 (单个空格) 交集运算符，(逗号) 联合运算符–(负号) 负数 %(百分号) 百分比 ?(乘方) 乘幂 *和/ 乘和除 +和–加和减 & 连接两个文本字符串(串连)=、>、<、>=、<=、<> 比较运算符5.2 使用公式操作方法:控制柄CTRL+ENTER5.3 单元格的引用相对引用和绝对引用单元格的引用通常是为了使用某个单元格的公式，而对单元格进行标识的方式。
引用单元格能够通过所标识的单元格来快速获得公式对数据的运算。
跨工作表引用跨工作簿引用5.4 认识函数函数格式,函数名(参数1，参数2，…)序号分类功能简介1 数据库工作表函数分析数据清单中的数值是否符合特定条件2 日期与时间函数在公式中分析和处理日期值和时间值3 工程函数用于工作分析4 信息函数确定存储在单元格中数据的类型5 财务函数进行一般的财务计算6 逻辑函数进行逻辑判断或者进行复合检验7 统计函数对数据区域进行统计分析在数据清单中查找特定数据或者查找一个单元8 查找和引用函数格的引用9 文本函数在公式中处理字符串10 数学和三角函数进行数学计算5.5 使用函数函数定义插入函数5.6 常用函数的应用SUM函数“sum”在英语中表示“总数、总和、求和”的意思，SUM函数是用来计算某一个或多个单元格区域中所有数字的总和的求和函数。
(超全)excel2007函数大汇总
返回特定时间的序列号??? 返回特定时间的序列号???
time
TIME(A3,B3,C3) 一天的小数部分（上面的第二个时 间）(0.700115741)
TIMEVALUE TODAY WEEKDAY
将文本格式的时间转换为序列号 返回今天日期的序列号 将序列号转换为星期日期
timevalue today
将字符串中的半角（单字节）英文字母或片假名
JIS
更改为全角（双字节）字符
JIS(text) LEFT(A2,4) 第一个字符串中的前四个字符 (Sale) LEFT(A3) 第二个字符串中的第一个字符 (S) LEN(A2) 第一个字符串的长度 (11) lower(text) MID(A2,1,5) 上面字符串中的 5 个字符，从第一个字符开始 (Fluid)
CHOOSE(2,A2,A3,A4,A5) 第二个参数 A3 的值 (2nd)
HYPERLINK("/report/budget report.xls", "名字")
INDEX INDIRECT LOOKUP
使用索引从引用或数组中选择值??? 返回由文本值指定的引用 在向量或数组中查找值
WORKDAY √
返回指定的若干个工作日之前或之后的日期的序列号
workday √
日期 说明 2008-10-1 起始日期 151 完成所需天数 2008-11-26 假日 2008-12-4 假日 2009-1-21 假日 公式 说明（结果） =WORKDAY(A2,A3) 从起始日期开始 151 个工作日的 日期 (2009-4-30) =WORKDAY(A2,A3,A4:A6) 从起始日期开始 151 个工 作日的日期，除去假日 (2009-5-5)
office2007函数说明
函数名函数结果
NOW
2013-4-16 10:07
ABS
26.5AVERAGE
40
17547
IF 大于INDEX 35INT -360ROUND -359LEFT 我爱北RIGHT 京天安门
MID 北京LEN 12MAX 99MIN -1MOD 3MONTH 153
SUBTOTAL SUBTOTAL(函数类型,汇总区间)
SU8MIF 15TEXT 75.80%TODAY 2013-4-16
VALUE 4565.8
WEEKDAY
4
DATEDIF
RANK
函数意义函数参数函数用途
此刻时间
绝对值
平均数
月份差2009-8-1用于计算工龄
天数差2011-1-30
如果函数15
定位某区间内中的某单元格的数值35
取整-359.258
取整-359.258
由左边字符串截取我爱北京天安门
由右边字符串截取我爱北京天安门
字符串中任意位置截取10
字符串字符数50
备选数值中最大的70
备选数值中最小的60
两数相除的余数20
指定日期的月份数40
降序排名30
升序排名
分类汇总
15
数字格式转换文本格式
今日日期
文本格式转换数字格式
备注
此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序
向下取整
只要不是0，都表示升序
F9刷新日期。
Excel2007中常用函数的应用
Office Excel提供300多种函数，但在日常工作中我们只会用到其中的一小部分，下面我们来简单的介绍几类日常工作中常用到的函数。
1.逻辑判断函数（IF、AND、OR）IF函数根据对指定的条件计算结果为TRUE或FALSE，返回不同的结果一般常利用IF函数对数值和公式执行条件检测，语法：IF(logical_test，value_if_true，value_if_false)实例：公式“=IF(C2>=85，"A"， "B")”若C2单元格数据>=85返回字母A，如果<85返回字母BAND函数用于对多个判断结果取交集，即取同时满足多个条件的那部分内容。
简单地说就是，当所有的参数都取真，其结果才为真。
语法：AND（logical1，logical2，⋯）实例：如果A1=2、A2=6，那么公式“=AND(A1>1,A2<2)”返回FALSE。
OR函数用于对多个判断条件取并集，即只要参数中有一个值为真就返回TRUE，如果都为假才返回FALSE。
语法：OR（logical1，logical2，⋯）实例：如果A1=2、A2=6，那么公式“=OR(A1>1,A2<2)”返回TRUE。
2.统计函数（COUNT、COUNTA、COUNTIF）COUNT函数的功能是统计含有数值数据的单元格个数；COUNTA函数的功能是统计所有非空值的单元格个数。
两个函数语法为：COUNT(value1，value2，...)；COUNTA (value1，value2，...)实例：如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36，则公式“=COUNT(A1:A5)”返回数值3。
实例：如果A1=6.28、A7=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于2。
COUNTIF函数的功能是统计单元格区域中满足条件的单元格个数，语法：COUNTIF(range，criteria)其中range为单元格区域， criteria表示统计条件，可以为数字、表达式或文本。
EXCEL2007常用函数
一、数字处理1、取绝对值=ABS(数字)2、取整=INT(数字)3、四舍五入=ROUND(数字,小数位数)二、判断公式1、把公式产生的错误值显示为空公式：C2=IFERROR(A2/B2,"")说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值公式：C2=IF(AND(A2<500,B2="未到期"),"补款","")说明：两个条件同时成立用AND,任一个成立用OR函数。
三、统计公式1、统计两个表格重复的内容公式:B2=COUNTIF(Sheet15!A:A,A2)说明：如果返回值大于0说明在另一个表中存在，0则不存在。
2、统计不重复的总人数公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
四、求和公式1、隔列求和公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式2、单条件求和公式：F2=SUMIF(A:A,E2,C:C)说明：SUMIF函数的基本用法3、单条件模糊求和公式：详见下图说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。
4、多条件模糊求和公式：C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)说明：在sumifs中可以使用通配符*5、多表相同位置求和公式：b2=SUM(Sheet1:Sheet19!B2)说明：在表中间删除或添加表后，公式结果会自动更新。
6、按日期和产品求和公式：F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)说明：SUMPRODUCT可以完成多条件求和五、查找与引用公式1、单条件查找公式公式1：C11=VLOOKUP(B11,B3:F7,4,FALSE)说明：查找是VLOOKUP最擅长的，基本用法2、双向查找公式公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))说明：利用MATCH函数查找位置，用INDEX函数取值3、查找最后一条符合条件的记录。
EXCEL函数公式实例大全
EXCEL函数公式实例大全1.SUM函数（求和）用途：对指定单元格范围内的数值进行求和。
语法：=SUM(number1,[number2],...)实例：=SUM(A1:A10)//求A1到A10的和2.AVERAGE函数（平均值）用途：计算指定单元格范围内数值的平均值。
语法：=AVERAGE(number1,[number2],...)实例：=AVERAGE(A1:A10)//求A1到A10的平均值3.COUNT函数（计数）用途：统计指定单元格范围内的数值个数。
语法：=COUNT(value1,[value2],...)实例：=COUNT(A1:A10)//统计A1到A10的数值个数4.MAX函数（最大值）用途：求指定单元格范围内数值的最大值。
语法：=MAX(number1,[number2],...)实例：=MAX(A1:A10)//求A1到A10的最大值5.MIN函数（最小值）用途：求指定单元格范围内数值的最小值。
语法：=MIN(number1,[number2],...)实例：=MIN(A1:A10)//求A1到A10的最小值6.CONCATENATE函数（合并文本）用途：将多个文本值合并为一个值。
语法：=CONCATENATE(text1,[text2],...)实例：=CONCATENATE("Hello"," ","World") // 合并为"Hello World"7.LEFT函数（左截取）用途：从文本字符串的左侧截取指定长度的字符。
语法：=LEFT(text,num_chars)实例：=LEFT(A1,3)//从A1单元格的文本左侧截取3个字符8.RIGHT函数（右截取）用途：从文本字符串的右侧截取指定长度的字符。
语法：=RIGHT(text,num_chars)实例：=RIGHT(A1,3)//从A1单元格的文本右侧截取3个字符9.LEN函数（字符长度）用途：计算文本字符串中的字符个数。
excel2007函数与公式使用教程
excel2007函数与公式使用教程在运用Excel中，不多不少会需要用到函数与公式进行对数据的计算并统计，现在请阅读学习来小编整理的一些excel2007函数与公式使用教程的内容。
excel2007函数与公式使用教程1：AVERAGE 求平均值函数计算一组数据的平均值数据选择一个单元，用来显示平均值数据在fx公式输入栏里边输入：=AVERAGE(D2:D4)其中D2 D4 为要计算平均的数据范围。
如下图所示，回车显示平均值数据excel2007函数与公式使用教程2：MAX 求最大值函数计算一组数据的最大值选择一个单元，用来显示最大值数据在fx公式输入栏里边输入：=MAX(D2:D4)其中D2 D4 为要计算平均的数据范围。
如下图所示，回车显示最大值数据excel2007函数与公式使用教程3：MIN 求最小值计算一组数据的最小值选择一个单元，用来计算最小值在fx公式输入栏里边输入：=MIN(D2:D4)其中D2 D4 为要计算平均的数据范围。
如下图所示，回车显示平均值数据excel2007函数与公式使用教程4：IF IF比较函数对数据进行比较分组下面我们来根据每小时的pm25数据，计算pm25等级，就要用到IF函数在K列中选中K2单元格点击公式栏目中的Fx 字样，打开公式选择器选中列表中的IF函数，点击确定excel2007函数与公式使用教程5：IF IF比较函数在logical_test 中输入比较条件 F2>200value_if_ture 输入当f2>200时候显示的结果value_if_false 输入单f2<=200 时候显示结果选中K2单元格，使其变成粗黑色边框，对中右下方的黑点，单击下拉，把公式应用到所有的行中。
excel函数教程2007
excel函数教程2007Excel是一款功能强大的电子表格软件，广泛应用于商业、金融、教育等各个领域。
Excel提供了丰富的函数，可以帮助用户处理数据、进行复杂的计算和分析。
本教程将介绍Excel 2007中一些常用的函数及其使用方法。
1. SUM函数：用于计算一组数值的总和。
例如，=SUM(A1:A10)可以计算A1到A10这10个单元格的数值之和。
2. AVERAGE函数：用于计算一组数值的平均值。
例如，=AVERAGE(A1:A10)可以计算A1到A10这10个单元格的平均值。
3. MAX函数：用于找出一组数值中的最大值。
例如，=MAX(A1:A10)可以找出A1到A10这10个单元格的最大值。
4. MIN函数：用于找出一组数值中的最小值。
例如，=MIN(A1:A10)可以找出A1到A10这10个单元格的最小值。
5. COUNT函数：用于计算一组数值中非空单元格的个数。
例如，=COUNT(A1:A10)可以计算A1到A10中非空单元格的个数。
6. IF函数：用于根据条件判断返回相应的值。
例如，=IF(A1>10, "大于10", "小于等于10")表示如果A1大于10，则返回"大于10"，否则返回"小于等于10"。
7. VLOOKUP函数：用于在一个范围内查找某个值，并返回与之对应的值。
例如，=VLOOKUP(A1, B1:C10, 2, FALSE)表示在B1到C10范围内查找A1的值，并返回与之对应的第二列的值。
8. SUMIF函数：用于根据指定条件来计算一组数值之和。
例如，=SUMIF(A1:A10, ">10")可以计算A1到A10中大于10的数值之和。
9. COUNTIF函数：用于根据指定条件来计算一组数值中满足条件的单元格个数。
例如，=COUNTIF(A1:A10, ">10")可以计算A1到A10中大于10的单元格的个数。
EXCEL常用函数公式大全及举例
EXCEL常用函数公式大全及举例1.SUM函数：用于计算一组数字或单元格的总和。
例子：=SUM(A1:A10)表示计算A1到A10这些单元格的总和。
2.AVERAGE函数：用于计算一组数字或单元格的平均值。
例子：=AVERAGE(A1:A10)表示计算A1到A10这些单元格的平均值。
3.COUNT函数：用于计算一组数字或单元格中的非空单元格的数量。
例子：=COUNT(A1:A10)表示计算A1到A10这些单元格中非空单元格的数量。
4.MAX函数：用于计算一组数字或单元格中的最大值。
例子：=MAX(A1:A10)表示计算A1到A10这些单元格中的最大值。
5.MIN函数：用于计算一组数字或单元格中的最小值。
例子：=MIN(A1:A10)表示计算A1到A10这些单元格中的最小值。
6.COUNTIF函数：用于计算一组数字或单元格中满足指定条件的单元格的数量。
例子：=COUNTIF(A1:A10,">50")表示计算A1到A10这些单元格中大于50的单元格的数量。
7.SUMIF函数：用于计算一组数字或单元格中满足指定条件的单元格的总和。
例子：=SUMIF(A1:A10,">50")表示计算A1到A10这些单元格中大于50的单元格的总和。
8.AVERAGEIF函数：用于计算一组数字或单元格中满足指定条件的单元格的平均值。
例子：=AVERAGEIF(A1:A10,">50")表示计算A1到A10这些单元格中大于50的单元格的平均值。
9.VLOOKUP函数：用于在一个区域中查找一些值，并返回对应的值。
例子：=VLOOKUP(A1,B1:C10,2,FALSE)表示在B1到C10这个区域中查找A1的值，并返回相对应的第二列的值。
10.HLOOKUP函数：用于在一个区域中查找一些值，并返回对应的值。
例子：=HLOOKUP(A1,B1:C10,2,FALSE)表示在B1到C10这个区域中查找A1的值，并返回相对应的第二行的值。
Excel2007函数范例(最实用最全)
第五年的收益 净现值NPV 现值指数
7900 ¥5,009.48 141.69%
8300 ¥5,667.18 147.16%
7000 ¥3,811.24 131.72%
7500 ¥5,118.37 142.60%
复利现值 投资报酬率 8% 投资年限 5 复利现值 1000000.00 投资现值 ¥-680,583.20 复利终值 利率 期数 本金 5年后的本利和 5% 5 -10000.00 ¥12,762.82
即付年金现值 月利率 10％/12 付款期限 10*12 月初付款 -1500.00 目前银行存款数 ¥114,452.63 即付年金终值 月利率 12%/12 期数 5*12 月初存款 -1000.00 5年后的年金终值 ¥82,486.37 本金函数(PPMT) 本金支付额 年利率 10% 贷款期限 8 贷款金额 120000 第10个月支付的本金 ¥-877.25 折旧值(DB) 电脑原值 8500 资产残值 1000.00 使用寿命 6 折旧值 2 使用期第二年的折旧值 ¥1,785.00
数 公式
利息函数(IPMT) 利息支付额 年利率 10% 贷款期限 8 贷款金额 120000 第10个月支付的利息 ¥-928.60
选择方案
方案A
内部报酬率IRR 方案 20.67% 方案A 19.67% 方案B 17.68% 方案C 20.10% 方案D
选择方案
方案B
净现值NPV 5009.48034 5667.182784 3811.23766 5118.367361
年金现值函数(PV) 普通年金现值 年利率 10% 付款期限 10 年末付款 -10000.00 目前银行存款数 ¥61,445.67 年金终值函数(FV) 普通年金终值 年利率 12% 期数 5 年末存款 -10000.00 5年后的年金终值 ¥63,528.47 等额还款函数(PMT) 年末等额还款 年利率 10% 贷款期限 8 贷款金额 120000 每年还款额 ¥-22,493.28 每月月初还款 ¥-1,805.85 利率函数(RATE) 利率 月初支付 -100.00 贷款期限 8 贷款金额 5000.00 实际利率 2% 内部报酬率(IRR)
EXCEL2007实战技巧(公式与函数)
根据函数的功能和应用领域，内置函数可分为11个类别
文本函数
信息函数
逻辑函数
查找和引用函数
日期和时间函数
统计函数
数学和三角函数
数据库函数
财务函数
工程函数
多维数据集函数
1.5函数
1.5.5函数的输入和编辑
1.5函数
1.5.6使用公式的常见问题
表1-6
错误值类型 ###### #VALUE #DIV/0！ #NAME？
1.3公式中的常量
1.3.1常量参数
公式中的5种常量 a) 数值常量，如：=（5+8）*3。 b) 日期常量，如：=DATEDIF（“2012-1-1”,NOW（），“m”）。 c) 文本常量，如：=“国际”&“复合”。 d) 逻辑值常量，如：=VLOOKUP（“张三丰”，A:B，2，FALSE）。 e) 错误值常量，如：=COUNTIF（A:A，#DIV/0!）
Content
目 录
1 公式和函数基础 2 文本处理 3 数学计算 4 日期和时间计算 5 查找与引用 6 统计与求和
1. 公式和函数基础
1.1认识公式 1.2公式中的运算符 1.3公式中的常量 1.4单元格引用 1.5函数
1.1认识公式
1.1.1公式的概念 1.1.2公式的组成要素
表1-1 序号 1 2 3
4.4时间的计算
TIME函数：用于按指定数字生成具体时间 HOUR函数：用于提取时间系列值中的小时数 MINUTE函数：用于提取时间系列值中的分钟数 SECOND函数：用于提取时间系列值中的秒数
5.查找与引用
5.1基本的查找函数 5.2常用的定位函数 5.3行号和列号函数 5.4特殊的查找函数
Excel2007函数表图解(二)
076 返回商的整数部分
功能 说明 实例
Quotient（被除数A，除数B）
舍去小数部分
返回商的整数部分，即舍去商的小数部分，注意是舍去不是四舍五入
如果A1=86、A2=9，则公式“=QUOTIENT(A1，A2)”返回9;=QUOTIENT(-10，3)返回–3。
被除数 A 除数 B
功能 数值A 样式T 说明
M1
A1 0 A2 将阿拉伯数字转换成文本形式的罗马数字
为需要转换的阿拉伯数字
Roman（数值A，样式T）
M2
样式？
为一个数字，它指定要转换的罗马数字样式。可以从经典到简化，随着form值的增加趋于简单 。 T为1-4
088 返回数值符号
功能
Sign（数值A）
返回数值对应的符号 1 表示正数 表示零 表示负数
M1 Round(A,N) 四舍五入 A1
0
A2
M2
负数
正数 -5 -4 0 4 5
负数
+
正数
无条件舍位 RoundDown(A,N)
负数
A1
M1
0
M2
A2
+
正数
无条件进位 RoundUp(A,N)
092 平方根
功能 说明
M2
A1
0
A2
M1
+
Sqrt（数值A）
最小公倍数:Lcm (A1,„,A29)
备注
094 分类汇总
Abs(A) 2
负数
3 4 A1 5 6 7 8 9 10 11
正数
方式T
0
MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP
第12讲 Excel 2007的公式使用(二)
12.1
Excel 2007常用函数的使用
12.2
Excel 2007标题地址类型的使用
12.1 Excel 2007常用函数的使用
Hale Waihona Puke 12.1.1 知识点讲解 12.1.2 范例解析 12.1.3 课堂练习
12.1.1 知识点讲解
一、SUM函数 SUM函数用来将各参数累加求它们的和。参数可以是数值常量，也 可以是单元格地址，还可以是单元格区域引用。下面是SUM函数的 例子。 SUM(1,2,3)：计算1+2+3的值，结果为6。 SUM(A1,A2,A3)：求A1、A2和A3单元格中数的和。 SUM(A1:F4)：求A1:F4单元格区域中数的和。 二、AVERAGE函数 AVERAGE函数用来求参数中数值的平均值，其参数要求与SUM函 数的一样。下面是AVERAGE函数的例子。 AVERAGE(1,2,3)：求1、2和3的平均值，结果为2。 AVERAGE(A1,A2,A3)：求A1、A2和A3单元格中数的平均值。 AVERAGE (A1:F4)：求A1:F4单元格区域中数的平均值。
12.1.1 知识点讲解
七、RIGHT函数 RIGHT函数用来取文本数据右面的若干个字符，其参数要求与 LEFT函数相同。下面是RIGHT函数的例子。 RIGHT("Excel 2007",3)：取"Excel 2007"右边的3个字符，结果为 "007"。 RIGHT("计算机",2)：取"计算机"右边的2个字符，结果为"算机"。 八、IF函数 IF函数检查第1个参数的值是真还是假，如果是真，则返回第2个参 数的值，如果是假，则返回第3个参数的值。此函数包含3个参数： 要检查的条件、当条件为真时的返回值和条件为假时的返回值。下 面是IF函数的例子。 IF（1+1=2, “天才”, “奇才”）：因为“1+1=2”为真，所以，结果为 “天才”。
Excel2007函数公式实例集
Excel2007函数公式实例集Excel公式2010-01-28 13:02:00 阅读4830 评论7 字号：大中小订阅Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。
公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。
对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)用SUM函数计数:{=SUM((B2:B9="男")*1)}求1累加到100之和:{=SUM(ROW(1:100))}多个工作表不同区域求前三名产量和:{=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)))}计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2: B10,4))对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵","刘","李"}&"*",C2:C10))汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">60 0")只汇总60～80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班")汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女")计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})计算参保人数:=SUMPRODUCT((C2:C11="是")*1)求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1) 汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11)汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1)汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11)根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,"非直角","直角")计算1到10的自然数的积:=FACT(10)计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)计算每小时生产产值:=PRODUCT(C2:E2)根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5 根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2 跨表求积:=PRODUCT(产量表:单价表!B2)求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1; 1;1}))计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))} 求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A 11={"A","B","C","D"})*1))}计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)} 计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)} 计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2: C17-D2:D17))}求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))} 求几号选手选票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)* 100+B2:B10))}总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}在不同班级有同名前提下计算学生人数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2: B17&C2:C17)),ROW(2:17)^0))}计算前进中学参赛人数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE( A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))} 串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1)) 计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:1 1)}计算成绩在60-80分之间合计数与个数:求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)} 汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}计算象棋比赛对局次数l:=COMBIN(B1,B2)计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")计算最大公约数:=GCD(B1:B5)计算最小公倍数:=LCM(B1:B5)计算余数:=MOD(A2,B2)汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0) *B2:J2)设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""))根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10) 汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))), 60^(3-COLUMN(A:A)))生成隔行累加的序列:=QUOTIENT(ROW()+1,2)根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2 计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}计算个人所得税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,8000 0,100000}+ABS(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,1000 00}))/2,0)产生100到200之间带小数的随机数:=RAND()*(200-100)+100产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"&OFFSET(B$1,RANK(G2,G$2: G$11),)&":"&OFFSET(C$1,RANK(G2,G$2:G$11),)产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)产生1到100之问的奇数随机数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBET WEEN(1,100))}产生1到10之间随机不重复数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBE TWEEN(1,12-ROW()))}根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是")计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))} 计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1))) 统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))统计参考人数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(R OW(A1:J12),3)=1)*(A1:J12<>""))计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06, TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2))根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5) 将金额见角进元与见分进元:见分进元=CEILING(TRUNC(A2,2),1);见角进元=CEILING(TRUNC(A2,1),1)分别统计收支金额并忽略小数:收入合计=SUMPRODUCT(INT(B2:B8));支出合计=SUMPRODUCT(TRUNC(C2:C8))成绩表的格式转换:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成绩=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1) 隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"")INT函数在序列中的复杂运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9) ;=INT((ROW(A2))*2/3)统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0)成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1, 5)+1)计算机上网费用:=CEILING(B2,30)/30*2统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)统计业务员提成金额，不足20000元忽略:=FLOOR(B2,20000)/20000*500 FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10))) 将数据转换成接近6的倍数:=MROUND(A1,6)以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50} 将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)对单价计量单位不同的品名汇总金额:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="G",1000,1),(D2:D10="G")*2)) }将金额保留“角”位，忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})} 计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))} 计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))}按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,{"平均成绩","科目数量","最高成绩","最低成绩","成绩合计";1,2,4,5,9},2,0),B2:D2) 不间断的序号:=SUBTOTAL(103,$B$2:B2)仅对筛选出的人员排名次:{=CONCATENATE("第",SUM(N(IF((SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名")}判断两列数据是否相等:计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))}计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}提取A产品最后单价:{=INDEX(C:C,MAX((B2:B10="A")*ROW(2:10)))} 判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>"汉族")所有裁判都给“通过”就进入决赛:{=AND(B2:E2="通过")}判断身份证长度是否正确:=OR(LEN(B2)={15,18})判断歌手是否被淘汰:{=OR(B2:E2="不通过")}根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部")*3))没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2="不通过"))}计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,"及格","不及格")根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好",IF(AVERAGE(B2:D2)<100,"优秀","满分"))) 根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))}根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))} 既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3))) 分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1<0,SUBSTITUTE(B2:B13,"负","-")*1))}将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"",LARGE(B$2:B$11,ROW(A1)))} 排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"",ROW($1:$11),ROWS($1:$11) +1),ROW()),COLUMN(B2))&""}有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))}混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="K",1000,1),2))} 计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修机",""),"换原料","")*1)}计算最大数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}找出谁夺冠次数最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12, B2:B12),ROW(2:12))))}将全角字符转换为半角:=ASC(A2)计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2))将半角字符转换成全角显示:=WIDECHAR(A2)计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))判断单元格首字符是否为字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)< 123))计算单元格中数字个数:{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE( MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<58))}计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>64)* (CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))<91))} 产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32)产生大写字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1)) &IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"")产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHA R(RANDBETWEEN(65,90))用公式产生换行符:=A2&CHAR(10)&B2将数字转换成英文字符:字符码=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)将字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),"年",MID(B2,11-2*(LEN(B2)=15),2),"月",MID(B2,13-2*(LEN(B2)=15),2),"日") 计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),":",IF(AVERAGE(B2:D2)>=60,"","不"),"及格")提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),"|",LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2)),A2:A11),"|",(LOOKUP(0,0/(B2:B11= LARGE(B2:B11,3)),A2:A11)))将单词转换成首字母大写:=PROPER(A2)将所有单词转换成小写形式:=LOWER(A2)将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1))) 将所有字母转换成大写形式:=UPPER(A2)计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))), 1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))}计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))) ,1)),MID(PROPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))))}将文本型数字转换成数值:{=SUM(VALUE(B2:B10))}计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)))) 提取混合字符串中的数字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&123 4567890)),ROW(INDIRECT("1:"&LEN(A2))))),0))}串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”"))根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女") 将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}将货款显示为“万元”为单位:=TEXT(B2,"￥#"&""""&"."&""""&"#,万元")根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日")显示今天的英文日期及星期:="资料日期："&TEXT(TODAY(),"dddd, mmmm dd, yyyy")显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午")统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))}将数据显示为小数点对齐:=TEXT(B2,"#.0")计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")将日期显示为中文大写:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日")将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),"[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;"),IF(B2>0,,"负")&TEXT(INT(ABS(B2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(B2)<>0,,"零")),"零分",""))判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")计算达成率，以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;") 计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第"&SEARCH("a",A2&"a")&"个")从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1)将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)} 计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;")计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)}计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DA Y(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))}检验日期是否升序排列:=TEXT(N(A3>=A2),";;日期有误;")判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A 3),A3),"[="&CODE(A3)&"]字母;;数字;汉字")计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))} 将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"？","@@@@@"),"？","") 将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00")根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)), "#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(L EN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟")将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000")在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000将数据对齐显示，将空白以“．”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)利用公式制作简易图表:=IF(B2>0,REPT("",5)&"｜"&REPT("■",ABS(B2))&B2&REPT("",5-ABS(B2)),REPT("",5-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&"｜"&REPT("",5)) 利用公式制作带轴的图表且标示升降:{=IF(A2<>"",A2&"┫","")&IF(A2="",REPT("〓",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT("",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&REPT("■",A BS(B2))&B2&REPT("",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT("",4+MAX(ABS(B$2:B$8))-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&IF( ROW()=1,"",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓"))&REPT(" ",4+MAX(ABS(B$2:B$8))))))}计算单元格中数字个数:=LEN(A2)*2-LENB(A2)将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*10^(ROW(INDI RECT("1:"&LEN(A2)))-1)),REPT(0,LEN(A2)))}计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1 将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1)) 分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,"/",REPT("",100)),COLUMN(A2)*100-99,100))提取英文名字:=LEFT(A2,FIND(" ",A2)-1)将分数转换成小数:=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2 从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND("~",SUBSTITUTE(" "&$A2&" ","","~",COLUMN(A2))),FIND("~",SUBSTITUTE(" "&$A2&" ","","~",COLUMN(B2)))-FIND("~",SUBSTITUTE(" "&$A2&" ","","~",COLUMN(A2)))),"")将单位为“双”与“片”混合的数量汇总:{=SUM(IF(ISNUMBER(FIND("/",C2:C9)),(LEFT(C2:C9,FIND("/",C2:C9)-1)+ RIGHT(C2:C9,LEN(C2:C9)-FIND("/",C2:C9)))/2,C2:C9*IF(B2:B9="片",0.5,1)))} 提取工作表名:=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filenam e")))根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2 ,FIND("*",B2)+1)-1-FIND("*",B2)),RIGHT(B2,LEN(B2)-FIND("*",B2,FIND("*",B 2)+1)))提取括号中的字符串:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"") 分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"（","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,"）","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2,"（","@",COLUMN(A1)))-1)提取学校与医院地址:{=IF(OR(IFERROR(FIND({"学校","医院"},A2),FALSE)),A2,"")}计算密码字符串中字符个数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2), FIND(ROW(1:10)-1,A2))}通讯录单列转三列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE ),ROW($1:$15),100000),ROW(A1))),LEN(C$1)+1,100)}将15位身份证号码升级为18位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&MID("10X98765432",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-RO W(INDIRECT("1:17")))),11)+1,1))}将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00! :00 00-00")),"hmm/dd/mm")}分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$ 1,$A$1&$A2)+1,)将年级或者专业与班级名称分开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)), 10,)}提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEA RCH("(",A2),)),1,)店名分类:=IF(COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2))=1,"餐饮娱乐",IF(COUNT(SEARCH({"干洗","医院","药","茶","蛋糕","面包","物流","驾校","开锁","家政","装饰","搬家","维修","中介","卫生","旅馆"},A2))=1,"便民服务",IF(COUNT(SEARCH({"游乐场","旅行社","旅游"},A2))=1,"旅游")))查找编号中重复出现的数字:重复数字个数{=COUNT(SEARCH((ROW($1:$10)-1)&"*"&(ROW($1:$10)-1),A2))};重复字符=IF(COUNT(SEARCH("0*0",A2)),0,"")&SUBSTITUTE(SUMPRODUCT(ISNU MBER(SEARCH(ROW($1:$9)&"*"&ROW($1:$9),A2))*ROW($1:$9)*10^(9-RO W($1:$9))),0,)统计名为“刘星”者人数:{=COUNT(SEARCH("?刘星",A2:A9))}剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重庆市",A2)),"","四川省"),"")将日期规范化再求差:=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-") 提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,"*",REPT(" ",50)),FIND("*",B2),100))产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,"：","("),"*",")*")&")"判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,"B",""))<>LEN(B2)提取姓名与省份:=TRIM(MID(A2,1,FIND("|",A2)-1)&MID(SUBSTITUTE(A2,"|",REPT("",100)),500,100))将IP地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("."&A2,".0","."),".0","."),".","",1 )提取最后一次短跑成绩:=REPLACE(A2,1,FIND("々",SUBSTITUTE(A2,"|","々",LEN(A2)-LEN(SUBSTITUTE(A2,"|",)))),)从地址中提取省名:=LEFT(A2,FIND("省",A2))计算小学参赛者人数:{=COUNT(0/(LEFT(B2:B11)="小"))}计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)="四川"),N(B2:B11="飞机"),C2:C11)通过身份证号码计算年龄:=TEXT(TODAY(),"YYYY")-(IF(LEN(B2)=18,"",19)&LEFT(REPLACE(B2,1,6, ""),2+(LEN(B2)=18)*2))从混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2 将金额分散填充:=LEFT(RIGHT(" ￥"&$A2*100,13-COLUMN()))提取成绩并计算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}提取参赛选手姓名:=MID(A2,FIND("：",A2)+1,LEN(A2))从混合字符串中提取金额:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),R OW($1:$99)))从卡机数据提取打卡时间:=730>--MID(A2,14,4)根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部") 根据身份证号码统计男性人数:{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))} 从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))} 将字符串位数统一:{=TEXT(RIGHT(A2,LEN(A2)-1),"!"&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$1 0))-1))}对所有人员按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3)) }取金额的角位与分位叫:=--RIGHT(ROUND(A2*100,),2)从格式不规范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,"."," ",2),3))计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2)计算90分以上的平均成绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)} 计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),D2:D10))}计算各业务员的平均奖金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)计算每人平均出口量:{=AVERAGEA((C2:C11="A")*D2:D11)}计算平均成绩，成绩空白也计算:{=AVERAGEA(B2:B11*1)}计算二年级所有人员的平均获奖率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)="二年级",B2:B10/C2:C10)),"0.00%")}统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,{1,2,3}))求每季度平均支出金额:=AVERAGEIF(B2:B9,"支出",C2)计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B1 1))生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")计算生产车间异常机台个数:=COUNT(C2:C11)计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")} 统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2:A11))}统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))} 统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}统计出勤异常人数:=COUNTA(B2:B11)判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"没有","有") 统计未检验完成的产品数:=COUNTBLANK(B2:B11)统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00") 根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,"*中学")计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}统计连续三次进入前十名的人数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))} 统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"诺基亚","摩托罗拉","联想"}&"*")) 统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{"诺基亚*","*联想*"})*{1,-1})}统计冠军榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B $12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(B$2:B$12,B$2:B$12) *((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A 1)))}统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,"=")对名册表进行混合编号:=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0"))提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1} 统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000")统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"})) 计算A列最后一个非空单元格行号:{=MAX((A:A<>"")*ROW(A:A))}计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1) }计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,)) 统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10="第一名")<>(B3:B11="第一名"))*ROW(2:10)))}低于平均成绩中的最优成绩:{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))}计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)计算数学成绩等于100分的男生最高总成绩:=DMAX(A1:E11,"总分",B1:B2) 根据下拉列表计算不同项目的最大值:=DMAX(A1:E11,G4,G1:G2)计算中间成绩:=MEDIAN(B2:B11)显示动态日期，但不能超过9月30日:=MIN("2008-9-30",TODAY())根据工作时间计算可休假天数:=MIN(SUM((B2={"A","B","C"})*{5,4,3})+(C2-1),10)确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,)计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("（?具类",A2:A11)),B2:B11)),"0.00%")}。
2007excel求和公式
2007Excel求和公式的理解和应用在日常工作中，Microsoft Excel是处理数据的常用工具之一。
它拥有强大的功能，包括计算、排序、过滤等。
其中，求和公式是最基础也是最常用的函数之一。
本文将详细解析Excel 2007中的求和公式，并提供一些实用的应用场景。
一、Excel 2007求和公式的使用方法在Excel中，求和公式非常简单，只需要输入“=SUM( )”，然后在括号内填入需要求和的单元格即可。
例如，如果我们要计算A1到A10这十个单元格的和，只需输入“=SUM(A1:A10)”。
按下回车键后，结果就会出现在当前单元格中。
二、Excel 2007求和公式的高级用法1. 非连续区域求和：有时候，我们需要对不连续的多个区域进行求和，这时可以在括号内同时填入这些区域。
例如，“=SUM(A1:A10,C1:C10)”会计算A1到A10以及C1到C10这两个区域的总和。
2. 使用名称定义求和：如果你经常需要对某个特定区域求和，可以先为这个区域定义一个名字，然后在求和公式中直接使用这个名字。
这样可以提高效率，也更易于理解。
定义名字的方法是在“公式”选项卡中选择“定义名称”，然后在弹出的对话框中填写名字和对应的区域。
3. 利用绝对引用：在复杂的表格中，我们可能需要在移动或复制公式时保持某个区域不变。
这时可以使用绝对引用，即在单元格前加上"$"符号。
例如，“=$A$1:$A$10”表示无论公式如何移动或复制，始终是对A1到A10这一区域求和。
三、Excel 2007求和公式的应用场景1. 财务统计：在财务管理中，求和公式是非常重要的工具。
例如，我们可以使用它来计算每个月的总收入，或者每个季度的总支出。
2. 销售分析：在销售数据分析中，求和公式可以帮助我们快速计算出某段时间内的总销售额，或者某种产品的总销量。
3. 教育评估：在学校教育中，教师可以使用求和公式来计算学生的总成绩，或者班级的平均分。
EXCEL2007公式函数
│ 6.5.3.xlsx
│ 6.5.4.xlsx
│ 6.5.5.xlsx
│ 6.6.1.xlsx
│ 6.6.2.xlsx
│ 6.6.3.xlsx
│ 8.3.1.xlsx
│ 8.3.2.xlsx
│ 8.3.3.xlsx
│ 8.3.4.xlsx
│ 8.3.5.xlsx
│ 8.4.2.xlsx
│ 8.5.3.xlsx
│ │ Cursor Asset.x32
│ │ Cursor Options.x32
│ │ FileIo.x32
│ │ Fireworks Import.x32
│ │ Font Asset Dialog.x32
│ │ Font Asset.x32
卷 Excel 2007 的文件夹 PATH 列表
卷序列号为 A7C9-7905
I:\
│ Autorun.exe
│ Autorun.inf
│ 配套视频教程下载.url
│
├─Xtras
│ │ Windows Media Asset.x32
│ │ budapi.x32
│ │ intro.htm
│ │ spring.htm
│ │ stagecopy.jpg
│ │ throughlingo.htm
│ │ usingw3d.htm
│ │ w3dhke.htm
│ 7.5.5.xlsx
│ 7.6.1.xlsx
│ 7.6.2.xlsx
│ 7.7.1.xlsx
│ 7.7.2.xlsx
│ 7.7.3.xlsx
excel2007常用函数的运用技巧
基于条件对数字进行求和SUMIF函数用于对满足条件的单元格进行求和语法：SUMIF(range,criteria,sum_range)参数说明：range：要进行计算的单元格区域，SUMIF函数在该区域中查找满足条件的单元格。
此区域中的空值和文本值将被忽略。
criteria：以数字、表达式或文本形式定义的条件。
sum_range：用于求和计算的实际单元格。
该参数如果省略，将对range区域中满足条件的单元格中的值进行计算。
基于多个条件进行求和通过IF函数和SUM函数的组合使用，可以基于多个条件先进求和。
一般采用以下公式格式：sum(if(条件1*条件2…，求和区域)注：公式中的*为通配符，带表任意数量的字符。
公式输入后，按<Ctrl>+<Shift>+<Enter>组合键将公式转换为数组公式。
求平均值并保留一位小数1、使用AVERAGE函数求平均值语法如下：AVERAGE(number1,number2,…)number它可以是数字或者是包含数字的名称、数组或引用。
2、使用ROUND函数进行四舍五入语法好下：ROUND(number,num_digits)number：表示要进行四舍五入的数字。
num_digits：表示指定的位数，按此位数进行四舍五入。
如果指定的位数大于0，则四舍五入到指定的小数位。
如果指定的位数等于0，则四舍五入到最接近的整数。
如果指定的位数小于0，则在小数左侧按指定位数四舍五入。
指定数值向下取整及返回两个数值相除后的余数1、INT函数（用于将指定数值向下取整为最接近的整数）语法如下：INT(number)number：表示要进行向下舍入取整的数值。
2、MOD函数（用于返回两个数值相除后的余数，其结果的正负号与除数相同。
）语法如下：MOD(number,divisor)number：表示被除数数值。
divisor：表示除数数值，并且该参数不能为0。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel2007函数公式实例集Excel2007函数公式实例集.txt-//自私，让我们只看见自己却容不下别人。
如果发短信给你喜欢的人，他不回，不要再发。
看着你的相片，我就特冲动的想P成黑白挂墙上!有时，不是世界太虚伪，只是，我们太天真。
Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。
公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。
对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)用SUM函数计数:{=SUM((B2:B9="男")*1)}求1累加到100之和:{=SUM(ROW(1:100))}多个工作表不同区域求前三名产量和:{=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)))}计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMAL L(B2:B10,4))对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵"," 刘","李"}&"*",C2:C10))汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FAL SE),">600")只汇总60,80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班")汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女") 计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})计算参保人数:=SUMPRODUCT((C2:C11="是")*1)求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1)汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11) 汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10=" 一车间男是")*1)汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11)根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)计算A1:A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))} 根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1, "非直角","直角")计算1到10的自然数的积:=FACT(10)计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)计算每小时生产产值:=PRODUCT(C2:E2)根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3}) ))^0.5根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2跨表求积:=PRODUCT(产量表:单价表!B2)求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))} 求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A1 1={"A","B","C","D"})*1))}计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))}求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}求几号选手选票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2 :B10))}总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}在不同班级有同名前提下计算学生人数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C 17)),ROW(2:17)^0))}计算前进中学参赛人数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2 :B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))}串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)} 计算成绩在60-80分之间合计数与个数:求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}计算象棋比赛对局次数l:=COMBIN(B1,B2)计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")计算最大公约数:=GCD(B1:B5)计算最小公倍数:=LCM(B1:B5)计算余数:=MOD(A2,B2)汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2) =0)*B2:J2)设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""))根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10) 汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A)))生成隔行累加的序列:=QUOTIENT(ROW()+1,2)根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}计算个人所得税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}+ABS(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}))/2,0) 产生100到200之间带小数的随机数:=RAND()*(200-100)+100产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"&OFFSET(B$1,RANK(G2,G$2:G$11), )&":"&OFFSET(C$1,RANK(G2,G$2:G$11),)产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)产生1到100之问的奇数随机数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}产生1到10之间随机不重复数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN (1,12-ROW()))}根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是")计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))统计参考人数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12 ),3)=1)*(A1:J12<>""))计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2)) 根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5) 根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)。