買這商品的人也買了...
-
$1,078Power System Analysis, 2/e (Paperback)
-
$820$648 -
$680$646 -
$680$537 -
$880$695 -
$1,180$1,121 -
$620$608 -
$350$277 -
$580$458 -
$1,200$948 -
$880$695 -
$550$435 -
$580$452 -
$600$480 -
$880$695 -
$2,390$2,271 -
$380$323 -
$580$458 -
$1,620$1,539 -
$580$493 -
$520$411 -
$480$379 -
$650$553 -
$820$648 -
$620$490
商品描述
Description
Financial Applications using Excel Add-in Development in C/C++ is a must-buy book for any serious Excel developer.Excel is the industry standard for financial modelling, providing a number of ways for users to extend the functionality of their own add-ins, including VBA and C/C++. This is the only complete how-to guide and reference book for the creation of high performance add-ins for Excel in C and C++ for users in the finance industry. Steve Dalton explains how to apply Excel add-ins to financial applications with many examples given throughout the book. It also covers the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++, and provides comprehensive code, workbooks and example projects on the accompanying CD-ROM. The impact of Excel 2007’s multi-threaded workbook calculations and large grids on add-in development are fully explored. Financial Applications using Excel Add-in Development in C/C++ features:
- Extensive example codes in VBA, C and C++, explaining all the ways in which a developer can achieve their objectives.
- Example projects that demonstrate, from start to finish, the potential of Excel when powerful add-ins can be easily developed.
- Develops the readers understanding of the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++.
A CD-ROM with several thousand lines of example code, numerous workbooks, and a number of complete example projects.
Table of Contents
Preface to Second Edition.Preface to First Edition.
Acknowledgements for the First Edition.
Acknowledgements for the Second Edition.
1 Introduction.
1.1 Typographical and code conventions used in this book.
1.2 What tools and resources are required to write add-ins.
1.2.1 VBA macros and add-ins.
1.2.2 C/C++ DLL add-ins.
1.2.3 C/C++ DLLs that can access the C API and XLL add-ins.
1.2.4 C/C++/C#.NET add-ins.
1.3 To which versions of Excel does this book apply?
1.4 The future of Excel: Excel 2007 (Version 12).
1.4.1 Summary of key workbook changes.
1.4.2 Aspects of Excel 2007 not covered in this book.
1.4.3 Excel 2007 file formats.
1.4.4 Compatibility between Excel 2007 and earlier versions.
1.5 About add-ins.
1.6 Why is this book needed?
1.7 How this book is organised.
1.8 Scope and limitations.
2 Excel Functionality.
2.1 Overview of Excel data organisation.
2.2 A1 versus R1C1 cell references.
2.3 Cell contents.
2.4 Worksheet data types and limits.
2.5 Excel input evaluation.
2.6 Data type conversion.
2.6.1 The unary = operator.
2.6.2 The unary – operator (negation).
2.6.3 Number-arithmetic binary operators: + - */^.
2.6.4 Percentage operator: %.
2.6.5 String concatenation operator: &.
2.6.6 Boolean binary operators: =,< , >,< =, >=,< >.
2.6.7 Conversion of single-cell references.
2.6.8 Conversion of multi-cell range references.
2.6.9 Conversion of defined range names.
2.6.10 Explicit type conversion functions: N(), T(), TEXT(), VALUE().
2.6.11 Worksheet function argument type conversion.
2.6.12 Operator evaluation precedence.
2.7 Strings.
2.7.1 Length-prepended versus null-terminated strings.
2.7.2 Byte strings versus Unicode strings.
2.7.3 Unmanaged versus managed strings.
2.7.4 Summary of string types used in Excel.
2.7.5 Converting one string type to another.
2.7.6 Hybrid length-counted null-terminated strings.
2.8 Excel Terminology: Active and Current.
2.9 Commands versus functions in Excel.
2.10 Types of worksheet function.
2.10.1 Function purpose and return type.
2.10.2 Array formulae – The Ctrl-Shift-Enter keystroke.
2.10.3 Required, optional and missing arguments and variable argument lists.
2.11 Complex functions and commands.
2.11.1 Data Tables.
2.11.2 Goal Seek and Solver Add-in.
2.12 Excel recalculation logic.
2.12.1 Marking dependents for recalculation.
2.12.2 Triggering functions to be called by Excel – the trigger argument.
2.12.3 Volatile functions.
2.12.4 Cross-worksheet dependencies – Excel 97/2000 versus 2002 and later versions.
2.12.5 User-defined functions (VB Macros) and add-in functions.
2.12.6 Data Table recalculation.
2.12.7 Conditional formatting.
2.12.8 Argument evaluation: IF(), OR(), AND(), CHOOSE()....
2.12.9 Controlling Excel recalculation programmatically.
2.12.10 Forcing Excel to recalculate a workbook or other object.
2.12.11 Using functions in name definitions.
2.12.12 Multi-threaded recalculation.
2.13 The Add-in Manager.
2.14 Loading and unloading add-ins.
2.14.1 Add-in information.
2.15 Paste function dialog.
2.15.1 Function category.
2.15.2 Function name, argument list and description.
2.15.3 Argument construction dialog.
2.16 Good spreadsheet design and practice.
2.16.1 Filename, sheet title and name, version and revision history.
2.16.2 Magic numbers.
2.16.3 Data organisation and design guidelines.
2.16.4 Formula repetition.
2.16.5 Efficient lookups: MATCH(), INDEX() and OFFSET() versus VLOOKUP().
2.17 Problems with very large spreadsheets.
2.18 Conclusion.
3 UsingVBA.
3.1 Opening the VB editor.
3.2 Using VBA to create new commands.
3.2.1 Recording VBA macro commands.
3.3 Assigning VBA command macros to control objects in a worksheet.
3.4 Using VBA to trap Excel events.
3.5 Using VBA to create new functions.
3.5.1 Function scope.
3.5.2 Declaring VBA functions as volatile.
3.6 Using VBA as an interface to external DLL add-ins.
3.6.1 Declaring DLL functions in VB.
3.6.2 Call-by-reference versus call-by-value.
3.6.3 Converting argument and return data types between VBA and C/C++.
3.6.4 VBA data types and limits.
3.6.5 VB/OLE Currency type.
3.6.6 VB/OLE Bstr Strings.
3.6.7 Passing strings to C/C++ functions from VBA.
3.6.8 Returning strings to VBA from a DLL.
3.6.9 Variant data type.
3.6.10 Variant types supported by VBA.
3.6.11 Variant types that Excel can pass to VBA functions.
3.6.12 User-defined data types in VB.
3.6.13 VB object data type.
3.6.14 Calling XLM functions and commands from VBA: Application.ExecuteExcel4Macro().
3.6.15 Calling user-defined functions and commands from VBA: Application.Run().
3.7 Excel ranges, VB arrays, SafeArrays, array Variants.
3.7.1 Declaring VB arrays and passing them back to Excel.
3.7.2 Passing arrays and ranges from Excel to VBA to C/C++.
3.7.3 Converting array Variants to and from C/C++ types.
3.7.4 Passing VB arrays to and from C/C++.
3.8 Commands versus functions in VBA.
3.9 Creating VB add-ins (XLA files).
3.10 VBA versus C/C++: some basic questions.
4 Creating a 32-bit Windows (Win32) DLL Using Visual C++ 6.0 or Visual Studio.NET.
4.1 Windows library basics.
4.2 DLL basics.
4.3 DLL memory and multiple DLL instances.
4.4 Multi-threading.
4.5 Compiled function names.
4.5.1 Name decoration.
4.5.2 The extern "C" declaration.
4.6 Function calling conventions: _cdecl, _stdcall, _fastcall.
4.7 Exporting DLL function names.
4.7.1 The _declspec(dllexport) keyword.
4.7.2 Definition (*.DEF) files.
4.7.3 Using a preprocessor linker directive.
4.8 What you need to start developing add-ins in C/C++.
4.9 Creating a DLL using Visual C++ 6.0.
4.9.1 Creating the empty DLL project.
4.9.2 Adding code to the project.
4.9.3 Compiling and debugging the DLL.
4.10 Creating a DLL using Visual C++.NET 2003.
4.10.1 Creating the empty DLL project.
4.10.2 Adding code to the project.
4.10.3 Compiling and debugging the DLL.
4.11 Accessing DLL functions from VB.
4.12 Accessing DLL functions from excel.
5 Turning DLLs into XLLs: The Add-in Manager Interface.
5.1 The Xlcall32 library and the C API functions.
5.2 What does the Add-in manager do?
5.2.1 Loading and unloading installed add-ins.
5.2.2 Active and inactive add-ins.
5.2.3 Deleted add-ins and loading of inactivate add-ins.
5.3 Creating an XLL: The xlAuto interface functions.
5.4 When and in what order does Excel call the XLL interface functions?
5.5 XLL functions called by the Add-in Manager and Excel.
5.5.1 xlAutoOpen.
5.5.2 xlAutoClose.
5.5.3 xlAutoAdd.
5.5.4 xlAutoRemove.
5.5.5 xlAddInManagerInfo (xlAddInManagerInfo12).
5.5.6 xlAutoRegister (xlAutoRegister12).
5.5.7 xlAutoFree (xlAutoFree12).
6 Passing Data Between Excel and the DLL.
6.1 Handling Excel’s internal data structures: C OR C++?
6.2 How Excel exchanges worksheet data with DLL add-in functions.
6.2.1 Native C/C++ data types.
6.2.2 Excel floating-point array structures: xl4_array, xl12_array.
6.2.3 The xloper/xloper12 structures.
6.2.4 The xlref/xlref12 structures.
6.2.5 The xlmref/xlmref12 structures.
6.2.6 The oper/oper12 structures.
6.3 Defining constant xlopers/xloper12s.
6.4 A C++ class wrapper for the xloper/xloper12 – cpp_xloper.
6.5 Converting between xloper/xloper12s and C/C++ data types.
6.6 Converting between xloper/xloper12 types.
6.7 Converting between xlopers and variants.
6.8 Converting between xlopers and xloper12s.
6.9 Detailed Discussion of xloper types.
6.9.1 Freeing xloper memory.
6.9.2 Worksheet (floating point) number: xltypeNum.
6.9.3 Length-counted string: xltypeStr.
6.9.4 Excel Boolean: xltypeBool.
6.9.5 Worksheet error value: xltypeErr.
6.9.6 Excel internal integer: xltypeInt.
6.9.7 Array (mixed type): xltypeMulti.
6.9.8 Worksheet cell/range reference: xltypeRef and xltypeSRef.
6.9.9 Empty worksheet cell: xltypeNil.
6.9.10 Worksheet binary name: xltypeBigData.
6.10 Initialising xloper/xloper12s.
6.11 Missing arguments.
7 Memory Management.
7.1 Excel stack space limitations.
7.2 Static add-in memory and multiple Excel instances.
7.3 Getting Excel to free memory allocated by Excel.
7.3.1 Freeing xloper memory within the DLL call.
7.3.2 Freeing Excel-allocated xloper memory returned by the DLL function.
7.3.3 Hiding xloper memory management within a C++ class.
7.4 Getting Excel to call back the DLL to free DLL-allocated memory.
7.5 Returning data by modifying arguments in place.
7.6 Making add-in functions thread safe.
7.6.1 Multi-threaded recalculations (MTR) in Excel 2007 (version 12).
7.6.2 Which of Excel’s built-in functions are thread-safe.
7.6.3 Allocating thread-local memory.
7.6.4 Excel’s sequencing of calls to xlAutoFree in a multi-threaded system.
7.6.5 Using critical sections with memory shared between threads.
8 Accessing Excel Functionality using the C API.
8.1 The Excel 4 macro language (XLM).
8.1.1 Commands, worksheet functions and macro sheet functions.
8.1.2 Commands that optionally display dialogs – the xlPrompt bit.
8.1.3 Accessing XLM functions from the worksheet using defined names.
8.2 The Excel4(),Excel12() C API functions.
8.2.1 Introduction.
8.2.2 Excel4(), Excel12() return values.
8.2.3 Calling Excel worksheet functions in the DLL using Excel4(), Excel12().
8.2.4 Calling macro sheet functions from the DLL using Excel4(), Excel12().
8.2.5 Calling macro sheet commands from the DLL using Excel4()/Excel12().
8.3 The Excel4v()/Excel12v() C API functions.
8.4 What C API functions can the DLL call and when?
8.5 Wrapping the C API.
8.6 Registering and un-registering DLL (XLL) functions.
8.6.1 The xlfRegister function.
8.6.2 Specifying which category the function should be listed under.
8.6.3 Specifying argument and return types.
8.6.4 Giving functions macro sheet function permissions.
8.6.5 Specifying functions as volatile.
8.6.6 Specifying functions as thread-safe (Excel 2007 only).
8.6.7 Returning values by modifying arguments in place.
8.6.8 The Paste Function dialog (Function Wizard).
8.6.9 Function help parameter to xlfRegister.
8.6.10 Argument help parameters to xlfRegister.
8.6.11 Managing the data needed to register exported functions.
8.6.12 Registering functions with dual interfaces for Excel 2007 and earlier versions.
8.6.13 A class based approach to managing registration data.
8.6.14 Getting and using the function’s register ID.
8.6.15 Un-registering a DLL function.
8.7 Registering and un-registering DLL (XLL) commands.
8.7.1 Accessing XLL commands.
8.7.2 Breaking execution of an XLL command.
8.8 Functions defined for the C API only.
8.8.1 Freeing Excel-allocated memory within the DLL: xlFree.
8.8.2 Getting the available stack space: xlStack.
8.8.3 Converting one xloper/xloper12 type to another: xlCoerce.
8.8.4 Setting cell values from a command: xlSet.
8.8.5 Getting the internal ID of a named sheet: xlSheetId.
8.8.6 Getting a sheet name from its internal ID: xlSheetNm.
8.8.7 Yielding processor time and checking for user breaks: xlAbort.
8.8.8 Getting Excel’s instance handle: xlGetInst.
8.8.9 Getting the handle of the top-level Excel window: xlGetHwnd.
8.8.10 Getting the path and file name of the DLL: xlGetName.
8.9 Working with binary names.
8.9.1 The xltypeBigData xloper.
8.9.2 Basic operations with binary names.
8.9.3 Creating, deleting and overwriting binary names.
8.9.4 Retrieving binary name data.
8.9.5 Example worksheet functions.
8.10 Workspace information commands and functions.
8.10.1 Setting the application title: xlfAppTitle.
8.10.2 Setting the document window title: xlfWindowTitle.
8.10.3 Getting a reference to the active cell: xlfActiveCell.
8.10.4 Getting a list of all open Excel documents: xlfDocuments.
8.10.5 Information about a cell or a range of cells: xlfGetCell.
8.10.6 Sheet or workbook information: xlfGetDocument.
8.10.7 Getting the formula of a cell: xlfGetFormula.
8.10.8 Getting a cell’s comment: xlfGetNote.
8.10.9 Information about a window: xlfGetWindow.
8.10.10 Information about a workbook: xlfGetWorkbook.
8.10.11 Information about the workspace: xlfGetWorkspace.
8.10.12 Information about the selected range or object: xlfSelection.
8.10.13 Getting names of open Excel windows: xlfWindows.
8.10.14 Converting a range reference: xlfFormulaConvert.
8.10.15 Converting text to a reference: xlfTextref.
8.10.16 Converting a reference to text: xlfReftext.
8.10.17 Information about the calling cell or object: xlfCaller.
8.10.18 Information about the calling function type.
8.11 Working with Excel names.
8.11.1 Specifying worksheet names and name scope.
8.11.2 Basic operations with Excel names.
8.11.3 Defining a name on a worksheet: xlcDefineName.
8.11.4 Defining and deleting a name in the DLL: xlfSetName.
8.11.5 Deleting a worksheet name: xlcDeleteName.
8.11.6 Getting the definition of a named range: xlfGetName.
8.11.7 Getting the defined name of a range of cells: xlfGetDef.
8.11.8 Getting a list of named ranges: xlfNames.
8.12 Working with Excel menus.
8.12.1 Menu bars and ID numbers and menu and command specifiers.
8.12.2 Short-cut (context) menu groups.
8.12.3 Getting information about a menu bar: xlfGetBar.
8.12.4 Creating a new menu bar or restoring a default bar: xlfAddBar.
8.12.5 Adding a menu or sub-menu: xlfAddMenu.
8.12.6 Adding a command to a menu: xlfAddCommand.
8.12.7 Displaying a custom menu bar: xlfShowBar.
8.12.8 Adding/removing a check mark on a menu command: xlfCheckCommand.
8.12.9 Enabling/disabling a custom command or menu: xlfEnableCommand.
8.12.10 Changing a menu command name: xlfRenameCommand.
8.12.11 Deleting a command from a menu: xlfDeleteCommand.
8.12.12 Deleting a custom menu: xlfDeleteMenu.
8.12.13 Deleting a custom menu bar: xlfDeleteBar.
8.13 Working with toolbars.
8.13.1 Getting information about a toolbar: xlfGetToolbar.
8.13.2 Getting information about a tool button on a toolbar: xlfGetTool.
8.13.3 Creating a new toolbar: xlfAddToolbar.
8.13.4 Adding buttons to a toolbar: xlcAddTool.
8.13.5 Assigning/removing a command on a tool: xlcAssignToTool.
8.13.6 Enabling/disabling a button on a toolbar: xlfEnableTool.
8.13.7 Moving/copying a command between toolbars: xlcMoveTool.
8.13.8 Showing a toolbar button as pressed: xlfPressTool.
8.13.9 Displaying or hiding a toolbar: xlcShowToolbar.
8.13.10 Resetting a built-in toolbar: xlfResetToolbar.
8.13.11 Deleting a button from a toolbar: xlcDeleteTool.
8.13.12 Deleting a custom toolbar: xlfDeleteToolbar.
8.14 Working with custom dialog boxes.
8.14.1 Displaying an alert dialog box: xlcAlert.
8.14.2 Displaying a custom dialog box: xlfDialogBox.
8.14.3 Restricting user input to dialog boxes: xlcDisableInput.
8.15 Trapping events with the C API.
8.15.1 Trapping a DDE data update event: xlcOnData.
8.15.2 Trapping a double-click event: xlcOnDoubleclick.
8.15.3 Trapping a worksheet data entry event: xlcOnEntry.
8.15.4 Trapping a keyboard event: xlcOnKey.
8.15.5 Trapping a recalculation event: xlcOnRecalc.
8.15.6 Trapping a window selection event: xlcOnWindow.
8.15.7 Trapping a system clock event: xlcOnTime.
8.16 Miscellaneous commands and functions.
8.16.1 Disabling screen updating during command execution: xlcEcho.
8.16.2 Displaying text in the status bar: xlcMessage.
8.16.3 Evaluating a cell formula: xlfEvaluate.
8.16.4 Calling user-defined functions from an XLL or DLL: xlUDF.
8.16.5 Calling user-defined commands from an XLL or DLL: xlcRun.
8.17 The XLCallVer() C API function.
9 Miscellaneous Topics.
9.1 Timing function execution in VBA and C/C++.
9.2 Relative performance of VBA, C/C++: Tests and results.
9.2.1 Conclusion of test results.
9.3 Relative performance of C API versus VBA calling from a worksheet cell.
9.4 Detecting when a worksheet function is called from an Excel dialog.
9.4.1 Detecting when a worksheet function is called from the Paste Function dialog (Function Wizard).
9.4.2 Detecting when a worksheet function is called from the Search and Replace dialog.
9.4.3 Detecting when a worksheet function is called from either the Search and Replace or Paste Function dialogs.
9.5 Accessing Excel functionality using COM/OLE automation using C++.
9.5.1 Initialising and un-initialising COM.
9.5.2 Getting Excel to recalculate worksheets using COM.
9.5.3 Calling user-defined commands using COM.
9.5.4 Calling user-defined functions using COM.
9.5.5 Calling XLM functions using COM.
9.5.6 Calling worksheet functions using COM.
9.6 Maintaining large data structures within the DLL.
9.7 A C++ Excel name class example, xlName.
9.8 Keeping track of the calling cell of a DLL function.
9.8.1 Generating a unique name.
9.8.2 Obtaining the internal name of the calling cell.
9.8.3 Naming the calling cell.
9.8.4 Internal XLL name housekeeping.
9.9 Passing references to Excel worksheet functions.
9.9.1 Data references.
9.9.2 Function references.
9.10 Multi-tasking, Multi-threading and asynchronous calls in DLLS.
9.10.1 Setting up timed calls to DLL commands: xlcOnTime.
9.10.2 Starting and stopping threads from within a DLL.
9.10.3 Calling the C API from a DLL-created thread.
9.11 A background task management class and strategy.
9.11.1 Requirements.
9.11.2 Communication between Excel and a background thread.
9.11.3 The software components needed.
9.11.4 Imposing restrictions on the worksheet function.
9.11.5 Organising the task list.
9.11.6 Creating, deleting, suspending, resuming the thread.
9.11.7 The task processing loop.
9.11.8 The task interface and main functions.
9.11.9 The polling command.
9.11.10 Configuring and controlling the background thread.
9.11.11 Other possible background thread applications and strategies.
9.12 How to crash Excel.
9.13 Add-in Design.
9.13.1 Separating interface code from core function code.
9.13.2 Controlling error propagation.
9.13.3 Making add-in behaviour Excel version-sensitive and backwards-compatible.
9.13.4 Version-dependent workbook recalculation results.
9.14 Optimisation.
9.14.1 Low level code optimisation.
9.14.2 VBA code optimisation.
9.14.3 Excel calculation optimisation.
10 Example Add-ins and Financial Applications.
10.1 String functions.
10.2 Statistical functions.
10.2.1 Pseudo-random number generation.
10.2.2 Generating random samples from the normal distribution.
10.2.3 Generating correlated random samples.
10.2.4 Quasi-random number sequences.
10.2.5 The normal distribution.
10.3 Matrix functions – eigenvalues and eigenvectors.
10.4 Interpolation.
10.4.1 Linear interpolation.
10.4.2 Bilinear interpolation.
10.4.3 Cubic splines.
10.5 Lookup and search functions.
10.6 Financial markets date functions.
10.7 Building and reading discount curves.
10.8 Building trees and lattices.
10.9 Monte carlo simulation.
10.9.1 Using Excel and VBA only.
10.9.2 Using Excel and C/C++ only.
10.9.3 Using worksheet functions only.
10.10 Calibration.
10.11 CMS derivative pricing.
10.12 The SABR stochastic volatility model.
10.13 Optimising the SABR implementation for CMS derivatives.
Appendix 1. Contents of the CD ROM.
Related Reading.
Web Links and Other Resources.
Index.
商品描述(中文翻譯)
**描述**
《使用 C/C++ 開發 Excel 加載項的金融應用程式》是任何認真 Excel 開發者必備的書籍。Excel 是金融建模的行業標準,提供多種方式讓用戶擴展其加載項的功能,包括 VBA 和 C/C++。這是唯一一本針對金融行業用戶創建高性能 Excel 加載項的完整操作指南和參考書。Steve Dalton 解釋了如何將 Excel 加載項應用於金融應用程式,並在書中提供了許多示例。書中還涵蓋了使用 VBA 與 C/C++ 開發 Excel 加載項的相對優缺點,並在隨附的 CD-ROM 上提供了全面的代碼、工作簿和示例項目。Excel 2007 的多線程工作簿計算和大型網格對加載項開發的影響也得到了充分探討。《使用 C/C++ 開發 Excel 加載項的金融應用程式》特色:
- 大量的 VBA、C 和 C++ 示例代碼,解釋開發者可以實現其目標的所有方式。
- 示例項目從頭到尾展示了當強大的加載項可以輕鬆開發時,Excel 的潛力。
- 增進讀者對使用 VBA 與 C/C++ 開發 Excel 加載項的相對優缺點的理解。
隨書附贈一張 CD-ROM,內含數千行示例代碼、眾多工作簿和若干完整的示例項目。
**目錄**
**第二版前言。**
**第一版前言。**
**第一版致謝。**
**第二版致謝。**
**1 介紹。**
1.1 本書中使用的排版和代碼約定。
1.2 編寫加載項所需的工具和資源。
1.2.1 VBA 宏和加載項。
1.2.2 C/C++ DLL 加載項。
1.2.3 可以訪問 C API 和 XLL 加載項的 C/C++ DLL。
1.2.4 C/C++/C#.NET 加載項。
1.3 本書適用於哪些版本的 Excel?
1.4 Excel 的未來:Excel 2007(版本 12)。
1.4.1 主要工作簿變更摘要。
1.4.2 本書未涵蓋的 Excel 2007 方面。
1.4.3 Excel 2007 文件格式。
1.4.4 Excel 2007 與早期版本的兼容性。
1.5 關於加載項。
1.6 為什麼需要這本書?
1.7 本書的組織結構。
1.8 範圍和限制。
**2 Excel 功能。**
2.1 Excel 數據組織概述。
2.2 A1 與 R1C1 單元格引用。
2.3 單元格內容。
2.4 工作表數據類型和限制。
2.5 Excel 輸入評估。
2.6 數據類型轉換。
2.6.1 一元 = 運算符。
2.6.2 一元 - 運算符(取反)。
2.6.3 數字算術二元運算符:+ - */^。
2.6.4 百分比運算符:%。
2.6.5 字串串接運算符:&。
2.6.6 布林二元運算符:=、<、>、<=、>=、<>。
2.6.7 單元格引用的轉換。
2.6.8 多單元格範圍引用的轉換。
2.6.9 定義範圍名稱的轉換。
2.6.10 明確的類型轉換函數:N()、T()、TEXT()、VALUE()。
2.6.11 工作表函數參數類型轉換。
2.6.12 運算符評估優先順序。
2.7 字串。
2.7.1 長度前綴與空終止字串。
2.7.2 位元組字串與 Unicode 字串。
2.7.3 非管理與管理字串。
2.7.4 Excel 中使用的字串類型摘要。
2.7.5 將一種字串類型轉換為另一種。
2.7.6 混合長度計數的空終止字串。
2.8 Excel 術語:活動與當前。
2.9 Excel 中的命令與函數。
2.10 工作表函數的類型。
2.10.1 函數目的和返回類型。
2.10.2 陣列公式 - Ctrl-Shift-Enter 鍵入。
2.10.3 必需、可選和缺失的參數及可變參數列表。
2.11 複雜的函數和命令。
2.11.1 數據表。
2.11.2 目標尋找和求解器加載項。
2.12 Excel 重新計算邏輯。
2.12.1 標記依賴項以進行重新計算。
2.12.2 觸發 Excel 調用的函數 - 觸發參數。
2.12.3 易變函數。
2.12.4 跨工作表依賴 - Excel 97/2000 與 2002 及以後版本。
2.12.5 用戶定義函數(VB 宏)和加載項函數。
2.12.6 數據表重新計算。
2.12.7 條件格式。
2.12.8 參數評估:IF()、OR()、AND()、CHOOSE()...
2.12.9 以編程方式控制 Excel 重新計算。
2.12.10 強制 Excel 重新計算工作簿或其他對象。
2.12.11 在名稱定義中使用函數。
2.12.12 多線程重新計算。
2.13 加載項管理器。
2.14 加載項的加載和卸載。
2.14.1 加載項信息。
2.15 粘貼函數對話框。
2.15.1 函數類別。
2.15.2 函數名稱、參數列表和描述。
2.15.3 參數構造對話框。
2.16 良好的電子表格設計和實踐。
2.16.1 文件名、工作表標題和名稱、版本和修訂歷史。
2.16.2 魔法數字。
2.16.3 數據組織和設計指南。
2.16.4 公式重複。
2.16.5 高效查找:MATCH()、INDEX() 和 OFFSET() 與 VLOOKUP()。
2.17 非常大的電子表格問題。
2.18 結論。
**3 使用 VBA。**
3.1 打開 VB 編輯器。
3.2 使用 VBA 創建新命令。
3.2.1 記錄 VBA 宏命令。
3.3 指派 VBA 代碼。