Klienditugi: 7440010 (E-R 10-18)

Abi | Registreeri | Logi sisse

E-raamat: Excel for Chemists: A Comprehensive Guide

4.00/5 (8 hinnangut Goodreads-ist)

E. Joseph Billo (Department of Chemistry, Boston College, Chestnut Hill, Massachusetts)

Formaat: PDF+DRM
Ilmumisaeg: 01-Nov-2011
Kirjastus: John Wiley & Sons Inc
Keel: eng
ISBN-13: 9781118093931

Teised raamatud teemal:

Formaat - PDF+DRM
Hind: 74,04 €*
* hind on lõplik, st. muud allahindlused enam ei rakendu
Lisa ostukorvi
Lisa soovinimekirja
See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
Raamatukogudele

Formaat: PDF+DRM
Ilmumisaeg: 01-Nov-2011
Kirjastus: John Wiley & Sons Inc
Keel: eng
ISBN-13: 9781118093931

Teised raamatud teemal:

DRM piirangud

Kopeerimine (copy/paste):

ei ole lubatud
Printimine:

ei ole lubatud
Kasutamine:

Digitaalõiguste kaitse (DRM)
Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

Vajalik tarkvara
Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

Seda e-raamatut ei saa lugeda Amazon Kindle's.

Reviews from previous editions: "Excel for Chemists should be part of any academic library offering courses and programs in Chemistry." Choice

"I highly recommend the book; treat yourself to it; assign it to a class; give it as a gift." The Nucleus

The newly revised step-by-step guide to using the scientific calculating power of Excel to perform a variety of chemical calculations

Chemists across all subdisciplines use Excel to record data in tabular form, but few have learned to take full advantage of the program. Featuring clear step-by-step instructions, Excel for Chemists illustrates how to use the scientific calculating power of Excel to perform a variety of chemical calculations.

Including a CD-ROM for Windows, this new edition provides chemists and students with a detailed guide to using the current versions of Excel (Excel 2007 and 2010) as well as Excel 2003.

Additional features in this third edition include:

How to perform a variety of chemical calculations by creating advanced spreadsheet formulas or by using Excels built-in tools How to automate repetitive tasks by programming Excels Visual Basic for Applications New chapters show how to import data from other language versions of Excel, and how to create automatic procedures The accompanying CD contains a number of Excel macros to facilitate chemical calculations, including molecular weight, nonlinear regression statistics, and data interpolation Several appendices provide extensive lists of useful shortcut keys and function descriptions

Arvustused

Finally this new edition, provides chemists and students a detailed guide and examples how to apply the current versions of Excel to their needs. It should be added to the shelves of those using this program within their scientific work. (Materials and Corrosion, 1 November 2012)

Preface to the Third Edition

xxv

Before You Begin

xxvii

Part I The Basics

Chapter 1 Working with Excel 2007 or Excel 2010*

What's New in Excel 2007 and Excel 2010

(3)

The Ribbon

(1)

New File Formats

(1)

A Much Larger Worksheet

(1)

Larger Limits for Some Features

(1)

New Worksheet Functions

(1)

The Downside

(1)

What's New in Excel 2010

(1)

The Excel 2007/2010 Document Window

(1)

Hiding, Moving or Resizing a Document Window

(1)

Working with Excel 2007/2010

(3)

The Office Button (Excel 2007)

(1)

The File Tab (Excel 2010)

(1)

The Excel Options Window

(1)

The Ribbon: An Overview

(3)

Shortcut Menus

(1)

Keyboard Access to the Ribbon

(1)

The Home Tab

(1)

Navigating Around the Workbook

(5)

Inserting or Deleting Worksheets

(2)

Changing the Name of a Worksheet

(1)

Rearranging the Order of Sheets in a Workbook

(1)

Selecting Multiple Worksheets: [ Group] Mode

(1)

Changing the Color of Sheet Tabs

(1)

Using Move or Copy Sheet or Delete Sheet

(1)

Navigating Around the Worksheet

(2)

Selecting a Range of Cells

(1)

Selecting Non-Adjacent Ranges

(1)

Extending a Selection

(1)

Selecting a Block of Cells

(1)

Entering Data in a Worksheet

(6)

Entering Numbers

(1)

How Excel Stores and Displays Numbers

(1)

Entering Text

(1)

Entering Formulas

(1)

Editing Cell Entries

(1)

The Order in Which Excel Performs Operations in Formulas

(1)

Adding a Text Box

(1)

Entering an Equation in a Text Box

(1)

Entering a Cell Comment

(1)

Opening, Closing and Saving Documents

(2)

Opening or Creating Workbooks

(1)

The List of Recently Used Files

(1)

Using Close or Exit

(1)

Using Save or Save As...

(1)

To Save a File for Use in Excel 2003

(1)

Editing a Worksheet

(12)

Inserting or Deleting Rows or Columns

(2)

Hiding Rows or Columns

(1)

Using Cut, Copy and Paste

(1)

Using Paste Special...

(2)

Using Paste Special to Transpose Rows and Columns

(1)

Copying and Pasting a Picture of Cells

(1)

Using Clear...

(1)

Copy, Cut or Paste Using Drag-and-Drop Editing

(1)

Duplicating Values or Formulas in a Range of Cells

(1)

Absolute, Relative and Mixed References

(1)

Relative References When Using Copy or Cut

(1)

Using AutoFill to Fill Down or Fill Right

(1)

Using AutoFill to Create a Series

(2)

The AutoFill Shortcut Menu

(1)

Formatting Worksheets

(2)

Using Column Width and Row Height

(2)

Formatting Cells

(6)

The Mini Toolbar

(1)

The Format Cells Dialog Box

(1)

Using Alignment

(2)

Using Font

(1)

The Alternate Character Set

(1)

Entering Subscripts and Superscripts

(1)

Using Border and Patterns

(1)

Using the Format Painter Toolbutton

(1)

Number Formatting

(11)

Using the Number Formatting Toolbuttons

(1)

Using Excel's Built-in Number Formats

(2)

Custom Number Formats

(2)

Custom Date Formats

(1)

Time Formats

(1)

Variable Number Formats

(1)

Conditional Number Formats

(1)

Formatting Numbers Using "Precision as Displayed"

(1)

Conditional Formatting (Part I)

(4)

Printing Documents

(5)

Using Page Setup...

(1)

Using Print...

(1)

Printing a Selected Range of Cells in a Worksheet

(1)

Using Set Print Area

(1)

Printing Row or Column Headings for a Multi-Page Worksheet

(1)

Protecting Data in Worksheets

(5)

Protecting a Workbook

(1)

Protecting a Workbook by Making It a Read-Only Workbook

(1)

Hiding a Worksheet

(2)

Protecting a Worksheet by Locking or Hiding Cell Contents

(2)

Controlling the Way Documents Are Displayed

(4)

Using New Window and Arrange

(2)

Different Views of the Same Worksheet

(1)

Using Split Screens

(1)

Using Freeze Panes

(1)

Using Zoom

(1)

Easing the Transition from Excel 2003 to Excel 2007/2010

(4)

Customize the Quick Access Toolbar

(1)

Use Shortcut Keys

(1)

Display Classic Menus

(3)

Excel 2007/2010 Workbook and Worksheet Specifications

(2)

Chapter 2 Working with Excel 2003*

The Excel 2003 Document Window

(3)

Changing What Excel Displays

(2)

Moving or Resizing Documents

(1)

Navigating Around the Workbook

(3)

Changing the Name of a Worksheet

(1)

Changing the Color of Sheet Tabs

(1)

Rearranging the Order of Sheets in a Workbook

(1)

Selecting Multiple Worksheets: [ Group] Mode

(1)

Navigating Around the Worksheet

(2)

Selecting a Range of Cells on the Worksheet

(1)

Selecting Non-Adjacent Ranges

(1)

Extending a Selection

(1)

Selecting a Block of Cells

(1)

Entering Data in a Worksheet

(7)

Entering Numbers

(1)

How Excel Stores and Displays Numbers

(1)

Entering Text

(1)

Entering Formulas

(2)

Editing Cell Entries

(1)

Adding a Text Box

(1)

Entering a Cell Comment

(1)

Using the Equation Editor

(1)

Excel's Menus: An Overview

(1)

Shortcut Menus

(1)

Menu Commands or Toolbuttons?

(1)

Opening, Closing and Saving Documents

(3)

Opening or Creating Workbooks

(1)

Using Move or Copy Sheet... or Delete Sheet

(1)

Using Close or Exit

(1)

Types of Excel Document

(1)

Using Save or Save As...

(1)

Using Save Workspace...

(1)

Editing a Worksheet

(10)

Inserting or Deleting Rows or Columns

(1)

Using Cut, Copy and Paste

(1)

Copying and Pasting Multiple Items

(1)

Using Paste Special.

100

(1)

Using Paste Special to Transpose Rows and Columns

101

(1)

Using Clear

101

(1)

Using the Insert Menu

101

(1)

Copy, Cut or Paste Using Drag-and-Drop Editing

102

(1)

Duplicating Values or Formulas in a Range of Cells

103

(1)

Absolute, Relative and Mixed References

104

(1)

Relative References When Using Copy and Cut

105

(1)

Using AutoFill to Fill Down or Fill Right

105

(1)

Creating a Series

105

(1)

Using AutoFill to Create a Series

106

(1)

The AutoFill Shortcut Menu

107

(1)

Formatting Worksheets

108

(5)

Using Column Width... and Row Height...

108

(1)

Formatting Cells

108

(1)

Using Alignment

109

(2)

Using Font

111

(1)

The Alternate Character Set

111

(1)

Entering Subscripts and Superscripts

112

(1)

Using Border and Patterns.

112

(1)

Using the Format Painter Toolbutton

113

(1)

Number Formatting

113

(7)

Using Excel's Built-in Number Formats

113

(1)

Custom Number Formats

114

(2)

Date Formats

116

(1)

Time Formats

117

(1)

Variable Number Formats

117

(1)

Conditional Number Formats

118

(1)

Using the Number Formatting Toolbuttons

118

(1)

Formatting Numbers Using "Precision as Displayed"

118

(1)

Using Conditional Formatting (Part I)

119

(1)

Printing Documents

120

(5)

Using Page Setup

120

(3)

Using Print Preview

123

(1)

Using Print...

123

(1)

Printing a Selected Range of Cells in a Worksheet

124

(1)

Printing Row or Column Headings for a Multi-Page Worksheet

125

(1)

Protecting Data in Worksheets

125

(5)

Protecting a Workbook

125

(1)

Protecting a Workbook by Making it a Read-Only Workbook

126

(1)

Hiding a Worksheet

126

(1)

Protecting a Worksheet by Locking or Hiding Cell Contents

127

(3)

Controlling the Way Documents Are Displayed

130

(5)

Viewing Several Worksheets at the Same Time

130

(1)

Using New Window and Arrange...

130

(1)

Different Views of the Same Worksheet

131

(1)

Using New Window

132

(1)

Using Split Screens

132

(1)

Using Freeze Panes

133

(1)

Opening Excel 2007/2010 Documents in Excel 2003

134

(1)

Excel 2003 Workbook and Worksheet Specifications

135

(2)

Chapter 3 Excel Formulas and Functions

The Elements of a Worksheet Formula

137

(6)

Constants

137

(1)

Operators

137

(1)

References: Absolute, Relative and Mixed

138

(1)

R1C1 Reference Style

139

(1)

Creating and Using External References

140

(1)

Creating an External Reference by Selecting

140

(1)

Creating an External Reference by Using Paste Link

140

(1)

The External Reference Contains the Complete Directory Path

141

(1)

Updating References and Re-Establishing Links

141

(1)

Creating and Using 3-D References

142

(1)

Worksheet Functions: An Overview

143

(2)

Function Arguments

143

(1)

Nested Functions

144

(1)

New Functions Introduced in Excel 2007

144

(1)

Changes to Functions in Excel 2010

144

(1)

Using Insert Function

145

(4)

A Shortcut to a Function

147

(1)

Using Formula AutoComplete (Excel 2007/2010 Only)

147

(2)

Math and Trigonometric Functions

149

(2)

Trigonometric Functions

150

(1)

Functions for Working with Matrices

151

(1)

Statistical Functions

151

(1)

Logical Functions

152

(9)

The IF Function

153

(2)

Nested IF Functions

155

(1)

AND, OR and NOT

155

(1)

Using IFERROR (Excel 2007/2010 Only)

155

(1)

Using COUNTIF

156

(1)

Using SUMIF

156

(1)

Using AVERAGEIF (Excel 2007/2010 Only)

157

(1)

Using COUNTIFS, SUMIFS and AVERAGEIFS (Excel 2007/2010 Only)

157

(2)

Using Conditional Formatting (Part II)

159

(2)

Date and Time Functions

161

(4)

How Excel Keeps Track of the Date and Time

161

(1)

Date and Time Arithmetic

162

(1)

Creating Date Series

162

(1)

Using Date and Time Functions

163

(1)

Examples of Date and Time Formulas

164

(1)

Text Functions

165

(5)

The LEN, LEFT, RIGHT and MID Functions

166

(1)

The UPPER, LOWER and PROPER Functions

166

(1)

The FIND, SEARCH, REPLACE, SUBSTITUTE and EXACT Functions

166

(3)

The FIXED and TEXT Functions

169

(1)

The VALUE Function

169

(1)

The CODE and CHAR Functions

169

(1)

Lookup and Reference Functions

170

(1)

Information Functions

171

(1)

Creating "Megaformulas"

172

(2)

Advantages and Disadvantages of Megaformulas

173

(1)

The Order in Which Excel Performs Operations in Formulas

174

(1)

Formula and Function Specifications

175

(2)

Chapter 4 Excel 2007/2010 Charts

Chart Types

177

(1)

Creating a Chart

177

(20)

Chart Tools in the Ribbon

179

(2)

Activating, Resizing and Moving an Embedded Chart

181

(1)

The SERIES Function

181

(1)

Chart Elements

182

(1)

Selecting Chart Elements

182

(1)

Formatting Chart Elements

183

(1)

Creating Column or Bar Charts

183

(3)

Creating Line Charts

186

(2)

Creating Pie Charts

188

(4)

Creating Radar Charts

192

(2)

Creating Surface Charts (3-D Charts)

194

(1)

Creating XY Charts

195

(1)

Switching Between Chart Types

196

(1)

Formatting the Elements of an XY Chart

197

(6)

Formatting Chart Elements by Using the Mini Toolbar

197

(1)

Formatting a Data Series

198

(1)

Formatting Chart Elements with Color

199

(1)

Modifying an Axis Scale

200

(1)

Changing the Number Format of an Axis Scale

201

(1)

Changing the Dimensions of a Chart

202

(1)

When the X-Values of a Category Chart Are Dates

203

(3)

Tutorial on Creating Scientific Charts

206

(1)

Excel 2007/2010 Chart Specifications

207

(2)

Chapter 5 Excel 2003 Charts

Chart Types

209

(1)

Creating a Chart

209

(12)

Creating a Chart Using the ChartWizard

209

(4)

Chart Elements

213

(1)

The SERIES Function

213

(1)

Creating Column or Bar Charts

214

(1)

Creating Line Charts

215

(1)

Creating Pie Charts

215

(3)

Creating Radar Charts

218

(2)

Creating Surface Charts (3-D Charts)

220

(1)

Activating, Resizing and Moving an Embedded Chart

221

(1)

Formatting Charts: An Introduction

221

(2)

Using the Chart Menu

222

(1)

Using Chart Type... to Switch from One Chart Type to Another

222

(1)

Using Chart Options... to Add Titles, Gridlines or a Legend

222

(1)

Using Location... to Move or Copy an Embedded Chart

222

(1)

Formatting the Elements of an XY Chart

223

(4)

Selecting Chart Elements

223

(1)

Formatting Chart Elements

223

(4)

When the X-Values of a Category Chart Are Dates

227

(3)

Excel 2003 Chart Specifications

230

(3)

Part II Advanced Spreadsheet Topics

Chapter 6 Advanced Worksheet Formulas

Using Names Instead of References

233

(1)

Guidelines for Creating Names

233

(1)

Defining Names in Excel 2003

234

(6)

Using Define Name

235

(1)

Using Create Names

235

(2)

Using the Drop-Down Name List Box

237

(1)

Names Can Be Local or Global

238

(2)

Defining Names in Excel 2007/2010

240

(2)

Using Define Name

240

(1)

Using Create Names from Selection

241

(1)

Using the Name Manager

241

(1)

More about Using Names

242

(5)

A Reference Using Implicit Intersection

242

(1)

A Name Can Refer to a Constant or to a Formula

243

(1)

Entering a Name in a Formula by Selecting

244

(1)

Using Apply Names

244

(1)

Using Paste Name

245

(1)

Deleting Names

245

(1)

Changing a Name

246

(1)

Using Paste List or Paste Names.

246

(1)

The Label... Command (Excel 2003 Only)

247

(1)

Worksheet Functions for the "Power User"

247

(1)

Information Functions

247

(2)

The CELL Function

248

(1)

The N Function

248

(1)

Lookup Functions

249

(6)

The CHOOSE Function

249

(1)

The VLOOKUP and HLOOKUP Functions

249

(1)

The INDEX and MATCH Functions

250

(1)

Getting Values from a One-Way Table

251

(1)

Getting Values from a Two-Way Table

251

(2)

Creating a Custom Lookup Formula to Obtain Values from a Table

253

(1)

Wildcard Characters with VLOOKUP, MATCH and Other Functions

254

(1)

The LOOKUP Function

254

(1)

Reference Functions

255

(2)

The OFFSET Function

255

(1)

The INDIRECT Function

256

(1)

The ADDRESS Function

256

(1)

Solving a Problem by Using Intentional Circular References

257

(2)

Using Excel 4 Macro Functions in Worksheet Formulas

259

(2)

Troubleshooting the Worksheet

261

(2)

Error Values and Their Meanings

261

(1)

Examining Formulas

262

(1)

Finding Dependent and Precedent Cells

263

(1)

Repairing or Removing Links in Documents

263

(4)

Re-establishing Links

264

(2)

Finding All Links in a Workbook

266

(1)

Chapter 7 Array Formulas

Introduction to Array Formulas

267

(5)

Array Constants

269

(1)

Formulas and Functions that Return an Array Result

270

(1)

Editing or Deleting Arrays

270

(1)

Creating a "Three-Dimensional" Array on a Single Worksheet

271

(1)

An Array Formula Example: Creating a Specialized SUMIF Formula

272

(1)

Evaluating Polynomials or Power Series Using Array Formulas

273

(1)

Using the ROW Function in Array Formulas

274

(1)

Using the INDIRECT Function in Array Formulas

274

(1)

Using Array Formulas to Work with Lists

274

(7)

Using Multiple Criteria to Count Entries in a List.

275

(1)

Counting Common Entries in Two Lists

276

(2)

Counting Duplicate Entries in a List

278

(1)

Counting Unique Entries in a List

278

(1)

Indicating Duplicate Entries in a List

279

(1)

Returning an Array of Unique Entries in a List

280

(1)

Sorting by Using an Array Formula

281

(5)

Sorting a 1-D List of Numbers

282

(1)

Sorting a 2-D List Using a Column of Numbers as the Sortkey

282

(3)

"Auto-Alphabetizing" Using an Array Formula

285

(1)

Another Example: Validating a CAS Registry Number

286

(3)

Chapter 8 Advanced Charting Techniques

Charts with More than One Data Series

289

(4)

Plotting Two or More Different Sets of Y Values in the Same Chart

289

(2)

Plotting Two Different Sets of X and Y Values in the Same Chart

291

(1)

Alternate Data Layout for Two Different Sets of X and Y Values

292

(1)

Extending a Data Series or Adding a New Series

293

(4)

The Copy and Paste Method

293

(1)

The Drag-and-Drop Method

293

(1)

The Color-Coded Ranges Method

294

(1)

Using Source Data...

295

(2)

Editing the SERIES Function in the Formula Bar

297

(1)

Customizing Charts

297

(4)

Good Charts vs. Bad Charts

298

(1)

Plotting Experimental Data Points and a Calculated Curve

298

(3)

Charts with Secondary Axes

301

(7)

An XY Chart with a Secondary Y Axis

301

(3)

A Chart with Secondary X Axis and Y Axis

304

(2)

A Column Chart with a Secondary Y Axis

306

(2)

Some Additional Customization Techniques

308

(9)

Adding Error Bars to an XY Chart

308

(5)

Using Sub- or Superscripts in Chart Legends

313

(1)

Modifying Tick Mark Labels on Axis Scales

313

(2)

Adding Data Labels to an XY Chart

315

(1)

Logarithmic Charts

315

(1)

Linking Chart Text Elements to a Worksheet

316

(1)

To Switch Plotting Order in an XY Chart

316

(1)

Getting Creative with Charts

317

(4)

A Chart with an Additional Axis

317

(3)

A Chart that Updates Automatically

320

(1)

Changing the Default Chart Format

321

(5)

Saving a Chart Template in Excel 2007/2010

321

(1)

Changing the Default Chart Format in Excel 2003

322

(2)

Copying and Pasting Chart Formatting

324

(2)

Excel Chart Specifications

326

(1)

Chapter 9 Using Excel's Database Features

The Structure of a List or Database

327

(3)

Creating a Database

327

(1)

Defining a Database

328

(1)

Adding or Deleting Records or Fields

328

(1)

Updating a Database Using Data Form

329

(1)

Sorting a List

330

(5)

Sorting a List Using Excel 2003

331

(1)

Sorting According to More Than One Field

332

(1)

Sorting a List Using Excel 2007/2010

332

(1)

Sort Options

333

(2)

Using AutoFilter to Obtain a Subset of a List

335

(5)

Using Multiple Data Filters

339

(1)

Using Advanced Filter to Obtain a Subset of a List

340

(6)

Defining and Using Selection Criteria

341

(1)

Using Multiple Criteria

342

(2)

Special Criteria for Text Entries

344

(1)

Extracting Records

344

(2)

Using Database Functions

346

(3)

Chapter 10 Importing Data into Excel

Direct Input of Instrument Data into Excel

349

(1)

Importing Data Files Using the Text Import Wizard

350

(3)

Importing a Delimited File

350

(3)

Importing a Fixed-Width File

353

(1)

Using Convert Text to Columns

353

(5)

Parsing a Delimited File

354

(1)

Parsing a Fixed-Width File

355

(3)

From Hard Copy (Paper) to Excel

358

(3)

Using a Scanner to Transfer Numeric Data to Excel

358

(2)

Using a Scanner to Transfer Graphical Data to Excel

360

(1)

Selecting Every Nth Data Point

361

(4)

Using AutoFill

361

(1)

Using the Sampling Tool

362

(1)

Using a Worksheet Formula

363

(2)

Chapter 11 Adding Controls to a Spreadsheet

Option Buttons, Check Boxes, List Boxes and Other Controls

365

(1)

How to Add a Control to a Worksheet

366

(7)

Control Properties

367

(2)

A List Box on a Worksheet

369

(1)

A Drop-down List Box on a Worksheet

370

(1)

Option Buttons with a Drop-down List Box

371

(2)

Using a Check Box to Enable or Disable Conditional Formatting

373

(1)

How to Add a Hyperlink to a Worksheet

373

(5)

Inserting a Hyperlink

373

(3)

Using Paste as Hyperlink...

376

(1)

Using the HYPERLINK Worksheet Function

376

(2)

Using Data Validation

378

(7)

Specifying Input Values

379

(2)

Specifying an Input Message or Error Alert

381

(1)

Using the Custom Category

382

(3)

Chapter 12 Other Language Versions of Excel

Differences in the Display of Numbers

385

(2)

Decimal and Thousands Separators

385

(1)

Argument Separators in Functions

386

(1)

Array Separators

387

(1)

Differences in the Display of Dates

387

(3)

Date Formats

387

(1)

Date Formatting Symbols

388

(1)

Creating Custom Date Formats for Other Language Versions

389

(1)

Importing Data

390

(3)

Importing Values that Are Numbers

390

(1)

Importing Values that Are Dates Using the Text Import Wizard

391

(2)

Worksheet Function Names in Other Languages

393

(10)

The FunctionName Translator

399

(4)

Part III Spreadsheet Mathematics

Chapter 13 Mathematical Methods for Spreadsheet Calculations

Interpolation

403

(4)

Table Lookup with Linear Interpolation

403

(2)

Cubic Interpolation

405

(2)

Arrays, Matrices and Determinants

407

(3)

An Introduction to Matrix Mathematics

408

(2)

Numerical Differentiation

410

(5)

First and Second Derivatives of a Data Set

411

(3)

Derivatives of a Function

414

(1)

Numerical Integration

415

(3)

Finding the Area Under a Curve

416

(2)

Finding Roots of Equations

418

(7)

The Graphical Method

419

(1)

The Method of Successive Approximations

419

(2)

The Secant Method

421

(2)

Using Goal Seek

423

(2)

Solving Sets of Simultaneous Linear Equations

425

(5)

Cramer's Rule

426

(1)

Solution Using Matrix Inversion

427

(1)

Analysis of Spectra of Mixtures Using Matrix Mathematics

428

(1)

Applying Cramer's Rule to a Spectrophotometric Problem

428

(2)

Analysis of Spectra of Mixtures Using Matrix Inversion

430

(1)

Polar to Cartesian Coordinates

430

(2)

Significant Figures

432

(3)

Chapter 14 Linear Regression and Curve Fitting

Linear Regression

435

(1)

Least-Squares Fit to a Straight Line

436

(2)

Using the SLOPE, INTERCEPT and RSQ Functions

437

(1)

Multiple Linear Regression

438

(13)

Using LINEST to Perform Multiple Linear Regression

439

(1)

Least-Squares Fit to a Straight Line Using the LINEST Function

440

(1)

Regression Line Without an Intercept

441

(1)

The Regression Parameters

441

(1)

Multiple Linear Regression: An Example

442

(3)

Fitting Data Using a Power Series

445

(2)

A LINEST Shortcut for Power Series

447

(1)

Handling Noncontiguous Ranges of known_x's in LINEST

448

(2)

How LINEST Handles Collinearity

450

(1)

Weighted Least Squares

451

(1)

Multiple Linear Regression Using Trendline

451

(2)

Multiple Linear Regression Using the Analysis ToolPak

453

(4)

Using the Regression Tool

455

(2)

Using the Regression Statistics

457

(6)

Testing Whether an Intercept Is Significantly Different from Zero

457

(2)

Testing Whether Two Slopes Are Significantly Different

459

(1)

Testing Whether a Regression Coefficient Is Significant

460

(1)

Testing Whether Regression Coefficients Are Correlated

460

(1)

Confidence Intervals for Slope and Intercept

461

(1)

Confidence Limits and Prediction Limits for a Straight Line

461

(2)

Chapter 15 Nonlinear Regression Using the Solver

Nonlinear Regression

463

(1)

Using the Solver to Perform Nonlinear Least-Squares Curve Fitting

464

(12)

Using the Solver for Optimization

464

(1)

Changes to the Solver in Excel 2010

465

(1)

Using the Solver for Least-Squares Curve Fitting

465

(1)

Using the Solver: An Example

466

(6)

Comparison with a Commercial Nonlinear Least-Squares Package

472

(1)

Solver Options (Excel 2007 or 2003)

472

(2)

Solver Options (Excel 2010)

474

(2)

The "Use Automatic Scaling" Option Is Important for Many Chemical Problems

476

(1)

Some Additional Solver Examples

476

(8)

Deconvolution of Spectra

476

(4)

Determination of Binding Constants by NMR Measurements

480

(4)

Other Examples: NIST Datasets

484

(1)

Statistics of Nonlinear Regression

484

(7)

A Macro to Provide Regression Statistics for the Solver

485

(1)

Using the Solver Statistics Macro

485

(2)

An Additional Benefit from Using the Solver Statistics Macro

487

(4)

Part IV Excel's Visual Basic For Applications

Chapter 16 Visual Basic for Applications: An Introduction

The Visual Basic Editor

491

(3)

Visual Basic Procedures and Modules

494

(2)

There are Two Kinds of Procedure: Sub Procedures and Function Procedures

494

(1)

The Structure of a Sub Procedure

495

(1)

The Structure of a Function Procedure

495

(1)

Using the Recorder to Create a Simple Sub Procedure

496

(5)

Recording a Simple Macro Using Excel 2007/2010

496

(2)

Recording a Simple Macro Using Excel 2003

498

(1)

The Personal Macro Workbook

499

(1)

Running a Sub Procedure

500

(1)

Assigning a Shortcut Key to a Sub Procedure

501

(1)

Creating a Simple Custom Function

501

(1)

Using a Function Macro

501

(1)

How Do I Save a Macro?

502

(1)

Chapter 17 Programming with VBA

Creating Visual Basic Code

503

(2)

Entering VBA Code

503

(1)

Components of Visual Basic Statements

504

(1)

Operators

504

(1)

Variables and Arguments

504

(1)

Objects, Properties and Methods

505

(1)

Objects

505

(2)

Some Useful Objects

506

(1)

"Objects" that Are Really Properties

506

(1)

You Can Define Your Own Objects

507

(1)

Making a Reference to a Cell or Range of Cells

507

(3)

Making a Reference to a Selected Cell or Range

507

(1)

Making a Reference to a Cell Other than the Active Cell

508

(2)

References Using the Union or Intersect Method

510

(1)

Getting Values from a Worksheet

510

(1)

Sending Values to a Worksheet

510

(1)

Properties

510

(2)

Properties of the Range Object

511

(1)

Some Useful Properties

511

(1)

Methods

512

(1)

Some Useful Methods

512

(1)

Two Ways to Specify Arguments of Methods

512

(1)

Arguments with or without Parentheses

513

(1)

VBA Functions

513

(2)

Using Worksheet Functions with VBA

514

(1)

VBA Commands

515

(1)

VBA Data Types

515

(3)

The Variant Data Type

516

(1)

Declaring Variables or Arguments in Advance

516

(1)

Specifying the Data Type of an Argument

516

(1)

Specifying the Data Type Returned by a Function Procedure

516

(1)

String Data Types

517

(1)

The Boolean (Logical) Data Type

517

(1)

Type Declaration Characters

518

(1)

Program Control

518

(3)

Decision-Making (Branching)

518

(2)

Logical Operators

520

(1)

Looping

521

(1)

For...Next Loops

521

(1)

For Each...Next Loops

521

(1)

Do While...Loop

522

(1)

Exiting from a Loop or from a Procedure

522

(1)

Subroutines

522

(2)

Scoping a Subroutine

523

(1)

Interactive Macros

524

(7)

MsgBox

524

(1)

MsgBox Return Values

525

(1)

InputBox

526

(1)

Using a Dialog Box to Open a Workbook

527

(1)

Using the GetOpenFilename Method

528

(1)

Using Excel's Built-In Dialog Boxes

529

(1)

Other Ways to Display Built-In Dialog Boxes

530

(1)

Using Excel 4 Macro Commands

531

(1)

Testing and Debugging

532

(6)

Tracing Execution

533

(1)

Stepping Through Code

534

(1)

Adding a Breakpoint

534

(1)

Examining the Values of Variables Using the Mouse Pointer

535

(1)

Examining the Values of Variables Using the Watch Pane

536

(1)

Using Conditional Watch

537

(1)

Using VBA On-Line Help

538

(3)

Using VBA Help (Excel 2007/2010)

538

(1)

Using VBA Help (Excel 2003)

539

(1)

Using the Object Browser

539

(2)

Some Ways to Improve Performance (i.e., Speed)

541

(2)

Chapter 18 Working with Arrays in VBA

Visual Basic Arrays

543

(2)

Dimensioning an Array

543

(1)

Use the Name of the Array Variable to Specify the Whole Array

544

(1)

Dynamic Arrays

544

(1)

Preserving Values in Dynamic Arrays

545

(1)

Using Arrays in Sub Procedures: Passing Values from Worksheet to VBA Module

545

(2)

Using a Loop to Transfer Values from a Worksheet to a VBA Array

545

(1)

A Range Specified in a Sub Procedure Becomes an Array Variable

546

(1)

Some Worksheet Functions Used Within VBA Create an Array

547

(1)

Using Arrays in Sub Procedures: Passing Values from VBA Module to Worksheet

547

(3)

Using a Loop to Transfer Values from a VBA Array to a Worksheet

547

(1)

Equating a VBA Variable to a Worksheet Range

548

(1)

A One-Dimensional Array Assigned to a Worksheet Range Can Cause Problems

548

(2)

Using Arrays In Function Procedures From Worksheet to Module

550

(2)

A Range Passed to a Function Procedure Automatically Becomes an Array

550

(1)

Passing an Indefinite Number of Arguments Using the ParamArray Keyword

551

(1)

Using Arrays in Function Procedures: Returning an Array of Values as a Result

552

(5)

Part V Some Applications Of VBA

Chapter 19 Command Macros

Creating Advanced Macros in VBA

557

(1)

A Sub Procedure to Format Text as a Chemical Formula

557

(5)

Adding Enhancements to the Chemical Format Macro

558

(2)

Adding More Enhancements

560

(2)

A Sub Procedure to Apply "Precision as Displayed" to a Selected Range of Cells

562

(1)

A Sub Procedure to Apply Data Labels in a Chart

563

(5)

Quick Chart: Create Excel 2007/2010 Charts with One Click

568

(3)

Chapter 20 Custom Functions

Some Additional Tools for Creating Custom functions

571

(7)

Arrays as Arguments or as Return Values

571

(1)

Returning an Array Result in Either a Row or a Column

571

(1)

Returning an Error Value

572

(1)

A Custom Function that Takes an Optional Argument

573

(1)

A Custom Function that Takes an Indefinite Number of Arguments

573

(3)

Providing a Description for a Function in the Paste Function Dialog Box

576

(1)

Providing Descriptions for Function Arguments in the Paste Function Dialog Box (Excel 2010 Only)

576

(1)

Assigning a Custom Function to a Function Category

577

(1)

A Custom Function to Calculate Acid-Base Species Distribution Diagrams

578

(2)

A Custom Statistical Function

580

(3)

A Custom Function to Calculate Molecular Weights

583

(2)

Creating Add-In Function Macros

585

(2)

How to Create an Add-In Macro

585

(1)

How to Load/Unload an Add-In Macro

586

(1)

How to Delete an Add-In Macro

586

(1)

How to Protect an Add-In Workbook

587

(1)

How to Edit an Add-In Workbook or Convert a .xla File to a .xls File

587

(1)

Advantages and Disadvantages of Using Function Macros

587

(2)

Chapter 21 Automatic Procedures

OnEvent Procedures

589

(3)

Auto_Open or Auto_Close Procedures

589

(1)

OnEvent Procedures

590

(1)

Some Examples of OnEvent Code

591

(1)

Using OnUndo

592

(1)

Event-Handler Procedures

592

(3)

Creating a Workbook Open Procedure

594

(1)

Chapter 22 Custom Menus

Modifying Menu Bars, Menus or Menu Commands in Excel 2003

595

(3)

Adding or Removing a Menu Command

595

(1)

Creating a New Menu Bar

596

(1)

Adding a Custom Menu to a Menu Bar

597

(1)

Adding a Custom Menu Command to a Menu

598

(1)

Modifying Menus or Menu Bars by Using VBA

598

(5)

The Basic Structure of a Procedure to Install a New Menu Command

599

(2)

Adding a Menu Command by Means of an Event-Handler Procedure

601

(1)

Exiting Gracefully: Removing a Menu Command

602

(1)

Modifying a Built-In Menu Command

602

(1)

Installing Menu Bars, Menus or Menu Commands in Excel 2007/2010

603

(4)

Displaying Classic Menus in Excel 2007/2010

603

(4)

Chapter 23 Custom Toolbars and Toolbuttons

Customizing Toolbars in Excel 2003

607

(4)

Moving and Changing the Shape of Toolbars

607

(1)

Activating Other Toolbars

608

(1)

Adding or Removing Toolbuttons from Toolbars

609

(1)

Creating a New Toolbar

610

(1)

Creating Custom Toolbuttons

611

(7)

The ToggleNumberFormat Macro

613

(1)

The FullPage Macro

614

(1)

Creating a Custom Toolbutton Image

615

(2)

How to Add a ToolTip to a Custom Button

617

(1)

Customizing in Excel 2007

618

(5)

Moving the Quick Access Toolbar

618

(1)

Adding a Built-In Command Button to the Quick Access Toolbar

618

(2)

Adding a Custom Toolbutton to the Quick Access Toolbar

620

(1)

Adding Custom Toolbuttons to the Ribbon

621

(1)

How to Use the Excel 2003 Button Editor in Excel 2007/2010

622

(1)

Removing Custom Toolbuttons from the Ribbon

622

(1)

Customizing the Ribbon (Excel 2010 Only)

623

Part VI Appendices

Appendix A What's Where in Excel 2007/2010

629

(4)

Appendix B Selected Worksheet Functions by Category

633

(6)

Appendix C Alphabetical List of Selected Worksheet Functions

639

(22)

Appendix D Renamed Functions in Excel 2010

661

(2)

Appendix E Selected Visual Basic Keywords by Category

663

(4)

Appendix F Alphabetical List of Selected Visual Basic Keywords

667

(22)

Appendix G Selected Excel 4 Macro Functions

689

(4)

Appendix H Shortcut Keys by Keystroke

693

(10)

Appendix I Selected Shortcut Keys by Category

703

(4)

Appendix J ASCII Codes

707

(2)

Appendix K Contents of the CD-ROM

709

(10)

Index

719

E. JOSEPH BILLO retired in 2006 as associate professor of chemistry at Boston College and is the author of Excel® for Chemists: A Comprehensive Guide, Second Edition. He has presented the two-day short courses "Advanced Excel for Scientists and Engineers" and "Excel Visual Basic Macros for Scientists and Engineers" to over 2,000 scientists at corporate clients in the U.S., Canada, and Europe.

Lisainfo e-raamatute kohta

Püsilink: https://www.kriso.ee/db/97811180939312e.html

Märksõnad:

E-raamat: Excel for Chemists: A Comprehensive Guide

DRM piirangud

Kopeerimine (copy/paste):

Printimine:

Kasutamine:

Arvustused

Konto & seaded

Otsing

Otsingu andmebaas

Filtreeri tulemusi

Teemad E-raamatute teemad

Vali ostukorv