Методичні вказівки до практичної роботи №8 - Створення елементів управління на робочому листі Excel

Практична робота №5

Тема: Створення елементів управління на робочому листі Microsoft Excel

Навчальна мета:

- Навчитися створювати елементи управління (поля зі списком, перемикачі і прапорці);

- Закріпити навики по створенню формул і використанню функцій Excel.

Матеріально-технічне забезпечення:

Комп'ютер з процесором 486 (і вище) і об'ємом оперативної пам'яті не менше 12 МБайт, операційна система Windows.

Теоретичні відомості.

При створенні форми користувача Excel надає можливість додавати до робочого листа елементи управління. Елементи управління дозволяють запропонувати користувачеві вибір різних параметрів. Для створення таких елементів управління як поле з списком, перемикач, кнопка, прапорець та ін. використовується панель інструментів Форма.

Прапорець — елемент, який вмикає або вимикає дію певного параметра. Одночасно може бути встановлено декілька прапорців на листі.

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

Поле з списком — це список, що розкривається. Елемент, який вибирається із списка, відображається в текстовому полі.

Розглянемо роботу з елементами управління панелі Форма на прикладі процеса розрахунку вартості зборки комп’ютера заданої конфігурації. Остаточний вигляд робочого листа, який ми маємо отримати, показаний на рисунку 1.

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

Всі дії і розрахунки виконуватимуться на листі Кошторис. На додаткових листах Процесори, Пам’ять і Монітори розміщуватимуться відповідні прайс-листи для вибору конфігурації комп’ютера.

Робота виконуватиметься за декілька етапів:

1st етап Підготовка основного текста кошторису — заповнення робочого листа “Кошторис”;

2nd етап Підготовка списка процесорів, моніторів, модулів пам’яті і вінчестерів, вибір з списку з використанням елемента управління Поле з списком, вибір значень з цих списків і обчислення вартості всіх комплектуючих;

3rd етап  Обчислення вартості гарантії з використанням елемента управління Перемикач;

4th етап  Обчислення вартості доставки з використанням елемента управління Прапорець;

5th етап Кінцевий розрахунок загальної вартості заказу — обчислення вартості комп’ютера, вартості гарантії і доставки, визначення загальної вартості заказу в гривнях.

Етап 1. Підготовка основного текста кошторису.

Виконаємо наступні дії:

• Введення тексту;

• Форматування тексту;

• Найменування робочих листів;

• Виведення поточної дати на листі.

Покроково:

1) Виділити комірку А1

2) Ввести текст “Комплектація комп’ютера”

3) Ввести текст в комірки А3, А5, А7, А9, А11, А13, А15, А17, А19, С3,  як показано на рис.1.

4) Для перейменування робочого листа клацнути правою кнопкою миші по ярличку  робочого листа Лист 1. З контекстного меню вибрати команду “Перейменувати”. Ввести нову назву листа. “Enter”.(Аналогічно перейменувати і інші листи).

Попрацюємо над зовнішним виглядом бланка кошторису:

Виділити діапазон комірок А1:Е1

5) Натиснути кнопку на панелі форматування. Текст “Комплектація комп’ютера” розміщуватиметься  по центру виділених комірок.

6) Натиснути кнопки і текст стане виділеним напівжирним шрифтом.

7) За допомогою вказівника миші встановимо таку ширину стовпчиків, щоб текст в комірках вміщувався повністю

А зараз зробимо так, щоб поточна дата проставлялася автоматично

8) Клацнути мишею в межах комірки В3 і натиснути кнопку  на стандартній панелі інструментів. На екрані з’явиться діалог так званого “Мастера функций”, який призначений для полегшення введення функцій в комірки ЕТ.

9) Клацнути мишею по рядку “Дата и время” в списку “Категория”.

10) Вибрати функцію “Сегодня” в списке “Функция”.  ОК. В комірці В3 з’являється поточна дата.

Останній крок: введення поточного курсу долара

11) Виділити комірку D3 і ввести значення поточного курсу долара. Вигляд робочого листа на даному етапі роботи.

Етап 2. Підготовка списка процесорів, моніторів, модулів пам’яті і вінчестерів

• Підготуємо прайс-лист на різні типи процесорів. Прайс-лист буде оформлено у вигляді окремого робочого листа, щоб при подальших змінах цін можна було б легко і зручно відкоректувати тільки цей робочий лист.

• На основному робочому листі встановимо елемент управління Поле з списком, через який зможемо проглянути список процесорів і вибрати необхідний тип.

• Використовуючи функцію “Индекс” зв’яжемо комірку на робочому листі “Кошторис” з ціною вибраного процесора.

Покроково:

1) Клацнути мишкою по ярличку “Процесори”. На екрані з’явиться порожній робочий лист.

3) Клацнути мишкою по ярличку “Кошторис”. Повертаємось до основного робочого листа.

4) Вибрати команду “Вид”=>”Панели инструментов”=>”Формы”. На екрані з’являється нова панель з елементами управління. Ця панель знадобиться нам і надалі.

А зараз виконаємо зв’язування інформації на різних листах робочої книги

5) Натиснути кнопку на панелі інструментів ”Формы”.

6) Вказівник мишки перетворюється на “Хрестик прицілу”, його потрібно перемістити у верхній лівий вугол комірки С5. Спочатку не натискайте на кнопку миші, а коли хрестик співпаде з вуглом комірки С5, протягніть вказівник-хрестик так, щоб він перейшов у комірку D5 приблизно на треть ширини комірки. На робочому листі “Кошторис” з’явиться елемент управління “Поле з списком”.

Але поки що це — тільки рамка, а нам потрібно в цю рамку вставити список типів процесорів.

Клацнувши правою кнопкою мишки, викликати контекстне меню для створеного елемента управління. Вибрати команди “Формат объекта”=>“Формат элемента управления“. На рис.5 показана вкладка “Элемент управления”.
Натиснути кнопку діалогу  1 (рис.5) і діалогове вікно “Формат элемента управления” перетворюється на однорядкове поле ввода (рис.6), в якому з’являється текстовий курсор.

9) Клацнути мишкою по ярличку “Процесори”. Відкриється робочий лист “Процесори”. В полі вводу діалогу “Формат элемента управления” з’явиться  рядок “Процесори!”.

11) Натиснути кнопку діалогу 3 (див. рис.7) . Діалогове вікно знову розгортається в повний розмір.

12) Натисути кнопку діалогу 2 (див.рис.5). Діалогове вікно знову згортається в однорядкове поле ввода і стає активним робочий лист “Кошторис”, елемент якого ми форматуємо.

13)  Клацнути мишкою по комірці Е5. Межі комірки виділяються пунктиром, а в полі ввода з’являється рядок “$E$5”.                

Таким чином ми вказали, в якій комірці на робочому листі “Кошторис” розміщуватиметься номер вибраного елемента.

14)  Натиснути кнопку діалога 3(див.рис.7). Вікно знову розгортається.

15) ОК. Елемент управління досі залишається поміченим маркерами (маленькими квадратиками”. Щоб маркери зникли, клацніть мишею в будь-якому іншому місці листа.
Натиснути кнопку вибора з списка. Список відкриється і з нього можна вибрати будь-який тип процесора.

Отже, ми вже можемо вибрати тип процесора, але ми маємо розмістити ціну цього процесора в комірці на робочому листі “Кошторис”. Розмістимо ціну процесора в комірці В5.

17)  Клацнути мишкою по комірці В5  і натиснути кнопку  на стандартній панелі інструментів. З’являється “Мастер функций”. (рис.9)

18)  Вибрати рядок “Ссылки и массивы” в списку “Категория”. В списку “Функция” вибрати рядок “Индекс”. ОК.

На екрані з’являється наступний діалог  “Мастера функций”. За замовченням в ньому виділений рядок “массив; номер_строки;номер_столбца”. Нам потрібна саме ця функція. ОК. На екрані з’являється діалог для визначення параметрів функції «Индекс».

В цьому вікні потрібно вказати діапазон комірок, з яких вибиратиметься необхідне значення, і комірку, в якій знаходитиметься номер певного рядка діапазона. Діапазоном має бути список цін на процесори на робочому листі “Процесори”.

19)  Натиснути кнопку 4.

20)  Клацнути мишкою по ярличку “Процесори”. Відкриється обочий лист “Процесори”, а в полі ввода параметрів йункції з’явиться рядок “Процесори!В3:В7”

Де ж буде знаходитися номер певного рядка з діапазону комірок?

21) Натиснути кнопку 3 (див.рис.7), діалогове вікно розгортається, натиснути кнопку 5 (див.рис.10). Знову згортається діалогове вікно. Відкривається робочий лист “Кошторис”.

22)  Клацнути мишею по комірці Е5. Межі комірки виділяються пунктиром, а в поле ввода з’являється рядок “Е5”

23) ОК. Діалог зникає, а в комірці Е5 з’являється ціна вибраного процесора.

Завдання для самостійного виконання.

Виконати аналогічні дії для вибору із списка модуля пам’яті, вінчестера і монітора.

Етап 3 . Обчислення вартості гарантії з використанням елемента управління Перемикач;

Наш кошторис буде передбачати два види гарантії: безплатна — терміном на півроку, або вартістю 10% від ціни замовника — терміном на рік.

Задамо цю умову на робочому листі “Кошторис”. Спочатку встановимо перемикач для першого виду гарантії.

1)  На панелі інструментів “Формы” натиснути кнопку

2) Перемістити вказівник миші у верхній лівий вугол комірки С15. Натиснути кнопку миші і, не відпускаючи її, провести вказівник-хрестик до лівого верхнього вугла комірки D15. На робочому листі з’явиться поле Перемикача.

3) Клацнути мишкою всередині рамки редагування, ввести замість стандартного заголовка новий заголовок: 6міс.

А зараз необхідно задати параметри елемента управління.

4) Клацнути правою кнопкою миші на полі Перемикача і в контекстному меню вибрати команду “Формат объекта”.

Встановимо другий перемикач — для гарантії на рік.

5) Спочатку звільнемо місце для нього місце в комірці. Викликати контекстне меню для перемикача “6 міс”. В полі елемента управління увімкнеться режим редагування.

6)  Натиснути клавішу “Esc”. Контекстне меню зникне.

7) Вказівником миші зменшити розмір Перемикача приблизно до половинирозміру комірки С15.

8) В комірці С15, зліва від першого, встановимо другий перемикач, виконуючи аналогічні дії.

9) Викликати діалог “Формат элемента управления” в даному випадку не треба, тому що параметри другого перемикача встановлюються автоматично. Перевірте функціонування перемикачів.

А зараз нам необхідно виконати наступні дії:

- По-перше, ввести в комірку В15 формулу обчислення вартості гарантії;

- По-друге, використати в цій формулі значення номера активного перемикача.

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

10) Виділити комірку В13. Натиснути кнопку автосуми на панелі інструментів. В комірці В13 і в рядку формул з’явиться формула, яка містить в собі функцію знаходження суми.

11) Виділити мишкою діапазон комірок В5:В11. Параметри функції СУММ набудуть необхідних значень.

12) Натисніть кнопку закінчення вводу формули, введення формули скінчиться. А в комірці В13 з’явиться значення сумарної вартості комплектуючих комп’ютера.

А зараз приділемо увагу формулі вартості гарантії.

13) Виділити комірку В15. Ввести розрахункову формулу =В13*0,1*(Е15-1). ”Enter”. В комірці В15 з’явиться значення 0.

14)  Активізуємо перемикач “1 рік”. В комірці В15 з’явиться значення, яке дорівнює 10% від вартості комп’ютера.

Етап 4. Обчислення вартості доставки з використанням елемента управління Прапорець;

Прапорець використовується в ситуації, коли необхідно вирішити, потрібна чи не потрібна доставка комп’ютера. Для обробки результатів нашого рішення працюватиме логічна функція “ЕСЛИ”.

Натиснути кнопку на панелі інструментів “Формы”.

15) Перемістити вказівник миші у верхній лівий вугол комірки С17. Натиснути кнопку миші і, не відпускаючи кнопку, перемістити вказівник-хрестик приблизно на дві треті ширини комірки С17. На робочому листі з’явиться поле Прапорця в режимі редагування, з стандартним заголовком “Флажок…”.

16) Клацнути мишкою всередині рамки редагування і ввести новий заголовок : “Потрібна”

Задамо параметри елемента управління.

17) Клацнути правою кнопкою миші на полі Прапорця і вибрати в контекстному меню команду “Формат объекта”. Встановити  параметри елемента управління Прапорець.

В комірку В17 введемо формулу вартості доставки.

18)  Клацнути мишкою по комірці В17 и натиснути кнопку на Стандартній панелі інструментів. З’являється вже знайомий нам діалог “Мастер функций”. У вікні “Мастера” вибрати рядок “Логические” в списку “Категория”. ОК.

Значення функції “ЕСЛИ” обчислюється на основі трьох її параметрів.

Перший параметр —логічний вираз, який може набувати значення “ИСТИНА” або “ЛОЖЬ”. Якщо логічний вираз має значення “ИСТИНА”, то значенням функції буде значення другого параметра, а якщо логічний вираз має значення “ЛОЖЬ”, то значенням функції буде значення третього параметра. В нашому випадку логічним виразом буде значення комірки Е17. Тобто, коли прапорець встановлений, в комірці В17 з’явиться значення 50, а коли прапорець знять— 0.

19) ОК. Діалог опису параметрів зникає, а в комірці В17 з’являється числове значення —50 або 0.

20) Додати знак $ до значень в стовпчику В.

Етап 5. Обчислення вартості комп’ютера, вартості гарантії і доставки, визначення загальної вартості заказу в гривнях.

Завдання:

Виконати самостійно.

З повагою ІЦ "KURSOVIKS"!