Excel 常用函数学习总结

使用Excel进行数据分析的9个常用函数学习总结
老师指路->简书文章：https://www.jianshu.com/p/68af835de06a
B站视频：https://www.bilibili.com/video/BV1L7411f7cc
王佩丰Excel实战课1800分钟：https://study.163.com/course/introduction/670032.htm?inLoc=ss_ssjg_tjlb_%E7%8E%8B%E4%BD%A9%E4%B8%B0

一、常见的4个页面操作

冻结窗口：视图-冻结窗格-冻结首行
冻结前两列标题：拆分-线-拉到目标位置-冻结窗口

筛选：选中首行-数据-自动筛选-（出现下拉箭头-可以筛选）

选中整行：选中该行首单元格-ctrl+shift+右箭头
选中整列：选中该列首单元格-ctrl+shift+下箭头
选中整张表：选中首行-ctrl+shift+下箭头

去重：选中目前区域-数据-删除重复项（查看共有多少家门店）

排序：选中单目标列-开始-排序（升序、降序）
多目标列：开始-排序-自定义排序-添加条件

不显示科学计数法：右键-设置单元格格式-改成文本

二、vlookup函数

在使用Excel时，有时需要从列表中根据特征数据查找返回相应的数据记录，此时要用到搜索函数，vlookup是使用频率较高函数之一
=vlookup（lookup_value,table_array,col_index_num,[range_lookup]）
=vlookup（H3,基础表！A:H,3,0)
H3:门店号所在位置（H3可通过点击选中，函数中自动填充）
基础表！A:H：表格的查找范围，（选中表格范围，函数自动填充）
3：查找内容所在列
0：精确查找，1模糊查找

第一行，搜索完成后，选中4个字段内容，出现黑色十字架，双击（或下拉）。此时表格填满

clipboard.png

三、if函数

使用if函数判断，判断业绩，入会，业绩入会完成情况
当达成目标时显示已完成，未完成时，显示目标差额

if(条件判断，结果为真返回值，结果为假返回值)

if1.png

if2.png

if3.png

四、countifs、sumifs函数

使用countifs计算门店数量，总业绩、入会均已达成，总业绩达成，入会达成
使用IF函数计算差额字段；使用sumifs函数计算业绩实际完成，业绩目标

计数
countifs（查询范围，计数目标or条件）
=COUNTIFS(基础表!B:B,A6)

countifs1.png

countifs2.png

countifs3.png

计算总值：
sumifs（求和区域，条件区域，条件1）
=SUMIFS(基础表!D:D,基础表!B:B,A6)

sumifs1.png

sumifs2.png

sumifs3.png

五、index+match

完成vlookup的功能，vlookup默认是以最左边字段为基准，例如门店
=MATCH(J4,C:C,0)

match1.png

=INDEX(区域，行号)

index1.png

index2.png

六、字符串拼接

运用字符串拼接，给sqlserver数据库中给表字段加注释
将双引号替换为单引号，可在数据库中使用

EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'注释内容', @level0type = N'SCHEMA',
    @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'表名', @level2type = N'COLUMN',
    @level2name = N'字段名'

string1.png

填写公式为：

="EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'"&H3&"注释内容', @level0type = N'SCHEMA',
    @level0name = N'ods', @level1type = N'TABLE',
    @level1name = N'"&F3&"表名', @level2type = N'COLUMN',
    @level2name = N'"&G3&"字段名'
 go
 "

string2.png

#复制：替换,双引号全部替换为单引号
'EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'订单主键注释内容', @level0type = N'SCHEMA',
    @level0name = N'ods', @level1type = N'TABLE',
    @level1name = N'ods_sales_orders表名', @level2type = N'COLUMN',
    @level2name = N'sales_order_key字段名'
 go
 '

七、数据透视表

源表为基础表，运用数据透视功能实现如下操作
源表->数据->数据透视：选择行、值（默认求和方式）

table.png

八、Ctrl+e

先复制一行。然后选中这列，Ctrl+e 会自动填充整列内容

Ctrle.png

九、left mid right

参考链接：https://jingyan.baidu.com/article/7f41ecec585feb593d095cc1.html

最后编辑于：2021.08.04 14:36:12