Automated Data Analysis Using Excel

Overview

Because the analysis of copious amounts of data and the preparation of custom reports often take away time from true research, the automation of these processes is paramount to ensure productivity. Exploring the core areas of automation, report generation, data acquisition, and data analysis, Automated Data Analysis Using Excel illustrates how to minimize user intervention, automate parameter setup, obtain consistency in both analysis and reporting, and save time through ...

See more details below
Other sellers (Paperback)
  • All (6) from $66.05   
  • New (5) from $66.05   
  • Used (1) from $69.29   
Sending request ...

Overview

Because the analysis of copious amounts of data and the preparation of custom reports often take away time from true research, the automation of these processes is paramount to ensure productivity. Exploring the core areas of automation, report generation, data acquisition, and data analysis, Automated Data Analysis Using Excel illustrates how to minimize user intervention, automate parameter setup, obtain consistency in both analysis and reporting, and save time through automation.

Focusing on the built-in Visual Basic® for Applications (VBA) scripting language of Excel®, the book shows step-by-step how to construct useful automated data analysis applications for both industrial and academic settings. It begins by discussing fundamental elements, the methods for importing and accessing data, and the creation of reports. The author then describes how to use Excel to obtain data from non-native sources, such as databases and third-party calculation tools. After providing the means to access any required information, the book explains how to automate manipulations and calculations on the acquired data sources. Collecting all of the concepts previously discussed in the book, the final chapter demonstrates from beginning to end how to create a cohesive, robust application.

With an understanding of this book, readers should be able to construct applications that can import data from a variety of sources, apply algorithms to data that has been imported, and create meaningful reports based on the results.

Read More Show Less

Editorial Reviews

From the Publisher
… this book shows the reader, in a step-by-step fashion, how to construct a useful automated data analysis application that is useful in both industrial and academic types of sets. Using this book, the reader should be able to construct applications that can import data from various sources, apply algorithms to data that have been imported, and create meaningful reports based on the results. The book could be used as a reference by researchers, engineers, and graduate students who use Excel to handle their data sets.
Technometrics, February 2010, Vol. 52, No. 1

Automated Data Analysis Using Excel explains how to conduct and automate a wide range of data and analysis tasks using VBA. It includes a CD-ROM with data examples and code that is very useful, especially to the new programmer … Overall, I consider this a very useful reference for the ambitious programmer wishing to fully harness the power of Excel.
—Madhumita (Bonnie) Ghosh-Dastidar, The RAND Corporation, The American Statistician, May 2009, Vol. 63, No. 2

Read More Show Less

Product Details

Table of Contents

INTRODUCTION
Purpose of This Text
Evolution of the Spreadsheet
Should I Be Using Excel? What Other Options Exist?
Scope of Coverage
Projects Which Lead to This Text
Before Beginning
Odd Behavior in Excel to Watch Out For
The Top Productivity Hindrances in Excel
Final Thoughts

ACCESSING DATA IN EXCEL: A MACRO WRITERS PERSPECTIVE
Introduction
The Workbook
The Worksheet
Ranges in Worksheets
Using Explicit Referencing
Rows and Columns
Searching Worksheets-Using Find
Copying, Clearing, and Deleting Data
Sorting Data
Deleting Rows and Columns
Summary

METHODS OF LOADING/SAVING DATA IN EXCEL
Introduction
Using the Standard Open File Dialog Box to Load a File
Using the Standard Save As Dialog Box to Save a File
Automatically Opening Files and Templates
Importing Data to a Worksheet
Automatically Saving Files and Templates
Allowing the User to Browse for a Directory
Setting the Starting Directory for a User to Browse From
Using the Windows Registry to Save Settings
Determining Subfolders of a Chosen Folder
Determining Files within a Chosen Folder
Practical Strategies for Dealing with Large Amounts of Data
Creating Database "Friendly" Files
Obtaining Drive, Directory, and File Information
Summary

CONTROL AND MANIPULATION OF WORKSHEET DATA
Introduction
Scope and Use of Variables in VBA
Operating in Excel's Environment from VBA
Utilizing Arrays to Store Data
Passing Parameters By Value or By Reference
Array Looping Structures
Using Object Variables
An In-Depth Look at Worksheets
Extraction of Data Using Landmarks and Looping Structures
Summary

THE UTILIZATION OF FUNCTIONS FROM WITHIN VBA
Introduction
Creating and Utilizing a VBA Function in Code
Handling Errors in VBA Functions
Adding a Function to a Worksheet Cell Using VBA Code
Creating Additional Built in Functions for Excel
Dynamic Formatting of Worksheets Using Functions
Applying Dynamic Formatting Using VBA
Using the Macro Recorder to Capture a Process
Creating a Linear Regression Tool Using the VBA Analysis Toolpak
Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak
Summary

DATA MINING IN EXCEL
Introduction
The Terrible Truth about Colors in VBA
Form Reuse in VBA Projects
The RefEdit Control and Its Associated Problems
Highlighting and Coloring Cell Fonts and Backgrounds
Creating a Highlight If Tool
Creating a Color Font If Tool
Creating a Copy If / Move If Tool
Creating an Extract If Tool
Creating a Windowing Tool
Linear and Nonlinear Mapping
Automatically Loading and Extracting Data from Complex Directory Structures
Summary

CREATING CUSTOM REPORT WORKSHEETS
Introduction
Use of Templates when Creating Custom Reports
Preparation of Dual View Reports
Executing Calculations upon Changing Views
Analysis within Report Worksheets
Basic Formatting Techniques
Automatically Emailing Reports
Summary

INTRODUCTION TO MICROSOFT ACCESS
Introduction
Elements of a Relational Database
Connecting to an MS Access Database
Queries: How to Retrieve Information in Databases using SQL
Using the Microsoft ODBC Add-In for Microsoft Excel (XLODBC.XLA)
Constructing a Database Query Tool
Using Data Access Objects (DAO)
Elements in the DAO Architecture
Summary

FROM EXCEL TO ACCESS AND BACK AGAIN
Introduction-DAO vs. SQL
Using Pointers in Dynamic Database Algorithms
Concepts in Database Alteration and Management
Creating New Tables in Access from Excel
Adding and Removing Fields in Access Tables from Excel
Adding Records to Specific Fields in Database Tables
Deleting Records in Databases Using Bound Controls
Compacting Databases Using VBA
Returning the Results of a Remote Access Database Query to an Excel Worksheet
Summary

ANALYSES VIA EXTERNAL APPLICATIONS
Introduction
Setting Up a Matlab ActiveX Server from Excel
Matrix and Vector Building
Defining Matrices and Vectors in Matlab from Excel
Using Matlab to Perform More Advanced Forms of Regression
The Inner Workings of the Multiple Linear Regression Example
Interfacing Excel and Origin to Perform More Complex Analyses
Excel to Origin Dynamic Data Exchange (DDE) Example
Interfacing Excel and Origin Using COM (Component Object Model)
Example: Creating a COM Tool to Perform Curve Fitting Using Origin from Excel
Opening and Plotting Excel Workbooks in Origin for Superior Graphics
Summary

AN EXAMPLE ADA APPLICATION WHICH GENERATES A REPORT
Introduction/Problem Definition
A Quick Word on Six Sigma
Dealing with the Raw Data
Process Analysis
The Final Report
Saving the Final Report
Summary of the Final Application

APPENDIX A: Advanced Browse for Directory Code Listing
APPENDIX B: The Menumakr Shareware Application
APPENDIX C: Regression Analysis and Best-Fit Lines
APPENDIX D: Built-In Constants for Microsoft Visual Basic for Applications
APPENDIX E: Excel VBA ODBC Functions (XLODBC.XLA)
APPENDIX F: Scope of Variables in Visual Basic for Applications
APPENDIX G: LONG Integer to a Hexadecimal Constant
APPENDIX H: Use of the SendKeys Statement in Excel VBA

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)