Microsoft Excel 2007. Крок за кроком

Про матеріал

Посібник призначений для вивчення табличного процесора Excel українізованої версії 2007 . Може бути використаний для використання на уроках з таких предметів: Технології комп’ютерної обробки інформації, Інформатика а також для самостійного вивчення. В посібнику наведено теоретичний матеріал згідно з програмою предмету «Технології комп’ютерної інформації» та «Інформатики». Після кожного теоретичного матеріалу йде одна або 2 практичні роботи. Для перевірки знань в кінці кожної теми наводиться ряд питань, на які потрібно дати відповідь. Щоб перевірити комплексні знання всіх тем, в кінці посібника наведено 5 комплексних завдань. Посібник рекомендується для учнів, викладачів профтехучилищ, і всім хто хоче оволодіти програмою Microsoft Office 2007.

Перегляд файлу

Грушецька І.О. Навчально-методичний посібник

Міністерство освіти і науки, молоді та спорту України

Управління освіти і науки Хмельницької облдержадміністрації

Вище професійне училище № 25 м.Хмельницького

І.О.Грушецька

мs Excel 2007

КРОК за кроком

НАВЧАЛЬНО-МЕТОДИЧНИЙ ПОСІБНИК

(професія: Оператор комп’ютерного набору)

м. Хмельницький

2018

ББК

УДК

Розглянуто і рекомендовано до використання та друку

педагогічною радою ВПУ № 25 м. Хмельницького

(протокол № 2 від 10 жовтня 2018 року)

Рецензенти:

Коржан Т.В.. – майстер виробничого навчання ВПУ № 25

Чуйкова О.М. – методист НМЦ ПТО ПК у Хмельницькій області

Грушецька І.О. МS EXCEL 2007. крок за кроком

Навчально-методичний посібник /І.О.Грушецька – Хмельницький: ІВВ ВПУ № 25, 2018 р. – 100 с.

В новому посібнику наведено теоретичний матеріал згідно з програмою предмету «Технології комп’ютерної інформації» та «Інформатики».

Після кожного теоретичного матеріалу йде одна або 2 практичні роботи.

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

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

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

Посібник рекомендується для учнів, викладачів профтехучилищ, і всім хто хоче оволодіти програмою Microsoft Office 2007.

ПЕРЕДМОВА

Сучасне суспільство характеризується глобальним процесом інформатизації, стрімким переходом на новий етап розвитку – інформаційне суспільство. У зв’язку з цим, серед основних стратегічних цілей розвитку інформаційного суспільства в Україні визначено забезпечення комп’ютерної та інформаційної грамотності населення, насамперед шляхом створення системи освіти, орієнтованої на використання новітніх інформаційно-комунікаційних технологій (ІКТ) у формуванні всебічно розвиненої особистості.

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

Однією із складових комп’ютерної грамотності є знання роботи в прикладних програмах, таких як MS Word, MS Power Point, MS Access, Excel.

Мої попередні посібники (електронні книги) описують роботу в текстовому процесорі MS Word 2003, Текстовому процесорі MS Word 2007, презентаціях MS Power Point 2007.

Мій новий навчально-методичний посібник «Excel 2007. Крок за кроком» продовжує вивчення пакету програм компанії Microsoft з метою використання даної програми в роботі, навчанні або для вирішення інших задач.

Excel – найпопулярніша програма електронних таблиць, що призначена для експрес-моделювання облікових, економічних, статистичних, наукових та інших табличних розрахунків. Разом з тим це грандіозна програма, що є універсальною системою обробки даних.

Microsoft Excel 2007 обчислює будь-які табличні розрахунки: суми по стовпчиках і рядках, вираховує відсотки, середньоарифметичні величини, банківські проценти, в ній можна використовувати велику кількість фінансових, математичних, логічних функцій. Можливості форматування тексту в Excel такі ж як у хорошому текстовому редакторі, що дозволяє вибирати будь-який шрифт, кольорову гамму, рамки, лінійки. Excel будує за даними таблиць графіки, діаграми; працює з графічними об’єктами.

З 1 теми Ви дізнаєтеся про історію виникнення програми MS Excel 2007, можливості програми, способи відкриття і виходу з програми.

2 тема присвячена інтерфейсу програми MS Excel 2007, різним способам виділення даних, методам переміщення по таблицям.

З теми 3 Ви дізнаєтеся про роботу з вікнами і з аркушами: створення, переміщення, копіювання, перейменування аркушів.

З 4 теми Ви дізнаєтеся як створювати і зберігати файли в програмі.

5 розділ описує способи встановлення параметрів сторінки, перегляд і друк документів.

В 6 розділі йдеться про введення різних типів даних: текстових, числових, дробових даних, приміток, дати і часу, формул, картинок.

7 тема описує редагування даних: перевірку орфографії, засіб «знайти і замінити», операції над вмістом комірок, автоматизоване заповнення комірок.

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

Тема 9 розкриває питання створення формул і виконання простих математичних підрахунків: множення, ділення, віднімання, додавання, піднесення до степеню і т.д.

В темі 10 мова йде про вбудовані в програму функції, призначені як для виконання не складних, так і для виконання доволі складних підрахунків. Тут можна познайомитися із засобом Майстра функцій, який значно спрощує введення і редагування вбудованих функцій.

Успіх в бізнесі потребує володіння багатьма важливими навичками. Одним із самих цінних якостей менеджера є вміння будувати моделі “що-якщо” і на їх основі складають прогнози на майбутнє. Саме питанню прогнозуванню присвячена тема 11, в якій описується 2 засоби прогнозування бізнесу: пошук рішенні і підбір параметру.

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

В 13 темі йде опис способів фільтрації і сортування даних. Описано роботу з авто фільтром і розширеним фільтром.

Опис 14 теми присвячений створенню і роботі зі зведеними таблицями, встановлення формату чисел.

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

В 16 темі йдеться про графічні об’єкти, які може створити MS Excel 2007. До них відносяться рисунки, фігури, надписи Word Art. Хоч це і не основна функція даної програми, але все ж таки і графічні об’єкти є одною із функцій програми.

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

В темі 19 Ви познайомитеся з поняттям макросу і ознайомитеся з етапами його запису і виконання. Також розглянете питання безпеки макросів.

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

В кінці посібника наведено 5 комплексних завдань, які охоплюють усі теми даного посібника.

Теоретичний матеріал має свою позначку –

Практичні роботи також мають свою позначку -

Одна тема від другої відділяється позначкою -

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

Для запуску тесту потрібно виконати наступне: відкрити папку Тести, запустити файл Test.exe, відкриється вікно оболонки тесту, виконайте команду Файл/Відкрити і виберіть назву тесту для його вирішення (назва тесту співпадає з темою в теоретичній частині посібника.

Бажаю успіхів в оволодінні дуже цікавої програми Microsoft Excel 2007!!!!!

ЗМІСТ

Загальні відомості про Microsoft Excel 2007. Запуск програми. ......................6
Інтерфейс вікна програми Excel. 2007 . ..........................................7

Практична робота № 1 Ознайомлення з вікном програми......................12

Практична робота № 2 Ознайомлення з вкладками робочого вікна...............12

Вікна книг, робота з аркушами книг. ..........................................13

Практична робота № 3-4. Робота з аркушами книг...........................15

Створення та збереження файлів книг...........................................16
Перегляд та друк книг........................................................17
Введення даних у таблиці.....................................................19

Практична робота № 5 Введення даних за типами............................21

Редагування даних в Excel 2007...............................................22

Практична робота № 6 Введення та редагування даних ........................25

Форматування комірок і документів. Умовне форматування........................26

Практична робота № 7 Форматування комірок та діапазонів комірок...........30

Практична робота № 8 Форматування комірок та діапазонів комірок...........32

Створення формул і обрахунків................................................33

Практична робота № 9 Створення формул. Введення формул..................35

Практична робота № 10. Створення формул. Введення формул.................36

Використання функцій.......................................................38

Практична робота № 11. Робота математичними функціями..................44

Практична робота № 12. Робота з математичними функціями

і статистичними.........................................................45

Практична робота № 13. Робота з функціями Дати і Часу.....................46

Практична робота № 14. Робота з логічними функціями.......................47

Практична робота № 15. Робота з фінансовими функціями.....................48

Засоби «Пошук рішення» та «Підбір параметра»..................................49

Практична робота № 16. Засоби «Пошук рішення» та «Підбір параметрів».......52

Практична робота № 17. Засоби «Пошук рішення» та «Підбір параметрів».......53

Робота зі списками в Excel 2007................................................55

Практична робота № 18-19. Робота зі списками..............................59

Способи фільтрації даних.....................................................60

Практична робота № 20. Фільтрація даних..................................62

Практична робота № 21. Робота з різними видами фільтрації..................63

Створення та робота зі зведеними таблицями.....................................64

Практична робота № 22. Зведені таблиці....................................68

Практична робота № 23. Зведені таблиці....................................69

Створення бази даних в Excel 2007.............................................70

Практична робота № 24-25. Створення бази даних в Excel 2007.................72

Графічні об’єкти в Excel 2007..................................................76
Побудова діаграм в Excel 2007................................................78
Редагування і форматування елементів діаграми..................................82

Практична робота № 26. Побудова діаграм в Excel2007........................85

Практична робота № 27. Побудова діаграм в Excel 2007.......................86

Практична робота № 28-29. Редагування і форматування діаграм у Excel 2007...87

Використання макросів.......................................................88

Практична робота № 30. Створення макросів................................90

Практична робота № 31. Створення макросів................................92

КОМПЛЕКСНІ ЗАВДАННЯ...............................................94

ПІСЛЯМОВА...........................................................99

СПИСОК РЕКОМЕНДОВАНОЇ ЛІТЕРАТУРИ..............................100

Тема 1. Загальні відомості про Microsoft Excel 2007. Запуск програми

План

З історії Excel
Можливості програми
Запуск програми
Вихід з програми

З історії Excel

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

Я не зупинятимуся на описі механічних рахункових пристроїв, передуючих комп'ютерам. Ця тема окремої книги, в якій проводиться достатньо серйозне історичне дослідження. Залишимо прерогативу написання такої книги історикам, наше завдання — прослідкувати витоки виникнення табличних процесорів, які і були попередниками наший гаряче коханій Excel.

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

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

Офіційна поява Excel у складі пакету MS Office — 1991 рік, саме ця дата знаменує початок епохи Excel.

2. Можливості програми Excel 2007

Електронні таблиці Excel 2007 — друга за значимістю програма, що входить в офісний пакет програм Microsoft Office 2007. В той же час це найпростіша і зрозуміліша програма з вдалим, інтуїтивно зрозумілим, інтерфейсом. Якщо ви хоч раз запускали програму Excel, то погодитеся з цим, оскільки навіть на перший погляд новачка в ній нічого складного не немає.

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

Що ж можна робити за допомогою електронних таблиць? Все, що потрібне для роботи і навчання. Їх навіть можна використовувати замість звичайного калькулятора. Проте програма Excel розроблялася для вирішення самих різних економічних і наукових завдань, тому її з із задоволенням застосовують скрізь, де потрібно виробити нескладні, але багато разів такі, що повторюються обчислення: розрахувати результати лабораторної роботи, бухгалтерський баланс, накреслити графіки за даними наукової роботи, та і просто вести домашній бюджет. Робота в програмі Excel інтуїтивно зрозуміла, оскільки в ній автоматизовані математичні операції, які всі вивчають в школі. До того ж Excel на відміну від інших продуктів корпорації Microsoft - майже компактна і не заплутана система.

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

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

Для роботи програми потрібне наступне апаратне забезпечення:

операційна система Windows ХР або Windows NT;
персональний комп’ютер із процесором 80486 DX;
оперативна пам’ять обсягом мінімум 16 Мб при роботі Windows NT;
пристрій для читання компакт-дисків;
не менше 31 Мб вільної пам’яті на жорсткому диску;
маніпулятор „миша”;
256-кольоровий SVGA - відеоадаптер.

3. Запуск програми

Excel запускається так само, як і більшість програм Office, а саме:

Пуск/Програми/MS Excel 2007.
Через ярлик на робочому столі.
Через запускаючий файл з програмної папки.

4. Вихід з програми

Для виходу з Excel потрібно виконати команду Кнопка Офіс/Вихід або . Закриваючи програму з’явиться повідомлення «Зберегти зміни у файлі «назва файлу»?». Натиснути «Так» і документ буде зберігатися туди, куди вкаже користувач, але про це пізніше (рис.1).

Рис. 1. Вікно підтвердження збереження

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Описати можливості програми.
Як відкрити Excel 2007?
Як можна вийти з Excel?
Перед закриття вікна програми що потрібно зробити?

Тема 2. Інтерфейс вікна програми Excel 2007

План

Основні елементи вікна програми
Переміщення по коміркам
Різні способи виділення даних

Основні елементи вікна програми

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

Рядок заголовка (верхній рядок вікна) містить назву програми «Microsoft Excel» і назву документа. У правій частині рядка заголовка розміщені відповідно кнопки згортання, розгортання і закриття вікна (рис. 2).

Рис. 2. Рядок заголовку

Панель швидкого доступу – містить по замовчуванню 3 кнопки: зберегти, відмінити, повторити (рис.3).

Рис. 3. Панель швидкого доступу

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

Для додавання елементу, якого немає в списку, потрібно вибрати команду Інші команди. В розділі Настройка вікна Параметри Excel в списку, що розкривається Вибрати команди з, вибрати вкладку, в якій розташований елемент, що додається, потім виділити елемент в списку і натиснути кнопку Додати (рис. 4).

Рис.4. Вікно настройки панелі швидкого доступу

Після настройки панель може виглядати так (рис. 5):

Рис. 5. Панель швидкого доступу після налаштувань

Кнопка Office, яка відкриває список можливих дій з документами: відкриття, збереження, попередній перегляд, друк документа. Також тут міститься список останніх документів, а також команда для настройки параметрів додатки,наприклад, Параметри Excel (рис. 6).

Рис.6. Вміст кнопки Office

Стрічка з вкладками. Вона складається з різних вкладок. Кожну вкладку присвячено особливим різновидам роботи, яку користувач може виконувати в Excel. Клацнувши корінець будь-якої вкладки над стрічкою, ви бачите набір команд, зібраних на цій вкладці. Вкладка Основне, перша з лівого боку, містить найбільш уживані команди, якими користуються найчастіше. Також містить в собі інші вкладки: Вставлення, Розмітка сторінки, Формули, Дані,

Рецензування, Вигляд, Розробник (рис. 7).

Рис.7. Стрічка (1- Стрічка охоплює верхню частину вікна Excel.

2 - Споріднені команди на стрічці об'єднано у групи)

Якщо вкладки Розробник немає на екрані, її можна відобразити командою: кнопка Офіс/Параметри Excel, відкриється вікно, в якому вибрати вкладку Найуживаніші і встановити прапорець «Відображати вкладку Розробник на екрані».

Кожній кнопці відображається спливаюча підказка. Якщо її немає, потрібно виконати команду Офіс/Параметри Excel, відкриється вікно, в якому вибрати вкладку Найуживаніші і в

списку Стиль спливаючих підказок вибрати потрібний стиль (не відображати підказки, відображати підказки, відображати повні підказки).

Команди зібрано в невеличкі групи за певною спільною ознакою. Наприклад, команди для редагування комірок зібрано у групі Редагування, а команди для роботи з комірками містяться у групі Клітинки. За допомогою кнопки f_x можна активізувати вікно з вбудованими функціями.

Рядок формул, розміщений під панелями інструментів, призначений для редагування вмісту елементів таблиці. Він розділений на три частини. Права частина служить для відображення вмісту активної клітини таблиці, яку можна редагувати за допомогою кнопок, розміщених зліва від відображеного вмісту клітини. У лівій частині рядка вказується адреса активної клітини або адреса виділеного діапазону клітин. Рядок формул можна сховати, виконавши команду Вигляд/Приховати/Рядок формул (рис. 8).

Рис. 8. Рядок формул

Робоча область електронної таблиці складається з рядків і стовпців. Межі між ними утворюють сітку, яку ви бачите, відкривши книгу (рис. 9).

Рис.9. Робоча область

Стовпці простягаються згори до низу аркуша по вертикалі. Рядки тягнуться горизонтально зліва направо. Клітинка — це місце перетину одного стовпця й одного рядка.

Угорі кожного стовпця міститься позначений буквами заголовок. Перші 26 стовпців позначено букви від A до Z. Кожен аркуш містить 16 384 стовпця, тому після Z букви в позначеннях повторюються попарно, AA – AZ, і так далі.

Після AZ пари букв знову повторюються у стовпцях BA – BZ і так далі, поки не буде використано всі 16 384 стовпця. Останній стовпець має заголовок XFD.

Кожний рядок також має заголовок. Рядки позначаються числами від 1 до 1 048 576.

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

У електронній таблиці існує поняття діапазону клітин. Діапазон клітин – це група послідовних елементів таблиці. Діапазон клітин може складатися з однієї клітини, рядка (або його частини ), стовпця ( або його частини ), а також послідовності рядків або стовпців (або їх частин).

Діапазон клітин визначається шляхом завдання адреси першої і останньої його клітини, між якими ставиться знак двокрапки «:». Прямокутний діапазон описується за допомогою клітин, які знаходяться в лівому верхньому і правому нижньому кутах діапазону.

Наприклад, діапазон А1:G1 задає групу послідовних клітин першого рядка, починаючи з клітини А1 і закінчуючи коміркою G1; діапазон А1:С5 задає групу послідовних клітин, які знаходяться на перетині перших п’яти рядків і перших трьох стовпців А, В, С.

Режими перегляду робочої області

Звичайний — цей режим вибраний за замовчуванням і відповідає стандартному режиму перегляду Excel.
Макет сторінки — цей режим перегляду дозволить вам отримати уявлення про те, яким чином виглядатиме таблиця Excel при виведенні на друк.
Розмітка сторінки — при виборі цього режиму перегляду можна отримати уявлення щодо таблиці Excel в цілому (виводиться структура таблиці Excel).

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

Ярлики аркушів – аркуші, на яких створюються таблиці (рис. 10).

Рис. 10. Ярлики аркушів

Запускаючи Excel, ви відкриваєте файл, який називається книгою. Кожна нова книга містить три аркуші, подібні до сторінок у документі. На цих аркушах ви вводите дані. (Аркуші також інколи називаються електронними таблицями.)

Кожний аркуш має ім'я, написане на корінці аркуша внизу, з лівого краю вікна книги: Аркуш1, Аркуш2 і Аркуш3. Щоб побачити кожний аркуш, потрібно клацнути його корінець.

Корінці аркушів доцільно перейменувати, щоб інформацію на кожному аркуші було легше розпізнати. Наприклад, можна назвати аркуші «Січень», «Лютий» і «Березень», якщо вони містять бюджети або дані успішності учнів за ці місяці, або «Північ» і «Захід», якщо йдеться про обсяги продажу в регіонах, і так далі.

Рядок стану розташований в нижній частині екрану і містить поле повідомлень, кнопки перегляду аркуша, елемент зміни масштабу (рис. 11.)

Рис. 11. Рядок стану

Для зміни масштабу потрібно повзунок масштабу перемістити вліво або вправо.

Додаткові елементи вікна

Міні-панель інструментів

Міні-панель інструментів містить основні найбільш часто використовувані елементи для оформлення даних (рис. 12).

На відміну від інших додатків Office 2007 (Word, PowerPoint і ін.) в Excel 2007 міні-панель не відображається автоматично при виділенні фрагмента листа. Для відображення панелі клацніть правою кнопкою по виділеній області.

Рис. 12. Міні-панель інструментів

2. Переміщення по комірках

Комірка, окреслена чорним прямокутником (курсором) називається поточною (активною). На початку роботи поточною коміркою завжди є комірка А1.

Щоб зробити активною іншу комірку, можна натиснути клавіші керування курсором або клацнути на потрібній комірці кнопкою миші. Ім'я виділеної комірки відображається в полі імені.

Якщо вказівник миші знаходиться над аркушем, він заміняється хрестиком – показником комірки.

Для переміщення по таблиці потрібно користуватися такими клавішами:

<Enter> – на 1 комірку вниз.

<Tab> – на 1 комірку в право.

<Shift+Enter> – на 1комірку вверх.

<Shift+Tab> – на 1 комірку вліво.

<Home> – до стовпця А поточного рядка.

<End+<-> Перехід в перший стовпець аркуша.

<End+T> Перехід в перший рядок аркуша.

<End+->> Перехід в останній стовпець аркуша.

<End+i> Перехід в останній рядок аркуша .

3. Різні способи виділення даних

Для виділення різних структур даних, що знаходяться на робочому аркуші Excel, застосовується ряд наступних методів.

Для виділення рядка в електронній таблиці потрібно клацнути лівою кнопкою миші на номері рядка.
Для виділення стовпця в електронній таблиці потрібно клацнути лівою кнопкою миші на імені стовпця.
Якщо ж потрібно виділити несуміжні блоки комірок, це завдання також цілком здійсниме. Спочатку потрібно виділити перший блок, потім, утримуючи клавішу <Ctrl>, виділити першу комірку другого блоку (після цього перетяганням виділити повністю другий блок) і так далі.
Для виділення декількох робочих аркушів слід просто виділити декілька ярликів, відповідних цим аркушам.
Якщо ж потрібно виділити всі робочі аркуші, потрібно клацнути лівою кнопкою миші на першому з них, потім клацнути правою кнопкою миші і в контекстному меню, що відобразилося, вибрати пункт Виділити всі аркуші.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Що містить в робочій області вікна?
Що містить в рядку формул?
Що містить в собі панель швидкого доступу?
Як переміщуватися по коміркам?
Як настроїти панель швидкого доступу?
Що містить в рядку стану вікна програми?
Що містить в собі кнопка Office?
Як змінити масштаб аркуша?
Описати рядок стану.

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

Тема. Ознайомлення з вікном програми.

Мета. ознайомити учнів з програмою Microsoft Excel 2007, її інтерфейсом та основними параметрами; виробити в учнів професійні навички роботи на ПК, розвивати логічне мислення та пам'ять; виховувати любов до праці і повагу до обраної професії.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

Завдання

1. Запустити програму Microsoft Excel наступними способами.

Через ярлик на робочому столі.
Через Пуск – Всі програми.
Через існуючий документ Microsoft Excel.

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

3. Відобразити вкладку «Розробник» на екрані.

4. Задати відображення рядків формул, сітки, заголовків

5. Скасувати режим відображення формул.

6. Відкрити вікно швидкого доступу і налаштуйте на свій смак.

7. Розглянути міні-панель інструментів.

8. Відкрити кнопку Office і перегляньте усі її елементи.

9. Переміститися по коміркам: вліво, вправо, вверх, вниз, в кінець стовпців, в кінець рядків.

10. Здійснити виділення стовпця А, комірки А10, рядка 1, діапазону А1:К5.

11. Виділити один робочий аркуш, виділити перший і другий аркуші, всі аркуші.

12. Закрити програму.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично і захистити свою роботу на оцінку.

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

Як відбувається виділення комірок?
Як приховати заголовки з екрану?
Що містить в собі панель швидкого доступу?
Що містить в рядку стану вікна програми?
Як змінити масштаб аркуша?
Скільки аркуші є в книзі?
Що містить в собі кнопка Office?
Як переміщуватися по коміркам?

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

Тема. Ознайомлення з вкладками робочого вікна.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. Відкрити вікно програми Excel 2007.
2. Відкрити почергово кожну вкладку і переглянути їх вміст.
3. Вивести вкладку «Розробник» на екран.

Приховати спливаючі підказки до кнопок вкладок.
Відобразити повні спливаючі підказки.
В конспекті створити таку таблицю.

Назва вкладки	Категорії кнопок, які в ній є
Основне	Буфер обміну, шрифт, …..
……	…..

Закрити програму.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично і захистити свою роботу на оцінку.

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

Які вкладки знаходяться у вікні програми?
Як вивести вкладку «Розробник» на екран?
Які стилі спливаючих підказок є?
Як змінити стилі спливаючих підказок?
Які категорії кнопок знаходяться на вкладці Основне?

Тема 3. Вікна книг, робота з аркушами книг

План

Робота з аркушами
Робота з вікнами

1. Робота з аркушами

Документи, що створюються в середовищі Excel називаються книгами.

Кожна книга складається з аркушів таких типів:

робочі аркуші;
аркуші діаграм;
аркуші макросів;
аркуші діалогу.

Робочі аркуші – це електронні таблиці. Що складаються з стовпчиків і рядків.

По замовчуванню робочі аркуші називаються “Аркуш...”, а аркуші-діаграм – “Діаграма...”.

Один з аркушів книги є активним (його ім'я виводиться інвертним кольором).

Перейменування аркуша

Можна змінити ім'я активного аркуша, виконавши команду Перейменувати з контекстного меню даного аркуша і ввести нове ім’я.

Додавання і видалення

Excel автоматично поміщає в кожну робочу книгу 3 робочих аркуша.

Цю кількість можна змінити, виконавши наступне:

Натиснути кнопку Microsoft Office і вибрати кнопку Параметри Excel.
Вибрати у вікні категорію Найуживаніші та в розділі Під час створення нових книг ввести у полі Включити цю кількість аркушів кількість аркушів, які за промовчанням має містити новостворена книга.

Щоб додати новий робочий аркуш, необхідно виділити будь-який ярлик аркуша і викликати контекстне меню (рис.13), в ньому вибрати команду Додати і у вікні діалогу вибрати “Аркуш”. Новий аркуш буде додано зліва від виділеного.

Щоб видалити аркуш потрібно виділити його ярлик і з контекстного меню вибрати команду Видалити і натиснути ОК для підтвердження видалення.

Перенесення і копіювання аркушів

Для переміщення робочого аркуша потрібно виділити його ярлик і притримувати натиснутою ліву кнопку миші поки її курсор не набуде вигляду стрілочки з аркушиком паперу. Перемістити цей курсор вздовж ярликів аркушів в потрібне місце і після появи чорної стрілки відпустити курсор.

Для копіювання треба виконати попередні дії з натиснутою кнопкою Ctrl.

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

Зміна кольору ярлика

Виконати команду Колір вкладки з контекстного меню аркуша і вибрати колір.

3. Робота з вікнами

Виведення нового вікна --- Команда Вигляд/Вікно/Нове (рис.14).

Упорядкування вікон --- Команда Вигляд/Вікно/Упорядкувати всі.

Розподіл вікна

На екран виводиться лише частина комірок активного вікна. Розподіл вікна на 2 або 4 частини дозволяє оглядати різні частини одного і того ж аркуша.

Можна розділити за допомогою розподільників. Щоб розділити вікно потрібно виконати команду Вигляд/Вікно/Розділити, з’являться розподільники, переміщуючи мишкою встановити їх місцезнаходження.

Щоб відмінити розподіл – виконати ту саму команду, або двічі клацнути на розподільниках.

Закріплення заголовків

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

Для закріплення стовпчика чи рядка слід перемістити табличний курсор під рядок і праворуч від колонки, яку потрібно закріпити і виконати команду Вигляд/Вікно/Закріпити панелі. Потім вибрати одне з 3:

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

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

Для зняття виділення закріплення - Вигляд/Вікно/Закріпити панелі/Звільнити області.

Захист книг і аркушів

Для захисту слід виконати команду Захист аркуша з контекстного меню аркуша.

Відкриється вікно (рис. 15), в якому встановити прапорець Захистити аркуш і вміст заблокованих клітинок, ввести пароль для зняття захисту аркуша і встановити дозволи для користувачів цього аркуша (що можна робити а що ні).

Для зняття захисту слід подати команду Зняти захист аркуша з контекстного меню аркуша, у вікні ввести пароль і ОК.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Які типи аркушів ви знаєте?
Що являє собою електронна таблиця Excel?
Як перейменувати робочий аркуш?
Скільки символів може мати назва аркуша?
Як збільшити або зменшити кількість робочих аркушів?
Яким чином встановлюється розподіл вікна?
Для чого встановлюється закріплення заголовків рядків і колонок?

Практична робота № 3-4

Тема. Робота з аркушами книг.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

Завдання

Перейменувати всі аркуші місяцями року.(Липень – Грудень).
Змінити кольори всім ярликам аркушів.
Зробити активним аркуш Грудень, а Липень видалити.
Скопіювати аркуш Серпень в кінець книги. Перенести аркуш Грудень на початок книги.
Закріпити область А1:А10 і переглянути, що відбулося. Відмінити закріплення.
Закріпити область А1:І10. Відмінити закріплення.
Закріпити перший стовпчик. Закріпити перший рядок. Відмінити закріплення.
Розділити вікно на 4 частини, на 2 частини. Відмінити розподіл.
Вивести нове вікно.
Впорядкувати вікна опцією «зліва направо».
Закрити одну з 2 книг.
Приховати сітку і заголовки (вкладка Вигляд). Вивести їх на екран.
Змінити вигляд перегляду на Макет сторінки, потім на Розмітку сторінки. А потім повернути на вигляд Звичайний.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично і захистити свою роботу на оцінку.

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

Як додати аркуш?
Як видалити аркуш?
Як змінити назву аркуша?
Як розділити
Яким чином закріпити область?
Як впорядкувати усі відкриті вікна?
Як приховати заголовки з екрану?

Тема 4. Створення та збереження файлів книг

План

Створення документів
Збереження аркушів книг

1. Створення документів

Для створення порожньої книги потрібно:

1. Виконати команду Кнопка Офіс/Створити.

2. У вікні, яке відкриється (рис. 16), вибрати Чисті і недавні.

3. Вибрати піктограму Нова книга і кнопка Створити.

4. Створиться чистий документ.

Для створення книги по шаблону потрібно:

1. Виконати команду Кнопка Офіс/Створити.

2. У вікні, яке відкриється (рис. 16), вибрати Інстальовані шаблони.

Серед них такі як, авансовий звіт, особистий бюджет на місяць, таблиця кров’яного тиску і т. інше.

3. Вибрати потрібний шаблон і кнопка Створити.

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

2. Збереження аркушів книг

Виконавши команду Офіс/Зберегти як (F12) відкриється підменю з такими командами для збереження (рис.17):

Книга Excel - збереження робочої книги у форматі за про мовчанням.
Книга Excel з підтримкою макросів – збереження робочої книги з підтримкою макросів.
Двійкова книга Excel – збереження робочої книги у двійковому форматі, настроєному для швидкого завантаження та збереження
Книга Excel 97/2003 – збереження у форматі, сумісному з раднішими версіями програми.
Знайти надбудови для інших форматів файлів.
Інші формати – відкриття діалогового вікна «Зберегти як» для вибору потрібного типу файлу.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Яким чином створити шаблон документу?
Як створити документ?
Які є шаблони документів?
Які команди дозволяють зберігати файл документу?
Яким чином відбувається збереження документу?

Тема 5. Перегляд та друк книг

План

1. Параметри сторінки

2. Попередній перегляд документа

3. Друк документа

1. Параметри сторінки

Для встановлення параметрів сторінки потрібно звернутися до вкладки Розмітка сторінки, категорії Параметри сторінки:

Поля – звичайне, широке, вузьке (рис. 18).
Орієнтація – книжкова, альбомна.
Розмір – формат електронного аркуша.
Область друку – дозволяє переглянути область для друку документу.
Тло – заливає сторінку візерунком. Відкривається вікно, в якому можна знайти малюнок заливки електронного аркуша.
Друк заголовків – відкриває вікно для настройки друку шапки таблиці.

Для встановлення параметрів сторінки також можна викликати вікно кнопкою з даної категорії.

Відкриється вікно діалогу Параметри сторінки, яке має ряд вклади нок

Сторінка – встановлює розмір паперу, орієнтацію, масштаб сторінок, які друкуються, номер першої сторінки, якість друку - кількість пікселів на дюйм – для матричного (рис. 19).
Поля – розміри верхнього, лівого, правого, встановлює центрування від краю паперу до верхнього (нижнього) колонтитула (рис. 20).
Колонтитули – вибрати дозволяє верхній і нижній колонтитул зі списку (ім’я аркуша книги, ім'я автора документу, номер сторінки, поточна дата, ім'я робочої книги).

Якщо потрібно створити власний, виконайте команду Створити верхній колонтитул (нижній). Вікно, яке відкриється, має ряд кнопок:

номер сторінки;
загальну кількість сторінок;
поточна дата, час;
ім'я файлу книги;
ім'я аркуша.

Аркуш – задає область друку, друк у режимі чернетки, чорно-білий друк, друк сітки, порядок друку сторінок (вниз, потім вправо; вправо, потім вниз).

2. Попередній перегляд документа

Виконавши команду кнопка Офіс/Друк/Попередній перегляд, відкриється вікно попереднього перегляду з активною вкладкою Попередній перегляд (рис. 21).

У вікні зображується одна чи декілька сторінок аркуша, що має друкуватися.

Вкладка має ряд кнопок:

Друк – викликає вікно діалогу Печать.
Параметри сторінки - відкриває вікно з параметрами сторінки.
Масштаб – змінює масштаб зображення.
Наступна сторінка, попередня сторінка - прокручує зображені сторінки.
Показати поля – дозволяє встановити поля сторінки.
Закрити вікно – закриває вікно перегляду.

3. Друк документа

Для друку слід виконати команду кнопка Офіс/Друк/Друк. Відкривається вікно діалогу Друк (рис. 22), в якому є поля:

ім’я – тип принтера вибирається.
друкувати – вказує діапазон сторінок, які друкуються (усі, сторінки з – по).
надрукувати – дозволяє вибрати одну з опцій:
- виділений діапазон – друк діапазону комірок, який був виділений попередньо;
- виділені аркуші – друк активного аркуша або групи виділених аркушів.
- усю книгу – друкує всі аркуші книги.
кількість копій – число копій сторінок.
прапорець «Розібрати за копіями».

Після установки всіх параметрів - кнопка ОК.

Щоб роздрукувати не встановлюючи додаткових параметрів, команда кнопка Офіс/Друк/Швидкий друк.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Яким чином аркуш поділити на сторінки?
Як встановити параметри сторінок?
Для чого використовують попередній перегляд?
Опишіть вікно попереднього перегляду.
Яким чином можна надрукувати книгу або частину книг?

Тема 6. Введення даних у таблиці

План

Поняття введення
Введення даних по типам

1. Поняття введення

З першого символу, набраного в поточній комірці, в рядку формул з’являться два значки:

“червоний хрестик” – відміна;

“зелений прапорець” – введення.

В самій комірці з’являється курсор клавіатури. Якщо під час заповнення комірки набрали не той символ, натиснути клавішу Esc.

Те, що набирається в поточній комірці, з’являється зразу в двох місцях: в самій комірці і в рядку формул.

Після набору всіх символів натискають клавішу Enter або Tab;  або , або кнопку в рядку формул і переходять до наступної комірки.

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

2. Введення даних за типами

Excel 2007 дозволяє вводити в комірки:

числові значення;
текстові значення;
дата і час;
примітки;
формули;
рисунки, карти.

Числові значення

Може число бути цілим (32), десятковим дробом (49,95), звичайним дробом (10 3/4), з експонентою (4,09Е+13). В нього можуть бути включені символи: +,-,%, дробова риска, долар.

Довге число розширює комірку автоматично. Якщо з’являються символи #######, вручну розширити потрібно комірку. Відділяється крапкою.

Щоб ввести дріб, залиште пробіл між цілою та дробовою частинами; наприклад, 1 1/8.

Щоб ввести лише дробову частину, замість цілої частини введіть нуль; наприклад, 0 1/4. Якщо ввести 1/4 без нуля, Excel розпізнає це число як дату 1 квітня.

Якщо ввести число в дужках (100), його буде відображено як від'ємне число -100.

По замовчуванню числові значення вирівнюються по правому краю комірки.

Текстові значення

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

Якщо потрібно, щоб Excel 2007 зберіг дані як текст, достатньо почати введення значення зі знака апострофа.

Дата і час

Щоб ввести дату у стовпці B (Дата), потрібно використати для відокремлення частин дати крапку, скісну риску або знак мінус: 16.7.2009, 16/7/2009 або 16-Лип-2009. Тоді введені дані буде розпізнано як дату.

Щоб ввести час, потрібно ввести цифри, пробіл і літеру «a» або «p» — наприклад, 9:00 p. Якщо ввести лише цифри, Excel розпізнає їх як час і запише як 9:00 AM.

Щоб ввести сьогоднішню дату, одночасно потрібно натиснути клавішу CTRL і крапку з комою (;). Щоб ввести поточний час, одночасно потрібно натиснути клавіші CTRL, SHIFT і крапку з комою (рис. 23)

Рис.23. Зразки вирівнювання введених даних

Примітка

Виконати команду Рецензування/Додати примітку, щоб відкрити тимчасове вікно з мигаючим курсором. Потім ввести текст примітки. Примітка готова.

Комірки з примітками можна впізнати по червоній крапці в правому верхньому куті (рис. 24).

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

Формули

Формула – вираз, який підраховує нове значення по вже існуючим.

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

Всі формули починаються з “=”.

В формулах використовуються стандартні арифметичні оператори: додавання, віднімання, множення (*), ділення (/), піднесення до степеня (^).

Якщо з’являється повідомлення Помилка в формулі, то формула введена невірно. Повторити набір або відредагувати комірку, натискаючи F2 і підводячи вказівник до помилки.

Формули можуть містити посилання на комірки (В5 або А3). Посилання на комірки створюються в формулах простим введенням імені, а також виділенням комірок мишею або клавіатури. =В5+С5.

Мишкою створюється так:

Виділити комірку, в якій створюється формула;
Набрати знак “=”. Клацнути на першій комірці, після чого ввести математичний оператор, потім “+” і іншу комірку виділити мишкою. При клацанні на комірці, навкруги неї виникає мигаюча рамка, а ім'я комірки з’явиться в рядку формул..
Натиснути Enter, щоб зберегти формулу.

Спеціальні об’єкти

Виділити комірку.
Виконати команду Вставлення/Зображення.
Вибрати з підменю:

рисунок;
графіка;
фігури;
smart Art;
діаграма.

Перемістити об’єкт на потрібне місце і змінити розміри;
Знищується об’єкт – Delete.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

1. Що означають позначки „червоний хрестик” і „зелений прапорець”?

2. Яким чином відбувається переміщення по електронній таблиці?

3. Які види даних можна вносити в комірки?

4.Які спеціальні об’єкти можна вставляти у комірки електронної таблиці?

5. Як вводяться формули?

6. Яким чином ввести поточну дату і час у комірку?

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

Тема. Введення даних за типами.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. Відкрити вікно програми Excel 2007.
2. Ввести в комірку А1 текст «Програма працює переважно з таблицями, формулами, діаграмами».
3. В комірці А2 ввести поточний рік, в комірці А3 ввести ваш рік народження, в комірці А4 ввести формулу, яка порахує скільки вам років =А2-А3
4. До комірки А1 ввести примітку вільного змісту.
5. В будь-яких комірках ввести числа -12458, 1 1/5, 1,5Е+10, 0 1/4
6. В комірці А5 ввести дату в різних форматах.
7. На аркуші 2 , починаючи з комірки А1ввести такі дані:

Загальний	Числовий	Дробовий	З експонентою	Формули
5102	5 102,00	1 ¼	1,4Е+7	=5+7
120	120,00	1 5/8	2,5Е+25	=А2+А3

Вставити картинку на робочий аркуш.
Закрити програму.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично і захистити свою роботу на оцінку.

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

Як вводять числові значення?
Як вводяться формули?
Як ввести примітку до комірки?
В яких форматах можна вводити дату?
Яким чином вставляються картинки на аркуш?

Тема 7. Редагування даних в Excel 2007

План

Перевірка орфографії
Засіб “знайти і замінити”
Операції над вмістом комірок
Автоматизоване заповнення комірок

1. Перевірка орфографії

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

Щоб перевірити орфографію виділеного діапазону комірок або всього робочого аркуша, необхідно виконати такі дії:

Виконати команду Рецензування/ Правопис/Орфографія (рис. 25), або натиснути клавішу F7, з'явиться вікно "Орфографія” (рис. 26);
Після напису "Немає в словнику" з'явиться перше слово з помилкою, у вікні "Рекомендації пропонується найбільш близьке слово без помилок;
Щоб замінити помилкове слово на пропоноване досить клацнути "мишею" на кнопці "Замінити". Щоб замінити помилкове слово по всьому документу, досить клацнути "мишею" на кнопці "Замінити все ...";
Щоб залишити знайдене слово без змін і перейти до розгляду наступного, досить клацнути "мишею" на кнопці "Пропустити" або "Пропустить все...".

2. Знайти і замінити

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

Щоб знайти потрібний текст, необхідно виконати такі дії:

Виділити діапазон комірок, у яких буде проведена заміна (для проведення заміни на всьому робочому аркуші зробити активною комірку А1);
Виконати команду Основне/Знайти й виділити/Знайти (рис. 27), з'явиться діалогове вікно з вкладкою Знайти;
У полі Знайти ввести текст-взірець;
Клацнути "мишею" на кнопці "Знайти далі" або натиснути клавішу F7, через деякий час курсор зупиниться на клітинці з потрібним текстом.

Заміна тексту

Виділити діапазон комірок, у яких буде проведена заміна;
Виконати команду Основне/Знайти й виділити/Замінити, з'явиться вікно з вкладкою Замінити (рис. 27);
У полі Знайти ввести текст-взірець, натиснути клавішу;
У полі Замінити на: ввести новий текст;
Щоб знайти текст-взірець, клацнути "мишею" на кнопці Знайти далі;
Щоб провести заміну, клацнути "мишею" на кнопці Замінити. Щоб провести заміну по всьому документу, клацнути "мишею" на кнопці Замінити все.

3. Операції над вмістом комірок

Переміщення (копіювання)

Щоб копіювати (перемістити) комірку або діапазон комірок, треба у меню Основне/Буфер обміну/Копіювати (Вирізати), перевести курсор "миші" на нове місце і команда Вставити — на старому місці вміст комірок залишиться і з'явиться на новому місці.

Вилучення комірок

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

Виконати команду Видалити… з контекстного меню комірки (рис. 28) і у вікні вибрати один з перемикачів: комірки з зсувом вліво, комірки з зсувом вверх.

Очищення комірок

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

Виконати команду Очистити вміст… з контекстного меню комірки;

4. Автоматизоване заповнення комірок

Введення даних, що повторюються

Щоб у наступні комірки повторити вміст попередньої комірки, необхідно виконати такі дії:

перевести курсор "миші" на правий нижній куточок комірки, щоб курсор перетворився на "худий плюс";
натиснувши і утримуючи ліву кнопку "миші", перевести курсор у потрібному напрямку до кінцевої комірки;
відпустити кнопку "миші", в усіх відмічених таким чином комірках появиться текст першої комірки (рис. 29).

Автозаповнення

Ехсе1 може виконувати розумні дії при заповненні комірок. Наприклад: можна автоматично заповнити комірки послідовністю назв днів тижня (Пн-Нд або Понеділок-Неділя)

або місяців року (Січень-Грудень або Січ-Груд). Щоб це зробити, досить у активну комірку записати назву першого місяця списку і далі виконати попередні дії.

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

виконати команду Офіс/Параметри/вкладка Найуживаніші/ кнопка Редагувати користувацькі списки. У вікні в полі Списки (рис. 30) набрати свій список, натискаючи клавішу Enter після кожного елементу списку;
натиснути кнопку Додати.

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

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

відпустити СПОЧАТКУ кнопку "миші", ПОТІМ клавішу Ctrl — в усіх відмічених комірках появиться послідовність чисел нумерації, які продовжать номер першої комірки.

Комірки, в яких встановлено час або дата, при автозаповненні мають цікаві особливості. Якщо в комірці, наприклад, встановлено час 22:00, то при автозаповненні в наступних комірках одержимо час 23:00, 0:00, 1:00 і т. д. Якщо в комірці, наприклад, встановлено дату 29.03.2000,

то при автозаповненні в наступних комірках одержимо дату 30.03.2000, 31.03.2000, 1.04.2000 і т.д.

Для заповнення комірок арифметичною прогресією чисел (наприклад, 3, 7, 11, 15...) досить занести у дві суміжні комірки стовпчика або рядка перші два числа (тут 3 і 7), виділити ці комірки і протягнути маркер заповнення до останньої комірки. Автоматично буде визначена різниця прогресії, з урахуванням якої будуть обчислюватися числа для заповнення послідовності комірок.

Автозавершення

Автозавершення застосовується тільки для тексту.

При наборі перших символів слова Ехсеl 2007 пропонує його завершення, використовуючи взірець із вже набраних подібних слів в тому самому стовпчику. Наприклад: набрали бан — Ехсеl пропонує слово банан (якщо воно було в комірці вище). Досить натиснути клавішу Enter і цей варіант завершення буде записано у комірку. Якщо запропонований варіант не підходить, продовжують набирати слово. Наприклад: карт — пропонується карман. Продовжуємо набирати карто — пропонується картонка. Якщо це потрібний варіант, натискаємо клавішу Enter, у комірці з'являється все слово картонка.

Щоб відмовитися від послуг автозавершення, треба введення тексту розпочати з апострофа.

Автозаміна

Автозаміна працює точно так, як і в Word. Після набору слова з орфографічними помилками і натискання клавіші "пробіл" або розділового знака виконується його автоматична заміна на слово, відповідний варіант заміни якого описано у вікні "Автозаміна".

Режим автозаміни дає можливість додавати у список свої варіанти заміни, для цього необхідно виконати такі дії:

виконати команду Офіс/Параметри/ Найуживаніші/ Правопис/ Параметри автозамінювання.
у вікні (рис. 31) в поля Замінювати набрати варіант слова з помилкою або скорочене слово; у вікні “на” набрати варіант слова, яке повинне замінити слово з помилкою;
виконати команду Додати, набраний варіант занесеться у список варіантів заміни і натиснути кнопку ОК.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

1. Що входить у засоби редагування електронних таблиць?

2. У чому полягає перевірка орфографії?

3. Які дії можливі при перевірці орфографії"?

4. Як перемістити вміст комірок буксировкою?

5. Як копіювати вміст комірок буксировкою?

6. В яких випадках рекомендується буксировка?

7. Де знаходиться і як виглядає маркер заповнення?

8. Як створити власний список автозаповнення?

9. Як працює автозавершення?

10. Як працює автозаміна?

11. Як внести у список автозаміни свій варіант?

12. Що робити, якщо автозаміна не працює?

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

Тема. Введення та редагування даних .

Мета. Набути навичок введення і редагування даних у Excel. Оволодіти прийомами роботи з автоматизованим заповненням комірок.

Оснащення. ПК, роздаткові картки.

Хід роботи

1. Ознайомитися з темою та метою практичної роботи.

2. Прочитати теоретичний матеріал, поданий вище.

3. Виконати практичне завдання.

3.1. Перейменувати Лист 1 іменем Таблиця.

3.2. Створити список автозаповнення днів неділі.

3.3. В комірку В2 ввести слово Таблиця, скопіювати слово від комірки В2 до В10.

3.4. Перейменувати один з аркушів назвою Календар і створити на ньому список автозаповнення з назвою місяця: Травень

Понеділок 1 8 15 22 29

Вівторок 2 9 16 23 30

Середа 3 10 17 24 31

Четвер 4 11 18 25

П’ятниця 5 12 19 26

Субота 6 13 20 27

Неділя 7 14 21 28

Знайти в тексті слово Таблиця.
Знайти в тексті слово Травень і замінити його на Квітень.
Перевірити як працює авто завершення, ввівши такі слова: картопля, карта, картонка, картопля, каша, картка карта.
Ввести за допомогою арифметичної прогресії числа: 1,3,5,7,9,11,13,15,17,19.
В режимі автозаміни ввести слова: програма – додаток, система-машина, дитина-дорослий.
Ввести в комірку В10 слово «Маркер» і виконати з ним наступні дії: перенести слово в комірку К15, скопіювати дане слово в комірку В11, скопіювати дане слово в діапазон К1:К10, очистити коміркуВ11.
Перевірити орфографію всього введеного тексту.
Закрити програму

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично і захистити свою роботу на оцінку.

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

Як перевірити орфографію документу?
Як працює автозавершення?
Що таке автозаміна? Як її створити?
Як працювати з автозаповненням?

Як створити власний список автозаповнення?
Як створити текст, що повторюється?
Яким чином здійснити заміну якогось слова на інше?

Тема 8. Форматування комірок і документів. Умовне форматування

План

Форматування комірок
Вікно форматування комірок
Форматування рядків і стовпчиків
Умовне форматування

1. Форматування комірок

Для форматування даних в комірках призначена вкладка Основне (рис. 32):

Шрифт.
1. Шрифт.
2. Розмір.
3. Збільшити розмір шрифта.
4. Зменшити розмір шрифту.
5. Жирний.
6. Курсив.
7. Підкреслення ( підкреслення, подвійне підкреслення)
8. Межі.
9. Заливка.
10. Колір шрифту.
Вирівнювання.
1. Вирівнювання по вертикалі.
2. Вирівнювання по горизонталі.
3. Орієнтація (рис. 33).
4. Зменшити відступ.
5. Збільшити відступ.
6. Перенесення тексту.
7. Об’єднати та розташувати в центрі.
Число.
1. Числовий формат (рис. 34).
2. Фінансовий формат числа ( рис. 35).
3. Відсотковий формат.
4. Формат із роздільниками.
5. Збільшити розрядність.
6. Зменшити розрядність.

Відкривши вікно Формат клітинок в даній категорії з’явиться ряд вкладок для форматування:

1. Число (рис.36) – дозволяє вибрати числовий формат для числа, зокрема такі, як зображено в таблиці 1.

Таблиця 1. Формати чисел

Формат числа	Вигляд числа	Пояснення
Загальний	41 41532	Стандартний числовий формат, вирівнювання по правому краю.
Числовий	-41,53 41,00 415 327	Гнучкий числовий формат, в якому числа оформлюються комами, змінною кількістю розрядів.
Грошовий	$ 41.53 41,00 грн. 415 327 €	Загальний грошовий формат, в якому числа оформлюються знаками грошової одиниці.
Фінансовий	41,53р. $41,00 0,327р.	Спеціальний грошовий формат, призначений для вирівнювання стовпчиків грошових сум
Дата	1/15/05 15 янв 2005 Янв-15-05	Загальний формат дати, який відображує календарні дати в декількох стандартних варіантах
Час	13:36 13:36:48	Загальний формат часу, який відображує значення часу в декількох стандартних варіантах
Відсотковий	153 % 25 %	Формат, який множить значення комірки на 100 і відображує результат зі знаком процента (%)
Дробовий	3/7 2/16	Формат, який представляє числа у вигляді дробів
Експоненціальний	1.25Е-08 4.58Е+12	Експоненціальний запис чисел з великою кількістю цифр
Текстовий	текст	Формат, в якому числа інтерпретуються як текст

Рис. 36 Вкладка Число вікна Формат клітинки

Вирівнювання (рис.37) – дозволяє вибрати вирівнювання даних в комірці по вертикалі і по горизонталі, змінити напрямок тексту, орієнтацію тексту.

Рис. 37 Вкладка Вирівнювання вікна Формат клітинки

Шрифт (рис.38) – дозволяє вибрати форматування шрифту: тип шрифту, розмір, написання, колір, видозміну.

Рис. 38. Вкладка Шрифт вікна Формат клітинки

Межі (рис.39) – дозволяє вибрати тип, товщину межі, вибрати окремі межі.

Рис. 39 Вкладка Межі вікна Формат клітинки

Заливка (рис.40) – дозволяє вибрати колір заливки комірки.

Рис. 40 Вкладка Заливка вікна Формат клітинки

Захист (рис. 41) - дозволяє встановити захист на комірку.

Рис. 41 Вкладка Захист вікна Формат клітинки

3. Форматування рядків і стовпчиків

Зміна висоти рядків і ширини стовпчиків

Якщо є потреба примусово змінити висоту рядка або ширину стовпчика, треба скористатися засобами двонаправлених стрілок, які з'являються при переведенні курсору на тонку лінію. Що розділяє стовпчики або рядки.

Також можна скористатися командою Основне/Клітинки/Формат. Відкриється меню (рис. 42). В меню можна здійснити зміну розмірів клітинки, перейменувати аркуші, приховати аркуші.

4. Умовне форматування

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

Поглянемо на рядок з прибутком. У нашій таблиці прибуток по місяцях має значення, менші 8500 рублів і великі 8500 рублів. Як зробити так, щоб прибуток, великий 8500 рублів, був написаний символами червоного кольору? Можна вказати вічка і за допомогою команди Колір тексту зробити символи червоними. Проте при зменшенні по яких-небудь причинах розміру прибули менше 8500 вона залишиться червоною. Як зробити так, щоб Excel сам визначав і форматував вічка? Інструмент для цього називається умовним форматуванням.

Щоб використати умовне форматування потрібно:

1.Виділити комірки з даними.

2.У вкладці Основне в розділі Стилі клацнути кнопку Умовне форматування. У меню, що з'явилося, вибрати пункт Виділити правила клітинок/Більше, меньше або дорівнює. З'явиться вікно Більше.

3.У першому полі вікна ввести адресу комірки, в другому полі вибрати червоний текст. (рис 43).

4. Натиснути кнопку ОК. Комірки, які відповідають даним умовам будуть зафарбовані червоним кольором.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Яка команда відкриває вікно діалогу для форматування комірок?
Які числа можна вводити в комірки?
Які способи вирівнювання існують?
Як можна відформатувати шрифти?
Як окреслити контури таблиць?
Яким чином можна змінити висоту рядків?
Яким чином використати умовне форматування?

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

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

Мета. Набути навичок створення таблиць у Excel і їх форматування. Освоїти усі способи форматування.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

3.1. Запустити програму Microsoft Excel 2007.

3.2. Створити бланк за зразком.

ЗВІТНА ДОВІДКА

Найменування цінностей	Прийнято цінностей , грам		Ціна за одиницю товару, г	На загальну суму , грн..	Передано цінностей, грам		Залишок на кінець дня, грн..
Найменування цінностей	Від завідувача каси	Від касових працівників	Ціна за одиницю товару, г	На загальну суму , грн..	Завідувач каси	Касовими працівник ками	Залишок на кінець дня, грн..
Золото	55000	40000	5,00 грн.		30000	39860
Срібло	40000	457	3,60 грн.		10000	300
Всього:

На новому аркуші створити таку таблицю.

Маршрутний аркуш

№ з/п	Дата	Найменування операції	Брак по видам				Відмітка ОТК
№ з/п	Дата	Найменування операції	к/з	м/е	з/е	БТУ	Відмітка ОТК
1	25.12.2012	Виготовлення	1%	666 грн.	0	0	добре
2	26.12.2012	Контроль	3%	546 грн.	7	23	відмінно
3	28.12.2012	Намотування	8%	12 грн.	4	45	відмінно
4	29.12.2012	Контроль ОТК
5	01.01.2012	Знищення лишків	3%	654 грн.	12	45	відмінно
Всього

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Як змінити межі комірки?
Як вилучити сітку з екрану?
Як задати фон комірки?
Як можна об'єднати комірки?
Як встановити бажаний тип даних?
Якими способами можна встановити вирівнювання тексту в комірці?
Як змінити колір фону комірки?

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

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

Мета. Навчитися форматувати таблиці у Excel.

Оснащення. ПК, картка-завдання.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

3.1. Запустити програму Microsoft Excel 2007.

3.2. Створити наведені нижче таблиці, причому кожну на окремому аркуші:

Таблиця № 1. Книга обліку товарів

Обсяги продажу товарів, робіт, послуг по яких виникає податкове зобов’язання					Обсяг проведених коригувань				Проведення розрахунків з продажу товарів, робіт, послуг.
Загальні обсяги продажу	Продаж на митній території України				Коригування продажу товарів, послуг, що оподатковуються за ставкою 20%		Коригування продажу товарів, робіт, послуг, по яких податок на додану вартість не нараховується		Форма розра-хунку (бартер, готівка, оплата розраху-нкового рахунку та ін.)	Дата проведення розра- хунку
	Платникам ПДВ		Неплатникам ПДВ
	База оподаткування	ПДВ	База оподаткування	ПДВ	База оподаткування	ПДВ	За ставкою 0%	Звільненні від оподатку-вання
1	2	3	4	5	6	7	8	9	10	11

Таблиця № 2. Журнал обліку працівників

Додаток

до інструкції Міністерства фінансів

ЖУРНАЛ

Обліку працівників, що вибувають у відрядження з

(найменування об’єднання, підприємства, установи, організації)

№ з/п

Прізвище, ім’я, по батькові відрядженого працівника

Посада

Номер посвідки

Прізвище, посада працівника який підписав посвідку

Дата

Підпис відрядженого

фактичного вибуття

фактичного прибуття

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Як змінити межі комірки?
Як вилучити сітку з екрану?
Як задати фон комірки?

Як можна об'єднати комірки?
Як встановити бажаний тип даних?
Якими способами можна встановити вирівнювання тексту в комірці?
Як змінити колір фону комірки?

Тема 9. Створення формул і обрахунків

План

Виконання обчислень
Посилання на комірки
Засіб Автосума
Помилки в формулах

1. Виконання обчислень

Формула – це сукупність операндів, з’єднаних між собою знаками операцій і круглих дужок, наприклад:

=А1+А2

Щоб записати формулу в комірку потрібно перед нею записати „=”. Далі записується сама формула. Вона повинна бути записана в 1 рядок. Використовуються при цьому математичні оператори. Excel допускає арифметичні операції

+ (додавання)

– (віднімання),

* - (множення),

/ - (ділення),

^ - піднесення до степеню).

Текстові оператори - & - амперсанд, який з’єднує частини тексту з різних комірок, наприклад записується так: =А1 & А2 (з’єднати 2 слова з комірок А1 і А2.

Логічні оператори - < - менше, > - більше, >= - більше дорівнює, <= - менше дорівнює, <> - не дорівнює.

Формулу записують безпосередньо в комірку, тобто мишкою вказують адреси комірок, які входять в склад формул (А1,В5, С7). (рис. 44)

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

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

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

2. Посилання на комірки

Адреси комірок (посилання на комірки) можна використовувати у формулах. Можливі відносні, абсолютні і змішані посилання.

Посилання, яке включає назву стовпця і номер рядка є відносним.

При копіюванні формули, а також редагуванні аркуша таке посилання буде модифікуватись. (Наприклад, є формула, яка посилається на комірку В4, то при вилученні рядка 2 і зсуву по таблиці вгору формула буде модифікуватись так, щоб посилатись на комірку В3).

В абсолютних посиланнях перед назвою стовпця і номером рядка стоїть символ $ (не модифікується). Щоб його поставити потрібно натиснути клавішу F4 на клавіатурі (рис. 45).

В змішаних – абсолютною є назва стовпця і відносною – номер рядка або навпаки (приклад $ А 1, А $ 1) модифікується тільки відносна частина посилання). Щоб його поставити потрібно натиснути клавішу F4 2 рази або 3 рази на клавіатурі.

Якщо у формулі є посилання на комірки на іншому аркуші, то посилання повинно містити ім’я аркуша, знак оклику та адресу комірки (аркуш 1! А1).

Комірка або діапазон комірок може мати ім’я, для цього потрібно:

Виділити комірку.
Записати потрібне ім’я в поле адреси активної комірки, що знаходиться ліворуч від рядка формул і натиснути Enter.

Для привласнення імен також можна виконати команду: Формули /Призначення імені / Визначити ім’я, попередньо виділивши потрібний діапазон комірок

Щоб швидко перейти у потрібну комірку, досить клацнути на значку „трикутник” ліворуч від рядка формул, відкриється список іменованих комірок, у якому вибрати потрібну.

3. Засіб Автосума

Exсel 2007 надає засоби автоматизації.

Нехай є потреба підрахувати суму чисел А1 : А6, і помістити в А7.

Для цього комірку А7 потрібно виділити і виконати команду Формули/Бібліотека функцій/Автосума.

В комірці А7 з’явиться формула SUM (А1:А6), комірки, для яких буде обчислюватись формула, окресляться пунктирною рамкою. Натиснути Еnter і сума окреслених комірок з’явиться в А7.

При бажанні із стовпчика можна вибрати комірки, сума яких цікавить (приклад А3:А6). Треба встановити “товстий плюс” на першу комірку, натиснувши провести курсор до останньої комірки діапазону. Пунктирна рамка стане тільки для відмічених комiрок. Клавіша Еnter і сума в А7 (рис. 46).

Також в кнопці Автосума є стандартні функції, які вважаються найбільш вживаними. Це такі функції, як:

МАКСИМУМ – визначення максимального числа серед діапазону чисел.

МІНІМУМ - визначення мінімального числа серед діапазону чисел.

СЕРЕДНЄ – середнє значення чисел.

КІЛЬКІСТЬ – кількість значень (рис. 47).

4. Помилки в формулах

Якщо при обчисленні сталася помилка, то в комірку виводиться повідомлення про помилку, яке починається із символу #.

Деякі з них:

##### – збільшити ширину комірки.

#NUM! – виконаний неприпустимий аргумент або операнд формули.

#DIV/0! – ділення на 0.

#NAME! – невірне посилання на комірку.

#N/A – неприпустимий аргумент.

#REF – неприпустиме посилання на комірку.

#VALUE! – неприпустиме числове значення.

Виконавши команду Формули/Аудит формули/Показати формули в комірках з’являться не результати обчислення, а формули, за якими обчислювалися дані (рис. 48).

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

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Які є види посилань на комірку?
Які посилання називають відносними?
Що означає абсолютне посилання?
Яким чином вводиться формула у комірку?
Якими символами виводиться помилка у формулі?
Як виконати перевірку функції на помилки?

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

Тема. Створення формул. Введення формул .

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. Створити таку таблицю.

Виконати підрахунок в стовпчику: Вартість товару (=С3*D3).
Додати стовпчик Вартість товару у $, порахувати даний стовпчик (=Вартість товару*Курс валют). Курс валют = 9,80 грн. розмістити в комірці А10.
Порахувати окремій комірці середню ціну товарів, максимальну ціну товарів, кількість товарів.
Створити таку таблицю.

Виконати підрахунки в стовпчику «На тиждень» (=H11*7), загалом порахувати через Автосуму.
Порахувати хто з тварин більше всього потребує денної норми суміші, хто менше всього потребує суміші і середнє значення денної суміші для всіх тварин разом.
Додати стовпчик Вартість товару у $, порахувати даний стовпчик (=На тиждень *Курс валют). Курс валют = 9,80 грн. розмістити в комірці А16.
Закрити програму.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Якими двома способами можливо ввести формулу?
Які є посилання на комірки?
Як створити абсолютне посилання на комірку?
Як швидко скопіювати формулу?
Що таке Автосума?

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

Тема. Створення формул. Введення формул.

Мета. Навчитися проводити обчислення у таблицях Excel за допомогою простих формул і Автосуми.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. Запустити програму Microsoft Excel 2007.
2. Виконати наступні завдання.

Завдання № 1

• Набрати таблицю з курсом валют такого зразка:

Валюта	Курс по відношенню до гривні
USD	7,25
Рублі	0,85
EURO	9,30

• Створити таблицю згідно взірця

№ з/п	Найменування товару	Кількість товару на складі	Ціна, грн.	Ціна в EURO	Ціна в рублях	Ціна в USD
1	Комп’ютер	8	4087,50	=Гривні/курс	=Гривні/курс	=Гривні/курс
2	Принтер	6	1907,50
3	Модем	4	1362,50
4	Сканер	3	953,75
5	Монітор	10	1635,00
6	Копіювальний апарат	2	2997,50
7	Факсимільний апарат	2	1744,50
Всього
Знижка, %			5	2	4	2
До оплати			=Всього*Знижка

Завдання № 2.

Обчислити квадрати і куби даних чисел, використовуючи формули. Обчислити суму даних чисел.

Число	2	3	4	5	6	7	8	9	10	Сума	Кількість значень	Мінімальне значення	Максимальне знач.
Квадрат числа
Куб числа

Завдання № 3

Є 5 ланів прямокутної форми з розмірами А і В. Знайти периметр і площу кожного з ланів по формулам Р=(А+В)*2, S= А*В, площа у % = S/Всього*100%.

	А, м.	В, м.	Р, м	S, м²	Площа у %
1	57,4	63,9
2	65,2	73,6
3	43,7	89,3
4	67,0	65,8
5	84,7	75,6
Всього
Середня площа
Максимальна площа

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Якими двома способами можливо ввести формулу?
Як можна виправити формулу?
Як швидко скопіювати формулу?
Що таке Автосума?
Як створити Автосуму?

Тема 10. Використання функцій

План

Поняття функції
Майстер функції
Функції по категоріям
Дослідження зміни функції

Поняття функції

Функція – це готова, вбудована в Excel 2007 формула, якій присвоєно унікальне ім’я.

В Excel 2007 - більше 1000 функцій.

Кожна функція має 3 обов’язкових елементи:

Знак =;
Ім’я функції;
Аргументи (адреси комірок, на основі яких виконується обчислення).

Приклад запису функції: =SUM(A1:A5) – сума чисел в діапазоні від A1 до A5.

Для того, щоб функція підраховувалася вірно, потрібно дотримуватися синтаксису (послідовності розташування символів):

Аргументи функцій записуються в круглих дужках. Відразу за назвою функції та відокремлюються один від одного символом ;
Проміжки між назвою функції і дужками не дозволяються.
Кількість дужок, що відкриваються, повинно відповідати кількості дужок, що закриваються.
Аргументами функцій можуть бути: числа, текст, логічне значення, масиви чисел, посилання на комірки.
Функції можуть бути вбудованими в інші функції і формули. Рівень вкладення – не більше 7 рівнів.
Деякі функції не вимагають аргументів (наприклад, функція поточної дати, математична константа РІ і т.д.).

2. Майстер функцій

Усі функції знаходяться в команді Формули/Бібліотека функцій (рис. 49). Є такі категорії функцій:

Математичні.
Статистичні
Фінансові.
Дати і часу.
Інженерні.
Текстові.
Логічні.

Достатньо вибрати категорію функцій, щоб побачити весь перелік функцій.

Можна також скористатися Майстром функцій.

Щоб використати Майстра функцій потрібно:

Виконати команду Формули/Бібліотека функцій/Вставити функцію.
У відкрившомуся вікні (рис. 50) вибрати категорію функцій (математичні, статистичні і т.д.), вибрати саму функцію і натиснути кнопку ОК.
У вікні Аргументи функції (рис. 51) вписати значення в текстові поля, внизу вікна з’явиться готовий результат. Якщо користувач забув правила роботи з даною функцією, потрібно натиснути посилання Довідка по даній функції.

Вказавши аргументи у вікні натиснути кнопку ОК. Результат внесеться в комірку, а в рядку формул з’явиться запис формули.

3. Функції по категоріям

Усі функції розділені по категоріям для зручності користувачу.

МАТЕМАТИЧНІ

ABS. Повертає абсолютне значення числа
ACOS. Обчислює арккосинус числа
ASIN. Повертає арксинус числа
ATAN. Повертає арктангенс числа
COS. Повертає косинус числа
DEGREES. Перетворює радіани на градуси
EVEN. Округлює число до найближчого більшого парного цілого
EXP. Повертає число e, піднесене до вказаного ступеня
INT. Округлює число до найближчого меншого цілого
LN. Повертає натуральний логарифм числа
LOG. Повертає логарифм числа за вказаною основою
LOG10. Повертає десятковий логарифм числа
MOD. Повертає остачу від ділення
ODD. Округлює число до найближчого більшого непарного цілого
PI. Повертає число «пі».
POWER. Повертає число, піднесене до степеня
PRODUCT. Перемножує аргументи
QUOTIENT. Повертає цілу частину частки від ділення
RADIANS. Перетворює градуси на радіани
ROMAN. Перетворює число, записане арабськими цифрами, на римське

ROUND. Округлює число до вказаної кількості знаків
ROUNDDOWN. Округлює число до меншого, у напрямку нуля
ROUNDUP. Округлює число вгору, у напрямку від нуля
SIN. Повертає синус указаного кута
SQRT. Повертає додатне значення квадратного кореня
SUM. Підсумовує аргументи
TAN. Повертає тангенс числа
TRUNC. Видаляє дробову частину числа

Наприклад. Щоб порахувати функцію 8⁵ потрібно: викликати командою Формули/ Бібліотека функцій / Математичні /POWER. У вікні аргументів функції (рис. 52) ввести в поле Число - 8, в поле Степінь – 5. Натиснути ОК і отримаємо результат.

СТАТИСТИЧНІ

AVERAGE. Повертає середнє арифметичне аргументів
COUNT. Підраховує кількість чисел у списку аргументів
GEOMEAN. Повертає середнє геометричне
MAX. Повертає найбільше значення у списку аргументів
MEDIAN. Повертає медіану вказаних чисел
MIN. Повертає найменше значення у списку аргументів

ФУНКЦІЇ ДАТИ І ЧАСУ

DATE. Повертає числове значення для вказаної дати
DAYS360. Обчислює кількість днів між датами на основі 360-денного року
HOUR. Перетворює числове значення дати на годину
MINUTE. Перетворює числове значення дати на хвилини
MONTH. Перетворює числове значення дати на місяць
SECOND. Перетворює числове значення дати на секунди
TODAY. Повертає числове значення для сьогоднішньої дати
WEEKDAY. Перетворює числове значення дати на день тижня
WEEKNUM. Перетворює числове значення дати на номер тижня року

ЛОГІЧНІ ФУНКЦІЇ

Функція TRUE

Повертає логічне значення ІСТИНА. Функція TRUE використовується зазвичай для забезпечення сумісності з іншими програмами електронних таблиць.

Функція FALSE

Повертає логічне значення ХИБНІСТЬ.

Функція NOT

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

Синтаксис NOT(логічне)

Функція AND

Повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА; повертає значення ХИБНІСТЬ, якщо хоча б один аргумент має значення ХИБНІСТЬ.

Синтаксис AND(логіч1; [логіч2]; ...)

Функція OR

Повертає значення ІСТИНА, якщо принаймні один аргумент має значення ІСТИНА, або ХИБНІСТЬ, якщо всі аргументи мають значення ХИБНІСТЬ.

Синтаксис OR(лог_значення1;лог_значення2;...)

Функція IF

Повертає одне значення, якщо обчислене значення заданої умови — ІСТИНА, та інше значення, якщо обчислене значення заданої умови — ХИБНІСТЬ.

Синтаксис IF(лог_вираз;значення_якщо_істина;значення_якщо_хибність)

Лог_вираз — це будь-яке значення або вираз, які можна оцінити як істинні або хибні. Наприклад, A10=100 — це логічний вираз; якщо значення в клітинці A10 дорівнює 100, то вираз є істинним.

Значення_якщо_істина — це значення, яке повертається, якщо лог_вираз має значення ІСТИНА. Наприклад, якщо цим аргументом є текстовий рядок «У межах кошторису», і значення лог_виразу є істинним, тоді функція IF відображає текст «У межах кошторису»..

Значення_якщо_хибність — це значення, яке повертається, якщо лог_вираз має значення ХИБНІСТЬ. Наприклад, якщо аргументом є текстовий рядок «Поза межами кошторису», і значення лог_виразу є хибним, тоді функція IF відображає текст «Поза межами кошторису».

ПРИКЛАД

Потрібно порахувати скільки отримають працівники премію, якщо є така умов: якщо пропрацював працівник більше 5 років премія у розмірі 200 грн., якщо менше премія в розмірі 100 грн.

Розв’язок

Створити таблицю такого зразка:

	А	В	С
1	Прізвище	Стаж	Премія
2	Петров	5
3	Сидорів	2
4	Маникін	8

Стати курсором в комірку С2 для підрахунку і викликати командою Формули/ Бібліотека функцій / Логічні функцію IF. У вікні аргументів функції (рис. 53) ввести в поле логічний вираз В2>5, в поле Значення якщо істина – 200 грн., в поле Значення якщо хибність – 100 грн. В комірці С2 з’явиться результат. Скопіювати дану формулу в діапазон С2:С4.

ФІНАНСОВІ ФУНКЦІЇ

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

Інвестицією називається вкладення грошей у деякий бізнес на певних умовах.

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

Розглянемо основні параметри фінансових функцій і їхні скорочені назви:

Ставка — це відсоткова ставка за період.
Період — це період, для якого потрібно обчислити суму сплати відсотків, і який має перебувати в діапазоні від 1 до кпер.
Кпер — це загальна кількість періодів сплати за рік.
Зв — поточна вартість або загальна сума, яка на цей час дорівнює сукупності майбутніх виплат.
Мв — це майбутня вартість або касовий залишок, якого слід досягти після останньої виплати. Якщо аргумент мв пропущено, він отримує значення 0 (наприклад, майбутня сума позики становить 0).
Тип — це число 0 або 1, яке вказує, коли заплановані виплати. Якщо аргумент тип пропущено, він має значення 0. 0 – наприкінці періоду, 1 – на початку.
Спл — сплата, яка здійснюється кожного періоду; вона залишається незмінною протягом строку фінансової ренти. Зазвичай спл містить основну частину боргу й відсоток і жодних додаткових внесків або податків. Якщо аргумент спл не вказано, необхідно включити аргумент зв.
Поч_вартість — початкова вартість активу.
Зал_вартість — залишкова вартість активу вкінці терміну експлуатації.
Термін_експлуатації — кількість періодів амортизації активу (іноді називається періодом корисного використання активу).

Функції

FV. Повертає майбутню вартість інвестицій.

Синтаксис: FV(ставка;кпер;спл;зв;тип).

Наприклад. Інвестор вкладає в бізнес 2000 грн. на умовах 5% ставки прибутку щомісяця. Яка вартість інвестицій через 36 місяців.

Розв’язок

В комірку А1 ввести ціну – 2000 грн, в комірку А2 ввести 5%, в комірку А3 ввести кількість місяців – 36.

Щоб порахувати майбутню вартість інвестицій потрібно виконати команду Формули/Бібліотека функцій/Фінансові/FV. У вікні функції (рис. 54) ввести: в поле ставка – 5% грн. в поле Кпер – 36, в поле Зв - -2000 грн, Тип – 0. Отримємо таку формулу: =FV(5%;36;;-2000;0). Порахувавши, отримаємо значення - 11 583,63 грн.

IPMT. Повертає суму сплати відсотків за інвестицією за вказаний період.

Синтаксис: IPMT(ставка;період;кпер;зв;мв;тип).

NPER. Повертає кількість періодів сплати за інвестицією.

Синтаксис: NPER(ставка;спл;зв;мв;тип)

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

Синтаксис: SLN(поч_вартість;зал_вартість;термін_експлуатації).

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

Синтаксис: SYD(поч_вартість;зал_вартість;термін_експлуатації;період)

Наприклад. Підприємство закупило пристрій для вимірювання вологи ціною 30 000 грн. Через 10 років вартість – 15 000 грн. Порахувати амортизацію пристрою.

Розв’язок

В комірку А1 ввести ціну – 30 000 грн., в комірку А2 ввести кількі сть років, в комірку А3 ввести нову вартість – 15 000 грн.

Щоб порахувати амортизацію потрібно виконати команду Формули/Бібліотека функцій/Фінансові. Вибрати функцію SLN. У вікні введення аргументів функції (рис. 55) ввести в список «Початкова

вартість» 30000, в списку Залишкова вартість 15000, в списку Термін експлуатації 10 р.

Відповідь отримаємо 1500 грн.

4. Дослідження зміни функції

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

Записати перше число діапазону в комірку.
Виділити дану комірку і виконати команду Основне/Редагування/Заповнити/ Прогресія.
У вікні, яке відкриється вибрати в полі Розташування – за рядками або за стовпцями, в полі Тип – вибрати арифметичну прогресію, в полі Крок – ввести значення кроку зміни функції, в полі Граничне значення вибрати кінцеве значення діапазону і натиснути кнопку ОК (рис. 56). З’явиться ряд чисел.

Рис. 56. Вікно для створення прогресії

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

значень діапазону.

Наприклад, необхідно дослідити зміну функції Y=СОS (X) на діапазоні [-1; 1 ] з кроком К=0,5.

Розв’язок

Записати в комірку А1перше число діапазону -1.
Виділити дану комірку і виконати команду Основне/Редагування/Заповнити/ Прогресія.
У вікні, яке відкриється вибрати в полі Розташування – за рядками , в полі Тип – вибрати арифметичну прогресію, в полі Крок – ввести 0,5, в полі Граничне значення вести 1 і натиснути кнопку ОК. З’явиться ряд чисел з розташуванням по рядкам.
Стати курсором в комірку В1, в якій буде вестися обрахунок і в категорії математичних функцій вибрати СОS. Вказати у вікні аргументи і ОК. В комірці має бути така функція = СОS(А1).
Скопіювати формулу з комірки В1 на всі інші числа діапазону від [-1; 1 ].

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

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

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

Тема. Робота математичними функціями.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання

3.1. Створити таблицю наведеного нижче зразка.

3.2. Стовпчик „Обчислення” повинен бути заповнений формулами.

Назва функції	Число 1	Число 2	Обчислення
Множення чисел	325, 6	259, 56	?
Сума чисел	3,59	152,95	?
Математична константа			?
Модуль числа	-2,45		?
Піднесення числа до степеню	0,45	степінь 2	?
Перетворення радіана на градуси	0,56		?
Десятковий логарифм	1,26		?
Повернення остачі від ділення	12	5	?
Перетворення градусів на радіани	36		?
Перетворення на римське число	45		?
Квадратний корінь числа	6		?
Видалення дробової частини числа	7, 6791	2	?
Сінус числа	0,81		?
Округлення до більшого непарного цілого	1,36		?
Округлення числа вверх	58,6926	до 1 знака	?
Округлення числа вниз	14,3269	до 2 знаків	?
Округлення до ближнього меншого цілого	14,3269		?
Округлення до парного числа	17,459		?

3.3. Засобами програми розрахувати значення функції: У= СOS (2x + x³- 3) при

х = [від 0 до 0,97], крок К =0,1.Створити та відформатувати таблицю значень функції при вказаних значеннях аргументу.

3.4. Вирішити задачу.

Дві крапки на площині мають такі координати.

А		В		Відстань між крапками
Х₁	У₁	Х₂	У₂	Відстань між крапками
3,5	1,78	7,8	3,87	?
5,7	3,4	9,7	6,5	?

Створити таблицю і визначити відстань між крапками по формулі.

АВ=

4. Написати творчий звіт про виконану роботу.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

З яких складових складається функція?
Які категорії функцій існують?
Як викликати математичні функції?
Як виправити функцію?

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

Тема. Робота математичними функціями і статистичними.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання:
1. Створити таблицю наведеного нижче зразка.

Назва функції	Аргумент	Аргумент	Обрахунок
Середнє арифметичне	12	21	?
Кількість значень	13	26	?
Середнє геометричне	14	25	?
Максимальне число	14	23	?
Мінімальне число	15	47	?
Медіана чисел	17	12	?

Порахувати дані функції, використовуючи статистичні функції, і дані записати в стовпчик Обрахунок.
Вирішити задачу.

Є п'ять ланів трикутної форми з розмірами А. В і С у метрах. Знайти площу кожного лану за допомогою формули Герона (Р=(А+В+С)/2, S= SQRT (P*(P-A)*(P-B)*(P-С). Площу S округлити до 0,01;

Код лану	А, м.	В, м.	С, м.	Р, м	S, м²	Середня площа ланів	Сумарна площа всіх ланів
001	57,4	63,9	89,5	?	?	?	?
002	65,2	73,6	51,2	?	?
003	43,7	89,3	98,7	?	?
004	67,0	65,8	87,4	?	?
005	84,7	75,6	61,9	?	?

4. Написати творчий звіт про виконану роботу.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

1. Що таке функція ?

2. Що називається аргументом функції ?

3. З чого може складатися список аргументів ?

4. Які функції відносяться до статистичних?

5. Яким чином порахувати середнє геометричне декількох чисел?

6. Які функції відносяться до математичних?

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

Тема. Робота з функціями Дати і Часу.

Мета. Набути навичок обчислення даних в таблицях Excel за допомогою функцій Дати і часу.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання

3.1. Створити таблицю наведеного нижче зразка.

3.2. Стовпчик „Обрахунок” повинен бути заповнений формулами.

Назва функції	Аргумент	Обрахунок
Поточна дата (сьогодні)		?
Дата	18.12.2011	?
Кількість днів між двома датами	(Дата; Поточна дата)	?
Час	12:36:50	?
Хвилини	12:36:50	?
Секунди	12:36:50	?
Місяць	18.12.2011	?
Номер дня тижня	15.10.11	?
Номер тижня року	01.01.2011

3.3. На другому аркуші в комірці А1 ввести поточну дату. А починаючи з комірки А2 почати будувати таблицю.

№ з/п	Прізви-ще	Дата прийняття на роботу	Місяць прийняття на роботу	День тижня прийняття на роботу	Номер тижня прийняття на роботу	Кількість відпрацьованих днів	Кількість повно років (округлення до цілого)
1	Іванов	12.12.2009	MONTH	WEEKDAY	WEEKNUM	DAYS360	=DAYS360/12
2	Петров	18.08.2009
3	Сидоров	01.01.2010
4	Петрик	05.01.2011
5	Іваненко	07.02.2011
6	Сидорко	09.04.2011

4. Написати творчий звіт про виконану роботу.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

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

Тема. Робота з логічними функціями.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання
1. Створити таблицю наведеного нижче зразка.

	A	B	C	D	E
1	Прізвище	Об’єм продаж	Комісійні 1	Комісійні 2	Кращий продавець
2	Іванов	9000	Формула 1.	Формула 2.	Формула 3.
3	Петров	23000
4	Сидоров	45000
5	Федоров	35000
6	Яковлев	45000

Обрахувати за формулами:

Формула 1. Якщо об’єм продаж <20000, то комісійні складають 10% від його об’єму, а в інших випадках 20%.

Формула 2. Якщо об’єм продаж <20000, то комісійні складають 10% від його об’єму, якщо < 30000, то 20%, а в інших випадках 30%.

Формула 3. Для вибору найкращих менеджерів використати формулу: якщо в менеджера максимальні продажі серед усіх, то він найкращий. Формула має вийти така:

=IF(В2=МАX ($В$2:$В$6);"Найкращий";"Покращити результат"

Використовуючи функцію IF здійснити розрахунки прибуткового податку.

	Тарифи на послуги мобільного зв'язку оператора life:)
	A	B	C	D
1	Оператор	life:)	life:)	Вигідніший оператор
2	Пакет	"Простір life:)"	"Єдиний"	Вигідніший оператор
3	Вихідні дзвінки в мережі	0,19	1,00	Формула 1
4	Вихідні дзвінки на інші номери	1,20	1,00
5	Плата за з'єднання	0,00	0,00
6	SMS	0,25	0,25
7	MMS	0,75	0,75

Формула 1. Якщо тариф "Простір life:)" дорівнює тарифу "Єдиний", то ставиться -----,

Якщо тариф "Простір life:)" більше тарифу "Єдиний", вигідніший тариф "Єдиний", і навпаки.

Формула має вийти така: =IF(B3=C3;"----";IF(B3>C3;Єдиний;Простір life:) ))

3.4. Використовуючи функцію IF і побудувати таблицю для підрахунку значень функції:

M = x²+3cos x , якщо х>0

6 + x, ,якщо х=0

х³ при всіх інших значеннях.

Якщо х змінюється від -0,9 до +0,85, з кроком 0,2.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

З яких складових складається функція?
Які функції відносяться до логічних?
Яким чином будується умова в логічних функціях?
Яким чином створити вкладену функцію?

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

Тема. Робота з фінансовими функціями.

Мета. Навчитися здійснювати обчислення використовуючи фінансові функції.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання, вирішуючи приведені нижче задачі:

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

Задача № 1

Інвестор вкладає в бізнес 2000 грн. на умовах 5% ставки прибутку щомісяця. Яка вартість інвестицій через 36 місяців? FV (5%;36;-2000)=11 583,63 грн.

Задача № 2

Клієнт відкриває рахунок у банку на умовах 5% ставки прибутку щомісяця, кладе 2000 грн. і планує на початку кожного місяця забирати з рахунку 100 грн. Яка сума буде на рахунку через 36 місяців? FV (5%;36;100-2000;1)=1 520,82 грн.

Задача № 3

Клієнт відкриває рахунок у банку на умовах 5% ставки прибутку щомісяця, кладе 2000 грн. і планує докладати по 100 грн. на початку кожного місяця. Яка сума буде на рахунку через 36 місяців? FV (5%;36;-100;-2000;1)=21 645,45 грн.

Задача № 4

Клієнт відкриває рахунок в банку і кладе 3000 грн. на 5%, докладатиме в кінці кожного місяця 200 грн. Яка сума буде на рахунку через 12 місяців?

Задача № 5

Функція для визначення суми періодичних виплат для погашення боргу – IPMT

Задача № 1

Фірма взяла в банку кредит на суму 2000 грн. терміном на 12 місяців за умови щомісячного погашення позики і місячної ставки кредиту 6%. Визначити величину щомісячних виплат на початку кожного місяця?

Задача № 2

Підприємець бере позику 5000 грн. у банку під 6% терміном на 6 місяців. Визначити щомісячну виплату на кінець кожного місяця?

Задача № 3

Підприємець бере позику 4000 грн. у банку під 6% терміном на 4 місяці. Визначити щомісячну виплату?

Функція обчислення кількості періодів, для погашення суми позики, наданої під деяку процентну ставку – NPER

Задача № 1

Позику 2000 грн. беруть за умови повернення у кінці кожного місяця 200 грн. і процентної ставки 6%. Скільки місяців потрібно для повернення позики?

Задача № 2

Який термін потрібний, щоб повернути банку кредит 3000 грн., взятий під 6% річних за умови повертання в кінці кожного місяця 500 грн.?

Функції для визначення амортизації активу за один період – SLN

Задача № 1

Підприємство закупило пристрій для вимірювання вологи ціною 30 000 грн. Через 10 років вартість – 15 000. Порахувати амортизацію пристрою.

(1 500 грн. в рік)

Задача № 2

Училище закупило обладнання на суму 20 000 грн. Через 5 років залишкова вартість на 20% від початкової суми. Порахувати амортизацію обладнання.

(800 грн. в рік)

Функції для визначення амортизації активу за період з використанням методу підсумовування річних чисел - SYD

Задача № 1

Фірма «Атлант» закупила офісне обладнання на суму $ 6 000. Через 5 років залишкова вартість знизилася до $ 3 000. Розрахувати річну амортизацію обладнання за перший рік і за всі 5 років.

($ 1000 – перший рік, $ 200 – на всі 6 років)

Задача № 2

Підприємство закупило верстат на суму $ 16 000. Через 10 років залишкова вартість знизилася на

40%. Розрахувати річну амортизацію верстату за перший рік і за всі 10 років.

($ 1163,64 – перший рік, $ 116,34 – на всі 10 років)

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Яке призначення фінансових функцій.
Назвати які ви знаєте?
Яка функція визначає майбутню вартість інвестицій? Поясніть її структуру.
Які функції підраховують амортизацію?

Тема 11. Засоби «Пошук рішення» та «Підбір параметрів»

План

Підбір параметру
Пошук рішення

Скільки чашок кави по $1,75 необхідно продати, щоб прибуток складав $30 000?

Що станеться з показниками, якщо знизити ціну на каву, але збільшити розходи на рекламу?

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

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

Коли бажаний результат одиночної формули відомий, але не відомі значення, які потрібно ввести для отримання цього результату, можна скористатися командою Дані/Знаряддя даних/Аналіз «якщо»/Підбір параметрів. При підборі параметра програма змінює значення в деяких комірках до тих пір, поки формула, яка залежить від цієї комірки, не повертає потрібний результат.

Щоб підібрати параметр потрібно:

Виконати команду Дані/Знаряддя даних/Аналіз «якщо»/Підбір параметрів.
З’явиться вікно (рис. 57), в якому потрібно вибрати:
1. В полі „Установити у клітинці” – вказати адресу комірки з формулою,
2. В полі „Змінюючи значення клітинки” – адресу комірки, значення якої потрібно буде змінювати до тих пір, поки формула не поверне результат, вказаний в полі Значення.
Натиснувши ОК, буде виконано підбір параметру. Цей процес і його результат відобразиться у вікні діалогу „Результат підбору параметра” (рішення знайдене, значення, що підбиралося, поточне значення).

ПРИКЛАД

Бізнесмен відкрив кафе і в ньому продають сік по 10,80 грн. за стакан. Необхідно отримати виручку в 20 000 грн. Визначити скільки стаканів соку потрібно продати?

Розв’язання

Введемо таку таблиці.

	А	В
1	Ціна за стакан, грн.	10,80
2	Необхідно продати
3	Прибуток, грн.

В комірку ввести формулу для визначення прибутку: =В1*В2.
Виділити комірку, в якій міститься формула, і виконати команду Дані/Знаряддя даних/Аналіз «якщо»/Підбір параметрів.
У відкрившомуся вікні в полі „Установити у клітинці” – комірка В3, в полі „Значення” – ввести ту величину, яка повинна бути повернена формулою – 20 000. , в полі „ Змінюючи значення клітинки ” - комірку В2.
Клацнути ОК. В результаті появиться повідомлення, а результат занесеться в комірку В2. Прогноз показав скільки потрібно продати стаканів соку.

2. Використання команди Пошук рішення

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

Першим кроком при роботі з командою Пошук рішення являється створення спеціалізованого аркуша. Для цього необхідно створити цільову комірку, в якій вирішується суть задачі (наприклад, формула вирішення спільного доходу, який необхідно максимізувати), а також одну або декілька змінних комірок, значення які можуть змінюватись для досягнення поставленої цілі. Крім того аркуш може включати другі значення і формули, використовуючи значення цільової і змінних комірок. Для успішного пошуку рішення необхідно, щоб кожна зі змінних комірок впливала на цільову комірку (іншими словами, формула у цільовій комірці повинна опиратись в обчисленнях на значення перемінних комірок). У іншому випадку при виконанні команди Пошук рішення з’являється повідомлення про помилку: Результати цільової комірки не співпадають.

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

Потрібно виділити на аркуші цільову комірку (з формулою основаною на шукаємих значеннях змінних комірок).
Потім виконати команду Дані/Аналіз/Пошук рішення. Відкривається діалогове вікно Пошук рішення (рис. 58). Оскільки на етапі 1 була виділена комірка, у текстовому полі Установити цільову комірку вже отримується вірне посилання. Крім того, у групі Дорівнює встановлений правильний перемикач Максимальному значенню, оскільки нас цікавить саме максимальне значення цільової комірки.

Далі перейти до поля Змінюючи комірки. Пересунути діалогове вікно вправо, щоб бачити зміні комірки аркуша, і виділити їх. Якщо зміні комірки являються суміжними, достатньо виділити весь діапазон пересуненням вказівника. У іншому випадку слід клацати по комірках при натисненій кнопці Ctrl - при цьому посилання в поле розділяються комами.

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

посилання.

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

ПРИКЛАД

В кафе продається сік двох різновидів: гранатовий – 11,50 грн., апельсиновий – 9,40 грн.. Нема повної інформації відносно потенціального прибутку і того, на який сік потрібно робити ставку.

Умови продажу дозволяють виготовляти на тиждень 500 стаканів соку (будь-якого виду) обмеження в поставках апельсинів дозволяють виготовити на тиждень не більше 270 стаканів апельсинового соку. Визначити скільки соку і якого потрібно продати, щоб отримати максимальний прибуток.

Розв’язання

1. Побудувати таблицю і ввести формули .

	А	В	С	D	E
1	Гранатовий сік	11,50		Запаси
2	Необхідно продати			Всього стаканів	500
3	Дохід	=В1В2*		Апельсиновий сік	270
4
5	Апельсиновий сік	9,40
6	Необхідно продати
7	Дохід	=В5В6*
8
9	Сумарний дохід	=В3+В7
10
11	Гранатовий сік	=В2
12	Апельсиновий сік	=В6
13	Всього	=В2+В6

2. Поставити курсор в комірку В9 (сумарний дохід) і виконати команду Дані/Аналіз/Пошук рішення.

3. У з’явившомуся вікні вибрати в полі „Змінюючи комірки” – комірки В2, В6. В полі „Обмеження” – ввести обмеження: кнопкою „Додати” ввести умову В13 <= Е2, В12<= E3.

4. Натиснути „Виконати”. У з’явившомуся вікні вибрати перемикач „Зберегти знайдене значення” і клацнути кнопку ОК. Розв’язок оптимізаційної задачі буде знайдено та буде створено новий аркуш Звіт по результатам 1.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Для чого використовують Засіб „Підбір параметра”?
Як відбувається робота по підбору параметра?
Для чого виконується Пошук рішення?
Що таке цільова комірка? Що в ній повинно бути?

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

Тема. Засоби «Пошук рішення» та «Підбір параметрів» .

Мета. Навчитися проводити обчислення у таблицях Excel за допомогою Засобу аналізу «Підбір параметру» і «Пошуку рішення».

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання, вирішуючи задачі.

ЗАДАЧА № 1

Бізнесмен відкрив кафе і в ньому продають чай по 2,80 грн. за чашку. Необхідно отримати виручку в 2 000 грн. Визначити скільки чашок чаю потрібно продати?

ЗАДАЧА № 2

Для виготовлення виробів А, Б, В використовують три види сировини: І, ІІ, ІІІ. У таблиці задано норми витрат сировини на один виріб кожного виду, ціна одного виробу, а також запаси сировини. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний.

	А	Б	В	Запаси сировини
І	18	15	12	360
ІІ	6	4	8	192
ІІІ	5	3	3	180
Ціна	9	10	16

Розв’язок:

а) Ввести дані в таблицю (рис.59);

б) Визначити реальні витрати на виробництво по формулі:

- в комірку F2 ввести =B2*$B$6+C2*$C$6+D2*$D$6;

- в комірку F3 ввести =B3*$B$6+C3*$C$6+D3*$D$6;

- в комірку F4 ввести =B4*$B$6+C4*$C$6+D4*$D$6;

в) Визначити формулу максимального прибутку. В комірку D8 ввести: =B5*B6+C5*C6+D5*D6;

г) Стати курсором на комірку D8 і викликати засіб Пошук рішення:

д) У з”явившомуся вікні ввести: в поле „Змінюючи комірки” - $B$6; $C$6; $D$6;

е) В поле „Обмеження” ввести (за допомогою кнопки Додати) такі умови: $B$6 = ціле; $B$6 >=0; $C$6 = ціле; $C$6 >= 0; $D$6 = ціле; $D$6 >= 0; $F$2 <= $E$2; $F$3 <= $E$3; $F$4 <= $E$4;

ж) Натиснути кнопку „Виконати”;

з) Виведеться результат і звіт про обчислене ( Відповідь: в комірках: В6 - 0 ; С6 - 8 ; D6 - 20).

ЗАДАЧА № 3

Для виробництва деталей Деталь 1 і Деталь 2 використовують 3 види сировини: І, ІІ, ІІІ. У таблиці задано норми витрат сировини на одну деталь кожного виду, ціна одної деталі, а також запаси сировини. Скільки деталей кожного виду потрібно виготовити, щоб прибуток був максимальний?

	Деталь 1	Деталь 2	Запаси сировини
І	13	25	708
ІІ	17	12	714
ІІІ	12	7	675
Ціна	5 грн.	7 грн.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Для чого використовують оптимізаційні задачі?
Які є види оптимізаційних задач?
Як скористатися засобом «Пошук рішення»?
Що таке цільова комірка?
Яким чином скористатися засобом «Підбір параметрів»?
Як додати обмеження?

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

Тема. Засоби «Пошук рішення» та «Підбір параметрів».

Мета. Розглянути вирішення задач на “Пошук рішення» та «Підбір параметрів».Засвоїти прийоми аналізу даних.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

ЗАДАЧА № 1

У зоопарку використовують два види кормів: 100 г першого корму містить 2 г білків, 6 г жирів, 4 г вуглеводів і коштує 5 грн.; 100 г другого корму містить 3 г білків, 2 г жирів, 9 г вуглеводів і коштує 4 грн. Потрібно скласти раціон харчування тварини в зоопарку за умови, що вона має щодня отримувати не менше 60 г білків, 80 г жирів і 150 г вуглеводів, а вартість добової норми їжі повинна бути мінімальною.

Побудова математичної моделі

Позначення: х1, х2 – кількість кормів І і ІІ виду в грамах.

Вартість добової норми їжі становить + (грн.) – це і є цільова функція, яку потрібно мінімізувати.
За умовою задачі відомо, що 100 г корму 1 виду містить 2г білків, а в х1 грамах цього корму міститься

Аналогічно, в х2 грамах корму 2 виду міститься

тварина в зоопарку має щодня разом з їжею отримувати не менше 60 г білків, то обмеження для білків, матиме такий вигляд: +.

Обмеження для жирів 80.

Обмеження для вуглеводів х1

Математична модель задачі має такий вигляд:

1. +;

2. +. 3.80. 4.

Розв’язання задачі

Задача лінійного програмування розв’язується на основі засобу Пошук рішення.

1. Значення Х1 і Х2 будуть у клітинках В7 і С7 (рис. 60).

2. Заповнити В3:В5, С3:С5, Е3:Е5 за умовою задачі.

3. У клітинку В9 ввести формулу цільової функції =(В7*5+С7*4)/100.

4. У D3 ввести ліву частину обмеження, а саме формулу:

=($B$7*B3+$C$7*C3)/100 – протягнути D4:D5.

5. Виконати команду: Дані/Аналіз/Пошук рішення – заповнити поля - обмеження через Додати

6. Клацнути – Виконати , у вікні Результати пошуку рішення вибрати зі списку Тип звіту - Результати –ОК. Розв’язок оптимізаційної задачі буде знайдено та буде створено новий аркуш Звіт по результатам 1.

ЗАДАЧА № 2

. На деякому підприємстві щомісяця випускається 1000 одиниць продукції. Собівартість одиниці продукції становить 10 грн., крім того фірма несе щомісячні витрати у розмірі 2000 грн, не пов’язані безпосередньо з виробництвом, та сплачує податок у розмірі 15% від доходу.

Потрібно визначити, якою має бути ціна одиниці продукції, щоб підприємство отримувало щомісячний прибуток у сумі 5000 грн. (припускаємо, що вся продукція продається).

Розв’язання задач

1. Побудувати таблицю за таким зразком (рис.61).

2. Для обчислення доходу від реалізації продукції Дохід у клітинку В5 введіть формулу =В4*В2.

3. Для обчислення суми податку Податок введіть у клітинку В6 формулу =В5*15%.

4. Для обчислення загальних витрат Витрати у клітинку В8 ввести формулу: =В2*В3+В6+В7.

5. У клітинку В9 ввести формулу для розрахунку прибутку: =В5-В8.

6. Виконати команду Дані/Знаряддя даних Аналіз «якщо»/Підбір параметра. Заповніть поля у вікні Підбір параметра враховуючи, що підбирається таке значення ціни продукції (клітинка В4), за якого прибуток становитиме 5000 грн.

7. Клацнути кнопку ОК, і можна дізнатися шукану ціну одиниці продукції.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Які є види оптимізаційних задач?
Як користуватися засобом «Підбір параметрів»?
Як скористатися засобом «Пошук рішення»?
Що таке цільова комірка?
Як додати обмеження?
Яким чином виводяться результати при вирішенні оптимізаційних задач?

Тема 12. Робота зі списками в Excel 2007

План

Створення списків
Проміжні підсумки
Диспетчер сценаріїв

1. Створення списків

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

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

Список складається із записів, причому кожний запис відповідає визначеному об’єкту, а властивості об’єкту відображаються в полях запису. У вигляді списку побудований, наприклад, телефонний довідник. В ньому записом є кожний окремий рядочок, який відповідає конкретному об’єкту – абоненту, а полями є стовпці ПІБ, адреса, телефон.

При роботі в Excel записи формуються з рядків таблиці, а записи зі стовпців.

Форма для списків

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

Форма являє собою діалогове вікно для створення і редагування записів. Щоб створити список у формі потрібно:

Вказати в списку комірку, починаючи з якої будуть додаватися в список нові записи.
Виконати команду Панель швидкого доступу/Форма і у відкрившомуся вікні клацнути кнопку Створити (рис. 62).
В пусті поля форми ввести дані нового запису. Відмінити додавання запису можна клацанням по кнопці Відновити. У даному вікні є такі кнопки:
1. Кнопки Знайти назад і Знайти Далі дозволяють переміщуватися по рядкам списку.
2. Видалити – видаляє поточний рядок.
3. Умови – визначає критерії пошуку рядків в cписку
Заповнивши поля натиснути кнопку Створити Після цього запис буде введено в список, а поля будуть очищені для набору слідую чого запису. По закінченню створення списку натиснути кнопку Закрити.

Підсумки

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

Для підведення підсумків потрібно:

1. Організувати список потрібно так, щоб записи кожної групи слідували у ньому підряд. Найпростішим способом

є сортування по тому полю, на якому основані групи. Наприклад, можна відсортувати список за іменем службовця, назвою регіону або складу.

2. Виконати команду Проміжні підсумки із категорії Структура вкладки Дані. Відкриється діалогове вікно Проміжні підсумки (рис. 63).

3. Далі потрібно вибрати із розкриваючого списку При кожній зміні в стовпчик, по якому буде виконано групування рядків. Це повинен бути той же стовпчик, по якому сортувався список.

4. Вибрати із списку Використовувати функцію статистичну функцію яка буде використовуватися для обчислення проміжного підсумку. Частіше за все застосовується функція СУМА але є і інші (рис. 64).

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

6.Клацнути по кнопці ОК, щоб включити проміжні підсумки у список.

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

Режим структури, у якому опиняється список після виконання команди Проміжні підсумки, нагадує режим структури у Word і дозволяє продивлятись

Кнопки, розміщені у верхній частині лівого поля, визначають кількість виведених рівнів даних. Кнопки зі знаками + і - призначені для згортання і розгортання окремих груп. (рис. 64).

Рис. 64. Дані після підведення Проміжних підсумків

Диспетчер сценаріїв

Сценарій являє собою сукупність значень, які зберігаються в Microsoft Office Excel і можуть автоматично підставлятися на аркуш. Можна створювати та зберігати як сценарії різні групи значень, а потім переходити до будь-якого з цих нових сценаріїв, щоб переглянути різні результати.

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

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

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

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

На вкладці Дані у групі Знаряддя даних клацнути категорію Аналіз «якщо», а потім Диспетчер сценарію.

Рис. 65. Вікно Зміна сценарію

Натиснути кнопку Додати.
У вікні (рис. 65) у полі Назва сценарію ввести назву сценарію.
У полі Змінювані клітинки ввести посилання на комірки, які має бути використано у сценарії. Наприклад, якщо потрібно дослідити, як змінення значень клітинок B1 і B2 впливає на результат формули, введіть B1;B2.
У розділі Захист вибрати потрібні параметри. Ці параметри застосовуються лише до захищених аркушів.
Установити прапорець Заборонити зміни, щоб заборонити редагування сценарію, коли аркуш захищено.
Установити прапорець Приховано, щоб заборонити відображення сценарію, коли аркуш захищено.
Натиснути кнопку ОК.
У діалоговому вікні Значення клітинок сценарію ввести потрібні значення змінюваних комірок для цього сценарію.
Щоб створити сценарій, натиснути кнопку ОК.
Якщо потрібно створити додаткові сценарії, повторити кроки 2-8. Після завершення створення сценаріїв натиснути кнопку ОК, відтак натиснути кнопку Закрити в діалоговому вікні Диспетчер сценаріїв.

Відображення сценарію

У результаті відображення сценарію відбувається перехід до набору значень, збережених як частина цього сценарію. Значення сценарію відображаються у клітинках, які змінюються від сценарію до сценарію, на додаток до клітинок результату. У нашому прикладі, якщо відобразити сценарій «Найкращий випадок», у клітинці B1 відобразиться 150000, у клітинці B2 — 26000, а у клітинці B3 — 124000.

На вкладці Дані у групі Знаряддя даних клацнути елемент Аналіз «якщо», а потім Диспетчер сценаріїв.
Клацнути ім’я сценарію, який потрібно відобразити.
Вибрати команду Показати.

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

Створення зведеного звіту сценарію

На вкладці Дані у групі Знаряддя даних клацнути елемент Аналіз «якщо», а потім Диспетчер сценаріїв.
Натиснути кнопку Звіт.
Вибрати варіант структура або зведена таблиця.
У полі Клітинки результату ввести посилання на клітинки, значення яких були змінені сценаріями. Послідовні посилання розділити комами.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

1. Що таке список в електронній таблиці?

2. З яких елементів складається список?

3.Як створити список за допомогою форми?

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

5. Як виконується пошук даних на всьому аркуші?

6. Як відбувається сортування даних?

7.Шо таке Диспетчер сценаріїв?

8. Як створити Сценарій?

9.Як вивести звіт сценарію?

Практична робота № 18-19

Тема. Робота зі списками.

Мета. Розглянути процес створення списків, роботу зі списками: додавання записів, видалення записів, зміна записів.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання в такій послідовності.

Ввести за допомогою форми таку таблицю.

Фірма	Код товару	Вид товару	Кінцева дата споживання	Закупівельна ціна, грн.	Закуплено, шт.	Залишок, шт.
Орфей	125	кондит.	25.03.12	2	45	2
Орфей	456	напої	25.05.12	23	88	11
Орфей	456	напої	25.06.12	23	77	10
Орфей	456	напої	25.01.12	23	33	7
Орфей	456	напої	25.03.12	23	69	4
Орфей	789	кондит.	05.11.12	4	45	12
Орфей	789	кондит.	25.01.12	4	47	10
Антей	123	напої	25.11.12	12	123	100
Антей	123	напої	25.01.12	12	47	30
Антей	123	напої	01.01.12	12	45	14
Антей	123	напої	05.01.12	12	25	2
Антей	456	напої	05.03.12	25	52	15
Антей	456	напої	05.02.12	25	78	12
Антей	456	напої	05.04.12	25	14	13
Марс	125	кондит.	25.03.12	3	44	16
Марс	456	напої	05.01.12	23	88	18
Марс	456	напої	25.01.12	23	66	17
Марс	789	кондит.	05.03.12	4	77	22
Марс	789	кондит.	25.03.12	4	12	10

Виконати з даною таблицею такі дії:

Порахувати закупівельну ціну усіх товарів, і залишок усіх товарів на складі.
Видалити останній запис.
Додати запис « Фірма Маршал», код товару 768, кондитерський виріб, дата споживання – 1 квітня 2012 р. , закупівельна ціна – 32,45 грн., закуплено 570 шт., залишок – 45 шт.
Додати ще 2 записи на власний розсуд.
Організувати пошук всіх даних по фірмі Орфей;вид товару – напої.
Організувати пошук даних по фірмі Марс, вид товару кондитерські вироби.
Організувати пошук фірм, назва яких починається на літеру О.
Організувати пошук товарів, в яких закупівельна ціна – 23 грн., більше 14 грн., менше 23 грн.
Організувати пошук товарів, яких залишилось на складі більше 12 шт.; менше 17 шт.
Здійснити сортування по зростанню для таблиці по стовпцю «Назва магазину».

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

Тема 13. Способи фільтрації даних

План

1. Робота з автофільтром

2. Розширений фільтр

3. Сортування даних

1. Робота з автофільтром

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

В Excel передбачено 2 режими фільтрації: Автофільтр і Розширений фільтр.

Щоб скористатися Автофільтром потрібно:

клацнути по будь-якій комірці списку, який підлягає фільтрації.
Виконати команду Дані/Сортування і фільтр/Фільтр. Справа від назви кожного стовпця з’явиться кнопка з розкриваючим списком.
Список кнопки відкриває позиції Виділити все, елементи стовпця. (рис.66).
Одразу після цього на екрані залишаться лише записи, які пройшли фільтрацію, а інші записи будуть приховані. Заголовки відфільтрованих рядків пофарбовані в синій колір.
Для повної відміни фільтрації потрібно виконати команду Дані/Сортування і фільтр/Фільтр і забрати прапорець з команди Фільтр.

Користувацький автофільтр

В списку автофільтра є позиція Фільтри чисел/Користувацький фільтр, яка дозволяє задати точні критерії. Вибравши цю позицію з’явиться діалогове вікно Користувацький авто фільтр (рис.67), в якому потрібно задати умову, наприклад: менше або дорівнює 5. Якщо скористатися ще перемикачами І або АБО то можна розширити критерії пошуку.

Для текстових полів можливі також критерії вибору: Починається з заданого тексту, Закінчується (не закінчується) заданим текстом, Містить (не містить) заданий текст.

2. Розширений фільтр

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

спочатку створити таблицю-критерій. Для цього в рядок під вихідною таблицею, пропустивши декілька рядків, скопіювати назву всіх стовпців. Рядком нижче під назвами ввести критерії відбору ( текстові символи, числа, умови.

Активізувати будь-яку комірку вихідної таблиці і виконавши команду Дані/Сортування і фільтр/ Додатково/Розширений фільтр з’явиться вікно (рис.68), в якому задати слідуючи параметри.

В полі Обробка вказати перемикач Скопіювати результат до іншого розташування.
В полі Вихідний діапазон ввести діапазон фільтруємої таблиці (програма зазвичай вже сама його вірно встановлює). В поле Діапазон умов задати діапазон таблиці-критерія.
В полі Діапазон для результату ввести місце розташування фільтра.
Натиснути кнопку ОК.

Відмінити дію розширеного фільтру можна командою Дані/Сортування і фільтр/Фільтр і забрати прапорець з команди Фільтр.

3. Сортування даних

Упорядкування даних виконується шляхом операцій сортування.

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

Дані зазвичай сортуються за алфавітом, за числовим значенням або по даті.

Для сортування потрібно виділити діапазон даних і виконати команду Дані/Сортування й фільтр/Сортувати.

У з’явившомуся вікні (рис. 69) вибрати, що сортувати і за яким принципом: по зростанню, по зменшенню. Сортувати можна не більше як по 3 полям.

Натиснувши кнопку Параметри відкриється вікно, в якому потрібно вибрати перемикач „Рядки діапазону” або „Стовпці діапазону”. Вибрати порядок сортування «Від найбільшого до найменшого» або навпаки. Після вибору параметрів натиснути кнопку ОК і дані стануть посортованими.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

1. Що таке фільтрація даних в списках?

2. Які є види фільтрів?

3.Як скористатися автофільтром?

4. Як відкрити користувацький автофільтр?

5. Як працювати з розширеним фільтром?

6. Як відмінити фільтр?

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

Тема. Фільтрація даних.

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

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання в такій послідовності.

3.1. Ввести в комірку А1 поточну дату і ввести таку таблицю , починаючи з комірки А2, за допомогою форми:

Список працівників

Прізвище	Посада	Оклад	Дата прийняття	Стаж роботи
Андрєєва	продавець	600	12.02.2011
Брилко	секретар	1500	05.01.2000
Буран	охоронець	980	05.12.2001
Величковська	охоронець	456	23.12.2002
Гладун	продавець	600	04.01.1975
Гнаток	продавець	600	28.07.1988
Григоренко	продавець	500	01.01.1996
Каденко	двірник	250	01.09.2005
Красій	продавець	500	08.09.1981
Матяш	продавець	450	02.01.2006
Міхалевська	продавець	750	11.03.2001
Мусієнко	продавець	600	08.05.2002
Онишко	продавець	600	12.12.2012
Остапюк	продавець	500	25.09.1999
Попович	продавець	600	10.09.2006
Романик	сантехнік	750	05.05.2005
Руда	продавець	456	02.01.2012
Савчук	продавець	600	27.12.1998
Сова	продавець	890	23.12.1980
Ящук	електрик	900	01.04.1999

3.2. Порахувати стаж роботи працівників в повних роках. (формула =DEYS360(початкова дата; поточна дата)/360.

3.3. Посортувати записи по зростанню.

3.4. Використовуючи автофільтр, вивести на окремому аркуші усіх людей, чия професія – продавець.

3.5. Використовуючи автофільтр, вивести на окремому аркуші усіх людей, чиї професія охоронець і оклад більше або дорівнює 980 грн.

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

3.7. Використовуючи розширений фільтр, вивести на окремому аркуші всіх людей, в яких посада – продавець і оклад не менше 560 грн.

3.8. Використовуючи розширений фільтр вивести на окремому аркуші всю інформацію про людей з прізвищем: Мусієнко і Савчук.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

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

Тема. Робота з різними видами фільтрації.

Мета. Розглянути процес з фільтрами: автофільтром і Розширеним фільтром. Ознайомитися зі способами введення умов для фільтрації в таблиці.

Оснащення: ПК, роздаткові картки.

Хід роботи

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

Фірма	Код товару	Вид товару	Кінцева дата споживання	Закупівельна ціна, грн.	Закуплено, шт.	Залишок, шт.
Орфей	125	кондит.	25.03.12	2	45	2
Орфей	456	напої	25.05.12	23	88	11
Орфей	456	напої	25.06.12	23	77	10
Орфей	456	напої	25.05.12	23	33	7
Орфей	456	напої	25.05.12	23	69	4
Орфей	789	кондит.	05.11.12	4	45	12
Орфей	789	кондит.	25.01.12	4	47	10
Антей	123	напої	25.05.12	12	123	100
Антей	123	напої	25.01.12	12	47	30
Антей	123	напої	01.01.12	12	45	14
Антей	123	напої	05.01.12	12	25	2
Антей	456	напої	05.03.12	25	52	15
Антей	456	напої	05.02.12	25	78	12
Антей	456	напої	05.04.12	25	14	13
Марс	125	кондит.	25.03.12	3	44	16
Марс	456	напої	05.01.12	23	88	18
Марс	456	напої	25.01.12	23	66	17
Марс	789	кондит.	05.03.12	4	77	22
Марс	789	кондит.	25.03.12	4	12	10

Виконати з даною таблицею такі дії:

Здійснити сортування по зростанню для таблиці по стовпцю «Назва магазину».
Використовуючи автофільтр, вивести на окремому аркуші усі фірми, які торгують напоями..
Використовуючи автофільтр, вивести на окремому аркуші всі товари, ціна яких більше 12 грн.
Через користувацький автофільтр дізнатися назви фірм, які закупили товари 25.05.12 і 25.01.12.
Використовуючи розширений фільтр, вивести на окремому аркуші всі товари, які закупилися 25.05.12 р. і більше 20 шт.
Використовуючи розширений фільтр, вивести на окремому аркуші всі товари, яких залишилося більше 15 штук.
Використовуючи розширений фільтр вивести на окремому аркуші всю інформацію про фірми Марс і Антей.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Які є види фільтрації?
Яким чином примінити автофільтр?
Як примінити розширений фільтр?
Яким чином створити фільтр по декільком показникам?
Як відмінити фільтр?

Тема 14. Створення та робота зі зведеними таблицями

План

Створення зведеної таблиці
Робота із зведеною таблицею
Створення зведеної діаграми

1. Створення звіту зведеної таблиці

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

Вибрати клітинку в діапазоні клітинок або розмістити курсор у таблиці Excel. Пееконатися, що діапазон клітинок містить заголовки стовпців.
На вкладці Вставлення у групі Таблиці вибрати елемент Зведена таблиця (рис.70), а потім — пункт Зведена таблиця.

З’явиться діалогове вікно Створення зведеної таблиці (рис.71).

Вибрати джерело даних.

Якщо вибрано клітинку в діапазоні клітинок або курсор містився в таблиці до початку роботи майстра, діапазон клітинок або посилань на імена таблиць відображається в полі Таблиця/Діапазон. Натиснути ОК.

Рис. 71. Вікно створення зведеної таблиці

З’явиться новий аркуш і область задач справа (рис. 72), в якій напроти кожного поля в таблиці встановлений прапорець. Вибрати ті поля які потрібні згідно з умовою задачі і перетягнути вниз області задач у відповідні області:
- Позначки стовпців;
- Позначки рядків;
- Значення.
Поля автоматично з’являться в зведеній таблиці (в робочій області).

Рис. 72. Область створення зведеної таблиці

Клацнути по полям в списку Значення і вибрати в меню команду «Параметри значення поля» і в полі «Операція» вибрати функцію обчислення – Сума, Кількість, Середнє, Мінімум, Максимум і т.д.
Закрити область задач, таблиця готова.

2. Робота зі зведеною таблицею

Зміна структури звіту

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

Для цього потрібно:

Клацнути звіт зведеної таблиці.
На вкладці Конструктор у групі Макет натиснути кнопку Структура звіту й виконати одну з таких опцій:

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

Поля

Виділити поле рядка, відтак на вкладці Параметри у групі Активне поле натиснути кнопку Параметри поля.

Відображається діалогове вікно Параметри значення поля (рис.73) з такими вкладинками:

операція – вибирається який потрібен тип операції. (Сума, Кількість, Середнє, Мінімум, Максимум і т.д.
відображати значення як – форма відображення обчислення.

Відображення проміжних підсумків над або під відповідними рядками

Виділити поле рядка й на вкладці Параметри у групі Активне поле натиснути кнопку Параметри поля. Відображається діалогове вікно Параметри поля.
Якщо проміжні підсумки не увімкнуто (вибрано параметр Немає), перейти на вкладку Проміжні підсумки та фільтри і в розділі Проміжні підсумки та фільтри вибрати команду Автоматично або Користувацький.
Перейти на вкладку Макет і друк, відтак у розділі Макет вибрати команду Відображати позначки елементів у формі макета.
Виконати одну з таких дій:

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

Зміна порядку елементів рядка або стовпця

Клацнути правою кнопкою миші підпис рядка або стовпця чи елемент підпису, вказати у контекстному меню команду Перемістити, після чого скористатися однією з команд меню Приклад переміщення елемента у звіті зведеної таблиці Перемістити щоб перемістити елемент (рис.74).

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

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

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

Зміна стилю формату зведеної таблиці

Можна легко змінити стиль зведеної таблиці, використовуючи колекцію стилів. Microsoft Office Excel підтримує численні стандартні стилі таблиць, за допомогою яких можна швидко форматувати зведені таблиці.

Щоб змінити стиль потрібно:

Клацнути звіт зведеної таблиці.
На вкладці Конструктор у групі Стилі зведеної таблиці вибрати видимий стиль і двічі по ньому клацнути (рис. 75).

Рис. 75. Стилі зведеної таблиці

Зміна формату чисел у полі

У звіті зведеної таблиці виділити поле, в якому потрібно змінити формат чисел.
На вкладці Параметри в групі Активне поле натиснути кнопку Параметри поля. Відображається діалогове вікно Параметри поля для підписів і фільтрів звітів, або вікно Параметри значення поля — для значень.
Натиснути кнопку Формат числа у нижній частині діалогового вікна. Відображається діалогове вікно Формат клітинок.

У списку Числові формати виберіть потрібну категорію формату (числовий, грошовий, відсотковий).
Вибрати потрібні параметри формату та двічі клацнути кнопку ОК.

Збереження або скасування форматування

Клацнути звіт зведеної таблиці.
На вкладці Параметри у групі Зведена таблиця натиснути кнопку Параметри.

Відображається діалогове вікно Параметри зведеної таблиці.

Перейти на вкладку Макет і формат і в розділі Формат виконати одну з таких дій:

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

3. Створення зведеної діаграми

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

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

1. Виділити потрібний нам діапазон даних (або встановити курсор на потрібну нам таблицю - тоді Excel автоматично підставить всю таблицю в діапазон даних);

2. На вкладці Вставлення у групі Таблиці вибрати розділ Зведена таблиця, а потім пункт Зведена діаграма.

3. У вікні Створити зведену таблицю і зведену діаграму, задати діапазон або джерело даних, та місце розміщення таблиці і діаграми, натиснути ОК.

Excel створить нову зведену таблицю і зведену діаграму (рис.76). Вам залишається тільки налаштувати поля та умови зведеної таблиці за допомогою вікна Список полів зведеної таблиці (як це зробити). Всі зміни будуть відображатися і на діаграмі.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

1. Для чого використовується зведена таблиця?

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

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

4. Як відбувається зміна формату числа у звіті?

5. Які є структури звіту?

6. Яким чином використати іншу структуру?

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

Тема. Зведені таблиці.

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

Оснащення. ПК, роз даткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання в такій послідовності.

Задача. В магазин протягом місяця доставляли товари (5 найменувань) і їх реалізовували. Створити систему обліку надходження і продажу товарів та залишку товарів у магазині.

Розв’язання.

В книзі аркуш 1 перейменувати в “Надходження товару (детально)”, аркуш 2 – в “Продаж товару (детально)”, аркуш 3 – в “Надходження товару”, аркуш 4 – в “Продаж товару”.
Заповнити дані аркушу 1 “Надходження товару (детально)” за такою схемою:

№ з/п	Найменування товару	Кількість	Дата надходження	Ціна за одиницю	Ціна товару
1	Мишка 2 кн.	25	05.03.12	16
2	Мишка 2 кн. PS/2	30	06.03.12	18
3	Килимок	50	09.03.12	5
4	Дискета 3,5	100	12.03.12	1
5	Дискета 3,5 HD	50	13.03.12	2
6	Мишка 2 кн.	10	14.03.12	16
7	Мишка 2 кн. PS/2	20	15.03.12	18
8	Килимок	10	16.03.12	5

3. Провести обчислення у стовпці Е - Ціна товару.

4. Заповнити дані аркушу 2 “Продаж товару (детально)” за такою схемою:

№ з/п	Найменування товару	Кількість	Дата продажу	Ціна за одиницю	Ціна товару
1	Мишка 2 кн.	5	07.03.12	17
2	Мишка 2 кн. PS/2	6	09.03.12	19
3	Мишка 2 кн.	10	11.03.12	17
4	Дискета 3,5	30	10.03.12	1,5
5	Дискета 3,5 HD	20	13.03.12	2,5
6	Килимок	5	14.03.12	6
7	Килимок	10	15.03.12	6
8	Дискета 3,5	5	16.03.12	1,5

5. Провести обчислення “Ціна товару”.

6. На аркуші “Надходження товару” скласти зведену таблицю.

Створити зведену таблицю “Всього товарів у магазині”.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

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

3. Що собою являє макет зведеної таблиці?

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

5. Як керувати відображенням даних в зведеній таблиці.

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

Тема. Зведені таблиці.

Мета. Навчитися створювати зведені таблиці та редагувати їх в MS Excel 2007.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання в такій послідовності.
1. Створити таблицю за зразком.

3.2. Додати до таблиці ще 7 записів на власний розсуд.

3.3. Створити зведену таблицю, в якій вивести який маршрут, вартість і фірма, в якій брали путівку.

3.4. Додати до таблиці дату відправлення.

3.5. Створити зведену діаграму вартості путівок.

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

3.7. Створити зведену таблицю, в якій вивести Маршрут, тривалість і вартість.

3.8. Створити зведену діаграму, в якій вивести Маршрут і тривалість.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

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

3. Що собою являє макет зведеної таблиці?

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

5. Як керувати відображенням даних в зведеній таблиці.

Тема 15. Створення бази даних в Excel 2007

План

1. Поняття бази даних

2. Створення бази даних

3. Робота з записами в базі даних

4. Консолідація даних

1. Поняття бази даних

База даних – це сукупність даних, яка організована у спеціальний спосіб.

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

Рядки в базі даних називаються записами, а стовпці – полями.

У Excel база даних будується за аналогією з рядками і стовпцями, причому стовпець таблиці є полем, а кожен рядок – окремим записом. Інформація з кожного поля заноситься в окрему комірку таблиці. Наприклад, у таблиці, представленої на мал. 1, імена занесені в стовпець із заголовком Ім'я, прізвища занесені в стовпець із заголовком Прізвище і т.д. Для того щоб приступити до створення бази даних, помістіть курсор в комірку верхньої частини таблиці і введіть у рядку назви полів.

2. Створення бази даних

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

У порожній рядок таблиці ввести назви полів.

У кожну комірку у рядку, що знаходиться безпосередньо під рядком з назвами полів, ввести фактичні дані по кожному полю. Не потрібно залишати порожніх рядків між рядком з назвами полів і наступними даними, оскільки в Excel можуть виникнути проблеми з розпізнаванням початку бази даних.

Працювати з базою даних буде набагато простіше, якщо розмістити її на окремому аркуші.

При роботі з базами даних у Excel придасться інформація про назви полів. Рядок з назвами полів повинний передувати списку. Залишати порожній рядок між рядком з назвами полів і фактичними даними неприпустимо. Назва кожного поля повинна бути унікальною. У верхній частині списку може бути кілька рядків із введеною в них інформацією, однак тільки один рядок буде сприйматися як такий, що містить назви полів. Назва поля не повинне перевищувати 255 символів (для зручності краще вибирати короткі назви).

3. Робота з записами в базі даних

Записи в базу даних додаються і редагуються шляхом введення даних безпосередньо в комірки, а знищуються з бази даних шляхом їхнього виділення і виконання команди Форма з панелі швидкого доступу. Однак більшість користувачів воліють знаходити і редагувати дані за допомогою форми, що надає Excel для введення і перегляду даних.

Для того щоб переглянути дані на екрані за допомогою форми, необхідно помістити курсор у будь-яку комірку бази даних і виконати команду Форма з панелі швидкого доступу. На екрані з'явиться форма з полями бази даних (рис.77).

Додавання нових записів

Для того щоб додати новий запис, потрібно клацнути у формі на кнопці Додати. На екрані з'явиться порожня форма і можна буде вводити дані в кожне поле. Ці дії потрібно повторити для кожного запису, що необхідно додати в базу даних.

Редагування записів

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

Видалення записів

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

Пошук даних за критерієм

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

За допомогою цього критерію при роботі з базою даних, у яку введені значення витрат, можна знайти всі записи про витрати, що перевищують 5000 грн. Для цього потрібно ввести >5000. Вказуючи критерій у формі даних, можна використовувати оператори порівняння (ті самі оператори, що використовуються у формулах, введених в комірки робочої таблиці). У табл. 1 перераховані оператори і їхнє значення.

Консолідація даних

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

У новій книзі, яка міститиме консолідовані дані встановити курсор в комірку А1 і на вкладці Дані в групі Знаряддя даних вибрати команду Консолідація.

У діалоговому вікні, що відкрилося, Консолідація зі списку Функція вибрати вид обчислень, які повинні проводитися з даними (рис. 78).

Рис. 78. Вікно створення консолідації даних

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

У полі Посилання з'явиться посилання на файл і діапазон комірок. Натиснути кнопку Додати, для додавання цього діапазону комірок в Список діапазонів.

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

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

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

Після вказівки усіх даних натиснути кнопку ОК.

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

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Що таке база даних?
Назвати складові бази даних?
Як створити базу даних?
Як видалити запис в базі даних?
Як змінити запис?
Як відбувається пошук даних в базі даних?
Що таке консолідація даних?
Як створити консолідацію даних?

Практична робота № 24-25

Тема. Створення бази даних в ЕТ.

Мета. Навчитися створювати та працювати з базами даних MS Excel 2007.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання в такій послідовності.

3.1. Створення довідників

Першу сторінку переіменувати в Довідники.

Для встановлення зв’язку з даними інших аркушів цієї книги, для наступних діапазонів комірок привласнити такі імена.

В3-В7 (перший діапазон) – Професія;

D3-D8 – Розряд;
D3-E8 – Тариф._коеф.

Для привласнення імен виконуємо команду: Меню Формули/Призначення імені/Визначити ім’я, попередньо виділивши потрібний діапазон комірок (В3-В7). У вікні, що з’явилося (рис.80), ввести в поле Ім’я з клавіатури Професія, у стовпчику Посилання перевірити діапазон, потім натиснути ОK. Наступним двом діапазонам привласнюємо імена аналогічно. Ім’я повинно вводитися одним словом (напр. Тариф._коеф.).

3.2. Скласти довідники професій і тарифних коефіцієнтів.

Згідно завдання передбачено 5 професій: бетонувальник, муляр, монтажник, тесляр, маляр. Розрядам 4, 5, 6, 7, 8, 9 відповідають тарифні коефіцієнти 1,39; 1,54; 1,70; 1,87; 2,06; 2,26 (рис. 81).

Рис. 81. Створення довідників

3.3. Облік робітників

Наступну сторінку перейменувати в Облік робітників.

Створити таблицю, яка містить наступні стовпці: № з/п., прізвище, ім’я, по-батькові, стать, дата народження, вік, професія, розряд, членство у профспілці, дата прийому на роботу, стаж роботи (визначається в повних роках, місяцях, днях). Кількість записів в таблиці відповідає чисельності бригади, що складає 21 чол.

З клавіатури ввести назви таблиці і назви робітників. Заповнити записами поля: № з/п., прізвище, ім’я, по-батькові, дата народження, дата прийому на роботу. Решту полів обробити за допомогою формул MS Excel.

Щоб розрахувати записи в полі Стать натиснути ліву клавішу миші у комірці E4. Так як у чоловіків по батькові закінчується на літеру “ч”, формула для автоматичного визначення статі буде виглядати так: =IF(RIGHT (D4,1)="Ч","чол.","жін.").

За допомогою Автозаповнення заповнити інші комірки у стовпчику Стать.

У полі Вік застосувати функцію =DAYS360 (дата початку;дата кінця;форма представлення). Форма представлення – роки (Y). Так як вік – це різниця між сьогоднішньою датою і датою народження, то його можна визначити через наступну формулу:

= DAYS360 (F4,TODAY(),"Y").

Для розрахунку записів у полях Професія, Розряд, Членство у профспілці виділити діапазон комірок (H4:H24). Натиснути – Дані/Знаряддя даних/Перевірка даних.

Після цього з’являється діалогове вікно (рис. 82). У Стовпчику Тип даних обрати Список, а у стовпці Джерело з клавіатури ввести =Професія і натиснути OK.

З правого боку з’являється кнопка, що дозволяє відкривати список і вибирати відповідні записи. Натискаючи на кнопку зі стрілкою , з’явиться список запропонованих професій (рис. 83). У полі Розряд комірки заповнити аналогічно, лише у стовпці Джерело ввести =Розряд. У полі Членство у профспілці комірки заповнюються також аналогічно, але у стовпчику Джерело з клавіатури ввести так або ні, не ставлячи знака “=”, так як немає відповідного діапазону з присвоєним ім’ям.

Для розрахунку записів у полі Стаж роботи використати функцію = DAYS360 (дата початку; дата кінця; форма представлення). Форма представлення – в повних роках, місяцях, днях (Y, YM, MD відповідно). Так як стаж роботи – це різниця між сьогоднішньою датою і датою прийому на роботу, то формула для визначення стажу роботи у повних роках буде виглядати так:

= DAYS360 (K4, TODAY (),"Y"),

а для двох сусідніх полів Y змінюється на YM, MD відповідно:

Рис. 83. Список професій

= DAYS360 (K4, TODAY (),"YM"); = DAYS360 (K4, TODAY (),"MD").

3.4. Розподіл відрядного заробітку

Потрібно розробити таблицю (рис. 84) розподілу суми відрядного заробітку – 22000 грн. між робітниками бригади та утримань з полями, які дані у завданні.

Щоб створити записи в полі Прізвище І.П. треба з’єднати разом прізвище і ініціали. Ініціали – це літери ім’я і по батькові, які є першими зліва, тому використовуємо таку формулу:

=CONCATENATE ('Облік робітників'!B4," ",LEFT('Облік робітників' !C4,1), "." , LEFT ('Облік робітників'!D4,1),"."). Всі записи у полі Кількість відпрацьованого часу вводяться з клавіатури, але щоб забезпечити введення даних між 152 і 184 годинами, натиснути Дані/Знаряддя даних/Перевірка даних.

Після цього з’являється діалогове вікно (рис.85). У графі Тип даних обрати Дійсне, у графі Мінімум з клавіатури ввести =152, а у графі Максимум – =184 і натиснути OK.

Тарифний коефіцієнт визначити за функцією VLOOKUP. Знаходячись у комірці D4, викликати вищезгадану функцію. В рядку Шукане значення перейти на попередній аркуш Облік робітників, відповідна комірка I4 (що відповідає розряду); Таблиця – Тариф._коеф. (з Довідників);

Номер стовпця – 2; Інтервальний перегляд – Істина (рис. 86.)

Формула має такий вигляд:

=VLOOKUP ('Облік робітників'!I4,Тариф_коеф.,2, ІСТИНА).

Відпрацьований час приведений до I-го розряду обчислити за формулою:

=D4*C4 (для комірки Е4).

Відрядний заробіток на 1 люд.-год. І-розряду обчислити за формулою: =$C$2/$E$25 (комірка F2).

Стовпчик «Розподілений відрядний заробіток «обчислити за формулою:

=E4*$F$2 (комірка F4).

При відсутності інших нарахувань відрядний заробіток співпадає з величиною суми «всього нараховано».

Для визначення прибуткового податку, відрахування до пенсійного фонду, відрахування на соцстрахування, збору на випадок безробіття, які залежать від розмірів процентів (13%, 2%, 1%, 0,5% відповідно), застосувати функцію =TRUNC(число;число_разрядов). Так як число відповідає всьому нарахованому (в даному випадку відрядному заробітку), а число_разрядів = 0, формула виглядає так: = TRUNC (F4;0)*відсоток (0,13; 0,02; 0,01; 0,005) відповідно для чотирьох полів, які розглядаються). Наприклад, для комірки G4 (Прибутковий податок):

=TRUNC (F4,0))*0,13.

Щоб вирахувати значення стопчика «Профспілкові внески» все нараховане помножити на коефіцієнт 0,01 або 0, якщо людина входить або не входить до профспілки відповідно. Формула така:

=IF('Облік робітників'!J4="так",F4*0,01).

Всього утримано вираховується за формулою:

=SUM(G4:K4) (для комірки L4).

Сума до видачі – це різниця стовпчика «Розподіленого відрядного заробітку» і «Всього утримано». Відповідно для комірки М4 формула має вигляд: =(F4-L4).

Кінцевий результат (рис.87).

Рис. 87. Готова таблиця

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Що таке база даних?
Назвати складові бази даних?
Як створити базу даних?
Як видалити запис в базі даних?
Як змінити запис?
Як відбувається пошук даних в базі даних?

Тема 16. Графічні об’єкти в Excel 2007

План

Створення графічних об’єкті
Форматування графічних об’єктів

1. Створення графічних об’єктів

Вставлення рисунків

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

Щоб вставити рисунок потрібно:

Розташувати курсор в тому місці документу, де потрібно встановити малюнок;
Виконати команду Вставлення/Зображення/Рисунок (рис.88).

Рис. 88. Вкладка вставлення графічних зображень

Відкриється вікно, в якому потрібно пройти шлях до папки з графічними зображеннями C:\Program Files\Microsoft Office\ClipArt\PUB60COR.
Виділити малюнок і натиснути кнопку Вставити.

Вставлення графіки

Щоб вставити графіку потрібно:

Виконати команду Вставлення/Зображення/Графіка.
В області задач в полі Шукати ввести ключове слово для пошуку і натиснути кнопку Почати.
Якщо натиснути на кнопку Упорядкувати кліпи, з’явиться вікно, в якому в дереві папок вибрати папку Колекція Microsoft Office. У відкрившомуся дереві (рис.89) вибрати категорію графіки, сам малюнок і з контекстного меню вибрати команду Копіювати.
Згорнути вікно, перейти на робочу область вікна і виконати команду Вставити з контекстного меню.

Вставлення фігури

Щоб вставити геометричну фігуру потрібно:

Виконати команду Вставлення/Зображення/Фігури.
У відкрившомуся підменю (рис.90) вибрати одну з категорій:
1. Лінії.
2. Основні фігури.
3. Фігурні стрілки.
4. Блок-схеми.
5. Виноски.
6. Зірки та стрічки.
Вибравши фігуру, потрібно її намалювати, провівши курсором по діагоналі в робочій частині вікна.

Вставлення об’єкту Word Art

Щоб вставити об’єкт Word Art потрібно:

Виконати команду Вставлення/Текст/Word Art.
Вибрати в підменю варіант об’єкту.
У вікні вписати текст надпису і натиснути ОК.

3. Форматування графічного об’єкту

Для форматування рисунка, його потрібно виділити. Рисунок стане обмеженим маркерами.

Щоб змінити розміри графічного об’єкту потрібно:

Виділити об’єкт.
Встановити вказівник миші на один з маркерів, з’явиться двонаправлена стрілка.
Щоб пропорційно збільшити (або зменшити) розміри малюнка, необхідно встановити вказівник миші на кутовий маркер, натиснути і не відпускаючи перетягти маркер, натиснути і не відпускаючи перетягти маркер від центру

(або до центру) і відпустити.

Щоб перемістити графічний об’єкт, потрібно:

Виділити об’єкт;
Встановити вказівник миші на ньому, натиснути і не відпускаючи кнопку миші, перетягнути об’єкт на потрібне місце і відпустити кнопку.

Щоб обернути об’єкт (рис.91) потрібно дочекатися появи зеленого маркера і повернути його проти або за годинниковою стрілкою.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Як вставити малюнок?
Як вставити малюнок з колекції кліпів?
Як створити фігуру?
Як змінити розмір малюнка?
Як перемістити малюнок?
Як вставити об’єкт Word Art?
Як вставити надпис?

Тема 17. Побудова діаграм в Excel 2007

План

1. Складові діаграми

2. Типи діаграм

3. Створення діаграми

1. Планування діаграми

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

Перед тим, як перейти до побудови діаграми, необхідно провести необхідну підготовку. Діаграми створюються на основі існуючих даних, тому потрібно спочатку створити аркуш, який містить всі необхідні дані.

Перед створення діаграми необхідно знати з яких елементів складається діаграма (рис.92):

Рис. 92. Складові елементи діаграми

Область діаграми - уся діаграма разом з усіма її елементами.
Область побудови - на плоских діаграмах обмежена осями область, яка містить ряди даних. На об'ємних діаграмах обмежена осями область, яка містить ряди даних, імена категорій, підписи та назви осей.
Ряд даних - пов'язані одна з одною точки даних, нанесені на діаграму. Кожний ряд даних на діаграмі має власний колір або інший спосіб позначення та представлений на легенді діаграми.
Маркери. Для відображення числових даних використовуються маркери. В якості маркерів використовуються стовпчики, лінії, крапки, сектора, долі.
Лінії сітки. Лінії сітки є продовженням поділок на вісі значень і вісі категорій.
Вісь - лінія, яка обмежує область побудови діаграми та служить віссю координат у вибраних одиницях виміру. Вісь Y звичайно розташована вертикально, а вздовж неї наносяться дані. Вісь X звичайно розташована горизонтально, а вздовж неї наносяться категорії.
Назви на діаграмах - описовий текст, який автоматично розташовується уздовж осей або по центру вгорі діаграми.
Підпис даних - підпис із додатковими відомостями про маркер даних, який представляє одну точку даних або значення клітинки аркуша.
Легенда - область, в якій подано кольори або інші способи позначення, що відповідають рядам даних або категоріям на діаграмі.

Вибір типу діаграми

Крім того потрібно подумати про тип діаграми, яку потрібно створити. Excel підтримує 11 основних типів діаграм, кожна з яких має декілька різновидів:

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

Об’ємна гістограма В об’ємних гістограмах використовуються три осі, які можна змінювати (горизонтальна вісь, вертикальна вісь і вісь глибини), і порівнюються значення Крапки даних.
Циліндричні, конусні та пірамідальні діаграми - відображають циліндричні, конічні та пірамідальні фігури замість прямокутників.

2. Графіки

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

3. Секторні діаграми (кругові)

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

4. Лінійчаті діаграми

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

5. Діаграми з областями

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

6. Точкові діаграми

Відображають відношення між числовими значеннями в кількох рядах даних, або візуалізують дві групи чисел у вигляді одного ряду координат x і y (рис.95).

Точкова діаграма має дві осі даних і відображає один набір числових даних уздовж горизонтальної осі (осі x), а інший —

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

Біржова діаграма 7. Біржові діаграми

Найчастіше застосовуються для ілюстрації коливань біржових цін. Однак ці діаграми можна використовувати також для наукових даних. Щоб створити біржову діаграму, потрібно розташувати дані у правильному порядку (рис.96).

8. Поверхневі діаграми

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

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

9.Кільцеві діаграми

Відображає співвідношення частин до цілого, але вона може містити кілька рядів даних (рис.97).

10. Бульбашкові діаграми

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

Пелюсткова діаграма 11. Пелюсткові діаграми

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

2. Створення діаграми

Для побудови діаграми на активному аркуші слід виконати наступні дії:

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

Вибрати тип діаграми (рис.99), а потім вибрати підтип, який потрібно використати.
Щоб переглянути всі доступні типи діаграм, вибрати тип діаграми та вибрати пункт Усі типи діаграм, щоб з’явилося діалогове вікно Вставлення діаграми. За допомогою стрілок можна переглядати всі доступні типи та підтипи діаграм. Клацнути потрібний варіант діаграми.

Рис. 99. Вікно вибору типу діаграми

Діаграму буде розташовано на аркуші як вбудовану діаграму . Якщо діаграму потрібно розташувати на окремому аркуші діаграми , потрібно змінити її розташування:

Зображення стрічки Excel

клацнути вбудовану діаграму або аркуш діаграми, щоб виділити їх і відобразити Знаряддя для діаграм.
на вкладці Конструктор у групі Розташування натиснути кнопку Перемістити діаграму (рис.100).
в полі Виберіть місце розташування діаграми на аркуші

виконати один з перемикачів: окремому (вказати назву аркуша) або наявному (вибрати потрібний аркуш зі списку в полі наявному).

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

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

за допомогою вкладки Конструктор можна відображати ряди даних за рядками або стовпцями, змінювати вихідні дані, розташування й тип діаграми, зберігати діаграму як шаблон чи вибирати стандартні параметри макета й форматування.
за допомогою вкладки Макет можна змінити відображення елементів діаграми (наприклад, її назви або підписів даних), використати засоби креслення або додати до діаграми написи та рисунки.
за допомогою вкладки Формат можна додати кольори заливки, змінити стилі ліній і застосувати інші ефекти.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Що таке діаграма?
Які є складові в діаграмі?
Які є типи діаграм?
В яких випадках створюється гістограма?
В яких випадках створюється поверхнева діаграма?
Як використовується секторна діаграма?
Як створити діаграму?
Яким чином розмістити діаграму на окремому аркуші?

Тема 18. Редагування і форматування елементів діаграми

План

1.Вкладка Конструктор

2. Вкладка Макет

3. Вкладка Формат

1.Вкладка Конструктор

Дана вкладка призначена для форматування діаграми (рис.101):

Рис. 101. Вкладка Конструктор

1. Тип - змінення типу наявної діаграми - у групі Тип клацнути елемент Змінити тип діаграми. У діалоговому вікні вибрати тип діаграми в першому вікні, відтак вибрати вид діаграми, який слід використати, у другому вікні.

2. Дані – перегляд даних, по яким будується діаграма, перехід від стовпця до рядка і навпаки.

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

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

2. Вкладка Макет

Призначена для редагування діаграми (рис.102).

Рис. 102. Вкладка Конструктор

Поточний виділений фрагмент - змінення макета елементів діаграми вручну. Вибір елемента діаграми зі списку елементів діаграми, натиснути стрілку поруч із полем Елементи діаграми та вибрати потрібний елемент.
Вставити – для вставки малюнків, фігур, надписів.
Підписи – підписи елементів діаграми. Має такі кнопки:

Назва діаграми – виберіть варіант Назва в центрі з накладанням або Над діаграмою. У текстовому полі Назва діаграми, яке з‘явиться в діаграмі, ввести потрібний текст.
Назви осей – щоб додати назву до головної горизонтальної осі (осі категорій), вибрати пункт Назва головної горизонтальної осі та вибрати потрібний параметр.
- Щоб додати назву до головної вертикальної осі (осі значень), потрібно вибрати пункт Назва головної вертикальної осі або Назва допоміжної вертикальної осі і вибрати потрібний параметр.
- Щоб додати назву до осі Z (осі рядів даних), потрібно вибрати пункт Назва осі Z і вибрати потрібний параметр. У текстовому полі Назва осі, яке з‘явиться в діаграмі, ввести потрібний текст.
- Легенда – вибір розміщення легенди на діаграмі.
- Підписи даних – підписи над кожним рядом даних.
- Таблиця даних – з’являється таблиця, по якій побудовано діаграму.

Осі - вибирається потрібний параметр осі або лінії сітки. Для приховання осі потрібно вибрати потрібний тип осі і вибрати пункт Немає.
Тло – вибір стінки діаграми, основи діаграми, обертання діаграми.
Аналіз – встановлення ліній тренду для специфічних типів діаграм.

3. Вкладка Формат

Призначена для форматування надписів діаграми (рис.103).

Рис. 103. Вкладка Формат

Поточний виділений фрагмент - зміна макету елементів діаграми вручну

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

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

Стилі фігур - щоб відформатувати фігуру вибраного елемента діаграми потрібно вибрати потрібний стиль або натиснути одну з кнопок:

Заливка фігури;
Контур фігури;
Ефекти для фігур.

Стилі WordArt - відформатувати текст у виділеному елементі діаграми засобами WordArt, у групі вибрати потрібний стиль або натиснути одну з кнопок Заливка тексту, Контур тексту чи Текстові ефекти, після чого виберіть потрібні параметри форматування.
Упорядкування – на задній план, на передній план, область виділення, вирівнювання.

Зміна вирівнювання та орієнтації підписів

Можна змінювати вирівнювання підписів осі як на горизонтальній осі (категорій), так і

вертикальній осі (значень). Якщо в діаграмі є багаторівневі підписи категорій Багаторівневі підписи категорій. Підписи категорій на діаграмі, відображувані у вигляді структури залежно від даних у клітинках на аркуші. Наприклад, заголовок "Фрукти" може відображатися над рядком із заголовками "Сливи", "Яблука" та "Груші"., можна змінювати вирівнювання всіх рівнів підписів. Можна також змінити інтервали між рівнями підписів на горизонтальній осі (категорій).

Зображення стрічки Excel На вкладці Формат у групі Поточний виділений фрагмент вибрати пункт Форматувати виділений фрагмент (рис.104).

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

Щоб змінити орієнтацію тексту, потрібно вибрати потрібне розташування полі Напрямок тексту.

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Які вкладки призначені для форматування діаграми?
Як відформатувати легенду?
Як підписати елементи діаграми?
Як змінити форматування надписів на діаграмі?
Як відформатувати ряди даних?

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

Тема. Побудова діаграм в Excel 2007.

Мета. Навчитися будувати діаграми і графіки різних типів в Excel 2007.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

У звіті повинні бути описані етапи виконання завдань: 3.3; 3.4; 3.5; 3.7.

Побудувати таблицю наведеного зразка:

Прізвище	Алгебра	Хімія	Біологія	Фізика
Грищук	10	11	8	11
Купратий	5	10	9	7
Іванов	8	9	9	8
Василишин	9	10	9	6

3.2. Побудувати гістограму, вказуючи її назву, назви вісей, легенду розмістити під діаграмою.

3.3. Побудувати кільцеву діаграму оцінок учнів з Хімії.

3.4. Побудувати на окремому аркуші діаграму з областями по даній таблиці.

3.5. Змінити гістограму на інший тип діаграми.

3.6. Побудувати на окремому аркуші графік функції від двох змінних: z = x² - y².

Для цього потрібно:

побудувати таблицю значень цієї функції для значень x та y на проміжках [-2;2] з кроком h=0,2. Для цього перший рядок, починаючи з комірки В1 заповнити значеннями x: -2; -1,8;......1,8; 2 за правилами створення арифметичної прогресії: ввести у В1 число -2, виконати команду Основне/Редагування/Заповнити/Прогресія, вибрати параметри Арифметична, По рядках, крок 0,2, Граничне значення 2, натиснути ОК. Аналогічно заповнити перший стовпець значеннями y, починаючи з комірки А2.;
у комірку В2 ввести формулу =В$1^2 - $A2^2 і скопіювати її у діапазон В1:V22.
побудувати діаграму, вибравши тип діаграми Поверхня.

Отримана поверхня називається сідлом або гіперболічним параболоїдом.

3.7. Побудувати на окремому аркуші графік функції z = x² * y².

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Які є види діаграм? Їх призначення.
З яких елементів складається діаграма?
Навести зразок діаграми і вказати на ній дані елементи.
Як побудувати діаграму?
Як змінити тип діаграми на інший?

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

Тема. Побудова діаграм і графіків у Microsoft Excel 2007.

Мета. Навчитися будувати діаграми і графіки функцій в Excel 2007 по зразку.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. Створити наведену нижче таблицю:

№ з/п	Прізвище	Ставка	Податок	До видачі
1	Петренко	2000 грн.	45% від ставки	?
2	Григорук	1800 грн.		?
3	Савченко	1500 грн.		?
4	Клімова	2500 грн.		?
5	Вітренко	3250 грн.		?

3.2. Побудувати діаграми по зразку (рис. 105):

Рис. 105. Зразки діаграм

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Які є види діаграм? Їх призначення.
З яких елементів складається діаграма?
Навести зразок діаграми і вказати на ній дані елементи.
Як побудувати діаграму?
Як змінити тип діаграми на інший?
Як побудувати діаграму на окремому аркуші?

Практична робота № 28-29

Тема. Редагування і форматування діаграм у Excel 2007.

Мета. Закріпити вміння будувати діаграми різних видів. Ознайомитися з форматуванням і редагуванням побудованих діаграм.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
- 3.1. Побудувати графіки функцій:

Y=tg(3x²-5x+1) , при х =[-05; 1] з кроком 0,1.

Y= (8x-2)*ln(x+3), при х =[1;3] з кроком 0,5.

У= 2х³+3сos у якщо х > 0, у > 0

4x+0,3*| у | якщо х <= 0, у <= 0 при х та у = [0;2,5] з кроком 0,35.

3.2. Підписати всі елементи діаграми.

3.3. Використовуючи дані з таблиці, побудувати об’ємну стовпчасту діаграму та кільцеву об’ємну діаграму розподілу сплати за газ по місяцям:

Місяць	Сума сплати
Березень	179,78
Квітень	93,45
Травень	74,96
Червень	13,09
Всього:	?

Відформатувати діаграми таким чином.

Стовпчасту.
1. Розмістити на окремому аркуші.
2. Примінити макет діаграми на власний розсуд.
3. Примінити стиль діаграми, змінити фігури рядів даних.
4. Підписати всі елементи діаграми (заголовок, назви вісей, легенду розмістити справа, підписи даних, основні лінії сітки, проміжні горизонтальні лінії сітки).
5. Вставити зліва біля області побудови фігуру .
6. Заголовок зробити надписом Word Art.
7. Залити стінку діаграми фіолетовим кольором, основу блакитним кольором.
8. Здійснити оберт діаграми на вільний кут.
9. Змінити глибину діаграми та зазор між рядами даних збільшити.
10. Змінити розміщення рядів даних в такому порядку: Червень, Травень, Березень, Квітень.
Кругову.
1. Підписати всі елементи діаграми.
2. Легенду залити заливкою текстури, навести тінь, границю зробити світло-зеленим кольором, штрихпунктирною лінією.
3. Рядам даних поміняти кольори заливки, підписи даних обвести рамкою.
4. Примінити ефекти для фігури діаграми.
5. Видалити з діаграми ряд даних – Червень.

3.4. За результатами перепису населення 2009 року в Україні проживало 34,3 млн. чоловік міського населення і 19,2 млн. чоловік сільського. На той самий час у Росії проживало у містах 50,5 млн. чоловік, а у сільській місцевості 45,2 млн. чоловік. У Латвії відповідно 2,1 млн. чоловік і 1,3 млн. чоловік.

Створити таблицю даних і побудувати по ній:

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

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Які є види діаграм? Їх призначення.
З яких елементів складається діаграма?
Як змінити форматування рядів?
Як відформатувати легенду?
Як ввести назву діаграми?
Яким чином залити стінку діаграми?
Як відформатувати вісі?

Тема 19. Використання макросів

План

Запис макросу
Виконання макросу
Безпека макросів

Запис макросу

Зображення стрічки Excel Макрос - макрокоманда або послідовність макрокоманд, яку використовують для автоматичного виконання завдань. Макроси записуються мовою програмування Visual Basic.

Запис макросу

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

Якщо вкладка Розробник недоступна, для її відображення виконайте такі дії:

Натиснути кнопку Microsoft Office і виберіть пункт Excel Параметри.
У категорії Найуживаніші в групі Основні параметри роботи з Excel встановити прапорець Відображати вкладку «Розробник» на стрічці, відтак натиснути кнопку ОК.

Щоб записати макрос потрібно:

На вкладці Розробник у групі Код натиснути кнопку Захист від макросів (рис.106). У меню Настройки макросів вибрати пункт Увімкнути всі макроси (не рекомендовано, оскільки можливе виконання потенційно небезпечного коду), відтак натиснути кнопку ОК.
На вкладці Розробник у групі Код вибрати пункт Записати макрос.
Відкриється вікно (рис.107). У полі Ім'я макросу ввести ім'я для макросу.
Щоб призначити сполучення клавіш в діалоговому вікні Сполучення клавіш ввести потрібну маленьку або велику літеру.
У списку Зберегти в вибрати книгу, в якій потрібно зберегти макрос.
Щоб додати опис макросу, в діалоговому вікні Опис ввести потрібний текст.
Натиснути кнопку ОК, щоб розпочати запис макросу.
Виконати дії, які потрібно записати.
На вкладці Розробник у групі Код натиснути кнопку Зупинити запис .

Можна також натиснути кнопку Зупинити запис у лівій частині рядка стану.

Використання макросів

Редагування макросу

На вкладці Розробник у групі Код натиснути кнопку Макроси.
У полі Ім’я макросу вибрати макрос для редагування.
Натиснути кнопку Редагувати.

Призначення макросу об’єкту, рисунку або елементу керування

На аркуші клацнути правою кнопкою миші об’єкт, рисунок або елемент керування, якому слід призначити наявний макрос, і в контекстному меню вибрати команду Призначити макрос.
У полі Ім’я макросу вибрати потрібний макрос.

Видалення макросу

Відкрити книгу з макросом, який потрібно видалити.
На вкладці Розробник у групі Код натиснути кнопку Макроси.
Зі списку Макроси з вибрати пункт Ця книга.
У полі Ім’я макросу вибрати ім’я макросу, який потрібно видалити.
Натиснути кнопку Видалити.

Запуск макросу

Відкрити книгу, яка містить макрос.
На вкладці Розробник у групі Код натиснути кнопку Макроси.
У полі Ім’я макросу вибрати макрос, який потрібно запустити.
Виконати одну з дій:

Щоб запустити макрос у книзі Excel, потрібно вибрати команду Виконати. Для запуску макросу можна також натиснути сполучення клавіш CTRL+F8.
Перервати виконання макросу можна, натиснувши клавішу ESC.

3. Безпека макросів

Налаштування захисту від вірусів можна змінити в Центрі безпеки та конфіденційності (кнопка Microsoft Office , кнопка Параметри Excel, категорія Центр безпеки та конфіденційності, кнопка Параметри центру безпеки та конфіденційності, категорія Настройки макросів. Або на вкладці Розробник, група Код, кнопка Захист від макросів). Якщо ви працюєте в установі, системний адміністратор може змінювати настройки за промовчанням і забороняти користувачам змінювати ці налаштування самостійно.

Усі зміни налаштувань захисту від макросів, зроблені в Excel у категорії Настройки макросів, стосуються лише програми Excel і не впливають на будь-які інші програми (таблиця 2).

Таблиця 2. Безпека макросів

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

Перегляньте презентацію на диску в папці Презентації під назвою, яка співпадає з назвою цієї теми.

ПЕРЕВІР СЕБЕ

Що таке макрос?
Як записати макрос?
Які є способи виконання макросу?
Як виконати макрос через вікно Макроси?
Як призначити комбінацію клавіш макросу?
Чому макроси вважаються небезпечними?

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

Тема. Створення макросів.

Мета. Навчитися створювати бланки за допомогою макросів.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.

3.1. Створити бланк доставки, використовуючи макроси.



Дата доставки		Час доставки
	Продавець
БЛАНК ДОСТАВКИ №
Прізвище ім’я та по батькові отримувача товару (повністю)
Адреса доставки
Назва магазину
Адреса магазину
Під’їзд		Поверх
Телефон		Код
Домофон		Ліфт
Вантажник		Пільги
Додаткова інформація
Найменування товарів




Кількість		№ накладної
Загальна сума		Ціна доставки
Правила доставки	(заповнюється при потребі)

З вимогами доставки ознайомлений_____________________(________)Дата:_______

3.2. Заповнити 2 бланки доставок: 1. побутової техніки. 2. продуктових товарів.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Що таке макрос?
В яких випадках створюється макрос?
Як записати макрос?
Які є способи виконання макросу?
Як виконати макрос через вікно Макроси?

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

Тема. Створення макросів.

Мета. Навчитися створювати бланки за допомогою макросів.

Оснащення. ПК, роздаткові картки.

Хід роботи

Ознайомитися з темою та метою практичної роботи.
Прочитати теоретичний матеріал, поданий вище.
Виконати практичне завдання.
1. На окремому аркуші ввести назву тесту з інформатики, прізвище виконавця роботи.
2. Створити таблицю 6х4 або 12х4 (в залежності від кількості запитань).
3. Відформатувати назву і таблицю таким чином, щоб вони набули естетичного вигляду і розміщення вмісту комірок було раціональним.
4. Заповнити перший рядок такими даними:

Номер за порядком.

Запитання.

Відповідь.

Заповнити кожний рядок перших двох стовпців відповідними даними (номер і запитання з обраної теми).
Щоб заповнити комірку «Відповідь» для першого запитання потрібно виділити комірку, в яку повинна бути внесена відповідь і виконати команду «Дані/Знаряддя даних/Перевірка даних» . У вікні, що відкрилось, в рядку тип даних вибрати «список», в рядку «джерело» через крапку з комою ввести варіанти відповідей (одна з яких вірна) – ок.
Заповнити аналогічно рядки відповідей для всіх інших запитань.
Щоб заповнити комірку «Бали» для першого запитання потрібно виділити комірку, в якій повинен вестися підрахунок балів і запустити майстра функцій (f_x), вибрати функцію «IF» (категорія «логічні»).
У вікні, що відкрилось в першому рядку ввести адресу комірки, в яку внесено відповідь, поставити знак = і вірну відповідь (якщо відповідь текстова – взяти в лапки); в другому рядку поставити кількість балів за вірну відповідь (без лапок); в третьому рядку – кількість балів за невірну відповідь (зазвичай – 0) або текст про те, що відповідь невірна – ок.
Скопіювати дану формулу на всі комірки з нарахуванням балів (можна скористатися маркером заповнення в правому нижньому кутку комірки, яку копіюють) і при виділенні кожної комірки в рядку формул потрібно змінити правильні відповіді для кожного наступного запитання.
Щоб приховати кількість балів, яка нараховується, потрібно відформатувати комірку таким чином, щоб колір літер співпадав з кольором фону комірки (наприклад білим по білому).
В будь-якій комірці або на наступному аркуші книги потрібно підрахувати суму балів (за алгоритмом, вивченим раніше). Відформатувати цю комірку і створити в ній примітку. В примітці потрібно зробити підказку, наприклад «Зафарбуй комірку, щоб побачити бали».
Щоб приховати формули, внесені в комірки «Бали», потрібно виконати такі дії: виділити всі комірки, крім стовпчика «бали» і в контекстному меню виконати команду Формат клітинок» на вкладці «захист» зняти прапорець біля команди «захищена комірка».
Виділити стовпчик з балами і в контекстному меню виконати команду Формат комірок на вкладці «Захист» крім прапорця «захищена комірка» потрібно поставити додатковий прапорець «приховати формули».
Захистити аркуш (контекстне меню аркуша – команда захистити аркуш.).

Записати макрос для витирання варіантів відповідей і призначити на нього сполучення клавіш і кнопку. Для цього необхідно виконати такі команди:
1. Поставити низьку безпеку макросу.
2. Відкрити вікно створення макросу.
3. Виконати дії по витиранню відповідей попереднього учасника тестування (виділити і натиснути кнопку delete).
4. Зупинити запис відповідною кнопкою панелі, що з’явилася на початку запису.
5. Вивести на панель швидкого доступу кнопку Форми.
6. На панелі, що з'явилась, вибрати кнопку і поставити її в кінці тесту.
7. У вікні, що відкрилось, вибрати ім’я щойно створеного макросу.
8. Перевірити, чи працює кнопка (витираються попередні відповіді).
9. Відформатувати весь тест таким чином, щоб з ним зручно було працювати.
10. Прибрати сітку на полотні.
11. Зберегти роботу, протестувати одного з учнів.

4. Написати творчий звіт по виконаній роботі та зробити письмові висновки.

5. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

Що таке макрос?
В яких випадках створюється макрос?
Як записати макрос?
Які є способи виконання макросу?
Як виконати макрос через вікно Макроси?

КОМПЛЕКСНІ ЗАВДАННЯ

ЗАВДАННЯ № 1

Створити наступну таблицю (від форматувавши її по зразку) та заповнити її, врахувавши, що:
1. Премія в розмірі 15 грн. нараховується працівникам, що відпрацювали не менше 176 годин;
2. Нараховано = Ставка * Час роботи + Премія;
3. Податок = 13% від Нараховано;
4. До видачі = Нараховано – Податок.

НАРАХУВАННЯ ЗАРПЛАТИ
№	Прізвище	Ставка (грн. за год.)	Час роботи	Премія	Нараховано	Податок	До видачі
1	Іванов	5,00	160
2	Петров	4,50	176
3	Сидоров	5,50	176
4	Кравчук	5,50	200
5	Прачук	6,50	176
6	Савчук	5,00	50
	Разом
	Середнє
	Макс.

Побудувати стовпчикову діаграму за зразком:

Побудувати кругову діаграму за зразком:

ЗАВДАННЯ № 2

Створити на двох аркушах книги в Excel

Створити таблицю за поданим зразком.

Відомість розрахунку вартості перевезення вантажів

№з/п	Модель	Вага вантажу	Відстань	Тариф	Вартість перевезення
№з/п	Модель	Вага вантажу	Відстань	Тариф	Грн.	USD
1	МАЗ	4,5	165	0,35
2	КАМАЗ	8,0	1200	0,50
Вартість разом
Мінімальний тариф
Курс USD						8,41

Примітка

Вартість перевезення 1 тонни вантажу задається на кілометр за вказаним тарифом по формулі.

Якщо відстань менша ніж 210 км., то по формулі: =Вага*Відстань*Тариф, якщо більша ніж 210 км. Тариф зменшується на 10%.

Підрахувати значення функцій Y(x) для Х (Х1;Х2), з кроком ΔХ. Знайти найбільші, найменші значення функцій та середні арифметичні значення.

Функція	Х1	Х2	ΔХ	Знайти	Знайти	Знайти
Y =x²+6,2x-4,5	-2,1	5,6	0,15	Середнє значення	Мінімум	Максимум

3. Побудувати графік функції Y(x) на окремому аркуші.

ЗАВДАННЯ № 3

Створити на двох аркушах книги в Excel

1. Створити таблицю по зразку.

Відомість для нарахування стипендії за травень 2011 р.

Прізвище учня	Предмети						Середній бал	Нараховано
Прізвище учня	Українська мова	Історія України	Зарубіжна література	Математика	Інформатика	Примітка	Середній бал	Нараховано
Сердюк М.М.	10	10	11	10	10	-	?	?
Коваль А.А.	8	7	9	5	6	-	?	?

Умова нарахування стипендії.

Нараховано 90 грн., якщо Середній бал >= 10;

80 грн., якщо Середній бал < 10,

0 грн., якщо Середній бал < 2.

2. Вивести на окремому аркуші тих учнів, які з української мови і з Математики мають 10 балів.

3. Вивести через Розширений фільтр тих учнів, які мають середній бал менше

4. Побудувати таблицю для підрахунку значень функції.

F(X,Y) = 2x²+3y² , на діапазоні [-1,5;1,5] з кроком 0

5. Побудувати графік функції F(X,Y).

6. Створити макрос, який би створював колонтитул з назвою документу і прізвищем автора.

ЗАВДАННЯ № 4

№	Замовник	Шифр товару	К-ть, шт.	Дата замовлення	Ціна, грн.
1	ПП "Маяк"	ШТ-44	4	01.11.2018	22,95
2	ПП "Маяк"	ШТ-98	3	18.10.2018	97,34
3	ПП "Маяк"	ШТ-29	3	17.10.2017	144,12
4	ЗАТ "Атом"	ШТ-52	9	14.10.2017	12,98
5	ЗАТ "Атом"	ШТ-44	3	11.10.2017	22,95
6	ЗАТ "Атом"	ШТ-45	6	30.11.2017	32,44
7	ЗАТ "Атом"	ШТ-29	1	22.10.2017	144,12
8	МП "Еталон"	ШТ-44	6	11.11.2017	22,95
9	МП "Еталон"	ШТ-81	5	01.11.2018	12,98
10	МП "Еталон"	ШТ-45	4	24.10.2018	32,44
11	"Протон"	ШТ-29	4	23.10.2018	144,12
12	"Протон"	ШТ-81	12	30.11.2018	12,98
13	АТ "Старт"	ШТ-45	8	18.10.2018	32,44
14	АТ "Старт"	ШТ-44	8	28.10.2018	22,95
15	АТ "Старт"	ШТ-45	3	15.10.2018	32,44
16	СП "Радон"	ШТ-98	3	14.08.2018	97,34

Створити таблицю за зразком.
Відформатувати її.
Скопіювати аркуш1 7 раз.

Відомість замовлення товарної продукції

4. Посортувати дані:

За збільшенням по полю Замовник.
За зменшенням по полю Замовник.
За зменшенням по полю Ціна, грн.

5. Відфільтрувати в таблиці на окремих аркушах:

Усі замовлення ЗАТ “Атом”.
Усі замовлення з шифром ШТ-81.
Усі замовлення з кількістю більше 6 штук.
Усі замовлення, вироблені не в 2000 році.
Усі замовлення, вироблені у листопаді 2000 року.
Усі замовлення МП “Еталон” та “Протон”.
Усі замовлення з ціною більше 30 грн., але менше 100 грн.
Усі замовлення, вироблені в жовтні 2000 р. вартістю більше 50 грн.

ЗАВДАННЯ 5

Створити такий бухгалтерський документ .

Платіжна відомість

Курс долара		5,57

№ з/п	Прізвище	Ставка	Аванс	Податок із зарплати	Зарплата	Зарплата у валюті	До видачі, грн.
1	Артюхін	6000,00	?	?	?	?	?
2	Буланова	9500,00	?	?	?	?	?
3	Вікторова	17706,00	?	?	?	?	?
4	Дуборко	6005,00	?	?	?	?	?
5	Мінін	20000,00	?	?	?	?	?
6	Жарков	4500,00	?	?	?	?	?
7	Заварін	5004,00	?	?	?	?	?
8	Маккой	1203,00	?	?	?	?	?
9	Харламов	3002,00	?	?	?	?	?
10	Щука	1600,00	?	?	?	?	?

					Середня ставка		?
					Середня зарплата		?
					Всього до видачі		?
		Директор_______________________________________________
		Головний бухгалтер______________________________________

Умови нарахування авансу
	Ставка	Сума авансу
	<=50	Ставка*0,5			Зарплата обраховується -
	>50	Ставка*0,33			Ставка-Податок із зарплати

Умови нарахування податку із зарплати
	Ставка	Податок із зарплати			До видачі, грн. обраховується -
	<=60	0			Ставка- (Аванс+Податок)
	<=120	(Ставка-60)*0,1
	>120	(Ставка-120)*0,2+6

ПІСЛЯМОВА

Ну ось Ви і прочитали мій навчально-методичний посібник «Excel 2007. Крок за кроком», виконалт всі практичні роботи, комплексні завдання.

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

Сподіваюся, що посібник був зрозумілий в написанні, доступний, достатньо наочний.

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

www.gryshetska.ucoz.ru

СПИСОК РЕКОМЕНДОВАНОЇ ЛІТЕРАТУРИ

Информатика: практикум по технологии работы на компьютере/ Под ред. Н.В.Макаровой. – М.: Финансы и статистика, 1997. – 384С.
Microsoft Office 97. Шаг за шагом (книга и компакт-диск): Практ. пособ./ Пер. с англ. – М.: Издательство ЭКОМ, 1998. – 800 с.
Практикум по экономической информатике: Учебное пособие. Часть 1. /Под ред. Шуремова Е.Л., Тимаковой Н.А., Мамонтовой Е.А. – М.: Изд-во «Перспектива», 2000. – 300 c.
Карлберг К. Бизнес-анализ с помощью Excel 2007./ Пер.с англ.:- М.: Издательский дом «Вильямс», 2001. – 480 с.
Попов А.А. Excel 2007: практическое руководство. - М.: ДЕСС КОМ, 2001. – 301 с.
Microsoft Excel. Версия 2002. Шаг за шагом: Практ. пособие/ Пер. с англ. – М.: Издательство ЭКОМ, 2003. – 368 с.
Паненко И.Г. Офисные программы. Шаг за шагом. - М.: Эксмо, 2007. – 384 с.
Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. – Спб.: Питер, 2007. – 524 с.http:/uchinfo.com.ua/
http://www.alleng.ru/edu/comp1.htm
http://infoschool.narod.ru/
http://www.metod-kopilka.ru/page-2-2.html
http://www.uroki.net/

Середня оцінка розробки

Структурованість

5.0

Оригінальність викладу

5.0

Відповідність темі

5.0

Загальна:

5.0

Всього відгуків: 2

Оцінки та відгуки

Кулак Ірина Леонідівна 06.03.2024 в 01:11

Щиро дякую!!!

Загальна:

5.0

Структурованість

5.0

Оригінальність викладу

5.0

Відповідність темі

5.0
Stohnii Olena 27.01.2024 в 21:28

Загальна:

5.0

Структурованість

5.0

Оригінальність викладу

5.0

Відповідність темі

5.0

doc

Завантажити

Зберегти на потім

Додав(-ла)

Грушецька Інна Олександрівна

Пов’язані теми

Інформатика, Матеріали до уроків

Додано

24 жовтня 2019

Переглядів

47428

Оцінка розробки

5.0 (2 відгука)