跨年数据统计思路整理（汇总数据拆分整理）

前言

在日常工作中，对数据汇总我们会根据各种条件，运用查找函数或者统计以及汇总函数来解决。但是如果给你一个汇总的数据，要求按照条件进行拆分整理，难度就要增加了。

1. 案例

要求：统计每个项目，每年的费用合计

总收费：是指从开始日期到结束日期的每日累计量

2. 解题思路

这个题目有三个问题需要了解：1、收款项目并不唯一，有重复。

2、对应的日期只有一个开始和结束日期，不是每天对应一个量

3、费用是一个总量，要分配给每一年

对应思路：1、收款项目有重复，年份有重复，那么拆分完后，用分类汇总函数

2、因为每天的费用标准是一样的，只要在日期区间算出每年各占了多少天，再乘以日均费用，就可以算出总费用在日期区间内每年的占比数量。问题2和3就变成了计算跨年区间每年的天数问题。

思路之一：把日期区间拆分为按日期排列的数组，统一转换成“年”，再计算“年”的个数

思路之二：增加辅助列——每年的最后一天日期，让后对比日期区间，算出每年多少天。

本贴制介绍思路之一的步骤

3. 步骤

第一步、算出每个项目的日均费用

这一步相对简单，不过多赘述，公式=总费用/（结束日期-开始日期+1）

第二步、拆分日期区间

借助sequence函数的特性，可以生成一个以日期区间为基准的日期序列，转成“年”

这个公式在具体操作中有些地方是不用转的，这里面转换的原因是为了更直观的认识公式的转换结果

第三步、计算生成的数组中不重复元素的个数

关于此步骤，大家第一反应就计数函数countifs。算出个数，再乘以日均费用。这样想也是没错，但是我们是写在一组公式中，对于countifs第一参数的设定问题随之而来。

分开公式来统计是没问题的，如下：

但是，如果放在一组公式中，就是报错，详见求助贴《countifs函数雷区到底在哪》，评论下方有各路大佬提供的解决方式，大家可以选择其中一种去解决。在此特别感谢提出宝贵意见和解答的各位大佬。

第四步、分别把每一行的数据都做上述处理，拼接起来

此步骤说是这样说，但实操过程中，不可能一行建立一个公式，这里就要用到循环函数去解决。

第五步、分类汇总

用pivotby函数进行分类汇总，得到最终结果。参考求助贴《countifs函数的雷区到底在哪》给出的其他解决方法，分别写了三种公式公共大家参考（只是在计算个数时公式不一样）：

第一种公式，也是我在遇到嵌套countifs函数时报错，用循环加sumproduct写的，不是很理想

第二种公式

第三种公式

4. 结束语

组合起来的公式中，涉及到了很多函数以及组合嵌套的使用，没做过多的详细说明。每个函数的使用原理大家都可单独查找了解，主要思路的阐述。

本帖的主要目的是：

1、让我们共同学习逆向统计时的思路，尽可能找到一组能够涵盖同一数据模板各种需求的的公式。比如上文中提到的思路之二，利用辅助列来解决时，如果日期区间只跨一个年份的情况，思路之二可以解决，但当跨的年份过多时，就不好解决了。

2，就是统计函数countifs第一参数的设定问题，这个在原贴评论中有些许答案，可以避免踩坑。

3、以此为例，可以举一反三，也可以按月，按天，按周，按季度等去统计、拆分、汇总数据。