How to use Excel for iterative calculation
As we all know, Sudoku is a logical puzzle (I was completely addicted to it three years ago). You are required to put the number 1-9 in a grid according to certain rules (more information about Sudoku can be found online).
When a researcher was in Excel's team, Coridan wrote that the spreadsheet used Excel's formula to solve Sudoku games and made it available online (here). Dan's spreadsheet is great, and it has been solved by many video games. It does not use any VBA or other scripts to solve problems, but relies on Excel with iterative calculation function. This is quite cool and has become a popular download, but there is one thing about spreadsheets that I want to see if I can't improve it. In fact, each cell has its own different formula, and he finally has to use the formula created by VBA, because the VBA it maintains and debugs can't write all these different formulas, and the automatic way is impossible.
Looking at Dan's spreadsheet, I think my own version of Sudoku solver is not only used to solve formulas, but also the place where formulas are relatively understood. There are several unique formulas. Needless to say, this has become difficult to establish, but I think I have learned a lot about trying different methods to make an iterative model like this, which performs well and is reasonably maintained and understood. In my opinion, there may even be a reasonable and beneficial way to learn abstract formulas, taking into account Excel formula language. I've always wanted to record the process of creating this spreadsheet in my blog and how to use iterative formulas to show the powerful functions of Excel formula language, because it shows useful circular references and iterative calculations, and because I think it doesn't have to be a very interesting thing here. Many people have created more powerful solvers, many spreadsheets, and some only use formulas, but I want to try to explain how to create solutions and share some formulas, and people will find them very useful.
prerequisite
Creating a spreadsheet to solve Sudoku is not an entry-level spreadsheet. In addition to good formulas, you also need to understand the concept of iteration. Chris has explained this topic brilliantly in his early post iteration and Conway's life game, so I don't want to repeat it. I will simply assume that you already know iteration. Secondly, we must use naming ranges extremely heavily and do something for me. The new name manager is very beneficial (see Formula Construction Improvement Part 4: Defining the names of some related books) and I must undertake the work and the knowledge of general naming ranges (but I must show some skills that may be new empirical formulas, even for users). Finally, you should at least be familiar with array symbols in Excel.
Establish an audit Committee
For those who haven't lost, I'm going to start building a series of motherboards, and I really hope to use them in Danke: a 9x9 board investment, a 9x9 board solution, and the possible value of each box of 27x27 boards. I do this by changing the row height, column width, font and proportion. All the cells are small squares, and then I use borders and padding to get the following results:
The input and reasonable solution board is intuitive (the input board is in the upper left corner, where you can input a problem to be solved, and the solution is the correct answer that the board wants to display). The board may be worth it. I called the RMS office, but there was a little trouble. This is 27x27, because each box is put into a 3x3 cell in the RMS board and solved by it. Whether each 9-cell represents a number 1-9 is still the actual value of the corresponding solution, and a series of possible values of the input/solution cell in a specific cell is a set of all numbers in a 3×3 "big cell" that is not blank. If not, the purpose/purpose of this committee should become clear in the future. Now, let's fill in all the possible values from 8: 59 in each big cell above.
Fill in the valid value bureau
We want to fill all kinds of numbers 1-9 by establishing a single formula. On this basis, locate the formula of continuous columns, and then we will add logical blanks with invalid numbers later. This formula is a little more complicated than the general formula in the spreadsheet, so I will decompose the whole formula first. This looks like this:
= Ministry of Defence (column (A 1)-1, 3)+ 1+ Ministry of Defence (column (A 1)-1, 3) * 3
When this is the cell RMS office in the upper left corner, the whole RMS office is loaded, which makes the result as follows:
Please note that what you have to do is paste | formula or CTRL input selectively, otherwise all beautiful formats will be messed up.
Breaking this formula, rows and columns return (du) references to rows or columns and pass some to them. Through these function boxes A 1, because in this formula, it means that they will give us a number, starting with rising. The formula in the first part uses a modular function to convert a number column into a number of 0-2, and then adds 1 to get 1-3. To this end, we add a 0, 3 or 6, and determine the line number according to the result of the modular function used by the line function.
Secondly, because it is a bit like gnarly formula, we will have to use it anywhere. We will borrow this formula and move it out of the cell and into the naming range. This enables us to abstract all logical formulas into a simple and understandable name. For lack of a better name, I will call it "one ketone base" and have the same exact formula, which we have just established. Since the background of relative references (that is, whether they are the current cells) consists of the cells in which you created the named range, it is very important to initially select cell A 1 and then create a new named range so that your formula will be valid throughout the balance sheet.
That's why we allow all the boards around the sink in three rows and three columns.
Now, we can put our new name in the station for testing, like this:
Pressing CTRL+ here is the easiest way to set the effective values of all cells in the formula. First, please select the whole game, and then enter it into the formula. Without pressing the key, just press Ctrl+Enter twice to fill in all the cells you just entered in the formula (don't confuse their formats).
Establish a clearing house
We will consider what the basic RMS left a box, what our current solution looks like (rather than input), but in order to do this, we need something to solve the circuit board. First of all, at least, the solution will definitely contain all the boxes from the input office. Do this in the simplest way first to catch up with the blank situation. In the solution board, let all our cells be equal, and use relative references in the corresponding cell input board, unless the input cell is empty. The simplest method is to use the following formula (in the table shown, this will be cell D 16):
= If (personal property, personal property, "")
Similarly, use Ctrl+Enter to fill in the appropriate cells. Now, we have the basic work to make our use of naming ranges more reusable and meaningful.
Just like our name onetonine, let the abstract concept refer to any solution that is correctly imported from cells, and make the Committee a name. We need to do something similar to all boards at a certain point, so we will start to make a name range for each board (I choose in_board, sol_board, val_board), and then go from solving boards (in_cell_from_sol) to a name input board, simple = main! D4 class, and then use this change formula is = if (in_cell_from_sol, in_cell_from_sol, ""). Please note that this requires D 16.
Of course, so far, we just let our formula go away, but believe me, this concept has become a life program. It is only a little troublesome to make the same RMS unit to solve the board unit. The name of sol_cell_from_val is:
= exponent (sol_board, interrupt ((column (main! A1)-1)/3)1,interrupt ((column (main! A 1) - 1)/3) + 1)
This must be determined by P4 of the cell. In this formula, the coordinates converted by the current cell in the 27x27 bureau are arranged on the 9x9 board together with the operator and INT, and then the coordinates corresponding to the cell sol_board are obtained by the index.
The way to test these two formulas is to click the name manager in the "Reference" box from the RMS offices of different units. Depending on your cell, you will see "dancing ants" of different cells-cells want corresponding solutions.
Now there is some basic knowledge that puts us into a practical problem. Please refer to about letting input spread to the solution board and RMB. This is confusing, and we will use:
Enter it and the solution board should look like this. To make the root mean square work, we use the following formula for all effective plate elements:
= If (sol _ cell _ from _ val
This means that the existing memory cell is covered by the existing value if the cell is solved and the value is not the current unary value.
This will provide you with:
Now, we are going to do something that will actually help to propose solutions according to the rules and strategies of the game.
Check the solution of some lines.
The main rule of the game is that there cannot be two identical numbers in any row, column or 3x3 box. We will start to add the rule that there cannot be more than one number in any row, and then in columns and large boxes. For example, in the second large cell in the first row, the numbers 4, 2, 7 or 9 may not be due to this rule. We can do this by setting the blank RMS of any cell to 1), which does not exist in the solution (which is the formula of the final Onetone RMS cell), and 2), the solution of line contains some committees equal to the present value Onetone. Please note that the condition # 1 is the past onetonine display (that is, there is no way to solve the current large cell), so what we can do is to put logic # 2 in it. This logic can be expressed as:
= If (COUNTIF (sol_row_from_val，Onetone) "0，" "，Onetone)
Where sol_row_from_val is:
= exponent (sol_board, interrupt ((column (main! a 65438+a 1)- 1)/3) 1+0，0)
Again, this must be input from P-4.
So, combining these, we get:
= If (sol _ cell _ from _ val
Among them, it is not simple, at least understandable, but can provide you with an effective value bureau, which looks like this:
Expand to columns and (3x3) large boxes.
When we buy the rules "No two identical numbers in a column" and "No two identical numbers in a big box", we will encounter two problems in the same way: 1. Sol_bigbox_from_val cannot be created to use the index directly, because the index only returns one cell, one row or one column in the range or the whole range and 2. This will start to bring all three cumbersome COUNTIFs or' d formulas together at the end of this month.
To solve the first problem, you can use offset printing-because you can use offset printing to create any other reference here-but because offset printing is unstable, it will lead to performance problems on the road. A better solution is to take the alliance you mentioned twice from the index (using the alliance operator in Excel-colon) to get the 3x3 range. This gives us the following formula for sol_bigbox_from_val (input from P-4):
= exponent (sol_board, interrupt ((column (main! A1)-1)/9) * 3+1,interrupt ((column (main! Grid a1)-1)/9) * 3+1): index (sol_board, interrupt ((column (main! A 1)-1)/9) * 3 +3, interrupt ((column (main! A 1) - 1)/9) * 3 +3)
Now we can choose this formula, except that it is easier. Interrupt, line and partial division mean that you will be in the RMS office every 9 lines. Move down the blocks of three lines to solve the office. There is a similar expression column that completes the same word. The second reference is exactly the same as the first reference, but it is shifted down two rows between the two columns, so there is a 3x3 square.
Now that we have this, we can write a big formula, including whether the onetonine value already exists in any row, column or big box, but we use abstraction again here to make the basic formula more simple. Let's create a new name called solution_in_rcb as "Are there any rows, columns or large boxes in the solution cell and my phone number?" This name only returns true or false (as part of the experiment, condition # 2 is not a paragraph). Although it is not short, it is actually very simple to write:
= or (COUNTIF (sol_row_from_val，ONETONINE) "0，COUNTIF (sol_col_from_val，ONETONINE)" 0)
Use this new name to make our new formula a valid cell:
= If (sol _ cell _ from _ val
This is not only easier to understand than this formula, but also leads to some definite places, and there is only one possible solution:
So we can look at some solutions, but the key is to feed them the solutions of these committees. This is the use of iteration. Next time we will use iteration and some formula techniques to solve some Sudoku.