Навігація
Головна
ПОСЛУГИ
Авторизація/Реєстрація
Реклама на сайті
МЕТОДИКА ФІНАНСОВО-ІНВЕСТИЦІЙНИХ РОЗРАХУНКІВ НА КОМП'ЮТЕРІ В...Організація баз даних засобами електронної таблиціЕлектронні таблиціРозрахунок ефективної процентної ставки в ExcelЮридичні бази даних із законодавстваВизначення процентної ставки в ExcelРобота з документами в табличному процесорі ExcelКласифікаційна таблиця за місцем творення приголоснихВикористання динамічних (електронних) таблиць для виконання завдань з...Оцінка ефективності інвестицій в Excel
 
Головна arrow Банківська справа arrow Інформаційні системи і технології в банках
< Попередня   ЗМІСТ   Наступна >

Організація бази даних засобами електронної таблиці Excel.

Технологія створення бази даних в Excel така. Після запуску Excel на екрані з'являється чиста робоча книга з новим вікном документа з тимчасовою назвою Воок 1 (Книга). Ця книга містить кілька чистих робочих аркушів (кількість аркушів визначається командою Сервис/Параметри/Общие).

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

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

На рис. 2.36 наведено зразок заповнення бази даних "Рух товарів".

Зразок заповнення бази даних

Рис. 2.36. Зразок заповнення бази даних

Якщо база передбачає наявність полів, що обчислюються, тоді для таких полів слід задати формули їх обчислення. У базі даних "Рух товарів" поле Сума в комірці 12 обчислюється за формулою = G2 х НІ. Після введення заголовків стовпців і зразка заповнення бази слід виділити ці два рядки, а потім вибрати з меню Data (Дані) команду Form (Форма). За допомогою цієї команди Excel аналізує рядок з іменами полів та елементами в першому записі і створює форму, в якій розташовані зверху вниз усі імена полів та їхні значення в першому записі. На рис. 2.37 показано форму для введення даних у базу даних - вона виглядає як діалогове вікно користувача (поля, що обчислюються, не мають текстових вікон). Після створення форми даних можна починати введення записів у базу даних активізацією кнопки Добавить.

Перехід до наступного поля форми після введення значення попереднього здійснюється виключно клавішею Tab. Клавіша Enter у цьому разі не забезпечує повного запису в базу. Якщо треба відредагувати значення поля, яке вже пройдено, слід натиснути Shift + Tab для повернення до помилкового поля. Під час уведення даних у певне поле можна скопіювати значення з того ж поля попереднього запису натисненням клавіш Ctrl + "(лапки). Якщо треба ввести число, що починається нулями (005102), які не повинні зникнути після введення, набір числа треба розпочати з апострофа ('005102).

Діалогове вікно форми з даними першого запису

Рис. 2.37. Діалогове вікно форми з даними першого запису

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

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

Заповнена база даних

Рис. 2.38. Заповнена база даних

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

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

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

Пошук даних за допомогою маски

Поряд зі зручним уведенням Форма даних надає можливість здійснювати пошук інформації. Для введення критерію пошуку відповідного запису (записів) призначена кнопка Критерии. Критерій пошуку може включати шаблони пошуку (*, ?), а також оператори порівняння (=, >, >=, <, <=, о). Наприклад, щоб відобразити у формі даних тільки тих постачальників, які поставляли товар на склад № і, потрібно в поле Склад ввести значення критерію пошуку - і, а в поле Операція - значення <1І (кодами операцій від 1 до 10 позначено надходження товарів на склад, кодами, що перевищують 10, - вибуття товарів зі складу). Далі слід активувати кнопку Далее або Назад для здійснення пошуку від поточного запису, який активований табличним курсором, відповідно вперед або назад. Уведення критеріїв пошуку у форму відображено на рис. 2.39. Результати пошуку даних у базі відображаються на екрані таким чином, як це показано на рис. 2.40, тобто у формі відображається лише один запис. Для перегляду інших знайдених записів треба скористатися кнопками Далее або Назад.

Діалогове вікно форми з маскою пошуку

Рис. 2.39. Діалогове вікно форми з маскою пошуку

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

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

Критерії пошуку та цільові функції

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

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

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

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

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

Таким чином, кожна Or-умова задається окремим рядком. Усередині рядка критерію значення окремих полів розглядаються як умови, що об'єднані за допомогою логічної операції And (І).

Якщо потрібно розшукати в базі даних усі поставки постачальників із кодом 1001 або з кодом 1590, тоді в області критеріїв у колонці Пост_Пок під ім'ям поля має стояти 1001, а в наступному рядку - 1590:

Пост_Пок

1001

1590

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

Склад Опер Пост_Пок Сума

2 < 11 125 > 1000

Перш ніж розпочати пошук відповідно до критеріїв пошуку, у таблиці слід передбачити ще одну область - цільову. В цю область Excel зможе копіювати знайдені дані. її слід розташувати так, аби уникнути конфліктів із частинами таблиці, що виділені під базу і критерії. У першому рядку цільової області слід записати імена полів, зміст яких потрібно побачити в знайдених записах. Цільова область повинна бути точно визначена. Якщо користувач під час оголошення цільової області визначить, крім рядка заголовка, ще, наприклад, три рядки, то тим самим він повідомляє Excel, що під час пошуку в базі його цікавлять перші три знайдені записи, які відповідають критеріям пошуку. У разі якщо Excel знайде більше ніж три записи - у зону будуть виведені перші три записи, а потім повідомлення "Extract Range is Рий" (Цільова область заповнена). Це свідчить про те, що існують й інші записи, які відповідають критеріям пошуку.

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

Зона електронної таблиці зі сформованою областю критеріїв та цільовою областю

Рис. 2.41. Зона електронної таблиці зі сформованою областю критеріїв та цільовою областю

Повний критерій пошуку даних у базі - поставки на склад № 2 на суму понад 1000 грн постачальника з кодом 1125.

Таким чином, база даних оголошена, визначені область критеріїв і самі критерії, вибрана цільова область. Можна розпочинати пошук даних. Пошук здійснюється за допомогою команди пошуку і вибірки даних з бази Данные/Фильтр/Расширенный фильтр... Після запуску команди на виконання на екрані з'являється діалогове вікно установки розширеного фільтра. У вікні діалогу в зоні Обработка слід установити опцію Скопировать результат в другое место, а далі слід визначити область бази даних - Исходный диапазон, Диапазон условий і зону комірок для фіксації результату пошуку - Поместить результат в диапазон. Оформлений фільтр у цьому разі буде таким, як на рис. 2.42.

Діалогове вікно установки розширеного фільтра

Рис. 2.42. Діалогове вікно установки розширеного фільтра

Після активації кнопки OK Excel починає пошук даних у базі. Результат пошуку оформляється як на рис. 2.43.

Електронна таблиця з результатами пошуку даних

Рис. 2.43. Електронна таблиця з результатами пошуку даних

У діалоговому вікні фільтра є опція Unique Records Only/Тільки унікальні записи, яка дозволяє ліквідувати повторну вибірку з бази однакових записів, якщо такі є. У разі якщо визначення деяких установок (база даних, критерії, цільова область) містили помилки, з'являється діалогове вікно з попередженням про те, наприклад, що цільова область недійсна, - Extract Range not Valid/Неправильно вказано діапазон. У цьому разі слід виявити помилку і задати правильні установки.

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

База даних в Excel має здатність забезпечити певний порядок записів (наприклад, у порядку зростання номерів або в алфавітному порядку). Однак під час заповнення бази новими записами складно зберігати встановлений порядок. При введенні нового запису Excel під'єднує його в кінець бази даних. Якщо всі записи впорядковано за певною ознакою, цей порядок може бути зручним для користувача в більшості випадків. Але іноді виникає необхідність розташувати записи в іншому порядку, причому для кожного конкретного випадку порядок може бути свій, особливий. Гнучкість упорядкування записів в Excel забезпечується командою Sort (Сортувати).

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

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

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

Після активації кнопки OK Excel відсортує записи. Якщо помилково було вибрано не ті ключі сортування або використаний не той порядок сортування, слід скористатися командою Правка/Отменить Сортировка.

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

Іноді потрібно вивести на екран лише записи бази даних певного змісту. Результат досягається фільтрацією даних, тобто здатністю Excel сховати не потрібні в певний момент записи бази даних. Для виконання фільтрації треба: табличний курсор попередньо встановити в одне з полів бази даних і вибрати команду Данные/ Фильтр/ Автофильтр, при цьому поряд з полями бази створюються вікна, що відкриваються; відкрити вікно поля для фільтрації; зі списку, що відкрився, вибрати значення фільтра (на рис. 2.44) наведено налагодження фільтрації даних за кодом товару 15120).

База даних із вікнами полів фільтрації

Рис. 2.44. База даних із вікнами полів фільтрації

У результаті фільтрації Excel виведе на екран тільки записи, що відповідають поставленим умовам. На рис. 2.45 наведено відфільтровану базу за фільтром 15120 поля Код_тов.

База даних після фільтрації

Рис. 2.45. База даних після фільтрації

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

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

Для створення власного фільтра потрібно:

• вибрати команду Данные/Фильтр/Автофильтр;

• відкрити вікно того поля, на базі якого буде створено власний автофільтр;

• зі списку, що відкрився, вибрати опцію Сustom..(Условие), на екрані з'являється діалогове вікно Пользовательский автофильтр;

• створити фільтр - з відкритого списку вибрати потрібний оператор порівняння (=, >, <, >=, <=, о); у текстове вікно, що справа, ввести значення (текст або число), стосовно якого здійснюватиметься порівняння значень поля; накласти створений фільтр на базу даних активацією кнопки ОК. На основі вікна Пользовательский автофильтр можна також розробити власний фільтр для вибірки записів, значення полів яких лежать у заданих межах або відповідають хоча 6 одному з двох уведених критеріїв. Для цього потрібно: ввесги оператор порівняння для нижньої межі; ввести найменше значення інтервалу; активувати перемикач And, якщо утворюється І-умова, або перемикач От, якщо утворюється Або-умова; вибрати оператор порівняння для визначення верхнього інтервалу; ввести найбільше значення інтервалу.

На рис. 2.46 ілюструється фільтр, утворений для фільтрації тільки тих записів, де значення поля Ціна перебуває в межах - більше (isgreater then) ніж 2,87і менше (is less than) ніж 34,78 грн.

Рис. 2.46. Діалогове вікно Автофільтр користувача з установленими параметрами фільтрації

Результат фільтрації за даним фільтром наведено на рис. 2.47.

Рис. 2.47. Результат фільтрації бази за фільтром 2,87< Ціна < 34,78

Відфільтровані дані можна копіювати в іншу таблицю або виводити на друк.

Аналіз даних за допомогою зведених таблиць

Зведені таблиці (Pivot Table) дають змогу різними способами бачити інформацію, що зберігається в

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

Для автоматизації створення зведеної таблиці Excel використовує програму - Майстер зведених таблиць. Для формування зведеної таблиці потрібно виконати таку послідовність дій:

• активувати базу даних (установити курсор на будь-яку комірку таблиці, що містить базу даних); вибрати команду Данные/Сводная таблица. На екрані з'являється діалогове вікно Мастер сводных таблиц и диаграмм - шаг 1 из 4, в якому треба задати тип даних, на основі яких формуватиметься зведена таблиця (рис. 2.48);

Діалогове вікно Мастер сводных таблиц и диаграмм

Рис. 2.48. Діалогове вікно Мастер сводных таблиц и диаграмм

• натиснути кнопку Далее, з'являється нове діалогове вікно Мастер сводных таблиц и диаграмм для визначення місцезнаходження вхідних даних (рис. 2.49);

Діалогове вікно Мастер сводных таблиц и диаграмм

Рис. 2.49. Діалогове вікно Мастер сводных таблиц и диаграмм

" після визначення місцезнаходження вхідних даних знову активувати кнопку Далее, з'являється третє діалогове вікно Мастер

сводных таблиц и диаграмм, в якому треба визначити місце формування таблиці - новий аркуш чи поточний (рис. 2.50);

Діалогове вікно Мастер сводных таблиц и диаграмм

Рис. 2.50. Діалогове вікно Мастер сводных таблиц и диаграмм

• вказавши місце формування зведеної таблиці, слід активувати кнопку Готово - на екрані з'являється макет зведеної таб7іиці (рис. 2.51);

Макет зведеної таблиці

Рис. 2.51. Макет зведеної таблиці

• заповнити макет зведеної таблиці - перемістити за допомогою миші імена полів із правої частини діалогового вікна в області Page (Сторінка), Row (Рядок), Column (Колонка), Data (Дані). Поля, що розміщуються в області Page (Сторінка), є умовою фільтрації даних для зведеної таблиці. Поля, що розміщуються в області Row (Рядок), відображають їхні конкретні значення в кожному рядку зведеної таблиці. Поля, що розміщуються в області Column (Стовпець), фіксують назви колонок таблиці і конкретні значення полів, що підраховуються. Поле, що розміщується в зоні даних, підраховується. Якщо елементи колонок містять числа, тоді для обчислення програма Excel автоматично використовує функцію Sum (Сума).

У зведеній таблиці можна змінювати порядок полів і повністю змінити її вигляд. На рис. 2.52 наведено сформовану Зведену таблицю, в якій рух товарів у розрізі складів фільтровані за датою (06.03.2004).

Форма зведеної таблиці

Рис. 2.52. Форма зведеної таблиці

Після поновлення даних у базі треба поновити і зведену таблицю. Для поновлення зведеної таблиці слід виділити будь-яку її комірку, після чого виконати команду Данные/Обновить данные.

 
Якщо Ви помітили помилку в тексті позначте слово та натисніть Shift + Enter
< Попередня   ЗМІСТ   Наступна >
 
Дисципліни
Агропромисловість
Банківська справа
БЖД
Бухоблік та Аудит
Географія
Документознавство
Екологія
Економіка
Етика та Естетика
Журналістика
Інвестування
Інформатика
Історія
Культурологія
Література
Логіка
Логістика
Маркетинг
Медицина
Менеджмент
Нерухомість
Педагогіка
Політологія
Політекономія
Право
Природознавство
Психологія
Релігієзнавство
Риторика
РПС
Соціологія
Статистика
Страхова справа
Техніка
Товарознавство
Туризм
Філософія
Фінанси