Excel函数应用教程.docx
《Excel函数应用教程.docx》由会员分享，可在线阅读，更多相关《Excel函数应用教程.docx（16页珍藏版）》请在冰豆网上搜索。
Excel函数应用教程
Excel函数应用教程：
函数与公式的概念
什么是函数？
Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。
以常用的求和函数SUM为例，它的语法是“SUM（number1,number2,......）”。
其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。
函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。
使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。
按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。
前者只要启动了Excel，用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。
什么是公式？
函数与公式既有区别又互相联系。
如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式。
以公式“=SUM（E1:
H1）*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。
上式中的“SUM（E1:
H1）”是函数，“A1”则是对单元格A1的引用（使用其中存储的数据），“26”则是常量，“*”和“+”则是算术运算符（另外还有比较运算符、文本运算符和引用运算符）。
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。
在Excel中巧做复杂表头
在Excel中，制作表头是常有的事，但鉴于中美文化的差异，中文表格中的表头部分有时很复杂，制作起来比较困难，如果遇到要制作像图1这样的表头，那就更麻烦了。
下面介绍一下笔者做工作中总结的方法。
十分复杂的表头
1.首先做斜线表头部分，确定一个4×4的区域，画上斜线，填入文字如图2。
先填入文字
2.调整文字的对齐方式及表格各列的宽度，得到理想的表格斜线头部部分，选定该4×4区域，设置框线，完成该部分制作，如图3。
调整文字对齐方式
3.制作横向表格头部，合理合并单元格，填入文字，确定对齐方式，完成制作，如图4。
合并单元格
4.制作纵向表格头部，与（3）类似。
合理确定表格线，完成表格制作。
探寻Excel中正确输入分数的秘密
Excel非常强大，但在它“强大”的背后，也隐藏着一些不尽如人意的地方，比如输入分数。
利用一些小工具来实现轻松输入吧。
加个车头分数不再变日期
比如现在要绘制一张股票市场报价表，在Excel表格中任意输入一个分数6/7，可一回车后就会变为“6月7日”，这当然不是我们希望看到的结果。
解决方法非常简单，先输入0，然后输入空格，再输入分数即可，如“0（空格）6/7”。
设置单元格分数格式听命令
在Excel2007中，选中要输入分数的单元格，然后单击“开始→格式”旁边的下拉按钮，在弹出的菜单中选择“设置单元格格式”命令（见图1），选择“分类”列表内的“分数”，接着在“类型”区域内选择“分母为一位数”，如图2所示。
单击“确定”按钮返回。
这样当我们在选定的单元格中输入小数，回车确认时就会自动转换成分数。
在Excel2003中，可以按Ctrl+1键打开“单元格格式”对话框，然后参照上面的方法来实现。
图1单元格式
图2分数格式
巧妙输入未被简化的分数
有时，我们需要输入一些未被简化的分数，如8/16，但回车确认后，却发现分数变为了1/2。
原来，Excel自动应用了一种分数格式，将分数中的分子分母最小化了。
在“设置单元格格式”对话框中分类”列表内的“分数”，接着在“类型”区域内选择“以16为分母”，单击“确定”后，会发现1/2变成了8/16。
此外还能设置“分母为二位数”、“分母为三位数”以及“百分之几”等形式，大家可以根据需要来设置。
采取上述方法输入的分数，在Excel表格中都可以用来计算。
有时为了美观，我们利用一些诸如公式输入、双行合一等方法，输入的符合日常书写形式的分数，虽然美观但不能计算，但只能在排版时用到。
Excel相对，绝对和混合引用的区别
规律：
加上了绝对引用符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。
混合引用时部分地址发生变化。
相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。
具体情况举例说明：
1、相对引用，复制公式时地址跟着发生变化，如：
C1单元格有公式：
=A1+B1
当将公式复制到C2单元格时变为：
=A2+B2
当将公式复制到D1单元格时变为：
=B1+C1
2、绝对引用，复制公式时地址不会跟着发生变化，如：
C1单元格有公式：
=$A$1+$B$1
当将公式复制到C2单元格时仍为：
=$A$1+$B$1
当将公式复制到D1单元格时仍为：
=$A$1+$B$1
3、混合引用，复制公式时地址的部分内容跟着发生变化，如：
C1单元格有公式：
=$A1+B$1
当将公式复制到C2单元格时变为：
=$A2+B$1
当将公式复制到D1单元格时变为：
=$A1+C$1
还不懂的看图
随着公式的位置变化，所引用单元格位置也是在变化的是相对引用；而随着公式位置的变化所引用单元格位置不变化的就是绝对引用。
下面讲一下“C4”、“$C4”、“C$4”和“$C$4”之间的区别。
在一个工作表中，在C4、C5中的数据分别是60、50。
如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就变成了50，里面的公式是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了“=D4”。
现在在D4单元格中输入“=$C4”，将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。
如果在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”。
如果在D4单元格中输入“=$C$4”，那么不论你将D4向哪个方向拖动，自动填充的公式都是“=$C$4”。
原来谁前面带上了“$”号，在进行拖动时谁就不变。
如果都带上了“$”，在拖动时两个位置都不能变。
绝对引用与相对引用的区别都清楚了吧
Excel数字自动转换大写金额
用Excel制作财务报表的时候，经常需要将数字表示为大写的中文数字金额，可以用设置“单元格格式”的方法来实现自动转换大写金额。
首先鼠标右键单击需要用大写金额显示的单元格，在弹出的快捷菜单中选择“设置单元格格式”，如下图。
在弹出的对话框中选择“数字”选项卡，并且在“分类”列表中选择“特殊”，在类型框中选择“中文大写数字”，然后单击“确定”选中此类型即可。
经过设置后，Excel数字自动转换为大写金额，输入的时候就不用每次输入大写这么麻烦了！
复制保留源列宽的Excel表格
在Excel中，如果把按纸张大小设置好列宽、准备打印输出的表格复制一份，想更改其部分表格内容作为另一份表格时，是否发现粘出的表格内容没变，但列的宽度却变了？
只有再次调整列宽才能打印输出。
其实我们只要再接着做一步就能使粘出的表格保留源列宽。
如在工作表中选中准备复制的单元格，复制到别的单元格，会发现新粘出的表格虽然列宽变了，但表格的右下角却多出来一个“粘贴选项”按钮，如果没有发现“粘贴选项”按钮，可以单击“工具”→选项，在打开的“选项”对话框中选择“编辑”选项卡，看一下“显示粘贴选项”是否勾选上了。
（如图1）
单击“粘贴选项”按钮，选择“保留源列宽”，粘过来的表格照样保持原来的列宽了！
（如图2）
了解七种"Excel公式错误值"解决方法
经常使用Excel的朋友可能都会遇到一些看起来似懂非懂的错误值信息：
例如#N/A！
、#VALUE！
、#DIV/O！
等等，出现这些错误的原因有很多种，你真的了解它们吗，熟练掌握解决这些错误的方法吗？
以下为大家介绍几种常见的错误及其解决方法。
1．#####！
如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####！
。
这个看起来比较简单，大家应该都了解吧。
解决方法：
如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。
如果使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值。
如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。
2．#VALUE!
当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!
。
这其中主要包括3点原因。
1）在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
解决方法：
确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。
例如：
如果单元格A1包含一个数字，单元格A2包含文本，则公式="A1+A2"将返回错误值#VALUE!
。
可以用SUM工作表函数将这两个值相加（SUM函数忽略文本）：
=SUM（A1:
A2）。
2）将单元格引用、公式或函数作为数组常量输入。
解决方法：
确认数组常量不是单元格引用、公式或函数。
3）赋予需要单一数值的运算符或函数一个数值区域。
解决方法：
将数值区域改为单一数值。
修改数值区域，使其包含公式所在的数据行或列。
3．#DIV/O!
当公式被零除时，将会产生错误值#DIV/O!
。
在具体操作中主要表现为以下两种原因。
1）在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用（在Excel中如果运算对象是空白单元格，Excel将此空值当作零值）。
解决方法：
修改单元格引用，或者在用作除数的单元格中输入不为零的值。
2）输入的公式中包含明显的除数零，例如：
公式=1/0。
解决方法：
将零改为非零值。
4．#N/A
当在函数或公式中没有可用数值时，将产生错误值#N/A。
解决方法：
如果工作表中某些单元格暂时没有数值，请在这些单元格中输入"#N/A"，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
5．#REF!
删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。
当单元格引用无效时将产生错误值#REF！
。
解决方法：
更改公式或者在删除或粘贴单元格之后，立即单击"撤消"按钮，以恢复工作表中的单元格。
6．#NUM！
当公式或函数中某个数字有问题时将产生错误值#NUM！
。
1）在需要数字参数的函数中使用了不能接受的参数。
解决方法：
确认函数中使用的参数类型正确无误。
2）由公式产生的数字太大或太小，Excel不能表示。
解决方法：
修改公式，使其结果在有效数字范围之间。
7．#NULL！
使用了不正确的区域运算符或不正确的单元格引用。
当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL！
。
解决方法：
如果要引用两个不相交的区域，请使用联合运算符逗号（，）。
公式要对两个区域求和，请确认在引用这两个区域时，使用逗号。
如果没有使用逗号，Excel将试图对同时属于两个区域的单元格求和，由于A1:
A13和c12:
c23并不相交，它们没有共同的单元格所以就会出错。
常用办公软件Excel技巧整理讲解
常用办公软件Excel技巧的整理、讲解，在这里给读者们看一看，给大家一些提示，希望在你在平时能用得上。
1、两列数据查找相同值对应的位置
=MATCH（B1，A：
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1！
C1）
已知结果得公式
定义名称=GET.CELL（6，Sheet1！
C1）
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了，也收起来吧。
一、单元格设置为文本；二、在输入数字前先输入'
5、如果隐藏了B列，如果让它显示出来？
选中A到C列，点击右键，取消隐藏
选中A到C列，双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
6、EXCEL中行列互换
复制，选择性粘贴，选中转置，确定即可
7、Excel是怎么加密的
（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1：
A10，">=90"）
介于80与90之间需用减，为=COUNTIF（A1：
A10，">80"）-COUNTIF（A1：
A10，">90"）
9、根据身份证号提取出生日期
（1）、=IF（LEN（A1）=18，DATE（MID（A1，7，4），MID（A1，11，2），MID（A1，13，2）），IF（LEN（A1）=15，DATE（MID（A1，7，2），MID（A1，9，2），MID（A1，11，2）），"错误身份证号"））
（2）、=TEXT（MID（A2，7，6+（LEN（A2）=18）*2），"#-00-00"）*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF（A：
A，A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A：
A，A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%，如果没有，请手工加上
13、Excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
=SUM（OFFSET（$A，（ROW（）-2）*10+1，，10，1））
15、查找数据公式两个（基本查找函数为VLOOKUP，MATCH）
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1，A1：
A7&B1：
B7，0））
16、如何隐藏单元格中的0
单元格格式自定义0；-0；；@或选项》视图》零值去勾。
呵呵，如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1！
D4+Sheet2！
D4+Sheet3！
D4，更好的=SUM（Sheet1：
Sheet3！
D4）
18、获得工作表名称
（1）、定义名称：
Name
=GET.DOCUMENT（88）
（2）、定义名称：
Path
=GET.DOCUMENT
（2）
（3）、在A1中输入=CELL（"filename"）得到路径级文件名
在需要得到文件名的单元格输入
=MID（A1，FIND（"*"，SUBSTITUTE（A1，""，"*"，LEN（A1）-LEN（SUBSTITUTE（A1，""，""））））+1，LEN（A1））
（4）、自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、如何获取一个月的最大天数
：
"=DAY（DATE（2002，3，1）-1）"或"=DAY（B1-1）"，B1为"2001-03-01
数据区包含某一字符的项的总和，该用什么公式
=sumif（a：
a，"*"&"某一字符"&"*"，数据区）
最后一行为文本：
=offset（$b，MATCH（CHAR（65535），b：
b）-1，）
最后一行为数字：
=offset（$b，MATCH（9.9999E+307，b：
b）-1，）
或者：
=lookup（2，1/（b1：
b1000<>""），b1：
b1000）
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分？
同时显示出被去掉的分数。
看看trimmean（）函数帮助。
被去掉的分数：
最大两个：
=large（data，）
最小两个：
=small（data，）
怎样很简单的判断最后一位是字母
right（a1）*1
出错的字母
=IF（ISNUMBER（--RIGHT（A1，1）），"数字"，"字母"）
=IF（ISERR（RIGHT（A1）*1），"字母"，"数字"）
如何设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror（find（""，a1））
数据--有效性--自定义--公式
=len（a1）=len（trim（a1））
原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22……
现在是=sum（n（offset（a1，（row（1：
10）-1）*3，）））
在一个工作表中引用其他工作表中的数据，但是被引用的工作表不是固定的，根据我输入的工作表名自动选择相应的工作表中的数据，请问在公式里怎样引用？
=INDIRECT（"A1"&"！
"&"E1"）A1为工作表名
奇数行求和=SUMPRODUCT（（A1：
A1000）*MOD（ROW（A1：
A1000），2））
偶数行求和=SUMPRODUCT（（A1：
A1000）*NOT（MOD（ROW（A1：
A1000），2）））
查看字符串字数
=LEN（A1）
求非空单元格数量
公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）
=COUNTIF（$E：
$E536，"？
*"）
动态求和公式，自A列A1单元格到当前行前面一行的单元格求和.
=SUM（INDIRECT（"A1：
A"&ROW（）-1））
20、比较好用的Excel文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入 =8^（1/3）
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的链接
Ctrl+~或编辑》链接
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把Word里的数字转换到Excel
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
实例教你学Excel函数排序与筛选
Execl本身具有很方便的排序与筛选功能，下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。
但也有不足，首先无论排序或筛选都改变了原清单的原貌，特别是清单的数据从其它工作表链接来而源数据发生变化时，或清单录入新记录时必须从新进行排序或筛选。
其次还有局限，例如排序只能最多对三个关键字（三列数据）排序，筛选对同一列数据可用“与”、或“或”条件筛选，但对不同列数据只能用“与”条件筛选。
例如对某张职工花名册工作簿，要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的，如同时要求性别是男的或女的也是可行的。
但要求筛选出女的年龄在22岁到45岁，男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。
再者排序与筛选不能结合使用，即不能在排序时根据条件筛选出来的记录进行排序。
例如有一张职工资料清单，其中有的职工已经退休，对在职职工的年龄进行排序时无法剔除已退休职工的数据。
本文试图用Execl的函数来解决上述问题。
一、用函数实现排序
题目：
如有一张工资表，A2:
F501，共6列500行3000个单元格。
表头A1为姓名代码（1至500）、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。
现要求对职工收入从多到少排序，且在职工总收入相同时再按工资从多到少排序，在职工总收入和工资相同时再按奖金从多到少排序，在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。
方法：
G1单元格填入公式
“=if（F2=0，10^100，INT（CONCATENATE（999-f2，999-e2，999-d2，999-c2）））”，
CONCATENATE是一个拼合函数，可以把30个以下的单元的数据拼合成一个数据，这些被拼合的数据之间用逗号分开。
用f2、e2等被拼合的数据用999来减，是为了使它们位数相同。
（假定任何一个职工的总收入少于899元）。
被拼合成的函数是文本函数，CONCATENATE与INT函数套用是为了使文本转换为数字。
最外层的if函数是排序时用来剔除不进行排序的记录，在本例中指收入为零的记录。
（在上文提到的职工年龄排序，则公式改为“if（f2="退休"，10^100，.....）”，即剔除了退休职工。
）
第二步把G1单元格的公式拖放到G500单元格（最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标，见到黑十时双击鼠标就完成了G1到G500的填充）。
第三步在在H2单元填入公式“=MATCH（SMALL（G:
G，ROW（A1）），G:
G，0）”与第二步一样拖放到H501单元格。
此公式实际上是把三列公式合成一列公式，ROW（A1）即为A1的行数是1，随着向下拖放依次为2、3、4...，SMALL（G:
G，ROW（A1））为G列中最小的数随着向下拖放依次为第2、第3、..小的数，MATCH（SMALL（G:
G，ROW（A1）），G:
G，0）即为G列各行的数据中最小、第2、第3小等的数据在第几行。
第四步把A1至F1单元格的表头复制到I1至N1单元格，在I2单元格输入公式“=INDEX（$A$2:
$F$501，$H2，COLUMN（A$1））”INDEX函数是一个引用函数，即把$A$2:
$F$501单元格列阵第$H2行第COLUMN（A$1）列的数据放入I2单元格。
然后把I2单元格的公式拖放到N2单元格，点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。
以上叙述看似繁杂实际非常简单，只要把A1至F1的表头复制到I1至N1单元格，再分别在G1、H2、I2单
升级会员 