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

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

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

Структура та різновиди веб-сайтів

Створення та редагування запитів та звітів

Сортування, пошук і фільтрація даних

Створення та редагування форм

Основні етапи роботи з базами даних

Огляд реляційної моделі даних

Поняття й призначення систем керування базами даних

Умовне форматування даних

 Умовний формат

Умовний формат — це формат (наприклад, візерунок комірки або колір шрифту), який Microsoft Excel автоматично застосовує до комірки, якщо виконується зазначена умова.

Додавання, зміна або видалення умовних форматів

Порядок дій:

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

2. У меню Формат виберіть команду Умовне форматування.

3. Виконайте відповідну дію.

Додавання умовного формату

Для використання значень виділених комірок як умов форматування необхідно вибрати параметр значення, вибрати операцію порівняння, а потім ввести стале значення або формулу. Перед формулою слід поставити знак рівності (=).

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

4. Порядок подальших дій:

Натисніть кнопку    Формат.

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

Щоб додати іншу умову, натисніть кнопку     Додати, а потім повторіть кроки.

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

Пошук комірок, які мають умовне форматування

Порядок дій:

1. Щоб знайти всі комірки з умовним форматуванням, клацніть будь-яку комірку.

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

2. У меню Правка виберіть команду Перейти.

3. Натисніть кнопку Виділити.

4. Виберіть умовні формати.

5. Виконайте одну з таких дій.

Щоб знайти комірки з умовним форматуванням, виберіть параметр "усіх" у групі Перевірка даних.

Щоб знайти комірки з однаковим умовним форматом, виберіть  параметр "цих самих" у групі Перевірка даних.

Вибір кольору для виділення дубльованих комірок

Тепер виберемо добре помітний формат для позначення всіх знайдених дублікатів. У діалоговому вікні Умовне форматування натисніть кнопку Формат. Відкрийте вкладку Вид і виберіть яскравий колір заливки, наприклад червоний або жовтий. Натисніть кнопку OK, щоб закрити діалогове вікно Формат комірок.

Вибраний формат буде показано у вікні зразка. Натисніть кнопку OK, щоб закрити діалогове вікно Умовне форматування.

Копіювання умовного формату до решти комірок

Нам треба скопіювати умовне форматування комірки A2 вниз до решти комірок діапазону. Не залишаючи комірки A2, виберіть команду  Копіювати в  меню  Правка. Натисніть клавіші CTRL+пропуск, щоб виділити весь стовпець. Потім виберіть команду Спеціальна вставка в меню Правка. У вікні Спеціальна вставка виберіть перемикач Формати й натисніть кнопку OK.

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

Не завадить перейти до комірки A3 та поглянути на умовну формулу (після того як її було скопійовано з A2). Виділіть комірку A3 і виберіть команду Умовне форматування в меню Формат. Видно, що формула в полі Формула змінилася й тепер обчислює, скільки разів дані з комірки A3 зустрічаються у стовпці A.

Умовне форматування може обробляти до 65536 комірок, порівнюючи кожну окрему комірку з рештою 65535 комірками. З  технічної точки зору, формула на першому кроці мала б виглядати так: =COUNTIF($A$2:$A$1751;A2)>1.

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

2.  Сортування даних

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

Спочатку введіть у комірці B1 заголовок Дублікат. Потім введіть у комірці B2 таку формулу:=COUNTIF(A:A;A2)>1.

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

Тепер можна відсортувати стовпці за стовпцем B (за спаданням), а потім за стовпцем A (за зростанням), і дубльовані номери рахунків опиняться на початку діапазону.

Зведені таблиці

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

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

За 17 днів продажів у нас вийшла велика таблиця на 350 записів. Але ця таблиця не вирішує наших проблем. Нам необхідно дізнатися обсяги продажів у грошовому та кількісному виразі по датах і по окремих магазинах, але як це зробити? Сортувати таблицю і підсумовувати окремі її частини? Це вимагає часу, а завтра надійдуть нові дані, і всю роботу потрібно буде знову повторити.

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

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

Таблиця відразу ж відобразить потрібні нам результати:

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

 

-       Дуже простий спосіб створення такої таблиці, який не потребує багато часу;

-       Можливість консолідувати дані з різних таблиць і навіть з різних джерел;

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

 

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

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

Для створення зведеної таблиці нам потрібна проста базова таблиця.

 

Алгоритм створення зведеної таблиці

1.            Для створення зведеної таблиці відкрийте вкладку Вставка, де в групі Таблиці виберіть команду Вільна таблиця.
2.       Відкриється наступне діалогове вікно: У цьому вікні Excel пропонує нам вказати вихідну таблицю або діапазон значень, на підставі яких буде будуватися зведена таблиця. Якщо Ви виконали команду Зведена таблиця, попередньо встановивши курсор на листі, де знаходяться будь-які дані, Excel автоматично заповнить це поле. Якщо ж на листі дані відсутні, або вони знаходяться в іншому місці, Вам потрібно буде вказати адресу діапазону даних вручну. І остання опція, яку потрібно встановити у цьому вікні - вибрати місце розташування зведеної таблиці: у новому вікні або на цьому ж листі. В останньому випадку потрібно вказати діапазон адрес, де повинна розташовуватися зведена таблиця.
3.  Натиснувши кнопку Ок після налаштування потрібних нам умов, ми отримуємо наступний робочий лист: Для нашого прикладу спробуємо створити таблицю, яка буде підсумовувати дані Обсяг продажів, шт. і Сума виручки для кожного значення в стовпці Дата і для кожної Точки продажи. Для цього потрібно виконати наступні дії:
а) у верхній частині вікна налаштувань відзначаємо всі назви необхідних нам стовпців:
б) Поле Точка продаж перетягуємо в область Фильтр отчета. У цьому випадку Excel додає на робочий лист фільтр, за допомогою якого ми встановлюємо умову для виведення підсумкових даних. Вибравши в нашому прикладі точку продажу, ми зможемо виводити підсумки з продажу для окремого магазину.     в) Поле Дата перетягуємо в область Названия строк. Excel використовує значення зі стовпця Дата для того, щоб озаглавити рядки нашої таблиці. Таким чином, ми будемо підсумовувати потрібні нам поля по кожній даті нашого звіту. г) Поля Сума по полю Об’єм продаж, шт. і Сума по полю Сума виручки перетягуємо в область Значения. Дані всіх стовпців з цієї області Excel підсумує і відобразить в рядках зведеної таблиці.
Налаштування нашої таблиці повинне виглядати ось так:
Тоді наша зведена таблиця буде мати такий вигляд:

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

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

Для цього досить у вікні налаштування відзначити галочками два додаткові поля - Марка телефону та Модель телефону, і перетягнути ці поля в область Фільтр звіту. Excel додасть до зведеної таблиці два нових фільтра, які допоможуть нам швидко дізнатися потрібну інформацію:

 

Сортування та фільтрація даних

• Сортування даних

Сортування – це зміна положення даних у списку відповідно до значення або типу даних. Якщо виникає потреба розташувати в алфавітному порядку дані, поставити в порядку зростання, то для цього на панелі інструментів є кнопки, що означають сортування від А до R, або від R до A.Також можна використати команду меню Дані → Сортування. Діалогове вікно Сортування діапазону призначене для вибору поля, на якому відбувається сортування (виділяємо комірки).

Правила сортування: 1)порожні комірки завжди вміщуються у кінець відсортованого списку;

2)числові типи даних сортуються від найменшого від'ємного до найбільшого додатного;

3)текстові типи даних сортуються познаково зліва направо;

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

5)під час сортування логічних значень значення ЛОЖЬ ставиться перед значенням ИСТИНА.

• Фільтрація даних

В Excel ми можемо поміщати величезне число записів (максимальне число рядків робочого аркуша –65536). Однак не завжди треба відображати всі ці записи. Фільтрацією називається виділення підмножини набору записів.

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

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

Прості – умови, створені з використанням операторів порівняння < , > , = і т.д.

Складені – це умови, побудовані з простих за допомогою логічних операцій  not (не),  and (і),  or (або).

В Excel передбачено два засоби фільтрації: автофільтр, та розширений фільтр.

Авто фільтр застосовується тоді, коли умова вибору стосується тільки одного стовпця або складається з умов щодо значень кількох стовпців, з’єднаних сполучником «і».

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

Застосування авто фільтру:

1. Виділити таблицю.
2. Дані → Фільтр → Авто фільтр
3. Клацніть на кнопку з трикутною позначкою у назві того стовпця, на значення якого накладається умова. Оберіть умову фільтрації.

Типи умов фільтрації

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

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

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

Відфільтрувавши таблицю за якимось параметром, ви можете накласти додаткову умову на значення іншого параметра. У такий спосіб відбирають рядки за складеними умовами, з'єднаними сполучником «і», наприклад: «знайти прізвища працівників, що є продавцями і народилися пізніше 1 січня 1980 року».

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

Використання розширеного фільтра

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

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

У таких випадках застосовують розширений фільтр, який дає змогу записати критерій відбору рядків у вигляді окремої таблиці. Її створюють за тими ж правилами, що і критерій у функціях для роботи з базою даних: умови, з'єднані сполучником «і», записують в одному рядку критерію, а з'єднані сполучником «або» — у різних.

Загалом алгоритм застосування розширеного фільтра такий.

1. У порожніх клітинках аркуша створіть критерій фільтрації.
2. Виберіть команду Дані ► Фільтр ► Розширений фільтр. Буде відображено вікно Розширений фільтр.
3. У полі Вихідний діапазон укажіть діапазон, де розміщується таблиця, а в полі Діапазон умов — діапазон критерію і клацніть кнопку ОК.

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

Використання функцій табличного процесора

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

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

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

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

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

Структура функції

Введення функції починається зі знаків “=”, “+” або  “”. Усі функції програми Ехсеl складаються з двох основних частин – імені функції і аргументів. Аргументи  функцій задаються в круглих дужках, що йдуть безпосередньо за ім’ям функцій. Функція може мати один, декілька чи не мати жодного аргументу. Аргументом може бути діапазон комірок, наприклад функція  =SUM(A1:A5) знаходить суму значень, які містяться в комірках діапазону A1:A5. Якщо функція має декілька аргументів, вони відокремлюються один від одного крапкою з комою, наприклад функція  =SUM(A1:A5; В1:В5; С1:С5) підсумовує усі значення, що міститься в зазначених діапазонах. Наявність круглих дужок під час уведення функції є обов’язковою, навіть якщо функція не має аргументів.

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

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

Способи введення функцій

1)                 Введення вручну. Щоб ввести функцію вручну, потрібно ввести в комірку знак “=”, набрати ім’я функції (можна малими літерами), задати список аргументів у круглих дужках, натиснути Enter. Якщо ім’я функції введено правильно, літери автоматично перетворяться на великі.

2)                 Використання діалогового вікна Вставка функции.

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

Щоб ввести функцію, потрібно виконати такі дії:

1.                 Виокремити комірку, куди потрібно ввести функцію.

2.                 Вибрати команду Вставка/Функция (або натиснути кнопку Вставка функции на стандартній панелі інструментів). Відкриється діалогове вікно Вставка функции.

3.                 В розкривному списку вибрати потрібну категорію, після цього в полі Выберите функцию з’являться функції, з яких треба вибрати потрібну.

4.                 Після вибору функції з’явиться діалогове вікно Аргументы функции. У відповідних полях цього вікна за потрібно задати аргументи функції, ввівши їх адреси вручну або виділивши мишею.

5.                 Після задання всіх аргументів потрібно натиснути кнопку ОК, діалогове вікно зникне, а в клітинці з’явиться результат обчислення функції.

3)     Використання розкривного списку Функции

Як тільки в комірку ввести знак “=”, кнопка розкривного списку Имя в рядку формул автоматично замінюється кнопкою розкривного списку Функции. Список Функции місить перелік з 10 функцій, що використовувались останніми. Якщо клацнути на будь-якій функції з цього списку, її буде введено в комірку, а на екрані з’явиться діалогове вікно Аргументы функции, де можна задати аргументи цієї функції.

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

Математичні функції

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

Функція SUM

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

Якщо треба просумувати всі значення рядка 4, треба ввести формулу =SUM (4:4), щоб просумувати всі значення стовпця С, слід ввести формулу =SUM (С:С).

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

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

Використання логічних функцій.

Розгалуження в ЕТ реалізовують за допомогою функції ЯКЩО, яка використовується у формулах і має таку структуру:

Якщо (<логічний вираз>;<вираз 1> ; <вираз 2>).

Логічний вираз – це форма запису умови: простої або складеної.

Якщо умова істинна, то функція набуває значення першого виразу, інакше – другого.

Вираз 1 чи вираз 2 також може бути функцією ЯКЩО – так утворюють вкладені розгалуження. Часто виразом 1 чи виразом 2 є лише адреса клітинки, яка містить деяке значення або конкретне число.

Прості умови записують як в алгоритмічних мовах – за допомогою операцій порівняння =, >, <, <=, >=, <>, визначених над виразами, наприклад 7>5, A5<=50 тощо.

Складні умови записують за допомогою логічних функцій І(<умова 1>;<умова 2>;…) та АБО(<умова 1>;<умова 2>;…).

Функція І (И, AND) істинна, якщо всі умови в її списку істинні.

Функція АБО (ИЛИ, OR) істинна, якщо хоч би одна умова в її списку істинна.

Наприклад, функція ЯКЩО (АБО (5>7; 5<7); 5; 7) отримує значення 5, а функція ЯКЩО(І(5>7; 5<7); 5; 7)– значення 7.

Якщо користувач не пам’ятає вигляду функції, він може вставити її у вираз за допомогою майстра функцій, який викликається командою Вставити → Функція. У цьому випадку потрібно вибрати назву функції з запропонованого списку (крок 1) і заповнити поля значеннями параметрів (крок 2).

Працюючи з програмою, потрібно користуватися російськими (ЕСЛИ, И, ИЛИ) або англійськими (IF, AND, OR) назвами логічних функцій.