Робота із зовнішніми даними

Microsoft Excel є чудовим засобом, що дозволяє аналізувати дані, проте, для того щоб провести будь-який подібний аналіз, спочатку необхідно помістити дані до книги Excel У багатьох випадках інформація, яку необхідно обробити, знаходиться поза програмою: у текстовому файлі, у файлі XML на веб-сайті або в базі даних, створеної такими засобами, як Oracle або Microsoft Access Excel надає зручний інструментарій для імпорту такої інформації в робочі листи Саме про це піде мова в даній главі

Імпорт зовнішніх даних за допомогою команди Відкрити

Знайома вам команда Файл ► Відкрити (File ► Open) здатна відкривати зовнішні файли різних форматів, дані з яких потім можна розподіляти по стовпцях електронної таблиці Поведінка цієї таблиці в Excel в якійсь мірі залежить від зовнішнього джерела даних У деяких випадках, наприклад при імпорті текстових файлів, зовнішні дані набувають стандартний табличний вигляд При бажанні їх неважко перетворити в обєкт типу «список» Цей обєкт можна опублікувати на сайті SharePoint, правда, опублікований список не звязується з вихідним текстовим файлом і тому не здатний оновлюватися первісним джерелом даних Деякі формати зовнішніх даних, такі як таблиці Microsoft Access, стають в Excel так званими діапазонами зовнішніх даних Вони підтримують оновлення з метою відповідати поточним змінам даних у зовнішньому джерелі, але їх не можна перетворювати в обєкт «список» і публікувати на сайті SharePoint

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

Відкриття текстових файлів

Щоб імпортувати дані текстового файлу з роздільниками або з полями фіксованої ширини, виберіть команду Файл ► Відкрити, а потім у діалоговому вікні Відкриття документа (Open) виберіть тип Текстові файли (Text Files) у списку, типів файлів (У файлі з роздільниками поля відокремлюються один від одного зумовленими символами, зазвичай запитом або пробілами В файлі з полями фіксованої ширини кожне поле містить певну кількість символів, і це досягається за рахунок додаткових пробілів) Ви побачите список файлів з розширеннями PRN, ТХТ і CSV Якщо цікавить вас файл має інше розширення, наприклад LOG або AS С, виберіть пункт Всі файли (All Files) у списку, типів файлів Все це потрібно, щоб побачити файл у списку, а насправді Excel визначає тип файлу, що відкривається по його вмісту, ^ тому його розширення не грає ніякої ролі Якщо програма не зуміє розпізнати формат файлу і прочитати дані, вона повідомить вам про це

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

Рис 291Майстер імпорту тексту дозволяє задати деякі параметри, які Excel

задіє при аналізі тексту

Використання Майстра імпорту тексту

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

У першому вікні майстра, представленому на рис 291, відображений фрагмент імпортованого файлу, а також результат автоматичного визначення формату даних (з роздільниками або фіксованої ширини) Як правило, майстер не помиляється, але якщо щось пішло не так, ви завжди можете його поправити (Якщо ви самі точно не знаєте формату вихідного файлу, натисніть кнопку Далі (Next), і вже на наступному кроці вам стане ясно, чи правильно було зроблено вибір) Перед тим як перейти до наступного кроку, слід переконатися в тому, що програма вірно розпізнала кодування символів, призначену текстового файлу Якщо він був створений додатком Windows, в

списку, що розкривається Формат файлу (File Origin) потрібно вибрати кодування Windows (ANSI) Якщо файл був створений програмою під управлінням MS-DOS, виберіть варіант DOS (PC-8) Якщо ж у файлі використовується будь-яка інша таблиця символів, спробуйте знайти її у списку Формат файлу Для файлів, що походять з інших операційних систем, найбільш імовірною є кодування Windows (ANSI)

Пропустити кілька перших рядків файлу можна за допомогою лічильника Почати імпорт з рядка (Start Import At Row) Рядки з заголовками полів часто призводять до помилок при розподілі вихідних даних по стовпцях, тому їх краще виключати

Вид наступного вікна майстра залежить від формату файлу, тому ми представили два малюнка – 292 і 293 В обох випадках вертикальні лінії в області попереднього перегляду Зразок розбору даних (Data Preview) показують, яким чином Excel збирається розібрати вихідний текст по стовпцях Область перегляду дозволяє бачити одночасно лише 5 рядків і 65 символів у кожному рядку, але переглянути будь-який фрагмент файлу можна за допомогою смуг прокрутки

Рис 292 Для файлу з роздільниками друге вікно майстра показує, який символ Excel

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

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

Прапорець Вважати послідовні роздільники одним (Treat Consecutive Delimeters As One) дозволяє задавати послідовності розділових символів Наприклад, якщо початково роздільником була група з двох або більше знаків табуляції, а ви порахуєте таким кожен окремий знак табуляції, це призведе до неправильного разбиению даних на стовпці Тому в деяких ситуаціях використання даного прапорця є необхідним

При імпорті файлу з фіксованою шириною полів первісна спроба Excel

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

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

Рис 293 Імпортуючи файл з полями фіксованої ширини, довіряйте Excel, але перевіряйте

При наявності помилок (що цілком можливо) внесіть необхідні виправлення

Третє вікно Майстра текстів (рис 294) дозволяє задати тип даних для кожного стовпця Ваш вибір обмежений наступними варіантами: Загальний (General), коли до тексту застосовується текстової формат, до чисел – числовий, а до дат – формат дати Текстової (Text), де всі дані, включаючи числа, форматуються як текст Дата (Date) і Пропустити стовпець (Skip) За замовчуванням Excel призначає для всіх стовпців тип Загальний, і, зрозуміло, в деяких ситуаціях вам доведеться внести зміни Наприклад, якщо значення в одному з текстових полів починається з дефіса, Excel намагається інтерпретувати подальший текст як формулу, так як за замовчуванням для типу

Загальний дефіс вважається мінусом Щоб уникнути помилки необхідно встановити для відповідного стовпця тип даних Текстовой

Рис 294 У цьому вікні майстра задається формат для кожного стовпчика Тут же можна відмовитися від імпорту деяких стовпців

УВАГА

Числа, що містять 16 і більше цифр (такі, як номери кредитних карток), спотворюються Excel, якщо вони не представлені в текстовому форматі Це пояснюється обмеженням точності представлення чисел в Excel, яка становить лише 15 цифр

Діалогове вікно третього кроку майстра також містить кнопку Детальніше (Advanced), що відкриває вікно Додаткова настройка імпорту тексту (Advanced Text Import Settings) Це діалогове вікно дозволяє змінити параметри визначення числових даних, у тому числі символи-роздільники десяткових розрядів і цілої / дробової частини числа За замовчуванням діють установки розділу Мова і регіональні стандарти (Regional Settings) Панелі управління Windows Якщо у файлі використовуються інші параметри, необхідно внести відповідні корективи у вікні Додаткова налаштування імпорту тексту

Розбір тексту, що вставляється з буфера обміну

Іноді при роботі із зовнішніми текстовими даними зустрічаються довгі текстові рядки, які потрібно розділити на частини і вставити останні в окремі стовпці Таке буває, наприклад, при вставці даних в книгу Excel через буфер обміну Щоб розподілити такі дані по стовпцях, виділіть їх, а потім у меню Дані (Data) виберіть команду Текст за стовпцями (Text To Columns) Ви знову опинитеся під опікою Майстри текстів, але який змінив назву

Відкриття таблиць Microsoft Access

За допомогою команди Файл ► Відкрити (File ► Open) ви можете імпортувати таблиці з баз даних Microsoft Access Після відкриття діалогового вікна Відкриття документа (Open) виберіть тип Бази даних Access (Access Databases) у списку, типів файлів Вкажіть потрібний файл MDB і натисніть кнопку Відкрити (Open) На екрані зявиться діалогове вікно Виділити таблицю (Select Table), показане на рис 295

Рис295 Діалогове вікно Виділити таблицю містить список таблиць і уявлень, доступних для імпорту в Excel

Список в діалоговому вікні Виділити таблицю насправді містить поряд з таблицями також і уявлення (Наприклад, всі елементи на рис 295 є уявленнями з бази даних «Борей», входить в комплект поставки Microsoft Access) Якщо ви відкриєте уявлення Access в Excel, то отримаєте всі записи, які відображаються цією виставою в Access При відкритті таблиці імпортуються всі її записи

У будь-якому випадку список в Excel буде повязаний з джерелом даних в Access, тому ви можете оновлювати його Щоб список відповідав поточному стану джерела даних в Access, виділіть будь-яку комірку в списку та виберіть команду Оновити дані (Refresh External Data) в меню Дата (Data) або натисніть однойменну кнопку на панелі інструментів Зовнішні дані (External Data) При потребі оновлювати список за розкладом виберіть команду Дані ► Імпорт зовнішніх даних ► Властивості діапазону даних (Data ► Import External Data ► Data Range Properties) або скористайтеся відповідною кнопкою панелі інструментів Зовнішні дані і потім вкажіть часовий інтервал у хвилинах

Відкриття файлів dBase

Щоб відкрити файл dBase в Excel, виберіть команду Файл ► Відкрити і потім у діалоговому вікні Відкриття документа виберіть тип Файли dBase (dBase Files) у списку, типів файлів Після цього в діалоговому вікні будуть відображатися тільки файли з розширенням DBF Якщо з якихось причин ваш файл dBase має

інше розширення, виберіть у списку типів файлів пункт Всі файли (Alt Files) Excel

розпізнає файл, що відкривається як базу даних dBase незалежно від розширення

Команда Файл ► Відкрити імпортує файл dBase цілком в неоновлювані список Excel Щоб імпортувати тільки певні записи і мати можливість синхронізувати їх зі зовнішнім джерелом, ви повинні створити запит за допомогою команди Дані ► Імпорт зовнішніх даних ► Створити запит (Data ► Import External Data ► New Database Query)

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*