Hi,我是秋小叶~ 函数公式难吗?难!对新手来说,碰到一个 Excel 问题,随便百度一下可能就会蹦出来好长一串公式,完全看不懂。 对于高手来说,他们不是函数函数公式,而是一环扣一环的问题,一层一层剥下来的洋葱。 看完下面这篇文章,帮你打通利用函数公式高效解决问题的任督二脉。 例如,下面这个公式,一眼看去确实挺吓人: 但是你仔细看就会发现,里面的函数都很简单。 我想说,公式长并不可怕,任何人都可以写得出来。你只要知道里面单个函数的应用规则,就能写出看起来很有逼格的公式来。 今天就来讲讲有逼格长难式吧。 长难式的用处 首先,我认为需要写这么复杂公式的,一定是有大批量,或重复性的数据处理需求。同时,还要保持数据的简洁。 有点「一劳永逸」的味道,才对得起这么努力的思考。我相信学 Excel,就是为了偷懒的(至少我是这样)。 毕竟写一个这样的公式,也是挺繁琐的一个步骤,如果仅仅是为了一次性的特殊数据,那就没必要费这么多功夫了,怎么方便怎么来就好。 如何看懂长难式 在尝试自己写之前,我们先试着看懂长公式。 拆分长公式,就像上学时拆分英语长难句一样,根据语法结构一层层倒推。 对于拆解公式的方法,我习惯叫它「剥洋葱法」。 很好理解,函数的一个重要组成部分,就是括号,一对儿括号就是一层函数。我们顺着括号,由外向内一层一层剥开,就能看出各函数的结构了。
比如下面这个公式: =IF(A2>=90,'A',IF(A2>=80,'B',IF(A2>=70,'C',IF(A2>=60,'D','F')))) 公式中只有一个函数,就是 IF,但是嵌套了很多层。我们用剥洋葱法,剥开每层括号,我在下面用不同的颜色表示。 =IF(A2>=90,'A',IF(A2>=80,'B',IF(A2>=70,'C',IF(A2>=60,'D','F')))) 红色部分是第一层函数也是我习惯说的「最外层函数」,蓝色是第一层函数的第三个参数。 IF(A2>=80,'B',IF(A2>=70,'C',IF(A2>=60,'D','F'))) 同理,紫色部分又是第二层函数的一个参数。 后面的层次一样,直到分解到最内层的函数。 IF(A2>=60,'D','F') 通过整个公式就能看出,它是一个条件判断:
这样是不是就看懂了呢?下面我们就可以自己来写公式了! 函数之间互相嵌套的原则 原则是内层函数须符合外层函数参数的规则! 举个例子说明: 大家都很熟悉的 VLOOKUP 函数,它的第四个参数只有两种情况,TRUE/FALSE(1/0)。 这时候,如果你想要用一个函数来代替,那么你这个函数是嵌套在 VLOOKUP 内,它需要符合 V 函数的条件,也就是这个函数返回的结果,只能是 TRUE 或者 FALSE(1或者0) 。否则整个 VLOOKUP 函数不能返回正确的结果。 长难式如何写 那么,我们该怎么来写这样的公式呢?这样长的公式,看似需要很强的逻辑才能办得到。 其实不然。如果公式较短,思路很容易理清楚,我们可以直接写。如果是像文章一开始的公式那么长,我们可以采用「先分步,后整合」的方法来写。 接下来,我们再看看一开始那个很长的公式。 这个公式是为了实现什么作用呢? 下图 A 列是一个「标准」,C 列是实验得到的结果,我们需要将 C 列与 A 列进行比较,找到与它最接近的一个,返回 B 列对应的「等级」。 还有一个条件,A 列中最接近的那个值,与 C 列结果相差如果绝对值大于 0.3,则不分级,显示「无数据」。 (例子随便写的,仅为提供嵌套函数的分解思路) 这么长的公式,让我一步到位,我也写不出来。 在最终公式前,我一共写了 5 个公式。 G2=MIN(IF($A$2:$A$8>C2,$A$2:$A$8)) (得到A列中,大于C2的最小值) H2=VLOOKUP(C2,$A$2:$A$8,1,1) (得到A列中,小于C2的最大值) I2=MIN(ABS(G2-C2),ABS(H2-C2)) (比较G2和H2得到的结果,与C2差值的绝对值,其中更小的那个) J2=IF(I2<>G2-C2)<>G2,H2),'无数据') (判断这个值是否小于0.3.不符合就返回“无数据”) K2=IF(J2='无数据','无数据',VLOOKUP(J2,$A$2:$B$8,2,0)) (不是无数据的,就查找对应的分级) 最后,将前 4 个公式分别一级一级地带入最后一个,就得到了那个超长的结果。 今日互动 你平时碰到过长难式吗? 快来留言区分享一下! 作者:朱莉,一位 Excel 玩得溜的工程师妹子。 |
|
来自: alayavijnana > 《文案脚本》