问题描述
我有一个复杂的场景，我试图在不使用宏的情况下用 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?
谢谢，
斯科特.
推荐答案
我将把这个分解成几个部分，因为最后的等式很长.
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 是您的 >从日期到Rate数组.这两个匹配语句将为我们提供您的范围所属的第一季度和最后一个季度的行.使用这些行号，我们使用 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.这是因为 1/31/13 - 1/1/13 = 30 days 我们希望它是 31 天.然后将此数组乘以每个月的费率.
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.
现在，当我们将它们拼凑在一起时，我们取第一个范围，使用 indirect()，并将我们的动态范围代入简单的 sumproduct() 公式，然后我们以 =SUMPRODUCT((INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(匹配(日期值(右(F16，LEN(F16)-2-FIND("- "，F16)))，A1:A12，1)，2))-间接(地址(匹配(日期值(左(F16，FIND)(" - ",F16))),A1:A12,1),1)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16)))),A1:A12,1),1))+1),间接(地址(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3)&":"&ADDRESS(MATCH(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(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3))) 和 =((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))) 就完成了.
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),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(地址(匹配(日期值(左(F16，FIND("- "，F16)))，A1:A12,1)，3)))-(间接(地址(匹配(日期值(右(F16，LEN))F16)-2-FIND(" - ",F16))),A1:A12,1),2))-DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))))*间接(地址(匹配(日期值(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 公式标识范围内的日期范围数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！