Automated data analysis using Excel / / Brian Bissett

• Autore: Bissett Brian
• Pubblicazione: Boca Raton : , : Chapman & Hall/CRC, , 2021; ©2020
• Edizione: Second edition.
• Descrizione fisica: 1 online resource (610 pages) : illustrations
• Disciplina: 005.1/112; 005.1
• Soggetto topico: Decision making - Computer programs; Computer software - Development
• Note generali: Previous edition: 2007.
• Nota di bibliografia: Includes bibliographical references and index.
• Nota di contenuto: 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.
• Sommario/riassunto: This new edition covers some of the key topics relating to the latest version of MS Office through Excel 2019, including the creation of custom ribbons by injecting XML code into Excel Workbooks and how to link Excel VBA macros to customize ribbon objects. It now also provides examples in using ADO, DAO, and SQL queries to retrieve data from databases for analysis. Operations such as fully automated linear and non-linear curve fitting, linear and non-linear mapping, charting, plotting, sorting, and filtering of data have been updated to leverage the newest Excel VBA object models. The text provides examples on automated data analysis and the preparation of custom reports suitable for legal archiving and dissemination. Functionality Demonstrated in This Edition Includes: Find and extract information raw data files Format data in color (conditional formatting) Perform non-linear and linear regressions on data Create custom functions for specific applications Generate datasets for regressions and functions Create custom reports for regulatory agencies Leverage email to send generated reports Return data to Excel using ADO, DAO, and SQL queries Create database files for processed data Create tables, records, and fields in databases Add data to databases in fields or records Leverage external computational engines Call functions in MATLAB and Origin from Excel
• Titolo autorizzato: Automated data analysis using Excel
• ISBN: 1-00-305188-X; 1-000-08849-9; 1-003-05188-X; 1-000-08847-2
• Formato: Materiale a stampa
• Livello bibliografico: Monografia
• Lingua di pubblicazione: Inglese
• Record Nr.: 9910814215603321
• Lo trovi qui: Univ. Federico II