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 с.

 

 

 

 

Посібник призначений для вивчення  табличного процесора Excel українізованої версії 2007 . Може бути використаний для використання на уроках з таких предметів: Технології комп’ютерної обробки інформації, Інформатика а також для самостійного вивчення.

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

Після кожного теоретичного матеріалу йде одна або 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!!!!!

 

 

 


ЗМІСТ

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

 

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

 

План

  1. З історії Excel
  2.   Можливості програми
  3.   Запуск програми
  4.   Вихід з програми
  1. З історії 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. Вікно підтвердження збереження

 

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

 

ПЕРЕВІР СЕБЕ

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

 

 

 

 

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

 

План

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

Вікно програми містить ряд типових елементів: рядок заголовка, панель швидкого доступу, кнопка 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, відкриється вікно, в якому вибрати вкладку Найуживаніші і в

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

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

 

Рядок формул, розміщений під панелями інструментів, призначений для редагування вмісту елементів таблиці. Він розділений на три частини. Права частина служить для відображення вмісту активної клітини таблиці, яку можна редагувати за допомогою кнопок, розміщених зліва від відображеного вмісту клітини. У лівій частині рядка вказується адреса активної клітини або адреса виділеного діапазону клітин. Рядок формул можна сховати, виконавши команду Вигляд/Приховати/Рядок формул (рис. 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>, виділити першу комірку другого блоку (після цього перетяганням виділити повністю другий блок) і так далі.
  • Для виділення декількох робочих аркушів слід просто виділити декілька ярликів, відповідних цим аркушам.
  • Якщо ж потрібно виділити всі робочі аркуші, потрібно клацнути лівою кнопкою миші на першому з них, потім клацнути правою кнопкою миші і в контекстному меню, що відобразилося, вибрати пункт Виділити всі аркуші.

 

 

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

 

ПЕРЕВІР СЕБЕ

  1. Що містить в робочій області вікна?
  2. Що містить в рядку формул?
  3. Що містить в собі панель швидкого доступу?
  4. Як переміщуватися по коміркам?
  5. Як настроїти панель швидкого доступу?
  6. Що містить в рядку стану вікна програми?
  7. Що містить в собі кнопка Office?
  8. Як змінити масштаб аркуша?
  9. Описати рядок стану.

 

 

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

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

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

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

Хід роботи

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

Завдання

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Як відбувається виділення комірок?
  2. Як приховати заголовки з екрану?
  3. Що містить в собі панель швидкого доступу?
  4. Що містить в рядку стану вікна програми?
  5. Як змінити масштаб аркуша?
  6. Скільки аркуші  є в книзі?
  7. Що містить в собі кнопка Office?
  8. Як переміщуватися по коміркам?

 

 

 

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

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

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

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

 

Хід роботи

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

 

 

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

 

Назва вкладки

Категорії кнопок, які в ній є

Основне

Буфер обміну, шрифт, …..

……

…..

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

 

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

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

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

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

 

 

 

 

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

 

План

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

 

ПЕРЕВІР СЕБЕ

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

 

 

 

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

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

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

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

Хід роботи

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

Завдання

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

 

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

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

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

  1. Як додати аркуш?
  2. Як видалити аркуш?
  3. Як змінити назву аркуша?
  4. Як розділити
  5. Яким чином закріпити область?
  6. Як впорядкувати усі відкриті вікна?
  7. Як приховати заголовки з екрану?

 

 

 

 

 

 

 

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

 

План

 

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

 

 

ПЕРЕВІР  СЕБЕ

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

 

 

 

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

 

План

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

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

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

 

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

 

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

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

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

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

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

 

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

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

 

 

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

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

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

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

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

 

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

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

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

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

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

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

ПЕРЕВІР СЕБЕ

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

 

 

 

 

 

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

 

План

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

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.

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

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

 

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

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

 

  • рисунок;
  • графіка;
  • фігури;
  • smart Art;
  • діаграма.
  1.    Перемістити об’єкт на потрібне місце і змінити розміри;
  2.    Знищується об’єкт – Delete.

 

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

 

 

ПЕРЕВІР СЕБЕ

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

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

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

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

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

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

 

 

 

 

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

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

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

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

Хід роботи

  1. Ознайомитися з темою та метою практичної роботи.
  2. Прочитати теоретичний матеріал, поданий вище.
  3. Виконати практичне завдання.
    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

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

 

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

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

 

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

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

 

 

 

 

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

 

План

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

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

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

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

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

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

 

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

 

 

 

 

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

 

План

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

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

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

 

 

 

 

 

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

 

 

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

 

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

 

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

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

 

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

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

 

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

 

 

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

 

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

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

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

 

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

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

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

 

 

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

 

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

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

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

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

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

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

 

 

 

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

 

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

 

 

ПЕРЕВІР СЕБЕ

  1. Яка команда відкриває вікно діалогу для форматування комірок?
  2. Які числа можна вводити в комірки?
  3. Які способи вирівнювання існують?
  4. Як можна відформатувати шрифти?
  5. Як окреслити контури таблиць?
  6. Яким чином можна змінити висоту рядків?
  7. Яким чином використати умовне форматування?

 

 

 

 

 

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

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

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

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

 

Хід роботи

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

 

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

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

 

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

 

Найменування цінностей

Прийнято цінностей , грам

Ціна за одиницю товару, г

На загальну суму , грн..

Передано цінностей,

грам

Залишок на кінець дня, грн..

Від завідувача каси

Від касових працівників

Завідувач каси

Касовими працівник

ками

Золото

55000

40000

5,00 грн.

 

30000

39860

 

Срібло

40000

457

3,60 грн.

 

10000

300

 

Всього:

 

 

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

 

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

№ з/п

Дата

Найменування операції

Брак по видам

Відмітка ОТК

к/з

м/е

з/е

БТУ

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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

  1.    Як змінити межі комірки?
  2.    Як вилучити сітку з екрану?
  3.    Як задати фон комірки?
  4.    Як можна об'єднати комірки?
  5.    Як встановити бажаний тип даних?
  6.    Якими способами можна встановити вирівнювання тексту в комірці?
  7.    Як змінити колір фону комірки?

 

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

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

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

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

Хід роботи

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

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

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

 

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

Обсяги продажу товарів, робіт, послуг по яких виникає податкове зобов’язання

Обсяг проведених коригувань

Проведення розрахунків з продажу товарів, робіт, послуг.

Загальні обсяги продажу

 

Продаж на митній території України

Коригування продажу товарів, послуг, що оподатковуються за ставкою 20%

Коригування продажу товарів, робіт, послуг, по яких податок на додану вартість не нараховується

Форма розра-хунку (бартер, готівка, оплата розраху-нкового рахунку та ін.)

Дата проведення розра-

хунку

Платникам ПДВ

Неплатникам ПДВ

База оподаткування

ПДВ

База оподаткування

ПДВ

База оподаткування

ПДВ

За ставкою 0%

Звільненні від оподатку-вання

1

2

3

4

5

6

7

8

9

10

11

 

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

Додаток

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

ЖУРНАЛ

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

 

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

№ з/п

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

Посада

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

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

Дата

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

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

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

 

 

 

 

 

 

 

 

 

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

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

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

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

 

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

 

 

 

 

 

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

 

План

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

 

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

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

=А1+А2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

 

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

 

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

 

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

  1. Виділити комірку.
  2. Записати потрібне ім’я в поле адреси активної комірки, що знаходиться ліворуч від рядка формул і натиснути 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).

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

 

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

 

ПЕРЕВІР СЕБЕ

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

 

 

 

 

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

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

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

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

Хід роботи

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

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

 

 

 

 

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

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

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

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

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

 

 

 

 

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

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

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

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

Хід роботи

  1.        Ознайомитися з темою та метою практичної роботи.
  2.        Прочитати теоретичний матеріал, поданий вище.
  3.        Виконати практичне завдання.
    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, м2

Площа у %

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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

 

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

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

 

 

 

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

 

План

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

 

 

 

 

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

 

 

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

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

 

 

МАТЕМАТИЧНІ

  1. ABS.   Повертає абсолютне значення числа
  2. ACOS.   Обчислює арккосинус числа
  3. ASIN.   Повертає арксинус числа
  4. ATAN.  Повертає арктангенс числа
  5. COS.   Повертає косинус числа
  6. DEGREES.  Перетворює радіани на градуси
  7. EVEN.   Округлює число до найближчого більшого парного цілого
  8. EXP.   Повертає число e, піднесене до вказаного ступеня
  9. INT.   Округлює число до найближчого меншого цілого
  10. LN.   Повертає натуральний логарифм числа
  11. LOG.   Повертає логарифм числа за вказаною основою
  12. LOG10.  Повертає десятковий логарифм числа
  13. MOD.   Повертає остачу від ділення
  14. ODD.   Округлює число до найближчого більшого непарного цілого
  15. PI.   Повертає число «пі».
  16. POWER.  Повертає число, піднесене до степеня
  17. PRODUCT.  Перемножує аргументи
  18. QUOTIENT.  Повертає цілу частину частки від ділення
  19. RADIANS.  Перетворює градуси на радіани
  20. ROMAN.  Перетворює число, записане арабськими цифрами, на римське

 

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

 

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

 

СТАТИСТИЧНІ

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

 

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

  1. DATE.   Повертає числове значення для вказаної дати
  2. DAYS360.  Обчислює кількість днів між датами на основі 360-денного року
  3. HOUR.  Перетворює числове значення дати на годину
  4. MINUTE.  Перетворює числове значення дати на хвилини
  5. MONTH.  Перетворює числове значення дати на місяць
  6. SECOND.  Перетворює числове значення дати на секунди
  7. TODAY.  Повертає числове значення для сьогоднішньої дати
  8. WEEKDAY.  Перетворює числове значення дати на день тижня
  9. 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 – на початку.
  • Спл — сплата, яка здійснюється кожного періоду; вона залишається незмінною протягом строку фінансової ренти. Зазвичай спл містить основну частину боргу й відсоток і жодних додаткових внесків або податків. Якщо аргумент спл не вказано, необхідно включити аргумент зв.
  • Поч_вартість — початкова вартість активу.
  • Зал_вартість — залишкова вартість активу вкінці терміну експлуатації.
  • Термін_експлуатації — кількість періодів амортизації активу (іноді називається періодом корисного використання активу).

 

Функції

  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 грн.

 

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

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

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

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

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

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

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

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

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

Розв’язок

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

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

 

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

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

 

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

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

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

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

 

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

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

 

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

Розв’язок

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

 

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

 

ПЕРЕВІР СЕБЕ

  1. Що таке функція?
  2. Як викликати функцію?
  3. Що таке аргументи функції?
  4. Назвіть математичні функції.
  5. Які функції відносяться до статистичних?
  6. Які функції належать до логічних?
  7. Які функції належать до фінансових?
  8. Як дослідити функцію?

 

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

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

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

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

Хід роботи

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

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 + x3 - 3)        при   

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

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

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

А

В

Відстань між крапками

Х1

У1

Х2

У2

3,5

1,78

7,8

3,87

?

5,7

3,4

9,7

6,5

?

 

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

АВ=

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

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

 

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

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

 

 

 

 

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

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

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

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

Хід роботи

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

Назва функції

Аргумент

Аргумент

Обрахунок

Середнє арифметичне

12

21

?

Кількість значень

13

26

?

Середнє геометричне

14

25

?

Максимальне число

14

23

?

Мінімальне число

15

47

?

Медіана чисел

17

12

?

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

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

 

Код лану

А, м.

В, м.

С, м.

Р, м

S, м2

Середня площа ланів

Сумарна площа всіх ланів

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 за допомогою функцій Дати і часу.

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

Хід роботи

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

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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

 

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

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

 

 

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

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

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

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

Хід роботи

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

 

A

B

C

D

E

1

Прізвище

Об’єм продаж

Комісійні 1

Комісійні 2

Кращий продавець

2

Іванов

9000

Формула 1.

Формула 2.

Формула 3.

3

Петров

23000

 

 

 

4

Сидоров

45000

 

 

 

5

Федоров

35000

 

 

 

6

Яковлев

45000

 

 

 

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

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

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

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

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

  1. Використовуючи функцію 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 =           x2+3cos x    , якщо х>0

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

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

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

 

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

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

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

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

 

 

 

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

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

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

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

Хід роботи

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

Функція для визначення майбутньої вартості теперішніх інвестицій – 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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

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

 

 

 

 

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

 

План

  1. Підбір параметру
  2. Пошук рішення

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

Скільки чашок кави по $1,75 необхідно продати, щоб прибуток складав $30 000?

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

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

  1. Підбір параметру

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

Щоб підібрати параметр потрібно:

  1. Виконати команду Дані/Знаряддя даних/Аналіз «якщо»/Підбір параметрів.
  2. З’явиться вікно (рис. 57), в якому потрібно вибрати:
    1. В полі „Установити у клітинці” – вказати адресу комірки з формулою,
    2. В полі „Змінюючи значення клітинки” – адресу комірки, значення якої потрібно буде змінювати до тих пір, поки формула не поверне результат, вказаний в полі Значення.
  3. Натиснувши ОК, буде виконано підбір параметру. Цей процес і його результат відобразиться у вікні діалогу „Результат підбору параметра” (рішення знайдене, значення, що підбиралося, поточне значення).

ПРИКЛАД

Бізнесмен відкрив кафе і в ньому продають сік по 10,80 грн. за стакан. Необхідно отримати виручку в 20 000 грн. Визначити скільки стаканів соку потрібно продати?

Розв’язання

  1. Введемо таку таблиці.

 

А

В

1

Ціна за стакан, грн.

10,80

2

Необхідно продати

 

3

Прибуток, грн.

 

  1. В комірку ввести формулу для визначення прибутку: =В1*В2.
  2. Виділити комірку, в якій міститься формула, і виконати команду Дані/Знаряддя даних/Аналіз «якщо»/Підбір параметрів.
  3. У відкрившомуся вікні в полі „Установити у клітинці” – комірка В3, в полі „Значення” – ввести ту величину, яка повинна бути повернена формулою – 20 000. , в полі „ Змінюючи значення клітинки ” -  комірку В2.
  4. Клацнути ОК. В результаті появиться повідомлення, а результат занесеться в комірку В2. Прогноз показав скільки потрібно продати стаканів соку.

2. Використання команди Пошук рішення

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

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

 

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

  1. Потрібно виділити на аркуші цільову комірку (з формулою основаною на шукаємих значеннях змінних комірок).
  2. Потім виконати команду Дані/Аналіз/Пошук рішення. Відкривається діалогове вікно  Пошук рішення (рис. 58). Оскільки на етапі 1 була виділена комірка, у текстовому полі Установити цільову комірку вже отримується вірне посилання. Крім того, у групі Дорівнює встановлений правильний перемикач Максимальному значенню, оскільки нас цікавить саме максимальне значення цільової комірки.

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

  1. При клацанні на кнопці Запропонувати Еxcel пробує самостійно вгадати склад змінних комірок, які входять в задачу. Для цього він передивляється формулу і вибирає комірки, на які є

посилання.

  1. Накладання обмежень не є обов’язковим для всіх задач оптимізації, але у цьому випадку  присутні три умови. Клацнути по кнопці Додати, щоб задати перше обмеження в діалоговому вікні Додавання обмеження.
  2. Далі треба клацнути по кнопці Додати, щоби ввести перше обмеження в текстове поле Обмеження значення.
  3. Потім потрібно клацнути по кнопці Додати, щоб ввести друге обмеження і перейти до третього і т.д.
  4. Далі клацнути по кнопці ОК, щоб занести всі обмеження в діалогове вікно Пошук рішення.
  5. Оптимізаційна задача готова до виконання. Можна клацнути по кнопці Виконати для отримання відповіді. Через декілька секунд з’явиться діалогове вікно з описом результатів процесу оптимізації. Якщо під час пошуку рішення з’явились проблеми, з’являється повідомлення про помилку; кнопка Довідка допоможе отримати додаткову інформацію. Якщо рішення знайдено відкриється діалогове вікно.

 

 

ПРИКЛАД

В кафе продається сік двох різновидів: гранатовий – 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.

 

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

 

ПЕРЕВІР СЕБЕ

  1. Для чого використовують Засіб „Підбір параметра”?
  2. Як відбувається робота по підбору параметра?
  3. Для чого виконується Пошук рішення?
  4. Що таке цільова комірка? Що в ній повинно бути?

 

 

 

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

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

Мета. Навчитися проводити обчислення у таблицях Excel за допомогою Засобу аналізу «Підбір параметру» і «Пошуку рішення».

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

Хід роботи

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

ЗАДАЧА № 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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

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

  1. Для чого використовують оптимізаційні задачі?
  2. Які є види оптимізаційних задач?
  3. Як скористатися засобом «Пошук рішення»?
  4. Що таке цільова комірка?
  5. Яким чином скористатися засобом «Підбір параметрів»?
  6. Як додати обмеження?

 

 

 

 

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

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

Мета. Розглянути вирішення задач на “Пошук рішення» та «Підбір параметрів».Засвоїти прийоми аналізу даних.

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

Хід роботи

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

 

ЗАДАЧА № 1

У зоопарку використовують два види  кормів: 100 г першого корму містить 2 г білків,  6 г жирів, 4 г вуглеводів і коштує  5 грн.; 100 г другого  корму містить 3 г білків, 2 г жирів, 9 г вуглеводів і коштує   4 грн. Потрібно скласти раціон  харчування тварини   в зоопарку  за умови, що вона має щодня отримувати   не менше  60 г білків, 80 г  жирів і 150 г вуглеводів, а вартість добової норми їжі повинна   бути  мінімальною.

Побудова математичної  моделі

  1. Позначення:     х1, х2 – кількість  кормів І і ІІ виду в грамах.

 

 

 

  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. Підготуватися теоретично по вказаних нижче питаннях і захистити свою роботу на оцінку.

 

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

  1. Які є види оптимізаційних задач?
  2. Як користуватися засобом «Підбір параметрів»?
  3. Як скористатися засобом «Пошук рішення»?
  4. Що таке цільова комірка?
  5. Як додати обмеження?
  6. Яким чином виводяться результати при вирішенні оптимізаційних задач?

 

 

 

 

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

 

План

  1. Створення списків
  2. Проміжні підсумки
  3. Диспетчер сценаріїв

1. Створення списків

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

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

Список складається із записів, причому кожний запис відповідає визначеному об’єкту, а  властивості об’єкту відображаються в полях запису. У вигляді списку побудований, наприклад, телефонний довідник. В ньому записом є кожний окремий рядочок, який відповідає конкретному об’єкту – абоненту, а полями є стовпці ПІБ, адреса, телефон.

При роботі в Excel записи формуються з рядків таблиці, а записи зі стовпців.

Форма для списків

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

Форма являє собою діалогове вікно для  створення і редагування записів. Щоб створити список у формі потрібно:

  1. Вказати в списку комірку, починаючи з якої будуть додаватися в список нові записи.
  2. Виконати команду Панель швидкого доступу/Форма і у відкрившомуся вікні клацнути кнопку Створити (рис. 62).
  3. В пусті поля форми ввести дані нового запису. Відмінити додавання запису можна клацанням по кнопці Відновити. У даному вікні є такі кнопки:
    1. Кнопки Знайти назад і Знайти Далі дозволяють переміщуватися по рядкам списку.
    2. Видалити – видаляє поточний рядок.
    3. Умови – визначає критерії пошуку рядків в cписку
  4. Заповнивши поля натиснути кнопку Створити Після цього запис буде введено в список, а поля будуть очищені для набору слідую чого запису. По закінченню створення списку натиснути кнопку Закрити.
  1. Підсумки

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

Для підведення підсумків потрібно:

1. Організувати список потрібно так, щоб записи кожної групи слідували у ньому підряд. Найпростішим способом

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

 2. Виконати команду Проміжні підсумки із категорії Структура вкладки Дані. Відкриється діалогове вікно Проміжні підсумки (рис. 63).

3. Далі потрібно вибрати із розкриваючого списку При кожній зміні в стовпчик, по якому буде виконано групування рядків. Це повинен бути той же стовпчик, по якому сортувався список.

4. Вибрати із списку Використовувати функцію статистичну функцію яка буде використовуватися для обчислення проміжного підсумку. Частіше за все застосовується  функція СУМА але є і інші (рис. 64).

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

6.Клацнути по кнопці ОК, щоб включити проміжні підсумки у список.

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

Режим структури, у якому опиняється список після виконання команди Проміжні підсумки, нагадує режим структури у Word і дозволяє продивлятись

Кнопки, розміщені у верхній частині лівого поля, визначають кількість виведених рівнів даних. Кнопки зі знаками + і - призначені для згортання і розгортання окремих груп. (рис. 64).

 

 

 

 

 

 

 

 

 

 

Рис. 64. Дані після підведення Проміжних підсумків

 

  1. Диспетчер сценаріїв

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

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

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

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

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

  1. На вкладці Дані у групі Знаряддя даних клацнути категорію Аналіз «якщо», а потім Диспетчер сценарію.

 

Рис. 65. Вікно Зміна сценарію

 

  1. Натиснути кнопку Додати.
  2. У вікні (рис. 65) у полі Назва сценарію ввести  назву сценарію.
  3. У полі Змінювані клітинки ввести посилання на комірки, які має бути використано у сценарії. Наприклад, якщо потрібно дослідити, як змінення значень клітинок B1 і B2 впливає на результат формули, введіть B1;B2.
  4. У розділі Захист вибрати потрібні параметри. Ці параметри застосовуються лише до захищених аркушів.
  5. Установити прапорець Заборонити зміни, щоб заборонити редагування сценарію, коли аркуш захищено.
  6. Установити прапорець Приховано, щоб заборонити відображення сценарію, коли аркуш захищено.
  7. Натиснути кнопку ОК.
  8. У діалоговому вікні Значення клітинок сценарію ввести потрібні значення змінюваних комірок для цього сценарію.
  9. Щоб створити сценарій, натиснути кнопку ОК.
  10. Якщо потрібно створити додаткові сценарії, повторити кроки 2-8. Після завершення створення сценаріїв натиснути кнопку ОК, відтак натиснути кнопку Закрити в діалоговому вікні Диспетчер сценаріїв.

Відображення сценарію

У результаті відображення сценарію відбувається перехід до набору значень, збережених як частина цього сценарію. Значення сценарію відображаються у клітинках, які змінюються від сценарію до сценарію, на додаток до клітинок результату. У нашому прикладі, якщо відобразити сценарій «Найкращий випадок», у клітинці B1 відобразиться 150000, у клітинці B2 — 26000, а у клітинці B3 — 124000.

  1. На вкладці Дані у групі Знаряддя даних клацнути елемент Аналіз «якщо», а потім Диспетчер сценаріїв.
  2. Клацнути ім’я сценарію, який потрібно відобразити.
  3. Вибрати команду Показати.

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

Створення зведеного звіту сценарію

  1. На вкладці Дані у групі Знаряддя даних клацнути елемент Аналіз «якщо», а потім Диспетчер сценаріїв.
  2. Натиснути кнопку Звіт.
  3. Вибрати варіант структура або зведена таблиця.
  4. У полі Клітинки результату ввести посилання на клітинки, значення яких були змінені сценаріями. Послідовні посилання розділити комами.

 

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