Четене на данни от външни източници
1. Импортиране на текстов файл в Excel
За да може да бъде прочетен текстов файл в Excel, той трябва да има разширение .txt, .csv.
Data → Get External Data → From Text
избираме файла → Import
Delimited - Characters such as commas or tabs separate each field - има определен знак, който разделя нашите данни в текстовия файл като запетая или табулатор → Next
Delimiters отличителен знак избирате разделителя Comma (Запетая), Space, Semicolon. По подразбиране е избран и табулатор.
В частта Data preview може да видите как ще изглеждат данните в Excel. Изберете 'Finish'.
Задача: Свалете файлa list.txt. Импортирайте файла в таблица в Excel. Съхранете файла като list_nomer_klas
2. Импортиране на текстов файл в Excel чрез свързване
Data → New Query → From File → From Text
Избираме файла, който искаме да свържем и натискаме Load
Промените в текстовия файл ще бъдат отразени и във файла на Excel след натискане на Reload.
Задача: Свалете файлa list.txt. Импортирайте файла в таблица в Excel чрез свързване. Направете промени в текстовия файл и вижте как ще се отразят тези промени във файла на Excel.
3. Четене на данни от XML файл - файлове, които са написани на Extensible Markup Language. Това е формат, който съдържа тагове.
Data → Get External Data → From Other Sources → From XML Data Import
Import Data - Where do you want import data? → Посочвате къде да се мъкне таблицата
4. Четене на данни от уеб страница
Data → New Query → From Other Sources → From Web
From Web → Url записваме (копираме името на сайта, от който искаме да импортираме таблицата)
Navigator → избираме таблицата, която искаме да импортираме → натискаме Load
Задача 35: Импортирайте таблицата със Списък на страните в ЕС по население от Wikipedia. 25. Вградени функции за работа с големи обеми от данни D-функции (Database функции) - функции за работа с база от данни.
1. Функция DSUM
=DSUM (database, field, criteria)
database - област от клетки, таблицата с данните (базата данни).
field - името на колоната, в която ще се прилагат действията от функцията. Записва се името на колоната в кавички или число, представящо позицията на колоната в базата данни(таблицата): 1 за първата колона, 2 за втората и т. н.
criteria - облас тта от клетки, съдържаща критериите за извличане на данни. Създава се допълнителна таблица, в която първият ред е с имената на колоните от базата данни (основната таблица).
2. Функция DCOUNT
=DCOUNT (database, field, criteria)
3. Функция DAVERAGE
=DAVERAGE (database, field, criteria)
4. Функция DMIN
=DMIN (database, field, criteria)
5. Функция DMAX
=DMAX (database, field, criteria)
6. Функция DGET
=DGET (database, field, criteria)
Задача: Свалете фйла d_oborot.xlsx
Задача: Свалете фйла uspeh_d_functions.xlsx
Задача: Свалете фйла klasirane_dget.xlsx
Задача: Свалете фйла dget_if_uspeh.xlsx
Сценарии What-If Analysis 1. Целево търсене (Goal Seek)
Data → What-If Analysis → Goal Seek
2. Таблица с данни (Data Tables)
Data → What-If Analysis → Data Tables Задача 29: Свалете файла zaem_what_if.xlsx
Задача 30: Свалете файла ocenka_what_if.xlsx 21. Сценарии What-If Analysis - Scenario Manager 1. Създаване на сцеарии (Scenario Manager)
Data → What-If Analysis → Scenario Manager
* Именуване на клетки: Formulas → Create from Selection Задача 31: Свалете файлa semeen_budget_scenario.xlsx
1. Именувате клетките Formulas → Create from Selection, поставяте отметка на Left Column
2. Data → What-If Analysis → Scenario Manager
3. Add, записвате името на сценария, в полето Changing Cells избирате клетките, които ще се променят в сценария.
4. Променяме данните в съответните полета за съответния сценарии, Ok.
5. С бутона Show можем да видим промените в съответния сценарий.
6. За създаване на обобщена таблица натискаме Summary - избираме Scenario Summary, в Result cells избираме клетката или клетките с резултата, който искаме да сравним в различните сценарии в една обща таблица. Тя се появява в нов работен лист.
Задача 32: Свалете файлa scenarii_studenti.xlsx
Задача 33: На базата на данните в статията създайте сценарии за студенти, които възнамеряват да направят своя избор.
Вградени функции за обработка на статистически данни Функцията COUNTA
- брои клетките в определена област, които не са празни. COUNTA брои клетки, съдържащи всякакъв тип информация.
=COUNTA (Value1; Value2; …)
Пример: =COUNTA(B1:B10)
Функцията COUNT
- брои клетките в дадена област, в които е въведено число или формула, резултатът, от която е число.
=COUNT(Value1; VaIue2;...)
Задача 24: Свалете файла studenti.xlsx
Функцията COUNTIF
- брои клетките в дадена област, които отговарят на даден критерий
=COUNTIF (Range; Criteria)
Пример: =COUNTIF(A1:A10;"Благоевград") - преброява клетките със стойност Благоевград в областта A1:A10
Задача 25: Свалете файла futbol.xlsx Условието на задачата е в работен лист "Задача" на този файл.
Функция COUNTIFS
- Преброява клетките, които отговарят на ПОВЕЧЕ условия(критерии).
= COUNTIFS (критерии_диапазон1; критерии1; [критерии_диапазон2; критерии2]…)
Функцията SUMIF
Функцията SUMIF сумира стойности от клетки в дадена област, които отговарят на определен Един критерий.
=SUMIF (Range; Criteria; Sum_range)
Range - областта, в която ще се търси критерия
Criteria - критерии
Sum_Range - областта, от която ще се сумират стойности
Функция SUMIFS
Сумира стойности от клетки в дадена област, които отговарят на ПОВЕЧЕ условия(критерии).
= SUMIFS (критерии_диапазон1; критерии1; [критерии_диапазон2; критерии2]…)
Функцията AVERAGEIF
Функцията AVERAGEIF сумира стойности от клетки в дадена област и ги дели на броя им, които отговарят на определен критерий.
=SUMIF (Range; Criteria; Sum_range)
Range - областта, в която ще се търси критерия
Criteria - критерий
Sum_Range - областта, от която ще се сумират стойности и ще се делят на броя им.
Задача 26: Свалете файла uspeh.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 27: Свалете файла klasiraneif.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 28: Свалете файла oborot.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 29. /задачата е по идея на Ангел Ангелов, от помагало "Учебна среда за обучение по електронни таблици"/
Изтеглете файла tickets_otstypka_new.xlsx
Използвайте функцията IF, за да oпределите цената на билета на пътуващите в зависимост от техния статус.
Задача 30: Изтеглете файла magazin.xlsx
В колона Поръчка да се появява "Да" или "Не" в зависимост от количеството на плодовете или зеленчуците, налични в магазина.
- ако количеството на продукта е по-малко от 20 кг, в колона Поръчка да се появява "Да",
- ако е по-голямо или равно от 20 кг да се появява "Не".
Задача 31. Изтеглете файла stipendia.xlsx В колона Стипендия, определете стипендията в лева при условията изписани в задачата.
Задача 32. Изтеглете файла stipendia_su.xlsx В колона Стипендия в лева, определете стипендията на студентите в лева при условията изписани в задачата.
Обобщаващи таблици в Excel
Обобщаващи таблици (PivotTables) позволяват да се комбинират, сравняват и анализират големи количества данни. Данните могат да се подреждат, сортират или филтрират от различни гледни точки.
Insert → PivotTables
1. Данните в таблицата трябва да бъдат разположени вертикално, в клони, а също да имат и имена на колоните.
2. В таблицата не трябва да има празни редове.
3. Не трябва да имаме допълнителна информация, залепена към таблицата.
4. Форматирайте таблицата.
Задача 1.
Използвайки файла bookstore_pivot.xlsx изгответе следните четири справки в различни работни листове на този файл:
• Изгответе справка за общия брой продадени книги в книжарницата за всяко издателство, както и общия оборот за всяко издателство и за цялата книжарница..
• Изгответе справка за минималната цена на книга от всяко издателство.
• Изгответе справка, която показва за всяко издателство средна единична цена..
• Изгответе справка, която за всяко издателство извежда имената на книгите и броя на продадени книги от съответното заглавие.
Задача 2. Създайте обобщаваща таблица за продажбите на отделни стоки за отделните дни, като използвате електронната таблица от файла oborot.xlsx. Създайте диаграма, отразяваща сумата от продажбите на стоките.
Форматиране на данни в обобщаваща таблица
Използвайте работния файл oborot.xlsx
• филтрирайте продажбите за сряда и петък и изчислете тяхната средна стойност;
• филтрирайте така, че при избрана категория зеленчуци да се появяват сумата от продажбите на съответните зеленчуци.
• поставете по редове записите от полето Ден, а по колони – записите от полето Категория, в стойност поставете сумата от продажбите.
• Форматирайте таблицата по свой избор.
Задача 3. Отворете файла kurs.xlsx.
• Изгответе справка, която да показва средната възраст на участниците в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва средния брой точки, които са получени в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва минималния и максималния брой точки, които са получени в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва средния брой точки, които са получени в отделните курсове и минималната и максималната възраст на човек от курс.
Задача 4. Отворете файла prodagbi.xlsx
Задача 5. Отворете файла register_new.xlsx
1) Изгответе справка, която да показва общата сума за заплати, която изплаша всяка фирма поотделно и всички заедно.
2) Изгответе справка, която да показва минималната сума за премия и максималната сума за заплата, които изплаща всяка фирма.
3) Изгответе справка, която да показва средните премии и заплати по месеци.
4) Изгответе справка, която да показва за всяка фирма общата сума за премии и средната сума за заплати по месеци.
5) Изгответе справка, която да показва средните премии за всяка фирма поотделно и общата заплата за всеки човек, дадени от тази фирма.
6) Изгответе справка, която да показва средната сума за премия и общата сума за заплата, която получава всеки човек.
Задача 6:
Свалете файла firma.xlsx
- Изгответе справка за:получените суми от работещите за всеки отделен месец;izpit_modul3.xlsx
- получените суми от работещите по пол;
- получените суми от работещите по категория персонал.
- Създайте диаграма, която отразява получените суми от работещите за месец април.dzi_zadacha.docx
Задача 7. Изтеглете файла stipendia_su.xlsx В колона Стипендия в лева, определете стипендията на студентите в лева при условията изписани в задачата.
Задача 8:
Свалете ресурсния файл results.xlsx
Условието на задачата се намира във файла - dzi_zadacha.docx
Тест