Загальні відомості про критерії запиту

Критерій подібний до формули — це рядок, який може складатися з посилань на поля, операторів Оператор. Знак або символ, який визначає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння та посилань. і констант Константа. Стале (не обчислюване) значення. Наприклад, число 210 і текст "Квартальні показники" є константами. Вираз і його значення не є константами.. У Microsoft Office Access 2007 - Українська версія також можна посилатися на критерії запиту як на вирази.

Наведені нижче таблиці містять кілька зразків критеріїв і пояснення, як вони працюють.

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

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

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

Критерії, що вказуються для різних полів у рядку Критерії, поєднуються за допомогою оператора AND. Іншими словами, критерії, указані в полях «Місто» і «Дата_народження», інтерпретуються таким чином:

Місто = "Тернопіль" AND Дата_народження < DateAdd("рррр", -40, Date())

Поля Поля «Місто» і «Дата_народження» містять критерії.

Цьому критерію відповідатимуть тільки записи, в яких поле «Місто» має значення «Тернопіль».

Цьому критерію відповідатимуть тільки записи тих, кому принаймні 40 років.

До результату увійдуть тільки записи, які відповідають обом критеріям.

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

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

Критерій «Місто» вказаний в рядку «Критерії».

Критерій «Дата_народження» вказаний в рядку «або».

Критерії, зазначені в рядкахКритерії та або, поєднуються оператором OR, як показано нижче:

Місто = "Тернопіль" OR Дата_народження < DateAdd("рррр", -40, Date())

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

Перш ніж продовжити вивчення прикладів, візьміть до уваги:

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

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

№ 39. Упорядкування баз даних

1. Упорядкування даних.

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

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

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

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

Якщо виникає потреба впорядкувати дані (розташувати в алфавітному порядку, поставити числа в порядку зростання/спадання), то для цього на панелі інструментів є кнопки:

- сортування за зростанням

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

3. Сортування за кількома параметрами.

Для сортування за кількома параметрами можна скористатися командою меню Дані → Сортування...

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

4. Фільтрація даних. Автофільтр.

База даних може містити величезне число записів (у програмі Excel природною межею служить максимальне число рядків робочого аркуша — 65536), тому не завжди треба відображати всі ці записи. Виділення підмножини загального набору записів називається фільтрацією.

Фільтрація - це виділення визначених записів, що задовольняють заданим критеріям.

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

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

Варіант Перші 10 дозволяє відібрати визначене число (чи відсоток) записів за яким-небудь критерієм.

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

5. Розширений фільтр.

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

Скасувати дію розширеного фільтра можна командою Дані → Фільтр → Показати все

№ 40. Розрахункові та підсумкові поля звіту

Звідні таблиці

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

Створення звідної таблиці

Як приклад розглянемо створення звідної таблиці, що дозволяє на основі таблиць з вихідними даними виконати аналіз продажу певних товарів в різних містах Росії. У книзі, приведеній на мал. 18.16, показаний продаж декількох моделей автомобілів: Волга, Жигулі, Ока в різних містах Росії: у Москві, Саратові і Тулі. Кожне місто показане на окремому аркуші. Передбачається, що звідні таблиці складаються по чотирьох місяцях: січень, лютий, березень і квітень. Таблиці відформатували з використанням команди Автоформат (AutoFormat) у меню Формат (Format) . Вибраний зразок з підписом Простій (Simple) .

Мал. 18.16 Вихідний список для складання звідної таблиці

Створення звідної таблиці бажано почати з виділення вічка усередині використовуваного списку (це дозволяє автоматично виділити діапазон, що містить вихідні дані).
Положенням перемикача в групі Створити таблицю на основі даних, що знаходяться: (Create Pivot table from data in:) встановите перемикач в положення: у декількох діапазонах консолідації (Multiple consolidation ranges) оскільки джерела даних для створення звідної таблиці, розташовані на різних аркуші Excel.
Призначення інших положень перемикача:

§ у списку або базі даних Microsoft Office Excel (Microsoft Office Excel list or Database ) — дозволяє створити звіт звідної таблиці або звідної діаграми за даними рядків або стовпців з підписами, розташованих на одному аркуші Excel;

§ у зовнішньому джерелі даних (External data source) — дозволяє створити звіт звідної таблиці або звідної діаграми за даними зовнішнього файлу або бази даних, наприклад, Microsoft Access, SQL Server, Paradox;.

§ у декількох діапазонах консолідації — дозволяє створити звіт звідної таблиці або звідної діаграми за даними рядків або стовпців з підписами, розташованих в декількох діапазонах консолідації;

§ у іншій звідній таблиці або звідній діаграмі (Another PivotTable report or Pivot Chart report) — використовується для створення звіту звідної таблиці або звідної діаграми за даними іншого звіту звідної таблиці в активній книзі.

Мал. 18.17 Вікно майстра звідних таблиць

У розділі Вигляд створюваного звіту (What kind of report do you want to create?) поставте перемикач в положення звідна таблиця (PivotTable) для створення лише звідної таблиці і натискуйте кнопку Далі (Next).
Якщо вихідні дані розташовані на декількох листах, то в наступному діалоговому вікні Майстер звідних таблиць і діаграм — крок 2а з 3 (PivotTable and Pivot Chart Wizard — Step 2a of3) поставте перемикач в положення Створити одне поле сторінки (Create а single page field for ті) оскільки всі листи аналогічні і відрізняються лише містом, в якому реалізовувалася продукція. Натискуйте кнопку Далі (Next) .
На екрані відображуватиметься діалогове вікно Майстер звідних таблиць і діаграм — крок 26 з 3 (PivotTable and PivotChart Wizard — Step 2b of 3) . Клацніть мишею в полі Діапазон (Range) (мал. 18.18). Виділите по черзі на всіх листах вічка з А1 по Е4, і натискуйте кнопку Додати (Add) після кожного виділення для додавання діапазону до списку вихідних діапазонів. Заслання на вихідну область буде додано в список Всі заслання (All references). Кнопка згортання вікна праворуч від поля дозволяє згортати діалогове вікно для виділення кожного діапазону. Повторне натиснення на цю кнопку відновлює вікно.
Можна зрушити діалогове вікно, аби був видний один з кутів діапазону, що виділявся. Поки йде виділення діапазону діалогове вікно автоматично згортається.

Мал. 18.18 Виділення діапазонів таблиць, що підлягають консолідації

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

§ новий аркуш (New worksheet) — звіт звідної таблиці буде розміщений на новому аркуші тієї ж книги;

§ існуючий аркуш (Existing worksheet) — звіт звідної таблиці буде розміщений на тому ж аркуші, де знаходиться вихідна таблиця. Кнопка згортання діалогового вікна праворуч від поля введення тимчасово прибирає діалогове вікно з екрану, що дозволяє вказати діапазон вічок, де буде розташована звідна таблиця, шляхом виділення вічок на аркуші. Після цього можна натискувати цю кнопку ще раз для відновлення діалогового вікна.

Кнопку Готово (Finish) доцільно натискувати перш, ніж кнопку Макет (Layout) в наступних випадках:

§ використовуються зовнішні дані з низькою швидкістю завантаження і оновлення;

§ планується створення поля сторінки і потрібно задати параметр запиту для кожного елементу окремо;

Мал. 18.19 Вибір місця розташування звідної таблиці

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

Звідну таблицю (мал. 18,20) можна створити шляхом перетягання заголовків полів в необхідну зону аркуша. Для деяких зовнішніх джерел даних, особливо для великих баз даних, .это може виявитися зручнішим в порівнянні з налаштуванням макету безпосередньо на аркуші. Так, якщо звіт створюється на основі даних куба (за допомогою майстра створення куба в Microsoft Query), налаштування макету в діалоговому вікні може значно скоротити час, який потрібний для витягання даних. Тут же можна вибрати параметри для створення полів сторінок, аби дані елементів витягувалися окремо. Параметри полів сторінок доступні лише в тому випадку, якщо джерелом даних звіту не є куб.

Мал. 18.20 Завдання місця розташування полів звідної таблиці

41. Введення та редагування бази даних з використанням команди Данные/Форма. Призначення елементів керування вікна діалогу Форма данных.

Елементи керування форми – це оригінальні елементи керування, сумісні з попередніми версіями програми Excel, починаючи з версії Excel 5.0. Елементи керування форми розроблено також для використання на аркушах макросів XLM.

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

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

Проте ці елементи керування не можна додавати до форм користувача, використовувати для керування подіями або змінювати для запуску сценаріїв веб-сторінок.

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