Excel自学三部曲_Part3：Excel工作场景实战（二）

文章目录

  • 二、基础概念、表格结构与常用函数
    • 1. 业务背景、字段含义
    • 2. 筛选、排序、冻结窗格
    • 3. 状态栏数据提示、调整数据显示格式
    • 4. 公式、引用、溢出
    • 5. 连接和提取函数、时间函数、IF和IFS函数、SUMIF和SUMIFS函数
      • （1）每个业务组的成交额有多少
      • （2）各个城市在8月上中下旬的逾期金额，以及逾期的环比变化

二、基础概念、表格结构与常用函数

1. 业务背景、字段含义

  • 打开表格首先看到了5列数据，分别是日期、业务组、成交额、应收利息和逾期金额，那么在对数据进行处理前，肯定要先知道数据的业务背景 ，并且熟悉每个字段的含义
    在这里插入图片描述

  • 可以看到，这份数据包含“应收利息、逾期金额”，看到这些可以想到是金融行业，而这份数据的背景很像各大银行在线下地推信用卡，不同的是：

    • 业务主体：某互联网金融公司（类似支付宝）
    • 业务模式：各城市线下推广
    • 推广产品：小额现金贷（类似借呗）

  • 可以想象为：支付宝组织了线下推广团队到各个城市，向一些不太会用手机的人群介绍借呗，然后帮助用户完成注册和贷款额度的申请。

  • 因此，字段含义 如下：

    • 日期：数据归属于哪一天
    • 业务组：业务小组名称，及所属的组织架构
    • 成交额：业务组的所有用户在当天贷款的金额总和
    • 应收利息：用户所贷金额在未来应该收到的利息总和
    • 逾期金额：用户到了时间没还的钱

  • 那么明确了字段的含义后，下一步就要深入了解数据了，看一看每一列有哪些数值，到底有多少行等等，方便我们进行接下来的处理和分析。

2. 筛选、排序、冻结窗格

  • 先来看每一列的具体数值和行数，从上往下滑，一个个看过去肯定不行，效率太低了，这里可以点击任意一个有数据的单元格，然后点击“开始-排序和筛选-筛选 ”（快捷键：Ctrl+Shift+L），可以看到每一列的右侧都出现了按钮 。

  • 点击“日期”右侧的按钮就可以看到这一列到底包含了哪些日期，并且日期、文本、数值型的数据各自支持不同的逻辑筛选，例如：日期可以筛选出某一天前的数据；文本可以筛序出特定开头的数据；而数值则支持大于、小于、等于等比较逻辑
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

  • 除了筛选，我们还可以在筛选模式下进行排序 ，只需要点击升降序即可，但是这样只能根据单一字段进行排序，如果我们想按多个字段进行排序，例如：先排业务组，再排日期，可以在点击数据区域后，点击“开始-排序和筛选-自定义排序”，设置主要关键字并添加条件，这样我们就能看到各个业务组在日期上连续的数据变化了
    在这里插入图片描述
    在这里插入图片描述

  • 但是下滑的时候列名会被一起滑上去，我们可以点击“视图-冻结窗格 -选择冻结模式-冻结首行”，这样就可以固定列名了
    在这里插入图片描述

3. 状态栏数据提示、调整数据显示格式

  • 这里我们已经大概查看了数据的特征，如果想要看数据的行数，点击任意一列上方的英文字母即可，选中整列，我们就可以在右下方状态栏看到所选中的列对应的平均值、计数和求和，左键单击数值可以直接复制，右键还可以查看最大值和最小值，并且勾选它们可以让它们和平均值一样默认显示出来；选中任意区域和多列也会进行计算。
  • 点击C列后可以看到计数是883，去掉首行数据共有882行，而总和为…，数据没有逗号看不清楚
    在这里插入图片描述
  • 我们可以看到表格上的数据默认有逗号，也就是千分号，但是右下角没有，如果右下角的数据能改成一样就好了，那么怎么改呢？
  • 点击“开始-数字-选择要显示的数据格式-千位分割符”，则右下角的数据跟我们选中的列统一了格式，这里千分号每三个数字一组，可以记一下读法：第一个逗号前面是千，第二个逗号前面是百万，第三个逗号前面是十亿，所以这里是9千7百万
    在这里插入图片描述

4. 公式、引用、溢出

  • 改完格式后，我已经可以通过筛选和选中列来快速查看一些数据了，但是，如果领导突然让我算一下：每人业务组的成交金额各自有多少？如果还是一个个筛选复制粘贴右下角的数值，时间来不及，此时就要用到excel的公式和函数了。
  • 先来说“公式”：单元格内可直接输入数值，输入等号后excel会对等号后的公式进行计算，公式内可以输入各种运算符、数值，也可以引用单元格
  • 引用，即同步所引用单元格中的数值变化；区域，两个及以上单元格可以组成区域；分别复制和引用一个区域，再改动原区域的数值。如果是复制粘贴后的数值，是不会跟随原有区域变动的；但是通过公式直接引用区域，就可以同步该区域数值的变化。
    在这里插入图片描述
  • 并且自学观察可以发现，引用区域有一个蓝色的框线，并且区域内只有一开始输入公式的单元格可以修改公式，其他单元格双击后默认为空，如果强行输入数值还会使之前输入的公式报错，这是为啥呢？
  • 这是这是EXCEL在2021版本刚支持的新功能，可以将单元格中计算的结果， 溢出显示的在周围的单元格上，接着点击报错的提示，查看报错的详情，里面显示的是“溢出区域不是空白区域”，原因就是因为原本会溢出显示数据的区域输入了数值，而一个单元格只能显示一个值，两个值互相冲突，导致原本溢出的值无法正常显示，所以报错了。此时只要删除输入的值就可以恢复正常了。
    在这里插入图片描述

5. 连接和提取函数、时间函数、IF和IFS函数、SUMIF和SUMIFS函数

（1）每个业务组的成交额有多少

  说回问题，既然老板要看 “每个业务组的成交额有多少”，那我们至少要给老板两列数据，一列是业务组的名称，一列是根据业务组名称计算的成交额；但是，现在的数据是每个业务组每天一行，要怎么变成每个业务组单独一行呢？像这样将多行数据汇总成一行，其实有个专业的名称概念，叫聚合。而excel中实现聚合的方式主要有两种：第一种是函数，第二种是数据透视

  • 业务组
    这里先用函数，我们需要每行只保留一个业务组，也就是要对业务组这一列进行去重。先输入列名“业务组”，光标会自动移动到下一个单元格，这里去重的话直接使用unique函数就好，现在已经搞定了业务组这一列
  • 成交额
    接下来就要对每个业务组所对应的成交额进行计算了，输入列名“成交额”，此时用sumif函数，它可以根据特定的条件对某个区域的数值进行求和，这里是根据业务组列找到所有和当前业务组（去重后）单元格数值相同的行，接着对这些行所对应的成交额进行求和。
    在这里插入图片描述
    在这里插入图片描述
  • 函数的一些用法：
    • 提高函数输入效率：直接按住crtl多选区域和单元格，就不用输英文逗号隔开区域和单元格了
    • 显示所有公式和函数的具体写法：crtl+`
    • 自动填充
      • 填充到边界：双击句柄
      • 自定义填充：手动拖拽
    • 填充原理
      • 向下：增大所有坐标的数字
      • 向上：减小数字
      • 向右：增大字母
      • 向左：减小字母
    • 锁定坐标
      • 同时锁定行列：在行列坐标前都加上$，也可选中坐标直接按F4
      • 例如：$D$5、Y$7、$A$1:$B$2
      • 分别锁定行列：分别在行列坐标前加$
      • 例如：$D5、Y$7、$A2:$D2
    • 引用方式
      • 相对引用：坐标前不加$，引用的单元格和区域会随拖拽移动
      • 绝对引用：坐标前加$，加$的坐标会被锁定，不会随拖拽移动
    • 填充数值
      • 直接拖拽就可以填充
      • 按住crtl可以切换填充的方式
      • 在填充结果的右下角，可以选择填充的方式
    • 移动选框
      • 向下：回车
      • 向上：Shift+回车
      • 向右：Tab
      • 向左：Shift+Tab
      • 方向键上下左右

（2）各个城市在8月上中下旬的逾期金额，以及逾期的环比变化

  此时，只看各业务组的成交金额还不够，领导说还想看 “各个城市在8月上中下旬的逾期金额，以及逾期的环比变化”，这又该怎么算呢？理清一下思路，把每一列的名称列出来“城市、8月上旬逾期金额、8月中旬逾期金额、8月下旬逾期金额、8月上旬逾期环比、8月中旬逾期环比、8月下旬逾期环比”，这样可以明确接下来要进行处理和分析的目的，接下来就挨个计算这几个字段

  • 城市
    首先是城市，城市现在包含在业务组这一列，需要想办法把它单独拆出来，观察发现“战区、城市、小组”之间是有减号的，像这种用来切分字段内容的符号一般称之为分隔符，除了用减号，还有逗号、短下划线等，而excel自带根据分隔符拆分字段的功能，选中“业务组”列，点击“数据-分列”，将其拆为“战区、城市、小组”三列，然后输入unique函数给城市去重即可
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    • 如果我们想要重新得到之前的业务组列该怎么做？其实只要合并原来的3列就好了，可以用连接函数
      • &符号
        • 直接连接要合并的数值和区域
        • 输入的数值要放在英文双引号中
        • 会溢出显示
      • CONCAT函数
        • 功能：提取区域中的数值，然后进行连接
        • 语法：CONCAT(text1,[text2],…)
        • 写法：直接按住ctrl多选需要连接的区域
        • 不会溢出显示，所有结果返回到一个单元格中
    • 数据提取函数
      • LEN函数
        • 功能：计算字符个数
        • 语法：LEN(text)
        • 写法：选择你要计算字符个数的区域
      • LEFT函数
        • 功能：从左往右截取字符
        • 语法：LEFT(text,[num_chars])
        • 写法：选择你要截取数值的区域，输入从左往右要截取到第几个字符
      • RIGHT函数
        • 功能：从右往左截取字符
        • 语法：RIGHT(text,[num_chars])
        • 写法：选择你要截取数值的区域，输入从右往左要截取到第几个字符
      • MID函数
        • 功能：从左往右指定起点，向后截取字符
        • 语法：MID(text, start_num, num_chars)
        • 写法：选择你要截取数值的区域，输入截取的起点，输入从左往右要截取到第几个字符

  • 8月上中下旬
      算完了“城市”，就要计算8月上中下旬的逾期金额了，要如何计算这个金额呢？首先，我们肯定要把哪些日期是上中下旬判断出来，接着只要根据城市和时间求和，应该就能算出对应的逾期金额了，那么如何判断上中下旬呢？一般情况下，上旬：1到10号、中旬：11到20号、下旬：21号及以后；根据上中下旬的定义，首先要把日期中的天取出来作为判断的逻辑，这里用day函数
    在这里插入图片描述

    • 时间函数
      • YEAR()、MONTH()、DAY()、 HOUR()、MINUTE()、SECOND()
        • 可以分别返回函数内时间在年、月、日、小时、分钟、秒单位上的数值
        • 写法：括号内直接输入日期时间即可
      • NOW函数
        • 功能：记录输入函数时的日期和时间
        • 语法：NOW()
        • 写法：直接输入函数
      • TODAY函数
        • 功能：记录当天的日期
        • 语法：TODAY()
        • 写法：直接输入函数
      • EOMONTH函数
        • 功能：求日期前后n个月的最后一天
        • 语法：EOMONTH(start_date,months)
        • 写法：输入日期，输入要求第几个月后的最后一天
        • 0代表当前月份，正数往后面的月份偏移，负数往前面的月份偏移
        • +1天，可求出n+1月后的第一天
      • 日期函数和数值相互转换
        • 日期：1到2958465的数字与1900年1月1日开始到9999年12月31日的每一天一 一对应
        • 时间：小数点后的数字，代表当天的24小时过去了百分之多少 在这里插入图片描述
      • 日期计算
        • 可以直接加减数值
        • 日期时间也可以相互加减得到数值
      • 输入分数
        • 直接输入分数会默认为日期
        • 输入的分数前要加上0和空格
        • 例如：03/2、06/5

  接下来需要思考下如何判断整个日期的天数，让它返回对应的上中下旬，逻辑上非常简单，那要怎么写呢？这里需要结合IF函数在这里插入图片描述

  • IF函数
    • 上中下旬的判断逻辑
      • 上旬：天数<=10
      • 中旬：11<=天数<=20
      • 下旬：天数>=21
    • IF函数
      • 功能：对单个条件进行逻辑判断，根据判断结果返回对应的数值
      • 语法： IF(logical test, value_if true, [value_if false]
      • 写法：判断条件，判断为真时返回的数值，判断为假时的数值
    • IF函数的判断条件
      • 只需要对文本数值加英文双引号，判断符号不用加
      • SUMIF函数中判断条件符号和文本都要加英文双引号
    • AND和OR函数
      • 功能：以且或逻辑连接多个判断条件
      • 语法：AND(logical1,[logical2], …)、 OR(logical1, [logical2], …)
      • 写法：逐个输入判断条件，用逗号隔开
    • IF函数嵌套
      • IF函数的数值参数项可以是另一个IF函数
      • 这样可以逐一判断多种情况
      • 例如：=IF(B2<=10"上旬”IF(AND(B2>=11,B2<=20)"中”“下旬”))
    • IF函数嵌套后的判断逻辑
      • 函数写法：=IF(条件1，数值1，IF(条件2，数值2，数值3))
      • 运行逻辑：
        • 先判断条件1
          • 条件1为真时：返回数值1
          • 条件1为假时：判断条件2
            • 条件2为真时：返回数值2
            • 条件2为假时：返回数值3

  其实回想起来也很简单，就是对天数判断一下，然后多条件用AND连接，多种情况稍微嵌套一下就好了；不过这种套娃感觉还是很麻烦，如果能让IF函数智能一点，直接根据每种情况返回对应的数值，而不是像现在这样嵌套下去逐个判断，那函数写起来是不是就比较简洁了，这就是IFS函数
在这里插入图片描述

  • IFS函数
    • IFS函数
      • 功能：对多个条件进行逻辑判断，根据判断结果返回对应的数值
      • 语法：=IFS([Something is Truel, Value if Truel,Something is True2,Value ifTrue2,Something is True3, Value if True3)
      • 写法：条件1，条件1为真时返回的数值，条件2，条件2为真时返回的数值…
    • 多情况判断的两种方法
      • IF嵌套逐层判|断
        • 函数写法：=IF(条件1，数值1，IF(条件2，数值2，数值3))
        • 运行逻辑
          • 先判断条件1
            • 条件1为真时：返回数值1
            • 条件1为假时：判断条件2
              • 条件2为真时：返回数值2
              • 条件2为假时：返回数值3
      • IFS按顺序逐一判断
        • 函数写法：=IFS(条件1，数值1，条件2，数值2，条件3，数值3)
        • 运行逻辑
          • 条件1为真时：返回数值1
          • 条件2为真时：返回数值2
          • 条件3为真时：返回数值3

  这里总结一下：IF函数可以在判断条件后，分别返回条件为真或假时的数值，但是一次只能判断一种情况，如果要判断多种情况就需要对函数进行嵌套；而IFS函数只能返回条件为真时对应的数值，但不需要嵌套就可以同时判断多种情况。因此，在单一条件判断时，IF函数只用写一次条件写起来更快，而在判断多种情况时，肯定是IFS函数写起来更加清晰，不至于套娃太多层直接把自已绕晕。

  • 8月上中下旬的逾期金额
      首先对每个城市在8月上旬的逾期金领进行求和，以北京市为例，其实就是对城市列等于北京，上中下旬列等于上旬的所有逾期金额单元格求和。之前已经学习了可以筛选判断单元格，然后进行求和的sumif函数，求出了各个业务组的成交额和应收利息；但是现在判断的条件有两个，用只能判断一组条件的sumif函数肯定不行，因此要用支持多条件的sumifs函数。
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    • SUMIFS函数
      • 功能：对满足多个条件的单元格求和
      • 语法：SUMIFS(sum_range, criteria_rangel,criterial, [criteria_range2, criteria2] …)
      • 写法：求和的区域，判断区域1，判断条件1，判断区域2，判断条件2…
    • SUMIF和SUMIFS函数对比
      • SUMIFS函数将求和区域提到了最前面，并且可以向后增加条件
      • 为了方便拓展增加条件，一般直接写SUMIFS函数
      • SUMIF：(criteria range, criteria, sum_range)
      • SUMIFS：(sum_range, criteria_rangel,criterial,criteria_range2, criteria2,…)
      • SUMIF：(判断区域，判断条件，求和区域)
      • SUMIFS：(求和区域，判断区域1，判断条件1，判断区域2，判断条件2，…)
    • 智能填充超出
      • 智能填充会按当前有内容的区域边界进行填充
      • 插入空列分割区域即可按当前区域填充
    • SUMIF等函数的判断条件写法
      • 判断条件为区域和函数时不需要加双引号，例如：A261、DAY(A261)
      • 而判断符、数字和文本都要加双引号，例如：“<=10”
      • 并且判断符需要加上&符号才能与区域和函数一起判断，例如：“<=”&A261、“<=”&DAY(A261)

  • 8月上中下旬的逾期环比
      根据同环比计算公式：8月上旬的逾期环比=8月上旬的逾期金额/邻近的7月下旬的逾期金额-1，但是现在只有8月的数据，没有临近的7月下旬的数据，所以8月上旬的逾期环比求不了了，像这样头一个时间单位求不出同环比的情况很常见，解决的办法除了补全数据，就只有空着了；
      接下来来求8月中旬的逾期环比，8月中旬的逾期环比=8月中旬的逾期金额/8月上旬的逾期金额-1；同理，8月下旬的逾期环比=8月下旬的逾期金额/8月中旬的逾期金额-1，结果以百分比的形式展示，当然这样看数据还不够清晰，可以以“条件格式-数据条”做一个简单的可视化
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    • 环比
      • 本期和上期作对比
        在这里插入图片描述
      • 例如：天环比就是今天/昨天，周环比就是这周/上周，月环比就是这个月/上个月
    • 同比
      • 本期和同期作对比
        在这里插入图片描述
      • 需要准确定义分子和分母

http://www.niftyadmin.cn/n/5147372.html

相关文章

JAVA整理学习实例（一）面向对象

JAVA整理学习实例（一）面向对象

JAVA整理学习实例&#xff08;一&#xff09;面向对象 注&#xff1a;整理一下之前写的东西&#xff0c;然后在修修补补&#xff0c;水平有限&#xff0c;有错误的请指正。 前言 基础部分的面试大部份是理论和一些语法细节&#xff0c;如果平时没有关注&#xff0c;在面试或者做…
阅读更多...
Java 8 中的 Stream API - map() 方法详解

Java 8 中的 Stream API - map() 方法详解

摘要&#xff1a; Java 8 中的 Stream API 提供了一种新的处理集合和数组的方式&#xff0c;可以使代码更加简洁、易读&#xff0c;同时还可以提高性能。其中 map() 方法是比较常用的方法之一&#xff0c;它可以将 Stream 对象中的每个元素映射为另一个元素。本文将对 Java 8 中…
阅读更多...
网络工程师进阶课：华为HCIP认证课程介绍

网络工程师进阶课：华为HCIP认证课程介绍

微思网络HCIP VIP试听课程&#xff1a;DHCP协议原理与配置https://www.bilibili.com/video/BV1cy4y1J7yg/?spm_id_from333.999.0.0 【赠送】IT技术视频教程&#xff0c;白拿不谢&#xff01;思科、华为、红帽、数据库、云计算等等 https://xmws-it.blog.csdn.net/article/det…
阅读更多...
C语言，数据结构指针，结构构体操作符 •，-＞,*的区别，看这篇就够了

C语言，数据结构指针，结构构体操作符 •，-＞,*的区别，看这篇就够了

在朋友们学习指针和数据结构这一章的时候&#xff0c;对各种操作符云里雾里。当你看到这么文章之后你就会明白了。 一 • 和 ->运算符 • 运算符&#xff1a;是结构变量访问结构体成员时用的操作符 -> 运算符&#xff1a;这是结构体指针访问结构体成员时调用的运算符。 …
阅读更多...
conda取消自动进入base环境

conda取消自动进入base环境

安装conda后取消命令行前出现的base&#xff0c;则默认进入了conda环境&#xff0c;如果想取消每次启动自动激活conda的基础环境。 方法一 每次在命令行通过conda deactivate退出base环境回到系统自带的环境 如果再进入的话&#xff1a; conda deactivate 方法二 1&#…
阅读更多...
软件设计模式原则（二）开闭原则

软件设计模式原则（二）开闭原则

继续讲解第二个重要的设计模式原则——开闭原则~ 一.定义 开闭原则(Open Closed Principle&#xff09;是编程中最基础、最重要的设计原则。一个软件实体如类&#xff0c;模块和函数应该对扩展开放(对提供方)&#xff0c;对修改关闭(对使用方)。用抽象构建框架&#xff0c;用实…
阅读更多...
突破性技术！开源多模态模型—MiniGPT-5

突破性技术！开源多模态模型—MiniGPT-5

多模态生成一直是OpenAI、微软、百度等科技巨头的重要研究领域&#xff0c;但如何实现连贯的文本和相关图像是一个棘手的难题。 为了突破技术瓶颈&#xff0c;加州大学圣克鲁斯分校研发了MiniGPT-5模型&#xff0c;并提出了全新技术概念“Generative Vokens "&#xff0c…
阅读更多...
最新 vie-vite框架下 jtopo安装使用

最新 vie-vite框架下 jtopo安装使用

官方地址 官方源码 安装下载 1.官方好像都没有给git地址&#xff0c;尝试npm安装报错 2.找到1.0.5之前的版本npm i jtopo2&#xff0c;安装成功后使用报错&#xff0c;应该是版本冲突了 1.本地引入&#xff0c; 点击官方源码下载&#xff0c;需要jtopo_npm文件 2.引入到本…
阅读更多...
最新文章

Copyright @ 2022~2023