BUS 311 Excel Chapter 5 TESTBANK QUIZ

अब Quizwiz के साथ अपने होमवर्क और परीक्षाओं को एस करें!

Which of the following would be an effective calculated field labeled Retail Price to calculate a retail price that is 300% of wholesale price (for example, the wholesale price is $3 and retail price is $9) from a Wholesale Price field?

= Wholesale Price * 3 with a Name of Retail Price in the Insert Calculated Field dialog box

Grand total

The full summary of all items in a group

MAX

The greatest value

MIN

The least value

Relationship

The link between the fields with matching data in two tables

COUNT

The number of values

VALUES

VALUES

Table

Where data is stored in a data model

Slicer

A small window containing one button for each unique item in a field.

Group

A tool to organize items in an outline

You created a slicer on the field State. What items would you be most likely to see in the slicer?

AL, CA, GA, KY, MS, NC, OR, and VA

You would like to see both average sales and total sales for a category in a pivot table. Which of these would best accomplish both a Total and an Average column on the field Sales?

Add sales to the VALUES area and change its Value Field Settings to Average in the Summary value field by and then repeat, allowing it to default to SUM.

Which function is not a Subtotal function?

Aggregate

Which is not true about slicers?

Aggregate values do not respond to slicers.

The slicer ________ is text that displays in the header at the top of the slicer window to identify the data in the field.

Caption

What field is in the ROWS area of the PivotTable that built this PivotChart?

Category

PivotTable Styles

Change the appearance of a PivotTable's color, Font, effects and shading

You created a PivotTable and later made changes to values in the underlying data. What happens to the PivotTable?

Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.

__________is the process of analyzing large amounts of data to identify trends and patterns in the data

Data Mining

A(n) ________________ is a collection of related tables forming a database.

Data model

A PivotTable time line can filter dates by all of these, except ________.

Decade

PivotTable Layout

Determine which totals and subtotals show

Given a list of candy bars in a field called Item and their prices in a field called Price on an empty PivotTable, how would you find out how many of each type of candy bar you have?

Drag Item to both the VALUES and the ROWS area.

A PivotTable Calculated field starts with a(n) _____________________.

Equal

Data mining

Evaluating a large dataset looking for trends.

Which of the following is not true about the PivotTable Fields task pane?

Excel automatically assigns the table the name Table1.

A PivotChart has no filters available.

False

A PivotTable can only display one summary statistic at a time.

False

A PivotTable has a live update feature which refreshes the data in real time as the dataset changes.

False

A PivotTable is limited to a single table of data.

False

A calculated field must be built in the original data set.

False

If you are interested in finding what products people buy when they buy coffee, you would employ data warehousing techniques on supermarket sales data.

False

When the underlying data changes, a PivotTable automatically updates.

False

Displaying subtotals at the top or bottom of a section is controlled on the PivotTable styles gallery.

Fasle

Filters and slicers are the same.

Fasle

Which of the following functions would you use to obtain the summary data that is visible in a PivotTable?

GETPIVOTDATA

Grouping is ________.

Joining rows or columns of related data into a single entity for analysis

The calculation option which displays the values of a specific field from greatest to least is Rank _______________________

Largest to Smallest

If you want to turn off grand totals for columns on a PivotTable, select this option in the Grand Totals options.

On for Rows Only

A(n) _____________ is a hierarchical structure used to group and summarize related data.

Outline

The row level above the row you are working with in a PivotTable is the__________row.

Parent

A primary benefit of creating a PivotTable is that you can rearrange or ____________ fields to view and summarize data from different perspectives.

Pivot

A PivotChart is similar to a regular chart, except it is based on the categories and structure of a(n)_____________________

Pivot table

A(n) ________ is an interactive table which uses calculations to consolidate and summarize information.

PivotTable

A(n) __________________ quickly formats a PivotTable with bold, fonts, shading, headers.

PivotTable style

____________________ is an add-in that enables you to relate tables and external data from multiple sources.

Power pivot

_________________Analysis is the built-in tool which automatically builds a recommended pivot table based on your dataset.

Quick

A(n) _______________ associates matching fields between 2 tables.

Relationship

Where would you access the Manage Relationships dialog box to create a data model in Excel?

Relationships on the Data tab

The ________ function is the default summary statistic for PivotTables.

SUM

You have a PivotTable with 2 fields in the ROWS area and you want to see only the top level of categories. How do you hide the entire second level without removing it from the ROWS area?

Select a field in the row headers and click Collapse Field in the Active Field Group on the Analyze tab.

Which of the following is not a valid method to remove a field from a PivotTable?

Select the field to be removed in the PivotTable and press DELETE.

What does applying a banded row format do to a Pivot Table?

Shades odd and even rows differently

A(n) _____________ is the tool which appears in a separate window and displays all available options to show or hide data for a field.

Slicer

The first step in using the subtotal feature in Excel is to ____________ the data.

Sort

A worksheet of faculty members at your university contains these columns in this sequence: Rank, Last Name, First Name, Department, and Salary. What is the first step to subtotal departmental salaries?

Sort by Department

_______________________display the total values for each particular row or column in a PivotTable.

Subtotals

Dataset

The underlying data of a pivot table

Which of the following is not true about grouping data?

This happens automatically when Excel creates an outline.

A PivotChart has live updates as you make changes to the PivotTable.

True

A PivotTable can display no subtotals and no grand totals.

True

A Recommended PivotTable creates a PivotTable on a new worksheet.

True

A relationship is an association between two tables where the tables contain a common field of data.

True

A slicer has a window with a button for each item in a field.

True

Auto Outline creates an outline based on the data structure.

True

Creating a data model in Excel is very similar to creating relationships between tables in Access.

True

Excel is able to group data both by rows and by columns in a subtotal.

True

In a Subtotaled Worksheet, this is a correct example of a subtotal function in cell B90 which sums B80:B89 : =SUBTOTAL(9,B80:B89).

True

Sum is the default action of a numerical VALUES field in a PivotTable.

True

The % Period Expense Column on this PivotTable uses a % of Parent Row Total calculation option.

True

You can apply filters on dates to display summary statistics for data occurring during a particular time period.

True

You can change the label of a value.

True

You can clear all fields from a PivotTable.

True

You can create custom calculations in a PivotTable.

True

If you create a Recommended PivotTable on a group of data about book sales with these columns: Discipline (i.e. Business), Area (i.e. Finance and Marketing), Units Sold, Wholesale Price, what column might Excel use as values?

Units Sold

What does a + button indicate to the left of a row heading in an outline?

You can expand the details of that category

Using the % of Grand Total Calculation Option will display the value as ________.

a percentage of the grand total

A subtotal row must contain at least one ________ function.

aggregate

PivotTables ___________ or summarize large amounts of data for analysis.

aggregate

A slicer can be formatted so the ___________ display in one or many columns.

buttons

If the underlying data for a PivotTable changes, you must ____________ the PivotTable to show the changes.

refresh

Data model

related tables

Which of the following is not controlled by a PivotTable style?

subtotal calculations

ROWS area

the field used to organize a pivot table along the left side of the table

COLUMNS area

the field used to organize a pivot table along the top of the table

FILTERS area

the first field in this area creates a filter that appears in cell B1

What dictates the order of hierarchy of fields in the rows of a PivotTable?

the order of the fields in the ROWS area.

Pivot Chart Styles

Change the colors and backgrounds of pivot charts

Quick Layout

Change the layout of the elements on a Pivot Chart

___________ apply different formatting and color schemes to a PivotChart.

Chart Styles

Related field

The field with matching data in two tables used to identify a correlation

Select PivotTable styles ________.

from thumbnails in a style gallery

Which of the following is true about creating a blank PivotTable?

A PivotTable may be created on the same worksheet as the source data.

SUM

Adds the values in the field together

AVERAGE

Adds the values in the field together and then divides by the number of values

Change Colors

Change the color scheme of a pivotChart

LEGEND (SERIES)

COLUMNS

Summary statistics

Calculate like totals or averages

Which is an effective way to change a SUM of Pricefield in the VALUES area to an AVERAGE function?

Click the field's arrow in the VALUES area and select Value Field Settings.

Which option would clear a PivotTable filter on a State field?

Click the funnel icon to the right of the State filter and then select All and then click OK.

Matching: Data model

Collection of related tables

No Calculation

Default value shows the raw summary of the value.

If you would like to show a subtotal of the wholesale price of a number of items of clothing on a PivotTable of data of both men and women. You have the following fields: Gender, Item and Price. Sum of Price is in the VALUES area. Gender is in the ROWS area. How do you get a subtotal of shirts and pants to display for each gender?

Drag the Item field beneath Gender in ROWS.

% of Column Total

Each value displays as a percentage of the column.

% of Parent Row Total

Each value displays as a percentage of the hierarchical level above it.

% of Grand Total

Each value displays as a percentage of the whole and adds up to 100%.

% of Row Total

Each value shows as a percentage of the row.

FILTERS

FILTERS

Date (date of the purchase)

FILTERS or not used, at all

Category (Regular Meal, Snacks, etc.)

First in ROWS

Quantity (quantity of item sold)

First in VALUES

VLOOKUP

Function similar to how a data model matches related fields

__________________display the total values for each particular row or column in a PivotTable.

Subtotals

Which is not true of filters in a PivotTable?

Summary statistics do not change to reflect the values selected in the slicer.

Plot Area

PivotTable

AXIS (CATEGORY)

Rows

Item (Pizza Slice, Cookies, Water, etc.)

Second in ROWS

Extended Price (unit price * quantity of item sold)

Second in VALUES

subtotal

Show intermediate calculated values for a group of items

Collapse

Show less detail

A(n) ________ is a user-defined field that derives its value by performing calculations on other fields in a PivotTable.

calculated field

A(n) ____________ hides some values from a field in a PivotTable.

collapse

Moving one of two fields from the ROWS area to the COLUMNS area of a PivotTable would ________.

consolidate data and remove redundant labels

If you have related data stored in multiple tables, create a(n) ________ to create a PivotTable on the combined data

data model

How would you add a date filter to a PivotTable with these fields: Date, Category, Item, Unit Price, Number Sold, and Extended Price?

drag the Date field to the FILTERS area.

VALUES area

fields added to this area have a calculation of some sort applied

In a PivotTable Fields task pane, the ____________ area filters the data to display results based on particular conditions you set.

filters

The four areas for organizing fields in a PivotTable are ROWS, COLUMNS, _______________, and VALUES.

filters

Outline

hierarchical structure

A(n) ________ is the association between tables where both tables contain a common field.

relationship

A PivotChart ________.

represents the consolidated data of a PivotTable visually

The ______________ area is replaced with the AXIS (CATEGORY) in PivotChart task pane.

rows

expand

show more detail


संबंधित स्टडी सेट्स

ITC203 Wk 5 Designing the User Interface

View Set

Chapter 7 Consumer Behavior MKT 3323

View Set

Using Addition to Solve Systems Attempt #1

View Set

vocab unit 4 (affiliated, ascertain, attainment)

View Set

COP3330 Exam 1, Exam 2, and Fake Final

View Set

Prep-U Ch. 44 - Digestion and GI Function

View Set

Family Medicine 05: 30-year-old female with palpitations

View Set