Використання звіту зведеної таблиці для доступу до даних зовнішньої таблиці в Excel Services

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

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

Порівняння багатовимірних і двовимірних даних

1. Перетворення багатовимірного звіту зведеної таблиці…

2. …на двовимірний звіт зведеної таблиці…

3. …що дає змогу переглянути табличні дані в Excel Services.

Виберіть одну з нижченаведених можливостей.

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

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

Роблять двовимірні табличний звіт зведеної таблиці

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

Додавання розмітити та впорядкування полів в області підписи рядків

Змініть параметри зведеної таблиці та параметри поля

Розмічення звіту зведеної таблиці як двовимірної таблиці

Настроювання макета звіту зведеної таблиці

Публікування книги в службах Excel Services

Дослідження підтримки підключень до зовнішніх джерел даних в Excel Services

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

Під час використання майстра зв'язків даних або програми Microsoft Query для підключення до зовнішніх даних, які зазвичай створення зовнішнього діапазону даних. Єдиний виняток – це під час створення звіту зведеної таблиці, яку підключено до зовнішніх даних. Звіт зведеної таблиці не створює діапазону зовнішніх даних. Цю різницю в тому, що створено зв'язки важливо розуміти під час публікації книги в Excel Services, оскільки служби Excel Services лише підтримує підключення до зовнішніх даних на основі звітів зведених таблиць і не підтримує діапазони зовнішніх даних.

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

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

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

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

Звичайно згруповані дані базуються на сортуванні даних за одним або кількома стовпцями. Наприклад, якщо потрібно згрупувати перелік співробітників за відділами, в яких вони працюють., можна відсортувати дані спочатку за відділами, а потім за прізвищами працівників. Групи можуть вкладатися одна в одну, наприклад, «Лінія продуктів» -> «Категорія» -> «Продукт» або «Розташування» -> «Країна» -> «Область» -> «Місто». За промовчанням звіти зведених таблиць автоматично сортуються в порядку справа наліво, утворюючи групи та підгрупи, щоб краще було видно споріднені дані, заголовки та підсумки й можна було розгортати або згортати споріднені елементи.

Дані, згрупованих за Лініяа потім ознакою в межах кожної Лінія за категорією

Лінія продуктів

Категорія

Продукт

Двигуни

Авіаційні

Поршневі

Реактивні

Ракетні

Автомобільні

269 к.с.

454 к.с.

Вихлопні труби

Легкові авто

Наджаростійкі

Стандартні

Позашляховики

Малі

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

Однакові дані Лінія розгрупувати, додавши стовпець Код

Код

Лінія продуктів

Категорія

Продукт

WM-345

Двигуни

Авіаційні

Поршневі

TM-231

Двигуни

Авіаційні

Реактивні

BSTR-567

Двигуни

Авіаційні

Ракетні

6C-734

Двигуни

Автомобільні

269 к.с.

8C-121

Двигуни

Автомобіль

454 к.с.

MF-202

Вихлопні труби

Легкові авто

Наджаростійкі

MF-321

Вихлопні труби

Легкові авто

Стандартні

MF-211

Вихлопні труби

Позашляховики

Малі

Якщо стовпець не потрібно відображати, після його додавання до звіту зведеної таблиці стовпець можна приховати. (Виділіть стовпець і на вкладці Основне у групі Клітинки клацніть стрілку біля кнопки Формат, виберіть пункт Приховати й відобразити, а потім пункт Приховати стовпці.)

Увага! : Якщо звіт зведеної таблиці виявляє у джерелі даних кілька повторюваних рядків даних, відображається лише один такий рядок. Якщо мають відображатися всі рядки, навіть повторювані, слід додати до таблиці, звіту або подання, які імпортуються із джерела даних, стовпець з унікальними значеннями. За відсутності такого стовпця у джерелі даних його необхідно додати до джерела. Наприклад, до таблиці Microsoft Office Access можна додати стовпець із даними типу AutoNum, до таблиці Microsoft SQL Server — стовпець із даними типу Identity, або додати обчислюваний стовпець, який відображає унікальне число для кожного запису, до запиту або подання бази даних.

Редагування даних клітинок і додавання обчислюваних стовпців

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

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

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

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

Створення діаграм

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

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

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

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

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

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

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

Порівняння властивостей зовнішнього діапазону даних і можливостей зведеної таблиці

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

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

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

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

  • додати нові клітинки для нових даних та очистити невикористовувані (варіант за промовчанням)

  • додати для нових даних нові рядки та очистити невикористовувані клітинки

  • записати нові дані в існуючі рядки та очистити невикористовувані клітинки

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

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

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

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

  • Меню Фільтр діапазону зовнішніх даних завжди видаляє елементи, які більше не дійсні, і меню фільтрування ніколи не містить нових елементів із застосованим фільтром після оновлення даних, але звіту зведеної таблиці є параметри для керування таку поведінку. Для отримання додаткових відомостей див. розділ Параметри зведеної таблиці змін і Параметри поля.

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

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

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

Крім того, якщо перший зліва стовпець використовується для унікальних значень, сортувати можна буде лише за цим стовпцем, оскільки таким чином створюється основна група для кожного рядка. Залежно від потреб користувачів може бути доцільно виконати попереднє сортування даних в Excel, а після публікації книги в Excel Services вимкнути знявши прапорець Сортування в області засобів веб-частини Microsoft Office Excel Web Access.

Використання гіперпосилань

На відміну від зовнішніх діапазонів даних, активні посилання у клітинках зведеної таблиці не підтримуються. Гіперпосилання вважається текстом і не спрямовує користувача на веб-сторінку або документ, як в Excel, так і в Excel Services.

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

Створення двовимірного, табличного звіту зведеної таблиці

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

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

Створення звіту зведеної таблиці та його підключення до зовнішнього джерела даних

  1. Виділіть пусту клітинку.

  2. На вкладці Вставлення, у групі Таблиця натисніть кнопку Зведена таблиця та виберіть пункт Зведена таблиця.

    зображення стрічки excel

    Відкриється діалогове вікно Створення зведеної таблиці.

  3. Виберіть перемикач Використовувати зовнішнє джерело даних.

  4. Натисніть кнопку Вибрати підключення.

    На Відображається діалогове вікно Наявні підключення .

  5. У списку Показати вгорі діалогового вікна виберіть категорію підключень, до якої належить потрібне підключення, або виберіть пункт Усі підключення (вибраний за промовчанням).

  6. Виберіть підключення у списку Виберіть підключення та натисніть кнопку Відкрити.

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

    • Щоб помістити звіт зведеної таблиці на новий аркуш починаючи з клітинки A1, виберіть перемикач новий аркуш.

    • Щоб помістити звіт зведеної таблиці на наявний аркуш, виберіть перемикач наявний аркуш і введіть адресу першої клітинки діапазону, в якому має розташуватися звіт зведеної таблиці.

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

  8. Натисніть кнопку ОК.

В указаному розташуванні буде створено пустий звіт зведеної таблиці та відображено список полів зведеної таблиці.

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

Додавання, розміщення та впорядкування полів в області «Позначки рядків»

За допомогою списку полів зведеної таблиці додайте, розташуйте та впорядкуйте поля. Усі додані поля мають міститися в області «Позначки рядків».

  1. Клацніть звіт зведеної таблиці.

  2. Виконайте одну або кілька таких дій.

    • Щоб додати поля до звіту, виконайте такі дії.

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

        За промовчанням нечислові поля додаються до області «Позначки рядків», числові поля — до області «Значення», а поля дати/часу — до області «Позначки стовпців».

      • Щоб перемістити поле до області «Позначки рядків», клацніть його правою кнопкою миші та виберіть у контекстному меню команду Додати до підписів рядків.

        Порада : Також можна клацнути ім'я поля та перетягнути його зі списку до потрібної області.

    • Щоб змінити порядок полів, клацніть ім'я поля в одній з областей і виберіть одну з таких команд.

Переміщення вгору   

Поле переміститься на одну позицію вгору в області.

Перемістити вниз   

Поле буде переміщено в області на одну позицію вниз.

Перехід на початок   

Поле переміститься на початок області.

Переміщення в кінець   

Поле переміститься в кінець області.

Переміщення до елемента позначки рядків   

Поле переміститься до області позначок рядків.

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

    Порада : Також можна клацнути ім'я поля на макеті звіту та витягнути його за межі макета.

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

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

Змінення настройок і параметрів полів зведеної таблиці

Щоб забезпечити поведінку звіту зведеної таблиці, подібну до зовнішнього діапазону, виконайте такі дії.

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

  1. Клацніть звіт зведеної таблиці.

  2. На вкладці Параметри, у групі Зведена таблиця натисніть кнопку Параметри.

    Відкриється діалогове вікно Параметри зведеної таблиці.

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

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

  5. Щоб команди меню «Фільтр» видаляли вже не чинні елементи, після оновлення відкрийте вкладку Дані та у списку Кількість збережених елементів для одного поля виберіть пункт Немає.

Змінення настройок полів   

  1. Клацніть звіт зведеної таблиці.

  2. Щоб нові елементи відображалися у звіті зведеної таблиці із застосованим фільтром, клацніть правою кнопкою миші клітинку кожного потрібного поля, виберіть у контекстному меню команду Параметри поля, відкрийте вкладку Проміжні підсумки та фільтри та у групі Фільтр установіть прапорець Включати нові елементи до ручного фільтра.

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

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

Щоб сформувати звіт зведеної таблиці як двовимірну таблицю, виконайте такі дії.

  1. Клацніть звіт зведеної таблиці.

  2. На вкладці Конструктор, у групі Макет клацніть команду Проміжні суми та виберіть пункт Не відображати проміжні підсумки.

  3. На вкладці Конструктор, у групі Макет клацніть команду Загальна сума та виберіть пункт Вимкнути для рядків і стовпців.

  4. На вкладці Конструктор, у групі Макет клацніть команду Розмітка звіту та виберіть пункт Відображати у табличній формі.

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

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

Оздоблення звіту зведеної таблиці

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

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

  1. Клацніть звіт зведеної таблиці.

  2. На вкладці Конструктор, у групі Стилі зведеної таблиці виберіть потрібний стиль і виконайте такі дії.

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

    2. Виберіть команду Дублювати.

      Відкриється діалогове вікно Змінити експрес-стиль зведеної таблиці.

    3. Якщо потрібно введіть нове ім'я в полі Ім'я.

    4. У списку Елемент таблиці виберіть пункт Уся таблиця та натисніть кнопку Формат.

      Відкриється діалогове вікно Формат клітинок.

    5. Відкрийте вкладку Межі та створіть межу клітинок.

    6. При потребі можна внести інші зміни на вкладках Шрифт і Заливка.

    7. Двічі натисніть кнопку ОК.

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

Відображення смуг рядків або стовпців за допомогою умовного форматування   

  1. Виділіть усі стовпці у звіті зведеної таблиці.

  2. На вкладці Home, у групі Styles клацніть стрілку поруч з елементом Conditional Formatting і виберіть пункт Manage Rules.

    зображення стрічки excel

    Відкриється діалогове вікно Диспетчер правил умовного форматування.

  3. Щоб додати умовний формат, виберіть команду Створити правило.

    Відобразиться діалогове вікно Нове правило форматування.

  4. У списку Виберіть тип правила виберіть пункт Використовувати формулу для визначення клітинок для форматування.

    1. У групі Редагувати опис правила введіть формулу в полі Форматувати значення, для яких ця формула має значення ІСТИНА.

    2. Виконайте одну з таких дій.

      • Щоб утворилися смуги рядків, введіть таку формулу.

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

  • Щоб утворилися смуги стовпців, введіть таку формулу.

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  • Щоб утворилися як смуги рядків, так і смуги стовпців, введіть таку формулу.

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  1. Натисніть кнопку Форматувати. Відкриється діалогове вікно Формат клітинок.

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

    Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.

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

Публікація книги в Excel Services

  1. Натисніть Кнопку Microsoft Office Зображення кнопки Office , клацніть стрілку поруч із кнопкою опублікуватита виберіть пункт Служби Excel Services у розділі розсилання документа іншим користувачам.

  2. У полі Ім'я файлу введіть шлях до сервера та прийміть пропоноване ім'я книги або введіть нове ім'я.

    На комп'ютері під керуванням ОС Windows Vista   

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

      На комп'ютері під керуванням ОС Microsoft Windows XP   

    • У полі Папка виберіть доступне користувачам мережне розташування та натисніть кнопку Зберегти.

      Наприклад, введіть http://сервера/сайт/ім'я файлу

      Примітка : Публікація книг Excel на сервері можлива лише в XML-форматі Microsoft Office Excel 2007 (.xlsx) або у двійковому форматі Office Excel 2007 (.xlsb).

  3. Натисніть кнопку ОК.

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

  5. Натисніть кнопку Зберегти.

Додаткові відомості перегляньте статтю опублікувати книгу в Excel Services.

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

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

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

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

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

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

×