Cập nhật nội dung chi tiết về How To Use Powerpivot In Excel: The Ultimate Guide mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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.
The Right Version Of Excel 2013 For Using Powerpivot #Powerpivot #Excel
UPDATE Aug 16, 2013: Excel 2013 stand-alone now includes Power Pivot (now the name has a space and it is no longer a single word!). This post was originally published on Feb 18, 2013 and is now outdated. If you already have Excel 2013 stand-alone, you should see PowerPivot enabled in an upcoming update.
Many people started using PowerPivot with Excel 2010. In order to start using PowerPivot for Excel 2010, you just have to download the add-in and install it for free. In Excel 2013, PowerPivot is already installed and you just have to enable it. However, you have to be careful about the Excel 2013 version you use, because not all the versions have all the features available.
Data Model features available in all Excel 2013 versions
Internal xVelocity engine
Load multiple tables in a data model
Create relationships
Navigate a data model with multiple tables using a single PivotTable
Use only implicit measures
PowerPivot features available in selected versions of Office 2013 (*)
Create calculated columns
Create calculated fields
Use PowerPivot window and all the other features available there
Also Power View is available only in these versions of Excel
So what are the version of Office 2013 that enable the usage of PowerPivot features?
Here is the list:
Office Professional Plus 2013 via Open, Select or EA
Excel 2013 stand-alone UPDATE Aug 16, 2013 – any stand-alone version of Excel 2013 has PowerPivot
Office 365 ProPlus via Office 365 (www.office365.com) Subscription when it becomes available (February 27, 2013)
The only way to get these full BI features is through a Microsoft Volume License Agreement or Office 365 service. If you are not included in a Microsoft Volume License Agreement, the only way to get a copy of Excel 2013 that has all PowerPivot and Power View features available is getting an Office 365 ProPlus subscription.
UPDATE Feb 27, 2013: read about a workaround to get a Volume License Agreement for just 30$. UPDATE Aug 16, 2013: you can get PowerPivot with Excel stand-alone, without the need of any subscription.
This might disappoint those of you that are used to buying a single license that never expires, but there is a good reason to move to Office 365 for using BI features: in the upcoming months and years, you will automatically receive updates of Office before perpetual (non-subscription) customers, and Excel will increase the number of BI features available at a faster cadence than ever before (yes, they promised it!). If you attended MS conferences and/or watched some of the last keynotes of BI speeches, you might have already seen some interesting previews (i.e. 3D mapping with GeoFlow for Excel), and probably more is coming.
Again, it’s important to know that Office Standard 2013 does not include Business Intelligence features, so all the options available when you need fewer than five licences does not include PowerPivot and Power View. You have to get a subscription of Office 365 ProPlus in this case, and the only action you can to today is using the free preview of Office 365 ProPlus until the end of February, when such a subscription will be commercially available.
I have seen some confusion in these first days of Office 2013 availability and for this reason I think it is important to clarify what is the right version of Excel you have to buy in order to use PowerPivot. I will update this blog post as soon as the Office 365 ProPlus will be commercially available.
UPDATE Aug 16, 2013: please remember that now Excel 2013 stand-alone now includes Power Pivot! You no longer need a subscription!
How To Use The Excel Counta Function
Random list of names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…
Add row numbers and skip blanks
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))
The IF function first checks if cell C5 has…
Cell contains all of many things
The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…
Count cells that are blank
The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…
Last row in mixed data with no blanks
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…
Count unique values
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…
Dynamic named range with OFFSET
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.
The first argument in OFFSET represents the first cell in the…
Running count group by n size
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and…
Count cells not equal to many things
First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:
But this doesn…
Project complete percentage
In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…
Count sold and remaining
The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11)
The second COUNTA function…
Generate random text strings
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by…
Sort by random
The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…
Score quiz answers with key
This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(–(C7:G7=…
Reverse a list or range
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is…
How To Use The Excel Roundup Function
The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:
Digits Behavior
Round up to nearest .1, .01, .001, etc.
Round up to nearest 10, 100, 1000, etc.
=0 Round up to nearest 1
Example #1 – round to right
To round up values to the right of the decimal point, use a positive number for digits:
=
ROUNDUP
(
A1,
1
)
// Round up to 1 decimal place
=
ROUNDUP
(
A1,
2
)
// Round up to 2 decimal places
=
ROUNDUP
(
A1,
3
)
// Round up to 3 decimal places
=
ROUNDUP
(
A1,
4
)
// Round up to 4 decimal places
Example #2 – round to left
To round up values to the left of the decimal point, use zero or a negative number for digits:
=
ROUNDUP
(
A1,
0
)
// Round up to nearest whole number
=
ROUNDUP
(
A1,
-
1
)
// Round up to nearest 10
=
ROUNDUP
(
A1,
-
2
)
// Round up to nearest 100
=
ROUNDUP
(
A1,
-
3
)
// Round up to nearest 1000
=
ROUNDUP
(
A1,
-
4
)
// Round up to nearest 10000
Example #3 – nesting
Other operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:
=
ROUNDUP
(
A1/
B1,
0
)
// round up result to nearest whole number
Rounding functions in Excel
To round normally, use the ROUND function.
To round to the nearest multiple, use the MROUND function.
To round down to the nearest specified place, use the ROUNDDOWN function.
To round down to the nearest specified multiple, use the FLOOR function.
To round up to the nearest specified place, use the ROUNDUP function.
To round up to the nearest specified multiple, use the CEILING function.
To round down and return an integer only, use the INT function.
To truncate decimal places, use the TRUNC function.
Bạn đang đọc nội dung bài viết How To Use Powerpivot In Excel: The Ultimate Guide trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!