(秦路)七周成为数据分析师（第三周）—— Excel

Myth丶恋晨 2022-10-10 01:41 204阅读 0赞

### 文章目录 ###

*   *  1 文本清洗函数
     *  2 关联匹配类
     *   *  2.1 LOOKUP和VLOOKUP
         *  2.2 INDEX和MATCH
         *  2.3 ROW和COLUMN
         *  2.4 OFFSET
         *  2.5 HYPERLINK
     *  3 逻辑运算类
     *  4 计算统计类
     *  5 时间序列类
     *  6 Excel的常见技巧
     *  7 用Excel进行数据分析

数分工具的应用：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70]  
小白学习顺序  
![在这里插入图片描述][2021062516020042.png]

Excel 学习要点：

*  Excel保证新版本(2013+)
 *  培养好的数据表格习惯
 *  主动性搜索
 *  多练习

**函数：**  
![在这里插入图片描述][2021062516054477.png]

## 1 文本清洗函数 ##

字符串：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20210625161004166.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 3]

![在这里插入图片描述][20210625165224826.png]

![在这里插入图片描述][20210625165151921.png]  
![在这里插入图片描述][20210625165308894.png]  
![在这里插入图片描述][2021062516540468.png]

![在这里插入图片描述][2021062516551052.png]

拼接函数：concatenate(text,text)  
![在这里插入图片描述][20210625170506522.png]  
![在这里插入图片描述][20210628095037602.png]  
将text中的old\_text转换为new\_text。  
![在这里插入图片描述][20210628095641140.png]  
将值转换为时间格式

![在这里插入图片描述][20210628095744169.png]  
删除字符串前后多余的空格。

len() 计算字符串的长度。

## 2 关联匹配类 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 4]

### 2.1 LOOKUP和VLOOKUP ###

![在这里插入图片描述][20210628172333795.png]  
![在这里插入图片描述][20210628172156144.png]

`=VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)`  
`搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 5]

### 2.2 INDEX和MATCH ###

INDEX:在给定的单元格区域中，返回特定行列交叉处单元格的值或引用  
![在这里插入图片描述][20210628173016729.png]  
MATCH:返回符合特定值特定顺序的项在数组中的相对位置  
![在这里插入图片描述][20210628173402798.png]  
组合：返回选定值在表格中对应的另一个值  
INDEX(表格，MATCH(值，表格，0)，另一个值)

### 2.3 ROW和COLUMN ###

ROW():返回一个引用的行号  
COLUMN():返回一个引用的列号

### 2.4 OFFSET ###

![在这里插入图片描述][20210629103228188.png]  
![在这里插入图片描述][20210629103251199.png]

![在这里插入图片描述][20210629103851268.png]  
以1所在的S2为参考，偏移一行一列，大小为一个单元格，所以结果为4。负数也可以，向上向左偏移。

### 2.5 HYPERLINK ###

HYPERLINK：超链接，创建一个快捷方式或链接，以便打开一个存储在硬盘、网络服务器或Internet上的文档

## 3 逻辑运算类 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 7]  
if:判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。  
![在这里插入图片描述][20210629110110544.png]  
is可以用来查找是否是数字或者逻辑值等等，用来数据清洗。  
![在这里插入图片描述][20210629110149422.png]

## 4 计算统计类 ##

*  sum：求和
 *  sumproduct：两列先相乘，结果求和。
 *  count：计数，为空的不计算在内
 *  max/min/average：某个区域内的最大值/最小值/平均值
 *  rank：返回某数字在一列数字中相对于其他数值的大小排名;如果多个数值排名相同，则返回平均值排名
 *  rand:返回大于或等于0且小于1的平均分布随机数(依重新计算而变)
 *  randbetween：返回一个介于指定的数字之间的随机数  
    ![在这里插入图片描述][20210629111216306.png]  
    quatile：基于从0到1之间(不含0与1)的百分点值，返回一组数据的四分位点，即统计所选数据的几分位点数。  
    ![在这里插入图片描述][20210629111520261.png]  
    ![在这里插入图片描述][20210629111659450.png]
 *  stdev：计算基于给定的样本总体的标准偏差(忽略逻辑值及文本)  
    subtotal：返回一个数据列表或数据库的分类汇总  
    ![在这里插入图片描述][20210629112326964.png]
 *  int：将数值向下取整为最接近的整数
 *  round：按指定的位数对数值进行四舍五入  
    ![在这里插入图片描述][20210629112524198.png]
 *  countifs：统计—组给定条件所指定的单元格数

## 5 时间序列类 ##

Year：返回日期的年份值，一个1900-9999之间的数字。

Month：返回月份值，是一个1(一月)到12(十二月)之间的数字。

Day：返回一个月中的第几天的数值，介于1到31之间。

Weekday：返回代表一周中的第几天的数值，是一个1到7之间的整数。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 8]  
weeknum：返回—年中的周数

![在这里插入图片描述][2021070514082055.png]

Now：返回日期时间格式的当前日期和时间。  
![在这里插入图片描述][20210705140033321.png]  
Today：返回日期格式的的当前日期。  
![在这里插入图片描述][20210705141023470.png]

Date：返回在Microsoft Excel日期时间代码中代表日期的数字，将分开的年月日组合起来  
![在这里插入图片描述][20210705140912870.png]  
Days：返回两个日期之间的天数。  
![在这里插入图片描述][20210705141234379.png]

## 6 Excel的常见技巧 ##

快捷键：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 9]  
定义名称：将一列数据定义为一个名称，通过调用这个名称就可以对整列进行操作  
avg（avg）就是对整列求均值  
![在这里插入图片描述][20210705143150169.png]  
数据 → \\rightarrow →删除重复值 → \\rightarrow →就可以知道某一列非重复的值有多少，也就是分类

数据 → \\rightarrow →数据验证 → \\rightarrow →验证条件选择序列，就可以实现在下拉菜单中选择  
![在这里插入图片描述][20210705143802106.png]  
迷你图：点击需要插入表格的单元格，插入 → \\rightarrow →折线图  
![在这里插入图片描述][20210705144544121.png]

## 7 用Excel进行数据分析 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 10]

1.  点评数最高的饭店  
    直接对点评数进行排序  
    或者使用max()找到最大值，使用match(max(),区域,0)找到最大值所在位置，然后用index(区域,match(max(),区域,0),1)找到匹配的店铺名。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 11]  
2. 哪个城市人均口味最好  
建立数据透视表，口味设为均值 ，然后对均值进行排序  
![在这里插入图片描述][20210705154048547.png]  
排序后的结果：  
![在这里插入图片描述][20210705154701876.png]

1.  哪个类型的餐饮评价最好  
    ![在这里插入图片描述][20210705155026268.png]
2.  类型为川菜的店中，有多少个带「辣」字，又有多少个带「麻」字?  
    ![在这里插入图片描述][20210705160349879.png]  
    筛选带‘麻’、‘辣’的字段，然后加入筛选器，空值不选，类型选择 川菜，最后看下面的计数。  
    麻  
    ![在这里插入图片描述][20210705160316800.png]  
    辣  
    ![在这里插入图片描述][20210705160524849.png]  
    减去首行的一行，最终结果减1。
3.  口味、环境、服务，三个评价都在8.0以上的饭店有几家?它们在哪个城市的占比最多?  
    在筛选器里选择 → \\rightarrow →数字筛选 → \\rightarrow →大于  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 12]  
    三个都筛选完，查看计数  
    ![在这里插入图片描述][2021070516091927.png]  
    然后使用数据透视表，查看城市的计数  
    ![在这里插入图片描述][20210705162330678.png]  
    排序  
    ![在这里插入图片描述][20210705162344365.png]
4.  上海地区中，各个类型饭店服务前五名?  
    使用筛选器，首先选择城市为上海，然后对类型和服务进行排序  
    ![在这里插入图片描述][2021070516330631.png]  
    使用if语句设置每个类型的店铺有几家  
    ![在这里插入图片描述][20210705163407102.png]  
    ![在这里插入图片描述][20210705163424266.png]  
    再使用筛选器，选择数量大于5的店铺
5.  没有评价的饭店有几家?  
    使用count()函数，count函数不计算空值，然后总数减去count()的值。
6.  将人均价格划分成0-50 ,50-100,100-150，150- 200，200+这几个档次，各个城市分别有几家?其中占比又是多少?  
    将人均除以50再向上取整，然后使用数据透视表
7.  将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的(开放题).  
    对口味、环境、服务加权求均值，人均求LOG()，评分求LOG来削弱影响，然后对三个结果进行加权，最后归一化排名，取分数最高的十家。

参考链接：[【秦路】七周成为数据分析师《第三周：Excel》][Excel]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70]: /images/20221005/c198947b4a254760aa392020b39915d8.png
[2021062516020042.png]: /images/20221005/4efd0008f8f54be1a641112d9306d7b9.png
[2021062516054477.png]: /images/20221005/73109781511448d892e782a2ca0007f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 1]: /images/20221005/263a64756410430c946be1202632f5f9.png
[20210625161004166.png]: /images/20221005/c7882220cb3a47d8aef6c702aef214d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 2]: /images/20221005/4d306b163cfe444e9bd6be3e1f883360.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 3]: /images/20221005/66152166e3604231b94cb24279584b09.png
[20210625165224826.png]: /images/20221005/424cd2bc22bd4b3495b6f37aaa4030fb.png
[20210625165151921.png]: /images/20221005/7da9eb3c9bda49468e150d7529d059a8.png
[20210625165308894.png]: /images/20221005/f59c341f23f04631b377b97f87bab315.png
[2021062516540468.png]: /images/20221005/55ae2b2dfe5c4bcaa8489b637d82b2e7.png
[2021062516551052.png]: /images/20221005/45a28b18be994ab89fefcd2f98050c12.png
[20210625170506522.png]: /images/20221005/5740add7c141457e928d5140ffb4767d.png
[20210628095037602.png]: /images/20221005/fe7f727fd110492ba902920f620cdb12.png
[20210628095641140.png]: /images/20221005/0608e816a5e74b82a50b6ba84fe4d718.png
[20210628095744169.png]: /images/20221005/6b08f0fd3f5c45f9b366200fa38b1b46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 4]: /images/20221005/1b3f2f76c4fe405bb5db9864a011e283.png
[20210628172333795.png]: /images/20221005/cc1453cf695140079a9cdd97816a1921.png
[20210628172156144.png]: /images/20221005/435b044e034e4d7097e4b41be222b275.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 5]: /images/20221005/25f1d7dd05334b0eac19f6e0f2a663a2.png
[20210628173016729.png]: /images/20221005/7ecdc42b9c69449e9a7f83389144f779.png
[20210628173402798.png]: /images/20221005/460c432fda804676803fdb8e0b7a39f3.png
[20210629103228188.png]: /images/20221005/db1a26b8f1c7460fa43a7dab9599c0da.png
[20210629103251199.png]: /images/20221005/49bd2ed151ce4b4e9bafd9f072f96f0f.png
[20210629103851268.png]: /images/20221005/eb6a7fd5dbd64ce8ad6ed75c69c3ef5a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 6]: /images/20221005/497b37fb2ff84aad9a7b99a6bd692bc8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 7]: /images/20221005/3935f7414cbe4179bb98b361b4b79428.png
[20210629110110544.png]: /images/20221005/76c3f66815224b508dbe07e5a83ebfaf.png
[20210629110149422.png]: /images/20221005/5e1578753d134928b6887d603e336cd2.png
[20210629111216306.png]: /images/20221005/e484272c50144bfe82f373e8904ac2cf.png
[20210629111520261.png]: /images/20221005/4f674336316e44d78d245026386d0cfe.png
[20210629111659450.png]: /images/20221005/d39a6a6aafe5446ba10a9057915c4fae.png
[20210629112326964.png]: /images/20221005/e58884fe79e1421498d9e378d466ae40.png
[20210629112524198.png]: /images/20221005/81350608345042c08fab19496e5266e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 8]: /images/20221005/b440c854712543feb3bda30b6bfc3d4d.png
[2021070514082055.png]: /images/20221005/218f08385f3d4d30877eeb1ec5ddc680.png
[20210705140033321.png]: /images/20221005/3e20b81495be4ef48ebc88607b7cc79a.png
[20210705141023470.png]: /images/20221005/cb7f3214e681442b9669825938479b2a.png
[20210705140912870.png]: /images/20221005/a56b36cd5e4e46aa91fe54d7d2034d37.png
[20210705141234379.png]: /images/20221005/858ebc6fc26e4262b7ad53cc831415c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 9]: /images/20221005/3ca598f17e334e32be3c575e8aa73aaa.png
[20210705143150169.png]: /images/20221005/1744442c9a0240e29fcf29b108e70c9d.png
[20210705143802106.png]: /images/20221005/b876d1adf31c4d42bebc2d51fa69b798.png
[20210705144544121.png]: /images/20221005/2f6639e949234bc7a60c46fe0c217c0c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 10]: /images/20221005/cc674bbcc11745259be60fa22e84694d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 11]: /images/20221005/5f7b2dacb22b47acb38c8ac25ee25b90.png
[20210705154048547.png]: /images/20221005/64f86e4344f4431dbe325ba4bdf7d31f.png
[20210705154701876.png]: /images/20221005/d579db22c0294b188d82a4e1c5f5707a.png
[20210705155026268.png]: /images/20221005/9f97feca9c0446d0888fb920816ef948.png
[20210705160349879.png]: /images/20221005/a5638ef399b54d8380340b0c499f7208.png
[20210705160316800.png]: /images/20221005/a83ec40f8e8749a982474661c26fc4cb.png
[20210705160524849.png]: /images/20221005/ff4e8cf42756405b91acec3a5b131586.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70 12]: /images/20221005/cf3e9890244349098b1e3908f12be45e.png
[2021070516091927.png]: /images/20221005/cde253479d2e487e8b3e9691bc67bea7.png
[20210705162330678.png]: /images/20221005/6726ef46b62f4c02a983900040576fac.png
[20210705162344365.png]: /images/20221005/63fbd960581f49d4bf5468870b8d6fa5.png
[2021070516330631.png]: /images/20221005/fda37c1b34e64267bf9fbab982164659.png
[20210705163407102.png]: /images/20221005/eb10ccbd23174fff9287436f9206ec77.png
[20210705163424266.png]: /images/20221005/9a0af4e7b9ae438a82773ee2d928ec26.png
[Excel]: https://www.bilibili.com/video/BV1cE411P7an