Muutke küpsiste eelistusi

E-raamat: Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)

2.00/5 (2 hinnangut Goodreads-ist)
  • Formaat: 512 pages
  • Sari: Business Skills
  • Ilmumisaeg: 25-Mar-2022
  • Kirjastus: Addison Wesley
  • Keel: eng
  • ISBN-13: 9780137559398
Teised raamatud teemal:
  • Formaat - EPUB+DRM
  • Hind: 35,09 €*
  • * 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.
Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)Suurem pilt
  • Formaat: 512 pages
  • Sari: Business Skills
  • Ilmumisaeg: 25-Mar-2022
  • Kirjastus: Addison Wesley
  • Keel: eng
  • ISBN-13: 9780137559398
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. 

Paul McFedries' step-by-step projects walk students through handling key tasks, from building timesheets to projecting cash flow and aging receivables. His practical examples and clear instructions demystify intermediate- to advanced-level formula construction, and help students leverage Excel's most useful functions in their everyday work. Becoming an Excel expert has never been easier!

By reading this book, students will:

  • Improve business analyses by adding intelligence and knowledge to their models
  • Replace cumbersome formulas with convenient predefined functions
  • Radically simplify complex calculations with Office 365's new dynamic arrays
  • Use conditional formatting to reveal anomalies, problems, or opportunities
  • Calculate loan payments, interest costs, terms, and amortization schedules
  • Project the future value of investments, and plan to achieve investment goals
  • Master essential discounting and cash-flow analysis tools, including net present value and internal rate of return
  • Sort, filter, and analyze tabular data, from customers to inventory
  • Easily analyze huge data sets with PivotTable calculations


Expert Paul McFedries helps you master key Excel tools for building more powerful spreadsheets.


Use Excel core features to build spreadsheets that solve business problems and deliver reliable answers. Drawing on his unsurpassed experience, Paul McFedries helps you make the most of formulas and functions, including the latest improvements to arrays, formula error handling, and statistics. McFedries' step-by-step projects walk you through handling key tasks, from building timesheets to projecting cash flow and aging receivables. His practical examples and clear instructions demystify intermediate- to advanced-level formula construction, and help you leverage Excel's most useful functions in your everyday work. Becoming an Excel expert has never been easier!


By reading this book, you will:
  • Improve business analyses by adding intelligence and knowledge to your models
  • Replace cumbersome formulas with convenient predefined functions
  • Radically simplify complex calculations with Office 365's new dynamic arrays
  • Use conditional formatting to reveal anomalies, problems, or opportunities
  • Calculate loan payments, interest costs, terms, and amortization schedules
  • Project the future value of investments, and plan to achieve investment goals
  • Master essential discounting and cash-flow analysis tools, including net present value and internal rate of return
  • Sort, filter, and analyze tabular data, from customers to inventory
  • Easily analyze huge data sets with PivotTable calculations

About This Book
  • For everyone who wants to get more done with Microsoft Excel in less time
  • For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data
Acknowledgments xxi
About the Author xxiii
Introduction xxv
Part 1 Mastering Excel Formulas
Chapter 1 Building basic formulas
3(22)
Understanding formula basics
3(4)
Formula limits in Excel 365
4(1)
Entering and editing formulas
4(1)
Using arithmetic formulas
5(1)
Using comparison formulas
6(1)
Using text formulas
7(1)
Using reference formulas
7(1)
Understanding operator precedence
7(3)
The order of precedence
7(1)
Controlling the order of precedence
8(2)
Controlling worksheet calculation
10(1)
Copying and moving formulas
11(4)
Understanding relative reference format
13(1)
Understanding absolute reference format
14(1)
Copying a formula without adjusting relative references
14(1)
Displaying worksheet formulas
15(1)
Displaying all worksheet formulas
15(1)
Displaying a cell's formula by using FORMULATEXT
15(1)
Converting a formula to a value
16(1)
Working with range names in formulas
16(5)
Pasting a name into a formula
17(1)
Applying names to formulas
17(4)
Naming formulas
21(1)
Working with links in formulas
21(4)
Understanding external references
22(1)
Updating links
23(1)
Changing the link source
24(1)
Chapter 2 Creating advanced formulas
25(26)
Working with arrays
25(8)
Using array formulas
26(4)
Using array constants
30(1)
Functions that use or return arrays
31(2)
Using iteration and circular references
33(2)
Consolidating multisheet data
35(6)
Consolidating by position
36(3)
Consolidating by category
39(2)
Applying data validation rules to cells
41(2)
Using dialog box controls on a worksheet
43(8)
Displaying the Developer tab
43(1)
Using the form controls
44(1)
Adding a control to a worksheet
44(1)
Linking a control to a cell value
45(1)
Understanding the worksheet controls
45(6)
Chapter 3 Troubleshooting formulas
51(20)
Understanding Excel's error values
52(5)
#CALC!
52(1)
#DIV/0!
52(1)
#FIELD!
53(1)
#N/A
53(1)
#NAME?
53(1)
Avoiding #NAME? errors when deleting range names
54(1)
#NULL!
55(1)
#NUM!
55(1)
#REF!
56(1)
#SPILL!
56(1)
#UNKNOWN!
56(1)
#VALUE!
56(1)
Fixing other formula errors
57(3)
Missing or mismatched parentheses
57(1)
Erroneous formula results
58(1)
Fixing circular references
59(1)
Handling formula errors with IFERROR
60(1)
Using the formula error checker
60(4)
Choosing an error action
61(1)
Setting error checker options
62(2)
Auditing a worksheet
64(7)
Understanding auditing
64(1)
Tracing cell precedents
65(1)
Tracing cell dependents
66(1)
Tracing cell errors
66(1)
Removing tracer arrows
66(1)
Evaluating formulas
66(1)
Watching cell values
67(4)
Part 2 Harnessing the Power of Functions
Chapter 4 Understanding functions
71(10)
About Excel's functions
72(1)
The structure of a function
72(3)
Typing a function into a formula
75(1)
Using the Insert Function feature
76(2)
Loading the Analysis ToolPak
78(3)
Chapter 5 Working with text functions
81(28)
Excel's text functions
81(2)
Working with characters and codes
83(3)
The CHAR function
83(2)
The CODE function
85(1)
Converting text
86(3)
The LOWER function
86(1)
The UPPER function
86(1)
The PROPER function
87(1)
The NUMBERVALUE function
87(1)
The ARRAYTOTEXT function
88(1)
The VALUETOTEXT function
88(1)
Formatting text
89(4)
The DOLLAR function
89(1)
The FIXED function
90(1)
The TEXT function
90(2)
Displaying when a workbook was last updated
92(1)
Manipulating text
93(7)
Removing unwanted characters from a string
93(1)
The TEXTDOIN function: Concatenating text with a delimiter
94(1)
The REPT function: Repeating a character or string
95(2)
Extracting a substring
97(1)
Converting text to sentence case
98(1)
A date-conversion formula
99(1)
Searching for substrings
100(4)
The FIND and SEARCH functions
100(1)
Extracting a first name or last name
101(1)
Extracting first name, last name, and middle initial
102(1)
Determining the column letter
103(1)
Substituting one substring for another
104(5)
The REPLACE function
104(1)
The SUBSTITUTE function
105(1)
Removing a character from a string
105(1)
Removing two different characters from a string
106(1)
Removing linefeeds
106(3)
Chapter 6 Working with logical and information functions
109(30)
Adding intelligence with logical functions
109(20)
Using the IF function
110(3)
Performing multiple logical tests
113(7)
Combining logical functions with arrays
120(9)
Getting data with information functions
129(10)
The CELL function
130(2)
The Error.Type function
132(2)
The INFO function
134(1)
The SHEET and SHEETS functions
134(1)
The IS functions
135(4)
Chapter 7 Working with lookup functions
139(22)
Taking a look at Excel's lookup functions
139(1)
Understanding lookup tables
140(1)
The CHOOSE function
141(3)
Determining the name of the day of the week
141(1)
Determining the month of the fiscal year
142(1)
Calculating weighted questionnaire results
143(1)
Integrating CHOOSE and worksheet option buttons
143(1)
Looking up values in ranges or tables
144(11)
The VLOOKUP function
145(1)
The HLOOKUP function
146(1)
Returning a customer discount rate with a range lookup
146(1)
Returning a tax rate with a range lookup
147(1)
Finding exact matches
148(2)
Advanced lookup operations
150(1)
The Match and Index functions
150(5)
Modern lookups with XLOOKUP
155(6)
The XLOOKUP function
156(1)
Looking up a customer account number with XLOOKUP
157(1)
Combining exact-match lookups with in-cell drop-down lists with XLOOKUP
157(2)
Using any column as the lookup column with XLOOKUP
159(1)
Creating multiple-column lookups with XLOOKUP
159(2)
Chapter 8 Working with date and time functions
161(28)
How Excel deals with dates and times
161(20)
Entering dates and times
162(1)
Excel and two-digit years
163(1)
Using Excel's date functions
164(1)
Returning a date
165(2)
Returning parts of a date
167(10)
Calculating the difference between two dates
177(4)
Using Excel's time functions
181(8)
Returning a time
181(1)
Returning parts of a time
182(2)
Calculating the difference between two times
184(5)
Chapter 9 Working with math functions
189(32)
Excel's math and trig functions
189(4)
Understanding Excel's rounding functions
193(5)
The ROUND function
193(1)
The MROUND function
193(1)
The ROUNDDOWN and ROUNDUP functions
194(1)
The CEILING.MATH and FLOOR.MATH functions
195(1)
Calculating Easter dates
195(1)
The EVEN and ODD functions
196(1)
The INT and TRUNC functions
196(1)
Using rounding to prevent calculation errors
196(2)
Summing values
198(3)
The SUM function
198(1)
The SUMIF function
199(1)
Summing only the positive or negative values in a range
199(1)
The SUMIFS function
199(1)
Calculating cumulative totals
200(1)
The MOD function
201(3)
A better formula for time differences
201(1)
Summing every nth row
202(1)
Determining whether a year is a leap year
202(1)
Creating ledger shading
203(1)
Generating random numbers
204(5)
The RAND function
205(2)
The RANDBETWEEN function
207(1)
The RANDARRAY function
207(1)
Creating increasing random numbers with the SEQUENCE function
208(1)
Programming with Excel's formula language
209(12)
Using LET to create formula variables
209(3)
Using LAMBDA to create custom functions
212(2)
Using a LAMBDA to process an array
214(7)
Part 3 Building Business Formulas
Chapter 10 Implementing basic business formulas
221(22)
Pricing formulas
221(7)
Price markups
221(2)
Price discounts
223(2)
Break-even point
225(3)
Financial formulas
228(5)
Sales ratios
228(1)
Cost of goods sold
229(1)
Gross margin
230(1)
Net margin
230(1)
Fixed-asset ratios
231(2)
Inventory formulas
233(3)
Inventory ratios
233(1)
Inventory management formulas
234(2)
Liquidity formulas
236(7)
Accounts receivable ratios
236(1)
Accounts payable ratios
237(2)
Working capital
239(1)
Liquidity ratios
240(2)
Liquidity Index
242(1)
Chapter 11 Building descriptive statistical formulas
243(16)
Understanding descriptive statistics
243(2)
Counting items
245(2)
The COUNT function
245(1)
The COUNTA function
245(1)
The COUNTBLANK function
246(1)
The COUNTIF function
246(1)
The COUNTIFS function
246(1)
Calculating averages
247(3)
The AVERAGE function
247(1)
The AVERACEIF function
247(1)
The AVERACEIFS function
248(1)
The MEDIAN function
248(1)
The MODE function
249(1)
Calculating the weighted mean
249(1)
Calculating extreme values
250(2)
The MAX and MIN functions
250(1)
The LARGE and SMALL functions
251(1)
Performing calculations on the top k values
252(1)
Performing calculations on the bottom k values
252(1)
Working with rank and percentile
252(2)
Calculating rank
253(1)
Calculating percentile
253(1)
Calculating measures of variation
254(2)
Calculating the range
254(1)
Calculating the variance
254(1)
Calculating the standard deviation
255(1)
Working with frequency distributions
256(3)
Chapter 12 Building inferential statistical formulas
259(22)
Understanding inferential statistics
259(1)
Sampling data
260(4)
Extracting a periodic sample
261(2)
Extracting a random sample
263(1)
Determining whether two variables are related
264(3)
Calculating covariance
264(2)
Calculating correlation
266(1)
Working with probability distributions
267(10)
Calculating probability
267(3)
Discrete probability distributions
270(2)
Understanding the normal distribution and the NORM. DIST function
272(5)
Determining confidence intervals
277(1)
Hypothesis testing
278(3)
Chapter 13 Applying regression to track trends and make forecasts
281(42)
Choosing a regression method
282(1)
Using simple regression on linear data
282(23)
Analyzing trends using best-fit lines
283(8)
Making forecasts
291(14)
Using simple regression on nonlinear data
305(15)
Working with an exponential trend
305(5)
Working with a logarithmic trend
310(3)
Working with a power trend
313(3)
Using polynomial regression analysis
316(4)
Using multiple regression analysis
320(3)
Chapter 14 Building loan formulas
323(20)
Understanding the time value of money
323(2)
Calculating a loan payment
325(5)
Loan payment analysis
325(1)
Working with a balloon loan
326(1)
Calculating interest costs, part I
327(1)
Calculating the principal and interest
328(1)
Calculating interest costs, part II
329(1)
Calculating cumulative principal and interest
329(1)
Building a loan amortization schedule
330(3)
Building a fixed-rate amortization schedule
331(1)
Building a dynamic amortization schedule
332(1)
Calculating the term of a loan
333(2)
Calculating the interest rate required for a loan
335(2)
Calculating how much you can borrow
337(6)
Chapter 15 Working with investment formulas
343(14)
Working with interest rates
343(3)
Understanding compound interest
343(1)
Nominal versus effective interest
344(1)
Converting between the nominal rate and the effective rate
345(1)
Calculating the future value
346(2)
The future value of a lump sum
346(1)
The future value of a series of deposits
347(1)
The future value of a lump sum plus deposits
348(1)
Working toward an investment goal
348(9)
Calculating the required interest rate
348(1)
Calculating the required number of periods
349(1)
Calculating the required regular deposit
350(1)
Calculating the required initial deposit
351(1)
Calculating the future value with varying interest rates
351(6)
Chapter 16 Building discount formulas
357(22)
Calculating the present value
358(4)
Taking inflation into account
358(1)
Calculating present value using PV
359(1)
Income investing versus purchasing a rental property
360(1)
Buying versus leasing
361(1)
Discounting cash flows
362(5)
Calculating the net present value
363(1)
Calculating net present value using NPV
364(1)
Net present value with varying cash flows
365(1)
Net present value with nonperiodic cash flows
366(1)
Calculating the payback period
367(3)
Simple undiscounted payback period
367(1)
Exact undiscounted payback point
368(1)
Discounted payback period
369(1)
Calculating the internal rate of return
370(9)
Using the IRR function
370(1)
Calculating the internal rate of return for nonperiodic cash flows
371(1)
Calculating multiple internal rates of return
372(7)
Part 4 Building Business Models
Chapter 17 Analyzing data with tables
379(20)
Sorting a table
379(4)
Sorting on part of a field
380(1)
Sorting without articles
381(1)
Sorting table data into an array, part I: The SORT function
382(1)
Sorting table data into an array, part II: The SORTBY function
383(1)
Filtering table data
383(6)
Using complex criteria to filter a table
384(3)
Entering computed criteria
387(2)
Filtering table data with the FILTER function
389(1)
UNIQUE
389(1)
Referencing tables in formulas
389(4)
Using table specifiers
390(1)
Entering table formulas
391(2)
Excel's table functions
393(6)
About table functions
393(2)
Using DAVERAGE
395(1)
Using DGET
396(3)
Chapter 18 Analyzing data with PivotTables
399(18)
Working with Pivot Table subtotals
399(2)
Hiding Pivot Table grand totals
400(1)
Hiding Pivot Table subtotals
400(1)
Customizing the subtotal calculation
401(1)
Changing the value field summary calculation
401(8)
Using a difference summary calculation
402(1)
Toggling the difference calculation with VBA
403(1)
Using a percentage summary calculation
404(2)
Using a running total summary calculation
406(2)
Using an index summary calculation
408(1)
Creating custom PivotTable calculations
409(5)
Creating a calculated field
411(1)
Creating a calculated item
412(2)
Using Pivot Table results in a worksheet formula
414(3)
Chapter 19 Using Excel's business modeling tools
417(22)
Using what-if analysis
417(6)
Setting up a one-input data table
418(2)
Adding more formulas to the input table
420(1)
Setting up a two-input data table
421(2)
Editing a data table
423(1)
Working with Goal Seek
423(7)
How does Goal Seek work?
424(1)
Running Goal Seek
424(2)
Optimizing product margin
426(1)
A note about Goal Seek's approximations
427(1)
Performing a break-even analysis
428(1)
Solving algebraic equations
429(1)
Working with scenarios
430(9)
Understanding scenarios
431(1)
Setting up your worksheet for scenarios
432(1)
Adding a scenario
432(1)
Displaying a scenario
433(1)
Editing a scenario
434(1)
Merging scenarios
435(1)
Generating a summary report
435(2)
Deleting a scenario
437(2)
Chapter 20 Solving complex problems with Solver
439(28)
Some background on Solver
439(2)
The advantages of Solver
440(1)
When do you use Solver?
440(1)
Loading Solver
441(1)
Using Solver
442(3)
Adding constraints
445(1)
Saving a solution as a scenario
446(1)
Setting other Solver options
447(5)
Selecting the method Solver uses
447(1)
Controlling how Solver works
448(3)
Working with Solver models
451(1)
Making sense of Solver's messages
452(4)
Displaying Solver's reports
456(11)
The Answer report
456(2)
The Sensitivity report
458(1)
The Limits report
459(8)
Index 467
Paul McFedries is an Excel expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 100 books to his credit, which combined have sold more than 4 million copies worldwide. His titles include the Que Publishing books My Office 2016, Windows 10 In Depth (with coauthor Brian Knittel), and PCs for Grownups, as well as the Sams Publishing book Windows 7 Unleashed. Please drop by Paul's personal website at paulmcfedries.com or follow Paul on Twitter (twitter.com/paulmcf) and Facebook (facebook.com/PaulMcFedries).
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97801375593986e.html
Märksõnad: