Formulas.and.Functions-Microsoft.Excel.2010.MrExcel.Library.pdf

(30504 KB) Pobierz
C o n t e n t s
a t
a
G l a n c e
Part 1—Mastering Excel Ranges and Formulas
Chapter 1—Getting the Most Out of Ranges ...............................................5
Chapter 2—Using Range Names................................................................33
Chapter 3—Building Basic Formulas .........................................................51
Chapter 4—Creating Advanced Formulas ..................................................85
Chapter 5—Troubleshooting Formulas....................................................109
MrExcel
LIBRARY
Part 2—Harnessing the Power of Functions
Chapter 6—Using Functions ....................................................................127
Chapter 7—Working with Text Functions................................................137
Chapter 8—Working with Logical and Information Functions ................159
Chapter 9—Working with Lookup Functions...........................................185
Chapter 10—Working with Date and Time Functions .............................201
Chapter 11—Working with Math Functions ............................................229
Chapter 12—Working with Statistical Functions .....................................249
FORMULAS AND FUNCTIONS
MICROSOFT
®
EXCEL 2010
Part 3—Building Business Models
Chapter 13—Analyzing Data with Tables ................................................283
Chapter 14—Analyzing Data with PivotTables ........................................315
Chapter 15—Using Excel’s Business-Modeling Tools ...............................341
Chapter 16—Using Regression to Track Trends and Make Forecasts .......363
Chapter 17—Solving Complex Problems with Solver ..............................401
Part 4—Building Financial Formulas
Chapter 18—Building Loan Formulas ......................................................421
Chapter 19—Building Investment Formulas ...........................................439
Chapter 20—Building Discount Formulas ................................................453
Index ..................................................................................475
Paul McFedries
Que Publishing
800 E. 96th Street
Indianapolis, Indiana 46240
Formulas and Functions: Microsoft® Excel 2010
Copyright © 2010 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, elec-
tronic, mechanical, photocopying, recording, or otherwise, with-
out written permission from the publisher. No patent liability
is assumed with respect to the use of the information contained
herein. Although every precaution has been taken in the prepara-
tion of this book, the publisher and author assume no respon-
sibility 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-4306-X
International Standard Book Number-13: 978-0-7897-4306-0
Printed in the United States of America
First Printing: May 2010
10 09 08 07
4321
Trademarks
All terms mentioned in this book that are known to be trade-
marks or service 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 provided is on an “as is” basis. The author and the
publisher shall have neither liability nor responsibility to any per-
son 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
Associate Publisher
Greg Wiegand
Acquisitions Editor
Loretta Yates
Development Editor
Sondra Scott
Managing Editor
Patrick Kanouse
Project Editor
Mandie Frank
Copy Editor
Keith Cline
Indexer
Tim Wright
Technical Editor
P.K. Hari Hara Subramanian
Publishing Coordinator
Cindy Teeters
Designer
Ann Jones
Page Layout
Jake McFarland
http://avaxhome.ws/blogs/igor_lv
Dedication
To Karen and Gypsy
Contents
Introduction
. ...........................................................................................................................................................................................
1
What’s in the Book
. ............................................................................................................................................................................
2
This Book’s Special Features
. ........................................................................................................................................................
2
I
MASTERING EXCEL RANGES AND FORMULAS
1 Getting the Most Out of Ranges
. .............................................................................................................................
5
Advanced Range-Selection Techniques
. ................................................................................................................................
5
Mouse Range-Selection Tricks
. ...........................................................................................................................................
6
Keyboard Range-Selection Tricks
. ....................................................................................................................................
7
Working with 3D Ranges
. ......................................................................................................................................................
7
Selecting a Range Using Go To
. ..........................................................................................................................................
8
Using the Go To Special Dialog Box
. ................................................................................................................................
9
Data Entry in a Range
.....................................................................................................................................................................
13
Filling a Range
....................................................................................................................................................................................
14
Using the Fill Handle
.......................................................................................................................................................................
14
Using AutoFill to Create Text and Numeric Series
................................................................................................
14
Creating a Custom AutoFill List
........................................................................................................................................
16
Filling a Range
............................................................................................................................................................................
17
Creating a Series
................................................................................................................................................................................
17
Advanced Range Copying
............................................................................................................................................................
18
Copying Selected Cell Attributes
..............................................................................................................................................
19
Combining the Source and Destination Arithmetically
.....................................................................................
20
Transposing Rows and Columns
.....................................................................................................................................
21
Clearing a Range
................................................................................................................................................................................
22
Applying Conditional Formatting to a Range
..................................................................................................................
22
Creating Highlight Cells Rules
..........................................................................................................................................
22
Creating Top/Bottom Rules
................................................................................................................................................
24
Adding Data Bars
......................................................................................................................................................................
26
Adding Color Scales
.................................................................................................................................................................
28
Adding Icon Sets
.......................................................................................................................................................................
31
From Here
.....................................................................................................................................................................................
32
2 Using Range Names
. ...........................................................................................................................................................
33
Defining a Range Name
................................................................................................................................................................
34
Working with the Name Box
.............................................................................................................................................
34
Using the New Name Dialog Box
....................................................................................................................................
35
Changing the Scope to Define Sheet-Level Names
.............................................................................................
37
Using Worksheet Text to Define Names
.....................................................................................................................
37
Naming Constants
...................................................................................................................................................................
39

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin