Програма, учебници, модули - линк
Входно ниво - https://forms.gle/vLPVgzBtJrdWhWXM7
Онлайн уроци Excel 2010
Тест и практически задачи входно ниво Excel (Просвета) - линк
MS Excel - преговор |
Задачи преговор MS Excel
|
Задача 1. Свалете файла bal.xlsx.
|
Задача 2. Свалете файла doklad__2_.xlsx
|
Сортиране и филтриране в електронна таблица по няколко признака Сортиране
|
|
Филтриране в електронна таблица по няколко признака Филтриране
|
Задачи филтриране
|
Валидиране на данни
Задача 5:
Свалете файла bal.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 6:
Свалете файла systezanie.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 7:
Свалете файла hotel.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 8: Свалете файла zaplati.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Използване на логически функции в електронни таблици. Функцията IF
Задача 9.
Изтеглете файла magazin.xlsx
В колона Поръчка да се появява "Да" или "Не" в зависимост от количеството на плодовете или зеленчуците, налични в магазина.
- ако количеството на продукта е по-малко от 20 кг, в колона Поръчка да се появява "Да",
- ако е по-голямо или равно от 20 кг да се появява "Не".
Задача 10. /задачата е по идея на Ангел Ангелов, от помагало "Учебна среда за обучение по електронни таблици"/
Изтеглете файла tickets_otstypka_new.xlsx
Използвайте функцията IF, за да oпределите цената на билета на пътуващите в зависимост от техния статус.
Задача 11: Изтеглете файла oborot_new_if.xlsx
Изчислете комисионната, която трябва да получи всеки служител за месеца,
ако при направен оборот 3 500 лв. и повече служителите получават 20% комисионна, в противен случай - 10%.
Задача 12. Изтеглете файла tickets_vlogen_if.xlsx
Изчислете отстъпката в лева за всеки пътник (студент, ученик, пенсионер, работник) според процентите отстъпка, които са дадени.
Изчислете крайната цена на билета за всеки пътник според неговия статус.
Задача 13: /задачата е по идея на Ангел Ангелов, от помагало "Учебна среда за обучение по електронни таблици"/
Изтеглете файла uspeh_if.xlsx
Добавете колона с име "С думи"
В колона "С думи" автоматично да се появяват отличен, мн.добър, добър, среден, слаб в зависимост от оценката в колона "Среден успех"
Задача 14. Изтеглете файла stipendia.xlsx В колона Стипендия, определете стипендията в лева при условията изписани в задачата.
Задача 15. Изтеглете файла stipendia_su.xlsx В колона Стипендия в лева, определете стипендията на студентите в лева при условията изписани в задачата.
Задача:
zadacha.xlsx
Функцията OR, AND, CHOOSE
Функцията CHOOSE връща стойност от списък, използвайки позицията му или индекса му.
Например, CHOOSE (2, "Ани", "Иван", "Петър") връща "Иван", тъй като Иван е втората подред стойност, изброена в списъка.
Общ вид на функцията:
=CHOOSE (index_num, value1, [value2], ...)
index_num - Индекса пореден номер в списък. Число между 1 и 254.
value1 - Първата стойност, от която да изберете.
value2 - Втората стойност, от която да избирате.
Задача 16: Изтеглете файла zadacha16.xlsx - задачата е създадена а база на примера от видеото - https://youtu.be/BGbYkS2wmTw
Задачите са в работен лист Задача 1 и Задача2
Условно форматиране - Conditional Formatting
Създаване на правило за форматиране
|
Задача 17: Свалете файла naselenie_evropa.xlsx. Задача 1, Задача 2, 3 и 4 са в съответните работни листове на този файл.
|
Защита на данните в електронни таблици
3. Защита на области от клетки в работния лист
Само заключени клетки могат да бъдат защитени с Protect Sheet
I начин:
1. Review → Allow Edit Ranges → New маркираме областите от клетки, в които искаме да можем да въвеждаме,
2. Review → Protect Sheet
II начин:
1. Маркираме областите от клетки, които искаме да не са защитени
2. Format Cells → Protection → Locked (махаме отметката в Locked)
3. Review → Protection → Protect Sheet
4. Премахване на забраната на достъп до клетки
Review → Protection → Unprotect Sheet
Задача 21: Защитете колони Име и ЕГН във файла hotel_protect.xlsx Съхранете файла под име hotel_protect в работната ви папка.
Задача 22: Защитете колони Име, ЕГН и Оценки (всички оценки) във файла bal.xlsx . Съхранете файла под име bal_protect в работната ви папка.
Задача 23: Защитете колони Име, Фамилия, ЕГН и Точки във файла systezanie.xlsx . Съхранете файла под име systezanie_protect в работната ви папка.
Защита на файл
|
|
Обобщаващи таблици в Excel |
Обобщаващи таблици (PivotTables) позволяват да се комбинират, сравняват и анализират големи количества данни.
Insert → PivotTables
1. Данните в таблицата трябва да бъдат разположени вертикално, в клони, а също да имат и имена на колоните.
2. В таблицата не трябва да има празни редове.
3. Не трябва да имаме допълнителна информация, залепена към таблицата.
4. Форматирайте таблицата.
Задача 24.
Използвайки файла bookstore_pivot.xlsx изгответе следните четири справки в различни работни листове на този файл:
• Изгответе справка за общия брой продадени книги в книжарницата и за всяко издателство, както и общия оборот за всяко издателство и за цялата книжарница..
• Изгответе справка за минималната цена на книга от всяко издателство.
• Изгответе справка, която показва за всяко издателство средна единична цена..
• Изгответе справка, която за всяко издателство извежда имената на книгите и броя на продадени книги от съответното заглавие.
Задача 25. Създайте обобщаваща таблица за продажбите на отделни стоки за отделните дни, като използвате електронната таблица от файлаИзползвайте работния файл oborot.xlsx
• филтрирайте продажбите за сряда и петък и изчислете тяхната средна стойност;
• филтрирайте така, че при избрана категория зеленчуци да се появяват сумата от продажбите на съответните зеленчуци.
• поставете по редове записите от полето Ден, а по колони – записите от полето Категория, в стойност поставете сумата от продажбите.
• Форматирайте таблицата по свой избор.
• Създайте диаграма, отразяваща сумата от продажбите на стоките.
Задача 26. Отворете файла kurs.xlsx.
• Изгответе справка, която да показва средната възраст на участниците в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва средния брой точки, които са получени в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва минималния и максималния брой точки, които са получени в отделните курсове и общо за всички курсове.
• Изгответе справка, която да показва средния брой точки, които са получени в отделните курсове и минималната и максималната възраст на човек от курс.
Задача 27. Отворете файла register_new.xlsx
1) Изгответе справка, която да показва общата сума за заплати, която изплаша всяка фирма поотделно и всички заедно. Създайте диаграма, която отразява общата сума за заплати за всяка фирма.
2) Изгответе справка, която да показва минималната сума за премия и максималната сума за заплата, които изплаща всяка фирма.
3) Изгответе справка, която да показва средните премии и заплати по месеци.
4) Изгответе справка, която да показва за всяка фирма общата сума за премии и средната сума за заплати по месеци.
5) Изгответе справка, която да показва средните премии за всяка фирма поотделно и общата заплата за всеки човек, дадени от тази фирма.
6) Изгответе справка, която да показва средната сума за премия и общата сума за заплата, която получава всеки човек.
Имена на области и приложения
Функция за търсене VLOOKUP
Линк: http://linoit.com/users/albena_uz/canvases/VLOOKUP%28%29
Задача 28: Свалете файла laptop.xlsx Условието на задачата е в работен лист "Задача" на този файл. Видео
Задача 29: Свалете файла klasirane.xlsx Условието на задачата е в работен лист "Задача" на този файл. Видео
Задача 30: Свалете файла bookstore.xlsx Условието на задачата е в работен лист "Задача" на този файл.
Задача учебник: На стр. 24 задача 2. Ресурсният файл се намира https://inftech.bgtest.eu/ Съхранете файла с име uchebnik_zadacha2
Задача учебник: На стр. 25 задача 3. Ресурсният файл се намира https://inftech.bgtest.eu/ Съхранете файла с име uchebnik_zadacha3
Задача учебник: На стр. 25 задача 4. Ресурсният файл се намира https://inftech.bgtest.eu/ Съхранете файла с име uchebnik_zadacha4
Функция за търсене HLOOKUP
Задача 32: Свалете файла oborot_search.xlsx
Задача 33 (подготовка за практическо изпитване) Отворете файла prodagbi.xlsx
Задача 34 (подготовка за практическо изпитване) Отворете файла noit.xlsx Условието на задачата се намира във файла: Задача_от_ДЗИ_2022г.pdf
Функция за търсене LOOKUP
Вградени функции за обработка на финансови данни
Лихва - Възнаграждението, което се изплаща за използването на определена парична сума, (капитал) за даден период от време (лихвен период).
начален капитал (главница)
лихвен процент
брой на периодите
проста лихва - Лихвата, която се изплаща, когато в края на всеки лихвен период се олихвява само първоначалната сума (начален капитал). Обикновено се използва при краткосрочни финансови взаимоотношения.
Функцията РМТ - видео
- изчислява вноската по заема на базата на постоянна вноска и постоянна лихва
=PMT (Rate; Nper; Pv; [Fv]; [Type])
Задача 35: Изчислете месечната вноска, общата сума, цената на кредита, както е показано на снимката по-долу:
Функцията IPMT (Interest Payment) - видео
- намира сумата, която ще се плати като лихва в конкретна вноска при погасяването на кредит за даден период от време при периодични постоянни плащания и постоянен лихвен процент.
=IРМТ (Rate; Per; Nper; Pv;[Fv];[Type])
Задача 36: Изчислете месечната вноска, общата сума, цената на кредита лихвата, както е показано на снимката по-долу:
Функцията PPMT (Principal Payment) - Онлайн урок (на английски език)
Тази финансова функция е свързана с начисляването на сложна лихва.
- намира каква част от вноската е за погасяване на главницата.
=PPMT(Rate; Per; Nper; Pv;[Fv];[Type])
Задача 36_1:
Свалете вече направената задача 36 и добавете в таблицата отдясно колона с име вноска по главница.
Изчислете вноската по главницата като използвате функцията PPMT.
Задача 36_2:
Изчислете месечната вноска, вноската по лихвата, вноската по главницата, както е показано на снимката по-долу:1
Функцията NPER
- намира броя периодите на базата на периодични постоянни плащания и постоянна лихва.
=NPER (Rate; Pmt; Pv; [Fv]; [Type])
18. Вградени функции за обработка на финансови данни (упражнение)
Погасителен план
Какво представлява погасителният план и защо той е важен?
https://fincity.bg/pogasitelen-plan/
Задача 37:
Изтеглете файла zad_37.xlsx Направете два погасителни плана в един файл, в два различни работни листа, като използвате началните данни зададени в статията: https://www.klearlending.com/blog/articles/pogasitelen-plan-na-kredit
Използвайте функцията EDATE(), за добавяне на месец от дата. Повече информация за функцията: линк
Задача 37_2:
Направете погасителен план при данните за главница, лихвен процент и лихвен период в години.
Използвайте функцията EDATE(), за добавяне на месец от дата. Повече информация за функцията: линк
Задача 37_3:
Направете погасителен план при данните за главница, лихвен процент и лихвен период в години от снимката.
Използвайте функцията EDATE(), за добавяне на месец от дата. Повече информация за функцията: линк
19. Вградени функции за обработка на статистически данни
Функцията COUNTA
- брои клетките в определена област, които не са празни. COUNTA брои клетки, съдържащи всякакъв тип информация.
=COUNTA (Value1; Value2; …)
Пример: =COUNTA(B1:B10)
Функцията COUNT
- брои клетките в дадена област, в които е въведено число или формула, резултатът, от която е число.
=COUNT(Value1; VaIue2;...)
Задача 38: Свалете файла studenti.xlsx
Функцията COUNTIF
- брои клетките в дадена област, които отговарят на даден критерий
=COUNTIF (Range; Criteria)
Пример: =COUNTIF(A1:A10;"Благоевград") - преброява клетките със стойност Благоевград в областта A1:A10
Задача 39: Свалете файла 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 - областта, от която ще се сумират стойности и ще се делят на броя им.
Задача 40: Свалете файла uspeh.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 41: Свалете файла klasiraneif.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
Задача 42: Свалете файла oborot.xlsx на вашия компютър. Условието на задачата е в работен лист "Задача" на този файл.
20. Сценарии What-If Analysis
1. Целево търсене (Goal Seek)
Data → What-If Analysis → Goal Seek
2. Таблица с данни (Data Tables)
Data → What-If Analysis → Data Tables
Задача 43: Свалете файла zaem_what_if.xlsx
Задача 44: Свалете файла ocenka_what_if.xlsx
21. Сценарии What-If Analysis - Scenario Manager
1. Създаване на сцеарии (Scenario Manager)
Data → What-If Analysis → Scenario Manager
* Именуване на клетки: Formulas → Create from Selection
Задача 45: Свалете файл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 избираме клетката или клетките с резултата, който искаме да сравним в различните сценарии в една обща таблица. Тя се появява в нов работен лист.
Задача 46: Свалете файлa scenarii_studenti.xlsx
Задача 47: На базата на данните в статията създайте сценарии за студенти, които възнамеряват да направят своя избор.
Задача 48: На базата на данните в таблицата от линка, създайте задача.
Задачи по математическа и приложна лингвистика: линк
Видео (английски) - What-If Analisis
Видео 1 (английски) - What-If Analisis
Видео 2 (английски) - What-If Analisis
Видеа Excel
23. Четене на данни от външни източници
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'.
Задача 49: Свалете файлa list.txt. Импортирайте файла в таблица в Excel. Съхранете файла като list_nomer_klas
2. Импортиране на текстов файл в Excel чрез свързване
Data → New Query → From File → From Text
Избираме файла, който искаме да свържем и натискаме Load
Промените в текстовия файл ще бъдат отразени и във файла на Excel след натискане на Reload.
Задача 50: Свалете файл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
Задача 51: Импортирайте таблицата със Списък на страните в ЕС по население от 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)
Задача 52: Свалете фйла d_oborot.xlsx
Задача 53: Свалете фйла uspeh_d_functions.xlsx
Задача 54: Свалете фйла klasirane_dget.xlsx
Задача 55: Свалете фйла dget_if_uspeh.xlsx
Видео1 (английски език) - Database Functions (DCOUNT, DSUM, DAVERAGE, DGET)
Видео2 (английски език) - D-функции
Видео3: Database Functions (DCOUNT, DSUM, DAVERAGE, DGET)
Използване на големи обеми от данни (Big Data) за решаване на сложни и комплексни проблеми
1. Области, в които се използват големи обеми от данни (Big Data технологиите): медицина, здравеопазване, телекомуникации, банки, транспорт, енергетика и други.
|