函数基础-基础概念篇

掌握核心概念，突破菜鸟瓶颈！Excel函数基础了解函数原理，掌握学习方法！提升办公效率，体会学习乐趣！引言这不是一个介绍excel基础知识的资料，它仅仅为一些想学习excel函数应用却又难以入门的朋友提供一些帮助；我们假定你已经有了一些excel的使用经验，例如新建一个表格并能够简单的排版，同时你也见过一些公式以及函数，尽管你可能不明白它们的作用和含义；你的工作需要经常使用表格人并且会遇到数据统计之类的任务，或者见过别人使用公式和函数自己很有兴趣想去了解；那么你可以每天抽出一点时间去了解基础知识，刚开始的节奏也许会比较慢，正如盖一座大厦，花在打地基的时间会比较多一样，只有基础牢固了，以后才能有更多的进步；每一页内容希望可以做到逐字琢磨，并加以理解和实践，看完每一章内容也许只需要花费一两个小时，但是掌握每一章内容可能需要一到两天甚至更久，第一次不必理解所有的文字，掌握每篇重点即可，所谓循序渐进；请关注每章的重点并保证有充足的练习时间；当你读完全部内容后可以重新再看一次，相信会有新的收获，所谓温故知新！每页最后会有一句话总结重点内容，页面下方有一个Excel技巧供学习积累；部分页面的内容对于功力尚浅的朋友来说可以看起来会有不适感，常见症状为眩晕、焦虑，如遇此类内容立即跳过进入下一页，并不会对你的学习有任何不良后果，可待日后功力提升再回头重新修炼，所谓循序渐进，切勿急于求成！第一篇、学习基础从概念开始本篇主要介绍一些公式中可能涉及到的基本概念；每页说明一个概念，每页下方或许会有一句话的重点提示，这是务必要搞清楚并且能够理解的要点；个别页面有些提示语，都是一些容易忽视的地方，需要谨记；还有些“成功秘技”之类的语句，仅仅是老鸟个人经验，并非重点；个别概念需要动手实践，一定不要偷懒，只有动手了、明白了，才是学会了！不要去背概念，这是老鸟深恶痛绝的，概念背的滚瓜烂熟也未必能懂得其中的要领，一定要在用的过程中去理解；本篇结束时会有小结部分总结重点内容；提供部分测试问题的答案；还有在学习中可能会遇到的问题解答；闲话打住，下面开始了解第一个概念：什么是公式？公式公式是Excel中以等号开头的可以得到一个结果的等式公式以等号(=)开头，公式中可以包括函数、运算符、引用和常量。例如：=5+2*3、=Sheet1!$Q$26、=A1>0、=sum(A:A)，等等都是公式。在Excel的公式中乘号（×）用星号（*）代替，除号（÷）用斜杠（/）代替；乘方运算用符号^，（这个符号使用shift和6键即可输入），例如，10的5次方，数学式为：105，Excel中写作：=10^5。小测试：试将以下算式改写为Excel的公式：算式1：3+(5+20)÷4算式2：10×[(5+7)÷6]算式3：23×（4-2）提示：在Excel公式中，数学算式里的中括号和大括号一律用小括号代替，中括号和大括号在Excel公式中有其他的含义，后文会有详细介绍；公式中的所有符号请在英文输入模式下完成。技巧分享：Ctrl+;（按着CTRL键按一下分号键）可以快速输入当天日期本页重点：公式必须以(=)等号开头，公式中不一定非得有函数存在。函数Excel中存在大量预设的函数，每个函数在使用不同的参数后都可以得到各种结果，例如：=SUM(A:A)，这个公式用到了SUM函数，可以实现求和功能。技巧分享：在单元格内按住Ctrl和shift键不松手，再按一下冒号键即可输入当前时间。可以在Excel中点击fx这个按钮（左图上方箭头所指位置）或者按住shift键和f3功能键，调出插入函数对话框，选择全部函数（左图中间箭头所指位置），查看Excel的全部内置函数，选择具体函数后，查看简短说明或者点击函数帮助（左图下方箭头所指位置）了解该函数的详细说明。也可以点击公式选项卡，在函数库中选择函数，如上图。函数的结构：函数名，一对括号，参数，逗号；每个函数的参数数量不尽相同，但是两个参数之间需要用逗号分开，如果仅有一个参数就不需要逗号了，也有的函数是不需要参数的。例如：=NOW()，这个函数前面加等号，可以直接构成公式，得到当前日期和时间，是个不需要参数的函数。=SUM(A:A,5)，这个函数有两个参数，A:A和5，两个参数之间用逗号分开，公式得到A列所有数字与5之和。本页重点：了解如何插入函数，了解函数的结构。编辑栏当有公式或者自定义格式时，编辑栏看到的和单元格看到的内容往往不同，例如上图中，编辑栏看到的是=NOW()，NOW是一个不需要参数的函数，可以得到系统日期和时间；而在单元格看到的是这个公式的结果，也就是说，编辑栏里看到的是真实的内容，单元格看到的是最终显示的内容。技巧分享：Ctrl+9（按着CTRL键按一下数字9）可以快速隐藏选定的行。菜鸟成功秘技：在你输入某个函数的过程中，不妨将鼠标停留在提示的其他函数上，大致了解一下对应函数的功能，不管是不是用的到，多认识几个函数，对今后的好处都是不可估量的除了上页所说的，用插入函数的方法来写公式，还可以直接在单元格输入公式内容，如果你记得函数名，那怕只记得函数名的开头几个字母也行，在单元格内输入=和函数名的开头字母，Excel会出现以你输入字母开头的所有函数，当我们选择其中某一个函数时，会有一个简短的函数说明，如左图。在编辑栏输入公式也有同样的效果，当函数名输入完成后，输入(，这个括号代表函数名完成输入，Excel会接着提示这个函数需要提供哪些参数。左图中红色框内白色的区域就是编辑栏，通常我们习惯双击单元格修改里面的内容或公式，其实也可以在编辑栏里进行修改。算术运算符算术运算符包括：加（+）、减（-）、乘（*）、除（/）、乘方（^），百分比（%）。关于百分号的一个用法：打开一个空表，A1单元格输入一个数字，例如150，B1输入公式：=A1/100，回车后B1显示结果为1.5，这个公式中用到了单元格引用（A1）和运算符除号（/），意思是对A1单元格的数据除以100，在C1单元格输入=A1%，回车后你会发现结果和B1的公式结果一样，这里就是利用了百分号（%）直接参与运算了。提示：除100（/100）可以用%代替，公式结果一样，但是公式减少了三个字符。那么除10000（/10000）如果用%的话改怎么写呢？不妨自己动手试试自己的想法是否正确。运算符分为四种不同类型：算术、比较、文本连接和引用。技巧分享：Ctrl+shift+9（按着CTRL键和shift键，再按一下数字9）可以快速显示隐藏的行菜鸟成功秘技：在学习Excel的过程中，尤其是公式函数的学习过程中，能够举一反三、触类旁通，大胆假设后动手去验证结果是一个非常好的习惯！本页重点：了解算术运算符有哪些，练习一下%的用法。其他运算符比较运算符：包括=(等号)、>（大于号）、<（小于号）、>=（大于等于号）、<=（小于等于号）、<>（不等号）。提示：Excel中大于等于是>=，不是≥，小于等于是<=，不是≤，不等于是<>，不是≠。例如：=(A1<=B1)，=(A1>B1)*10，=A1=“优秀”，这三个公式里用到了比较运算符，比较运算的结果为逻辑值，关于逻辑值第二章会详细介绍，这里了解即可。文本运算符：&，这个符号使用shift和7键即可输入。打开一个空表，A1输入：学习，B1输入：公式函数，C1输入：=A1&B1，回车后可以看到，这个公式会把两个单元格的内容合起来。提示：使用&得到的值是文本类型，假如A1内容为3，B1内容为5，=A1&B1可以得到35，但是这个35是文本类型的数字。关于数字类型第二章会详细介绍，这里了解即可。引用运算符：“:”（冒号）区域运算符、“,”（逗号）联合运算符、“”（空格）交集运算符。这三个运算符的含义下页介绍。技巧分享：在单元格内按住alt键回车，即可在同一单元格内换行输入。本页重点：了解其他的运算符有哪些，能够掌握&的用法。名称框技巧分享：选择多个单元格，输入内容后按住Ctrl键回车，可以快速批量输入相同内容。左图中红色箭头所指的白色区域就是名称框，通常显示当前选定的单元格位置，左图中选定的C4单元格。当表格数据较大时，如果要选择后面的单元格，可以直接在名称框输入，比如，要选择Z100单元格，可以直接在名称框输入Z100，回车，而不用去拉滚动条来找单元格了。当选择一个区域的时候，同时观察名称框，会显示选择范围的行数和列数，字母R代表行，字母C代表列，左图中选定的范围就是7行3列的一个区域。掌握这一点很重要，当你后面学习了函数的时候，经常要知道行列等信息，就可以通过名称框来知道这些。当你需要选择较多的单元格时，例如要选择A列的1000个单元格，用鼠标拉选会很费力，这时候可以在名称框里输入A1：A1000，回车即可完成，可谓是准确快捷。当选择一行或者一列的时候，名称框显示选择范围内第一个单元格，这个可以自己试试。上页介绍了三个引用运算符：“:”（冒号）、“,”（逗号）、“”（空格）。刚才的例子里已经用了冒号，下面再通过几个例子理解一下引用的含义。名称框里输入E5：F6回车、3:5回车、E4,F7,G1:G8回车、5:7C:G回车（7后面有个空格哦），你还可以输入更多的引用方式，相信可以理解冒号、逗号和空格在引用中的作用了，这里需要提示一下，理解作用即可，不必纠结具体定义和为什么会这样。本页重点：会看名称框里的信息，会在名称框输入引用地址。A1引用样式技巧分享：按住shift键拖动行、列或者单元格，可以移动选择的数据。默认情况下，Excel使用A1引用样式，字母和数字被称为行号和列标，例如，B2引用列B和行2交叉处的单元格。A1，E7，AB3，F22……等等这些都是如此，在2007以上的版本中，列号从A到XFD，共16,384列，行号从1到1048576，共1048576行，最后一列最后一行可以表示为：XFD1048576。在A2单元格输入：=A1，这就是一个单元格引用的公式，这时候，不管你在A1单元格里输入什么内容，A2单元格都会随着改变现实结果。=Sheet1!A1，这个公式也是一个单元格的引用，不过带了工作表（也就是sheet）名，公式会随着sheet1这个表的A1单元格内容发生变化。需要说明的是：在公式里，sheet名后面要加个感叹号。如果sheet名是自己修改的，那么引用其中的单元格后，sheet名还会有一对单引号，例如：=‘1’!A1，=‘1月’!$A$1，如果修改的sheet名只有字母，则不需要单引号，例如=Sheet!$A$1，=a!$A$1。以上的引用都发生在同一个文件（工作薄）里，对于跨工作薄的引用，还需要带有文件名，文件名在一对中括号里，例如：=[1.xls]Sheet1!$C$5，=[舒尔特训练法.xlsx]Sheet1!$F$1。对于单元格引用，可以输入等号，然后点击需要引用的单元格，或者复制要引用的单元格，然后在目的单元格粘贴链接即可。本页重点：掌握引用单元格内容的方法，了解中括号和感叹号在引用中的作用。相对引用技巧分享：在excel中F4键可以重复上一步的操作，例如你插入一列，按F4可以重复插入操作。绝对引用、相对引用和混合引用可以说是三个概念，也可以说是一个概念，就是“$”这个符号的用法。而关于这个问题，理解比概念本身更重要。按照以下步骤实际操作一下有助于你轻松掌握本页内容。在表格里输入：=a1（如果你是在a1单元格输入的话，可能会提示循环引用，不用管提示，直接确定），下拉几个单元格（不用管公式得到什么结果），显示公式：按公式选项卡的显示公式按钮或者按住Ctrl按一下~键（在数字1的左边），看到的结果应该是右图所示。发现规律了吗？字母（列号）不变，数字（行号）发生了变化。将A1公式右拉，结果如下图所示，字母（列号）变，数字（行号）不变。复制A1单元格，粘贴到其他任意单元格，你会发现：公式所在单元格的位置改变，引用也随之改变。这种引用就是相对引用，可以自己做下图的例子，金额一列的公式就是相对引用的最常见应用。绝对引用技巧分享：按住Ctrl键，按方向下（↓）或者右（→)键，可以快速到达最后一行或者最后一列数据。上一页里我们知道了相对引用，但是很多时候我们需要引用不要随位置发生变化，例如下面的例子：在单元格输入=A1,不要回车，按一下F4功能键，会发现变成了=$A$1，自动多了两个$，回车后将公式右拉，然后再下拉，结果如下图所示，字母（列号）不变，数字（行号）也不变。当然，你也可以直接输入=$A$1，这样就不用按F4了。F2的公式可以写作：=B2*C2，这没有问题，但是公式向右拉就出错了，G2的公式变成了：=C2*D2，在这个例子里，我们希望公式里的B2不要随着公式的位置发生变化，为此就需要用到绝对引用，还是按前页的方法来了解这个问题。相信现在你应该可以解决本页开始的问题了，输入=，点一下B2单元格，按一次F4功能键，继续输入*，再点一下C2单元格，回车右拉完成。如果对于这个问题你已经没有任何问题了，那么可以继续下一页的学习，否则一定要反复测试到成功为止，因为这个技能太重要了！本页重点：掌握F4功能键的用法，初步理解公式中“$”的作用。混合引用这是上一页的问题解决方法，对于商品2，你当然可以重复商品一的做法，F3单元格里写：=$B$3*C3，然后右拉完成，但是如果有一百个甚至一千个商品呢？需要一个一个修改吗？有没有一种更灵活的引用方式来解决呢？答案是肯定的，这就是混合引用。继续用我们自学的方法：就是动手实验法来寻找答案。在单元格输入=A1,不要回车，按一下F4功能键，会发现变成了=$A$1，不要回车，继续按F4，变成=A$1，还是不回车按F4，又变成=$A1，再继续按F4，变成=A1……你可以一直不停的按F4，最终发现，只有四种变化，区别就是在不同的位置出现$，对于没有$的，术语叫做相对引用，有两个$的，术语叫做绝对引用，前面用了两页来说明，只有一个$的，叫做混合引用，而混合引用有两种情况，分别是列号前面有$和行号前面有$，希望能够自己用前面两页的方法，通过下拉和右拉来领略两种混合引用的作用，并且能够用自己的理解总结出一句话，这是你完成蜕变的第一个门槛！老鸟总结如下：行号前有$，下拉不变；列号前有$，横拉不变！相信你的总结一定比老鸟更精辟吧，吼吼~~~本页问题的答案，F2写公式：=$B2*C2，右拉下拉完成，你是不是做出来了呢？阅读本页内容时，可能略有眩晕感，但是请你务必坚持下去，这是提升功力必不可少的一个技能！本页重点：能够熟练运用F4功能键，反复练习理解“$”。技巧分享：按住Ctrl键，按方向上（↑）或者左（←)键，可以快速到达第一行或者第一列数据。R1C1引用样式R1C1引用样式中：R代表行(row)，C代表列(column)，也就是使用“R”加行数字和“C”加列数字来指示单元格的位置，在A1方式下，列在前行在后，在RC模式下，行在前列在后。举例对比：如何切换两种引用方式：“Excel选项”，“公式”分类中的“使用公式”部分下，“R1C1引用样式”前面打钩即可进入RC模式。使用RC模式后，表格里的列号会以数字方式显示。RC模式有什么用：在录制宏的过程中，Excel将使用R1C1引用样式录制一些命令。在某些函数中，例如：indirect，支持RC模式的引用方式，对于一些特殊的引用问题非常有用。阅读本页内容时，如有不适感，可随时进入下一页，待功力较强时再回来温习。如果你刚才在选项里勾选了“R1C1引用样式”。那么提醒你赶快取消这个勾。技巧分享：双击单元格的边缘（鼠标指针出现黑色十字箭头时）也有Ctrl键+按方向键的效果。常量老鸟闲话：不知不觉已经介绍了好多概念，也许你觉得学概念好无趣，老鸟也觉得记那些很累，其实根本不用记的，知道是怎么回事足够了，学excel不是为了应付考试（笔试和机试），如果学excel是为了解决工作中的问题，那么你可以继续坚持，但是会觉得累；如果你觉得excel挺有意思，挺神的，好玩，那么保持你的这种心态，excel不会让你失望，你只会发现更多好玩的东西，终有一天不知不觉你也成为了高手之一！常量是不用计算的值。如果在公式中使用常量而不是对单元格的引用（例如，=30+70+110），则只有在自己更改公式时其结果才会更改。汉字、字母、数字、符号、日期、时间这些都是常量。在A1单元格输入一个1到12之间的数字，B1单元格输入一个1到20之间的数字，C1单元格输入公式：=“今天是”&A1&“月”&B1&“日”，可以根据A1和B1的内容得到一句话，今天是某月某日。这个公式里用了三个常量（都是汉字的）和两个变量，还用了文本连接符&。需要强调的是，在公式里（包括函数里）引用常量时，只要不是数字，常量一律要加双引号（是英文状态的）。技巧分享：按住Ctrl、shift和方向键，可以快速选择连续的非空区域。认识更多的公式=5+2*3将5加到2与3的乘积中。=A1+A2+A3将单元格A1、A2和A3中的值相加。=SQRT(A1)使用SQRT函数返回A1中值的平方根。=TODAY()返回当前日期。=UPPER("hello")使用UPPER工作表函数将文本“hello”转换为“HELLO”。=IF(A1>0,"包含","不包含")测试单元格A1，确定它是否包含大于0值。小测试：以上公式中分别包含了函数、运算符、引用和常量的一种或几种？=D5*60/$D$13*60%这个公式是什么意思？本篇小结公式必须以(=)等号开头。函数的结构：函数名，一对括号，参数，逗号。编辑栏里看到的是真实的内容，单元格看到的是最终显示的内容。在公式里（包括函数里）引用常量时，只要不是数字，常量一律要加双引号（是英文状态的）。本篇见到了这些符号：加（+）、减（-）、乘（*）、除（/）、乘方（^）、百分比（%）、冒号（:）、逗号（,）、连字符（&）、还有最最重要的一个符号（$），成对出现的符号有：小括号（）、中括号[]、单引号‘‘、双引号“”。公式中所有符号都是在英文状态下输入。小测试答案小测试：试将以下算式改写为Excel的公式：算式1：3+(5+20)÷4=3+(5+20)/4算式2：10×[(5+7)÷6]=10*((5+7)/6)算式3：23×（4-2）=23*（4-2）小测试：以下公式中分别包含了函数、运算符、引用和常量的一种或几种？=5+2*3常量、运算符=A1+A2+A3引用、运算符=SQRT(A1)函数、引用=TODAY()函数=UPPER(“hello“)函数、常量=IF(A1>0,“包含”,“不包含“)函数、引用、运算符、常量=D5*60/$D$13*60%这个公式是什么意思？D5单元格的数字乘以60，再除以D13单元格的数字，结果乘以百分之六十（等同于乘以0.6），其中D5单元格是相对引用，D13单元格是绝对引用。你可能遇到的问题单元格输入了1+1，没有得到2，如右图。错误原因：公式少了等号，正确写法，=1+1单元格输入了=1+1，还是没有得到2，如右图。错误原因：单元格为文本格式，检查一下单元格格式是否为文本，如果是文本格式的话，改为常规格式，按一下F2键再回车。如果还是不行，则可能表格为显示公式状态，按住Ctrl键，按一下~（这个键在ESC键的下面，或者点击公式选项卡的显示公式按钮，如右图位置。在用NOW函数的时候发生了错误，如右图。错误原因：函数名写在括号里面了，正确写法：=NOW()输入公式=A1时报错，如右图。错误原因：列号显示为数字，说明表格处于R1C1引用样式，删除错误公式后在选项里去掉“R1C1引用样式”前面的勾，重新输入公式即可。输入公式=32+[5*(6+8)]时报错，如右图。错误原因：括号使用错误，正确写法：=32+(5*(6+8))切换绝对引用时，按F4键没变化。问题原因：需要双击单元格在编辑状态按F4，或者在鼠标点在编辑栏里按F4；如果是笔记本的话，使用功能键可能需要