Goldmeier Jordan - Advanced Excel Essentials.pdf

(14740 KB) Pobierz
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
About the Author �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½xiii
About the Technical Reviewer �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
xv
Acknowledgments �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
xvii
Part I: Core Advanced Excel Concepts �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
1
Chapter 1: Introduction to Advanced Excel Essentials �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½3
Chapter 2: Visual Basic for Applications for Excel, a Refresher �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½11
Chapter 3: Introducing Formula Concepts �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½31
Chapter 4: Advanced Formula Concepts �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½49
Chapter 5: Working with Form Controls �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½67
Part II: A Real World Example �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
93
Chapter 6: Getting Input from Users �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½95
Chapter 7: Storage Patterns for User Input �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½115
Chapter 8: Building for Sensitivity Analysis �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½137
Chapter 9: Perfecting the Presentation �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½165
Index �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½191
v
Part I
Core Advanced Excel Concepts
In this part, I’ll review the core concepts that make up the essentials of advanced Excel.
Chapter 1 explains what is meant by advanced Excel development, and how this book differs from many
others. For instance, several books place significant emphasis on Visual Basic for Applications code, believing
macros to be the most important feature of Excel development. This chapter will challenge that notion and
present advanced concepts as a product of many different Excel features, including code. Additionally,
I discuss the most important required skill—creativity.
Chapter 2 provides a brief Visual Basic for Applications refresher. I’ll discuss how best to set up the
coding environment to make it conducive to headache-free coding. I’ll also challenge conventional coding
conventions and propose alternatives that will prove more effective.
Chapter 3 introduces the formula concepts that will be used in this book. The chapter starts with tips
that will make your experience developing advanced formulas run more smoothly. I’ll then show you how to
perform advanced calculations by simply using range operators. You’ll develop advanced alternatives to the
IF
function that will prove more powerful in practice and more readable later on. In addition, you’ll
investigate the full extent of Excel’s Boolean logic features.
Chapter 4 continues the discussion of formulas by demonstrating how they can be used with advanced
applications. I take you through several examples applying these formula concepts and demonstrate how they
can be understood with a little bit of algebra. The chapter concludes by introducing the notion of reusable
components, which are spreadsheet mechanics that can be easily reused for other projects.
Chapter 5 shows how advanced capabilities can be built into spreadsheets by using the humble form
control. In this chapter, I argue against using ActiveX and UserForms. Instead, you’ll rely on the flexibility of
form controls combined with the speed and prowess of formulas. Chapter 5 concludes with several practical
reusable components that you can start using in your own work right away.
Chapter 1
Introduction to Advanced Excel
Essentials
I set out to write a book on the
essentials
of Excel development—that is, a book that concisely presents many of the
development principles and practices I’ve discovered through my work and consulting experience.
But whether on purpose or by accident, this book has become something considerably more than that. Indeed,
another name for this book could be
A Contrarian’s Guide To Excel Development.
You see, this book will push
back against the wisdom of other terrific Excel books, including my favorite book,
Professional Excel Development
(Addison-Wesley 2005). To be sure, the information in those books is terrific, and whatever merits this book might
achieve, it will likely never come close to the impact of
Professional Excel Development.
At the same time, much of the information in these books, I believe, is somewhat dated. For instance, let’s take
the case of Hungarian Notation. Hungarian Notation is a variable naming convention encouraged by virtually all
Excel development books. Even if you’ve never heard of Hungarian Notation, you’ve likely seen and used it, if you’ve
ever looked at or learned from example code. It basically says a variable’s name should start with a prefix of the
variable’s type. For instance,
lblCaption, intCounter,
and
strTitle
are all examples of Hungarian Notation: the
lbl
in
lblCaption
tells us we’re working with a Label object; the
int
in
intCounter
tells us we’re working with an integer
type, and the
str
in
strTitle
tell us we’re working with a string type. If you’ve done any VBA coding before, this is
likely not new information.
You might not know this, however: most modern languages have all but abandoned Hungarian Notation.
Microsoft’s .NET style guidelines, for instance, even discourage its use. More than a decade has passed since Microsoft
last recommended Hungarian Notation. I argue that it’s time for a more modern naming style, which I introduce in
Chapter 2.
But this book is concerned with more than just naming conventions. I argue that we should change the way we
think about development. Previous books have placed significant emphasis on user interface with ActiveX objects
and UserForms. This book will eschew these bloated controls; rather, this book will show you how to develop complex
interactivity using the spreadsheet as your canvas. You’ll see that it’s easier and provides for more control and
flexibility compared to conventional methods from other books.
In addition, I’ll place less emphasis on code and a stronger emphasis on formulas (Chapters 3, 4, and 5). Many
books have narrowly defined the principles of advanced Excel in terms of VBA code. But formulas can be powerful.
And often they can be used in place of VBA code. You might be surprised by how much interactivity you can create
without writing a single line of code. And how much quicker your spreadsheet runs because of it.
This book is divided into two parts. Part I (Chapters 1-5) deals with concepts that are likely already familiar to you.
Specifically they concern VBA code and formulas—but I present these concepts in new ways. Part II makes up the last
four chapters of the book (Chapters 6-9). These chapters apply concepts from Part I to a real-world example product
I built in my consulting experience. Futhermore, in Part II, you’ll learn how to input form data without making your
spreadsheet bloated. You’ll also apply some data analytics used in the field of management science.
3
Zgłoś jeśli naruszono regulamin