Excel高级应用技巧

Excel培训

—高级篇，杰出效率

版权所属1培训提纲

Excel高级使用技巧

Excel函数使用技巧

Excel数据分析技巧

Excel图表技巧2

Excel高级使用技巧构思和设置表结构建立和输入数据编辑和修饰表格简单使用和分析保存表格数据打印输出数据增加3

Excel高级使用技巧

-构思及表结构设置制作过程重点技巧构思和设置结构控制表格结构的合理性数据项目拆（独立列）与逻辑关系建立和输入数据把握表格数据的准确性输入技巧与建立和复制公式关系编辑和修饰表格增强表格数据的可读性编辑对象、数字、对齐条件格式等简单使用和分析有效运用数据的分析性组织数据、创建、编辑和修饰图表保存表格数据正确进行文件归档保存保存、另存为及安全性打印输出数据掌握表格数据打印输出小表格、大表格及超表格的打印技巧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表格中输入人名时为了美观，我们一般要在两个字的人名中间空出一个字的间距,选中该列，点击“格式→单元格→对齐”，在“水平对齐”中选择“分散对齐文件加密另存新档/工具/一般选项部分加密选取整篇文档/右键储存格格式/保护/解锁/选区需要加密区域/右键储存格格式/保护/加锁/菜单工具/保护文档9

Excel函数使用技巧Excel数学函数Excel逻辑函数Excel文本函数Excel日期时间函数Excel查询与引用10函数类函数函数名称常用否Excel的数学函数ABS返回参数的绝对值NCOUNTIF计算给定区域内满足特定条件的单元格的数目YINT返回实数舍入后的整数值NROUND返回某个数字按指定位数舍入后的数字YSUBTOTAL返回数据清单或数据库中的分类汇总NSUM返回某一单元格区域中所有数字之和NSUMIF根据指定条件对若干单元格求和Y

Excel函数使用技巧

-数学函数函数函数函数名称显示常用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)。Y

Excel函数使用技巧

-逻辑函数函数类函数函数名称显示常用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)Y

Excel函数使用技巧

-时间函数函数类函数函数名称显示常用否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)16

Excel函数使用技巧

-函数案例讲解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数据透析表18

Excel数据分析技巧

-分级显示及分类汇总部门工号身份证号码姓名应纳税所得额税率速算个人所得税DSA00011AAA

10,000.000.20325

1,675.00DSA00012BBB

500.000.050

25.00DSA00013CCC

1,500.000.1025

125.00DSA00014DDD

3,800.000.15125

445.00DSA0汇总

15,800.00475

2,270.00GSA00015EEE

4,000.000.15125

475.00GSA00016FFF

800.000.1025

55.00GSA00017GGG

1,200.000.1025

95.00GSA00018HHH

650.000.1025

40.00GSA0汇总

6,650.00200

665.00总计

22,450.00675

2,935.0019数据透视表是Excel中最具技术性的复杂组件之一。数据透视表的本质是将表格数据库变为动态数据汇总报告。数据透视表的优势：人性化：方便的操作和界面灵活性：可以方便的得到任何类别的数据

Excel数据分析

-数据透析表-使用数据透视表分析数据20如何生成数据透视表下面我们以此数据来进行数据透视表的分析。21为数据透视表选择合适的数据虽然Excel可以对任何数据生成数据透视表，但并不是所有的数据资料都可以从中受益。一般而言，数据资料应为数据库格式，包含下面两种类型的字段：数据（包括数字或文本）类别：对数据的描述数据的第一行要求为项目标题。当数据列不包含数字时数据透视表默认为以计数的方式使用数据；当数据列为数字时默认为使用求和方式。22如何生成数据透视表Step1:选择工具栏中的：数据数据透视表和数据透视图。23如何生成数据透视表Step2:一般情况按照默认的选项即可，单击下一步。上部分选项选择数据来自本Excel文件，或其它数据类型，或多个Excel数据文件。24如何生成数据透视表Step3:弹出对话框，选择将要生成数据分析表的数据源。当活动单元格的位置在数据区域时，透视表会自动选择数据区域。25如何生成数据透视表Step4:选择新建工作表，使数据透视表生成在一张新的工作表中，点击“完成”。选择“布局”和“选项”可以设置一些数据透视表的模式，但不是必需的，所以如果是新手入门就当作没有这些键好了，但是我们也分别解释一下。26布局在“布局”对话框里，可以将透视表的各字段安排在不同的位置。但是这个步骤也可以在数据透视表建立后进行。27选项数据透视表提供了丰富的“选项”。列总计：以列汇总；行总计：以行汇总；打开时刷新：打开文件时自动进行数据刷新；显示明细数据：可以显示每个数字对应的数据库中的数据；28如何生成数据透视表Step5:生成新的一页工作表，并会弹出包含所有项目标题的对话框。29如何生成数据透视表Step6:将项目标题拖到左侧表格的对应位置，一张数据透视表就完成了。30如何读取数据透视表项目选项：选择不同组合下的数据。点击右键，对交叉表进行字段分析，如求和，计数，最大值等31数据透视表的组成部分列字段。如除主详细特征；行字段。如time；数据区域。中间的数字；总数求和。如合计；元素。如个人；条件。如city;字段列表。32移动在数据透视表中无法进行普通的操作，例如插入或删除一行或一列，也不能对数字进行硬性修改。如何移动数据透视表中的项目？将鼠标移到想要移动的项目，点右键，选择“顺序”，然后选择想要移动方向即可。注意只能移动项目，而不能仅仅移动数据。33手工组合组合项目是数据透视表的一个有用特性。如何将选项进行组合？选择要组合的元素，点击右键，选择“组合显示明细数据”“组合”，出现新的项目名称“city2”，将生成的“数据组1”改为“重点城市”。这样就可以得到分“重点城市”和“其他城市”的数据。34手工组合35自动组合当被组合项为数字时，可以进行自动组合。例如日期可以自动选择按照月、季度、年等组合；收入，年龄等都可以按照自己指定的区间段进行组合；当进行手工组合时会生成新的元素，如对“收入”进行组合会生成“收入2”的元素；在进行自动组合时没有新的元素生成，组合数据替代了详细数据；因此建议在原数据库中复制一列数据。36插入计算字段数据透视表有自我进行计算的功能，这种功能叫做“插入计算字段”。例如在数据透视表中有销售额和销量的数据，则可以通过计算来得到单价的数据。步骤：“数据透视表工具栏””公式””计算字段”在“名称”写入新建数据的名称；在“公式”内键入计算公式；确定。37数据透视表的其他阅读详细数据对于数据透视表产生的数据可以查看详细的数据产生来源，具体的方式是双击此数。例如数据透视表计算出库中有3个样本符合某条件，那么通过双击此数可以得到具体是哪3个样本符合这个条件，并在新的工作表中显示。在双击前请保证“表格选项”中的“显示明细数据”已被选择。设置报告格式如果需要直接把数据透视表作为报告提交，那么一个正规的格式是必须的，请在“数据透视表工作栏”中选择“设置报告格式”，选择合适的个是显示数据透视表选择显示元素点击数据透视表