excel学习宝典

excel学习宝典.txt9母爱是一滴甘露，亲吻干涸的泥土，它用细雨的温情，用钻石的坚毅，期待着闪着碎光的泥土的肥沃；母爱不是人生中的一个凝固点，而是一条流动的河，这条河造就了我们生命中美丽的情感之景。

目录 11.1 自学部分 21.1.1 隐藏与加密 21.1.2 字符串应用 21.1.3 日期函数 31.1.4 排序： 31.1.5 自动筛选 41.1.6 word删除空行 51.2 Excel录入技巧 51.2.1 快速填入相同内容 51.2.2 快速复制上单元格 51.2.3 快速输入系统日期 51.2.4 文本数字输入技巧 61.2.5 分数输入技巧 61.2.6 输入法快速切换技巧 61.2.7 信息快速添加技巧 61.2.8 参数快速插入技巧 61.2.9 快速编辑技巧 61.2.10 重复操作71.2.11 轻松实现多行文本输入71.2.12 读出来帮你校对71.2.13 数据同步滚动71.2.14 小数点或零输入技巧71.2.15 在单元格区域内换行81.2.16 多次输入的特殊符号81.3 Excel工作簿技巧81.3.1 设置好你的姓名及选项81.3.2 快速切换工作簿81.3.3 快速最大/小化/还原工作81.3.4 快速打开文件夹91.3.5 强制退出Exce 91.3.6 快速切换工作表91.3.7 快速打开插入对话框91.3.8 快速重命名工作表91.3.9 快速打开固定工作簿91.3.10 在不同工作表中输入相同内容91.3.11 让Excel打开两个同名文件101.3.12 生成备份工作薄101.3.13 在工作簿中使用工作表组101.3.14 重复使用格式刷101.3.15 使用格式表达式111.3.16 小提示111.4 Excel函数应用教程：查找与引用函数121.4.1 ADDRESS 121.4.2 AREAS 121.4.3 CHOOSE 131.4.4 COLUMN 131.4.5 COLUMNS 131.4.6 HLOOKUP 141.4.7 HYPERLINK 141.4.8 INDEX 141.4.9 INDIRECT 151.4.10 LOOKUP 151.4.11 MATCH 161.4.12 OFFSET 161.4.13 ROW 171.4.14 ROWS 171.4.15 RTD 171.4.16 TRANSPOSE 181.4.17 VLOOKUP 181.4.18 R1C1 引用样式191.4.19 如何插入标题母板201.1 自学部分1.1.1 隐藏与加密1、隐藏：选择使用的行、或列、单元格、工作表，点击格式\行、或列、单元格、工作表\隐藏显示隐藏：编辑\定位，选择需要定位的区域，点击“取消隐藏”2、加密保护：工具\保护\工作簿，输入密码1.1.2 字符串应用1、字符串比较：exact(“”,””)2、获取单元格A1中字符串的长度，LEN(A1)3、判断A1中字符串的长度是否是11位，IF(LEN(A1)=11，条件为真是所执行的操作，条件为假时所执行的操作)4、取单元格A1中字符串的左边3位，LEFT(A1,3)a) 取单元格A1中字符串的右边3位，RIGHT(A1,3)b) 取单元格A1中字符串中的第4位到第7位，即总共4位，MID(A1,4,4)5、将数值转换为按指定数字格式表示的文本a) TEXT(value, format_text)b) Value:为数值、计算结果数字值的公式，或对包含数字值单元格的引用。

c) Format_text:为“单元格格式”对话框中“数字”选项卡上“分类”框中文本形式的数字格式。

d) 例：A3单元格的内容为“李化”，B3单元格的内容为百分比数字“20%”，则公式：=A3&”的销售额占总销售额的”&TEXT(B3,”0%”)e) 将显示为：李化的销售额占总销售额的20%。

6、合并字符串a) CONCATENATE(text1,text2,…)1.1.3 日期函数1、插入当前日期：=TODAY() 插入当前日期（可变）=NOW() 插入当前日期和时间（可变）2、计算两个日期之间的天数、月数、年数DATEIF(start_date,end_date,unit)假设F4是某个员工参加工作的时间，现在要求出这个员工的工龄，则公式：= CONCATENATE(DATEIF(F4, TODAY(),”y”),”年”, DATEIF(F4, TODAY(),”ym”),”个月”)将显示“X年X个月”3、返回代表特定日期的序列号：DATE(year,month,day)1.1.4 排序：1、RANK(number，ref,order),例如，在学生成绩表中，I列存放学生的总成绩，I2~I45是所有学生的总成绩，在J列显示按总成绩的降序排列的名次：=RANK(I2，I$2:I$45,0)2、计算平均成绩AVERAGE（C2：C45）4、计算优秀率和及格率=COUNTIF(C2:C45,” >=80”)/COUNTA(C2:C45)5、统计前三名和最后三名第一名：=MAX(C2:C45) 或者 =LARGE(C2:C45,1)第二名：=LARGE(C2:C45,2)第三名：=LARGE(C2:C45,3)第一名：=MIN(C2:C45) 或者=SMALL(C2:C45,1)第二名：= SMALL (C2:C45,2)第三名：= SMALL (C2:C45,3)6、使用if条件语句时，若条件判断后使用字符的话，需要使用双直引号””括起。

如班级排列=IF(J2<=15,"高三（1）班",IF(J2<=30,"高三（2）班","高三（3）班"))7、 offsetOFFSET(reference,rows,cols,height,width)以指定的引用为参照系，通过给定偏移量得到新的引用。

例如：在学生表中的平均成绩统计，=SUM(IF($K$2:$K$45=$M14,C$2:C$45))/$N14，并同时按下shift+ctrl+enter8、分段函数统计（见学生成绩统计练习）a、使用countif函数统计使用COUNTIF统计语文成绩，以60~69之间为例：在P42中输入：=COUNTIF(C$2:C$45,"<70")-COUNTIF(C$2:C$45,"<60")b、用DOCUNT函数统计以统计“数学“学科各分数段学生数，在W40~X49单元格中输入表中信息分别选种Q41、Q42……Q45，输入公式：=DCOUNT(D1:D45,”数学成绩”,W40:X41)=DCOUNT(D1:D45,”数学成绩”,W42:X43)=DCOUNT(D1:D45,”数学成绩”,W44:X45)=DCOUNT(D1:D45,”数学成绩”,W46:X47)=DCOUNT(D1:D45,”数学成绩”,W48:X49)c、用FREQUENCY函数统计FREQUENCY函数的主要功能：以一列垂直数组返回某个区域中数据的频率分布。

使用格式：FREQUENCY(data_array,bins_array)data_array表示用来计算频率的一组数据或单元个区域；bins_array表示为前面数组进行分隔的一列数值。

分别选Y41~Y46单元格，输入分数段的分隔数值同时选种R41~R45单元格区域，在“编辑栏”中输入公式=FREQUENCY(E2:E45,$Y$41:$Y$45),输入完成后，按下Ctrl+Shift+Enter组合键进行确认，即可一次性统计出“英语”学科各分数段的学生人数。

d、用SUM函数统计——计算所有参数数值的和使用格式： SUM(number1,number2……)参数说明：number1、number2……代表需要计算的值，可以是具体的数值、引用的(单元格（区域）、逻辑值等。

以统计物理成绩在80~89分的人数，在单元格中输入=SUM(($F$2:$F$45>=80)*($F$2:$F$45<90))，输入完成后，按下Ctrl+Shift+Enter组合键进行确认1.1.5 自动筛选――――――――――自动过滤表中的数据，选择满足条件的记录1. 选定数据清单中的某个单元格为当前单元格2. 单击“数据”菜单中“筛选”命令中的“自动筛选”子命令（该命令式一个选项开关，默认为关闭状态，当处于打开状态时，数据清单每列的标志旁边都会出现一个下拉箭头）3. 单击目标字段的下拉箭头，这时将显示该字段值的列表，以及“全部”÷“前10个”和“自定义”等选项4. 可以从中选择一个值作为筛选数据清单的条件5. 这时工作表中满足条件的记录所在的行号和设置了筛选条件的筛选箭头都是蓝色的，以提醒操作者注意当前显示的时筛选的结果以及对哪些字段进行了筛选。

6. 单击“数据”菜单中“筛选”命令中的“全部显示”子命令，可恢复所有记录。

MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

语法:MID(text,start_num,num_chars)自定义条件筛选：在选择“自动筛选”后，点击目标列的下拉箭头，选择“自定义”，输入相应的条件。

高级筛选：详见“股市行情分析”辅助投资分析——投资方案，模拟运算1.1.6 word删除空行1、编辑”菜单中打开“查找和替换”对话框，在“查找内容”中输入“^p^p”“替换为”中输入“^p”，然后“全部替换”。

2、如果文件中用的是手动换行符，那么就要用“^l^l”替换成“^l”(这里用的不是1，而是L字母的小写，也可以直接用替换对话框里的“特殊字符”，里面有“有手动换行符”)3、若还是行不通，请尝试：把^l ^l”(注意两个换行之间有一个空格)替换成“^l”，替换终于有成果了。

或“p ^l”替换成“^l”。

4、另外，可以通过“工具/选项/视图/格式标记”选全部，可看出要替换的原有标记，直接复制粘贴，作为要查找内容。

此法比较行之有效。

1.2 Excel录入技巧1.2.1 快速填入相同内容选中多个单元格后，输入字符，然后按Ctrl+Enter组合键，即可在选中的每个单元格中填入上述相同的字符。

1.2.2 快速复制上单元格选中下面的单元格，按Ctrl+ '（'为西文的单引号）组合键，即可将上一单元格的内容快速复制下来。

1.2.3 快速输入系统日期按下Ctrl+；组合键，即可快速输入系统日期。

1.2.4 文本数字输入技巧一般情况下，在单元格中输入的任何数字，均以“右对齐”格式放置，而且Excel会把综当成数字来处理。