具有多个条件的复杂Excel方程

Attempt #2 to answer! >8(

The end equation to do this all in one cell is going to get ugly,but it will work. In order to explain this and basically how I developed it, I am going to break it down into parts. At the end the parts will be back substituted into the big equation.

The first thing I did was determine what row was last filled, or as per the comments what is the last row of column P that has a value greater than 0. In order to determine this I used the aggregate function in a temporary cell of T15 (yes, in the middle of your spreadsheet but it wont matter in the end):

=AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)

To break this function down:

• 14 tells it we want to do an array type calculation sorting the array from largest to smallest.
• 6 tells it to ignore errors
• (P5:P30>0) tells it to build a true (or 1) and false (or 0) array of cells greater than 0
• (ROW(P5:P30)-ROW(P5)+1) generates an array listing row numbers
• 1 tells it to return the largest value in the array, if it was 2 the second largest.

Now the important thing here is what happens when you multiply the greater than 0 array with the row number array. You wind up getting only the row numbers where there is a value in P greater than 0. And when we sort that and ask for the largest number we get the last row you have completed. Something to work with.

So now we can look up the last date completed, do some checks to see if its the end of the month or not and figure out how many sick days. The ugly formula starts out as:

=IF(MONTH(INDEX(I5:I30,T15))=MONTH(INDEX(I5:I30,T15)+14),MONTH(INDEX(I5:I30,T15))-1,MONTH(INDEX(I5:I30,T15)))

The logic test here is to find our if the last filled out date and the date 14 days in the future are still the same month. If they are the same month, you are not at the end of the month yet and there for have only earn up to the previous month's number in sick days. As such this part will tell us the previous month's number os sick days:

MONTH(INDEX(I5:I30,T15))-1

Now if the date 14 days in the future is not the same month then we know the last entry for the month has been completed and therefore we have accrued that month's number in sick days and use basically the same formula:

MONTH(INDEX(I5:I30,T15))-1

well I can see we have called on cell T15 4 times just to determine if we are subtracting 1 or 0. While the IF formula may feel more inline with your thought process, we can rearrange things and still get the same results but shortening the formula, reducing the calls to cell t15 by 1 and dropping the IF all together. This only works because we are dealing with 1 and 0 which is also true and false.

=MONTH(INDEX(I5:I30,T15))-(MONTH(INDEX(I5:I30,T15))=MONTH(INDEX(I5:I30,T15)+14))

Now lets bypass that T15 calculation and back substitute it in to the month formula above to get:

=MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))-(MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))=MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1))+14))

Not done yet. That only tells you the number of days you have accrued this year. Not what you really want to know. you need to convert it to hours. It also need to be reduced by the number of sick days used. The following need to be added to the big ugly above:

• *8 for 8 sick hours to a sick day
• -sum($L$5:$L$30) to account for sick time used

this results in:

=(MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))-(MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))=MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1))+14)))*8-sum($L$5:$L$30)

Now I did notice during testing that if no entries are in the spreadsheet, then the row of the last entry become 0 and this is simply not acceptable as it causes some strange results. So we will wrap this whole formula in a small error catcher to make sure 0 is the results when no payperiods have been completed.

=if(sum(P5:P30)=0,0,(MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))-(MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1)))=MONTH(INDEX(I5:I30,AGGREGATE(14,6,(P5:P30>0)*(ROW(P5:P30)-ROW(P5)+1),1))+14)))*8-sum($L$5:$L$30))

锦上添花的是从上一年开始累计的病假天数增加了。由于我不确定患病率和病假开始如何协同工作，因此我将把该计算留给您，让您知道上一年结转的任何数字，只需在最后一个末尾将其添加到上述公式中即可） 。

这是我的测试床，上面显示了概念证明：

警告：此方法## WILL ##会产生错误的结果，因为最后一个日期之前的支付期= = 0，且支付期> 0，请参见以下示例：