Практична робота 16 на тему Введення та редагування даних та виконання обчислень в електронних таблицях у середовищі MS Excel

Практична  робота № 16 Тема: Введення, редагування, форматування даних та виконання обчислень в електронних таблицях у середовищі  MS Excel

Мета: Освоїти технології введення, редагування даних. Виробити практичні навички форматування даних і таблиць, виконання  простих обчислень за формулами з використанням вбудованих функцій MS Excel.

Технологія виконання роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_16, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР16.docx та зберегти його у папку ПР_16. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. У папці  ПР_16 створити нову книгу і зберегти її у файлі з назвою
ПР16_ <ваше прізвище>_1.xlsx.

Завдання 4. На робочому аркуші Лист1 створити зображену нижче таблицю 1:

Таблиця 1

1) Вибрати формати даних: Денежный (з двома десятковими знаками і грошовою одиницею "грн.") у діапазонах D4:D13, F4:G13 і Процентный з двома десятковими знаками у діапазоні E4:E13.

2)  Обчислити значення стовпця "Ціна реалізації", використовуючи дані стовпців "Ціна" та "Торгова націнка".

3) Значення в стовпчику "Всього за тип" обчислити, скориставшись даними стовпців "Кількість" і "Ціна реалізації".

4)  В клітинку F15 записати значення ціни найдорожчого принтера. В клітинку F16 записати значення ціни найдешевшого принтера. В клітинку F17 записати середнє значення ціни принтерів.

Завдання 5. Виконати форматування  таблиці:

1) При формуванні заголовка таблиці використати об’єднання клітинок та опцію форматування Переносить по словам.

2) Зробити обрамлення і заповнення діапазонів кольором (будь-яким), як показано на зразку.

3) Зробити вирівнювання текстових і числових значень згідно зразка, поданого у таблиці.

4) Перейменувати аркуш Лист1 на Таблиця продажу принтерів.

5) Переглянути створену таблицю і  зберегти результати

Завдання 6. На робочому аркуші Лист2 створити зображену нижче таблицю 2 та перейменувати аркуш Лист2 на Народонаселення країн СНД.

Таблиця 2

Завдання 7. Створити другий файл книги з іменем ПР16 <Власне прізвище>_2.xlsx, на першому аркуші якої помістити таблицю 3:

Таблиця 3

Останні  два стовпці в обох  таблицях повинні мати процентний формат з двома десятковими розрядами після коми.

1) Клітинкам В4 і С4 присвоїти імена Площа_світу та Народонаселення_світу, відповідно.

2)  До діапазону таблиці застосувати Автовычисление ( у рядку стану), виділивши функції середнє, кількість, кількість  чисел, мінімум, максимум, сума. Зробити скріншот та помістити поряд з таблицею.

3) Виконати аналогічні обчислення,  застосувавши відповідні функції та помістити поряд результати для порівняння.

4) Перейменувати Лист1 у Народонаселення Європи  і зберегти книгу.

Завдання 8.  Відкрити книгу ПР16 <Власне прізвище>_1.xlxs і на аркуші Лист2 підрахувати суми в стовпцях «Площа» і «Населення», для першого стовпчика скориставшись кнопкою Сумма ∑, а для другого кнопкою Вставка функции рядка формул  та функцією  СУММ.

1) Для кожної країни визначити щільність населення у   чол./км², для цього записати  формулу один раз у клітинку D3  і скопіювати її до клітинки D4, D5.

2) Для кожної країни визначити частку її населення (в %) від населення усього світу і  у клітинку поряд з таблицею помістіть відповідну формулу.

3) Так само визначити частку території (в %) кожної країни від території, яку заселено людьми на всій земній кулі і  у клітинку поряд з таблицею помістіть відповідну формулу.

4) Визначити за допомогою функції СРЗНАЧ середню щільність населення у країнах. Результати помістити у рядок "Усього" відповідного стовпця.

Завдання 9. *

1) На робочому аркуші Лист3 побудувати таблицю за наведеним зразком (дотримуючись відповідного форматування комірок).

2) Номери квартир введіть за допомогою операції автозаповнення.

3) Виконати необхідні обчислення для квартири № 1 за формулами:

Кількість електроенергії = Поточні-Попередні (в комірку D4 введіть = B4-C4);

Оплата за електроенергію = Кількість спожитої електроенергії *Тариф (в комірку Е4 введіть =D4*$G$1);

Послуги банку = 2% * Оплата за електроенергію (в комірку F4 введіть  =2%*E4);

Далі відповідно до варіанту змініть число відсотків.

Разом = Оплата за електроенергію + Послуги банку ( в комірку G4 введіть =E4+F4).

4) Отримані в попередньому пункті формули слід скопіювати для решти квартир за допомогою операції автозаповнення.

5) У стовпці B за допомогою функцій знайдіть суму, найбільше, найменше та середнє значення поточних показань лічильників.

6) Отримані в попередньому пункті формули слід скопіювати для решти стовпців за допомогою операції автозаповнення.

7) Виконати попередній перегляд через вкладку Файл командою Печать, в разі необхідності  слід відкоректувати  параметри сторінки або розміри таблиці.

8) Перейменуйте Лист3на Електроенергія _<Власне прізвище>.

Завдання 10. Зберегти результати роботи.

Завдання 11. Помістити до звіту відповіді на контрольні запитання.

Завдання 12. Перемістити папку ПР_16 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 13. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Запитання до захисту практичної роботи

1. Які типи даних використовуються у середовищі Excel?

2. Які числові формати застосовуються в Excel?

3. Як здійснюють обчислення в таблиці?

4. Які елементи може містити формула?

5. Що таке абсолютне та відносне посилання на клітинки?

6. Як виділити несуміжний діапазон клітинок?

7. Що таке автозаповнення таблиці і як його виконувати?

8. Як захистити комірки від змін у них сторонніми особами?

9. Які категорії стандартних функцій використовуються у середовищі  Excel?

10. Як задати режим відображення формул?

11. Яке значення мають наступні функції:

МИН(8;3;23)?

СУММ(8;3;23)?

СРЗНАЧ(8;3;23)?

12. Яке значення мають наступні вирази:

СУММ(8;3;23)-2*МИН(8;3;23)+SQRT(9)*SIN(0)?

МАКС(8;13;МИН(18;23))?

СРЗНАЧ(8;МАКС(34;23))?

Практична  робота №17 Тема: Обчислення в електронних таблицях з використанням логічних функцій MS Excel

Мета: Освоїти технології виконання  обчислень за формулами з використанням логічних функцій,  набути практичні навички виконання умовного форматування таблиць.

Технологія виконання роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_17, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР17.docx та зберегти його у папку ПР_17. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. Викликати табличний процесор MS Excel, створити нову книгу і зберегти у файлі ПР17_<Власне прізвище>.xlsx

Завдання 4. На першому аркуші Лист1 створити таблицю 1 за поданим зразком:

1)  Використовуючи вбудовані функції (математичні, статистичні, логічні) виконати обчислення максимального, мінімального та середнього балу по кожному студенту.

2) У стовпчику Характеристика студентаза допомогою логічних функцій в залежності від оцінок вивести повідомлення відмінник (всі оцінки  5), хорошист (оцінки 4 або 4,5), трієчник (оцінки 3 або 3,5), двієчник (хоч би одна оцінка 2).

3) У стовпчику Контракт вивести повідомлення «так», якщо середній бал 3,5 і нижче або «ні»,  якщо немає двійок і середній бал вище 3,5.

4) У стовпчику Пільги записати є , немає або сирота.

Завдання 5. Розрахувати  розмір стипендії:

1) У стовпчику Розмір стипендії записати формулу з логічною функцією ЕСЛИ, враховуючи наступні умови:

2) Перейменувати аркуш Лист1 у Стипендія.

Завдання 6.  На другому аркуші створити  таблицю 2  та  перейменувати аркуш в  Автотранспорт:

1) Під таблицею запишіть наступні завдання та, використовуючи дані таблиці, обчисліть:

Ø кількість транспортних одиниць, пробіг яких перевищує 100 тис. км.=

Ø кількість мікроавтобусів, рік капремонту яких є після 2010 р.=

Ø кількість транспортних одиниць із середніми витратами пального від 10 літрів =

Ø кількість транспортних одиниць, шифр яких більший за значення, записане в деякій клітинці поза таблицею =

Ø середній пробіг вантажівок=

Ø сумарний пробіг для мікроавтобусів=

Ø %% середніх витрат пального від всіх вантажівок (останні беруться за 100%)для:

- всіх автомобілів=

- всіх мікроавтобусів=

2) До таблиці додати стовпчик Капремонт , в клітинках якого для відповідного транспортного засобу з’являється фраза "Капремонт", якщо останній капремонт не був у поточному році та пробіг становить від 500 тис. км.

3) У новому стовпчику із попереднього завдання фраза "Капремонт" має бути написана червоним кольором, якщо пробіг понад 700 тис. км.

4) У стовпчику "Пробіг" виділити значення жовтим  кольором для тих транспортних засобів, пробіг яких вже понад 500 тис. км.

Завдання 7. Перегляньте таблицю, при необхідності виконайте відповідне форматування та збережіть результати роботи.

Завдання 8. Помістити до звіту відповіді на контрольні запитання.

Завдання 9. Перемістити папку ПР_17 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 10. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Запитання до захисту практичної роботи

1. Які логічні функції використовуються у табличному процесорі MS Excel?

2. Якими функціями  реалізуються розгалуження в табличному процесорі MS Excel?

3. Який загальний вигляд має функція ЕСЛИ?   ИЛИ?    И?

4. Яка різниця між логічними функціями И та ИЛИ?

5. Яке значення функції ЕСЛИ(5=5;4; 5)?

6. Яке значення функції ЕСЛИ(2>1; 10; 20)?

7. Яке значення функції ЕСЛИ(1=2; 15; 25)?

8. Яке значення функції ЕСЛИ(2>1; ЕСЛИ(1>2; 5; 8); 6)?

9. Скільки параметрів має функція СЧЁТЕСЛИ()?

10.  Чи є помилка у запису СЧЁТЕСЛИ(E16:E24; >=200) ?

11.  Яка кількість параметрів у функції СУММЕСЛИ()?

12.  Яку кількість варіантів форматування можна задати в умовному форматуванні?

Практична  робота № 18 Тема. Графічний аналіз даних у середовищі MS Excel. Побудова, редагування та форматування діаграм і графіків у середовищі табличного процесора MS Excel

Мета. Набути практичні навички створення, редагування та форматування діаграм і графіків та їх елементів.

Технологія виконання роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_18, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР18.docx та зберегти його у папку ПР_18. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. Викликати табличний процесор MS Excel і створити нову книгу з іменем ПР18_<Власне прізвище>.xlsx.

Завдання 4. На робочий  аркуш Лист1 скопіювати Таблицю1 з Практичної роботи №16 та побудувати гістограму " Продаж принтерів " на окремому аркуші з такою ж назвою.

1) Виділити діапазон клітинок А2:А13 і, тримаючи натиснутою клавішу <Ctrl>, діапазон G2:G13 та натиснути клавішу F11.

2) На листі Діаграма1, використовуючи контекстне меню кожного елементу діаграми,  відредагувати Заголовок діаграми (Сумарний продаж принтерів у 2012 році), видалити легенду, змінити фон області побудови діаграми з сірого на білий, змінити формат рядів даних, включивши в підписи даних значення, а в оформленні стовпчиків гістограми використати різні способи заливки.

3) Змінити назву аркуша на Продаж принтерів.

Завдання 5. Побудуйте за даними Таблиці1 кругову діаграму (розрізаний варіант) Ціна принтерів  на тому ж аркуші (виділити діапазони А4:А13 і D4:D13).

1) Задати заголовок діаграми  Ціна принтерів у 2012 році, додати легенду та розмістити її зліва, включити в підписи опції Доли і Ключ легенды та  імена категорій.

2) Використовуючи команду Формат подписей данных,  задати параметри підписів, щоб вони не перекривалися.

3) Використовуючи команду Формат точки данных контекстного меню для секторів діаграми, замінити фон секторів відтінками світлих кольорів з використанням різних способів заливки (штриховку, текстуру, візерунок).

4) Змінити назву аркуша на Ціна принтерів.

Завдання 6. На робочий  аркуш Лист3 скопіювати таблицю Автотранспортне господарство з Практичної роботи №17.

1) За даними таблиці побудувати діаграму (графік з маркерами) Середні витрати пального на тому ж аркуші (виділити діапазон клітинок D4:D12 і G4:G12), задати параметри діаграми:

- в підписах даних – значення;

- лінії сітки – основні лінії осі Y;

- в заголовках: назва діаграми – Середні витрати пального (літрів/на 100 км),  ось Х – Тип авто,  ось Y – Кількість пального;

- таблиця даних і легенда відсутні.

2) Змінити назву аркуша на Графік витрат пального.

Завдання7. Переглянути таблиці і діаграми, при необхідності відредагувати та зберегти результати.

Завдання 8. Помістити до звіту відповіді на контрольні запитання.

Завдання 9. Перемістити папку ПР_18 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 10. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Запитання до захисту практичної роботи

1. Для чого використовують діаграми?

2. Які є типи діаграм?

3. З яких елементів складається діаграма?

4. Яке призначення Майстра діаграм?

5. Як редагують діаграми?

6. В яких випадках діаграму розташовують на одному аркуші з таблицею?

7. Як змінити розміщення діаграми?

8. Яке призначення маркерів даних?

9. Як побудувати діаграму за даними, розміщеними в несуміжних діапазонах?

10. Які є типи нестандартних діаграм?

11. Чим відрізняється область побудови діаграми від області діаграми?

12. Чим відрізняється вісь категорій від осі значень?

Практична робота №19 Тема: Створення та використання списків в середовищі MS Excel. Використання автофільтру, розширеного фільтру, зведених таблиць та зведених діаграм у середовищі MS Excel

Мета: Ознайомитися з основними поняттями списку. Навчитись впорядковувати дані у списку і здійснювати  пошук та вибірку даних за допомогою автофільтру та розширеного фільтру, створювати зведені таблиці та зведені діаграми.

Технологія виконання роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_19, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР19.docx та зберегти його у папку ПР_19. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту

Завдання 3. Викликати табличний процесор MS Excel і створити нову книгу з іменем ПР19_<Власне прізвище>.xlsx.

Завдання 4. На робочому аркуші Лист1 побудувати наведену нижче таблицю.

1. Виконати форматування  таблиці згідно зразка.

2. Виконати сортування даних за полем Назва фірми.

3. Скопіювати таблицю на Лист2, Лист3, Лист4, Лист5, Лист6, Лист7, Лист8, Лист9, Лист10.

Завдання 5. Здійснити пошук даних за визначеними критеріями використовуючи автофільтр.

1. На робочому аркуші Лист2 за допомогою автофільтру потрібно відібрати дані з інвестиційними витратами більше 100 тис. грн. і терміном окупності більше півроку.

2. На робочому аркуші Лист3 за допомогою автофільтру потрібно відібрати дані для фірми "Лідер", інвестиційні витрати яких перевищують 100 тис. грн.  і мають термін окупності більше 1 року.

3. На робочому аркуші Лист4 за допомогою автофільтру потрібно відібрати дані для фірми "Лідер", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

4. На робочому аркуші Лист5 за допомогою автофільтру потрібно відібрати дані для фірм "Лідер", "Термопласт", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

Завдання 6. Виконати фільтрацію даних таблиці моніторингу діяльності фірм за вказаними критеріями за допомогою розширеного фільтру.

1. На робочому аркуші Лист6 за допомогою розширеного фільтру відібрати дані з інвестиційними витратами більше 100 тис. грн. і терміном окупності більше півроку.

2. На робочому аркуші Лист7 за допомогою розширеного фільтру відібрати дані для фірми "Лідер", інвестиційні витрати яких перевищують 100 тис. грн.  і мають термін окупності більше 1 року.

3. На робочому аркуші Лист8 за допомогою розширеного фільтру відібрати дані для фірми "Лідер", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

4. На робочому аркуші Лист9 за допомогою розширеного фільтру відібрати дані для фірм "Лідер", "Термопласт", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

5. На робочому аркуші Лист10 за допомогою розширеного фільтру відібрати дані для фірм "Лідер", "Термопласт", коли податки більше 25 тис. грн. або   менше  20 тис. грн.

Завдання 7. Створити проміжні підсумки по полюНазва фірми.

На робочому аркуші Лист1 створити проміжні підсумки по полю Назва фірми для обчислення  суми інвестиційних витрат, виручки від реалізації продукції, поточних витрат, податків, терміну окупності, використавши команду Промежуточный итог на вкладці Данные в групі  Структура.

Завдання 8.Створити зведену таблицю та зведену діаграму для вказаних у умов на окремому робочому аркуші.

1. На робочому аркуші Лист7 за допомогою зведеної таблиці визначити максимальну виручку від реалізації продукції кожною фірмою.

2. За допомогою зведеної таблиці визначити відсоткове відношення суми поля "Інвестиційні витрати" кожної фірми до загальних витрат по цьому полю.

Завдання 9. Побудувати зведену діаграму відсоткового відношення суми поля "Інвестиційні витрати" кожної фірми до загальних витрат.

Завдання 10. Зберегти результати роботи.

Завдання 11. Помістити до звіту відповіді на контрольні запитання.

Завдання 12. Перемістити папку ПР_19 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 13. По закінченню роботи вийти з сеансу: Пуск → Завершення сеансу.

Запитання до захисту практичної роботи

1. Що таке база даних?

2. Що таке поле бази даних? Що таке запис бази даних?

3. Як виконати сортування записів у базі даних?

4. Що таке фільтрація даних?

5. Що таке складний критерій пошуку?

6. Як створити автофільтр?

7. Як створити розширений фільтр?

8. Яке призначення мають проміжні підсумки?

9. Яка технологія створення проміжних підсумків?

10. Яке призначення мають зведені таблиці і зведені діаграми?

11. Яка технологія створення зведених таблиць і зведених діаграм?

Практична робота № 20 Тема. Використання засобів оптимізації у середовищі MS Excel

Мета. Навчитись застосовувати для аналізу даних засоби надбудови MS Excel: підбір параметра, пошук рішення.

Технологія виконання  роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_20, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР20.docx та зберегти його у папку ПР_20. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. Викликати табличний процесор MS Excel і створити нову книгу з іменем ПР20_<Власне прізвище>.xlsx.

Завдання 4. Розвя’зати задачу розрахунку виплат за вкладами, використовуючи засіб Подбор параметра.

1) На робочому аркуші Лист1 побудувати таблицю для розрахунку виплат за вкладами.

2) Використовуючи функцію Подбор параметра та дані таблиці, знайти, при якому значенні терміну вкладу сума виплат становитиме 12 000 грн. Для цього слід виконати наступні дії:

– виділити комірку С16 і записати формулу ;

– запустити програму Подбор параметра;

– в діалоговому вікні Подбор параметра заповнити текстові поля;

– в діалоговому вікні Подбор параметра клацнути на кнопці ОК;

– проаналізувати отриманий результат. Яке значення терміну вкладу з’явилось в комірці С13?

3) Скопіювати таблицю для розрахунку виплат за вкладами нижче.

4) Використовуючи Подбор параметра та дані наведеної таблиці, самостійно знайти, при якому значенні відсоткової ставки сума виплат становитиме 12 000 грн.

5) Проаналізувати отриманий результат. Яке значення відсоткової ставки з’явилось в комірці С21?

6) Перейменуйте Лист1 на Підбір параметра_Прізвище.

Завдання 5. На робочому аркуші Лист2 розв’язати кубічне рівняння свого варіанту, використовуючи засіб Подбор параметра.

Завдання 6. Розв’язати задачу, використовуючи програму Поиск решения.

1) На робочому аркуші Лист3 побудувати таблицю «Відомості про випуск продукції за місяць».

2) Використовуючи програму Поиск решения, знайти оптимальний план випуску продукції (скільки одиниць товару кожного виду слід випускати), при якому витрати на виготовлення будуть мінімальними. Кількість продукції кожного виду змінюється в межах від 15 до 35 одиниць, а загальна кількість одиниць складає 100.

3) Для розв’язання задачі слід виконати наступні дії:

– виконати необхідні обчислення у стовпці Витрати на партію товару;

– у рядку Всього обчислити сумарну кількість одиниць продукції та сумарні витрати на партію продукції;

– виділити комірку, в якій обчислено сумарні витрати на партію продукції; ця комірка є цільовою;

– запустити програму Поиск решения;

– в діалоговому вікні Параметры поиска решений заповнити текстові поля;

– натиснути кнопку Добавить для внесення обмежень по загальній кількості продукції;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду не перевищує 35 одиниць;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду не менше 15 одиниць;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду  - ціле число;

– натиснути кнопку ОК, результатом буде діалогове вікно;

– натиснути кнопку Найти решение, натиснути ОК;

– проаналізувати результат у комірках F8:F13 та значення G14. Значення величин у комірках F8:F13 та G14 і є розв’язком задачі.

Завдання 7. Перейменувати Лист3 на Пошук розвязку_Прізвище та зберегти результати роботи.

Завдання 8. Помістити до звіту відповіді на контрольні запитання.

Завдання 9. Перемістити папку ПР_20 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 10. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Запитання до захисту практичної роботи

1. Яке призначення має функція Подбор параметра?

2. Які фінансово-економічні задачі можна розв’язувати за допомогою функції Подбор параметра?

3. Які задачі можна розв’язувати з використанням програми Поиск решения?

4. Яку комірку називають цільовою?

5. Як записати обмеження на значення у певних комірках в програмі Поиск решения?

6. Як задати точність розв’язку?

7. Наведіть приклади задач, які потребують використання програми Поиск решения.

Практична робота №21 Тема: Управління обчислювальним процесом за допомогою методу сценаріїв у середовищі табличного процесора MS Excel

Мета: На підставі економічних даних бізнес-ситуації використовуючи засіб Диспетчер сценариев створити три сценарії: реальний, оптимістичний, песимістичний

Технологія виконання  роботи

Завдання 1. На Робочому столі створити папку з назвою ПР_21, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ПР21.docx та зберегти його у папку ПР_21. Документ має містити номер практичної роботи, тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. Викликати табличний процесор MS Excel і створити нову книгу з іменем ПР21_<Власне прізвище>.xlsx.

Завдання 4. На робочому листі Лист1 книги  створити таблицю:

В комірку С4 ввести формулу: =B2-B3.

Завдання 5. Створення оптимістичного сценарію:

1) Приховати все активізувати Диспетчер сценариев на вкладціДанные, група Работа с данными, піктограма  Анализ "что если" і натиснути кнопку Добавить

2) ввести в поле Название сценария назву сценарію;

3) в полі Изменяемые ячейкивведіть посилання на осередки, які потрібно змінити

4)  встановити необхідні прапорці в наборі прапорців Защита;

5) натиснути кнопку Oкі ввести необхідні значення в діалоговому вікні Значения ячеек сценария;

6) щоб створити сценарій, натисніть кнопку Oк;

7) Кінцевий результат має виглядати:

Завдання 6. Створення реального сценарію:

1) активізувати Диспетчер сценариев на вкладціДанные, група Работа с данными, піктограма  Анализ "что если" і натиснути кнопку Добавить

2) ввести в поле Название сценария назву сценарію;

3) в полі Изменяемые ячейкивведіть посилання на осередки, які потрібно змінити

4)  встановити необхідні прапорці в наборі прапорців Защита;

5) натиснути кнопку Oк і ввести необхідні значення в діалоговому вікні Значения ячеек сценария;

6)  щоб створити сценарій, натисніть кнопку Oк;

7) Кінцевий результат має виглядати:

Завдання 7. Створення песимістичного сценарію:

1) активізувати Диспетчер сценариев на вкладціДанные, група Работа с данными, піктограма  Анализ "что если" і натиснути кнопку Добавить

2) ввести в поле Название сценария назву сценарію;

3) в полі Изменяемые ячейкивведіть посилання на осередки, які потрібно змінити

4) встановити необхідні прапорці в наборі прапорців Защита;

5) натиснути кнопку Oкі ввести необхідні значення в діалоговому вікні Значения ячеек сценария;

6) щоб створити сценарій, натисніть кнопку Oк;

7) Кінцевий результат має виглядати:

5. Отримуємо три сценарії:

Завдання 8. Створення підсумкового звіту за сценаріями :

1) активізувати Диспетчер сценариев на вкладціДанные, група Работа с данными, піктограма  Анализ "что если" і натиснути кнопку

2) натиснути кнопку Отчет;

3) встановити перемикач в положення Структура або Сводная таблица;

4) в полі Ячейки результата ввести посилання на осередки, значення яких були змінені за допомогою сценаріїв (як роздільник посилань використовується кома);

5) натиснути кнопку Ок.

Завдання 9. Зберегти результати роботи.

Завдання 10. Помістити до звіту відповіді на контрольні запитання.

Завдання 11. Перемістити папку ПР_21 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 12. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Запитання до захисту практичної роботи

1. Що таке Диспетчер сценариев?

2. Які фінансово-економічні задачі можна розв’язувати за допомогою засобу Диспетчер сценариев?

3. Технологія створення сценарію

4. Технологія створення підсумкового звіту за сценаріями

Індивідуальна робота до ЗМ3 Тема. Розв’язування задач професійного спрямування у середовищі MS Excel

Мета. Навчитись виконувати обчислення в задачах економічного змісту  засобами MS Excel.

Технологія виконання  роботи

Завдання 1. На Робочому столі створити папку з назвою ІР_ЗМ3, в яку поміщати результати своєї роботи.

Завдання 2. У редакторі  WordPad створити текстовий файл Звіт ІР до ЗМ3.docx та зберегти його у папку ІР_ЗМ3. Документ має містити заголовок,  тему, мету, результати виконання завдань у вигляді скріншотів та супроводжуючого тексту.

Завдання 3. Викликати табличний процесор MS Excel і створити нову книгу з іменем ІР до ЗМ3_<Власне прізвище>.xlsx..

Завдання 4.Створити Лист1 з розрахунками ціни та вартості товарів, Лист2 із Звітом за деякий період про закуплені та продані товари з діаграмами (за матеріалами власної бази даних)

Завдання 5. Імпортувати з БД власної фірми список співробітників на Лист3(перейменувати у  Зарплата) у форматі ПІБ, посада, тарифна ставка, підготувавши в БД відповідний запит. Встановити власні імена для комірок з ПІБ, посадою та тарифною ставкою для кожного з працівників.

Завдання 6. На Лист4 побудувати таблицю із Зарплата на 12 місяців для власної фірми.

Завдання 7. Додати Лист5 зведених даних за рік по співробітникам. На листі зведених даних побудувати Спарклайн по кожному співробітнику

Завдання 8. Підготувати захист листів для введення даних з елементами захисту

Завдання 9. Розібратися з поняттям Консолідація даних та створити відповідну таблицю.

Завдання 10*. Розв’язати задачу за варіантом:

Завдання 5. Зберегти результати роботи.

Завдання 6. Перемістити папку ІР_ЗМ3 з Робочого столу на  диск Z:  у папку Практичні роботи Власне прізвище та передати на SkyDrive.

Завдання 7. По закінченню роботи вийти з сеансу:     Пуск → Завершення сеансу …

Варіанти індивідуальних завдань

Варіант 1. Побудувати таблицю розрахунків страхування майна від крадіжки, пожежі і руйнування (у довільній комбінації). Бажаний вид страхування позначається знаком "+". Клієнт може застрахуватися на будь-яку <Страхову суму>, для чого робить <Страховий внесок> у розмірі 10% від страхової суми, якщо майно страхується від пожежі, 8% - якщо від руйнування, 7% - від крадіжки. Страховка від крадіжки знижується на 1 % при наявності сталевих дверей і ще на 2%, якщо в під'їзді є вахтер. Внесок зменшується на 1%, якщо страхування здійснюється на всі види страхових випадків одразу.

Варіант 2. Визначити добовий заробіток робітників у залежності від кількості годин, відпрацьованих ними в денну (з 9:00 до 16:00 годин) і вечірню (решта часу) зміни. Розцінки на роботу у вечірню зміну на 60% вище, ніж у денну. Крім того, якщо робітник відробив більш ніж 8 годин, йому передбачена доплата за понаднормовий час (години понад восьму) у розмірі 50% від звичайного тарифу. Для кожного працівника задані фактичні початок і кінець його зміни.

Варіант 3. Обчислити вартість ремонту квартири. Вона складається з <Вартості ремонту стелі> (визначається множенням <Площі> квартири на <Ціну фарбування 1 кв.м стелі>) плюс <Вартість ремонту стін> (визначається множенням <Площі стін> на <Ціну

Варіант 4. Обчислити розмір заробітку продавців фірми. Зарплата працівника складається з двох частин - фіксованого невеликого <0кладу>, що залежить від <Розряду>, і <Премії>, що залежить від фактичного обсягу продажів (<Продано>). Якщо обсяг продажів менше за <Норми>, зарплата складає 10% від <Продажу>; якщо більше - 20%.

Варіант 5. Обчислити <Вартість загалом> товару, що зберігається на складі магазина. Вона визначається вартістю першого гатунку товару (<Число одиниць 1 гатунку>, помноженої на <Ціну 1 гатунку>) плюс вартість другого гатунку (<Число одиниць 2 гатунку>, помножена на <Ціну 1 гатунку>, зменшену на <Відсоток знижки 2 гатунку>), плюс вартість товару 3 гатунку, отриману аналогічним образом, плюс вартість простроченого товару за ціною 10% від ціни 1 гатунку.

Крім того, потрібно визначити факт затоварення чи недостачі товару. Якщо сукупна вартість будь-якого товару всіх гатунків складає величину більшу за 1000 грн., у стовпці <Стан запасів> формується слово "Надлишок". Якщо вартість менше 500 грн. -"Недостача". Якщо дорівнює нулю - слово "Немає". В інших випадках не видається ніякого повідомлення - порожні лапки ("").

Варіант 6. Побудувати таблицю розрахунків страхування майна від крадіжки, пожежі і руйнування (у довільній комбінації).

Бажаний вид страхування позначається знаком "+". Клієнт може застрахуватися на будь-яку <Страхову суму>, для чого робить <Страховий внесок> у розмірі 10% від страхової суми, якщо майно страхується від пожежі, 8% - якщо від руйнування, 7% - від крадіжки. Страховка від крадіжки знижується на 1 % при наявності сталевих дверей і ще на 2%, якщо в під'їзді є вахтер. Внесок зменшується на 1%, якщо страхування здійснюється на всі види страхових випадків одразу.

Варіант 7. Визначити добовий заробіток робітників у залежності від кількості годин, відпрацьованих ними в денну (з 9:00 до 16:00 годин) і вечірню (решта часу) зміни. Розцінки на роботу у вечірню зміну на 60% вище, ніж у денну. Крім того, якщо робітник відробив більш ніж 8 годин, йому передбачена доплата за понаднормовий час (години понад восьму) у розмірі 50% від звичайного тарифу. Для кожного працівника задані фактичні початок і кінець його зміни.

Варіант 8. Обчислити вартість ремонту квартири. Вона складається з <Вартості ремонту стелі> (визначається множенням <Площі> квартири на <Ціну фарбування 1 кв.м стелі>) плюс <Вартість ремонту стін> (визначається множенням <Площі стін> на <Ціну обклеювання/фарбування/ оббивки 1 кв. м. стін >).

Вид обробки стін позначається буквами "обкл", "фарб" чи "об" у стовпці <Обробка стін>. Допускається відсутність будь-якої обробки стін. Вартість термінового ремонту збільшується на 40%. Цей факт відбивається (якщо є) у стовпці <Терміновість> знаком "+".

Варіант 9. Обчислити розмір заробітку продавців фірми. Зарплата працівника складається з двох частин - фіксованого невеликого <0кладу>, що залежить від <Розряду>, і <Премії>, що залежить від фактичного обсягу продажів (<Продано>). Якщо обсяг продажів менше за <Норми>, зарплата складає 10% від <Продажу>; якщо більше - 20%.

Варіант 10. Обчислити <Вартість загалом> товару, що зберігається на складі магазина. Вона визначається вартістю першого гатунку товару (<Число одиниць 1 гатунку>, помноженої на <Ціну 1 гатунку>) плюс вартість другого гатунку (<Число одиниць 2 гатунку>, помножена на <Ціну 1 гатунку>, зменшену на <Відсоток знижки 2 гатунку>), плюс вартість товару 3 гатунку, отриману аналогічним образом, плюс вартість простроченого товару за ціною 10% від ціни 1 гатунку.

Крім того, потрібно визначити факт затоварення чи недостачі товару. Якщо сукупна вартість будь-якого товару всіх гатунків складає величину більшу за 1000 грн., у стовпці <Стан запасів> формується слово "Надлишок". Якщо вартість менше 500 грн. -"Недостача". Якщо дорівнює нулю - слово "Немає". В інших випадках не видається ніякого повідомлення - порожні лапки ("").

Практична  робота №22 Тема: Поточний контроль знань до ЗМ3

Мета: Визначити рівень опанування студентами навчального матеріалу за темами ЗМ3. Перевірити результати самостійної роботи студентів. Оцінити індивідуальну роботу студентів.

Теоретичні питання

1. Яким чином завантажити програму MS Excel?

2. Наведіть способи виходу з програми MS Excel.

3. Як створити нову робочу книгу MS Excel?

4. Наведіть структурні елементи вікна програми MS Excel.

5. Для чого використовують Область задач у MS Excel?

6. Назвіть основні панелі Області задач MS Excel.

7. Що розуміють під робочою книгою MS Excel?

8. Які формати даних використовуються у MS Excel?

9. Наведіть способи адресації комірок MS Excel.

10. Наведіть способи виділення комірок, стовпчиків, рядків.

11. Яким чином виділити весь аркуш робочої книги?

12. Як вилучити дані з комірок електронної таблиці?

13. Як вставити та вилучити непотрібні стовпчики та рядки електронної таблиці?

14. Наведіть типи аркушів робочої книги MS Excel.

15. Яким чином змінити назву аркуша робочої книги?

16. Які структурні елементи може містити формула?

17. Наведіть порядок виконання операцій у формулах.

18. Яким чином можна використовувати вбудовані функції у формулах?

19. Як створити діаграму чи графік у середовищі MS Excel?

20. Наведіть кроки створення діаграм та графіків.

21. Які типи діаграм використовуються у MS Excel?

22. Яким чином викликати Мастер функций?

23. Наведіть основні категорії функцій, що можна використовувати у MS Excel?

24. Яким чином задають аргументи функцій?

25. Що таке база даних у MS Excel?

26. Що називають полем та записом у базах даних Microsoft Excel?

27. Наведіть список полів діалогового вікна Форма.

28. Яке призначення кнопки Критерии діалогового вікна Форма?

29. Наведіть послідовність операцій для сортування даних у MS Excel.

30. Що таке фільтрація бази даних?

31. Яка різниця між автофільтром і розширеним фільтром?

32. Яким чином можна переглянути всі записи бази даних після виконання операції фільтрації?

33. Для чого використовуються надбудови у MS Excel?

34. Як можна створити зведену таблицю?

35. Яке призначення зведеної таблиці?

36. Для чого використовують засіб Поиск решения?

37. Для чого використовують сценарії?

38. Для чого використовують макроси у MS Excel?

39. Як надрукувати окрему таблицю або діаграму робочої книги MS Excel?

40. Як створити колонтитули для кожного надрукованого аркуша?

Приклад практичного завдання

1. Створити нову книгу MS Excel. Перший лист має назву ___. На даному листі виконати імпорт даних довільного товару з сайту ______ з автоматичним оновленням при відкритті документу

2. Другий лист має назву ____. На даному листі виконати імпорт даних товарів Вашої фірми з БД  з автоматичним оновленням при відкритті документу

3. Третій лист має назву ______. На даному листі виконати розрахунок мінімальної, максимальної та середньої ціни товару з листа ___ та з листа ____

4. Четвертий лист має назву ___ та містить зв'язані дані з листа___ . За даними побудувати дві діаграми

5. П'ятий лист має назву _____ та містить розрахунки ціни продажу по ціні закупки (на основі даних з листа ___) з введенням %надбавки. Захистити лист таким чином, щоб користувач міг ввести тільки %надбавки в діапазоні від ____ до ____

6. Захистити вказаний діапазон комірок (___) таким чином, щоб тільки Ви та Викладач  могли редагувати дані без введення паролю.

7. Книгу зберегти в папці ______.

З повагою ІЦ “KURSOVIKS”!