Vissza

Excel Advanced (angol nyelvű képzés)

Excel Advanced (angol nyelvű képzés)

Kód: E319A
Excel-Advanced.jpg
Időtartam:2 nap /16 tanóra
Nehézségi szint:
  • Haladó
68 000 Ft
(Bruttó ár: 86 360 Ft)
Kérdésem van!
Képzési forma

LIVE ONLINE

Képzés nyelve
angol
- + Jelentkezem

 

Course description

Macros. Multidimensional searches. Data tools. Dynamic array functions. Excel has many amazing features for the advanced users. Find all the hidden gems in Excel, become a real Excel ninja!

Prerequisites

Confident usage of Excel, knowledge of basic functions, absolute and relative references

Course outline

A little revision

  • Useful keyboard combinations, shortcuts
  • Tips and tricks for quicker work
  • Basic functions, references

Advanced functions, formulas

  • Reaching data in external files
  • Creating and managing links
  • Advanced formulas, special mathematical and statistical functions (SUMIFS, SUBTOTAL, etc.)
  • Special date and time functions
  • Database (D-) functions

Using special functions

  • Complex search functions (INDEX, MATCH, OFFSET)
  • Multidimensional searches, combining VLOOKUP with other search functions
  • Array functions and formulas
  • Solving special problems with array functions
  • Improving calculation speed, optimizing workbooks
  • Analysing calculation steps, error correction

Using named ranges

  • Name box
  • Manual and automatic naming of ranges
  • Managing names
  • The (INDIRECT) function
  • Clever usage of named ranges and dropdown lists

 

Data tools in Excel

  • Goal seek
  • Scenario manager
  • Data tables
  • Consolidate
  • grouping, subtotals
  • Advanced filters
  • Creating and using web queries
  • Data connections, Excel Data Model

Advanced pivot tables

  • Pivot table basics
  • Calculated fields
  • Calculated items
  • Displaying data as a percent of a given total
  • GETPIVOTDATA function
  • Sort and filter the Pivot table, special filtering
  • Slicer, Timeline
  • Attaching one slicer to many pivot tables
  • Pivot charts
  • Standalone pivot charts
  • Understanding the pivot cash
  • PowerPivot basics
  • Data connections, connecting to multiple data sources

Macros

  • A little theory
  • Using the macro recorder
  • Storing macros
  • Absolute and relative references in macros
  • What’s behind the macro? a quick peek at the code
  • Errors in macros
  • Running macros from keyboard shortcuts, buttons and menus
  • Macros on the internet

 

Hírlevél feliratkozás

Az Adatvédelmi szabályzatot megértettem és elfogadom, feliratkozom a Számalk hírlevelére.