Лекція №1.
Тема: Вступ до VBA. Базові відомості по роботі з електронними таблицями Microsoft Excel.
Електронні таблиці Microsoft Excel входять до складу пакета Microsoft Office і використовується у багатьох сферах діяльності, де початкові дані та результати являють собою таблиці. Це одна з найкращих програм, створених фірмою Microsoft. Проте далеко не всі користувачі застосовують у своїй роботі мову програмування VBA – (Visual Basic for Application), а значить більшість реальних можливостей Excel залишається невикористаною.
Базові відомості по роботі з електронними таблицями Microsoft Excel.
Виклик електронних таблиць здійснюються за допомогою меню Пуск – Все програми - Microsoft Office - Microsoft Excel 2007.
Малюнок 1
Оформлення вікна відрізняється
від попередніх версій. Розробники
нарешті вирішили проблему захаращеності
робочого поля і необхідності довго
шукати необхідний пункт меню. Замість
рядка меню присутні вкладки Главная,
Вставка, Разметка страницы, Формулы,
Данные, Рецензирование, Вид, Разработчик.
Вибираєш одну з них і маєш в розпорядженні
всі необхідні інструменти у вигляді
значків на горизонтальній лєнті вздовж
верхньої границі вікна програми, які
сгруповані в групи (Буфер
обмена, Шрифт, Выравнивание…).
На відміну від панелі інструментів
добавити чи видалити з неї значок
неможливо. Деякі групи мають кнопку
діалогового вікна
у правому нижньому куті програми, при
натисканні на яку відкривається вікно
з усіма параметрами групи. Завдяки
такому рішенню у користувачів, які
звикли до інтерфейсу попередніх версій,
не виникне почуття дискомфорту. Якщо
підвести курсор миші до цієї кнопки, то
побачимо зменшену копію вікна а також
підказку про його застосування.
У
Малюнок 2
,
на якій знаходяться
команди роботи з файлами
(створення , збереження,
відкриття,закриття, друку) список
останніх документів, що розглядалися
і кнопка
,
яка дає доступ до вікна налаштувань
програми.
Поряд з кнопкою Office
знаходиться панель швидкого доступу
,
на якій розташовані кнопки операцій,
що часто використовуються (збереження
, відміни останньої дії…). ЇЇ можна
роташувати й нижче лєнти, а також
налаштувати на свій смак. Для цього
викликається контекстне меню, з якого
вибирається команда Разместить
панель быстрого доступа под лентой
або Настройка панели быстрого доступа.
Доповнити панель швидкого
доступа іншими
командами можна, натиснувши праву кнопку
миші на потрібній кнопці, що розташована
на лєнті, і вибрати команду Добавить
на панель быстрого доступа.
Вікно Excel містить багато
елементів, властивих програмам Windows,
але, зрозуміло, є елементи, що належать
тільки цьому додатку. Наприклад, Ярлики
листів з ім'ям робочих
листів
,
розташовані ліворуч від смуги
горизонтального прокручування. Кнопки
прокручування ярликів
,
розташовані ліворуч від ярличків робочих
листів.
Вони допомагають швидко перебрати листи
робочої книги. Рядок
формул, у якій уводяться
текст, числа чи формули. Розташований
він над основною робочою областю (мал.
3).
Малюнок 3
Рядок формул складається з адресного віконця (поле «Ім'я»), кнопки «Вставити функцію» і вікна уведення формул.
Якщо рядок формул за якимись причинами не відображається у вікні програми при її запуску, то для його активації треба зробити наступне:
У вікні відкритої книги перейдіть до вкладки «Вид».
У групі «Показати або сховати» активуйте значення «Рядок формул»
Файл Microsoft
Excel
називається робочою
книгою (Workbook).
Файли книг Microsoft Excel
мають розширення xlsx
і значок
Робоча книга складається з робочих листів (Worksheet), імена яких (Лист1, Лист2, …) виведені на ярликах у нижній частині вікна робочої книги . Клацаючи по ярликах, можна переходити від листа до листа усередині робочої книги. Робочі листи можна видаляти, додавати, перейменовувати, копіювати і переміщати, використовуючи праву клавішу мишки для виклику контекстного меню на ярлику з назвою листа.
Робочий лист являє собою таблицю, що складається з стовпців і рядків. Стовпці іменуються латинськими буквами(A, B, C,… Z, AA, AB,…IV,…), а рядка – цифрами. На перетині рядків і стовпців знаходиться комірка. Кожна комірка таблиці має адресу, що складається з імені рядка й імені стовпця. Наприклад, якщо комірка знаходиться в стовпці F і рядку 7, то вона має адресу F7. Таку адресу називають відносною. Адреса прямокутного блоку (діапазону) комірок складається з адреси комірки, що знаходиться у лівому верхньому куті блоку, двокрапки (оператору діапазону) і адреси комірки, яка стоїть у правому нижньому куті, наприклад С4:D6 – звертання до комірок С4, С5, С6, D4, D5, D6. Крапка з комою – означає об’єднання комірок, наприклад, D2:D4;D6:D8 –звертання до комірок D2, D3, D4, D6, D7, D8. Пробєл визначає сумісні комірки
Адреси використовують для посилань на комірки і блоки у формулах або в якості аргументів функцій. Адреси можна набирати з клавіатури. Можна також використовувати для цього мишу, виділяючи комірку або діапазон.
Стилі посилань
В МS Excel існують посилання двох стилів: «А1» (за замовчуванням) і «R1C1» (R від англійського Row – рядок, C від column –стовбець. R5C2 – посилання на комірку В5). Щоб вибрати тип посилань необхідно:
Кнопка Ofice – Параметри Excel – розділ Формули – Область Робота з формулами - прапорець Стиль ссылок R1C1.
Повні імена комірок
Для зазначення повного імені комірки до її адреси дописують ім’я листа (електронної таблиці), в якій вона знаходиться, і назву книги (файлу) у такому форматі [Книга1]Лист1!А1. Ім’я листа не може містити символів «\», «/», «*», «:», «?», «!», «[» та «]». Максимальна довжина імені листа 31 символ.
Виділення елементів таблиці
Одна з комірок таблиці завжди є активною. Активна комірка виділяється рамкою. Щоб зробити комірку активною, необхідно клавішами управління курсором підвести рамку до цієї комірки чи клацнути в ній мишею.
Для виділення декількох суміжних комірок необхідно установити покажчик миші в одну з комірок , натиснути ліву кнопку миші і, не відпускаючи її, розтягти виділення на всю область. Для виділення декількох несуміжних груп комірок варто виділити одну групу, натиснути клавішу Ctrl і, не відпускаючи її, виділити інші комірки .
Щоб виділити цілий стовпець чи рядок таблиці, необхідно клацнути мишею на його імені. Для виділення декількох стовпців чи рядків варто клацнути на імені першого стовпця чи рядка і розтягти виділення на всю область.
Для виділення декількох листів необхідно натиснути клавішу Ctrl і, не відпускаючи її, клацати на ярликах листів.
Заповнення комірок
Для
введення даних в комірку необхідно
зробити її активної і ввести дані з
клавіатури. Дані з'являться в комірці
й у рядку редагування формули (мал.4).
Для завершення уведення варто натиснути
Enter,
одну з клавіш управління курсором або
кнопку
“Ввод”
у рядку формул. Процес уведення даних
закінчиться й активною стане сусідня
комірка. Для відміни уведених даних
натискається кнопка
“Отмена”.
Малюнок 4
Щоб відредагувати дані в комірці , необхідно:
зробити комірку активною і натиснути клавішу F2 чи двічі клацнути в комірці мишею;
в комірці з'явиться текстовий курсор, який можна пересувати клавішами управління курсором;
відредагувати дані;
вийти з режиму редагування клавішею Enter.
Увага! Перед виконанням будь-якої команди Microsoft Excel варто завершити роботу з коміркою, тобто вийти з режиму введення чи редагування.
Типи даних
У таблиці MS Excel можна вводити два види даних: константи і формули.
Константи поділяються на три категорії: текстові значення, числові значення та значення дат і часу.
Текстові значення можуть містити будь-які символи. Для вводу тексту виділіть комірку, наберіть на клавіатурі потрібний текст і натисніть клавішу <Enter>.
Числові значення можуть містити тільки цифри від «0» до «9», спеціальні символи «+», «–», «Е», «е», «(», «)», «.», «,», «%», «/» і позначення грошових одиниць. Для вводу числа виділіть комірку, наберіть на клавіатурі число і натисніть клавішу <Enter> або кнопку Ввод в рядку формул.
Дату можна ввести в одному з форматів:
ДД.М.РРРР, ДД.МММ.РР, ДД.МММ, МММ.РР,
а час — в одному з форматів:
ЧЧ:ММ, ЧЧ:ММ:СС.
Дату і час можна ввести в об’єднаному форматі:
Д.ММ.ГГ Ч:ММ.
Дата вводиться через слэш / або крапку.
Діапазон комірок можна заповнювати за рядками, натискаючи клавішу <Таb> для переходу в комірку праворуч, або за стовпцями, натискаючи клавішу <Enter> для переходу в комірку нижче заповненої.
Форматування даних
Виконати
форматування комірок робочого листа
в більшості випадків можна на вкладці
Число
діалогового
вікна Формат
ячеек,
що викликається
командою
Формат
ячеек з
контекстного меню або
натискаючи
кнопку діалогового вікна групи
на вкладці Главная. Щоб застосувати
потрібний формат, виберіть його у списку
Числовые
форматы
і
натисніть кнопку ОK.
Якщо формат має додаткові параметри,
то після його вибору на вкладці з’являються
поля, в яких можна вказати ці параметри.
Загальний формат. Якщо явно не змінювати формат комірки, то Excel застосовує до неї формат Общий, який відображає дані в такому вигляді, як вони вводяться.
Числовий формат. Формат Числовой дозволяє встановлювати кількість відображуваних десяткових знаків, змінювати вигляд від’ємних чисел (наприклад, виділяти їх червоним кольором).
Грошовий формат. Формат Денежный має усі можливості форматування формату Числовой, а також дозволяє вибирати грошову одиницю.
Фінансовий формат. Формат финансовый подібний до грошового; він має додаткові можливості вертикального вирівнювання грошової одиниці і числа.
Відсотковий формат. При застосуванні до числа формату Процентный десяткова кома зміщується в числі на два знаки праворуч, а в кінці числа виводиться знак відсотків «%».
Дробовий формат. Формат Дробный дозволяє виводити числові значення у вигляді звичайних дробів. Вигляд дробу можна виберіть із списку Тип, який відкривається після вибору формату Дробный.
Експоненційний формат. У форматі Експоненциальный числа подаються у вигляді аЕ
n,
де а —
ціле число або десяковий дріб (мантиса),
n — ціле
число (порядок). Зображення аЕ
n
означає, що
в комірці знаходиться число a×10±n.Текстовий формат. Застосування до комірки текстового формату означає, що число або формула записані в комірці, будуть вважатися текстом.
Формат дати. Формат відображення дати в комірці можна вибрати із списку Тип формату Дата або визначити його при вводі дати, як описано вище в п. «Типи даних».
Обчислення в таблицях виконуються за допомогою формул. Формула може складатися з математичних операторів, значень, посилань на комірку і імен функцій. Результатом виконання формули є деяке нове значення, що міститься в комірці, де знаходиться формула. Усі формули в Excel починаються символом «=». У формулах можна використовувати такі операції:
Арифметичні: додавання «+», віднімання «–» , множення «*», ділення «/», піднесення до степеня «^», відсоток % ;
Відношення:>, >= (не менше),<, <= (не більше), =. <> (не дорівнює)
Конкатенації (зчеплення) рядків «&»
Оператор посилання. Для опису посилань на діапазони комірок використовуються наступні оператори
: (двокрапка) Ставиться між посиланнями на першу й останню комірки діапазону. Наприклад (B5:B15)
; (крапка з комою) Оператор об'єднання. Поєднує кілька посилань в одне посилання (СУМ(B5:B15;D5:D15))
(пробіл) служить для посилання на сумісні комірки двох діапазонів (B7:D7 C6:C8)
Операндами можуть бути:
числа;
тексти (вводяться в подвійних лапках, наприклад “константа”);
логічні значення (наприклад «ИСТИНА» та «ЛОЖЬ»,умови типа А2=А5);
значення помилок (типа #ДЕЛ/0 (ділення на 0), #Н/Д (нема даних або значення недоступні), #ИМЯ? (не розпізнається ім’я), #ПУСТО (задано перетин двох областей, які не мають спільних комірок), #ЧИСЛО (невірне числове значення), #ССЫЛКА (невірне посилання), #ЗНАЧ (недопустимий тип аргументу чи посилання));
посилання - адреси комірок ( при перерахуванні посилання розділяються крапкою з комою, для завдання діапазону використовується двокрапка);
вбудовані функції MS Excel
імена або заголовки стовпців (рядків) таблиці.
Формула обчислюється зліва направо, відповідно до встановленого пріоритету
Пріоритет оператора
Оператор |
Опис |
: (двокрапка) (один пробіл) ; (крапка з комою) |
Оператори посилань. |
– |
Знак «мінус» |
% |
Відсоток |
^ |
піднесення до степені |
* і / |
Множення й ділення |
+ і – |
Додавання й віднімання |
& |
Об'єднання двох текстових рядків в один |
= < > <= >= <> |
Порівняння |
Примітка. виклик функції і дужки (мають найвищий пріоритет, тобто виконуються в першу чергу)
Якщо розміри комірки недостатні для розміщення результату, то у ній з’являються значки діезів ####. В такому випадку потрібно розширити границю стовпця, у якому знаходиться ця комірка.
.
Види адресації
Основними засобами автоматизації обчислень в MS Excel є автоматичне переобчислення всієї таблиці при зміні будь-яких вхідних даних і можливість копіювання однотипних формул. Останнє забезпечується правилом відносної адресації комірок: при копіюванні формули MS Excel автоматично змінює відносні адреси, на які є посилання у формулі, згідно з розташуванням копії відносно оригінальної комірки. Якщо посилання на комірку не повинно змінюватися при копіюванні, то використовують абсолютну адресу комірки. Абсолютне посилання створюють з відносного вставкою символу «$» перед позначенням стовпця і / або номером рядка з клавіатури або за допомогою клавіші F4. Наприклад, $А$1 і $Е$5 — абсолютні адреси комірок А1 і Е5. Іноді використовують змішані адреси, коли постійною є одна частина адреси, наприклад, А$1 або $А1. При редагуванні адреси у формулі циклічну зміну вигляду посилання здійснюють за допомогою повторного натискання клавіші <F4>.
Використання функцій
Функції — це програми, що за готовими формулами реалізують обчислення над аргументами, які задаються користувачем згідно правил запису (синтаксису) функції. Доступ до функції здійснюється за її іменем. Аргументи функцій записують у круглих дужках через крапку з комою. Ними, зокрема, можуть бути числа, посилання на комірки та діапазони, імена, текст, логічні значення, значення помилок, вкладені функції тощо. MS Excel містить приблизно 400 стандартних функцій, які умовно поділяють на окремі категорії:
математичні: SIN, EXP, LN, ABS, КОРЕНЬ, СУММ, тощо;
статистичні: МИН, МАКС, СРЗНАЧ, ТЕНДЕНЦИЯ тощо;
логічні: ИСТИНА, ЛОЖЬ, ЕСЛИ, ИЛИ, И тощо;
фінансові: БЗ, ПЗ, НПЗ тощо;
інженерні: МНИМ.СУММ, МНИМ.ABS, ДЕЛЬТА, ФОШ тощо;
інформаційні: КОМІРКА, ТИП ОШИБКИ, ИНФОРМ тощо;
дати і часу: ГОД, ДЕНЬ, СЕКУНДЫ, РАБДЕНЬ тощо;
управління базами даних: БДСУММ, БДПРОИЗВЕД тощо;
для роботи з текстом: ЗНАЧЕН, ТЕКСТ, СТРОЧН та інші.