Curriculum
ADVANCED SEARCH AND CONDITIONAL FUNCTIONS
| ➢ Environment and protection of environment elements (cells, cell ranges, sheets and workbooks) |
| ➢ Preparing a database for conversion to PDF format. |
| ➢ Theory and application of: VLOOKUP(), HLOOKUP(), LOOKUP(), XLOOKUP(), INDEX(), MATCH(), IFERROR(), SUMIF(), COUNTIF(), ISBLANK(), DMAX() functions. Application examples. |
| ➢ Applications of matrix formulas for advanced calculations. |
| ➢ Use of advanced filters, dynamic array functions: Filter(), Unique() and Sort(). |
| ➢ Example of a Kardex chart using array formulas. Using OFFSET() |
| ➢ Database functions (using AND and OR criteria) |
| ➢ The reference operators. |
| ➢ Use of the Text operator to concatenate characters. |
| ➢ The functions: Roundup(), Roundup(), k.smallest(), k.largest(). |
| ➢ Use of conditional formatting that changes the format of cells according to their content. |
| ➢ Tables, pivot tables and pivot charts: design, percentage applications (row, column and grand total), calculated field. |
| ➢ Writing formulas within drawing objects. |
| ➢ Creation of Dashboards to optimize and manage databases: design of data presentation, filters in pivot tables, dynamic charts, connections with other tables, changing slicer styles and time scales. |
FINANCIAL FUNCTIONS AND DATA ANALYSIS – APPLICATIONS
| ➢ Functions: PM, NPER, IPMT, PRIMTP. |
| ➢ Amortization and interest tables. |
| ➢ Example of a dynamic amortization table with graphical applications and use of database management functions. |
| ➢ Profitability indicators: Net Present Value: NPV, Internal Rate of Return: IRR, Benefit/Cost Ratio: Results analysis – application example using a projected cash flow. |
| ➢ Simulation of process scenarios. |
| ➢ Sensitivity analysis to cost and price variations. |
STATISTICAL FUNCTIONS – APPLICATIONS
| Statistical Data Analysis: |
| ➢ Use of statistical functions: average, max, min, count, counta, median, mode, var, dest, sumif, countif, sumifset, countifset, frequency, linear.estimate, trend, k.largest, k.smallest, rank, forecast. |
| ➢ Use of matrix functions. Application examples. |
| ➢ Statistical graphs: bar and line graphs combined with multiple scales, Frequency Histogram, Trend Lines (forecasts). |
| Data entry error detection: |
| ➢ Use of ES functions, application examples. |
| ➢ Data validation: validation of dates, texts, numbers, times, lists, text length and custom validation. |
| ➢ Detection and removal of duplicate data, removal of spaces between words, blank spaces, and records with blank fields. Comparison with the Unicos() function. |
SCENARIO MANAGEMENT, GOAL SEEK, AND DATA TABLE
| ➢ Hypothesis Analysis: WHAT IF analysis with Goal Seek, Tables and Scenarios. |
| ➢ Data tables in scenario management. |
| ➢ Application of Scenarios: normal, optimistic and pessimistic. |
| ➢ Solver function. |
| ➢ Formulation and solution of problems in linear programming (maximization and value). |
| ➢ Using the Solver tool to solve the objective function (practical examples). |
PROGRAMMING USING FORMS, MACROS, RECORDER, AND VISUAL BASIC
| Using macros to automate instructions: |
| ➢ Activate the Programmer or Developer tab. |
| ➢ Use of relative references and macro security. |
| ➢ Using the macro recorder (practical examples). |
| ➢ The environment and creation of functions in Visual Basic. |
| ➢ Basic macros in the Visual Basic Editor. |
EXCEL ADDITIONS: POWER QUERY – POWER PIVOT
| General Database Concepts: |
| ➢ Entity-Relationship Criteria in data tables. |
| Power Query: |
| ➢ Extract databases (Text, Web, Access, Excel, etc.) |
| ➢ Connection with multiple sources (Query Editor) |
| ➢ Transformation of data tables using M language (Queries) |
| ➢ Application examples using Power Query environment commands |
| ➢ Database Normalization |
| ➢ Attach and Combine Tables |
| ➢ Remove column dynamization |
| Power Pivot: |
| ➢ Importing data from different formats and sources |
| ➢ Table modeling (Databases) |
| ➢ Some applications of DAX Functions |
| ➢ Metrics and KPI indicators |
| ➢ Multiple Pivot Tables |
| ➢ Dashboards with Excel |