Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
ІКТ КЛ - Розділ 4.doc
Скачиваний:
12
Добавлен:
04.02.2016
Размер:
2.84 Mб
Скачать

Міністерство надзвичайних ситуацій україни

Академія пожежної безпеки ім. Героїв Чорнобиля

Факультет пожежно-профілактичної діяльності

Кафедра вищої математики та інформаційних технологій

ЗАТВЕРДЖУЮ

Начальник кафедри

ВМ та ІТ

к.ф-м.н., доц.,

полковник с.ц.з.

_______ І.П. Частоколенко

“___”______2013р.

Навчальна дисципліна: Інформатика та комп’ютерна техніка

1-й курс (стаціонар, курсанти).

ЛЕКЦІЯ

Тема № 4.1 Створення та робота з табличною базою даних.

Час: 12, з них 2 години лекцій, 6 годин практичних занять, 4 годинb самостійної роботи

Тема лекції № 16: Створення та робота з базою даних в MS Excel.

Навчальна мета: Розглянути теоретичний матеріал у відповідності з темою лекції.

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

Матеріально-методичне забезпечення: Дошка, крейда.

Розробив:

викладач

кафедри ВМ та ІТ А.П. Марченко

Лекція обговорена та схвалена на засіданні кафедри

Протокол №____від “__”_____________2013р.

Лекція 16. Створення та робота з табличною базою даних.

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

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

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

У цьому плані Excel можна розглядати як нескладну СУБД реляційного типу. Реляційні бази даних подають­ся у формі звичайних двовимірних електронних таблиць-відношень (relation); в останніх версіях Excel їх назива­ють просто списками. База даних (список) в Excel — той самий робочий аркуш із його стовпцями і рядками, текс­том, числами й іншими елементами, але сформований за певними правилами.

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

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

10 АлмазовО. М. 1968 Інженер Харків 86

Кожний запис — це рядок бази даних. Усі записи ма­ють однакову фіксовану довжину, їх кількість, у принципі, не обмежена.

Поле — певна характеристика об'єкта або окремий еле­мент даних у запису. Кожне поле має унікальне ім'я, йому відповідають дані одного стовпця. Для ефективного пошу­ку, селекції та сортування даних бази доцільно записи по­діляти по полях, що містять найдрібніші елементи даних. Так, замість одного поля «Прізвище, ім'я, по батькові» кра­ще задати три: «Прізвище», «Ім'я» і «По батькові». Єдину адресу клієнта можна розділити на поля «Поштовий ін­декс», «Місто», «Вулиця», «Номер будинку» і т. п.

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

  1. Загальні правила формування бази даних:

      • у базі даних, як правило, розміщуються тільки поля початкових даних. Поля, що обчислюються, шапку документа і підсумкові рядки до бази не включають, їх формують на етапі створення документа-звіту;

      • заголовний рядок має відрізнятися від рядків-записів кольором, шрифтом або обрамленням. У разі моно­хромного друку його краще взяти в рамку;

      • після заголовного рядка мають іти рядки записів; розділяти їх навіть порожніми рядками не рекомен­дується;

      • в однойменних полях записів розміщують дані тіль­ки одного типу: числа, тексти або дати. Не треба розпочинати поля з пропусків;

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

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

      • на одному аркуші бажано розміщувати тільки од­ну базу даних. Інші дані краще розташувати на ін­ших аркушах. Поєднання різнорідних баз даних мо­жливе, якщо відокремити їх порожніми рядками і стовпцями.

  1. Формати файлів баз даних

Базу даних, створену засобами Excel, можна зберегти не тільки у стандартному форматі книги (.xls), а й у звичному для баз даних форматі, наприклад, dbf-файлі. У цьому випадку програма коректно визначає типи полів (текст, числове, дата) та їхні довжини (для тексту) на підставі на­явних даних. Для створення dbf-файлу достатньо вибрати значення DBF 4 (dBASE IV) (*.dbf) у полі Тип файлу діалогового вікна Збереження документа, яке викликається командою Файл – Сохранить как...

Excel дає змогу також працювати з dbf-файлами та файлами баз да­них системи керування базами даних (СКБД) Microsoft Office Access. Для відкриття таких файлів у полі Тип файлу діалогового вікна Відкрит­тя документа треба, відповідно, вибрати значення Файли dBase (*.dbf) або Бази даних Access (*.mdb; *.mdb). Іншим способом відкриття таких файлів є використання команди Получение внешних данных з вкладки Даные.

  1. Створення бази даних.

Базу даних, як і будь-яку книгу Excel, створюють коман­дою Файл—Создать. Спочатку можна ввести назву таблиці, наприклад «Відомості про замовлення товарної продукції», потім — її заголовний рядок із однорядковими та коротки­ми іменами полів: «Замовник», «Шифр товару» і т. ін.. Далі потрібно замінити системне ім'я бази «Книга1» на призначене для користувача, наприклад на «Замов­лення», клацнувши мишею на кнопці Сохранить.

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

Рисунок 16.1 – Перегляд вмісту БД по одному запису

Для того, щоб відкрити діалогове вікно Форма, необхідно спочатку додати кнопку виклику вікна на панель швидкого доступу. Для цього натисніть на іконку налаштування панелі швидкого доступу та оберіть пункт Другие команды…

Рисунок 16.2 – Перегляд вмісту БД по одному запису

У вікні оберіть в полі Выбрать команды из пункт Команды не на ленте. Після чого, знайдіть в списку команд «Форма…» виділіть її, та натисніть на кнопку «Добавить >>» та натисніть ОК, після чого вона з’явиться на панелі швидкого доступу.

Значення полів запису вводять до відповідних комі­рок діалогового вікна у тій послідовності, в якій вони сфор­мовані в таблиці. Перехід між комірками виконується за допомогою клавіші Tab або клавіш керування курсо­ром. Усі комірки вікна мають таку ж ширину, як і найширше поле (стовпець) бази. При потребі її можна збіль­шити (зменшити) «буксируванням» правої межі стовпця таблиці. Запис передають у кінець бази даних командою Добавить. Після цього вводять наступний запис і т. п. до кінця бази.

Попередження

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

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

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

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

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

  1. Використання фільтра та сортування даних

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

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

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

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

  1. Автоматичне введення та вибір зі списку. Контроль введення даних.

Засобами ЕТ можливо реалізувати списки значень, які має містити запис (або комірка) а також реалізувати перевірку введених даних. Виконання цих операцій доступне через команди, розміщені на вкладці Данные.

Для перевірки введення даних використовується функція Проверка вводимых значений, що викликається з панелі Данные кнопкою Проверка данных. Ця функція дозволяє контролювати введення даних, шляхом валідації згідно з вказаними параметрами, серед яких можна вказати як тип даних, так і діапазон, а також списки підстановки значень. Для комірок, що потребують контролю введення даних, можливо також ввести підказку та своє повідомлення про помилку та дію, якщо значення не входить до сказаного діапазону, це можна налаштувати на вкладках Сообещение для ввода та Сообщение об ошибке. Серед умов перевірки може бути також вказана формула, яка має повертати значення ИСТИНА або ЛОЖЬ.

Рисунок 16.3 – Контроль введення даних

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

Наприклад при вказаному переліку місяців, що містяться в комірках з О10 по О21, вказавши їх у якості джерела для списку, комірка матиме наступний вигляд:

Рисунок 16.4 – Списки підстановки

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

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

Також можна використовувати команди швидкого сортування, що також доступні на вкладці Даные, кнопками Сортировка от А до Я (кнопка ) та Сортировка от Я до А (кнопка ), які сортуватимуть виділений діапазон за зростанням або за зменшенням відповідно.

Рисунок 16.5 – Вікно сортування

  1. Пошук записів бази даних

В Excel процедура пошуку і селекції даних організо­вується за принципом збігу або селективним способом.

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

ПП «Маяк»; ТШ-45; 27.10.02; 22,95

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

ПП*;ШТ-4?;ТШ*;ЗАТ*;

И (Ціна, грн. >=20; Ціна, грн. <=60);

ИЛИ (Дата замовлення<=20.10.02; Дата замовлення >=28.10.02),

де * — ознака будь-якої групи символів; ? — ознака того, що в цій позиції може міститися будь-який символ. Кри­терій ПП* ідентифікує всі записи бази «Замовлення» (рис. 2.104), що стосуються приватних підприємств; кри­терій ПІТ-4? — всі записи з шифрами товарів від ПІТ-40 до ІПТ-49; а критерій И (Ціна, грн. >=20; Ціна, грн. <=60) — всі записи з цінами товарів від 20 до 60 грн. Аналогічні функції виконують інші критерії.

На етапі створення бази даних, щоб розпочати ручне введення критеріїв, пошук записів, їх послідовній перегляд і редагування, слід активізувати команду Критерии, яка трохи видозмінює початкове вікно Форма й очищає всі його поля.

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

Рисунок 16.6 – Пошук за критеріями

Переходять до режиму пошуку клацанням мишею на кнопці Далее. Як результат, у початковому вікні (рис. 2.105) з'являється перший із записів бази даних, що задовольняє задані критерії. Запис переглядають і в разі потреби корегують. Після клацання мишею на кнопці Закрыть Excel записує відновлений запис у базу да­них. Усі подальші записи переглядають у вікні за допо­могою кнопок Назад і Далее.

Примітка

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

Повернення до поточного запису й ігнорування резуль­татів пошуку за критерієм забезпечується кнопкою Прав­ка. Для введення чергового критерію потрібно заздале­гідь активізувати команду Очистить, після чого пошук від­новлюється. Повертаються до початкової бази даних через команду Данные деактивувавши кнопку Фильтр.

  1. Формування підсумкових даних

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

Розглянемо роботу команди.

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

  1. Список доцільно відсортувати за полем, що містить групи. Наприклад, за місяцем, прізвищем тощо.

  2. Виконати команду з вкладки Данные кнопка Промежуточный итог. Відкриється діалогове вікно Промежуточные итоги.

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

  4. Вибрати зі списку Операция одну з функцій (Сума – додавання чисел у групі, Кількість - підрахунок кількості заповне­них клітинок у групі, Середнє — середнє арифметичне чисел у групі, Максимум - визначення найбільшого числа у групі, Мінімум - ви­значення найменшого числа у групі, Добуток - добуток чисел у гру­пі, Кількість чисел - кількість клітинок, котрі містять числові дані у групі, Зсунене відхилення - розрахунок стандартного відхилення за генеральною сукупністю, Незсунене відхилення - розрахунок стан­дартного відхилення за групою, Зсунена дисперсія - розрахунок дис­персії за генеральною сукупністю, Незсунена дисперсія - розрахунок дисперсії за групою).

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

Структура списку після виконання команди Промежуточные итоги до­зволяє переглядати різні частини списку з допомогою кнопок, розміще­них в лівому полі аркуша.

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

  1. - Вивести тільки загальний підсумок;

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

  3. - Вивести всі дані.

Кнопки з позначками (+) і (-) призначені для розгортання і згортання окремих груп.

Об’єднання даних із кількох аркушів на одному аркуші.

Щоб підсумувати результати даних, розміщених на окремих аркушах, і створити звіти, дані з кожного аркуша можна об’єднати в один аркуш (головний аркуш). Аркуші можуть бути в одній книзі з головним аркушем або в різних. Після об’єднання дані простіше оновлювати та підсумовувати як на постійній основі, так і для певних одноразових потреб.

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

Вирізняють два основні способи об’єднання даних.

  • Об'єднання за розташуванням.    Цей метод слід використовувати, коли дані з кількох джерел упорядковані однаково та мають однакові підписи рядків і стовпців. Наприклад, коли у вас є низка аркушів витрат, які створено за одним шаблоном.

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

Дані можна об’єднувати за допомогою команди Об’єднати (вкладка Дані, група Знаряддя для даних), формули або звіту зведеної таблиці.

Об’єднання даних за розташуванням

  1. У кожному аркуші з даними, які ви хочете об’єднати, упорядкуйте дані в такий спосіб.

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

  • Помістіть кожний діапазон на окремому аркуші, але не розташовуйте діапазони на тому аркуші, де буде розміщено об’єднані дані.

  • Переконайтеся, що всі діапазони мають однаковий макет.

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

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

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

  1. На вкладці Данные у групі Работа с данными клацніть елемент Консолидация.

Рисунок 16.7 – Вибір команди «Консолідація»

  1. У полі Функция виберіть функцію зведення Підсумкова функція. Тип обчислення при об'єднанні даних у звіті зведеної таблиці, у таблиці консолідації або при обчисленні підсумків у списках або базах даних. Приклади підсумкових функцій: SUM, COUNT і AVERAGE., яку потрібно використовувати для об’єднання даних у програмі Microsoft Excel.

  2. Якщо аркуш за даними, які потрібно об’єднати, розташовано в іншій книзі, натисніть кнопку Обзор, знайдіть потрібний файл і натисніть кнопку ОК, щоб закрити діалогове вікно Обзор.

Шлях до файлу вводиться в полі Ссылка, і в кінці його додається знак оклику.

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

  1. У полі Ссылка натисніть кнопку «Згорнути діалогове вікно», щоб вибрати дані на аркуші.

  2. Клацніть аркуш, який містить дані для об’єднання, виберіть дані та натисніть кнопку «Розгорнути діалогове вікно».

  1. У діалоговому вікні Об’єднання клацніть Додати, а потім повторіть кроки 6 та 7, щоб додати всі потрібні діапазони.

  2. Щоб настроїти спосіб оновлення об’єднання, виконайте одну з таких дій.

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

 Увага!   Встановіть цей прапорець, лише якщо аркуш із даними розташовано в іншій книзі. Після його встановлення ви не зможете змінювати клітинки та діапазони, які входять до об’єднання.

  • Щоб настроїти оновлення об'єднання вручну шляхом змінення клітинок і діапазонів, які входять в об’єднання, зніміть прапорець Створювати зв'язки з вихідними даними.

Об’єднання даних за категорією.

  1. У кожному аркуші з даними, які ви хочете об’єднати, упорядкуйте дані в такий спосіб.

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

  • Помістіть кожний діапазон на окремому аркуші, але не розташовуйте діапазони на тому аркуші, де буде розміщено об’єднані дані.

  • Переконайтеся, що всі діапазони мають однаковий макет.

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

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

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

  1. На вкладці Дані у групі Знаряддя даних клацніть елемент Консолідація.

  1. У полі Функція виберіть функцію зведення Підсумкова функція. Тип обчислення при об'єднанні даних у звіті зведеної таблиці, у таблиці консолідації або при обчисленні підсумків у списках або базах даних. Приклади підсумкових функцій: SUM, COUNT і AVERAGE., яку потрібно використовувати для об’єднання даних.

  2. Якщо аркуш розташовано в іншій книзі, натисніть кнопку Огляд, щоб знайти потрібний файл, і натисніть кнопку ОК, щоб закрити діалогове вікно Огляд.

Шлях до файлу вводиться в полі Посилання, і в кінці його додається знак оклику.

  1. Якщо аркуш розміщено в поточній книзі, виконайте такі дії.

  1. Натисніть кнопку посилання праворуч від поля Посилання , щоб відкрити діалогове вікно Об’єднання – посилання.

  2. Відкрийте аркуш, який містить дані для об’єднання, виберіть дані та натисніть кнопку праворуч від поля.

  1. У діалоговому вікні Об’єднання клацніть Додати, а потім повторіть кроки 6 та 7, щоб додати всі потрібні діапазони.

  2. Для настроювання способу оновлення об’єднання виконайте одну з таких дій:

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

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

  • Щоб настроїти оновлення об'єднання вручну шляхом змінення клітинок і діапазонів, які входять в об’єднання, зніміть прапорець Створювати зв'язки з вихідними даними.

  1. Установіть прапорці в розділі Використовувати як імена поруч із тими полями, які вказуватимуть на розташування підписів у вихідних діапазонах: у рядку вище, у стовпці ліворуч або обидва.

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

  • Переконайтеся, що категорії, які не слід об'єднувати, мають унікальні підписи, які з'являться лише в одному вихідному діапазоні.

Інші способи об’єднання даних

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

Об’єднання даних за допомогою формули.

  1. У головному аркуші скопіюйте або введіть підписи стовпців або рядків, які потрібно об'єднати.

  2. Виберіть клітинку, яка має містити об'єднані дані.

  3. Введіть формулу, яка містить посилання на вихідні клітинки в кожному аркуші або об'ємне посилання, що містить дані, які потрібно об'єднати. Залежно від типу посилання виконайте такі дії:

Дані, які потрібно об’єднати, розташовані в різних клітинках на різних аркушах    

  • Введіть формулу (окрему для кожного аркуша) з посиланнями на клітинки на інших аркушах. Наприклад, щоб об’єднати дані з аркушів «Продаж» (у клітинці В4), «Кадри» (у клітинці F5) і «Маркетинг» (у клітинці В9), у клітинці А2 на головному аркуші введіть:

Рисунок 16.8 – Приклад опису функції для даних з різних клітинок та аркушів

Порада. Щоб ввести посилання на клітинку, наприклад «Продаж!В4», у формулу, не набираючи його, введіть формулу до того місця, де має початися посилання, клацніть вкладку потрібного аркуша, а потім клацніть потрібну клітинку.

Дані, які слід об’єднати, розташовані в однакових клітинках на різних аркушах

  • Введіть формулу з тривимірним посиланням, яка посилається на діапазон назв аркушів. Наприклад, щоб об’єднати дані з клітинок А2 аркушів «Продажі» — «Маркетинг» (включно), на головному аркуші у клітинці А2 введіть:

Рисунок 16.9 – Приклад опису функції для даних з однакових клітинок але різних аркушів

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

Використання звіту зведеної таблиці для об'єднання даних

Звіт зведеної таблиці Звіт зведеної таблиці. Інтерактивний перехресний звіт Microsoft Excel, який містить підсумкові дані та виконує аналіз таких даних, як записи бази даних із різних джерел, у тому числі поза межами Microsoft Excel. можна створити на основі кількох діапазонів для об’єднання. Цей спосіб схожий на об’єднання за категорією, але надає більше можливостей для повторного впорядкування категорій. Додаткові відомості див. у статті Швидке створення звіту зведеної таблиці.

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

Найбільш потужним засобом обробки даних в Excel є команда Зведена таблиця. В результаті її виконання створюється нова таблиця. Зведені таб­лиці надають користувачу можливості:

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

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

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

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

Створювати зведені таблиці можна двома способами. Розглянемо кожен з них.

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

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

2. Відкрийте вкладку «Вставка» і виберіть з розділу «Таблицы» інструмент «Сводная таблица». Якщо разом зі зведеною таблицею потрібно створити і зведену діаграму - натисніть на стрілку в нижньому правому куті значка «Зведена таблиця» і виберіть пункт «Зведена діаграма».

3. У діалоговому вікні «Создание сводной таблицы» виберіть щойно створену таблицю з даними або її діапазон. Для цього виділіть потрібну область.

Рисунок 16.10 – Вікно створення зведеної таблиці

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

4. Далі потрібно буде вказати, де розміщувати звіт зведеної таблиці. Найзручніше це робити на новому аркуші.

5. Після підтвердження дії натисненням кнопки "ОК", буде створений і відкритий макет звіту. Розглянемо його.

У правій половині вікна створюється панель основних інструментів управління - «Список полей сводной таблицы». Всі поля (заголовки стовпців в таблиці вихідних даних) будуть перераховані в області «Выберите поля для добавления в отчет». Позначте необхідні пункти і звіт зведеної таблиці з вибраними полями буде створений.

Рисунок 16.11 – Приклад вибору полів зведеної таблиці

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

Інші опції для редагування звітів доступні з меню «Робота зі зведеними таблицями» на вкладках «Параметры» і «Конструктор». Майже кожен з інструментів цих вкладок має масу настройок і додаткових функцій.

 Спосіб 2. Створення зведеної таблиці з використанням інструменту «Мастер сводных таблиц и диаграмм». Щоб застосувати цей спосіб, доведеться зробити доступним інструмент, який за замовчуванням на стрічці не відображається. Процедура подібна до тої, де додавали кнопку «Форма…» на панель швидкого доступу. Відкрийте вкладку "Файл" - "Параметры" – «Панель быстрого доступа». У списку «Выбрать команды с» відзначте пункт «Команды не на ленте». А нижче, з переліку команд, виберіть «Мастер сводных таблиц и диаграмм». Натисніть кнопку "Добавить". Іконка майстра з'явиться вгорі, на панелі швидкого доступу. Майстер зведених таблиць в Excel 2010 зовсім не багатьом відрізняється від аналогічного інструменту в інших версіях. Для створення зведених таблиць з його допомогою виконайте наступне.

1. Клацніть по іконці майстра в панелі швидкого допуску. У діалоговому вікні поставте перемикач на потрібний вам пункт списку джерел даних:

- «в списке или базе данных Microsoft Excel» - джерелом буде база даних робочого листа, якщо така є;

- «во внешнем источнике данных» - якщо існує підключення до зовнішньої бази, яке потрібно буде вибрати з доступних;

- «в нескольких диапазонах консолидации» - якщо потрібне об'єднання даних з різних джерел;

- «данные в другой сводной таблице или сводной диаграмме» - в якості джерела береться вже існуюча зведена таблиця або діаграма.

Рисунок 16.12 – Вікно майстра зведеної таблиці

2. Після цього вибирається вид створюваного звіту - «сводная таблица» або «сводная диаграмма (с таблицей)».

- Якщо в якості джерела обрано поточний документ, де вже є проста таблиця з елементами майбутнього звіту, задайте діапазон охоплення - виділіть курсором потрібну область. Далі виберіть місце розміщення таблиці - на новому або на поточному листі, і натисніть "Готово". Зведена таблиця буде створена. 

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

При виборі «Создать поля страницы» насамперед доведеться вказати діапазони джерел даних: виділіть перший діапазон, натисніть «Добавить», потім наступний і т.д. Для зручності діапазонами можна присвоювати імена. Для цього виділіть один з них у списку і вкажіть число створюваних для нього полів сторінки, потім задайте кожному полю ім'я (мітку). Після цього виділіть наступний діапазон і т.д.

Рисунок 16.13 – Заповнення параметрів зведеної таблиці

Після завершення натисніть кнопку «Далее», виберіть місце розташування майбутньої зведеної таблиці - на поточному аркуші або на іншому, натисніть «Готово» і ваш звіт, зібраний з декількох джерел, буде створений. При виборі зовнішнього джерела даних використовується додаток Microsoft Query, що входить в комплект поставки Excel 2010 або, якщо потрібно підключитися до даних Office, використовуються опції вкладки «Данные». Якщо в документі вже присутній звіт зведеної таблиці або зведена діаграма - в якості джерела можна використовувати їх. Для цього достатньо вказати їх розташування і вибрати потрібний діапазон даних, після чого буде створена нова зведена таблиця.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]