Обчислення формули і функції в MS Excel

1. Обчислення формули і функції в MS Excel

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

знаку рівності «=». У цю послідовність символів можуть входити: константи, адреси

осередків, функції, оператори.

Результат обчислень відображається в комірці, а сама формула – в рядку формул.

Функції використовуються для виконання стандартних обчислень. Excel має більше

400 вбудованих функцій, об'єднаних в 9 груп: фінансові, дата і час, математичні, статистичні,

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

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

користувача функції, які створюються за допомогою засобів Excel.

Правила роботи з формулами і функціями в MS Excel

Можна виділити наступні правила роботи з формулами і функціями:

1. Формули починаються із знаку «=»

2. Формули можуть включати звернення до однієї або до декількох функцій.

3. Після імені кожної функції в дужках задаються аргументи. Якщо функція не

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

4. Аргументи перераховуються через крапку з комою.

5. Як елемент формули і аргументу функції може виступати адреса комірки. В

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

6. У формулах недопустимі пропуски.

7. Функція не може мати більше 30 аргументів.

8. У формулі можна використовувати знаки арифметичних операцій: +, - /, *, ^

(зведення в ступінь), % ( узяття відсотка) (застосування 2).

9. Не можна вводити числа у форматах дати і часу дня безпосередньо у формули.

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

10. Довжина формули не повинна перевищувати 1024 елементів.

Спеціальне копіювання в MS Excel, яке можна виконати:

1. за допомогою меню:

- вибрати копійовану клітку або блок кліток;

- вибрати в меню Правка / Копіювати;

- перейти в нове місце;

- вибрати в меню Правка /Специальная вставка.

2. за допомогою контекстного меню:

- вибрати копійовану клітку або блок кліток;

- викликати контекстне меню;

- вибрати Копіювати;

- перейти в нове місце;

- вибрати Спеціальна вставка.

У діалоговому вікні, що з'явилося, залежно від прапора в стовпці Вставити

копіюється: все, формули, значення, формати, примітки, без рамки.

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

від прапора в колонці операції можна:

- просто копіювати;

- скласти вміст клітки з копійованим значенням;

- відняти із значення в клітці копійоване значення;

- помножити значення в клітці на копійоване значення;

- розділити значення в комірці на копійоване значення.

Якщо встановлені відповідні прапори, то при копіюванні можна:

- пропустити порожні комірки(не копіювати штрихування, бордюр, формат,

шрифт комірки, якщо в них відсутні значення);

- транспортувати;

- вставити посилання (при встановленні зв'язку з копійованим осередком, будь-

яка зміна початкового комірки приводить до зміни

результуючого комірки).

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

Копіювання вмісту клітки (або блоку кліток) включає наступні кроки:

- вибрати копійовану клітку або блок кліток;

- вибрати меню Правка / Копіювати або викликати контекстне меню і вибрати

Копіювати, або натиснути Ctrl+C;

- перейти в нове місце;

- вибрати меню Правка / Вставка або викликати контекстне меню і вибрати

Вставка, або натиснути Ctrl +V.

При копіюванні формула перенастроюється на нові адреси. Щоб при копіюванні

формули адреса деякого комірки була абсолютною ( не перенастроювався на нові адреси)

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

адресу у вигляді, наприклад $А$4.

Майстер автозаповнення  клітинок в MS Excel

При переміщенні формули в нове місце таблиці посилання у формулі не змінюються.

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

- ввести в клітку перше значення;

- зробити цю клітку активною;

- встановити покажчик миші в правий нижній кут клітки (курсор при цьому

прийме вигляд +);

- перетягнути убік або вниз ( крок рівний 1).

Якщо необхідно задати крок, відмінний від 1, то слід виконати:

- задати два значення в двох сусідніх клітках;

- ці клітки виділити;

- встановити курсор в правий нижній кут виділених кліток так, щоб він прийняв

вигляд +;

- перетягнути курсор убік або вниз.

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

копіювання.

Створення своєї послідовності для заповнення

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

Сервіс / Параметри / Списки / Елементи списку

У опції Списки вибрати опцію Новий список. У опції Елементи списку внести вільну

послідовність, потім клацнути по клавіші Додати (введена послідовність при цьому

запам'ятовується).

Якщо потрібна послідовність вже існує в робочому листі, то її досить виділити і

передати в це вікно, клацнувши на кнопці Імпорт.

Майстер функцій в MS Excel

Функції покликані полегшити роботу при створенні і взаємодії з електронними

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

Скористаємося такою операцією для демонстрації переваг функцій. Не використовуючи

систему функцій потрібно буде вводити у формулу адресу кожного осередку(комірки)

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

чином: =B1+B2+B3+C4+C5+D2

Помітно, що на написання такої формули пішло багато часу, тому здається що простіше

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

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

знака суми або з Майстра функцій, можна і уручну удрукувати ім'я функції після знака

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

дужку. В результаті формула буде виглядати таким чином: =СУММ(B1:B3;C4:C5;D2)

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

Комі розділяються аргументи функцій. Використовування блоків осередків, або областей, в

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

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

потрібно підрахувати суму чисел в осередках з А1 по А4. Це можна записати так:

=СУММ(А1;А2;А3;А4)

При введенні формули звернення до вбудованої функції можна виконати уручну або

викликати Майстер функцій. Цю ж операцію можна виконати через меню Excel: вибрати в

меню Вставка / Функція. Або на панелі інструментів або на панелі формул вибираємо

інструмент fx. У лівому вікні вибрати категорію функції, в правом – саму функцію.

Вибравши кнопку крок > виконати покрокову підстановку аргументів. Значення аргументу можна записати уручну, а можна виділити на листі діапазон кліток. Результат обчислень

отримаємо, клацнувши на кнопці Закінчити.

Питання для самоконтролю  з теми MS Excel

1. Способи і технологія запуску програми Excel

2. Способи і технологія виходу з Excel

3. Назвати основні компоненти екрану Excel.

4. Призначення і структура:

- рядки заголовка;

- рядки основного меню;

- панель інструментів Стандартна;

- панель інструментів Форматування;

- рядки формул і поля імені;

- смуги прокрутки;

- смуги перебору сторінок робочої книги;

- рядок стану;

5. Які кнопки управління вікном використовуються при роботі з ним.

6. Склад основного меню Excel, структура і призначення кожної команди.

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

меню.

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

9. Панель інструментів Форматування – її призначення, структура і склад

інструментів.

10. Які можливості представляє настройка панелі інструментів.

11. Як створити власне піктографічне меню і додати в нього піктограму

12. Призначення робочих таблиць і де вони зберігаються.

13. Що миназиваємо робочою книгою

14. Структура робочої таблиці в Excel і переміщення по ній.

15. Способи виділення кліток і їх технологія виконання.

16. Типи даних, в Excel.

17. Що містить кожен тип даних.

18. Як задати або змінити формат комірки.

19. Процес введення даних в осередок.

20. Як відредагувати вміст комірки.

21. Процес видалення вмісту комірки.

22. Способи переміщення даних в іншу позицію.

23. Як вставити рядок в таблицю.

24. Заміна даних в клітці.

25. Як заховати робочий лист і його відновити за допомогою меню Excel і контекстного

меню.

26. Як заховати колонку або рядок, а також їх відновити за допомогою меню Excel і

контекстного меню.

27. Що ми розуміємо під формулою і які символи вона може містити.

28. Для чого призначені функції. Скільки їх на які групи розбиті.

29. Перерахувати правила роботи з формулами і функціями.

30. Процес переміщення і копіювання клітки.

31. Що означає спеціальне копіювання і його виконання здопомогою меню Excel і

контекстного меню.

32. Як здійснити автоматичне заповнення клітки.

33. Призначення Майстра функції.

34. Процес звернення до вбудованої функції за допомогою Майстра функцій і через

меню Excel.

35. Як змінити шрифт в текстовому документі за допомогою меню.

36. Способи вирівнювання вмісту осередків.

37. Як виконати обрамлення осередків.

38. Видалення і відновлення сітки з екрану.

39. Що розуміємо під фоном комірок?

40. Як залити комірки кольором.

41. Як розуміти «стиль представлення даних».

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

43. Як встановити параметри сторінки.

44. Як записати табличний документ на диск.

45. Процес перезапису існуючого файлу на диск.

46. Як викликати документ на екран.

47. Створення нового табличного документа.

48. Виведення документа на друк.

49. Що таке діаграма і які типи діаграм існують.

50. Технологія побудови діаграм.

51. Виведення діаграми на друк (на новому листі).

Створення макросів

Макрос – це набір інструкцій, які задають набір послідовних дій, які Microsoft вионує замість

вас.

 Макроси по своєму запису – це комп’ютерні програми, які виконуються і працюють

тільки в середовищі Excel. Макроси призначені для швидкого виконання послідовності дій.

Макроси бувають простими і складними. Вони можуть працювати в інтерактивному ритмі.

 Існує два способи макросів:

1. автоматичний запис послідовності своїх дій;

2. інструкції вводяться в ручну на окремому листі Excel, який називається модулем.

Процес запису макросу складається з трьох кроків:

- активізується режим запису макроса;

- присвоюється йому ім’я;

- виконуються дії, які потрібно записати.

При записі макросу Excel записує всі дії і вставляє в модуль відповідні інструкції

Visual Basic. Для того, щоб почати новий модуль виберіть в меню Сервіс/Макрос/Макрос.

Після чого в діалоговому вікні макрос виділіть ім’я макроса, який був створений і натисніть

кнопку „изменить” і з’явиться модуль дій, який містить вибраний макрос.

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

вікні запис макросу. Такою комбінацією може бути будь-яке сполучення кнопок, тобто Ctrl і

прописна буква.

Макроси можуть застосовуватись для виконання таких дій : відкривання і закривання

таблиць, запитів, форм, звітів; фільтрування, пошук та перехід до певного запису БД;

виведення на екран інформаційних повідомлень; подачу звукового сигналу; запуск та вихід з

додатків та ін. Найчастіше д ля виконання дій, що часто повторюються, і використовують

макроси.

2. Елементи керування

Елемент керування – об'єкт графічного інтерфейсу користувача (такий як поле,

прапорець, смуга прокручування або кнопка), що дозволяє користувачам керувати додатком.  19

Елементи керування використовують для відображення даних або параметрів, для виконання

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

В Microsoft Excel існує два види елементів керування. Елементи керування ActiveX,

які підходять в більшості випадків і працюють з макросами Visual Basic для додатків (VBA)

та з WEB-сценаріями. Елементи керування з панелі Формы, які сумісні з ранніми версіями

Microsoft Excel, починаючи з версії 5.0, та можуть використовуватися на листах макросів

XLM.

Елементи ActіveX використовуються для більшості інтерактивних форм, особливо у

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

керування. Наприклад, на аркуш можна додати вікно списку, а потім написати макрос, що

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

керування ActіveX аналогічні елементам керування мов програмування, таких як Mіcrosoft

Vіsual Basіc, використання даних елементів вимагає знання цієї мови програмування для

написання макросу.

Елементи керування панелі інструментів Формы варто використати, якщо потрібно

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

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

користувачами за допомогою Mіcrosoft Excel. Елементу керування можна призначити

наявний макрос або написати чи записати новий макрос. Коли користувач форми клацає на

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

Елементи керування панелі інструментів Формы перебувають на панелі інструментів

Формы, яку можна викликати скориставшись командою ВидПанели

инструментовФормы.

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

ньому правою кнопкою миші, та вибрати з контекстного меню команду Формат обьекта, а

потім відкрити вкладку Элемент управления. Кнопки й написи не мають властивостей.

Зв’язок елемента з коміркою використовується для отримання логічного чи

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

3. VBA основні положення

Відкрити Сервіс/Макрос/Редактор Visual Basic, або Alt+F11

Закрити Alt+Q

VBA- Visual Basic for Applications –це програмний додаток створений спеціально для

роботи з додатками Microsoft Office.

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

Макрос – це набір інструкцій, які повідомляють програмі які дії потрібно виконати що

досягнути визначеної мети.

Макрос об’єднує інструкції в один сценарій. Список інструкцій складається з

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

Процедурою називається набір зв’язаних операторів, які утворюють один блок і

виконують певну задачу.

Процедури поділяють на дві групи: Командні макроси (або просто макроси) і

визначені користувачем Користувацькі функції (або просто функції)

Командні макроси найбільш поширені вони містять оператори які рівнозначні

командам меню чи іншим командам командний макрос може впливати на поточний

документ.

Макрос створюють двома способами ручним і автоматизованим. При ручному

запуску багато можливостей програми будуть недоступні.

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

відкрити додаток в якому будете працювати виберіть команду сервіс-макрос-почати

запись, в полі ім’я макроса за замовченням визначається стандартне ім’я макроса Макрос1,

його можна замінити будь-яким (не допускається використання в імені пробелів та крапок

імя довжиною не більше 255 символів першим символом має бути буква або_)  20

призначити комбінацію клавіш(необов’язково)

зберегти в (місце знаходження макроса )

задати опис макроса

провести запис макроса

зупинити запис макроса

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

або ж сервіс – макрос - макроси-вибрати зі списку потрібний-кн. Виконати.

Щоб переглянути потрібний макрос в його лістинговому записі сервіс-макрос-

макроси-вибрати потрібний – изменить ( або команда войти)

Макрос містить наступні елементи:

Ключове слово Sub (subrontine - підпрограма) –позначає початок макроса End sub -

кінець макроса відображається синім

Ім’я макроса – оригінальна назва макроса що дозволяє його розпізнавати серед інших

(за замовченням при авто записі Макрос1) чорним

Коментарі – інформація про макрос та його команди, які від тіла програми

відділяються ‘ ‘ відображається зеленим кольором

Макрооператори - основне тіло макроса - складаються з послідовності операторів і

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

чорним кольором.

Редактор VBA містить наступні елементи вікно проектів – Project де відображаються

модулі, форми, і об’єкти пакету Microsoft Office, вікно властивостей Properties - , вікно

обробки подій який містить список об’єктів - Objekt list, список процедур – Procedure list.

Макрос відображається в модулі. Модуль – це елемент VBA який містить запис

кількох макросів чи процедур

Питання для контролю

1. Які види елементів управління ви знаєте?

2. Як викликати панель інструментів Форми?

3. Як викликати панель інструментів Елементи управління?

4. Які кнопки відображені на панелі інструментів Форми?

5. Які кнопки відображені на панелі інструментів Елементи управління?

6. Макрос – це…

7. Як запустити макрос на виконання?

8. Як створити макрос?

9. Як виправити назву макросу?

10. Як здійснити редагування макросу?

11. Де записується лістинг макроса?

12. Які комбінації клавіш назначається макросам?

13. Які відомості містить вікно запису макроса?

14. Модуль -це...

15. Макрооператори –це..

16. Імя макроса –це..

17. Імя макроса відображається у вікні...

18. Як змінити імя макроса?

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

20. VBA –це ....

21. Види елементів що підтримують виконання макросу?