... of Excel Objects. 10. 2.3 Entering Data into Cells ... 6.6 Exercises. 144. CHAPTER 7 Statistical Analysis with Excel .... A.3 Data Analysis Toolpack. 847. A.4 The ...
CH00_FM_4769
2/23/07
10:22 AM
Page i
Developing Spreadsheet-Based Decision Support Systems Using Excel and VBA for Excel
Michelle M.H. S¸eref Decision and Information Sciences Warrington College of Business University of Florida, Gainesville
Ravindra K. Ahuja Industrial and Systems Engineering University of Florida, Gainesville and Innovative Scheduling, Inc., Gainesville
Wayne L. Winston Operations and Decision Technologies Kelly School of Business Indiana University, Bloomington
Dynamic Ideas Belmont, Massachusetts
CH00_FM_4769
2/23/07
10:22 AM
Page v
Contents
PREFACE
PART I: CHAPTER 1 1.1 1.2 1.3 1.4 1.5 1.6
CHAPTER 2 2.1 2.2 2.3 2.4 2.5 2.6 2.7
CHAPTER 3 3.1 3.2 3.3 3.4 3.5
CHAPTER 4 4.1 4.2
xiii
EXCEL ESSENTIALS Introduction
1
Introduction to Decision Support Systems Defining a Decision Support System Decision Support Systems Applications Textbook Overview Summary Exercises
2 3 5 6 8 8
Excel Basics and Formatting
9
Introduction Defining the Set of Excel Objects Entering Data into Cells Understanding Excel Menus and Toolbars Formatting Summary Exercises
10 10 10 13 20 27 28
Referencing and Names
31
Introduction Referencing Cells Names for Cells, Ranges, and Worksheets Summary Exercises
32 32 40 48 48
Functions and Formulas
53
Introduction Formulas and Function Categories
54 54 v
CH00_FM_4769
vi
2/23/07
10:22 AM
Page vi
Contents
4.3 4.4 4.5 4.6 4.7 4.8 4.9 4.10 4.11
CHAPTER 5 5.1 5.2 5.3 5.4 5.5 5.6
CHAPTER 6 6.1 6.2 6.3 6.4 6.5 6.6
CHAPTER 7 7.1 7.2 7.3 7.4 7.5 7.6
CHAPTER 8 8.1 8.2 8.3 8.4 8.5 8.6
CHAPTER 9 9.1 9.2 9.3
Logical and Information Functions Text and Lookup & Reference Functions Date & Time Functions Mathematical and Trigonometry Functions Statistical and Financial Functions Conditional Formatting Formulas Auditing Summary Exercises
58 66 77 84 88 95 97 99 99
Charts
105
Introduction Creating Charts with Chart Wizard Working with Chart Options Creating a Dynamic Chart Summary Exercises
106 106 111 116 119 120
Pivot Tables
123
Introduction Pivot Tables Further Modifications Pivot Charts Summary Exercises
124 124 130 142 144 144
Statistical Analysis with Excel
147
Introduction Understanding Data Relationships in Data Distributions Summary Exercises
148 148 161 174 181 182
Using the Excel Solver to Solve Mathematical Programs
187
Introduction Formulating Mathematical Programs The Excel Solver Applications of the Solver Summary Exercises
Introduction Importing Data Exporting Data Creating Pivot Tables from External Data Using Excel as a Database Summary Exercises
246 246 255 256 258 278 279
VBA FOR EXCEL Introduction to the Visual Basic Environment
283
Introduction The Visual Basic Editor The Object Browser Summary Exercises
284 284 287 289 290
Recording Macros
291
Introduction Macros Customizing Toolbars and Menu Options Summary Exercises
292 292 306 310 311
More on Objects
315
Introduction More on Properties and Methods The With Construct Referencing and Names in VBA Formulas in VBA Summary Exercises
316 316 344 347 355 361 361
Variables
367
Introduction Variable Declarations and Data Types Variable Scope Variables in User Interface VBA Math Functions Applications Summary Exercises
368 368 372 373 376 381 392 393
CH00_FM_4769
viii
2/23/07
10:22 AM
Page viii
Contents
CHAPTER 15
Sub Procedures and Function Procedures
399
15.1 15.2 15.3 15.4 15.5 15.6 15.7
Introduction Sub Procedures Function Procedures Public and Private Procedures Applications Summary Exercises
400 400 402 405 406 409 410
CHAPTER 16
Programming Structures
413
Introduction If, Then Statements Select Case Loops Exit Statements and End Applications Summary Exercises
414 414 419 420 423 427 434 434
Arrays
439
Introduction When and Why to Use Arrays Defining Arrays Dynamic Arrays Sorting Arrays Applications Summary Exercises
440 440 440 442 444 445 450 451
User Interface
455
Introduction User Form Controls User Form Options Event Procedures Variable Scope Error Checking Importing and Exporting Forms Navigating Professional Appearance Applications Summary Exercises
Introduction Review of Chapter 8 Solver Commands in VBA Applications Summary Exercises
500 500 503 508 514 515
Simulation Revisited
519
Introduction Review of Chapter 9 Simulation with VBA Applications Summary Exercises
520 520 521 526 531 532
Working with Large Data Using VBA
537
Introduction Creating Pivot Tables with VBA Using External Data Exporting Data Applications Summary Exercises
538 538 544 557 558 565 566
CASE STUDIES The DSS Development Process
571
Defining the DSS Development Process Application Overview and Model Development Worksheets User Interface Procedures Re-solve Options Testing and Final Packaging Summary Exercises
572 572 574 580 587 589 593 594 594
GUI Design
595
GUI Design The Theory Behind Effective GUI Design Effective and Ineffective GUI Design Summary Exercises
