Managing Data With Microsoft Excel (Paperback)

Conrad Carlberg

  • 出版商: QUE
  • 出版日期: 2004-05-10
  • 售價: $1,670
  • 貴賓價: 9.5$1,587
  • 語言: 英文
  • 頁數: 384
  • 裝訂: Paperback
  • ISBN: 0789731002
  • ISBN-13: 9780789731005
  • 相關分類: Excel
  • 已過版

買這商品的人也買了...

相關主題

商品描述

Book Description

Even experienced Excel users have never learned how to manage data efficiently, and there are few books that provide this information for people who use Excel to do their jobs. Within Excel, efficient management includes designing the layout of data on the worksheet and knowing which functions to use to take advantage of that layout.

When users need data that's maintained elsewhere (e.g., a database with G/L information or HR management software) they frequently resort to copying from the source and pasting to an Excel worksheet. There are more effective ways to move data. This book provides advice on using Excel's data management to get the results readers want. This is the only book that focuses on managing data in Excel. Covers Excel 97 - 2003.

The Business Solution series was created to provide professionals with books that are focused on a specific use or application of a software product. They don't want general software information, but need specific features and functions related to their particular productivity use. This series is for those who need to go farther with the software to specifically solve complex applications and uses. They want a book that addresses their immediate needs, and is likely to answer any additional questions they might have in the future

Table of Contents

Introduction.

About Excel. How to Use This Book.

I. PITFALLS IN DATA MANAGEMENT.

1. Misusing Excel as a Database Management Tool.

Putting Data Management into Context. Overworking Functions. Transposing Rows and Columns. Using Labels Instead of Codes. Handling Variable Numbers of Records per Category. Changing Horses.

II. MANAGING DATA INSIDE EXCEL.

2. Excel's Data Management Features.

Using the Worksheet Functions: An Overview. Locating Data with OFFSET. Finding Data with INDEX. Finding Data with MATCH. Using MATCH with Other Functions. Using VLOOKUP. Rearranging Data with TRANSPOSE. Getting Data from a Pivot Table: GETPIVOTDATA. Looking Ahead.

3. Excel's Lists, Names, and Filters.

Creating Lists. Sorting Lists. Working with Names. Filtering Data with the AutoFilter. Using the Advanced Filter. Looking Ahead.

III. MANAGING EXTERNAL DATA FROM INSIDE EXCEL.

4. Importing Data: An Overview.

Getting External Data into the Workbook. Importing Data to Pivot Tables. Looking Ahead.

5. Using Microsoft Query.

Understanding "Query". Querying Multiple Tables. Joining Parent and Child Records. Using Database Queries. Handling the Data Range. Managing Boolean Fields and Check Boxes. Looking Ahead.

6. Importing Data: Further Considerations.

Understanding Pivot Tables. Preparing Data for Pivot Tables. Using Criteria with Microsoft Query. Querying Web Sites. Looking Ahead.

IV. MANAGING DATABASES FROM INSIDE EXCEL.

7. VBA Essentials Reviewed.

Using VBA to Manage Data. Establishing Subroutines. Understanding Dot Notation. Using Loops. Using With Blocks. Understanding the Macro Recorder's Code. Using Object Variables. Looking Ahead.

8. Opening Databases.

Connecting to a Database. Connecting Using ADO. Specifying the Data Source. Opening the Connection. Opening a Database Using DAO. Dealing with Secured Jet Databases. Opening a Secured Access Database. Looking Ahead.

9. Managing Database Objects.

Creating Tables. Defining Fields. Creating Queries. Looking Ahead.

10. Defining Fields and Records with ActiveX Data Objects and Data Access Objects.

Creating Databases from Excel. Creating a New Database Using DAO. Creating a Table and Fields Using ADO. Declaring and Using Recordsets. Understanding DAO Recordset Types. Understanding ADO Recordset Types. Looking Ahead.

11. Getting Data from Access and into Excel with ADO and DAO.

Using CopyFromRecordset. Building an Application. Bringing Data Back from a Parameterized Query. Returning Data from a Database to a User Form. Looking Ahead.

12. Controlling a Database from Excel Using ADO and DAO.

Using DAO to Execute Action Queries. Using ADO to Execute Action Queries and Stored Procedures. Using DAO Recordsets to Move Data from Excel to a Jet Database. Using ADO to Move Data from Excel to a Database. Looking Ahead.

Index