如何学好函数

梅州

    谈如何学好函数及对常见问题的简单释疑      

— 梅州2021-7-12

前言

  初来论坛学习的朋友们大家好，请允许我写这一篇文章来供您们参考。

  此文分2部分，第1部分是学习方法，第2部分是新手常见问题的简单解释。

  新手常见问题有很多，因精力问题，本人只是列举几个来讲解，如有其它疑问，可以在论坛搜索或者发帖求助。

  通篇内容均根据本人思维所写，未有半点抄袭，水平有限，若有不足之处，请见谅！若对您有半点帮助，请不吝献上鲜花鼓励，谢谢！

第一部分    如何学好函数

（一）打造扎实的基本功，初步可以写出公式

   刚开始学习的时候，应该要熟悉各个函数的用途，懂得

每个函数的参数的写法，要熟记运算符的优先级，数据类型，单元格引用类型等基础知识。

如公式：=-A1^-3% 。在这个公式中，哪部分先算，要熟悉，而且你也要知道应有A1<0；

再比如：=SMALL(A1:A9,ROW(A2)/2)和=SMALL(A1:A9,INT(ROW(A2)/2))

这2个公式下拉表示的都是返回A1:A9区域的第一个最小值，第一个最小值，第二个最小值，第二个最小值……但是第一个公式明显简洁明了，所以熟悉函数的参数用法是可以使得公式更加简洁美观的。

（二）善于发现问题的本质，开始写出精炼高效的公式

   当你熟悉了各个函数的用法时，应该要开始学习如何简化自己书写的公式了。

在论坛里，有不少日常工作中常见的难题，这类难题通常都有一个特点，就是它往往涉及很多的情况，令解答者较难梳理个中关系。这个时候，如果我们想一步写一步公式的话，最后会发现公式写出来很臃肿，这是因为公式里面含有大量重复的判断和计算。

   打个比方，假如有个要求是：若A1<=3，就返回3，

   若A1>=5，就返回5，其余情况返回A1。那么在B1单元格如何书写？

   =IF(A1<=3,3,IF(A1>=5,5,A1))这样写完全可以，但是可以更加简洁。

   我们发现，可以用中位数函数来简化判断，于是我们可以有

   =MEDIAN(A1,3,5)，达到了一个简化公式的效果。这只是一个简单的例子，目的在于让大家知道，发现问题的本质有助于写出高效的公式。

（三）学会温故知新，不断写出更棒的公式

   很多朋友在学习了一个技巧后，喜欢把它收藏起来，慢慢地，就会把这个技巧忘记。

   很多题目的答案，你当时学会了，弄懂了，并不代表你一直都会，随着时间的推移，你会把这个问题的解法忘记，毕竟人脑不是硬盘。

   我们应该要时不时的查阅之前自己收藏的问题，继续思考题目的其它思路，举一反三。

最终我相信，之前对你来说的难题，随着你不断的学习，不断的巩固，最终难题会变成容易的题，你甚至会想出比之前更棒的答案。

   加油！各位在EXCELHOME学习的朋友们O(∩_∩)O。

梅州

第二部分   常见问题的简单解释

（一）EXCEL中常见的空格取值法

假设在某单元格内,有文本它以字符串1,2,3(字符长度分别为x,y,z)和某固定字符相连,现在要分别取出这3个字符串到不同单元格(分裂效果)。

我们有这样一个设想,就是我们不妨用2个很长的空格(a)去取代这个固定字符,然后以一个固定长度(b)去截取,然后用TRIM函数去掉多余的空格,如上图所示。如何确定a,b的取值大小？。

据图我们有：

b>=x,2b>=x+y+a,3b>=2a+x+y+z,b<=x+a,2b<=x+2a+y

整理一下有：

b-a<=x<=b

2b-2a<=x+y<=2b-a

x+y+z<=3b-2a

我们发现并不是需要求出所有的a,b才能进行拉伸截取,我们只需要找出符合条件的一组a,b即可。于是,我们可以令a=b达到一个简化方程的效果(注意：a,b值可以不相等,实际上,满足截取条件的a,b值往往有相当多组,可令a为自变量,b为因变量画出函数图像,图像包围的范围即为a,b的取值范围。),于是上式即变为：a>=x,a>=x+y,a>=x+y+z(因为x>=1),即应有a>=x+y+z。

所以,我们设置一个空格值及截取长度值都等于a的数值,它要大于等于所有字符串的长度总和。显而易见,对于4个,5个甚至更多个字符串连接,我们也可以得出类似结果,就是应该有a>=x+y+z+.........

对此,我们可以就可以截取了：

即在0*a+1处取a,在1*a+1处取a,在2*a+1处取a.........

(就是在前面的总长度上再加1处取)

即开始位置为：

(ROW(A1)-1)*a+1

(ROW(A2)-1)*a+1

(ROW(A3)-1)*a+1

.........

即在

ROW(A1)*a-(a-1)

ROW(A2)*a-(a-1)

ROW(A3)*a-(a-1)

......

处取a长度的字符串。

最终,就有了我们常见的取值公式：

=TRIM(MID(SUBSTITUTE(A1,”,”,REPT(““,99)),ROW(A1)*99-98,99))

当我们目测单元格文本不是很长的时候,我们就可以大致用一个比较大的a值来算,例如99或999。

梅州

(二)EXCEL中常见的超额累进法

有关超额累进法的文章在论坛有很多,它的算法也不止一种,在这里,我根据一种常见的算法给大家讲讲,这个公式究竟怎么来的？

在这里我们设4个(或者更多)区间[0,a1],(a1,a2],(a2,a3],(a3,+∞),每个区间对应的进率为b1,b2,b3,b4,所求值设C,结果为S。

若C落在第1个区间,我们有：

S=C*b1=(C-0)*(b1-0)；

若C落在第2个区间,我们有：

S=a1*b1+(C-a1)*b2+C*b1-C*b1

=C*b1+(C-a1)*b2+a1*b1-C*b1

=(C-0)*(b1-0)+(C-a1)*(b2-b1)

若C落在第3个区间,我们有：

S=a1*b1+(a2-a1)*b2+(C-a2)*b3+C*b1-C*b1+C*b2-C*b2

     =C*b1+C*(b2-b1)+a1*b1-a1*b2+(C-a2)*b3+a2*b2-C*b2

     =(C-0)*(b1-0)+(C-a1)*(b2-b1)+(C-a2)*(b3-b2)

若C落在第4个区间,我们有：

S=a1*b1+(a2-a1)*b2+(a3-a2)*b3+(C-a3)*b4+

C*b1-C*b1+C*b2-C*b2+C*b3-C*b3

那么在这里,我们就可以明显发现规律了，就是b2-b1部分，b3-b2，b4-b3部分明显可以提取出来

于是就有了

S=(C-0)*(b1-0)+(C-a1)*(b2-b1)+(C-a2)*(b3-b2)+(C-a3)*(b4-b3)

我们发现4个区间的公式有着高度相似性,右边部分都是常数部

分b1,b2-b1,b3-b2之类的。如果C是在第4区间,就用上式求得

结果,如果C是在第3区间,那么+(C-a3)*(b4-b3)这一部分我们

就不要了,那么C是在第2区间,那么+(C-a2)*(b3-b2)+(C-a3)*(b4-b3)这一部分我们也不要,如果C是在第1区间,那么我们+(C-a1)*(b2-b1)+(C-a2)*(b3-b2)+(C-a3)*(b4-b3)这一部分也舍去。

我们怎么去判断哪部分要舍去呢？可以发现,如果C在第3区间,C-a3是小于等于0的,C在第2区间,C-a2,C-a3都是小于等于0的。所以,我们就用TEXT函数的”0;!0”来把小于0的部分变为0,等于舍去。不过要是有小数的,如时间类的算法,那么我们就要改为”[<]!0”。

对于更多的区间,我们也可以得出类似的结果。于是,就有了我们常见的超额累进公式

=SUM(TEXT(A1-{0,a1,a2,a3},"0;!0")*{b1,b2-b1,b3-b2,b4-b3})特别的,如果b1,b2,b3,b4为等差数列,公差为D,A1>=0，还可以把公式简化成：=A1*b1+SUM(D*TEXT(A1-{a1,a2,a3},"0;!0"))

梅州

(三)EXCEL中常见的去9法

在一些数据校验算法（如银行卡的Luhn校验算法）中常见到这个公式,下面我来给大家简单的讲解一下其中原理,即反复去9原理。

如8→8,38→11→2,8983→28→10→1,999→27→9.......

等等(即把各个位数上的数字反复相加,直至结果为1位数)

在这里我简单的假设有一个4位数e=abcd(或任意正整数),设各个位数上数值反复相加的最终结果设为j(1<=j<=9)。

则e=a*10^3+b*10^2+c*10^1+d*10^0

   =a*(999+1)+b*(99+1)+c*(9+1)+d

   =(a+b+c+d)+9*(a*111+b*11+c)

设f=a+b+c+d

一、若MOD(e,9)=0,则明显有MOD(f,9)=0,即f是9的倍数，那么反复将f去9后必有j=9。

二、若MOD(e,9)<>0,则我们反复将f去9,最终会剩余一个1到8之间的整数,即可得到j=MOD(e,9)。

我们将2个情况合并一下：

第一种情况,我们把e减去1,那么f反复去9后j=8,那么j+1=9.

第二种情况,若最终j=1,则在去9之前的数值必为10的n次方,那么减去1必为9的倍数,那么此时有MOD(e-1,9)=0,加1即为结果1.若最终j<>1,即2到8,那么在去9之前的数值减去1后反复去9后就变成1到7,加上1后即为2到8。

所以就有了我们常见的判断公式=MOD(A1-1,9)+1。

梅州

（四）EXCEL中常见的MOD函数详解(数轴旋转法)及其简单应用

新手在学习MOD函数之初应该会遇到以下几个疑问：

疑问1：为什么-3除以2是商-2余1，为什么不能是商-1余-1？

疑问2：为什么余数的符号要跟除数相同？为什么相同？可以不相同吗？

疑问3：为什么2除以-1是商-2余0，那0的正负号跟除数-1不同呀？

带着种种疑问，下面我来简单的说一下个中原理及用数轴旋转法来直观快速的理解这些问题的答案。

首先大家知道求余数在数学中一开始只是作为正整数的一种运算法则，比如9/2=4……1呀，那么在EXCEL中，可没那么多要求，可能是正数也可能是负数，那么如何去定义运算规则呢？

在正整数中的运算法则是：MOD(A,B)=A-INT(A/B)*B，A为被除数，B为除数，这个公式也非常好理解，那么对于负数运算，也要按照这个规则计算，即要统一。INT(A/B)这一部分是关键，INT表示向下取整，何为向下？2.1到2是向下，那-2.1到哪里是向下取整呢？就是-3，只要弄懂了这个，就容易求得MOD函数的结果。不要觉得奇怪，这个结果非常自然，向下就是往小的方向去舍入。

现在按照此规则推导余数与除数符号将相同。

一、设A,B为被除数与除数，商为C，余数为D，其中B不为0

  （一）若MOD(|A|,|B|)=0,则依据定义有D=0，此时0与B的正负号将不同（所以常说的余数与除数正负号相同是在不整除的情况下才成立）；

（二）若MOD(|A|,|B|)<>0;

    1.若A>0，B>0，则明显C>0,D>0，即与B符号相同；

    2.若A<0，B<0,则明显C>0,D<0,即与B符号也相同；

    3.若A>0，B<0，则因为有|A/B|<|INT(A/B)|的，所以有

    A<B*INT(A/B),即有余数需要小于0，即与B符号相同。

    4.若A<0，B>0，则因为有|A/B|<|INT(A/B)|的，所以有

    A>B*INT(A/B),即有余数需要大于0，即与B符号相同。

我们可以根据数轴旋转法来直观的看这个结果。

如下图所示：

我们首先看MOD(-3,2)的结果，就是2怎么变到-3。我们根据|A|<|B*INT(A/B)|的性质可以发现，首先将2逆时针旋转180到-2的位置，此时还需继续伸缩整数倍（INT)，超过或等于（等于的情况是整除的情况）-3，也就是再伸缩2倍，最后往正半轴方向再加一个单位即可得-3。

所以至此，我们就可以根据这种方法得出计算方式，即：

1. 除数在数轴上的线段往被除数所在半轴进行旋转；

2. 伸缩整数倍后的绝对值要大于等于被除数的绝对值。

我们来看看一些具体的计算规则：

（1）如：MOD(-5,6)，6怎么变-5？往负半轴旋转（乘以-1），再往正半轴方向加1，即商-1，余1；

（2）如：MOD(-22/24,1),1怎么变-22/24？1往负半轴旋转（乘以-1），再正半轴加2/24，即商-1，余2/24;

（3）如：MOD(6,-6),-6怎么变到6？-6往正半轴旋转（乘以-1），刚好相同，余数为0，即商-1，余0；

（4）如：MOD(0.9,-0.11),-0.11往正半轴旋转（乘以-9,乘以-1再乘以9，要保证最后的长度大于0.9），往负半轴减0.09，即商-9，余-0.09；

（5）如：MOD(5.8,-5.7),-5.7正半轴旋转（乘以-2）,得11.4,11.4减去5.8等于5.6，即商-2，余-5.6，即往负半轴减5.6。

（6）如：MOD(-4.99,1.23),这里乘4为4.92不够，只能乘以5，所以商为-5，余数为+（5*1.23-4.99）=+（6.15-4.99），即1.16（即往正半轴加1.16）。

我们来看一下实际应用，比如求跨(同)天时长(小于24小时)

例如算今晚22:00至明天凌晨02:00的时长,即2/24-22/24=-20/24,若是同天的例如6:00到20:00,即20/24-6/24=16/24。

即跨天就加1,同天就不变,我们发现同天的可以用MOD(A1,1)保留原值,那么跨天可以吗？我们可以发现：设N为跨天时长相减的结果,因MOD(N,D)=N-INT(N/D)*D,若D=1,因跨天(小于24小时)的相减结果为(-1,0)之间的数值,则有INT(N/D)=-1,即有MOD(N,1)=N--1=N+1。所以跨天的也适合此方法，于是就有了我们常见的公式=MOD(A1,1)。

梅州

(五)EXCEL中常见的虚数函数及其简单用法

    大家经常可以看到一些虚数的乘除法来进行简单的求和,下面我就简单的讲解一下个中原理及一些简单用法。

首先讲解一下虚数的由来,虚数字符i是取英文单词Imaginary(形容词,虚构的,想象的意思)首字母来的。EXCEL中的常见的虚数函数IMDIV,IMSUB,IMREAL也是取首2个字母(IM)加用法单词如

除法Division(DIV),减法Subtract(SUB),求和Summation(SUM)等等结合而成。

虚数最早来源于解方程中出现的问题,如：根号-1,大家知道,在实数范围中,没有数的平方是等于-1的。所以为了扩充数系,让更多的问题得到合理解释，就把i^2=-1变换了形式,变为1*i*i=-1,视为把乘以i定义了数轴上的1逆时针旋转90°,即数轴上的1逆时针旋转90°再旋转90°即为-1,给虚数定义了几何意义,进一步把原来只用一条数轴表示的实数系扩充为二维的复数系。

在此基础上根据(1+1/n)^n取极限为e（EXCEL中的EXP函数）,即有e^i表示旋转1弧度（EXCEL中的RADIANS函数）,那么e^πi即为旋转180°(到-1点),于是有了欧拉公式e^πi+1=0，在通信行业的傅里叶变换中，欧拉公式能起到很大的简化作用。

在虚数（复数）的基础上（二维）进一步发展就有了更为高级的四元数（空间），四元数在航天领域有着重要作用。

经过了简单的介绍，现在我们就来简单讲解一下虚数：

形如a+bi的数我们就叫复数，其中a（实部），b（虚部）为实数，i^2=-1，b=0就是实数，a=0就是纯虚数，a，b均不为0，我们就叫它为复数或者虚数。

而在EXCEL中,我们主要用到的还是虚数函数中的一个最为基本的特点,就是i^2=-1，其实之前说那么多，只是让大家知道虚数其实并不虚也并不是毫无用处，它仅是一个事物的另一种表现形式罢了。

简单用法一  求值

表格中经常会有类似a-b或a+b这种的字符串,我们可以&"i"

进行一些虚数运算(注意时间类的字符串&i也能被正确识别为虚数)。

(一)a-b型(如：3-38,8:00-13:30)

求a+b,令(a-bi)*(1+i)=a+b+(a-b)i(IMREAL取实部+IMPRODUCT相乘)

求a-b,令(a-bi)*(1-i)=a-b-(a+b)i(IMREAL取实部+IMPRODUCT相乘)

求b-a,令(a-bi)*(i-1)=b-a+(a+b)i(IMREAL取实部+IMPRODUCT相乘)

(二)a+b型(如：3+38,8:00+13:30)

求a+b,令(a+bi)*(1-i)=a+b+(b-a)i(IMREAL取实部+IMPRODUCT相乘)

求a-b,令(a+bi)*(1+i)=a-b+(a+b)i(IMREAL取实部+IMPRODUCT相乘)

求b-a,令(a+bi)*(1-i)=a+b+(b-a)i(IMREAL取虚部+IMPRODUCT相乘)

在实际计算过程中,算法很灵活,上述方法只是作为一个参考。

例如：我们在乘以1+i的时候,我们也可以用IMDIV(虚数,"1-i")*2的方法来实现。假设这个虚数是a,那么a*2/(1-i)=a*2*(1+i)/(1-i)/(1+i)=a*2*(1+i)/(1--1)=a*2*(1+i)/2=a*(1+i),毕竟IMDIV比IMPRODUCT看上去稍微简短点。

简单用法二  判断区间

经常可以遇到一些类似于120-310的字符串用来表示整数120到310之间的数,现在我们要判断一个数值在不在这个区间内,也同样可以借助虚数函数来进行一个简洁的判断。

设有一字符串S=a-b,其中b>a,现有一值c,我们需要判断c属不属于区间[a,b]。

令S&i变为虚数d=a-bi,c也变为虚数e=c&-c&i=c-ci。

令f=IMSUB(e,d),记f的实部为g,虚部为h；

若c属于[a,b],则有g>=0,h>=0;

若c<a,则有g<0,h>0;

若c>b,则有g>0,h<0。

于是我们就可以发现,c不在[a,b]区间f必会出现负号"-"。

所以就可以利用FIND("-",f)来进行正确区间的判断,大致结构为(例如返回正确区间,当然也可以判断返回正确数值,灵活应用)：

=LOOKUP(,0/ISERR(FIND("-",IMSUB(需判断的值(虚数结构),所有区间(虚数结构)))),所有区间)。

梅州

(六)EXCEL中常见的时间间隔计算

此篇分2部分，一是同天时间计算，二是跨天计算。

描述均采用线段表示法。

一、同天时间计算

图一：（数字0点记为大写字母O点）

   

图二：

  

在开讲以前，我们先看看图二，我假设红色区域加黑色区域里面总共有5人，其中红色区域有3人，那么黑色区域有几人呢？那肯定是2人嘛对吧，那我再问你，2个区域加起来长得帅的人一共有3人，红色区域里帅的有1人，那么黑色区域里帅的呢？那肯定是3-1=2人嘛。其实时间类的算法也是根据这个来进行相减的，就是总的符合值减去前半段的符合值就是后半段的符合值。

（一）计算线段EB+CF的和(区间内)

    我们根据图一来进行展开联想，我们先求E点以前包含AB段、CD段的线段长有多少，明显是AE-BE+CE-DE，F点以前包含AB段、CD段的线段长是AF-BF+CF-DF。然后两段一减就是所求结果。

    但在实际情况下，E点可能在A、B、C、D点左侧，那么这个时候，我们只需令线段长为负值的返回0即可，例如线段BE是正值，EB就为负值。

所以E、F点前符合的值先用TEXT函数表示出来就是：

=TEXT({OE,OF}-{OA;OB;OC;OD},"[<]!0")*{1;-1;1-1}

因为减去一个数，就是加上它的相反数(即乘以-1)和减法性质(即(a+b+c)-(e+d+f)=(a-e)+(b-d)+(c-f))，所以我们就可以把{-1,1}乘进去，再相加，最后的结果就是：

EB+CF=SUM(TEXT({OE,OF}-{OA;OB;OC;OD},"[<]!0")*{1;-1;1;-1}*{-1,1})

（二）计算线段BC(区间外)

根据第一部分的理解，我们就很容易得出一模一样的结果：

BC=SUM(TEXT({OE,OF}-{0;OA;OB;OC;OD},"[<]!0")*{1;-1;1;-1;1}*{-1,1})

二、跨天时间计算

因为跨2天及以上的，中间的时间不会变，所以我们只算头尾2天（跨1天）的差值再配合所跨天数的总符合值运算即可求得结果。

如图所示：

因为跨天的区间内外的算法原理都一样，这里就不再赘述区间外的算法，就简单讲解一下区间内的计算过程。

如图，我们可以把黄色箭头部分视为一辆会伸缩的小火车，总长24小时，从车头在24点位置向右移动。。

现分三种情况：

1.头到第二天到E点，那么明显第一天的EB+CD加上第二天的AE就等于AB+CD的长度,即结果是：AB+CD；

2.头缩到第二天E点左侧的F点，那么有EB+CD+AF的值等于等于AB+CD减去第一天的AE减去第二天的AF的差的差，即结果是：AB+CD-(AE-AF);

3.头缩到第二天E点右侧的G点，那么有EB+CD+AB的值等于等于AB+CD减去第一天的AE减去第二天的AB的差的差，即结果是：AB+CD-(AE-AB)，即：AB+CD+(AB-AE)。

综上，就容易得到跨多天的区间内算法，设开始日期为S，结束日期为Y，则结果为：

(INT(S)-INT(Y))*(AB+CD)-SUM(TEXT({OE,24F或24E或24G}-{OA;OB;OC;OD},"[<]!0")*{1;-1;1;-1}*{1,-1})

注意：这里是{1,-1}不是{-1,1}。

跨多天的区间外算法类似。

     怎样？看完是不是觉得很简单呢？如果满意了，请献上几朵小花，毕竟手打不容易咧。

  

梅州

哎，我用word文档写的，没想到复制进来字体，大小，颜色，填充色全都变了。。好难看。。。。
大家将就看一看吧

丢丢表格

支持一下。（今没分了，再补上）

jivy1212

一看吓一跳，牛牛牛！我只是个学车时候记着教练说的“左打死，然后到看到那个点了再回正.....”，后面学会了就凭感觉打方向就是了，不会去纠结为啥要这么打的...