22. 手动排序 :默认的都是按列排序,按行排序需要手动设置手动排序,排序-其他排序-手动排序-选中要修改的单元格,鼠标变成四个箭头后下拉,完成手动排序 |
23. 移动或复制工作表: 工作表-右键-移动或复制-可以数据建立到新工作表或者新建的工作簿-建立副本(建立副本表示复制,否则就是剪切) |
24. 快速复制工作表: 按住Ctrl键鼠标往右拖工作表。 |
25. 数据清洗:条件格式-筛选内容按特定格式呈现-排序(将要选的内容呈现在最前面)-放到word进行数据的查找替换分割(eg:将 |
|
# 三、常用公式 |
|
1. 一列变多列,多列变一列:利用单元格地址引用 |
|
2. 利用错误值计数: 输入公式:=COUNT(0/(K3:K15>=1000)),CTRL+shift+enter结束公式输入。当(K3:K15>=1000)为FALSE,0除0就不是数字,就不会被计数 |
|
3. 排错:公式-追踪引用单元格、公式检查、追踪错误,可以检查公式等的错误原因及源头 |
|
4. 数据验证:可以设置单元格数据的取值范围和其他规则等、并图释不符合规则的数据 |
|
5. 公式-公式求值:可以逐步理解公式函数的实现过程(F9可以单独选择解释) |
|
6. 文本链接符&:A1是张,B1是三,=A1&B1结果为张三,利用文本链接&可以将数据合并到一个单元格 |
|
7. 条件求和:求迟到次数,=COUNTIF(B2:K2,"b"),b表示迟到,~ countif(求和范围,标准) |
|
8. 某月的最后一天:=EOMONTH(B3,2),返回从B3日期开始2个月后的当月的最后一天的日期 |
|
9. |
|
|
|
10. 生成间断序号:=IF(C2="","",COUNTA($C$2:C2)) |
|
11. 图片链接:hyperlink(图片路径,友好名称);选中图片所在文件夹,按住shift,右击,选择“复制为路径”命令。 |
|
12. 多条件求和:=COUNTIFS(E2:E10,">90",F2:F10,">90") ;=SUMPRODUCT((B2:B10="销售一部")(C2:C10>950)), '=SUMPRODUCT((区域1=条件1)(区域2=条件2)* 求和区域)。 |
|
13. 计算数字出现的次数:=LEN(A2)-LEN(SUBSTITUTE(A2,"9","")),计算单元格内容9出现的次数 |
|
14. if函数成绩判断:=IF(M3<60,"不及格",IF(M3<=70,"一般",IF(M3<=85,"良好","优秀"))) |
|
15. countif:=COUNTIFS(P2:P122,">="&R2,P2:P122,"<="&S2),地址要用文本连接符,不然在引号里面会当成文本,只显示r2,s2 |
|
# 四、常用函数 |
|
数学函数: |
--- |
INT() |
MIN() |
MOD() |
COUNT() |
ROUND() |
ABS() |
SQRT() |
指定日期前后月份的日期 |
RAND() |
某个月份最后一天的序列号 |
RANDBETWEEN() |
DATEDIF() |
|
文本函数: |
MID() |
垂直方向查找 |
LEFT() |
计算偏移量 |
RIGHT() |
返回一个匹配的值的位置(数字) |
LEN() |
TEXT() |
文本字符串指定的引用 |
REPT() |
REPLACE() |
引用列的数据 |
SUBSTITUTE() |
水平方向查找 |
DATEDIF函数 |
--- |
**起始日期 ** |
2010/8/1 |
2010/8/1 |
2010/8/1 |
2010/8/1 |
2010/8/1 |
2010/8/1 |
|
large(数值范围,第k个大的数值) :返回第k个大的数值 |
|
workday(2019/2/13,10):返回从2019/02/13开始第10个工作日 |
|
SUMPRODUCT(条件求和之王) :SUMPRODUCT((区域1=条件1)(区域2=条件2) 求和区域) |
|
# 五、数组 |
|
1. 数组快捷键:Ctrl+shift+enter( 注:以下数组公式都要以此快捷键结束,*/+替代AND/OR,不能直接使用and/or ) |
2. 数组常量:以逗号分列,以分号分行。 |
3. 判断身份证长度:=((LEN(B2:B11)=15)+(LEN(B2:B11)=18)) ;其中+表示or的意思,结束时按Ctrl+shift+enter |
4. 判断是否退休:=IF(((B2:B11="男")(C2:C11>=60))+((B2:B11="女")(C2:C11>=55)),"是","否") |
5. 数组求和:需要用快捷键,不用快捷键的话用sumproduct求和 |
6. OFFSET:求偏移量,对于合并单元格,不能用填充柄来拖,全部选中用Ctrl+enter,举例:=OFFSET($H$1,COUNTA($B$1:B1),) |
7. 统计一车间男职工工资总和:=SUM((C2:C10="男")(D2:D10)(B2:B10="一车间")) ;其中*表示and的意思 |
8. 计算频率:frequency(数值范围,分割数据组):以一列垂直数组返回 一组数组的频率分布 |
9. 前三名的数量总和:=SUM(LARGE(B2:B10,{1,2,3})) ;{1,2,3}为数组常量,不需要用数组快捷键 |
|
# 六、查找与引用函数 |
|
查找与引用函数: |
--- |
VLOOKUP() |
|
垂直方向查找 #vlookup(查找值,查找范围,返回第几列,精确或模糊查找) |
|
使用条件:查找值在查找范围中必须位于第一列,并且是唯一值 |
|
OFFSET() |
计算偏移量(返回以某一个单元格为基点后偏移长宽高的数据内容)#OFFSET(reference基点,rows,cols,[height],[width]) |
MATCH() |
INDEX() |
ROW() |
COLUMN() |
INDIRECT() |
HLOOKUP() |
Choose() |
Find() |
|
1. 计算累计数据:=SUM(OFFSET($B$2,,,ROW()-1)) |
2. 计算两列数据相同个数:=COUNT(MATCH(B2:B11,A2:A11,0)) 结束时用数组快捷键 |
3. 标记匹配结果:=MATCH(B3,$B$1:$H$1,0),在条件格式里面实现 |
4. 跳行提取数据:=INDEX($B$1:$B$12,ROW()*2) |
5. index(已知行列号找值),match(已知值找行号或者列号),常搭配使用:=INDEX(E2:G10,MATCH(B3,D2:D10,0),MATCH(B4,E1:G1,0)) |
6. INDIRECT函数的使用,数据验证,批量定义名称。 |
7. 一列变五列:=INDIRECT("A"&5*ROW(B2)-5+COLUMN(B2))&"" |
8. 批量定义名称:选中数据区域-定位常量-公式-定义名称下面根据所选内容创建。 |
9. indirect(间接引用),A2(A3),A3(100元),indirect(A2):返回的是A2中A3地址对应的数据100元,indirect(”A2”),返回的是文本A3 |
|
# 七、图表 |
|
1. 1 条形图 : #修改数据标签格式代码(将负号修改为正号):(0;0); 右键-另存为模板,下次可以直接调用,必须使用默认的路径 |
|
![](https://img-blog.csdnimg.cn/20190214154530115.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 2 气泡图: #1.不能选文本 2.注意修改坐标轴横纵交叉位置的值 3.删除网格线:选中然后delete 4.图标:用插入文本实现 |
|
![](https://img-blog.csdnimg.cn/20190214161300584.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 3 组合图:(散点图+条形图) |
|
![](https://img-blog.csdnimg.cn/20190214162826853.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 4 动态图表:数据验证 |
|
![](https://img-blog.csdnimg.cn/20190214170736909.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 5 动态图表:定义名称(选择数据时用定义的名称) |
|
![](https://img-blog.csdnimg.cn/20190215111101880.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 6 前后对比图表 (注意修改坐标轴值为一致,将裸眼视力设置为次坐标轴,方便重叠) |
|
![](https://img-blog.csdnimg.cn/20190215112626182.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 7 数据标签格式设定:[红色]<0;[蓝色][>100]0;0 |
|
![](https://img-blog.csdnimg.cn/20190215113413594.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 8 计划与实际对比图表 |
|
![](https://img-blog.csdnimg.cn/20190215113737139.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
1. 9 利用横行分割背景效果的折线图(设置辅助列) |
|
![](https://img-blog.csdnimg.cn/20190215114352336.png?x-oss- |
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
# 八、数据透视表 |
|
1. 注意事项:数据透视表不能有空单元格,以空单元格分割数据区域,选中一个数据区域的单元格就可以插入透视表 |
2. 修改汇总方式:可以对汇总列-右键-字段设置:设置显示集中汇总方式 |
3. 调整分类汇总显示方式:设计-分类汇总 |
4. 取消行标签:设计-报表布局-以大纲方式显示 |
5. 按月汇总:表头-月-右键-字段设置-同时设置几种汇总方式 |
6. 值显示方式:数据透视表格-右键-只显示方式-显为为百分比、差异百分比、升序、降序等等 值显示方式-升序 |
7. 累计数值:汇总项-右键-值显示方式-按某一字段汇总-可以按照日期汇总(比offset更简单) ![](https://img-blog.csdnimg.cn/20190215144706717.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
8. 数据组合-取消组合:列名-右键-组合-可以按季度、年、月等组合(从而显示不同的值的组合)、还可以按天数组合 |
9. 切片器:数据透视表工具-分析-插入切片器,也可以加入数据透视图,切片器工具-报表连接,可以连接多个数据透视表。 ![](https://img-blog.csdnimg.cn/20190215150055969.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
10. 合并几张表格作为数据透视表:文件-选项-快速访问工具栏-添加数据透视表和数据透视图向导;左上角合并透视表-多重合并计算数据区域 ![](https://img-blog.csdnimg.cn/20190215151101340.png) |
11. 随数据增减的透视表:用offset定义名称(=OFFSET(源数据2!$A$1,,,COUNTA(源数据2!$A:$A),COUNTA(源数据2!$1:$1))),数据透视表插入的时候:选择一个表或区域-用定义名称的名字 |
12. 显示数据透视表工作(行列值等设置)界面:点击数据透视表-分析-字段列表 |
13. 显示报表筛选页:将相关数据放到筛选-分析-选项-显示报表筛选页,可以按筛选的各个字段名生成多张报表,可以利用这个功能 同时生成不同表名的工作表 |
14. 显示需要的行或列总计数:数据透视表-分析-总计-仅对行、列启用等 |
15. 生成汇总信息的详细工作表:数据透视表-点中一个聚合数据-右键-显示详细信息-生成与该数据相关信息新的表格 |
16. 插入计算字段,可以增加其他列 ![](https://img-blog.csdnimg.cn/20190217145811222.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
17. 数据透视表行列标签修改:按F2进行修改 |
18. 右键-组合,可以将数据进行分组、右键-数据透视表选项可以设置分组对齐方式 |
|
# 九、交互式界面和组合框动态制作 |
|
1. 插入组合框: 注意选中数据源区域和单元格链接。设置属性时可以按住Ctrl+鼠标右键能够选中控件。 |
2. 链接工作表的交互界面:=IFERROR(INDEX(INDIRECT($B6&"!A1:L11"),MATCH($C$2,INDIRECT($B6&"!A1:A11"),0),MATCH($C$3,INDIRECT($B6&"!A1:L1"),0)),"") #其中“!”代表工作表的意思 |
3. 计算人生剩余时间:条件格式=(ROW(A1)-1)*30+COLUMN(A1)<=DATEDIF($A$1,TODAY(),"m") |
4. 高级筛选:条件区域自己设置,可以是列表,也可以是函数列表 ![](https://img-blog.csdnimg.cn/20190216193013724.png) |
5. 动态图标与链接:数据粘贴到图表上面,可以选择性粘贴-图标链接-图片下移一层 |
6. 开发工具-插入-表单控件(不能选active-X控件),可以有相应的下拉列表等 |
|
# 十、录制宏 |
|
1. 录制宏的文件要另存为xslm后缀名的文件,防止录制宏的文件丢失数据 |
2. 录制宏流程:(1)先选中-使用相对引用 (2)鼠标放在表头(如:A1 (3)选中录制宏:修改宏表名、快捷键 Ctrl+d等 (4)开始录制相关操作 (5)录制完后光标放在下一个开始需要重复上一个操作的单元格 (6)执行宏:光标放在A1,按Ctrl+d开始录制的操作 |
3. 执行宏的方式:(1)快捷键(如上面设置的Ctrl+d) (2)开发工具-插入表单控件-按钮-选择刚才录制的宏,然后按按钮进行操作(3)修改宏代码:visual basic-工具-宏:修改代码,可以给予for循环语句……next,让执行宏一次性完成 ![](https://img-blog.csdnimg.cn/20190216201542820.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
# 十一、Power BI(商业智能) |
|
#IT傻瓜化与数据分析的完美结合,Power BI 包含了一系列的组件和工具,分为以下: |
|
## ** (一)Power Query : ** #查询编辑器# |
|
数据收集和清洗、获取和整理数据, 和原始数据有链接,修改原数据后需要刷新 |
|
- 进入界面: |
|
1. 从表格:数据-新建查询一栏-从表格,进入Power Query,没有撤销的功能(可以在应用的步骤里面删除和修改相应的操作);如果数据是表格-点击其中单元格可以看到-表格工具(设计-转换为区域就不是表格了) |
2. 从网页:数据-新建查询-自其他源-自网站 |
|
- 身份证相关操作: |
|
1. 提取前2、6位(添加列-提取-首字符) |
2. 提取出生日期(添加列-提取-范围<数字开始位置为0>-转换-数据类型-日期) |
3. 提取性别:身份证性别区分要看第17位数字,第17位数字代表性别,奇数为男,偶数为女(添加列-提取-范围-转换-数 据类型为:整数-转换-信息-偶数(偶数为TRUE,代表女)-转换-数据类型为:文本-转换-替换值-true改为女,false为男 |
4. 添加一列带姓氏的先生、女士: 选中姓名-添加列-提取首字符、选中性别列-添加列-替换值-男替换为先 生,女替换为女士,同时选中新生成的两列数据(注意先后顺序,按选中顺序合并)转换-合并列。 |
5. 提取工龄或者年龄:选中入职日期(或出生日期)-添加列-日期-年限-转换-持续时间-总年数-舍入-向下舍入 |
|
- 保存数据-关闭并上载 ( 和原始数据有链接,修改原数据后需要刷新 ) |
- 分组依据: 可以对数据进行分组聚合 |
- 添加列-条件列 :可以对数据进行范围分类 ![](https://img-blog.csdnimg.cn/20190217154259921.png) |
- 删除前后空格 (功能&trim函数只能删除前后空格,不能删除中文文本中间的空格):转换-格式-修整 |
- 取消合并单元格 -转换-填充-向下 |
- 透视列、逆透视 :类同于数据透视表,将数据改为简洁模式 |
- 转置 :注意修改:转换-将标题作为第一行-然后再转置-然后再将第一行作为标题 |
- 合并查询 :分别将两个表格上传到PQ,然后在PQ选中主表格-合并查询-将查询合并为新查询-选中两个表格的中介数据 |
- 追加查询 :标题不一样的数据会自动转到所属标题下,方法同合并查询 |
- 反转行 :就是数据逆序,转换-反转行 |
- 合并多个工作表 :数据-新建查询-从文件-excel工作簿-编辑(修改原始工作簿的数据,可以刷新生成的PQ数据,会跟着一起改变 |
- 合并多个工作簿 :放到一个文件夹-从文件夹导入PQ-编辑 ,=Excel.Workbook([Content],true) 首字母必须大写 |
- 统计离、入职员工信息: 将各年数据分别导入PQ,合并查询-左反、右反等等 |
- 统计流动人员的销售额: 将数据导入,编辑好之后关闭并上载至链接,然后插入数据透视表-使用外部数据源-选择链接 |
|
## ** (二)Power Pivot :#“超级”数据透视表# ** |
|
微软power BI系列工具的大脑,负责建模分析。模仿数据库,建立多个表的关系 |
|
- 计算列(行和列的位置)、度量值(表格下方) |
- 进入pp的方式 |
|
1. 添加到数据模型:直接将所在表格添加到数据模型 |
2. 从其他源:管理数据模型-从其他源-excel工作簿,可以同时添加几张工作簿 |
3. 从剪贴板:选择要添加的表格数据:Ctrl+c,在管理数据模型页面,点到表格单元格,点工作栏的粘贴,就可以添加了 |
|
- 数据源发生变化: |
|
1. 现有连接:一般都是工作簿的连接(点击刷新) |
2. 从剪贴板的:需要重新复制,然后替换粘贴以及追加粘贴等 |
|
- pp创建的大致流程 |
|
1. 打开需要建立表关系的相关表格:添加到数据模型等 |
2. 关系图视图:连接表格信息,1表示一端,*表示多端 |
3. 点击连线-右键-可以删除和编辑视图的关系 |
4. 数据表有了关系之后,可以在表格添加列录入函数:=related()会自动弹出相关链接,=calculate()计数, ![](https://img-blog.csdnimg.cn/20190218134708437.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
=SWITCH([省会],"广东","北上广","北京","北上广","上海","北上广","非北上广") |
|
5. 引用关系表数据: ![](https://img-blog.csdnimg.cn/20190217114538319.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
6. 管理度量值:excel工作表界面:度量值-管理度量值 |
7. 退出点关闭,再次进入pp点管理数据模型进入 |
8. 数据透视表:从pp界面进入并建立数据透视表 |
|
- 创建KPI |
|
1. 在度量值区域:完成率:=SUM('销售员'[实际完成])/SUM('销售员'[销售任务额]),鼠标右键-创建KPI,进行相关设置 |
2. 创建数据透视表之后,可以使用kpi相关数据 ![](https://img-blog.csdnimg.cn/20190218151739280.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
- 创建层次结构 |
|
1. 在关系视图中,选择要创建层次结构的字段,右键-创建出层次结构 |
2. 创建完可以方便创建数据透视表: ![](https://img-blog.csdnimg.cn/20190218153335219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU4NDEzNw==,size_16,color_FFFFFF,t_70) |
|
- 安全除法 2008年增长率:=DIVIDE([2008年销售额]-[2007年销售额],[2007年销售额]) |
- 省订单数量:=COUNTROWS(RELATEDTABLE('订单')) |
- 计值上下文 |
- TOTALYTD(年累计)、TOTALQTD(季度累计)、TOTALMTD(月累计) |
- 同比今年/去年、环比是这月/上月 |
|
## ** (三)Power View :#交互式报表# ** |
|
**在 Power View中,可以快速创建各种可视化效果,从表格和矩阵到[ 饼图 |
|
](https://baike.baidu.com/item/饼图/10816566) 、 [ 条形图 |
|
](https://baike.baidu.com/item/条形图/10816583) 和 [ 气泡图 |
|
](https://baike.baidu.com/item/气泡图/10816649) |
,以及多个图表的集合。 ** |
|
## ** (四)Powet Map : #地图可视化# ** |
|
- 打开 |
|
1. 选中数据-插入-三维地图-打开三维地图 |
2. 添加字段设置等 |
3. 数据:选择相关图表类型 |
4. 新场景:可以增加几个场景 |
5. 场景选项:设置播放的效果等 |
6. 播放:播放演示 |
7. 保存:创建视频 |
|
## (五)Power BI Desktop |
|
![](https://img-blog.csdnimg.cn/20190217133514329.png) |
和excel是独立的,主要用于发布到网上 |
|
- 导入 |
|
1. 文件-导入-excel工作簿内容 |
2. 可以根据操作界面进行可视化分析了 |
|
- 表数据最小单位为列,数据表最小单位为单元格 |
|
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210608151750993.gif) |