返回提示词目录第 1358 页

电子表格报表迁移与对账规划

梳理现有工作簿的数据、公式和人工步骤,设计商业智能报表迁移方案及并行对账与切换流程。

完整提示词

<context>
An Excel report pack mixes three things in the same tabs: data preparation (pasted exports, lookups, cleanup), calculation (SUMIFS grids, ratios, year-to-date formulas) and presentation (a laid-out page someone prints or screenshots). Migrations that copy the tabs one for one into Power BI end up slow and fragile. Migrations that work separate the layers: raw data and lookups go to Power Query or the source; row-level logic goes to Power Query or the source rather than DAX calculated columns; aggregations become measures, with ratios built as a ratio of sums rather than an average of row ratios; year-to-date and prior-period formulas become time intelligence on a proper date table. The hidden risks are the manual steps: values typed over formulas, a monthly adjustment pasted in, a mapping of products to categories kept in someone's head or a hidden tab. Each must become a governed table with an owner, or the new report will quietly disagree with the old one. Cell-by-cell layouts such as a management P&L need a layout table to control row order and subtotals. Users will compare the new numbers with the old ones on day one, so parity must be proven for at least two cycles before the old pack stops, and differences that turn out to be errors in the old workbook must be documented, not hidden.
</context>

<task>
<workbook>
{{workbook}}
</workbook>
{{#constraints}}

<constraints_given>
{{constraints}}
</constraints_given>
{{/constraints}}

1. Inventory the workbook: each tab with its role (input, preparation, calculation, output), data sources, refresh method, and the outputs people actually use. Mark outputs nobody seems to use as candidates to drop, to confirm with users.
2. Propose the target design: source connections, Power Query steps, the tables of a star-shaped model (facts, dimensions, a date table), mapping and adjustment tables, and the report pages that replace the outputs.
3. Map formulas: for each important Excel calculation, where it goes (source, Power Query, measure) and the measure logic in words or short DAX, flagging any Excel logic whose result differs by design (averages of ratios, hard-coded cells, circular references).
4. Turn every manual step into a controlled mechanism: an adjustments table with amount, reason, author and period; a mapping table with an owner; or an automated rule. List any step that cannot be automated and who will do it.
5. Write a parity test plan: which figures to compare (grand totals, each output total, breakdowns by the main dimensions, a few individual rows), the tolerance, the number of cycles to run in parallel (at least two), how to record differences, and how to decide whether a difference is an Excel error or a migration error.
6. Plan cutover: sign-off criteria, communication, how users who need Excel get it (for example Analyze in Excel or a governed export), archiving the old workbook, and a rollback option.
7. List risks with mitigations: data access and gateway, licence limits, key-person knowledge, and timeline.
</task>

<constraints>
- Base the inventory on the workbook description; do not invent tabs, sources or formulas. Where the description is thin, list what you need to see.
- Keep DAX and Power Query snippets short and illustrative; say they need testing against the real model.
- Do not plan to switch off the Excel pack before parity sign-off.
- Respect the licence and access constraints given; flag features that need a higher licence.
</constraints>

<output_format>
## Inventory
Table: tab | role | source | refresh | used by | keep or drop.

## Target design
Sources, Power Query, model tables, report pages.

## Formula mapping
Table: Excel calculation | where it goes | logic | note on differences.

## Manual steps
Table: manual step | new mechanism | owner.

## Parity test plan
Table: check | Excel figure | Power BI figure | tolerance | cycles. Then the difference log format.

## Cutover
Checklist.

## Risks
Table: risk | mitigation.
</output_format>

可替换内容

  • {{workbook}}
  • {{#constraints}}
  • {{constraints}}
  • {{/constraints}}

分类与标签

办公效率、报表迁移、公式映射、数据建模、并行对账、切换规划

前往 AI 图片创作