北京最專業(yè)的Excel高端培訓(xùn),*的Excel專業(yè)培訓(xùn)機(jī)構(gòu),
北京Microsoft Excel Advanced(12 hours)
課程簡介
--------------------------------------------------------------------------------
Microsoft Excel has a range of smart functions that can be used to calculate a variety of outcomes. This course will cover many common and useful functions in Excel and explore how to develop custom formulas with functions to produce the results which financial professionals may need.
培訓(xùn)對象
--------------------------------------------------------------------------------
At Course Completion
After completing this course, students will be able to:
Use date and time functions to handle time-related information
Use database functions, information functions, lookup functions, logical functions to handle large amount of data stored in Excel
Use logical, math and trigonometry functions to process data
Use financial functions to handle Accounting Issue.
培訓(xùn)大綱
--------------------------------------------------------------------------------
Microsoft Excel Advanced
Course Code：ExcelAdv
Introduction
This advanced training course will give financial professionals the ability to manipulate data and to share workbooks in a team workgroup environment. Participants will learn how to audit data ,use the workgroup features ,Import and export data in different formats and create simple macros in Microsoft Excel .This course will introduce the advanced Excel techniques which participants can further utilize Excel’s powerful features to handle different financial statements.
At Course Completion
After completing this course, students will be able to:
Sort and filter financial figures
Manage and audit spreadsheets
Share accounting and financial statements with others
Customize and automate Excel using macros and toolbars
Key Topics
1. Organizing and Analyzing data
1) Use subtotals
2) Define and apply advanced filters
3) Group and outline data
4) Use data validation
5) Create and modify list ranges
6) Using scenarios
7) Perform data analysis using automated tools
8) Create PivotTable and PivotChart reports
9) Use lookup and Reference functions
10) Use database functions
11) Trace formula precedents, dependents and errors
12) Watch and evaluate formulas
13) Using named ranges
14) Structure work books using XML
2. Formatting Data and Content
1) Create and Apply custom data formats
2) Use conditional formatting
3) Format and resize graphics
4) Format charts and diagrams
3. Collaborating
1) Protect cells, worksheets, and workbooks
2) Apply workbook security settings
3) Share workbooks
4) Merge workbooks
5) Track, accept ,and reject changes to workbooks
4. Managing Data and Workbooks
1) Import data to Excel
2) Export data from Excel
3) Publish and edit Web worksheets and workbooks
4) Create and edit templates
5) Consolidate data
6) Define and modify workbook properties
7) Customizing Excel
8) Customize toolbars and menus
9) Create, edit ,and run macros
10) Modify Excel default settings
Microsoft Excel Formulas and Functions
Course Code：Excel Fun
Introduction
Microsoft Excel has a range of smart functions that can be used to calculate a variety of outcomes. This course will cover many common and useful functions in Excel and explore how to develop custom formulas with functions to produce the results which financial professionals may need.
At Course Completion
After completing this course, students will be able to:
Use date and time functions to handle time-related information
Use database functions, information functions, lookup functions, logical functions to handle large amount of data stored in Excel
Use logical, math and trigonometry functions to process data
Use financial functions to handle Accounting Issue.
Key topics
1. Naming Cells and Ranges
1) Creating a Name
2) Making a name list
3) Using the name for calculation
2. Address Notation
1) Relative address
2) Absolute Address
3. Database functions(Used to analyze data stored in lists or databases)
1) DAVERAGE
2) DCOUNT
3) DCOUNTA
4) DMAX
5) DMIN
6) DPRODUCT
7) DSUM
4. Date and Time Functions(Used to convert a serial number to different date and time format)
1) TODAY
2) YEAR,MONTH,DAY
3) DATE
4) TIME
5) HOUT,MINUTE,SECOND
5. Financial Functions(Used to calculate the Payment for a loan or investment)
1) PMT
2) IPMT
3) PPMT
4) PV
5) FV
6. Information Functions(Used to checks the type of value an d returns TRUE or FALSE depending on the outcome)
1) COUNTBLANK
2) ISBLANK
3) ISERR
4) ISERROR
5) ISLOGICAL
6) ISNONTEXT
7) ISNUMBER
8) ISTEXT
7. Logical Functions(Used to perform a logical test or comparison for a value)
1) AND
2) IF
3) OR
4) TEUE
5) FALSE
8. Lookup Functions(Used to look for a value in a reference table and return the desired result from the table
1) HLOOKUP
2) VLOOKUP
3) LOOKUP
9. Math and Trigonometry Functions(Used to perform some Math’s calculations)
1) ABS
2) CONUTIF
3) INT
4) MOD
5) PRODUCT
6) RAND
7) ROUND
8) ROUNDUP
9) ROUNDDOWN
10) SUMIF
11) SUMPROCUCT
12) TRUNC
10. Statistical Functions (Used to perform the statistical analysis)
1) AVERAGEA
2) COUNTA
3) MAXA
4) MINA
11. Text Functions(Used to apply the text format to the data in Excel )
1) CONCATENATE
2) DOLLAR
3) EXACT
4) LEFT
5) RIGHT
6) LEN
7) SUBSTITUTE
8) TEXT
9) TRIM
10) UPPER
11) LOWER
12) VALUE
Microsoft Excel (Fundamentals of Macros and VBA)
Course Code：EMVBA
Introduction
The training course is designed to give advanced users of Microsoft Excel an understanding of how to automate procedures using excel Macros and Microsoft Visual Basic for Applications (VBA).This course concentrates on introductory programming topics and good programming practices, showing how these programming techniques can be used to model and manipulate date. Participants will be encouraged to write programs to meet their specific needs, as there is an increase in the need to analyze and develop ever more complex “what if” scenarios.
At Course Completion
After completing this course, students will be able to:
Record macros
Write control structures
Build procedures, which use variables
Program components within Excel
Key topics
1. Introduction to Excel Macros
1) What is a macro?
2) Record a macro
3) Urn a macro
4) Relative references
5) Assign a macro to a button on a worksheet
6) Assign a macro to a button on a toolbar
7) Button image editor
2. Editing Excel Macros
1) The Visual basic editor
2) The project explorer
3) The code window
4) Procedures
5) Insert comments
3. Introduction to VBA
1) Development environment
2) Menu and toolbars
4. Structure of Procedures
1) Call procedures
2) Organize procedures
5. Working with objects
1) Introduction to objects and collections
2) Control objects
3) Use the object browser
4) Use objects, properties and methods
6. VBA codes
1) If …Then …Else
2) Select case
