ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: 胡剑0227

[分享] Step By Step,我们一起学习函数与公式(开课了...)

    [复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 13:41 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖已被收录到知识树中,索引项:公式基础
编辑公式之公式中的字符

        公式中功能性的字符,比如表征黑箱的左右括号"(",")",间隔参数的逗号",",用来封装文本的双引号“"”等等,最好都使用半角状态的字符。虽然,有些时候EXCEL会帮我们进行智能的转换,但养成好习惯还是值得的。实战时,将输入法切换至英文状态即可。而这一法则,即"功能性字符使用半角状态的字符"不仅仅适用于公式,养成习惯必将免去不少麻烦。

        初学时,另一个常犯的错误就是...卖个关子,看这个公式,大家看了就知道,也许你也同样犯过错误,呵呵
                                  “=if(a1>60,及格,不及格)”
        刚开始,我左看、右看我,上看、下看,可是我怎么看都没有发现哪里不一般?不一般....
        但是,Excel返回错误值:#NAME?
        EXCEL是说,公式里用了名称(NAME)?,但它怎么就没发现定义过这个名称,问我是否搞错了?当然,刚开始,看到错误是不会去理会的,错了就直接盯着公式看,看看哪里是否多了什么头发丝,呵呵。其实,其实这里"及格"、"不及格"这两个参数少了双引号来封装。当缺少双引号封装时,EXCEL就会将其当成“名称”处理,而检索“名称”列表如果未发现有此名称,就报上面的错误了。
        这种错误初学时会犯,即使熟练后待头昏脑胀时依旧会犯,而且还查不出错误来,呵呵。所以这个习惯一定要养成。顺便透露一下,真正的"名称"在编辑公式时字体是彩色的,那些单元格引用也是彩色的,所以鲜亮的一般是没有问题,我们查错误时可以把重点放在那些黑白的字符上,看看是否缺了双引号“"”,另一种可能就是把函数名拼错了,不过同样符合上面的方向,函数名都是黑白的。

如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:09 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 13:50 | 显示全部楼层
本帖最后由 胡剑0227 于 2011-8-17 13:33 编辑

编辑公式之鼠标点选

        编辑公式,初学函数时会发觉编辑公式是一件头疼的事情。码字!尤其要输入大量类似“$A$1”的单元格引用地址时,更是叫苦不迭!其实,编辑公式并非一兵一卒都需要纯手工输入,鼠标、“函数提示工具”,复制粘贴、F3粘贴名称(先可以不理会这个概念)、等手段都可以极大的提升编辑效率。

        
        例如上图F1单元格中的公式:“=A1+C1*E1”,这是一个非常简单的公式,但如果每个字符都需要手工输入,那么也是件繁琐的事情,当遇到公式字符几十甚至上百时更是件苦参事。我相信很多同学不怕苦,这很好,但有些事情还出力不讨好的。对于上百字符的公式,如果纯手工码字方式来编辑,那么输入差错就会让人崩溃...公式会不断报错。

        其实,这个公式可以如此编辑:F1中输入“=”,进入公式编辑状态,用鼠标单击单元格A1,此时字符A1被直接添加至F1单元格中光标活动位置,此时公式变成:“=A1",而活动光标顺延其后,这里的关键就是使用鼠标单击动作替代了输入字符 A1,这样极大的提高了输入准确率,尤其当需要变换单元格引用格式,结合F4功能键,就能简化 "$" 的录入。依照该方法,可以比较快捷准确的录入上述公式。详见动态图:


        使用鼠标点选单元格来提取单元格地址,除了便捷外还有一个特点就是格式准确。其格式准确的优点在下文介绍INDIRECT函数时还会讲到,是一项非常实用的技巧。


如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:10 编辑 ]

5编辑公式.rar

10.84 KB, 下载次数: 5204

TA的精华主题

TA的得分主题

发表于 2009-12-7 14:10 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 14:26 | 显示全部楼层
本帖最后由 胡剑0227 于 2011-8-17 13:36 编辑

编辑公式之函数提示工具
手术台可以用来砧板,但只把手术台拿来当砧板就不能物尽所用了。前文介绍,通过"函数提示工具”的函数名可以用来访问函数帮助,但“函数提示工具”的作用却远不止于此。
        编辑公式时,在输完函数名及其左扩号时,对应的“函数提示工具”就会弹将出来,如果没有那很可能是你输入的函数名有误(这个也很重要,不是吗,呵呵),并且第一“形式参数”字体加粗变黑,当输入“,”后,第二“形式参数”加粗变黑。这一点在编辑嵌套关系比较复杂的公式时相当有用,有定位指示的作用,否则自己都会迷茫,不知道在编辑哪个内容了。

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




        以上这点只要心细留意就会发觉,作用方向为编辑公式对“函数提示工具”的影响。反方向的应用同样具有极强的实用性。单击“函数提示工具”上的形式参数,就会选定对应的参数字符,这是一项非常有用的功能,不仅可以用来审查嵌套层次是否符合编辑思路,如下图所示:


同时使得可以直接使用CTRL+C来复制代码段。如果在同一公式中对某段代码要多次使用,那么这是相当有用的。虽然可以使用鼠标来手动选中目标代码,但这大大增加了出错的概率,而且会打断思路。---这是个小技巧,但非常实用。如下图所示。



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

        F3功能键,按F3功能键就能弹出当前可用的名称(“名称”可以理解为一个变量,封装了一个公式),然后就可以直接使用鼠标选取需要的名称到正在编辑的公式中了。这么做同样是提高了录入时的准确率...同时,另一个好处依然是不会打断思路,因为不用去思考到底为某个特定功能的公式取了什么名字。

        这里提到了名称,蜗牛为梦想而生,对于名称我的感觉就是“名称”为函数公式而生。大家先有个印象,以后碰到了要和名称多亲多近,搞好关系。名称用多了,名称会保佑你的!
        
        最后再介绍一个功能键,F2,F2的功能就是进入编辑状态,虽然在单元格编辑公式时显示不出有什么用,当在基本菜单功能的对话框编辑公式时就比较有用。省去了腾出手去控制鼠标进入对应公式编辑框中,这看上去没有什么,但相信我,以后你会觉得很好用的。

如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:10 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 14:42 | 显示全部楼层
编辑函数公式的策略

        关于编辑函数公式前面已讲了函数公式中的字符选用、码字、审核结构的技巧,这里将一下策略。对于比较简短公式策略师用不到的,当需要编辑的公式比较复杂,需要中间打断一下的时候策略就比较有用。
        这里的策略是指构建函数公式时的一种停顿处理。在编辑一个复杂的公式时,如果没有编辑完成,但又已经编辑了很大一段,这个时候如果确实需要停顿一下也是很正常的。但这个时候按公式编辑栏的√吧,直接报错,按×吧,先前努力付之东流...进退不是,甚为苦恼呀。
        如果单纯是需要停顿,有一个比较简单的方法,方法简单,但比较解决问题,在公式前导符“=”之前插入一个空格即可。如此,单元格的格式属性就变了,原先是功能性的公式,现在就变成了普通文本,这样就为中场休息提供了保障。也许有些同学会说,“倒,这也算技巧?”,呵呵,确实比较简单,但还是挺有用的。
        第二,不是时间上需要停顿,而是思维上需要停顿,这个也是很正常的。在写一个比较负责的条件公式时,分支多,层次深,很难一下子把公式全都书写到位的,这个时候可以使用“空文本”的方式先来填充,或者使用一段有意义的字符串先来占一个位,这个也是非常重要的。这样就在思维上将一个复杂的问题切割成几块来解决,降低了思维上的难度。
        在编写函数公式的过程中,及时将完整的可重复利用的模块用名称封装起来也是值得提倡的。可以先有个印象,以后介绍名称后可以尝试。在书写复杂的公式时,你会对这句话有深一度的了解。那时很可能先把小的逻辑模块封装好,再使用这些小模块来堆砌我们的函数公式的。

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


如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:11 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 14:52 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 胡剑0227 于 2011-8-17 13:38 编辑

审核公式

        公式编辑完成,但EXCEL一个劲的报错,这个时候就需要审核公式了。这时的主要问题就是函数结构层次错误,具体的说就是那些左右括号所在的位置和预想的不同,导致语法审核不通过。比如说,IF函数它应该有三个参数,那意味着它的括号内部应该只有2个逗号,如果由于某种查错导致一个括号内有4个参数那么它就会报错了。
        这时就要审核函数公式的逻辑层次了,或板块层次。使用什么工具呢?---“函数提示工具”。可以从最外层函数开始,利用单击形式参数直接选中对应代码块的特性,查看各形式参数的结构布局是否符合要求...然后依次逐层剥茧,很快就能找到错误所在了。这是个小技术,但非常有效,多使用几次提高信心后效果更佳!
        此处征集使用该方法解决嵌套错误的实例!
        当然,要尽量避免这类错误还是有方法的,就是养成良好的书写习惯。每输入一个函数,输入左括号后直接输入右括号,然后再填充参数。这样就会大大降低此类错误的发生。当然,没个人都有自己的喜欢,这个不强求。
        如果审核通过,但返回值与预想有差别,那么依然使用以上手段,首选查看函数结构是否符合要求。如果这一步通过没有问题,那么就需要使用新式武器了,F9功能键。这就是论坛中常提到了独孤九剑。

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


        这里提一下,在EXCEL中对于公式审核有很多工具,我曾经也使用过其中的“公式求值”,但接触F9功能键后我就只用该工具了。理由:简单方便,让我觉得自己是一个剑客,而不是一个机械化工具的操作员。工具的关键不是在于它有多强大,而是用得趁手,简单有效。伏羲造八卦,只用了3位二进制数字,用了最简单的符号,因为太过复杂的东西对大多数人来说是没有意义的。这里也是一样。至少到目前为止,所有的审核,纠错我就只使用以上这两个主要工具。
        又为大家节省了很多时间爱你学习哪些复杂的审核工具。好在我们只求实战,如果考试的话估计要吃亏的,但考试真那么重要吗?
        F9的使用依然是和手术台(函数提示工具)密不可分的,手术台结合鼠标为其定位攻击方向,锁定目标(直接抹黑选定目标),然后直接F9执行运算,查看是否返回错误值,或者返回值是否合理。F9和手术台绝对是天生一对,可谓人为鱼肉,我为刀俎呀。
        此外,作为一个剑客,还需要犀利的目光。进入单元格编辑状态时,公式中的各成分会着上五彩的霞光,同时长城内外,大河上下,目光所及,那些被公式引用的单元格及单元格区域也将淡妆浓抹,光艳照人。凭借这些可以查看,公式的引用区域是否正确,公式成分是否有误...
        这里提到的工具相当简单,但假以时日不断磨练很快就能练就一身厉害的厨艺,为你的函数公式历程增添光彩。
         需补充一些图片让其更加精彩。
====================================================================================
配合使用 函数提示工具 和 F9 可以将逐层对逻辑块求值,这里可能又涉及一个回退的问题,就是怎么将 F9求值结果返回为原始公式代码? 记得可以使用CTRL+Z,另一个更彻底地是按公式编辑栏中的 叉叉 ,这样一次性返回到原始状态--即纯函数公式状态。其中那个 单击 叉叉×,可以用 ESC键替代。
这些都是小技巧,但很实用。

如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:11 编辑 ]

TA的精华主题

TA的得分主题

发表于 2009-12-7 15:09 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
跟着版主走。。。。

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 15:19 | 显示全部楼层
本帖最后由 胡剑0227 于 2011-8-17 13:57 编辑

飘逸的剪切

        在前文桩功练习中,我们充分体会了公式复制时,其引用的单元格地址会根据不同的引用方式而随之变化。经过“函数初识”篇的附件练习,相信这一特性你已经掌握得差不多了。这里再分享一个有趣的事情,以“剪切-粘贴”方式,记住是剪切而不是复制,来对数据源进行重新布局,这个时候我们会发现这个动作居然可以直接修改宿主单元格的公式,奇哉怪哉!也真是太好了,不需要因为稍稍的变动而一一修改目标公式了。
        如图所示。


        看到这个现象,你有什么想法吗?感觉“奇怪,奇怪...”,但就是没有什么想法?这里可能蕴藏着很深沉,很本质的东西,大家可以想想,猜想,遐想...当然,这个对于成为函数高手没有多大关系,但有点想象力总是好的,生活就不会无趣了。
        猜想的东西往往是错的,但只有大胆的猜想才能发现新的东西,这个是更重要的。
        这里我抛砖引玉一下,这个也是刚刚想到的:
        1.目标公式在提取引用单元格的值时,并不是直接从工作表上按地址行列来取的。
        2.引用单元格和目标公式之间是通过第三方关联起来的,这个第三方估计是内存。
        3.引用单元格1--链接1--对应内存--链接2--目标公式,剪切时只是链接1得到了更新,链接2是没有断的,所以公式能自动更新单元格引用地址。
        4.内存中一定包含行列信息。
        仅供参考与讨论。
        本节,大家只要感受一下剪切的飘逸即可,到时省得大面积修改公式。希望你喜欢。


如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:12 编辑 ]

6飘逸的剪切.rar

13.05 KB, 下载次数: 4429

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-7 16:01 | 显示全部楼层
本帖最后由 胡剑0227 于 2011-8-17 13:49 编辑

璀璨的项链,灵动的挂坠

                                       
        
        此处征集赞美词,先胡诌一段。
        你是不世的红绳,为珍珠玛瑙牵线;你是绝世的搭扣,成就璀璨的珠链;你是电,你是光,你是唯一的神话,我只爱你,you are my super star! 是你!成就了函数公式的节节贯穿、变幻莫测、充满灵气。

        先罗列一下在函数公式中可以出现的独立成分:函数、运算式、单元格引用、名称、"字符串"及运算符等。除“运算符”以外,其他成分可以直接使用 & 来拼装,组合成一个新的字符串,由于可以自由组装,使函数公式极具智能性。字符串在函数公式中具有特殊的地位:
        1.她是信息的承载者,比如公式“=if(a2>60,"绿灯","红灯")”中,函数返回值就需要使用"字符串"来承载,这是基础。
        2.有很多函数的参数类型是字符串,这就为字符串创造了广袤的用武之地。
        比如公式:"=sumif(a2:a21,">60",a2:a21)",其第二参数是字符串,值为">60"。这里逻辑比较符">"和比较值“60        ”可以分割成两个更小的逻辑意义,这样可以为公式增添智能性。由于,A1单元格存储不同的逻                辑比较符,b1单元格存储不同的比较值,那么上述sumif函数的第二参数可以如此细化:">60"=">"&"60"=a1&b1(字符串">60"相当于将字符串 ">" 和 字符串 "60"连接(&)起来,如果 a1单元格 承载了 比较符号 ">",b1单元格承载了 比较数 60,那么就可以表示成 A1&B1了。关键是说,把逻辑细分以后可以更加灵活了。
),这样在无需修改公式的前提下,简单设置A1,B1单元格的内容就能表达新的逻辑意义了。
        3.EXCEL有一族字符串处理函数,比如:left,right,mid,substitute,find,len...使得字符串可以进行精细化加工,这是技术保证。比如,上述单元格B1值为“分数60”而不是直接的“60”,这样有点就是可读性增加,这个时候上述SUMIF的第二参数就不能直接使用 "a1"&"b1",但经过处理可以修改为"a1"&mid(b1,3,100)。这样有了技术上的保证,大大增加了灵活性。
        爱上&吧,她会给你自由。
        这里大家也可以揣摩,很多函数公式的参数类型设置为文本字符串,其中应该也是看上了字符串处理的灵活性。
       当然啦,想戴项链还是下载附件尝试一下吧。

如果您有函数公式方面的任何想法、疑问那么请您直接跟帖,如果有小巧的例子以更透析的说明问题,那么还可以Email至sword0227@189.cn,胡剑需要您的帮助。邮件不一定回复,但胡剑感谢您的支持!永久有效

[ 本帖最后由 胡剑0227 于 2011-1-11 12:12 编辑 ]

7灵动的挂坠.rar

20.5 KB, 下载次数: 5900

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-12-7 16:04 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 17:49 , Processed in 0.056199 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表