Course Data AnalystLC-ANALYSIS

Excel as a tool in data analysis

1. Using Excel effectively
   • Keyboard shortcuts
   • Naming cells
   • Tables
2. Formulas and most commonly used functions
   • Logical functions
   • Search functions
   • Decision support functions
   • Mathematical and statistical functions
   • Formulas and array functions
3. Preparing data for analysis
   • Creating tables – Good practice
   • Viewing data: auto-filters and advanced filter
   • Removing duplicates
   • Text as columns tool
   • Text functions
   • Eliminating data errors
4. Collaboration in Excel and creating worksheets for other users
   • Data validation
   • Protecting a worksheet and locking cells
   • Hiding formulas and securing a worksheet
   • Creating your own forms and using formants
5. Pivot tables
   • Creating a pivot table
   • Modifying a pivot table
   • Filtering and sorting
   • Slicers
   • Grouping data
   • Benchmarking using a pivot table
   • Fields and calculation elements
6. Extending Pivot Tables – Power Pivot
   • Adding Excel tables to the model
   • Creating joins between tables
   • Pulling data from other sources into the data model
   • Creating hierarchies
   • Formatting data
   • Sorting, filtering and hiding data
7. Situational analysis and optimisation
   • Scenario analysis – Scenario manager
   • Search for a result
   • Solver
8. Using external data
   • Importing data from external databases (MS Access, SQL Server)
   • Importing data from text files
   • Importing data from websites
   • Exporting data
9. Collecting and transforming data using Power Query
   • Importing data (e.g. from the Internet and databases)
   • Preparing data for analysis – Introduction to M language formulas
   • Transforming reports into a form enabling further analysis
10. Visualizing data with conditional formatting
    • Using built-in templates
    • Creating rules based on formulas
    • Illustrating shares, discrepancies and exceptions

Data visualisation

1. Data visualization with charts
   • Types of charts and their uses
   • Chart styles – Built-in and custom charts
   • Irregular charts
   • Charts based on grouped data
   • Frequency charts
   • Time charts
   • Pivot charts
2. Interactive scenario analysis – Dashboard
   • Control of report parameters using formants
   • Charts with selectable series displayed
   • Coupling of tables and pivot charts using slicers

Statistical methods

1. Analysis ToolPak – launching and usage
2. Introduction to statistical methodology
   • Basic concepts: population, sample, random variables, hypothesis, statistical significance
   • Sampling – Principles of data collection
3. Descriptive statistics
   • Basic functions and descriptive statistics: mean, median, variance, standard deviation, skewness, kurtosis
   • Frequency, relative and cumulative frequency, quantiles
   • Histograms and resolution series
   • Trend analysis using graphs
4. Mathematical statistics
   • Basic concepts: probability, distribution, most frequently used distributions (normal, exponential, t-Student, chi-square)
   • Generation of random numbers with different distributions
   • Most important statistical tests in applications: t-Student test, z-test, F-test, chi-square test, analysis of variance
   • Searching for potential relations between data: covariance and correlation – Calculation and interpretation
   • Trend analysis and prediction: regression analysis
   • Data waveform analysis and forecasting – Moving average, time series smoothing, exponential smoothing
5. What’s next?
   • Presentation of data mining methods using machine learning

MS Access

1. Introduction to Microsoft Access 2016
   • Uses and capabilities of the program
   • Building Access databases: forms, tables, queries, reports
   • Basics of using ready-made databases created in Access
   • Import/export of data between Excel and Access
   • When to use Access and when to use Excel?
2. Fundamentals of database design
3. Creating tables
   • Data types
   • Relationships between tables, foreign keys and primary keys
   • Default values
4. Queries
   • Query Wizard
   • Query Design View
5. Forms
   • Form Wizard
   • Form Design View
6. Reports
   • Report Wizard
   • Report Design View
7. Printing data and reports

SQL language in Access and MS SQL Server

1. Relational databases – basics
   • The concept of relations
   • Table, row, column
   • Key, primary key
   • Foreign keys and relationships between tables.
2. Basic views and operations in a database program (on the example of MS SQL Server and MS Access).
3. SQL Language
   • Simple Queries – SELECT structure
   • Functions and operators
   • Row selection – WHERE clause
   • Ordering of data – ORDER BY clause
   • TOP clause
   • Joining multiple tables
   • Row grouping
   • Aggregating functions
   • Selecting groups of rows – HAVING clause
   • Subqueries
   • Theory and multiplicity operations
4. Database schema
   • Data types
   • Creating tables – CREATE TABLE
   • Consistency ties
   • Row autonumbering.
5. Adding and modifying data
   • Adding data – INSERT
   • Modifying data – UPDATE
   • Deleting data – DELETE