Course description
This course is assigned for skilled Microsoft Excel users, who want to use special tools, techniques, methods and functions.
Course structure
Conditions in formulas
- IF function, other logical functions
- CHOOSE function
- COUNTIF, SUMIF
- COUNTIFS, SUMIFS
- Specific Excel 2016 functions conditioned by using Office 365
Database functions
- Critaria tables
- Kind of criteria
- Best practices
Lookup functions, joins of datasets
- VLOOKUP, HLOOKUP functions
- INDEX function
- MATCH function
Retrieving data from external databases
- Basic ways of external data importing in Excel
- Definition of datasource
- Import using MS Query application
- Configuration of external data range
- Import using PowerQuery addin
Datamodel
- Relations between Excel Tables
- Definition of Datamodel using PowerPivot addin (introduction to topic)
- Relation between datamodel and PivotTable (extension of PivotTable calculation power)
PivotTable reports
- Concept of PivotTables
- Aggregation funcions, analytical calculations
- Additional calculations
- Calculated fields, ratio
- Data Consolidation using PivotTable
- Synchronized controling of PivotTables group
Scenarios
- Scenario as attribute of sheet
- Stored cells
Special mathematical methods
- Solver
- Goal seek
- Matrix formulas
Advanced operations with worksheets/workbooks
- Named ranges
- Pojmenování oblasti buněk na listu pomocí pole názvů
- Příkaz pro práci s názvy
- Global/local names
- Absolute/relative names
- Named formulas & constants
- Using of name in formula
- Comparation to labels
- 3D formulas (Workbook as 3D structure)
- Workbook window visibility
- Interactive dynamic graphical controls (Combobox, Checkbox, OptionButton, etc.) in forms, calculations, model and visual reports.
Worksheet/Worbook protection
- Exceptions for specified users
Macros
- Macro recorder
- Ways of macro execution
- Assignment to floating object in worksheet
