Automated Data Analysis Using Excel

Automated Data Analysis Using Excel

  • Electronic book text
By (author) 

List price: US$79.95

Currently unavailable

We can notify you when this item is back in stock

Add to wishlist

AbeBooks may have this title (opens in new window).

Try AbeBooks

Description

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 (R) for Applications (VBA) scripting language of Excel (R), 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.show more

Product details

  • Electronic book text | 480 pages
  • Taylor & Francis Ltd
  • Chapman & Hall/CRC
  • London, United Kingdom
  • 255 Illustrations, black and white
  • 1584888865
  • 9781584888864

Review quote

... 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. 2show more

Table of contents

INTRODUCTIONPurpose of This TextEvolution of the Spreadsheet Should I Be Using Excel? What Other Options Exist?Scope of CoverageProjects Which Lead to This TextBefore BeginningOdd Behavior in Excel to Watch Out ForThe Top Productivity Hindrances in ExcelFinal ThoughtsACCESSING DATA IN EXCEL: A MACRO WRITERS PERSPECTIVE IntroductionThe WorkbookThe WorksheetRanges in WorksheetsUsing Explicit ReferencingRows and ColumnsSearching Worksheets-Using FindCopying, Clearing, and Deleting DataSorting DataDeleting Rows and ColumnsSummaryMETHODS OF LOADING/SAVING DATA IN EXCELIntroductionUsing the Standard Open File Dialog Box to Load a FileUsing the Standard Save As Dialog Box to Save a FileAutomatically Opening Files and TemplatesImporting Data to a WorksheetAutomatically Saving Files and TemplatesAllowing the User to Browse for a DirectorySetting the Starting Directory for a User to Browse FromUsing the Windows Registry to Save SettingsDetermining Subfolders of a Chosen FolderDetermining Files within a Chosen FolderPractical Strategies for Dealing with Large Amounts of DataCreating Database "Friendly" FilesObtaining Drive, Directory, and File InformationSummaryCONTROL AND MANIPULATION OF WORKSHEET DATAIntroductionScope and Use of Variables in VBAOperating in Excel's Environment from VBAUtilizing Arrays to Store DataPassing Parameters By Value or By ReferenceArray Looping StructuresUsing Object VariablesAn In-Depth Look at WorksheetsExtraction of Data Using Landmarks and Looping StructuresSummaryTHE UTILIZATION OF FUNCTIONS FROM WITHIN VBAIntroductionCreating and Utilizing a VBA Function in CodeHandling Errors in VBA FunctionsAdding a Function to a Worksheet Cell Using VBA CodeCreating Additional Built in Functions for ExcelDynamic Formatting of Worksheets Using FunctionsApplying Dynamic Formatting Using VBAUsing the Macro Recorder to Capture a ProcessCreating a Linear Regression Tool Using the VBA Analysis ToolpakCreating a Polynomial Regression Tool Using the VBA Analysis ToolpakSummaryDATA MINING IN EXCELIntroductionThe Terrible Truth about Colors in VBAForm Reuse in VBA ProjectsThe RefEdit Control and Its Associated ProblemsHighlighting and Coloring Cell Fonts and BackgroundsCreating a Highlight If ToolCreating a Color Font If ToolCreating a Copy If / Move If ToolCreating an Extract If ToolCreating a Windowing ToolLinear and Nonlinear MappingAutomatically Loading and Extracting Data from Complex Directory StructuresSummaryCREATING CUSTOM REPORT WORKSHEETSIntroductionUse of Templates when Creating Custom ReportsPreparation of Dual View ReportsExecuting Calculations upon Changing ViewsAnalysis within Report WorksheetsBasic Formatting TechniquesAutomatically Emailing ReportsSummaryINTRODUCTION TO MICROSOFT ACCESSIntroductionElements of a Relational DatabaseConnecting to an MS Access DatabaseQueries: How to Retrieve Information in Databases using SQLUsing the Microsoft ODBC Add-In for Microsoft Excel (XLODBC.XLA)Constructing a Database Query ToolUsing Data Access Objects (DAO)Elements in the DAO ArchitectureSummaryFROM EXCEL TO ACCESS AND BACK AGAINIntroduction-DAO vs. SQLUsing Pointers in Dynamic Database AlgorithmsConcepts in Database Alteration and ManagementCreating New Tables in Access from ExcelAdding and Removing Fields in Access Tables from ExcelAdding Records to Specific Fields in Database TablesDeleting Records in Databases Using Bound ControlsCompacting Databases Using VBAReturning the Results of a Remote Access Database Query to an Excel WorksheetSummaryANALYSES VIA EXTERNAL APPLICATIONSIntroductionSetting Up a Matlab ActiveX Server from ExcelMatrix and Vector BuildingDefining Matrices and Vectors in Matlab from ExcelUsing Matlab to Perform More Advanced Forms of RegressionThe Inner Workings of the Multiple Linear Regression Example Interfacing Excel and Origin to Perform More Complex AnalysesExcel to Origin Dynamic Data Exchange (DDE) ExampleInterfacing Excel and Origin Using COM (Component Object Model)Example: Creating a COM Tool to Perform Curve Fitting Using Origin from ExcelOpening and Plotting Excel Workbooks in Origin for Superior GraphicsSummaryAN EXAMPLE ADA APPLICATION WHICH GENERATES A REPORTIntroduction/Problem DefinitionA Quick Word on Six SigmaDealing with the Raw DataProcess AnalysisThe Final ReportSaving the Final ReportSummary of the Final Application APPENDIX A: Advanced Browse for Directory Code ListingAPPENDIX B: The Menumakr Shareware ApplicationAPPENDIX C: Regression Analysis and Best-Fit LinesAPPENDIX D: Built-In Constants for Microsoft Visual Basic for ApplicationsAPPENDIX E: Excel VBA ODBC Functions (XLODBC.XLA)APPENDIX F: Scope of Variables in Visual Basic for ApplicationsAPPENDIX G: LONG Integer to a Hexadecimal ConstantAPPENDIX H: Use of the SendKeys Statement in Excel VBAshow more