Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.
Week 5 Discussion
Loading...
From the course by Macquarie University
Excel Skills for Business: Advanced
165 оценки
From the lesson
Advanced Lookup Functions
It is always a good idea to make your spreadsheets (and work!) more automated. Automated lookups can make your work fast and efficient. This module builds on your array capabilities and explores a range of functions to create dynamic lookup ranges – INDIRECT, ADDRESS, OFFSET and INDEX.
Meet the Instructors
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
Welcome to week five, and welcome Nicky.
>> Hi, Prashan.
>> This week, we're going to be looking at advanced LOOKUP functions in Excel.
Now, in a previous course in this specialization,
we've already looked at the LOOKUP functions CHOOSE, VLOOKUP, INDEX, and
MATCH which are already advanced functions.
So do we need to get any more advanced, Nicky?
>> Quite possibly not.
But at the beginning of this course, we said it was really important
to make sure you use the right function for the job.
And we want to make sure that our learners know about all the options, so
that they can make an informed decision.
It's also possible that they may encounter some of these advanced LOOKUP functions.
In workbooks, they come across in the workplace, and
they need to know what these functions do and how they work.
And most importantly, we want our learners to be aware of what the advantages and
the disadvantages are of these more specialized functions.
>> So what are we going to start with this week?
>> We're going to start with the INDIRECT function.
This is a curious function because it allows us to
indirectly refer to cell reference.
In other words, normally, when we refer to a cell,
we get the value coming back from that cell.
But with the INDIRECT function, when we refer to a cell, it expects that to have
an address and we get the value back from that address, so quite interesting.
Now, generally, it works with regular style addresses, like A1 style addresses,
but it also has the capability to work with R1C1 style addresses.
>> So what is R1C1 referencing?
>> Well, when we talk about our regular addresses,
we all say the column letter and the row number.
R1C1 just lets us specify the row number followed by the column number.
But this is quite handy when you're dynamically calculating a position in
a workbook because very often,
you'll end up with a column number rather than a letter.
So what are some of the applications of the INDIRECT function?
>> They're quite a few applications.
For one thing, if an INDIRECT refers to a cell, it effectively locks it.
So for example, if my INDIRECT referred to C2, if I inserted a row above C2,
regular functions will then start referring to C3, but
the indirect will stay doggedly referring to C2.
And that can be quite useful when your workbook is changing.
You can also get some fantastic results when you combine it with named ranges.
So for example, we're going to see in the video how we can create cascading,
drop down lists, very easily, just using the INDIRECT function.
>> Now, I always had this discussion about the INDIRECT function being powerful, and
then others say that the INDIRECT function is just evil.
What's the context of this debate?
>> The truth is, it's both.
It is really powerful and there are some problems that could be more easily solved
with INDIRECT rather than other functions.
But it has two very serious drawbacks which we need to be aware of.
The first is is what's called a volatile function.
Excel, by default, uses smart recalculation.
So when you change something in a workbook, it works out which cells
are going to be impacted by that change and only recalculates those formulas.
But where you have a volatile function, they get recalculated regardless.
So if you have a lot of these functions in a large workbook,
you might see a real performance impact.
The other problem with the INDIRECT is it's hard to audit.
Because of its very nature, letting you specify where the inputs are coming
from on the fly, it's very hard to detect when an error creeps in.
So those are the two reasons why you might want to think about staying away
from INDIRECT.
>> Now, I often see the INDIRECT function being used with the ADDRESS function.
Will we be looking at that?
>> Absolutely, the ADDRESS function allows us to construct an address and that can
be an R1C1 or an A1 style, so it's a natural partner for the INDIRECT function.
And we're also going to look at some little helper functions that often go
alongside them and that's the ROW, ROWS, COLUMN, and COLUMNS functions.
>> And then, later on in the week,
we're going to be looking at the OFFSET function.
>> Absolutely, and the OFFSET function is a really powerful function, but
quite simple, really.
What it does is given a specified cell reference, it will return a reference or
range, a certain number of rows or columns away from our original starting point.
So it has great lookup potential, but
what it's mostly used for is creating dynamic ranges.
And from this, you can actually create dynamic charts and
quite a lot of other applications, so really useful function as well.
>> Any downsides that our learners need to be aware of with the OFFSET function?
>> Unfortunately, yes, similar to the INDIRECT, the OFFSET is also volatile, so
probably best to avoid it with large data sets.
And it also has auditing problems in that if you were, for example,
to trace precedence, it will only return the original reference.
So it has its drawbacks.
>> So some advanced yet very useful functions in Excel for looking up.
Thank you so much, Nicky.
Now, we've got some practice videos coming up for you.
Make sure you check them out and download the Excel workbooks, so
that you can work alongside us step by step.
Check out this week's quizzes, the practice challenge, the tool box,
as well as this week's great ninja tip.
Now, it's over to you.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
