在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杂志，相关内容刊登已获授权。
点击这里，查看操作视频。
点击这里，阅读微信原文。
