Automated Data Analysis Using Excel

相似文件 换一批

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

Automated Data Analysis Using Excel
Table of Contents
Chapter 1
Accessing Data in Excel: A Macro Writer’s Perspective............................................1
1.1 Introduction...............................................................................................................................1
1.2 The Workbook ..........................................................................................................................1
1.3 The Worksheet ..........................................................................................................................4
1.4 Ranges in Worksheets ..............................................................................................................9
1.5 Using Explicit Referencing ....................................................................................................17
1.6 Rows and Columns.................................................................................................................18
1.7 Searching Worksheets — Using Find ....................................................................................19
1.8 Copying, Clearing, and Deleting Data...................................................................................22
1.9 Sorting Data............................................................................................................................27
1.10 Deleting Rows and Columns..................................................................................................31
1.11 Summary.................................................................................................................................34
Chapter 2
Methods of Loading and Saving Data in Excel ........................................................35
2.1 Introduction ...........................................................................................................................35
2.2 Using the Standard Open File Dialog Box to Load a File ..................................................35
2.3 Using the Standard Save As Dialog Box to Save a File......................................................39
2.4 Automatically Opening Files and Templates........................................................................43
2.5 Importing Data to a Worksheet.............................................................................................44
2.6 Automatically Saving Files and Templates ..........................................................................47
2.7 Allowing the User to Browse for a Directory ......................................................................51
2.8 Setting the Starting Directory for a User to Browse From..................................................54
2.9 Using the Windows Registry to Save Settings .....................................................................59
2.10 Determining Subfolders of a Chosen Folder........................................................................66
2.11 Determining Files within a Chosen Folder...........................................................................68
2.12 Practical Strategies for Dealing with Large Amounts of Data ............................................71
2.13 Creating Database-“Friendly” Files ......................................................................................78
2.14 Obtaining Drive, Directory, and File Information................................................................78
2.15 Summary................................................................................................................................84
Chapter 3
Control and Manipulation of Worksheet Data...........................................................85
3.1 Introduction ...........................................................................................................................85
3.2 Scope and Use of Variables in Excel VBA ..........................................................................85
3.3 Operating in Excel’s Environment from VBA .....................................................................88
3.4 Utilizing Arrays to Store Data ..............................................................................................92
3.5 Passing Parameters by Value or by Reference?....................................................................94
3.6 Array Looping Structures......................................................................................................96
3.7 Using Object Variables..........................................................................................................97
3.8 An In-Depth Look at Worksheets .........................................................................................99
3.9 Extraction of Data Using Landmarks and Looping Structures..........................................104
3.10 Summary..............................................................................................................................110
Chapter 4
Utilizing Functions in Excel ....................................................................................113
4.1 Introduction .........................................................................................................................113
4.2 Creating and Utilizing VBA Functions in Code.................................................................113
4.3 Handling Errors in VBA Functions ....................................................................................114
4.4 Adding a Function to a Worksheet Cell Using VBA Code ...............................................116
4.5 Creating Additional Built-in Functions for Excel ..............................................................119
4.6 Dynamic Formatting of Worksheets Using Functions .......................................................124
4.7 Applying Dynamic Formatting Using VBA.......................................................................128
4.8 Using the Macro Recorder to Capture a Process ...............................................................133
4.9 Creating a Linear Regression Tool Using the VBA Analysis Toolpak..............................138
4.10 Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak......................147
Chapter 5
Data Mining in Excel ...............................................................................................157
5.1 Introduction..........................................................................................................................157
5.2 The Terrible Truth about Colors in VBA............................................................................158
5.3 Form Reuse in VBA Projects..............................................................................................168
5.4 The Refedit Control and its Associated Problems..............................................................169
5.5 Highlighting and Coloring Cell Fonts and Backgrounds ...................................................176
5.6 Creating a Highlight if Tool................................................................................................179
5.7 Creating a Color Font if Tool..............................................................................................185
5.8 Creating a Copy If/Move If Tool ........................................................................................186
5.9 Creating a Windowing Tool ................................................................................................193
5.10 Linear and Nonlinear Mapping...........................................................................................200
5.11 Automatically Loading and Extracting Data from Complex
Directory Structures.............................................................................................................213
Chapter 6
Creating Custom Report Worksheets .......................................................................221
6.1 Introduction...........................................................................................................................221
6.2 Use of Templates when Creating Custom Reports..............................................................221
6.3 Preparation of Dual-View Reports.......................................................................................223
6.4 Executing Calculations Upon Changing Views...................................................................227
6.5 Analysis within Report Worksheets .....................................................................................230
6.6 Basic Formatting Techniques ...............................................................................................235
6.7 Automatically Emailing Reports..........................................................................................244
6.8 Summary...............................................................................................................................246
Chapter 7
Introduction to Microsoft Access.............................................................................247
7.1 Introduction...........................................................................................................................247
7.2 Elements of a Relational Database ......................................................................................247
7.3 Connecting to an MS Access Database ...............................................................................251
7.4 Queries: How to Retrieve Information in Databases Using SQL .......................................255
7.5 Using the Microsoft ODBC Add-in For Microsoft
Excel (XLODBC.XLA)........................................................................................................259
7.6 Constructing a Database Query Tool ...................................................................................264
7.7 Using Data Access Objects — DAO ...................................................................................273
7.8 Elements in the DAO Architecture ......................................................................................276
7.9 Summary...............................................................................................................................278
Chapter 8
From Excel to Access and Back Again ...................................................................279
8.1 Introduction..........................................................................................................................279
8.2 Using Pointers in Dynamic Database Algorithms ..............................................................279
8.3 Concepts in Database Alteration and Management............................................................281
8.4 Creating New Tables in Access from Excel .......................................................................281
8.5 Adding and Removing Fields in Access Tables from Excel..............................................286
8.6 Adding Records to Specific Fields in Database Tables......................................................291
8.7 Deleting Records in Databases Using Bound Controls......................................................294
8.8 Compacting Databases Using VBA....................................................................................306
8.9 Returning the Results of a Remote Access Database Query to an Excel Worksheet........307
8.10 Summary..............................................................................................................................311
Chapter 9
Analyses Via External Applications.........................................................................313
9.1 Introduction..........................................................................................................................313
9.2 Setting Up a Matlab ActiveX Server from Excel...............................................................313
9.3 Matrix and Vector Building.................................................................................................315
9.4 Defining Matrices and Vectors in Matlab from Excel........................................................317
9.5 Using Matlab to Perform More Advanced Forms of Regression ......................................322
9.6 The Inner Workings of the Multiple Linear Regression Example .....................................330
9.7 Interfacing Excel and Origin to Perform more Complex Analyses ...................................336
9.8 Excel-to-Origin DDE Example ...........................................................................................344
9.9 Interfacing Excel and Origin using COM (Component Object Model) ............................352
9.10 Example: Creating a COM Tool to Perform
Curve Fitting Using Origin from Excel..............................................................................356
9.11 Opening and Plotting Excel Workbooks in Origin for Superior Graphics ........................368
9.12 Summary..............................................................................................................................372
Chapter 10
An Example ADA Application That Generates a Report......................................373
10.1 Introduction and Problem Definition ..................................................................................373
10.2 A Quick Word on Six Sigma ..............................................................................................377
10.3 Dealing with the Raw Data.................................................................................................378
10.4 Process Analysis ..................................................................................................................382
10.5 The Final Report..................................................................................................................393
10.6 Saving the Final Report.......................................................................................................393
10.7 Summary of the Final Application......................................................................................395
Appendix A:
Option Explicit .....................................................................................................397
Appendix B:
Excel Developer Tip.............................................................................................405
Appendix C:
Regression Analysis and Best Fit Lines (XE0124).............................................409
Appendix D:
Built-in Constants in Visual Basic for Applications (WC0993)...........................411
Appendix E:
xlodbc.exe Add-In ................................................................................................413
Appendix F:
XL: Scope of Variables in Visual Basic for Applications ...................................415
Appendix G:
How to Convert Hexadecimal Numbers to Long Integer ............................421
Appendix H:
SendKeys Statement ...........................................................................................429
Index
..............................................................................................................................................433

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Automated Analysis Analysi Analys alysis Excel

多谢分享，O(∩_∩)O谢谢

Thank you for your sharing

感谢分享！
教材介绍的是VB的神操作啊！！哈哈