Microsoft Excel – Advanced Methods and Functions

Struktura kurzu

Tato část není lokalizována

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