'

Spreadsheets for developers Felienne Hermans

Понравилась презентация – покажи это...





Слайд 0

Spreadsheets for developers Felienne Hermans @Felienne


Слайд 1

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


Слайд 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.


Слайд 3

Spreadsheets are code


Слайд 4

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


Слайд 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 ;)


Слайд 6

Spreadsheets are code


Слайд 7

1) Used for similar problems


Слайд 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.


Слайд 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.


Слайд 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.


Слайд 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 :)


Слайд 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.


Слайд 13

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


Слайд 14

And not just a programming language!


Слайд 15

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


Слайд 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.


Слайд 17

pure functional You love pure functional languages?


Слайд 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.


Слайд 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!


Слайд 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.


Слайд 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:


Слайд 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


Слайд 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


Слайд 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


Слайд 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


Слайд 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


Слайд 27

What we want to sort


Слайд 28

The index


Слайд 29

The first formulas is obvious: find the minimum


Слайд 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)


Слайд 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)


Слайд 32

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


Слайд 33

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


Слайд 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


Слайд 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


Слайд 36

Awww... Something went wrong!


Слайд 37

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


Слайд 38

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


Слайд 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


Слайд 40

It works!


Слайд 41

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


Слайд 42

We will swap based on the index, starting at 1


Слайд 43

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


Слайд 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


Слайд 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)


Слайд 46

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


Слайд 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’


Слайд 48

Works! We marked both swap spots with an X


Слайд 49

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


Слайд 50

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


Слайд 51

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


Слайд 52

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


Слайд 53

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


Слайд 54

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


Слайд 55

Swapped!


Слайд 56

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


Слайд 57


Слайд 58

Whoops! Something went wrong. Can you spot what?


Слайд 59

1 is picked as minimum everywhere


Слайд 60

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


Слайд 61

We can use the OFFSET function for that


Слайд 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


Слайд 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


Слайд 64

Works!


Слайд 65

But let’s make things a bit scarier


Слайд 66

PopQuiz! What does this mean?


Слайд 67

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


Слайд 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.


Слайд 69

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


Слайд 70

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


Слайд 71

With that, this becomes...


Слайд 72


Слайд 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.


Слайд 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


Слайд 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


Слайд 76

We can repeat this trick


Слайд 77

We can repeat this trick So it becomes...


Слайд 78

Isn’t that nice?


Слайд 79

Isn’t that nice?


Слайд 80

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


Слайд 81

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


Слайд 82

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


Слайд 83

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


Слайд 84

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


Слайд 85

All You Need is Love...


Слайд 86

Love is All You Need!


Слайд 87

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


Слайд 88

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


Слайд 89

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


Слайд 90

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


Слайд 91


Слайд 92

But we can also put a formula in


Слайд 93


Слайд 94

Looks like a range, but is a formula


Слайд 95

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


Слайд 96

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


Слайд 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


Слайд 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


Слайд 99

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


Слайд 100

The cell above


Слайд 101

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


Слайд 102

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


Слайд 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


Слайд 104

So this formula


Слайд 105

So this formula Is equal to this one


Слайд 106

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


Слайд 107

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


Слайд 108


Слайд 109

Just add the ‘van Buuren factor’


Слайд 110


Слайд 111

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


Слайд 112

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


Слайд 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)


Слайд 114

TADAAA!


Слайд 115

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


Слайд 116

Similar, but a lot less concise! :)


Слайд 117

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


Слайд 118

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


Слайд 119

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


Слайд 120

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


Слайд 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.


Слайд 122


Слайд 123

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


Слайд 124

You can ask BumbleBee for rewrites to apply


Слайд 125

BumbleBee suggests a refactoring


Слайд 126

And shows you how the new formula will look like


Слайд 127

Click apply and your formula will be refactored!


Слайд 128

Click apply and your formula will be refactored!


Слайд 129

The transformations are programmable, with a small language


Слайд 130

And of course, if you say refactoring...


Слайд 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!


Слайд 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!


Слайд 133

Look at that! It is like a test


Слайд 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


Слайд 135

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


Слайд 136

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


Слайд 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


Слайд 138

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


Слайд 139

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


Слайд 140

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


Слайд 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 mail@felienne.com Spreadsheets are code


×

HTML:





Ссылка: