excel函数使用实例

由天下分享时间：2024/11/25 21:30:17 加入收藏我要投稿点赞

Excel函数使用实例

唱润刚

1 快捷输入

ALT+178 (M2),ALT+179(M3)

Alt+10(代表excel中alt+enter)

2 excel只能显示小数点后6位,多于6位会自动四舍五入到小数点后1位

（0.0999999会显示成0.1）或整数位（0.9999999会显示成1）。

在自动筛选时，会按实际数据进行大小对比，比如，0.0999999，当筛选条件为大于等于0.1时，这个数据无法被筛选出来，按0.0999999<0.1对待。

3 引用单元格的各种方法对比

1) Indirect(ref_text,a1)

简单引用，使用Indirect($a$1)；

表示列号和行号的方式同时为文本时，使用”a1”样式；

不同时为文本，比如一个是文本一个为引用（或函数），或者两者都是引用（或函数），可以用连字符&，连接列号和行号，如”a”&b1；”a”&row()；a1&”2”；a1&row()。

如：需要引用a1的值，则为=indirect(“a1”)，如果需要使用a1单元格内的内容作为参数，则=indirect(a1&a2),其中，a1中应为a、b等字母，a2中应为数字。

2) Index(reference,row_num,column_num,area_num)

Reference 对一个或多个单元格区域的引用，格式为：(A1:B4,D1:E4,G1:H4)，然后利用area_num参数表示需要返回的区域数，用1、2、3等数字表示。

=index(a:a,1)表示a1单元格。 =index(1:1,2)表示b1单元格。

3) Address(row_num,column_num,abs_num,a1,sheet_text)

abs_num：1或省略，绝对引用；2 绝对行号，相对列标；3 相对行号，绝对列标；4 相对引用

indirect可以返回单一的单元格，没有数组功能 index函数的行号和列号是相对于引用范围而言的

address利用列号和行号就可以引用某个单元格，这里的行号和列号是相对于整个表格的

4 替换掉单元格内空格

1) 保留两个符号中间的一个，去掉前后的所有空格。

因为在trim公式中，两个字符之间有一个单元格的ASCII码值为160，而其他普通空格是32。

=trim()。

2) 替换掉单元格内所有空格

需使用substitute函数，先替换出里面的保留空格。 char(32)普通空格 char(160)特殊空格

CLEAN 函数被设计为删除文本中7位ASCII码的前32个非打印字符（值为 0 到 31）。在Unicode字符集（Unicode：Unicode Consortium 开发的一种字符编码标准。该标准采用多（于一）个字节代表每一字符，实现了使用单个字符集代表世界上几乎所有书面语言。）中，有附加的非打印字符（值为 127、129、141、143、144 和 157）。CLEAN函数自身不删除这些附加的非打印字符。

clean可以清除0到31的非打印符号，所以可用以下公式替换单元格中所有的空格变成非打印符号以清除空格。其中的7可以换成0到31中的任意数。

=clean(substitute(a1,char(32),char(7)))。 5 替换函数有两个：

1) replace()替换掉指定位置的任意字符，将“001-107”数字1加上2。 =REPLACE(D2,FIND(\。

2) substitute()替换掉指定字符：=SUBSTITUTE(A1,\。第三个参数用来确定替换掉第几次出现的指定字符，例中指第三次123，用456替换。如不指定，将替换所有的123。 6 Replace和substitute的区别

例如：随机生成身份证号码中的月、日

=REPLACE(A1,IF(LEN(A1)=15,9,11),4,TEXT(INT(RAND()*(\

利用文本函数得日期格式，返回由rand函数随机产生日期，由替换函数replace替换原来的日期数据。

REPLACE(old_text,start_num,num_chars,new_text) 第二个参数确定从原文本的第几个字符开始搜索。

这里用replace就是因为，它利用旧数据在原文本中的相对位置为依据，替换掉指定数目的数据，符合本题只能确定替换文本的相对位置的要求。

SUBSTITUTE(text,old_text,new_text,instance_num)

SUBSTITUTE函数是利用查找到的文本出现的次数为依据来替换的，比如a在abcabc中出现两次，那么如果只替换第二次出现的a为d，那么函数可以用=SUBSTITUTE(\，返回值为abcdbc。当确切知道要替换的旧数据出现的次数时，用此函数。当然，如果不指定第四个参数，那么它将替换掉所有符合条件的数据。

7 Countif应用，统计两列单元格中的不同

在C1中输入下面公式，找出b中有a中也有的，每有一个重复增加1。 =countif(a:a,b1)

注：countif第一个参数没法对直接输入或者通过引用计算得到的数组进行操作，必须是单元格区域才行。

Countif第二个参数可以设置通配符，但是查找范围内的格式就必须设置成非数字格式。

8 Match函数

最后一个参数为0时，可以设置通配符查找。默认为1，这时需对查找区域排序，否则可能出现错误。

9 将数字进行舍入

将数字向上舍入

要执行此任务，请使用 ROUNDUP、EVEN 或 ODD 函数。

ROUNDup(number,num_digits) 远离零值，向上（绝对值增加的方向）舍入数字。

EVEN 或 ODD都是向上舍入到最接近的偶数或奇数。即返回沿绝对值增大方向取整后最接近的偶数（奇数）。

将数字向下舍入

ROUNDDOWN(number,num_digits) 靠近零值，向下（绝对值减小的方向）舍入数字。

与int功能类似，区别在于，（1）负数时，int远离0舍入，rounddown接近0舍入；（2）它功能更强大一点，可以指定舍入的位数，即规定舍入到小数点前后几位。

INT(number)将数字向下舍入到最接近的整数。

注意，负数舍入时也是向下，即远离0的方向，而正数时是接近0的方向。 3)

将数字舍入到最接近的数字，即四舍五入法

ROUND(number,num_digits) 返回某个数字按指定位数取整后的数字。按绝对值四舍五入。 4)

将数字的小数部分截去，返回整数

TRUNC(number,num_digits) 函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同：TRUNC(-4.3) 返回 -4，而 INT(-4.3) 返回 -5，因为 -5 是较小的数。

将数字舍入到 0 以上的有效位

要执行此任务，请使用 ROUND、ROUNDUP、ROUNDDOWN、LEN 和 INT 函数。 6)

将数字舍入为指定的倍数

MROUND(number,multiple)将第一个参数四舍五入到最接近第二个参数倍数的数，如果数值 number 除以基数的余数大于或等于基数的一半，则函数 MROUND 向远离零的方向舍入。

CEILING(number,significance) 将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。无论数字符号如何，都按远离 0 的方向向上舍入，但是第一个参数和第二个参数的符号必须保持一致，与他相反的函数是FLOOR(number,significance)。

10 统计一列数据（a列）中出现相同次数的数据的个数

=SUMPRODUCT(--(COUNTIF($A$1:$A$10,$A$1:$A$10)=COLUMN(A:A)))/COLUMN(A:A)

输入到b1，向右拖拉，依次能够得到出现1、2、3次等等的数据统计。这里，用COLUMN(A:A)来控制统计的次数，以便实现向右拖拉时可以自动增加。

11 统计一列数据中数字出现的频率

比如1234，和4567，其中在出现一次一栏中显示123567等几个数字。需借助辅助列，在e:l行里面输入公式 =MID($A2,COLUMN(A:A),1)

拖拉到所有区域，最大可以统计8位数据。然后下面为主公式。

=SUMPRODUCT(LARGE(IF(FREQUENCY(LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)=\)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)=\:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)=\

1:10))*10^(ROW(1:10)-1))

上面公式输入到出现一次的栏里面，然后向右拖拉，一次可以显示出现两次、三次，等等。

12 自定义格式中,输入1显示早餐,输入2显示午餐,其余显示晚餐

[=1]\早餐\午餐\晚餐\

13 Ran()函数产生大于等于0小于1的函数，注意上限没有等于

如需要取10±7内的随机数，函数： =10+(rand()-rand())*7

如果10+7不包含10，可以写成： =17-rand()*7

如果是10±7内的随机数，不包含10，

其中odd为四舍五入取奇数（无论数字符号如何，都按远离 0 的方向向上舍入。），目的为了得到正负一。

=10+ODD(RAND()-RAND())*(1-RAND())*7

或者：=10+ODD(RANDBETWEEN(-1,1))*(1-RAND())*7 14 将“001”“001-107”数字前后都加上2，变成“003-109”

=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,\+2,\