excel使用实例.docVIP

Excel函数使用实例 唱润刚 快捷输入 ALT+178 (M2),ALT+179(M3) Alt+10(代表excel中alt+enter) excel只能显示小数点后6位,多于6位会自动四舍五入到小数点后1位（0.0999999会显示成0.1）或整数位（0.9999999会显示成1）。 在自动筛选时，会按实际数据进行大小对比，比如，0.0999999，当筛选条件为大于等于0.1时，这个数据无法被筛选出来，按01对待。 引用单元格的各种方法对比 Indirect(ref_text,a1) 简单引用，使用Indirect($a$1)； 表示列号和行号的方式同时为文本时，使用”a1”样式； 不同时为文本，比如一个是文本一个为引用（或函数），或者两者都是引用（或函数），可以用连字符，连接列号和行号，如”a”b1；”a”row()；a1”2”；a1row()。 如：需要引用a1的值，则为=indirect(“a1”)，如果需要使用a1单元格内的内容作为参数，则=indirect(a1a2),其中，a1中应为a、b等字母，a2中应为数字。 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单元格。 Address(row_num,column_num,abs_num,a1,sheet_text) abs_num：1或省略绝对引用绝对行号，相对列标相对行号，绝对列标相对引用CLEAN 函数被设计为删除文本中7位ASCII码的前32个非打印字符（值为 0 到 31）。在Unicode字符集（Unicode：Unicode Consortium 开发的一种字符编码标准。该标准采用多（于一）个字节代表每一字符，实现了使用单个字符集代表世界上几乎所有书面语言。）中，有附加的非打印字符（值为 127、129、141、143、144 和 157）。CLEAN函数自身不删除这些附加的非打印字符。001-107”数字1加上2。 =REPLACE(D2,FIND(-,D2)-1,1,MID(D2,FIND(-,D2)-1,1)+2)。 2) substitute()替换掉指定字符：=SUBSTITUTE(A1,123,456,3)。第三个参数用来确定替换掉第几次出现的指定字符，例中指第三次123，用456替换。如不指定，将替换所有的123。 Replace和substitute的区别 例如：随机生成身份证号码中的月、日 =REPLACE(A1,IF(LEN(A1)=15,9,11),4,TEXT(INT(RAND()*(12-31-1-1)),MMDD)) 利用文本函数得日期格式，返回由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(abcabc,a,d,2)，返回值为abcdbc。当确切知道要替换的旧数据出现的次数时，用此函数。当然，如果不指定第四个参数，那么它将替换掉所有符合条件的数据。 Countif应用，统计两列单元格中的不同 在C1中输入下面公式，找出b中有a中也有的，每有一个重复增加1。 =countif(a:a,b1) 注：countif第一个参数没法对直接输入或者通过引用计算得到的数组进行操作，必须是单元格区域才行。 Countif第二个参数可以设置通配符，但是查找范围内的格式就必须设置成非数字格式。 Match函数 最后一个参数为0时，可以设置通配符查找。默认为1，这时需对查找区域排序，否则可能出现错误。 将数字进行舍入将数字向上舍入 要执行此任务，请使用 ROUNDUP、EVEN 或 ODD 函数。ROUNDup(number,num_digits) 远离零值，向（绝对值的方向）舍入数字。 EVEN 或 ODD返回沿绝对值增大方向取整后最接近的偶数。将数字向下舍入 ROUNDDOW