Використання розширених можливостей табличного редактора Microsoft Excel

Принципи побудови та сфери застосувань електронних таблиць

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

Існують розновиди программ ЕТ: самостійні та програми, які входять до складу прикладного програмного забезпечення, таких як Microsoft Office, Open Office.org (програми Excel, Calc). Розглянемо роботу з ЕТ на прикладі офісних програм. Програми Excel і Calc мало чим відрізняються одна від одною за структурою та діями, які виконуються над даними, також мають практично однаковий зовнішний вигляд, онаковий набір команд. Але є деякі різниці: так  у Calc інструмент Анализ данных має обмежені можливості, декілько відрізняються вікна Мастеров функций и диграмм. Розглянемо роботу у програмі Excel і наведемо особливості у роботі з програмою Calc.  

1.1.Загальні положення

Завантаження програми ЕТ.  Стандартний запуск здійснюється з головного меню

Пуск Программы Microsoft Office MS Excel  (OpenOffice.org OpenOffice.org Calc), або з допомогою кнопок відповідної офісної панели або ярликів на Рабочем столе.

Структура ЕТ. ЕТ складається з клітинок, що утворюють рядки і стовпці. Стовпці таблиці позначені буквами (А, В, С, …, Z, АА, АВ, …, AZ, ВА, …), а рядки — цифрами (1, 2, …). Кожна клітинка має ім’я, яке складається з літери стовпця і номера рядка, на перетині яких розташована клітинка, наприклад  А1. Стовпців може бути до 256, а рядків — до 65536 (Calc  – 32000).

Клітинки утворюють таблицю, яка міститься на сторінці і має назву за умовчанням  Лист 1, Лист 2 тощо, назва якою відображена на бірці внизу екрана. За умовчанням їх має бути три або може бути збільшено до 255. Сторінки можуть бути декількох типів: саме таблиць, диаграмм, програмного коду VBA (Visual Basic for Application) або окно діалога. Ім’я листа можно змінювати, але воно не може містити символів “\”,”/”, “*”,”:”,”?”,”!”,”[“, “]” і його довжина не повинна перевищувати 31 символа.

Декілька сторінок утворюють книгу, яка має назву Книга 1 (Саlс – Без имени 1). Книга зберігається у файлі з розширенням  .xls (Саlс – .sxc).

Адресація. В ЕТ у формулах та функціях для посилання на клітинки і блоки клітинок використовують імена клітинок як адреси. Існує три вида адресів: відносні, абсолютні та змішені. Відносні адреси змінюються при копіюванні формул і мают вигляд просто адреси – А1, ящо посилання на клітинку не повинно змінюватися при копіюванні, то використовують абсолютну адресу клітинки, наприклад, $А$1, тобто додається символу “$” перед позначенням стовпця і номером рядка. Іноді використовують змішані адреси, коли постійною є одна частина адреси, наприклад А$1 або $А1. При редагуванні адреси у формулі циклічну зміну вигляду посилання здійсню­ють за допомогою клавіші F4.

Адреса прямокутного блоку (діапазону) клітинок, сумісного, складається з адреси лівої верхньої клітинки блоку, двокрапки (оператора діапазону) і адреси правої нижньої клітинки, наприклад А1:Е5. Якщо треба  описати не сумісний діапазон клітинок, то використовують «;», наприклад,  А1;В3;Е5.

Адреси використовують також повні імена (адреси) клітинок. Для зазначення повного імені клітинки до її адреси дописують ім’я листа, де вона розташована, і назву книги (файлу):

[Книга 1]Лист1!А1.    

 

1.2.Типи даних, введення та форматування даних у ЕТ

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

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

Числові дані використовуються для введення числових значень у різному відображенні, для  цього використовують цифри від 0 до 9 та спеціальні символи “+”, “-“, “Е”, “е”, “(“,  “)”, “.”,  “,”, “%”,  “/”  i позначення грошових одиниць.

Формули призначені для виконання дій над вмістом клітинок (над даними) згідно з умовою конкретної задачі. Усі формули починаються символом “=“. У формулах можна використовувати такі операції: додавання “+”, віднімання”-“, множення “*”, ділення “/”, піднесення до степеня “^”. Операндами у формулі можуть бути числа, адреси клітинок, функції або заголовки стовпців (рядків) таблиці. Для зміни стандартного порядку виконання операції застосовують круглі дужки.

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

Щоб побачити всі формули у таблиці, треба задати режим відображення формул у клітинках  СервисàПараметры…à вкладка Вид à опція Параметры окна  àФормулы

                      (àOpenOffice.org Calc àВид àФормулы).

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

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

Щоб увести в клітинку дане, її виокремлюють, набирають дане на клавіатурі та натискають на клавішу вводу Enter або на клавішу клавішу Tab або кнопку Ввод у Строка формул.

Дані активної клітинки та її адреса відображаються у Строка формул. Під час введення дане можна редагувати. Уведений у клітинку текст (до 255 символів) автоматично вирівнюється до лівого краю, а числа — до правого. Якщо почати вводити нове дане у клітинку, то старе пропадає.

Якщо poзмip тексту перевищує розмір клітинки, то для його відображення використовують клітинки, розміщені праворуч. Зберігається текст тільки в одній клiтинці. Його відображення в сусідніх клітинках зникає, якщо в ці клітинки вводяться дані. Для відображення в одній клітинці кількох рядків тексту виконайте команду Формат à Ячейки...à вкладка Выравнивание à  переносить по словам. Висота рядка збільшиться i текст розміститься на додаткових рядках всередині клітинки.

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

–  двічі клацають мишею;

–  натискають на клавішу F2;

–  застосовують рядок формул.

Вилучити з клітинки дане, примітку, формат даного можна ПравкаàУдалить содержимое… àУдалить все.

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

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

Формати чисел у вибраних клітинках задають командою ФорматàЯчейки…à вкладка Числа àЧисловые форматы вибрати потрібний формат. Якщо формат має додаткові параметри, то після його вибору на вкладці з’являються поля, в яких можна зазначити ці параметри.

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

Общий  (Calc –Все)  формат  відображає дані в такому вигляді, як вони вводяться.

Числовой формат дає змогу встановлювати кількість відображуваних десяткових знаків, змінювати вигляд від’ємних чисел (наприклад, виділяти їх червоним кольором).

Формат Денежный має вci можливості форматування формату Числовой, а також дає змогу вибирати грошову одиницю.

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

Процентный формат при застосуванні до числа формату  десяткова кома зміщується в числі на два знаки вправо, а в кінці числа виводиться знак відсотків “%”.

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

Экспоненциальный (Calc –Научный) формат. У форматі числа подаються у вигляді аЕ ± п, де а — ціле число або десятковий дріб (мантиса); п — ціле число (степінь, порядок). Зображення аЕ ± п означає, що в клiтинцi розміщується число а*10±п.

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

Дата та Время. Формати відображення дати або часу, Дату можна ввести в одному з форматів Д.М.ГГ, а час — в форматі Ч.ММ.СС.

Дополнительный формат (Calc -Пользовательский). Створюється користувачем, для таких даних, як номер телефону, індекс тощо.

Також до форматування відносяться такі дії, як: вирівнювання вмісту клітинок, варіанти розміщення даних у клітинці, все це задається у вікні ФорматàЯчейки…à вкладка Выравнивание .

За замовчуванням для горизонтального вирівнювання застосовується варіант по значению, згідно з яким числові значення вирівнюються відносно правого краю клітинки, а текстові — відносно лівого. Серед решти шести вapiaнтiв відокремимо вирівнювання по центру выделения, згідно з яким текст центрується відносно вcix виділених праворуч незайнятих клітинок. Список по вертикали містить чотири варіанти вертикального вирівнювання тексту: по верхнему краю, по центру, по нижнему краю, по высоте.

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

Якщо ширини стовпця недостатньо для відображення вмісту клiтинок, її можна змінити одним з таких способів:

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

–        виділіть будь-яку клітинку потрібного стовпця або весь стовпець, клацнувши на його заголовку, i виконайте команду Формат à Столбец àАвтоподбор ширины;

– ФорматàЯчейки…à вкладка Выравниваниеà Отображение àавтоподбор ширины (Calc –На странце àУменьшить по размеру ячейки).

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

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

Обрати параметр Отображение à переносить по словам, якщо треба переносити текст у клітинці.

Для об’єднання клітинок треба виділить клітинки, які об’єднуються àФормат ячеек…à вкладка Выравнивание діалогового вікна Формат ячеек à опцію Объединение ячеек .

В об’єднанні клітинкі зберігаються тільки дані верхньої лівої клітинки. Щоб зберегти дані інших клітинок, їх потрібно перед об’єднанням перенести до верхньої лівої клітинки.

Щоб застосувати обрамлення до окремих клітинок, групи клітинок або всієї таблиці, виконайте такі дії: виділіть потрібний діапазон клітинок à ФорматàЯчейки…à вкладка Граница (Calc – Обрамление) виберіть тип i колір лінії обрамлення, зазначте вигляд обрамлення.

Щоб вибрати колір для виділених клітинок, на вкладці Вид (Calc – Фон) зазначте колір i/або візерунок тла.

 

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

Для роботи з базами даних використовується команда головного меню Данные. Для сортування списку достатньо розмістити активну клітинку в діапазоні списку і скористатися командою Данныеà Сортировка… програма автоматично визначає розмір списку, рядок з іменами полів (який не треба переміщувати) і відкриває вікно діалогу Сортировка диапазона, де обирають до трьох полів для сортування з урахуванням їх взаєм­них пріоритетів та порядку, що збільшується чи зменшується. При сортуванні для першого (найголовнішого) поля можна враховувати порядок, що визначається власним списком користувача. Вибрати потрібний власний список можна у вікні діалогу Параметри сортировки, відкривши його натисканням кнопки Параметры…

Якщо результат сортування потрібно скасувати, можна скористатися командою Правкаà Отменить Сортировка або комбінацією клавіш Ctrl+Z.

Крім того, доцільно зважати й на такі поради.

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

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

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

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

При сортуванні списків, що містять формули, потрібно дотримуватись простих правил:

–                  у формулах посилання на клітинки (адреси), що належать до полів одного запису, оформлювати як відносні;

–                  посилання на клітинки поза списком оформлювати у формулах тільки як абсолютні;

–                  уникати у формулах посилань на клітинки, що належать до полів з інших записів.

Відбір даних засобами Автофильтра. За допомогою фільтрів, що вбудовані в EТ, можна відібрати та відобразити тільки ті записи, які задовольняють задані критерії (умови). Це спрощує процес пошуку потрібної інформації, редагування та видалення записів, допомагає аналізувати дані. ЕТ надають дві команди для фільтрації даних:

–                  для простих критеріїв — Автофильтр;

–                  для складних критеріїв — Расширенный фильтр.

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

Можливостями Автофільтра можна скористатися, розмістивши активну клітинку всередині списку і скориставшись командою ДанныеàФильтрàАвтофильтр (Рис.13). Кнопки зі стрілкою, що з’являються справа від кожного імені поля, дають змогу відкрити перелік значень поля і накласти обмеження, створивши критерій відбору за кількома полями. У результаті відбору відображаються тільки ті записи, що відповідають заданому критерію, а рядок стану містить повідомлення про кількість знайдених записів.

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

Елемент Все списку дає змогу зняти обмеження щодо поля.

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

Можна задавати й складніші критерії, ніж перевірка рівності. Скориставшись елементом списку Условие…, можна відкрити вікно діалогу Пользовательский автофильтр і з’єднати за допомогою логічних операторів окремі умови відбору записів за одним полем (з будь-якими відношеннями порівняння). При визначенні критеріїв за текстовими полями можна використовувати також символи шаблона “?” та “*”.

Застосувати автофільтр можна тільки до одного списку на робо­чому листі.

Для відображення всіх записів списку можна скористатись командою ДанныеàФильтрàОтобразить все. Для відміни (деактивізації) автофільтра потрібно повторно скористатись командою Данныеà ФильтрàАвтофильтр.

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

Використання Расширенного фильтра. Для фільтрації списків програм ЕТ крім команди Автофильтр пропонує користувачам команду Расширенный фильтр, яку використовують для створення складних умов відбору даних (Рис. 4.13).

Команда Расширенный фильтр має додаткові можливості порів­няно з командою Автофильтр, а саме:

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

–  задавати три або більше умов для конкретного стовпця з вико­ристанням хоча б одного логічного оператора ИЛИ;

–  задавати обчислювані умови.

Крім того, команду Расширенньїй фильтр використовують для від­бору рядків зі списку за деякою умовою та копіювання відібраних рядків в іншу частину робочого аркуша.

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

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

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

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

У діапазоні умов можна ввести будь-яку кількість умов.

Програма сприймає їх за таким правилом:

–   умови одного рядка вважаються з’єднаними логічним оператором И (тобто всі умови повинні виконуватись одночасно);

–   умови у двох рядках вважаються з’єднаними логічним операто­ром ИЛИ (тобто достатньо виконання хоча б однієї умови).

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

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

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

Задаючи текстові умови, слід дотримуватися таких правил:

–   одна літера означає, що пошуку підлягають усі значення, що по­чинаються із зазначеної літери;

–   символ “>” або “<” означає, що пошуку підлягають значення, що за абеткою стоять після введеного текстового значення або перед ним;

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

Після введення діапазону умов відбору слід виконати команду Данные  àФильтр àРасширенный фильтр.

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

Перемикач Обработка слід встановити в положення Фильтровать список на месте.

 У результаті виконання команди Расширенньій фильтр прихо­вуються всі рядки, що не задовольняють введені умови. Номери відібраних рядків виділяються синім кольором.

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

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

–  посилання на клітинки, що перебувають за межами списку, по­винні бути абсолютними;

–  посилання на клітинки списку повинні бути відносними (за од­ним винятком, який розглянемо далі).

Копіювання відфільтрованих рядків в іншу частину робочого аркуша. За допомогою вікна діалогу команди Расширенный фильтр можна встановити режим копіювання відібраних рядків в іншу частину аркуша замість виведення відфільтрованого списку. Щоб скопіювати рядки, треба перемикач Обработка встановити в по­ложення Скопировать результат в другоє место, а в полі Поместить результат в диапазон зазначити адресу діапазону, куди по­трібно скопіювати інформацію.

Режим Только уникальные записи у вікні команди Расширенный фильтр додає додатковий фільтр до заданих умов відбору. Він приховує рядки, що повторюються.

Проміжні ітоги. Часто буває потрібним знати проміжні підсумки для тих або інших таблиць. Для великих таблиць зручно користуватися командою Данные à Итоги… (Рис. 4.14). Проміжні підсумки розраховауються для полів, які мають значення, що повторюються. Такими підсумками можуть бути сума, кількість, мінімальне, максимальне значення тощо. Перед виконанням цієї операції БД має бути відсортована за значеннями поля, які повторюються.

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

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

Для вилучення розрахованих проміжних підсумків слід установити курсор в БД й активізувати команди Данные à Итоги… à Убрать все.