Posobie_1_chast2_Excel

Контрольні питання

1.Проаналізуйте, які рівні захисту робочої книги Excel Ви знаєте?

2.Як захистити документ від несанкціонованого доступу до нього інших користувачів?

3.Створений Вами документ містить коштовні розрахунки, результати яких можуть бути використані співробітниками Вашого відділу. Який вигляд захисту документа Ви виберете, якщо потрібно, щоб усі виконані в книзі Ехсel розрахунки залишалися непошкодженими?

4.Подумайте, який вигляд захисту Ви виберете, якщо потрібно захисти діаграму, що знаходиться на одному з листів відкритого документа?

5.При яких умовах Ви зможете зняти захист з листка, установлену кимось іншим?

6.Для чого застосовуються обмеження на введення даних у комірку?

7.Як установити перевірку даних у комірці на відповідність призначеним умовам?

Література: [1], [4], [5], [6], [7], [8], [11], [12], [13], [16], [19], [21], [22], [23], [25], [26].

90

Практичне заняття № 9

Тема дисципліни: Microsoft Office. Табличний процесор EXCEL (рівень експерта).

Тема заняття: Microsoft Office. Табличний процесор EXCEL.

Мета заняття: закріпити теоретичні знання щодо роботи з даними електронної таблиці, набути практичні навички використання таблиць підстановки з одним вхідним параметром, тблиць підстановки з двома вхідними параметрами, створення сценаріїв, створення звітів по сценаріях, підбору параметра, використання надбудови Пошук рішення.

Хід виконання роботи

1.Увімкніть комп’ютер, увійдіть в систему під ім’ям USER.

2.Завантажте табличний процесор Microsoft Excel.

При виконанні практичних завдань ретельно ознайомтесь з розділами довідки про використання таблиць підстановки з одним вхідним параметром, тблиць підстановки з двома вхідними параметрами, створення сценаріїв, створення звітів по сценаріях, використання надбудов Підбор параметра та Пошук рішення.

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

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

Для створення таблиці підстановки необхідно:

•розмістити дані для підстановки в стовпці (або рядку);

•в комірку, що знаходиться правіше і вище стовпця даних (або ліворуч і нижче рядка даних) помістити формулу, у яку Ви хочете підставити ці дані;

•виділити діапазон, що містить дані, формулу й комірки, у які буде поміщений результат;

•вибрати команду Данные/Таблица подстановки;

•у діалоговому вікні Таблица подстановки натиснути поле Подставлять значения по строкам - якщо дані були розташовані в стовпці, або поле Подставлять значения по столбцам в - якщо дані були розташовані в рядку;

•натиснути комірку, посилання на яку необхідно замінити підстановчими даними; натиснути ОК.

3.Створіть лист Таблиця підстановки1 у книзі ПР9_Прізвище.

4.Заповніть таблицю підстановки з одним вхідним параметром, що показує

91

зміну розміру чистого прибутку (комірка В13) у залежності від росту знижки, наданої замовникам (комірку В6).

5.Заповніть другий стовпець таблиці на листі Таблиця підстановки1 обчисленнями по формулі, що розраховує відсоток чистого прибутку (комірка В14).

92

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

Якщо потрібно підставляти дані замість значень, що утримуються в двох комірках, варто використовувати таблицю підстановки з двома вхідними параметрами. Для цього треба:

•розмістити дані для підстановки в рядку і стовпці таким чином, щоб у отриманій таблиці, лівий верхній комірку залишався вільної;

•у лівий верхній комірці помістити формулу, у яку слід підставляти дані;

•дати команду Данные/Таблица подстановки;

•у вікні введення натиснути поле Подставлять значения по строкам в, потім ту комірку, посилання на яку заміняється на дані, що містяться в стовпці таблиці;

•у полі Подставлять значения по столбцам в натиснути комірку, посилання на яку заміняється даними, що містяться в рядку; натиснути ОК.

6.Перейдіть на лист Таблиця підстановки2.

7.Заповніть таблицю підстановки з двома вхідними параметрами, що ілюструє залежність величини чистого прибутку від знижки і кількості продажів. (Підказка: підставте значення по рядках в комірку В6, значення по стовпцях – в комірку В5).

8.На листі Таблиця підстановки2 змініть значення комірки D17 на 990. Подивіться, які зміни відбулися з таблицею.

93

9.Перейдіть на лист Вправа1.

10.Скопіюйте формулу з комірки В5 в комірку В7.

11.Заповніть таблицю залежності виплати по внеску від терміну і розміру внеску. (Підказка: підставляйте дані по стовпцях в комірку В4, дані по рядках – в комірку В3).

94

Створення сценаріїв

Використовуючи сценарії, можна зберегти в пам'яті комп'ютера кілька наборів вихідних даних так, щоб їх можна було швидко завантажити. Кожний сценарій відповідає одному наборові значень вихідних даних.

Для створення сценарію необхідно:

•вибрати команду Сервис/Сценарии;

•у вікні Диспетчер сценариев натиснути кнопку Добавить;

•у полі введення Название сценарияввести назву створюваного сценарію;

•у полі введення Изменяемые ячейки ввести посилання на діапазон змінюваних комірок, які можуть бути як суміжними, так і несуміжними;

•натиснути ОК;

•у вікні Значение ячеек сценария змінити значення комірок, якщо необхідно;

•натиснути ОК.

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

12. Перейдіть на лист Сценарии.

95

13.Створіть сценарій Варіант1, що показує, як зміниться чистий прибуток (комірка В15) при ціні за од. товару - 620грн., кількості продажів - 4000, і знижці 15%.

14.Створіть сценарій Варіант2, що показує, як зміниться чистий прибуток (комірка В15) при ціні за од. товару - 551 грн., кількості продажів - 6000, і знижці 20%.

Імена сценаріїв на тому самому листі повинні відрізнятися.

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

•виділити зі списку необхідний сценарій;

•якщо сценарій повинний бути змінений, натиснути кнопку Изменить і зробити зміни даних;

•якщо обраний сценарій повинний бути віддалений, натиснути кнопку Удалить;натиснути кнопку ОК.

Щоб захистити сценарій від небажаних змін, у вікні Зміна сценарію

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

96

Звіт по сценаріях

Для порівняння декількох сценаріїв створюється звіт, що узагальнює їх на одній сторінці. Звіт – це лист, на якому представлені значення змінюваних і результуючих комірок, що відповідають даному сценарію.

Для створення звіту у виді структури треба:

•у діалоговому вікні Диспетчер сценариев натиснути кнопку Отчет;

•у діалоговому вікні Отчет по сценарию поставити перемикач у полі структура;

97

•ввести в поле Ячейки результата посилання на діапазон комірок, що містить формули, які залежать від змінюваних комірок;

•натиснути ОК.

Уробочу книгу буде доданий новий лист, що містить звіт по сценарії.

15. Створіть звіт Структура по сценаріях Варіант1 і Варіант2.

Щоб обчислення на листі виконувалися по одному зі створених сценаріїв, необхідно:

•у діалоговому вікні Диспетчер сценариев вибрати зі списку необхідний сценарій;

•натиснути кнопку Вывести.

16. На листі Сценарії виведіть сценарій Варіант1.

Підбір параметра

Підбор параметра – один із засобів аналізу даних. При цьому значення комірок, що містять параметри, змінюються так, щоб цільова комірка одержала задане значення.

Щоб підібрати потрібне значення комірки, треба:

98

•дати Сервис/ Подбор параметра – відкриється діалогове вікно Подбор параметра;

•у полі введення Установить в ячейке увести посилання на комірку, що містить формулу, натиснувши цю комірку;

•у полі введення Значение увести значення, що задається;

•у полі введення Изменяя значение ячейки увести посилання на комірку, значення якої треба підібрати, клацнувши цю комірку; натиснути ОК;

•у діалоговому вікні, Результат подбора параметра:

9 натиснути OK, якщо результат відповідає необхідним вимогам;

9 в іншому випадку натиснути Отмена для повернення до вихідних значень

комірок.

17. Перейдіть на лист Підбір параметра.

18.Пізнайте, за якою ціною потрібно продавати товар, щоб у перший рік продажів одержати прибуток 25%. Для цього:

f у полі Установить в ячейке діалогового вікна Подбор параметра уведіть посилання на комірку В16;

fу полі введення Значение уведіть значення 25%;

fу полі введення Изменяя значение ячейки уведіть посилання на комірку В6.

99