officeba > 单独文章

---

如何使用Excel迭代计算

如何使用Excel迭代计算
大家都知道，数独是一种逻辑拼图（我3年前完全沉迷于它） ，要求您把数字1-9遵守一定的规则成一个网格（抽签更多信息关于Sudoku游戏可在网上找到） 。

研究员时在Excel的球队，科里丹写道，试算表为解决数独游戏使用Excel的公式，并使它可在网上办公（在这里） 。丹的电子表格是伟大的，与许多电子游戏解决了，它没有使用任何的VBA或其他脚本做的工作，解决难题，并依靠用迭代计算功能的Excel 。这是相当冷静，一直是热门下载，但有一件事情的电子表格，我想看看如果我不能改善只是它是多么复杂。事实上，丹取得的每一个细胞自身不同的公式，他最后不得不使用VBA创建的公式，因为它维护和调试的VBA没有写入所有这些不同的公式，一种自动的方式是不可能的。

我一看丹的电子表格，我想我自己的版本的数独解算器，不仅只用于公式，但也是一个地方的公式相对理解和有少量的独特配方。它变成了不说，很难建立，但我想我学到了相当多尝试不同的办法的问题作出了迭代模型类似这样的表现良好，并在同一时间得到合理维护和理解的。我认为，甚至可能出现一个合理有益的方式，在研究抽象的公式，考虑到Excel公式语言。我总是希望博客的过程，创造此试算表，以及如何工作的迭代公式，以显示力量， Excel的公式语言，因为它表明了有益的循环引用和迭代计算，并因为我觉得这是一种令人难以置信数额的乐趣，使这里不用。很多人创造了更强大的求解器，许多电子表格，有的只用公式，但我想尝试解释如何您可以创建一个求解，希望分享一些公式过关，人们找到有用的。

预reqs
创建一个电子表格的解决数独不是入门级spreadsheeting 。除了不错的公式，您需要了解的概念，迭代。克里斯来的表现非常出色的解释专题在其早先的职位迭代和康威的生命游戏，所以我不想重复，我会简单地假设你已经了解迭代。第二，我们要作出极其沉重的使用已命名的范围，并为我做的东西，新的名称经理是非常有益的（见公式建设改善第4部分：定义名称的一些有关本）和我米要承担工作的知识和普遍的命名范围（但我要表现出一定的技巧可能是新的经验公式，即使用户） 。最后，您需要至少熟悉阵列符号在Excel中。

设立审计委员会
对于那些我还没有输了，我要开始建立了一系列的主板非常希望那些旦科里使用：一个9x9局对我的投入，一个9x9局解决方案，以及27x27局可能的值在每一个箱子。我这样做是通过改变行高，列宽，字体和缩放等，所有的细胞是小广场，然后运用边界和填充得到如下：

  

的投入和合理的解决方案板直截了当（输入板是一个在左上角在这里您可以键入一个难题有待解决，该解决方案是董事会的正确答案希望显示） 。董事会可能的值，我会打电话的有效值局，是有一点麻烦。这是27x27 ，因为每一个箱子中的投入和解决板由一个3x3的细胞在有效值局。每个9细胞是否代表一个1-9的数字仍然在运行的实际价值相应的解决方案中局和一系列可能的值为特定细胞中的输入/解决细胞是一套所有数字在一个3x3 “大细胞”不属于空白。如果尚未，其目的/使用本委员会应成为明确以后。现在，让我们填写所有可能的值从8时59分在上述每个大细胞。

填写有效值局
我们要做到这一点，建立一个单一的公式，将填补在各种数字1-9在此基础上连续列公式坐落在，然后我们将稍后添加逻辑空白的数字是无效的。这个公式是有些复杂的电子表格比平均公式，所以我会首先整个公式，然后分解。这看起来如下：

=国防部（栏（格A1 ） -1,3 ） + 1 +国防部（列（格A1 ） -1,3 ） * 3

当这是进入左上角细胞有效值局，然后装入整个有效值局，这使得结果如下：

  

请注意，您想要做的填写带有选择性粘贴|公式或CTRL输入，否则你会搞砸所有漂亮格式。

打破这个公式下，行和列返回（杜）的行或列的参考传递给他们一些。通过这些职能格A1 ，因为在此公式中，意味着他们将给予我们一个号码，始于一上升。第一部分的公式使用模函数变换编号栏柱成0-2的数字，然后添加一个获得1-3 。对此，我们添加一个0 ， 3 ，或6 ，根据行号使用的模函数的结果ROW函数。

其次，因为这有点gnarly公式已经坐在一起，我们将不得不使用它所有的地方，我们要借此配方及将其移出细胞和成一个命名范围。这使我们能够摘要以外的所有逻辑公式到一个单一的，易于理解的名称。由于没有一个更好的名字，我将称之为“ onetonine ” ，将有相同的精确公式，我们刚刚建立。由于背景的相对引用（即他们是否为当前的细胞）是由什么细胞你在当您创建的命名范围，这是关键的是，您刚开始时选择单元格A1 ，然后建立新的命名范围，从而使您的公式工程各地的资产负债表内。

  
  

这也是为什么我们允许水槽的三排和三栏周围所有的董事会。

现在，我们可以把我们的新名称和测试它在局，象这样：

  

这里按CTRL +输入是目前最简单的方法来设定的公式中的所有细胞的有效值局。首先，请选择整个局，然后键入公式中，而是紧迫的输入，只需按两下Ctrl + Enter键来填补公式你刚才输入的所有细胞（不搞乱他们的格式） 。

建立解决局
我们会想什么基础有效值离开某一方块就我们目前的解决办法看起来像（而不是输入） ，但为了做到这一点，我们需要的东西，解决局。首先，至少，该解决方案一定会包含所有的箱子的数量从投入局。让我们先通过这样做最简单的方式，而追赶的情况下空白。在解决局，让我们所有的细胞有平等只是在相应的单元格输入板使用相对引用，除非输入细胞是空白的。绝对最简单的方式做到这一点是与下列公式（中显示的形式，这将是进入细胞D16 ） ：

=如果（个人财产，个人财产， “ ” ）

再次，请使用Ctrl + Enter键来填补这一到适当的细胞。现在，我们有基础的工作，让我们更可重复使用的和有意义的使用已命名的范围。

就像我们的名字onetonine ，让抽象的概念，指的是正确的输入细胞从细胞中的任何解决方案，使该委员会成为一个名称。我们需要做一些类似的所有板在某一点，所以我们将开始使命名范围为每个板（我选择in_board ， sol_board ，并val_board ） ，然后去一个名字从溶液局输入板（ in_cell_from_sol ）这是简单=主要！ D4类，然后使用这个改变公式是=如果（ in_cell_from_sol ， in_cell_from_sol ， “ ” ） 。请注意，这需要投入D16 。

确定，到目前为止，我们只是使我们的公式不再，但相信我，这一概念成为一种生活程序。做同样的有效值细胞解决董事会细胞是唯一有一点麻烦。 sol_cell_from_val的名称是：

=指数（ sol_board ，中断（ （列（主要！格A1 ） -1 ） / 3 ） 1 ，中断（ （栏（主！格A1 ） -1 ） / 3 ） +1 ）

这必须是建立由细胞的P4 。此公式使用Row和柱连同司运营商和INT转换的坐标从目前的细胞在27x27局的坐标在9x9板，然后使用索引获得细胞出sol_board相应的坐标。

对二者的方式测试这个公式是按一下“是指”框中的名称经理从不同细胞的有效值局。取决于细胞你在您会看到“舞蹈蚂蚁” （移动突出）为不同的细胞-细胞希望相应的解决办法局。

  

现在，我们有一些基本知识，让我们在一个实际难题，并请参阅关于让投入传播到该解决方案董事会和有效值局。这是困惑我们会使用：

  

进入它，解决局看起来应该像这样输入板。为了使有效值局的工作中，我们使用这个公式，所有有效的董事会细胞：

=如果（ sol_cell_from_val <>"",中频（ sol_cell_from_val = onetonine ， onetonine ,""), onetonine ）

这意味着，现有的存储单元是笼罩了存在的价值，如果在解决细胞和价值不是当前的onetonine价值。

这应该给您：

  

现在，我们准备这样做的东西，实际上将有助于拿出解决方案的基础上的规则和策略的游戏。

检查中的一些行的解决办法局
主要规则的游戏是，你不能有两个相同的号码中的任何行，列，或3x3大方块。我们将开始增加的规则不能有一个以上的号码，任何行，然后在列和大箱子。例如，在第二个大细胞在第一行中，没有一个号码4 ，第2 ，第7或9有可能是由于这条规则。我们可以做到这一点，把空白的任何细胞的有效值局为1 ）的解决方案中不存在（而这正是我们进入决赛onetonine的公式有效值细胞）和2 ）行的解决方案包含了一些委员会等于现值onetonine 。请注意，条件＃ 1正是过去onetonine显示（即还没有办法可以解决当前的大细胞） ，因此，所有我们能做的就是把逻辑＃ 2有。这种逻辑可以表示为：

=如果（ COUNTIF （ sol_row_from_val ， onetonine ） “ 0 ， ” “ ， onetonine ）

凡sol_row_from_val是：

=指数（ sol_board ，中断（ （列（主要！格A1 ） -1 ） / 3 ） 1 ， 0 ）

同样，这必须输入从P - 4 。

因此，结合这些我们得到：

=如果（ sol_cell_from_val <>"",中频（ sol_cell_from_val = onetonine ， onetonine ,""),中频（ COUNTIF （ sol_row_from_val ， onetonine ） “ 0 ， ” “ ， onetonine ） ）

其中，而不是简单的，至少是可以理解的，可以为您提供一个有效的价值局看起来像这样：

  

延伸至栏和（ 3x3 ）大盒
当我们去购买的规则， “没有两个相同的号码一栏”和“没有两个相同的号码，一个大盒子” ，在这同样的方式，我们会遇到两个问题： 1 。您不能创建sol_bigbox_from_val直接使用指数，因为索引仅返回一个细胞，行，或列从范围或整个范围和2 。这将开始让笨重的所有三个COUNTIFs OR'd一起在本月底公式。

为了解决第一个问题，您可以使用胶印-因为你可以使用抵销创建的任何其他参考这里-而是因为offset是挥发性这将导致性能问题的道路。一个更好的解决办法是采取联盟的两次提到，你从指数（使用Excel中的联盟运营商-冒号） ，以便使3x3范围。这给我们提供了sol_bigbox_from_val如下公式（输入从P - 4 ） ：

=指数（ sol_board ，中断（ （列（主要！格A1 ） -1 ） / 9 ） * 3 +1 ，中断（ （栏（主！格A1 ） -1 ） / 9 ） * 3 +1 ） ：指数（ sol_board ，中断（ （列（主要！格A1 ） -1 ） / 9 ） * 3 +3 ，中断（ （栏（主！格A1 ） -1 ） / 9 ） * 3 +3 ）

现在我们可以选择这个公式除了更容易。该中断，行，司的一部分说，每9行您将在有效值局，下移了一块三行解决局。有一个类似的表达栏完成了同样的话跨越。第二次提及的恰恰是第一个参考，但抵消两排下来，两栏之间，让您有3x3方块。

现在我们有了这一点，我们可以写一个大的公式，包括是否onetonine价值目前已经存在的任何行，列或大框，但让我们在这里再次使用抽象保持基本公式有效值局更简单。而不是把它直接插入公式，让我们创造一个新的名字叫做solution_in_rcb为“不存在解决细胞与我的电话号码中的任何一种行，列，或bigbox ？ ”这名只有已返回true或false （做试验的一部分，条件＃ 2不段） ，尽管没有得到短，其实是很简单写：

=或（ COUNTIF （ sol_row_from_val ， onetonine ） “ 0 ， COUNTIF （ sol_col_from_val ， onetonine ） ” 0 ， COUNTIF （ sol_bigbox_from_val ， onetonine ） “ 0 ）

利用这个新名字使我们的新配方有效值细胞：

=如果（ sol_cell_from_val <>"",中频（ sol_cell_from_val = onetonine ， onetonine ,""),中频（ solution_in_rcb ， “ ” ， onetonine ） ）

这不仅是少于这个公式已经和更可以理解的，这也导致一些明确的地方，只有一个可能的解决办法：

  

所以我们可以眼球了一些解决办法，但诀窍是要养活这些委员会的解决办法。这是迭代的用武之地下一次我们会使用迭代和几个公式技巧来解决一些Sudokus 。

编辑：更新sol_bigbox_from_val公式，以反映它看起来像什么时，从开始进入细胞中的P4 。还澄清一对夫妇在其他地方的出发细胞应的P4 。

声明：欢迎各大网站转载本站文章，还请保留一条能直接指向本站的超级链接，谢谢！

时间:2009-02-05 20:02:09,点击:65824