EXCEL数据分析与处理(上)总结

EXCEL数据处理与分析讲师：梁国清EXCEL数据处理与分析EXCEL处理过程数据输入数据整理计算和分析数据信息第一部分工作环境和基本操作第二部分输入和导入数据第三部分数据整理和编辑第四部分数据查询第五部分常规计算和分析EXCEL数据处理与分析提纲1.最佳EXCEL学习方法2.工作环境基本操作3.数据输入与导入：数据有效性…4.数据整理与编辑：设置单元格、排序…5.数据查询：筛选、条件格式…6.常规计算分析：分类汇总、合并计算、数据透视表7.高级分析：模拟运算、单变量求解、规划求解、分析工具EXCEL数据处理与分析EXCEL学习阶段刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式---+数组公式+VBA简单编程EXCEL技能+专业知识+行业经验特点：个人电脑中最广泛的办公软件最好的表格处理软件一向具有良好的向下兼容性学习方法：循序渐进善用资源，学以致用多阅读，多实践学习进程EXCEL数据处理与分析EXCEL功能划分Excel数据操作与处理图表与图形公式与函数数据分析宏与VBAEXCEL数据处理与分析第一章工作环境与基本操作主要介绍Excel2007工作环境和基本操作熟悉界面设置、工作簿和工作表的操作以及窗口显示等方面的内容。•功能区旨在使Excel使用起来更简单，并帮助用户快速找到和使用所需的命令。•功能区由选项卡、组和命令三部分组成。您会发现您熟悉的命令都按照一定的方式组合在一起EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•功能区旨在使Excel使用起来更简单，并帮助用户快速找到和使用所需的命令。•功能区由选项卡、组和命令三部分组成。您会发现您熟悉的命令都按照一定的方式组合在一起EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•命令更多，但仅在需要时出现。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•需要时可提供更多选项EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•功能区上的每个按钮都有快捷方式。•快捷方式需要的键通常更少•例如，在Excel中可以使用键提示居中对齐文本。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•Excel中的新页面布局视图(视图选项卡)1.列标题。2.行标题。3.页边距标尺。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•在Excel2007中，可以打开用Excel早期版本（从Excel95到Excel2003）创建的文件。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•新文件类型：.XLSX•*工作区和工作区文件(.xlw)•工作区文件仅记录了工作簿文件的路径和名称、排列位置等信息，但不是工作薄文件。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•新功能工作表的行数从65,536增加到了1,048,576，列数从256增加到了16,384。•数据更有用由于新文件格式基于XML，因此更容易与其他计算机和程序中的其他数据集进行集成，从而可以对数据进行更多处理。例如，可以在专业化的程序中存储预算数据，选择所需的数据，然后将其导入到Word文档、Excel工作表和Access数据库。EXCEL数据处理与分析第一章工作环境与基本操作•Excel2007工作环境介绍•修改个性设置•修改默认文件位置•最近打开的文档相关设置•添加序列EXCEL数据处理与分析第一章工作环境与基本操作•Excel2003工作环境介绍打开方法：工具菜单\选项EXCEL数据处理与分析第一章工作环境与基本操作•Excel基本操作让工作表永远显示标题行——冻结&拆分窗口冻结窗格利用这个功能用户可以指定某些行列永远留在屏幕，不会因滚动工作表消失拆分窗口用户可以在不同区域查看同一工作表的不同部分。多窗口协作用户可以在不同窗口中查看同一工作簿，互不影响，保持同步更新。并排比较在每一页上都打印行列标题或行列标志页面设置项\打印标题\顶端标题行EXCEL数据处理与分析第一章工作环境与基本操作Excel基本操作撤销和恢复操作（Ctr+Z、Ctr+Y）撤销和恢复注意重复只有一级、撤销可以有16级或更多在多个工作表间切换工作表切换快捷键Ctr+PgUpCtr+PgDn工作表导航栏基本快捷键操作全选当前数据区：Ctr+A;选定数据区域Ctr+Shift+箭头;定位到工作表区域的右下角Ctr+End保存：Ctr+S控制单元格的移动方向选定区域内循环录入数据（先选定范围，再回车）EXCEL数据处理与分析第二章数据输入与导入使用Excel进行数据处理分析的前提本章主要掌握有关数据输入和导入的操作技巧，以及和数据输入和导入相关的列表、定义名称、数据有效性等功能技巧。EXCEL数据处理与分析第二章数据输入与导入自动填充填充种类数字填充文本填充文本+数字填充等差填充等比填充按日期填充特殊内容填充自定义序列填充填充方法使用菜单命令使用鼠标左键配合智能标记配合Ctrl键根据范本填充使用鼠标右键EXCEL数据处理与分析第二章数据输入与导入序列在自动填充与排序功能中起到重要作用自定义序列导入序列进行更多的个性的填充初始选择扩展序列1,2,34,5,6,...9:0010:00,11:00,12:00,...周一周二,周三,周四,...星期一星期二,星期三,星期四,...一月二月,三月,四月,...一月,四月七月,十月,一月,...1999年1月,1999年4月1999年7月,1999年10月,2000年1月,...1月15日,4月15日7月15日,10月15日,...1999,20002001,2002,2003,...1月1日,3月1日5月1日,7月1日,9月1日,...Qtr3（或Q3或Quarter3）Qtr4,Qtr1,Qtr2,...文本1,文本A文本1,文本A,文本3,文本A,...第1阶段第2阶段,第3阶段,...产品1产品2,产品3,...EXCEL数据处理与分析第二章数据输入与导入一些输入技巧•输入分数整数部分+空格+分子部分+”/”+分母部分•输入特殊符号ALT+41420=对号ALT+41409=错号XALT+178=M2平方AlT+179=M3•输入身份证号码（超过15位精度的数字录入）把证件号码、银行帐号、零件编号等作为文本输入，以单引号‘开始的输入会认为是文本数据。•在单元格中换行（Alt+Enter）EXCEL数据处理与分析第二章数据输入与导入一些输入技巧•神奇的选择性粘贴1.全部2.数值：只复制数值3.格式4.有效性验证5.选项：①跳过空单元格②转置：行列互换6.粘贴链接EXCEL数据处理与分析第二章数据输入与导入斜线表头单斜线表头多斜线表头示例：灵活制作斜线表头EXCEL数据处理与分析第二章数据输入与导入数据有效性设置选项卡输入信息出错警告EXCEL数据处理与分析第二章数据输入与导入数据有效性了解有效性允许的条件数值——限定输入数值范围序列——列表限定输入数据日期——限定输入日期范围时间——限定输入时间范围自定义——公式：=LEFT（A1）=“A”（只允许输入字母A开头的文本）数据有效性可以被复制示例：1.限制输入重复数值2.限制重复订餐3.使用单元格下拉菜单规范输入EXCEL数据处理与分析第二章数据输入与导入函数公式基础什么是公式？公式是以等号“=“为引导，通过运算符按照一定顺序组合进行数据处理的等式。例如：【=5+2*3】结果等于2乘3再加5。EXCEL数据处理与分析第二章数据输入与导入函数公式基础公式的组成部分①函数：PI()函数返回值pi：3.142...②引用：A2返回单元格A2中的值。③常量：直接输入公式中的数字或文本值，如2。④运算符：^（脱字号）运算符表示将数字乘方，*（星号）运算符表示相乘。EXCEL数据处理与分析第二章数据输入与导入函数公式基础什么是公式？公式的组成要素：等号(=)、函数、引用、运算符和常量、名称。函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。运算符：一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。常量：不进行计算的值，因此也不会发生变化。例如:数字210以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。EXCEL数据处理与分析第二章数据输入与导入函数公式基础函数是预定义的公式，通过使用一些称为参数的特定数值来特定的顺序或结构执行计算。函数可用于执行简单或复杂的计算。函数的语法：ROUND函数示例说明了函数的语法，它将单元格A10中的数字四舍五入。函数的结构:①函数的结构以等号(=)开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。②函数名称。如果要查看可用函数的列表，可单击一个单元格并按Shift+F3。③参数。参数可以是数字、文本、TRUE或FALSE等逻辑值、数组、错误值或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或其他函数。④参数工具提示。在键入函数时，会出现一个带有语法和参数的工具提示。EXCEL数据处理与分析第二章数据输入与导入函数的结构当函数以公式的形式出现，其结构如图：以等号开始，函数名，左括号、以半角逗号隔开的参数，右括号。①函数名：条件函数IF(logical_test,value_if_true,value_if_false)。②引用:(F2:F5)返回单元区域F列第2行到5行中的值。③常量:(50)直接输入公式中的数字或文本值，如2。④运算符:()逻辑运算符，进行比较大小的运算。⑤参数：AVERAGE(F2:F5)50；SUM(G2:G5)；0。⑥当函数做参数时，称为嵌套函数。EXCEL数据处理与分析第二章数据输入与导入关于函数：①部分函数没有参数。例如：Now(),Today(),Rand()。②Excel的公式是为了有目的的计算结果，只能在单元格或内存中返回值，而不能对单元格进行删除或对其它单元格进行赋值。EXCEL数据处理与分析第二章数据输入与导入运算符类型1、算术运算符2、比较运算符算术运算符含义示例+（加号）加法3+3–（减号）减法3–1负数–1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%^（脱字号）乘方3^2比较运算符含义示例=（等号）等于A1=B1（大于号）大于A1B1（小于号）小于A1B1=（大于等于号）大于或等于A1=B1=（小于等于号）小于或等于A1=B1（不等号）不等于A1B1EXCEL数据处理与分析第二章数据输入与导入运算符类型3、文本连接符可以使用与号(&)联接或连接一个或多个文本字符串，以生成一段文本。4、引用运算符引用运算符含义示例:（冒号）区域运算符，生成对两个引用之间所有单元格的引用（包括这两个引用）B5:B15,（逗号）联合运算符，将多个引用合并为一个引用SUM(B5:B15,D5:D15)（空格）交集运算符，生成对两个引用中共有的单元格的引用B7:D7C6:C8EXCEL数据处理与分析第二章数据输入与导入公式运算的次序在某些情况中，执行计算的次序会影响公式的返回值。计算次序公式按特定次序计算值。Excel中的公式始终以等号(=)开头，这个等号告诉Excel随后的字符组成一个公式。等号后面是要计算的元素（即操作数），各操作数之间由运算符分隔。Excel按照公式中每个运算符的特定次序从左到右计算公式。使用括号若要更改求值的顺序，请将公式中要先计算的部分用括号括起来。优先顺序运算符说明1:（冒号）引用运算符2（单个空格）3,（逗号）4–负数（如–1）5%百分比6^乘方7*和/乘和除8+和–加和减9&连接两个文本字符串（串连）10=比较运算符1112=