EXCEL中数据分析涉及的一些操作

还是b站up：【课程2.0】Excel基础操作|大厂周报制作|常用函数_哔哩哔哩_bilibili

表格组成：

工作簿-工作表-行/列-单元格

每一列的名称：表头（在sql中 表头+后面数据->字段）

功能区组成：…略

      

二、基础操作

—源数据备份，可以将一个工作簿的数据在操作进行右键备份并隐藏，防止后续源数据没保存

—导出pdf

—数据透视表：选中区域建立 对选中区域的统计，这个表不太好看不方便展示，所以有时需要引用数据表的数值后续继续操作

三、常用函数

函数可以在不同的工作簿中进行操作 e.g. =SUM(‘sheet2’!D:D)

1、求和部分

—sum：求和（可以通过逗号对多个区域进行求和）

—sumif：单条件求和（条件范围，条件，求和范围）条件不需要手动再输一遍，可以和表格内容匹配，当然手动输入也可，就用双引号引起来就好

—sumifs：多条件求和（求和范围，第一个条件范围，第一个条件，第二个条件范围，第二个条件） 并且在条件中 ，不是相同比较时，比方用>=x   书写方式应该为: “>=”&x

e.g. =sumifs(D:D,A:A,”>=”&1,A:A,”<=”&30)

—subtotal：根据筛选求和   subtotal(选择函数的序号, 带操作的数列, ?)涉及函数有求和，还有其他求平分之类的

       与sum的区别是 可以根据你源数据的筛选来变化代 自动求为筛选出的这部分值

2、判断部分

—if：逻辑：略，if可以嵌套 if(1>2,if(2>3,”都对”,”第二个不对”), if(2>3,”第一个不对”,”都不对”))

3、查询部分

—VLOOKUP：连接匹配数据 VLOOKUP(要找的条件，找的范围（包括条件，条件要在第一列+返回值），找的返回值位于范围中的第几列，精确0or近似1)

       Vlookup的匹配很精准，要是一个格是a，另外一个是a ，多了一个空格都不行，这个时候需要用到通配符 e.g. Vlookup(B15&”*”,C:D,2,0) 但是vlookup此时会查出 a , ab, abc…各种的都算是匹配成功，然后输出查出来成功匹配的第一个值

4、查询升级match，index

—match：查找数值在区域中的位置，只能是判断单行或者单列的中的位置，存在合并单元格的情况下是不能正常使用的

—index：根据区域的位置返回数值，要是指定位置填0就是返回整行or整列。

       e.g. =index(B:F,0,1)就是返回B列

—match、index：一起使用：自动根据列名查找数据，不过match的还必须是唯一字段，不然只会给出match成功的第一个数据位置

Match, index相较于vlookup的区别在于：前面两者的组合，可以使得一列成功的情况下，使得后面几列甚者增加指标都可以直接自动化的找到，不需要再像vlookup一个个重改范围。（但似乎速度不怎么样）

       e.g. =INDEX('拌客源数据1-8月'!$A:$X,MATCH($B131,'拌客源数据1-8月'!$I:$I,0),MATCH(D$130,'拌客源数据1-8月'!$1:$1,0))

—sumifs、match、index高级用法：自动根据列名的数据和条件进行求和

       这种相对于前面那个的区别在于，在数据中有一个例子（一个商家）出现多次时，前面那个直接找match只会找到数据中这个商家第一个出现的值，对于这个商家的基础属性的查找显示（名称、代号，id）这些时数据中商家出现几次不影响，但是对于算出商家总的赚的金额、总的进店人数这些时，自然不能只用第一次出现的金额来计算，需要算出总的，这个时候就还是得用sumifs来看，此时的match主要作用就是让行的名称（指标属性）找出其在数据的列位置了（还是方便多加几个指标的时候直接拉），所有的这家商家出现情况还是得靠sumifs

       e.g. =SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(I$130,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B131)

此处就是绝对引用以及相对引用这些需要注意一下

5、日期

—date, month, year, day 形成日期

       e.g. =date(1999,0,1): 1998-12-01   =date(1999,2,30): 1999-03-02     year(2020-07-01)=2020

特别的操作：

1. 每一个工作簿可以右键复制一个副本，并隐藏，防止表删掉后数据丢失（源数据的备份）
2. 要是在计算公式想选择一列，可以直接在最上面，或者 e.g. =SUM(D:D)就是D列的所有数据求和
3. 在视图 新建窗口，可以打开两个相同excel，数据是不变的，但可以方便操作
4. 在视图 冻结窗格，可以将首行首列 or 你选中的格子的左上部分冻结，在往下往右翻的时候可以固定住 方面对着看 冻结B2就可以冻结首行和首列
5. Excel日期最好是不要存储为excel日期，用string

快捷键：

Control+Shift+L：进入筛选模式，两次取消

Win+方向键左右 可以分屏

F4：可以将绝对引用变成相对引用

       e.g. =sum(D:D)--à(F4)   =sum($D:$D) 多按几次就是几种锁定形态

数据表相关：