50450B Creating and Analyzing Database Using Microsoft Excel

350€

Microsoft SQL Server

Scopul principal al acestui curs de o zi este de a oferi participantilor cunoștințele și abilitățile de a utiliza funcții avansate în crearea și analizarea bazelor de date. Participantii vor învăța cum să sorteze și să gestioneze datele în liste, datele de filtrare și de interogare, aplica funcțiile de căutare și de bază de date. De asemenea, participantii vor invata cum să analizeze și să evalueze informațiile din bazele de date, creând tabele pivot și pivot.

Acest curs este destinat angajaților care au cel puțin un an de experiență în utilizarea Microsoft Office Excel 2003/2007.

După finalizarea acestui curs Microsoft SQL Server, participantii vor putea:
•Lucra cu bazele de date.
•Utiliza AutoFilter.
•Lucra cu Advanced Filters.
•Utiliza formulele de căutare(Lookup).
•Exporta și importa date
•Crea / revizui PivotTable
Continutul cursului poate fi adaptat in functie de necesitatile si/sau specificul cursantilor. Totodata, editia de curs poate fi aleasa de catre cursanti inainte de inscierea in cadrul grupei: 2010/2013/2016.

La finalul fiecarui curs, fiecare persoana va primi o diploma ce atesta participarea in cadrul respectivei grupe de curs, Certificate of Achievement, elaborata de ITAdviser, Microsoft Certified Partner on Learning Solutions. Diploma are recunoastere atat la nivel national cat si international.

Detalii curs

  • Durata 1 zi
  • Nivel 200
  • Limba curs Engleza/Romana
  • Limba suport Engleza
  • Cursanti/grupa 10
  • Examen
  • Certificare
Course Outline

Module 1: Working with Databases

This module explains how to make use of Excel to create a sample database format.

Lessons

  • Creating a Database
  • Modifying a Database
  • Sorting Records by Multiple Fields
  • Using Data Validation
  • Validating Data using a List
  • Creating a Custom Error Message
  • Removing Data Validation
  • Creating Subtotals in a List
  • Removing Subtotals from a List

Lab : Practice 1

After completing this module, students will be able to:
  • Creating and Modify Database
  • Using Data Validation
  • Creating, Removing Subtotals

Module 2: Using AutoFilter

This module explains how to use AutoFilter to get their desired details from Excel List.

Lessons

  • Enabling AutoFilter
  • Using AutoFilter to Filter a List
  • Clearing AutoFilter Criteria
  • Creating a Custom AutoFilter

Lab : Practice 2

After completing this module, students will be able to:
  • Use AutoFilter to get their desired details from Excel list.
  • Create Custom AutoFilter.

Module 3: Working with Advanced Filters

This module explains how to make use of the advanced filter to set criteria range and copy the result to another location in Excel ranges.

Lessons

  • Creating a Criteria Range
  • Using a Criteria Range
  • Showing All Records
  • Using an Advanced And Condition
  • Using an Advanced Or Condition
  • Copying Filtered Records
  • Using Database Functions

Lab : Practice 3

After completing this module, students will be able to:
  • Set Criteria range for the advanced filters.
  • Copying Filtered record to another location in Excel ranges.
  • Use Database function for calculating required results.

Module 4: Lookup Formulas

This module explains how to make use of Vlookup, Hlookup to retrieve desired items in Excel Tables.

Lessons

  • Using VLookup
  • Using HLookup

Lab : Practice 4

After completing this module, students will be able to:
  • Make use of Vlookup and Hlookup

Module 5: Exporting and Importing Data

This module explains how to import and export Excel data to text formats. It also shows how to import data from the web.

Lessons

  • Exporting Data to Other Applications
  • Exporting to XML
  • Importing Data from Text Files
  • Changing External Data Range Properties
  • Importing Data from Other Applications
  • Removing the Query Definition
  • Importing Dynamic Data from the Web
  • Copying a Table from a Web Page

Lab : Practice 5

After completing this module, students will be able to:
  • Import and export data to Text format
  • Import data from other applications
  • Import data from the Web

Module 6: Creating/Revising PivotTables

This module explains how to use determine the source needed to create its PivotTable/PivotChart report.

Lessons

  • Creating a PivotTable Report
  • Adding PivotTable Report Fields
  • Selecting a Page Field Item
  • Refreshing a PivotTable Report
  • Changing the Summary Function
  • Adding New Fields to a PivotTable Report
  • Moving PivotTable Report Fields
  • Hiding/Unhiding PivotTable Report Items
  • Deleting PivotTable Report Fields
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report
  • Publishing PivotTable Reports to the Web

Lab : Practice 6

After completing this module, students will be able to:
  • Determine the source needed for its PivotTable
  • Create the PivotTable
  • Adding/removing Fields for the PivotTable
  • Creating Page Field Report
  • Creating a PivotChart Report
  • Publishing PivotTable Report to the Web