Excel菜鸟学习笔记

Excel 专栏收录该内容
1 篇文章 0 订阅

一、常用的分析模型:

波特五力模型:用于行业分析和商业战略研究

SWOT分析模型:用于市场分析(strength、weakness、opportunity、threats)

5W2H分析模型:用于解决问题(what、why、when、where、who、how、how much)

PEST分析模型:用于宏观环境分析(political、economic、social、technological)

SCP分析模型:用于结构绩效分析(structure、conduct、performance)

二、常用数据分析方法:

1、对比分析法

① 根据时间标准进行对比分析:

同比:为了消除季节的影响,说明本期分析数据与去年同期分析数据对比而达到的相对数据。

环比:本期分析数据与前一期数据之比,表明现象逐期的发展速度。

② 根据空间标准进行对比分析:

与相似空间比较:与同行公司比较,与公司不同部门之间比较

与先进空间比较:本公司与国内最好的公司比较

与扩大的空间标准比较:本公司与全国平均水平比较

③ 根据经验或理论标准进行对比分析:恩格尔系数

④ 根据计划标准进行对比分析:

2、分组分析法;

① 按品质标志分组分析法:

② 按数量标志分组分析法:

3、结构分析法:

4、高级数据分析法:

① 时间序列分析法

② 聚类分析

③ 相关分析

④ 回归分析

三、常用的快捷键:

工作簿新建:ctrl+N

关闭工作簿:ctrl+F4

保存:ctrl+s 另存为:F12

输入当前的日期:ctrl+;

输入当前的时间:ctrl+shift+;

查找:ctrl+f

替换:ctrl+h

定位:ctrl+g

插入行:ctrl+“+”小键盘上的加号

1

 

 

1

隔行批量插入:添加辅助列下拉增加——ctrl+g定位——常量——右击1选插入整行,确定

快速删除空白行:选中区域,——ctrl+g——空值——确定——右击删除整行

在输入如身份证号码、电话号码等很长的数字时,需要将单元格设置为文本格式。

四、公式与函数

① 算术运算符:

② 比较两个值,结果为TRUE或FALSE:

③ 文本连接运算符(&)用于连接两个文本字符串

例如“="张三"&" 先生/女士"”,结果为:张三先生/女士

 

 

④ 引用运算符:对单元格区域进行合并计算

⑤ 运算符优先级:引用运算符(:、,、(空格))>负数(-)>百分比(%)>乘和除运算符(*、/)>加和减运算符(+、-)>文本连接运算符(&)>比较运算符(=、>、<、<=、>=、<>)

⑥ 使用公式和函数产生的常见错误信息:

五、数据收集方法

1.       网站

① 国家统计局

② 中国经济信息网

③ 百度数据研究中心

④ 人大经济论坛

⑤ 各省统计局网站

2.    问卷调查

① 标题:家电市场调查问卷

② 前言:您好,我们正在进行一项关于家电市场状况的调查,需要占用您大约2分钟的时间。对于您能在百忙之中填写此问卷表示感谢!

③ 正文:被调查者信息(性别、年龄、职业等),调查项目,调查者信息。

④ 结束语:为了保证调查结果的准确性,请您如实回答所有问题。您的答案对于我们得出正确的结论很重要,希望能得到您的配合和支持,谢谢!

3.      用Excel设计调查问卷:

① 打开Excel文件-选项-自定义功能区-开发工具-确定

② 插入-文本框-横排文本框输入问卷标题和前言

 

③ 用分组框对问题进行分组,开发工具-控件-插入-控件工具箱-表单控件-分组框-拖动十字形鼠标-将“分组框1”改为“1、您的年龄”

④ 选项:开发工具-插入-控件工具箱-表单控件-选项按钮-添加选项,右击按住ctrl可以调整位置

⑤ 复选框:多选时,开发工具-插入-复选框

⑥ 美化问卷:视图-显示-取消网络线,右击文本框-设置形状格式-无线条

4、创建Excel表

在工作表上,选择区域,插入-表格-创建表-确定

设置表标题-表格工具-标题取消勾选,表格工具-删除重复项-数据包含标题

5、在Excel中输入数据

按Enter键移动到同一列下一行的单元格;

按Tab键移动到同一行下一列的单元格。

① 数值数据的输入:整数、小数、分数(如1/3,应输入0空格1/3回车)、百分比、科学计数法

② 文本数据的输入:数据中需要换行,可使用Alt+Enter键

仅包含数字的文本数据的输入:输入时在其前面加西文半角的“'”号或者设置单元格格式为“文本”格式

③ 日期和时间数据的输入:

连字符(-)或斜杠符(/)分隔日期

使用冒号(:)分隔时间的时、分、秒

在时间数字加空格和字母a或者am(上午)或p或者pm(下午)

Ctrl+分号(;)输入系统当前的日期

Ctrl+Shift+分号(;)可以输入系统当前的时间

6、快速输入数据的方法:填充

① 序列的填充方法:拖曳填充句柄,或双击填充句柄右下角黑色点,自动填充,指定填充方式

② 使用Ctrl+Enter键填充相同的数据

③ 自定义序列:月份,季度,星期。Excel选项-高级-编辑自定义列表-自定义序列

7、在Excel中导入数据:

① 导入文本数据:文件-打开-文本文件-

选中-分隔符号-数据包含标题-下一步-Tab键选中-下一步常规-完成

② 导入网站数据:新建工作簿-数据-获取外部数据-自网站-输入网址-转到-选择箭头导入-工作表-确定

③ 导入数据库数据:数据-其他来源

8、数据加工

(1)    有效性审核:

① 定义有效性规则:定义单元格范围的数据有效性规则

② 圈出无效数据:“数据”|“数据工具”|“数据验证”|“圈释无效数据”命令,Excel将以红色圈出显示无效的数据

(2)    处理重复数据:

① 用条件格式找出重复数据:选择区域-开始-样式-条件格式-突出显示单元格规则-重复值-浅红填充深红色文本-确定

② 用公式找出重复数据:增加一列,重复计数,用COUNTIF函数:统计某个区域内符合指定单个条件的单元格数量。=COUNTIF(B:B,B3)意思是在B列中统计B3单元格中的内容的出现次数,相对引用将公式向下拖动复制公式=COUNTIF(B:B,B4),得到重复计数的列表。

修改公式=COUNTIF($B$3:B3,B3)绝对引用统计当前出现的次数,得到按出现顺序编号的列表。

绝对引用与相对引用:

相对单元格引用(例如A1)基于包含公式和所引用单元格的相对位置

绝对单元格引用(例如$A$1)总是引用所指定单元格的位置

混合引用具有绝对列和相对行,或是绝对行和相对列(例如$A1、A$1等)

切换相对引用、绝对引用和混合引用的快捷键为F4功能键

③ 删除重复数据:数据-删除重复项-数据包含标题-列-确定

(3)    处理缺失数据:

① 股票代码:六位数002467,前面的0可以设置自定义单元格格式,由Excel自动添加前面的0,操作方法:在工作表中拖动鼠标选中A列中保存股票代码的单元格区域;右击-设置单元格格式-数字-分类-自定义-在右侧类型中输入‘00000#’--确定

② 均值插补:用插补类的均值代替缺失值。

③ 推理插补:通过对已有数据进行推断来确定插补值。公式=ROUND((H135-I135)/I135*100,2)&”%” 计算上涨空间,单元格J135的值可以用H135和I135的值来推算。

④ 回归插补:建立回归模型

⑤ 随机插补:=ROUND(I132*(1+INT(RAND()*20+10)/100),2)

RAND是随机函数,返回一个大于或等于0且小于1之间的随机数字,生成A~B区间的随机数公式为:=INT(RAND()*(B-A)+A),要注意将结果转换为数值型。

9、排序:

(1)    工资条的制作:添加一个辅助列,自动填充1-15,向下复制两遍-复制表头行到第三遍的位置-选中所有区域-筛选-辅助列那里点击升序-确定

(2)    按行排序:选中第一行-开始-排序和筛选-自定义排序-选项-按行排序-主要关键字-行1-确定

(3)    按列排序:选中一列-开始-排序-升序降序-确定

(4)    按多个列或行进行排序:当关键字相同的数据,再选一列按次要关键词排序:选中要进行多列排序的区域中的某一个单元格-排序-自定义-主要关键字-添加条件-次要关键字-确定,在次序里可以自定义序列进行排序。

(5)    按颜色排序:排序依据-单元格颜色-红色在顶端-确定

10、用筛选剔除数据:

(1)    自动筛选:选择一个单元格-筛选-标题行下拉箭头-文本筛选/数字筛选/日期筛选-自定义自动筛选

(2)    高级筛选:选择筛选区域-筛选-高级筛选-设置筛选条件-选择不重复的记录-确定:定义条件区域时,两个条件在同一行,则必须同时满足;不在同一行,则满足一个条件即可。

(3)    清除筛选:全部显示,清除

11、分组

(1)    按品质标志分组:按研究对象的某种属性特征分组

(2)    按数量标志分组:

① 单项式分组:变量值不多且变化范围不大的离散型变量

② 组距式分组:把总体按数量标志分为几个区间,每个区间组成一个分组。

(3)    快速统计各分组:

① 使用数据分析加载宏:文件-选项-加载项-管理Excel加载项-转到-加载宏-分析工具库-确定-数据分析

② 按品质标志分组——统计空调安装的服务评价

评价共为4个级别:A:非常满意B:满意C:一般D:不满意

统计满意度,需要在数据分析加载宏中用“直方图”工具,但是必须是数值,因此将A~D转换为1~4,用公式=IF(A2=’A’,1,IF(A2=’B’,2,IF(A2=’C’,3,4)))或者用=CODE(A2)-CODE(‘A’)+1

CODE函数:返回文本字符串中第一个字符的数字代码,使用格式:CODE(text),只有一个参数,返回text文本中的第一个字符的代码。

‘A’;数字代码为65,’B’为66....

直方图:数据分析-直方图-确定,加一列接收区域1234,,输入输入区域、接收区域、输出区域-确定。

③ 数量标志分组——电视销量分组

收集近100天公司电视机的日销售台数,要求电视机日销售数据进行分组统计。

组距式分组0-100,100-120,120-140,140-160。。。。

直方图:加一列接收区域,输入每个分组区间的上限值-数据-分析-直方图-确定-输入区域(销量的区域)-接收区域(自己加的那一列数)-输出区域(选择一列如$N$1)-确定-得到接收和频率,

 

 

将统计结果复制到另一张工作表中,得到

图表输出得到直方图

(4)    分类汇总:

在使用分类汇总之前,数据区域必须先按分类的字段排序,将同一类关键字排列在相邻行。

选择B列中任意一个单元格-开始-排序-升序-数据-分级显示-分类汇总-分类字段选择-汇总方式计数、求和、平均值、最大值、最小值-选定汇总项-确定

 

 

12、数据透视表:汇总、交换行列、筛选数据、查看明细数据

(1)    创建数据透视表:打开工作簿,选择一个单元格-插入-数据透视表-选择区域-新工作表-确定-空的数据透视表-右侧的数据透视表字段-每列名称选择-拖动行标签,列标签

(2)    透视表的计算和汇总:值汇总依据-求和、平均值、最大值、最小值

(3)    透视表的筛选和排序:建完数据透视表后在标题有下拉箭头,标签筛选、值筛选,或者在数据透视表字段下方有-筛选器-将字段拖到列表中

 

 

13、工资数据:

 

(1)      利用 Excel 的条件格式将可能有错误的数据突出显示出来:将工作表中的数据全部选中。“开始”-“样式”-“条件格式”-“突出显示单元格规则”命令-- 其他规则-“新建格式规则”- -“使用公式确定要设置格式的单元格”-“为符合此公式的值设置格式”文本框中输入以下公式: =IF(V2=L2−U2,FALSE,TRUE)若是V2=L2−U2,则返回FALSE,就不需要突出显示,当V2的值出错时,就要突出显示,返回TRUE值。单击“格式”按钮-“设置单元格格式”-可以设置符合条件的单元格的格式。这里设置为填充红色底纹,单击“确定”

(2)       多项检查,需要定义的公式比较复杂,通常可使用 AND 或 OR 函数将多个条件组合在一起:=OR(E2<0,IF($V2=$L2-$U2,FALSE,TRUE))

检查两个错误,数据小于0或者不满足V2=L2-U2的数据。

 

 

(3)    数据透视表查表合并数据:在工资表中加入员工信息(职称和学历)

 

用VLOOKUP函数将职称和学历填到对应员工的位置:

① 插入-职称和学历-在职称单元格E2下定义函数:

=VLOOKUP(B2,员工信息!$A$2:$C$63,2,FALSE)

VLOOKUP查表函数,搜索某个单元格区域的第一列,返回该区域相同行上任何单元格的值,VLOOKUP 函数的语法如下:

VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup]) 

各参数的含义如下:

 lookup_value:要在表格或区域的第一列中搜索的值。(要在员工信息表中的第一列搜索B2的值,所以是B2)

 table_array:包含数据的单元格区域。(要搜索查找的是员工信息表) 

 col_index_num:table_array 参数中必须返回的匹配值的列号。col_index_num 参数为 1 时,返回 table_array 第一列中的值;col_index_num 为 2 时,返回 table_array 第二列中的值,依此类推。(要返回职称信息,因为职称在员工信息表中是第二列,所以这里是2)

 range_lookup:一个逻辑值,指定希望 VLOOKUP 查找精确匹配值还是近似匹配值,如果 range_lookup 为 FALSE,则不需要对 table_array 第一列中的值进行排序,且只查找精确匹配值。

在本例的公式中,就是根据同一行姓名所在列(B 列)的值进行查找,在“员工信息”表中查找相同的姓名,找到后返回被查表的第 2 列(职称)。这样,就可将员工的职称数据填充到工资表中了。

② 类似的方法,学历F2定义公式:

=VLOOKUP(B2,员工信息!$A$2:$C$63,3,FALSE)

③ 拖动选中 E2、F2 单元格,向下拖动填充柄,则可以将 1 月工资表中员工的“职称”和“学历”信息填充完成。

④ 12张工作表,每张表插入两列,同时操作:选择12张表,ctrl在“02 月”工作表中进行操作:插入两列,输入这两列的列名“职称”和“学历” -在 E2、F2 单元格中定义查表公式 -向下拖动填充柄复制公式-操作完成后,在“01 月”工作表标签上单击,取消对多张工作表的选中状态。

14、拆分信息:将A列的信息拆分成姓名和电话号码:公式

B2:=LEFTB(A2,SEARCHB(“?”,A2)-1)

C2:=MIDB(A2,SEARCHB(“?”,A2),11)

SEARCHB是在一个字符串中查找特定的字符位置的函数,而且可以区分单双字节,?表示任意一个单字节的字符,属于通配符,不是真的查找问号。

MIDB是按字节数截取,一个汉字算两个字节,字母和数字分别算一个字节。

15、描述性分析:

(1)    算数平均数:AVERAGE

(2)    众数:MODE

(3)    中位数:MEDIAN

(4)    三者关系:

① 算数平均数=众数=中位数:表示数据由单一众数,且频数分布对称。

② 算数平均数>众数>中位数:表示数据存在最大值,且频数分布呈现右偏状态。

③ 算数平均数<中位数<众数:表示数据存在最小值,且频数分布呈现左偏状态。

(5)    样本总体方差:VARP

(6)    样本总体标准差:STDEVP

(7)    对工资数据进行描述性分析:数据-分析-数据分析-描述统计-确定-选择输入区域-勾选标志位于第一行-汇总统计-确定

 

 

 

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

小糖姜爱学习

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值