R04.03.01.06 · 任务小类

表格宏与脚本

Spreadsheet macros & scripts

Write and fix VBA, Office Scripts or Apps Script that automate workbook tasks correctly.

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

典型应用: Excel (VBA, Office Scripts) · Google Sheets (Apps Script) · LibreOffice Basic
小类 GUI 需求标签: HY · 混合

任务提案 · 20

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

R04.03.01.06-T01 Fix a VBA consolidation macro that drops rows

原始指令

fixtures/monthly_ops.xlsm has macro ConsolidateMonths that copies 12 monthly sheets into 'Summary', but it skips each sheet's last row and stops at the first blank row. Fix it so all data rows are copied (blank rows inside data skipped, not terminating), headers once, and a 'Month' column added. Save as out/monthly_ops.xlsm.

平台: win · Windows mac · macOS
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令 type · 文字与表单
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader runs ConsolidateMonths via COM on the saved file and on 2 hidden workbooks (different row counts, blank rows, a 13th non-month sheet to ignore); Summary must equal the expected table exactly (values + Month column).

建议部分得分

40% all rows copied on fixture; 20% blank rows handled; 15% Month column; 25% hidden workbooks pass

R04.03.01.06-T02 Office Script to flag and sort overdue tasks

原始指令

In Excel for the web (test M365 tenant), write an Office Script 'FlagOverdue' saved to OneDrive that, on the active workbook's table 'Tasks', fills rows whose Due < today and Status <> 'Done' with #FFC7CE, sorts by Due ascending, and adds a total row counting overdue items. Run it on fixtures/tasks.xlsx.

平台: web · 任意系统（网页）
应用: Excel (Office Scripts)
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令 type · 文字与表单 click · 指向与点击
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader runs the saved .osts via Power Automate 'Run script' on tasks.xlsx and 2 hidden workbooks with a frozen date: fills on exactly the overdue rows, sort order, total-row formula result; fixture workbook already processed.

建议部分得分

40% correct rows highlighted; 25% sorted; 20% total row count; 15% script saved and runnable

R04.03.01.06-T03 Apps Script custom function and edit-timestamp trigger

原始指令

In the test Google Sheet 'Leave Tracker', add Apps Script: a custom function WORKDAYS_BETWEEN(start, end, holidays) that counts Mon-Fri days inclusive excluding a holiday range, and an onEdit trigger that writes the current timestamp to column D when column C of sheet 'Requests' changes (not on other sheets/columns).

平台: web · 任意系统（网页）
应用: Google Sheets (Apps Script)
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令 type · 文字与表单
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader (Sheets API as owner) writes 12 test cases into cells using WORKDAYS_BETWEEN and compares results; edits C5 on Requests (D5 timestamp within 60 s), edits B5 and C5 on another sheet (no timestamp).

建议部分得分

50% custom function correct on all cases; 35% trigger fires on correct column only; 15% no errors in executions log

R04.03.01.06-T04 LibreOffice Basic: export every sheet to UTF-8 CSV

原始指令

In fixtures/regions.ods, write a LibreOffice Basic macro ExportSheetsCSV in the document's Standard library that saves each sheet as <sheetname>.csv (UTF-8, comma, double-quoted text, formulas as values) next to the file, and bind it to a toolbar button labelled 'Export CSVs'. Save the document.

平台: lin · Linux win · Windows
应用: LibreOffice Calc (Basic)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令 click · 指向与点击
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader runs the macro headless (soffice macro:///) on the saved file and on a hidden .ods with non-ASCII sheet names: CSV byte-compare with expected (encoding, quoting); toolbar button with that label in the document's toolbar config (Configurations2).

建议部分得分

55% CSV output exact; 20% hidden file with unicode names; 25% toolbar button bound

R04.03.01.06-T05 VBA UserForm for validated order entry

原始指令

In fixtures/orders.xlsm design a UserForm 'frmOrder' with fields Date (validated dd/mm/yyyy), Customer (combo from named range Customers), Qty (integer 1-999) and Notes, plus Save and Cancel. Save appends a row to table tblOrders; invalid input shows a message and saves nothing. Add a sheet button 'New order' that opens it.

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

建议验证方式

Grader drives the form via UI Automation: 3 valid and 4 invalid entries; tblOrders gains exactly 3 correct rows; combo items equal Customers. Trust access to the VBA project object model is disabled by policy (AccessVBOM=0), so code/forms cannot be injected via COM; vbaProject.bin provenance must be EXCEL.EXE (Sysmon).

建议部分得分

30% form controls and combo source; 35% valid saves exact; 25% invalid input rejected; 10% sheet button opens form

R04.03.01.06-T06 Speed up a slow VBA macro on 200k rows

原始指令

fixtures/ledger.xlsm macro TagTransactions loops cell by cell over 200,000 rows and takes ~10 minutes. Rewrite it with array reads/writes (and screen updating/calculation off during the run, restored after) so it finishes in under 5 seconds and produces identical output.

平台: win · Windows
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 4 / 5

建议验证方式

Grader runs the original and new macro via COM on a hidden 200k-row copy: outputs (values and fills) identical; new runtime < 5 s on the grader VM; Application.ScreenUpdating and Calculation restored to original afterwards (also after an injected error).

建议部分得分

40% identical output; 40% runtime under 5 s; 20% app state restored even on error

R04.03.01.06-T07 Apps Script daily digest trigger and custom menu

原始指令

In the test Google Sheet 'Sales Log', write Apps Script that (a) adds a menu 'Reports > Send digest now' and (b) installs a time-driven trigger running sendDigest daily at 07:00 in the sheet's timezone. sendDigest emails (via MailApp to the test inbox in fixtures/inbox.txt) yesterday's total and top 3 reps.

平台: web · 任意系统（网页）
应用: Google Sheets (Apps Script) · Gmail
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: AP · API 足够
交互原语: code · 代码与命令 click · 指向与点击
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader: ScriptApp triggers list (via Apps Script API as owner) has one daily trigger at hour 7 for sendDigest; menu present after reload; grader invokes sendDigest with a frozen date and the sandbox inbox receives a mail with exact total and top-3 list.

建议部分得分

30% trigger configured exactly; 20% menu works; 50% digest content correct

R04.03.01.06-T08 Lock shipped rows with an audited Worksheet_Change handler

原始指令

In fixtures/fulfilment.xlsm add a Worksheet_Change handler on sheet Orders that undoes any edit to a row whose Status is 'Shipped' (except the Notes column) and logs every accepted change (timestamp, user, cell, old value, new value) to sheet Audit. Multi-cell pastes must be handled.

平台: win · Windows
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 4 / 5

建议验证方式

Grader scripts 10 edits through COM with events on (incl. a 3x2 paste spanning shipped and open rows): shipped cells revert, Notes edits allowed, Audit rows equal expected (old/new values exact), no recursion errors.

建议部分得分

40% shipped rows protected; 15% Notes exception; 30% audit log exact; 15% multi-cell paste handled

R04.03.01.06-T09 Port a VBA report macro to Office Scripts

原始指令

fixtures/region_report.xlsm has VBA macro BuildRegionReport. Port it to an Office Script 'BuildRegionReport' (Excel for the web, test tenant) that produces the same 'Report' sheet (values, number formats, conditional formatting) from the 'Data' sheet, and save it to OneDrive.

平台: web · 任意系统（网页） win · Windows
应用: Excel (Office Scripts) · Excel (VBA)
交互表面: brow · 浏览器 desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: HY · 混合
交互原语: code · 代码与命令 multi · 跨应用
工作产物: WP06 · 代码 / 工程
条件: multiapp · 多应用
提案难度: 4 / 5

建议验证方式

Grader runs the VBA via COM and the Office Script via Power Automate on 3 hidden workbooks; Report sheets compared cell by cell (values, number formats) and conditional-format rules equal.

建议部分得分

50% values identical on all workbooks; 25% number formats; 25% conditional formatting

R04.03.01.06-T10 Make an Apps Script survive the 6-minute limit

原始指令

The bound script in the test sheet 'Inventory Sync' processes 60,000 rows and times out. Refactor processAll to use batched getValues/setValues and resume from a checkpoint saved in PropertiesService via a chained time-based trigger, so a full run completes and cleans up its triggers. Output must match the original logic.

平台: web · 任意系统（网页）
应用: Google Sheets (Apps Script)
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 5 / 5

建议验证方式

Grader starts processAll on a fresh 60k-row copy: no execution exceeds 6 min (executions log), the run finishes within 30 min, output column equals expected for all rows, no leftover triggers or checkpoint properties at the end.

建议部分得分

30% batched I/O; 35% checkpoint and resume work; 25% output exact; 10% triggers cleaned up

R04.03.01.06-T11 LibreOffice Basic macro to highlight cross-column duplicates

原始指令

In fixtures/contacts.ods write a Basic macro MarkDupes that highlights (background #FFEB9C) every cell in column A whose value also appears in column C, comparing case-insensitively and ignoring leading/trailing spaces. Run it and save.

平台: lin · Linux win · Windows mac · macOS
应用: LibreOffice Calc (Basic)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 2 / 5

建议验证方式

Saved file: exactly the 14 expected A cells have background 0xFFEB9C; grader re-runs the macro headless on a hidden file and checks the highlighted set.

建议部分得分

60% correct cells highlighted in the fixture; 40% hidden file passes

R04.03.01.06-T12 Robust VBA CSV importer with error log

原始指令

fixtures/importer.xlsm macro ImportFolder reads every CSV in a chosen folder into sheet Data but crashes on malformed files. Add error handling so malformed files (wrong column count, bad dates) are skipped and listed on sheet 'Import Log' with the reason, valid files are imported, and a summary MsgBox is suppressed when run with argument silent:=True.

平台: win · Windows mac · macOS
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader runs ImportFolder silent on a hidden folder of 9 CSVs (3 malformed): Data equals the 6 valid files concatenated; Import Log lists the 3 with correct reason codes; no runtime error dialogs (COM call returns).

建议部分得分

40% valid files imported exactly; 35% malformed files logged with reasons; 25% silent mode works

R04.03.01.06-T13 Record a macro and put it on the Quick Access Toolbar

原始指令

In Excel, use the macro recorder to record 'FormatReport' (bold header row, freeze top row, autofit columns, apply currency format to column E) into the Personal Macro Workbook with relative references so it works on any sheet. Then add it to the Quick Access Toolbar with the 'table' icon.

平台: win · Windows
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: G2 · GUI 必要
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: click · 指向与点击 key · 快捷键
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 2 / 5

建议验证方式

PERSONAL.XLSB contains FormatReport; grader runs it on 2 hidden sheets and checks formatting; Excel.officeUI QAT entry references PERSONAL.XLSB!FormatReport (GUI-only property: written by Excel's customization dialog). AccessVBOM=0 blocks COM code injection; Sysmon detects officeUI writes by non-Excel processes -> 0.

建议部分得分

50% macro works on hidden sheets; 20% stored in Personal workbook; 30% QAT button present

R04.03.01.06-T14 Apps Script sidebar that builds a regional summary

原始指令

In the test sheet 'Field Sales', build an Apps Script HTML sidebar (menu 'Tools > Region summary') with a region dropdown populated from the Regions sheet and a 'Build' button that creates/overwrites a sheet 'Summary_<region>' with totals by product and month.

平台: web · 任意系统（网页）
应用: Google Sheets (Apps Script)
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令 click · 指向与点击
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 3 / 5

建议验证方式

Grader opens the sidebar via a browser driver, picks 2 regions and clicks Build: resulting sheets equal expected pivots; server function also called directly through the Apps Script API for a third region; dropdown options equal Regions list.

建议部分得分

25% sidebar and menu; 15% dropdown populated; 60% summaries correct for 3 regions

R04.03.01.06-T15 VBA macro that builds a pivot table from a table

原始指令

In fixtures/sales.xlsm write macro BuildPivot that creates (or rebuilds) a PivotTable on sheet 'Pivot' from table tblSales: rows Region then Rep, columns Quarter, values Sum of Revenue (format #,##0) and Count of OrderID, with a slicer on Channel. openpyxl cannot do this, so it must be a real PivotCache.

平台: win · Windows
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 4 / 5

建议验证方式

Grader runs BuildPivot twice via COM (idempotence) on the file and a hidden variant: one PivotTable whose pivotTableDefinition XML has the field layout and number format; values equal expected; one slicer bound to Channel.

建议部分得分

40% pivot layout exact; 30% values and formats; 15% slicer; 15% re-run idempotent

R04.03.01.06-T16 Analyze and neutralize a suspicious inherited macro

原始指令

fixtures/vendor_invoice.xlsm came from an old vendor. Without enabling or running macros, determine what its Workbook_Open/Auto_Open code does, write ~/out/macro_report.md listing every URL, file path and shell command it uses, and save a clean macro-free copy out/vendor_invoice.xlsx with the same cell values.

平台: lin · Linux win · Windows
应用: oletools (olevba) · Excel
交互表面: term · 终端 desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令 click · 指向与点击
工作产物: WP01 · 文档
条件: 未声明
提案难度: 3 / 5

建议验证方式

Report lists the 2 URLs, 1 dropped file path and the PowerShell command (exact strings, deobfuscated from Chr()/StrReverse); grader sandbox telemetry shows the macro never executed; xlsx has no vbaProject part and identical cell values.

建议部分得分

50% indicators extracted exactly; 20% macro never executed; 30% clean copy correct

R04.03.01.06-T17 Port an Excel VBA macro to native LibreOffice Basic

原始指令

Port macro CleanAddresses from fixtures/addresses.xlsm to LibreOffice Basic using the UNO API (no 'Option VBASupport 1'), stored in fixtures/addresses.ods, so it trims, proper-cases street names, normalizes 9 US state names to codes and splits ZIP+4. Output must equal the Excel version's.

平台: lin · Linux win · Windows
应用: LibreOffice Calc (Basic) · Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令 multi · 跨应用
工作产物: WP06 · 代码 / 工程
条件: multiapp · 多应用
提案难度: 4 / 5

建议验证方式

Grader runs the Excel macro (COM) and the Basic macro (headless soffice) on 2 hidden datasets: outputs identical; Basic module contains no VBASupport option and uses ThisComponent/UNO calls.

建议部分得分

30% trim and case; 30% state normalization; 25% ZIP split; 15% no VBA-compat mode

R04.03.01.06-T18 Generate 50 PDF invoices from a template sheet with VBA

原始指令

In fixtures/billing.xlsm write macro MakeInvoices that, for each row of tblClients, fills sheet 'Template' (name, address, line items from tblLines by client id, totals with 8.25% tax), exports it as out/invoices/INV-<id>-<yyyymm>.pdf, and restores the template afterwards.

平台: win · Windows mac · macOS
应用: Excel (VBA)
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 4 / 5

建议验证方式

Grader runs MakeInvoices via COM: 50 PDFs with exact names; pdftotext of each contains the right name, line count and total (to the cent); Template sheet equals its original after the run.

建议部分得分

25% 50 files named correctly; 50% invoice contents and totals correct; 25% template restored

R04.03.01.06-T19 Minimal OAuth scopes for an Apps Script project

原始指令

The Apps Script bound to the test sheet 'Team Roster' requests full Drive and Gmail scopes but only reads this spreadsheet and sends one email. Set explicit oauthScopes in appsscript.json to the minimum needed, switch DriveApp usage to SpreadsheetApp.getActive(), and keep the 'Notify' menu working.

平台: web · 任意系统（网页）
应用: Google Sheets (Apps Script)
交互表面: brow · 浏览器
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: code · 代码与命令
工作产物: WP06 · 代码 / 工程
条件: 未声明
提案难度: 2 / 5

建议验证方式

Manifest oauthScopes exactly {spreadsheets.currentonly, script.send_mail, script.container.ui}; no DriveApp/GmailApp references remain; grader authorizes as a test user and runs Notify: email arrives in the sandbox inbox and roster unchanged.

建议部分得分

45% scope set exact; 25% Drive/Gmail calls removed; 30% Notify still works

R04.03.01.06-T20 Excel add-in with a custom ribbon tab

原始指令

Package the macros in fixtures/tools.xlsm as an add-in out/TeamTools.xlam with a custom ribbon tab 'Team' (group 'Clean', buttons 'Trim cells' and 'Fix dates' with callbacks), install the add-in in Excel, and confirm both buttons work on fixtures/messy.xlsx.

平台: win · Windows
应用: Excel (VBA) · Office RibbonX Editor
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令 click · 指向与点击 multi · 跨应用
工作产物: WP06 · 代码 / 工程
条件: multiapp · 多应用
提案难度: 5 / 5

建议验证方式

xlam contains customUI14.xml with tab Team, group Clean, 2 buttons whose onAction callbacks exist in the VBA project; Excel registry Add-ins entry (OPEN key) points at it; grader clicks both buttons via UI Automation on a hidden messy file and checks results.

建议部分得分

30% xlam with valid ribbon XML; 20% callbacks exist; 15% installed; 35% both buttons work

已有研究映射

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

表格宏与脚本

任务提案 · 20

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

已有研究映射

同工作流族的小类