在Excel中创建还款计划表非常简单，但如果想要使用公式创建有免息期的还款计划表，可以借助“单变量求解”（Goal Seek）功能。
您可以点击播客边学边听。
样例分析
该解决方案包含几种不同的方法，也可用于其他场景。图1显示了我们将用到的工作表结构。
图1
黄色单元格用于输入数据。B5单元格将填充单变量求解的结果。请注意：还款金额以负数输入。我们需要算出每月还款额，以便在B2单元格输入的月数内还清贷款。
E6单元格可从下方的计划表中提取最后一期还款余额。该单元格应为零，若该值不为零，则以橙色填充这两个单元格。这两个单元格均设置了条件格式，公差为1。
D6单元格会根据E6单元格的值显示不同文本，公式如下：
=IF(ABS(E6)<=1,"",IF(E6<0,"OverPaid","Under Paid"))
从第8行开始的还款计划表使用B5单元格中的每月还款额。
使用“单变量求解”是为了找出返回零（或接近零）余额的每月还款额。
“单变量求解”的步骤
选择E6单元格，下一步中的“目标单元格：”（Setcell:）会填入该单元格名称。
图2
点击“数据”（Data）功能区选项卡，然后点击模拟分析（What-IfAnalysis）图标下拉菜单（最右侧）并选择“单变量求解”（GoalSeek）。打开的对话框以及各项输入值如图2所示。
在“目标值：”（To value:）框中输入0，在“可变单元格：”（By changing cell:）框中输入B5，然后点击“确定”（OK）。
图3
如果达到目标值，则会显示另一个对话框，如图3所示。点击“取消”（Cancel）可清除B5中的金额。点击“确定”（OK）可接受该金额。需要时还可点击“撤消”（Undo）删除该金额。
总之，使用“单变量求解”功能计算还款额需要点击或输入大约七次，它不会保留以前的设置，每次必须重新输入。通过录制宏可以自动执行单变量求解，点击一个按钮即可。
开发工具选项卡
显示“开发工具”（Developer）功能区选项卡可以让宏操作更方便。
右击功能区并选择“自定义功能区”（Customize the Ribbon），勾选右侧列表中的“开发工具”（Developer）然后点击“确定”（OK）即可显示开发工具选项卡。
宏警告：执行宏后，撤消列表会清空。这意味着无法撤消宏操作以及执行宏之前的所有操作。在执行宏之前保存文件，以便在出现问题时可以直接关闭。
如果创建了新宏并想保存在文件中，确保选择启用宏的文件类型（.xlsm）或二进制文件类型（.xlsb）。普通的Excel文件类型（.xlsx）无法保存宏。
图4
点击“开发工具”（Developer）选项卡，然后点击左上角的录制宏（RerdMacro）图标。打开的对话框如图4所示。
在“宏名：”（Macro name:）框中输入Calculate_Repayment，确保“保存在：”（Store macro in:）框中内容为This Workbook。如果不是，可使用下拉菜单进行选择。点击“确定”（OK）。
请注意：宏名称不应包含空格。
重复上述步骤1至4。“单变量求解”操作完成后即可停止录制宏。点击“开发工具”功能区选项卡，然后点击“停止录制”（S Rerding）（之前的“录制宏”图标所在位置）。
宏录制完成后，可以将它分配给一个形状，之后点击该形状即可方便地执行宏。
点击“插入”（Insert）选项卡，然后点击“形状”（Shape）下拉菜单。这里使用圆角矩形。在工作表上绘制形状并输入文字Calculate Repayment。如果在绘制形状后立即输入文字，则会自动插入文字。
右击该形状并选择“指定宏”（Assign Mar）。在打开的对话框中，确保在下拉列表中选择This Workbook，然后选择Calculate_Repayment宏并点击“确定”（OK）。
现在点击该形状即可执行宏。
试着更改贷款输入值，D6和E6单元格可能会改变颜色。点击该形状可更新还款额，D6和E6单元格会恢复为绿色。
编辑宏
录制宏时，Excel会将引用的单元格写入代码中。如果工作表结构发生变化（插入或删除行或列），宏中的引用可能会无效。为避免这种情况，可为这两个引用的单元格定义名称，然后编辑录制好的宏，用定义名称取代。
点击B5单元格，然后点击名称框（Name Box），它位于公式栏左侧，字母序号的上方，通常显示当前单元格的名称。在名称框中输入Repayment，然后按回车键。
点击E6单元格，然后点击名称框，输入“Balance”后按回车键。请注意：该名称也不能包含空格。
如需编辑录制好的宏，点击“开发工具”功能区选项卡，点击“宏”（Macros）图标，选择名为Calculate_Repayment的宏，然后点击右侧的编辑（Edit）按钮。
将E6改为Balance，将B5改为Repayment，确保保留引号，如图5所示。
图5
通过使用“单变量求解”功能和宏，我们能够返回所需的值，而用公式却难以实现。在配套视频中，我将演示更多功能，如在更新任意一个贷款输入值后会自动执行单变量求解的宏。
本文内容编译自INTHEBLACK杂志，相关内容刊登已获授权。
点击这里，查看操作视频。
点击这里，阅读微信原文。
