excel函数使用实例.doc

Excel函数使用实例唱润刚1 快捷输入ALT+178 (M),ALT+179(M)Alt+10(代表excel中alt+enter)2 excel只能显示小数点后6位,多于6位会自动四舍五入到小数点后1位（0.0999999会显示成0.1）或整数位（0.9999999会显示成1）。在自动筛选时，会按实际数据进行大小对比，比如，0.0999999，当筛选条件为大于等于0.1时，这个数据无法被筛选出来，按0.09999990.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(-,D2)-1,1,MID(D2,FIND(-,D2)-1,1)+2)。2) substitute()替换掉指定字符：=SUBSTITUTE(A1,123,456,3)。第三个参数用来确定替换掉第几次出现的指定字符，例中指第三次123，用456替换。如不指定，将替换所有的123。6 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。当确切知道要替换的旧数据出现的次数时，用此函数。当然，如果不指定第四个参数，那么它将替换掉所有符合条件的数据。7 Countif应用，统计两列单元格中的不同在C1中输入下面公式，找出b中有a中也有的，每有一个重复增加1。=countif(a:a,b1)注：countif第一个参数没法对直接输入或者通过引用计算得到的数组进行操作，必须是单元格区域才行。Countif第二个参数可以设置通配符，但是查找范围内的格式就必须设置成非数字格式。8 Match函数最后一个参数为0时，可以设置通配符查找。默认为1，这时需对查找区域排序，否则可能出现错误。9 将数字进行舍入1) 将数字向上舍入要执行此任务，请使用 ROUNDUP、EVEN 或 ODD 函数。ROUNDup(number,num_digits) 远离零值，向上（绝对值增加的方向）舍入数字。EVEN 或 ODD都是向上舍入到最接近的偶数或奇数。即返回沿绝对值增大方向取整后最接近的偶数（奇数）。2) 将数字向下舍入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 是较小的数。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)=,0,$E$1:$L$1000),ROW(1:10),LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10)=0,0,LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10),ROW(1:10)*10(ROW(1:10)-1)上面公式输入到出现一次的栏里面，然后向右拖拉，一次可以显示出现两次、三次，等等。12 自定义格式中,输入1显示早餐,输入2显示午餐,其余显示晚餐=1早餐;=2午餐;晚餐13 Ran()函数产生大于等于0小于1的函数，注意上限没有等于如需要取107内的随机数，函数：=10+(rand()-rand()*7如果10+7不包含10，可以写成：=17-rand()*7如果是107内的随机数，不包含10，其中odd为四舍五入取奇数（无论数字符号如何，都按远离 0 的方向向上舍入。），目的为了得到正负一。=10+ODD(RAND()-RAND()*(1-RAND()*7或者：=10+ODD(RANDBETWEEN(-1,1)*(1-RAND()*714 将“001”“001-107”数字前后都加上2，变成“003-109”=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,000)&-&TEXT(RIGHT(A1,3)+2,000)如果有8位数字，则需要进行为数判断，使用find()函数15 excel 转置自动填充的问题1) A列为a、b、c，b列为1、2、3，c列为11、22、33，d列为111、222、333，e列为aa、bb、cc效果为A111111Aa依此类推。=OFFSET(Sheet2!$A$1,INT(ROW()-1)/5),MOD(ROW()-1,5)然后向下拖拉2) A列为a、b、c、d、e、f、g、h、i，b列为1、2、3、4、5、6、7、8、9效果为：abc123def456ghi789公式=OFFSET(Sheet3!$A$1,COLUMN(A:A)-1+3*INT(ROW(1:1)-1)/2),MOD(ROW(1:1)-1),2)先向右拖拉3列，再向下拖拉16 excel计算分别满足多个条件的单元格数例一：有四列数据，如A B C D，A与B是对应的，C与D是对应的要求当C=A时，D=B列中“X”的个数 问题补充：B列有“X”，有的没有，只用统计X的个数！不是显示“X” 比如A B C D1 X 12 X 23 31 42 X 51 X 2 X3 X现在要D中显示“1”对应的“X”有多少个我的回答: D1 =COUNTIFS(B:B,=X,A:A,C1)此公式的缺点是只能用在office2007以上的版本。别人的回答:D1=SUMPRODUCT($A$1:$A$100=C1)*($B$1:$B$100=X)例二：计算出A列姓名如张三，在B列状态如所有显示“完成”的行项目数.用countifs。A2为张三。C2=COUNTIFS(A:A,A2,B:B,完成)用sumproduct函数：=SUMPRODUCT(a2:a100=张三)*(b2:b100=完成)解析：countifs是多条件统计，第一个参数是范围一，第二个参数是条件一，第三个参数是范围二，第四个参数是条件二，一次类推，可以多条件选择统计。此函数在2003版本中没有。Sumproduct函数是返回对应的数组元素的成绩的和，即1，2，34，5，6，结果是1*4+2*5+3*6=32。这里用来多条件统计的原理是，把每个条件作为乘积的因子，函数用数组来处理，和对应区域中的每个单元格对比，如果条件成立时，返回true，如果不成立返回false，这样得到一对数组false，true，falsetrue，true，false，接着进行计算false*true+ truetrue+false*false=0+1+0,因为两个逻辑函数相乘，只有当两个都是true的时候乘积为1，否则都为0，依此计算出来的结果，就是这些条件都满足的单元格的行数。因此上述两行的数组元素不但要维数相等，而且一定要对应。即选择范围是要在行间对应，否则求出的值就会出错。如条件一从A1到A10，条件二从B2到B11，虽然也是维数相同的数组，但是得数不正确，因为错行，它会把A1和B2相比较。上述公式因为是直接定义的数组，所以输入公式时不用单独应用数组公式。17 SumifSumif是对符合条件地单元格求和，第一个参数为需要符合条件的单元格区域范围，第二个参数是条件，第三个参数是实际需要计算的单元格区域，按与第一参数相等的区域进行计算。这里需要注意的是，参数一满足条件后会返回区域里面单元格所在的行数，求和时，按照参数三里面区域对应行数进行。如参数一区域选的A3:A10，参数三是B4到B11，假如a4，a5符合条件，那么将返回的行数是2、3，而不是4、5，然后，将对b5、b6进行求和，而不是b4、b5。=SUMIF(B:B,条件,A:A)本公式的意义是，当b列中有单元格等于“条件”两个字时，计算对应a列单元格的和。其中条件参数可以使用通配符，*，？，实现模糊查找。18 隐藏函数1) Numberstring(value,type)，阿拉伯数字转换成中文数字，type为1时，转换为小写中文数字（带千百万等等），2转换为大写中文数字（带千百万等），3转换为小写中文数字，不含千百万。2) Datedif(start_date,end_date,unit)，计算时间差，第一参数为开始时间，第二参数为结束时间，unit为返回的类型，”y”、”m”、”d”分别为年、月、日，这些参数返回的是总体差值，比如2008年1月1日到2009年1月1日，返回年数为整年数1年，返回天数是整个期间的总天数366天。”yd”、”md”这两个参数分别返回除去年或年月的天数，既不包括其中的年数或年月数所得到的天数。”ym”指出去年数的整月份数，多余的天数向下舍入。19 宏表函数的使用宏表函数实际上是现在广泛使用的VBA的“前身”，虽然后来的各版本已经不再使用它，但还能支持。在公式中使用宏表函数，最重要的一点就是：不能在公式中直接使用，而必须定义成名称。很多宏表函数即使按F9也不能自动更新，而必须按Alt+Ctrl+F9才能更新。解决的办法：在定义名称时加入一个易失函数，利用其易失性强迫结果更新，例如，定义X=GET.CELL(63,A1)当背景颜色改变时，公式结果不能自动更新，必须按Alt+Ctrl+F9才能更新，而如果定义为 X=GET.CELL(63,A1)&T(NOW(), 则只需按F9或激活当前工作表的任一单元格即可以立即更新。连接的易失函数有多种形式，除了上面的例子，还有 &T(RAND()+TODAY()*0等等。宏表函数对公式长度有限制。特别是EVALUATE，它的长度限制为251字符。在对长公式求值的时候这点往往不能满足要求。宏表函数的运行速度是比较慢的，这在应用时应予注意。1) 计算文本方式输入的算式，使用=evaluate()函数，但要使用名称定义。使用方法: 假设文本数据在a列，在这数据单元格之外任选一个单元格，使用“插入”-“名称”-“定义”，定义出一个名称aa，注意这里需要更改定义名称的方框中的最下部“引用位置（R）：”处输入“=EVALUATE(a1)”，点击“添加”。然后可以在b1中输入公式：=aa，即可得到a1的计算结果，往下拖拉可得到其他单元格的值。2) Get.cell函数1) 关于get.cell的使用的帖子，/forum/showthread.php?postid=26849432) 添加辅助列B存取单元格颜色序号，结束后可隐藏该列。3) 插入，名称管理，引用位置=get.cell(63,sheet1!a1)，其中63为取得单元格颜色序号。这里用到了宏表函数,宏表函数在excel中只能在自定义名称中使用,Get.Cell(类型号,单元格(或范围),其中类型号,中关于颜色的有24(返回单元格内字符的颜色值),63(填充颜色),64(传回图样前景色彩),20 不规则数据转置（数据量比较大）例：1 A 变为 1 A BC 1 B 2 D EFG 1 C 3 H 2 D . . . . 2 E 2 F 2 G3 H现在假设数据在A、B两列，从第一行开始。现在在C1、D1、E1等中输入你的开头序号是1的数据，也就是把你举例中的“1 A B C ”先人工输入。在C2单元格输入公式：=IF($A2=$A1,A2)向下拖拉，到你的数据的末尾。在D2中输入公式：=IF($A2=$A1,IF(ISERR(INDIRECT(b&IF(INDIRECT(A&ROW()+COLUMN(A2)-1)=$A2,ROW()+COLUMN(A2)-1,),INDIRECT(b&IF(INDIRECT(A&ROW()+COLUMN(A2)-1)=$A2,ROW()+COLUMN(A2)-1,)向右拖拉（注意向右时要拖拉到足够的列数，保证出来的每行数据后面的单元格都拖拉不出数据为止），再向下拖拉到数据的底部。21 不规则数据合并单元格我现在要把B123合并起来，与前面的单元格数一样，一个单词与对应的意思占有同样的单元格数。下面还有很多这样的，并不是连续的，要怎么操作？ 不要告诉我一个一个来，因为有很多！ 首先新建一个表，假设你的数据在sheet1，那么在sheet2进行第一步，将sheet1中复制所有需要的数据，在sheet2中，a1单元格右击，选择选择性粘贴，选数值，确定。第二步，按你的图给的数据，我在c1先输入1，在C2输入公式：=IF(A2=,INDIRECT(C&ROW()-1),C1+1)然后向下拖拉到数据最后一行，这样会形成一列数字1，2，3，等等第三步，因为是条件合并单元格，需借助自定义函数按Atl+F11进入VBE编辑器，选择“插入”“模块”，粘贴下面代码，关闭VBE编辑器。Function Joinif(R1 As Range, tj As String, R2 As Range) As String Dim X() As String, i As Integer, ii As Integer 声明变量 ii = 0 初始化变量 For i = 1 To R1.Cells.Count 循环R1单元格 If R1.Cells(i) = tj Then 条件 ii = ii + 1 ReDim Preserve X(1 To ii) X(ii) = R2.Cells(i) 赋值到X()数组 End If Next Joinif = Join(X, ；) 将X()各元素用；相连赋值给JoinifEnd Function倒数第二行代码中，“；”可以变成你需要的间隔，这个是把你的两个单元格合并后中间插入的符号，可以变成空格或逗号等等。第五步，在D1中输入公式=joinif($C$1:$C$100,C1,B$1:B$100)这里假设你的数据有100行，如果有更多，将上面两个100都改过来。拖拉上面公式到数据最后一行。第六步，选择所有数据，选择自动筛选，在A列选择所有空格的行，删除这些行，自动筛选出所有数据，删除掉B、C两列。最后剩下的两列数据就是所要的。22 隔行和跳行填充的问题（隔行表示引用为连续，填充不连续；跳行表示引用不连续，填充连续）思路方法：（1）column(a:a)*2-2利用列号得到包括0在内的偶数，可以实现行向拖拉。行号同理。Row(1:1)*2-1利用行号得到奇数，可以实现列向拖拉。列号同理。（2）mod(row(),2)，用以判断当前行是奇数还是偶数行，等于0为偶数，等于1为奇数。（3）int(row(1:1)-1)/n)得数每n行增加1，解决跳行填充的思路之一。示例：隔行1) 如果须填充的数据一样,可以输入下面公式:=IF(MOD(ROW()-3,30)=0,)每隔30个填充一个.2) 如果数据来自引用，隔行填充可以输入下面公式：=IF(MOD(ROW(),2)=1,INDIRECT(d&INT(ROW()/2)+1),)单数单元格填充，双数为空值。如果从第5个单元格填充第六行的数字，公式变成：=IF(MOD(ROW(),2)=1,INDIRECT(d&INT(ROW()/2)+4),)后面的相加的数字等于公式输入行的行数除以2再加1，row()/2+1，最后引用的行必须等于公式输入行。如果不等于，则公式后面要加上错位的行数（区分正负数）。上面公式可以变成：=IF(MOD(ROW(),2)=1,INDIRECT(d&INT(ROW()/2)+row()/2+1),)跳行3) 如果填充连续，但引用需要跳行。比如在引用的单元格区域，每隔五行填充进来一个数据：=INDEX(a:a,row(1:1)*5)=offset($a1,column(a:a)*2-2) 行填充，引用区域为第一行，填充奇数列=INDIRECT(ADDRESS(1,COLUMN(a:a)*2-1) 行填充，引用区域为第一行，填充奇数列=INDIRECT(ADDRESS(ROW(1:1)*2-1,1) 列填充，引用区域为第一列a列，填充奇数行23 工资条问题公式法：综合利用了隔行和跳行填充的问题，利用choose语句来选择不同的情况，比if判断要简洁。因为假设第一行为标题行，工资条、成绩单等类似问题，向来备受关注，方法也众多，什么邮件合并啦，什么借助辅助列重新排序，当然还有vba，公式法等等。这些方法中最简洁易操作的当然还是公式法，但网上流行的公式法都没有留出空行，给将来裁剪带来不便，追根究底，工资条是要剪开然后发给大家的，方便为主。笔者重新编写了公式，解决了裁剪的问题。首先假设工资或成绩数据都在sheet1，第一行为标题行。本公式考虑到裁剪的问题，结果会是：第一行是标题，第二行是工资或成绩，第三行是空行，向下以此类推。=CHOOSE(MOD(ROW(1:1)-1),3)+1,Sheet1!A$1,INDEX(Sheet1!A:A,INT(ROW(1:1)-1)/3)+2),)将上面公式输入到sheet2，任选一个单元格作为起始单元格，（或者输入到sheet1数据表格，但一定要选没有数据的列作为起始列），当然可以选sheet2中的a1作为起始，回车后选中a1，鼠标移到右下角，出现黑色十字时，向右拖拉到g列（当然由你的数据决定），然后再统一向下拖拉，直到数据完结。此公式当数据完结后，会在第二行工资处出现零值，为了不显示零值，可以再上述公式后面加上&“”，但是这样会将原来的数字格式的数据变成文本格式，所以依据使用者的需要，请自行选择添加。当然，具体的格式、字体、边框等等可以自行设置，如要设置边框，可以利用筛选剔除空白行，然后统一设置好边框。24 按条件a列要求在b列中对应的单元格填充自然数,中间会出现空单元格1) Max函数，在b列当前单元格上面已经得出的数据里面选取最大值加一。本方法适用于按递增或递减填充。2) row函数，从1开始填充的话，适用row(1:1)，此法适用于连续不间断填充，即中间不会出现空单元格，否则需要引入条件判断。25 取出S5200（带有充电器、电池）中,带括号的文字部分=MID(A1,FIND(（, A1),FIND(）, A1)如果前面的数字的位数一样，就用如下公式=RIGHT(TRIM(A1),LEN(TRIM(A1)-5)26 关于奇数、偶数=odd()取绝对值大的舍入的奇数，即正（负）数向上（下）舍入=even()=isodd()判断是否为奇数=ISEVEN()是的话返回true，否则返回false。27 计算所有单元格中偶数值的和=SUM(IF(MOD(A1:F1,2)=0,A1:F1,)此为数组公式，按ctrl+shift+enter结束28 计算单元格中,含偶数的个数:=SUMPRODUCT(MOD(A1:F1,2)=0)*A1:F1) 29 多重条件求和在excel表中，如果A列是日期，B列名称，C列是数量要求出，A列符合某个日期以前的以及B列符合某个名称的C的数量总和1) 如果是2007版，应用sumifs很好解决。第一个参数是计算范围，第二个参数是条件1范围，第三个是条件1，第四个是条件2范围，第五个是条件2，依次类推。=SUMIFS(C:C,A:A,日期,B:B,名称)2) 如果2003，公式如下：=SUM(IF(A:A=日期)*(B:B=名称)=1,C:C,)数组公式3) Sumif公式不支持数组操作，所以没办法进行多条件选择后求和。30 删除重复数据公式法1) 很简单的数据在a列中，有重复项。在b列中将所有多余项变成空单元格。其中，在具体例子中，可以将INDIRECT(a&ROW()变成a2，a3等等实际的单元格，可以实现一样的功能，可以不必排序。=IF(COUNTIF($a$1:INDIRECT(a&ROW(),INDIRECT(a&ROW()1,INDIRECT(a&ROW()选出的通过筛选删除。2) 在a列中，有重复项。在b列中剔除所有重复项，顺序列出所有不重复值，自动剔除空格项。 =INDEX(a:a,SMALL(IF(countif(A$1:A$200),(A$1:A$200)1,65536,ROW(a$1:a$200),ROW(1:1)&”数组公式3) 在a列中，有重复项。在b列中剔除所有多余项，顺序列出所有不重复值，自动剔除空格项。=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH($A$1:$A$10,$A$1:$A$10,),MATCH($A$1:$A$10,$A$1:$A$10,)0,ROW($A$1:$A$10),65536),ROW(1:1)&数组公式4) 在a列中，有重复项，b、c有相对应的数据。在d、e、f列中，剔除不重复，列出所有重复的项，自动剔除空格项。=INDEX(A:A,SMALL(IF(COUNTIF($A$1:$A$10,$A$1:$A$10)1,ROW($1:$10),65536),ROW(1:1)&数组公式，可以向右拖拉以便得到其他各列的相应数据。5) A、b两列数据，相应数据有重复（即a1和b1对比，a2和b2对比），在c列列出b列中不重复的选项，自动剔除空格项。这个问题如果不剔除空格，其实很简单，左右对比，if语句，如果相等即显示空格，如想剔除空格，则相对复杂，要用到small函数来从数组里取数。=INDEX(b:b,SMALL(IF(A$1:A$200)(B$1:B$200),ROW(B$1:B$200),65536),ROW(1:1)&6) 数据和要求都比较复杂。有两种数据，一种是TSDG112A,TSDG112B,TSDG113C类型，要求去掉所有的最后一个字母，然后筛选重复数据，还有一种是TSMIN08类型，后面没有字母，要求保留整个数据。数据从a2开始，公式输入到b2，如下：=IF(COUNTIF($A$2:A2,IF(CODE(RIGHT(A2,1)1,LEFT(A2,6)其中利用code函数判断最后一位是不是字母，数字的代码是48-57，大写字母是65-90，小写字母是97-122。利用countif函数可以使用通配符，模糊查找数据。31 借用10的指数达到数据的重新排序1) 数据倒置将5679变成9765，位数不规则=SUMPRODUCT(MID(A1,ROW(INDIRECT(1:&LEN(A1),1)*10(ROW(INDIRECT(1:&LEN(A1)-1)2) 数字从小到大排序将54671，变成14567，位数不规则=SUM(LARGE(-MID(A1,ROW(INDIRECT($1:&LEN(A1),1),ROW(INDIRECT($1:&LEN(A1)*10(ROW(INDIRECT($1:&LEN(A1)-1)数组公式32 数字过滤掉重复，重新排序比如0141253645267变成01234567，以下均为数组公式。2003版本中，因为没有函数iferror，使工作繁琐了很多。用if语句又导致公式太长，无法输入，解决办法：在工具栏，选插入名称自定义，在名称的地方输入名字，我这里用了数组序列的缩写szxl，在对话框的下面可以输入公式=IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1)回车确定以后，在任意单元格里输入公式：=IF(ISERR(SMALL(SZXL,1),SMALL(SZXL,1)&IF(ISERR(SMALL(SZXL,2),SMALL(SZXL,2)&IF(ISERR(SMALL(SZXL,3),SMALL(SZXL,3)&IF(ISERR(SMALL(SZXL,4),SMALL(SZXL,4)&IF(ISERR(SMALL(SZXL,5),SMALL(SZXL,5)&IF(ISERR(SMALL(SZXL,6),SMALL(SZXL,6)&IF(ISERR(SMALL(SZXL,7),SMALL(SZXL,7)&IF(ISERR(SMALL(SZXL,8),SMALL(SZXL,8)&IF(ISERR(SMALL(SZXL,9),SMALL(SZXL,9)&IF(ISERR(SMALL(SZXL,10),SMALL(SZXL,10)如果是2007版本，iferror语句=IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),1),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),2),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),3),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),4),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),5),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),6),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),7),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),8),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),9),)&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1),ROW($1:10)-1),10),)上面的解决办法公式太长容易出错，采用10的指数方法，解决：=SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,A1),ROW($1:$10)-1),ROW(INDIRECT($1:&COUNT(FIND(ROW($1:$10)-1,A1)*10(ROW(INDIRECT(1:&COUNT(FIND(ROW($1:$10)-1,A1)-1)上式未能包含0值，因为采用数字算法，首位为零会自动省略，在实际应用中，还要加入0值判断：=IF(ISERROR(FIND(0,A1),0)&SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,A1),ROW($1:$10)-1),ROW(INDIRECT(1:&COUNT(FIND(ROW($1:$10)-1,A1)*10(ROW(INDIRECT(1:&COUNT(FIND(ROW($1:$10)-1,A1)-1)或者=IF(ISERROR(FIND(0,A1),0)&SUMPRODUCT(LARGE(ISNUMBER(FIND(ROW($1:$10)-1,A1)*(ROW($1:$10)-1),ROW(1:10)*10(ROW(1:10)-1)其中count函数用来统计不重复数字的个数。如果是横向三个单元格,a1,b1,c1,可以先对其合并，再用下面公式，由于横向没法用frequency函数，所以还得先合并数据，再用find函数查找。=IF(iserror(FIND(0,T(CONCATENATE(A1,B1,C1),0)&SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1),ROW($1:$10)-1),ROW(INDIRECT(1:&COUNT(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1)*10(ROW(INDIRECT(1:&COUNT(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1)-1)上面公式可以计算一个单元格中多个数字的，如果三个单元格中只有单个数字,可以用下面公式。经过考虑，可以不用合并，直接用match函数进行查找，简化公式变成（但不能用hlookup，不支持第一参数数组查找）：=IF(ISERROR(MATCH(0,A1:C1,0),0)&SUMPRODUCT(LARGE(IF(ISERROR(MATCH(ROW($1:$10)-1,A1:C1,0),ROW($1:$10)-1),ROW(INDIRECT(1:&COUNT(MATCH(ROW($1:$10)-1,A1:C1,0)*10(ROW(INDIRECT(1:&COUNT(MATCH(ROW($1:$10)-1,A1:C1,0)-1)33 从b1中查找a1中的所有字符，有则显示，无则删除A1，b1中字符为非数字形式，所以无法用10的指数形式显示，下面公式必须依赖在几个单元格中输入整体数组公式才能实现，属于半成品。=IF(ISNUMBER(FIND(MID(A1,ROW(INDIRECT($1:$&LEN(A1),1),B1),MID(A1,ROW(INDIRECT($1:$&LEN(A1),1),)如果a1长度不定，可以利用多个辅助列来处为，可以可以解决问题：因为公式超出嵌套的问题，需借用名称定义，定义一个名称cd，=ROW(INDIRECT($1:$&IF(COLUMN()=3,LEN($A1),LEN(B1)然后输入数组公式：C1=REPLACE(IF(COLUMN()=3,$A1,B1),MIN(IF(ISNUMBER(FIND(MID(IF(COLUMN()=3,$A1,B1),cd,1),$B1),cd,),1,)向右拖拉，直到出现错误为止，因为嵌套问题，所以无法再添加判断最后的错误。如果a1中字符有重复，则此公式将会出现错误。可以尝试用substitute函数进行全面搜索替换。因函数过长，涉及到两个名称定义，未予考虑。34 条件格式突出显示包含字符的单元格1) 2003版本 选择a1单元格，然后在工具栏中选择格式条件格式，弹出的对话框里，条件下拉框选择公式，在右边输入公式=not(iserror(find(-,a1)在格式里面选择要的颜色，然后确定，用格式刷，把格式刷到所有的数据单元格。2) 2007版本选择所有数据，在工具栏选开始，条件格式，突出显示单元格规则文本保含，弹出对话框输入-，选择你想要的颜色，确定。35 在列表中查找值的方法有：1) 使用精确匹配在列表中垂直查找值=INDEX(A2:B5,MATCH(梨,A2:A5,0),2)此法可以在查找区域内任意范围内查找。2) 使用精确匹配在未知大小的列表中垂直查找值=OFFSET(A1,MATCH(梨,A2:A5, 0),1)此法可以在查找区域内任意范围内查找。MATCH，返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。3) Lookup，从单行或单列区域查找指定值，返回指定一列或一行中的数据。如果是数组形式，在数组第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。4) Vlookup，只能在区域内第一列查找，可以返回所有区域内的任意列的数据。如果查找值有两个以上，则返回最后一个数据。5) Hlookup，只能在区域内第一行查找，可以返回所有区域内的任意行的数据。6) 使用vloo