Muutke küpsiste eelistusi

Automated Data Analysis Using Excel 2nd edition [Pehme köide]

(Pfizer Global Research and Development, Groton, Connecticut, USA)
  • Formaat: Paperback / softback, 592 pages, kõrgus x laius: 254x178 mm, kaal: 980 g, 28 Tables, black and white; 50 Illustrations, black and white
  • Sari: Chapman & Hall/CRC Data Mining and Knowledge Discovery Series
  • Ilmumisaeg: 19-Aug-2020
  • Kirjastus: Chapman & Hall/CRC
  • ISBN-10: 1482250136
  • ISBN-13: 9781482250138
Teised raamatud teemal:
Automated Data Analysis Using Excel 2nd editionSuurem pilt
  • Formaat: Paperback / softback, 592 pages, kõrgus x laius: 254x178 mm, kaal: 980 g, 28 Tables, black and white; 50 Illustrations, black and white
  • Sari: Chapman & Hall/CRC Data Mining and Knowledge Discovery Series
  • Ilmumisaeg: 19-Aug-2020
  • Kirjastus: Chapman & Hall/CRC
  • ISBN-10: 1482250136
  • ISBN-13: 9781482250138
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781482250138.html
Märksõnad:
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
Preface xi
About the Author xv
Acknowledgments xvii
Introduction 1(26)
It Isn't 2007 Anymore, Dorothy
1(2)
Purpose of This Text
3(2)
What Differentiates This Text from Its Competitors?
5(2)
Evolution of the Spreadsheet
7(1)
Should I Be Using Excel? What Other Options Exist?
8(1)
Scope of Coverage
9(3)
Projects Which Lead to This Text
12(3)
Before Beginning: Setting Up the Examples
15(7)
Odd Behavior in Excel to Watch Out For
22(1)
The Top Productivity Hindrances in Excel
23(2)
Final Thoughts
25(2)
Chapter 1 Customizing Excel's Ribbon Interface
27(52)
Introduction
27(1)
XML Code Structure for Creating Ribbons
28(2)
Simple Ribbon Example
30(10)
Creating an Add-In to Automatically Display Custom Ribbons When Excel Starts
40(3)
Working with Existing Ribbon Tabs, Groups, and Buttons
43(3)
Automating Code to Generate Large Complex Ribbons
46(7)
Customization of the Quick Access Toolbar
53(3)
Programming more Esoteric Ribbon Controls
56(19)
Helpful Resources for Creating Custom Ribbons
75(1)
Final Thoughts
76(3)
Chapter 2 Accessing Data in Excel: A VBA Macro Writer's Perspective
79(56)
Introduction
79(1)
The Workbook
79(3)
The Worksheet
82(5)
Ranges in Worksheets
87(10)
The Union Method for Ranges
97(1)
Using Explicit Referencing
98(1)
Rows and Columns
99(3)
Searching Worksheets - Using Find
102(5)
Finding the Next Instance of an Item
107(3)
Finding a Specific Instance of an Item
110(3)
Tokenizing an Item into an Array
113(1)
Copying, Clearing, and Deleting Data
114(5)
Sorting Data
119(4)
Sorting Data by Absolute Value
123(3)
Sorting a Range within a Worksheet
126(2)
Deleting Rows and Columns
128(2)
Deleting Hidden Rows and Columns
130(2)
Automatically Deleting Rows When a Condition is True
132(1)
Finding Duplicates
133(1)
Summary
134(1)
Chapter 3 Methods of Loading and Saving Data in Excel
135(52)
Introduction
135(1)
Processing the New File Types Present in Excel 2007 and Beyond
135(1)
Using the Standard Open File Dialog Box to Load a File
136(5)
Using the Standard Save As Dialog Box to Save a File
141(4)
Automatically Opening Files and Templates
145(1)
Importing Data to a Worksheet
146(3)
Importing a Worksheet from Another Workbook - Automatically Saving Files and Templates
149(3)
Allowing the User to Browse for a Directory
152(3)
Setting the Starting Directory for a User to Browse From
155(4)
Using the Windows Registry to Save Settings
159(7)
Determining Subfolders of a Chosen Folder
166(2)
Determining Files Within a Chosen Folder
168(3)
Practical Strategies for Dealing with Large Amounts of Data
171(6)
Creating Database "Friendly" Files
177(1)
Obtaining Drive, Directory, and File Information
178(5)
Writing Information to Excel's Status Bar
183(1)
Writing Log Files
183(2)
Summary
185(2)
Chapter 4 Control and Manipulation of Worksheet Data
187(26)
Introduction
187(1)
Scope and Use of Variables in Excel VBA
187(3)
Operating In Excel's Environment from VBA
190(3)
Utilizing Arrays to Store Data
193(3)
Passing Paramaters by Value or by Reference?
196(2)
Array Looping Structures
198(1)
Using Object Variables
199(2)
An In-Depth Look at Worksheets
201(4)
Extraction of Data Using Landmarks and Looping Structures
205(6)
Summary
211(2)
Chapter 5 Utilizing Functions in Excel
213(72)
Introduction
213(1)
Creating and Utilizing VBA Functions in Code
213(1)
Handling Errors in VBA Functions
214(2)
Adding a Function to a Worksheet Cell Using VBA Code
216(1)
Array Formulas and Overcoming the Character
216(3)
Array Formula Limit
219(3)
Creating Additional Built in Functions for Excel
222(9)
Dynamic Formatting of Worksheets Using Functions
231(5)
Applying Dynamic Formatting using VBA
236(7)
Using the Macro Recorder to Capture Processes
243(4)
Using the Big Five Functions - Forecast, Trend, Growth, Linest, and Logest
247(4)
Creating a Linear Regression Tool Using the VBA Analysis Toolpak
251(14)
Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak
265(14)
Keeping VBA from Crashing During Intense Computations
279(2)
Correlation vs. Causation - Sure It Fits Well, But What Does That Mean?
281(1)
Summary
282(3)
Chapter 6 Data Mining in Excel
285(76)
Introduction
285(2)
The Terrible Truth about Colors in VBA
287(10)
Form Reuse in VBA Projects
297(2)
The Refedit Control and Its Associated Problems
299(6)
Creating a Refedit Control from a Textbox
305(3)
Highlighting and Coloring Cell Fonts and Backgrounds
308(4)
Creating a Highlight If Tool
312(6)
Creating a Color Font If Tool
318(2)
Creating a Copy If/Move If Tool
320(5)
Creating a Windowing Tool
325(7)
Linear and Nonlinear Mapping
332(12)
Using Lookup Functions - Lookup, Vlookup, Hookup, Match, Index, etc
344(8)
Automatically Loading and Extracting Data from Complex Directory Structures
352(9)
Chapter 7 Creati ng Custom Report Worksheets
361(32)
Introduction
361(1)
Use of Templates When Creating Custom Reports
361(1)
Preparation of Dual View Reports
362(7)
Executing Calculations Upon Changing Views
369(3)
Analysis Within Report Worksheets
372(6)
Basic Formatting Techniques
378(8)
Automatically Emailing Reports
386(5)
Summary
391(2)
Chapter 8 Introduction to Microsoft Access
393(34)
Introduction
393(1)
Elements of a Relational Database
394(4)
Connecting to an MS Access Database
398(4)
Queries: How to Retrieve Information in Database Using SQL
402(3)
Constructing a Database Query Tool
405(15)
Using Data Access Objects (DAO) to Create a New Database File from Code
420(4)
Elements in DAO Architecture
424(1)
Summary
425(2)
Chapter 9 From Excel to Access and Back Again
427(70)
Introduction
427(3)
Using Pointers in Dynamic Database Algorithms
430(2)
Concepts in Database Alteration and Management
432(1)
Creating New Tables in Access from Excel
433(12)
Adding and Removing Fields in Access Tables from Excel
445(13)
Adding Records to Specific Fields in Database Tables
458(7)
Deleting Records in Databases Using Bound Controls
465(21)
Returning the Results of a Remote Access Database Query to an Excel Worksheet
486(7)
Compacting Databases Using VBA
493(2)
Summary
495(2)
Chapter 10 Analyses Via External Applications
497(60)
Introduction
497(1)
Setting Up a MATLAB ActiveX Server from Excel
497(2)
Matrix and Vector Building
499(2)
Defining Matrices and Vectors in MATLAB from Excel
501(5)
Using MATLAB to Perform More Advanced Forms of Regression
506(9)
The Inner Workings of the Multiple Linear Regression Example
515(5)
Interfacing Excel and Origin to Perform More Complex Analyses
520(8)
Excel to Origin DDE Example
528(9)
Interfacing Excel and Origin Using COM (Component Object Model)
537(4)
Example: Creating a COM Tool to Perform Curve Fitting Using Origin from Excel
541(9)
Opening and Plotting Excel Workbooks in Origin for Superior Graphics
550(6)
Summary
556(1)
Chapter 11 An Example ADA Application
557(24)
Introduction/Problem Definition
557(4)
A Quick Word on Six Sigma
561(1)
Dealing with the Raw Data
562(3)
Process Analysis
565(11)
The Final Report
576(1)
Saving the Final Report
576(2)
Summary of the Final Application
578(3)
Index 581
Brian Bissett holds graduate degrees from Rensselaer Polytechnic Institute in Troy, NY in Electrical Engineering (MSEE) and Business Administration (MBA).

He is the author of three technical books: Practical Pharmaceutical Laboratory Automation, and Automated Data Analysis with Excel, as well as numerous technical articles, and co-author of several book chapters.

He began his career as a co-operative student at the Naval Undersea Warfare Center (NUWC) in New London, CT where he worked in the Experimental Test and Measurements Laboratory for Edward G. Marsh, the Hybrid Microelectronics Laboratory for Thomas A. Freehill, and the Digital Design Laboratory for Robert J. White. He worked on projects ranging from digital design of communication cards for the New Sonar Intercept System (NSIS) and analog preamplifier testing and fabrication for towed arrays.

For 16 years he worked at Pfizer Global Research and Development in Groton, CT in the Physical Measurements Laboratory for Dr. Christopher Lipinski (author of the Rule of 5), where he designed an automated kinetic solubility assay for which Pfizer applied for a patent on. After Chris retirement, he reported to Dr. Franco Lombardo in the Molecular Properties Group, automating the analysis of drug discovery parameters across 5 worldwide pharmaceutical drug discovery sites. He is a frequently invited presenter at Bio-IT World in Boston, MA for the last 15 years.

He returned to the federal government in 2010, at the Social Security Administration (SSA) in Woodlawn, MD. In the Office of Investment Management (OIM) in the Office of the Chief Information Officer (OCIO), he automated the data collection and analysis for SSAs Strategic IT Asset Reviews for Associate CIO Lester Diamond. He is currently a senior advisor in the Office of Systems in the Office of Hardware Engineering.