За допомогою зведеної таблиці для доступу до зовнішньої таблиці даних у службах Excel Services

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

Примітка.: Ця стаття зробила свою роботу та скоро буде видалена. Щоб не з’являлися повідомлення "Сторінку не знайдено", ми видаляємо посилання, про які знаємо. Якщо ви створили посилання на цю сторінку, видаліть їх. Так ми разом зробимо Інтернет кращим.

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

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

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

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

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

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

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

Малі

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

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

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

Перед публікацією книги в 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. Натисніть кнопку OK.

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

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

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

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

  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. Натисніть кнопку Зберегти.

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

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

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

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

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

×