Использование отчета сводной таблицы для предоставления доступа к данным внешних таблиц в службах Excel

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

При работе с данными таблиц, основанными на связи с внешними данными в компоненте службы Excel, использование внешнего диапазона данных (называемого также таблицей запроса) невозможно. Необходимо создать отчет сводной таблицы, с помощью которого можно сделать многомерные иерархические данные плоскими, преобразовав их в двухмерную таблицу. Ниже описывается решение этого вопроса.

Сравнение многомерных и двухмерных данных

1. Преобразование многомерного отчета сводной таблицы…

2. …в двухмерный отчет сводной таблицы…

3. …для отображения плоских табличных данных в службах Excel.

Выберите действие

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

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

Сделать создать двумерных табличный отчет сводной таблицы

Создание отчета сводной таблицы и его подключение к внешнему источнику данных

Добавление, изменение макета и расположения полей в область метки строк

Измените параметры сводной таблицы и параметры поля

Изменение макета отчета сводной таблицы как двумерная таблица

Настроить внешний вид отчета сводной таблицы

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

Поддержка службами Excel подключений к внешним источникам данных

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

При использовании мастера подключения данных или Microsoft Query для подключения к внешним данным, обычно создается диапазона внешних данных. Единственным исключением является при создании отчета сводной таблицы, подключенные к внешним данным. Отчет сводной таблицы не приводит к созданию диапазона внешних данных. Различия в способ создания подключения важно понять, когда публикация книги в службах Excel, так как службы Excel поддерживает подключения к внешним данным из отчетов сводных таблиц, а не поддерживает диапазоны внешних данных.

К началу страницы

Сведения о различиях между отчетами сводных таблиц, подключенными к внешним данным, и диапазонами внешних данных

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

Упорядочивание данных в отчетах сводных таблиц по группам и подгруппам слева направо

Обычно сгруппированные данные основываются на сортировке данных по одному или нескольким столбцам. Например, если необходимо сгруппировать сотрудников по отделам, можно выполнить первичную сортировку данных по отделу, а дополнительную — по сотрудникам. Можно также создавать вложенные группы, например: «Линия продуктов», «Категория» и «Продукт» или «Географическое расположение», «Страна или регион», «Область» и «Город». По умолчанию в отчетах сводных таблиц данные автоматически упорядочиваются по группам и подгруппам слева направо, чтобы упростить представление связанных данных, отображение заголовков и итоговых значений и развертывание или свертывание связанных элементов.

Данные, сгруппированные по Линейкеи subgrouped внутри каждой Линии продуктов по категориям

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

Категория

Товар

Двигатели

Самолеты

Подкрыльный

Хвостовой

Вспомогательный

Автомобили

269 HP

454 HP

Выхлопные трубы

Легковые

Экстрим

Стандарт

Внедорожники

Малые

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

Те же данные Линейке сглаженные путем добавления столбца Код Num

Код

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

Категория

Товар

WM-345

Двигатели

Самолеты

Подкрыльный

TM-231

Двигатели

Самолеты

Хвостовой

BSTR-567

Двигатели

Самолеты

Вспомогательный

6C-734

Двигатели

Автомобили

269 HP

8C-121

Двигатели

Автомобили

454 HP

MF-202

Выхлопные трубы

Легковые

Экстрим

MF-321

Выхлопные трубы

Легковые

Стандарт

MF-211

Выхлопные трубы

Внедорожники

Малые

Если этот столбец не следует отображать, после добавления столбца в отчет сводной таблицы его можно скрыть. (Выберите столбец, затем на вкладке Главная в группе Ячейки щелкните стрелку рядом с полем Формат, выделите Скрыть и отобразить и щелкните Скрыть столбцы.)

Важно : Если в отчете сводной таблицы будут обнаружены две одинаковые строки данных или более, которые были взяты из источника данных, отображаться будет только одна строка. Если необходимо, чтобы в отчете сводной таблицы отображались все строки, даже одинаковые, необходимо добавить столбец, в котором будут содержаться уникальные значения таблицы, запроса или представления, импортированного из источника данных. Если в источнике данных нет уникального столбца, можно его добавить. Например, можно добавить столбец с типом данных «AutoNum» (Счетчик) в таблицу Microsoft Office Access или столбец с типом данных «Identity» (Удостоверение) в таблицу Microsoft SQL Server, или вычисляемый столбец, который будет содержать уникальный номер для каждой записи, в запрос или представление базы данных.

Изменение данных в ячейках и добавление вычисляемых столбцов

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

При двойном щелчке ячейки в диапазоне внешних данных открывается режим редактирования, где можно изменить значение или ввести формулу. Однако обратите внимание, что исходное значение в источнике данных не изменяется, так что при следующем обновлении данных новое значение может быть переписано в зависимости от параметра, установленного для свойства Если количество строк изменится диапазона внешних данных.

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

Вычисляемые столбцы можно добавить в любом месте таблицы Microsoft Office Excel, основанной на диапазоне внешних данных, или можно вставить столбцы и заполнить формулы в диапазоне внешних данных. Невозможно вставить вычисляемые столбцы в отчет сводной таблицы (хотя можно вставить вычисляемые поля). Однако можно добавить столбец, заполняющий данные или формулы, слева или справа от отчета сводной таблицы, а также внутри заполненной формулы. В отчете сводной таблицы можно использовать ссылки на ячейки.

Создание диаграмм

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

Интерактивность.     При работе со стандартными диаграммами пользователь создает одну диаграмму для каждого представления данных, но эти представления не являются интерактивными. При работе со сводными диаграммами можно создать одну диаграмму и интерактивно представлять данные различными способами, изменяя структуру отчета или уровень отображаемых подробностей. В службах Excel можно обновить как стандартную, так и сводную диаграмму, основываясь на интерактивной работе пользователя с книгой, но обе диаграммы будут отображаться как статические изображения.

Типы диаграмм.     Типом стандартной диаграммы по умолчанию является сгруппированная гистограмма, с помощью которой сравниваются данные по категориям. Тип сводной диаграммы по умолчанию — это гистограмма с накоплением, с помощью которой оценивается вклад каждого значения в итог по категориям. Сводная диаграмма может быть изменена на любой тип, кроме точечной, биржевой и пузырьковой диаграммы.

Элементы диаграммы     Отчеты сводной диаграммы содержат те же элементы, что стандартными диаграммами, но они также содержат поля и элементы, которые могут быть добавлены, повернуты или удалены для отображения различных представлений данных. Категории, рядов и данные в стандартных диаграмм: поля категорий, полями рядов и полей значений в отчете сводной диаграммы. Отчеты сводных диаграмм, также могут содержать фильтры отчетов. Каждое из этих полей содержит объекты, которые в стандартные диаграммы отображаются как имена названия или ряд категории в условных обозначений.

Форматирование     Большинство параметров форматирования, включая элементы, макет и стиль, сохраняется, когда вы обновить отчета сводной диаграммы. Тем не менее линии тренда, метки данных, пределы погрешностей и другие изменения рядов данных не сохраняются. Стандартные диаграммы не будут потеряны этот раз применить форматирование.

Перемещение или изменение размеров элементов.     В сводной диаграмме невозможно переместить или изменить размер легенды область построения, названия диаграммы или осей, хотя можно выбрать одну из нескольких предварительно созданных позиций легенды, а также изменить размер шрифта заголовков. В стандартной диаграмме эти элементы можно перемещать и изменять их размеры.

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

Сравнение свойств диапазона внешних данных и параметров сводной таблицы

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

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

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

При обновлении диапазона внешних данных можно управлять поведением при изменении количества строк в диапазоне, установив один из следующих параметров:

  • Добавить новые строки, удалить неиспользуемые ячейки (по умолчанию)

  • Добавить новые строки, очистить пустые ячейки

  • Заменить существующие ячейки, очистить пустые ячейки

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

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

Фильтрация данных в диапазоне внешних данных аналогична фильтрации в отчете сводной таблицы, однако существуют некоторые отличия:

  • В диапазонах внешних данных используются команды Текстовый фильтр, Числовой фильтр и Фильтр данных, тогда как в отчетах сводных таблиц используются команды Фильтр по подписи, Фильтр по значению и Фильтр по дате.

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

  • В отчете сводной таблицы имеется функциональная возможность фильтрации отчета (недоступная для диапазонов внешних данных), которая позволяет работать с подмножеством данных.

Сортировка данных

При работе с диапазоном внешних данных можно выполнить сортировку по нескольким столбцам (до 64 столбцов), можно выбрать любой столбец в качестве первичного столбца сортировки, а любой другой столбец — в качестве дополнительного. Однако сортировка данных в отчете сводной таблицы отличается от сортировки в диапазоне внешних данных. По сравнению с диапазоном внешних данных в отчете сводной таблицы можно выполнить первичную сортировку в самом левом столбце. Дополнительная сортировка в других столбцах основывается на столбце, расположенном рядом слева. Иными словами, первичная и дополнительная сортировки могут быть выполнены только строго слева направо.

Более того, при размещении столбца с уникальными значениями в качестве первого столбца слева можно сортировать только по этому столбцу, так как таким образом создается первичная группа для каждой строки. В зависимости от потребностей можно предварительно упорядочить данные в Excel, а после публикации книги в службах Excel отключить сортировку, сняв флажок Сортировка на панели инструментов веб-части Microsoft Office Excel Web Access.

Использование гиперссылок

В отличие от диапазонов внешних данных, активные гиперссылки в ячейках сводной таблицы не поддерживаются. Гиперссылка обрабатывается в качестве текста, но открывать с ее помощью веб-страницу или документ в Excel или службах Excel невозможно.

К началу страницы

Создание двухмерного табличного отчета сводной таблицы

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

К началу страницы

Создание отчета сводной таблицы и подключение его к внешнему источнику данных

  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. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.

    Изображение ленты 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

  1. Нажмите Кнопку Microsoft Office Изображение кнопки Office , щелкните стрелку рядом с кнопкой Опубликовать и нажмите кнопку Служб Excel в разделе Рассылка документа другим людям.

  2. В окне Имя файла введите путь к серверу и согласитесь с предложенным именем книги или введите новое имя при необходимости.

    На компьютере с операционной системой Windows Vista   

    • Выберите в адресной строке сетевое расположение, доступное для предполагаемых пользователей, и нажмите кнопку Сохранить.

      На компьютере с операционной системой Microsoft Windows XP   

    • В списке Папка выберите сетевой ресурс, доступный внешним пользователям, а затем нажмите кнопку Сохранить.

      Например введите http://server/сайта/имя файла

      Примечание : Excel сможет опубликовать книгу на сервере только в формате Microsoft Office Excel 2007 на основе XML (XLSX) или в формате двоичных файлов Office Excel 2007 (XLSB).

  3. Нажмите кнопку ОК.

  4. Чтобы убедиться, что доступные для просмотра области книги отображаются корректно, установите флажок Открыть книгу в обозревателе после сохранения.

  5. Нажмите кнопку Сохранить.

Для получения дополнительных сведений обратитесь к статье Публикация книги в службах Excel.

К началу страницы

Примечание : Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×