excel学习笔记之五.docVIP

这是我去年学习excel时学习笔记，后来用于教学工作，发现挺有用，只在要sohu网上发表过（/entry/6062074/#entry），想起来发在豆丁网上，不是为了赚钱，只是希望给大家一点帮助了。 Excel 学习笔记 1、Excel中当被引用单元格发生剪切操作时,如何保持引用单元格的内容仍然引用原位置的内容？ 例如，A1等于B5时,当B5的内容剪切到C8时,A1将等于C8的内容。如果要求不管B5作什么操作, A1始终要等于B5的内容，A1单元格公式如下： =INDIRECT(B5) 2、如何在公式单元格前面插入或删除列时始终引用当前单元格左边相邻单元格的值？ 公式： =OFFSET(A1,0,COLUMN()-2,1,1) 3、如何只显示（筛选）奇数行或偶数行？ 在后面空白列(假定为F列)的第2行输入筛选条件: =MOD(ROW(A2),2)=0 选定该表所有数据列,点数据-筛选-高级筛选,条件区域选择: =$F$1:$F$2 点确定。这样就只显示偶数行。 如果要只显示奇数行，将公式改为： =MOD(ROW(A2),2) 4、如何根据A列的身份证号码在B列判断女的是否大于40岁，男的是否大于50岁？ 在B1输入公式: =IF(YEAR(TODAY())-1*TEXT((LEN(A1)=15)*19MID(A1,7,2+(LEN(A1)=18)*2),0000)IF(MOD(MID(A1,15,3),2),50,40),是,否) 将公式向下复制。 5、如何计算单元格中数学表达式的值？ 假定A1内容为21*17+5/4,要在B1单元格计算其结果。 单击B1，然后点“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。 在“在当前工作表中的名称”中输入定义的名称“X”，在下方的“引用位置”编辑栏中输入: =EVALUATE(A1) 单击[确认]按钮退出。 在 B1中输入“=X” (注:不含引号) 公式可向下复制。 6、如何求单元格中任意数字的所有各个位数之和？ 如5126各位数的和5+1+2+6=14，公式如下： =SUMPRODUCT(1*MID(A1,ROW(INDIRECT(1:LEN(A1))),1)) 注：A1可以为任意位数字。 7、如何让用户在C1单元格选择的月份后，A列中自动显示当年当月所有日期，而在B列自动填充对应的星期几？ 假定C1单元格用数据有效性设置一个选择(1-12)月份的下拉菜单,A1单元格输入公式: =IF(C$1=,,IF(C$1MONTH(DATE(YEAR(TODAY()),C$1,ROW())),,DATE(YEAR(TODAY()),C$1,ROW()))) B1单元格输入公式: =IF(A1=,,TEXT(WEEKDAY(A1),AAAA)) 将两个公式向下复制到第31行。 将A列单元格格式设置为你要的日期格式。 8、如何填充特殊序列（如第一期、第二期……，第壹号、第贰号……）？ 有两种方法： 一是设置“自定义”单元格格式，“类型”分别为： [DBNum1]第G/通用格式期 [DBNum2]第G/通用格式号 然后输入阿拉伯数字进行填充。 二是利用公式直接生成，公式分别为： =第TEXT(ROW(A1),[DBNum1]G/通用格式)期 =第TEXT(ROW(A1),[DBNum2]G/通用格式)号 9、如何查找某列中不重复的数据并连续存放在另一列中? 假定原数据在A1:A100区域中，其中有若干数据重复，先将A1数据复制到B1，然后在B2单元格输入数组公式： =IF(SUMPRODUCT(1/COUNTIF(A$1:A$100,A$1:A$100))ROW(),,INDEX(A:A,MIN(IF(COUNTIF(B$1:B1,A$1:A$100)=0,ROW(A$1:A$100))))) 将公式向下复制，直到出现空白为止。 补充：下面这个数组公式可以从B1单元格直接输入: =INDEX(A:A,SMALL(IF(MATCH(A$1:A$100,A$1:A$100,)=ROW(A$1:A$100),ROW(A$1:A$100),65536),ROW())) 公式以CTRL+SHIFT+回车结束。 10、如何将一个单元格中的数字去掉重复的后从小到大排序输出在另一个单元格中? 假定A1为209537233，要在B1得到023579，公式如下： =IF(ISERROR(FIND(0,A1)),,0)SUBSTITUTE(SUM(IF(FIND(ROW($1:$9),A1123456789)=LEN(A1),ROW($1:$9)*10^(9-ROW($1:$9)))),0,) 公式以CTRL+SHIFT+回车结束