很多人想装光伏（自家屋顶、工商业厂房、农光互补），但一算账就头大：容量怎么定？发电量多少？多少年回本？IRR 和 NPV 又是什么？网上模板要么太简陋、要么收费几百块。我用 AI 配合 Python，一次性生成了一个会"动态联动"的专业测算 Excel——改一个参数，全表重算。下面把完整做法、提示词、踩坑优化和成品全部公开。
一、成品长什么样？（先看结果，再讲过程）
这个表一共 6 个工作表，覆盖了从"我想装多大"到"25 年后赚多少"的全链路：
| |
|---|
| |
| |
| 容量 / 发电量 / 投资额 / 回收期 / IRR / NPV |
| |
| |
| |
📊 真实跑一遍（北京·5000㎡ 工商业屋顶）：推荐安装容量 1,000 kWp ｜ 首年发电量 116.80万 kWh总投资 350.00万（贷款 70%）｜ 首年净现金流 20.41万投资回收期 5.27 年 ｜ IRR 19.42% ｜ NPV 141.21万
▲ 截图：计算结果工作表（所有指标由 Excel 公式自动算）
二、我是怎么做出来的？（工具与思路）
核心思路就三步：① 列清楚要哪些输入参数 → ② 把财务/工程公式写成 Excel 公式 → ③ 用脚本批量生成带样式、带数据验证、带条件格式的 xlsx。
工具链：AI 助手（WorkBuddy） 负责理解需求、写提示词、调试；底层用 Python + openpyxl 真正生成 .xlsx 文件。AI 帮我把"衰减逐年递减""等额本息还款""IRR 现金流序列"这些容易算错的地方一次性写对。
关键设计点
- 地区下拉联动：选城市 → VLOOKUP 自动带出日照时数、辐射量，不用手填。
- 动态联动：所有输出全是公式引用输入单元格，改任一参数全表秒重算。
- 衰减模型：第 n 年发电量 = 首年 ×(1−衰减率)^(n−1)，25 年真实衰减。
- 敏感性分析：电价 −20%~+30%、利率 −2%~+3% 共 36 个组合，颜色刻度一眼看风险。
三、提示词大公开（可直接复制用）
第一版我用的是"需求描述型"提示词，把要哪些参数、要哪些输出一次说清：
帮我制作一个光伏投资测算 Excel 表格。 输入参数：地区（关联当地日照时数和辐射量）、可铺设光伏面积、银行贷款利率、 售出电价、光伏组件单位面积功率(W/m²)、系统效率系数、组件衰减率、 初始投资单价(元/kWp)、年运维费用比例、贷款比例与期限、地方补贴政策、 土地租金、保险费用等。 输出：推荐安装容量(kWp)、年发电量(kWh)、25年累计发电量、总投资金额、 年发电收益、年贷款还款额、投资回收期(年)、IRR、NPV、25年现金流明细表。 要求：参数动态联动计算、地区下拉菜单、敏感性分析表（电价和利率变动对回收期的影响）。
💡 进阶技巧：想要结果更专业，把"计算口径"也写进提示词，例如"贷款还款用等额本息 PMT 函数""NPV 折现率单独作为参数""回收期用累计现金流插值法"。AI 越知道你的财务假设，算出来的东西越靠谱。
四、优化复盘（这部分最值钱）
第一版跑出来后，有两个真问题，优化后体验完全不一样：
问题 1：投资回收期"不显示"
最初的公式用 COUNTIF 跨表引用"公式单元格"来定位"累计现金流由负转正"的年份，部分 Excel 版本下算不出结果，单元格一片空白。
优化方案：在现金流表加一列"回收标记"=IF(累计≥0,1,0)，再用 MATCH(1, 标记列, 0) 精确定位首次转正的年份，最后用 INDEX 插值算出精确到"几点几年"的回收期。改完之后任何版本 Excel 都能稳定显示。
问题 2：地区数据不够细，尤其没有山西
读者里做工商业分布式最多的就是山西（光照资源全国前列）。原表只有 30 城，没有地级市层级。
优化方案：补录了山西省 11 个地级市的日照时数与辐射量（大同 2800h/4.8、朔州 2700h/4.7、忻州 2600h/4.5…），下拉菜单从 30 城扩到 40 城，数据验证范围自动更新。
▲ 截图：地区数据表，山西 11 个地级市已高亮收录
五、使用教程（5 步，小白也会）
▲ 截图：输入参数表（黄色=可编辑，绿色=自动算）
1、选地区：在"输入参数"表点黄色单元格，下拉选城市，日照/辐射自动带出。
2、填面积与系统参数：可铺设面积、组件功率密度（约 150–220 W/m²）、系统效率（0.75–0.85）、衰减率（晶硅约 0.5%）。
3、填财务参数：投资单价、电价、贷款比例/利率/期限、运维比例、补贴、土地租金、折现率。
4、看结果：切到"计算结果"，容量、发电量、回收期、IRR、NPV 立刻出来。
5、看明细与敏感性：25 年现金流逐年可查；敏感性矩阵帮你看"电价跌了/利率涨了"还划不划算。
▲ 截图：25 年现金流明细表（逐年衰减、还贷、净现金流）
敏感性分析怎么看？
下面这张矩阵：行是电价变动，列是利率变动，每个格子是该组合下的投资回收期。绿色=回本快（风险低），红色=回本慢（风险高）；"无法回收"=该情景下年均现金流已经为负。
▲ 截图：电价×利率 双因素敏感性分析（回收期矩阵）
六、山西的朋友看过来（实测对比）
同样 5000㎡、同样财务条件，换个光照更好的地方，结果差别很大：
光照每好一点，回收期就短一截——这也是为什么山西、西北做分布式光伏特别划算。
📥 想要这个 Excel 模板？关注公众号 「逐绿前行」，回复关键词 「测算表」，自动发你下载链接。（模板已内置全国 40 城数据 + 动态公式，拿到就能用。）
