Bạn đang xem bài viết How To Use Powerpivot In Excel: The Ultimate Guide được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Power Pivot is an add-in first introduced in Excel 2010 and now a staple part of the modern Excel. It has changed the way that we can work with and manipulate large volumes of data in Excel.
In this article, we will not only answer the question of what is Power Pivot? But also why and how to use PowerPivot with real business use cases.
Download your data files
Follow along with the steps in the article by downloading these data files
What is Power Pivot & why is it useful?
Although an Excel worksheet can handle 1,048,576 rows of data. In reality, it can struggle as you get to 100,000 or even before that depending on what you have in your workbook.
Power Pivot enables us to work with big data beyond the 1,048,576 limitation and still produce smaller, leaner and faster workbooks than a standard PivotTable.
It does this by loading the data into the internal data model of Excel and not onto a worksheet. Relationships can then be created between the different tables of data. No more VLOOKUPs to pull data together into one big list.
We can then create PivotTables based on this model to analyze multiple tables of data.
You can also use a powerful formula language in Power Pivot called DAX. This stands for Data Analysis Expressions.
The DAX language is vast and enables us to perform more complex calculations than we can do with a standard PivotTable.
So what is Power Pivot? It is really a combination of using PivotTables and DAX calculations with the internal data model of Excel for analysis of big data.
Check out this short video that explains why we need Power Pivot:
A Power Pivot use case
Let’s look at an example business use case to see where Power Pivot will help us and I’ll explain how to use PowerPivot in this case.
Let’s imagine a scenario where we export sales data from our database. This includes a CSV file of all sales transactions for a specified time period.
It also includes a CSV file with all our customers and their details, and one with all our product details.
We would like to import these 3 files into an Excel workbook to analyze them and find the top 5 selling products, as well as which countries we received over £10 million.
Previously we would have imported the files into three different sheets and then used VLOOKUPs to pull the data into one big list for use in a PivotTable.
But with Power Pivot, we will import them directly into the data model for efficient storage. Then create relationships between the tables (instead of thousands of VLOOKUPs). And perform analysis with a PivotTable and DAX.
How to get and install the Power Pivot add-in
In Excel 2013, 2016 and 365 Power Pivot is included as part of the native Excel experience. It will just take a few seconds to install it from the COM add-ins the first time you want to use it.
The Power Pivot tab will then be visible on the Ribbon.
If you are using Excel 2010 you will need to download the Power Pivot Add-In from the Microsoft Site.
How to import CSV files to the Data Model
We will now walk through our use case scenario.
You can download the files and follow along for some hands-on practice.
Download your data files
Follow along with the steps in the article by downloading these data files
Firstly we need some data. This data could already be in Excel. But often if you are working with large data sets you are getting data from a database, a folder or multiple text/CSV files.
The best way to bring this data into Excel is by using Power Query. Power Query is a tool built into Excel to make importing and transforming external data simple.
Power Query is outside the scope of this article, but here is a quick example of getting our sales data from CSV files. I will start with the chúng tôi file.
The Power Query Editor window will load. There are a lot of tools we can use here to transform the data.
This is just a quick example to get the data into the model for Power Pivot. So we will just close and load the data.
The Import Data window appears. Select Only Create Connection and check the box to Add this data to the Data Model.
The data is then loaded into the model. So you will not see it on the worksheet, but you will see a Queries and Connections pane appear showing the number of rows loaded.
The image below shows the chúng tôi file loaded. It contains 106,693 rows. That is a lot of rows, but you will see that it does not impact the performance of calculations in Power Pivot.
Repeat the process for the other 2 CSV files. When finished the loaded queries will look like below.
Viewing the Data Model in Power Pivot
The Power Pivot for Excel window is displayed.
The initial view you are taken to is called the Data View. The tables of data are shown on different tabs, similar to worksheets. This is, however, just a display and not how they are stored.
Underneath the tabs is the Calculation Area. We will talk more about this shortly when we cover measures.
This provides a better view of the model and is great for viewing the relationships between the tables which we will create.
Create relationships between the tables
With the tables loaded into the model, we will now create relationships between them. This will enable us to create PivotTables using the data from all three tables.
The Diagram View is the easiest way to set this up. Let’s start by arranging the window more efficiently.
Drag the Sales table under the Products and the Customers tables.
The Sales table contains the transactional information and is referred to as “the data”, or “the fact table”.
The Products table and the Customers table contain information on groups of objects that interact with the data and are referred to as “lookup”, or “dimension tables”.
We will create two ‘one to many’ relationships. One between the Customers table and the Sales table, and another between the Products table and the Sales table.
This is because a customer could make one or many sales with us. And the same for the products. A product could be sold once or many times.
Repeat the step to create a relationship between the Product ID field in the Sales table and the ID field in the Products table.
The image below shows the completed relationships. The filter direction of the data is displayed by an arrow, and a 1 and asterisk (*) symbols are also displayed to show the relationship type.
Create a PivotTable from the Data Model
With the Data Model set up, we can create a PivotTable.
The PivotTable appears and in the field list you can see the three tables. We can now access the fields from each table and drag them to the areas of a PivotTable as normal.
So what is Power Pivot? It is a PivotTable that uses data from the internal model.
Now let’s create one of our use case examples. We will find the top 5 selling products.
Drag the Product Name field from the Products table into the Rows area. Then drag the Total Sales field from the Sales table into the Values area.
This will sum the total for each product in our data.
We have the top 5 products in a PivotTable.
Now when we dragged the Total Sales field into the Values area of our PivotTable, it created what is called an Implicit Measure.
We can use a PivotTable from the Data Model in the same way as we may be used to doing. This is great at the beginning or if you’re just performing simple analysis. But it is better and more efficient to create measures and to use them in PivotTables.
Using DAX to create Measures
Let’s begin to have a look at the DAX language to perform calculations in Power Pivot.
There are two types of DAX calculations – Calculated Columns and Measures.
A Calculated Column is used to create additional columns in your data model. And these columns can then be used as labels in the rows, columns and Slicer areas of a PivotTable.
It is encouraged to create these columns in the original data, or in Power Query instead of the model if possible. These columns can be really useful for a further breakdown of our data such as grouping dates into weekday and weekend labels.
In this article, we are going to create the other type of DAX calculation – called a Measure. Measures are calculations that are dragged into the values area of a PivotTable such as Sum and Average.
The DAX language is huge, going far beyond the standard Sum and Average. So creating these in the model provides far more power than within a standard PivotTable and Implicit Measures.
Measures created with DAX can also be used multiple times and in multiple PivotTables (but calculated just once). This improves the processing speed. You can also assign a format to a Measure so you won’t need to format them every time they are used.
We will create a Measure to sum the Total Sales field from the Sales table.
The Measure window appears.
Select the table from the list that you would like the new measure stored within. This measure will be stored in the Sales table.
Enter a name for the measure. This measure is named Sum of Sales.
You can enter a description for a measure. Especially if complex. Here it is omitted since the name serves that role as well.
Enter the following formula into the box provided: =SUM(Sales[Total Sales])
Using a Measure in a PivotTable
With the measure created we can use it in our PivotTables for analysis.
Using the PivotTable we created earlier in the tutorial, we can remove the Sum of Total sales implicit measure.
Our new measure is shown in the list of table fields and can be dragged into the Values area as a replacement.
We will now create our other use case of showing in which countries we received over £10 million and re-use the same measure.
Insert a new PivotTable as before and drag the Country field from the Customers table into Rows, and the Sum of Sales measure from the Sales table into Values.
The countries that meet the criteria are shown as below.
Our measure has been used in both PivotTables to help us achieve both use cases. The DAX language is capable of far more and I encourage you to read further in that area.
In this article, we have answered the question of what is Power Pivot and demonstrated two business use cases on how to use PowerPivot through the entire process.
We imported the data into the model, created relationships and a measure, and then used them in PivotTables.
Power Pivot is one of the best improvements to how we use Excel. It is an extremely powerful tool and this article is an introduction to what it is capable of. I encourage you to learn and develop your Power Pivot skills even further.
Alan Murray
Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spreading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner.
How To Use Vba In Powerpoint: A Beginner’S Guide
Here at BrightCarbon we’re always looking for new ways to improve our own PowerPoint productivity and then share that knowledge with the presentation community (that includes you, by the way!). One of the ways we do this is by using VBA code to automate and extend the functionality of PowerPoint. We publish free PowerPoint VBA code snippets here in our blog for you to use. This article explains how to grab the code from our articles and use it in your PowerPoint project, so that you can take your productivity to the next level!
Visual Basic for Applications (VBA) is a programming environment for Microsoft Office applications. It’s included with your installation of Office by default ( unless your system administrator has deactivated it ) . PowerPoint VBA provides you with a way to do one of two things using macros and add-ins:
int:If you ever find yourself repeating the same task over and over again, VBA could be your new best friend. Let’s say you have 100 slides and you need to unhide all hidden objects across all those slides . That could take you many eye-straining minutes, but with a PowerPoint VBA it takes around a second.
way in PowerPoint to get them back. PowerPoint :Sometimes PowerPoint doesn’t have the feature you need to complete your task . As an example, if you end up deleting default layouts from a template, there’s no easy This article includes PowerPoint VBA code to do just that!
How to open the VBE (Visual Basic Editor)
Getting to meet your VBA friend is very simple. With PowerPoint open and at least one presentation file open, press * on your keyboard. This will open the VBE (Visual Basic Editor):
Adding PowerPoint VBA code
You now have a module ready to paste the VBA code into from one of our blog articles :
‘ PowerPoint VBA Macro to display Hello World message. ‘ Copyright (c) 2019 BrightCarbon Ltd. All Rights Reserved. ‘ Source code is provided under Creative Commons Attribution License ‘ This means you must give credit for our original creation in the following form: ‘ “Includes code created by BrightCarbon Ltd. (brightcarbon.com)” ‘ Commons Deed @ http://creativecommons.org/licenses/by/3.0/ ‘ License Legal @ http://creativecommons.org/licenses/by/3.0/legalcode ‘ Purpose : Displays a dialog box with a Hello World text message. ‘ Author : Jamie Garroch ‘ Date : 06MAY2019 ‘ Website : https://brightcarbon.com/
Sub HelloWorld() MsgBox “Hello World!”, vbInformation + vbOKOnly, “This is my first VBA Macro” End Sub
You should now see something like this:
How To Use The Data Model In Excel
Excel can analyze data from many sources. But are you using the Data Model to make your life easier? In this post you learn how to create a pivot table using two tables by using the Data Model feature in Excel.
What is a Data Model
Excel’s Data Model allows you to load data (e.g. tables) into Excel’s memory. It is saved in memory where you don’t directly see it. You can then instruct Excel to relate data to each other using a common column. The ‘Model’ part of Data Model refers to how all the tables relate to each other.
Old school Excel Pro’s, use formulas to create a huge table containing all data to analyze. They need this big table so that Pivot Tables can source a single table. Yet by creating relationships, you surpass the need for using VLOOKUP, SUMIF, INDEX-MATCH formulas. In other words, you don’t need to get all columns within a single table. Through relationships, the Data Model can access all the information it needs. Even when it resides in multiple places or tables. After creating the Data Model, Excel has the data available in its memory. And by having it in its memory, you can access data in new ways. For example, you can start using multiple tables within the same pivot table.
A Simple Task
Imagine your boss wants to have insight in the sales but also wants to know the Sex of the sales person. You got below dataset containing one table with the sales per person and another table containing the salespeople and their respective sex. A way to analyze your data is to use a LOOKUP formula and make a big table containing all information. As a next step you can then use a Pivot Table to summarize the data per sex.
Advantages of the Data Model
Checking and updating formulas may get arbitrary when working with many tables. After all, you need to make sure all the formulas are filled down to the right cell. And after adding new columns, your LOOKUP formulas also need to be expanded. The Data Model requires only little work at setup to relate a table. It uses a common column at the setup. Yet columns that you add later, automatically add to the Data Model.
Working with big amounts of data often results in a very slow worksheet due to calculations. The Data Model however handles big amounts of data gracefully without slowing down your computer system.
Excel 2016 has a limit of 1.048.576 rows. However, the amount of rows you can add to the memory of the Data Model is almost unlimited. A 64-bit environment imposes no hard limits on file size. The workbook size is limited only by available memory and system resources.
If your data resides only in your Data Model, you have considerable file size savings.
Add Data to Data Model
You will now learn how to add tables to the Data Model. To start with, make sure your data is within a table. Using Power Query you can easily load tables into the Data Model.
Select From Table / Range
In the home tab of the Power Query editor
Select Only Create Connection
Make sure to tick the box Add this data to the Data Model
This adds the data to the Data Model. Please make sure to do these steps for both tables.
Creating Relationships Between Data
After adding your data to the Data Model, you can relate common columns to each other. To create relationships between tables:
The Power Pivot screen will appear.
Using the Data Model
Now we come to the exciting part. To use the Data Model in a PivotTable perform the following steps:
The ‘Create PivotTable’ pop-up screen will appear. As you have a Data Model in place, you can now select to use it as data source.
In the PivotTable Fields you will now see all the possible Data Sources for your PivotTable. The yellow database icon on the lower right corner of the marked tables, shows that it is part of Excel’s Data Model.
As the two tables have a relationship between each other, you can use fields from both tables within the same pivot! Read previous sentence again. Isn’t that amazing?? Below example uses the Sales and Seller field from the ProductSales table, while the Sex field comes from the other table. And the numbers are still correct!
Using the Data Model you can analyze data from several tables at once. All without using any LOOKUP, SUMIF or INDEX MATCH formula to flatten the source table. Yet the data analyzed could also come from a database, text file or cloud location. The possibilities are endless.
To minimize the usage of LOOKUP formulas even more, an amazing tool to look at is Power Query. There’s several articles to find about it on my website. For example, you can read how to use Power Query for Creating Unique Combinations or for Transforming Stacked Columns.
Categories Excel report this ad Tags Data Model, Pivot Table, Power Pivot
How To Use The Excel Vlookup Function
VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the first column of the table passed into VLOOKUP. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.
V is for vertical
The purpose of VLOOKUP is to get information from a table organized like this:
Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:
=
VLOOKUP
(
1004
,
B5:F9,
4
,
FALSE
)
// returns "Sue Martin"
For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.
VLOOKUP is based on column numbers
When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, provide the appropriate number as the “column index”. For example, the column index to retrieve the first name below is 2:
The last name and email can be retrieved with columns 3 and 4:
=
VLOOKUP
(
H3,
B4:E13,
2
,
FALSE
)
// first name
=
VLOOKUP
(
H3,
B4:E13,
3
,
FALSE
)
// last name
=
VLOOKUP
(
H3,
B4:E13,
4
,
FALSE
)
// email address
VLOOKUP only looks right
VLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values:
If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.
Exact and approximate matching
VLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is “range_lookup“. This is a confusing name, because it seems to have something to do with cell ranges like A1:A10. Actually, the word “range” in this case refers to “range of values” – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades.
It is important to understand that range_lookup defaults to TRUE, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set range_lookup to FALSE to force exact matching:
=
VLOOKUP
(
value,
table,
col_index)
// approximate match (default)
=
VLOOKUP
(
value,
table,
col_index,
TRUE
)
// approximate match
=
VLOOKUP
(
value,
table,
col_index,
FALSE
)
// exact match
Note: You can also supply zero (0) instead of FALSE for an exact match.
Exact match
In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:
The formula in H6 to find Year, based on an exact match of movie title, is:
=
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
)
// FALSE = exact match
Approximate match
In cases when you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:
=
VLOOKUP
(
C5,
$G$5:$H$10,
2
,
TRUE
)
// TRUE = approximate match
VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will “step back” and match the previous row.
Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.
First match
=
VLOOKUP
(
E5,
B5:C11,
2
,
FALSE
)
// returns 17
Wildcard match
The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table after typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify the exact match mode by providing FALSE or 0 for the last argument, range_lookup. The formula in H7 retrieves the first name, “Michael”, after typing “Aya” into cell H4:
=
VLOOKUP
(
$H$4&
"*"
,
$B$5:$E$104,
2
,
FALSE
)
Read a more detailed explanation here.
Two-way lookup
Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:
=
VLOOKUP
(
H4,
B5:E13,
MATCH
(
H5,
B4:E4,
0
),
0
)
For more details, see this example.
Note: In general, INDEX and MATCH is a more flexible way to perform two-way lookups.
Multiple criteria
The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example below, Column B is a helper column that concatenates first and last names together with this formula:
=
C5&
D5// helper column
VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:
=
VLOOKUP
(
H4&
H5,
B5:E13,
4
,
0
)
For details, see this example.
Note: INDEX and MATCH and XLOOKUP are more robust ways to handle lookups based on multiple criteria.
VLOOKUP and #N/A errors
If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just means “not found”. For example, in the screen below, the lookup value ”Toy Story 2″ does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:
One way to “trap” the NA error is to use the IFNA function like this:
The formula in H6 is:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
"Not found"
)
The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string like this:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
""
)
// no message
The #N/A error is useful because it tells you something is wrong. In practice, there are many reasons why you might see this error, including:
The lookup value does not exist in the table
The lookup value is misspelled, or contains extra space
Match mode is exact, but should be approximate
The table range is not entered correctly
You are copying VLOOKUP, and the table reference is not locked
Read more: VLOOKUP without #N/A errors
More about VLOOKUP
Other notes
Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.
Cập nhật thông tin chi tiết về How To Use Powerpivot In Excel: The Ultimate Guide trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!