专利名称：一种实现电子表格单变量求解的方法
技术领域：
本发明属于电子表格数据分析领域，具体涉及一种以连分式法为核心的单变量求 解方法。
背景技术：
单变量求解为在电子表格程序中的一项数据分析功能，单变量求解是通过调整另 一个单元格中的值，从而可求得指定单元格中的特定值的方法。在单变量求解过程中，电子 表格程序会不断更改指定单元格中的值，直到依赖于该单元格的公式返回满足要求的值为 止。现有单变量求解的方法主要有二分法、牛顿法(切线法)、割线法和连分式法等。采用二分法进行单变量求解时，要求目标函数连续、在初始区间上有且只有一个 实根，虽然条件容易满足，但是收敛速度慢。牛顿法尽管收敛速度很快(收敛阶数为2)，但 必须用到函数导数，在实际问题中往往求不出或很难求出目标函数关于自变量的导数，所 以不适合实际应用。割线法(收敛阶数约为1. 618)和连分式法(收敛阶数约为1. 83)，要 求函数在初始区间上存在一阶、二阶导数等条件，对于不满足条件的函数无法确保收敛性。通过以上分析可知，函数条件要求较低的方法(如二分法)，收敛速度较慢；而收 敛速度较快的方法，如牛顿法、割线法和连分式法等，对函数条件都比较苛刻。实际应用中 问题的条件千变万化，纯粹使用其中一种方法往往无法保证收敛性与收敛速度。
发明内容
本部分的目的在于概述本发明的实施例的一些方面以及简要介绍一些较佳实施 例。在本部分以及本申请的说明书摘要和发明名称中可能会做些简化或省略以避免使本部 分、说明书摘要和发明名称的目的模糊，而这种简化或省略不能用于限制本发明的范围。本发明所解决的技术问题在于提供一种电子表格单变量求解方法，用以提高单变 量求解的实用性。为了解决上述问题，根据本发明提供了一个电子表格单变量求解方法，其包括A 选定目标单元格和可变单元格，设定目标单元格的目标值，获得可变单元格的 迭代初始值和目标单元格的结果初始值，其中该目标单元格含有的公式引用所述可变单元 格，每对迭代值和结果值可被称为一个数据点；B:在初始迭代值左右两个方向上进行初始搜索得到新的迭代值和结果值，其中对 于每个新的结果值，判断该新的结果值是否较初始结果值更接近目标值，如果是则进入步 骤C，否则，则继续判断该新的结果值与目标值之差和初始结果值与目标值之差是否为异 号，如果是异号则进入步骤D，否则继续初始搜索；C 将其结果值最接近目标值的两个数据点确定为连分式迭代的两个初始数据点； 基于该两个初始数据点开始连分式迭代得到新的迭代值和结果值，其中对于每个新的结果 值，判断该新的结果值是否较前一结果值更接近目标值，如果是则继续连分式迭代，否则， 则继续判断该新的结果值与目标值之差和前一结果值与目标值之差是否异号，如果是异号则进入步骤D，否则，则进入步骤E ；D 将其结果值与目标值的差异号的两个数据点确定为二分迭代的两个初始数据 点；基于该两个初始数据点开始二分迭代得到新的迭代值和结果值，其中对于每个新的结 果值，判断该新的结果值是否较前两结果值中距目标值近的结果值更接近目标值，如果是 则进入步骤C，否则，则继续判断该新的结果值与目标值之差和前两结果值中的一个与目标 值之差是否异号，如果是异号则继续二分迭代，否则，则进入步骤E ；E 在当前最接近目标值的结果值对应的迭代值左右两个方向上进行进退搜索得 到新的迭代值及结果值，其中对于每个新的结果值，判断该新的结果值是否较当前最接近 目标值的结果值更接近目标值，如果是则进入步骤C，否则，则继续判断该新的结果值与目 标值之差和当前最接近目标值的结果值与目标值之差是否异号，如果是异号则进入步骤D， 否则，则继续进退搜索；其中每获得一个新的结果值后都进行终止条件检验。进一步的，在步骤A中还获得精度和最大迭代次数，所述终止条件包括I yk-t < ε MS，如果其中的一个条件为真，则结束求解，并返回求得的迭代值和结果值，其 中yk为第k个结果值，t为所述目标值，ε为精度，MS为最大迭代次数。进一步的，在步骤B中，对于每个新的结果值，判断该新的结果值是否较初始结果 值更接近目标值，如果是则进入步骤C，否则继续初始搜索。进一步的，在步骤D中，对于每个新的结果值，判断该新的结果值是否较前两结果 值中的一更个接近目标值，如果是则进入步骤C，否则，则继续判断该新的结果值与目标值 之差和前两结果值中的一个与目标值之差是否异号，如果是异号则继续二分迭代，否则，则 进入步骤E。进一步的，在步骤E中，对于每个新的结果值，判断该新的结果值是否较当前最接 近目标值的结果值更接近目标值，如果是则进入步骤C，否则，则继续进退搜索。更进一步的，在步骤B中，在初始迭代值左右两个方向上按照以下次序进行初始 搜索x。+sn，X0-Sil, x0+sal, Xtl-Sal，其中Xtl为初始迭代值，Sil为第一微观步长，Sal为第一宏 观步长，第一宏观步长大于第一微观步长，初始搜索一次完毕后，则缩小第一微观步长，扩 大第一宏观步长，之后继续初始搜索。再进一步的，第一宏观步长为两倍的第一微观步长， 缩小第一微观步长时将其缩小为原来的N分之一，扩大第一宏观步长时将其扩大为原来的 N倍，基于初始迭代值确定第一微观步长，N为大于1的自然数。更进一步的，在步骤C中，若连分式达到预定节数，就以最靠近目标值的两个数据 点为两个初始数据点重新开始连分式迭代。更进一步的，在步骤D中，在继续判断该新的结果值与目标值之差和前两结果值 中的一个与目标值之差是否异号时，如果yk+1_t与yk_t为异号，那么保留xk+1和Xk而舍弃 Xlri，之后继续二分迭代，如果yk+1_t与y^-t为异号，那么保留xk+1和Xh而舍弃xk，之后继 续二分迭代，二分迭代得到新的迭代值和结果值为xk+1 = (xk+xk_1)/2, yk+1 = f(xk+1)，其中 Xk+1为新的迭代值，yk+1为新的结果值，Xk和Xlri为二分迭代前的迭代值，yk和yk-i分别为前 两结果值，t为目标值。更进一步的，在步骤E中，在当前最接近目标值的结果值对应的迭代值左右两个 方向上按照以下次序进行进退搜索
xm+si2 ；xm+si2/2 ；xm+si2/4 ；xm+si2/8 ； Xm+Sa2 ；xm+2sa2 ；Xm+4sa2 ；xm+8sa2 ；χ = Xm-Si2 ；χ = xm-si2/2 ；χ = xm_si2/4 ；χ = xm_si2/8 ；Xm-Sa2 ；xm-2sa2 ；xm-4sa2 ；xm-8sa2 ；其中Xm为当前最接近目标值的结果值对应的迭代值，Si2为第二微观步长，Sa2为 第二宏观步长，第二宏观步长大于第二微观步长，进退搜索一次完毕后，则缩小第二微观步 长，扩大第二宏观步长，之后继续进退搜索。再进一步的，根据当前最接近目标值的结果值对应的迭代值和当前的结果值对应 的迭代值的差确定第二微观步长和第二宏观步长，缩小第二微观步长时将其缩小为原来的 M分之一，扩大第一宏观步长时将其扩为原来的M倍，M为大于1的自然数。与现有技术相比，本发明中采用以连分式法为核心结合初始搜索、二分法和进退 法等方法进行求解，这样对于满足局部连续可导的函数方程，有很好的适应能力，在基本保 留求解性能的同时拓宽了应用范围，显示出较强的实用性。此外，其易通过计算机软件实 现，不仅仅适用于电子表格数据分析功能，容易推广至其他领域。
结合参考附图及接下来的详细描述，本发明将更容易理解，其中同样的附图标记 对应同样的结构部件，其中图1为运行本发明中的电子表格程序的计算机在一个实施例中的结构框图；图2为本发明中的电子表格单变量求解方法在一个实施例中的方法流程图；图3a为本发明中的一个电子表格单变量求解示例的初始状态界面；和图3b为本发明中的该电子表格单变量求解示例的最终状态界面。
具体实施例方式本发明的详细描述主要通过程序、步骤、逻辑块、过程或其他象征性的描述来直接 或间接地模拟本发明技术方案的运作。为透彻的理解本发明，在接下来的描述中陈述了很 多特定细节。而在没有这些特定细节时，本发明则可能仍可实现。所属领域内的技术人员 使用此处的这些描述和陈述向所属领域内的其他技术人员有效的介绍他们的工作本质。换 句话说，为避免混淆本发明的目的，由于熟知的方法、程序和成分已经很容易理解，因此它 们并未被详细描述。此处所称的“一个实施例”或“实施例”是指可包含于本发明至少一个实现方式中 的特定特征、结构或特性。在本说明书中不同地方出现的“在一个实施例中”并非均指同一 个实施例，也不是单独的或选择性的与其他实施例互相排斥的实施例。此外，表示一个或多 个实施例的方法、流程图或功能框图中的模块顺序并非固定的指代任何特定顺序，也不构 成对本发明的限制。由于本发明的实施例通常结合一定的计算环境、计算机系统或者说硬件来实现。 故图1及相应的描述旨在提供对可以实现本发明的实施例中的合适的计算环境进行简单 和概括的描述。尽管图1中利用在个人计算机系统上执行的计算环境作为一般性环境来描 述本发明，但是本领域的技术人员应该意识到，本发明也可以结合其它类型的计算机系统和程序模块来实现。请参考图1，其示出了本发明的一个实施例中使用的计算机100的说明性计算机 体系结构。所述计算机100为常规的台式计算机或者膝上型笔记本，所述计算机100包括 中央处理单元(CPU) 101、包括随机存取存储器(RAM) 102和只读存储器(ROM) 103的系统存 储器104，以及连接系统存储器104和中央处理单元101的系统总线105。所述计算机100 还包括帮助计算机内的各个器件之间传输信息的基本输入/输出系统(I/O系统)106，和用 于存储操作系统、应用程序和其他程序模块的大容量存储设备107。所述基本输入/输出系统106包括有用于显示信息的显示器108和用于用户输入 信息的诸如鼠标、键盘之类的输入设备109。其中所述显示器108和输入设备109都通过连 接到系统总线105的输入输出控制器110连接到中央处理单元101。所述基本输入/输出 系统106还可以包括输入输出控制器110以用于接收和处理来自键盘、鼠标、或电子触控笔 等多个其他设备的输入。所述大容量存储设备107通过连接到系统总线105的大容量存储控制器(未示 出)连接到中央处理单元101。所述大容量存储设备107及其相关联的计算机可读介质为 计算机100提供非易失性存储。也就是说，所述大容量存储设备107可以包括诸如硬盘或 者CD-ROM驱动器之类的计算机可读介质(未示出)。所述大容量存储设备107可以存储 电子表格应用程序114，所述电子表格应用程序114可以用于生成、访问和修改例如工作薄 115。所述电子表格应用程序可以为微软的EXCEL，也可以为其他公司的电子表格程序。下 文中，基于图1所示的计算环境对本发明中的电子表格应用程序中的单变量求解方法做进 一步地描述。一般来说，函数在根附近容易满足连续与可导等条件，所以可以先用简单的、条件 宽松、收敛较慢的方法求得靠近根的近似解，然后用收敛较快的方法使之精确化。基于此， 本发明在电子表格单变量求解方法采用了二分法、进退法(慢速方法)与连分式法(快速 方法)的组合方法，大大提高了电子表格单变量求解方法的实用性。图2为本发明中的电子表格单变量求解方法在一个实施例200中的方法流程图。 所述电子表格单变量求解方法200包括以下步骤。步骤210，通过电子表格用户界面取得单变量求解的初始条件。在一个实施例中，取得单变量求解的初始条件包括选定目标单元格和可变单元 格，设定目标单元格的目标值t，获得可变单元格的迭代初始值Xtl和目标单元格的结果初始 值yo，其中该目标单元格含有的公式(y = f(x))引用所述可变单元格，当前迭代次数k = O0在一个优选的实施例中，取得单变量求解的初始条件还包括获得精度ε和最大 迭代次数MS。所述精度ε和最大迭代次数MS可以为默认设定的，也可以是用户主动设定 的。图3a示出了本发明中的一个电子表格单变量求解示例的初始状态界面，其中目 标单元格为B2，可变单元格为Bi，可变单元格Bl的迭代初始值为3，目标单元格B2的结果 初始值为5，目标单元格B2的目标值为10，该目标单元格含有的公式为=SUM(A1:B1)，其引 用所述可变单元格Bl，最大迭代次数为100和计算精度为0. 001。在单变量求解过程中，电子表格程序会不断更改可变单元格Bl中的值，直到含有依赖于该可变单元格Bl的公式的目标单元格B2返回满足目标值t为止。步骤220，确定初始搜索的步长及规则，根据所述步长及规则在初始迭代值Xtl左右 两个方向上进行初始搜索得到新的迭代值&+1和结果值yk+1，其中对于每个新的结果值yk+1， 判断该新的结果值yk+1是否较初始结果值九更接近目标值t，即|yk+1-t| < |y。-t|，如果是 则进入步骤230，否则，则继续判断该新的结果值yk+1与目标值t之差(yk+1-t)和初始结果 值与目标值之差(ycrt)是否为异号，即(yk+1_t) MytTt) < 0，如果是异号则进入步骤240， 否则继续初始搜索。在一个实施例中，确定初始步长为0. Olxtl (若Xtl = 0，初始步长设为0. 01)，设定第 一微观步长(Sil)为初始步长，第二宏观步长(Sal)为两倍的初始步长，初始搜索的顺序(即 规则)依次为XQ+Sn，X0-Sil, X0+Sal, X0-Salo具体来说，在第一次初始搜索时，将XQ+Sil作为 新的迭代值xk+1，在第二次初始搜索时，将Xtl-Sil作为新的迭代值Xk+1，在第三次初始搜索时， 将Xo+Sal作为新的迭代值Xk+1，在第四次初始搜索时，将Xtl-Sal作为新的迭代值Xk+1。初始搜 索一遍完毕后，则将第一微观步长缩小为原来的N分之一(比如1/2，sn = sn/2)，扩大第 一宏观步长为原来的N倍(比如2倍，sal = sal*2)，N为大于1的自然数，之后继续以第一 微观步长和第一宏观步长分别在迭代初始值Xtl左右两个方向搜索，搜索顺序依然为X(l+Sil，
X0_Sil，X0+Sal，X0_Sal °在其他实施例中，可以将初始步长设定为其他值，比如0. Ixtl，第一微观步长和第 二宏观步长的相对大小都可以调节，搜索的顺序和个数也都可以调整。初始搜索的首要目 的是为了搜索到较初始结果值I0更接近目标值t的新的结果值，只要能实现这个目的，所 属领域内的普通技术人员可以随意的设定步长和搜索规则。在一个实施例中，其中对于初始搜索得到的每个新的结果值xk+1，判断是 否lyk+1_t| < IytTtI,如果是则进入步骤230，否则，则直接继续初始搜索，而不进行 (yk+1-t)*(yQ-t) <0 的判断。在另一个实施例中，还可以采用其他方式得到较初始结果值lo更接近目标值t的 新的结果值。步骤230，将其结果值最接近目标值的两个数据点确定为连分式迭代的两个初始 数据点；基于该两个初始数据点开始连分式迭代得到新的迭代值Xk+1和结果值yk+1，其中 对于每个新的结果值yk+1，判断该新的结果值是否较前一结果值更接近目标值，即|yk+1_t < |yk_t|，如果是则另k = k+l继续连分式迭代，否则，则继续判断该新的结果值与目标值 之差(yk+1_t)和前一结果值与目标值之差(yk_t)是否异号，即(yk+1-t)*(yk-t) <0，如果是 异号则进入步骤240，否则，则进入步骤250。在一个实施例中，如果由步骤220进入步骤230，则其结果值最接近目标值的两个 数据点为(Xo，y。)和初始搜索最后得到的数据点(xk+1，yk+1)，如果由步骤250进入步骤230， 其结果值最接近目标值的两个数据点为(xm，ym)和进退搜索最后得到的数据点(xk+1，yk+1)， 其中Xm为进退搜索时最接近目标值的迭代值，ym为进退搜索时最接近目标值的结果值，如 果由步骤240进入步骤230，其结果值最接近目标值的两个数据点为二分迭代最后得到的 数据点(xk+1，yk+1)以及yk和yk-工中更接近目标值t的数据点。在一个实施例中，若连分式达到预定节数(比如7、8或9节)，就以最靠近目标值 的两个数据点(也就是(xk，yk)，(xk+1,yk+1))为两个初始数据点重新开始连分式迭代。
下面简单介绍一下连分式法。对于方程y = f(x)，假设其反函数为χ = F (y)，我们将其用连分式表示，即
x = F(y) = b0 +
bl+_b2 +-—~
b3+广少3
b +lzl±
υΑ T ,
O5 + ...其中Ivb1,...，、，...可以由各数据点(Xi，yi) (i = 0，1，2...)来确定。如果在 上式中令y = 0，则可以算出方程的根。
权利要求
1.一种实现电子表格单变量求解方法，其特征在于，包括A 选定目标单元格和可变单元格，设定目标单元格的目标值，获得可变单元格的迭代 初始值和目标单元格的结果初始值，其中该目标单元格含有的公式引用所述可变单元格， 每对迭代值和结果值可被称为一个数据点；B:在初始迭代值左右两个方向上进行初始搜索得到新的迭代值和结果值，其中对于每 个新的结果值，判断该新的结果值是否较初始结果值更接近目标值，如果是则进入步骤C， 否则，则继续判断该新的结果值与目标值之差和初始结果值与目标值之差是否为异号，如 果是异号则进入步骤D，否则继续初始搜索；C 将其结果值最接近目标值的两个数据点确定为连分式迭代的两个初始数据点；基 于该两个初始数据点开始连分式迭代得到新的迭代值和结果值，其中对于每个新的结果 值，判断该新的结果值是否较前一结果值更接近目标值，如果是则继续连分式迭代，否则， 则继续判断该新的结果值与目标值之差和前一结果值与目标值之差是否异号，如果是异号 则进入步骤D，否则，则进入步骤E ；D 将其结果值与目标值的差异号的两个数据点确定为二分迭代的两个初始数据点； 基于该两个初始数据点开始二分迭代得到新的迭代值和结果值，其中对于每个新的结果 值，判断该新的结果值是否较前两结果值中距目标值近的结果值更接近目标值，如果是则 进入步骤C，否则，则继续判断该新的结果值与目标值之差和前两结果值中的一个与目标值 之差是否异号，如果是异号则继续二分迭代，否则，则进入步骤E ；E 在当前最接近目标值的结果值对应的迭代值左右两个方向上进行进退搜索得到新 的迭代值及结果值，其中对于每个新的结果值，判断该新的结果值是否较当前最接近目标 值的结果值更接近目标值，如果是则进入步骤C，否则，则继续判断该新的结果值与目标值 之差和当前最接近目标值的结果值与目标值之差是否异号，如果是异号则进入步骤D，否 则，则继续进退搜索；其中每获得一个新的结果值后都进行终止条件检验。
2.如权利要求1所述的单变量求解方法，其特征在于，在步骤A中还获得精度和最大迭 代次数，所述终止条件包括I yk"t I < ε和k > MS,如果其中的一个条件为真，则结束求解，并返回求得的迭代值和结果值，其中yk为第k 个结果值，t为所述目标值，ε为精度，MS为最大迭代次数。
3.如权利要求1所述的单变量求解方法，其特征在于，在步骤B中，对于每个新的结果 值，判断该新的结果值是否较初始结果值更接近目标值，如果是则进入步骤C，否则继续初 始搜索。
4.如权利要求1所述的单变量求解方法，其特征在于，在步骤D中，对于每个新的结 果值，判断该新的结果值是否较前两结果值中的一更个接近目标值，如果是则进入步骤C， 否则，则继续判断该新的结果值与目标值之差和前两结果值中的一个与目标值之差是否异 号，如果是异号则继续二分迭代，否则，则进入步骤Ε。
5.如权利要求1所述的单变量求解方法，其特征在于，在步骤E中，对于每个新的结果 值，判断该新的结果值是否较当前最接近目标值的结果值更接近目标值，如果是则进入步 骤C，否则，则继续进退搜索。
6.如权利要求1-5任一所述的单变量求解方法，其特征在于，在步骤B中，在初始迭代值左右两个方向上按照以下次序进行初始搜索X0+Sil，X0_Sil，X0+Sal，X0_Sal，其中Xtl为初始迭代值，Sil为第一微观步长，Sal为第一宏观步长，第一宏观步长大于第 一微观步长，初始搜索一遍完毕后，则缩小第一微观步长，扩大第一宏观步长，之后继续初始搜索。
7.如权利要求6所述的单变量求解方法，其特征在于，第一宏观步长为两倍的第一微 观步长，缩小第一微观步长时将其缩小为原来的N分之一，扩大第一宏观步长时将其扩大 为原来的N倍，基于初始迭代值确定第一微观步长，N为大于1的自然数。
8.如权利要求1-5任一所述的单变量求解方法，其特征在于，在步骤C中，若连分式达 到预定节数，就以最靠近目标值的两个数据点为两个初始数据点重新开始连分式迭代。
9.如权利要求1-5任一所述的单变量求解方法，其特征在于，在步骤D中，在继续判断 该新的结果值与目标值之差和前两结果值中的一个与目标值之差是否异号时，如果yk+1_t 与yk_t为异号，那么保留xk+1和Xk而舍弃Xlri，之后继续二分迭代，如果yk+1_t与y^-t为 异号，那么保留xk+1和Xlri而舍弃xk，之后继续二分迭代，二分迭代得到新的迭代值和结果值为Xk+Ι — (Xk+Xk—1)/2，Yk+Ι 一 f (Xk+1)，其中Xk+1为新的迭代值，yk+1为新的结果值，Xk和Xlri为二分迭代前的迭代值，yk和yk-i 分别为前两结果值，t为目标值。
10.如权利要求1-5任一所述的单变量求解方法，其特征在于，在步骤E中，在当前最接 近目标值的结果值对应的迭代值左右两个方向上按照以下次序进行进退搜索Xm+Si2 ；Xm+Si2 /2 ； xm+s 12/4 ；xm+si2/8 ；a2a2 ，X — Si2 ；Χ — Xm_Si2/2 ；X — ^m-Si2/4 ;X — Xm-Si2/8 ；Xm_Sa2 ；Xm_2Sa2 ；Xm_4Sa2 ；Xm_8Sa2 ；其中Xm为当前最接近目标值的结果值对应的迭代值，Si2为第二微观步长，Sa2为第二宏 观步长，第二宏观步长大于第二微观步长，进退搜索一遍完毕后，则缩小第二微观步长，扩大第二宏观步长，之后继续进退搜索。
11.如权利要求10所述的单变量求解方法，其特征在于，根据当前最接近目标值的结果值对应的迭代值和当前的结果值对应的迭代值的差确 定第二微观步长和第二宏观步长，缩小第二微观步长时将其缩小为原来的M分之一，扩大第一宏观步长时将其扩为原来 的M倍，M为大于1的自然数。
全文摘要
本发明公开了一种实现电子表格单变量求解的方法，该方法包括通过电子表格的用户界面获取自变量、因变量、初始值与目标值、精度与迭代步数限制等初始条件，采用以连分式法为核心结合初始搜索、二分法和进退法等方法进行求解。本发明提供的方法，应用于电子表格数据分析领域，有很好的计算性能与精度，达到了实际应用的水平。
文档编号G06F17/10GK102004645SQ201010593298
公开日2011年4月6日 申请日期2010年12月17日 优先权日2010年12月17日
发明者俞志刚 申请人:无锡永中软件有限公司