EXCEL与数据分析

既然不是仙"

已于 2024-04-10 16:18:37 修改

阅读量3.1k

点赞数 5

分类专栏： Excel 文章标签： excel 数据分析数据挖掘

于 2019-02-18 20:13:54 首次发布

本文链接：https://blog.csdn.net/weixin_44584137/article/details/86765133

版权

本文详述了Excel在数据分析中的应用，包括常用技巧、数据收集与清洗、公式和函数的使用、数据透视表的创建与操作，以及如何通过Power BI进行商业智能分析。内容涵盖隐藏公式、数据验证、条件格式、图表制作、数据透视表的组合与切片器，以及Power Query、Power Pivot、Power View等工具的运用。

摘要由CSDN通过智能技术生成

（一）Power Query ：#查询编辑器#

（二）Power Pivot：#“超级”数据透视表#

（三）Power View：#交互式报表#

（四）Powet Map: #地图可视化#

（五）Power BI Desktop

一、常用技巧

隐藏公式：设置单元格格式-保护-隐藏、视图-保护工作表
同时打开多个工作簿，选中后：shif+enter
定位条件：选择某种特定单元格
多个单元格一次性输入一个值：Ctrl+Enter
仅复制分类汇总结果：定位-可见单元格
定位-公式-错误
选择性粘贴-运算
选择性粘贴-将两列合并为一列：利用数字和空格-插入行-选择性粘贴跳过空格
选择性粘贴：数据转换乘
查找特定格式-从单元格中选取格式
讲0替换为补考：查找-替换-单元格匹配（以防70中的0被替换）
一列变5列：A1-A100，替换A为=A
快速合计（ =sum（））：Alt+=
快速选择：Ctrl+shift+上下左右箭头
快速选定不连续单元格：shift+F8
利用辅助列快速插入空行：1、1.5、2、2.5
打印设置
保护工作表：保护的是锁定的工作表，若是不保护—单元格格式设置-取消锁定
拆分冻结窗口
下拉列表：定义-定义名称-选择一列（名称管理可以删除相应数据）、-数据验证-序列（=姓名）、名称管理器-编辑（这里可以选择为多行多列）
行内容差异，查找两行内容的差异（定位条件-行内容差异单元格）
单元格格式自定义：a,b,c,d,e,f,则会这个规则下拉循环
下拉填充工作日：点击右键下拉可以实现
文字记忆式输入法：点击右键-从下拉列表中选择
数据验证：序列-男,女
单元格格式-数字自定义：[=1]"男";[=0]"女"
RANDBETWEEN(10，99):10-99的随机变量
填充√或×：选中区域，设置字体为 Wingdings 2，设置数据验证，序列,来源为：R,S，点击下拉按钮的R或S，获取带方框的√和×

二、数据收集、清洗技巧

抓取网页数据=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&I33&"&doctype=xml"),"//translation")
导入文本数据：数据-自文本-注意分隔符
快速填充：.输入一个值，右键下拉-快速填充与输入值代码相同的值
Ctrl+e：快速填充数据提取与上个输入值代码相同的值
快速输入1-10000：方法：在A列生成1至10000的连续序号，可以在A1单元格输入数字1，在名称框内输入“A1:A10000”，按Enter键。然后单击【开始/填充】，在下拉列表中选择【系列】，在【序列】对话框中，终止值输入10000，单击【确定】。
文本型数字转换为数值型数字
FIND-MID提取数据：find函数返回数字，可以作为MID函数的提取位置或长度. =MID(A2,1,FIND(" #",A2)); =MID(A2,FIND(" #",A2)+2,100)
设置数字自定义格式：0"元/工龄年“
设置数字自定义格式：“北京新东方”@ （输入内容后自带“北京新东方前缀”）
数字自定义格式：0.0%;[红色](0.0%);[蓝色]0.0
设置数字自定义格式：00000
规范日期： =TEXT(F7,"#-00-00")；SUBSTITUTE:可以将日期中的点改为-(替换旧为新)。 #：只显示有效数字而不显示无意义的零。
规范时间： =TEXT(G9,"00!:00!:00") “！”类似于转义符：例如显示“"”。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来“"”。要想显示出来，须在前加入“！”
数字格式最多可包含四个代码部分，各个部分用分号分隔。这些代码部分按先后顺序定义正数、负数、零值和文本的格式。　规则：<正数>;<负数>;<零>;<文本>
=INT(8.9) 将 8.9 向下舍入到最接近的整数 8
取消大区域的单引号：1. 格式刷一个空单元格 2.格式刷覆盖到这个区域即可
数据-删除重复值
数据-分列，注意分隔符
数据整理提取网页歌曲：分列、转置、在word中消除数字（查找替换-任意数字）、消除空格（用TRIM函数，去除文本的空格）
数据-合并计算，可以选择不同的表单，注意标签位置，标签顺序不同的话要选择标签位置
批量复制照片：="<table><imgsrc=""E:\photo\"&A2&".JPG""width=""188""height=""217"">"
手动排序：默认的都是按列排序，按行排序需要手动设置手动排序，排序-其他排序-手动排序-选中要修改的单元格，鼠标变成四个箭头后下拉，完成手动排序
移动或复制工作表：工作表-右键-移动或复制-可以数据建立到新工作表或者新建的工作簿-建立副本（建立副本表示复制，否则就是剪切）
快速复制工作表：按住Ctrl键鼠标往右拖工作表。
数据清洗：条件格式-筛选内容按特定格式呈现-排序（将要选的内容呈现在最前面）-放到word进行数据的查找替换分割（eg：将|替换为段落标记，就可以将数据按段落分开了）

三、常用公式

一列变多列，多列变一列：利用单元格地址引用
利用错误值计数：输入公式：=COUNT(0/(K3:K15>=1000))，CTRL+shift+enter结束公式输入。当(K3:K15>=1000)为FALSE，0除0就不是数字，就不会被计数
排错：公式-追踪引用单元格、公式检查、追踪错误，可以检查公式等的错误原因及源头
数据验证：可以设置单元格数据的取值范围和其他规则等、并图释不符合规则的数据
公式-公式求值：可以逐步理解公式函数的实现过程（F9可以单独选择解释）
文本链接符&：A1是张，B1是三，=A1&B1结果为张三，利用文本链接&可以将数据合并到一个单元格
条件求和：求迟到次数，=COUNTIF(B2:K2,"b")，b表示迟到，~countif（求和范围，标准）
某月的最后一天：=EOMONTH(B3,2)，返回从B3日期开始2个月后的当月的最后一天的日期
隐藏几位手机号：=REPLACE(B2,8,3,"***")

手机号 隐藏8-10位

18996471864 1899647***4
生成间断序号：=IF(C2="","",COUNTA($C$2:C2))
图片链接：hyperlink（图片路径，友好名称）；选中图片所在文件夹，按住shift，右击，选择“复制为路径”命令。
多条件求和：=COUNTIFS(E2:E10,">90",F2:F10,">90") ；=SUMPRODUCT((B2:B10="销售一部")*(C2:C10>950))， '=SUMPRODUCT((区域1=条件1)*(区域2=条件2)* 求和区域)。
计算数字出现的次数：=LEN(A2)-LEN(SUBSTITUTE(A2,"9",""))，计算单元格内容9出现的次数
if函数成绩判断：=IF(M3<60,"不及格",IF(M3<=70,"一般",IF(M3<=85,"良好","优秀")))
countif：=COUNTIFS(P2:P122,">="&R2,P2:P122,"<="&S2)，地址要用文本连接符，不然在引号里面会当成文本，只显示r2,s2
*代表任意字符，例如将0001-小花替换为小花，直接用“*-”查找替换就可以完成
自动求和：选中黄色区域加一列空区域加一行空区域，可以自动求和整行或整列的合计数
对多个工作表的同一个单元求和：=sum('1月:12月'!A1)
取消合并单元格：选中相应数据，设置单元格格式-对齐-合并单元格（取消√）
快速向下填充：定位空单元格，=A1,Ctrl+enter（相对应用）
跨列居中
删除空白行：筛选出空白行，定位可见单元格，右键-删除整行
快速将文本格式修改为数字格式：数据-分列-完成（最后一步有默认为常规格式（数字格式）所以直接点完成）
取小数位（四舍五入）：=round（）
求两个日期之间的工作日数：=networkdays（）
求下月1号的日期：=eomonth（date，0）+1
求下周一的日期：用power bi添加列：星期结束值，后+1
快捷键：隐藏列：Ctrl+0（取消隐藏：Ctrl+Shift+0），隐藏行：Ctrl+9（取消隐藏：Ctrl+Shirt+9）