Excel 常用技巧.doc

Excel 常用技巧一、 单元格中数据的输入和编辑1、 单元格中输入001、002（文本型，保留前面的00）西文状态下+ 单引号 + 数字2、 分数（2/3）：西文状态下 0+空格+2/3 3、 负数：-7或（7）4、 时间与日期：2009-01-12 或2009/01/12 11:305、 在单元格中输入“五（三）班期中成绩表”可以选择在“公式栏”中输入字符6、 单元格内换行Alt + Enter7、 “格式”菜单栏下设置“单元格格式”包括：数值（小数点后面几位）、货币、百分比、时间等数值形式的设计；对齐方式（合并/ 拆分单元格）；字体；边框底纹等设置。8、 “格式”菜单栏下设置“行高” “列宽”二、 单元格中的范围引用1、 相对引用：A3：J3 (范围会变动)2、 绝对引用：$A$3:$J$3 或者A$3 : A$15 （引用范围不变）三、 条件格式和自动套用格式1、 条件格式：例如设置单元格某些区域每门课成绩等于100蓝色加粗 每门课成绩小于60红色倾斜2、 自动套用格式：“格式”菜单栏下几种类型格式四、 Excel 中常用的公式1、 求和Sum（number1: B3:B7 number2: D7:D18.）求number1、number2等所有数字之和。2、 求平均Average (number1: B3:B7 number2: D7:D18.) 求number1、number2等所有数字的平均值。3、 求最大值Max (number1: B3:B7 number2: D7:D18.) 求number1、number2等所有数字的最大值。4、 求最小值Min (number1: B3:B7 number2: D7:D18.) 求number1、number2等所有数字的最小值。5、 求余Mod (number，divisor) 例如：求A2单元格和4的余数，表达Mod(A2,4)6、 求绝对值Abs（1个number）求一个数值的绝对值。Abs（套用求和或其他公式求出来的一个值）7、 时间和日期函数1) Date(year, month, day) year(19049999),month (1-12),day (1-31) 若单元格格式数字不满足其要求，自动计算其年月日值。Date(A1, D3, G7)2) Year(sevial-number) 返回19049999的年份值。例如：A2=2011/11/10 20:30:45 Year(A2)=20113) Month(sevial-number) 返回112的月份值。例如：A2=2011/11/10 20:30:45 Month (A2)=114) Day(sevial-number)返回1-31的值。例如：A2=2011/11/10 20:30:45 Day(A2)=105) Hour(sevial-number)返回0-23 的值。例如：A2=2011/11/10 20:30:45 Hour (A2)=206) Minute(sevial-number)返回0-59的整数。例如：A2=2011/11/10 20:30:45 Minute (A2)=307) Second(sevial-number)返回0-59的整数。例如：A2=2011/11/10 20:30:45 Second (A2)=458) Today(无参数)返回当前电脑设置的日期。Today(无参数)=2011/11/109) Now(无参数)返回当前电脑设置的日期和时间。Now(无参数)= 2011/11/10 20:3010) 计算年龄/工龄：若A3=1989年7月7号 或1989/7/7 Year(Today() year(A3) 11) 计算时间：入库时间A4=8:12:25 出库时间A4= 11:15:35 计算停车时间，数字形式不变仍为时：分：秒。 第一，编辑公式栏直接 =A4-A2；第二，插入text 函数。12) Text (value, format-text) Text(A4-A2, hh: mm:ss)8、 检测单元格中内容是否为文本Istext（value）,是文本返回true，不是文本返回false。9、 检测单元格中内容是否为数值Isnumber（value）,是数值返回true，不是数值返回false。10、 A2=1234567 Left(A2,3)=123 ; Right(A2,4)=456711、替换Replace函数的应用：Replace（Old-text , start-num , num-chars ,new-text）1)使用REPLACE函数，对Sheet1中用户的电话号码进行升级。要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。2) 使用REPLACE函数，对Sheet1中的员工代码进行升级，要求： a. 升级方法：在PA后面加上0；b. 将升级后的员工代码结果填入表中的“升级员工代码”列中。11、 Vlookup函数和Hlookup函数Vlookup函数 Vlookup(Lookup-value, Table-array, Col-index-num, Range-lookup)1)使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用VLOOKUP函数， 将其单价自动填充到采购表中的“单价”列中。 注意：函数中参数如果需要用到绝对地址的，请使用绝对地址进行答题，其他方式无效。2)使用HLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用HLOOKUP函数， 将其单价自动填充到采购表中的“单价”列中。 注意：函数中参数如果需要用到绝对地址的，请使用绝对地址进行答题，其他方式无效。12、Rank函数排名（返回单元格中的数在范围内的大小排序）Rank(number, ref , order)number: A