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

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

Примечание: Эта статья выполнила свою задачу, и в скором времени ее использование будет прекращено. Чтобы избежать появления ошибок "Страница не найдена", мы удаляем известные нам ссылки. Если вы создали ссылки на эту страницу, удалите их, и вместе мы обеспечим согласованность данных в Интернете.

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

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

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

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

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

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

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

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

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

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

Добавление, разметка и упорядочивание полей в области названий строк

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

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

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

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

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

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

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

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

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

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

Данные сводной таблицы групп и подгрупп в порядке справа налево

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

Данные, сгруппированные по Линейкеи 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 таблицу или столбец, который содержит удостоверение типа данных в таблицу Microsoft SQL Server или вычисляемый столбец, отображающий уникальный номер для каждой записи запроса к базе данных или представлении w.

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

Перед публикацией книги в службах 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. Нажмите кнопку Сохранить.

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

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

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

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

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

×