Excel常规操作与简单函数

1、EXCEL常规操作与简单函数CONTENT 目录01Excel简介02基本操作03简单函数0405其他数据透视表Part1Excel简介Part One Excel 简介Microsoft Office Excel 2007 是微软公司开发的表格式数据管理系统，也是办公人员必备的数据处理软件。利用Excel 2007，可以帮助用户进行简单的报个制作以及复杂的数据分析。默认情况下，每张工作簿包含三张工作表。常见后缀名和含义.xls后缀.xls是Excel 2007之前版本的使用的默认格式。它文件兼容性好，但是列最多只有256列，行无限。.xlsx后缀.xlsx是Excel 2007之后的版本使用

2、的默认格式，包括2007的版本。之前版本无法直接打开，版本较低不兼容。它的占用空间比较小，文件列无限、行无限，但不适合保存宏，容易丢失。基本操作Part2Part Two 基本操作快捷键描述快捷键描述CTRL+C复制CTRL+ S保存CTRL+V粘贴CTRL+F查找CTRL+ALT+V选择性粘贴CTRL+H替换CTRL+X 剪切CTRL+*选取单元格所在区域表格CTRL+D复制上一行CTRL键 选择多个不连续的目标CTRL+复制上一单元格SHIFT键先选中首个，再选中最后一个，之间的将全部被选中CTRL+A全选CTRL+ 箭头键移动到当前数据区域的边缘CTRL+Z撤销操作CTRL+SHIFT+

3、选中当前单元格向右所有连续单元格CTRL+Y 恢复操作CTRL+SHIFT+选中当前单元格向上所有连续单元格常用快捷键Part Two 基本操作常规：默认的常规格式。数值：可设置各种数字表示，保留小数点后几位，负数标红等。货币：可显示各类货币标符号。日期：根据需要设置日期格式，之后也可以进行日期的加减。文本：如输入身份证号、工作单号、手机号等，可以先将单元格设置成文本格式，再输号码便不会变成4.111E+5，但该格式无法进行公式计算。自定义：除了系统给的格式，我们可以用占位符“0”“#”加“元”方便编写。如：要显示001分这种格式，我们可以在自定义中写 00“分” 然后在单元格里直接输入“1”

4、即可；要显示1号这种格式，自定义中写 #“号” 然后在单元格里直接输入“1”即可。要在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”；要在输入数字数据之前自动添加文本，使用自定义格式为：”文本内容”。符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如：设置“340801” 可以直接录入身份证后12位。可在开始界面选择数字，或单元格内单击右键打开设置界面。设置单元格格式Part Two 基本操作相对引用与绝对引用1.相对引用 单元格或单元格区域的相对引用是指相对于包含公式的单元格的相对位置。例如单元格D3包含公式=A1+B1，值为3。在复制公式或者用填充柄拖动到单元格

5、E3中，公式就变成=B1+C1，此时计算结果的值为5。2.绝对引用 绝对引用是指引用单元格的绝对名称。$符号在这里表示锁定的含义。在例如讲前例中单元格D3中的公式进行绝对引=$A$1+$B$1，值为3。这时候再复制公式或者用填充柄拖动到单元格E3中，公式依旧是=$A$1+$B$1，值依旧为3。如果只是想粘帖数值，可以在粘贴的时候使用“选择性粘贴”只针对数值进行粘贴，结果将是单元格D1的值，不管是相对引用还是绝对引用。Part Two 基本操作4.相对引用与绝对引用之间的切换 如果创建了一个公式并希望将相对引用更改为绝对引用(反之亦然)操作步骤如下:u步骤1:选定包含该公式的单元格；u步骤2:在

6、编辑栏中选择要更改的引用并按 F4 键；u步骤3:每次按 F4 键时，Excel 会在以下组合间切换： 绝对列与绝对行(例如，$A$1)； 相对列与绝对行(A$1)； 绝对列与相对行($A1)； 相对列与相对行 (A1)。 3.混合引用 混合引用是指引用中包含绝对列或者绝对行的引用。例如 绝对引用列$A1或者$B1，而绝对引用行则是A$1或者B$1。如果公式所在单元格的位置改变，则相对引用的部分会随之改变，而绝对引用部分则不会改变。例如单元格C15，绝对引用了B列和14行，其公式为=$B15*C$14，复制到单元格C16时，公式为=$B16*C$14，复制到单元格D15时，公式为=$B15*D

7、$14。相对引用与绝对引用Part Two 基本操作1.排序 Excel有多种排序方法，按行、按列、升序、降序，也可以按颜色进行排序，有些时候我们也会多条件排序。l第一种普通的排序，我们选中所要排序的列的某一个单元格，点击右键，排序，选择要升序还是降序即可。l第二种排序就是自定义排序，操作和普通排序是一样的，只是自定义排序可以选择多个条件。2.筛选 筛选功能是非常的强大，可以单条件筛选，也可以多条件筛选，不同的格式筛选也会有不同的方法。例如：日期的我们可以筛选是介于某个时间段内、在某个时期之后的、或者是仅看某个月或者某个季度等等，像文本就是包含或者等于或者不等于某个字某个短语的，数字的就是更多

8、了，什么等于、不等于、大于小于，大于等于等等。筛选过的数据仅显示满足指定条件的行，对于筛选过的数据子集，可以直接进行复制、查找、编辑、设置格式、制作图表和打印。排序与筛选Part Two 基本操作排序与筛选3.高级筛选 有时候，我们会需要多个条件进行筛选。除了通过普通筛选项，分部进行筛选外，我们可以直接通过高级筛选进行，一步直接出来结果。u将我们所需要筛选的条件先设置一个条件区域，第一行输入排序的字段名称，在第二行中输入条件。详细说明：列表区域中选择我们所有要筛选的全部区域条件区域中选择我们所设定的条件区域u选择数据中的高级筛选，选定所有筛选的区域和我们的条件区域即可。Part Two 基本操

9、作1.分数的输入： 如果直接输入“1/5”,系统会自动默认为“1月5日”。快捷输入办法是：先输入“0”，然后输入空 格，再输入分数“1/5”。2.文本序列的输入： 如果输入“002”，系统会自动默认显示为“1”。快捷输入办法是：先输入英文单引号”，然后再输入“002”。3.日期和时间的输入： 当前系统日期输入：Ctrl+: (冒号) 当前系统时间输入：Ctrl+; (分号)4.快速输入同样数据： 先选择好区域，再输入数据，最后按Ctrl+Enter5.快速格式填充： 如果需要复制一个单元格的格式，可以选择该单元格，然后点击格式刷按钮，再点击需要设置的单元格即可。如果需要多次复制，可以在选择单元

10、格后，双击格式刷按钮，即可多次复制。6.行列数据转置： 选择需要转换的行或列，复制后在目标区域选择性粘帖，点击转置即可。输入技巧Part 3简单函数Part Three 简单函数Excel中的公式由主要由等号(=)、操作符、运算符组成。公式以等号 (=) 开始，用于表明之后的字符为公式。紧随等号之后的是需要进行计算的元素（操作数），各操作数之间以算术运算符分隔。例如，公式=(A2+67)/SUM(B2:F2)说明如下:该公式的含义为:将A2单元格中的数值加上67;:计算B2单元格到F2单元格的和,即B2+C2+D2+E2+F2;:将的结果除以的结果。Excel 包含四种类型的运算符：算术运算符

11、、比较运算符、文本运算符和引用运算符。算术运算符 完成基本的数学运算，包括以下算术运算符：公式与函数Part Three 简单函数比较运算符 可以使用下列操作符比较两个值。当用操作符比较两个值时，结果是一个逻辑值，为 TRUE或 FALSE,其中TRUE表示真,FALSE表示假。文本运算符 使用和号(&)连接一个或更多字符串以产生更大的文本。引用运算符 用于标名工作表中的单元格或单元格区域。公式与函数Part Three 简单函数日期与时间函数 TODAY()返回日期格式的当前日期NOW() 返回日期时间格式的当前日期和时间以上两个函数均不需要填写参数。WEEKDAY函数是返回某日期的

12、星期数，在默认情况下，它的值为1（星期天）到7（星期六）之间的一个整数。其语法为：WEEKDAY(serial_number，return_type)userial_number 是要返回日期数的日期。ureturn_type 为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期天。 DAY/MONTH/YEAR函数 返回日期/月份/年度的函数，方便提取参数，配合“&”+ 日/ 月/年/，可快速填充固定格式。DATEDIF函数是Excel隐藏函数，返回两个日期之间的年月日间隔数，用来计算两日期间的差

13、值。其语法为：DATEDIF(start_date,end_date,unit)ustart_date 为一个日期，它代表时间段内的第一个日期或起始日期。(起始日期必须在1900年之后)uend_date 为一个日期，它代表时间段内的最后一个日期或结束日期。uunit 为所需信息的返回类型。注：结束日期必须大于起始日期。常用函数Part Three 简单函数统计函数SUM函数指的是返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。其语法为： SUM(number1,number2, .)l逻辑值及数字的文本表达式将被计算。l如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白

14、单元格、逻辑值、文本将被忽略。l如果参数中有错误值或为不能转换成数字的文本，将会导致错误。SUM(3, 2) 等于5。SUM(3, 2, TRUE) 等于6，因为文本值被转换成数字，而逻辑值 TRUE 被转换成数字1。SUM（A1:B5）将会计算该区域内的所有值。也可用鼠标选中该区域，状态栏也会显示该区域的总计数。AVERAGE函数是计算平均值的函数，返回参数平均值（算术平均）,参数可以是数字，或者是涉及数字的名称、数组或引用，如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。其语法为： AVERAGE(number1,number2,)常用函

15、数Part Three 简单函数SUMIF函数根据指定条件对若干单元格、区域或引用值求和。其语法为： SUMIF(range，criteria，sum_range)urange为条件区域，用于条件判断的单元格区域。ucriteria是求和条件，由数字、逻辑表达式等组成的判定条件。usum_range 为实际求和区域，需要求和的单元格、区域或引用。当省略sum_range时，则条件区域就是实际求和区域。SUMIFS函数是SUMIF函数的升级版，可对多条件单元格、区域或引用值求和。其语法为： SUMIFS(sum_range, criteria_range1, criteria1, criteri

16、a_range2, criteria2, .)usum_range 是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值。ucriteria_range1为计算关联条件的区域1ucriteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对区域1参数中的哪些单元格求和。u后面就是区域2，条件2，总是成对出现，最多允许127个区域、条件对，即参数总数不超255个。常用函数Part Three 简单函数COUNTIF函数是对指定区域中符合指定条件的单元格计数的一个函数。其语法为： COUNTIF（range，criteria）urange

17、 要计算其中非空单元格数目的区域。ucriteria 以数字、表达式或文本形式定义的条件。用法：求各种类型单元格的个数，求大于或小于某个值的单元格个数，等于或包含某N个特定字符的单元格个数，两个条件求个数。COUNTIFS函数用来计算多个区域中满足给定条件的单元格的个数，可以同时设定多个条件。该函数为 COUNTIF函数的扩展，Excel2007 中新增，前期版本不支持。其语法为： COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,)ucriteria_range1：为第一个需要计算其中满足某个条件的单元格数目的单元格区域

18、（简称条件区域1）ucriteria1为第一个区域中将被计算在内的条件（简称条件1），其形式可以为数字、表达式或文本。u同理，criteria_range2为条件区域2，criteria2为条件2，依次类推。常用函数Part Three 简单函数常用函数ROUND函数是按指定的位数对数值进行四舍五入。其语法为：ROUND(number, num_digits)unumber 为要四舍五入的数字，或目标单元格unum_digits 为位数，按此位数对 number 参数进行四舍五入。VLOOKUP函数VLOOKUP函数是一个纵向查找函数，可以用来核对数据，多个表格之间快速导入数据等函数功能。功能

19、是按列查找，最终返回该列所需查询列序所对应的值；与之对应的HLOOKUP是按行查找的。其语法为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value为需要在数据表第一列中进行查找的数值。table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。col_index_num为table_array 中查找数据的数据列序号。range_lookup为一逻辑值，指明函数查找时的匹配方式。如果为FALSE或0 ，则返回精确匹配。若为TRUE或1，将查找近似匹配值，如果找不到精确匹配值，则返回

20、小于查找数值的最大数值。如果省略，则默认为模糊匹配。查询和引用函数Part Three 简单函数文本函数RIGHT /LEFT/MID函数这三个均是用来从字符串取指定个数字符的函数。其语法为：Right ( text, num_chars)LEFT( text, num_chars)MID(text, start_num, num_chars)utext为需要提取字符的字符串。unum_chars为函数提取的字符数ustart_num为函数提取字符的起始位置。说明：RIGHT截取目标单元格右起几个字符LEFT截取目标单元格左起几个字符MID截取目标单元格从指定位置开始特定数目的字符LEN函数是

21、一种函数，功能是返回文本串的字符数。其语法为： LEN（text）对需要提取字符长度的字符串进行计数。CONCATENATE函数可将文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合。其语法为： CONCATENATE(text1, text2, .)utext1必需要连接的第一个文本项。utext2, .可选。其他文本项，最多为 255 项。项与项之间必须用逗号隔开。注释:也可以使用连接符号 (&) 计算运算代替该函数来连接文本项常用函数Part Three 简单函数逻辑函数 IF函数可根据指定的条件来判断其“真（TRUE）、“假”（FALSE），根据逻

22、辑计算的真假值，从而返回相应的内容，可对数值和公式进行条件检测。其语法为：IF(logical_test,value_if_true,value_if_false)uLogical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。uValue_if_true logical_test 为 TRUE 时返回的值。uValue_if_false logical_test 为 FALSE 时返回的值。IF函数可多层嵌套使用，但最多只能7层。IFERROR函数为检查是否存在错误的函数，如果公式的计算结果为错误，则返回指定的值；否则将返回公式的结果。一般与其他函数配合使用。其语法为

23、：IFERROR(value, value_if_error)value_if_error 必需。公式的计算结果为错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。常用函数Part Three 简单函数返回错误值产生原因#！公式计算的结果太长，单元格列宽不够；或者单元格中存在无效的日期和时间#DIV/0!公式或函数中的除数为零，或为空白单元格#N/A公式中无可用的数值或缺少函数参数#NAME删除了公式中使用的名称，或使用了不存在的名称，以及包含不可识别的文本#NULL!使用了不正确的区域运算，或者引用单元格

24、之间的交集为空格#NUM!在需要数字参数的函数中使用了无效的数值，或者公式计算结果的数字太大或太小，Excel无法表示#REF!单元格引用无效，删除或覆盖了由其他公式引用的单元格，或将移动单元格粘帖到其他公式引用的单元格中#VALUE!使用的参数或者运算对象的类型不正确，例如当公式中需要输入数字或逻辑值时输入了文本常见报错数据透视表Part 4Part Four 数据透视表数据透视表（Pivot Table）是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。操作步骤u选中原始数据区域，点击“插入”数据透视表（注：选中区域不可出现无列名情况，除非该列不参与透视）。u选择在新工作表还是在当前工作表创建，如果数据透视表较大，建议在新工作表中生成透视表。u选择完毕