Spreadsheets for developers Felienne Hermans


The Presentation inside:

Slide 0

Spreadsheets for developers Felienne Hermans @Felienne


Slide 1

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


Slide 2

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.


Slide 3

Spreadsheets are code


Slide 4

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


Slide 5

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 ;)


Slide 6

Spreadsheets are code


Slide 7

1) Used for similar problems


Slide 8

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.


Slide 9

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


Slide 10

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.


Slide 11

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 :)


Slide 12

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.


Slide 13

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


Slide 14

And not just a programming language!


Slide 15

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


Slide 16

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.


Slide 17

pure functional You love pure functional languages?


Slide 18

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.


Slide 19

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!


Slide 20

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.


Slide 21

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:


Slide 22

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


Slide 23

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


Slide 24

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


Slide 25

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


Slide 26

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


Slide 27

What we want to sort


Slide 28

The index


Slide 29

The first formulas is obvious: find the minimum


Slide 30

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)


Slide 31

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)


Slide 32

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


Slide 33

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


Slide 34

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


Slide 35

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


Slide 36

Awww... Something went wrong!


Slide 37

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


Slide 38

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


Slide 39

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


Slide 40

It works!


Slide 41

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


Slide 42

We will swap based on the index, starting at 1


Slide 43

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


Slide 44

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


Slide 45

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)


Slide 46

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


Slide 47

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’


Slide 48

Works! We marked both swap spots with an X


Slide 49

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


Slide 50

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


Slide 51

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


Slide 52

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


Slide 53

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


Slide 54

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


Slide 55

Swapped!


Slide 56

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


Slide 57


Slide 58

Whoops! Something went wrong. Can you spot what?


Slide 59

1 is picked as minimum everywhere


Slide 60

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


Slide 61

We can use the OFFSET function for that


Slide 62

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


Slide 63

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


Slide 64

Works!


Slide 65

But let’s make things a bit scarier


Slide 66

PopQuiz! What does this mean?


Slide 67

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


Slide 68

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.


Slide 69

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


Slide 70

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


Slide 71

With that, this becomes...


Slide 72


Slide 73

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.


Slide 74

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


Slide 75

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


Slide 76

We can repeat this trick


Slide 77

We can repeat this trick So it becomes...


Slide 78

Isn’t that nice?


Slide 79

Isn’t that nice?


Slide 80

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


Slide 81

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


Slide 82

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


Slide 83

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


Slide 84

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


Slide 85

All You Need is Love...


Slide 86

Love is All You Need!


Slide 87

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


Slide 88

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


Slide 89

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


Slide 90

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


Slide 91


Slide 92

But we can also put a formula in


Slide 93


Slide 94

Looks like a range, but is a formula


Slide 95

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


Slide 96

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


Slide 97

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


Slide 98

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


Slide 99

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


Slide 100

The cell above


Slide 101

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


Slide 102

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


Slide 103

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


Slide 104

So this formula


Slide 105

So this formula Is equal to this one


Slide 106

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


Slide 107

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


Slide 108


Slide 109

Just add the ‘van Buuren factor’


Slide 110


Slide 111

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


Slide 112

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


Slide 113

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)


Slide 114

TADAAA!


Slide 115

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


Slide 116

Similar, but a lot less concise! :)


Slide 117

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


Slide 118

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


Slide 119

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


Slide 120

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


Slide 121

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.


Slide 122


Slide 123

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


Slide 124

You can ask BumbleBee for rewrites to apply


Slide 125

BumbleBee suggests a refactoring


Slide 126

And shows you how the new formula will look like


Slide 127

Click apply and your formula will be refactored!


Slide 128

Click apply and your formula will be refactored!


Slide 129

The transformations are programmable, with a small language


Slide 130

And of course, if you say refactoring...


Slide 131

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!


Slide 132

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!


Slide 133

Look at that! It is like a test


Slide 134

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


Slide 135

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


Slide 136

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


Slide 137

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


Slide 138

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


Slide 139

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


Slide 140

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


Slide 141

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 [email protected] Spreadsheets are code


×

HTML:





Ссылка: