Microsoft Excel Functions, Marcos & Data Commands

Sizin üçün oyun:

Google Play'də əldə edin


Yüklə 0.62 Mb.
səhifə1/11
tarix12.08.2018
ölçüsü0.62 Mb.
  1   2   3   4   5   6   7   8   9   10   11







ASA Research



Microsoft Excel Functions, Marcos &
Data Commands


exceleyes5.jpg
J. Carlton Collins

ASA Research - Atlanta, Georgia

770.734.0950

Carlton@ASAResearch.com


asar1
http://www.xl-faq.de/grafik/gxl.jpg http://www.xl-faq.de/grafik/gxl.jpg

Table of Contents

Course Information 3


Chapter 1 – All Functions 4
Chapter 2 – The If Function 17
Chapter 3 – Using Functions to Crunch & Clean Data 22
Chapter 4 – Data Commands 40
Chapter 5 - Macros 72
Chapter 6 – Miscellaneous 75
Chapter 7 – XML Data 83
Chapter 8 – Quick Tips 91
Chapter 9 – New in Excel 2007 94
Appendix - Instructor’s Biography 105

Excel Functions, Marcos & Data Commands
Course Information





Learning Objectives

To increase the productivity of accountants and CPAs using Excel’s functions, macros, & data commands

Course Level

Intermediate

Pre-Requisites

Good Familiarity with Microsoft Excel

Advanced Preparation

None

Presentation Method

Live lecture using full color projection systems and live Internet access with follow up course materials

Recommended CPE Credit

8 hours 

Handouts

Templates, checklists, web examples, manual

Instructors

J. Carlton Collins, CPA



AdvisorCPE is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be addressed to the national Registry of CPE Sponsors, 150 Fourth Avenue, Nashville, TN, 37219-2417. Telephone: 615-880-4200.
Copyright  August 2009, AdvisorCPE and Accounting Software Advisor, LLC
4480 Missendell Lane, Norcross, Georgia 30092 770.734.0450

All rights reserved. No part of this publication may be reproduced or transmitted in any form without the express written consent of AdvisorCPE, a subsidiary of ASA Research. Request may be e-mailed to marylou@advisorcpe.com or further information can be obtained by calling 770.734.0450 or by accessing the AdvisorCPE home page at: http://www.advisorcpe.com/


All trade names and trademarks used in these materials are the property of their respective manufacturers and/or owners. The use of trade names and trademarks used in these materials are not intended to convey endorsement of any other affiliations with these materials. Any abbreviations used herein are solely for the reader’s convenience and are not intended to compromise any trademarks. Some of the features discussed within this manual apply only to certain versions of Excel, and from time to time, Microsoft might remove some functionality. Microsoft Excel is known to contain numerous software bugs which may prevent the successful use of some features in some cases. AdvisorCPE makes no representations or warranty with respect to the contents of these materials and disclaims any implied warranties of merchantability of fitness for any particular use. The contents of these materials are subject to change without notice.
Contact Information:

J. Carlton Collins

carlton@asaresearch.com

770.734.0950

Chapter 1

All Functions




Introduction to Excel Functions
Excel Functions are preprogrammed commands that make the task of writing complex formulas easier. There are a total of 333 functions in Excel. These functions are separated into 11 categories as follows:


  1. Database Functions (12)

  2. Date and Time Functions (20)

  3. Engineering Functions (39)

  4. Financial Functions (53)

  5. Information Functions (17)

  6. Logical Functions (6)

  7. Lookup and Reference Functions (18)

  8. Math and Trigonometry Functions (59)

  9. Statistical Functions (80)

  10. Text Functions (27)

  11. External Functions (2)

Some Excel functions are more powerful than others and some are more relevant to the CPA than others. For example, most CPAs will find the IF, SUM, COUNT, SUBTOTAL, TEXT, and VLOOKUP are very relevant to the CPA while other engineering and trigonometry functions such as LOG, PI, RADIENS, DELTA, TAN, COMPLEX, and HAX2DEC are typically less relevant to CPAs. It has been my experience that the following 67 functions are most relevant to the CPA; therefore CPAs wishing to increase their command of Excel functions should concentrate on these functions first.
Carlton’s List of The Top 67 Functions Most Relevant to CPAs

Sorted By Carlton’s Opinion of the Most Useful


  1. IF

  1. SUM

  1. SUMIF

  1. COUNT

  1. COUNTA

  1. AVERAGE

  1. COUNTBLANK

  1. COUNTIF

  1. VALUE

  1. TEXT

  1. VLOOKUP

  1. HLOOKUP

  1. LOOKUP

  1. TRIM

  1. PROPER

  1. LOWER

  1. LEFT, LEFTB

  1. MID, MIDB

  1. RIGHT, RIGHTB

  1. FIND, FINDB

  1. REPLACE

  1. CONCATENATE

  1. CLEAN

  1. UPPER

  1. LEN, LENB

  1. SUBSTITUTE

  1. NOW

  1. TODAY

  1. MONTH

  1. DATE

  1. DAY

  1. YEAR

  1. WEEKDAY

  1. ROUND

  1. ROUNDDOWN

  1. ROUNDUP

  1. MAX

  1. MIN

  1. MEDIAN

  1. MODE

  1. PERCENTILE

  1. PERCENTRANK

  1. PMT

  1. NPV

  1. DSUM

  1. DCOUNT

  1. DCOUNTA

  1. AND

  1. OR

  1. CHOOSE

  1. TIME

  1. FV

  1. IRR

  1. YIELD

  1. CELL

  1. ERROR.TYPE

  1. INFO

  1. ISBLANK

  1. ISNA

  1. GETPIVOTDATA

  1. HYPERLINK

  1. TRANSPOSE

  1. ABS

  1. RAND

  1. RANDBETWEEN

  1. CONFIDENCE

  1. REPT











Dostları ilə paylaş:
  1   2   3   4   5   6   7   8   9   10   11
Orklarla döyüş:

Google Play'də əldə edin


Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2017
rəhbərliyinə müraciət

    Ana səhifə