Полегшення читання зведених даних за допомогою перехресного запиту

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

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

У цій статті

Огляд

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

Використання діапазонів та інтервалів для заголовків

Запит параметра для обмеження заголовків рядків

Заміна Null-значень на нулі

Поради щодо зведеного запиту

Огляд

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

вибірковий запит і перехресний запит, які відображають однакові дані

1. Цей вибірковий запит групує зведені дані по вертикалі за працівником і категорією.

2. Цей зведений запит відображає ті самі дані, групуючи їх по горизонталі та вертикалі.

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

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

схема перехресного запиту

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

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

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

4. Тут відображаються зведені значення.

Переваги використання майстра зведених запитів

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

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

  • Використання запиту як основи.    Рекомендовано використати майстер для створення потрібного базового зведеного запиту, а потім настроїти структуру запиту в режимі конструктора.

Функції зведеного запиту, доступні в режимі конструктора

  • Введення даних під час виконання запиту (додавання запиту параметра).    Під час додавання рядка параметра запит відображає діалогове вікно та пропонує вказати дані, які потрібно включити. Наприклад, ваш зведений запит відображає обсяги продажів за категоріями продуктів протягом кварталу, але потрібно, щоб одночасно відображалися дані лише для одного регіону. У такому випадку можна використати параметр, щоб у запиті пропонувався вибір потрібного регіону.

  • Використання виразів як полів у запитах.    Інколи потрібно виконувати проміжні підрахунки або інші дії з даними для поля. Наприклад, якщо у ваших даних є багато Null-значень, результати зведеного запиту, можливо, неправильні або неповні. Щоб виправити це, застосуйте до поля функцію Nz – функція замінить Null-значення на нулі.

  • Зазначення списку фіксованих значень для використання замість заголовків стовпців.    Можливо, вам знадобиться замінити заголовки стовпців на власні значення. Наприклад, якщо в заголовках стовпців є інтервали, вони не сортуються належним чином. Жодних проблем – просто замініть їх на значення, які сортуватимуться правильно.

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

Частини перехресного запиту, відображені в режимі конструктора

1. Параметри в цих рядках визначають, чи поле – це заголовок рядка, заголовок стовпця або зведене значення.

2. Цей параметр відображає значення поля як заголовки рядків.

3. Цей параметр відображає значення поля як заголовки стовпців.

4. Ці параметри створюють зведені значення.

На початок сторінки

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

Початок роботи з майстром зведених запитів

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

  1. На вкладці Створити у групі Запити натисніть кнопку Майстер запитів.

  2. У діалоговому вікні Новий запит виберіть пункт Майстер зведених запитів і натисніть кнопку OK.

    Буде запущено майстер зведених запитів.

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

  4. На наступній сторінці виберіть поле, що містить значення, які потрібно використовувати як заголовки рядків.

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

    Примітка : Якщо для заголовків рядків вибрати кілька полів, порядок вибору полів визначає порядок за промовчанням, у якому будуть відсортовані результати.

  5. На наступній сторінці виберіть поле, що містить значення, які потрібно використовувати як заголовки стовпців.

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

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

  6. Якщо для заголовків стовпців вибрати поле "Дата й час", на наступній сторінці майстра буде запропоновано вказати інтервал для групування дат. Можна вказати Рік, Квартал, Місяць, Дата або Дата/час. Якщо для заголовків стовпців не вибрано поле "Дата й час", майстер пропустить цю сторінку.

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

  8. На тій самій сторінці встановіть або зніміть прапорець Так, включати суми рядків, щоб включити або виключити суми рядків.

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

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

  9. На наступній сторінці майстра введіть ім’я запиту, а потім вкажіть, чи потрібно переглянути результати або змінити макет запиту.

Створення зведеного запиту в режимі конструктора вручну

  1. На вкладці Створити у групі Запити натисніть кнопку Макет запиту.

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

    Якщо використовується кілька джерел записів, переконайтеся, що таблиці або запити об’єднано за спільними для них полями. Додаткові відомості про об’єднання таблиць і запитів див. в розділі Див. також.

  3. Закрийте діалогове вікно Відображення таблиці.

  4. На вкладці Конструктор у групі Тип запиту натисніть кнопку Перехресний.

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

  6. У сітці макета запиту в рядку Перехресний для кожного поля заголовка рядка виберіть пункт Заголовок рядка.

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

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

  8. У сітці макета запиту в рядку Перехресний для поля заголовка стовпця виберіть пункт Заголовок стовпця.

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

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

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

  10. У сітці макета запиту в рядку підсумків для поля зведених значень виберіть агрегатну функцію для обчислення значень.

  11. У рядку Перехресний для поля зведених значень виберіть Значення.

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

  12. На вкладці Конструктор у групі Результати натисніть кнопку Запуск.

Указання фіксованих значень для заголовків стовпців

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

  1. Відкрийте зведений запит у режимі конструктора.

  2. Якщо вікно властивостей не відображається, натисніть клавішу F4, щоб відобразити його.

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

  4. У вікні властивостей на вкладці Загальні в полі властивості Заголовки стовпців введіть список значень, які потрібно використовувати як заголовки стовпців, з роздільниками-комами.

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

На початок сторінки

Використання діапазонів та інтервалів для заголовків

Іноді потрібно згрупувати значення поля в діапазони, а потім використовувати ці діапазони для заголовків рядків або стовпців (замість використання окремих значень). Наприклад, для заголовків стовпців використовується поле "Вік". Замість окремих стовпців для кожного віку можна використовувати стовпці, які представляють вікові групи, наприклад 40–49.

Можна використати функцію IIf у виразі, щоб створити діапазони для заголовків рядків або стовпців.

Порада : Якщо потрібно створити інтервали з полем "Дата/час", варто скористатися майстром зведених запитів. Майстер дає змогу групувати дати в інтервали Рік, Квартал, Місяць, Дата або Дата/час. Якщо жоден із цих інтервалів не підходить, слід створити зведений запит у режимі конструктора, а потім створити потрібні інтервали за допомогою методу, описаного в цьому розділі.

Принцип роботи функції IIf

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

Синтаксис функції IIf

IIf( вираз, якщо_істина, якщо_хибність )

Синтаксис функції IIf має такі аргументи:

Аргумент

Опис

вираз

Обов’язковий. Вираз, який потрібно обчислити.

якщо_істина

Обов’язковий аргумент. Значення або вираз, що повертається, якщо вираз має значення True (істина).

якщо_хибність

Обов’язковий аргумент. Значення або вираз, що повертається, якщо вираз має значення False (хибність).

Створення діапазонів за допомогою виразу

  1. Відкрийте зведений запит у режимі конструктора.

  2. У сітці макета запиту в рядку Поле клацніть правою кнопкою миші пустий стовпець, а потім виберіть пункт Масштаб у контекстному меню.

  3. У вікні Масштабування введіть псевдонім поля, а потім двокрапку (:).

  4. Введіть текст IIf().

  5. У дужках після IIf введіть порівняльний вираз, який визначає перший діапазон значень поля.

    Наприклад, потрібно створити діапазони для поля "Вік", причому кожен діапазон має становити двадцять років. Порівняльний вираз для першого діапазону – [Вік]<21.

  6. Введіть кому після порівняльного виразу, а потім ім’я діапазону, взяте в лапки. Введене ім’я слугуватиме заголовком зведеного запиту для значень у межах цього діапазону.

    Наприклад, після [Вік]<21 введіть кому, а потім введіть "0–20 років".

  7. Введіть кому після імені діапазону (поза лапками), а потім виконайте одну з наведених нижче дій.

    • Щоб створити інший діапазон, введіть текст IIf() і повторіть кроки 5, 6 і 7.

    • Для останнього діапазону введіть лише ім’я діапазону.

      Наприклад, повний вкладений вираз IIf, який розділяє поле "Вік" на діапазони по двадцять років, може виглядати так (розриви рядків додано для зручності читання):

      IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))

      Примітка : Програма Access припинить обчислення виразу, щойно один з операторів IIf прийме значення "істина". Вказувати нижню межу кожного діапазону не обов’язково, оскільки будь-яке значення, яке знаходиться нижче нижньої межі заданого діапазону, вже матиме значення "істина".

  8. У сітці макета запиту в рядку підсумків виберіть пункт Групувати за.

  9. У рядку Перехресний вкажіть, чи використовувати діапазони як заголовки рядків або стовпців. Пам’ятайте, що можна вказати від одного до трьох заголовків рядків і один заголовок стовпця.

На початок сторінки

Запит параметра для обмеження заголовків рядків

Можливо, вам буде потрібно, щоб під час виконання зведений запит пропонував введення. Наприклад, якщо використовується кілька заголовків рядків, один із яких – "Країна/регіон". Замість того щоб постійно відображати дані для кожної країни або регіону, запит може запропонувати ввести ім’я, а потім відобразити дані відповідно до введених користувачем даних.

Запит параметра можна додати до будь-якого поля заголовка рядка.

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

  1. Відкрийте зведений запит у режимі конструктора.

  2. У рядку Умови для поля заголовка рядка, для якого потрібно пропонувати введення, введіть текст запитання у квадратних дужках. Текст запитання відобразиться як запит під час виконання запиту.

    Наприклад, якщо під час виконання запиту в рядку Умови ввести [Яка країна або регіон?], відобразиться діалогове вікно з запитанням "Яка країна або регіон?", полем вводу та кнопкою OK.

    Порада : Якщо параметр має бути гнучкий, об’єднайте вираз із символами узагальнення за допомогою оператора Like. Наприклад, замість використання [Яка країна або регіон?] як критерію можна використати Like [Яка країна або регіон?]&"*", щоб параметр відповідав ширшому діапазону введених даних. Використання оператора Like не змінює вигляд запиту параметра.

  3. На вкладці Конструктор у групі Відобразити або приховати натисніть кнопку Параметри.

  4. У діалоговому вікні Параметри запиту у стовпці Параметр введіть той самий запит параметра, який було використано в рядку Умови. Додайте квадратні дужки, але не додавайте жодні об’єднані символи узагальнення або оператор Like.

  5. У стовпці Тип даних виберіть тип даних для параметра. Тип даних має відповідати типу даних поля заголовка рядка.

На початок сторінки

Заміна Null-значень на нулі

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

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

Синтаксис функції Nz

Nz( варіант [, значення_якщо_null ] )

Синтаксис функції Nz має такі аргументи:

Аргумент

Опис

Варіант

Обов’язковий. Змінна, що належить до тип даних варіант (Variant).

значення_якщо_null

Необов’язковий (за винятком використання в запитах). Вираз типу варіант (Variant), що вказує значення, яке слід повернути, якщо аргумент варіант має Null-значення. За допомогою цього аргументу можна повертати значення, відмінні від нуля або рядка нульової довжини.

Примітка : Якщо використати функцію Nz у виразі в запиті без використання аргументу значення_якщо_null, то для полів, які містять Null-значення, повертатиметься рядок нульової довжини.

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

  2. У контекстному меню виберіть пункт Масштаб.

  3. У вікні Масштаб візьміть ім’я поля або вираз у дужки, а потім введіть Nz перед дужками.

  4. Введіть , 0 безпосередньо перед правою дужкою.

    Наприклад, якщо для заміни Null-значень на нулі було використано Nz з полем "Втрачені години", кінцевий вираз матиме такий вигляд:

Nz([Hours Lost], 0)

На початок сторінки

Поради щодо зведеного запиту

  • Запит має бути простий.    Через зростання кількості комбінацій рядків зведені запити можуть стати складні для сприйняття. Не використовуйте більше заголовків рядків, ніж вам потрібно.

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

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

На початок сторінки

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

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×