R04.03.01.01 · 任务小类

清洗、公式与查找

Cleaning, formulas & lookups

Sort, filter, transpose, fill, clean and normalize data in place, and write formulas and lookups that produce the right values — from single operations to whole-sheet fixes.

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

典型应用: Excel · Google Sheets · Numbers · LibreOffice Calc
小类 GUI 需求标签: AP · API 足够

任务提案 · 20

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

R04.03.01.01-T01 Split full names and normalize phone numbers in a customer sheet

原始指令

fixtures/customers.xlsx sheet 'Raw' has 1,200 rows. Split 'Full Name' (col B) into First and Last in C:D (last token is the surname; keep particles like 'van der' with the surname), trim stray spaces, and rewrite phones in F as '+1-XXX-XXX-XXXX'. Leave all other columns unchanged. Save in place.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单 code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

C:D and F compared cell-by-cell to the answer key (exact string match); all other cells hashed and must be unchanged. Accuracy threshold 100% for phones, ≥99% for names (particle edge cases).

建议部分得分

45% names split (pro-rata); 40% phones normalized (pro-rata); 15% untouched cells unchanged.

R04.03.01.01-T02 Coerce text amounts with currency symbols to numbers in Google Sheets

原始指令

In the Google Sheet 'Expense import Q2', column E 'Amount' holds a mix of numbers, text numbers ('1,204.50'), values with '$' or 'USD' and parentheses negatives ('(85.00)'). Convert every cell in E2:E842 to a true number with format #,##0.00 and negatives as minus values. Change nothing else.

平台: web · 任意系统（网页）
应用: Google Sheets
交互表面: brow · 浏览器 api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: type · 文字与表单 click · 指向与点击
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

Sheets API read: E2:E842 effectiveValue.numberValue equals key (abs tol 0.005); none are stringValue; numberFormat pattern '#,##0.00'; other ranges unchanged by hash.

建议部分得分

70% values numeric and correct (pro-rata); 15% parentheses negatives handled; 15% format applied and nothing else changed.

R04.03.01.01-T03 Remove duplicate AP invoices keeping the earliest posting

原始指令

fixtures/ap_raw.xlsx sheet 'AP_Raw' (500 rows) has duplicate invoices: same Invoice# and Amount, sometimes with different Posting Date or trailing spaces in Invoice#. Remove duplicates in place keeping the row with the earliest Posting Date, preserving the original order of surviving rows. Expected: 37 rows removed.

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

建议验证方式

Surviving rows compared to key as ordered tuples: exactly 463 data rows, correct survivor per duplicate group, original relative order kept, cell values unchanged (no trimming beyond matching).

建议部分得分

40% correct row count; 40% correct survivor per group; 20% order and values preserved.

R04.03.01.01-T04 Convert mixed-format text dates to real dates by source system

原始指令

fixtures/shipments.ods in LibreOffice Calc: column C 'Ship Date' holds text like '03/07/25', '07.03.2025', '2025-3-7' and Excel serials. Column B 'Source' says 'US' or 'EU'; slash dates follow the source's day/month order, dotted dates are always day.month. Replace C with real dates formatted YYYY-MM-DD. Save as .ods.

平台: mac · macOS win · Windows lin · Linux
应用: LibreOffice Calc
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单 code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

C2:C2001 are date-typed cells (office:date-value) equal to key; number format YYYY-MM-DD; ambiguous rows (both parts ≤12) checked separately; other columns unchanged.

建议部分得分

50% unambiguous dates correct; 30% ambiguous dates resolved by Source; 20% real date type + format.

R04.03.01.01-T05 Add live XLOOKUP region mapping with an UNMAPPED fallback

原始指令

In fixtures/sales_fy25.xlsx fill Sales!E2:E501 with formulas that look up each row's Country in Mapping!A:B and return Region; unmatched countries must return 'UNMAPPED'. Matching must ignore case and leading/trailing spaces. Values pasted as text do not count.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

openpyxl (data_only=False): every E cell is a formula. Grader makes 3 perturbed copies (new countries, edited mapping, spaces/case noise), recalculates in LibreOffice headless, and compares E to key on each copy.

建议部分得分

20% all cells are formulas; 50% correct on original data; 30% correct on 3 perturbations (equal shares).

R04.03.01.01-T06 Write marginal tiered commission formulas in Google Sheets

原始指令

In Google Sheet 'Commissions FY25', fill Commissions!C2:C41 with formulas computing each rep's commission from annual sales in B using the marginal tiers in Rates!A2:C4 (0–50k 5%, 50–100k 7%, >100k 9%). Formulas must reference the Rates table so tier changes flow through.

平台: web · 任意系统（网页）
应用: Google Sheets
交互表面: brow · 浏览器 api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

Sheets API: C2:C41 hold formulas referencing Rates!; values match key; grader then edits Rates (new thresholds, 4th tier row inside range) and B values via API and checks recomputed C against key twice (abs tol 0.01).

建议部分得分

20% formulas reference Rates; 40% correct values; 40% correct after 2 rate/sales perturbations.

R04.03.01.01-T07 Unpivot a 12-month wide sales table into long format from the terminal

原始指令

fixtures/units_wide.xlsx sheet 'Wide' has Product, Region and Jan..Dec columns (200 rows). Produce a new sheet 'Long' in the same workbook with columns Month (YYYY-MM for FY2025), Product, Region, Units: 2,400 rows ordered by Product, Region, Month. Blank month cells become 0. Use any CLI tooling.

平台: mac · macOS win · Windows lin · Linux
应用: Excel · pandas
交互表面: term · 终端
GUI 需求 · 任务设定: CL · CLI 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

'Long' sheet read with openpyxl: header exact, 2,400 rows, ordered tuple list equals key; Units numeric; 'Wide' sheet unchanged.

建议部分得分

25% correct header and row count; 50% values correct; 25% ordering and blanks-as-zero.

R04.03.01.01-T08 Compute fiscal quarter and fiscal week with live date formulas

原始指令

fixtures/orders_2025.xlsx Orders!B holds order dates. The fiscal year starts 1 February; fiscal weeks start Sunday and week 1 contains 1 February. Fill C with fiscal year label ('FY26' for Feb 2025–Jan 2026), D with fiscal quarter (1–4) and E with fiscal week (1–53), all as formulas.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

C:E are formulas; values equal key for 3,650 dates spanning 2024–2026 boundary cases; grader appends 50 edge dates (31 Jan, 1 Feb, leap day) and recalculates in LibreOffice headless.

建议部分得分

30% fiscal year; 30% fiscal quarter; 40% fiscal week incl. boundary dates.

R04.03.01.01-T09 Price shipments from a weight-band × zone rate card in Numbers

原始指令

Open fixtures/shipping.numbers in Numbers. Table 'Shipments' has Weight (kg) and Zone; table 'Rate Card' has weight-band lower bounds in rows and zones 1–8 in columns. Fill 'Shipments'::Cost with a formula that finds the band (≤ weight) and zone, then adds a 12% fuel surcharge rounded to cents.

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

建议验证方式

Grader AppleScript reads each Cost cell's formula and value: all formulas, values match key; then edits 3 rate-card cells and 5 weights and re-reads. Pasted constants fail. Numbers has no Python write path for formulas, so AppleScript or GUI entry is required.

建议部分得分

25% formulas present in all rows; 45% correct values; 30% correct after rate-card perturbation.

R04.03.01.01-T10 Parse free-text addresses into components with regex formulas

原始指令

Google Sheet 'Donor addresses' column A has 500 US addresses in 6 styles ('12 Elm St Apt 4, Springfield IL 62704', 'PO Box 77 / Dover, DE 19901'). Fill B:F (Street, Unit, City, State, ZIP5) with REGEXEXTRACT-based formulas. Blank Unit when absent; ZIP keeps leading zeros.

平台: web · 任意系统（网页）
应用: Google Sheets
交互表面: brow · 浏览器 api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

B:F hold formulas; field-level accuracy vs key ≥98% per column required for full credit; grader adds 50 held-out addresses in A via API and re-reads results (≥95% on held-out).

建议部分得分

15% all formulas; 75% component accuracy across 5 columns (equal shares, pro-rata); 10% held-out generalization.

R04.03.01.01-T11 Build a refreshable Power Query that cleans a folder of monthly CSVs

原始指令

In Excel on Windows, create a Power Query over folder fixtures/pos_exports/ (12 CSVs, inconsistent headers, a 3-line preamble, mixed decimal commas). Output table 'Sales_Clean' on sheet 'Clean' with Date, Store, SKU, Qty, Net typed correctly and rows with Qty=0 removed. Save as output/pos_clean.xlsx.

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

建议验证方式

Workbook must contain a DataMashup query bound to 'Sales_Clean' (openpyxl cannot author it). Grader adds a 13th CSV, refreshes via Excel COM, and compares the table to key (row count, typed values). A static pasted table fails the refresh check.

建议部分得分

25% query exists and loads to named table; 35% correct output on 12 files; 25% correct after refresh with new file; 15% types correct.

R04.03.01.01-T12 Fuzzy-match vendor names to a master list with Power Query merge

原始指令

fixtures/vendors.xlsx has 'Invoices' (900 rows, free-typed vendor names) and 'VendorMaster' (140 names with VendorID). In Excel, add a query that fuzzy-merges invoices to the master (similarity threshold 0.80, ignore case) and loads 'Invoices_Matched' with VendorID, leaving unmatched rows blank. Save.

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

建议验证方式

DataMashup query uses Table.FuzzyNestedJoin/FuzzyJoin with Threshold 0.8; after Excel COM refresh, VendorID precision ≥97% and recall ≥92% vs key; grader edits 5 master names and refreshes to confirm it is live.

建议部分得分

25% fuzzy merge query present; 40% precision/recall thresholds met; 20% refresh reflects edits; 15% unmatched left blank.

R04.03.01.01-T13 Convert multi-currency expenses using date-effective FX rates

原始指令

fixtures/travel_exp.xlsx: 'Expenses' has Date, Currency, Amount; 'FX' has daily rates to EUR with weekend gaps. Fill Expenses!E with formulas returning EUR amounts using the most recent rate on or before each date (same-currency EUR = 1), rounded to 2 decimals. Missing rates must show 'NO RATE'.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 4 / 5

建议验证方式

E cells are formulas; values equal key (tol 0.005); grader perturbs FX (deletes 4 dates, adds a currency) in 3 copies, recalculates in LibreOffice headless and compares. Constant values fail perturbation.

建议部分得分

20% formulas; 40% correct EUR values; 20% NO RATE handling; 20% correct on perturbations.

R04.03.01.01-T14 Restore SKU codes mangled into scientific notation from the source CSV

原始指令

fixtures/catalog.ods was built by opening catalog_source.csv with defaults: 18-digit SKUs became 1.23457E+17 and leading zeros vanished. Re-import column A from the CSV as text into the existing sheet so all 3,200 SKUs match the source exactly, without disturbing columns B:H.

平台: mac · macOS win · Windows lin · Linux
应用: LibreOffice Calc · csvkit
交互表面: term · 终端 desk · 桌面 GUI
GUI 需求 · 任务设定: CL · CLI 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 3 / 5

建议验证方式

Column A cells are string-typed and equal source strings exactly (leading zeros, 18 digits); B:H hashes unchanged; row alignment verified by a checksum column in H.

建议部分得分

60% SKUs exact (pro-rata); 20% stored as text; 20% other columns and row alignment intact.

R04.03.01.01-T15 Build a single-formula cohort retention matrix with LET and LAMBDA

原始指令

fixtures/orders_raw.xlsx 'Orders' has 48k rows (CustomerID, OrderDate). On sheet 'Cohorts' A1, write one dynamic-array formula (LET/LAMBDA/MAKEARRAY allowed) that spills a matrix: rows = first-order month (2024-01..2025-06), columns = months since first order (0–17), values = % of cohort active, 1 decimal.

平台: mac · macOS win · Windows
应用: Excel
交互表面: desk · 桌面 GUI api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单 code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Cohorts!A1 contains the only formula on the sheet; Excel COM recalc spills a 18×18 (+headers) array equal to key (tol 0.05 pp); grader appends 2,000 synthetic orders and recalcs to confirm it updates.

建议部分得分

20% single spilled formula; 20% correct cohort/period axes; 40% values within tolerance; 20% updates after appended orders.

R04.03.01.01-T16 Match two customer lists with formula-only fuzzy normalization

原始指令

Google Sheet 'CRM merge' has tabs 'CRM' (2,100 accounts) and 'Billing' (1,950). In CRM!H write formulas producing a normalized key (lowercase, strip punctuation, drop Inc/LLC/Ltd/GmbH, collapse spaces) and in CRM!I the matching Billing AccountID or 'NEW'. No Apps Script; formulas only.

平台: web · 任意系统（网页）
应用: Google Sheets
交互表面: brow · 浏览器 api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Sheets API: H and I are formulas, no bound Apps Script project; I accuracy vs key ≥99%; grader inserts 30 new Billing rows with suffix variants and checks updated matches ≥97%.

建议部分得分

25% normalization key matches key column; 45% match accuracy threshold; 20% generalization on inserted rows; 10% formulas only.

R04.03.01.01-T17 Score a messy survey export with lookup tables across Numbers tables

原始指令

fixtures/staff_survey.numbers has table 'Responses' (Likert answers as text with typos like 'Agre'), 'Scale' (text→1–5, with typo variants) and 'Items' (item, subscale, reversed Y/N). Add formula columns to 'Responses' for each of 4 subscale means using the lookup tables, with reversed items coded 6−x. Keep it live.

平台: mac · macOS
应用: Numbers
交互表面: desk · 桌面 GUI
GUI 需求 · 任务设定: HY · 混合
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: type · 文字与表单 click · 指向与点击 nav · 平移 / 缩放 / 滚动
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

Grader AppleScript reads formulas/values in the 4 new columns: all formulas; means equal key (tol 0.01); then flips one item's 'reversed' flag and edits 10 responses and re-reads. Constants or a separate pasted table fail.

建议部分得分

20% formula columns present; 20% text-to-score lookup incl. typos; 20% reverse coding; 40% subscale means correct incl. after perturbation.

R04.03.01.01-T18 Delete rows that conditional formatting currently shows in red

原始指令

fixtures/stock_review.xlsx 'Stock' has 5 overlapping conditional-format rules (one with Stop If True) that color some rows red. Delete exactly the rows whose rendered row fill is red, keep all others in order, and keep the conditional-format rules themselves. Save in place.

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

建议验证方式

Remaining rows equal key (ordered IDs); CF rules still present and unchanged. Red state is computed only at render time (openpyxl cannot evaluate CF); the key was produced by evaluating rules in Excel COM.

建议部分得分

60% correct rows removed (precision and recall); 20% order kept; 20% CF rules preserved.

R04.03.01.01-T19 Map country names to ISO codes with a lookup and fallback

原始指令

In Google Sheet 'Leads export', column D has free-typed countries ('U.S.A.', 'Deutschland', 'UK'). Using the provided tab 'ISO' (names and aliases → alpha-2), fill E2:E1500 with formulas returning the ISO alpha-2 code, or 'XX' when no alias matches. Do not edit the ISO tab.

平台: web · 任意系统（网页）
应用: Google Sheets
交互表面: brow · 浏览器 api · API / SDK
GUI 需求 · 任务设定: AP · API 足够
GUI 需求 · 普通权限: G2 · GUI 必要
交互原语: type · 文字与表单
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 2 / 5

建议验证方式

Sheets API: E2:E1500 are formulas; values equal key; ISO tab hash unchanged; grader adds 2 aliases to ISO and confirms affected rows update.

建议部分得分

20% formulas present; 60% correct codes (pro-rata); 20% fallback and live update.

R04.03.01.01-T20 Flatten a paginated report export into a tidy table with carried keys

原始指令

fixtures/gl_report.xlsx is a printed-report export: repeated page headers every 50 rows, merged Region/Period block headers, subtotal and blank rows. Produce sheet 'Clean' with columns Region, Period, Account, Description, Debit, Credit: exactly 4,812 detail rows, block headers carried down, no subtotals.

平台: mac · macOS win · Windows lin · Linux
应用: Excel · pandas · openpyxl
交互表面: term · 终端
GUI 需求 · 任务设定: CL · CLI 足够
GUI 需求 · 普通权限: CL · CLI 足够
交互原语: code · 代码与命令
工作产物: WP02 · 记录 / 表格
条件: 未声明
提案难度: 5 / 5

建议验证方式

'Clean' read with openpyxl: header exact; 4,812 rows; multiset of row tuples equals key; Debit/Credit numeric; sum of Debit equals report grand total to the cent; original sheet unchanged.

建议部分得分

20% header + row count; 30% carried Region/Period correct; 30% detail values match; 20% totals reconcile.

已有研究映射

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

清洗、公式与查找

任务提案 · 20

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

原始指令

建议验证方式

建议部分得分

已有研究映射

同工作流族的小类