I work on the team responsible for the design and development of future versions of Microsoft Excel. My focus is on the calculation engine and Excel's formula language. Prior to joining the team, I worked in corporate advisory and specialized in financial modelling.Перейти в профиль
Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas. Join us to understand how you can leverage this powerful new feature!
Everyone. Thanks for coming along to the session. My name is Joe. I'm a program manager on the Excel team. And then I work on formulas and functions and the couch engine. So all things to do with calculator. And get some background I am I used to work as a financial analyst. I worked in probably 10 years building models spending a lot of time building very big spreadsheets. And then I had the opportunity to join the team to help improve it and do some interesting things and the last five years. I've
been doing this and there's a lot of things that I learnt of the last 5 years that I never knew before joining the team, share some of that with you. So this is a 400 level session we're going to go in pretty deep. We're not going to cover some of the basics because we going to seem like you you're familiar with writing formulas and that you may have seen array formulas and a lot of people uncomfortable with the right woman is but you have an idea that they exist you've seen them around. And by the end of the session I'm confident that you will be the formula
expert in your office. And as long as you don't bring your colleague along, that would be a critical mistake. Okay, let's see. This is our agenda. We're going to take off we're going to take a look at the evolution of formulas in Excel make cells been around for a long time. So is it a timeline and I'll give you a demo you may have seen at them a few times they with me on a burner for Massena. So we're going to go through that demo and then we're going to go in and do an evaluation Deep
dive going to do a quiz just to find highlight what we know and what we don't know and then I'm going to jump into this describing some devaluation money that we have an Excel function signatures and how they affect evaluation. I'm going to take a look at the re-evaluation behave is not really is kind of the core. She can you understand the revaluation behaviors rightfulness will be possible to work with understand. And then finally, we'll go back to the array the dynamic to write down my but this time I'll explain in a bit more detail where those different array behaviors that we discussed
apply. So we'll just revisit that and explain a bit better. formulas in Excel today in Excel. So here are not running Office 365 and you're not in the inside is channel you dealing with emotion of excel that? Return to single value for every formula rise. She write a formula that you get one value you want another value you write a new formula copy paste at not just kind of what we've been used to as XL uses. It was always array formulas, but there was a kind of black magic thing that some people did and
they would like significant negatives for that. So many people resort to that. Napa take a look at the history of array formulas Excel introduced array formulas back. At the start of this isn't timeline for Excel for Windows and Excel for Windows. There was version 2 was the first version version one was actually on the on the Mac. This is way back. Then I haven't concerned if we hadn't control shift enter a formula conversion one. I couldn't find an old Mac to go and test it on and I believe it was there, but it was definitely windows. And a
rifle Miss have been largely untried nothing's really changed since then on the calculation front until now in Excel 365. We are introducing Dynamic arrays, which is a fundamental change to the way formulas evaluate and calculate it's a big change. And if you want any version in between version 2 of Excel and Excel 2019, you going to be using all formulas and we'll talk about what that means. This will not be coming to excel 2019 is going to be in Office 365.
The Legacy array formulas Excel to through Excel 2019 prices around the formula using is Kelly Price like that and what that really tells you is that excels going to use a different evaluation multivalued that formula. Does control shift enter formulas could spend multiple cells so you can say so I can block hit answer that Uncle ship center and give it a big range and it would end up committing that true that right. I might even just scoot across here to give you.
So I'm just going to enter a formula hear the M unit fractions been around for a long time. Not many people use it because it's kind of a mathematical thing. This is going to be the unit control shift into that. AMC I get this is Kelly prices around its I get my re formula and I get this an output from a single formula negatives. I can't delete that individually. I completed as a group that kind of pain and if I want to modify that I have to Modern slick everything in modified is a hole in the Rock really indication that they Blanca stats
formulas. They weren't they weren't widely used in people wouldn't write down for them. Get back. I guess but they did all the key array logic thing. So we introduced that back in the 80s the array logic in in all formulas. They do things like broadcasting listing and Pennywise arithmetic and those things are probably by the end of the session you understand what each one of those are those are the three things that you need to understand if you wanted to stand array formulas. And this is just a list of a significant
negatives. They can't read imagine. You got to figure out how big they are ahead of time. They're hard to work with you can contact later and you got to like do wacky things collect them as a set and then not discoverable like no one's ever going to hit Ctrl shift into by accident is not going to happen. Someone has to tell you you have to bump into the spreadsheet so that they're really kind of weird. Now to fix a love that we're introducing Dynamic rise. So answer Dynamic rise. What's Dynamic arrays in natively understands
array formula Excel will just spill it to the grid little spill to the neighboring cells if there's enough space. This is really array formulas be too and he is the key thing. This is the default formula type for Excel going forward. So these are Excel formula in the future that will just be if you won't even think about what they used to be. It'll just be dynamic array formulas everywhere. But I'll just go back to send a message and see if you have a phone when it was returning instead of names in the past. We couldn't even give you one time. We would do something cool things to see in
the section to be familiar with that later on. But what we do is pull one value from that right and we'll just pop it into selling side as you're one value and that's not useful if you trying to do something like ass or three unique and what we do now is whilst Alexa. Now what sign am I to raise where is using 6 new functions and power going to give you some fun things to do with that. We've got unique. We got soul. We got soul by sequins and Randall Ray. The
first to be a really big hero ones go to UNI consult anorexic and bulimic functions and people doing wacky things in The Rifleman the past they were doing these Wheel Road reminder throws things to generate sequences that was just an efficient and very difficult to read. So we introduced sequence ran away already helped some things like on to call analysis. Will you need to generate large amounts of random numbers anyone can see in them? No, just talkin about some availability. This feature is dynamic arrays was announced at ignite September 2018, and it's a cool change
to excel. So we've been stabilizing that we can work with iPod knows they be looking at the radon's making sure everything works with us. And so it's still coming soon currently. We've rolled out throughout Microsoft to order Microsoft using it. Corporate everywhere. We're testing it internally and we're at a position that we're hooking to progress further through the public ring. So, could you find a release date but is progressing very well. I'm coming soon as something that's worth understanding now because it's a big change. We Got Talent jump into a demo
if you've been to any of the other sessions you probably seen this demo so just bear with me it's going to be short and a few people that haven't seen it and then we will revisit this later with our Newfound knowledge I guess I hear I have a set of sails. I got some products sold out of the reason I was told in it, but you not surprised and I'm the revenue. The first thing I want to do is do something else. I want to calculate the number of units sold for each of the product. Now you could
do a pivot table do tables have some disadvantages, but you could also use the new unique function. So I go equals unique. And I got to say give me the unique Converse unique function is going to return an array. When it returns at a ray that are raised going to school to the grid. So I just had to answer and it suppose one formula many values. We have a blue border around that to show that that is an array and they're all related stuff. I got outside you'll see we indicate that that is a grouping. On the formula bar is the formula only lives in the
top-left cell. If you look at the formula on the top left so you can see the formula to go to the other ones. There's a ghost in formula to indicate. It's calculated by formula one. That's not really there so I can hit up to you'll see it just about right there. if I type over the top of this Write it enter the formula which is in the top still there it just Constable anymore this something in the way. So rolls up gives me a pound Scalera, which is a new era time when you interact with a terrorist shows you where would like to go? Now that I cut that
border I can go down here and I can just delete the obstruction and the second I do is filled. So these things I'm a lightweight. They're not going to damage your spreadsheet then I'll come over right to data just going to roll out because not enough space and I'll let you go and correct. The issue makes a person's face in the spreadsheet. Knock on my list. That's nice. You ready greatest that was so too. So I'm just going to go and sort that straight away. So I'm going to give that a store country. So so function takes for arguments will just use the simple version for now.
And then I'm going to do some it's now the reason I'm demonstrating some if now is this Amma fizza hasn't been around forever and you'll see that we're going to be able to get this thing to school to automatically and before I do that. I just want to show you how deeply ingrained Dynamic arrays is an accountant. So I'm just gonna go equals and I'm not even going to use the formula. I was going to say a line through a 12 oz equals that range no function anywhere if identify the values from a cell
So just because that isn't a condom because I just said equals that is not now and then just go down. But now I'm going to use it with the summer functions the sum if give me the products are here. Hi, Terry. I was going to give it a single one for now. And then I'm going to say all these units over here and I hit answer and that'll give me the number to charging station sold. Now. What you doing the pasta to copy that down and you be done but then the next day they come along and update you a spreadsheet with ABC News sales.
and all the sudden meeting a VR headset and the unique list of expanded but the number of units sold has not expanded because it's being driven Z formation for 21. So if you need to do is copy their value spreadsheet remember to read I mention things you don't have other provision because it's ugly. And so what we did was we added a new way of referencing a real spill the rise. And you'll see that now so I'm going to start up on the Slick 3. And if I am selected
M3 Elementary CH 9 through 12 11, well and I hit enter there that'll spill you know, why that spells later on we're going to talk about. All right formulas and why this is behaving the way it does. But when I expand to the entire array, we get a new notation 89 pound. And that says that's what we were referring to as a spool ref and it says get meeting High School range for the same as saline H9. So I hit answer there and I've got my spill. And when I go along here, and I'm just going to get rid of this VR headset see
anyone in the list which will mean that the unique list will shrink and because my stomach is depending on that this time it's going to straight to the spreadsheet is dimensioning and self-sufficiency is just growing as to the date of this coming into it so you don't have to go and make changes to it just because your date has changed. Adam final formula I want to demonstrate has the filter function now. This is going to show you a couple of things. Amazing things here is that it's a raisin Excel adjust 2D XL
is the only programming language in the world that he has to the arrays. We don't have a concept of one day rise. We just do 2D arrays ask is masturbating a piece of bread. So I'm going to go and filter and I'm just collecting this entire table. Now. I have some questions at the Lost sessions saying or do we need to always use this with name Granger name things now, you don't see any reason. I'm just using this for Simplicity of the formula. I'm so I'm going to take the sales table over there
and I'm going to include anything where the region equals the value in the cell function wax. First argument is the data in the second argument is an array of food Liam's truth and false has if it's true. We keep it if it's false. We throw that throw away. So it has to be the same dimensions as your data and and that's what we're going to generate here. You'll understand why this generation the right later on but that a century generation the right. And we end up getting a way to dispose of all the sales in Kentucky and I can go and switch that
and this Sunday to build to make it easy to select a different sites. I didn't see how this is redone interesting looking at is just three formulas. That's it. And it's doing something that would probably have taken a full day to build if you could get there and it would have had serious negatives in the past because you would have had some over into range has to deal with resizing. So three formulas only three places you can make an era is doing stuff you could never do in the past.
And it was way faster save town of time a lot of fun and one of my favorite comments on the forums and he was going to read a post. Someone said it made them feel like an evil with an S. Kind of I think I've been pretty much sums it up. So I'm going to stop this one and we got this erase feeling on the right and We want to I want to sort that I'm in descending order. I want the highest sales at the top. So if anyone sold the sort index here, we going to be in next to the thing you want to sort and we're going to sort on Revenue which happens to be the six column. So I'm going to stay
on the sick, and I want to say descending and that is it and if I hit enter their we now have the sorted in descending order. And I will stay in descending order for really simple to Rock'n'Roll urinalysis getting sorted make it look good. Once we've gone through some of the couch topics Switchback. So we're probably going to be looking at slides for the next half hour. So, let's Dive In The kids autism Dynamic arrays more capable Foster to build less transparent and it's difficult to understand the importance of that last one. You formulas means less after of you means less
chance of error means more liable spreadsheets. It's it's a great advantage to use these whenever possible. Okay, so let's dive in to evaluation. Is a quiz you going to school yourself in trouble if you fail if you get a hundred percent, don't tell me you're expected not to get them. All right you with 5 scenarios hear the date is all the same. They will have just one two, three four in the grid and then we've got two formulas if the formula is entered in Old Excel and if the formula was invented as an array formula Excel, so we just going to take a look at it
the Highlight where they might be some deficiencies Municipal formula. So you might expect that, you know the answer but what's the old Excel formula e formula the old Excel formula is what would happen if you enter in Excel 2019 and before and The Rifleman is what would happen if you're interested in Dynamic array Excel. Or you control shift into it in older versions of Excel formula and what you have in in production Excel today. First question what would happen if you enter that in Old Excel?
Alianza B2 Maybe not what you expected. Will dig into that in a bit. I'm not going to tell you why these things do these things just yet. We'll talk about at the end if you array formula into that. fantasy 2 you just get one. We're just calculating the value of C2. So what would happen in situ? Yes, that's the one scenario scenario in Excel if I entered B1 or B2 so equals this rain. As a regular formula, what would I get? You got a pound value if you're right formula answer that same thing. ocse into that thing you end up getting
I think by the end of this you will probably be questioning why all the cells are turning what it's returning to excel you have right now you understand in the end. You will probably end up spotting at the rifleman's actually simpler to understand. On The Rifleman scarring kind of have a bad rap because people do crazy things with them. Then he have a resort to it when they're going you fear it but honestly array formulas have is less complex theater. k so if we entered equals a 1 through a
2 + 1 into the cell cycle is this range + 1 in Excel today? cracked And then if you're right into that. Now we wrapped it in the sum. This thing which we had in the previous one and wrap it in the song. ounces 323 And if you re formula answer that A1 for a 2 + 1 + UCSC are oriented and dynamic array Excel you end up getting five. And then let me backup 1 second. Hippies in mind the next slide is really the same thing except the A1 or A2 plus one gets put into a named formula C create a name. And you say the name is A1 to A2 + 1
and I know cold at exact same thing the A1 or A2 which is the name and I'm just putting it in here now and I answer that as a regular formula. and now it gives me 5 and he has the same. Listen Rifleman. So what's going on there? Get out of percent. Good otherwise. What you could have taken the rest of session. So what's going on here? Let's understand what's going on. Why are for misbehaving this way? XS Army has to grid evaluation mods. Yeah. And they differ and how they treat their arguments. The default you have today in Excel Excel 2019 and before and non Dynamic
array 365 is the default is what's known as range in Parsippany intersecting evaluation. We'll explain what the Simplicity intersecting is in a second. And then we have a re-evaluation. And this is used by Legacy CSC rise, and it's also used by Dynamic arise and this will be the only valuation mode in future Excel. If you want to do something similar to what this one's doing, there's an operator for you as the app operator, which will trigger this thing called implicit intersection in a second section is the true difference between these two models.
This one doesn't this one doesn't do it. You want to do it in this one you got to ask it to do it. It's not going to do it for you. Intersection. What is it? That was an old Excel Excel 2019 2016 or does the returns the length of the string dividing it? Typically you provided with one string and if you provide it with multiple, this is how it behaves so if I entered over here It returns 5K if I end to that exact same formula exactly the same formula character for character the same
and I hit enter. Here it gives me. Why the difference? The difference is infested intersection. The men's formula function expects. One thing I did it with multiple. We provided with the rain. So what does the name Felicity intersecting count is it says you've given me a Range expected one thing. I'm going to pick one from that rain. And what is does kicks the one on the same row will call him? That's what's Happening Here. The reason this function is returning eighth is because no backup on. the reason I
pretending ice is because it's pitching sell a 4K it says I only expect expect one. So I'm just going to pick one that I feel like it I'm using the rule and I truly respect what I'm saying right now. I'm just going to come to the backbone and give you the results back pain. Exact same formula down here. Era, because there is nothing on the same road as well. I can figure it out so you can narrow it down here. Now that there's multiple things in the same column.
Because an error is too because the configured out which one from that cone. So that's interesting section is the default behavior in Excel today and going away. That is the key thing that changes with the introduction of dynamic the rise. So if you want to actually do this in new Excel. When you enter if you wanted to see havea and very few people want this behavior analyst do once his behavior was they do that named ranges by carnifex? And then what they do is they just go Dale does call it there was
only spreadsheets lineup. So then if they call it in a particular column it'll refers to the fire Netflix right on another sheet for the same but most people don't want this. But if you did you can put an ass in front of that. So you'd go Len at a two-column a7r you replicate the same behavior to some people still want that behavior very few people, but some people And there's a way for them to do that. Judging by the puzzle faces in the audience as to why this Behavior swear. I'm assuming not a lot of people the unhappiness went away, which is a good
thing. Let's talk about function arguments and Emily looked at that length function years old Behavior changing based on what the arguments are. The one of the key things under the hood is Excel function arguments have expectations. And if you give them something they don't expect they do something that they haven't defined behavior for that thing. If we take a look at this. Some examples here. I've got the sum function and sometimes it takes a numbers with square roots. It takes a number of the vlookup function with a lot of
people love not my favorite, but there it is then as index and match these to wipe it in my opinion, but they go they take some arguments and you have you operate this which you can think of its functions as well. Just like they take about you text me about you tonight do something on it just happened to be in a different way. You could have called A Plus function the plus and then you give it to us again. But all these arguments have expectations. You can't really see it when you look at them, but you can inferred so I can take a look at
some function expect a multi. Some multi is a multiple values a function is expecting a multi reach with arguments the square root function just expects to single value expecting something we pull a scalar which is just a value. And then it has its expecting about you. So this is the thing is looking up. It's expecting a multi for the table. So saying up because this rain and then you giving us a call for seriously. This is actually a multi so you can provide call him to
1, 3, 5 and will return Calling the 1/3 and 5 with that is been skipping out + 2 + 4 But you have to rain in tonight to see how many people have you use this? I have already seen it used in the wild if you could in theory do it and then there is the final argument which is a right hook up the volume. True false. So you say do naproxen astronauts kind of thing and that's expected to be a scalar. So this is expectations under the hood GTA V in expansion specs of multi. So that's the block is looking up and then it takes to Skylar's so I want
this round does Colin Allen Iverson? Skip to the operator again. I'm operators typically expect. I'm Skyler Luv is a single plus single thing on the ride or single thing on the left / single thing on the right today dealing with scale is so this is something to keep in mind. What is an array? and raise a collection of values unique to excel array array as always have two Dimensions makes it really easy to map erase the Grid on the grid to arise but the important thing is I get that a second we got is a 1 by 5, right? So this is not
just a Five Element array. We always have two dimensions on the right in Excel. And this is a 5 by 1 array and those two things are different because it's always a direction to us by the trail. Why is it called of mice? And this is a 3 by 3 array array of arrays ranges ranges ranges Kimiko s to write sometimes but sometimes not so if you have a function like the row function you can say what row is a 3 San Jose, it's Row 3, but you can say what row is an array it will fail because an array doesn't exist in the grid. It says no row
It's associated integrated two different things. You can if you set function you can use an offset on the Range Recon using off sit on the right the different that difference really comes through an old Excel when you try and do the empress in section because we supposed to be in the SEC Ranger doesn't to see intersect. All right, so it's not a curiosity an existing Excel. Now, let's talk about creating in the Rye. There are two ways you can create and write. Says you statically create the right.
So you just give it a braces and you specify the numbers so, is denied you call Amos and semicolons on Gyros. I think if you want to. Here are some examples of 12345 between it's going to be a r o r a r u semicolons. It's a column array and if I use a mixture of, semicolons, I can create a 2d array that have to be as many roses call as you have the same number of elements. The way of creating an array is by using a formula that returns and write so in the past and all the celebrities m-unit weird one your
frequency function would return and right but they were very limited number of functions that sell. UDF grocery Tron price now in New Excel we have the sequence function which we have a unique function function. And this was Third Way of creating it which is through lifting now listing causes a function that doesn't return a raise to return the right and we'll talk about that behavior. Those are in the array Behavior section. Let's talk about a raise. Oh. nxl today Excel 2019 MP4 The grid is
basically only place that you don't encounter, right even though everything else is arrival to email. The grid is not normally use name formulas that is already re-evaluated. Even an old Excel conditional formatting. If you specify using a formula, that's a re-evaluation re-evaluated the formula bar itself a re-evaluation reviews. The S9 will see some examples in a bit. I'm using that to explain some of the behaviors and basically Everything in Excel
is re-evaluated with exception of the Grid in Old Excel and we're going to make it consistent. So everything is just going to be in your life. Now here's an interesting thing. Are implicitly intersecting formulas the ones that you have right now in Excel nonner a equals the sum of that plus that in the sun, right? And I answer that. I get. That's what you'd expect basal insulin to section thing I said before so we've got the class A classes expecting a scalar on the left and right but you giving it a range. So it's going to pick one and is going to pick the one in the same call on
this isn't this one in this one? It's an atom together and give you to get 18 explain when I get 18 Broadcasting. If an old Excel Eye instead enter one, two, one, one, one, one plus one semicolon one semicolon. What is the representation of these things as a raise and I just answered that no control shift into just answer just answer. I got 18 exactly the same as control center. And that's because regular formulas in Excel today exhibit. Only right behaviors except they have one except the one exception and that isn't this the intersection of Rangers only
ranges. They don't All right today in Excel the finest old mm. Just moving to erase on this route. And I told that these lines Epiphany parallel then look at to me but when I use a ruler they are the world is not as it seemed in the past older. I always felt it was an alright behaviors except for one interested in setting formulas the array Behavior. What are they how to write formulas work? And how do you leverage? The first thing is listening when is scalar is expected when I buy
formula but you give it an array excelling Tommy says well that thing I expect one thing. So what I'm going to do is I'm going to give it each value value from that already. I'm going to get fiance on the stitch together a return array. So I end up generating an honorary as a retainer, right? the square root function for instance if I give a square root function an array so you can see I'm getting an array 1234 right and if I hit answer there And you Excel or icse into that was actually if you regularly answer that to you'll get the same because
regular old Excel does array calc when given an array onion pussy and aside from giving a rain so that will end up giving you a ride back. And the reason he gives you that a right back is because of lifting so we apply the ray to the function has dropped on return result. That is lifting. That's the one where a behavior The next one is pairwise Operation. So what we do is when we have multiple scale is right and you provided multiple arrays what we do is we do a pair
of pairwise operation. So if I have one semicolon two semitones, please add these elements pairwise. And then we get the return array. glass pairwise operation and we have pairwise operation. That's two of the three. And then the Lost of lost the right behavior is broadcasting. Now how broadcasting works is this all we wanted to do pairwise operation. Will I need to pay wise operation if you have passed so we try and create his if I have the array 123 123 and I X 3 or I
have one two three in a x and a single element array of 3 we want to do this is how I so we need to expand this out. So that we can then do it element by element. So what we do is we copy that down in the case of a 1 by or scalar and then we multiply it across case of paralyzed. if we have an array that is not won by we don't know which elements to copy down. So what we do is we just put pounding a there and then we multiply to Crossing to pay wise fashion
said we get three six down tonight. And that's the way we expand a raised to be the same Dimension as other arrays in that formula and will do that for a given set of scales. If you provide multiple arrays a scale of arguments. We will size the mole to be the same and we will expand them using broadcasting. So that's really it is one more coat translate, but that's just to run K2 down to a single value bcsc is Andrew. That's really not Behavior Dynamic arrays has I'm so kissable Talking Zombies
Advanced example of scalar and I'm adding summarize the first one to scale of then it's a three by one one by for excetera. Excetera to fit them all. We need a 3 by 4 array at the end of this want to do this in pairwise fashion. So we know our final result size and we got all our raised in between and then we speak to Adam expandable to the same size. So the one is easy. We just wrote down we go to cross. The three by one is easy to just throw it across the one by fours easy mcspadden down the one by two little more complex. So extend that down. That's fine. But when
they could have been different values, we don't know which one we should be copying so we can fill it with his and over here I go to to buy too. Well, I don't know what to copy down and I don't know what to copy across to get pounded and we just add the Milana pairwise fashion and you get the end result. Truncating a race which exists for CSE rise? Because if you entered in a single cell, which is going to pick up any given us enough space for one cell. So we're just going to give you $1 back. I can't get us to
South. We're going to give you two. I'm sorry this why wasn't returning AT&T. Well, that's because in Old Excel we do broadcast broadcast. We are the elements. Rustam the elements. That's how that worked. This is kind of a summary slide. In Old Excel, we have impulsively intersecting evaluation, which influenza B intersected range is only notarized only Rangers broadcast arrays and a lifted arise and they would not self dimensioning with array of that evil witch is coming. Directions to the behavior for
CSE section doesn't exist. Everything is broadcast ranges or raised and we left everything Rangers or rice and I need self dimensioning for dynamic arrays. Yes and Paseo see the size and length formula and formatting all the rest. You'll see that line up the line up with a razor. So this is really consistent implicitly intersecting if I was an anomaly. He is a comprehensive example and old Excel. I take an array arranged and I add the array 123 on this one two, one two, one two,
three. We've lost a single range from now we end up getting a value from it we broadcast out. So we resize those things and then re-add them are wise and then because it's an old Excel formula in a single cell is going to pull that one value when you get to the leave for that's what would have happened internally if we take a look at new Excel. So this is CSC or dynamic Drake's album. You have your array. We end up pulling out the values. We use them all. We don't throw anything away. We roll cost outs to go and create a
similarly-sized. I'm a re so that we can do pairwise operation. We then do the pairwise operation of adding a mole. And if it's a CSE we truncate if it's a dynamic array, we just spill you say there's your right. That's what you calculated if we don't throw anything away. So let's revisit that damn I had earlier and we'll talk about why things behave the way they did. The first one was the summit. So we take a look at the stomach function here. And I'm going to try to make it bigger
so you can see. Seneca Summit now the reason to Summit spilled is the summit function signature expect a multi. a scalar And a multi, can I search how do I sense the area with tracking? What we want to some the range on a son that the criteria is like a single thing. What are you what do you want? Do you want to ask, you know digital projectors and found them all but when I provided multiple values and it was expecting a scalar we left so we call that multiple times and we were turning away. So that's the behavior you seen here using lifting and lifting a listing is it means that
every function in Excel deal with an array you can provide an array to any argument argument expected to rise in which case it'll do what it does with the race or if it expects us Kayla and you give them a right it's going to list. So there is no scenario. You cannot provide an array to function in Excel. You can provide a raise everywhere so I can do some interesting things. Send this case we using listing we providing an array to something to be expected and we getting an array return through lifting now, please skip across here. We take a look at the ad
and I was going to simplify. And then get the sort out the way just so nice to look at. So we got the filter function functions interesting is expecting any food is an array of things to include once in a ride there and we're getting it in the right. But if we take a look at our calculating near Raywood giving it I'm saying the sales region which is an array as arranged. But if I hit up 9 you can see and that is there muscle that he's innocent multi-body Ryan said he didn't know and you
can see these are all the values coming through from that sale area is a great trick by the way using Excel when you want to figure out what something is returning just remembered that he'd escaped because if you'd answer that's going to come if then formulas But I got this rain multi-cell range and I'm saying equals a scalar and sex single-celled thing. The equals here is expecting a scale on the left and a scalar on the right. I give him a ride. Okay. So what it's going to do is it's going to try broadcast out that scale which is the m6 on the
right to be equal size should be right on the left. Okay broadcast that out. So I end up with two arrays in memory. I have the one which is the sales region and I have this New Jersey which is now just a bunch of copies of New Jersey to NRA the same size and a does a pairwise comparison of each of those. Okay. So now I come pass them all once it's done comparing animal. It returns an array of all the results if I select this and hit F9, you'll see it's going to give me a bunch of Truth and false has K whole bunch of them and just to make it simpler to understand. I'm going to
really reduce the size of my data. So just reduced its like 7 columns. and you should now be able to see the results. It'll be 7 truth and pulses whiteness. If I get my 7 trees and false has a function works is if it's also throw it away if it's a true keeps it so I know which is why I get the error in a little Spanish when you can provide it. Argument to say what to do if is nothing so I'm going to say nothing. So that's what's happening there and you can use this in interesting ways. So for instance may take with my data and get all my data back.
If I go and if I wanted to stay I want to do an or so. I've got my New Jersey over there and I want to fill to this list to two states. Don't lie to an or so I've got this which is returning as we stole an array. Well, if we generate another array and we add them together we could it'll do it'll pairwise fashion. And then that resulting array will be fed into my filter. So what I'm going to do is I'm just going to wrap this condition instead of brackets
Plus. and I'm going to go and say he's a sample Master region equals that I live in the South Carolina and New Jersey sales come through and I do and that is because of the pairwise operation. So this one here is going to generate an array expanse of Scylla and then it does pairwise operation to generate in the rain by getting a r a i get another array from this one. I didn't add the to rise together. So it's going to add them pairwise. The bus function doesn't know what to do with the right is just no scale as those as well
and give me a give me another right or they happened to be the same size as they will be in this scenario because I'm using same sales region and it just adds them together. You add two throughs together you get outside in Excel a true is something that is a fall to zero in a truth anything that's greater than 1 So we end up adding them together. So if you have two throughs you add them together and you get to but I still will treat that as being a true. So true plus 3 equals true false true equals true as well. So that's how you can end up
generating this decision or function you can use multiply to do so. I'll show you a quick and here so I'm going to switch this out. Domino's Pizza X Two doing and so what I'm going to do is this state the first state down here where the revenue is greater than 70 value in a Cell. So right now I have only my Styles greater than 70 from New Jersey and I can go and change this and it'll be just greater than 100 and less than statement through by using pairwise
operation and the broadcasting and then pairwise operation. That is cool. That's kind of. And intro to rake out. So what's that? I'm going to switch back to the rest of my slime. And I'm I'll be here at the end if you have any questions or you want to dive in. But this is a rake XL Community if you have any questions or anything with Dynamic arrays or anything in Excel, this is a place to go provide some feedback participate in the community. This is where I hang out. every piece of
contribution to reactive community members I got you a concession useful instead of the 400 level session. So we go down in the weeds a little but I wanted to explain the way it'll calculates and how it behaves under the hood and thank you. I'll be here if you have any questions.
Купить этот доклад
Access to all the recordings of the event
Купить это видео
ConferenceCast.tv — архив видеозаписей докладов и конференций.
С этим сервисом вы можете найти интересные лекции специально для вас!