028 3824 5819 traincert@tdt-tanduc.com
Tiếng ViệtEnglish

Excel 2016 Expert Skills Part 1

This course will teach you how to use Excel’s advanced features to streamline your work. By the end of the course, you’ll be able to create sophisticated business tools.

Session 1: Tables and Ranges

  • Apply a simple filter to a range
  • Apply a top 10 and custom filter to a range
  • Apply an advanced filter with multiple OR criteria
  • Apply an advanced filter with complex criteria
  • Apply an advanced filter with function-driven criteria
  • Extract unique records using an advanced filter
  • Add totals using Quick Analysis
  • Add percentage and running totals Quick Analysis
  • Convert to range into a table and add a total row
  • Format a table using table styles and convert a table into a range
  • Create a custom table style
  • Sort a range or table by rows
  • Sort a range by columns
  • Sort a range or table by custom list
  • Name a table and create an automatic structured table reference

Session 2: Data Integrity, Subtotals and Validations

  • Split fixed width data using Text to Columns
  • Split delimited data using Text to Columns
  • Automatically subtotal a range
  • Create nested subtotals
  • Consolidate data from multiple data ranges
  • Use data consolidation to generate quick subtotals from tables
  • Validate numeric data
  • Create user-friendly messages for validation errors
  • Create data entry input messages
  • Add a table-based dynamic list validation
  • Use a function-driven custom validation to enforce complex business rules
  • Remove duplicate values from a range or table
  • Use a custom validation to add a unique constraint to a column

Session 3: Advanced Functions

  • Use the IF logic function
  • Use the SUMIF & COUNTIF functions to create conditional totals
  • Use the DATEIF function
  • Use the AND and OR functions to construct complex Boolean criteria
  • Understand calculation options
  • Concatenate string using the concatenation operator (&)
  • Use the TEXT function to format numerical values as strings
  • Extract text from fixed width strings using the LEFT, RIGHT and MID functions
  • Extract text from delimited strings using the FIND and LEN functions
  • Use a VLOOKUP function for an exact lookup
  • Use an IFERROR function to suppress error messages
  • Use a VLOOKUP function for an inexact lookup

Session 4: Using Names and the Formula Auditing Tools

  • Automatically create single-cell range names
  • Manually create single cell range names and named constants
  • Use range names to make formulas more readable
  • Automatically create range names in two dimensions
  • Use intersection range names and the INDIRECT function
  • Create dynamic formula-based range names using the OFFSET function
  • Create table-based dynamic range names
  • Create two linked drop-down lists using range names
  • Understand background error checking and error checking rules
  • Manually check a worksheet for errors
  • Audit a formula by tracing precedents
  • Audit a formula by tracing dependents
  • Use the watch window to monitor cell values
  • Use Speak Cells to eliminate data entry errors

Thời lượng: 02 ngày
Giảng viên: Microsoft Certified Trainer, MOS Master
Chứng nhận hoàn thành khóa học của Microsoft

KH 2
KH 3
KH 5