excel学习笔记之六.docVIP

这是我去年学习excel时学习笔记，后来用于教学工作，发现挺有用，只在要sohu网上发表过（/entry/6062074/#entry），想起来发在豆丁网上，不是为了赚钱，只是希望给大家一点帮助了。 Excel 学习笔记 1、求字符串中某字符出现的次数： 例：求A1单元格中字符a出现的次数：=LEN(A1)-LEN(SUBSTITUTE(A1,a,)) 3、如何在EXCEL中引用当前工作表名 如果你的工作薄已经保存,下面公式可以得到单元格所在工作表名: =RIGHT(CELL(filename),LEN(CELL(filename))-FIND(),CELL(filename))) 4、相同格式多工作表汇总求和方法 假定同一工作薄有SHEET1至SHEET100共100个相同格式的工作表需要汇总求和，结果放在SHEET101工作表中，请在SHEET101的A1单元格输入：=SUM(单击SHEET1标签，按住Shift键并单击SHEET100标签，单击A1单元格，再输入：）此时公式看上去内容如下：=SUM(SHEET1:SHEET100!A1)按回车后公式变为??????????????? =SUM(SHEET1:SHEET100!A1)所以，最简单快捷的方法就是在SHEET101的A1单元格直接输入公式：=SUM(SHEET1:SHEET100!A1)然后按回车。 5、如何判断单元格里是否包含指定文本？ 假定对A1单元格进行判断有无指定文本,以下任一公式均可: =IF(COUNTIF(A1,*指定文本*)=1,有,无) =IF(ISERROR(FIND(指定文本,A1,1)),无,有) 6、EXCEL中排名次的两种方法： （1）、用RANK()函数： 假定E列为成绩，F列为名次，F2单元格公式如下： =RANK(E2,E:E) 这种方法，分数相同时名次相同，随后的名次将空缺。 例如：两个人99分，并列第2名，则第3名空缺，接下来是第4名。（2）、用公式排序(中国式排名)： 假定成绩在E列,请在F2输入公式：=SUM(IF(E$2:E$1000E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)))+1公式以Ctrl+Shift+Enter三键结束。 第二种方法分数相同的名次也相同，不过随后的名次不会空缺。 7、注意：工作薄和工作表都是绝对引用，没有相对引用。 8、求某一区域内不重复的数据个数 例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法：一是利用数组公式：=SUM(1/COUNTIF(A1:A100,A1:A100))输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号{}。二是利用乘积求和函数：=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) 9、EXCEL中如何动态地引用某列的最后一个单元格？ 在SHEET2中的A1单元格中引用表SHEET1中的A列的最后一个单元格中的数值(SHEET1中A列的最后一个单元格的数值不确定,随时会增加行数)：=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0,1,1) 或者:??? =INDIRECT(sheet1!ACOUNTA(Sheet1!A:A)) 注:要确保你SHEET1的A列中间没有空格。 10、如何在一个工作薄中建立几千个工作表 右击某个工作表标签,点插入,选择工作表,点确定,然后按住Alt+Enter键不放,你要多少个你就按住多久不放,你会看到工作表数量在不断增加,几千个都没有问题。 但是不能准确插入,其实就是F4的用法 11、如何知道一个工作薄中有多少个工作表 按Ctrl+F3(或者点插入-名称-定义),打开定义名称对话框 定义一个X 引用位置输入:=get.workbook(4) 点确定。 然后你在任意单元格输入=X 出来的结果就是sheet的个数。 12、一个工作薄中有许多工作表如何快速整理出一个目录工作表1、用宏3.0取出各工作表的名称，方法：??Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入：?=MID(GET.WORKBOOK(1),FIND(],GET.WORKBOOK(1))+1,100)??确定??2、用HYPERLINK函数批量插入连接，方法：???在目录工作表（一般为第一个sheet）的A2单元格输入公式：=HYPERLINK(#INDEX(X,ROW())!A1,INDEX(X,ROW())) 将公式向下填充，直到出错为止，目录就生成了。 1、DATE(year,month,day)对于 1900 日期系统：