【Excel】基础应用

案例—项目排期表

---

---

一、Excel常用函数

要想更好、更清晰的完成项目，首先就要将Excel里的常用函数学好，学透彻。

1.1 条件计数函数

计数一般情况下用到的函数为Count，但是Count函数仅仅对数值类数据有效，而且不能附加条件，如果要附加条件计数，就要用到Count if函数

count if(range,criteria)计算某个区域中满足给定单元条件的单元格数目,参数range为单元格区域，参数criteria为判断条件
count if是指单个条件判断，若判断条件不止一个，则需用到count ifs
count ifs里面判断条件是且的关系（只要使用count ifs函数，就应该两个函数是and的关系）

1.2 累计数据

• 所谓累计，通俗的讲就是给出某一天数量，统计这一天之前的数量之和
  例：给出1月1日数量为293,那么这一天的累计也为293（1月1日是1月第一天），给出1月2日数量为192，那么这一天的累计为293+192，以此类推

• 业务价值：如果某个员工1月销售目标为10000，那么累计数据则可随时观测到距离目标还差多少。

首先需要知道SUM函数，它能对单元格进行填充，运用填充柄时会改变行，而我们进行累计计算时不需要改变起始单元格，为此我们可以选用SUM函数对区域进行求和，且固定起始单元格（f4）则可实现这一目标。

1.3 条件求和函数

• SUMIF(range, criteria, [sum_range])对满足条件的单元格进行求和
  range：条件区域，criteria：条件。sum_range：求和区域。可以省略，当省略时，条件区域就是求和区域。
• 若条件有多个则选择SUMIFS函数
  SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)与SUMIF不同的是这里第一个参数为求和区域，接着条件区域，条件。
  引用符号一定要用英文符号

1.4 条件格式

在做数据呈现时会涉及到符合某个规则希望它突出显示，那么就可以用到条件格式

1.4.1 单元格值为基础的条件格式

所选条件是15到25之间

1.4.2 公式为基础的条件格式

若公式中存在单元格地址引用注意相对地址和绝对地址的使用

最后点击确定，即可实现以上案例
逻辑：从所选区域左上第一个单元格开始判断

例：偶数行加颜色
除以下公式外，其他都与上面同样步骤

公式实现规则：ROW() 返回的是所选单元格行数（可不填参数），MOD() 返回值是两个参数取余数（第一个参数为被除数，第二个参数为除数），行数除以2，若余数为零则该行数为偶数。为我们所需标记的单元格。

1.5 数据条、色阶、图标集

数据条：
可以突出当前的数据大致差异

默认条件下会选择所选区域的最大值作为完全填充，其他数值会相较于最大值进行查看数位是多少。

色阶
与数据条所呈现结果相同，区别为色阶是在颜色深重上有差异

色阶一般应用在热力图表上

图标集
突出百分比数值是增长还是下降

1.6 日期时间类函数

在Excel里将数据展示为标准日期格式（年/月/日）才能运用日期时间类函数

二、项目排期表

根据一个项目开始时间和结束时间来绘制一个项目排期表
下图为一个已经完成的项目排期表

所给我们数据仅有前三列表，后两列表需要我们自行计算

• 项目时长=结束时间-开始时间+1
• 距离项目结束时间还剩*天=IF(结束时间<Today(),0,IF(结束时间=Today(),“仅剩今天”,结束时间-Today()))

接下来就是绘制图，这里运用到的是堆积条形图，我们需要知道的是蓝色部分表示的是项目时长，而每一阶段蓝色左端到左轴距离表示的是每一阶段起始时间（这里用的颜色是无色），而每一阶段蓝色右端离表示的是每一阶段结束时间

• 选择数据（项目描述，开始时间，项目时长），插入堆积条形图，这时我们发现除了把项目阶段作为类别同时也把开始时间作为类别（如下图），这时需要我们进行调整。
  

按照如下步骤：

• 接下来首先要将表中起始时间和结束时间改为数值类型，然后设置坐标轴格式，最后再将起始时间和结束时间改为日期类型即可

到此项目排期表就绘制完成了。

三、员工考勤表

本题案例选用2019,2020,2021年员工考勤表

选择序列原因为序列表较灵活，这样即可实现动态表标签，同理将月份也改为动态标签。

接下来需手动输入一些指标：

由于每月最后一天数值不同，这时需要我们运用公式(如上图)
公式逻辑：首先我们要确定选择不同年月份时当月天数，前面我们已经知道DATE()可以返回标准日期格式，EOMONTH()可以返回标准日期当月最后一天，DAY()可以返回当月第几天数值，结合以上我们可以最终得到当月有多少天的动态数值，最后用IF函数结合填充柄则可实现填充到每个月最后一天就终止

• 接下来实现星期填充

公式逻辑：首先我们知道DAY()可以返回当月第几天数值，WEEKDAY()能够返回指定日期是周几，最后加判断原因是由于有的单元格是空值，那么这时候我们不需要进行判断是周几。

由于我们后面要用到自定义填充，所以WEEKDAY()要选择参数1（也就是说返回值为1时代表当天星期日，以此类推） ，这是系统规定

• 将星期数值改为自定义格式

选中我们已经写好公式的星期单元格，单击右键，选择【设置单元格格式】

最后用填充柄填充即可实现动态数据表，接下来可以选中日期、星期两个区域，在【开始】选项卡下的【格式】选择【自动调整列宽】即可让图表更美观

• 对周六、周日和空白单元格突出显示：

这里运用到上面说过的条件格式（灰色表示空白单元格，深蓝色为周六，浅蓝色为周日）