Системи обробки табличної інформації

Загальні поняття про електронну таблицю

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

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

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

Основні функції електронних таблиць:

1. оформлення документу у вигляді таблиці;

2. проведення однотипних розрахунків над великими наборами даних;

3. автоматизація підсумкових обчислень; ;

4. робота з таблицями як з базами даних: пошук, сортування, фільтрація;

5. створення графіків та діаграм на основі даних таблиці;

6. статистична обробка результатів експериментів;

7. проведення пошуку оптимальних значень параметрів (рішення оптимізаційних задач);

8. угрупування даних та проміжні підсумки по групам.

Основні елементи електронної таблиці

Файл, що зберігає ЕТ має розширення файлу *.xlsx, назва ж самого документу - книга.

Робоча книга — це відкритий в середовищі MS Excel документ. За замовчуванням порожній книзі надається ім’я Книга1, але після внесень змін документ можна зберегти з будь-яким іменем. Книги можуть містити один чи кілька робочих аркушів, кожен аркуш має ім`я, за замовченням – Лист1, Лист2, … Переходити з аркуша на аркуш можна за допомогою вкладок з їх назвами під робочим аркушем. Аркуші книг можуть містити різну інформацію: таблиці, діаграми, графіки. Кожна книга за замовченням містить 3 аркуші, але це залежить від налаштування параметрів MS Excel. За допомогою контекстного меню аркуша можна додати аркуші до книги, вилучити, перейменувати, перемістити.

Структура аркушів однакова: кожен з них розбито на стовпці і рядки, на перетині яких розташовуються комірки. Для позначення стовпців використовуються латинські літери від А до Z та їх подвійні сполучення від АА до ІV. Усього на аркуші 256 стовпців, рядки позначаються цифрами 1,2,3,4 …65536. Для позначення комірки спочатку записується ім’я стовпця, а потім номер рядка, на перетині яких ця комірка міститься. Таке позначення називається Іменем комірки, наприклад D22, AN42.

Діапазоном називається поєднання сусідніх клітин, що утворюють прямокутник. Адреса діапазону клітин складається з адреси верхньої лівої клітини і нижньої правої, розділені двокрапкою, наприклад: А2:А8, або С4:С22.

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

Стовпці - вертикально розташовані смуги, ідентифіковані буквами латинського алфавіту (А, В, С, ..., Z) або комбінаціями з двох латинських букв (АВ, АС,..., IV), що знаходяться у верхній частині таблиці.

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

Рядки - горизонтально розташовані смуги, ідентифіковані числами (1,2, 3...), що знаходяться у крайній лівій частині таблиці.

Діапазон рядків – горизонтальна смуга таблиці. Виділяється та використовується аналогічно діапазону стовпців.

Інтерфейс табличного процесора Excel

Інтерфейс табличного процесора Excel містить стандартний набір складових будь-якого вікна додатку Офіс (7, 10).

1. Рядок заголовка містить:

• Кнопка «офіс» відкриває меню Документ (для Офіс 2007), меню роботи з вікном (Офіс 2010);

• Панель швидкого доступу – в неї користувач виставляє команди роботи з документом (файлом), що найчастіше використовує. Поряд з панеллю є значок налаштування панелі;

• Назва активного документу-книги та назва самої програми Excel;

• Кнопки управління вікном програми: згорнути, розгорнути, закрити;

2. Вкладки, що відкривають групи команд на стрічці: Файл (для Офіс 2010), Головна, Вставка, Розмітка сторінки, Формули, Дані, Рецензування, Вид, Довідка;

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

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

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

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

Типи даних в електронному табличному процесорі Excel

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

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

Числа в ЕТ бувають цілі, десяткові дроби, звичайні дроби та числа з плаваючою комою:

1. Цілі числа та десяткові дроби записуються як і в математиці;

2. Звичайні дроби вводяться так: ;

3. Числа з плаваючою комою використовуються в математиці для скороченого запису чисел з великою кількістю значущих нулів: 1200000000=1,2·109; В комірку ЕТ в цьому випадку введемо: 1,2Е+9 – мантису та порядок.

Формула ЕТ – це інструкція з обчислень. Правила запису формули:

• На відміну від математичного запису, в ЕТ Excel формула записується горизонтально – символ за символом;

• починається із знака “=”;

• до складу входять числа та посилання на комірки з числами, поєднані знаками арифметичних дій: (+) – додати; (-) – відняти; (*) – помножити; (/) – ділити; (^) – звести в степінь (R8^5 ⇔ а3);

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

• може містити стандартні функції, вбудовані в Excel.

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

Стандартні функції в Excel

Функції - це операції з аргументами, задані не математичними символами, а певними буквосполученнями. Аргументи функції записуються в круглих дужках відразу за назвою функції і відокремлюються один від одного символом крапка з комою (;). Дужки дозволяють Excel визначити, де починається і де закінчується список аргументів. MS Excel має набір вбудованих функцій, що має назву стандартних. Їх назви іноді співпадають з математичними, але іноді визначається певним буквосполученням. Наприклад, для обчислення √х використовується функція КОРЕНЬ(аргумент), а sinx також визначається як SIN(аргумент).

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

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

Статистичні функції виконують операції по обробці даних діапазону, наприклад середнє значення СРЗНАЧ(список), найменше значення МИН(список) і т. п.

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

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

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

Всі функції мають однаковий формат запису: ім'я функції (аргументи). Звернімо увагу, аргументи функції завжди в дужках. Аргументів для деяких функцій може бути кілька, наприклад, log29 ~ log(9;2). Розділяє елементи списку символ «;». Аргументами можуть бути не просто числа, а посилання на дані інших комірок, наприклад, log2х ~ log(В12;2).

Для багатьох функцій аргументом може бути список елементів, які треба включити в розрахунок, наприклад, СУММ(С12; С15:В22) – додати значення комірки С12 і діапазону комірок С15:В22.

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

Майстер функцій, його використання

Excel містить більше ніж 400 вбудованих функцій. Функція має ім'я і список аргументів у круглих дужках. Аргументами можуть бути числові та текстові константи, посилання на клітинки, діапазони клітинок.

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

Звертання до майстра функцій – це команда на вкладці Формула. На екрані з'явиться діалогове вікно Мастер функцій - шаг 1 из 2. В його середовищі треба обрати категорію функції і саму функцію. В нижній частині вікна приводиться формат команди та коментарі що до її дії. Натиснувши кнопку ok, отримаємо друге діалогове вікно для визначення аргументів функції. Аргументи можна визначати введенням їх з клавіатури, або виділенням мишею в самій ЕТ.

Обрати функцію можна також з бібліотеки функцій, приведеної на вкладці Формула.

Способи редагування інформації в електронній таблиці

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

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

Щоб перемістити вміст клітини, треба зробити її активною, зачепити показником миші границю і потягти. З затиснутою клавішею CTRL відбудеться копіювання. Як і в будь-якому додатку можна скористатись буфером обміну – Вирізати, Копіювати, Вставити.

При копіюванні інформації в суміжні клітини ЕТ можна скористатись маркером заповнення – зачепити його і потягти в напрямку копіювання.

Треба зазначити, при копіюванні певних даних оригінал відрізняється від копії:

• при копіюванні текстової інформації, що закінчується числом, числове значення копії збільшується (зменшується) залежно від напрямку копіювання: вправо, вниз – збільшення, вгору, вліво – зменшення;

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

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

• при копіюванні формули, посилання в формулі змінюються так само, як адреса копії в порівнянні з оригіналом. Наприклад, скопіювавши формулу =А12*В12 на 3 клітини вниз в копії отримаємо =А15*В15, а на 3 клітини вправо, отримаємо =D12*E12.

Можливості форматування чарунки (комірки) електронної таблиці

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

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

Вікно Формат містить такі вкладки: Число, Вирівнювання, Шрифт, Межа, «Вид» та Захист.

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

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

Вкладка Шрифт - тип шрифту, нарис шрифту може бути звичайним, курсивом, жирним та жирним і курсивом, розмір шрифту можна вибрати зі списку, або ж ввести з клавіатури у поле Розмір навіть проміжні розміри (ті, яких немає у списку, наприклад 11,5 пункту).

На вкладці «Заливка» можна задати колір клітинок і візерунок.

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

На вкладці Захист можна зняти чи задати режими захисту клітинок від несанкціонованих змін і ховання формул.

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

Розміри колонок і рядків. Excel за замовчуванням встановлює ширину колонки, що дорівнює 8.43 символу, а висоту — за розміром найвищого шрифту у рядку. Команди Головна/Клітинки/Висота рядків і Стовпців для встановлення ширини і висоти комірки.

Діаграми та графіки

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

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

На вертикальній осі (Y) розміщуються числові значення, а на горизонтальній осі (Х) – категорії.

Порядок створення діаграми.

1. Виділити дані для відображення;

2. На вкладці Вставка у групі Діаграми виберіть тип діаграми, а потім виберіть підтип.

Створена діаграма містить кілька складових:

• область діаграми; область побудови;

• горизонтальна вісь (вісь категорій) і вертикальна вісь (вісь значень), уздовж яких відкладаються дані на діаграмі;

• ряди даних; мітки даних; легенда (ключ легенди та елемент легенди),

• назви діаграми та осей, які можна використати на діаграмі;

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

Редагувати діаграму за допомогою команд на стрічці на вкладках Робота з діаграмами: Коструктор, Макет, Формат. Ці вкладки виникають при виділеній діаграмі, виділяючи комірку ЕТ ці вкладки зникають.

Вкладка Конструктор дозволяє змінити чи уточнити вигляд обраної діаграми (макет, стиль). Команда Вибрати дані дозволяє змінити підписи на осі ОХ;

Вкладка Макет дозволяє вставити чи змінити складові діаграми: заголовки, підписи, легенда, тощо;

Вкладка Формат дозволяє змінити вигляд складової: колір, фон, гарнітура та розмір шрифту, тощо.

В Excel є 15 типів діаграм. Розглянемо основні з них.

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

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

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

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

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

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

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

Сортування записів в ЕТ

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

Просте впорядкування – за однією ознакою:

• Курсор в будь-яку комірку стовпчика, по якому впорядковуємо записи;

• Вкладка Головна – група Редагування – список Сортування і фільтр ▼;

або вкдадка Дані – група Сортування і фільтр;

• Напрямок впорядкування або .

Багаторівневе впорядкування – по кільком ознакам. Наприклад, в таблиці є інформація по всім студентам технікуму, що містить стовпчики: Прізвище, Група, Спеціальність, інше. Треба впорядкувати записи, щоб І рівнем впорядкування були записи однієї спеціальності, потім ІІ рівень – групи, ІІІ рівень – по алфавіту прізвищ.

• Курсор в будь-яку комірку таблиці;

• Вкладка Головна – група Редагування – список Сортування і фільтр ▼ - ;

або вкдадка Дані – група Сортування і фільтр - ;

• В діалоговому вікні визначаємо рівні впорядкування:

Фільтрація даних в ЕТ

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

Простий фільтр (автофільтр):

• Курсор в будь-яку комірку таблиці;

• Вкладка Головна – група Редагування – список Сортування і фільтр ▼ - ;

або вкдадка Дані – група Сортування і фільтр - ;

• В назві кожного стовпчика таблиці з’явиться кнопка ▼ – список умов фільтрації;

• В списку приведено перелік всіх значень поля, можна вибрати певне значення;

• Якщо в списку обрати пункт - текстовій (числовий) фільтр, то зявиться діалогове вікно, в якому можна визначити складну умову відбору даних:

Проміжні підсумки

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

Угрупування даних ЕТ та проміжні підсумки по групам:

• Спочатку сортувати дані за ознакою, за якою буддемо поєднувати дані в групи;

• Вкладка Дані – група Структура – Проміжні підсумки;

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

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

Зведена таблиця дозволяє перерозподілити дані ЕТ в більш компактному вигляді за двома ознаками та обчислити проміжні підсумки по кільком ознакам. Розглянемо приклад:

• Курсор в будь-яку комірку таблиці;

• Вкладка Вставка – група Таблиці – Зведена таблиця;

• В діалозі визначаємо місце розташування зведеної таблиці;

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

Отримана зведена таблиця може редагуватись: активізуємо будь-яку комірку таблиці, при цьому на стрічку можемо вивести групи команд для зміни параметрів зведеної таблиці. Наприклад, замість операції Сума, можемо визначити Середнє, максимум, тощо.