Ms excel ilə verilənlərin analiziSyllabus

Subject code

İFS-40185y

Credits

6

Student workload

180

Semester

Spring semester

Academic year

2025-2026

Subject type

Elective

Form of education

Full-time

Language of instruction

Azerbaijani

In-class hours

a) 30 saat - mühazirə b) 30 saat - seminar

Out-of-class hours

a) 72 saat - sərbəst iş b) 48 saat - hazırlıq

“excel ilə verilənlərin analizi” fənni microsoft excel mühitində verilənlərin emalı, təhlili və vizuallaşdırılması üzrə nəzəri bilik və praktik bacarıqların formalaşdırılmasına yönəlib. fənn çərçivəsində excel-in əsas funksiyaları, məntiqi, riyazi, statistik, mətn, tarix və axtarış funksiyaları, verilənlərin çeşidlənməsi və filtrlənməsi, şərti formatlaşdırma, diaqramların qurulması, pivottable və pivotchart, data analysis toolpak, power query, power pivot və DAX alətlərindən istifadə öyrədilir. kursun sonunda tələbələr verilənlərin təhlili, hesabat və interaktiv dashboardların hazırlanması, məlumatların transformasiyası və vizuallaşdırılması üzrə praktiki bacarıqlar əldə edir, excel vasitəsilə analitik qərarvermə prosesində səmərəli işləmək bacarığı qazanırlar.

• lecture
• interactive discussion
• seminar
• presentations
• project-based learning

1. 1

   CLO 1: Explains the basic capabilities of the excel environment, data entry, formatting and application of basic functions.

2. 2

   CLO 2: Processes and analyzes data using mathematical, statistical, logical, text, date and search functions.

3. 3

   CLO 3: Applies data sorting, filtering, conditional formatting and visualization techniques.

4. 4

   CLO 4: Model and analyze data using pivottable, pivotchart, power query, and power pivot tools.

5. 5

   CLO 5: Performs calculations using DAX functions, prepares reports and interactive dashboards.

6. 6

   CLO 6: Prepares reports and projects that support decision-making based on real data using excel's analytical tools.

1. 1.
   Introduction to excel and basic featuresLecture

   The subject studies the interface and working principles of microsoft excel, one of the most widespread tools in data analysis and management. students master the structure of the excel window, menus, arrangement of rows, columns and cells, different types of data entry and visual formatting. also, the principles of performing mathematical calculation operations within the program and writing and applying fundamental functions according to the syntax are analyzed.

   • Explains the basic capabilities of the excel environment, data entry, formatting and application of basic functions.
2. 2.
   Data organization and formattingLecture

   In the subject, the methods of organizing large data sets in excel, bringing them to a visual structure that facilitates analysis and managing them are studied. students master the mechanisms of sorting data according to certain criteria and filtering and hiding unnecessary data. also, the conditional formatting tool, which automatically changes the color and visual style of cells based on specified conditions, the correct selection of cell ranges, the use of absolute and relative references, as well as the principles of putting data into a formal table format, are analyzed.

   • Applies data sorting, filtering, conditional formatting and visualization techniques.
3. 3.
   Mathematical and statistical functionsLecture

   In the subject, fundamental mathematical and statistical functions are studied, which are considered as the basis for the analysis of numerical data in excel. students master the application of functions that find the sum of a set of data, calculate its average value, determine its largest and smallest values, as well as extract the number of numerical data in cells. in the second part of the lesson, methods of rounding fractional numbers are analyzed to ensure accuracy in financial and statistical calculations — standard rounding, up and down rounding functions.

   • Processes and analyzes data using mathematical, statistical, logical, text, date and search functions.
4. 4.
   Logic and conditional functionsLecture

   The topic explores the decision-making tools used to manage complex business scenarios and conditional calculations in excel. students master logical functions that return different results based on given criteria, logical operators that combine conditions, and nested IF structure for multilevel decisions. also analyzed are the IFERROR and IFNA tools to avoid errors in formulas, as well as aggregate functions that group filtered data based on certain condition(s).

   • Processes and analyzes data using mathematical, statistical, logical, text, date and search functions.
5. 5.
   Search and reference functionsLecture

   In the subject, methods of linking data between different tables or sheets, indexing and building dynamic search systems are studied. students master excel's classic vertical and horizontal search tools, the more flexible and modern XLOOKUP function that overcomes their limitations, as well as the combination of INDEX and match, which provides high-performance search in large data sets and can also search to the left. additionally, choose, which selects from a list at a given index, and offset, which sets a dynamic cell range from a specified starting point, are analyzed.

   • Processes and analyzes data using mathematical, statistical, logical, text, date and search functions.
6. 6.
   Data validation and data controlLecture

   In the subject, the methods of minimizing the errors caused by the human factor and protecting the data integrity when entering raw data into the database in the excel environment are studied. students master the mechanisms of setting special restrictions on the values ​​to be written in the cells, building functional messages that warn the user when wrong data is entered. it also examines the implementation of control mechanisms that check the accuracy, duplication and inconsistency of data at the stage of formulating weekly and month-end reports, which are critical for business continuity.

7. 7.
   Pivottable and pivotchartLecture

   In the subject, pivottable and pivotchart functions, excel's most powerful analytical tools, are studied for the rapid grouping, analysis and visualization of large databases. students analyze the construction of dynamic tables from raw data and the proper configuration of four key fields—rows, columns, values, and filters. in the main part of the lesson, the creation of special fields that allow you to perform new calculations with special mathematical formulas on existing data, the connection of slicers for interactive management of reports, and the transformation of analysis results into dynamic graphs are practically investigated.

8. 8.
   Statistical functions and data analysisLecture

   The subject explores basic statistical analysis methods and functions used to detect structure, central tendency, and distribution patterns in large data sets. students analyze median, which finds the middle point that is insensitive to outliers, standard deviation, which measures how far data deviates from the mean, and dispersion. in the main part of the lesson, the calculation of the correlation coefficient, which determines the strength and direction of the linear relationship between two different variables, as well as the preparation of analytical reports by applying these statistical indicators to real business and research data are practically investigated.

   • Processes and analyzes data using mathematical, statistical, logical, text, date and search functions.
9. 9.
   Additional function of data analysis toolpakLecture

   The topic explores the data analysis toolpak, excel's built-in analytical module for automating complex statistical and engineering calculations. tələbələr bu təkmil əlavənin proqrama inteqrasiya edilməsi və aktivləşdirilməsi addımlarını təhlil edirlər. in the main part of the lesson, the application of regression predicting cause-and-effect relationships between variables, correlation measuring the degree of dependence of data and t-test analyzes comparing the average values ​​of two sample groups, as well as the construction of histograms and scales that visually reflect the frequency of data distribution are practically investigated.

   • Prepares reports and projects that support decision-making based on real data using excel's analytical tools.
10. 10.
    Power query - data retrievalLecture

    In the subject, power query, an ETL tool used for automatic cleaning, structuring and merging of scattered, voluminous and differently formatted data, is studied. students analyze how to query data from local excel files, CSV files, and directly from web pages. in the main part of the lesson, filtering, ordering and grouping transformations for formatting raw data are practically explored, as well as the application of merge queries that work with database logic — linking different tables by columns and append queries by rows.

    • Model and analyze data using pivottable, pivotchart, power query, and power pivot tools.
11. 11.
    Power pivot and data modelLecture

    The topic explores the power pivot add-in and the relational database architecture, which allows you to work with large databases of millions of rows, exceeding the standard spreadsheet limits of excel. students analyze how to combine tables from different sources into a single data model and establish one-to-many (1:n) relationships between them. in the main part of the lesson, the application of CALCULATE and SUMX formulas, which modify the existing filters through dax, the most powerful calculation language in business analytics, and SUMX formulas, which differ in terms of memory and calculation optimality, are practically investigated.

    • Performs calculations using DAX functions, prepares reports and interactive dashboards.
12. 12.
    Creating a dashboardLecture

    Mechanisms for transforming complex data sets into easy-to-understand, visual and interactive control panels for management and decision-makers are studied in the subject. students analyze slicers used to dynamically filter data, timelines that allow for chronological analysis, and the application of custom form controls that enhance interactivity. in the main part of the lesson, conventional formatting rules that emphasize important business indicators, visualization of key performance indicators, selection of color palette and principles of professional report design according to user experience are practically examined.

    • Performs calculations using DAX functions, prepares reports and interactive dashboards.
13. 13.
    Final project and presentationLecture

    In the subject, the final project phase is studied, which ensures that the theoretical and practical knowledge gained by students during the course is integrated into real business scenarios or applied tasks. working on real data sets, students combine the processes of building complex data models, preparing analytical reports or applying algorithmic solutions in the form of a project. in the main part of the lesson, the visual presentation of the prepared projects in front of a professional audience, presentation techniques, argumentation of the obtained results, as well as collective discussion of the strengths and weaknesses of the project and question-and-answer sessions are practically investigated.

    • Prepares reports and projects that support decision-making based on real data using excel's analytical tools.

1. J. walkenbach - "excel 2021 bible" (2021)(opens in a new tab)
2. M. alexander, r. kusleika - "excel dashboards and reports" (2016)(opens in a new tab)