新手必学的几个入门函数

文/朱莉

本文系作者授权“清南”发布

今天要提到的这几个函数，有一个共同的特征。就是单独看，它们的功能很简单，但是在进阶的函数嵌套中，却常常出现，实现比较复杂的功能，所以希望大家都能掌握。

 

1、COLUMN/ROW

http://mmbiz.qpic.cn/mmbiz_png/y7DiaalA5WwhVBH5ic9lUMNntqibtTq2D0FYhXU3gxqC3hVIb6kC3XZQxn0D9b5Mc7QomU1gO5UUomUGIcSShjHNg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

列号和行号函数。

=COLUMN([reference])

=ROW([reference])

这两个函数的格式完全相同，第一参数都是可选参数。返回单元格的列号（COLUMN）或行号（ROW）。

省略参数时，返回当前单元格的行号或列号。

=COLUMN(C2)

=3

=ROW(C2)

=2

这两个函数的好处是，当采用相对引用或混合引用时，公式在向右或向下复制后，返回的结果分别加1。我们可以利用这个特性，去构成一个连续的序列。

例如，在INDIRECT函数那一节，我们举了一个例子。将一列数据，转换成多列。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958747101623.jpg

想要达到如下的引用效果：

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958747619193.jpg

我们利用INDIRECT函数，用A+数字的方式凑出单元格引用。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958747856487.jpg

 

C1中的公式为：

=INDIRECT("A"&(ROW(A1)+4*COLUMN(A1)-3))

ROW(A1)就是A1单元格的"行号"，也就是1.

COLUMN(A1)就是A1单元格的"列号"，也是1.

所以，

ROW(A1)+4*COLUMN(A1)-3

=1+4*1-3

=2

=INDIRECT("A"&(ROW(A1)+4*COLUMN(A1)-3))

=INDIRECT(A2)

当公式向下复制的时候，变成

ROW(A2)+4*COLUMN(A2)-3

这其中，ROW(A2)=2; COLUMN(A2)=1

（A2还是在第一列，所以列号不变）

所以整个公式：

ROW(A2)+4*COLUMN(A2)-3

=2+4*1-3

=3

所以当我们需要函数的某一个参数，在单元格中是逐渐递增，就可以考虑这两个函数。

 

2、COLUMNS/ROWS

http://mmbiz.qpic.cn/mmbiz_png/y7DiaalA5WwhVBH5ic9lUMNntqibtTq2D0FYhXU3gxqC3hVIb6kC3XZQxn0D9b5Mc7QomU1gO5UUomUGIcSShjHNg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

这两个函数返回的是一个区域的“列数”和“行数”。

例如：

=COLUMNS(C1:E5)

=3

=ROWS(C1:E5)

=5

讲完下一个函数后，我们再一起举例。

3、LEN/LENB

http://mmbiz.qpic.cn/mmbiz_png/y7DiaalA5WwhVBH5ic9lUMNntqibtTq2D0FYhXU3gxqC3hVIb6kC3XZQxn0D9b5Mc7QomU1gO5UUomUGIcSShjHNg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

LEN函数返回字符串的“字符数”。LENB函数返回字符串的“字节数”。

一个汉字为2个字节、1个字符。数字和英文字母为1个字节、1个字符。

=LEN("Excel小超人")

=8

=LENB("Excel小超人")

=11

下面来看一个例子：

我们需要在票据中，将金额拆开，分别填入到右边的每一格当中。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958748474121.jpg

 

在B2单元格中输入如下公式：

=MID(REPT(" ",COLUMNS($B:$L)-LEN($A2*100))&$A2*100,COLUMN(A:A),1)

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958748755686.jpg

 

然后再向右和向下填充，就可以得到我们想要的效果。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958748997743.jpg

公式讲解：

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函数的第一参数。（不了解MID函数的戳这里）

COLUMN(A:A)返回A列的列号，也就是1，作为MID的第二参数。也就是，从第一参数的第1个字符开始取值。

注意，这个函数用的是相对引用，在向右复制时会发生变化。比如C2单元格中，这个参数就会变成COLUMN(B:B)=2。也就是从第1参数的第2个字符开始取值。以此类推。

MID函数的第3参数，就是1。表示每次取一个字符，固定不变。

总结一下：

这个公式就是将原始的金额的字符数，按照所需填写的单元格列数，在它前面用空格进行补齐。然后再用MID函数一次提取一个数，具体从第几位开始，由第二参数COLUMN(A:A)来决定。

 

4、LARGE/SMALL

http://mmbiz.qpic.cn/mmbiz_png/y7DiaalA5WwhVBH5ic9lUMNntqibtTq2D0FYhXU3gxqC3hVIb6kC3XZQxn0D9b5Mc7QomU1gO5UUomUGIcSShjHNg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

Large/Small函数分别返回一个区域中，第k大/小的数。

=LARGE(array,k)

=SMALL(array,k)

举例，求出下面前三名成绩的总和。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958749242168.jpg

D2中的公式为：

=SUM(LARGE(B2:B9,{1,2,3}))

数组公式，输完记得用Ctrl+Shift+Enter来结束。

其中LARGE函数嵌套了一个常量数组，表示分别提取，由大到小的，第1、第2和第3个数。然后，有SUM函数来求和。（不了解常量数组的戳这里）

 

5、MAX/MIN

http://mmbiz.qpic.cn/mmbiz_png/y7DiaalA5WwhVBH5ic9lUMNntqibtTq2D0FYhXU3gxqC3hVIb6kC3XZQxn0D9b5Mc7QomU1gO5UUomUGIcSShjHNg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

最简单的两个函数，分别求最大和最小值。除了第1参数外，后面的都是可选参数

=MAX(number1,[number2],…)

=MIN(number1,[number2],…)

例如，我们要给销售人员发奖金，奖金的比例为销售额10%，奖金上限为10000元。另外，一组人员的奖金有下限为1000元。

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958749489558.jpg

D2单元格中的公式为：

=MAX(MIN(C2*10%,10000),1000*(B2="一组"))

其中，

MIN(C2*10%,10000)

用来选取销售额的10%，与上限10000的最小值。

1000*(B2="一组")

如果B2="一组",则返回TRUE，也就相当于1.

所以MAX函数，如果是一组人员，就判断第一参数算出的奖金时候比1000大，是，就返回第一参数的结果，否就返回1000.既两者的最大值。

 

-END-

来源公号：Excel小超人（ID；Julie1391）

http://www.cwbgj.com/advance/ueditor/php/upload/20170527/14958750263596.jpg

​https://mp.weixin.qq.com/s?src=3&timestamp=1495874667&ver=1&signature=PjCFIFaFhTxsKBK-ZbfGN37YXWXFnQ3lgRzpZ2Me8SmxnPoyq9KROnajbLc6owHOg9wQoWduooTb3VDlka2UVDOkoCtBNzpV1CgZyRbJhSMv4UrYWKnH7Xkc8x9gaaLEdGT8bpALxYI1GVRCBZYxlDVB6Z4GCtltPfQuTgO6-o0=