| 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）； 右键-另存为模板，下次可以直接调用，必须使用默认的路径 |
|
|  |
|
| 1. 2 气泡图： #1.不能选文本 2.注意修改坐标轴横纵交叉位置的值 3.删除网格线：选中然后delete 4.图标：用插入文本实现 |
|
|  |
|
| 1. 3 组合图：（散点图+条形图） |
|
|  |
|
| 1. 4 动态图表：数据验证 |
|
|  |
|
| 1. 5 动态图表：定义名称（选择数据时用定义的名称） |
|
|  |
|
| 1. 6 前后对比图表 （注意修改坐标轴值为一致，将裸眼视力设置为次坐标轴，方便重叠） |
|
|  |
|
| 1. 7 数据标签格式设定：[红色]<0;[蓝色][>100]0;0 |
|
|  |
|
| 1. 8 计划与实际对比图表 |
|
|  |
|
| 1. 9 利用横行分割背景效果的折线图（设置辅助列） |
|
|  |
|
| # 八、数据透视表 |
|
| 1. 注意事项：数据透视表不能有空单元格，以空单元格分割数据区域，选中一个数据区域的单元格就可以插入透视表 |
| 2. 修改汇总方式：可以对汇总列-右键-字段设置：设置显示集中汇总方式 |
| 3. 调整分类汇总显示方式：设计-分类汇总 |
| 4. 取消行标签：设计-报表布局-以大纲方式显示 |
| 5. 按月汇总：表头-月-右键-字段设置-同时设置几种汇总方式 |
6. 值显示方式：数据透视表格-右键-只显示方式-显为为百分比、差异百分比、升序、降序等等  值显示方式-升序 |
7. 累计数值：汇总项-右键-值显示方式-按某一字段汇总-可以按照日期汇总（比offset更简单）  |
| 8. 数据组合-取消组合：列名-右键-组合-可以按季度、年、月等组合（从而显示不同的值的组合）、还可以按天数组合 |
9. 切片器：数据透视表工具-分析-插入切片器，也可以加入数据透视图，切片器工具-报表连接，可以连接多个数据透视表。  |
10. 合并几张表格作为数据透视表：文件-选项-快速访问工具栏-添加数据透视表和数据透视图向导；左上角合并透视表-多重合并计算数据区域  |
| 11. 随数据增减的透视表：用offset定义名称（=OFFSET(源数据2!$A$1,,,COUNTA(源数据2!$A:$A),COUNTA(源数据2!$1:$1))），数据透视表插入的时候：选择一个表或区域-用定义名称的名字 |
| 12. 显示数据透视表工作（行列值等设置）界面：点击数据透视表-分析-字段列表 |
| 13. 显示报表筛选页：将相关数据放到筛选-分析-选项-显示报表筛选页，可以按筛选的各个字段名生成多张报表，可以利用这个功能 同时生成不同表名的工作表 |
| 14. 显示需要的行或列总计数：数据透视表-分析-总计-仅对行、列启用等 |
| 15. 生成汇总信息的详细工作表：数据透视表-点中一个聚合数据-右键-显示详细信息-生成与该数据相关信息新的表格 |
16. 插入计算字段，可以增加其他列  |
| 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. 高级筛选：条件区域自己设置，可以是列表，也可以是函数列表  |
| 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，让执行宏一次性完成  |
|
| # 十一、Power BI（商业智能） |
|
| #IT傻瓜化与数据分析的完美结合，Power BI 包含了一系列的组件和工具,分为以下： |
|
| ## ** （一）Power Query ： ** #查询编辑器# |
|
| 数据收集和清洗、获取和整理数据， 和原始数据有链接，修改原数据后需要刷新 |
|
| - 进入界面： |
|
| 1. 从表格：数据-新建查询一栏-从表格，进入Power Query，没有撤销的功能（可以在应用的步骤里面删除和修改相应的操作）；如果数据是表格-点击其中单元格可以看到-表格工具（设计-转换为区域就不是表格了） |
| 2. 从网页：数据-新建查询-自其他源-自网站 |
|
| - 身份证相关操作： |
|
| 1. 提取前2、6位(添加列-提取-首字符） |
| 2. 提取出生日期（添加列-提取-范围<数字开始位置为0>-转换-数据类型-日期） |
| 3. 提取性别：身份证性别区分要看第17位数字，第17位数字代表性别，奇数为男，偶数为女（添加列-提取-范围-转换-数 据类型为：整数-转换-信息-偶数（偶数为TRUE，代表女）-转换-数据类型为：文本-转换-替换值-true改为女，false为男 |
4. 添加一列带姓氏的先生、女士：  选中姓名-添加列-提取首字符、选中性别列-添加列-替换值-男替换为先 生，女替换为女士，同时选中新生成的两列数据（注意先后顺序，按选中顺序合并）转换-合并列。 |
| 5. 提取工龄或者年龄：选中入职日期（或出生日期）-添加列-日期-年限-转换-持续时间-总年数-舍入-向下舍入 |
|
| - 保存数据-关闭并上载 （ 和原始数据有链接，修改原数据后需要刷新 ） |
| - 分组依据： 可以对数据进行分组聚合 |
- 添加列-条件列 ：可以对数据进行范围分类  |
| - 删除前后空格 （功能&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（）计数，  |
|
| =SWITCH([省会],"广东","北上广","北京","北上广","上海","北上广","非北上广") |
|
5. 引用关系表数据：  |
| 6. 管理度量值：excel工作表界面：度量值-管理度量值 |
| 7. 退出点关闭，再次进入pp点管理数据模型进入 |
| 8. 数据透视表：从pp界面进入并建立数据透视表 |
|
| - 创建KPI |
|
| 1. 在度量值区域：完成率:=SUM(\'销售员\'[实际完成])/SUM(\'销售员\'[销售任务额])，鼠标右键-创建KPI,进行相关设置 |
2. 创建数据透视表之后，可以使用kpi相关数据  |
|
| - 创建层次结构 |
|
| 1. 在关系视图中，选择要创建层次结构的字段，右键-创建出层次结构 |
2. 创建完可以方便创建数据透视表：  |
|
- 安全除法  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 |
|
 |
| 和excel是独立的，主要用于发布到网上 |
|
| - 导入 |
|
| 1. 文件-导入-excel工作簿内容 |
| 2. 可以根据操作界面进行可视化分析了 |
|
| - 表数据最小单位为列，数据表最小单位为单元格 |
|
 |
