Excel函数教材培训

Excel函数培训教材演讲人：一、相对引用和绝对引用二、常用函数解析三、结合公式自定义数据有效性和条件格式培训大纲相对引用和绝对引用一、相对引用和绝对引用如图：C1=A1+B1，如果将C1单元格复制粘贴到D1，得到的结果是7而不是原来是单元格的值5，

这是因为复制过程中我们一并也把公式复制到了D1，当计算结果栏位由原来的C1偏移到D1时，则参与计算的其他栏位也会同时发生偏移，即D1=B1+C1。倘若将A1、B1绝对引用，那么参与公式计算是相应栏位就被锁定，若此时将C1复制粘贴到其他栏位，得到的结果就是5如果只是想粘贴数值，则可在粘贴的时候用“选择性粘贴”只针对数值进行粘贴，如下图在将C1栏位粘贴到D1栏位时，右击—选择性粘贴—数值，则的得到的结果将是原来C1栏位的数值5（无论是绝对引用还是相对引用）。一、相对引用和绝对引用混合引用是有绝对的行或相对的列。绝对引用列是采用$A1、$B1等形式。绝对引用行是采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用的改变而绝对引用的不变。如果多行或多列的复制公式，相对引用自动调整而绝对引用将不做调整。例如，下图公式中绝对引用了A列和23行，公式复制过程中只对相对引用的部分进行调整。注：相对引用与绝对引用可以通过快捷键“F4”进行相互切换。常用函数解析（1）日期与时间函数TODAY():返回日期格式的当前日期NOW():返回日期时间格式的当前日期和时间WEEKDAY():返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。语法：WEEKDAY(serial_number，return_type)参数：Serial_number是要返回日期数的日期，通常用带引号的文本串(如“2001/02/26”)。Return_type为确定返回值类型的数字，数字1或省略，则1至7代表星期天到星期六；数字2，则1至7代表星期一到星期天；数字3，则0至6代表星期一到星期天。（1）日期与时间函数NETWORKDAYS(）函数专门用于计算两个日期值之间完整的工作日数值。这个工作日数值将不包括双休日和专门指定的其他各种假期。语法：NETWORKDAYS(Start_date,End_date,Holidays)Start_date表示开始日期；End_date为终止日期，Holidays表示作为特定假日的一个或多个日期。这些参数值既可以手工输入，也可以对单元格的值进行引用。例如，下图中要计算2012-5-1到2012-5-31的工作日，A2为起始日期，B2为终止日期C2:C4为特定的假日。计算公式如下图：注：手工输入的日期需加英文状态的引号。可Ctrl+.进行切换。（1）日期与时间函数WORKDAY：返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和专门指定的假日)的某一日期的值，并扣除周末或假日。语法：WORKDAY(start_date，days，holidays)参数：Start_date为开始日期；Days为Start_date之前或之后不含周末及节假日的天数；Days是正值将产生未来日期、负值产生过去日期；Holidays为可选的数据清单，表示需要从工作日中排除的日期值(如法定假日或非法定假日)。此清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。例如，下图中要计算2012-12-25前排除特定节假日往前、后推10个工作日的日期，计算公式如下：（1）日期与时间函数DATEDIF（）返回两个日期之间的年\月\日间隔数。语法：DATEDIF(start_date,end_date,unit)，Start_date代表时间段内的第一个日期或起始日期。End_date它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。Unit返回"Y"时间段中的整年数。"M"时间段中的整月数。"D"时间段中的天数。"YD"start_date与end_date日期中天数的差。忽略日期中的年。"MD"start_date与end_date日期中天数的差。忽略日期中的月和年。"YM"start_date与end_date日期中月数的差。忽略日期中的日和年。注：结束日期必须大于起始日期。（2）统计函数COUNTIF（）用于计算区域中满足给定条件的单元格的个数。语法：COUNTIF(range，criteria)参数：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。（2）统计函数COUNTIFS（）用于计算区域中满足给定多条件的单元格的个数。语法：COUNTIFS(range1，criteria1，range2，criteria2，…)Rang1为需要计算其中满足条件1的单元格数目的单元格区域。Criteria1为确定哪些单元格将被计算在内的第一个条件,Rang2为需要计算其中满足条件2的单元格数目的单元格区域。Criteria2为确定哪些单元格将被计算在内的第二个条件，以此类推，每一对range和criteria之间用逗号隔开。（2）统计函数SUMIF():根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(range，criteria，sum_range)参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。例如，下图中要计算销售部的工资总和，A列为用于条件判断的单元格区域，$E$4为判断条件，C列需要求和的区域。计算公式如下：（2）统计函数SUMIFS():根据指定的多条件对若干单元格、区域或引用求和。语法：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)参数：sum_range是需要求和的实际单元格区域criteria_range1为用于条件1判断的单元格区域。criteria1为条件1。criteria_range2为用于条件2判断的单元格区域。criteria2为条件2。例如，下图中要计算求8月1日以前的笔记本销量总额以及求8月1日到8月7日期间服务器的销量总额，属于多条件引用求和。计算公式如下：（3）查找和引用函数VLOOKUP（）：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)参数：Lookup_value为需要在数据表第一列中查找的数值，Table_array为需要在其中查找数据区域。Col_index_num为table_array中待返回的匹配值的列序号。Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果Range_lookup为1或省略，则返回近似匹配值（模糊匹配），如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为0，函数VLOOKUP将返回精确匹配值。注：1.查找值一定要在第一列。2.模糊匹配时第一列一定要升序排列。（3）查找和引用函数精确查找案例，下图中要查找AB-S05的销售额，G3为要查找的数值，A:D为要查找的区域，4表示要查找值在区域的第4列，0表示精确查找，公式如下图所示：模糊查找案例，下图中要查找收入$21,566对应的税率，B3为要查找的数值，D3:F9为要查找的区域，3表示要查找值在区域的第3列，1表示模糊查找，公式如下图所示：（3）查找和引用函数OFFSET（），以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。如下图使用OFFSET函数实现动态图表，OFFSET最少包含了3个参数，其中第一个参数表示从该栏位进行偏移（起点），第二个参数表示行偏移数，第三个参数表示列偏移数，所以当第二、第三参数值变化时，对结果区域引用并插入图表，则该图表就是会随参数值的变化而变化。（4）文本函数CONCATENATE（）：将若干文字串合并到一个文字串中，其功能与"&"运算符相同。语法：CONCATENATE(text1，text2，...)参数：Text1，text2，...为将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。LEN()函数返回文本字符串中的字符数。语法：LEN(text)Text是要查找其长度的文本。空格将作为字符进行计数。（4）文本函数MOD():函数返回两数相除的余数。结果的正负号与除数相同。语法：MOD(number,divisor)Number为被除数；Divisor为除数。right（）：根据所指定的字符数返回文本串中最后一个或多个字符。语法：RIGHT(text，num_chars)参数：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字符数，它必须大于或等于0。LEFT（）：根据所指定的字符数返回文本串中第一个或多个字符。语法：RIGHT(text，num_chars)参数：Text是包含要提取字符的文本串；Num_chars指定希望left提取的字符数，它必须大于或等于0。（4）文本函数MID()：返回文本串中从指定位置开始的特定数目的字符。语法：MID(text，start_num，num_chars)参数：Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置，Num_chars指定希望MID从文本中返回字符的个数。数据分列：可以对单元格的内容进行拆分，采用数据分列—固定宽度或分隔符进行拆分，拆分过程中注意相应的格式。（5）逻辑函数IF():执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测。语法：IF(logical_test，value_if_true，value_if_false)。参数：Logical_test计算结果为TRUE或FALSE的任何数值或表达式；Value_if_true是Logical_test为TRUE时函数的返回值。而且Value_if_true可以是一个表达式；Value_if_false是Logical_test为FALSE时函数的返回值。Value_if_false也可以是一个表达式。（5）逻辑函数IF函数可以多层嵌套使用，但最多只能嵌套7层例如，下图中要将人员的总分按照大小进行分级，公司如下：结合公式自定义数据有效性和条件格式三、结合公式自定义数据有效性和条件格式限定栏位中的文本长度：例如，身份证号通常是18位，当输入的位数不等于18位是需要能够进行提示，我们就可以通过“条件格式”来实现提示，当号码不等于18位是显示红色。设置过程如下图：条件格式----新建规则----设置公示----编辑相应的格式还可以通过查看“条件格式----管理规则”查看公式、格式、应用范围是否正确三、结合公式自定义数据有效性和条件格式通过公式自定义设置单元格格式，例如，下图中要将不同大小值的单元格设置不同的格式。右击设置单元格格式----自定义----用公式对通用格式进行编辑。例如下图中要将A列数值根据大小设置相应的颜色，Excel函数培训教材EXCEL操作培训Excel技巧与操作培训演讲人：ONTENTSC目录Excel操作与技巧2314基础常识报表制作图表制作常用技巧基础常识1Excel基础常识（1）工作簿（2）工作表（3）单元格工作簿菜单栏单元格，通过位置（列标行标）进行标识。工作表状态栏1张工作表（sheet）由65536行*256列，即65536*256=1677万个单元格组成；1个工作簿（book）最多可含有255张工作表。注：EXCEL2007以下版本。工具栏Excel基础常识（1）公式（2）函数公式与函数公式：在工作表中对数据进行计算的式子，它可以对工作表数值进行加、减、乘、除等运算。函数：EXCEL预定义的内置程序，可以使用在公式中。公式与函数数据类型（1）数值型：一般的数字格式。（2）货币型：货币格式，对一列数值进行小数点对齐。（3）日期时间型：可以对两个时间段的间隔时长进行计算。（4）文本型：在文本单元格中，数字、公式作为文本处理。公式与函数运算符（1）算术运算：+（加）、-（减）、*（乘）、/（除）、%（百分比）、^（乘方)（2）比较运算：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）（3）字符运算：&（连接）（4）引用运算：（：冒号）、（，逗号）、（空格）要创建一个公式，首先需要选定一个单元格，输入一个等于号“=”，然后在其后输入公式的内容，按回车键就可以按公式计算得出结果公式与函数单元格引用引用运算符：（：冒号）、（，逗号）、（空格）（1）：（冒号）——区域运算符，如B2:F6表示B2单元格到F6单元格矩形区域内的所有单元格。（2），（逗号）——联合运算符，将多个引用合并为一个引用，如SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有单元格求和（SUM是求和函数）（3）（空格）——交叉运算符，如SUM(B5:B26A4:D22)表示求B5至B26与A4至D22两区域交叉单元格之和，即B5至B22之和。公式与函数绝对地址与相对地址相对地址：=B3+B4+B5+B6绝对地址：=$B$3+$B$4+$B$5+$B$6混合运用，F4切换。两者的差异是：相对地址：如果在A7单元格内输入公式=B3+B4+B5+B6，那么将A7中的公式复制至A8单元格时，A8的结果变成=B4+B5+B6+B7，再将A8中的公式复制至B8时，B8的结果变成=C4+C5+C6+C7，也就是相对地址在公式复制时，会随单元格的不同而发生变化。绝对地址：在公式复制时，不会随单元格的不同而发生变化。不管复制到哪个单元格，结果都是同一个公式，即=$B$3+$B$4+$B$5+$B$6。输入公式公式的输入类似于数据的输入，在单元格中输入公式时以等号（=）作为开始，然后输入公式表达式。（1）启动Excel2010，在工作簿中创建工作表。在工作表中选择需要创建公式的单元格，在单元格中输入公式，如表1所示。（2）完成公式输入后，按Enter键或单击其他的单元格，输入公式的单元格中将显示计算结果。输入公式在工作表中可以将公式复制到其他的单元格中。（1）将鼠标指针放置到需要复制的单元格右下角的控制柄上，鼠标指针变为十字形，按住鼠标左键拖动鼠标至最后一个单元格释放鼠标，公式将被复制到鼠标拖动过的单元格中并显示计算结果。（2）在工作表中选择源单元格，打开“开始”选项卡，在“剪贴板”组中单击“复制”按钮。在工作表中选择目标单元格，单击“粘贴”按钮上的下三角按钮，在下拉列表中“公式”选项，此时选择的单元格中将能预览公式粘贴后的计算结果。使用函数向导插入函数使用函数栏输入使用公式记忆输入功能在单元格中输入函数的第一个字母时，Excel2010会给出一个以该字母开头的函数列表，显示一个列表，按“↓”键在列表中移动光村选择需要的函数，按Tab键将选择的函数输入到单元格中。函数功能数值统计（SUM、SUMIF、COUNT、COUNTIF等）逻辑判断（AND、IF、ISERROR、NOT、TRUE等）文本处理（MID、MIDB、RIGHT、FIND等）日期时间（NOW、TODAY、TIME、TIMEVALUE等）查找引用（VLOOKUP、ROWS、HYPERLINK等）财务计算工程分析公式与函数SUM、SUMPRODUCT、SUMIF、SUMIFS函数公式与函数COUNT、COUNTA、COUNTIF、COUNTIFS函数公式与函数AND、OR、NOT函数公式与函数IF、ISERROR、IFERROR函数公式与函数MID、LEFT、RIGHT函数FIND函数NOW、TODAY函数TIME&TIMEVALUE函数VLOOKUP函数HYPERLINK函数注意事项(一)（1）括号（）要对应显示，不可出现单独的括号，或少括号（2）输入法的半角全角、大小写需注意，如：“”不可输入成“”*不可输入成×（3）如条件是寻找某个文字：南京，在函数中应输入为:“南京”如条件是寻找某个单元格或数字：A5(A列第5行)或5，在函数中应输入为：A5或5，无需加""符号（4）函数中设置的计算范围需一致（5）需在函数公式打开状态下,同时按下Ctrl+Shift+Enter,当公式头尾出现花括号｛｝时才算计算完成注意事项(二)（1）源数据较大时，应将“菜单栏/工具”的重新计算调整为“手动计算”（2）格式：不得出现空格，如：“黑电”和“黑电”（3）某一字段暂不需/无法统计，如是需量化的单元格，则需以“0”填充，不可写“无”（4）不可将明细全部删除后，在原有模板的基础上重写贴入明细再次使用，将会改变计算范围，应重新使用新模板（5）注意统计周期的输入（6）注意EXCEL函数设置中某条件的计算列是否是需要的计算列（7）如需从一个EXCEL表格的数据复制到另一表格中，应点击“菜单栏/编辑”的链接中“断开链接”报表制作2数据排序一次排序最多可以选择三个关键字：主关键字、次关键字、第三关键字。或者利用工具栏上的两个排序按钮。数据筛选数据透视数据透视表实际上是一种交互式表格，能够方便地对大量数据进行快速汇总，并建立交叉表，还能根据用户的需要显示区域中的细节数据。报表排版报表应当含有：1、报表名称（在表格抬头，通常字号略大些）2、表头（在表格主体的顶端与侧端，通常可用颜色着重标注）3、各字段数据值（如有文字内容应相对简略，避免视觉疲劳）报表需要：1、合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可）2、当表格过长或过宽时，应使用“冻结窗口”功能。图表制作3图表制作在处理电子表格时，需要对大量繁琐的数据进行分析和研究，我们需要利用图形方式再现数据变动和发展趋势。EXCEL提供了强有力的图表处理功能，使得用户很快就可得到所要的图表。我们日常工作和生活中经常使用的图表类型有：1、柱形图：比较分析2、折线图：趋势分析3、饼图：占比分析着重标色以柱形图为例常用技巧4快捷键复制Ctrl+C粘贴Ctrl+V查找Ctrl+F替换Ctrl+H数据全选