Excel数据分析与处理

Excel知识点汇总快捷键：Ctrl+A全选Ctrl+X剪切Ctrl+V粘贴Ctrl+D自动填充Ctrl+B加粗Ctrl+U下划线Ctrl+I斜体Ctrl+K超链接Ctrl+F查找Ctrl+H替换Ctrl+L创建表Ctrl+N新建工作簿Ctrl+O打开文件Ctrl+P打印Ctrl+S保存Ctrl+W关闭Ctrl+Y撤销后重做Ctrl+Z撤销Ctrl+C复制对一个Excel表中各个不同的工作表名（Excel表的下方）进行修改时，可双击进行修改，或点鼠标右键再点重命名。输入公式和特殊符号时，点插入，右上角有“公式”、“符号”的字样，点公式、符号再进行。在文本表中，向右延伸是自动延伸的，无需设置；换行：1.指定地方换行（强制换行）：鼠标光标移动到需要换行的地方，按ALT+回车（enter键）；2.自动换行：进入设置单元格格式（点鼠标右键可看见设置单元格格式的字符；在“开始”栏中的单元格中的“格式”中最后一个也是设置单元格格式）——“对齐”——选中自动换行需显示为文本（即输入什么显示什么）：在前面加上’号负数输入法：见上表日期与时间的输入：见上表，注意设置日期或时间显示格式（对输入内容显示格式的设置）的步骤：进入设置单元格格式，点“数字”栏（看具体情况），——时间、日期（看具体情况）——选择显示的格式分数的输入：见上表，注需显示为分数时和“对输入内容格式的设置”方法一致；需显示为文本时，在前加’号；需显示为真分数时：法一：0加上空格键再加上假分数形式；法二;输入真数部分再空格键再输入分数；需要显示为假分数时，进入设置单元格格式中的自定义——将类型改为???/???选择区域的输入：选择连续区域后，当前活动单元格输入完成后，按TAB光标移到下一列，按回车键光标移到下一行，到边界时自动折行（或折列）相邻连续数据或文本的输入:对有规律的数字或文本加数字的输入：输入前两个单元格产生规律，再选中这两个单元格下拉；对文本加数字或相同文本、相同数据的输入：只需在一个单元格中输入再下拉完成不相邻连续的输入（即在不相邻的单元格中需输入相同的内容）：按ctrl键，鼠标点需输入相同内容的单元格再输入，注输入内容后按空格键，按ctrl,再按回车（enter）；绑定工作表（即改变其中一个表，所绑定的表都改变）：按ctrl键，用鼠标点需要绑定的工作表。带零和小数位较多数据的输入（设置后可以不用输入小数点或0）：选中需输入的区域，点“文件”中的“选项”——高级——勾中自动插入小数点，并填小位数（小位数为正时，小数点往右移动，如设置小位数为3时，输入12345时显示的为12.345，如设置的小位数为-3时，显示为12345000）删除所选区域的所有内容：选中区域，右键，点清除内容自定义序列的应用：法一：1、建立自定义序列（点文件——选项——高级——编辑自定义列表，此项在下面一点——在输入序列处输入，每一个序列用回车键enter隔开，在左边找到刚输入的内容，再点确定）；2、在单元格输入自定义序列中的第一个数据；3。向下拖动填充柄即可得到所需要的整个自定义序列中的内容。法二：先在单元格输入序列——点开编辑自定义列表——鼠标移动到“导入”的左边——选中刚在单元格中输入的内容——点导入，确定构造等比、等差数列：在第一个单元格中输入——选中所需的区域——点开始栏右边的“填充（一个向下的箭头）”进行设置快速输入复杂的序列（即在单元格中输入简单的、不同的，就表示出复杂的内容）：进入设置单元格格式——自定义——在类型中输入（相同的内容用双引号引起来，注意是在英文状态下输入，不同的有几个数则在后面输几个0）——确定——在单元格中只需输入每个单元格中不同的内容设置数据的有效性(对单元格内输入的内容有要求或对输入内容错误的出错警告)：选中区域——点数据栏的数据验证再进行相应设置设置下拉列表：1.利用数据有效性（点数据栏的数据验证，利用此法时进入后点设置——在允许栏必须选择“序列”，因为进行下拉列表设置必须选序列在来源处，如果表中有内容，可直接选中，如果没有，则直接输入，输入时中间用英文状态下的逗号隔开，如对性别进行下拉列表的设置，则子来源处输入男,女）；2.利用窗体组合框（鼠标移动到需设置下拉列表的单元格处——点开发工具一栏——插入——表单控件中的第二个组合框——鼠标回到单元格显示一个加号，按鼠标左键不放，移动此加号控制大小——点鼠标右键，选设置控件格式进行设置）；3.利用控件工具箱的组合框（鼠标移动到需设置下拉列表的单元格处——点开发工具一栏——插入——Activex控件的第二个组合框——鼠标回到单元格显示一个加号，按鼠标左键不放，移动此加号控制大小——点鼠标右键，选“属性”——找ListFillRang,输入下拉列表中需要显示内容的“原位置”如n3:n9——点开发工具栏的设计模式，即退出设计模式——可进行下拉选择，设置后如果要对里面的字体更改大小和字体，则先进入设计模式，右键点单元格，进入“属性”，点“font”,对应一格后面有三个小点，点此进入设置字体及大小）设置级联下拉列表（即在一级菜单中输入后，二级菜单中出现的选项与一级菜单相对应）:将一级菜单和二级菜单的内容先在旁边做出来，做辅助，再在正式的地方选中一级菜单区域，利用数据有效性设置下拉列表（点数据栏的数据验证，利用此法时进入后点设置——在允许栏必须选择“序列”，在来源处，选中刚刚作为辅助的一级菜单区域——确定）——在一级菜单区域用下拉列表先选一个——全选中辅助区域——点公式栏中“根据所选内容创建”，再据情况选择，选择首行还是最左列是由辅助区域内一级菜单的摆向决定的，如果辅助区域内一级菜单是横向摆放，则选首行——选中正式的二级菜单区域利用数据有效性设置下拉列表（点数据栏的数据验证，利用此法时进入后点设置——在允许栏必须选择“序列”，在来源处，输入=indirect(B2)，此处B2为第一个二级菜单单元格所对应的一级菜单中第一个单元格的位置选择性粘贴的设置：选中，复制内容——点左上角“粘贴”——选择性粘贴数据的编辑、运算操作：选中，复制要与原数据进行运算的数——选中需要进行运算的区域——进入选择性粘贴——粘贴处选“数值”，运算处选择需要进行的运算，确定对单元格内的文本进行分散对齐设置：选中需设置的单元格——进入设置单元格格式——选中对齐——水平对齐中选择分散对齐单元格区域格式的套用：选择单元格区域——开始栏点“套用表格格式”单元格内容的替换:点开始栏的“查找和选择”——点替换，输入内容即可单元格格式的替换：点开始栏的“查找和选择”——点替换——点“选项”——对格式进行替换单元格格式的复制：法一：复制单元格格式的单元格——鼠标移动到需要单元格格式的单元格上，进行选择性粘贴（1.鼠标右键——选择性粘贴——选中格式；2.鼠标移动到单元格格式的单元格——点左上角的格式刷——移动到需要单元格格式的单元格上）数值型、日期型、时间性数据的不同内置格式（即对单元格中的各种型数据设置显示出来的格式）：进入设置单元格格式，注意显示为中文大、小写数字在设置单元格格式中的“特殊”中；显示为完整时间即有时、分、秒自定义数字格式的创建与删除（利用格式代码）：进入设置单元格格式——“数字”选项——自定义——在“类型”框中输入自定义的数据格式代码，或者修改原有已有的格式代码——确定；删除时，则在类型框中找到需要删除的自定义格式，点删除即可，注意删除了自定义格式，工作簿中所有使用该格式的单元格都将变为默认的常规模式，并且该操作无法进行撤销注意在输入代码时，所有的标点符号一定要在英文状态下输入代码说明0.00，，按百万缩放数值，保留两位小数，输入正数情况0.00，，按百万缩放数值，保留两位小数，输入负数情况0.00，，按百万缩放数值，保留两位小数，输入零的情况0.00，，百万按百万缩放数值，并显示“百万”字样0"."0,万按万缩放数值，保留一位小数，并显示“万”字样0.00，千按千缩放数值，保留两位小数，并显示“千”字样0"."00百按百缩放数值，保留两位小数，并显示“百”字样代码说明[>100]0.00;大于100才显示[>100]0.00;大于100才显示;;只显示文本，不显示数字;;只显示文本，不显示数字0.00;0.00;0;**只显示数字，文本用*表示0.00;0.00;0;**只显示数字，文本用*表示;;;任何类型的数据都不显示;;;任何类型的数据都不显示2.4条件格式的应用设置基于数值的条件格式化：选中需设置条件格式化的区域——点开始栏的“条件格式”——点“突出显示单元格格式”——再进行设置，在“设置为”处有默认的，也可进行自定义（在设置为处选择自定义格式再进行设置）设置基于公式的条件格式化：（如判断数据是否合规，如身份证号的位数）选中需设置条件格式化的区域——点开始栏的“条件格式”——点“最前最后规则”——点其他规则——选中“使用公式确定要设置格式的单元格”——填写“编辑规则说明”（如突出显示周末日期需要输入公式=weekday(b2,2）>5;如验证身份证位数是否合规，显示不合规的，则输入公式=len(d3)<>18其中<>表示不等于，d3表示所选区域第一格的位置)——点格式对满足条件的进行设置格式自动显示数据前n名：选中数据区域——点开始栏的“条件格式”——点“最前最后规则”——点“前十项”——在框中输入数（要显示前几就输几）——右边对满足条件的进行设置格式设置隔行着色：选中需设置条件格式化的区域——点开始栏的“条件格式”——点“最前最后规则”——点其他规则——选中“使用公式确定要设置格式的单元格”——输入公式=mod(row(),2=1第一章随堂测试在EXCEL中,选定一个单元格后按DEL键,将被删除的是单元格中的内容，如想删除格式或内容加格式，点开始栏中的“格式”的右边的橡皮擦的符号，再选择要删除什么如果用预置小数的方法输入数据时,当设定小数位数是“2”时,输入12345表示12345.00（当设定小数位数是“2”意思是保留两位小数，12345是整数所以设定小数位数是“2”就是12345.00）删除当前工作表中某行的正确操作步骤是,选定该行,执行"编辑→删除"菜单命令excel系统默认一个工作簿包含3张工作表。在输入身份证号时,若输入的内容不是18位,则阻止其输入,这种要求可以通过用数据有效性限制文本的录入长度来实现。如果一个工作表中有数据，现在想在另外的工作表中也快速输入相关的数据，请问可以怎么操作？先建立工作组（按ctrl键）——选中需要输入到其他表中的内容——点开始栏中的“填充”（一个向下的箭头）——选择“至同组工作表”3.1公式及其应用公式的输入：选中需输入公式的单元格——先输入=——再输入相应公式——按回车键（enter）或编辑栏左边一个“√”的符号即可得到结果公式的修改：双击单元格，在单元格内对格式进行修改，或单击单元格，在上方的编辑栏中修改公式的移动和复制：与单元格的移动复制一样（鼠标移动到单元格，右键点复制），但不同的是公式的移动和复制时原有的单元格地址会发生改变，从而对计算结果产生影响，如果要对公式正确的复制，不改变其中的单元格地址的引用，可采用法一：选中有公式的单元格——双击——在公式左侧（即等号左侧）输入’——再复制公式至单元格中——复制后再把两边单元格中的’删除；法二:选中有公式的单元格——在上面的编辑栏处复制公式至单元格中即可比较运算符有=、>、<、>=、<=、<>文本连接运算符是&如输入="中国"&"北京"显示中国北京SUM函数是一个数学和三角函数，可将值相加。你可以将单个值、单元格引用或是区域相加，或者将三者的组合相加。LINKExcel.Sheet.12C:\\Users\\ASUS\\Desktop\\数据分析与处理\\教学案例的操作\\第三章\\3.1公式最新版-付诗意.xlsx运算符的优先顺序!R1C1:R11C2\a\f5\h运算符（优先级从高到低）说明：区域运算符，联合运算符空格交集运算符-负号%百分比^乘幂*和/乘法和除法+和-加法和减法&文本连接符=.>,<,>=,<=,<>比较运算符公式的隐藏：选中需隐藏公式的单元格——右键点“设置单元格格式”——进入“保护”——选中“隐藏”——点“确定”——进入“审阅”中的“保护工作表”——输入密码，确定——返回即可发现公式被隐藏起来了公式的循环：选中单元格——点“文件”中的“选项”——点“公式”——选中“启用迭代计算”——点击确定3.2引用公式的引用：相对引用：单元格地址会随之变动，绝对引用:单元格地址不会变动，绝对引用符号是$，可直接输入$符号，也可按f4键（本电脑按fn+f4键）3.3名称的应用定义区域名称：选中单元格区域——点“公式”中的“定义名称”——输入名称，确定即可根据所选内容定义名称：选中有内容的全部区域——点“公式”中的“根据所选内容”创建——确定——再点“公式”中的“名称管理器”中可看到全部的名称使用名称框（位于编辑栏左侧）定义名称：选中需定义名称的单元格区域——在名称框中输入名称——点击回车键（enter）即可创建名称，这时如果选中单元格输入=sum(刚刚输入的名称)，点击回车键，即可得到刚刚名称一栏数据的和将整行或整列定义名称（常用于数据会时常变化时，在计算过程中期望能随时变化结果是，就先将数据的整行、整列定义名称）：直接在上方选中整行或整列——点“公式”中的“定义名称”——输入名称，确定即可——再点“公式”中的“名称管理器”中可看到刚刚设置的名称，这时如果选中单元格输入=sum(刚刚输入的名称)，点击回车键，即可得到刚刚名称一栏数据的和，如果原有数据有变化，则此单元格中的和也会发生变化为常量数值定义名称：点“公式”中的“定义名称”——在“名称框”中输入要定义的名称“PI”（一圆周率为例），在“引用位置”处输入=3.14——确定，返回后输入=PI*A5(或是其他公式)，按回车键即可得到结果为公式定义名称（公式较复杂时）：点“公式”中的“定义名称”——在“名称框”中输入要定义的名称“今天日期”（举例），在“引用位置”处输入=TODAY()——确定，返回后在单元格中输入=今天日期，按回车键即可得到当天的日期为文本常数定义名称：点“公式”中的“定义名称”——在“名称框”中输入要定义的名称（简单的，如浦发），在“引用位置”处输入=”上海浦东发展银行”(复杂的，注意用引号引起来)——确定，返回后在单元格中输入=浦发，按回车，即可显示上海浦东发展银行使用名称查询数据：先定义好名称——在单元格中输入=定义的名称（如三月销售量）——回车即可查询，得到三月销售量的数据，查询过程中，与数据有效性、设置下拉列表相结合更方便查询（在另外一个动态查询框中，将月份、项目根据数据有效性设置下拉列表，将数据栏设置函数=indirect(月份单元格的位置)空格indirect(项目单元格的位置)，其中月份、项目为举例，运用时按情况）节日倒计时：先在单元格中写好节日名称和节日开始日期——节日开始日期的单元格进行定义名称，定义为“节日日期”——对今天日期进行公式定义名称（见上面）——在任意单元格中输入=”距离”&节日名称位置的绝对引用&”还有”——回车——在右边单元格中输入=节日日期-今天日期——回车对公司上半年情况进行汇总(每个月的销售情况等放在不同的工作表中，且每一张工作表的行数、列数相同)：对每个月的数据区域定义名称（点“公式”中的“定义名称”——在“名称框”中输入要定义的名称“数据区”，在“引用位置”处输入=单击所需要的第一张工作表的标签（下方），即一月份的工作表——按shift键，单击最后一张工作表——在任意一张工作表中选取数据区域——点确定，这样就设置好了）——在汇总表中，直接输入即可（如总计就输入=sum（数据区），平均则输入=aveerage（数据区））3.4数组公式、二维数组数组公式的输入：选中单元格或单元格区域（具体看返回结果是否在一个单元格、或者在单元格区域中）——输入公式——按shift+ctrl+enter键（同时按下）二维数组（有上、左标题）的应用：如在一季度汇总表中（各月数据情况在不同的工作表中）选中全部单元格区域——输入公式=选中一月份数据区域+二月份数据区域+三月份数据区域——同时按shift+ctrl+enter键数组公式的拓展（如各产品涨价后的价格表，分别是两个表：原来价格表、涨价后的价格表）：先在除了两个表区域中的单元格输入价格涨价比率——选中涨价后表的全部区域——输入公式=选取原来价格表的全部区域*选取价格比率的单元格——同时按shift+ctrl+enter键即可得到涨价后的价格数组公式的拓展运用：如计算各分店（1/2/3）一季度的总销售数额，选中需要输入1分店总销售额的单元格，输入公式=sum（(原有的一季度总销售额的全部数据区域*各分店序号的全部区域=现有需要输入1分店总销售额的单元格对应的1的单元格位置)）其中的*号意思是且的意思，筛选出满足1分店的要求，满足两个要求（或用+表示）单个单元格使用数组公式（数组公式的返回结果只在一个单元格）：先进行整行、或者整列定义名称（当数据会变化，希望最后计算结果能随时变化时设置，也可不需要这步骤），如计算累计销售额（单个单元格的数组公式），先将单价、数量的整列进行整列定义名称，在累计销售额的单元格中输入公式=sum(单价*数量)，同时按shift+ctrl+enter键构造数组公式，因为设置了整列定义名称，因此当单价、数量的数据变化或增减时，累计销售额中的数据会进行相应的变化利用数组公式对多数据区域求和：如表中有各原材料各月的进货数量和单价数据（有上、右标题），计算月进货总额，此时选中月进货总额的单元格所有区域，输入公式=材料1各月的数量*其单价+材料二各月的数量*其单价，同时按shift+ctrl+enter键4.1函数基本知识Sum函数：数字之和Max函数：数字中的最大值首字母大写的函数（不清楚是什么函数），则点“公式”栏中的“插入函数”，或者在在“开始”栏右边橡皮形状那一列的第一格符号中可以直接找到“其他函数”——在“或选择类别”处选择“文本”——在“选择函数”处找到“PROPER”函数（下面有相应的文字说明函数的意思）——点击确定——在出现框的”Text”中，鼠标移动到框中，再在表中选择要进行首字母大写的文本——确定成绩判断等级：单元格中输入函数=IF(A2>=60,"合格","不合格")，此处A2指成绩的单元格地址，再在已经判断出等级的第一格单元格右下角往下拖动，即可得到所有的成绩等级函数前面必须要有=4.2常用函数自动求和功能的函数：（自动求和函数在“开始”栏右边橡皮形状那一列的第一格符号中可以直接找到，则不需要手动输入）求和函数：sum()平均值函数：average()最大值函数：max()最小值函数：min()注意在单元格手动输入时等号、括号不能少，输入后按回车（enter）键常用计数函数：计算数字内容的单元格个数的函数（如计算应考人数）：count()计算非空单元格的个数的函数：counta()计算空单元格的个数的函数：countblank()计算满足某个条件的单元格个数的函数：countif()如计算85分以上的人数，则输入公式=COUNTIF(C2:C16,">85")其中的C2:C16为全部分数的单元格位置，文本必须要加上半角状态下的双引号4.3逻辑函数常用逻辑函数：and()——逻辑与or()——逻辑或（或者）not()——逻辑非（否定），not()函数只有一个参数if()，此函数有三个参数（分别是条件判断、结果为真的返回值、结果为假的返回值，三者之间用逗号隔开，如if1>2,”真值”,”假值”其中1>2为条件判断，真值（可按要求输入返回值）为真的返回值，假值为假的返回值，即满足或不满足条件的返回值），返回只有两种结果，此函数除了单独使用外，还可进行嵌套使用（最多可嵌套七层），此函数刻直接输入，也可采用函数向导方法（点击编辑栏fx处，找到if函数，点击确定，在出现的框中，第一处输入条件判断，第二处输入当条件判断为真的时候的返回值，第三处输入当条件判断为假的时候的返回值，注意当返回值为文本或表达式时，手动输入必须加上双引号，但用向导法时，不需要加双引号，它可自动加上）输入几个if函数，则在公式后面加上几个半括号4.4数学函数ABS() 返回数字的绝对值MOD() 返回两数相除的余数，其结果符号与除数相同SQRT() 返回某一正数算术平方根SIGN() 返回数字符号。PRODUCT() 将参数数字相乘，返回乘积值FACT() 返回数的阶乘POWER() 返回给定数字的乘幂（与“^”运算符相同）随机取整函数：RAND() 返回一个大于等于0小于1的随机数RANDBETWEEN() 产生位于两个指定数值之间的一个随机数RAND()*(b-a)+a产生a到b之间的随机数取整函数：INT() 将数字向下舍入到最接近的整数，即向下取整ROUND(）按指定位数四舍五入某个数字ROUNDUP()向上取位ROUNDDOWN()向下取位CEILING（）将参数向上舍入为最接近的指定基数的倍数单元格中用公式算出的结果为0，不显示时，可通过点“文件”——“选项”——“高级”——找到“此工作表的显示选项”——勾中“在具有零值的单元格中显示零”4.5日期和时间函数提取系统日期、时间函数（无参数）：TODAY()返回当前日期NOW()返回当前日期和时间提取日期中的年、月、日函数：YEAR()返回指定日期对应的年份MONTH()返回指定日期中的月份DAY()返回指定日期对应的日输入公式=YEAR(2008/5/15)返回2008；输入公式=MONTH(2008/5/15)返回5；输入公式=DAY(2008/5/15)返回15根据参数，返回对应日期、时间函数：Time(hour,minite,second)返回参数对应时间DATE(year,month,day)返回参数对应日期（三个参数间用逗号隔开）其中的三个参数中：Year为年份，也可以为年份的序列号，用一到四位数字表示。Month为月份，如果所输入的月份大于12，将从指定年份下一年的一月份开始往上加算。如：DATE(2008,14,2)返回代表2009年2月2日的序列号。Day代表在该月份中的天数，如果day大于该月份的最大天数，则将从指定月份的下一月的第一天开始往上累加。如：输入=DATE(2008,5,50)”将返回2008年6月19日其他函数：WEEKDAY(serial_number，return_type)返回某日期为星期几参数Serial_number的值返回结果数字及其含义说明1或省略返回数字1到7，其中：1表示星期日，2表示星期一，……，7表示星期六；2返回数字1到7，其中：1表示星期一，2表示星期二，……，7表示星期日；3返回数字0到6，其中：0表示星期一，1表示星期二，……，6表示星期日示例：2008年5月15日是星期四，则：“=WEEKDAY("2008/5/15")”返回“5”；“=WEEKDAY("2008/5/15",2)”返回“4”；“=WEEKDAY("2008/5/15",3)”返回“3”参数Return_type用来确定返回值类型的数字NETWORKDAYS(start_date，end_date，holidays)返回两个日期之间完整的工作日数值(不包括周末和专门指定的假期)在计算时间差时，注意excel中以小数来处理时间，每一天为1，每一小时记做二十四分之一，因此计算时间差时应在时间相减后乘24显示对应月份相应的天数，则在单元格中输入公式=IF(F13=2,IF(OR(D13/400=INT(D13/400),AND(D13/4=INT(D13/4),D13/100<>INT(D13/100))),29,28),IF(OR(F13=4,F13=6,F13=9,F13=11),30,31))其中的D13为月份的单元格地址，年份能被400整除（D13/400=INT(D13/400)），或者年份能被4整除，但不能被100整除(AND(D13/4=INT(D13/4),D13/100<>INT(D13/100))，则改月为29天，否则为28天；如果月份不是2月，而是4/6/9/11月，则改月为30天，其他月份为31天4.6文本函数1.大小写字母互换：LOWER(text)将文本中所有大写字母转变为小写字母UPPER(text)将文本转换成大写形式PROPER(text)将文本字符串的首字母及任何非字母之后的首字母转换为大写2.提取、拆分文本：LEFT(text,[num_chars])左切取对应数量的字符（根据指定的字符数n从文本字符串左边取前n个字符）num_chars表示取几个字符，如果不填，则默认取一个字符RIGHT(text,[num_chars])右切取对应字符（根据指定的字符数n从文本字符串右边取后n个字符）MID(text,start_num,num_chars)从指定位置切取特定数字的字符3.替换文本：REPLACE(old_text,start_num,num_chars,new_text)根据所指定的字符数，使用其他文本替换当前字符串中的部分文本SUBSTITUTE(text,old_text,new_text,[instance_num])文本字符串中用新的文本替换旧的文本，其中的instance_num表示要替换第几次出现的文本，如果不填，则默认替换全部的旧文本4.其他：TRIM(text)删除文本前后的空格LEN(text)返回文本字符串中的字符数CONCATENATE(text1,text2,….)将若干字符串合并为一个文本身份证号判断性别：15位的身份证最后一位奇数为男，偶数为女18位的身份证倒数第二位奇数为男，偶数为女Iseven()若为偶数则为true5.2数据图表的创建与编辑对数据处理，excel有两种处理方式：嵌入式图表（将数据图表直接插入数据所在工作表中，主要用于说明数据和工作表的关系）和图表工作表（将数据图表与与之相关的原数据进行分开存储，单独为数据图表建立一张工作表，适用于只需要图表的场合）利用图表向导创建嵌入式图表：选择所需的数据区域——点击“插入”栏（如果要对图表进行相应的设置，则点击图表——点“设计”、“格式”栏；如果要对图表添加数据，则先复制需要添加的数据，点图表，点右键，点粘贴；如果要删除图表中某系列数据，则点击图表相应数据，右键点删除即可完成）利用快捷键建立单独的三维柱状图表：选中原数据——按f11键（本电脑按fn+f11键）或alt+f1键（本电脑按alt+fn+f1键）选取不连续区域制作饼图：在选择数据区域时，按住ctrl键，然后逐个选取5.3复杂数据图表的操作 双轴图表：选中——鼠标右键——设置数据系列格式——次坐标轴合并图表：单击空白单元格——插入二维柱形图——右键——选择数据——点添加——填上系列名称、选择系列值——确定5.4动态图表的制作本节所用函数讲解：CELL(info_type,[reference])返回引用中第一个单元格的格式、位置或内容的信息COLUMN([reference])返回某一引用的列号ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])创建一个以文本方式对工作簿中某单元格的引用 INDIRECT(ref_text,[a1])返回文本字符串所指定的引用 CHOOSE(index_num,value1,[value2],...)根据给定的索引值，从参数串中选出相应值或者操作VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup}) VLOOKUP（你想要查找的内容，要查找的位置，包含要返回的值的区域中的列号，返回近似或精确匹配-表示为1/TRUE或0/假） COLUMN([reference])返回某一引用的列号 动态图表：指数据图表的数据源可以根据需要进行动态变化，从而使数据图表也作相应调整实现动态图表：1.利用有关函数设置动态数据区域制作动态图表：在原数据对应下方设置动态数据区域（先输入原表中相应的文字，再在单元格中输入公式=INDIRECT(ADDRESS(CELL("row"),COLUMN(B3)))其中b3为原数据的单元格地址，，再向右拉动——单击原数据按fn+f9键，下方的动态数据区域也相应变化——选中动态数据区域制作图表，改变数据则图表变化）2.利用choose函数和组合框建立动态图表：在原数据对应下方或右方设置动态数据区域（先输入原表中相应的文字，再在单元格中其上方一个单元格输入公式=CHOOSE($L$2,B2,C2,D2,E2,F2)其中L2为等下要插入组合框的单元格地址，b2,c2,d2,e2,f2为原数据表中需要设置动态的的单元格地址，并将b2,c2,d2,e2,f2中的内容复制到其他位置，单击L2，开发工具，插入组合框，右击组合框，设置控件格式，在控制栏的数据源区域选中刚刚b2,c2,d2,e2,f2中的内容复制到的位置，单元格链接则选L2.这样在组合框中选择相应的，则数据就会变化，选中动态数据区域，插入图表，，将组合框移动到图表中，并组合（按ctrl键）3.利用VLOOKUP和复选框控件建立动态图表：在原数据对应下方或右方设置动态数据区域（动态区域与原表之间隔几行，在东岱区域中先输入原表中相应的文字，再在单元格中输入公式=IF($B$8,VLOOKUP($B$11,$2:$6,ROW()-10,FALSE),0)），并向下拉动，可在b8单元格中输入true/false看看，其中b8为原表与动态表之间，且与动态表中此时输入公式单元格相对应的单元格地址，b11为动态表中此时输入公式单元格该列的最上的文字的单元格地址，2:6为原表行的范围，-10为原表与动态表行的差距，其他列同此法，，单击b8，开发工具，插入表单控件中的第三个复选框控件，右击复选框控件，编辑文字（文字为要变化的相应数据的文字题头），右击设置控件格式，单元格链接中选中B8,,在颜色与线条中选择填充颜色（以便看）复制控件，粘贴（动态表中有几个领导变化的则复制几个，复制后再编辑文字，设置格式）再讲几个控件组合在一起（按ctrl键）右击，排序，设为置于顶层，选中动态区域，插入图表，将组合在一起的复合框控件拖动到图表中，选中控件前的正方形打钩，则显示相应数据6.2数据排序按数据排序：排序时按照某一列数据作为关键字排序时，只需单击该列任一单元格，而不用全选该列数据，否则会造成其他信息不相应变化，造成张冠李戴的情况对汉字排序：1.字母序（按汉字的拼音进行排序，汉字拼音在比较大小时实行对应位置字符相比较的原则，当第一个字符相同时，比较第二个字符，第二个字符相同时，比较第三个字符）2.笔画序（按照笔画的多少进行排序）默认为按照字母顺序，要想按照笔画顺序，可以在对话框中进行设置，仍然只需单击该列任一一个单元格，单击任意一个单元格——开始栏——排序和筛选——自定义排序——在主要关键字（此关键字为按照此来排序）、排序依据、次序、选项（可以设置按什么排序）输入相应内容自定义次序排序：单击表中任一一个单元格——开始栏（或数据栏）——排序和筛选（或数据栏中的排序）——自定义排序——在主要关键字（此关键字为按照此来排序）、排序依据、选项（可以设置按什么排序）输入相应内容——在次序选自定义序列——输入内容（内容之间用enter键隔开）——确定多关键字排序：单击表中任一一个单元格——开始栏（或数据栏）——排序和筛选（或数据栏中的排序）——自定义排序——点击添加条件，并输入相应内容6.3数据排序操作的应用技巧恢复原来的排序：引入辅助列实现可恢复（在原表右一列输入原序号，并在这一列依次填上1、2、3等等）此时单击原表中任一一个单元格进行排序，后面一列也会变化，这时对原序号列进行升序的排序，原来的排序就会恢复Rank函数:rank(number,ref,order)——返回一个值在数组中的排位，三个参数表示的含义：Number:需要找到排位的数值的单元格Ref:包含一组数字的数组或引用（如果需要有多个数据的排位，此需要绝对引用，第一个完成后，直接向下拉动）Order:指明排位的方式，0为降序（0可以省略），1为升序Large函数：large(array,k)——返回一组数据中第k个最大值，如第三名（即第三大，）参数含义:Array:数据或者数据区域K:一个正整数得到工资前三名的总额，输入公式=SUM(LARGE(B2:B19,{1,2,3}))Small函数：small(array,k)——返回一组数据中第k个最小值，如倒数第三名隐藏数据排序：选中需要隐藏所在的行（如果不连续，则按ctrl键）——右击隐藏——对留下的进行排序——排序后，选中区域——右击，取消隐藏对数据表删除空行：按照排序时空行总是排在最后的原理：增加辅助列（序列号，依次填入1,2,3等等）——将鼠标移动到数据表中的任意单元格，进行相应排序（此时空行全部排在最后）——删除末尾空行——将鼠标移动到辅助列的任意单元格，进行升序排列——删除辅助列6.4数据筛选筛选方法：自动筛选、高级筛选，可以将那些符合条件的记录显示在工作表中，而将其他不满足条件的记录隐藏起来，或者将筛选出来的记录送到指定位置存放，而原数据表不动自动筛选：将鼠标移动到需要筛选的数据库中的任意一个单元格——点开始栏中中的排序与筛选中的筛选（此时表中会出现筛选的箭头）——点击相应要筛选的条件区域设置：条件区域分为两部分：字段、字段所对应的取值，筛选条件如果是在同一行，说明是“与”的关系，即必须同时满足，在同一列说明是“或”的关系（以及是或的关系），即满足其中一个即可。条件区域设置规则的图形化解释 高级筛选：设置条件区域（此区域与原区域一定要有空行）——将鼠标移动到需要排序的数据库中的任意单元格中——单击数据栏中排序与筛选中的高级——在出现的高级筛选框中填入相应内容（可将筛选结果复制到其他位置，在方式中选择将筛选结果复制到其他位置，在列表区域选择原数据表的所有内容，条件区域则选择刚开始设置的条件区域范围，在复制到框中选择需要复制到的单元格地址，选择一个单元格即可，此单元格作为存放筛选结果的左上角的单元格）将公示结果作为高级筛选的条件区域：在高级筛选的条件区域中，可以将公式作为条件来使用：设置好以公式为条件的条件区域——选中原数据（全选）——单击数据栏中排序与筛选中的高级（在方式中选择将筛选结果复制到其他位置，在列表区域选择原数据表的所有内容，条件区域则选择刚开始设置的条件区域范围，在复制到框中选择需要复制到的单元格地址，选择一个单元格即可，此单元格作为存放筛选结果的左上角的单元格）条件区域使用通配符：在条件设置时，可能会用到通配符：？代表一个任意符号。*代表任意多个符号，通配符用于对条件模糊时，当长度确定是用？，长度不确定时用*，步骤：通过通配符设置条件区域——单击数据栏中排序与筛选中的高级（在方式中选择将筛选结果复制到其他位置，在列表区域选择原数据表的所有内容，条件区域则选择刚开始设置的条件区域范围，在复制到框中选择需要复制到的单元格地址，选择一个单元格即可，此单元格作为存放筛选结果的左上角的单元格）7.1函数汇总利用SUMIF函数实现单条件汇总SUMIF函数的功能是根据指定条件对若干单元格求和，其语法格式如下：SUMIF（条件判断区域，条件，求和区域），其中“条件”可以是数字、表达式或文本，直接输入必须用英文状态下的引号引起来，选单元格地址则不需要SUM函数和IF函数联合实现多条件汇总：SUMIF函数只能根据一个条件进行求和，如果要实现对两个以上的条件求和，可以联合使用SUM函数和IF函数来实现。IF函数包含两个参数，他们之间用*连接，表示两个条件是“与”的关系（两个条件同时满足才能求和），用+连接表示“或”的关系。（and和or也表示与、或，但是在数组公式中不能用and和或or）注意：SUM函数和IF函数联合对多条件求和时，必须按照数组公式的输入，即输入公式后按ctrl+shift+enter键，否则返回错误值“#VALUE”日期函数DATEVALUE：将文本表示的日期转换成一个序列号数字，方便公式中的逻辑比较，参数用引号引起来利用SUMPRODUCT函数实现多条件汇总：SUMPRODUCT函数的功能是计算几个数组之间对应元素乘积之和，语法格式如下：SUMPRODUCT（数组１，数组２，数组３，……）可以看出，该函数的数组参数个数不定，但是使用时一定要注意各个数组的维数必须相同，否则SUMPRODUCT函数将返回错误值“#VALUE”。利用DSUM函数进行数据库表格多条件汇总：DSUM(database,field,criteria)其中：database为构成数据库的单元格区域；field是database区域中某列数据的列标题，它可以是文本，即两端带引号的标志项（如“数量”、“单价”等），也可以是代表数列中数据列位置的数字：1表示第一列，2表示第二列……；criteria称为条件区域7.2数据库表格的分类汇总分类汇总的建立：在执行分类汇总命令之前，首先应该对数据库进行排序，将数据库中关键字相同的一些记录集中到一起。当对数据库排序之后，就可以对数据库进行数据分类汇总运行“数据”标签下“分类汇总”命令多重分类汇总：即对同一分类进行多重汇总。若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算即可。其中，第二次分类汇总在第一次汇总结果上进行。嵌套分类汇总：就是在一个已经按照某一个关键字建立好分类汇总的汇总表中，再按照另一个关键字进行另一种分类汇总，这里要求的是两次分类汇总的关键字不同。建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余的以此类推。在进行嵌套分类汇总时，有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余的以此类推。说明：嵌套分类汇总与多重分类汇总的相同点在于二者都需要进行多次的分类汇总操作；区别在于后者每次的汇总关键字都相同，而前者每次的分类汇总关键字不同。7.3特定情形下的数据汇总方法数据累加汇总处理：将一列数据从上往下（或者自左向右）进行累加汇总，使用SUM函数，但是在单元格区域的引用中第一个单元格应为绝对引用，而第二个单元格地址应为相对引用。如=SUM($B$2:B2)动态更新区域的数据汇总：可以考虑使用SUMIF函数,SUMIF（条件判断区域，条件，求和区域），其中“条件”可以是数字、表达式或文本，直接输入必须用英文状态下的引号引起来，&为文本连接符，如=SUMIF(A2:A27,"<="&E1,B2:B27)不连续区域的数据汇总：SUMIF（条件判断区域，条件，求和区域）注意：相对、绝对、和混合引用，其中“条件”可以是数字、表达式或文本，直接输入必须用英文状态下的引号引起来，数据区域中前若干个最大（小）数值的汇总：结合sum、large、small函数对含有错误值的单元格区域进行汇总：1.sum(if(iserror(数据区),0,数据区))，需按数组键，表示如果是错误的，这为0（错误那个数据为0，只对正确的数据求和），如果是正确的，则求和（只对正确数据求和）；2.sum(iferror(数据区),0))，需按数组键7.4数据的多表合并有时数据被存放到不同的工作表中，这些工作表可在同一个工作薄中，也可来自不同的工作薄。它们格式基本相同，只是由于所表示的数据因为时间、部门、地点、使用者不同而进行了分类。但到一定时间，还需要对这些数据表进行合并，将合并结果放到某一个主工作薄的主工作表中说明：Excel支持将不多于255个工作表中的信息收集到一个主工作表中。利用SUM函数实现多表数据合并：通过公式的“三维应用”来实现，也就是通过使用SUM等函数对来自不同工作表乃至其他不同工作薄的数据进行跨表格引用。按位置进行合并计算：如果所有需要合并的各个工作表源区域中的数据按同样的顺序和位置排列（例如，数据来自同一模板创建的一系列工作表），则可按位置进行合并计算。点数据栏中的合并计算，再进行相应的数据引用、添加注意：按位置进行合并计算时，各工作表中行标题和列标题的位置和顺序一致按分类进行合并计算：如果需要合并的各个工作表中的数据区域具有相同的行标题或列标题，但它们是以不同的方式组织的（比如位置不同或者顺序不同），则可按分类进行合并计算。点数据栏中的合并计算，再进行相应的数据引用、添加，勾选标签位置的最左列7.5数据的透视分析数据透视分析就是从数据库的特定字段中概况信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计。数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表。数据透视表是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似于前面介绍的动态图表功能。数据透视表的创建：单击数据表中任意一个单元格，点插入——数据透视表，再进行相应设置创建数据透视图：单击数据表中任意一个单元格，点插入——数据透视表，再进行相应设置创建数据透视图：第一步要选择“数据透视图”。另外，如果数据透视表已经制作好，要制作与之对应的数据透视图，则单击“选项”→“工具”→“数据透视图”，选择一种需要的图形模型，即可得到数据透视表相对应的数据透视图。新数据透视表中数据显示形式的修改：在数据透视表中，数据的默认显示形式是以数值形式显示，但这不是固定不变的，可以修改数据透视表中数据的显示形式，如显示为小数、百分数或其他需要的形式。第8章数据的查询与核对简单数据查询：在“查找和选择”中选择“查找”8.2.1使用CHOOSE函数从值的列表中选择一个值CHOOSE（index_num，value1，value2，…）其中：参数index_num

用以指明待选参数序号的参数值,它必须为1到29之间的数字，或者是包含数字1到29的公式或单元格引用。如果index_num为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，以此类推。 如果index_num小于1或大于列表中最后一个值的序号，函数CHOOSE返回错误值#VALUE!。 如果index_num为小数，则在使用前将被截尾取整。8.2.2用VLOOKUP和HLOOKUP函数进行表格查询VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中：lookup_value是需要在数据表第一列中查找的数值。table_array是需要在其中查找数据的数据表。col_index_num是table_array中待返回的匹配值的列序号。range_lookup为一个逻辑值，指明VLOOKUP()函数返回时，是“精确匹配”还是“近似匹配”。如果其取值为“TRUE”或省略，则返回近似匹配值；如果为“FALSE”，将返回精确匹配值；如果找不到，则返回错误值“#N/A”。VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值，并且由此返回表格或数组当前行中指定列处的数值。Iferror函数对整个vlookup函数查询的结果进行判断，如果没有错误，就是返回vlookup查询的结果，如果有错误，则返回相应输入的结果8.2.3用MATCH和INDEX函数构造灵活的查询1．MATCH函数的功能与语法格式MATCH函数的灵活性比LOOKUP（包括VLOOKUP、HLOOKUP）更强，它可以在工作表的一行（或一列）中进行数据查询，并返回数据在行