Excel公式识别范围内日期范围的数量 [英] Excel formula identifying number of date ranges within a range

问题描述

我有一个复杂的场景，我试图用Excel公式编写，而不用进入宏。

I have a complicated scenario that I'm trying to write in an Excel formula without going into macro's.

这种情况是我给了一个日期范围，被称为范围A.即10/1/2011 - 5/4/2011

The scenario is that I am presented with a date range, refered to as Range A. ie 10/1/2011 - 5/4/2011

我有一个包含3列的查找表;从日期，到日期，价格。此表格包含大约50行，每行代表一个独特的3个月期间（年度季度）和相应的费率。

I have a lookup table with 3 columns; From Date, To Date, Rate. This table contains around 50 rows, and each row represents a unique 3 month period (yearly quaters) and a corresponding rate.

我需要能够获得范围A，找到每个季度的几天，并将这些日子乘以四分之一的比率。

I need to be able to take Range A, find how many days are in each quater and multiply those days by the quaterly rate.

在示例中，10/1/2011 - 5/4/2011我应该最终与81 * 2011_Q1_rate + 5 * 2011_Q2_rate。

In the example 10/1/2011 - 5/4/2011 I should end up with 81*2011_Q1_rate + 5*2011_Q2_rate.

这将是简单的几个for循环和VLOOKUP，但我需要避免宏的。有没有人有任何其他建议？

This would be simple with a couple of for loops and VLOOKUP, but I need to avoid macro's. Does anyone have any other suggestions?

谢谢，

Scott。

推荐答案

我打算将这一个分解成部分，因为最终方程很长。

I am going to break this one down into parts because the final equation gets quite long.

必须做两件事情，首先拉开你的日期范围。我的范围是单元格 F16 进行测试。这是使用 = MATCH（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1：A12,1）和 = MATCH（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1）其中 A1 通过 C12 是您的从日期到价格数组。这两个比赛语句将给我们您的范围所属的第一季度和最后一个季度的行。使用这些行号，我们使用 address（）创建引用，例如 = ADDRESS（MATCH（DATEVALUE（LEFT（F16，FIND（ - ，F16 ）），A1：A12,1），2）&：& ADDRESS（MATCH（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1： A12,1），2）。以前的公式将在我的测试用例中吐出 $ B $ 1：$ B $ 9 。

We have to do two things, first pull apart your date range. My range is in cell F16 for testing. This is done using =MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1) and =MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1) where A1 through C12 is your From Date to Rate array. These two match statements will give us the rows that the first quarter and the last quarter that your range falls into. Using these row numbers we create references using address() such as =ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2). The previous formula will spit out $B$1:$B$9 in my test case.

第二部分是找到总数。我们将利用 sumproduct（）。以下是简单的 sumproduct（）公式： = SUMPRODUCT（（B1：B7-A1：A7 + 1），C1：C7）。该范围现在只是静态测试。将采用四分之一秒减去开头的阵列格式的季度，并为每个加1。这是因为当我们想要31天时， 1/31/13 - 1/1/13 = 30天的事实。然后将这个数组乘以每个月的费率。

The second portion is finding the totals. We will make use of sumproduct(). Here is the simple sumproduct() formula: =SUMPRODUCT((B1:B7-A1:A7+1),C1:C7). The range is just static now for testing. It will take the quarter end minus the quarter beginning in array format, and add 1 to each. This is due to the fact that 1/31/13 - 1/1/13 = 30 days when we want it to be 31 days. This array is then multiplied by the rates for each month.

现在，当我们将它们拼凑在一起时，我们采用我们的第一个范围，使用间接（ ），并将我们的动态范围替换为简单的 sumproduct（）公式，我们用 = SUMPRODUCT（（INDIRECT地址（匹配（日期（F16，FIND（ - ，F16））），A1：A12,1），2）&：& ADDRESS（MATCH（DATEVALUE ）-2-FIND（ - ，F16））），A1：A12,1），2）） - 间接（ADDRESS（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1 ：A12,1），1）&：& ADDRESS（MATCH（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1） 1））+ 1），INDIRECT（ADDRESS（MATCH（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1：A12,1），3）&：& ADDRESS DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1），3）））。

Now when we piece it all together, we take our first range, use indirect(), and substitute our dynamic ranges into the simple sumproduct() formula and we finish with =SUMPRODUCT((INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),1))+1),INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3))).

现在公式没有考虑到部分月份。我们减去部分月份（从我们以前的方程式窃取）与 =（（DATEVALUE（LEFT（F16，FIND（ - ，F16））） - INDIRECT（ADDRESS（MATCH（DATEVALUE（LEFT F16，FIND（ - ，F16））），A1：A12,1），1）））* INDIRECT（ADDRESS（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1： A12,1），3）））和 =（（INDIRECT（ADDRESS（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND ，F16））），A1：A12,1），2）） - 日期值（右（F16，LEN（F16）-2-FIND（ - ，F16））））*间接（地址（匹配（DATEVALUE RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1），3）））我们完成了

Now that formula does not take into account partial months. We minus out the partial month (stealing from our previous equations) with =((DATEVALUE(LEFT(F16,FIND(" - ",F16)))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)))*INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3))) and =((INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))))*INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3))) and we are done.

最终方程：
= SUMPRODUCT（（INDIRECT（ADDRESS（MATCH（DATEVALUE（LEFT（F16，FIND（ - ，F16））） ，A1：A12,1），2）&：& ADDRESS（MATCH（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1 ）） - INDIRECT（ADDRESS（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1：A12,1），1）&：& ADDRESS（MATCH（DATEVALUE （RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1），1））+ 1），间接（地址（日期（日期（F16， （ - ，F16））），A1：A12,1），3） amp; ADDRESS（MATCH（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1），3））） - （（DATEVALUE （LEFT（F16，FIND（ - ，F16））） - 间接（ADDRESS（DATEVALUE（LEFT（F16，FIND（ - ，F16））），A1：A12,1），1））） * INDIRECT（ADDRESS（DATEVALUE（DATEVALUE（RIGHT（F16，F16，FIND（ - ，F16））），A1：A12,1），3） （F16）-2-FIND（ - ，F16））），A1：A12,1），2）） - 日期值（右（F16，LEN（F16） ））* INDIRECT（ADDRESS（DATEVALUE（RIGHT（F16，LEN（F16）-2-FIND（ - ，F16））），A1：A12,1），3）））

如果将数据范围拆分成两个单独的单元格，则会使方程式更小，并且具有更高的正确工作的可能性，因为有较少的错误余地。

If you split the data range into two separate cells, it will make the equation much smaller and have a higher likelihood of working correctly since there is less room for error.

这篇关于Excel公式识别范围内日期范围的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！