搜索
Excel培训—高级篇，杰出效率版权所属1培训提纲Excel高级使用技巧Excel函数使用技巧Excel数据分析技巧Excel图表技巧2Excel高级使用技巧构思和设置表结构建立和输入数据编辑和修饰表格简单使用和分析保存表格数据打印输出数据增加3Excel高级使用技巧-构思及表结构设置制作过程重点技巧构思和设置结构控制表格结构的合理性数据项目拆（独立列）与逻辑关系建立和输入数据把握表格数据的准确性输入技巧与建立和复制公式关系编辑和修饰表格增强表格数据的可读性编辑对象、数字、对齐条件格式等简单使用和分析有效运用数据的分析性组织数据、创建、编辑和修饰图表保存表格数据正确进行文件归档保存保存、另存为及安全性打印输出数据掌握表格数据打印输出小表格、大表格及超表格的打印技巧4构思及表结构设置表结构设置指北针凡须独立管理的项目，必须单独设置项目名称，独占一列，避免混合设置管理项目的独立性必须与扩展性结合考虑，以方便统计分析表格结构设计必须考虑后期统计分析的应用状态。控制原始数据输入准确性的解决方法用调用法控制原始数据的准确性用计算法控制加数据的准确性使用校对工具5其他技巧-录入技巧下拉菜单一、直接输入：1.选择要设置的单元格，譬如A1单元格；2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口；3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据，譬如“1,2,3,4,5,6,7,8,9”(不包括双引号，分割符号“,”必须为半角模式)→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。6其他技巧-录入技巧下拉菜单如果同一工作表的某列就是下拉菜单想要的数据，譬如引用工作表Sheet1的B2：B5，B2：B5分别有以下数据：1、2、3、4，操作如下：1.选择要设置的单元格，譬如A1单元格；2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口；3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据“=$B$2:$B$5”，也可以按右边带红色箭头的直接选择B2：B5区域→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。7其他技巧-定义工作表名称1.CTRL+F32.=OFFSET(费用明细!$A$1,,,COUNTA(费用明细!$A:$A),COUNTA(费用明细!$1:$1))8其他高级技巧功能命令选择整个单元格范围Ctrl+Shift+*快速关闭多个文件按住Shift键，打开“文件”菜单，单击“全部关闭”选定多个工作表一组相邻的工作表，可先选第一个表，按住Shift键，再单击最后一个表的标签；选不相邻的工作表，要按住Ctrl键对多个工作表快速编辑选定多个工作表，可在相同位置设置1.页面设置2.输入相同的数据3.快速设置4.输入公式快速浏览长工作簿按下“Ctri+Home”键可以回到当前工作表的左上角(即A1单元格)，按下“Ctrl+End”键可以跳到工作表含有数据部分的右下角。同一个单元格换行Alt+回车键巧变文本为数字在空白的单元格中填人数字1，然后选中这个单元格，执行“复制”命令，然后再选中所要转换的范围，选择“选择性粘贴”中的“乘”在单元格中输入0值设置单元格格式”，在接着出现的界面中选“数字”标签页，在列表框中选择“文本”分散对齐在Excel表格中输入人名时为了美观，我们一般要在两个字的人名中间空出一个字的间距,选中该列，点击“格式→单元格→对齐”，在“水平对齐”中选择“分散对齐文件加密另存新档/工具/一般选项部分加密选取整篇文档/右键储存格格式/保护/解锁/选区需要加密区域/右键储存格格式/保护/加锁/菜单工具/保护文档9Excel函数使用技巧Excel数学函数Excel逻辑函数Excel文本函数Excel日期时间函数Excel查询与引用10函数类函数函数名称常用否Excel的数学函数ABS返回参数的绝对值NCOUNTIF计算给定区域内满足特定条件的单元格的数目YINT返回实数舍入后的整数值NROUND返回某个数字按指定位数舍入后的数字YSUBTOTAL返回数据清单或数据库中的分类汇总NSUM返回某一单元格区域中所有数字之和NSUMIF根据指定条件对若干单元格求和YExcel函数使用技巧-数学函数函数函数函数名称显示常用Excel逻辑函数COUNTIF根据指定条件对若干单元格次数COUNTIF(B4:B10,90)YAND其中Logical1,logical2,...表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用AND(logical1,logical2,...)，YOROR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUEOR(B1:B3)YIFIF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。IF(logical_test,value_if_true,value_if_false)。YExcel函数使用技巧-逻辑函数函数类函数函数名称显示常用Excel函数引用之文本函数CONCATENATE将若干文字串合并到一个文字串中。CONCATENATE(text1,text2,...)YDOLLAR依照货币格式将小数四舍五入到指定的位数并转换成文字。DOLLAR或RMB(number,decimals)EXACT该函数测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则，返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。利用函数EXACT可以测试输入文档内的文字。EXACT(text1,text2)LEFTLEFT基于所指定的字符数返回文本串中的第一个或前几个字符。LEFT(text,num_chars)YMIDMID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text,start_num,num_chars)YRIGHTRIGHT根据所指定的字符数返回文本串中最后一个或多个字符。RIGHT(text,num_chars)YTRIM除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数TRIM。TRIM(text)YVALUE将代表数字的文字串转换成数字。VALUE(text)Excel函数使用技巧-引用及文本函数函数类函数函数名称显示常用否Excel时间函数MONTH返回以系列数表示的日期中的月份。月份是介于1（一月）和12（十二月）之间的整数。MONTH(serial_number)YTODAY返回当前日期的系列数，系列数是MicrosoftExcel用于日期和时间计算的日期-时间代码。TODAY()YEAR返回某日期的年份。返回值为1900到9999之间的整数。YEAR(serial_number)YExcel函数使用技巧-时间函数函数类函数函数名称显示常用否Excel查询与引用函数VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)YHLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)YTRANSPOSETRANSPOSE用于返回区域的转置。函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。使用函数TRANSPOSE可以改变工作表或宏表中数组的垂直或水平走向。由于此处是以数组公式输入，因此需要按CRTL+SHIFT+ENTER组合键来确定为数组公式，此时会在公式中显示{}。随即转置成功TRANSPOSE(array)Excel函数使用技巧-查询及引用函数Excel函数使用技巧-函数案例讲解1案例详见插入EXCEL文档函数类函数应用常用否语法格式隐藏函数DATEDIF返回两个日期之间的年\月\日YDATEDIF(起始日期,终止日期,Y/M/D)NUMBERSTRING返回数字的大小写NNUMBERSTRING(数字,1/2)DATESTRING返回日期的中文NDATESTRING(日期)数学函数ABS返回参数的绝对值NABA(数字)COUNTIF计算给定区域内满足特定条件的单元格的数目YCOUNTIF(选择列,筛选规则)ROUND返回某个数字按指定位数舍入后的数字YROUND(数字,保留位数)SUMIF根据指定条件对若干单元格求和YSUMIF(选择列,筛选规则,汇总计算列)逻辑引用AND同时设定条件，需满足其中所有条件YAND(logical1,logical2,...)，OR同时设定条件，只满足其中一项条件即可YIF条件函数YIF(条件,符合条件返回值,不符合条件返回值)。VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。YVLOOKUP(参照列,选择区域,返回第几列的值，0/FALSE)16Excel函数使用技巧-函数案例讲解2案例详见插入EXCEL文档函数类函数应用常用否语法格式逻辑引用HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。YHLOOKUP(参照行,选择区域,返回第几行的值，1/FALSE)RANK返回某一数值在一列数值中的相对于其他数值的排位YRANK（Number,ref,order）(ORDER0升序，order1降序）文本函数CONCATENATE将若干文字串合并到一个文字串中。NCONCATENATE(text1,text2,...)LEFTLEFT基于所指定的字符数返回文本串中的第一个或前几个字符。YLEFT(text,num_chars)MIDMID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。YMID(text,start_num,num_chars)RIGHTRIGHT根据所指定的字符数返回文本串中最后一个或多个字符。YRIGHT(text,num_chars)TRIM除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数TRIM。YTRIM(text)时间函数MONTH返回以系列数表示的日期中的月份。月份是介于1（一月）和12（十二月）之间的整数。YMONTH(serial_number)TODAY返回当前日期的系列数，系列数是MicrosoftExcel用于日期和时间计算的日期-时间代码。TODAY()YEAR返回某日期的年份。返回值为1900到9999之间的整数。YYEAR(serial_number)17Excel数据分析技巧Excel分级显示及分类汇总Excel数据透析表18Excel数据分析技巧-分级显示及分类汇总部门工号身份证号码姓名应纳税所得额税率速算个人所得税DSA00011AAA10,000.000.203251,675.00DSA00012BBB500.000.05025.00DSA00013CCC1,500.000.1025125.00DSA00014DDD3,800.000.15125445.00DSA0汇总15,800.004752,270.00GSA00015EEE4,000.000.15125475.00GSA00016FFF800.000.102555.00GSA00017GGG1,200.000.102595.00GSA00018HHH650.000.102540.00GSA0汇总6,65