Seite 3
PUBLISHED BY Microsoft Press A division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2015 by Curtis Frye All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number: 2015935269 ISBN: 978-0-7356-9880-2 Printed and bound in the United States of America. First Printing Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at mspinput@microsoft.com. Please tell us what you think of this book at http://aka.ms/tellpress. This book is provided “as-is” and expresses the author’s views and opinions. The views, opinions and information expressed in this book, including URL and other Internet website references, may change without notice. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. Acquisitions Editor: Rosemary Caperton Developmental Editor: Rosemary Caperton Editorial Production: Online Training Solutions, Inc. (OTSI) Technical Reviewer: Rozanne Whalen Copyeditor: Kathy Krause (OTSI) Indexer: Susie Carr (OTSI) Cover: Twist Creative • Seattle
Seite 5
Contents I Introduction Who this book is for The Step by Step approach Download the practice files Sidebar: Adapt exercise steps Ebook edition Get support and give feedback Errata and support We want to hear from you Stay in touch Part 1: Create and format workbooks 1 Set up a workbook Explore the editions of Excel 2016 Excel 2016 Excel Online Excel Mobile Apps Become familiar with new features in Excel 2016 Create workbooks Modify workbooks Modify worksheets Merge and unmerge cells Customize the Excel 2016 app window Zoom in on a worksheet Arrange multiple workbook windows Add buttons to the Quick Access Toolbar Customize the ribbon Skills review Practice tasks 2 Work with data and Excel tables Enter and revise data
Seite 6
Manage data by using Flash Fill Move data within a workbook Find and replace data Correct and expand upon data Define Excel tables Skills review Practice tasks 3 Perform calculations on data Name groups of data Create formulas to calculate values Sidebar: Operators and Precedence Summarize data that meets specific conditions Set iterative calculation options and enable or disable automatic calculation Use array formulas Find and correct errors in calculations Skills review Practice tasks 4 Change workbook appearance Format cells Define styles Apply workbook themes and Excel table styles Make numbers easier to read Change the appearance of data based on its value Add images to worksheets Skills review Practice tasks Part 2: Analyze and present data 5 Manage worksheet data Limit data that appears on your screen Manipulate worksheet data Sidebar: Select list rows at random Summarize data in worksheets that have hidden and filtered rows
Seite 7
Find unique values within a data set Define valid sets of values for ranges of cells Skills review Practice tasks 6 Reorder and summarize data Sort worksheet data Sort data by using custom lists Organize data into levels Look up information in a worksheet Skills review Practice tasks 7 Combine data from multiple sources Use workbooks as templates for other workbooks Link to data in other worksheets and workbooks Consolidate multiple sets of data into a single workbook Skills review Practice tasks 8 Analyze alternative data sets Examine data by using the Quick Analysis Lens Define an alternative data set Define multiple alternative data sets Analyze data by using data tables Vary your data to get a specific result by using Goal Seek Find optimal solutions by using Solver Analyze data by using descriptive statistics Skills review Practice tasks 9 Create charts and graphics Create charts Create new types of charts Customize chart appearance Find trends in your data
Seite 8
Create dual-axis charts Summarize your data by using sparklines Create diagrams by using SmartArt Create shapes and mathematical equations Skills review Practice tasks 10 Create dynamic worksheets by using PivotTables Analyze data dynamically by using PivotTables Filter, show, and hide PivotTable data Edit PivotTables Format PivotTables Create PivotTables from external data Create dynamic charts by using PivotCharts Skills review Practice tasks Part 3: Collaborate and share in Excel 11 Print worksheets and charts Add headers and footers to printed pages Prepare worksheets for printing Fit your worksheet contents to the printed page Change page breaks in a worksheet Change the page printing order for worksheets Print worksheets Print parts of worksheets Print charts Skills review Practice tasks 12 Automate repetitive tasks by using macros Enable and examine macros Set macro security levels in Excel 2016 Examine macros Create and modify macros
Seite 9
Run macros when you click a button Run a macro when you open a workbook Insert form controls into a worksheet Skills review Practice tasks 13 Work with other Microsoft Office apps Include Office documents in workbooks and other files Link Office documents to Excel workbooks Embed files in Excel and other Office apps Create hyperlinks Paste charts into documents Skills review Practice tasks 14 Collaborate with colleagues Share workbooks Save workbooks for electronic distribution Manage comments Track and manage colleagues’ changes Add protection to workbooks and worksheets Finalize workbooks Authenticate workbooks Save workbooks for the web Import and export XML data Work with OneDrive and Excel Online Skills review Practice tasks Part 4: Perform advanced analysis 15 Perform business intelligence analysis Enable the Data Analysis add-ins Define relationships between tables Analyze data by using Power Pivot View data by using timelines
Seite 10
Bring in external data by using Power Query Skills review Practice tasks 16 Create forecasts and visualizations Create Forecast Worksheets Define and manage measures Define and display Key Performance Indicators Create 3D maps Skills review Practice tasks Keyboard shortcuts Glossary Index About the author Give us feedback Tell us what you think of this book and help Microsoft improve our products for you. Thank you! http://aka.ms/tellpress
Seite 11
I. Introduction Welcome! This Step by Step book has been designed so you can read it from the beginning to learn about Microsoft Excel 2016 and then build your skills as you learn to perform increasingly specialized procedures. Or, if you prefer, you can jump in wherever you need ready guidance for performing tasks. The how-to steps are delivered crisply and concisely —just the facts. You’ll also find informative, full-color graphics that support the instructional content. Who this book is for Microsoft Excel 2016 Step by Step is designed for use as a learning and reference resource by home and business users of Microsoft Office apps who want to use Excel to manage their data, create useful analyses and visualizations, and discover insights into their operations by using the rich business intelligence analysis tools found in Excel. The content of the book is designed to be useful for people who have previously used earlier versions of Excel and for people who are discovering Excel for the first time. The Step by Step approach The book’s coverage is divided into parts representing general Excel skill sets. Each part is divided into chapters representing skill set areas, and each chapter is divided into topics that group related skills. Each topic includes expository information followed by generic procedures. At the end of the chapter, you’ll find a series of practice tasks you can complete on your own by using the skills taught in the chapter. You can use the practice files that are available from this book’s website to work through the practice tasks, or you can use your own files. Download the practice files Before you can complete the practice tasks in this book, you need to download the book’s practice files to your computer from http://aka.ms/Excel2016SBS/files. Follow the instructions on the webpage. Important Excel 2016 is not available from the book’s website. You should install that app before working through the procedures and practice tasks in this book. You can open the files that are supplied for the practice tasks and save the finished versions of each file. If you later want to repeat practice tasks, you can download the original practice files again.
Seite 12
See Also For information about opening and saving files, see “Create workbooks” in Chapter 1, “Set up a workbook.” The following table lists the practice files for this book.
Seite 16
Adapt exercise steps This book contains many images of the Excel user interface elements (such as the ribbon and the app window) that you’ll work with while performing tasks in Excel on a Windows computer. Unless we’re demonstrating an alternative view of content, the screen shots shown in this book were captured on a horizontally oriented display at a screen resolution of 1920 × 1080 and a magnification of 100 percent. If your settings are different, the ribbon on your screen might not look the same as the one shown in this book. As a result, exercise instructions that involve the ribbon might require a little adaptation. Simple procedural instructions use this format: 1. On the Insert tab, in the Illustrations group, click the Chart button. If the command is in a list, our instructions use this format: 1. On the Home tab, in the Editing group, click the Find arrow and then, in the Find list, click Go To. If differences between your display settings and ours cause a button to appear
Seite 17
differently on your screen than it does in this book, you can easily adapt the steps to locate the command. First click the specified tab, and then locate the specified group. If a group has been collapsed into a group list or under a group button, click the list or button to display the group’s commands. If you can’t immediately identify the button you want, point to likely candidates to display their names in ScreenTips. Multistep procedural instructions use this format: 1. To select the paragraph that you want to format in columns, triple-click the paragraph. 2. On the Layout tab, in the Page Setup group, click the Columns button to display a menu of column layout options. 3. On the Columns menu, click Three. On subsequent instances of instructions that require you to follow the same process, the instructions might be simplified in this format because the working location has already been established: 1. Select the paragraph that you want to format in columns. 2. On the Columns menu, click Three. The instructions in this book assume that you’re interacting with on-screen elements on your computer by clicking (with a mouse, touchpad, or other hardware device). If you’re using a different method—for example, if your computer has a touchscreen interface and you’re tapping the screen (with your finger or a stylus)—substitute the applicable tapping action when you interact with a user interface element. Instructions in this book refer to Excel user interface elements that you click or tap on the screen as buttons, and to physical buttons that you press on a keyboard as keys, to conform to the standard terminology used in documentation for these products. When the instructions tell you to enter information, you can do so by typing on a connected external keyboard, tapping an on-screen keyboard, or even speaking aloud, depending on your computer setup and your personal preferences. Ebook edition If you’re reading the ebook edition of this book, you can do the following: Search the full text Print Copy and paste You can purchase and download the ebook edition from the Microsoft Press Store at http://aka.ms/Excel2016SBS/detail.
Seite 18
Get support and give feedback This topic provides information about getting help with this book and contacting us to provide feedback or report errors. Errata and support We’ve made every effort to ensure the accuracy of this book and its companion content. If you discover an error, please submit it to us at http://aka.ms/Excel2016SBS/errata. If you need to contact the Microsoft Press Support team, please send an email message to mspinput@microsoft.com. For help with Microsoft software and hardware, go to http://support.microsoft.com. We want to hear from you At Microsoft Press, your satisfaction is our top priority, and your feedback our most valuable asset. Please tell us what you think of this book at http://aka.ms/tellpress. The survey is short, and we read every one of your comments and ideas. Thanks in advance for your input! Stay in touch Let’s keep the conversation going! We’re on Twitter at http://twitter.com/MicrosoftPress.
Seite 19
Part 1: Create and format workbooks CHAPTER 1 Set up a workbook CHAPTER 2 Work with data and Excel tables CHAPTER 3 Perform calculations on data CHAPTER 4 Change workbook appearance
Seite 20
1. Set up a workbook In this chapter Explore the editions of Excel 2016 Become familiar with new features in Excel 2016 Create workbooks Modify workbooks Modify worksheets Merge and unmerge cells Customize the Excel 2016 app window Practice files For this chapter, use the practice files from the Excel2016SBS\Ch01 folder. For practice file download instructions, see the introduction. When you create a new Excel 2016 workbook, the app presents a blank workbook that contains one worksheet. You can add or delete worksheets, hide worksheets within the workbook without deleting them, and change the order of your worksheets within the workbook. You can also copy a worksheet to another workbook or move the worksheet without leaving a copy of the worksheet in the first workbook. If you and your colleagues work with a large number of documents, you can define property values to make your workbooks easier to find when you and your colleagues attempt to locate them by using the Windows search box. Another way to make Excel easier to use is by customizing the Excel app window to fit your work style. If you find that you use a command frequently, you can add it to the Quick Access Toolbar so it’s never more than one click away. If you use a set of commands frequently, you can create a custom ribbon tab so they appear in one place. You can also hide, display, or change the order of the tabs on the ribbon. This chapter guides you through procedures related to creating and modifying workbooks, creating and modifying worksheets, merging and unmerging cells, and customizing the Excel 2016 app window. Explore the editions of Excel 2016 The Microsoft Office 2016 suite includes apps that give you the ability to create and manage every type of file you need to work effectively at home, business, or school. The apps include Microsoft Word, Excel, Outlook, PowerPoint, Access, OneNote, and Publisher. You can purchase the apps as part of a package that includes multiple apps or purchase most of the apps individually. By using the Office 2016 apps, you can find the tools you need quickly and, because they
Die ersten 20 Seiten werden angezeigt. Laden Sie das Dokument herunter, um alles zu sehen.
Kommentare (0)
Noch keine Kommentare. Seien Sie der Erste!