Pivot Tables Tutorial

本文详细介绍如何使用 Excel PivotTable 对原始数据进行高效汇总与分析。通过实例展示如何创建及利用 PivotTable 进行数据分组,解答如政党分布、选民年龄结构等问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

What is an Excel Pivot Table?

You might think of a pivot table as a user created summary table of your original spreadsheet. You create the table by defining which fields to view and how the information should be displayed. Based on your field selections, Excel organizes the data so you see a different view of your data.

As example, I’ve uploaded a sample spreadsheet of 4000 fictitious people, which includes the following data fields:

  • Voter ID
  • Party Affiliation
  • Their precinct
  • Age group
  • When they last voted
  • Years they’ve been registered
  • Ballot status

sample excel flat file

Looking at the first 20 voter records, you can see the content is boring. In this format, the key question it answers is how many voters exist in all the precincts.

Using Excel pivot tables, you can organize and group the same data in ways that start to answer questions such as:

  • What is the party breakdown by precinct?
  • Do voters use permanent absentee ballots?
  • Which precincts have the most Democrats?
  • How many voter pamphlets do I need for Precinct 2416?
  • Do 18-21 year olds vote?

Excel pivot tables allow you to group the spreadsheet or external data source by any of your data fields. The thumbnail below shows a count of voters by party by precinct.

blank excel pivot table

Using a pivot table, I can continue to slice the information by selecting additional fields from thePivotTable Field List. For example, I can take the same data and segment by voter age group.

Example pivots table with multiple criteria

Understanding Pivot Table Structures

In the thumbnail above, I’ve labeled the main areas of the pivot table.

(1) PivotTable Field List – this section in the top right displays the fields in your spreadsheet. You may check a field or drag it to a quadrant in the lower portion.

(2) The lower right quadrants - this area defines where and how the data shows on your pivot table. You can have a field show in either a column or row. You may also indicate if the information should be counted, summed, averaged, filtered and so on.

(3) The red outlined area to the left is the result of your selections from (1) and (2). You’ll see that the only difference I made in the last pivot table was to drag the AGE GROUP field underneath the PRECINCT field in theRow Labels quadrant.

How to Create an Excel Pivot Table

There are several ways to build a pivot table. Excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. For example, numeric data such as Precinct counts tends to appear to the right in columns. Textual data, such as Party would appear in rows.

While you can simply check fields to display and let Excel build your pivot table, I prefer to use the “drag and drop” method. This is partly because I like to visualize my data in columns and rows. I think it may also be easier if you have fields, which can appear to be numbers like a precinct value.

1. Open your original spreadsheet and remove any blank rows or columns.

2. Make sure each column has a heading, as it will be carried over to the Field List.

3. Make sure your cells are properly formatted for their data type.

4. Highlight your data range

5. Click the Insert tab.

6. Select the PivotTable button from the Tables group.

7. Select PivotTable from the list.

Insert tab and create pivot table button

The Create PivotTable dialog appears.

create pivot table dialog box

8. Double-check your Table/Range: value.

9. Select the radio button for New Worksheet.

10. Click OK.

A new worksheet opens with a blank pivot table. You’ll see that the fields from our source spreadsheet were carried over to thePivotTable Field List.

blank Excel pivot table

11. Drag an item such as PRECINCT from the PivotTable Field List down to theRow Labels quadrant. The left side of your Excel spreadsheet should show a row for each precinct value. You should also see a checkmark appear next to PRECINCT.

adding one criteria to pivot table

12. The next step is to ask what you would like to know about each precinct. I’ll drag the PARTY field from thePivotTable Field List to the Column Labels quadrant. This will provide an additional column for each party. Note that you won’t see any numerical data.

dragging second criteria for pivot table

13. To see the count for each party, I need to drag the same field to the Values quadrant. In this case, Excel determines I want a Count of PARTY. I could double-click the entry and choose anotherField Setting. Excel has also added Grand Totals.

Adding column labels to pivot table

Additional Groupings and Options

As you build your Excel pivot table, you’ll probably think of additional ways to group the information. For example, you might want to know the Age Range of voters by Precinct by Party. In this case, I would drag theAGE GROUP column from the PivotTable Field List down below thePRECINCT value in Row Labels.

adding a second row critera to pivot table

Each age group is broken out and indented by precinct. At this stage, you might also be thinking of usability. As with a regular spreadsheet, you may manipulate the fields. For example, you might want to rename “Grand Total” to “Total” or even collapse the age values for one or more precincts. You can also hide or show rows and columns. These features work the same way as a regular spreadsheet.

One area that is different is the pivot table has its own options. You can access these options by right-clicking a cell within and selectingPivotTable Options… For example, you might only want Grand Totals for columns and not rows.

There are also ways to filter the data using the controls next to Row Labels or Column labels on the pivot table. You may also drag fields to theReport Filter quadrant.


内容概要:该研究通过在黑龙江省某示范村进行24小时实地测试,比较了燃煤炉具与自动/手动进料生物质炉具的污染物排放特征。结果显示,生物质炉具相比燃煤炉具显著降低了PM2.5、CO和SO2的排放(自动进料分别降低41.2%、54.3%、40.0%;手动进料降低35.3%、22.1%、20.0%),但NOx排放未降低甚至有所增加。研究还发现,经济性和便利性是影响生物质炉具推广的重要因素。该研究不仅提供了实际排放数据支持,还通过Python代码详细复现了排放特征比较、减排效果计算和结果可视化,进一步探讨了燃料性质、动态排放特征、碳平衡计算以及政策建议。 适合人群:从事环境科学研究的学者、政府环保部门工作人员、能源政策制定者、关注农村能源转型的社会人士。 使用场景及目标:①评估生物质炉具在农村地区的推广潜力;②为政策制定者提供科学依据,优化补贴政策;③帮助研究人员深入了解生物质炉具的排放特征和技术改进方向;④为企业研发更高效的生物质炉具提供参考。 其他说明:该研究通过大量数据分析和模拟,揭示了生物质炉具在实际应用中的优点和挑战,特别是NOx排放增加的问题。研究还提出了多项具体的技术改进方向和政策建议,如优化进料方式、提高热效率、建设本地颗粒厂等,为生物质炉具的广泛推广提供了可行路径。此外,研究还开发了一个智能政策建议生成系统,可以根据不同地区的特征定制化生成政策建议,为农村能源转型提供了有力支持。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值