Excel小白到大神必学的5个函数,立马让你效率翻倍
来源:Excel 小超人(ID:Julie1391)今天要提到的这几个函数,有一个共同的特征。就是单独看,它们的功能很简单,但是在进阶的函数嵌套中,却常常出现,实现比较复杂的功能,所以希望大家都能掌握。这两个函数的格式完全相同,第一参数都是可选参数。返回单元格的列号(COLUMN)或行号(ROW)。这两个函数的好处是,当采用相对引用或混合引用时,公式在向右或向下复制后,返回的结果分别加1。我们可以利用这个特性,去构成一个连续的序列。例如,在INDIRECT函数那一节,我们举了一个例子。将一列数据,转换成多列。我们利用INDIRECT函数,用A+数字的方式凑出单元格引用。=INDIRECT("A"&(ROW(A1)+4*COLUMN(A1)-3))ROW(A1)就是A1单元格的"行号",也就是1。COLUMN(A1)就是A1单元格的"列号",也是1。
=INDIRECT("A"&(ROW(A1)+4*COLUMN(A1)-3))这其中,ROW(A2)=2; COLUMN(A2)=1所以当我们需要函数的某一个参数,在单元格中是逐渐递增,就可以考虑这两个函数。LEN函数返回字符串的“字符数”。LENB函数返回字符串的“字节数”。一个汉字为2个字节、1个字符。数字和英文字母为1个字节、1个字符。我们需要在票据中,将金额拆开,分别填入到右边的每一格当中。=MID(REPT(" ",COLUMNS($B:$L)-LEN($A2*100))&$A2*100,COLUMN(A:A),1)COLUMNS($B:$L)返回B到L列一共有几列,也就是11,也就是我们要填的区域一共有11列;LEN($A2*100)就是将我们原始的金额扩大100倍,消除了小数点,然后返回字符数。上面两者相减,得到在要填的区域最前面一共有几个空格。所以用REPT函数,按照相减得到的值,重复第一参数" "(空格)。A2单元格的原始金额乘以100后,为6352846563,共有10个字符,所以在填写的区域有1个空格(11-10=1)。REPT(" ",COLUMNS($B:$L)-LEN($A2*100))&$A2*100=MID(REPT(" ",COLUMNS($B:$L)-LEN($A2*100))&$A2*100,COLUMN(A:A),1)然后将上面计算得到的空格,与扩大100倍后的金额连在一起,构成一个字符串,作为MID函数的第一参数。COLUMN(A:A)返回A列的列号,也就是1,作为MID的第二参数。也就是,从第一参数的第1个字符开始取值。注意,这个函数用的是相对引用,在向右复制时会发生变化。比如C2单元格中,这个参数就会变成COLUMN(B:B)=2。也就是从第1参数的第2个字符开始取值。以此类推。MID函数的第3参数,就是1。表示每次取一个字符,固定不变。这个公式就是将原始的金额的字符数,按照所需填写的单元格列数,在它前面用空格进行补齐。然后再用MID函数一次提取一个数,具体从第几位开始,由第二参数COLUMN(A:A)来决定。Large/Small函数分别返回一个区域中,第k大/小的数。=SUM(LARGE(B2:B9,{1,2,3}))数组公式,输完记得用Ctrl+Shift+Enter来结束。其中LARGE函数嵌套了一个常量数组,表示分别提取,由大到小的,第1、第2和第3个数。然后,有SUM函数来求和。最简单的两个函数,分别求最大和最小值。除了第1参数外,后面的都是可选参数=MAX(number1,[number2],…)=MIN(number1,[number2],…)例如,我们要给销售人员发奖金,奖金的比例为销售额10%,奖金上限为10000元。另外,一组人员的奖金有下限为1000元。=MAX(MIN(C2*10%,10000),1000*(B2="一组"))用来选取销售额的10%,与上限10000的最小值。如果B2="一组",则返回TRUE,也就相当于1。所以MAX函数,如果是一组人员,就判断第一参数算出的奖金时候比1000大,是,就返回第一参数的结果,否就返回1000.既两者的最大值。如果这篇文章对你有帮助,就请将它转发到朋友圈,让更多人一起来学习吧!暂时用不到也没关系,可以先收藏起来,需要的时候就很方便查找啦~~
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。