Microsoft Excel 2010_ Data Analysis and - Winston_ Wayne L_.pdf - Microsoft Excel 2010_ Data Analysis and (3335) - superktos86

Microsoft ® Excel ® 2010:

Data Analysis and

Business Modeling

Wayne L. Winston

PUBLISHED BY

Microsoft Press

A Division of Microsoft Corporation

One Microsoft Way

Redmond, Washington 98052-6399

means without the written permission of the publisher.

Library of Congress Control Number: 2010934987

ISBN: 978-0-7356-4336-9

Printed and bound in the United States of America.

Microsoft Press books are available through booksellers and distributors worldwide. For further infor mation

about international editions, contact your local Microsoft Corporation office or contact Microsoft Press

International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to

mspinput@microsoft.com.

Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/

Trademarks/EN-US.aspx ar e trademarks of the Microsoft group of companies. All other marks are property of

their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and

events depicted herein are ictitious. No association with any real company, organization, product, domain name,

e-mail address, logo, person, place, or event is intended or should be inferred.

This book expresses the author’s views and opinions. The information contained in this book is provided without

any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or

distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by

this book.

Acquisitions Editor: Rosemary Caperton

Developmental Editor: Devon Musgrave

Project Editor: Rosemary Caperton

Editorial and Production: John Pierce and Waypoint Press

Technical Reviewer: Mitch Tulloch; Technical Review services provided by Content Master,

a member of CM Group, Ltd.

Cover: Twist

Body Part No. X17-37446

Table of Contents

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Lx

1 What’s New in Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

2 Range Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

3 Lookup Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4 The INDEX Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

5 The MATCH Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

6 Text Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

7 Dates and Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

8 Evaluating Investments by Using Net Present Value

Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

9 Internal Rate of Return. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

10 More Excel Financial Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

11 Circular References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

12 IF Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

13 Time and Time Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

14 The Paste Special Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

What do you think of this book? We want to hear from you!

Microsoft is interested in hearing your feedback so we can continually improve our books and learning

resources for you. To participate in a brief online survey, please visit:

www.microsoft.com/learning/booksurvey/

iii

Table of Contents

15 Three-Dimensional Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

16 The Auditing Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

17 Sensitivity Analysis with Data Tables . . . . . . . . . . . . . . . . . . . . . . 127

18 The Goal Seek Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137

19 Using the Scenario Manager for Sensitivity Analysis. . . . . . . . . 143

20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and

COUNTBLANK Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

21 The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

22 The OFFSET Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

23 The INDIRECT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177

24 Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

25 Sorting in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

26 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

27 Spin Buttons, Scroll Bars, Option Buttons, Check Boxes,

Combo Boxes, and Group List Boxes. . . . . . . . . . . . . . . . . . . . . . 229

28 An Introduction to Optimization with Excel Solver. . . . . . . . . . 241

29 Using Solver to Determine the Optimal Product Mix. . . . . . . . 245

30 Using Solver to Schedule Your Workforce . . . . . . . . . . . . . . . . . 255

31 Using Solver to Solve Transportation or Distribution

Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

32 Using Solver for Capital Budgeting . . . . . . . . . . . . . . . . . . . . . . . 267

33 Using Solver for Financial Planning . . . . . . . . . . . . . . . . . . . . . . . 275

34 Using Solver to Rate Sports Teams. . . . . . . . . . . . . . . . . . . . . . . . 281

Microsoft Excel 2010_ Data Analysis and - Winston_ Wayne L_.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: