Solving Sudoku using Excel #lambdafunctions #sudoku #excel #exceltips #novba #exceltips #excel

Показать описание

In October 2006, I left the UK to take a job in Redmond, working at Microsoft on Excel. I'd spent ten years in investment banking, and I fancied something different.

At Excel, I was on the "Calc" Team. I worked on things like formulas, functions, defined names, performance - anything to do with the core computation engine. The featured we worked on weren't usually very glitzy - we weren't doing 3D graphics or the like (we rarely had any UI at all), but I really enjoyed it.

There was a lot of good-natured rivalry in the team about who could make the most complicated spreadsheets. They had to be real spreadsheets though - none of this VBA nonsense. On the calc team, you did it with formulas or you... well, you went out on dates or something.

In winter 2008, a few of us got into the idea of building Sudoku solvers. I made one that could solve easy puzzles, but my Sudoku solving workbook was soundly outclassed by the beast of a spreadsheet that my boss, Charlie, came up with. It used iterative calc. It relied on that foible where worksheets are calced in alphabetical order.

My spreadsheet didn't solve Sudoku quite like Charlie's did, but it was nicer looking than his, and it was easier to understand. There was just one ugly part: At one point in the calculations, I had ended up with a list of digits that couldn't be in a given cell (e.g. "29374"), but needed a list of digits which could be in the cell (i.e. "1568"). I did come up with a way of calculating this, but the calculation involved three steps and it couldn't all be done in one formula. This meant I had to make three extra instances of the whole 9x9 Sudoku grid as interim calculations, and now my spreadsheet was ugly.

What I really needed was some way to define that conversion process as some sort of subroutine, but you can't do that in Excel.

We in the calc team had had a lot of ideas over the years about how to do something like this (we often called it "worksheet as a function") - we'd specced a few of them out, and we'd made some prototypes. But the concept never made it to production in the time I was on the team - our customers were generally clamouring for more pressing things, and changing core calc was risky because we had 500m users.

Fast-forward fourteen years. A few months ago, I learned that Excel has a new function called LAMBDA. This function allows you to define inputs and outputs, perform a calculation and then return a result. A simple LAMBDA function would be something like:

[In defined name "aPlusB"]
=LAMBDA(a,b,a+b)

[In a cell]
=aPlusB(1,2)
3

It's the "worksheet as a function" that we'd talked about back in 2008, and that I needed for my Sudoku solver. I sat down last week and made this new Sudoku spreadsheet, and it's quite a lot cleaner. It's not actually any cleverer than my original solver (so Charlie is safe), but it's much easier to explain.