Excel交叉表转表格：基于内置公式实现周时间追踪器需求

I’ve tackled similar time-tracking setups before, and since you’re restricted to built-in formulas (no VBA/custom functions), here’s a step-by-step approach to fix your work order matching issue and build the summary table you need:

Assumptions About Your Source Data #

Let’s assume your calendar-style data is laid out like this (adjust ranges to match your sheet):

• Column A: Dates (only populated once per day; blank rows for subsequent time entries that day)
• Column B: Work Order numbers (only populated when a new work order starts; blank for entries under the same WO)
• Column C: Start Time
• Column D: End Time

1. Fill Missing Work Order Numbers (Match to Dates) #

Since you already have dates repeating correctly, use this formula in a helper column (e.g., Column E) to carry forward the last non-blank work order to all entries under the same date:

=IF(B2<>"", B2, LOOKUP(2, 1/(B$2:B1<>""), B$2:B1))

• How it works: The LOOKUP function finds the last cell above the current row that isn’t blank in Column B, and returns that work order number. If the current row has a WO, it uses that instead.
• Drag this formula down all rows of your data.

2. Calculate Duration per Time Entry #

Add another helper column (e.g., Column F) to compute the duration of each entry. Use this formula for decimal hours (easier for summation):

=(D2-C2)*24

• If you prefer hours:minutes format, use =D2-C2 and format the cell as [h]:mm (the brackets ensure it handles durations over 24 hours).

3. Generate a Summarized Table (Hours per Date/Work Order) #

To create a copy-pasteable summary table, we’ll use built-in functions to get unique date-WO pairs and total hours.

For Excel 365/2021 (Spill Functions):

1. In a new range (e.g., H1), add headers: Date, Work Order, Total Hours
2. In H2, pull unique dates:

   =UNIQUE(A:A)
   

3. In I2, get unique work orders for each date (this will spill automatically):

   =UNIQUE(FILTER(E:E, A:A=H2))
   

4. In J2, sum hours for each date-WO pair:

   =SUMIFS(F:F, A:A, H2, E:E, I2)
   

   Drag this formula down to match all rows in the spill range.

For Older Excel Versions (No Spill Functions):

1. In H2, get the first unique date (enter as an array formula with Ctrl+Shift+Enter):

   =INDEX(A:A, MATCH(0, COUNTIF(H$1:H1, A:A), 0))
   

   Drag down until you get #N/A (stop at that point).
2. For each date in H2:Hn, get unique work orders in I2 (array-enter with Ctrl+Shift+Enter):

   =INDEX(E:E, MATCH(0, COUNTIF(I$1:I1, E:E)*(A:A<>H2), 0))
   

   Drag down for each date until you hit #N/A.
3. Sum hours in J2:

   =SUMIFS(F:F, A:A, H2, E:E, I2)
   

   Drag this down all rows.

4. Copy-Paste to Your Worksheet #

Once your summary table is complete, select the entire table (headers + data), copy it, and paste into your target worksheet as values (right-click > Paste Special > Values) to ensure the formulas don’t break when moved.

Content of the question originates from Stack Exchange, question author Wallaby