Завдання 3

1. Уважно прочитати теоретичні відомості.

2. У робочій книзі МS Excel створити 5 робочих листів і надати їм відповідно назви: Кошторис, Процесори, Пам'ять, Вінчестери, Монітори.

3. На робочих листах Процесори, Пам'ять, Вінчестери, Монітори створити прайс-листи з назвами та цінами, згідно рис. 6, 13, 16, та 19. Підготувати основний текст кошторису - заповнити основний робочий лист Кошторис згідно рис. 5.

4. На основному робочому листі Кошторис встановити такі елементи керування:

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

• для пам'яті використати елемент керування Счетчик;

• для вінчестерів - елемент керування Счетчик;

• для моніторів - елемент керування Поле со списком;

5. Розрахувати вартість гарантії, використовуючи такий елемент керування, як Переключатель.

6. Розрахувати вартість доставки за допомогою елемента керування Флажок, використовуючи при цьому логічну функцію ЕСЛИ.

7. Провести остаточний розрахунок загальної вартості замовлення -підсумувати вартість комп'ютера, вартість гарантії і доставки в ум, одн. та в гривнях.

8.Зберегти файл звіту та робочу книгу Кошторис в особистій папці.

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

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

Рис. 5. Робочий лист розрахунку ціни на комп'ютер замовленої конфігурації

Методичні вказівки

1. Відкрити Місrosoft Excel. У робочій книзі є в наявності 3 листа. Додати ще 2, натиснувши правою кнопкою миші по надпису Лист1 і вибравши з контекстного меню команди Добавить Лист. Далі надати 5-ти листам назви: Кошторис, Процесори, Пам'ять, Вінчестери, Монітори, натиснувши правою кнопкою миші по ярличку листа і вибравши з контекстного меню команду Переименовать.

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

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

■ Ввести текст Складання комп'ютера, застосувавши напівжирний шрифт (піктограма ).

■ Організувати виведення сьогоднішньої дати. Для цього ввести в комірку АЗ текст "Дата", а в комірку ВЗ сьогоднішню дату, вибравши на панелі інструментів піктограму і застосувавши функцію СЕГОДНЯ з категорії Дата и время.

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

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

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

■ Відкрити список масштабів на панелі інструментів Стандартная.

■ Вибрати рядок По выделению. Масштаб відображення робочого листа зміниться так, що на екрані поміститься 19 рядків, і в заголовку списку масштабів з'явиться нове значення.

4. Оформити прайс-лист для процесорів, а потім підключити його дані до основного листа. Для цього виконати наступні дії:

■ Натиснути мишею на ярличку Процесори. На екрані відкриється порожній робочий лист.

■ Ввести дані, як показано на рис. 6. Зліва, в стовпці А, - назви типів процесорів, справа, у стовпці В - ціни в доларах.

■ Натиснути мишею на ярличку Кошторис для повернення до основного робочого листа розрахунку.

■ Вибрати команду меню Вид ► Панели инструментов ► Формы. На екрані поверх робочого Листа з'явиться нова панель з елементами керування.

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

5. Тепер необхідно зв'язати різні листи робочої книги.

Рис. 6. Список типів процесорів та їх цін

Створення елементу керування Поле со списком

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

■ Перемістити вказівник миші (має вигляд «хрестику прицілу») до верхнього лівого кута комірки С5.

■ Після того, як хрестик сполучиться з вищезгаданим кутом комірки С5, натиснути кнопку миші і, не відпускаючи кнопку, акуратно провести покажчик-хрестик по лінії, що розділяє рядки 4 і 5 так, щоб хрестик виявився в комірці В5, десь на третину ширини комірки від її лівого краю.

■ При переміщенні вказівник малює прямокутник на робочому листі -контур майбутнього елемента керування. Цей контур повинен закривати комірку С5.

■ Встановити контур прямокутника так, як показано на рис. 7. Відпустити кнопку. Елемент керування Поле со списком встановиться на робочому листі.

Тепер Поле со списком набуло потрібного вигляду. Але поки що -це тільки рамка, а нам потрібно в цю рамку вставити список типів процесорів. Для цього слід натиснути правою кнопкою миші на створеному елементі керування. У контекстному меню, що з'явилося, вибрати команду Формат обьекта. На екрані з'явиться діалогове вікно Формат злемента управлення (рис. 8). За замовчуванням обрана вкладка Злемент управлення, якщо ні, то потрібно її вибрати.

Рис. 7. Встановлення елемента керування Поле со списком

Рис. 8. Діалогове вікно Формат злемента управлення Поля со спискгом

Далі слід натиснути кнопку полі введення Формировать список по диапазону. Діалогове вікно Формат злемента управлення згорнеться в однорядкове поле введення (рис. 9), в якому з'явиться текстовий курсор.

Рис. 9. Діалогове вікно Формат злемента управлення в згорнутому вигляді

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

■ Натиснути мишею на ярличку Процесори. Відкриється робочий лист Процесори. У поле введення діалогу Формат злемента управлення з'явиться рядок «Процесори!».

■ Виділити на робочому листі діапазон комірок АЗ:А7 з найменуваннями типів процесорів. Рядок у полі ведення діалогу набуде вигляду «Процесори! $А$3:$А$7». Є доцільним виділити трохи більший діапазон комірок, що дасть можливість додавати записи до прайс-листів. Тільки що ми зазначили діапазон комірок, що будуть переглядатися через наше Поле со списком на основному робочому листі Кошторис. Це посилання на комірки можна було задати і по-іншому: ввести з клавіатури в поле введення Формировать список по диапазону зазначений рядок, що містить ім'я робочого листа «Процесори!» і діапазон комірок на цьому листі «$А$3:$А$7».

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

■ Натиснути кнопку діалогу Формат злемента управлення. Діалог знову розгорнеться в повному розмірі. При цьому він змінить свою назву на Форматирование обьекта.

■ Натиснути кнопку | в полі введення Помещать результат в ячейку. Діалог Форматирование обьекта знову згорнеться в однорядкове поле введення, в якому з'явиться текстовий курсор. При цьому в робочій книзі відчиниться лист Кошторис, елемент керування якого форматується.

■ Натиснути мишею на комірку Е5. Межа комірки виділиться пунктиром, а в полі введення діалогу Форматирование обьекта з'явиться рядок «$Е$5».

Через Поле со списком ми не тільки переглядаємо список, але й вибираємо елемент цього списку. Тільки що ми зазначили, в якій комірці на робочому листі Кошторис буде міститися номер обраного елемента. У діалоговому вікні Форматирование обьекта ми не змінювали значення поля Количество строк списка. Цей параметр визначає кількість рядків у списку, що відкривається з Поля со списком (рис. 10). Однак, розмір списку, що відкривається, не може бути менше розміру списку-джерела. В нашому випадку джерело - прайс-лист процесорів, що містить 5 рядків, тому розкривається список, який також має розмір у 5 рядків. Заповнювати поле Количество строк списка потрібно в тому випадку, коли список-джерело досить великого розміру. Тепер вже можна вибрати потрібний процесор, але в остаточному підсумку потрібно помістити ціну цього процесора в комірку на основному робочому листі для подальшого опрацювання.

Виведення ціни комплектуючого в сусідній зі списком комірці

Розглянемо на прикладі виведення ціни обраного за списку процесора в комірку В5.

■ Виділити мишею комірку В5 і натиснути кнопку fx на стандартній панелі інструментів. З'явиться діалогове вікно Мастер функций.

■ Вибрати рядок Ссылки и массивы в списку Категория. В списку Функция вибрати рядок ИНДЕКС.

■ Натиснути ОК. На екрані з'явиться наступний діалог Майстра функций. За замовчуванням в ньому виділений рядок массив; номерстроки; номерстолбца, який нам потрібний.

■ Натиснути ОК. На екрані з'явиться діалог для задання параметрів функції ИНДЕКС (рис. 11).

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

Рис. 10. Перегляд списку процесорів

Натиснути кнопку в полі введення Массив. Діалог задання параметрів функції згорнеться в однорядкове поле введення (рис. 12). Дане поле введення досить непримітне, у ньому навіть немає заголовка вікна. Знайти його на екрані можна за двома ознаками: по-перше, в ньому буде знаходитися текстовий курсор; по-друге, це поле буде розташоване на місці верхньої частини попереднього діалогу - саме під рядком формул вікна Excel.

Рис. 11. Діалогове вікно для завдання параметрів функції ИНДЕКС

Рис. 12. Поле введення параметрів функцій

■ Натиснути мишею на ярличку Процесори. Відкриється робочий лист Процесори. У полі введення параметрів функції з'явиться рядок «Процесори!».

■ Виділити на робочому листі діапазон комірок ВЗ:В7 з цінами процесорів. Рядок в полі введення параметрів функції матиме вигляд «Процесори! ВЗ:В7».'

Діапазон комірок можна також ввести в поле введення Массив безпосередньо з клавіатури. Тепер слід зазначити комірку, в якій буде знаходитися номер потрібного рядка з діапазону комірок.

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

■ Натиснути кнопку в полі введення Номер строки. Діалог згорнеться в однорядкове поле введення з текстовим курсором (рис. 12). При цьому відчиниться робочий лист Кошторис.

■ Натиснути мишею на комірці Е5. Межа комірки виділиться пунктиром, а в полі введення параметрів з'явиться рядок «Е5».

■ Натиснути кнопку ОК. Діалог введення параметрів зникне з екрану. В комірці В5 з'явиться ціна обраного процесора «180».

Тепер слід перевірити роботу вибудованої конструкції. Необхідно відкрити список процесорів і вибрати рядок «Р200 ММХ». В рядку Поля со списком з'явиться обране найменування, в комірці Е5 - значення номера рядка «5». В комірці В5 з'явиться відповідна ціна - «250».

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

1. Виділити комірку Е5. Відкрити список кольорів шрифту на панелі інструментів Форматирование. Вибрати білий колір. Текст в комірці Е5 стане білим, а тому невидимим на білому фоні комірки. Слід звернути увагу, що в рядку формул як і раніше залишається значення «5». Тобто, вміст комірки - невидимий, але значення в комірці не зникло, його можна використовувати у формулах робочого листа. Тепер слід зазначити на робочому листі кошторису той факт, що ціни зазначені в доларах.

■ Натиснути правою кнопкою миші на комірці В5. На екрані з'явиться контекстне меню.

■ Вибрати команду Формат ячеек. На екрані з'явиться діалог Формат ячеек. Вибрати вкладку Число.

■ У списку Числовые форматы вибрати Денежный, справа в полі Обозначение вибрати $Английский(США), при потребі зменшити кількість знаків після коми.

■ Натиснути ОК. Діалог Формат ячеек зникне з екрана. У комірці В5 вартість обраного процесора буде показана в наступному вигляді: $250.

Вибір конфігурації пам'яті

Для того щоб виводилися назва і вартість обраної конфігурації пам'яті потрібно виконати наступні дії.

По-перше, слід заповнити даними прайс-лист Пам'ять. Для цього:

■ Натиснути мишею на ярличку робочого листа Пам'ять. На екрані відчиниться порожній робочий лист.

■ Ввести значення об'ємів оперативної пам'яті та їхніх цін, як показано на рис. 13. Зліва, в стовпці А, - об'єми пам'яті, справа, в стовпці В -ціни в доларах.

Рис. 13. Список конфігурацій пам'яті та їх цін

По-друге, встановити елемент керування Счетчик на основному робочому листі. Для цього:

■ Натиснути мишею на ярличку Кошторис. Відчиниться основний робочий лист розрахунку.

■ Натиснути кнопку на панелі інструментів Форми. Встановити Счетчик в комірці D7.

Встановлення елементу керування Счетчик виконується аналогічно встановленню елемента Поле со списком. Розмір поля елемента керування варто встановити приблизно на чверть ширини комірки.

По-третє, задати параметри лічильника.

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

■ Ввести значення полів, як показано на рис. 14.

Начальное значение вводиться з клавіатури. Минимальное значение, Максимальное значение і Шаг изменения можна також вводити з клавіатури, а можна змінювати за допомогою стрілок

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

Рис. 14. Діалогове вікно Формат злемента управления -параметри Счетчика

Зміст параметрів зрозумілий з їх назви. Вони задають інтервал значень лічильника, його початкове значення і крок зміни при кожному натисканні на стрілку. Інтервал значень лічильника являє собою кількість рядків списку конфігурацій пам'яті. Результат - обране значення лічильника - заноситься в зазначену комірку для подальшого використання. В якості такої комірки взяти комірку Е7 на основному робочому листі.

■ Натиснути ОК. Діалог Форматирование обьекта зникне. В комірці Е7 з'явиться початкове значення лічильника - «1».

■ Натиснути верхню стрілку лічильника. Значення лічильника збільшиться і в комірці Е7 з'явиться нове значення - «2».

■ Натиснути нижню стрілку лічильника. Значення лічильника зменшиться, в комірці Е7 з'явиться значення - «1».

По-четверте, потрібно помістити ціну і назву обраної конфігурації пам'яті в комірках В7 і С7 відповідно. В даному випадку порядок дій такий:

■ виділити потрібну комірку;

■ викликати Мастер функций;

■ вибрати функцію ИНДЕКС;

■ зазначити робочий лист Память і діапазон комірок відповідного списку (колонку цін або колонку назв);

■ зазначити комірку, в якій знаходиться номер обраного в списку рядку;

■ натиснути ОК. У виділеній комірці з'явиться обране в списку значення.

При зв'язуванні комірки В7 з ціною пам'яті задати параметри, як показано на рис. 15. При зв'язуванні комірки С7 з найменуванням конфігурації пам'яті усе аналогічно, тільки діапазон комірок АЗ:А7. Залишилося додати знак долара до ціни і зробити невидимим значення лічильника.

Рис. 15. Параметри функції ИНДЕКС для ціни вибраної конфігурації пам'яті

Отже, ми навчилися працювати зі списком даних за допомогою двох елементів керування: Поле со списком (список, що розкривається) і Счетчик. Який елемент застосувати, залежить від конкретного випадку та власного смаку.

Вибір вінчестера і монітора

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

Спочатку слід заповнити даними відповідні листи Вінчестери та Монітори, як показано на рис. 16 та 19.

Рис. 16. Список типів вінчестерів та їх цін

Рис. 17. Параметри Счетчика для списку вінчестерів

Параметри лічильника для вінчестерів задати, як на рис. 17. Знову використати функцію ИНДЕКС для виведення назв та цін вінчестерів, при цьому задамо параметри функції, як показано на рис. 18. Нарешті, оформити ціну в доларах та приховати вміст комірки Е9. Перевірити правильність роботи створених елементів керування.

Рис.18. Параметри функції ИНДЕКС для ціни вибраного вінчестера

Рис. 19. Список типів моніторів та їх цін

Розрахунок вартості гарантії

Excel дає і такі корисні і зручні у використанні елементи керування.

Переключатель застосовується в ситуації, коли потрібно зробити вибір одного з декількох взаємовиключних варіантів, причому число цих варіантів невелике. Наш кошторис буде передбачати два види гарантії на розсуд замовника: безкоштовна - терміном на півроку, або вартістю 10% від ціни комп'ютера - терміном на рік.

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

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

■ Перемістити вказівник миші до верхнього лівого кута комірки С15. Натиснути кнопку миші і, не відпускаючи кнопку, провести покажчик-хрестик до лівого верхнього кута комірки D15. На робочому листі з'явиться поле Переключателя у режимі редагування із стандартним заголовком «Перекл... ».

■ Натиснути мишею усередині рамки редагування, видалити стандартний заголовок поля і ввести новий заголовок: 6 мес. Робочий лист прийме вигляд, як на рис. 20.

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

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

■ Ввести значення полів, як показано на рис. 21.

■ Натиснути ОК. Перемикач виділиться, кружок стане темним, а в комірці Е15 з'явиться значення 1 - номер активного перемикача на робочому листі.

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

■ Натиснути правою кнопкою миші на Перемикачі «б мес». З'явиться контекстне меню. В полі елемента керування включиться режим редагування.

■ Натиснути клавішу [Еsс|, Контекстне меню зникне.

■ Встановити покажчик миші на ліву межу елемента керування так, щоб покажчик прийняв форму двонаправленої стрілки. Перемістити межу вліво, зменшивши розмір Перемикача приблизно до половини розміру комірки СІ5.

■ Тепер встановити другий перемикач в комірці С15 зліва від першого. Це робиться аналогічно описаному вище. Видалити стандартний заголовок перемикача, ввести новий: 1 рік. Змінити, якщо потрібно, розміри поля перемикача.

Рис. 20. Встановлення елемента керування Переключатель

Рис. 21. Параметри поля Переключатель "6 мес."

Викликати діалог Формат злемента управлення в даному випадку не потрібно - параметри другого перемикача настроюються автоматично.

Перевірити функціонування перемикачів:

■ Натиснути мишею на перемикачі «1 рік». Виділення переміститься з першого на другий перемикач. У комірці Е15 з'явиться значення «2» - номер активного (другого) перемикача.

■ Натиснути мишею на перемикачі «6 мес». Виділення переміститься на перший перемикач. У комірці Е15 відновиться значення «1». Тепер потрібно виконати наступні дії.

■ по-перше, ввести в комірку В15 формулу розрахунку вартості гарантії;

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

Оскільки вартість гарантії залежить від вартості комп'ютера, слід визначити спочатку вартість комп'ютера як суму цін комплектуючих елементів. Для цього слід: ■ Виділити комірку В1З. Натиснути кнопку автосуми на стандартній панелі інструментів. В комірці В1З і в рядку формул з'явиться формула, що містить функцію автосуми.

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

■ Натиснути кнопку в рядку формул. Введення формули завершиться, а в комірці В1З з'явиться значення сумарної вартості комплектуючого комп'ютера.

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

■ Слід виділити комірку В15.

■ Ввести формулу розрахунку: = В13*0,1*(Е15-1).

■ Натиснути кнопку в рядку формул. В комірці В15 з'явиться значення «0».

■ Активізувати перемикач «1 рік». В комірці В15 з'явиться значення, рівне 10% від вартості комп'ютера.

Розтлумачимо введену формулу. «В13*0,1» - це зрозуміло, 10% від суми комплектуючих. Другий співмножник «(Е15-1)» дасть нам 0, якщо в Е15 - значення 1 (встановлений перемикач «6 мес.»). Якщо ж встановлений перемикач «1 рік», то в Е15 буде значення 2, «Е15-1» буде дорівнює 1, і загальне значення формули буде дорівнює «В 13*0,1», що і було потрібно.

Залишилося додати знак долара до суми в комірці В15 і зробити невидимим вміст комірки Е15 (аналогічно попереднім випадкам). Отриманий робочий лист зображений на рис. 22.

Рис. 22. Розрахунок вартості гарантії