全年生产数据与图表看板

A is substantially more reliable: it preserves the supplied Week 1 data, maintains all nine operators’ machine/shift assignments across 48 weeks, and leaves unreported weeks blank. Its formulas correctly produce 38,880 Week 1 units, Operator 1 as leader with 4,720, and Machine 3 with 13,300. B invents production figures for Weeks 2–48, contaminating YTD results. Its selection calculations reference descriptive text in C5:C7 instead of numeric weeks, while its numeric-week helper is populated only on Operator 1 rows; the selected-week summaries therefore cannot work correctly. B also contains a self-referencing formula in C66. Neither fully satisfies the explicit PivotTable requirement: the purported PivotTable output areas are ordinary formula-driven summaries.

B is the more reliable tracker: it preserves the Week 1 inputs and fixed assignments, leaves future production blank, and calculates selected-period totals and averages using the dropdown weeks and recorded-day counts. Its Week 1 KPI formulas yield 38,880 units, Operator 1 with 4,720 units, and Machine 3 with 13,300 units. A populates Weeks 2–48 with unsupported production, corrupting YTD reporting. A’s selection formulas also reference explanatory text in C5:C7, its numeric week helper is populated only on Operator 1 rows, and C66 contains a circular reference. Neither fully meets the explicit PivotTable requirement: the areas labelled PivotTables are ordinary SUMIFS/SUMPRODUCT formula summaries.

Both workbooks share the same basic structure. Each has a 432-row data sheet for Weeks 1–48 with Week 1 copied correctly from the input. Each operator keeps a fixed machine and shift, and Average/Total are auto-calculated. Each has a Week 1 KPI table that computes correctly: 38,880 total units, Operator 1 top at 4,720, Machine 3 top at 13,300, 4,320 average per operator, and 58.4% day / 41.6% night. Each reports 4 charts. Neither contains true PivotTable objects; both use formula tables labelled as PivotTables. A's interactive dashboard works. The Start Week (D4) and End Week (H4) dropdowns feed SUMIFS against a WkNum helper column (L2:L433) that is filled on every row. As a result, operator output, machine totals, shift average and share, and a sorted YTD leaderboard (LARGE with INDEX/MATCH) all recalculate correctly. The rendered data sheet shows correct top-3 green and bottom-3 red highlighting on both Average and Total. Weeks 2–48 are left blank for entry, which fits a tracker where only Week 1 data is supplied and the graphs are "based on the Week 1 data". B's selection logic is broken, for two reasons: - D9 and E9 point to C5, C6 and C7, but those cells hold text notes ("← week number used by the single-week view" etc.) rather than week numbers. The SUMPRODUCT filters in PivotTables 1–3 therefore return 0, and the per-week and per-day columns built on ($E$9-$D$9+1) evaluate to #VALUE!. - Helper column M has a week number only on the first row of each 9-row block (M2, M11, …). Even with C5–C7 fixed, only Operator 1 rows would be counted. B's other weaknesses: - The leaderboard's operator, machine and shift order is typed in as fixed values, not calculated. - Weeks 2–48 are filled with invented, unlabelled production numbers. This contaminates a real tracker and means the YTD chart and leaderboard reflect made-up data. - The colour-scale formatting is less explicit about top and bottom performers than A's rules. B's data sheet looks fuller when rendered, but A is the correct and usable deliverable.

Neither workbook appears to contain true PivotTables: both 'PivotTable' blocks are SUMIFS/SUMPRODUCT formula ranges. B is clearly the more correct and usable tracker. B: The Start/End Week dropdowns (D4, H4, list from O2:O49) feed SUMIFS using a fully populated WkNum helper (L2:L433). The operator, machine and shift tables and the YTD leaderboard (LARGE/INDEX) therefore respond to the selection. Week 1 data is correct, and the KPI formulas resolve correctly: 38,880 units; Operator 1 at 4,720; Machine 3 at 13,300; 4,320 per operator; 58.4% day and 41.6% night. Weeks 2-48 carry fixed operator, machine and shift assignments with blank-safe formulas, ready for entry. YTD therefore reflects only supplied data, which fits the 'based on Week 1' chart instruction. Rendered conditional formatting clearly marks top and bottom performers. A: Week 1 data, the KPI formulas and the chart source formulas are sound, and a colour scale is applied, but the required selection logic is broken. D9 and E9 read C5:C7, which hold annotation text ('← week number used…') rather than week numbers. Pivots 1-3 would therefore return zeros, and their average columns and C9 would return #VALUE!. Helper column M is populated only on the first row of each week (M2, M11, …), so fixing that reference would still capture only Operator 1 rows. C66 (=$C66/48) is a circular reference, and the leaderboard order is hard-coded. A also invents output for weeks 2-48, with no label on the sheets identifying it as simulated, and that data feeds the YTD chart and leaderboard. This is not supplied data and is risky in a production-meeting tool. Its fuller-looking table does not offset dropdowns and selected-week tables that do not work.