free google play gift card codes
For more infomation >> FCL Wednesday October 31st Halloween and Google - Duration: 3:20. 
For more infomation >> Scoring, Bitcoin-Bug, Google-Login, Siemensstadt 2.0 | Kurz informiert vom 01.11.2018 - Duration: 2:20. 
For more infomation >> Google employees stage walkout over sexual harassment scandals - Duration: 1:29. 
For more infomation >> Did Google help hide a secret military base from view? - Duration: 5:01. 
For more infomation >> Google employee walkout begins over handling of sexual harassment scandals - Duration: 1:24. 
For more infomation >> Connect to Hadoop and Yarn interface on Google Cloud Dataproc (GCP) | Hadoop Spark Pig Hive (ENG/PL) - Duration: 1:57. 
For more infomation >> Google Pixel 3 - Duration: 0:31. 
For more infomation >> Google Pixel 3 - Duration: 0:31. 
Spreadsheets have a concept called named ranges and what it is is
just the ability to give a range, so let's say G5:G13, a nickname that
makes more sense.
You could call this quantity of lemons.
We're going to go through how and why you would do this and show some
examples using this made-up inventory of the grocery store back in the
warehouse where they have a lot of citrus fruit.
You're looking at a list of how many oranges that we have listed by
variety, how many lemons, and how many grapefruits.
I think there's an s on the end a grapefruit, I don't know, we'll say
how many grapefruit.
You'll notice one thing different about the grapefruit is
that there's no price per each, and we'll go over why later, so I left that out on
purpose to show you another thing that you can do with name ranges.
One of the more common reasons why you would use named ranges is if you are
working between multiple worksheets.
I went to Sheet2 and I'm going to be counting the things in Sheet1.
You'll see pretty quickly that's going to be a whole lot easier if we can just
refer to it with normal names.
But, let's do it the regular way first so you can
see the pain points here.
The number of varieties of oranges.
I'm going to count the number of occurrences of non empty cells.
Come over here.
Here we go.
You can see the formula working right here.
But, it has to have a Sheet1! point before it.
Sheet!A5:A12.
This obviously works, but I want to say it's really human readable,
right?
You're not going to be talking to your friend, explaining how you did a
spreadsheet looking at Sheet!A5:A12, but let's do that, it works right?
That's the number of varieties.
We're going to sum up all the inventory, and we'll have to come back here, sum all the
quantities, and then you see the pattern here, right?
I'm going to pick up the cost like that.
That's doable.
It didn't produce any errors, but it can be done a
little bit easier.
When we go down to the lemons, what we're going to do first is
proactively name the ranges.
Let's go back to this sheet.
We're going to highlight what we're going to name first.
You're going to go to Data, Named ranges, and then you'll have your little dashboard
come up here on the right.
It starts out, it called it NamedRange1.
We're going to hopefully give it a better name than that.
We're going to call it variety.
I've purposely been a little short-sighted in what I'm doing here in two
different ways and we'll use both of them as a lesson here in a little bit.
But, for now, we're going to call these Variety, Quantity, and Total.
When we come to do our lemons, we're going to
count the number of varieties.
We'll do COUNTA.
Start the parentheses.
Ss soon as you type of V, the first choices are
going to be the names of functions, and there's a little statement as to the purpose
of the function here, but if you go down, you can see the one that has the
green icon to the left of it is your named range.
Go down, left click on that.
Close that off with the ending parentheses, hit enter, and it works.
The number of varieties is nine.
Let's go back.
Let's double check that.
Yeah, there's one more here, so there are nine
of them.
Let's count it.
We're going to count what was it, Quantity I think.
Let's see, yeah that worked.
We're going to SUM.
Now you've done the lemons and you've done it using plain English.
You've kind of done it how you would explain it to someone, right?
Sum the total, sum the quantity, and I counted the
variety.
Let's come and do this for grapefruits, and you're going to see the
first problem is that I should have done more specific ranges.
So, if you have multiple ranges that are similar, give them
a better name.
When we do the range for grapefruit, we're going to say grapefruit.
I wanted a space there, but you're not allowed to do a space.
You can do an underscore, or you can just skip
the space.
And just grapefruit variety if you want, let's do that.
The second thing that we're going to do is, instead of limiting the range to 12 what
you can do, and you could also do this in the original formula as well, but
it's a good practice for named ranges so when you go back and add to them, you're not
stuck.
This range now, is going to start at K5 and then go all the way down to
the bottom of your spreadsheet in the column K. I click done.
You can see now that the definition of the range goes
all the way down to a 1007.
1007 may seem like a bit of an odd number, but if I go all the
way down by holding the control key and pressing down, 1007 is the number of rows
in my spreadsheet.
Now, when you add a row, it's going to automatically add that data into that
named range without you modifying the named range.
Let's do the same thing on the quantity.
We'll highlight this.
We're going to add it and just get rid of your last row now.
I'm going to go through and fix the names of what was
referring to the lemons and I'm just going to do that all from this dashboard.
Now I have good names here for grapefruits and lemons and then let's
add the oranges.
As you look at the names of these named ranges, a few other things to keep
in mind, you can't just use the word True, you can't use the word False, and you
can't name it something that is also the name of a range.
So, you couldn't name it A2:G6.
These have to be referencing within the same file.
If you want to do a named reference from outside of the file,
you can try the IMPORTRANGE function, but that's outside the scope of this video
so we're not going to go into that.
We're going to do one more thing with the named range.
We're going to count the number the varieties of the grapefruits.
That's going to be straightforward.
We're going to copy this formula and paste it.
We'll go in and change this from Lemonvariety to Grapefruitvariety.
You'll notice, when this was a named range, the Lemonvariety,
it didn't move.
It's a fixed reference.
If this were referencing say to A10, when I copied it down here, it
would have incremented down because it would have been a relative reference.
But, named ranges in Google Sheets act like to
fixed references.
They don't change when you copy and paste them.
We're going to count the inventory as well.
Now the cost of the grapefruits.
We're going to do that differently because we're going to say grapefruits are
so expensive that there's a market price.
Kind of like expensive seafood, right?
Now that market price is $9.80.
Let's name that range.
Obviously, in this example, you really don't need to name it because you
see it right here, but just be aware that you can do this as a reference.
It can just be one cell.
We'll say MarketPrice.
Then the costs of the grapefruit inventory is 98 x, there it is,
MarketPrice.
Select that.
It's $960.40.
I want that to look like currency.
Let's copy the formatting from C12 and apply it.
Now, if the market price changes to $10, that's going to update the price of
your inventory.
There you go.
I hope that was helpful and I hope that you can use named ranges to work in
your spreadsheet a little bit easier.
If you like that video, please subscribe in the lower right hand corner and
click the little icon of a bell and then you'll see the new videos as they come
out.
Thanks!
Đăng ký:
Đăng Nhận xét (Atom)
Không có nhận xét nào:
Đăng nhận xét