Combine similar Excel worksheets safely

작성자: AILesson8 분 소요테스트::ChatGPT검토일: 2026-08-28

빠른 답변

Append recurring sheets after auditing schema drift, provenance, duplicates, types, and totals. 제공할 내용: Workbook sheets and schemas, Combined schema and record identity, Refresh and validation rules. 예상 결과: A refreshable consolidation plan with schema mapping, source lineage, and reconciliation controls.

맥락 추가

텍스트는 이 브라우저에 유지됩니다. AILesson Prompts는 이를 모델이나 서버로 보내지 않습니다.

Workbook sheets and schemasCombined schema and record identityRefresh and validation rules

프롬프트

채워지지 않은 필드는 플레이스홀더로 표시되므로 프롬프트를 복사하고 편집할 수 있습니다

Design a safe, refreshable method to combine the similar Excel worksheets.

Sheets, inclusion, headers, types, ranges, samples, totals, notes, and known differences:
[sheets]

Target columns, grain, key, provenance, duplicates, mappings, and missing-column behavior:
[target]

Excel version, new-sheet pattern, approval, method, errors, privacy, reconciliations, and archives:
[refresh]

Inventory and compare schemas before appending. Normalize headers through an explicit mapping rather than fuzzy similarity, distinguish missing columns from blank values, and prevent title rows, subtotals, totals, footnotes, hidden helper ranges, and the output sheet from becoming data. Add source lineage before combination. Define type conversion and error quarantine; do not silently coerce identifiers, dates, or amounts. Check duplicate natural keys both within and across sheets and apply only the supplied resolution rule. Prefer Power Query for repeatable refresh and avoid selecting sheets solely by a broad name pattern without an allowlist or schema gate. Return: inclusion/exclusion manifest; schema-drift report; canonical mapping; staged Power Query procedure and key operations; provenance and duplicate controls; row/count/amount reconciliation by source; error quarantine; refresh checklist; sample combined rows; and stop conditions.

Playground에서 사용해 보기

기본적으로 비공개프롬프트 구성은 브라우저에서 로컬로 이루어집니다. 조직에서 허용하지 않는 한 기밀 정보를 AI 서비스에 입력하지 마세요.

입력에서 결과까지

적용 예시

구체적인 맥락이 이 레시피를 바로 사용할 수 있는 결과로 바꾸는 방법을 확인하세요

실제 입력

Workbook sheets and schemas: Workbook has Excel Tables Sales_Jan on sheet Jan, Sales_Feb on Feb, Sales_Mar on Mar, plus Summary and Lookup sheets. Jan columns Order ID text, Order Date date, Region text, Net Sales decimal; 102 data rows, sum 48,320.50. Feb uses OrderID, Date, Region, Net Sales; 98 rows, sum 46,105.00. Mar adds Sales Rep between Region and Net Sales; 110 rows, sum 52,880.25. Each table has no total row. Summary must be excluded. Order IDs have leading zeroes. One Feb date cell contains text ‘TBD’. Sample duplicate: order 00481 appears in Jan and Feb because Feb corrected the amount.
Combined schema and record identity: Canonical columns Order ID, Order Date, Region, Sales Rep, Net Sales, Source Sheet, Source Table, Source Row. Grain is one current record per Order ID. Explicit mappings OrderID -> Order ID and Date -> Order Date. Missing Sales Rep in Jan/Feb remains null. Cross-sheet duplicate: later month wins only when a supplied Corrections table lists that Order ID and replacement month; otherwise quarantine both. Keep original source rows for audit.
Refresh and validation rules: Microsoft 365, Power Query. Approved source tables are Sales_Jan, Sales_Feb, Sales_Mar; future Sales_Apr etc require schema validation and explicit addition to allowlist. Type errors go to an Errors query, not dropped. Validate listed source counts/sums and combined counts. Output table is ConsolidatedSales and must never be read as input. No external files or personal customer data.

예시 출력

Manifest: include only Sales_Jan, Sales_Feb, Sales_Mar; exclude Summary, Lookup, ConsolidatedSales, and every future table until approved. Drift: Feb renames two columns; Mar adds optional Sales Rep; Feb has one Order Date type error. Canonical mapping is exact: OrderID→Order ID, Date→Order Date; all other headers must match. Missing Sales Rep becomes typed null, not an empty string.

Power Query staging: create one query per approved table with Source Sheet/Table and an index-based Source Row added before transformation. Rename from the mapping record, add missing Sales Rep as nullable text, select canonical columns, and apply types while retaining a pre-type copy or Try-based record for error capture. Append only the three staged valid-row queries. Build Errors from rows where date or amount conversion fails; the Feb TBD row appears there with its raw value and lineage. Do not delete it silently. Build DuplicateAudit by grouping Order ID and keeping all nested rows. Join duplicates to Corrections; select a replacement only when the listed month/table matches, otherwise exclude both from CurrentSales and keep them in quarantine.

Reconciliation before duplicate resolution: Jan 102 / 48,320.50; Feb 98 / 46,105.00 subject to the quarantined TBD date but Net Sales still traceable; Mar 110 / 52,880.25; appended raw count 310 and raw sum 147,305.75. Reconcile valid, error, duplicate-quarantine, and final-current row counts back to 310 with mutually exclusive statuses. Stop refresh on an unknown header, missing required column, unapproved source table, amount conversion error without quarantine, unlisted duplicate, or source count/total mismatch. A future April table is not auto-included.

효과가 있는 이유

1
Schema gating prevents a similarly named summary or changed sheet from entering the dataset unnoticed.
2
Per-source reconciliation makes dropped rows and duplicate ingestion traceable to one sheet.

결과 확인

Is every included sheet approved by name and canonical schema rather than name pattern alone?
Are provenance, missing columns, type errors, and duplicates visible after refresh?
Do row counts and material totals reconcile for every source sheet and the combined result?

안심하고 사용하세요

자주 묻는 질문

이 레시피를 언제 사용해야 하는지, 무엇을 제공해야 하는지, 그리고 어떤 부분에서 사람의 검토가 여전히 중요한지에 대한 실용적인 답변

What should I prepare before using “Combine similar Excel worksheets safely”?

For “Combine similar Excel worksheets safely,” prepare Workbook sheets and schemas, Combined schema and record identity, and Refresh and validation rules. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Combine similar Excel worksheets safely” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A refreshable consolidation plan with schema mapping, source lineage, and reconciliation controls—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Combine similar Excel worksheets safely”?

The published test record for “Combine similar Excel worksheets safely” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

더 많은 탐색 방법