数据规范

一维表和二维表概念

一维表

二维表

一维表的判断依据就是看其列的内容，每一列是否是一个独立的变量。

数据表的规范要求

数据导入

早期的导入方式

文件-选项-数据-勾选导入方式

导入文本

数据-获取和转换数据-获取数据-传统向导-从文本，选择要导入的文本确定分隔符号。

导入excel工作簿

数据-获取和转换数据-获取数据-现有连接-浏览更多，选择要导入的工作簿。

基于导入的excel工作簿编写sql

右键表格-编辑查询-输入sql语句，表名要加英文中括号，逗号也是英文逗号。

基于PowerQuery的导入方式

导入文本

数据-获取和转换数据-获取数据-来自文件-从文本-转换数据-关闭并上载至-表-现有工作表

导入excel工作簿

数据-获取和转换数据-获取数据-来自文件-从excel工作簿-转换数据-关闭并上载至-表-现有工作表

数据清洗

数据排序

定义

数据排序是指按一定顺序对数据进行排列，以便研究者能够通过浏览数据发现一些明显的特征、规律，找到解决问题的线索。除此之外，排序还有助于对数据进行检查、纠错，以及为重新归类或分组提供方便。

其他和第一篇笔记相同。

重复数据处理

定义

重复数据处理，就是将数据中重复、多余的部分删除，以保证数据的唯一性，也称数据去重。

菜单

数据-数据工具-删除重复值-勾选所有字段

数据透视表

插入数据透视表，将所有字段拖到“行”，设计-布局-分类汇总-不显示分类汇总，总计-对行和列禁用，报表布局-以表格形式显示，数据透视表分析-显示-+/-按钮，就可以得到不重复的表格

高级筛选

数据-排序和筛选-高级-勾选选择不重复的记录

函数

UNIQUE（去重的表格），“=UNIQUE(A1:C9)”

PowerQuery

数据-获取和转换数据-来自表格或区域-看数据类型是否需要修改-主页-删除行-删除重复值-关闭并上载-表-现有工作表

空格数据处理

概念

空格数据，是指字符型数据的前中后存在空格。由于系统BUG或人为原因，空格数据在日常工作中经常出现。

查找-替换

开始-编辑-查找和选择-替换，查找内容输入空格，替换内容不输入任何内容，全部替换

函数

Excel中有时候我们并不希望去除所有空格。例如“Jack Ma”这个英文名中间的空格，是用来分隔姓氏跟名字的，属于正常的用法，是不能去除的。这个时候可以使用TRIM函数只去除字符串前后的空格。如果是文字中间有多个空格，会留下一个空格。   

        TRIM函数语法：

        TRIM(text)

        text:需要删除前后空格的字符串或单元格引用。

         SUBSTITUTE函数语法：

         SUBSTITUTE(text,old_text,new_text,[instance_num])

         text:需要处理的目标字符串或单元格引用；

         old_text:需要替换的旧字符串；

         new_text:指定将旧字符串替换成什么样的新字符串；

         [instance_num]:指定替换第几次出现的旧字符串，如果省略，则目标字符串中所有与old_text参数相同的部分都将被替换。

“=SUBSTITUTE([@姓名]," ","")”

PowerQuery

转换-文本列-格式-修整，字符前后的空格去除，字符中间的空格无法去除，再用替换值-替换值，要查找的值输入空格，替换为不输入任何内容，去除中间的空格

缺失数据处理

概念

缺失数据也被称为缺失值，是指由于系统BUG或人为等原因，造成数据表中某些字段的值缺少的情况。缺失值常见的表现形式有"NULL"和空值两种。

菜单

取消合并单元格-查找和选择-定位条件-空值-反白单元格输入公式“=A1”，按住Ctrl Enter批量填充。

PowerQuery

转换-填充-向下

数据抽取

字段拆分

概念

字段拆分，就是指抽取某一字段中的部分信息，形成新字段。

分列

数据-分列-固定宽度-手动划分-不需要的列选择“不导入此列”-需要的列设置对应的格式

函数

函数语法

MID(text,start_num,num_chars)

text:字符串

start_num:抽取字符的开始位置

num_chars:要抽取字符的个数

TEXT(value,format_text)

value:value是要处理的内容

format_text:用于指定的格式代码

输入公式：=TEXT(MID([@身份证号码],7,8),"00-00-00")

PowerQuery

添加列-从文本-提取，PowerQuery的索引是从0开始。

记录抽取

概念

记录抽取，是指根据一定的条件抽取相应的数据记录，这是数据处理中非常常见的操作之一。

常用的记录抽取方式有：

1- 指定值抽取；

2- 关键词抽取；

3- 数据范围抽取；

4- 时间范围抽取；

5- 组合条件抽取。

指定值抽取

高级筛选：先写好条件区域，点击高级筛选，再选择对应的区域。

PowerQuery:出版社筛选按钮-文本筛选器-等于。

关键词抽取

高级筛选

PowerQuery：文本筛选器-包含

数据范围抽取

高级筛选

PowerQuery：数字筛选器-小于

日期范围抽取

高级筛选

PowerQuery：日期筛选器-晚于

组合条件抽取

高级筛选

PowerQuery：日期筛选器-文件筛选器-数字筛选器

数据合并

字段合并和分组合并

字段合并

把年和月合并，格式为年-月

方法一：=[@年]&"-"&[@月]

方法二：=CONCAT([@年],"-",[@月])

方法三：=TEXTJOIN("-",TRUE,[@年],[@月])

分组合并

函数

第一步：=UNIQUE(名单[部门])

第二步：=TEXTJOIN(",",TRUE,FILTER(名单[姓名],名单[部门]=E7))

PowerQuery

见上一篇

字段匹配

概念

字段匹配，也称横向合并，是将原始数据表中没有但其他数据表（维表）中有的字段，通过共同的关键字段进行一一对应并匹配至原数据表中，从而达到获取新字段的目的。

      注意点：用于两表匹配的共同关键字段，数据类型要一致！

单条件匹配

单条件匹配的定义

      单条件匹配，就是在一个共同的关键字段相等的情况下进行两表连接匹配。

      注意点：用于两表匹配的共同关键字段，数据类型要一致！！！

      lookup_value:要查找的值（根据什么找）；

      table_array:要查找的区域（在哪里找），lookup_value的值必须处于table_array的首列；

      col_index_num:返回数据在查找区域的第几列（找到第几列）；

      range_lookup:精确匹配（FALSE或0）、模糊匹配（TRUE、非0值或省略）

函数

=VLOOKUP([@用户编号],用户表!A:C,3,0)

=VLOOKUP([@用户编号]*1,用户表!A:C,3,0) 查找值是文本“*1”转换为数字

PowerQuery

上一篇横向合并

多条件匹配

函数

=G2&"@"&H2，生成唯一一个共同的关键段

=VLOOKUP(F2,A:D,4,0)

PowerQuery

合并查询-将查询合并为新查询-按住Ctrl选中两个字段

记录合并

函数

=VSTACK(销售业绩_1月[#全部],销售业绩_2月,销售业绩_3月)

“销售业绩_1月”要整个表选中，“销售业绩_2月”和“销售业绩_3月”不用选中标题行。

PowerQuery

需要先修改表的数据类型，去掉“更改的类型”，再将姓名和月份修成文本，销量修改成数字，点追加查询-将查询合并为新查询-选中三个表。

数据转换

数据类型转换

文本转数值

分列

函数：=A1*1，清楚格式

PowerQuery:直接修改数据类型

数值转文本

分列，选文本

函数：=A1&“”，清除格式

PowerQuery:直接修改数据类型

数值转日期

分列，选日期

函数：=TEXT(A3,"00-00-00")*1

PowerQuery:先修改成文本类型在修改成日期类型

数据形式转换

二维表转一维表

菜单

ALT+D再按P-多重合并计算区域-创建单页字段-选定表格区域-现有工作表，生成数据透视表，双击对角线单元格292020，弹出详细信息。

PowerQuery

逆透视

数据计算

简单计算

简单计算，是指通过对已有字段进行加、减、乘、除等运算，得到新字段的操作。Excel中的加减乘除分别对应+、-、*、/。

=[@折扣价]*0.5

日期计算

=DATEDIF([@出生日期],"2023/12/31","Y")

数据标准化

数据标准化的概念

数据标准化，是指将数据按比例缩放，使之落到一个特定区间。数据标准化就是为了消除量纲(单位)的影响，方便进行比较分析。常用的数据标准化方法有0-1标准化和Z标准化。

0-1标准化的概念

0-1标准化，也称离差标准化，它是对原始数据进行线性变换，使结果落到[0,1]区间。0-1标准化还有个好处，就是很方便做十分制、百分制的换算，只需乘上10或100即可，其他分制同理。

常用0-1标准化公式：X*=(X-min)/(max-min)

=([@成绩]-MIN([成绩]))/(MAX([成绩])-MIN([成绩]))

加权求和

SUMPRODUCT(array1,[array2],[array3],…)    先求积再求和

array1:必需。其相应元素要进行相乘并求和的第一个参数。

array2,array3,……:可选。第2到255个数组参数，其相应元素需要进行相乘并求和。

=SUMPRODUCT(B2:F2,$K$2:$O$2)

数据分组

概念

数据分组，是指根据分析的目的，将某个数值型字段划分为不同的区间(等距或不等距)，并生成一个分组字段，以便进行下一步数据处理或分析工作。

IF&IFS

=IF(A3>=90,"A",
      IF(A3>=80,"B",
          IF(A3>=70,"C",
              IF(A3>=60,"D","E"))))

=IFS(A3>=90,"A",A3>=80,"B",A3>=70,"C",A3>=60,"D",A3<60,"E")

VLOOKUP

=VLOOKUP(A3,G:I,3,1) 模糊匹配