Automated Data Analysis Using Excel (Chapman & Hall/CRC Data Mining and Knowledge Discovery Series)

Cover
Half Title
Series Page
Title Page
Copyright Page
Dedication
Contents
Preface
About the Author
Acknowledgments
Introduction
	It Isn’t 2007 Anymore, Dorothy
	Purpose of This Text
	What Differentiates This Text from Its Competitors?
	Evolution of the Spreadsheet
	Should I Be Using Excel? What Other Options Exist?
	Scope of Coverage
	Projects Which Lead to This Text
	Before Beginning: Setting Up the Examples
	Odd Behavior in Excel to Watch Out For
	The Top Productivity Hindrances in Excel
	Final Thoughts
Chapter 1: Customizing Excel’s Ribbon Interface
	Introduction
	XML Code Structure for Creating Ribbons
	Simple Ribbon Example
	Creating an Add-In to Automatically Display Custom Ribbons When Excel Starts
	Working with Existing Ribbon Tabs, Groups, and Buttons
	Automating Code to Generate Large Complex Ribbons
	Customization of the Quick Access Toolbar
	Programming more Esoteric Ribbon Controls
	Helpful Resources for Creating Custom Ribbons
	Final Thoughts
Chapter 2: Accessing Data in Excel: A VBA Macro Writer’s Perspective
	Introduction
	The Workbook
	The Worksheet
	Ranges in Worksheets
	The Union Method for Ranges
	Using Explicit Referencing
	Rows and Columns
	Searching Worksheets – Using Find
	Finding the Next Instance of an Item
	Finding a Specific Instance of an Item
	Tokenizing an Item into an Array
	Copying, Clearing, and Deleting Data
	Sorting Data
	Sorting Data by Absolute Value
	Sorting a Range within a Worksheet
	Deleting Rows and Columns
	Deleting Hidden Rows and Columns
	Automatically Deleting Rows When a Condition is True
	Finding Duplicates
	Summary
Chapter 3: Methods of Loading and Saving Data in Excel
	Introduction
	Processing the New File Types Present in Excel 2007 and Beyond
	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
	Importing a Worksheet from Another Workbook – 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
	Writing Information to Excel’s Status Bar
	Writing Log Files
	Summary
Chapter 4: Control and Manipulation of Worksheet Data
	Introduction
	Scope and Use of Variables in Excel VBA
	Operating In Excel’s Environment from VBA
	Utilizing Arrays to Store Data
	Passing Paramaters 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
Chapter 5: Utilizing Functions in Excel
	Introduction
	Creating and Utilizing VBA Functions in Code
	Handling Errors in VBA Functions
	Adding a Function to a Worksheet Cell Using VBA Code
	Array Formulas and Overcoming the 256 Character Array Formula Limit
	Creating Additional Built in Functions for Excel
	Dynamic Formatting of Worksheets Using Functions
	Applying Dynamic Formatting using VBA
	Using the Macro Recorder to Capture Processes
	Using the Big Five Functions – Forecast, Trend, Growth, Linest, and Logest
	Creating a Linear Regression Tool Using the VBA Analysis Toolpak
	Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak
	Keeping VBA from Crashing During Intense Computations
	Correlation vs. Causation – Sure It Fits Well, But What Does That Mean?
	Summary
Chapter 6: Data Mining in Excel
	Introduction
	The Terrible Truth about Colors in VBA
	Form Reuse in VBA Projects
	The Refedit Control and Its Associated Problems
	Creating a Refedit Control from a Textbox
	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 a Windowing Tool
	Linear and Nonlinear Mapping
	Using Lookup Functions – Lookup, Vlookup, Hookup, Match, Index, etc
	Automatically Loading and Extracting Data from Complex Directory Structures
Chapter 7: 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
Chapter 8: Introduction to Microsoft Access
	Introduction
	Elements of a Relational Database
	Connecting to an MS Access Database
	Queries: How to Retrieve Information in Database Using SQL
	Constructing a Database Query Tool
	Using Data Access Objects (DAO) to Create a New Database File from Code
	Elements in DAO Architecture
	Summary
Chapter 9: From Excel to Access and Back Again
	Introduction
	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
	Returning the Results of a Remote Access Database Query to an Excel Worksheet
	Compacting Databases Using VBA
	Summary
Chapter 10: 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 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
Chapter 11: An Example ADA Application
	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
Index