Managing Data with Microsoft Excel

  
Managing Data with Microsoft® Excel
By Conrad Carlberg
 
Publisher: Que
Pub Date: May 10, 2004
ISBN: 0-7897-3100-2
Pages: 384
   


The Business Solutions series was created to provide professionals like you with books that are focused on a specific use or application of a software product. Managing Data with Excel is the ONLY book that focuses on efficient and effective data management, including designing the layout of data on the worksheet and knowing which functions to use to take advantage of that layout. The book features step-by-steo descriptions of how to accoomplish a task. Also included are Case Studies that present a problem and offer solutions which you can apply to situations you face. Armed with the information presented in this book, you will save time and increase your productivity. Covers Excel versions 97-2003.

   
  
• Table of Contents
• Index
Managing Data with Microsoft® Excel
By Conrad Carlberg
 
Publisher: Que
Pub Date: May 10, 2004
ISBN: 0-7897-3100-2
Pages: 384
   
Copyright
   About the Author
   Acknowledgments
   We Want to Hear from You!
   Introduction
      About Excel
      How to Use This Book
    Part I:  Pitfalls in Data Management
        Chapter 1.  Misusing Excel as a Database Management Tool
      Putting Data Management into Context
      CASE STUDY Overworking Functions
      Transposing Rows and Columns
      Using Labels Instead of Codes
      Handling Variable Numbers of Records per Category
      Changing Horses
    Part II:  Managing Data Inside Excel
        Chapter 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
        Chapter 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
    Part III:  Managing External Data from Inside Excel
        Chapter 4.  Importing Data: An Overview
      Getting External Data into the Workbook
      CASE STUDY
      Importing Data to Pivot Tables
      Looking Ahead
        Chapter 5.  Using Microsoft Query
      Understanding "Query"
      Querying Multiple Tables
      CASE STUDY
      Joining Parent and Child Records
      Using Database Queries
      Handling the Data Range
      Managing Boolean Fields and Check Boxes
      Looking Ahead
        Chapter 6.  Importing Data: Further Considerations
      Understanding Pivot Tables
      Preparing Data for Pivot Tables
      Using Criteria with Microsoft Query
      Querying Web Sites
      Looking Ahead
    Part IV:  Managing Databases from Inside Excel
        Chapter 7.  VBA Essentials Reviewed
      Using VBA to Manage Data
      Establishing Subroutines
      Understanding Dot Notation
      Using Loops
      CASE STUDY
      Using With Blocks
      Understanding the Macro Recorder's Code
      Using Object Variables
      Looking Ahead
        Chapter 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
        Chapter 9.  Managing Database Objects
      Creating Tables
      Defining Fields
      Creating Queries
      CASE STUDY
      CASE STUDY
      Looking Ahead
        Chapter 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
      CASE STUDY
      Declaring and Using Recordsets
      Understanding DAO Recordset Types
      Understanding ADO Recordset Types
      Looking Ahead
        Chapter 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
        Chapter 12.  Controlling a Database from Excel Using ADO and DAO
      Using DAO to Execute Action Queries
      CASE STUDY
      Using ADO to Execute Action Queries and Stored Procedures
      CASE STUDY
      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
flylib.com © Copyright 2008-2013. All Rights Reserved. If you may any questions please contact us: flylib@qtcs.net