R04.03.01.02 · 任务小类

数据透视与分组

Pivot tables & grouping

Build pivot tables and groupings by dragging fields, with the right filters and layout.

20 条本项目编写的提案 · 分类 v1.1 · 新增基准运行 0 次。下方验证方式与部分得分规则是设计，不是执行结果。

典型应用: Excel · LibreOffice Calc · Google Sheets
小类 GUI 需求标签: G2 · GUI 必要

任务提案 · 20

展开查看完整原文与建议检查方式；两种界面均保留提案英文原文。

R04.03.01.02-T01 Build a Region × Quarter revenue pivot with date grouping

原始指令

From table 'Sales' in fixtures/sales_2025.xlsx (18k rows), insert a PivotTable on a new sheet 'Pivot' at A3: Region in rows, OrderDate grouped by Quarters (not Years) in columns, Sum of Revenue as values formatted #,##0. Grand totals on both axes. Save as output/sales_pivot.xlsx.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

Parse pivotTableDefinition: location A3 on 'Pivot', rowFields=Region, colFields=OrderDate with fieldGroup groupBy='quarters', one dataField sum Revenue numFmt #,##0; refreshed cell values equal key. openpyxl cannot author pivots; VBA disabled by policy and no COM, AppleScript or Python automation in the VM.

建议部分得分

20% pivot at right location from 'Sales'; 30% rows/columns fields; 30% quarter grouping; 20% values and format correct.

R04.03.01.02-T02 Count tickets by priority and status for one team in Calc

原始指令

Open fixtures/helpdesk_q3.ods in LibreOffice Calc. Create a pivot table on new sheet 'Summary' with Priority in rows (order P1, P2, P3, P4), Status in columns, Count of TicketID as data, and a page filter on Team set to 'Network'. Save as .ods.

平台: mac · macOS win · Windows lin · Linux
应用: LibreOffice Calc
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

Parse content.xml table:data-pilot-table: row field Priority with custom order, column field Status, data field count TicketID, page field Team selected 'Network'; output cells equal key. Agent session has macros disabled and no UNO/Python access.

建议部分得分

25% pivot on 'Summary'; 25% row/column fields; 25% Network filter; 25% counts and order correct.

R04.03.01.02-T03 Show product share of category with a tabular, repeated-label layout

原始指令

In fixtures/retail_lines.xlsx build a PivotTable on sheet 'Mix': Category then Product in rows, Sum of Units shown as % of Parent Row Total (1 decimal), Tabular Form with Repeat All Item Labels, no subtotals, sorted by Units descending within each category. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击 hover · 悬停与上下文
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

pivotTableDefinition: compact=0 outline=0 (tabular), fillDownLabels set, dataField showDataAs='percentOfParentRow', subtotals off, autoSortScope by Units desc; refreshed values equal key (tol 0.05 pp). VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

25% fields and nesting; 30% % of parent row; 25% tabular + repeated labels + no subtotals; 20% sort order.

R04.03.01.02-T04 Bin order values into 250-wide groups and filter by year slicer

原始指令

fixtures/orders.xlsx: create a PivotTable on 'Bins' counting OrderID by OrderValue grouped into bins starting at 0, ending 2999, by 250 (values ≥3000 fall into the top '>3000' bin). Add a Year slicer from OrderDate and set it to 2025 only. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击 type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

fieldGroup rangePr startNum=0 endNum=2999 groupInterval=250; slicer cache bound to the pivot with only 2025 selected; refreshed counts equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

35% numeric grouping exact; 25% count of OrderID; 25% slicer connected and set to 2025; 15% counts correct.

R04.03.01.02-T05 Manually group states into named custom regions inside a pivot

原始指令

In fixtures/field_sales.xlsx pivot 'PT_States' lists 14 states. Group them into 4 named groups per input/regions.txt ('Pacific', 'Mountain', 'Plains', 'Lakes'), rename the new field to 'Sales Region', collapse it to group level and sort groups alphabetically. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 drag · 拖放 type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

pivotCacheDefinition has a discrete fieldGroup named 'Sales Region' whose groupItems map states exactly per regions.txt; group items collapsed (sd=0); sort ascending; displayed totals per group equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

45% 14 states grouped correctly (pro-rata); 20% group names and field renamed; 20% collapsed; 15% sorted.

R04.03.01.02-T06 Add a calculated margin field and a top-10 filter to a pivot

原始指令

In fixtures/product_pnl.xlsx add a calculated field 'Margin %' = Profit / Revenue to pivot 'PT_Products', format it 0.0%, sort products by Margin % descending and apply a Top 10 by Sum of Revenue value filter. Remove the Sum of Profit field from values. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 type · 文字与表单 drag · 拖放
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

cacheField with formula 'Profit/Revenue'; dataField uses it with numFmt 0.0%; filter type 'count' top 10 on Sum of Revenue; Sum of Profit absent; refreshed visible rows and values equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

35% calculated field correct; 25% top-10 value filter; 25% sort by margin; 15% profit field removed and formats.

R04.03.01.02-T07 Build a Numbers pivot table with category rows and year columns

原始指令

Open fixtures/bookstore.numbers in Numbers. Create a pivot table from table 'Orders' on a new sheet 'Pivot': Category in rows, Order Date grouped by Year in columns, values Sum of Net Sales and Average of Discount (percentage, 1 decimal). Hide the grand-total column. Save.

平台: mac · macOS
应用: Numbers
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G3 · 仅 GUI
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader exports the file to .xlsx via its own AppleScript and checks pivot output values, row/column headers and number formats against key; also inspects pivot options in the exported sheet. osascript/JXA is blocked for the agent; the file must be saved by Numbers and keep the original document id, so an imported .xlsx pivot fails.

建议部分得分

30% pivot sourced from 'Orders' on 'Pivot'; 25% rows/columns incl. year grouping; 30% two value fields correct; 15% formats and hidden total.

R04.03.01.02-T08 Wire one slicer and one timeline to two pivots and set Q3 EMEA

原始指令

fixtures/bookings_dash.xlsx has pivots 'PT_Revenue' and 'PT_Units' on sheet 'Dash'. Insert a Region slicer and a BookingDate timeline, connect both to both pivots via Report Connections, then set Region = EMEA and the timeline to Jul–Sep 2025. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 drag · 拖放
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

slicerCache for Region and timelineCache for BookingDate each list both pivots; selection EMEA and timeline range 2025-07-01..2025-09-30; both pivots' refreshed values equal key. Timeline objects cannot be authored by openpyxl; VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

25% slicer inserted; 25% timeline inserted; 25% both connected to both pivots; 25% selections and resulting values correct.

R04.03.01.02-T09 Distinct-count customers by month and channel via the Data Model

原始指令

In Excel for Windows, load table 'Txns' from fixtures/txn_log.xlsx into the Data Model and build a PivotTable on 'Active' showing Distinct Count of CustomerID with TxnDate by Month (Year > Month hierarchy) in rows and Channel in columns. Save as output/active_customers.xlsx.

平台: win · Windows
应用: Excel · Power Pivot
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 drag · 拖放
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

Workbook contains a Data Model (xl/model) with 'Txns'; pivot is OLAP-based with a distinct-count measure on CustomerID; Excel COM refresh by grader yields values equal key. Regular cache pivots cannot distinct-count; VBA disabled; no COM host or Python in the VM.

建议部分得分

25% table in Data Model; 35% distinct count measure; 25% month and channel layout; 15% values correct.

R04.03.01.02-T10 Repoint three stale pivots to a growing table and refresh

原始指令

fixtures/ops_report.xlsx has 3 pivots built on fixed range Data!A1:K4200; new 2026 rows were pasted below. Convert the data to an Excel Table 'tblOps', change all 3 pivots' data source to tblOps so they share one cache, refresh, and confirm 2026 appears in each year filter. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: hidden · 隐藏 / 过期状态
提案难度: 4 / 5

建议验证方式

Table tblOps spans all rows; all 3 pivots reference cacheId with worksheetSource name='tblOps' (single shared cache); cached records include 2026; refreshed values equal key. Grader appends rows and refreshes to confirm table-based source. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

20% table created; 40% three pivots repointed (pro-rata); 20% single shared cache; 20% refreshed values incl. 2026.

R04.03.01.02-T11 Group a Calc pivot's dates by month and quarter with two data fields

原始指令

In fixtures/energy_meter.ods create a LibreOffice Calc pivot table on sheet 'Monthly' with ReadingDate grouped by Quarters and Months in rows, Site in columns, and data fields Sum of kWh and Average of PeakKW. Turn off 'Show expand/collapse buttons' and add a pivot chart (columns, Sum of kWh only). Save.

平台: mac · macOS win · Windows lin · Linux
应用: LibreOffice Calc
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

content.xml data-pilot-table: date grouping quarters+months, Site column field, two data fields with sum/average, drill-down buttons off; a chart object sourced from the pilot with one series; output values equal key. Macros/UNO disabled.

建议部分得分

30% date grouping; 30% two data fields correct; 15% buttons off; 25% pivot chart with right series.

R04.03.01.02-T12 Build a pivot and a GETPIVOTDATA summary that survives re-layout

原始指令

From fixtures/hr_headcount.xlsx build pivot 'PT_HC' (Department rows, Grade columns, Count of EmployeeID). On sheet 'Board' fill B2:E9 with GETPIVOTDATA formulas for the 8 departments × 4 grades listed there. The grader will move fields around, so plain cell references are not acceptable.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

Pivot fields as specified; every Board!B2:E9 cell is a GETPIVOTDATA formula referencing PT_HC; grader swaps rows/columns and moves the pivot via COM, refreshes, and Board values must still equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

30% pivot correct; 30% GETPIVOTDATA in all 32 cells; 40% values correct before and after re-layout.

R04.03.01.02-T13 Relate three tables in Power Pivot and report YoY revenue by quarter

原始指令

In Excel for Windows open fixtures/sales_model.xlsx (tables Sales, Products, Calendar). In Power Pivot Diagram View create relationships Sales[ProductKey]→Products and Sales[DateKey]→Calendar, mark Calendar as date table, add DAX measures Revenue and Revenue YoY %, and build a pivot by Calendar Year > Quarter and Products[Line].

平台: win · Windows
应用: Excel · Power Pivot
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击 type · 文字与表单 code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Model inspected with the grader's DAX/AMO reader: 2 active relationships with correct columns, Calendar marked as date table, measures evaluate to key for 12 probe filter contexts; pivot output equals key after refresh. VBA disabled; no COM host or Python in the VM.

建议部分得分

25% relationships; 10% date table; 35% measures correct; 30% pivot layout and values.

R04.03.01.02-T14 Reproduce a pivot report exactly from a reference screenshot

原始指令

input/target_pivot.png shows a management pivot report built from fixtures/claims.xlsx. Rebuild it on sheet 'Report': same fields and order, layout form, subtotal placement, grand-total settings, number formats, value filter, collapsed items and PivotStyle. Only the screenshot specifies the target.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击 vis · 视觉判断
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Grader compares pivotTableDefinition attributes to the reference spec (fields, order, outline/compact, subtotalTop, grand totals, numFmts, filter, sd flags, style name) and renders the sheet via Excel COM to PNG: SSIM ≥0.92 vs target. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

30% fields and order; 25% layout/subtotal/totals options; 20% formats, filter and collapse state; 25% rendered SSIM threshold.

R04.03.01.02-T15 Combine running-total and rank views and collapse all but one region

原始指令

In fixtures/subscriptions.xlsx pivot 'PT_MRR' (Region > Country rows, Month columns): add MRR twice, once as Running Total In Month shown as % (Running Total in %), once as Rank Largest to Smallest within Region. Collapse every region except EMEA, and set the pivot to keep column widths on update. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 drag · 拖放 hover · 悬停与上下文
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Two dataFields on MRR with showDataAs percentOfRunningTotal (baseField Month) and rankDescending (baseField Country within Region); region items sd=0 except EMEA; useAutoFormatting=0; refreshed values equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.

建议部分得分

35% running-total % field; 35% rank field with right base; 20% collapse state; 10% column width option.

R04.03.01.02-T16 Control pair of T01: create the same pivot through the Sheets API

原始指令

Control pair of T01. The same 18k-row sales data is in Google Sheet 'Sales 2025'. Using the Sheets API (service-account credentials provided), create a pivot table on new sheet 'Pivot' at A3 with Region rows, OrderDate grouped by quarter in columns and SUM of Revenue. No UI use needed.

平台: web · 任意系统（网页）
应用: Google Sheets · Sheets API
交互表面: api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: AP · API 足够
交互原语: code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

Sheets API read: pivotTable on 'Pivot'!A3 with rows Region, columns OrderDate with dateTimeRule QUARTER, value SUM Revenue; computed cells equal the T01 key.

建议部分得分

30% pivot created at location; 40% fields and quarter grouping; 30% values equal key.

R04.03.01.02-T17 Insert department subtotals and collapse the outline to level 2

原始指令

fixtures/cost_centres.xlsx 'Costs' is sorted by Department. Use Data > Subtotal to add Sum of Cost and Sum of Budget at each change in Department with a grand total, then collapse the outline to level 2 so only subtotals show, and set print titles to repeat row 1. Save.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

SUBTOTAL(9,…) rows inserted after each department with correct ranges; outline levels set and detail rows hidden (level 2 view); grand total row; print titles $1:$1; values equal key. openpyxl could emulate this, so the task tests the feature path, not exclusivity.

建议部分得分

40% subtotal rows correct; 15% grand total; 30% outline collapsed to level 2; 15% print titles.

R04.03.01.02-T18 Filter a Calc pivot by hidden items and group dates into Monday weeks

原始指令

In fixtures/web_sessions.ods create a pivot on 'Weekly': SessionDate grouped into 7-day intervals starting Monday 2025-06-02, Browser in rows sorted by Sum of Sessions descending, page fields Country (hide 'Unknown' and 'Bot') and Device = 'Mobile'. Save.

平台: mac · macOS win · Windows lin · Linux
应用: LibreOffice Calc
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: drag · 拖放 click · 指向与点击 type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

data-pilot grouping with step 7 days and start 2025-06-02; Browser sorted by data field desc; Country members 'Unknown' and 'Bot' hidden; Device page field 'Mobile'; output values equal key. Macros/UNO disabled.

建议部分得分

35% weekly grouping with correct start; 20% sort; 30% page-field filters incl. hidden members; 15% values.

R04.03.01.02-T19 Create and rearrange a pivot table in Numbers for iPad by touch

原始指令

On iPad, open 'Club Dues.numbers' in Numbers. Create a pivot table from table 'Payments': Member Type in rows, Month in columns, Sum of Amount. Then, by dragging in the pivot options, move Month to rows under Member Type, add Count of Payment ID, and sort Member Type by Sum of Amount descending.

平台: ios · iOS/iPadOS
应用: Numbers
交互表面: mob · 移动触控
GUI 需求 · 任务设定: G3 · 仅 GUI
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: touch · 触控手势 drag · 拖放
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

File synced via iCloud; grader exports to .xlsx on a Mac via AppleScript and checks pivot output headers, nesting, two value columns and order against key. Shortcuts is disabled on the agent's iPad and it has no Mac or iCloud web access; the file must be saved by Numbers for iPadOS.

建议部分得分

25% pivot created from 'Payments'; 30% Month nested under Member Type; 25% count field added; 20% sort order.

R04.03.01.02-T20 Consolidate four regional sheets with a multiple-range pivot

原始指令

fixtures/budget_regions.xlsx has sheets North, South, East, West with the same accounts in different row orders. Using the PivotTable and PivotChart Wizard (multiple consolidation ranges), build pivot 'PT_Consol' on 'Consolidated' with one page field whose items are named North/South/East/West, accounts in rows, months in columns, Sum of values.

平台: win · Windows
应用: Excel
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 key · 快捷键 drag · 拖放
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

pivotCacheDefinition consolidation source with 4 rangeSets and page item names North..West; rows = accounts, cols = months; values equal key; selecting page item 'East' (grader via COM) shows East-only totals. VBA disabled; no COM host or Python in the VM.

建议部分得分

30% 4 ranges consolidated; 25% page items named; 30% totals equal key; 15% per-region page filter works.

已有研究映射

以下沿用研究目录中的基准与小类映射，不是逐题标注、执行记录或产品背书。

SheetCopilot

数据透视与分组

任务提案 · 20

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

已有研究映射

同工作流族的小类