Excel Formulas and Data Analysis Course：PivotTables and charts for analysis

不限时专题测验：关于PivotTables and charts for analysis的17道题，每道题作答后都有解释。每次题目和选项都会重新打乱。

课程
加拿大
96道题
核对于2026年10月13日
事实核查：2026年10月13日

从课文开始Excel Formulas and Data Analysis Course: Lessons：阅读时间约240分钟。测试中的每道题在课文中都有讲解。

专题测验6：PivotTables and charts for analysis

第1题，共17题
How should data be set up before you make a PivotTable from it?
- A blank row between months to separate them
- A subtotal row after each group of records
- One header row, no blank rows or columns
- Merged header cells over each group of columns
显示答案
答案: C. One header row, no blank rows or columns
Microsoft says PivotTable data should be in a tabular format with a single header row and no blank rows or columns. An Excel table is ideal.
第2题，共17题
You have selected a cell in your data. Which command starts a new PivotTable?
- Data > Subtotal
- Formulas > Insert Function
- Insert > PivotTable
- Home > Format as Table
显示答案
答案: C. Insert > PivotTable
Select the data, then choose Insert > PivotTable and pick where to put it.
第3题，共17题
In the PivotTable Field List, which area shows a field's items as labels down the left side?
- Columns
- Filters
- Rows
- Values
显示答案
答案: C. Rows
Rows area fields appear as Row Labels on the left. Columns area fields appear across the top.
第4题，共17题
Where does a field in the Filters area appear?
- As summarized numbers in the body of the table
- On a separate worksheet next to the PivotTable
- As a report filter above the PivotTable
- As row labels down the left side of the table
显示答案
答案: C. As a report filter above the PivotTable
Filters area fields are shown as top-level report filters above the PivotTable, so you can show one region or rep at a time.
第5题，共17题
You add a column of numbers, with no blanks or text, to the Values area. How does the PivotTable summarize it by default?
- Average
- Sum
- Count
- Max
显示答案
答案: B. Sum
Fields in the Values area are shown as a Sum by default when Excel reads them as numbers.
第6题，共17题
A Units column has a few blank cells. You add it to the Values area. Which summary does Excel use by default?
- Count
- Sum
- Average
- Min
显示答案
答案: A. Count
Count is the default for value fields that have nonnumeric values or blanks. Change it with Summarize Values By > Sum.
第7题，共17题
You fixed a wrong amount in the source data, but the PivotTable still shows the old total. What should you do?
- Refresh it (right-click > Refresh)
- Sort the source data from smallest to largest
- Type the right total over the PivotTable cell
- Press Ctrl+Z to undo the change to the source
显示答案
答案: A. Refresh it (right-click > Refresh)
Refresh (right-click > Refresh, or Alt+F5) makes the PivotTable read the source data again. Refresh All updates every PivotTable in the workbook.

第8题，共17题

You make a PivotTable from the sales sheet below with Region in Rows and Sum of Amount in Values. What does the South row show?

	A	B	C	D	E	F
1	日期	Rep	区域	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

3
2950
7540
1500

显示答案

答案: B. 2950

The PivotTable adds every South amount: 850 + 1,500 + 600 = 2,950.

第9题，共17题

A PivotTable from the sales sheet below has Product in Rows, Region in Columns and Sum of Units in Values. What number is shown for Alarms in the North column?

	A	B	C	D	E	F
1	日期	Rep	区域	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

显示答案

答案: C. 10

North Alarms rows have 6 units (Ana) and 4 units (Dev), so the cell shows 10.

第10题，共17题

A PivotTable from the sales sheet below has Region in Rows and Sum of Amount shown as % of Grand Total. What does the North row show (to 1 decimal place)?

	A	B	C	D	E	F
1	日期	Rep	区域	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

43.9%
33.3%
50.0%
56.1%

显示答案

答案: A. 43.9%

North's total is 3,310 and the grand total is 7,540. 3,310 / 7,540 = 43.9%.

第11题，共17题

From the sales sheet below, you put Region in Rows and drag the Rep field (text) into Values. What does the South row show?

	A	B	C	D	E	F
1	日期	Rep	区域	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

3
2950
1
Ben

显示答案

答案: A. 3

Rep holds text, so Excel uses Count. There are 3 South rows.

第12题，共17题
What is a slicer?
- A tool that splits one cell into two columns
- Buttons you click to filter a table or PivotTable
- A small chart that fits inside a single cell
- A rule that deletes repeated rows from a list
显示答案
答案: B. Buttons you click to filter a table or PivotTable
Slicers give clickable buttons for filtering and show which items are currently selected. Hold Ctrl to select more than one item.
第13题，共17题
Your chart compares monthly revenue (about $50,000) with the number of orders (about 120). The orders line looks flat. What does Microsoft suggest?
- A combo chart with orders on a secondary axis
- A 3-D clustered column chart for both series
- A pie chart with one slice for each month
- Delete the revenue series from the chart
显示答案
答案: A. A combo chart with orders on a secondary axis
A secondary axis helps when values vary widely between series or you mix data types, such as price and volume.
第14题，共17题
What is a sparkline?
- A chart that refreshes itself every second
- A red line that marks errors in formulas
- A tiny chart inside one cell
- A thick border style used under totals
显示答案
答案: C. A tiny chart inside one cell
A sparkline is a tiny chart in a cell. The types are Line, Column and Win/Loss.
第15题，共17题
You want to see whether staff with more training hours also have higher customer scores. Each person has one value for each. Which chart fits best?
- Sparkline
- Stacked column chart
- Pie chart
- Scatter (XY) chart
显示答案
答案: D. Scatter (XY) chart
Scatter charts plot pairs of numbers (x and y) as single points, which helps you compare many data points.
第16题，共17题
Which chart shows how values are spread out by grouping them into ranges called bins?
- Histogram
- Combo chart
- Doughnut chart
- Line chart
显示答案
答案: A. Histogram
A histogram shows the distribution of data grouped into frequency bins, for example how many invoices fall in each $100 range.
第17题，共17题
You added a trendline to a sales chart. Where do you set it to project the trend into future periods?
- Page Layout > Print Titles
- Data > Sort > Add Level
- Format Trendline pane > Forward
- Data > Data Validation > Settings
显示答案
答案: C. Format Trendline pane > Forward
In the Format Trendline pane, a value in Forward (or Backward) projects the trendline beyond your data.

专题测验6：PivotTables and charts for analysis

相关测试