利用EXCEL进行数据分析与图表处理课件

EXCEL进行数据分析与图表处理1EXCEL进行数据分析与图表处理1整体概述THEFIRSTPARTOFTHEOVERALLOVERVIEW,PLEASESUMMARIZETHECONTENT第一部分2整体概述第一部分2大纲１、认识EXCEL２、数据输入那些事儿３、给数据整整容４、数据计算很简单５、数据分析人人会６、拿图表讲故事3大纲１、认识EXCEL3定义：EXCEL是一款制作图表的工具软件。它是微软公司的办公软件Microsoftoffice的组件之一，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。认识EXCEL4定义：EXCEL是一款制作图表的工具软件。它是微软公司的办公EXCEL分析数据五步骤:认识EXCEL5认识EXCEL5认识EXCEL标题栏功能区编辑栏工作表编辑区自定义访问工具栏单元格名称框状态栏6认识EXCEL标题栏功能区编辑栏工作表编辑区自定义访问工具栏一张源数据表是做数据统计分析和制作图表的基础；数据表“列”为字段，“行”为记录，一条记录有多个属性用多个列值来体现；

例：一批图书信息购成一张表，每一种图书属性包括（书名、作者、出版社、出版时间、ISBN等信息），每一种图书信息占一行。数据输入那些事儿书名作者出版社出版时间ISBN公共政策与地区差距刘尚希,孟春等著中国财政经济出版社20137-5005-9398-8压力应对与大学生心理健康李虹著北京师范大学出版社20147-303-07163-6企业管理学主编胥悦红经济管理出版社2012978-7-5096-0212-6国际贸易实务百问百答马荣编著企业管理出版社20127-80147-708-1问题首富黄光裕张小平著浙江人民出版社2014978-7-213-04382-6欧莱雅美容王国的财富传奇李野新,周俊宏著浙江人民出版社2014978-7-213-04251-57一张源数据表是做数据统计分析和制作图表的基础；数据输入那些事一维表与二维表一维表的列标签是字段。要从上述表中找销售量最高是哪一年哪个地区,对一维表一个排序简单搞定,对二维表就相对复杂，为了数据分析的方便,尽量使用一维表；表1：二维表表2：一维表8一维表与二维表一维表的列标签是字段。表1：二维表表2：一维数据分析前请将合并表拆分此表按地区进行数据筛选时(比如:北京)，结果只有一条数据,本来有3条销售信息;

方法:选中合并格,点击“合并后居中”,将单元格进行拆分。对于合并单元格,系统默认把它置于被合并的最左上单元格中,其它单元格为空;9数据分析前请将合并表拆分此表按地区进行数据筛选时(比如:北数据表站着比躺着好方法：让躺着的表站起来，通过"复制--选择性粘贴--转置"完成;10数据表站着比躺着好方法：让躺着的表站起来，通过"复制--选择数据输入技巧输入以0开头的数字，先输入英文状态的单引号即可，也可在输入前将单元格格式设置为"文本"再输入;

输入分数,在开头输入0和空格即可;

输入身份证号码,先输入英文状态单引号,系统会将其识别为文本而非数值;

设置单元格格式为日期即可；11数据输入技巧输入以0开头的数字，先输入英文状态的单引号即可，选中当前列，设置单元格格式,自定义格式中输入"HDFT"00"-"000,在输入时直接输入00020，系统自动显示为HDFT00-020,下列各单元格依次输入04150,06018…即可;自定义数字格式在输入系列设备代号或人事编号是可以使用此功能;此处0为数字占位符;12选中当前列，设置单元格格式,自定义格式中输入"HDFT"00大量小数的输入通过"文件-选项-高级-自动插入小数点",设置小数点位数(例如:2位),只输入数字部分,提高输入效率;整数后需补齐0(0的个数与设置的小位点位数一致);使用：学生成绩、财务金额等；13大量小数的输入通过"文件-选项-高级-自动插入小数点",设置数据有效性设置当一张表格给多人来填报或需输入数据条数较多时通过数据有限性设置保证数据输入的正确性和规范性;可以设置数据类型（整数、小数、序列、日期等）；可以设置输入提示，也可以设置输入错误时警告语；14数据有效性设置当一张表格给多人来填报或需输入数据条数较多时通填充柄的妙用填充柄可以将事先设置好的系列内容填充到拖拉到的区域,系统提供了等差填充,等比填充,星期填充,月份填充,日期填充等；在星期填充,日期填充中还可选择工作日填充;在带公式的填充中填充柄也发挥极大的作用；通过“文件--选项--高级--常规--自定义序列”可以导入表中某一列固定内容,下次使用时填入第一项,拖拉填充柄实现自动填充序列；15填充柄的妙用填充柄可以将事先设置好的系列内容填充到拖拉到的区单元格引用单元格引用：相对引用、绝对引用、混合引用使用“$”符号来锁定引用。相对引用：A1、B5绝对引用：$A$5、$C$2快捷键为F4混合引用：$A2、D$1主要在公式中灵活选用。16单元格引用单元格引用：相对引用、绝对引用、混合引用16引用格式:工作薄存放地址[工作薄名称]工作表名称!单元格地址例：E3=[EXCEL演示图表.xlsx]加班!C2跨工作薄引用17引用格式:工作薄存放地址[工作薄名称]工作表名称!单元格目的:使数据便于阅读;路径:视图--窗口--冻结窗格三种方式:冻结首行,冻结首列,冻结拆分窗格;冻结首行:光标条上下拉动时首行不动,对于行数多的表,往下翻看数据时可以清楚看到表头;冻结首列:光标条左右拉动时首列不动,对于列数多的表,往下翻看数据时可以清楚看到第一列;冻结拆分窗格:将光标放在C4单元格,执行此命令,表示C之前的列(即AB列冻结)4之前的行(123行冻结);冻结窗格18目的:使数据便于阅读;冻结窗格18目的:使数据便于阅读;方式:选中要隐藏的行(或列),点击鼠标右键,执行"隐藏"命令;取消:选中被隐藏的行(或列)的上下(或前后)两行,点击鼠标右键,执行"取消隐藏"命令即可;

隐藏行或列19目的:使数据便于阅读;隐藏行或列19目的:快速阅读数据的最外边(Ctrl+右箭头),最下边(Ctrl+下箭头);在大量数据面前,用鼠标滚动下翻实在OUT了;"Ctrl+箭头键"数据一步到底20目的:快速阅读数据的最外边(Ctrl+右箭头),最下边(Ct选中数据区域,通过"数据--分级显示--分类汇总",设置汇总项(销售额),分类字段(省份),汇总方式(求和),系统自动生成按省份的分类汇总表;通过左侧+和-按钮可以显示和隐藏被汇总的信息;前提:进行分类汇总前需对分类字段进行排序;缺点:一次只能对一个字段进行汇总,只能构成一维汇总表;要想对两个或两个以上字段进行汇总,制作二维汇总表.分类汇总表21选中数据区域,通过"数据--分级显示--分类汇总",设置汇总例如:要进行每个省份每个月销售汇总;或者每个产品在每个省份的销售汇总,这类问题比较常见,需使用"数据透视表"功能;选中数据区域,"插入--数据透视表",设置存放位置,选择字段,在行标签,列标签,数值区域拖动字段;数据透视表22例如:要进行每个省份每个月销售汇总;或者每个产品在每个省份的查找与去重23查找与去重23查找与替换24查找与替换24排序与筛选25排序与筛选25函数函数分类函数名功能举例文本MID从文本字符串中指定的起始位置起,返回指定长度的字符=MID(D3,7,4)"1980LEFT从文本字符串左边起,返回指定长度的字符;A11"=LEFT(D4,1)"ALEN返回文本字符串的字符数F713.50"=LEN(D4)"7逻辑IF判断是否满足某个条件,满足返会一个值,不满足返回另一个值;89"=IF(D5>80,"优","")"优AND所有条件参数均为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87"=AND(A1>80,B1>80)"FALSEOR只要其中一个条件为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87"=OR(A1>80,B1>80）"TRUE统计COUNTIF计算某个区域中满足给定条件的单元格个数;参考函数说明和帮助

MAX返回一组数值的最大值

MIN返回一组数值的最小值

查找与引用LOOKUP从单行或单列或数组中查找一个值

HLOOKUP通过与首行值的对比来查找值

VLOOKUP通过与首列值的对比来查找值

财务

可以计算贷款月支付额,累计偿还金额,资产折旧值多种财务数据;

数学和三角函数

工程

日期和时间

…

26函数函数分类函数名功能举例文本MID从文本字符串中指定的起始MID：从文本字符串中指定的起始位置起,返回指定长度的字符;例：A1B1=MID（A1，7，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文本函数27MID：从文本字符串中指定的起始位置起,返回指定长度的字符;IF：判断是否满足某个条件,满足返会一个值,不满足返回另一个值;例：A1=89，D1=IF（A1>80，,“优”,“”),结果：优AND：所有条件参数均为真,结果返回TRUE,否则返回FALSE;例：A1=68,B1=87，C1=AND(A1>80,B1>80)，结果：FALSE；OR：只要其中一个条件为真,结果返回TRUE,否则返回FALSE;例：A1=68,B1=87，C1=OR(A1>80,B1>80），结果：TURE；逻辑函数28IF：判断是否满足某个条件,满足返会一个值,不满足返回另一个统计函数COUNTIF：计算某个区域中满足给定条件的单元格个数;例：=COUNTIF（B：B，B3），计算B列中与B3单元格值相同的单元格个数。COUNTIF（range,criteria)参数：range要计算其中非空单元格数目的区域参数：criteria以数字、表达式或文本形式定义的条件MAX：返回一组数值的最大值MIN：返回一组数值的最小值29统计函数COUNTIF：计算某个区域中满足给定条件的单元格个查找与引用函数LOOKUP：从单行或单列或数组中查找一个值在计算第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%”就提取出来了。30查找与引用函数LOOKUP：从单行或单列或数组中查找一个值在EXCEL为用户提供了11类73种图表。最基本类型有六种:柱形图折线图饼图条形图面积图散点图图表31EXCEL为用户提供了11类73种图表。图表31图表元素图表标题图例纵坐标轴（Y轴)网格线横坐标轴（X轴）图表区绘图区数据系列插入到图表中的其他对象32图表元素图表标题图例纵坐标轴（Y轴)网格线横坐标轴（X轴）图不等宽柱形图说明：右键设置数据系列格式（重叠型、无间距）33不等宽柱形图说明：右键设置数据系列格式（重叠型、无间距）33处理超大值用截断标记技巧：超大值44实用值为5，手工绘制截断标记34处理超大值用截断标记技巧：超大值44实用值为5，手工绘制截断处理负数技巧：用堆积柱形图，绘图前将正负数放在不同列35处理负数技巧：用堆积柱形图，绘图前将正负数放在不同列35给折线图整容36给折线图整容36美化后的折线图37美化后的折线图37美化的折线图是对数据进行了预处理38美化的折线图是对数据进行了预处理38迷你图39迷你图39饼图源数据表注意：计算百分比涉及到相对引用和绝对引用。正确应为D3=C3/$C$14，向下拖拉D4=C4/C14可能错误D3=C3/C14，向下拖拉D4=C4/C15，系统会报错40饼图源数据表注意：计算百分比涉及到相对引用和绝对引用。40饼图41饼图41源数据与作图数据比较42源数据与作图数据比较42巧用空行和错行作图43巧用空行和错行作图43正确表达作图目的目的：比较"订单"与"库存+进货“技巧：使用堆积柱形图,利用"空格"在堆叠时无显示,达到预期效果.44正确表达作图目的目的：比较"订单"与"库存+进货“44提问与解答环节Questionsandanswers45提问与解答环节45添加标题添加标题添加标题添加标题此处结束语点击此处添加段落文本.您的内容打在这里，或通过复制您的文本后在此框中选择粘贴并选择只保留文字46添加标题添加添加添加标题此处结束语点击此处添加段落文本.谢谢聆听THANKYOUFORLISTENING演讲者：XX时间：202X.XX.XX47谢谢聆听47EXCEL进行数据分析与图表处理48EXCEL进行数据分析与图表处理1整体概述THEFIRSTPARTOFTHEOVERALLOVERVIEW,PLEASESUMMARIZETHECONTENT第一部分49整体概述第一部分2大纲１、认识EXCEL２、数据输入那些事儿３、给数据整整容４、数据计算很简单５、数据分析人人会６、拿图表讲故事50大纲１、认识EXCEL3定义：EXCEL是一款制作图表的工具软件。它是微软公司的办公软件Microsoftoffice的组件之一，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。认识EXCEL51定义：EXCEL是一款制作图表的工具软件。它是微软公司的办公EXCEL分析数据五步骤:认识EXCEL52认识EXCEL5认识EXCEL标题栏功能区编辑栏工作表编辑区自定义访问工具栏单元格名称框状态栏53认识EXCEL标题栏功能区编辑栏工作表编辑区自定义访问工具栏一张源数据表是做数据统计分析和制作图表的基础；数据表“列”为字段，“行”为记录，一条记录有多个属性用多个列值来体现；

例：一批图书信息购成一张表，每一种图书属性包括（书名、作者、出版社、出版时间、ISBN等信息），每一种图书信息占一行。数据输入那些事儿书名作者出版社出版时间ISBN公共政策与地区差距刘尚希,孟春等著中国财政经济出版社20137-5005-9398-8压力应对与大学生心理健康李虹著北京师范大学出版社20147-303-07163-6企业管理学主编胥悦红经济管理出版社2012978-7-5096-0212-6国际贸易实务百问百答马荣编著企业管理出版社20127-80147-708-1问题首富黄光裕张小平著浙江人民出版社2014978-7-213-04382-6欧莱雅美容王国的财富传奇李野新,周俊宏著浙江人民出版社2014978-7-213-04251-554一张源数据表是做数据统计分析和制作图表的基础；数据输入那些事一维表与二维表一维表的列标签是字段。要从上述表中找销售量最高是哪一年哪个地区,对一维表一个排序简单搞定,对二维表就相对复杂，为了数据分析的方便,尽量使用一维表；表1：二维表表2：一维表55一维表与二维表一维表的列标签是字段。表1：二维表表2：一维数据分析前请将合并表拆分此表按地区进行数据筛选时(比如:北京)，结果只有一条数据,本来有3条销售信息;

方法:选中合并格,点击“合并后居中”,将单元格进行拆分。对于合并单元格,系统默认把它置于被合并的最左上单元格中,其它单元格为空;56数据分析前请将合并表拆分此表按地区进行数据筛选时(比如:北数据表站着比躺着好方法：让躺着的表站起来，通过"复制--选择性粘贴--转置"完成;57数据表站着比躺着好方法：让躺着的表站起来，通过"复制--选择数据输入技巧输入以0开头的数字，先输入英文状态的单引号即可，也可在输入前将单元格格式设置为"文本"再输入;

输入分数,在开头输入0和空格即可;

输入身份证号码,先输入英文状态单引号,系统会将其识别为文本而非数值;

设置单元格格式为日期即可；58数据输入技巧输入以0开头的数字，先输入英文状态的单引号即可，选中当前列，设置单元格格式,自定义格式中输入"HDFT"00"-"000,在输入时直接输入00020，系统自动显示为HDFT00-020,下列各单元格依次输入04150,06018…即可;自定义数字格式在输入系列设备代号或人事编号是可以使用此功能;此处0为数字占位符;59选中当前列，设置单元格格式,自定义格式中输入"HDFT"00大量小数的输入通过"文件-选项-高级-自动插入小数点",设置小数点位数(例如:2位),只输入数字部分,提高输入效率;整数后需补齐0(0的个数与设置的小位点位数一致);使用：学生成绩、财务金额等；60大量小数的输入通过"文件-选项-高级-自动插入小数点",设置数据有效性设置当一张表格给多人来填报或需输入数据条数较多时通过数据有限性设置保证数据输入的正确性和规范性;可以设置数据类型（整数、小数、序列、日期等）；可以设置输入提示，也可以设置输入错误时警告语；61数据有效性设置当一张表格给多人来填报或需输入数据条数较多时通填充柄的妙用填充柄可以将事先设置好的系列内容填充到拖拉到的区域,系统提供了等差填充,等比填充,星期填充,月份填充,日期填充等；在星期填充,日期填充中还可选择工作日填充;在带公式的填充中填充柄也发挥极大的作用；通过“文件--选项--高级--常规--自定义序列”可以导入表中某一列固定内容,下次使用时填入第一项,拖拉填充柄实现自动填充序列；62填充柄的妙用填充柄可以将事先设置好的系列内容填充到拖拉到的区单元格引用单元格引用：相对引用、绝对引用、混合引用使用“$”符号来锁定引用。相对引用：A1、B5绝对引用：$A$5、$C$2快捷键为F4混合引用：$A2、D$1主要在公式中灵活选用。63单元格引用单元格引用：相对引用、绝对引用、混合引用16引用格式:工作薄存放地址[工作薄名称]工作表名称!单元格地址例：E3=[EXCEL演示图表.xlsx]加班!C2跨工作薄引用64引用格式:工作薄存放地址[工作薄名称]工作表名称!单元格目的:使数据便于阅读;路径:视图--窗口--冻结窗格三种方式:冻结首行,冻结首列,冻结拆分窗格;冻结首行:光标条上下拉动时首行不动,对于行数多的表,往下翻看数据时可以清楚看到表头;冻结首列:光标条左右拉动时首列不动,对于列数多的表,往下翻看数据时可以清楚看到第一列;冻结拆分窗格:将光标放在C4单元格,执行此命令,表示C之前的列(即AB列冻结)4之前的行(123行冻结);冻结窗格65目的:使数据便于阅读;冻结窗格18目的:使数据便于阅读;方式:选中要隐藏的行(或列),点击鼠标右键,执行"隐藏"命令;取消:选中被隐藏的行(或列)的上下(或前后)两行,点击鼠标右键,执行"取消隐藏"命令即可;

隐藏行或列66目的:使数据便于阅读;隐藏行或列19目的:快速阅读数据的最外边(Ctrl+右箭头),最下边(Ctrl+下箭头);在大量数据面前,用鼠标滚动下翻实在OUT了;"Ctrl+箭头键"数据一步到底67目的:快速阅读数据的最外边(Ctrl+右箭头),最下边(Ct选中数据区域,通过"数据--分级显示--分类汇总",设置汇总项(销售额),分类字段(省份),汇总方式(求和),系统自动生成按省份的分类汇总表;通过左侧+和-按钮可以显示和隐藏被汇总的信息;前提:进行分类汇总前需对分类字段进行排序;缺点:一次只能对一个字段进行汇总,只能构成一维汇总表;要想对两个或两个以上字段进行汇总,制作二维汇总表.分类汇总表68选中数据区域,通过"数据--分级显示--分类汇总",设置汇总例如:要进行每个省份每个月销售汇总;或者每个产品在每个省份的销售汇总,这类问题比较常见,需使用"数据透视表"功能;选中数据区域,"插入--数据透视表",设置存放位置,选择字段,在行标签,列标签,数值区域拖动字段;数据透视表69例如:要进行每个省份每个月销售汇总;或者每个产品在每个省份的查找与去重70查找与去重23查找与替换71查找与替换24排序与筛选72排序与筛选25函数函数分类函数名功能举例文本MID从文本字符串中指定的起始位置起,返回指定长度的字符=MID(D3,7,4)"1980LEFT从文本字符串左边起,返回指定长度的字符;A11"=LEFT(D4,1)"ALEN返回文本字符串的字符数F713.50"=LEN(D4)"7逻辑IF判断是否满足某个条件,满足返会一个值,不满足返回另一个值;89"=IF(D5>80,"优","")"优AND所有条件参数均为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87"=AND(A1>80,B1>80)"FALSEOR只要其中一个条件为真,结果返回TRUE,否则返回FALSE;A1=68,B1=87"=OR(A1>80,B1>80）"TRUE统计COUNTIF计算某个区域中满足给定条件的单元格个数;参考函数说明和帮助

MAX返回一组数值的最大值

MIN返回一组数值的最小值

查找与引用LOOKUP从单行或单列或数组中查找一个值

HLOOKUP通过与首行值的对比来查找值

VLOOKUP通过与首列值的对比来查找值

财务

可以计算贷款月支付额,累计偿还金额,资产折旧值多种财务数据;

数学和三角函数

工程

日期和时间

…

73函数函数分类函数名功能举例文本MID从文本字符串中指定的起始MID：从文本字符串中指定的起始位置起,返回指定长度的字符;例：A1B1=MID（A1，7，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文本函数74MID：从文本字符串中指定的起始位置起,返回指定长度的字符;IF：判断是否满足某个条件,满足返会一个值,不满足返回另一个值;例：A1=89，D1=IF（A1>80，,“优”,“”),结果：优AND：所有条件参数均为真,结果返回TRUE,否则返回FALSE;例：A1=68,B1=87，C1=AND(A1>80,B1>80)，结果：FALSE；OR：只要其中一个条件为真,结果返回TRUE,否则返回FALSE;例：A1=68,B1=87，C1=OR(A1>80,B1>80），结果：TURE；逻辑函数75IF：判断是否满足某个条件,满足返会一个值,不满足返回另一个统计函数COUNTIF：计算某个区域中满足给定条件的单元格个数;例：=COUNTIF（B：B，B3），计算B列中与B3单元格值相同的单元格个数。COUNTIF（range,criteria)参数：range要计算其中非空单元格数目的区域参数：criteria以数字、表达式或文本形式定义的条件MAX：返回一组数值的最大值MIN：返回一组数值的最小值76统计函数COUNTIF：计算某个区域中满足给定条件的单元格个查找与引用函数LOOKUP