- Главная
- Разное
- Дизайн
- Бизнес и предпринимательство
- Аналитика
- Образование
- Развлечения
- Красота и здоровье
- Финансы
- Государство
- Путешествия
- Спорт
- Недвижимость
- Армия
- Графика
- Культурология
- Еда и кулинария
- Лингвистика
- Английский язык
- Астрономия
- Алгебра
- Биология
- География
- Детские презентации
- Информатика
- История
- Литература
- Маркетинг
- Математика
- Медицина
- Менеджмент
- Музыка
- МХК
- Немецкий язык
- ОБЖ
- Обществознание
- Окружающий мир
- Педагогика
- Русский язык
- Технология
- Физика
- Философия
- Химия
- Шаблоны, картинки для презентаций
- Экология
- Экономика
- Юриспруденция
Spreadsheets for developers Felienne Hermans презентация
Содержание
- 1. Spreadsheets for developers Felienne Hermans
- 2. So
- 3. So
- 4. Spreadsheets are code
- 5. Spreadsheets are code
- 6. Spreadsheets are code
- 7. Spreadsheets are code
- 8. 1) Used for similar problems
- 9. This
- 10. I go
- 11. Here you
- 12. 2) Formulas are Turing complete
- 13. 3) They suffer from the same problems
- 14. Spreadsheets are code In summary: both the activities, complexity and problems are the same
- 15. And not just a programming language!
- 16. I argue that Excel is the next language to learn Resistance is futile!
- 17. Stukje Bret Victor hier live programming
- 18. pure functional You
- 19. pure functional You
- 20. Lingua franca of computing
- 21. Excel is the next language to learn
- 22. Excel is the next language to learn
- 23. Excel is the next language to learn
- 24. Excel is the next language to learn
- 25. Excel is the next language to learn
- 26. Excel is the next language to learn
- 27. Excel is the next language to learn
- 28. What we want to sort
- 29. The index
- 30. The first formulas is obvious: find the minimum
- 31. Little known fact: you could also use
- 32. Next is finding the location of the
- 33. MATCH results in 10: the minimum is found on index 10
- 34. Let’s start small and make a formula that places an “X" in the swap spots
- 35. So: if our index is equal to
- 36. So: if our index is equal to
- 37. Awww... Something went wrong!
- 38. By default, Excel transforms formulas by location, so this one is changed incorrectly
- 39. If we do not want that, we
- 40. If we do not want that, we
- 41. It works!
- 42. Let’s also fix the index row here, because we are dragging all this down later
- 43. We will swap based on the index, starting at 1
- 44. We also the swap value, which we can find with INDEX
- 45. We also the swap value, which we
- 46. INDEX takes as arguments: the range to
- 47. With this, we can edit the second
- 48. With this, we can edit the second
- 49. Works! We marked both swap spots with an X
- 50. Let’s fill in the easiest blank first,
- 51. Let’s fill in the easiest blank first,
- 52. Before we go any further, I added conditional formatting to indicate the swap spots
- 53. So what goes on this spot? If the index is equal to the swap spot...
- 54. So what goes on this spot? If
- 55. And if the index is swap, we output the swap value
- 56. Swapped!
- 57. Swapped! Looks like we are ready to draw all formulas down
- 59. Whoops! Something went wrong. Can you spot what?
- 60. 1 is picked as minimum everywhere
- 61. 1 is picked as minimum everywhere This range needs to shift right every step
- 62. We can use the OFFSET function for that
- 63. We can use the OFFSET function for
- 64. We can use the OFFSET function for
- 65. Works!
- 66. But let’s make things a bit scarier
- 67. PopQuiz! What does this mean?
- 68. PopQuiz! What does this mean? Let me give you a hint
- 69. Let me give you a hint
- 70. = Range1 Range2 results in a referece
- 71. We can use this to make our formula easier. Let’s call this range Index
- 72. With that, this becomes...
- 74. And adding ‘ E:E’ is optional. If
- 75. And adding ‘ E:E’ is optional. If
- 76. And adding ‘ E:E’ is optional. If
- 77. We can repeat this trick
- 78. We can repeat this trick So it becomes...
- 79. Isn’t that nice?
- 80. Isn’t that nice?
- 81. There’s just one problem, that ugly E3! Isn’t that nice?
- 82. To fix that, we need to dive into named ranges A2:B7
- 83. So far, we have used named ranges, to name, well, ranges.
- 84. So far, we have used named ranges,
- 85. So far, we have used named ranges,
- 86. All You Need is Love...
- 87. Love is All You Need!
- 88. Love is All You Need! But we
- 89. “However much I love you, You will always love me more”
- 90. “However much I love you, You will
- 91. We can put a constant in (nothing new so far)
- 93. But we can also put a formula in
- 95. Looks like a range, but is a formula
- 96. “Everyday I love you more” Named ranges got you covered again!
- 97. What we want now, is to refer
- 98. ROW(cell) results in the row of a
- 99. ROW(cell) results in the row of a
- 100. We can use that to create the address of the cell above, as such
- 101. The cell above
- 102. But we need the value of the
- 103. But we need the value of the
- 104. INDIRECT turns a string into a reference,
- 105. So this formula
- 106. So this formula Is equal to this one
- 107. We can use INDIRECT to get the reference to the above cell
- 108. The name is now equal to the value in the cell above
- 110. Just add the ‘van Buuren factor’
- 112. Now we have a formula in a
- 113. We can use this named ranged trick to get rid of that UGLY E3!
- 114. We can use this named ranged trick
- 115. TADAAA!
- 116. TADAAA! Doesn’t that read like a novel?
- 117. Similar, but a lot less concise! :)
- 118. If spreadsheets are code, can we apply software engineering methods to improve them?
- 119. If spreadsheets are code, can we apply
- 120. The conclusion is: More info: felienne.com/archives/2534
- 121. Because SE methods
- 122. Because SE methods
- 124. This formula is ‘smelly’: it can be improved by using an AVERAGE
- 125. You can ask BumbleBee for rewrites to apply
- 126. BumbleBee suggests a refactoring
- 127. And shows you how the new formula will look like
- 128. Click apply and your formula will be refactored!
- 129. Click apply and your formula will be refactored!
- 130. The transformations are programmable, with a small language
- 131. And of course, if you
- 132. And of course, if you
- 133. And of course, if you
- 134. Look at that! It is like a test
- 135. Look at that! It is like
- 136. So we built Expector: a tool that
- 137. Once the test formulas are saved, we can run them and validate the outcome
- 138. Once the test formulas are saved, we
- 139. We can even show ‘coverage’, by taking the cell dependencies into account
- 140. In a similar fashion, we can visualize non-tested cells, to help direct testing effort.
- 141. In a similar fashion, we can visualize
- 142. That’s all folks!
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.
In this deck, I explain you why.
Слайд 3
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.
In this deck, I explain you why.
People often think about spreadsheets as data, but that is a gross misslabeling.
Слайд 5Spreadsheets are code
I have made it my life’s work to spread
the happy word
“Spreadsheets are code!”
“Spreadsheets are code!”
Слайд 6Spreadsheets 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!”
If you don’t immediately believe me, I have three reasons*
* If you do believe me, skip the next 10 slides ;)
Слайд 9
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.
The problems Excel is used for are often (not always) similar to problems solved in different languages.
Слайд 10
I go to great lengths to make my point. To such
great lengths that I built a Turing machine in Excel, using formulas only.
Слайд 11
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.
This makes it, in addition to a proof that formulas are Turing complete,
Also a nice visualization of a Turing machine.
Слайд 12 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 :)
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 :)
Слайд 13 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.
Слайд 17Stukje 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.
Just type up your formula and you will get the result immediately.
Слайд 19pure 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.
No side effects possible.
Слайд 20Lingua 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!
Everyone knows this, expect for developers!
That’s just crazy!
Слайд 21Excel 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.
Слайд 22Excel 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:
To refresh your memory: selection sort sorts a list by repeatedly selecting the smallest value and putting it in the front:
Слайд 23Excel 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
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
Слайд 24Excel 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
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
Слайд 25Excel 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
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
Слайд 26Excel 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
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
Слайд 27Excel 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
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
Слайд 31Little 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)
(much like the more known A:A for the A column)
Слайд 32Next 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)
Слайд 35So: if our index is equal to the index of the
minimum,
we swap (X) otherwise we do nothing (_)
we swap (X) otherwise we do nothing (_)
Let’s start small and make a formula that places an “X" in the swap spots
Слайд 36So: if our index is equal to the index of the
minimum,
we swap (X) otherwise we do nothing (_)
we swap (X) otherwise we do nothing (_)
That looks cool, let’s drag it right
Слайд 39If we do not want that, we add a $ before
a reference to fix it. Now, only the row is updated
Слайд 40If 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
Слайд 45We 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
Слайд 46INDEX 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)
Слайд 48With 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’
Слайд 50Let’s fill in the easiest blank first, the _
In case we
do not swap, we can just use the value above
Слайд 51Let’s fill in the easiest blank first, the _
In case we
do not swap, we can just use the value above
Слайд 63We 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
Слайд 64We 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
Слайд 69Let 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.
Слайд 70= Range1 Range2 results in a referece to the intersection of
the two ranges. In this case C5 with value 3.
Слайд 74And adding ‘ E:E’ is optional. If you remove it, Excel
assumes you want the intersection of the range and the cell you are in.
Слайд 75And 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
Слайд 76And 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
Слайд 85So far, we have used named ranges, to name, well, ranges.
But
we can
also name:
strings
also name:
strings
All You Need is Love...
Слайд 90“However much I love you,
You will always love me more”
This too
can be expressed with a named range
Слайд 98ROW(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
Слайд 99ROW(cell) results in the row of a cell, for example ROW(A8)
= 8 Without arguments ROW returns the current row.
3 in this case
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
Слайд 103But we need the value of the cell rather than the
address. Excel’s got you covered!
We can use INDIRECT for this
Слайд 104INDIRECT 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
Слайд 114We 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)
Слайд 116TADAAA!
Doesn’t that read like a novel?
For comparison, I have written selection
sort in Python
Слайд 119If spreadsheets are code, can we apply software engineering methods to
improve them?
That is the central research question of my dissertation
Слайд 121
Because SE methods transfer so well, after my graduation, I built
a spreadsheet refactoring tool called BumbleBee.
Слайд 122
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.
Here you see the user interface in Excel 2010.
Слайд 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 how to get end-users to test?
This is already hard for
professional developers!
Слайд 133
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!
But how to get end-users to test?
This is already hard for
professional developers!
But spreadsheet users are already good testers!
Слайд 135Look at that!
It is like a test
We figured that rather
than learning spreadsheet users a new tool, we could exploit these formulas
Слайд 136So we built Expector: a tool that can detect these test
formulas and save them in a ‘test suite’
Слайд 138Once 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
Слайд 141In a similar fashion, we can visualize non-tested cells, to help
direct testing effort.
Also available:
felienne.com/Expector
Слайд 142
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
More info?
www.felienne.com
www.spreadsheetlab.org
Want to connect?
@felienne
mail@felienne.com
Spreadsheets are code
