77-727試験無料問題集「Microsoft Core Data Analysis, Manipulation, and Presentation 認定」
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers" worksheet,sortthe table to order the records by the "CountryOrRegion" field, with
customers in the United States first and customers in Canada second.
Then sort the customers in each country alphabetically (from A to Z) by the "StateOrProvince" field.
Finally, sort the customers in each state or province in ascending order by the 'PostalCode" field.
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers" worksheet,sortthe table to order the records by the "CountryOrRegion" field, with
customers in the United States first and customers in Canada second.
Then sort the customers in each country alphabetically (from A to Z) by the "StateOrProvince" field.
Finally, sort the customers in each state or province in ascending order by the 'PostalCode" field.
正解：
See explanation below.
Explanation
1. Select a cell in the "CountryOrRegion" field on the "Customers" worksheet.
2. Select Sort from theSort & Filtergroup in theDatatab.
3. In theSortdialog box, underColumnin theSort bybox, Select the "CountryOrRegion" field.
4. Under theSort Onoption, select the type of sort desired, in this caseValues, to sort by text.
5. UnderOrder, selectCustom List, then set it for customers in the United States to appear first, then the
customers in Canada.
6. Then to add another column to sort by, clickAdd Level.
7. In theSortdialog box, underColumnin theSort bybox, Select the "StateOrProvince" field.
8. Under theSort Onoption, selectValues.
9. UnderOrder, selectA to Z.
10. Add a final column by clickingAdd Level.
11. In theSortdialog box, underColumnin theSort bybox, Select the "PostalCode" field.
12. UnderSort On, selectValues.
13. UnderOrder, selectSmallest to Largest.
14. ClickOK.
References:
https://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654
Explanation
1. Select a cell in the "CountryOrRegion" field on the "Customers" worksheet.
2. Select Sort from theSort & Filtergroup in theDatatab.
3. In theSortdialog box, underColumnin theSort bybox, Select the "CountryOrRegion" field.
4. Under theSort Onoption, select the type of sort desired, in this caseValues, to sort by text.
5. UnderOrder, selectCustom List, then set it for customers in the United States to appear first, then the
customers in Canada.
6. Then to add another column to sort by, clickAdd Level.
7. In theSortdialog box, underColumnin theSort bybox, Select the "StateOrProvince" field.
8. Under theSort Onoption, selectValues.
9. UnderOrder, selectA to Z.
10. Add a final column by clickingAdd Level.
11. In theSortdialog box, underColumnin theSort bybox, Select the "PostalCode" field.
12. UnderSort On, selectValues.
13. UnderOrder, selectSmallest to Largest.
14. ClickOK.
References:
https://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654
Project 5 of 7: City Sports
Overview
The city events manager wants to analyze the enrollment changes over the past five years for various adult and
youth sports programs. You have been tasked to prepare tables for the analysis.
Unhide the "Summary" worksheet.
Overview
The city events manager wants to analyze the enrollment changes over the past five years for various adult and
youth sports programs. You have been tasked to prepare tables for the analysis.
Unhide the "Summary" worksheet.
正解：
See explanation below.
Explanation
1. SelectFormatfrom theHometab then chooseHide and Unhidethen selectUnhide.
OR
1. Hold on the Ctrl button on the keyboard and choose all the worksheet tabs then choose theUnhideoption in
the right menu.
2. Click on the first worksheet tab, then hold down the Shift key on the keyboard as you click on the last
worksheet tab, then choose theUnhideoption.
OR
1. From theViewtab, clickCustom Viewsin theWorkbook Viewsgroup.
2. Click the "Summary" view which is hidden, then clickShow.
References:
https://www.isunshare.com/blog/hide-or-unhide-sheets-in-excel-2016/
Explanation
1. SelectFormatfrom theHometab then chooseHide and Unhidethen selectUnhide.
OR
1. Hold on the Ctrl button on the keyboard and choose all the worksheet tabs then choose theUnhideoption in
the right menu.
2. Click on the first worksheet tab, then hold down the Shift key on the keyboard as you click on the last
worksheet tab, then choose theUnhideoption.
OR
1. From theViewtab, clickCustom Viewsin theWorkbook Viewsgroup.
2. Click the "Summary" view which is hidden, then clickShow.
References:
https://www.isunshare.com/blog/hide-or-unhide-sheets-in-excel-2016/
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers by Order" worksheet, use an Excel data tool to remove all records with duplicate
"CustomerID" values from the table. Do not remove any other records.
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers by Order" worksheet, use an Excel data tool to remove all records with duplicate
"CustomerID" values from the table. Do not remove any other records.
正解：
See explanation below.
Explanation
1. Select the cells in the "CustomerID" column which you want to have checked for duplicates.
2. SelectRemove Duplicatesfrom theDatatab.
3. Select the "CustomerID" column which you want to have checked under theColumnsoptions.
4. ClickOK.
References:
https://support.office.com/en-us/article/Find-and-remove-duplicates-00E35BEA-B46A-4D5D-B28E-66A552DC
Explanation
1. Select the cells in the "CustomerID" column which you want to have checked for duplicates.
2. SelectRemove Duplicatesfrom theDatatab.
3. Select the "CustomerID" column which you want to have checked under theColumnsoptions.
4. ClickOK.
References:
https://support.office.com/en-us/article/Find-and-remove-duplicates-00E35BEA-B46A-4D5D-B28E-66A552DC
Project 7 of 7: Farmers Market
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, use the data in the "Products" and "Total" columns only to create a 3-D
Pie chart. Position the new chart to the right of the column charts.
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, use the data in the "Products" and "Total" columns only to create a 3-D
Pie chart. Position the new chart to the right of the column charts.
正解：
See explanation below.
Explanation
1. Select the data you would like to use, in this case it would be the data in the "Products" and "Total"
columns from the "Summer Sales" worksheet.
2. Click onInsert Pie Chartsituation on theInserttab then pick the 3-D Pie chart as desired.
3. Format the chart as desired by usingChart Elements, theChart Styles, or theChart Filters.
4. Drag the Pie Chart to the desired location which is to the right of the column charts.
References:
https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863
Explanation
1. Select the data you would like to use, in this case it would be the data in the "Products" and "Total"
columns from the "Summer Sales" worksheet.
2. Click onInsert Pie Chartsituation on theInserttab then pick the 3-D Pie chart as desired.
3. Format the chart as desired by usingChart Elements, theChart Styles, or theChart Filters.
4. Drag the Pie Chart to the desired location which is to the right of the column charts.
References:
https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863