excel - 连续查找并制定下一个星期六 - excel

Question

我需要编写一个公式，它可以在月份变化时相应地动态变化。

第 1 周（单元格 B16）计算第 4 行直到该月的第一个星期六，第 2 周（单元格 C16）计算第 4 行从下一个星期一（第一个星期日之后）到第二个星期六，第 3 周（单元格 D16）计算第从每月的第二个星期一（第二个星期日之后）到第三个星期六和第 4 周（单元格 E16）的第 4 行计算从该月的第三个星期一（第三个星期日之后）到第四个星期六和第 5 周的第 4 行（单元格 F16）计算从该月的第四个星期一（第四个星期日之后）到第五个星期六（如果有）的第 4 行。

我知道我需要使用index、match、row等，但我无法制定它。

我需要在公式中制定数组，这样我们就可以拥有根据月份和日期变化的动态数组，而无需更改列... B4:G4 ， B$3:G$3 等...

请帮忙。

谢谢...

示例文件

标签： excelindexingmatch

Answer 1

假设数据位于B1:AF4您喜欢的单元格中输入这些公式：

第 1 周：

= SUMIFS( $B$4:$AF$4,    
$B$2:$AF$2, ">=" & SUM( $B$2, WEEKDAY( $B$2, 1 ) = 1 ),    
$B$2:$AF$2, "<=" & SUM( 7, $B$2, -WEEKDAY( $B$2, 1 ) ) )

第 2 周：

= SUMIFS( $B$4:$AF$4,    
$B$2:$AF$2, ">=" & SUM( 9, $B$2, -WEEKDAY( $B$2, 1 ) ),    
$B$2:$AF$2, "<=" & SUM( 14, $B$2, -WEEKDAY( $B$2, 1 ) ) )

第 3 周：

= SUMIFS( $B$4:$AF$4,    
$B$2:$AF$2, ">=" & SUM( 16, $B$2, -WEEKDAY( $B$2, 1 ) ),    
$B$2:$AF$2, "<=" & SUM( 21, $B$2, -WEEKDAY( $B$2, 1 ) ) )

第 4 周：

= SUMIFS( $B$4:$AF$4,    
$B$2:$AF$2, ">=" & SUM( 23, $B$2, -WEEKDAY( $B$2, 1 ) ),    
$B$2:$AF$2, "<=" & SUM( 28, $B$2, -WEEKDAY( $B$2, 1 ) ) )

第 5 周：

= SUMIFS( $B$4:$AF$4,    
$B$2:$AF$2, ">=" & SUM( 30, $B$2, -WEEKDAY( $B$2, 1 ) ),    
$B$2:$AF$2, "<=" & SUM( 35, $B$2, -WEEKDAY( $B$2, 1 ) ) )

第 6 周（*）：

= SUMIFS( $B$4:$AF$4,
$B$2:$AF$2, ">=" & SUM( 37, $B$2, -WEEKDAY( $B$2, 1 ) ),    
$B$2:$AF$2, "<=" & SUM( EOMONTH( $B$2, 0 ), -( WEEKDAY( EOMONTH( $B$2, 0 ), 1 ) = 1 ) ) )

(*) 请注意，根据您对一周的定义，2020 年 8 月期间将有六周。

另外，建议C2:AF2用这个改变公式：

= IFERROR( IF( B2= EOMONTH( B2, 0 ), "", 1 + B2 ), "" )