Категории

Excel 2010 Expert Skills

Сумата се прибавя директно в кошницата

7.20лв

Описание

Contents

Feedback 15

Downloading the sample files.. 15

Problem resolution 15

Typographical Conventions Used In This Book 16

Excel version and service pack 18

Sessions and lessons 18

Read the book from beginning to end.... 18

How this book avoids wasting your time 19

Why our classroom courses work so well 19

How this book mimics our classroom technique 19

Avoiding repetition 20

Use of American English 20

First page of a session 21

Every lesson is presented on two facing pages 22

Learning by participation 23

Session Objectives 25

Lesson 1-1: Check your program and operating system version 26

Windows 7 27

Windows Vista 27

Windows XP 27

Lesson 1-2: Apply a simple filter to a range 28

Lesson 1-3: Apply a top 10 and custom filter to a range 30

Lesson 1-4: Apply an advanced filter with multiple OR criteria 32

Lesson 1-5: Apply an advanced filter with complex criteria 34

Lesson 1-6: Apply an advanced filter with function-driven criteria 36

Lesson 1-7: Extract unique records using an advanced filter 38

Lesson 1-8: Convert a range into a table and add a total row 40

Lesson 1-9: Format a table using table styles and convert a table into a range 42

Lesson 1-10: Create a custom table style 44

Lesson 1-11: Sort a range or table by rows 46

Lesson 1-12: Sort a range by columns 48

Lesson 1-13: Sort a range or table by custom list 50

Lesson 1-14: Name a table and create an automatic structured table reference 52

Lesson 1-15: Create a manual structured table reference 54

Lesson 1-16: Use special items in structured table references 56

Lesson 1-17: Understand unqualified structured table references 58

Session 1: Exercise 61

Session 1: Exercise Answers 63

Session Objectives 65

Lesson 2-1: Split fixed width data using Text to Columns 66

Lesson 2-2: Split delimited data using Text to Columns 68

Fixed width data 68

Delimited data 68

Lesson 2-3: Automatically subtotal a range .70

Lesson 2-4: Create nested subtotals 72

Lesson 2-5: Consolidate data from multiple data ranges 74

Lesson 2-6: Use data consolidation to generate quick subtotals from tables 76

Lesson 2-7: Validate numerical data 78

Lesson 2-8: Create user-friendly messages for validation errors 80

Lesson 2-9: Create data entry Input Messages 82

Lesson 2-10: Add a formula-driven date validation and a text length validation 84

Lesson 2-11: Add a table-based dynamic list validation 86

Lesson 2-12: Use a formula-driven custom validation to enforce complex business rules 88

Lesson 2-13: Remove duplicate values from a table 90

Lesson 2-14: Use a custom validation to add a unique constraint to a column 92

Session 2: Exercise 95

Session 2: Exercise Answers 97

Session Objectives 99

Lesson 3-1: Understand precedence rules and use the Evaluate feature 100

Lesson 3-2: Use common functions with Formula AutoComplete 102

Lesson 3-3: Use the Insert Function dialog and the PMT function 104

Lesson 3-4: Use the PV and FV functions to value investments 106

Present Value 106

Future Value 106

Lesson 3-5: Use the IF logic function 108

Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals 110

Lesson 3-7: Understand date serial numbers 112

How Excel stores dates 112

The world began in 1900 112

In Excel, every time is a date, and every date is a time 112

Lesson 3-8: Understand common date functions 114

Lesson 3-9: Use the DATEDIF function 116

Lesson 3-10: Use date offsets to manage projects using the scheduling equation 118

Lesson 3-11: Use the DATE function to offset days, months and years 120

Lesson 3-12: Enter time values and perform basic time calculations 122

Serial number recap 122

Lesson 3-13: Perform time calculations that span midnight 124

Lesson 3-14: Understand common time functions and convert date serial numbers to decimal values.... 126

Lesson 3-15: Use the TIME function to offset hours, minutes and seconds 128

Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria 130

Lesson 3-17: Understand calculation options (manual and automatic) 132

Lesson 3-18: Concatenate strings using the concatenation operator (&) 134

About strings 134

The concatenation operator (&) 134

Lesson 3-19: Use the TEXT function to format numerical values as strings 136

Custom format strings recap 136

Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions 138

Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions 140

Lesson 3-22: Use a VLOOKUP function for an exact lookup 142

Lesson 3-23: Use an IFERROR function to suppress error messages 144

Lesson 3-24: Use a VLOOKUP function for an inexact lookup 146

Session 3: Exercise 149

Session 3: Exercise Answers 151

Session Objectives 153

Lesson 4-1: Automatically create single-cell range names 154

Lesson 4-2: Manually create single cell range names and named constants 156

Lesson 4-3: Use range names to make formulas more readable 158

Lesson 4-4: Automatically create range names in two dimensions 160

Lesson 4-5: Use intersection range names and the INDIRECT function 162

Lesson 4-6: Create dynamic formula-based range names using the OFFSET function 164

Lesson 4-7: Create table-based dynamic range names 166

Lesson 4-8: Create two linked drop-down lists using range names 168

Lesson 4-9: Understand the #NUM!, #DIV/0! and #NAME? Error Values 170

Lesson 4-10: Understand the #VALUE!, #REF! and #NULL! Error Values 172

Lesson 4-11: Understand background error checking and error checking rules 174

Lesson 4-12: Manually check a worksheet for errors 176

Lesson 4-13: Audit a formula by tracing precedents 178

Lesson 4-14: Audit a formula by tracing dependents 180

Lesson 4-15: Use the watch window to monitor cell values 182