- Categories

If you like this presentation – show it...

Spreadsheets for developers Felienne Hermans @Felienne

So you are a developer? Why should you bother to learn spreadsheets? In this deck, I explain you why.

So you are a developer? Why should you bother to learn spreadsheets? In this deck, I explain you why. People often think about spreadsheets as data, but that is a gross misslabeling.

Spreadsheets are code

Spreadsheets are code I have made it my life’s work to spread the happy word “Spreadsheets are code!”

Spreadsheets are code I have made it my life’s work to spread the happy word “Spreadsheets are code!” If you don’t immediately believe me, I have three reasons* * If you do believe me, skip the next 10 slides ;)

Spreadsheets are code

1) Used for similar problems

This tool (for stock price computation) could have been built in any language. C, JavaScript, COBOL, or Excel. The problems Excel is used for are often (not always) similar to problems solved in different languages.

I go to great lengths to make my point. To such great lengths that I built a Turing machine in Excel, using formulas only.

Here you see it in action. Every row is an consecutive step of the tape. This makes it, in addition to a proof that formulas are Turing complete, Also a nice visualization of a Turing machine.

2) Formulas are Turing complete Here you see it in action. Every row is an consecutive step of the tape. This makes it, in addition to a proof that formulas are Turing complete, Also a nice visualization of a Turing machine. Many people liked it :)

3) They suffer from the same problems Finally, spreadsheets suffer from typicial ‘software’ problems like lack of documentation and a long lifespan during which many different users are involved.

Spreadsheets are code In summary: both the activities, complexity and problems are the same

And not just a programming language!

I argue that Excel is the next language to learn Resistance is futile!

Stukje Bret Victor hier live programming Spreadsheet are ‘live programming’ avant la lettre. What Bret Victor is been advocating for lately, we had that since VisiCalc! Just type up your formula and you will get the result immediately.

pure functional You love pure functional languages?

pure functional You love pure functional languages? We’ve got you covered. All a formula can do is take input and do something with it. No side effects possible.

Lingua franca of computing Finally, spreadsheets are the lingua franca of computing. You grandfather probably knows how to put a spreadsheet together for his savings. Your next door neighbour? Uses it for his fantasy football league. That history major you are helping out with his math work? I am sure he out-pivot tables you! Everyone knows this, expect for developers! That’s just crazy!

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power.

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front:

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front: 7-8-3-1-12-15

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front: 7-8-3-1-12-15 | 1 is the minimum, swap it with 7

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front: 1-8-3-7-12-15 | 1 is the minimum, swap it with 7

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front: 1-8-3-7-12-15 | Now 3 is the minimum, swap it with 8

Excel is the next language to learn I am just going to assume you are convinced and you want to learn spreadsheets. Therefore, I’ll implement selection sort in a spreadsheet to show you their power. To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front: 1-3-8-7-12-15 | Rinse and repeat

What we want to sort

The index

The first formulas is obvious: find the minimum

Little known fact: you could also use 3:3 here, to indicate the 3th row (much like the more known A:A for the A column)

Next is finding the location of the minimum, we can do that with MATCH MATCH takes as arguments: the search value, the range to search in and the type of match (exact or bigger/smaller)

MATCH results in 10: the minimum is found on index 10

Let’s start small and make a formula that places an “X" in the swap spots

So: if our index is equal to the index of the minimum, we swap (X) otherwise we do nothing (_) Let’s start small and make a formula that places an “X" in the swap spots

So: if our index is equal to the index of the minimum, we swap (X) otherwise we do nothing (_) That looks cool, let’s drag it right

Awww... Something went wrong!

By default, Excel transforms formulas by location, so this one is changed incorrectly

If we do not want that, we add a $ before a reference to fix it. Now, only the row is updated

If we do not want that, we add a $ before a reference to fix it. Now, only the row is updated Let’s try dragging again

It works!

Let’s also fix the index row here, because we are dragging all this down later

We will swap based on the index, starting at 1

We also the swap value, which we can find with INDEX

We also the swap value, which we can find with INDEX INDEX takes as arguments: the range to locate a value in, followed by the row and column

INDEX takes as arguments: the range to locate a value in, followed by the row and column We also the swap value, which we can find with INDEX We use row 1 (as we are looking in only one row) and the column in B4 (the index of the swap)

With this, we can edit the second branch of the if, to add the second swap situation

With this, we can edit the second branch of the if, to add the second swap situation We also swap is the index is equal to the ‘swap index’

Works! We marked both swap spots with an X

Let’s fill in the easiest blank first, the _ In case we do not swap, we can just use the value above

Let’s fill in the easiest blank first, the _ In case we do not swap, we can just use the value above

Before we go any further, I added conditional formatting to indicate the swap spots

So what goes on this spot? If the index is equal to the swap spot...

So what goes on this spot? If the index is equal to the swap spot, we output the minimum

And if the index is swap, we output the swap value

Swapped!

Swapped! Looks like we are ready to draw all formulas down

Whoops! Something went wrong. Can you spot what?

1 is picked as minimum everywhere

1 is picked as minimum everywhere This range needs to shift right every step

We can use the OFFSET function for that

We can use the OFFSET function for that OFFSET takes as arguments: the range you want to shift, followed by number of rows and number of columns

We can use the OFFSET function for that OFFSET takes as arguments: the range you want to shift, followed by number of rows and number of columns We’ll shift no rows down and B4 (swapindex)-1 left

Works!

But let’s make things a bit scarier

PopQuiz! What does this mean?

PopQuiz! What does this mean? Let me give you a hint

Let me give you a hint = Range1 Range2 results in a referece to the intersection of the two ranges. In this case C5 with value 3.

= Range1 Range2 results in a referece to the intersection of the two ranges. In this case C5 with value 3.

We can use this to make our formula easier. Let’s call this range Index

With that, this becomes...

And adding ‘ E:E’ is optional. If you remove it, Excel assumes you want the intersection of the range and the cell you are in.

And adding ‘ E:E’ is optional. If you remove it, Excel assumes you want the intersection of the range and the cell you are in. So we can simplify

And adding ‘ E:E’ is optional. If you remove it, Excel assumes you want the intersection of the range and the cell you are in. So we can simplify

We can repeat this trick

We can repeat this trick So it becomes...

Isn’t that nice?

Isn’t that nice?

There’s just one problem, that ugly E3! Isn’t that nice?

To fix that, we need to dive into named ranges A2:B7

So far, we have used named ranges, to name, well, ranges.

So far, we have used named ranges, to name, well, ranges. But we can also name:

So far, we have used named ranges, to name, well, ranges. But we can also name: strings All You Need is Love...

All You Need is Love...

Love is All You Need!

Love is All You Need! But we can name funkier stuff, let’s stick with the love theme!

“However much I love you, You will always love me more”

“However much I love you, You will always love me more” This too can be expressed with a named range

We can put a constant in (nothing new so far)

But we can also put a formula in

Looks like a range, but is a formula

“Everyday I love you more” Named ranges got you covered again!

What we want now, is to refer to the cell in C4, and then increase its value

ROW(cell) results in the row of a cell, for example ROW(A8) = 8 Without arguments ROW returns the current row. What we want now, is to refer to the cell in C4, and then increase its value We can use the ROW for that

ROW(cell) results in the row of a cell, for example ROW(A8) = 8 Without arguments ROW returns the current row. 3 in this case What we want now, is to refer to the cell in C4, and then increase its value We can use the ROW for that

We can use that to create the address of the cell above, as such

The cell above

But we need the value of the cell rather than the address. Excel’s got you covered!

But we need the value of the cell rather than the address. Excel’s got you covered! We can use INDIRECT for this

INDIRECT turns a string into a reference, and is in that sense similar to the ‘eval’ of JavaScript But we need the value of the cell rather than the address. Excel’s got you covered! We can use INDIRECT for this

So this formula

So this formula Is equal to this one

We can use INDIRECT to get the reference to the above cell

The name is now equal to the value in the cell above

Just add the ‘van Buuren factor’

Now we have a formula in a named range that depends on the cell you call it from

We can use this named ranged trick to get rid of that UGLY E3!

We can use this named ranged trick to get rid of that UGLY E3! By making a named range which points to the row above (we use the row:row syntax)

TADAAA!

TADAAA! Doesn’t that read like a novel? For comparison, I have written selection sort in Python

Similar, but a lot less concise! :)

If spreadsheets are code, can we apply software engineering methods to improve them?

If spreadsheets are code, can we apply software engineering methods to improve them? That is the central research question of my dissertation

The conclusion is: More info: felienne.com/archives/2534

Because SE methods transfer so well, after my graduation, I built a spreadsheet refactoring tool called BumbleBee.

Because SE methods transfer so well, after my graduation, I built a spreadsheet refactoring tool called BumbleBee. Here you see the user interface in Excel 2010.

This formula is ‘smelly’: it can be improved by using an AVERAGE

You can ask BumbleBee for rewrites to apply

BumbleBee suggests a refactoring

And shows you how the new formula will look like

Click apply and your formula will be refactored!

Click apply and your formula will be refactored!

The transformations are programmable, with a small language

And of course, if you say refactoring...

And of course, if you say refactoring, you say testing! When users modify their spreadsheet, theywant to be sure the fuinctionality of their spreadsheets remains the same. But how to get end-users to test? This is already hard for professional developers!

And of course, if you say refactoring, you say testing! When users modify their spreadsheet, theywant to be sure the fuinctionality of their spreadsheets remains the same. But how to get end-users to test? This is already hard for professional developers! But spreadsheet users are already good testers!

Look at that! It is like a test

Look at that! It is like a test We figured that rather than learning spreadsheet users a new tool, we could exploit these formulas

So we built Expector: a tool that can detect these test formulas and save them in a ‘test suite’

Once the test formulas are saved, we can run them and validate the outcome

Once the test formulas are saved, we can run them and validate the outcome We can even show ‘coverage’, by taking the cell dependencies into account

We can even show ‘coverage’, by taking the cell dependencies into account

In a similar fashion, we can visualize non-tested cells, to help direct testing effort.

In a similar fashion, we can visualize non-tested cells, to help direct testing effort. Also available: felienne.com/Expector

That’s all folks! Thanks for watching my talk on SlideShare! Don’t forget that: More info? www.felienne.com www.spreadsheetlab.org Want to connect? @felienne mail@felienne.com Spreadsheets are code

×

HTML:

Ссылка:

Or sign in using your account

Do you have the account? Create account