EXCEL操作技巧培训课件

前言EXCEL操作技巧分享集团人力资源

XXXX年XX月课程目录前言操作与技巧公式与函数数据透视表图表总结123456700010203040506072前言——EXCEL有些什么VBA编程VBA编程透视表透视表函数、图表函数、图表基本操作基本操作3前言——如何成为EXCEL高手No.1积极的心态“兴趣是最好的老师、压力是前进的动力”No.2正确的学习方法循序渐进善用资源，学以致用多阅读多实践No.3持之以恒的努力，每天进步一点点4前言——关于本次培训重点掌握EXCEL基本操作技巧，及解决问题的思路构成理论+案例讲解说明采用Excel2007进行讲解5课程目录前言操作技巧公式与函数数据透视表图表总结12345670001020304050607操作技巧查找与替换条件格式选择性粘贴自动筛选，高级筛选数据有效性批量填充“相同内容”效率加倍的快捷键6操作技巧——查找与替换开始→查找和选择→查找/替换查找：Ctrl+F替换：Ctrl+H查找与替换的高级设置（字体、颜色…)专业技术职称性别民族出生年月文化程度参加工作时间上年绩效等级会计员男汉1978-10本科1996-10A经济师男汉1971-08本科~a助会师女汉1974-08本科1992-11b*女汉1971-05大专1989-12b助经师女汉1962-09大专~b*?男汉??高中1979-10A经济员女蒙古1963-04大专1983-12b助经师男汉1963-09大专1980-11B查找与替换的范围可以选择按工作表或工作薄的查找查找方式，可选择按行或按列查找7操作技巧——条件格式开始→条件格式条件格式常用于自动标记出错误、异常、值得注意的数据根据单元格数值设定条件格式根据公式设定条件格式条件限制选择条件如大于、小于、介于等设置满足条件时显示的格式8操作技巧——选择性粘贴开始→粘贴→选择性粘贴选择性粘贴可根据数据运用的需要实现“部分”粘贴功能9专业技术职称性别民族出生年月文化程度参加工作时间月基本工资经济师男汉1971-08本科1992-12

4,282.00经济员男苗1973-10本科1994-08

2,097.00助经师男汉1963-09大专1980-11

2,340.00

女汉1971-09本科1989-12

4,196.00操作技巧——自动筛选数据→筛选按颜色筛选按文本（数字）筛选专业技术职称性别民族出生年月文化程度参加工作时间月基本工资会计员男汉1978-10本科1996-10

1,966.00经济师男汉1971-08本科1992-12

4,282.00经济员男苗1973-10本科1994-08

2,097.00经济师女汉1955-10高中1979-12

6,428.00助会师女汉1974-08本科1992-11

5,284.00助经师男汉1963-09大专1980-11

2,340.00

女汉1971-09本科1989-12

4,196.00助会师男汉1966-03大专1985-03

8,685.00

女汉1967-01本科1989-12

8,635.00筛选结果10操作技巧——高级筛选数据→筛选→高级筛选筛选结果部门岗位职务专业技术职称性别民族出生年月文化程度参加工作时间月基本工资财务部科员会计员男汉1978-10本科1996-10

1,966.00科技部总经理经济师男汉1971-08本科1992-12

4,282.00财务部总经理经济员男苗1973-10本科1994-08

2,097.00办公室总经理经济师女汉1955-10高中1979-12

6,428.00财务部科员助会师女汉1974-08本科1992-11

5,284.00财务部科员

女汉1971-05大专1989-12

9,753.00办公室科员助经师女汉1962-09大专1978-12

5,368.00科技部科员

男汉1961-06高中1979-10

4,288.00科技部科员经济员女蒙古1963-04大专1983-12

8,079.00财务部总经理助经师男汉1963-09大专1980-11

2,340.00科技部总经理

女汉1971-09本科1989-12

4,196.00财务部科员助会师男汉1966-03大专1985-03

8,685.00办公室科员

女汉1967-01本科1989-12

8,635.00条件1条件2部门文化程度财务部本科部门岗位职务专业技术职称性别民族出生年月文化程度参加工作时间月基本工资财务部科员会计员男汉1978-10本科1996-10

1,966.00财务部总经理经济员男苗1973-10本科1994-08

2,097.00财务部科员助会师女汉1974-08本科1992-11

5,284.00财务部科员助会师女汉1977-02本科1999-10

3,832.0011操作技巧——防止重复录入12选取要防止重复录入的单元格区域，数据-有效性-自定义-公式：如果重复录入，会提示错误并清除录入内容操作技巧——制作下拉菜单13例：如下图所示，要求在销售员一列设置可以选取的下拉菜单。设置步骤：步骤1：选取销售员一列需要设置下拉菜单的单元格区域（这一步不能少），打开数据有效性窗口（excel2003版数据菜单-有效性，excel2007和2010版本数据选项卡-数据有效性-数据有效性），在窗口中的“设置”选项卡里选“序列”。步骤2：在来源输入框里我们需要设置下拉菜单里要显示的内容，有两种设置方法。1直接输入法。在来源后的框里输入用“,”（英文逗号）连接的字符串：张一，吴汉青，刘能，将文胜，李大民2引用单元格的内容法。如果销售员在单元格B4:B8区域里，在“来源”后输入或点框最后的折叠按钮选这个区域分析：在excel里制作下拉菜单有好几种方法，我们这里是介绍用数据有效性设置下拉菜单，操作技巧——制作二级联动下拉菜单14例：如下图所示，在手机列输入或选取苹果，型号下拉里会显示所有苹果手机的所有型号，如果手机列输入三星，在型号列下拉菜单显示所有三星的型号。手机列选取三星大家应该明白什么是二级下拉联动菜单了，还等什么，我们一起开始制作吧。步骤1：设置数据源区域。就是把手机名称和型号整理成如下图格式备用，存放的位置随意。操作技巧——制作二级联动下拉菜单步骤3：设置数据有效性。选取型号列，打开数据有效性窗口，在来源中输入=indirect(D5)进行如下设置后，二级联动菜单设置完成。步骤2：批量定义名称。选取手机名称和型号区域后，打开指定名称窗口（excel2003版里，插入菜单-名称-指定，07和10版公式选项卡-定义的名称组-根据所选内容创建），选取窗口上的“首行”复选框。如下图所示。操作技巧——表格只能填写不能修改操作步骤步骤1：按ctrl键，选取所有黄色的区域，然后按ctrl+1（数字1）打开“单元格格式”窗口，在锁定选项卡中，去掉“锁定”前面的勾选。步骤2：保护工作表。excel2003版工具菜单-保护-保护工作表。excel2010版审阅选项卡-保护工作表。

按上述步骤操作后，你试着修改黄色区域单元格：ok。你试着在黄色之外的区域修改或插入行/列，就会弹出如下图所示的提示。

操作技巧——批量填充“相同内容”的问题在单元格区域中批量填充“相同内容”，这里通常要涉及Excel中定位功能的使用。定位的目的在于选中要填充的单元格。选中全表，按下Ctrl+G--定位条件--空值--单击确定，在单元格编辑栏上输入“没数据”，按住Ctrl然后再按Enter,这时候我们就可以看到所有的空白单元格都被填上了“没数据”。请看下面的演示：操作技巧——效率加倍的快捷键在单元格中导航的快捷键

选择单元格／选择单元格区域

上、下、左、右移动箭头方向键选择单元格区域Shift+方向键Ctrl+Shift+方向键移动到右侧单元格Tab键选择单元格所在的整行Shift+空格键（可能需要关闭输入法）移动到左侧单元格Shift+Tab键选择单元格所在的整列Ctrl+空格键（可能需要关闭输入法^^）移动到下方单元格回车键选择单元格所在的区域Ctrl+Shift+*(同Ctrl+A)移动到上方单元格Shift+回车键隐藏单元格所在的整行Ctrl+9移动到单元格区域的边缘Ctrl+箭头方向键取消隐藏单元格所在的整行Ctrl+Shift+9扩展选取单元格区域Ctrl+Shift+箭头方向键隐藏单元格所在的整列Ctrl+0达到工作表的开始Ctrl+Home键取消隐藏单元格所在的整列Ctrl+Shift+0达到工作表的末尾Ctrl+End键插入单元格Ctrl+Shift++到达某行的开始Home键删除单元格Ctrl+-全选活动单元格区域Ctrl+A快速输入/编辑/格式操作

标准操作

在选中的单元格区域中一次性输入Ctrl+回车键保存Ctrl+S重复上一次操作F4键打开Ctrl+O输入当前日期Ctrl+;(分号)打印Ctrl+P输入当前时间Ctrl+Shift+;(分号)新建工作表Ctrl+N依据首行内容向下填充Ctrl+D键复制Ctrl+C依据首列内容向右填充Ctrl+R键剪切Ctrl+X筛选/取消筛选Ctrl+Shift+L键粘贴Ctrl+V编辑单元格F2键在单元格中换行Alt+回车键工作簿/工作表导航

设置单元格格式Ctrl+1工作簿导航ATL+Tab\Ctrl+Tab查找/替换对话框Ctrl+F，Ctrl+H工作表导航Ctrl+PageUP/Down定位Ctrl+G（F5键)18课程目录前言操作技巧公式与函数数据透视表图表总结12345670001020304050607公式与函数常用运算符介绍认识公式、函数单元格引用逻辑判断逻辑函数数据汇总统计类函数查找引用类函数19公式与函数——常用运算符介绍类型符号说明实例结果算数运算符-算术运算符：负号=8*-5=-40-40%算术运算符：百分号=60*5%=33^算术运算符：乘幂=3^2=99=16^(1/2)=44*和/算术运算符：乘和除=3*2/4=1.51.5+和-算术运算符：加和减=3+2-5=00比较运算符=,<>比较运算符：等于、不等于、大于、小于、大于等于和小于等于=(A1=A2)判断A1与A2相等FALSE>,<=(B1<>"ABC")判断B1不等于"ABC"TRUE>=,<==(A10>=5)判断A10大于等于5TRUE文本连接符&文本运算符：连接文本="jim"&"Zhang"返回"JimZhang"jimZhang引用:区域运算符：冒号=SUM(A1:B10)引用冒号两边所引用的单元格为左上角和右下角之间的所有单元格组成的矩形区域210空格交叉运算符：单个空格=SUM(A1:B5A4:D9)引用A1:B5与A4:D9的交叉区域，公式相当于=SUM(A4:B5)34,联合运算符：逗号=AVERAGE(A1:A5,B6:B10)引用A1:A5和B6:B10两个不连续的单元格区域10.520公式与函数——常见的错误信息序号错误信息原因1#DIV/0!当数字除以零(0)时，会出现此错误2#N/A当数值对函数或公式不可用时，将出现此错误3#NULL!如果指定两个并不相交的区域的交点，将出现此错误。引用之间的交叉运算符为空格。4#NAME?当MicrosoftOfficeExcel不识别公式中的文本时，会出现此错误5#NUM!公式或函数中使用了无效的数值，则会出现此错误6#REF!当单元格引用无效时，会出现此错误7#VALUE!使用错误的参数或运算对象类型，会出现此错误21公式与函数——公式？函数？

公式：是以等号“=”开头，通过使用运算符将数据、函数等元素按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等式。例如C1=A1+A2+A3+A4+B1+B2+B3+B4函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式就可以用函数C1=SUM(A1:B4)代替。

函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能替代的功能。例如查找引用，逻辑判断等等。22公式与函数——单元格引用1.相对引用当把公式复制到其它单元格中时，行或列的引用会改变。2.绝对引用当把公式复制到其它单元格中时，行和列的引用不会改变。绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.混合引用

混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用，在行号前面加了一个$符号，行号就固定不变了，而列标前面没有加$符号，列标就会发生变化.

例:行变列不变:$A4

列变行不变:A$44.单元格引用快捷键：F423如果有兴趣，可以练习一下看看，这样会更有助于对引用方式的理解哦。公式与函数——逻辑判断Excel的逻辑值只有2个：TRUEFALSETRUEFALSE是函数的关键，可以这么说：任何函数都是在解决TRUE还是FALSE的问题TRUEFALSE与运算符进行各种计算时，请牢记：TRUE=1FALSE=0，请务必牢记24公式与函数——逻辑函数（IF、AND、OR）25考核处罚考核等级优秀员工900A优502D800B600D701C950S优A7B7C7=IF(A2>=95,"S",IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","D"))))=IF(AND(OR(C2="S",C2="A"),B2=0),"优","")>=95分—S,90～95—A,80～90—B,70～80—C,其他分—D考核等级为S或A,且处罚为0的为优秀员工公式与函数——数据汇总统计类函数Sum函数解析26案例1:汇总多个表格中相同单元格的数据（sum)公式：=SUM(‘*’!D3)*就是通配符，代表着任意字符，用在!前面就表示除当前外的工作表公式与函数——数据汇总统计类函数Sumif函数解析27案例2:如何汇总某品名的销售数量（sumif)单条件汇总品名销售量公式铁观音225=SUMIF(B3:B26,F3,D3:D26)品名销售量公式

*茶*493=SUMIF(B3:B26,F7,D3:D26)品名销售量公式

???茶134=SUMIF(B3:B26,F11,D3)公式语法：=sumif(条件区域，条件，求和区域）28

数据计数类函数，关键词:count,,countif,countifs公式与函数——数据汇总统计类函数1、COUNTCOUNT是个数据控，只要看到阿拉伯数字，不管它是数值、日期还是时间，它就开始计数（统计有日期和数值的单元格个数）2、COUNTA(COUNT+A)COUNTA,是个直肠子，看见单元格里有东西，它就开始数数（统计所有非空单元格个数）3、COUNTIF(COUNT+IF)COUNTIF,只要符合规定它就开始计数，（统计满足条件的单元格个数）4、COUNTIFS(COUNT+IF+S)COUNTIFS，统计满足多个条件的单元格个数5、COUNTBLANKCOUNTBLANK，统计空白单元格的个数29

数据计数类函数，关键词:count,案例解析公式与函数——数据汇总统计类函数部门姓名工号性别年龄6月份销售额(万元)营销一部A2男352000B3女301500营销二部C6女332500D10男281600营销三部E15女402100F30男191800A2:A7C2:C7D2:D7E2:E7F2:F7部门数量总人数男性员工数平均年龄最高营业额363312500=COUNTA(A2:A7)=COUNT(C2:C7)=COUNTIF(D2:D7,"男")=AVERAGE(E2:E7)=MAX(F2:F7)A2:A7非空值计数C:C7计数D2:D7值为"男"的计数求平均年龄求最高销售额30

数据计数类函数，关键词:SUBTOTAL,案例解析公式与函数——数据汇总统计类函数语法subtotal(参数1，区域1，区域2。。。区域n)

subtotal的强大之处在于参数1，参数1不同的值会有不同的计算方式，下面列举常用的几个值参数1同等函数意义1average求算术平均值2count

数值计数3counta

非空单元格计数4

max最大值5

min最小值6

product求乘积9sum求和101average求算术平均值102count数值计数103counta非空单元格计数104

max最大值105

min最小值106product求乘积109sum求和1-9表示区域中如果有的行被隐藏了，那么隐藏的行还是参与计算的。101-109表示区域中隐藏的行不参与计算。【注】只是隐藏行会有区别，隐藏列不会有计算区别的。公式与函数——查找引用类函数VLOOKUP函数解析（办公室函数使用率最高的函数之一）31主要用途：例如：按员工号查找某位员工的姓氏，或通过查找员工的姓氏查找该员工的电话号码。下图为某公司的员工信息表，有N多行数据，我们需要找出某员工的详细记录。函数解析：需要查找的姓名记录在单元格F5中，然后在G5中写公式：

=VLOOKUP($F$5,$B$1:$D$10,2,0)由于员工部门对于员工信息表在第2列（以姓名所在列为1，向右数），故第三个参数为2。因为我们想要精确的找到“天竺僧”的部门，即第四个参数采用精确查找的查找方式，所以需要写为False，而在Excel中，False的逻辑值为0，因此可以简写为0。采用上述的公式，我们就很容易的查到天竺僧是的部门是生产部。如果需要了解该员工的详细记录的话，可以继续在其他单元格里书写公式，当然第三个参数会有变化，比如职务在第3列就应该写成3公式与函数——查找引用类函数HLOOKUP函数解析32绩效等级SAB+BB-CD直接支持人员+职级为E级及以上人员21.51.2510.750.50间接支持人员1.81.41.210.750.60员工类型工号绩效等级等级系数直接支持人员+职级为E级及以上人员3A1.5=HLOOKUP(C7,$B$1:$H$3,2,0)间接支持人员6A1.4=HLOOKUP(C8,$B$1:$H$3,3,0)B1:H3在区域B1:H3查找C7值对应列第2行的数据；在区域B1:H3查找C8值对应列第2行的数据C7C8公式与函数——查找引用类函数INDEX函数解析33INDEX数组形式：获得目标单元格或区域的引用=index(array,row_num,[col_num])引用形式：获得目标单元格引用=index(reference,row_num,[col_num],[area_num])数组形式行、列参数为0或省略时，公式以数组形式结束公式与函数——查找引用类函数MATCH函数解析34公式与函数——查找引用类函数函数组合（利用MATCH函数查找位置，用INDEX函数取值）351、反向查找【例】如下图所示，要求根据产品名称，查找编号。分析：先利用Match函数根据产品名称在C列查找位置=MATCH(B13,C5:C10,0)再用Index函数根据查找到的位置从B列取值。完整的公式即为：=INDEX(B5:B10,MATCH(B13,C5:C10,0))2、双向查找【例】如下图所示，要求根据月份和费用项目，查找金额分析：先用MATCH函数查找3月在第一行中的位置=MATCH(B10,$A$2:$A$6,0)再用MATCH函数查找费用项目在A列的位置=

MATCH(A10,$B$1:$G$1,0)最后用INDEX根据行数和列数提取数值INDEX(区域,行数,列数）=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))公式与函数——查找引用类函数函数组合（利用MATCH函数查找位置，用INDEX函数取值）363、多条件查找【例】如下图所示，要求根据入库时间和产品名称，查找入库单价。分析：由于match的第二个参数可以支持合并后的数组所以可以直接进行合并查找：=MATCH(C32&C33,B25:B30&C25:C30,0)查找到后再用INDEX取值=INDEX(D25:D30,MATCH(C32&C33,B25:B30&C25:C30,0))由于公式中含有数组运算(一组数同另一组数同时运算)，所以公式需要按ctrl+shift+enter三键完成输入

公式与函数——多条件判断1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1、多条件区间判断【例1】按销售量计算计成比率。2、多条件组合判断【例2】如果金额小于500并且B列为“未到期”则返回补款，否则为空=IF(AND(A2<500,B2="未到期"),"补款","")说明：两个条件同时成立用AND,任一个成立用OR函数。公式与函数——多条件求和1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

业务区域发生额（万元）结算方式一、计算区域为华南区的发生额

华北区50现金结果公式公式写法西南区60转账361=SUMIF(A:A,"华南区",B:B)=SUMIF(条件区域,条件,求和区域)华南区45汇款华东区80汇款二、追加一个条件，计算业务区域为“华南区”、结算方式为“转账”的发生额西南区60汇款结果公式公式写法华南区91转账196=SUMIFS(B:B,A:A,"华南区",C:C,"转账")=SUMIFS(求和的区域,条件区域一,条件一,条件区域二,条件二….)华东区80汇款196=SUMPRODUCT((A:A="华南区")*(C:C="转账"),B:B)=SUMPRODUCT(条件一*条件二*……条件N,求和区域)西南区51现金华南区40转账三、算业务区域为“华南区”、结算方式为“转账”的业务发生笔数。华东区52汇款结果公式公式写法华北区11转账3=COUNTIFS(A:A,"华南区",C:C,"转账")=COUNTIFS(条件区域一,条件一,条件区域二,条件二……)西南区46汇款3=SUMPRODUCT((A:A="华南区")*(C:C="转账"))=SUMPRODUCT(条件一*条件二*……条件N)华南区75现金西南区90转账华南区45汇款华东区88汇款西南区55汇款华南区65转账华东区78现金公式与函数——多条件计数1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

【例】根据下图，统计公司1人事部有多少人=COUNTIFS(A2:A6,"公司1",B2:B6,"人事部")公式与函数——字符串处理公式1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1、多单元格字符串合并公式：c2=PHONETIC(A2:A7)说明：Phonetic函数只能对字符型内容合并，数字不可以。2、截取除后3位之外的部分公式：=LEFT(D1,LEN(D1)-3)说明：LEN计算出总长度,LEFT从左边截总长度-3个3、截取-前的部分公式:B2=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

公式与函数——日期计算公式1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

1.两个范围相乘并求和

=SUMPRODUCT（范围1，范围2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多条件求和

=SUMPRODUCT（（范围1=条件1）*（范围2=条件2）*求和范围）最后一个可以不用括号的

3.综合用法条件相乘

=SUMPRODUCT（（范围1=条件1）*范围2，范围3）范围1与范围2可以是同一范围。

范围及条件是可多达30个的。通常也只会用到3个左右。这个函数与SUMIF有相似之处，但更强大。

Datedif:两日期相隔的年、月、天数计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d")结果：557相隔多少月?

=datedif(A1,B1,"m")结果：18相隔多少年?=datedif(A1,B1,"Y")结果：1不考虑年相隔多少月？=datedif(A1,B1,"Ym")结果：6不考虑年相隔多少天？=datedif(A1,B1,"YD")结