1、1EXCEL进行数据分析与图表处理2整 体 概 述THE FIRST PART OF THE OVERALL OVERVIEW, P L E A S E S U M M A R I Z E T H E C O N T E N T第一部分3大纲、认识EXCEL、数据输入那些事儿、给数据整整容、数据计算很简单、数据分析人人会、拿图表讲故事4定义：EXCEL是一款制作图表的工具软件。它是微软公司的办公软件Microsoft office的组件之一，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。认识EXCEL5EXCEL分析数据五步骤:认识EXCEL6认
2、识EXCEL标题栏功能区编辑栏工作表编辑区自定义访问工具栏单元格名称框状态栏7 一张源数据表是做数据统计分析和制作图表的基础； 数据表“列”为字段，“行”为记录，一条记录有多个属性用多个列值来体现； 例：一批图书信息购成一张表，每一种图书属性包括（书名、作者、 出版社、出版时间、ISBN等信息），每一种图书信息占一行。数据输入那些事儿书名作者出版社出版时间ISBN公共政策与地区差距刘尚希, 孟春等著中国财政经济出版社20137-5005-9398-8压力应对与大学生心理健康李虹著北京师范大学出版社20147-303-07163-6企业管理学主编胥悦红经济管理出版社2012978-7-5096-
3、0212-6国际贸易实务百问百答马荣编著企业管理出版社20127-80147-708-1问题首富黄光裕张小平著浙江人民出版社2014978-7-213-04382-6欧莱雅美容王国的财富传奇李野新, 周俊宏著浙江人民出版社2014978-7-213-04251-58一维表与二维表 一维表的列标签是字段。 要从上述表中找销售量最高是哪一年哪个地区,对一维表一个排序简单搞定,对二维表就相对复杂，为了数据分析的方便, 尽量使用一维表；表1：二维表表2：一维表9数据分析前请将合并表拆分此表按地区进行数据筛选时 (比如:北京)，结果只有一条数据,本来有3条销售信息; 方法:选中合并格,点击“合并后居中”
4、,将单元格进行拆分。对于合并单元格,系统默认把它置于被合并的最左上单元格中,其它单元格为空;10数据表站着比躺着好方法：让躺着的表站起来，通过复制-选择性粘贴-转置完成;11数据输入技巧输入以0开头的数字，先输入英文状态的单引号即可，也可在输入前将单元格格式设置为文本再输入; 输入分数,在开头输入0和空格即可; 输入身份证号码,先输入英文状态单引号,系统会将其识别为文本而非数值; 设置单元格格式为日期即可；12 选中当前列，设置单元格格式,自定义格式中输入HDFT00-000,在输入时直接输入00020，系统自动显示为HDFT00-020,下列各单元格依次输入04150,06018即可;自定义
5、数字格式 在输入系列设备代号或人事编号是可以使用此功能;此处0为数字占位符;13大量小数的输入通过文件-选项-高级-自动插入小数点,设置小数点位数(例如:2位), 只输入数字部分,提高输入效率;整数后需补齐0(0的个数与设置的小位点位数一致);使用：学生成绩、财务金额等；14数据有效性设置当一张表格给多人来填报或需输入数据条数较多时通过数据有限性设置保证数据输入的正确性和规范性;可以设置数据类型（整数、小数、序列、日期等）；可以设置输入提示，也可以设置输入错误时警告语；15填充柄的妙用填充柄可以将事先设置好的系列内容填充到拖拉到的区域,系统提供了等差填充,等比填充,星期填充,月份填充,日期填充
6、等；在星期填充,日期填充中还可选择工作日填充;在带公式的填充中填充柄也发挥极大的作用；通过“文件-选项-高级-常规-自定义序列”可以导入表中某一列固定内容,下次使用时填入第一项,拖拉填充柄实现自动填充序列；16单元格引用单元格引用：相对引用、绝对引用、混合引用使用“$”符号来锁定引用。相对引用：A1、B5绝对引用：$A$5、$C$2 快捷键为F4混合引用：$A2、D$1主要在公式中灵活选用。17 引用格式: 工作薄存放地址工作薄名称工作表名称!单元格地址例：E3=EXCEL演示图表.xlsx加班!C2跨工作薄引用18 目的:使数据便于阅读; 路径:视图-窗口-冻结窗格 三种方式:冻结首行,冻结
7、首列,冻结拆分窗格; 冻结首行:光标条上下拉动时首行不动,对于行数多的表,往下翻看数据时可以清楚看到表头; 冻结首列:光标条左右拉动时首列不动,对于列数多的表,往下翻看数据时可以清楚看到第一列; 冻结拆分窗格:将光标放在C4单元格,执行此命令,表示C之前的列(即AB列冻结)4之前的行(123行冻结);冻结窗格19 目的:使数据便于阅读; 方式:选中要隐藏的行(或列),点击鼠标右键,执行隐藏命令; 取消:选中被隐藏的行(或列)的上下(或前后)两行,点击鼠标右键,执行取消隐藏命令即可; 隐藏行或列20 目的:快速阅读数据的最外边(Ctrl+右箭头),最下边(Ctrl+下箭头); 在大量数据面前,用
8、鼠标滚动下翻实在OUT了;Ctrl+箭头键数据一步到底21 选中数据区域,通过数据-分级显示-分类汇总,设置汇总项(销售额),分类字段(省份),汇总方式(求和),系统自动生成按省份的分类汇总表; 通过左侧+和-按钮可以显示和隐藏被汇总的信息; 前提:进行分类汇总前需对分类字段进行排序; 缺点:一次只能对一个字段进行汇总,只能构成一维汇总表;要想对两个或两个以上字段进行汇总,制作二维汇总表.分类汇总表22 例如:要进行每个省份每个月销售汇总;或者每个产品在每个省份的销售汇总,这类问题比较常见,需使用数据透视表功能; 选中数据区域,插入-数据透视表,设置存放位置,选择字段,在行标签,列标签,数值区
9、域拖动字段;数据透视表23查找与去重24查找与替换25排序与筛选26函数函数分类函数名功能举例文本MID从文本字符串中指定的起始位置起,返回指定长度的字符;420001198005015721=MID(D3,7,4)1980LEFT从文本字符串左边起,返回指定长度的字符;A11=LEFT(D4,1)ALEN返回文本字符串的字符数F713.50=LEN(D4)7逻辑IF判断是否满足某个条件,满足返会一个值,不满足返回另一个值;89=IF(D580,优, )优AND所有条件参数均为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87=AND(A180,B180) FALSEOR只要其中
10、一个条件为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87=OR(A180,B180）TRUE统计COUNTIF计算某个区域中满足给定条件的单元格个数;参考函数说明和帮助MAX返回一组数值的最大值MIN返回一组数值的最小值查找与引用LOOKUP从单行或单列或数组中查找一个值HLOOKUP 通过与首行值的对比来查找值VLOOKUP 通过与首列值的对比来查找值财务可以计算贷款月支付额,累计偿还金额,资产折旧值多种财务数据;数学和三角函数工程日期和时间27MID：从文本字符串中指定的起始位置起,返回指定长度的字符;例：A1（420001198005015721）B1=MID（A1，7
11、，4）结果：1980LEFT：从文本字符串左边起,返回指定长度的字符;例：A1（F713.50） B1=LEFT（A1，1） 结果：FRIGHT：从文本字符串右边起,返回指定长度的字符;例：A1（HDFT020-25） B1=RIGHT（A1，2）结果：25LEN：返回文本字符串的字符数；例：A1（F713.50） B1=LEN（A1） 结果：7文本函数28IF：判断是否满足某个条件,满足返会一个值,不满足返回另一个值;例：A1=89，D1=IF（A180，,“优”,“ ”), 结果：优AND：所有条件参数均为真,结果返回TRUE,否则返回FALSE;例：A1=68, B1=87，C1=AND
12、(A180,B180)，结果：FALSE；OR：只要其中一个条件为真,结果返回TRUE,否则返回FALSE;例：A1=68, B1=87，C1=OR(A180,B180），结果：TURE；逻辑函数29统计函数COUNTIF：计算某个区域中满足给定条件的单元格个数;例：=COUNTIF（B：B，B3），计算B列中与B3单元格值相同的单元格个数。COUNTIF（range,criteria)参数：range 要计算其中非空单元格数目的区域参数：criteria 以数字、表达式或文本形式定义的条件MAX：返回一组数值的最大值MIN：返回一组数值的最小值30查找与引用函数LOOKUP：从单行或单列或数
13、组中查找一个值在计算第1个“税率”时，输入函数公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”，回车，便可得到“36.00%”。用F4中的第1个收入数“$123,409”，与左侧表的“收入最低”各档数据(“$B$3:$B$8”)进行对比，虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是会与其中小于它的最大数“$58,501”相匹配。这样，同一行对应的“36.00%”就提取出来了。31EXCEL为用户提供了11类73种图表。最基本类型有六种: 柱形图 折线图 饼图 条形图 面积图 散点图图表32图表元素33不等宽柱形图说明：右键设置数据系列格式
14、（重叠型、无间距）34处理超大值用截断标记技巧：超大值44实用值为5，手工绘制截断标记35处理负数技巧：用堆积柱形图，绘图前将正负数放在不同列36给折线图整容37美化后的折线图38美化的折线图是对数据进行了预处理39迷你图40饼图源数据表注意：计算百分比涉及到相对引用和绝对引用。正确应为D3=C3/$C$14，向下拖拉D4=C4/C14可能错误D3=C3/C14，向下拖拉D4=C4/C15，系统会报错41饼图42源数据与作图数据比较43巧用空行和错行作图44正确表达作图目的目的：比较订单与库存+进货“技巧：使用堆积柱形图,利用空格在堆叠时无显示,达到预期效果.45提问与解答环节Questions and answers46添加标题添加标题添加标题添加标题此处结束语点击此处添加段落文本 . 您的内容打在这里，或通过复制您的文本后在此框中选择粘贴并选择只保留文字47谢谢聆听THANK YOU FOR LISTENING演讲者：XX 时间：202X.XX.XX
