101 Ready-to-Use Excel Formulas.pdfexcel formula r

文件名称: 101 Ready-to-Use Excel Formulas.pdf

所属分类: 微软认证

开发工具:

文件大小: 6mb

下载次数: 0

上传时间: 2019-07-08

提供者: sp***

下载 (6mb)

不能下载？报告错误

详细说明：excel formula ready for use. pdf101 Ready-to-USe excel Formulas Publishedby:jOhnWiley&SonsInc.,111RiverStreetHoboken,Nj07030-5774,www.wiley.com Copyright o 2014 by John Wiley sons, Inc, Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley sons, Inc, 111 River Street, Hoboken, N.J07030, (201)748-6011, fax(201)748-6008, or online at http://www.wileycom/go/permissions Trade marks: Wiley and the wiley logo are trademarks or registered trademarks of John Wiley Sons, Inc and/or its affiliates in the United states and other countries and may not be used without written permission microsoft and Excel are registered trademarks of the Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley sons, Inc. is not associated with any product or vendor mentioned in this book LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY. THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITII RESPECT TO TIIE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES. INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL ACCOUNTING OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE FURTHER READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the u.s. at 317-572-3993, or fax 317-572-4002. For technical support, please visit www.wiley.com/techsupport Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material inchuded with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a Cd or dvd that is not inc hided in the version you purchased, you may download this material at http:/booksupport.wileycomFormoreinformationaboutWileyproductsvisitwww.wiley.com Library of Congress Control Number: 2014935514 ISBN978-1-118-90268-4(pbk);ISBN978-1-118-90259-2(cbk)ISBN978-1-118-902899(cbk) Manufactured in the United States of america 10987654321 101 Ready-to-Use Excel Formulas Table of contents 1. Introduction 1. What You Need to Know 2. What You Need to have 3. How This Book Is organized 1. Chapter 1: Introducing Excel Formulas 2. Chapter 2: Common Mathematical Operations 3. Chapter 3: Manipulating Text with Formulas Chapter 4: Working with Dates and Times 5. Chapter 5: Performing Conditional Analysis 6. Chapter 6: Using Lookup Formulas 7. Chapter 7: Common Business and Financial Formulas 8.Chapter 8: Common Statistical Analysis 9. Chapter 9: Using Formulas with Conditional Formatting 4. Conventions in This book 1. What the icons mean About the Sample files 2. Chapter 1: Introducing Excel Formulas 1. Creating and Editing Excel Formulas 1. Methods for entering formulas 2. Editing a formula 2. USing Formula Operators Understanding the order of operator precedence Using nested parentheses 3, Relative versus absolute cell References 4. Using External Cell References 5. Formula calculation modes 6. Leveraging Excel Functions 1. Why to use Excel functions Understanding function arguments 3. Getting Ilelp from the Insert Function Wizard 7. Understanding Formula Errors 8. Using named ranges in formulas 1. Creating a named range 2. Working with the Name Box 3. Chapter 2: Common Mathematical Operations 1. Formula 1: Calculating Percent of Goal 1. How it works Alternative: Using a common goal 2. Formula 2: Calculating Percent Variance 1. How it works 2. Alternative: Simplified percent variance calculation 3. Formula 3: Calculating Percent Variance with Negative alues 1. How it works 4. Formula 4: Calculating a Percent Distribution 1. How it work 2. Alternative. percent distribution without a dedicated Total cell 5. Formula 5: Calculating a Running Total How it works 6. Formula 6: Applying a Percent Increase or Decrease to Values How it works 7. Formula 7: Dealing with Divide-by-Zero Errors How it works 8. Formula 8: Basic Rounding of Numbers How it works 9. Formula 9: Rounding to the Nearest Penny How it works 10. Formula 10: Rounding to significant Digits 1. How it works 1. Formula 11: Counting Values in a Range 1. How it works 12. Formula 12: Creating a Conversion Table 1. How it works 4. Chapter 3: Manipulating Text with Formulas 1. Formula 13: Joining Text Strings How it works 2. Formula 14: Setting Text to Sentence Case How it works 3. Formula 15: Removing Spaces from a Text String 1. How it works 4. Formula 16: Extract Parts of a Text String 1. How it works 5. Formula 17: Finding a particular Character in a Text String H works 2. Alternative: Finding the second instance of a character 6. Formula 18: Substituting Text String 1. How it work 7. Formula 19: Counting Specific Characters in a Cel 1. How it works 8. Formula 20: adding a line break within a formula 1. How it works 9. Formula 21: Clcaning Strange Characters from Text Ficlds How it works 10. Formula 22: Padding numbers with zeros How it works 11. Formula 23: Formatting the Numbers in a Text String 1. How it works 2. Alternative: Using the DOLLAR function 5. Chapter 4: Working with Dates and Times 1. Formula 24: Getting the Current Date and Time How it works 2. Formula 25: Calculating age 1. Ilow it works 3. Formula 26: Calculating the Number of days between Two Dates How it works 4. Formula 27: Calculating the Number of workdays between Two Dates How it works 2. Alternative. Using NETWORKDAYSINTI 5. Formula 28: Generate a List of Business Days Excluding Holidays How it work 6. Formula 29: Extracting Parts of a Date 1. How it worl 7. Formula 30: Calculating the Number of Years and months between dates 1. How it works 8. Formula 31: Converting Dates to Julian Date Formats How it works 9. Formula 32: Calculating the Percent of Year Completed and Remaining 1. How it works 10. Formula 33: Returning the Last Date of a Given Month How it works 2. Alternative: Using the EOMONTII function 11. Formula 34: Calculating the Calendar Quarter for a date How it works 12. Formula 35: Calculating the Fiscal Quarter for a Date How it works 13. Formula 36: Returning a Fiscal month from a Date 1. How it works 14. Formula 37: Calculate the Date of the nth Weekday of the Month How it works 15. Formula 38: Calculate the Date of the Last Weekday of the Month 1. How it works 16. Formula 39: Extracting Parts of a Time How it works 17. Formula 40: Calculating Elapsed Time How it works 18. Formula 41: Rounding til me values 1. How it works 19. Formula 42: Converting decimal Hours Minutes, or Seconds to a time 1. How it worl 20. Formula 43: adding hours minutes or seconds to a time 1. How it works 6. Chapter 5: Performing Conditional Analysis 1. Formula 44: Check to See Whether a Simple Condition Is Met How it works Formula 45: Checking for Multiple Conditions How it works 2. Alternative 1: Looking up values 3. Formula 46: Check Whether Condition l and Condition 2 Are met 1. How it works 2. Alternative 1: Referring to logical conditions in cells 4. Formula 47: check Whether Condition 1 or condition 2 Is 1. How it works 5. Formula 48 Sum all values That meet a Certain Condition 1. How it works Alternative I: Summing greater than zero 6. Formula 49: Sum all values that meet Two or more Conditions How it works 2. Alternative: SUMPRODUCT 7. Formula 50: Sum values That Fall between a Given Date Range 1. How it works 2. Alternative 1: SUMIFS 3. Alternative 2: SUMPRODUCT 8. Formula 51: Get a Count of values that meet a certain Condition How it works 2. Alternative: SUMPRODUCT 9. Formula 52: get a Count of values that meet two or more Conditions 1. How it works 2. Alternative. SUMPRODUCT 10. Formula 53: Get the Average of All Numbers That Meet a Certain Condition 1. How it works 2. Alternative 11. Formula 54: Get the Average of All Numbers That Meet Two or more conditions 1. How it works Alternative hapter 6: USing Lookup Formulas 1. Formula 55: Looking Up an Exact Value Based on a Left Lookup column 1. How it works 2. Formula 56: Looking Up an Exact Value Based on Any Lookup column 1. How it works Alternative: The looKuP function 3. Formula 57: Looking Up values Horizontally 1. How it work Alternative 4. Formula 58: Hiding Errors Returned by lookup Functions 1. Ilow it works 2. Alternative. The ISNA FU unction Formula 59: Finding thc Closest Match from a List of Banded values How it works Alternative: INDEX and MatCll 6. Formula 60: Looking Up Values from Multiple tables 1. How it works 7. Formula 61: Looking Up a value Based on a Two-Way Matrix How it works 2. Alternative: Using default values for MATCH 8. Formula 62: Finding a Value Based on Multiple criteria How it work 2. Alternative: Returning text with SUMPRODUCT 9. Formula 63: Finding the Last value in a Column 1. How it works 2. Alternative: Finding the last number using LOOKUP 10. Formula 64: Look Up the nth Instance of a Criterion How it works 11. Formula 65: Performing a Case-Sensitive Lookup 1. How it works 12. Formula 66: Letting the User Select How to Aggregate Data 1. Ilow it works

(系统自动生成,下载前可以参看下载内容)