Google Sheets条件求和/按周平均工时动态公式需求

Step 1: Clean Raw Data (Handle Duplicate Body Parts) #

First, we'll preprocess the raw data to average hours for any duplicate body parts. Assuming your raw data is in Sheet1!A1:F, add this formula to a blank range in Sheet1 (e.g., H1) to create a cleaned dataset:

=QUERY(A1:F, "SELECT A, B, C, AVG(D), AVG(E), AVG(F) GROUP BY A, B, C", 1)

This groups rows by body part, start week, and end week, then averages the hours for each person. If there are no duplicates, it just returns the original data—safe to use either way.

Cleaned data columns will be:

• H: Body Part
• I: Start Week
• J: End Week
• K: Arnold (avg hours)
• L: Usian (avg hours)
• M: Bob (avg hours)

Step 2: Set Up the Target Report #

In a new sheet (e.g., Sheet2):

• A1: 工时
• B1: Arnold
• C1: Usian
• D1: Bob
• F1: Start Week (input your desired start week here, e.g., 1)
• G1: End Week (input your desired end week here, e.g., 6)

Generate dynamic week labels in A2:A with:

=ARRAYFORMULA("Week "&SEQUENCE(G1-F1+1,1,F1))

This updates automatically if you change the start/end week values in F1/G1.

Step 3: Core Weekly Calculation Formulas #

Use these formulas to calculate the weekly hours for each person. They’ll adjust automatically when start/end weeks change.

Arnold’s Column (B2) #

=BYROW(A2:A, LAMBDA(week, IF(week="", "", SUMPRODUCT((Sheet1!$I$2:$I <= VALUE(RIGHT(week,2))) * (VALUE(RIGHT(week,2)) <= Sheet1!$J$2:$J) * (Sheet1!$K$2:$K/(Sheet1!$J$2:$J - Sheet1!$I$2:$I +1))))))

Usian’s Column (C2) #

=BYROW(A2:A, LAMBDA(week, IF(week="", "", SUMPRODUCT((Sheet1!$I$2:$I <= VALUE(RIGHT(week,2))) * (VALUE(RIGHT(week,2)) <= Sheet1!$J$2:$J) * (Sheet1!$L$2:$L/(Sheet1!$J$2:$J - Sheet1!$I$2:$I +1))))))

Bob’s Column (D2) #

=BYROW(A2:A, LAMBDA(week, IF(week="", "", SUMPRODUCT((Sheet1!$I$2:$I <= VALUE(RIGHT(week,2))) * (VALUE(RIGHT(week,2)) <= Sheet1!$J$2:$J) * (Sheet1!$M$2:$M/(Sheet1!$J$2:$J - Sheet1!$I$2:$I +1))))))

How It Works #

• BYROW iterates over each week label in A2:A
• VALUE(RIGHT(week,2)) extracts the numeric week number from "Week X"
• The boolean checks (<=, >=) filter rows where the week falls within the activity’s start/end range
• Sheet1!$K$2:$K/(Sheet1!$J$2:$J - Sheet1!$I$2:$I +1) calculates the weekly contribution for each activity (total hours divided by the number of weeks it spans)
• SUMPRODUCT sums all valid weekly contributions for the week

Step 4: Format for Target Output #

Select columns B:D, go to Format > Number > Number and set decimal places to 2 to match the target report’s formatting.

Verification #

For example, Week 1 Arnold’s total:

• Arms: 6 hours / 3 weeks = 2
• Legs:12 hours /6 weeks=2
• Core:10 hours/5 weeks=2
  Sum: 2+2+2=6, which matches the target value.

内容的提问来源于stack exchange，提问作者Logan Schenkey