excel一起学习函数与公式一.docx

excel一起学习函数与公式一.docx

《excel一起学习函数与公式一.docx》由会员分享，可在线阅读，更多相关《excel一起学习函数与公式一.docx（50页珍藏版）》请在冰豆网上搜索。

excel一起学习函数与公式一

目录

前言3

一、初识函数4

1-1初学07版的相关问题4

1-2桩功练习5

1-3函数初识5

1-4自学函数6

1-5编辑公式之公式中的字符8

1-6编辑公式之鼠标点选8

1-7编辑公式之函数提示工具9

1-8编辑函数公式的策略10

1-9审核公式11

1-10飘逸的剪切12

1-11璀璨的项链，灵动的挂坠13

1-12格式转换14

1-13功能性符号的作用16

1-14形参表达中的玄机16

1-15参数类型的玄机续：

19

1-16闲谈错误值21

1-17闲谈错误值-续24

1-18玉不琢不成器26

1-19君君臣臣父父子子30

二、嵌入式应用开篇31

2-1名称31

2-2用活名称的秘密35

2-3再识名称-名花无主36

2-4再识名称之谁人不识君38

2-5栖息地40

2-6谁人不识君补充及用活名称的秘密演练44

2-7SUMIF复杂逻辑解析-插曲45

2-8谁人不识君-相约在“公式记忆式键入”46

三、条件格式47

3-1条件格式秀47

3-2条件格式桩功之单元格引用格式50

3-3条件格式桩功250

3-4桩功点评及思维上的进阶51

3-5管理器简介52

3-6绚丽落幕55

四、数据有效性56

4-1数据有效性桩功演练58

4-2公式切入口62

4-3管理面板简介64

4-4有效性应用两三例69

五、筛选71

5-107版筛选秀-让你一见倾心71

5-2高级筛选（筛选不重复系列）73

5-3条件语法与关键字75

5-4关键字之似曾相识76

5-5移花接木，倒打一钯81

六、回帖补充资料83

6-1学生名册83

前言

从今天开始，我会花一定的精力来耕耘这个帖子，争取让她成为大家学好函数公式的一个筑基好帖。

让更多的人来了解函数公式，来感受函数公式的魅力，来分享函数公式给我们带来的快乐。

在身边同事的心目中我是函数公式的高手，其实我掌握的函数并不多，对单个函数的挖掘也并不十分透彻，于是我也很少能写出比较精妙的函数公式，甚至想在函数公式的竞赛区捞点分都比较困难。

看到这里也许你比较失意，甚至有点沮丧--这家伙自己都不怎么样，看来也学不到什么东西---，当然也有些嗅觉特敏锐的同学，一定感觉到这里将来一个小小的转折。

是的，看问题我们需要经常变换着角度来看，大家也可以把上面这些消极的信息转换成积极的：

要成为函数公式高手，并不需要掌握一大堆的函数，也并不要求对单个函数有特别深入的研究。

开心吗？

开心就鼓鼓掌吧，呵呵。

其实，这一点也不假，而且事物是相通的，打斯诺克就是一个很好的例子，一个斯诺克高手的厉害不在于他的某一项技术（比如远台强行拉杆、全台直线球精准度），也不要求他精通各种杆法，他所擅长的是使用自己熟悉的杆法，合理的控制母球，在不经意之间就拿下一个高分，而中间似乎都没有一个特别精彩的进球。

函数公式有很多问题我不明白，有很多知识我也缺乏，但有一点是肯定的，我会将我所知道的毫无保留的传授给你，而且我确信：

当你真正掌握了这些知识，就为成为一个函数公式的高手打下了坚实的基础，而是否能成为真正的函数高手就看你是否愿意帮助大家了！

开心吗？

开心就再鼓点掌吧，呵呵

本贴我定位为筑基帖，使用“筑基”两字我是想以练功的方式来讲函数公式的学习，希望能带来一点新意。

本贴是迎接刚刚从基础版转战而来的朋友的，所以我会从基础讲起，并倡导使用平实的函数公式，说实话很精妙的函数应用我自己都不太会，呵呵。

要学好函数公式，需要做到“静”，不可浮华；还有“敬”，当然啦不是“敬”我，只是我觉得怀着一个“敬”字来做一件事情容易有心得和收获；另外就是“放下”，不要过多的去争，去比，自己有所收获才是最重要的；最后就是“练”，我师爷在初学太极十三字诀中提到总诀就是一个“练”字，函数公式同样是需要“练”的，只有通过一定时间的磨练，那些要记忆的东西才能固化到身体里面，用我的话说就是固化在手指尖上了，这样大脑才能腾出足够的空间来处理更高层次的逻辑关系...那怎么练？

用kevin的话讲就是“在帮助别人的同时提升自己”。

当然啦，需要掌握的函数不是太多，但也不能太少，巧妇难为无米之炊么。

计数我熟悉的常用函数，十个手指头是打不住的，不过加上它们的那些近亲兄弟们就差不多了，呵呵上路吧==================================================================================

脑瓜中一团浆糊，目前的状态就像是从脑瓜中抽了个线头，抽丝...

本资料内容收集于excelhome论坛，其版权归该论坛作者所有。

一、

初识函数

一-1初学07版的相关问题

早期版本的文件优先使用2003版打开

一-2

桩功练习

练拳不练功，到老一场空。

连太极是从桩功开始的，这里我也借鉴一下。

此处介绍的这个“桩功”是贯穿整个函数公式的学练进程的，算是一种基础的能量流，主要是要体会、领悟公式中单元格的引用格式对公式在复制、拖拉，以及日后在条件格式、数据有效性、名称中使用时的规律性。

这个是比较容易领悟的，但需要一定的演练才能将妙处谨记在心，进而能所心所欲的使用。

新建一个工作簿，并在其中一个工作表中码上如下文本“体悟公式中单元格的不同引用方式给公式复制、拖拉带来的影响”，如下图所示。

2桩功练习附件

一-3函数初识

函数，是意味着我们必须编程吗？

初识函数的时候我自己确实有过这样的担心，因为大学时学编程语言做的最多的就是写函数，于是听到函数一词还是挺紧张的。

不过接触函数以后，我欣喜地发现此处的函数公式不是编程，更像是一堆积木，我们要做的就是根据逻辑将这些积木进行拼装组合...

函数可以看成一个处理器或说一个黑箱，我们只要关心它的输入与输出，对里面的具体实现我们无需关心。

这些黑箱能对输入进行某种处理，最后输出结果，这些输入就叫参数（概念上类似数学里函数概念中的自变量），各参数之间用半角的逗号","来划分，输出就是函数返回值，而黑箱可以用一对半角状态的括号“（”，“）”来表示，比较形象，一来是将参数包裹起来，而来就是表达内部处理对外层函数（涉及嵌套时）来说是不可见的，是透明的（这点比较重要）。

当然拉，人有姓，树有名，为了对这些黑箱起个标识作用，就在括号之前贴上了“标签”，这个标签就是函数名。

函数名除用于标识外还起到一定的功能描述作用。

当然啦，就像一个人回到古代，即使用尽最贴切的比喻也难以让古人对三明治有真切的认识，最好的最直接的方式就是带块三明治，然后让古人尝一口，然后告诉他，这就是三明治。

如此，即使了解不是太全面，那也一定真切。

现在我们就来咬一口我们的三明治。

拿vlookup函数开刀。

大家可以下载附件，以更好的理解。

同时看下图,请尝试对以上概念进行对号，以明晰概念。

当然拉，这只是让大家看了下三明治的解剖图，要真正咬它一口还请劳驾下载附件，完成里面的任务。

相信这会给你带来真切的体会。

学习函数，重要的一点就是练，直接去做例子。

这回给你带来丰富的感性认识。

当然，光练还不够，还需要悟。

悟，具体怎么做呢？

最初级的，可以用自己的语言，结合其参数来描述这个函数的功能，使得看到这个函数，就能理解各参数的意义，不用去记住整个语法，但碰到了就能知道怎么使用...切记，不要去背，只要学会对应就行了，尽可能地减轻记忆的负担。

上眼，下图就是给大家准备的三明治，呵呵，咬它一口吧。

3初识函数附件

一-4自学函数

如果把编辑公式比作造句，那么函数好比组成句子的词汇。

如果在一个句子中遇上一两只拦路虎就会对理解造成一定影响，虽然可以揣摩，但毕竟有点隔靴搔痒，尤其大家刚学函数，碰到这样或那样的拦路虎的几率还是非常大的，那怎么办呢？

读书时对付此类拦路虎的方式就是备一本字典，现在也差不多，不过我们用的是帮助。

怎么用呢？

比较简单，比脑筋急转弯“把大象装进冰箱”要容易那么一点，少一步。

把“大象装进冰箱”大致要三步，分别为打开冰箱、将大象放进冰箱、关上冰箱，我们这里只要两步：

定位函数、调出对应的帮助。

哈哈，等于没讲，呵呵。

在“初识函数”中提过，函数就是一个黑箱，定位函数就是要找到那个黑箱，而黑箱就是那一对括号“（”，“）”。

所以第一步要做的就是将光标定位到对应函数的括号内部。

此时，EXCEL就会弹出对应的“函数提示工具”（这个东西非常重要，是编辑公式的趁手利器），就是一个“函数结构示意”，如下图所示。

单击其中的函数名，就会弹出对应的帮助文档，如下图所示。

不过现在想来，初次见面还是要重视一下，大略看一下整个语法讲解也是好的，是否掌握倒在其次，主要混个脸熟。

碍于时间，就不在上图标示中修改了

当然，要查看函数帮助，还可以直接按F1调出帮助窗口，然后再搜索栏中输入对应的函数名即可。

当然啦，方法不在于多，大家只要选一种自己的方式即可。

本人喜欢直接在单元格公式中定位对应函数，然后单击“函数提示工具”中的函数名的方式来查看对应的帮助。

查看帮助时尽量以解决当前问题为度，不要想着把帮助中的各种细节都掌握。

欲速则不达。

运用函数以解决实际问题为首要，当实际应用时发现新的问题，这时带着问题再看帮助就能牢牢的记住其中细节的意义，进而提高对这个函数的认识...多磨砺几遍，这些细节就不需要去记了，不用记就不会忘。

要真正咬一口三明治，那么还请下载附件，直接在单元格上演练一把。

里面虽然有很多函数你不认识，没有关系，你可以揣摩，也可以查找帮助，尽可能的克服这些拦路虎。

碰到哪个函数就消化那个函数，当然这里的消化层次定位在能应用其基本功能。

掌握了“自学函数”的方式，那么应用时只要记住函数名就可以了，函数名一般与其功能对应，因此记忆量锐减，得到函数名我们就能顺藤摸瓜掌握这个函数的基本用法，当然啦，最好是看到这个函数，根据函数提示工具就能摸索着使用这个函数。

如果每次都要求助帮助，那么在思路上就会不断打断...

4自学函数附件

一-5编辑公式之公式中的字符

公式中功能性的字符，比如表征黑箱的左右括号"（","）",间隔参数的逗号",",用来封装文本的双引号“"”等等，最好都使用半角状态的字符。

虽然，有些时候EXCEL会帮我们进行智能的转换，但养成好习惯还是值得的。

实战时，将输入法切换至英文状态即可。

而这一法则，即"功能性字符使用半角状态的字符"不仅仅适用于公式，养成习惯必将免去不少麻烦。

初学时，另一个常犯的错误就是...卖个关子，看这个公式，大家看了就知道，也许你也同样犯过错误，呵呵

“=if（a1>60,及格,不及格）”

刚开始，我左看、右看我，上看、下看，可是我怎么看都没有发现哪里不一般？

不一般....

但是，Excel返回错误值:

#NAME?

EXCEL是说，公式里用了名称（NAME）?

，但它怎么就没发现定义过这个名称，问我是否搞错了？

当然，刚开始，看到错误是不会去理会的，错了就直接盯着公式看，看看哪里是否多了什么头发丝，呵呵。

其实，其实这里"及格"、"不及格"这两个参数少了双引号来封装。

当缺少双引号封装时，EXCEL就会将其当成“名称”处理，而检索“名称”列表如果未发现有此名称，就报上面的错误了。

这种错误初学时会犯，即使熟练后待头昏脑胀时依旧会犯，而且还查不出错误来，呵呵。

所以这个习惯一定要养成。

顺便透露一下，真正的"名称"在编辑公式时字体是彩色的，那些单元格引用也是彩色的，所以鲜亮的一般是没有问题，我们查错误时可以把重点放在那些黑白的字符上，看看是否缺了双引号“"”，另一种可能就是把函数名拼错了，不过同样符合上面的方向，函数名都是黑白的。

一-6编辑公式之鼠标点选

编辑公式，初学函数时会发觉编辑公式是一件头疼的事情。

码字！

尤其要输入大量类似“$A$1”的单元格引用地址时，更是叫苦不迭！

其实，编辑公式并非一兵一卒都需要纯手工输入，鼠标、“函数提示工具”，复制粘贴、F3粘贴名称（先可以不理会这个概念）、等手段都可以极大的提升编辑效率。

例如上图F1单元格中的公式：

“=A1+C1*E1”，这是一个非常简单的公式，但如果每个字符都需要手工输入，那么也是件繁琐的事情，当遇到公式字符几十甚至上百时更是件苦参事。

我相信很多同学不怕苦，这很好，但有些事情还出力不讨好的。

对于上百字符的公式，如果纯手工码字方式来编辑，那么输入差错就会让人崩溃...公式会不断报错。

其实，这个公式可以如此编辑：

F1中输入“=”，进入公式编辑状态，用鼠标单击单元格A1,此时字符A1被直接添加至F1单元格中光标活动位置，此时公式变成：

“=A1",而活动光标顺延其后，这里的关键就是使用鼠标单击动作替代了输入字符A1,这样极大的提高了输入准确率，尤其当需要变换单元格引用格式，结合F4功能键，就能简化"$"的录入。

依照该方法，可以比较快捷准确的录入上述公式。

详见动态图：

使用鼠标点选单元格来提取单元格地址，除了便捷外还有一个特点就是格式准确。

其格式准确的优点在下文介绍INDIRECT函数时还会讲到，是一项非常实用的技巧。

5编辑公式附件

一-7编辑公式之函数提示工具

手术台可以用来砧板，但只把手术台拿来当砧板就不能物尽所用了。

前文介绍，通过"函数提示工具”的函数名可以用来访问函数帮助，但“函数提示工具”的作用却远不止于此。

编辑公式时，在输完函数名及其左扩号时，对应的“函数提示工具”就会弹将出来，如果没有那很可能是你输入的函数名有误（这个也很重要，不是吗，呵呵），并且第一“形式参数”字体加粗变黑，当输入“,”后，第二“形式参数”加粗变黑。

这一点在编辑嵌套关系比较复杂的公式时相当有用，有定位指示的作用，否则自己都会迷茫，不知道在编辑哪个内容了。

请看下图，注意输入函数的"（"时弹出对应“函数提示工具”，当输入函数的"）"时关闭对应的“函数提示工具”，（如果有）弹出外层函数的“函数提示工具”，当输入参数分隔符“,”号时，相应的提示工具的形式参数字体加粗变黑。

以上这点只要心细留意就会发觉，作用方向为编辑公式对“函数提示工具”的影响。

反方向的应用同样具有极强的实用性。

单击“函数提示工具”上的形式参数，就会选定对应的参数字符，这是一项非常有用的功能，不仅可以用来审查嵌套层次是否符合编辑思路，如下图所示：

同时使得可以直接使用CTRL+C来复制代码段。

如果在同一公式中对某段代码要多次使用，那么这是相当有用的。

虽然可以使用鼠标来手动选中目标代码，但这大大增加了出错的概率，而且会打断思路。

---这是个小技巧，但非常实用。

如下图所示。

插一句，让我在没有“函数提示工具”的EXCEL版本上编辑公式，我常常崩溃...写完公式后就被拖入无尽的查错恶梦中。

F3功能键，按F3功能键就能弹出当前可用的名称（“名称”可以理解为一个变量，封装了一个公式），然后就可以直接使用鼠标选取需要的名称到正在编辑的公式中了。

这么做同样是提高了录入时的准确率...同时，另一个好处依然是不会打断思路，因为不用去思考到底为某个特定功能的公式取了什么名字。

这里提到了名称，蜗牛为梦想而生，对于名称我的感觉就是“名称”为函数公式而生。

大家先有个印象，以后碰到了要和名称多亲多近，搞好关系。

名称用多了，名称会保佑你的！

最后再介绍一个功能键，F2，F2的功能就是进入编辑状态，虽然在单元格编辑公式时显示不出有什么用，当在基本菜单功能的对话框编辑公式时就比较有用。

省去了腾出手去控制鼠标进入对应公式编辑框中，这看上去没有什么，但相信我，以后你会觉得很好用的。

一-8编辑函数公式的策略

关于编辑函数公式前面已讲了函数公式中的字符选用、码字、审核结构的技巧，这里将一下策略。

对于比较简短公式策略师用不到的，当需要编辑的公式比较复杂，需要中间打断一下的时候策略就比较有用。

这里的策略是指构建函数公式时的一种停顿处理。

在编辑一个复杂的公式时，如果没有编辑完成，但又已经编辑了很大一段，这个时候如果确实需要停顿一下也是很正常的。

但这个时候按公式编辑栏的√吧，直接报错，按×吧，先前努力付之东流...进退不是，甚为苦恼呀。

如果单纯是需要停顿，有一个比较简单的方法，方法简单，但比较解决问题，在公式前导符“=”之前插入一个空格即可。

如此，单元格的格式属性就变了，原先是功能性的公式，现在就变成了普通文本，这样就为中场休息提供了保障。

也许有些同学会说，“倒，这也算技巧？

”，呵呵，确实比较简单，但还是挺有用的。

第二，不是时间上需要停顿，而是思维上需要停顿，这个也是很正常的。

在写一个比较负责的条件公式时，分支多，层次深，很难一下子把公式全都书写到位的，这个时候可以使用“空文本”的方式先来填充，或者使用一段有意义的字符串先来占一个位，这个也是非常重要的。

这样就在思维上将一个复杂的问题切割成几块来解决，降低了思维上的难度。

在编写函数公式的过程中，及时将完整的可重复利用的模块用名称封装起来也是值得提倡的。

可以先有个印象，以后介绍名称后可以尝试。

在书写复杂的公式时，你会对这句话有深一度的了解。

那时很可能先把小的逻辑模块封装好，再使用这些小模块来堆砌我们的函数公式的。

===========================

此处没有补上贴切的图，征集呀....因为根据经验，后来自己是没有心思处理细节的，不过也不一定，呵呵

一-9审核公式

公式编辑完成，但EXCEL一个劲的报错，这个时候就需要审核公式了。

这时的主要问题就是函数结构层次错误，具体的说就是那些左右括号所在的位置和预想的不同，导致语法审核不通过。

比如说，IF函数它应该有三个参数，那意味着它的括号内部应该只有2个逗号，如果由于某种查错导致一个括号内有4个参数那么它就会报错了。

这时就要审核函数公式的逻辑层次了，或板块层次。

使用什么工具呢？

---“函数提示工具”。

可以从最外层函数开始，利用单击形式参数直接选中对应代码块的特性，查看各形式参数的结构布局是否符合要求...然后依次逐层剥茧，很快就能找到错误所在了。

这是个小技术，但非常有效，多使用几次提高信心后效果更佳！

此处征集使用该方法解决嵌套错误的实例！

当然，要尽量避免这类错误还是有方法的，就是养成良好的书写习惯。

每输入一个函数，输入左括号后直接输入右括号，然后再填充参数。

这样就会大大降低此类错误的发生。

当然，没个人都有自己的喜欢，这个不强求。

如果审核通过，但返回值与预想有差别，那么依然使用以上手段，首选查看函数结构是否符合要求。

如果这一步通过没有问题，那么就需要使用新式武器了，F9功能键。

这就是论坛中常提到了独孤九剑。

对F9，独孤九剑如果没有感性认识的话，请看下图，这是热心的Fehr提供的

这里提一下，在EXCEL中对于公式审核有很多工具，我曾经也使用过其中的“公式求值”，但接触F9功能键后我就只用该工具了。

理由：

简单方便，让我觉得自己是一个剑客，而不是一个机械化工具的操作员。

工具的关键不是在于它有多强大，而是用得趁手，简单有效。

伏羲造八卦，只用了3位二进制数字，用了最简单的符号，因为太过复杂的东西对大多数人来说是没有意义的。

这里也是一样。

至少到目前为止，所有的审核，纠错我就只使用以上这两个主要工具。

又为大家节省了很多时间爱你学习哪些复杂的审核工具。

好在我们只求实战，如果考试的话估计要吃亏的，但考试真那么重要吗？

F9的使用依然是和手术台（函数提示工具）密不可分的，手术台结合鼠标为其定位攻击方向，锁定目标（直接抹黑选定目标），然后直接F9执行运算，查看是否返回错误值，或者返回值是否合理。

F9和手术台绝对是天生一对，可谓人为鱼肉，我为刀俎呀。

此外，作为一个剑客，还需要犀利的目光。

进入单元格编辑状态时，公式中的各成分会着上五彩的霞光，同时长城内外，大河上下，目光所及，那些被公式引用的单元格及单元格区域也将淡妆浓抹，光艳照人。

凭借这些可以查看，公式的引用区域是否正确，公式成分是否有误...

这里提到的工具相当简单，但假以时日不断磨练很快就能练就一身厉害的厨艺，为你的函数公式历程增添光彩。

需补充一些图片让其更加精彩。

====================================================================================

配合使用函数提示工具和F9可以将逐层对逻辑块求值，这里可能又涉及一个回退的问题，就是怎么将F9求值结果返回为原始公式代码？

记得可以使用CTRL+Z，另一个更彻底地是按公式编辑栏中的叉叉，这样一次性返回到原始状态--即纯函数公式状态。

其中那个单击叉叉×，可以用ESC键替代。

这些都是小技巧，但很实用。

一-10飘逸的剪切

在前文桩功练习中，我们充分体会了公式复制时，其引用的单元格地址会根据不同的引用方式而随之变化。

经过“函数初识”篇的附件练习，相信这一特性你已经掌握得差不多了。

这里再分享一个有趣的事情，以“剪切-粘贴”方式，记住是剪切而不是复制，来对数据源进行重新布局，这个时候我们会发现这个动作居然可以直接修改宿主单元格的公式，奇哉怪哉！

也真是太好了，不需要因为稍稍的变动而一一修改目标公式了。

如图所示。

看到这个现象，你有什么想法吗？

感觉“奇怪，奇怪...”，但就是没有什么想法？

这里可能蕴藏着很深沉，很本质的东西，大家可以想想，猜想，遐想...当然，这个对于成为函数高手没有多大关系，但有点想象力总是好的，生活就不会无趣了。

猜想的东西往往是错的，但只有大胆的猜想才能发现新的东西，这个是更重要的。

这里我抛砖引玉一下，这个也是刚刚想到的：

1.目标公式在提取引用单元格的值时，并不是直接从工作表上按地址行列来取的。

2.引用单元格和目标公式之间是通过第三方关联起来的，这个第三方估计是内存。

3.引用单元格1--链接1--对应内存--链接2--目标公式，剪切时只是链接1得到了更新，链接2是没有断的，所以公式能自动更新单元格引用地址。

4.内存中一定包含行列信息。

仅供参考与讨论。

本节，大家只要感受一下剪切的飘逸即可，到时省得大面积修改公式。

希望你喜欢

6飘逸的剪切附件

一-11璀璨的项链，灵动的挂坠

此处征集赞美词，先胡诌一段。

你是不世的红绳，为珍珠玛瑙牵线；你是绝世的搭扣，成就璀璨的珠链；你是电，你是光，你是唯一的神话，我只爱你，youaremysuperstar!

是你！

成就了函数公式的节节贯穿、变幻莫测、充满灵气。

先罗列一下在函数公式中可以出现的独立成分：

函数、运算式、单元格引用、名称、"字符串"及运算符等。

除“运算符”以外，其他成分可以直接使用&来拼装，组合成一个新的字符串，由于可以自由组装，使函数公式极具智能性。

字符串在函数公式中具有特殊的地位：

1.她是信息的承载者，比如公式“=if（a2>60,"绿灯","红灯"）”中，函数返回值就需要使用"字符串"来承载，这是基础。

2.有很多函数的参数类型是字符串，这就为字符串创造了广袤的用武之地。

比如公式：

"=sumif（a2:

a21,">60",a2:

a21）",其第二参数是字符串，值为">60"。

这里逻辑比较符">"和比较值“60”可以分割成两个更小的逻辑意义，这样可以为公式增添智能性。

由于，A1单元格存储不同的逻辑比较符，b1单元格存储不同的比较值，那么上述sumif函数的第二参数可以如此细化：

">60"=">"&"60"=a1&b1（字符串">60"相当于将字符串">"和字符串"60"连接（&）起来，如果a1单元格承载了比