Інформаційні системи і технології

ІНФОРМАЦІЙНІ СИСТЕМИ В СЕРЕДОВИЩІ EXCEL

Таблиці даних

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

19

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

Крім того форма дає змогу шукати записи за певними критеріями. Для цього потрібно клацнути на кнопці <Критерии>, після чого у правому верхньому куті форми з'являється напис Критерии. Якщо заповнити відповідні поля форми, кнопки <Назад> і <Далее> показуватимуть лише ті записи таблиці, що задовольняють введеним умовам. Для числових полів можна вводити математичні умови порівняння (=, <, >, <>, <=, >=). У текстових полях можна використовувати символи шаблону ("*" — будь-яка кількість будь-яких припустимих символів, "?" — будь-який один довільний символ).

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

20

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

IСтолбец А ~^\, ІСтолбец В

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

кнопками панелі інструментів Стандартная: за зростанням |яІ | і за

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

В Excel можливе сортування за встановленими в програмі списками (дні тижня, місяці року тощо), а також за списками користувача, що створюються за допомогою вкладки Списки діалогового вікна Параметры, яке викликається однойменною командою пункту меню Сервис:

21

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

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

Для обробки таблиць даних у програмі Excel призначено низку функцій і процедур. Для простих умов фільтрації даних найзручніше скористатися командою Автофильтр з підменю Фильтр пункту меню Данные. Якщо помістити курсор всередину таблиці даних (списку) і виконати цю команду, то справа від кожного заголовка стовпця з'явиться кнопка зі стрілкою вниз _tJ, за допомогою якої можна виконувати різні команди фільтрації списку. Коли потрібно відфільтрувати дані лише у деяких стовпцях, можна застосувати ав-тофільтр лише для них (треба, щоб вони були розташовані поряд, створювали один діапазон комірок). Перед виконанням команди Автофильтр потрібно ці стовпці виділити.

Кнопка _rj розкриває команди автофільтра

, а та(Первые 10...) (Условие,,,)

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

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

22

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

Основне праве поле элементов списка

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

Параметр |% от количества элементов застосовується для відображення визначеної у відсотках кількості рядків з найбільшими або найменшими значеннями.

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

23

Якщо стовпець містить числові дані, для них можна встановити звичайні умови порівняння (равно, не равно, меньше, меньше или равно і т. ін.). Для текстових даних можуть бути застосовані умови начинается с, не начинается с, заканчивается на, содержит тощо, а також використані підстановочні знаки: "?" — один будь-який довільний символ, "*" — будь-яка кількість будь-яких припустимих символів. При пошуку в тексті власне символу "знак питання" або "зірочка", перед ними треба поставити знак "-"(тильда).

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

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

У разі застосування розширеного фільтра можна задати складніші умови відбору записів зі списку (більш як два набори умов для одного стовпця, одна умова для кількох стовпців, умови, що задаються за результатом обчислення за формулою). На відміну від ав-тофільтра, для розширеного фільтра умови фільтрації для списку вводяться в окремий діапазон комірок. Після виконання команди Расширенный фильтр з підменю Фильтр пункту меню Данные відкривається діалогове вікно з такою ж назвою, в якому потрібно встановити діапазони комірок для таблиці даних, умов відбору і результатів фільтрації, а потім натиснути кнопку <OK>:

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

24

Для формулювання умов відбору в розширеному фільтрі треба в будь-якому вільному місці робочого аркуша створити додатковий рядок із заголовками стовпців, а наступні кілька рядків використати для введення в них умов відбору записів таблиці даних. Можна обмежитися тільки тими заголовками стовпців (назвами полів), для яких встановлюються умови фільтрації. Умови відбору в комірках відповідних стовпців можна записувати у кілька рядків. Критерії в одному рядку з'єднуються логічним оператором "И", в різних рядках — логічним оператором "ИЛИ". Якщо в діапазоні умов деяка комірка порожня, це означає, що таку умову задовольнятиме будь-яке значення у стовпці. Тому фільтрація списку не відбудеться, якщо в діапазоні умов виявиться порожній рядок.

У найпростішому випадку, коли зі списку треба отримати тільки записи з одним значенням в одному стовпці (наприклад, на рис. на с. 19 виділити лише записи, що мають значення "Васильківська" для стовпця "Вулиця"), достатньо для діапазону умов вказати тільки дві комірки — з назвою стовпця і нижче з потрібним значенням

. Якщо ж цікавлять два значення з одного стовпця (логічний оператор "ИЛИ" — або "Васильківська", або "Антоновича"), в діапазон умов слід нижче додати ще одну комірку з відповідним знаВулиця

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

додатковою умовою для іншого стовпця (цікавлять з вулиці "Васильківська" лише записи з прізвищем "Петренко", тобто з'єднані логічним оператором "И"), у тих самих рядках слід додати назву стовпця та

відповідне значення ^їлицв ПрІііищ* ддя jHinojСИТуащї коли

□асипьчпгэка    Петренко J

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

стовпця (цікавлять усі з вулиці "Васильківська" та всі з прізвищем

"Петренко", тобто зв'язані логічним оператором "ИЛИ"), значення

для    другого    стовпця    записують    у    наступному рядку

D-лил П с із аг ще

.

Пет рент

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

25

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

Крім критеріїв точної рівності деякому значенню можна використовувати інші критерії порівняння: <, <=, <>, >, >=. Для текстових критеріїв слід враховувати певні правила. Якщо в комірці записати одну літеру, при фільтрації будуть знайдені всі значення, що починаються на цю літеру. За умовою ">Л" після фільтрації залишаться записи, що починаються з літер від "М" до "Я", для "<Л" — від "А" до "К". Крім того символи шаблону ("+", "?") обробляються так само, як в автофільтрі.

Система Excel дає можливість також використовувати обчислювальні критерії, що потребують виконання складніших операцій, ніж просте порівняння. Це може бути, наприклад, порівняння із середнім значенням якогось поля всієї таблиці даних (середній вік, середня заробітна плата працівників тощо). Для виконання фільтрації такого типу треба в деякій комірці за межами списку обчислити потрібне значення (наприклад, у комірці G15 записати =CP3HA4(D2:D11)), потім у діапазоні умов ввести заголовок стовпця, що не повинен збігатися з будь-яким заголовком таблиці даних, а нижче ввести умову обчислювального критерію (=D2>$G$15). При цьому слід дотримуватись правил: посилання на комірки за межами списку повинні бути абсолютними, а посилання на комірки всередині списку — відносними.

Використання Microsoft Query

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

Програму MS Query доцільно використовувати, якщо джерело даних велике за обсягом, а потрібно проаналізувати лише частину з них або виконати спеціалізовані завдання, такі як фільтрація рядків і стовпців, сортування даних або об'єднання кількох таблиць перед їх перенесенням в Excel. Це може бути також створення параметричного запиту (запит, перед виконанням якого визначається один або кілька параметрів чи умов).

26

З точки зору MS Query зовнішні дані — це діапазон даних, розташованих не в Excel, а наприклад, у базі даних, текстовому файлі або таблиці зовнішнього файла Excel.

Інструментом аналізу даних у MS Query є запит. Це засіб пошуку в джерелі даних тих елементів, що відповідають певному критерію, певній умові. Запити можна створювати самостійно, а можна скористатися майстром.

Для створення запиту за допомогою Мастера запросов потрібно виконати команду Создать запрос з підпункту Импорт внешних данных пункту меню Данные або натиснути кнопку <Создать запрос>

панелі інструментів Microsoft Query та у вікні Выбор источника

данных встановити прапорець режиму Использовать мастер запросов:

Для обробки таблиць з файлів Excel треба вибрати рядок ЕДВВННШВ. Після натискання кнопки <OK> з'являється напис Подключение к источнику данных і відкривається вікно Выбор книги

27

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

За допомогою кнопки <Просмотр> можна переглянути дані вибраного стовпця та відповідними кнопками із стрілками додати >

вибраний стовпець до запиту або видалити < | уже доданий. Кнопка   << І дає змогу видалити усі вже вибрані до запиту стовпці.

Наступне вікно майстра запитів призначене для встановлення критеріїв відбору для всіх стовпців запиту:

28

Для кожного стовпця можна встановити кілька умов, кожна з наступних може бути з'єднана логічним оператором "И" або "ИЛИ". У разі потреби можна вибрати порядок сортування відібраних записів, а потім визначитися з місцем збереження даних, а також використати можливість збереження запиту у файлі з розширенням DQY .

Використання Microsoft Query без майстра запитів дає змогу створювати складніші види запитів. Якщо у вікні Выбор источника данных вимкнути режим Использовать мастер запросов, після виконання команди відразу запускається програма Microsoft Query з відкритим вікном відкриття файлів. У разі вибору файлів Excel відкриється вікно Выбор книги, в якому слід вибрати потрібний файл.

Далі MS Query аналізує файл даних і виводить на екран список знайдених у файлі таблиць:

Кнопкою <Добавить> потрібні для запиту таблиці вводяться до MS Query, і списки полів цих таблиць з'являються в робочому вікні MS Query rt^JtjJJIJiJiJiLiiläS].

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

29

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

Замовлення$. Отримувач Замовлення$. Співробітник

має приблизно такий вигляд: Кліенти$.Адреса , потрібне поле.

Кліенти$. Звертатися до

Ще один спосіб — виконати команду Добавить столбец пункту меню Записи програми MS Query та використати діалогове вікно Добавление столбца. За допомогою елемента * у списку полів можна вивести всі поля цієї таблиці.

Після того, як у ділянці даних будуть виведені всі потрібні поля, ділянку таблиць можна сховати за допомогою кнопки <Отображение таблиц> I або команди Таблицы пункту меню Вид. Поля записів у ділянці даних можна легко міняти місцями за допомогою миші. Для цього спочатку треба виділити мишею стовпець, клацнувши в зоні заголовку стовпця, а потім перетягнути заголовок стовпця на нове місце. Так само просто можна видалити непотрібний стовпець, попередньо його виділивши, за допомогою клавіші <Delete> або команди Удалить столбец пункту меню Записи. Також заголовок виділеного стовпця можна змінити, виконавши команду Изменить столбец з того ж пункту меню.

Виведені в ділянці даних записи розташовані у тому ж порядку, в якому вони зберігаються у початковому джерелі даних. Ці записи можна впорядкувати, якщо використати кнопки <Сортировать по возрастанию> та <Сортировать по убыванию>, або команду Сортировать пункту меню Записи.

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

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

<Фильтр по выделенному> V=

30

За замовчуванням MS Query автоматично обновляє записи в ділянці даних після кожної зміни умов їх подання. Індикатором автоматичного оновлення є галочка біля рядка Автоматический режим у

панелі

пункті меню Записи або вдавлена кнопка з тією ж назвою |

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

запрос пункту меню Записи або кнопки з тією ж назвою | \ |,

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

або команди Условия пункту

кнопки <Отображение условий> меню Вид.

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

Условия

II і:........і

ÜopoMe-i.jTjspttu E*j*rW

Умови можна вводити безпосередньо в комірки ділянки критеріїв, а також за допомогою команд MS Query.

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

Для видалення критерію або скасування умов фільтрації потрібно виділити відповідний стовпець умов і натиснути клавішу <Delete>. Команда Удалить все условия пункту меню Условия відміняє всі критерії фільтра і повертає список записів у початковий стан.

Програма MS Query дає можливість створювати критерії порівняння. Для цього використовується діалогове вікно Добавление условия, що викликається однойменною командою Добавить условие пункту меню Условия.

31

Умова порівняння встановлюється в полі Оператор, а назву стовпця, для якого вводиться ця умова, можна вибрати в полі Поле, Кнопка <Значения> відкриває додаткове вікно, в якому елементи вибраного поля виводяться, впорядковані за зростанням, що дає змогу швидко вибрати значення, відносно якого порівнюватимуться елементи записів. Можна також ввести цю величину в поле Значение.

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

Для операторів между та не между потрібно ввести два граничні значення, які можна визначити за допомогою вікна Выбор значений

або набрати у полі Значение, відокремивши їх крапкою з комою.

Крім звичайних операторів порівняння для числових значень підсистема MS Query містить оператори для аналізу символьних фрагментів. Наприклад, можна використовувати оператори начинается с, не начинается с, заканчивается на, не заканчивается на, содержит, не содержит. У цьому разі програма автоматично до набору символів, що введені у поле Значение, додає символ шаблону (наприклад "%" або де знак відсотків "%" позначає будь-яку кількість будь-яких символів, а знак підкреслення "_" — лише один будь-який символ) відповідно до конкретної умови (табл. 1).

32

Таблиця 1

Відповідність між операторами порівняння та значеннями умови в діапазоні ділянки критеріїв

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Оператор порівняння

Значення умови

равно

 

Країна отримувача

 

'Німечина'

не равно

 

Країна отримувача

 

о'Німечина'

больше или равно

 

Країна отримувача

 

>='Німечина'

меньше или равно

 

Дата виконання

 

<='10-07-199Є'

содержится в

 

Країна отримувача

 

В ('Іспанія';'Італія';'Мексика')

не содержится в

 

Країна отримувача

 

Не В ('Австрія';'Бельгія';'США')

между

 

Країна отримувача

 

между 'Італія' И 'США'

не между

 

Країна отримувача

 

Не между 'Італія' И 'США'

начинается с

 

Ім'я

Похоже на Та%'

не начинается с

 

Ім'я

 

Не Похоже на 'П%'

заканчивается на

 

Прізвище

 

Похоже на '%ова'

не заканчивается на

 

Прізвище

 

Не Похоже на '%\н'

содержит

 

Прізвище

Похоже на '%оро%'

не содержит

І Ім'я

1 Не Похоже на '%ан%'

похоже

1 Країна отримувача Похоже на 'анія'

не похоже

1 Країна отримувача Не Похоже на 'Шве'

Null

 

Дата виконання

 

Имеется Пустое значение

не Null

 

Дата виконання

 

Имеется Не Пустое значение

33

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

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

Діалогове вікно Свойства запроса, що викликається однойменною командою з пункту меню Вид, дає змогу вилучити з таблиці результатів запиту всі записи, що повторюються, у разі увімкнення режиму Только уникальные записи:

іяайстлл шпрота

Ще однією можливістю MS Query є групові обчислення. Якщо у ділянці даних вивести лише одне поле (наприклад, з якимись числовими значеннями), виділити стовпець і натиснути кнопку <Цикл по

групповым операциям>

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

Счмма из Вартість

166573,0 ). Додаткове натискання цієї кнопки виведе на

екран інші функції групових обчислень: Среднее, Число (кількість значень у списку), Минимум, Максимум. Ще одне натискання виводить увесь список значень.

Якщо в ділянці даних вивести два стовпці даних (наприклад, з текстовими і числовими даними), виділити стовпець з числовими даними і натиснути кнопку <Цикл по групповым операциям>, MS Query знайде проміжні підсумки, тобто виконає сумування за стовпцем з числовими даними для кожного окремого значення стовпця з текстовими даними. Як і для одного стовпця, при повторному натисканні цієї кнопки будуть обчислені інші числові характеристики текстових даних.

34

За допомогою підсистеми MS Query можна також створити параметричний запит. Параметричний запит — це тип запиту, у разі запускання якого потрібно задати умови відбору записів у таблицю результатів, тобто один запит може бути використаний для отримання різних таблиць результатів. Для створення такого запиту після вибору поля в ділянці умов початкової таблиці у рядку Значение потрібно ввести квадратні дужки [ ] з деяким текстом. При виконанні запиту MS Query автоматично виводить вікно Ввод значения параметра, в яке користувач повинен ввести певне значення, відносно якого запит виконується:

Ol Отмена

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

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

Відфільтровані дані з MS Query можна повернути до Excel за допомогою   кнопки    <Вернуть   данные> або команди

Ет.-■ Е■.it пункту меню Файл.

За замовчуванням у діалоговому вікні Импорт данных пропонується помістити дані у поточну комірку робочого аркуша або визначити нове місце розташування даних:

35

Кнопка <Изменить запрос> дає можливість повернутися до MS Query і встановити новий варіант фільтрації даних, а кнопка <Свойства> — визначити режими збереження даних запиту. Зокрема, якщо встановлено режим сохранить определение запроса, дані в Excel автоматично оновлюватимуться по мірі їх змін у зовнішньому джерелі даних.

Підбиття підсумків

При обробці списків велике значення має й отримання підсумкових значень. Програма Excel має низку засобів для одержання узагальнених величин. Одним із таких засобів є зведена таблиця. Така таблиця тісно пов'язана з вихідними даними, але автоматично не перерозраховується. Для оновлення даних у таблиці використову}   панелі інструментів Сводные

ють кнопку <Обновить данные> таблицы.

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

36

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

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

За замовчуванням у ділянці Данные для числових даних використовується функція сумування СУММ, а для нечислових — функція обчислення кількості комірок з певними значеннями СЧЕТ.

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

в ділянці даних |Тип    У]і та у вікні Вычисление поля сводной таблицы вибрати потрібну функцію:

37

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

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

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

38

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

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

Питання для самоконтролю

1. Що таке список з точки зору Excel?

2. Що таке поле даних і запис у списку Excel?

3. За яких умов Excel автоматично створить форму для списку?

4. Яке основне призначення форм для списків в Excel?

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

6. Які перетворення можна виконувати з даними у списку в Excel?

7. Що таке список користувача та як його створити в Excel?

8. Які можливості надає Excel для фільтрації даних?

9. За якими правилами Автофильтр виводить дані списку певного стовпця?

10. Скільки умов можна накласти на дані одного стовпця списку за допомогою вікна Пользовательский автофильтр?

11. Як сформулювати умови фільтрації для розширеного фільтра?

12. Як скасувати результати фільтрації при використанні розширеного фільтра?

13. Яке основне призначення Microsoft Query?

14. Які типи даних можна проаналізувати за допомогою Microsoft Query?

15. Як виконується фільтрація за виділенням у Microsoft Query?

16. Для чого призначений Автоматический режим у Microsoft Query?

17. За якою умовою об'єднуються критерії в одному рядку? У різних рядках?

18. Яка функція обчислення використовується за замовчуванням для нечислових даних у зведеній таблиці в Excel?

19. Які функції можуть використовуватися у зведених таблицях в

Excel?

20. Як перебудувати зведену таблицю в Excel?

39

Список використаної та рекомендованої літератури

1. Аникеев И., Бардина О. Microsoft Office 2000. — М.: Бином, 1999.

2. Берне П. Секреты Excel 97. — К.: Диалектика, 1998.

3. Берне П., Николсон Дж. Секреты Excel для Windows 95. — К.: Диалектика, 1999.

4. Ботт Э., Леонард В. Использование Microsoft Office 2000. Специальное издание. — М.: Вильямс, 2000.

5. Гаевский А. Ю. Самоучитель работы с Microsoft Office: Word 97/2000, Excel 97/2000, электронная почта. — К.: А.С.К., 2002.

6. Гебхардт P. Excel 97: Справ. — М.: Бином, 1998.

7. Додж М. Стинсон К. Эффективная работа с Microsoft Excel

2000. — СПб.: Питер, 2000.

8. Карпов Б. Microsoft Office 2000: Справ. — СПб.: Питер, 2000.

9. Рогов И. П. Office 97 (Microsoft Office 97). — М.: ЗАО "Изд-во БИНОМ", 1998.

10. Рыжков В. Самоучитель Excel 2000. — СПб.: Питер, 2000.

11. Стоицкий Ю. Office 2000. — СПб.: Питер, 2000.

 

 ...  6



Обратная связь

По любым вопросам и предложениям

Имя и фамилия*

Е-меил

Сообщение*

↑ наверх