馃攳
Excel LAMBDA - HOW & WHEN you Should use it - YouTube
Channel: Leila Gharani
[0]
Today I'm going to share with you
[2]
a new function in Excel
[3]
that allows you to create functions.
[7]
A function to create functions.
[9]
It's called the Lambda function.
[11]
This is currently only
available in the Beta channel.
[15]
This is a function
[17]
that's both for Excel
beginners and experts.
[20]
Because even if you don't end
up creating your own Lambdas,
[25]
you need to know what they are
[26]
because you'll probably end up
using someone else's Lambda.
[30]
I'm really curious to get your
impression of this function
[33]
so comment below as you're watching this
[36]
and let me know what you think.
[38]
Personally, at first I
thought it's a nice to have
[42]
and then I saw Recursive Lambdas.
[47]
It changed everything.
[49]
Now I'm no going to talk so much because
[50]
you need to see this.
[52]
(upbeat music)
[57]
Now this is going to blow your mind.
[59]
In this example, we have a list of names
[61]
and the skills of each person.
[62]
Now, unfortunately, we have mistakes here
[65]
because some people put in office 365
[68]
where they should have put
in Microsoft 365 instead
[72]
or Power Point with a space
where it should be PowerPoint.
[75]
So here we have a list of before and after
[78]
and we want to get this corrected.
[80]
So notice that some of them
[82]
are going to require multiple corrections
[85]
because they have made mistakes
for every single skill.
[89]
What we want to do is go to each cell
[91]
and loop it through the before,
[94]
whenever it comes across a match,
[96]
it should take the after version of it
[99]
make the replacement and put
it through this list again.
[103]
So it's similar to a "For
Next" or a "Do While" in VBA
[109]
we're going to do this with a formula,
[111]
check this out.
[112]
I've already written the Lambda for this.
[115]
My function is called "Mega Replace".
[120]
So the moment I start to
type it, it shows up here
[123]
it needs the texts that I want to replace
[127]
then it also needs the
start of my before table
[132]
and the start of the after one.
[134]
Now I going to fix this
because this is a table
[138]
and the moment I press enter
[139]
right here beside a table
[141]
it's going to add this
as a part of the table.
[145]
Now, when I do this press enter
[147]
take a look at what's going to happen.
[149]
Wow.
[150]
Everything is replaced.
[153]
Look at this,
[154]
Officer 365 PowerPoint,
[157]
Microsoft 365 PowerPoint
written correctly.
[160]
The one where we had a lot of mistakes
[163]
that was this month for example,
[165]
it's all corrected.
[167]
This is an example of a Recursive Lambda.
[169]
Now we're going to get to the
engine of Lambdas in a second
[173]
but let me show you a
practical example of a Lambda
[178]
that's not recursive.
[180]
So let's see in your workbook,
[183]
you generally need to do a certain task
[185]
which is to sort certain fields
[189]
by the sum of another
field in descending order.
[193]
So for example, you
want to sort department
[197]
by the sum of salary.
[199]
Kind of like doing a sum ifs on your
[202]
salaries by department
[203]
and then sorting them just by department.
[206]
Because notice that the sales
department is repeated here
[210]
so are the other departments.
[212]
Now you can create a
Lambda for this so that
[215]
all you have to do is type
in your own custom function.
[220]
I called mine "Sort by Sum".
[223]
This one just needs two arguments,
[226]
the column that you want to sort,
[228]
so let's say this
[229]
and then the column that has your numbers
[231]
that you want to do a sum ifs on
[234]
and then you close bracket press enter.
[236]
I have departments sorted
by the sum of salary.
[240]
And if you don't trust me,
we can double-check it here.
[243]
I'll do a sum ifs quickly.
[245]
My sum range is this,
[248]
my criteria range one is this
[251]
and my criteria one is this
[253]
and hash to make it spill and press enter,
[257]
notice I have the correct sorting.
[259]
And because this is the Lambda
[261]
I can apply it to anything I want
[263]
as long as it has a similar structure.
[266]
So sort by position, I go to sort by sum
[270]
and then I take the position column
[273]
and then the salary column,
[275]
close bracket press enter
[277]
and I have my positions sorted
[279]
by the sum of salary in descending order.
[283]
So this is already been programmed
[285]
that it always gives it
to me in descending order.
[288]
And the reason I did
this is this is something
[290]
that I might use often in my workbook.
[292]
Now, how are this' set up?
[294]
Well, let's cover the basics
of Lambdas right here.
[298]
What are Lambdas to begin with?
[300]
These are just functions that you create.
[303]
These functions act like machines.
[305]
Once you've created that machine
[307]
you can put through it other ingredients
[310]
and reuse it as often as you want.
[312]
This means that
[313]
you don't have to recreate
the machine every time.
[317]
So let's do an example here.
[318]
Let's say you have this list of prices
[320]
and I want to calculate
a 30% discount rate.
[324]
So what would be the price
after a 30% discount.
[327]
To do that with a formula
[329]
I just go B4 multiply it with
[331]
one minus 30% close
bracket and press enter.
[336]
If this is a formula that I'm using a lot
[339]
I could turn it into a machine
[341]
this way I can reuse it
[343]
with other similar ingredients.
[346]
So how would this look as a Lambda?
[348]
Well, the great thing
about creating your machine
[351]
with Lambdas is that
[352]
you can create it directly
in your formula bar.
[356]
It's a function.
[358]
So I'd start with typing in Lambda
[360]
and then you need
parameter or calculation.
[363]
So basically each argument
[366]
is either a parameter or a calculation.
[370]
Well, any where you see cell references,
[373]
these are going to become your parameters.
[376]
So here I'm just referencing B4,
[378]
B4 is going to be a parameter
[381]
and you can call this
parameter anything you want.
[384]
Now I'm not really creative with names
[386]
and I don't like to waste
time thinking of good names
[390]
so I usually just go
with A B C D or X Y Z.
[393]
So I'm going to call the parameter B4
[396]
which is my cell reference, A.
[398]
This is my calculation.
[400]
And I have to mention the
parameter at the beginning.
[403]
So it's A comma
[405]
so then I go to the next argument
[407]
and it's always that the
last argument of your Lambda
[411]
is your calculation.
[413]
Anything before are your parameters.
[415]
Now I've created my machine.
[418]
I need to close the bracket
to close the Lambda function.
[421]
So what happens when you press enter?
[423]
Well, before you do that,
[425]
just take a look
[425]
to see if you're
subscribed to this channel.
[427]
If you aren't yet, consider subscribing.
[432]
Let's take a look, when I present her,
[434]
I get an error.
[436]
Why?
[437]
Because I've just created the machine
[439]
but I'm not putting anything through it.
[441]
I need to put an ingredient in there.
[444]
To test your machine on the grid here,
[447]
you can do it by putting
in a cell reference.
[450]
To do that
[451]
at the end of your Lambda function
[453]
open the bracket and do a cell reference,
[456]
that's going to act like your parameter.
[458]
So since I only have one parameter
[461]
this B4 is going to become A
[463]
and it's going to get
put through my machine.
[466]
Now, when I press enter,
[468]
I get what I got back before.
[471]
Now, obviously this formula right now
[474]
is not so useful.
[475]
It's better just to
have what I had before.
[478]
The advantage of machines like this,
[480]
like the Lambda function
[482]
is that you can give this a name
[484]
and then you can reference
this anywhere on the grid.
[487]
And we're going to see this in a second
[489]
but I want to show you a Lambda
[491]
that has two parameters.
[493]
So let's say instead of 30%
[495]
you want this to be dynamic as well.
[497]
So you can turn it into a parameter.
[500]
I'm going to call this one B.
[503]
Now because this machine
needs two ingredients A and B,
[506]
I have to have them as
parameters on the side here.
[509]
So it's A comma B comma
[513]
and last is my calculation.
[515]
Okay, now, what's going to happen
[518]
when I press enter?
[519]
Let's check it out.
[521]
It's no going to work.
[522]
Why doesn't it work?
[524]
Well, the machine needs A and B to work.
[527]
I've only given it one thing,
[529]
I've only given it A.
[531]
So what is B?
[532]
Well, it's 30%.
[534]
So you can either do a cell reference
[536]
or directly type it in.
[538]
Now, when I pull this
down or send this down,
[541]
I get the same results.
[543]
Okay, so how can I turn this
[545]
into a name that I can reuse?
[547]
Well, once you test that your
Lambda works on the grid,
[551]
you can copy it,
[553]
it's actually this, copy
that equals as well.
[555]
Do just copy the machine part of it
[557]
and then you go to name
managers under formulas,
[561]
Name manager, you create a new name.
[564]
You can call this anything you want,
[566]
I'll do CalcDiscount
[570]
and then paste in my
machine in here, and okay.
[575]
Now, whenever you use
more than one parameter,
[578]
I recommend you add in the comment
[580]
what each parameter is used for.
[582]
So something like A equals your value
[586]
and B equals your discount,
[590]
and okay.
[591]
Here so now we have this as a Lambda.
[594]
This means that whenever
we need to call this
[597]
all we have to do
[599]
is type in the name of our function
[601]
which was CalcDiscount.
[602]
And it needed two things
[604]
which was this and our discount rate.
[607]
I'm just going to put
the discount rate here,
[609]
let's fix it.
[611]
Close bracket press enter,
[613]
I have nothing so it's 30.
[615]
If I put 20%, now I have a 20% discount.
[620]
This is something I can
reuse anywhere in my file.
[624]
Now let's take a look
at the practical Lambda
[626]
I showed you before.
[627]
How can we create that?
[629]
Well, the first step to creating Lambdas
[631]
is to try it out on the grid.
[633]
This works for Lambdas
that aren't recursive.
[636]
All you have to do is figure
the function you need first
[640]
then turn it into a
machine, basically a Lambda
[643]
and then give it a name in Name manager
[645]
so you can reuse that
machine anywhere you want.
[649]
In this case, we want to sort
[651]
any column that we want
[653]
by the sum of another column.
[656]
So here, for example, I want
to either sort the departments
[660]
or sort the position in descending order
[663]
based on the sum of salary.
[665]
So I'm going to pick
position as my Guinea pig.
[669]
I'll try to write up
the function that works
[672]
and then I'm going to
turn it into a Lambda.
[675]
So I'll start from the
inside of the function
[677]
and work outwards.
[679]
I need this sum ifs function.
[682]
The sum range is this one,
[684]
my criteria range,
[686]
that's position here
[688]
and the criteria itself
[690]
well, it's the unique values
of my position column.
[695]
Now, when I close and close
sum ifs and I press enter,
[699]
my results are going to spill
[700]
because it's giving me the
sum of each unique position.
[704]
Now because I don't want
to get the values back
[707]
I actually want to get the position back.
[709]
I can put this inside
[712]
this sort by function.
[714]
Sort by needs an array,
[715]
the array is what you
want to see in the end.
[718]
I only want to see position in the end.
[721]
But not all positions,
[723]
I want to see the unique positions
[728]
by array is my sum ifs
[731]
and I want to sort this
in descending order.
[735]
So I need a minus one here,
close bracket, press enter.
[739]
And I get my position
sorted by the sum of salary.
[742]
Now we can quickly test that
[744]
just to make sure it works.
[746]
I'll do a quick sum ifs.
[748]
This is my sum range,
[750]
my criteria range
[752]
and my criteria itself.
[754]
I want it to spill
[755]
so I'm going to use the hash,
[757]
close bracket press enter, it works.
[759]
Now that I've created my machine,
[762]
I'm going to turn this into a Lambda.
[764]
So let's go and start off with Lambda.
[767]
First thing is to figure
out our parameters.
[769]
Well, that depends on how many
different references we have.
[774]
I have two different references.
[776]
It's either this one or this one.
[780]
The C4 to C19 reference,
[782]
that's where I have my labels
[784]
and D4 to D19 that's
where I have my numbers.
[788]
So I'll be better this time,
[790]
I'll go with A as my labels
[793]
and for my numbers,
[795]
so I need two parameters here.
[797]
Now, the last argument
remember is the calculation now
[801]
and I have to replace every time
[803]
I see a cell reference here,
[805]
I have to replace it with my parameters.
[808]
So this is going to become my A
[810]
because C4 to C19 are my labels.
[814]
So that's going to be A.
[816]
I'm going to replace this also with A
[819]
and this A as well.
[822]
D4 to D19 that's going to
become my number parameters
[826]
I'm going to change it to "num".
[828]
My Lambda is done.
[830]
Let's close the bracket press enter.
[832]
Well, it's no going to work because
[833]
I can test it
[835]
to see once I pass the ingredients to it
[837]
Is it going to work?
[839]
I'll pass department this time
[841]
and then comma and my numbers.
[844]
I guess you have to respect
[846]
the order of the parameters,
[848]
when I close bracket and press enter
[850]
I get Sales, Finance and Procurement.
[852]
Okay, so this works.
[853]
Now I can turn it into a name
[856]
that I can reuse anywhere I want.
[858]
So I'm going to press control C
[860]
go to formulas, Name manager
[862]
add a new name,
[864]
call this "sort by some"
[867]
and paste in our values.
[869]
Now I can quickly add A equals labels
[874]
and "num" equals numbers, and okay.
[878]
Okay, so sort by some, is my new machine.
[882]
So now I can use it anywhere.
[884]
I want to sort division by Revenue.
[887]
I'm going to go with sort by sum
[889]
the machine needs two things,
[891]
first is the column I want returned
[894]
and then my numbers.
[896]
Close bracket press
enter, I have it sorted.
[899]
I can use this anywhere
I want in my workbook.
[903]
Now the last one and the
most exciting one for me
[906]
is the Recursive Lambdas.
[909]
Now, because this video
is already so long,
[912]
I'm no going to do this
justice if I rushed for this.
[914]
So I'm going to keep this as
a topic of our next video.
[918]
Okay, so tune in next week
[920]
and let's talk about Recursive Lambdas.
[923]
Okay, so that's an introduction
[925]
to the new Excel Lambda function.
[927]
Next week, I'm going to
cover Recursive Lambdas
[930]
and we're going to do that
last example together.
[933]
Let me know what you think.
[934]
Do you have the Beta channel?
[936]
Are you able to test Lambdas?
[939]
Let me know below.
[940]
Now, I assume since
you're watching this far
[943]
you enjoy the content of the channel,
[945]
so if you do, consider subscribing
[948]
and I'm going to see
you in the next video.
[951]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage