Xem 27,918
Bạn đang xem bài viết Excel 2022: Intro To Pivottables được cập nhật mới nhất ngày 28/05/2022 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. Cho đến thời điểm hiện tại, bài viết này đã đạt được 27,918 lượt xem.
--- Bài mới hơn ---
Lesson 27: Intro to PivotTables
/en/excel2016/inspecting-and-protecting-workbooks/content/
Introduction
When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways.
Optional: Download our practice workbook.
Watch the video below to learn more about PivotTables.
Using PivotTables to answer questions
Consider the example below. Let’s say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders inpidually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with.
Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read. When we’re done, the PivotTable will look something like this:
Once you’ve created a PivotTable, you can use it to answer different questions by rearranging—or pivoting—the data. For example, let’s say we wanted to answer What is the total amount sold in each month? We could modify our PivotTable to look like this:
To create a PivotTable:
If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then go to AnalyzeRefresh.
Pivoting data
One of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover new trends and patterns.
To add columns:
So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you’ll need to add a field to the Columns area.
- Drag a field from the Field List into the Columns area. In our example, we’ll use the Month field.
- The PivotTable will include multiple columns. In our example, there is now a column for each person’s monthly sales, in addition to the grand total.
To change a row or column:
Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in question, then replace it with another.
- Drag the field you want to remove out of its current area. You can also uncheck the appropriate box in the Field List. In this example, we’ve removed the Month and Salesperson fields.
- Drag a new field into the desired area. In our example, we’ll place the Region field under Rows.
- The PivotTable will adjust—or pivot—to show the new data. In our example, it now shows the amount sold by each region.
To learn more
Once you’re comfortable with PivotTables, check out our Doing More with PivotTables lesson for even more ways to customize and manipulate them.
Challenge!
- Open our practice workbook.
- Create a PivotTable in a separate sheet.
- We want to answer the question What is the total amount sold in each region? To do this, select Region and Order Amount. When you’re finished, your workbook should look like this:
- In the Rows area, remove Region and replace it with Salesperson.
- Add Month to the Columns area.
- Change the number format of cells B5:E13 to Currency. Note: You might have to make columns C and D wider in order to see the values.
- When you’re finished, your workbook should look like this:
/en/excel2016/doing-more-with-pivottables/content/
--- Bài cũ hơn ---
Cập nhật thông tin chi tiết về Excel 2022: Intro To Pivottables 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!
