excel常用函数公式方法搜集.docxVIP

excel常用函数公式技巧搜集（四）默认分类 2010-10-09 15:06:21 阅读429 评论0 字号：大中小订阅 .筛选后自动产生序列号并汇总自动产生序列号：在A1输入以下公式，往下拖。=SUBTOTAL(3,$B$2:B2)*1自动汇总，用以下公式：=SUBTOTAL(9,$B$2:B2)说明：汇总时，不要在“全选”状态下进行，先“筛选”出某一单位，自动求和∑。然后再恢复到“全选”或者选择任何单位，就能自动汇总了（在“筛选”出某一单位进行求和时，一般表格会自动产生以上汇总公式）。其它：如同时要在其它单元格显示人数，在“全选”状态下，选定单元格，点“fx”（用“sum”函数）再点击序列号最末尾数，即可。如何筛选奇数行公式=MOD(A1,2)=1函数筛选姓名如何把两列中只要包含A和A+的人员筛选出来=IF(ISNUMBER(FIND(A,C2))+ISNUMBER(FIND(A,B2))0,OK,)名次筛选名次=RANK(K5,K$2:K$435)班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))如何实现快速定位（筛选出不重复值）=IF(COUNTIF($A$2:A2,A2)=1,A2,)=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,)=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))（数组公式）如何请在N列中列出A1:L9中每列都存在的数值｛=IF(ROW()SUM(--x),,INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))｝自动为性别编号的问题有一个编码,5位，第1位,1为男,2为女，后面4位,代表他的编号,从0001-9999，如何达到下表：性别编码男 10001男 10002女 20001男 10003女 20002男的也是从0001-9999女的也是从0001-9999如果你是已经输入了其它信息，仅仅为快速输入编码的话。用筛选可以实现吧。先以“男”为关键字进行排序，然后在第一个男的编码输入10001，下拉复制到最后一单即可。同理再以“女”排序。完成目标。用公式：=IF(A2=,TEXT(COUNTIF(A$2:A2,A2),10000),TEXT(COUNTIF(A$2:A2,A2),20000))向下拖㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【文本与页面设置】EXCEL中如何删除*号在录入帐号是录入了*号，如何删除。可以用函数 SUBSTITUTE(a1,*,)查找~*，替换为空。将字符串中的星号“*”替换为其它字符在查找栏输入~*替换为“-”即可。去空格函数如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法。如：中国，改为：中国。1、用公式：=SUBSTITUTE(A2, ,) 注：第一对双引号中有一空格。而第二个“”中是无空格的。2、利用查找－替换，一次性全部解决。“编辑”－“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替换”什么也不输入（空白）。然后“全部替换”即可。3、有一个专门删除空格的函数: TRIM()在EXCEL编辑栏里，不管输中文还是英文只能输一个字节的空格，但如果字与字中间是两个字节的空格，那么TRIM（）就不起作用了，它就不认为是一个空格，而是一个汉字，怎么去“TRIM”也没用。如：单元格A1中有“中　　心　　是”，如果用TRIM则变成“中　心　是”，想将空格全去掉，只能用SUBSTITUDE（）函数，多少空格都能去掉。如何去掉字符和单元格里的空格8900079501 8900079501~1900078801 1900078802~=SUBSTITUTE(B2,~,)怎样快速去除表中不同行和列的空格编辑-定位-定位条件-空值,可选中所有空单元格, 再删除。如何禁止输入空格在Excel中如何通过编辑“有效数据”来禁止录入空格？烦请大侠们费心解答。解答：有效性公式。=COUNTIF(A1,* *)=0(注：COUNTIF(A1,* *) 在单元格有空格时结果为1,没有空格时结果为0如希望第一位不能输入空格:countif(a1, *)=0如希望最后一位不能输入空格:countif(a1,* )=0)代替单元格中字符串单元格编号，开始位数，从开始位数算起第几位数，要用于代替的的字符串。windows2000变成windows2K=REPLACE(B2,8,3,K)单元格编号，要代替掉的字符，要用