Excel实用教程大全

1、Excel高级使用教程大纲公式和格式(此部分主要用于使报表做得更规范、更有效)1、常用函数介绍求和类：Suma) SUM (numberl ,number2,)计数类： Count、countaCOUNT (value1 ,value2,.)COUNTA (value1 ,value2,)日期类：Today、Now (日期可进行加减)TODAY ()NOW ()财务类：PMT (rate,nper,pv,fv,type)FV(rate,nper,pmt,pv,type)2、序列填充方式：公式填充：拖动填充手柄+数字序列填充：同时选择已知2位数字拖动填充手柄+选择已知1位数字Ctrl+拖动填充手

2、柄+文本序列填充：Tools Option Custom list Newa)日期序列填充：同时选择已知日期值拖动填充手柄+3、单元格的引用：相对引用：A1在行中复制时，行不动，列在动，在列中复制时，列不动,行在动.绝对引用：$A$1 f4不能动白要加$混合引用：$A1 , A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。4、输入数据的技巧选择活动单元格周围的相邻区域 ：Ctrl+a选择活动单元格相邻方向上的区域：ctrl+shift+方向键移动列：按shift+拖动边框长数字的输入：3412 formatnumbertext分数的输入：0空格1/5单元格中的内

3、容换行：定位光标到要换行处Alt+enter快捷日期输入 “ Ctrl + ;” 当前时间输入 “Ctrl + shift + ;5、 条件格式：Format Conditional formatting自身值决定自已的格式。由公式来决定格式。And(条件1,条件2,条件3)Or(条件1,条件2,条件3)6、输入数据的有效性：数据有效性Data Validation list粘帖和选择性粘帖：Copy Edit Paste special粘贴数值或公式粘贴链接转置：行和列的切换8、格式刷的使用：双击多次应用格式单击 单次应用格式9、工作表背景 Format Backgroup10、自定义菜单和

4、工具栏Tools Customize 拖放按钮到工具栏11、模板应用模板： File New Templates on my computer spreadsheet solutions自制模板： File Save as Template二、预览打印及发送文档（此部分可令打印更技巧，节省纸张）1、打印页面设置：.调整打印大小文件页面设置：ii.在每页上都打印行列标题（在每页上都打印标题，避免要与第一页对照才可找到数 据说明）File Page setup Sheet：打印批注： File Page setup Sheet comments创建页眉页脚：打印预览 页眉页脚2、使用E-mail功

5、能：文件发送工具栏：单击 e-mail图表和批注1、批注 Insert Comment图表Insert ChartType修改图表类型：各种图表的作用修改图表数据：刻度、色彩等组合图表的使用（一类数据系列使用一种图表；另一类数据系列使用另种图表）双轴线柱图及饼图的制作四、 数据库入门1、排序排行：a）单依据：定位光标到依据列，单击按钮b）多依据：datasort排列2、筛选：（按条件显示记录）自动筛选：datafilterauto filtera）指定内容筛选b）取极端数据top 10：（只对数值性字段有效）c）自定义：（最多可以定义两个条件，and or）高级筛选：a）创建条件区域：b）将条

6、件中用到的列的标题复制到原表格以外的某处c） 在复制出的标题下方写条件。d）如果条件写在同一行中为and如果条件写在同一列中为or*:表示多个字符？：只表示一个字符如：Make ModelHondaChevroletfordTypeRate*family*family*family*Price Price2000030000Datafilteradvanced filter去除重复记录：3、数据库函数:=dsum(database,field,criteria)=daverage()=dmax()=dmin()=dcount().=dcounta()4、记录单(便于定位，录入数据)i. Dat

7、a:Forms数据库管理1、数据导入和导出Export: FileSave as type Text （Tab delimited）Import:FileOpen typeText Import wizard2、分类汇总Data subtotal设置分类自段/汇总方式3、数据透视表及数据透视图i.ii.datapivot table and pivot chart report制作透视有的方法按向导操作：a）透视表的布局：b）分析出需求中要求计算的字段（data）c）分析出需求中要求显示字段（除去计算的都是要显示）显示指定项 page显示多项或所有项 rowb）直接拖放字段名a）如果row中有

8、多项字段，可将一项放到column中形成交叉表Page ,row,column中可以为空，但不能放重复的内容.data可以重复，但不能为空.数据透视图:单击工具栏上图标按钮工作表管理1、链接工作表超级链接Insert hyperlink Place in this document公式链接= select source data粘贝占链接 copy paste special paste link矩阵链接 insert name define select target cell insert menu name pase2、名称引用：定位功能，用作公式中的参数，跨工作表操作。方法：选择要命名

9、的区域，单击名称框输入名字：删除：insertnamedefinedelete3、数据合并（如全年报表合计）Data Consolidate 选择要合并的区域Add Use Lable4、 组合Select theColumns orRows Group and outline Groupalt+shift+取消组合alt+shift+5、创建报表视图-自定义视图： View customer views add-显示视图： View customer views show七、共享和保护J1、保护指定的区域：Ctrl+A选择所有单元格取消 locked Hidden选择要保护的单元格区域选中

10、locked Hidden加密，取消第一个复选框。2、工作簿保护i.加密3、文件的保护：i. ToolsOptionsSecurityPassword To Open 或者 Password To Modify4、共享工作簿Toolsshare workbook八、 宏1、宏的制作VBA CODE录制2、宏的执行菜单快捷键制作宏按钮View Toolbars Form button调试宏Toolsmacromacors选择一个调试的macro单击edit按F8单步执行macrocode4、编辑宏修改前：Sub Macro1()Range(C10).SelectRange(A5:J82).Adv

11、ancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range(D1:E2), Unique:=FalseEnd Sub修改后：Sub Macro1()Range(C10).SelectRange(A5:J82).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(D1).currentregion, Unique:=False End Sub九、IF逻辑块1、趋势分析：TREND(known_ys,known_xs,new_xs,const)CTRL+SHIFT+ENT

12、ER2、变量求解：Tools Goal seek3、规划求解：Tools Add Ins Solver4、方案管理器：Tools Scenario5、频率分布：Tools Add Ins Analysis Toolpak Histogram6、数据审核：可以让你对公式和结果看的明明白白? 审核工作表 工具公式审核显示审核工具栏一 Toolsformula auditingshow formula auditingcircle invalid data? 单元格的追踪：一 Toolsformula Auditingshow formula Auditingtrace precedents7、数据

13、确认：Datavalidationsettingscustom数值的效性：=SUM($H$4:$H$25)=5000数值的唯一性：=COUNTIF($A$4:$A$18,A4)=1下拉列表的制作：a)内容来源于其他表格：a)选择要制作下拉列表的内容区域，命名b) 选择要使用的区域，=nameb) 内容自定义：选择要使用的区域，datavalidationallowlistsource,输入 内容，任意两内容之间英文半角的逗号隔开8、使用 IF 函数：IF(logical_test,Value_if_true,Value_if_false)嵌套IF函数=IF(条件，真，IF(条件，真，假)合并逻辑函数IF(AND(条件)，真，假)a) IF嵌套(一个IF最多可以嵌套7个if)共8个IF=SUMIF(range, criteria,data_range)Range:条件所在的区域Criteria:条件，表达式用Data_Range:求和的区域=COUNTIF(range,criteria)十、高级数据管理HLOOKUP/VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。table_array：数据源，命名数据源。col_in