Advanced Filtering

Loading...

Del curso dictado por PwC

Problem Solving with Excel

2727 calificaciones

Explore Related Videos

At Coursera, you will find the best lectures in the world. Here are some of our personalized recommendations for you

PwC

Problem Solving with Excel

2727 calificaciones

Curso 2 de 5 en SpecializationData Analysis and Presentation Skills: the PwC Approach

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

De la lección

Logical Functions & Pivot Tables

In this module you will learn about logical functions and pivot tables. We will show you how to create and use pivot tables to solve business problems. We will give you an opportunity to practice creating and using a pivot table to solve a business problem. Finally, we will share some insight on PwC’s perspectives on the impact of Excel on your career and work.

Pivot Tables4:34

Navigate a Pivot Table7:49

Sort a Pivot Table3:43

Advanced Filtering5:53

Calculated Fields3:26

Conoce a los instructores

Alex Mannella
Alumni / Former Principal

[MUSIC]

The next tool in pivot tables that we will be looking at are advanced filters.

The first exercise,

Problem 5a is very similar to something we did earlier in our exercises.

In the past example, we were tying to solve the top five countries by revenue.

So we use a sort function to get the highest revenue at the top.

Using an advanced filter,

we're going to display just the top four products by quantity sold.

To do this, let's click the dialogue box next to Products.

And there is an option for Value Filters, so

let's scroll down there and see what pops up.

In this example,

we're going to use the Top 10 value filter, so I'm going to click on that.

And you can see right now we are showing the top 10 items by sum of quantity.

The question asks only for the top four products, so

I'm going to change this from 10 to 4.

And now it reads top four items by sum of quantity.

So I'm going to hit OK.

And now you can see we are only displaying the top four products by quantity sold.

Technically, we could get to the same answer using a sort function, but

you'll see here the advanced filters out anything that doesn't answer our question.

Problem 5b reads, which products contain in their description 200 milliliters and

what are their quantities sold?

To answer this, we'll use an advanced filter.

So I'll click the dialogue box next to products and

in this case, we're not looking at a value, we're looking at a label or text.

So I'll scroll down to Label Filters.

Now, there are many options here.

We could say, it equals 200 milliliters.

It begins with 200 milliliters, but

I'm not sure where that 200 milliliters is going to come in the title.

So I'm going to click Contains.

Because we are only concerned with products

that contain 200 milliliters in their description.

When I click on that, a box pops up asking me what I would like to filter for.

And I'm going to type in 200ml and click OK.

This pivot table shows only products with 200 milliliters within their title,

and it also shows the quantity sold.

The next function of pivot tables that we're going to learn is the get pivot

data function.

So this is a function just like anything else, like a sum or

a vlookup, but it's actually referencing into your pivot table.

This is very beneficial because you've already taken the time

to craft your pivot table to summarize your data.

And you can use an easy formula to reference into that.

To illustrate this I'm going to type the = sign,

click into our pivot table and you can see that a formula was auto populated.

So it says GETPIVOTDATA, Revenue, F44,

which is the first cell of our pivot table, Country, and France.

So, very easily here, we just typed the = sign and clicked in and

this formula will pull the revenue for the country of France within our pivot table.

Other ways to access this function other than to hit = and

click into your pivot table,

you can also type out the function by typing = and typing GETPIVOTDATA.

Or if you have a function that's already referencing your pivot table data, you can

just copy that down and change some of those fields to get the desired data.

In 5C it's your turn to filter for

countries with revenue between $2000 and $3000.

Since we're looking at dollars, you're going to use a value filter.

Read through the options in the value filter's menu to try to get to this answer

as efficiently as possible.

Problem 5D asks us to complete the following chart based on the pivot table

you used in Problem 5C, by editing the pivot table reference formula.

Let's click into the table to see what we're starting with.

So, it looks like someone typed in Canada in this cell.

And in the next cell there is a formula, it says GETPIVOTDATA(Revenue,

a cell reference, Country, Canada.

If we click into this formula up above we'll get a little more information.

Now, the formula says, GETPIVOTDATA( and then asks for

the data field you are trying to get.

And we are trying to look at Revenue.

Next, it says the pivot table, so

we reference the cell where the pivot table starts.

Next, shows the country is what we're looking for and

the country's name is Canada.

So I'm going to copy and paste this below to utilize per problem.

When I copy and paste this, everything here is the same except instead of Canada,

I'm looking for information on Finland.

So I'm just going to replaced Canada with Finland.

And now we can easily see that the revenue for Finland is $2,277.

For Problem 5E, which is the last problem in the advanced filter section,

you're going to complete the following chart based on the pivot table in Problem

5C by editing the pivot table reference formula.

You can do this by copying it down and typing in Mongolia where Canada is

written or you can try to write the formula yourself.

It's up to you.

[MUSIC]

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play