Безпосередня робота з Microsoft Query – ЧАСТИНА 2

Умови рівності

Найпростішим критерієм є умова рівності значення поля деякої заданої величини Query формулює такі умови гранично просто

1 Виділіть у поле значення, яке вас цікавить

2 Натисніть кнопку Фільтр по виділеному (Criteria Equals) на панелі інструментів

Microsoft Query (рис 2927)

Створити запит Перегляд SQL Умови Виконати запит автозапрос

Довідка

Відкрити запит Зберегти файл

Повернути данн

Таблиці Додати таблицю

Фільтр по виділеному

Сортування за спаданням Сортування за зростанням

Цикл за груповими операціями

Рис 2927 Панель інструментів Microsoft Query

Припустимо, що ми хочемо відфільтрувати таблицю результатів, представлену на рис 2923, так, щоб у ній залишилися тільки записи, в яких значення в поле Назва рівні Karkki Oy Для цього достатньо виділити значення Karrki Oy в будь запису і натиснути кнопку Фільтр по виділеному У відповідь на це Query виведе на екран область умов (якщо вона ще не відображена) і застосує новий фільтр до таблиці, як показано на рис 2928

Рис 2928 Результат фільтрації за назвою фірми-постачальника

РАДА

Після використання кнопки Фільтр по виділеному Query залишає поточне значення умови виділеним в області умов Ви можете швидко переключитися на іншу умову рівності, замінивши поточний значення в галузі умов новим і натиснувши клавішу Enter

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

РАДА

Щоб видалити область умов, натисніть кнопку Відображення умов на панелі інструментів або виберіть команду Вид ► Умови (View ► Criteria) Щоб видалити область таблиць, натисніть кнопку Відображення таблиць на панелі інструментів або виберіть команду Вид ► Таблиці (View ► Tables)

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

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

ті, у яких значення поля Назва одно ТОВ Екзотика, а значення поля Категорія одноНапоїЗвичайним чином ви можете вибрати значення ТОВ Екзотика в стовпці Назва і натиснути Фільтр по виділеному, а потім – значення Напої в стовпці Категорія і натиснути Фільтр по виділеному ще раз У результаті в області умов зявляться дві умови, розташованих на одному рядку (рис 2929) Як і в діапазоні умов розширеного фільтра Excel, критерії, що знаходяться в одному рядку області умов, обєднуються логічним оператором І

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

За допомогою меню умови рівності створюються таким чином:

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

2 Виберіть у меню Умови (Criteria) команду Додати умова (Add Criteria) і у вікні Додавання умови (Add Criteria) натисніть кнопку Додати (Add)

Видалення умов

Щоб видалити умову відбору, виділіть його заголовок у галузі умов і натисніть клавішу Delete Щоб видалити всі умови і відновити неотфільтро-ванну таблицю результатів, виберіть у меню Умови команду Видалити всі умови (Remove AIL Criteria)

Умови порівняння

Створення умов порівняння починається з вибору команди Додати умова в меню

Умови Ви побачите діалогове вікно, аналогічне представленому на рис 2930

Рис 2930Діалогове вікно Додавання умови позволяетвибрать поля, оператори порівняння та значення

У вікні Додавання умови можна сконструювати необхідна умова, використовуючи списки, що розкриваються Так, розкривши список Поле (Field), ви можете вибрати в ньому потрібне поле, а потім – оператор, наприклад Більше (Is Greater Than), у списку Оператор (Operator) Введення даних у полі Значення (Value) здійснюється двома способами: з клавіатури або вибором із списку натискаючи кнопки Значення (Values) В останньому випадку зявиться діалогове вікно Вибір значень, що містить список всіх значень для обраного поля Однак у більшості випадків легше набрати значення, а не шукати його у списку Після заповнення діалогового вікна Додавання умови натисніть кнопку Додати У відповідь на це Query створить відповідний рядок у галузі умов і (якщо включений відповідний режим) виконає новий запит Вікно Додавання умови залишається відкритим, що дозволяє сформулювати додаткові критерії Для цього встановіть перемикач в положення І або АБО і введіть іншу інформацію, як описано раніше Коли закінчите опис умов, клацніть на кнопці Закрити

ПРИМІТКА

Для умов порівняння, що не містять обчислюваних полів, переконайтеся, що поле Групова операція (Total) у діалоговому вікні Додавання умови є порожнім (Ми обговоримо групові операції пізніше в розділі «Фільтрація за обчислюваним полям» даної глави)

Фільтрація по полях, не включених в таблицю результатів

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

Виняток з таблиці результатів дублікатів значень

Для того щоб виключити з таблиці результатів дублікати значень, виберіть у меню Вигляд (View) команду Властивості запиту (Query Properties) і в однойменному діалоговому вікні встановіть прапорець Лише унікальні значення (Unique Values ​​Only) Це можна зробити як перед створенням фільтру, так і після

Порівняння полів

Умови порівняння дозволяють порівнювати значення одного поля зі значенням іншого поля Це робиться таким чином: у діалоговому вікні Додавання умови (рис 2930) у списку, Поле виберіть імя одного з порівнюваних полів Потім із списку Оператор виберіть необхідний оператор порівняння І нарешті, в полі Значення вкажіть імя другого з порівнюваних полів аналогічно рис

2931 Тут зверніть увагу на нетиповий використання поля Значення, в якому в ролі «значення» виступає імя поля

Рис 2931 Ця умова дозволяє відібрати записи, в яких значення поля НаСкладе менше значення поля Очікується

Режим виконання запиту

За замовчуванням Microsoft Query оновлює таблицю результатів кожен раз при додаванні нового поля в область даних, при зміні порядку розташування полів в області даних, порядку сортування або умов відбору записів У відповідь на ці дії Query створює і виконує нову інструкцію SQL (яку можна побачити, натиснувши кнопку Режим SQL (View SQL) на панелі інструментів), що представляє запит Якщо джерело даних дуже великий або мережу досить завантажена потоками інформації, при використанні режиму автоматичного виконання запиту можуть виникати помітні затримки Тому часто має сенс відключити цей режим і ініціювати запити тільки на вимогу

Щоб визначити, чи включений режим автоматичного виконання запиту, досить поглянути на кнопку Автоматичний режим (Auto Query) на панелі інструментів Якщо кнопка виглядає натиснутою, значить, режим активний Для його відключення досить

натиснути (тобто віджати) кнопку Автоматичний режим або вибрати однойменну команду в меню Записи (Records)

Щоб виконати поточний запит на вимогу, натисніть кнопку Виконати запит

(Query Now) або скористайтеся командою Виконати запит меню Записи

Обчислення підсумків

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

Query розглядає всі обчислення як підсумки, хоча підсумовування значень – це лише одна з пропонованих програмою можливостей Іншими загальними для всіх драйверів баз даних підсумковими функціями є Середнє (AVG), Число (COUNT), Мінімум (MIN) і Максимум (МАХ) Деякі драйвери підтримують додаткові підсумкові функції

Кнопка Цикл за груповими операціями

Підсумкові обчислення можна виконати за допомогою кнопки Цикл за груповими операціями (Cycle Through Totals) на панелі інструментів Query Наприклад, для того щоб обчислити суму всіх значень поля Очікується, необхідно виконати наступні дії:

1 Включіть в область даних тільки поле Очікується і видаліть всі умови з області умов

2 Виділіть стовпець Очікується і натисніть кнопку Цикл за груповими операціями

Як показано на рис 2932, Query відображає в області даних загальну суму значень по полю Очікується

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

ПРИМІТКА-

Для деяких типів полів доступні не всі підсумкові функції

Використання команд меню

Крім кнопки на панелі інструментів у вашому розпорядженні є команда Змінити стовпець (Edit Column) меню Записи (Records) Виберіть цю команду і після відкриття діалогового вікна Зміна шпальти (Edit Column) вкажіть потрібну функцію в списку, що розкривається Групова операція (Total), як показано на рис 2933

Рис 2932 Результат підрахунку суми значень по полю Очікується за допомогою кнопки Цикл за груповими операціями

Рис 2933 Для виконання обчислень у таблиці результатів можна замість кнопки Цикл за груповими операціями використовувати команду Змінити стовпець

Обчислення підсумків для груп записів

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

1 В області даних виведіть стовпець Назва, а за ним – стовпець Очікується

2 Виділіть стовпець Очікується і натисніть кнопку Цикл за груповими операціями на панелі інструментів Query

Як видно з рис 2934, тут Query виводить для кожного постачальника одну запис з полем для суми всіх одиниць товару

Рис 2934 Приклад обчислення підсумків для груп записів

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

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

Фільтрація за обчислюваним полях

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

Створення запиту з параметрами

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

виконання (Auto Query) на панелі інструментів Query Потім за допомогою діалогового вікна Додавання умови або прямо в галузі умов оформите умова, але замість конкретного значення введіть текст підказки (запрошення), укладений у квадратні дужки (Підказка не зобовязана бути ідентичною імені поля, але може містити це імя) По ходу виконання запиту користувач побачить діалогове вікно з текстом підказки Обмежень на число параметрів запиту немає На рис 2936 показаний приклад запиту з параметрами

Рис 2935 Ми отримали вибірку з постачальників, у яких замовлено не менш 20

одиниць товару

Збереження запиту

Щоб зберегти визначення запиту у файлі DQY, в меню Файл (File) виберіть команду Зберегти запит (Save Query) і потім введіть імя файлу в діалоговому вікні Цей крок не є обовязковим Якщо ви не збережете запит, ви все одно зможете оновлювати його з діапазону зовнішніх даних, який він створить в аркуші Excel Але такий запит не можна буде використовувати знову в будь-якому іншому Аркуші

Передача таблиці результатів у Microsoft Excel

Щоб передати таблицю результатів в Excel для подальшої роботи з нею, в меню Файл виберіть команду Повернути дані в Microsoft Excel (Return Data To Microsoft Excel) Зявиться діалогове вікно Імпорт даних (Import Data) (див рис 2915), в якому ви повинні вказати, куди слід помістити вміст таблиці

Рис 2936 Створюваний запит при своєму виконанні буде запитувати користувача ввести марку товару

Витяг інформації з Інтернету за допомогою веб-

запитів

Веб-запити дозволяють витягати з Інтернету або корпоративної мережі вашої організації таку специфічну інформацію, як біржові котирування, результати спортивних змагань або поточні дані продажів товарів вашої фірми Вебзапроси в стані запитувати вас, яка саме інформація вам потрібна, або при кожному своєму виконанні надавати одну і ту ж інформацію Разом з Excel 2003 поставляються навчальні запити, з якими ми рекомендуємо вам потренуватися

Графічний інтерфейс Excel дозволяє створювати запити шляхом простого зазначення на потрібні дані Вам надається можливість оновлювати запит регулярно або в задані моменти часу, а також зберігати запит у файлі IQY для використання в інших робочих аркушах Excel Зауважте: для побудови запиту до веб-сторінки вам зовсім не обовязково знати її структуру

Використання готового веб-запиту

Щоб запустити на виконання існуючий веб-запит (наприклад, один з тих, що включені в комплект Excel або створені ким-небудь з користувачів),

виберіть команду меню Дані ► Імпорт зовнішніх даних ► Імпортувати дані (Data ► Import External Data ► Import Data) і відкрийте один з файлів з розширенням IQY, перерахованих у списку Зявиться діалогове вікно Імпорт даних (Import Data), в якому слід вказати, куди робити імпорт даних Якщо кнопка Параметри (Parameters) доступна, то, натиснувши її, ви потрапите в діалогове вікно, аналогічне представленому на рис 2937

Рис 2937 У цьому діалоговому вікні ви можете вибрати спосіб отримання значення параметра веб-запитом

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

У разі використання діапазонів, що містять значення параметрів веб-запиту, ви можете також встановити прапорець Автоматично оновлювати при зміні значення комірки (Refresh Automatically When Cell Value Changes), щоб запит автоматично підлаштовувався під вміст діапазону

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

На рис 2938 показані дані, отримані в результаті виконання одного з вебзапросов, включених в комплект поставки Excel 2003 Зверніть увагу на те, що цей запит поряд зі звичними даними повернув гіперпосилання Ви можете клацнути на будь гіперпосиланням, щоб отримати додаткову інформацію, розміщену на іншій сторінці сайту MSN MoneyCentral

Рис 2938 У даних, отриманих в результаті цього веб-запиту, містяться гіперпосилання на сайт MSN MoneyCentral

Створення веб-запиту

У Excel веб-запити створюються трьома способами:

► за допомогою команди Дані ► Імпорт зовнішніх даних ► Створити веб-запит

(Data ► Import External Data ► New Web Query)

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

► командою Експорт в Microsoft Excel (Export To Microsoft Excel)

Команда Створити веб-запит

Для того щоб сформулювати запит за допомогою команди Створити веб-запит,

виконайте наступні дії:

1 Виберіть команду Дані ► Імпорт зовнішніх даних ► Створити веб-запит На екрані зявиться форма Створення веб-запиту (New Web Query), представлена ​​на рис 2939 Дана форма – не що інше, як урізаний браузер, і спочатку на ньому відображається ваша домашня сторінка

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

3 Натисніть кнопку Пуск (Go) Тепер у вікно форми завантажиться сторінка сайту, адреса якого ви вказали на попередньому кроці На рис 2940 показано вікно форми Створення веб-запиту з відображеної на вимогу веб-сторінкою

Рис 2939 Форма Створення веб-запиту являє собою спеціальний браузер

Рис 2940 Так виглядає у формі Створення веб-запиту цікавить нас сайт

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

Збереження запиту і завдання параметрів форматування

Після того як імпортовані дані виділені, саме час натиснути кнопку Імпорт (Import), що призведе безпосередньо до створення запиту Однак перед цим ви, можливо, захочете зберегти запит або попрацювати з його параметрами Для того щоб зберегти запит у його поточній формі, клацніть на значку Зберегти запит (Save Query) зліва від кнопки Параметри (Options) у вікні форми Для доступу до параметрами запиту натисніть кнопку Параметри На рис 2941 представлено діалогове вікно Налаштування веб-запиту (Web Query Options)

Рис 2941 Це діалогове вікно дозволяє задавати різні параметри веб-запиту, зокрема рівень складності форматування даних, що імпортуються в Excel

Встановіть перемикач Формат (Formatting) в положення Ні (None), якщо хочете імпортувати дані як простий текст Якщо потрібно проігнорувати гіперпосилання і деякі інші елементи форматування, виберіть варіант Тільки формат RTF (Rich Text Formatting Only) Якщо ж ви хочете зберегти вихідне форматування в максимальному ступені, виберіть варіант Повністю формат HTML (Full HTML Formatting) На рис 2942 представлений результат запиту до сайту з підтримкою оригінального формату HTML Дані цього запиту містять гіперпосилання

Якщо обрані параметри запиту призведуть до не задовольняє вас результату, ви завжди можете змінити форматування і повторити запит Для цього виберіть команду Дані ► Імпорт зовнішніх даних ► Змінити запит (Data ► Import External Data ► Edit Query) Тим самим ви опинитеся у вікні форми

Зміна веб-запиту (Edit Web Query), яка відрізняється від форми Створення веб-запиту лише заголовком Для того щоб змінити настройки запиту, натисніть кнопку Параметри (Options) у цій формі

Рис 2942 Так виглядає фрагмент веб-сторінки, імпортований з параметром Повний формат HTML

Копіювання і вставка даних з браузера

Метод створення веб-запитів, описаний раніше, хороший у тому випадку, якщо ви починаєте створення запиту в Excel і знаєте адресу ресурсу, до якого будете звертатися із запитом Однак у вас є також можливість почати процес створення запиту прямо в браузері Виділіть потрібні дані, скопіюйте їх за допомогою комбінації клавіш Ctrl + C, а потім створіть в Excel новий робочий лист і вставте скопійоване інформацію, натиснувши клавіші Ctrl + V У правому нижньому кутку вставленого діапазону зявиться значок смарт-тега Відкрийте його меню, показане на малюнку, і виберіть команду Створити оновлюваний веб-запит (Create Refreshable Web Query) Вставлені дані відобразяться у формі Створення веб-запиту, і ви зможете зробити необхідні налаштування вашого запиту

Експорт в Microsoft Excel з браузера Internet Explorer

Якщо в якості браузера-використовується Internet Explorer, запит легко ініціювати, клацнувши правою кнопкою миші на веб-сторінці і вибравши в меню пункт Експорт в Microsoft Excel (Export To Microsoft Excel) (Якщо ця команда відсутня в меню, ймовірно, в даній веб-сторінці вже щось виділено Зніміть це виділення і повторіть спробу) Виконання команди призводить до запуску окремого примірника Excel, щоб уникнути втрат поточної робочої інформації Якщо ваш клацання припав на обєкт, який Internet Explorer розпізнає як HTML-таблицю, ця таблиця буде імпортована прямо за адресою Лист1 А1 як новий веб-запит В іншому випадку на екрані зявиться форма Створення веб-запиту

Джерело: Ефективна робота: 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>

*

*