Detail kurzu
Microsoft Excel III - For Advanced Users
IT LEARNING SLOVAKIA, s.r.o.
Popis kurzu
A pivot table allows for many different settings for easier pivot table design. However, for day-to-day data processing it is not enough. Very often it happens, that before using a pivot table source data should be optimized. It happens, for example, when there are two tables containing data, or when we need to process data by type. Advanced users are able to prepare source data in such a way that a pivot table can be smoothly designed.
Microsoft Excel III is intended for those who use Excel for data analysis on day-to-day basis. The course focuses on pivot table settings and preparing the data to be analyzed by means of functions (logical functions and VLOOKUP particularly). At the end, the students will learn how to prepare simple forms for users, so as to save work when processing them in later stages.
Obsah kurzu
===Microsoft Excel III for Advanced Users - How to analyze data by means of pivot tables - Grouping data - Grouping text fields - Grouping dates and times - Grouping data by number ranges - Showing values as a percentage of - Grand total - Column or row total - Parent total - Difference of values - Pivot chart - A chart vs. a pivot chart - Chart options overviews - How to choose a proper chart type - How to create a dashboard - What is a dashboard and how to create it? - Conditional formatting and sparklines - How to use a slicer - Functions - Statistics calculations background - Adding values and summing the values in a range that meets criteria SUM, SUMIF, SUMIFS - Counting of cells COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK - Average calculations by AVERAGE, AVERAGEIF, AVERAGEIFS functions - Rounding functions - Basic rounding functions ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC - Rounding to the nearest multiple of 100 and 1000 - Rounding a number to a specific multiple by MROUND, CEILING, FLOOR, CEILING.MATH and FLOOR.MATH functions - Text strings - Splitting text into several columns - CONCATENATE function - Operator & - Logical functions - How to nest a function within a function and how to check it - IF function advanced features and Nested IF function - Evaluating multiple criteria by AND and OR functions - Checking cell value by Information functions - Using Information Functions and IF function to create a formula with cell value evaluation - Date and time calculations - What to do if Excel does not work with date values - Date and time parts for pivot tables - Calculation of a difference between two dates in days, workdays, weeks, months and years - Adding days, workdays, months and years - VLOOKUP function - Looking up values in another table - Looking up values in ranges - Looking up missing values in two almost identical tables - Using functions in conditional formatting - Goal Seek feature - Collaboration on Excel data - Workbook protection - Encryption with a password - Protection of workbook structure - Sheet protection - Cell ranges protection - Workbook sharing - Data validation tool - Basic principles ===Formulas used for data validationCieľová skupina
pokročilýHodnotenie
Organizátor
Podobné kurzy
podľa názvu a lokality
