Data AnalysisLC-ANALYSIS

Currently there are no scheduled dates

London
  • 20.06 - day classes (4-days classes, week break, 4-days classes)

Also available on demand, at time and place convenient to you, for groups of at least 6 participants.

exempt from VAT

Price: £ 1390

ability to pay in 3 installments

Categories: Databases and SQL, Access


Data Analysis


A solid course of data analysis
Excel, Access, SQL, statistics, visualization

  • Practical, hand-on course,
  • taught by experienced analysts and experts,
  • analysing financial and business data,
  • wide variety of tools: from visual data expoloration to statistical methods and SQL.
  • PRICE 1390 GBP
    • instalment payment plans available for private persons
    • additional information can be found here


Our course is intended for:
  • graduates, junior analysts, people who want to become analysts or broaden their existing skill-set
  • employees of all industries, including public institutions, dealing or planning to deal with data processing,
  • people who use Excel and other office software but want to know more

Requirements: from participants we do not require any experience in data analysis, but only basic MS Excel skills.

Valuable knowledge and skills

The course is taught by instructors who have many years experience not only in MS Office programs or databases but also in practical data analysis, finances and statistics. They are professionals, teaching in a lively manner, introducing many realistic examples.

The main idea of this training course is to introduce the wide variety of tools and techniques of data analysis and working with databases. We focus our training on practical exercises – participants have an opportunity to practise the discussed techniques on realistic data, similar to the data they work with. During the course we discuss:

  • the most important Excel tools and techniques of managing data (including Pivot Tables, Solver and Analysis Toolpak).
  • statistical methods of data analysis
  • data virtualization and creating interactive visualizations in Excel
  • using databases in MS Access and MS SQL Server
  • SQL: database language

After completing the course the students will be able to:

  • analyse large amounts of data
  • use the most important tests and statistical methods
  • make predictions and analyse trends on the basis of existing data
  • keep MS Access and MS SQL Server databases up to date

On the completion of the course the students receive course completion certificate.

Theoretical knowledge is important but practice matters most.

  • during the course particpants analyse the real data,
  • solid training materials, which enable students to learn easily both at home and during the class

If you need more information or have any questions regarding the course please contact our office.

Duration

64h

Agenda

Excel as a tool in data analysis

  1. How to use Excel effectively
    • Introduction to Excel
    • Formatting and conditional formatting
    • Relative and absolute cell references
    • Naming cells
    • Tables
  2. Formulas and the most common functions
    • Data and time functions
    • Logical functions (including if conditional function)
    • Information functions
    • Text functions
    • Lookup functions
    • Decision making functions
    • Mathematical and statistical functions
    • Database functions
    • Financial functions
    • Array formulas
  3. Databases in Excel
    • Table design- good practices
    • Exploring data: sorting and filtering
    • Removing duplicates
    • Data consolidation
    • Subtotals and outlines
  4. Collaboration in Excel and sharing worksheets with other users
    • Data validation
    • Drop-down lists
    • Worksheet and cell protection
    • Hidden formulas and worksheet protection
    • Custom forms and controls
  5. Pivot Tables
    • Creating Pivot Table
    • Pivot Table modifications
    • Filtering and sorting; report filter
    • Slicers
  6. What-If analysis
    • Managing scenario manager
    • Data tables
  7. Using external data
    • Importing data from external databases
    • Importing data from text files
    • Exporting data

Statistical Methods

  1. Analysis Toolpak – running and usage
  2. Introduction to statistical methodology
    • Basic terms: population, sample, random variables, hypothesis, statistical significance
    • Random sampling
    • Collecting data, creating questionnaires and surveys
  3. Descriptive statistics
    • Basic functions of descriptive statistic: average, standard deviation, variance, median, skewness, kurtosis
    • Frequency, relative and cumulative frequency; mode and quantiles
    • Histograms and frequency distributions
    • Data visualization and descriptive statistics visualization: histograms, pivot tables, sparklines
    • Using charts in trend analyzing
  4. Mathematical statistics
    • Basic terms: probability, probability distribution, the most common distributions (normal distribution, Student’s t distribution, Chi-squared distribution)
    • Generating random numbers from different distributions
    • The most important statistical tests in practice: Student’s t test, z- test, F -test, Chi-squared test
    • Examining relations between data:
      • Covariance and correlation – calculation and interpretation
      • Variance analysis
      • Trends prediction and examination: regression analysis.
    • Time series and smoothing – predictions, moving average, curve fitting, exponential smoothing.

Data visualization

  1. Charts
    • Types of charts and how to use them
    • Chart styles– templates and custom
    • Custom charts
    • Charts on grouped data
    • Frequency charts
    • Charts with time series
  2. Pivot Charts
  3. Excel Pivot Table Slicers
  4. Interactive Scenario Analysis
    • Controls
    • Interactive charts, selecting series, charting on-the-fly
    • Practical examples

MS Access

  1. Introduction to Microsoft Access 2010
    • Understanding purpose and features of Access
    • How it all works? Forms, Tables, Queries, Reports.
    • User interface: Ribbon and Quick Access Toolbar
    • Using Access templates databases
    • Import/export of databases between Excel and Access
    • When to use Access and when Excel?
  2. Designing databases – basics
  3. Creating tables
    • Data types
    • Understanding relationships, primary keys and foreign keys
    • Default values
  4. Forms
    • Form wizard
    • Working in design view and layout view
  5. Reports
    • Reports wizard
    • Working in design view and layout view
  6. Printing reports and data

SQL Language in MS Access and MS SQL Server

  1. Relational databases – basics
    • Basics of relational theory
    • Table, row, column
    • Key, primary key
    • Foreign keys and relationships between tables
  2. Basic views and operations in database applications (MS SQL Server and MS Access examples)
  3. SQL – Structured Query Language
    • Simple queries – SELECT
    • Functions and operators
    • Filtering rows – WHERE clause
    • Sorting – ORDER BY clause
    • TOP clause
    • Joining multiple tables
    • Grouping rows,
    • Aggregate functions
    • Filtering row groups – HAVING clause
    • Subqueries
    • Set operations (UNION, INTERSECT, EXCEPT)
  4. DDL – Data Definition Language
    • Data types
    • Creating tables – CREATE TABLE
    • Integrity constraints
    • Auto-numbering rows
  5. DML – Data Manipulation Language
    • Adding data- INSERT
    • Modifying data – UPDATE
    • Removing data – DELETE

Download as PDF

Audience and prerequisites

The participants should have the basic knowledge of Excel, no knowledge of data analysis is required.

Certificates

Course participants receive completion certificates signed by ALX.

Sign up for a course

Choose your preferred date and location

London:

No suitable term?

Enter your e-mail address and we will notify you about future dates

Question?

Ask us anything about this training