作为 Excel 数据分析专家，读取央行历年贷款基准利率，对《贷款利息计算表》中的每笔贷款按利率调整日期拆分时间段，填写各段的起始日、结束日、对应利率和天数，最终生成精确的新表格。适用于财务计息、贷款利息核算等场景。

中文版提示词

你是一名 Excel 数据分析专家，现在面对一份名为《贷款利息计算》的特殊数据表格，它包含两个 Sheet：《1989年-2022年央行贷款基准利率》和《贷款利息计算表》。你的任务是按照以下步骤修改和整理《贷款利息计算表》：

1. 从《1989年-2022年央行贷款基准利率》中获取不同时间段的利率信息。
2. 《贷款利息计算表》包含 5 笔贷款，需要你根据贷款笔数、本金金额、计息起始日、计算结束日以及计息天数，结合从《1989年-2022年央行贷款基准利率》获取的利率信息，填写贷款表中"六个月至一年（含一年）"的利率。
3. 注意，一笔贷款的计息起始日和计算结束日可能跨越多个利率调整期，因此需要根据利率调整日期，对每笔贷款进行拆分，写出调整的起始时间、结束时间、对应的利率，以及该段时间的天数。

例如，对于第一笔贷款 1.9 亿元，从 2019年10月20日开始，到 2023年5月8日结束，你需要在表格中填写起始日和结束日分别落在不同利率调整日期范围内的每个时间段的利率，如 2015/10/24 至下一次利率调整日期的利率 4.35%，然后是下一次调整日期至再下一次调整日期的利率，一直到 2022/12/13 的利率 3.65%（下一次利率调整期在 2023/7/20，但计息结束日是 2023年5月8日，因此该段利率为 3.65%），以及每个时间段的天数。

根据以上步骤，修改并重新整理《贷款利息计算表》。我需要你完成这个任务，并生成一个新的、详细且精确的《贷款利息计算表》。

现在，你可以开始处理数据并生成新的表格。

英文版提示词

You are an Excel data analysis expert. You are now given a special data table called "Loan Interest Calculation", which contains two sheets: "PBOC Loan Benchmark Interest Rates 1989-2022" and "Loan Interest Calculation Table". Your task is to modify and organize the "Loan Interest Calculation Table" by following these steps:

1. Obtain the interest rate information for different time periods from "PBOC Loan Benchmark Interest Rates 1989-2022".
2. The "Loan Interest Calculation Table" contains 5 loans. Based on the number of loans, principal amount, interest start date, calculation end date, and interest days, and using the interest rate information obtained from "PBOC Loan Benchmark Interest Rates 1989-2022", fill in the rate for "six months to one year (inclusive)" in the loan table.
3. Note that a loan's interest start date and calculation end date may span multiple rate adjustment periods. Therefore, split each loan according to the rate adjustment dates, and record the start time, end time, corresponding interest rate, and the number of days for each period.

For example, for the first loan of 190 million yuan, from October 20, 2019 to May 8, 2023, you need to fill in the interest rate for each time period whose start and end dates fall within different rate adjustment date ranges, such as the rate of 4.35% from 2015/10/24 to the next rate adjustment date, then the rate from that adjustment date to the next, up to the rate of 3.65% on 2022/12/13 (the next rate adjustment period is 2023/7/20, but the interest end date is May 8, 2023, so this period's rate is 3.65%), along with the number of days in each period.

Based on the steps above, modify and reorganize the "Loan Interest Calculation Table". I need you to complete this task and generate a new, detailed, and accurate "Loan Interest Calculation Table".

Now you can begin processing the data and generating the new table.

🛠️ **适用 AI 工具**：ChatGPT、Claude、Kimi、通义千问、DeepSeek、豆包