- DATA ANALYSIS WITH POWER TOOLS AND CREATING MACROS GETTING STARTED Open the file SCEX1610aFirstLastName1.xlsx, available for download from the SAM website. Save the file as macro-enabled worksheetSCEX1610aFirstLastName2.xlsmby changing the “1” to a “2”. O If you do not see the.xlsmfile extension in the Save As dialog box, do not type it.
- Excel 9: Formula Auditing, Data Validation, and Complex Problem Solving. Excel 10: Data Analysis with Power Tools and Creating Macros. Excel 11: User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features in Excel. Access 8: Macros, Navigation Forms, and Control Layouts. Access 9: Administering a Database System.
A much better chart to analyze this data is the Pareto Chart. It's hard to create a Pareto Chart in Excel, but its easy using an add-in like QI Macros. Just select your data, click on the QI Macros menu on your Excel tool-bar and select Pareto Chart. QI Macros will do all of the math and draw the chart for you. Interpreting the Pareto Charts. I also created a date table to use with our dataset (see Creating a Date Table below). Power Pivot for Excel makes it easy to create a date table quickly in order to consolidate by months, quarters, and days of the week. The user can also create a more custom date table to analyze by weeks, fiscal years, or any organization-specific groupings.
Quality Learning! This series goes beyond learning Office applications by providing contextual relevance with a trusted and proven pedagogy combined with real world scenarios and assessments. With a deeper focus on employability, this new edition demonstrates real world relevance and professionalism.. more
Quality Learning! This series goes beyond learning Office applications by providing contextual relevance with a trusted and proven pedagogy combined with real world scenarios and assessments. With a deeper focus on employability, this new edition demonstrates real world relevance and professionalism with all-new case scenarios written to reflect the top industries hiring for Microsoft Office skills. In addition, in the Shelly Cashman Collection Series pedagogy, each chapter presents a practical problem to be solved within a project planning framework and includes step-by-step instructions with support screenshots to guide students along. Instructional steps are further supported by key features, including Q&A boxes, Experimental Steps, and BTW features. This proven project-based pedagogy teaches learning through context of personal, academic, and business-related skills to help extend and apply student learning.
Easy! As part of our new intuitive design, the updated readings are now closely aligned with SAM (Skills Assessment Manager) Trainings, Exams, and Projects to reflect the latest and greatest Office 365 and Office 2019 features. With a focus on more practice opportunities, SAM Projects have expanded Textbook Projects to all module levels (Introduction, Intermediate, and Advanced). To help students utilize Textbook Projects fully, a new SAM icon is now embedded in the readings. The icon indicates when a student should download their files from SAM or MindTap to start a Textbook Project, and when they should upload their completed Textbook Project for instantaneous grading and feedback. There are also new SAM End-of-Module Projects, which are based on the Case Problems found in each module and offer full auto grading. With a variety of easy-to-use assessments, students will be able to quickly and efficiently demonstrate their understanding of Office.
Affordable! The product suite is available in a variety of purchase options to give students the best value for the tools and resources that they need and to allow students to choose how to learn. With the launch of Cengage Unlimited, student can access all of the Microsoft Office content for just one price, plus thousands of other resources, plus opt for a low-cost rental or purchase of a bound book or loose-leaf text. https://softwareassets.mystrikingly.com/blog/street-fighter-demo. Students benefit from access to the full Office Collections in MindTap.
Trusted Partner! This product suite was developed to cover the most critical features of the Office applications to drive student engagement and comprehension. By implementing feedback from partners in the Cengage Computing Community and beyond, as well as incorporating the 2019 Microsoft Office Skills (MOS), students will learn and apply key skills they need for success not just on their educational and professional journeys, but also in their day-to-day usage of Office applications. With Cengage’s dedication to providing meaningful and engaging solutions for students, our team is available to connect and assist with onboarding and training in the classroom to further support students on their Microsoft Office journey.
DATA ANALYSIS WITH POWER TOOLS AND CREATING MACROS
GETTING STARTED
· Open the file SC_EX16_10a_FirstLastName_1.xlsx, available for download from the SAM website.
· Open the file SC_EX16_10a_FirstLastName_1.xlsx, available for download from the SAM website.
· Save the file as macro-enabled worksheetSC_EX16_10a_FirstLastName_2.xlsmby changing the “1” to a “2”.
o If you do not see the .xlsmfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
· To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
o Support_SC_EX16_10a_2013-2015.xlsx
o Support_SC_EX16_10a_Population.xlsx
Shareit for mac 10 9 5 download. · With the file SC_EX16_10a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
Tornado Center Data Analysis With Power Tools And Creating Macros For Beginners
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
· To complete this project, you need to add the Power Pivot and Developer tabs to the ribbon and the Power View button to the Insert tab as follows:
o From the File tab, click the Options button. In the Data section of the Advanced tab, click the checkbox next to Enable Data Analysis add-ins: Power Pivot, Power View, and Power Map.(Microsoft Silverlight is required for Power View.) In the Add-ins tab, choose Manage COM add-ins and click Go… Click the box next to Microsoft Power Map for Excel, Microsoft Power Pivot for Excel, and Microsoft Power View for Excel, and click OK.
*Power Pivot, Power View, and Power Map are not available on Microsoft Office Home and Business 2016 and Microsoft Office 365 Business.
o Right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu.In the Main Tabs area of the Excel Options dialog box, click the Developer check box, then click the OK button to close the Excel Options dialog box and add the Developer tab to the ribbon.
PROJECT STEPS
1. You work as a support specialist for the Tornado Center, a group of researchers at the University of Nebraska who provide statistics and other information to businesses and citizens in Nebraska. Your supervisor,Karen Chao, has asked you to gather data on Nebraska tornadoes from 2013 to 2015 and then provide statistical information to a client interested in business development. To do so, you need to import data from various sources and use the Excel power tools.
Start by collecting tornado data from another Excel workbook, which includes the Enhanced Fujita scale, a way to rate tornadoes according to the amount of damage they cause. Use the Get & Transform tools to create a query and load data from this workbook into a new table as follows:
a. Create a new query that imports data from the Support_SC_EX16_10a_2013-2015.xlsxworkbook.
b. Load the data from the Tornadoesworksheetto a table in a new worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook, using Tornadoes as the name of the new worksheet.
c. Because all of the data is for tornadoes in Nebraska, use the Query Editor to remove the State column, and then load the transformed data into the worksheet.
![Power Power](https://s-media-cache-ak0.pinimg.com/236x/02/79/f7/0279f7d17d706132fcceb0c0b32980e8--goal-budget.jpg)
2. The client wants to know the population of the twenty most populous counties in Nebraska. To provide this information, create another query and load data from another Excel workbook into a new table as follows:
a. Create a new query that imports data from the Support_SC_EX16_10a_Population.xlsxfile, available for download from the SAM website.
b. Load the data from the Population by County worksheet to a table in a new worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook, using Top 20 Population as the name of the new worksheet.
c. Edit the table to display the data the client requested and make it more useful as follows:
o Use the Query Editor to remove the top3 rows, which contain a heading or blank cells.
o Remove column 5, which includes a ranking calculation.
o Use the first row as headers.
o Sort the data in descending order by Population.
o Keep the top 20 rows of the data.
o Load the transformed data into the worksheet.
3. Add the two queries you created to the data model so you can use them with Power Pivot as follows:
a. Add the Population by County query to the data model.
b. Add the Tornadoes query on the Tornadoes worksheet to the data model. (Hint: Do not close the Power Pivot for Excel window after performing this substep.)
c. Use the Power Pivot for Excel window to create a PivotTable in a new worksheet.
d. Use Tornado PivotTable as the name of the new worksheet.
The client wants to know which of the twenty most populated counties experienced tornadoes in 2013–2015 along with the population of each county. Build the new PivotTable to display the tornado and population data at the same time as follows:
e. Use the following fields from the Population_by_Countytable in the PivotTable areas:
o County field: Rows box
o Population field: Values box
f. Use the following field from the Tornadoestable in the PivotTable areas:
o Fujita field: Values box
g. Create a relationship using the following tables and columns:
o Table: Tornadoes
o Column (Foreign): County
o Related Table: Population_by_County
o Related Column (Primary): County
h. Use County as the column heading in cell B3, use Population as the column heading in cell C3, and use Tornadoes as the column heading in cell D3. (Hint: The “(blank)” entry is for tornadoes that occurred in counties not among the 20 most populated counties.)
4. The client wants to know how concentrated the population is in each of the 20 most populous counties. Create another PivotTable that shows the population per square mile in these countiesas follows:
a. Use the Power Pivot for Excel window to create a second PivotTable in a new worksheet. (Hint: If necessary, use the Manage button in the Data Model group on the Power Pivot tab to open the Power Pivot for Excel window.)
b. UsePopulation PivotTable as the name of the new worksheet.
Close the Power Pivot for Excel window.
c. Build the new PivotTable to use the following fields from the Population_by_Countytable in the PivotTable areas:
o County field: Rows box
o Population field: Values box
o Sq Mi field: Values box
5. To add a column showing the population per square mile, create a measure as follows:
a. Use Population per Sq Mi as the name of the new measure.
b. Use [Sum of Population]/[Sum of Sq Mi]as the formula.
c. ChooseNumber as the category and Whole Number as the format.
6. To provide another visual representation of the tornado data for the top 20 most populated Nebraska counties, create a Power View report as follows:
Tornado Center Data Analysis With Power Tools And Creating Macros Spreadsheet
a. Insert a Power View report on a new worksheet, using Power View as the name of the new worksheet.Move the newly created worksheet so that it’s the fifth worksheet in the workbook. (Hint: If you cannot move the worksheet itself, move the other worksheets.)
b. Select the Fujitafield in the Tornadoestable.
c. Select the County and Populationfields (in that order) in the Population_by_Countytable. (Hint: If a message appears indicating you may need relationships between tables, close the message. You already created the relationships.)
d. Resize the table to fill the left pane of the Power View area.
e. Add the Fujita field in the Tornadoes table to the Filters pane.
f. Filter the data to display populations for counties that experienced tornadoes with an EF-1 rating.
g. Switch the visualization of the data to a Clustered Bar chart.
h. Use County Population and Tornado Rating as the chart title.
7. Switch to the Top 20 Population worksheet.To develop a map showing tornado data for the 20 most populated counties in Nebraska, create a 3D map as follows:
a. Open the 3D Maps window, and in Scene 1, use the State field in the Population_by_County table as a Location to focus on the state of Nebraska.
Tornado Center Data Analysis With Power Tools And Creating Macros Tutorial
b. Display state name labels on the map.
c. Add the County field in the Population_by_County table as a second Location to indicate the 20 most populous counties in Nebraska.
d. Add the Fujita field in the Tornadoes table as the Height to show where the most tornadoes occurred.
e. Close the field list, remove the legend, and zoom in six times to display the Nebraska city names on the map, and then tilt the map up four times.
f. Create a text box using Tornadoes in the Top 20 Nebraska Counties as the text in the title field.
g. Capture the screen showing the 3D map, and then close the 3D Maps window.
Vegas crest casino no deposit. h. Paste the map in cell A1 of the Home Page worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook.
8. Add hyperlinks to the Home Page worksheet as follows to improve navigation in the workbook:
How to zip large files on mac. a. In cell M3, link the “Top 20 Nebraska Counties” text to cell A1 of the Top 20 Population worksheet in the current workbook.
b. In cell M4, link the “Nebraska Tornadoes by County” text to cell A1 of the Tornadoes worksheet in the current workbook.
9. Your supervisor, Karen Chao, wants to include a note on two worksheets that the Tornado Center created this workbook. Create and record a macro to automate this task as follows:
Tornado Center Data Analysis With Power Tools And Creating Macros Pdf
a. Enable all macros in the workbook, and make sure the Use Relative References option is selected in the Developer tab.
b. Select cell M7.
c. Create a macro to be stored in this workbook using Created_by as the name of the macro.
d. Use CTRL+m as the shortcut key.
e. Begin recording the macro, and enter the following text in cell M7:
This workbook was created by the Tornado Center at the University of Nebraska.
f. Apply wrap text formatting to cell M7.
g. Stop recording the macro.
10. Go to the Enhanced Fujita Scale worksheet. In cell E3, run the Created_by macro.
Your workbook should look like the Final Figures on the following pages. Depending on your version of Office, the order of the worksheets may be different. With the exception of the worksheet mentioned in step 6, this will not affect your grading. Save your changes (as a macro-enabled workbook), close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.