Cập nhật nội dung chi tiết về How To Use The Data Model In Excel 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.
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 Sort And Filter Data In Excel
Sorting and filtering data offers a way to cut through the noise and find (and sort) just the data you want to see. Microsoft Excel has no shortage of options to filter down huge datasets into just what’s needed.
Advertisement
The first and most obvious way to sort data is from smallest to largest or largest to smallest, assuming you have numerical data.
We can apply the same sorting to any of the other columns, sorting by the date of hire, for example, by selecting the “Sort Oldest to Newest” option in the same menu.
How to Filter Data in Excel
Because our list is short, we can do this a couple of ways. The first way, which works great in our example, is just to uncheck each person who makes more than $100,000 and then press “OK.” This will remove three entries from our list and enables us to see (and sort) just those that remain.
Advertisement
We can also combine filters. Here we’ll find all salaries greater than $60,000, but less than $120,000. First, we’ll select “is greater than” in the first dropdown box.
In the dropdown below the previous one, choose “is less than.”
Next to “is greater than” we’ll put in $60,000.
Next to “is less than” add $120,000.
Advertisement
How to Filter Data from Multiple Columns at Once
In this example, we’re going to filter by date hired, and salary. We’ll look specifically for people hired after 2013, and with a salary of less than $70,000 per year.
Add “70,000” next to “is less than” and then press “OK.”
Type “2013” into the field to the right of “is after” and then press “OK.” This will leave you only with employees who both make less than $70,000 per year who and were hired in 2014 or later.
Advertisement
Excel has a number of powerful filtering options, and each is as customizable as you’d need it to be. With a little imagination, you can filter huge datasets down to only the pieces of information that matter.
How To Sort And Filter Data In Excel 2022
Setting Up Data
When you sort and conditionally format data, usually it’s a large number of rows. The following examples use a set of data with 20 rows. The data represents a list of customers and the amount of revenue made from each sale.
(Data setup for sorting and formatting examples)
Notice that headers are used at the top of each column. This is important for sorting when you want to change the column to sort on. Excel’s sorting functionality is handy even when you only have a few rows. If you want to view a list of revenue numbers based on the highest value or lowest value, instead of eyeing values and determining the right one based on your own human review, Excel 2019 will ensure that you can sort values and find the ones that have the highest revenue.
Sorting Data
(Excel sort buttons)
Excel can identify if your data is a set of dates, textual values or numbers. The sort function then orders cells based on the detect data type. For instance, if you have a list of revenue sales, Excel knows to sort cells based on numeric values. If you have cells formatted as dates, Excel knows that these values should be ordered in chronological order. Cells that are text values such as customer names are ordered alphabetically.
(Sort configuration window)
The “Sort By” dropdown has the headers for each column listed. Since we have “Customer” and “Revenue” as a column header, these two values display in the “Sort By” dropdown. If you don’t have column headers, Excel lists the column letter labels. Should you have several columns, having only letter labels make it difficult to configure your sort order.
The “Sort On” dropdown defaults to “Cell Values,” which means that the value is used for the sort. This is the typical configurations, but you can also sort on cell color or font color. This is useful when you set conditional formatting, which is covered in the next section.
The “Order” dropdown indicates if you want to sort data in ascending or descending order. The “A to Z” option means that you want to sort data in ascending order. The “Z to A” option means that you want to sort data in descending order.
(Data sorted by “Revenue”)
Notice that names still match up with revenue values. This is because the “Sort” functionality knows to keep rows aligned even though you’re ordering data by one column. If you decide to change the order to customer names, repeat these steps and choose “Customer” from the “Sort By” dropdown. Columns are still aligned properly but rows are ordered again based on the customer’s name.
Conditional Formatting
Sorting data doesn’t highlight certain cells that might need to stand out among the others. For instance, you might want to know which customers had revenue within a specified range. You might want to know which customers had revenue under or over a certain threshold. You can sift through all of your records, but conditional formatting that changes the font or background makes these cells stand out much more and makes them easier to find. With a short customer revenue list that contains only 20 rows, you can easily find the customers that bought and added revenue to your income, but if you had thousands of records even a sorted list would make it difficult to find specific records.
Excel has a function called “conditional formatting” that changes the color of a cell’s font or the background color of a cell to make it stand out and easy to find when you’re looking for certain values that meet a condition.
(Conditional Formatting button)
The “Conditional Formatting” button is found in the “Home” ribbon tab. The image above shows the Conditional Formatting button, which is also in the “Styles” category.
(Conditional formatting dropdown options)
With conditional formatting, you aren’t limited to just one color with one condition. You can set multiple colors using multiple conditions. For instance, you might want to know which customers brought in revenue under $100 and which customers brought in over $1000. You can then take this data and use it for reporting and product information. Using revenue charts and conditional formatting, you then know which customers are the best (or worst) to market to and upsell additional product.
From the “Highlight Cells Rules” dropdown options, choose the “Greater Than” option. This opens a new configuration window.
(Greater than conditional formatting configuration window)
(Conditional formatting set on cells greater than $1000)
With conditional formatting, you can now quickly see which customers brought in revenue over $1000. This formatting persists even when you sort cells again using the “Sort” option. Should you decide to use other conditions, you can make them other colors to make it easy to distinguish between the two conditions.
Once you understand the way conditional formatting and sorting works, you can make it much easier to work with large data sets that must be evaluated each month, especially revenue sheets.
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 The Data Model In Excel 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!