Excel实用教程大全.doc

E区内部资料 Excel 高级使用教程大纲一、 公式和格式 （此部分主要用于使报表做得更规范、更有效）1、 常用函数介绍i. 求和类：Suma) SUM(number1,number2, .)ii. 计数类：Count、countaa) COUNT(value1,value2,.)b) COUNTA(value1,value2,.)iii. 日期类：Today、Now（日期可进行加减）a) TODAY( )b) NOW( )iv. 财务类：a) PMT(rate,nper,pv,fv,type)b) FV(rate,nper,pmt,pv,type)2、 序列填充方式： i. 公式填充：拖动填充手柄+ii. 数字序列填充：a) 同时选择已知2位数字拖动填充手柄+b) 选择已知1位数字Ctrl+拖动填充手柄+iii. 文本序列填充：ToolsOptionCustom listNewa) 日期序列填充：同时选择已知日期值拖动填充手柄+3、 单元格的引用：i. 相对引用：A1 在行中复制时，行不动，列在动，在列中复制时，列不动，行在动.ii. 绝对引用：$A$1 f4不能动的要加$iii. 混合引用：$A1 , A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。4、 输入数据的技巧i. 选择活动单元格周围的相邻区域: Ctrl+a ii. 选择活动单元格相邻方向上的区域： ctrl+shift+方向键iii. 移动列：按shift+拖动边框 iv. 长数字的输入：134134123412formatnumbertext v. 分数的输入：0空格1/5 vi. 单元格中的内容换行：定位光标到要换行处Alt+enter vii. 快捷日期输入 “ Ctrl；” 当前时间输入 “Ctrl shift ； 5、 条件格式： FormatConditional formattingi. 自身值决定自已的格式。 ii. 由公式来决定格式。 iii. And(条件1，条件2，条件3) iv. Or(条件1，条件2，条件3) 6、 输入数据的有效性：数据有效性DataValidationlist7、 粘帖和选择性粘帖：CopyEditPaste speciali. 粘贴数值或公式ii. 粘贴链接iii. 转置：行和列的切换8、 格式刷的使用：i. 双击多次应用格式ii. 单击单次应用格式9、 工作表背景 FormatBackgroup10、 自定义菜单和工具栏ToolsCustomize拖放按钮到工具栏11、 模板i. 应用模板：FileNewTemplates on my computerspreadsheet solutions ii. 自制模板：FileSave asTemplate 二、 预览打印及发送文档（此部分可令打印更技巧，节省纸张）1、 打印页面设置：i. 调整打印大小C 文件页面设置：ii. 在每页上都打印行列标题（在每页上都打印标题，避免要与第一页对照才可找到数据说明）C FilePage setupSheet：iii. 打印批注：FilePage setupSheetcommentsiv. 创建页眉页脚：打印预览页眉页脚2、 使用E-mail 功能：i. 文件发送ii. 工具栏：单击 e-mail 三、 图表和批注1、 批注 InsertComment2、 图表i. InsertChartTypeii. 修改图表类型：各种图表的作用iii. 修改图表数据：刻度、色彩等iv. 组合图表的使用（一类数据系列使用一种图表；另一类数据系列使用另一种图表）双轴线柱图及饼图的制作 四、 数据库入门 1、 排序i. 排行：a) 单依据：定位光标到依据列，单击按钮b) 多依据：datasortii. 排列2、 筛选：(按条件显示记录) i. 自动筛选：datafilterauto filtera) 指定内容筛选b) 取极端数据top 10：（只对数值性字段有效）c) 自定义：（最多可以定义两个条件，and or）ii. 高级筛选：a) 创建条件区域：b) 将条件中用到的列的标题复制到原表格以外的某处c) 在复制出的标题下方写条件。d) 如果条件写在同一行中为ande) 如果条件写在同一列中为 or f) *:表示多个字符？：只表示一个字符如：MakeModelTypeRatePricePriceHonda*family*2000030000Chevrolet*family*ford*family*g) Datafilteradvanced filterh) 去除重复记录：3、 数据库函数：i. =dsum(database,field,criteria)ii. =daverage()iii. =dmax()iv. =dmin()v. =dcount()vi. =dcounta()4、 记录单（便于定位，录入数据）i. Data:Forms 五、 数据库管理 1、 数据导入和导出i. Export: FileSave astype Text (Tab delimited)ii. Import:FileOpen type TextImport wizard 2、 分类汇总i. Datasubtotal设置分类自段/汇总方式3、 数据透视表及数据透视图i. datapivot table and pivot chart reportii. 制作透视有的方法a) 按向导操作：a) 透视表的布局：b) 分析出需求中要求计算的字段（data）c) 分析出需求中要求显示字段（除去计算的都是要显示）d) 显示指定项pagee) 显示多项或所有项rowb) 直接拖放字段名a) 如果row中有多项字段，可将一项放到column中形成交叉表b) Page ,row,column中可以为空，但不能放重复的内容.data可以重复,但不能为空.数据透视图：单击工具栏上图标按钮六、 工作表管理 1、 链接工作表 i. 超级链接 InserthyperlinkPlace in this documentii. 公式链接 =select source dataiii. 粘贴链接 copypaste specialpaste linkiv. 矩阵链接 insertnamedefineselect target cellinsert menunamepase2、 名称引用：定位功能，用作公式中的参数,跨工作表操作。i. 方法：选择要命名的区域，单击名称框输入名字：ii. 删除：insertnamedefinedelete3、 数据合并（如全年报表合计）i. DataConsolidate选择要合并的区域AddUse Lable4、 组合i. Select the Columns or RowsGroup and outlineGroupii. alt+shift+iii. 取消组合alt+shift+5、 创建报表视图 自定义视图：Viewcustomer viewsadd 显示视图：Viewcustomer viewsshow七、 共享和保护1、 保护指定的区域：i. Ctrl+A选择所有单元格ii. FormatCellsProtection取消locked Hiddeniii. 选择要保护的单元格区域iv. FormatCellsProtection选中locked Hiddenv. ToolsProtectionProtect Sheet加密，取消第一个复选框。2、 工作簿保护i. ToolsProtectionProtect Workbook加密3、 文件的保护：i. ToolsOptionsSecurityPassword To Open 或者 Password To Modify4、 共享工作簿Toolsshare workbook八、 宏1、 宏的制作i. VBA CODEii. 录制2、 宏的执行i. 菜单ii. 快捷键iii. 制作宏按钮 ViewToolbarsFormbutton3、 调试宏 i. Toolsmacromacorsii. 选择一个调试的macro单击editiii. 按F8单步执行macro code4、 编辑宏i. 修改前：Sub Macro1() Range(C10).Select Range(A5:J82).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range(D1:E2), Unique:=FalseEnd Subii. 修改后：Sub Macro1() Range(C10).Select Range(A5:J82).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range(D1).currentregion, Unique:=FalseEnd Sub九、 IF逻辑块1、 趋势分析：i. TREND(known_ys,known_xs,new_xs,const)ii. CTRL+SHIFT+ENTER2、 变量求解：ToolsGoal seek3、 规划求解：ToolsAdd InsSolver4、 方案管理器：ToolsScenario 5、 频率分布：ToolsAdd InsAnalysis ToolpakHistogram6、 数据审核：可以让你对公式和结果看的明明白白 审核工作表工具公式审核显示审核工具栏 Toolsformula auditingshow formula auditingcircle invalid data 单元格的追踪： Toolsformula Auditingshow formula Auditingtrace precedents7、 数据确认：i. Datavalidationsettingscustoma) 数值的效性：=SUM($H$4:$H$25)=5000b) 数值的唯一性：=COUNTIF($A$4:$A$18,A4)=1ii. 下拉列表的制作：a) 内容来源于其他表格:a) 选择要制作下拉列表的内容区域，命名b) 选择要使用的区域，datavalidationallowlistsourcenameb) 内容自定义：选择要使用的区域，datavalidationallowlistsource,输入内容，任意两内容之间英文半角的逗号隔开8、 使用IF函数：IF(logical_test,Value_if_true,Value_if_false)i. 嵌套IF函数= IF(条件,真, IF(条件,真,假)ii. 合并逻辑函数IF(AND(条件),真,假) a) IF嵌套（一个IF最多可以嵌套7个if）共8个IFiii. =SUMIF(range, criteria,data_range)a) Range:条件所在的区域b) Criteria:条件，表达式用”c) Data_Range：求和的区域iv. =COUNTIF(range,criteria)十、 高级数据管理1、 HLOOKUP/VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)i. lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。ii. table_array：数据源，命名数据源。iii. col_