Formulas and Functions with Microsoft Office Excel 2007 - QUE 2007.pdf

(19065 KB) Pobierz
80975734 UNPDF
www.sharexxx.net - free books & magazines
80975734.004.png
Contents at a Glance
usiness solutions
I Mastering Excel Ranges and Formulas
1 Getting the Most Out of Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2 Using Range Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3 Building Basic Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
4 Creating Advanced Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
5 Troubleshooting Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
II Harnessing the Power of Functions
6 Understanding Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
7 Working with Text Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
8 Working with Logical and Information Functions . . . . . . . . . . . . . . . . . . 167
9 Working with Lookup Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
10 Working with Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
11 Working with Math Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
12 Working with Statistical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Formulas and
Functions with
Microsoft ® Office
Excel 2007
III Building Business Models
13 Analyzing Data with Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
14 Business Modeling with PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
15 Using Excel’s Business-Modeling Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
16 Using Regression to Track Trends and Make Forecasts . . . . . . . . . . . . . . 385
17 Solving Complex Problems with Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
IV Building Financial Formulas
18 Building Loan Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
19 Building Investment Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
20 Building Discount Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
Paul McFedries
800 E. 96th Street
Indianapolis, Indiana 46240
?
80975734.005.png 80975734.006.png 80975734.007.png
2007 by Pearson Education, Inc
All rights reserved. No part of this book shall be reproduced, stored in a
retrieval system, or transmitted by any means, electronic, mechanical, pho-
tocopying, recording, or otherwise, without written permission from the
publisher. No patent liability is assumed with respect to the use of the infor-
mation contained herein. Although every precaution has been taken in the
preparation of this book, the publisher and author assume no responsibility
for errors or omissions. Nor is any liability assumed for damages resulting
from the use of the information contained herein.
International Standard Book Number-10: 0-7897-3668-3
International Standard Book Number-13: 978-0-7897-3668-0
Printed in the United States of America
First Printing: March 2007
0987
Associate Publisher
Greg Wiegand
Acquisitions Editor
Loretta Yates
Development Editor
Kevin Howard
Managing Editor
Patrick Kanouse
Project Editor
Mandie Frank
Copy Editor
Kelli Brooks
Indexer
Tim Wright
Proofreader
Kathy Bidwell
Technical Editor
Greg Perry
Publishing Coordinator
Cindy Teeters
Designer
Ann Jones
Page Layout
Gina Rexrode
4321
Trademarks
All terms mentioned in this book that are known to be trademarks or ser-
vice marks have been appropriately capitalized. Que Publishing cannot
attest to the accuracy of this information. Use of a term in this book
should not be regarded as affecting the validity of any trademark or service
mark.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate
as possible, but no warranty or fitness is implied. The information pro-
vided is on an “as is” basis. The author and the publisher shall have nei-
ther liability nor responsibility to any person or entity with respect to any
loss or damages arising from the information contained in this book.
Bulk Sales
Que Publishing offers excellent discounts on this book when ordered in
quantity for bulk purchases or special sales. For more information, please
contact
U.S. Corporate and Government Sales
1-800-382-3419
corpsales@pearsontechgroup.com
For sales outside of the United States, please contact
International Sales
international@pearsoned.com
Library of Congress Cataloging-in-Publication Data
McFedries, Paul.
Formulas and functions with Microsoft Office Excel 2007 / Paul
McFedries.
p. cm.
Includes index.
ISBN-10: 0-7897-3668-3
ISBN-13: 978-0-7897-3668-0
1. Microsoft Excel (Computer file) 2. Business--Computer programs. 3.
Electronic spreadsheets. I. Title.
HF5548.4.M523M3756 2007
005.54--dc22
2007003274
Formulas and Functions with Microsoft® Office Excel 2007
Copyright
©
80975734.001.png
Formulas and Functions with Microsoft Office Excel 2007
iii
80975734.002.png
iv
Contents
Mouse Range-Selection Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Keyboard Range-Selection Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Working with 3D Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Selecting a Range Using Go To . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Using the Go To Special Dialog Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Using AutoFill to Create Text and Numeric Series . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Creating a Custom AutoFill List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Filling a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Copying Selected Cell Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Combining the Source and Destination Arithmetically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Transposing Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Creating Highlight Cells Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Creating Top/Bottom Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Adding Data Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Adding Color Scales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Adding Icon Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Working with the Name Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Using the New Name Dialog Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Changing the Scope to Define Sheet-Level Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Using Worksheet Text to Define Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Naming Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
80975734.003.png
Zgłoś jeśli naruszono regulamin