ГлавнаяРазноеИсточник данных в excel

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


Внешние источники данных | Excel для финансиста

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

Например, при расчётах требуется узнать текущие курсы валют. Эти данные ежедневно публикуются на разных сайтах, и можно получить их через ?нтернет. Всё очень просто, единственная сложность – найти такой сайт, чтобы информация на нём была в пригодном для Excel виде.

Нажмите в меню Данные кнопку Получить внешние данные, далее ?з Веба:

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

Появится диалоговое окно ?мпорт данных. Лучше выбрать помещение данных на Новый лист, кроме того, нажмите на кнопку Свойства. В открывшемся окне посмотрите важное свойство – Обновление экрана: как часто внешняя информация будет обновляться. Для курсов валют нет необходимости постоянного обновления информации, достаточно обновлять информацию раз в день при открытии рабочей книги:

После нажатия ОК данные будут вставлены на новый лист и будут обновляться при каждом открытии книги. Вручную обновить данные можно в любой момент нажатием кнопки Обновить всё (справа от кнопки Получить внешние данные). Для удобства можно задать этим данным имена диапазонов:

Смотрите также: Экспорт данных из 1С в Excel на примере отчёта о движении денежных средств

Об именах диапазонов написано в учебнике Excel онлайн: Глава 2. Работа с ячейками: ввод, форматирование

finexcel.ru

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

  1. Щелкните отчет сводной таблицы.

  2. На вкладке " Анализ " в группе данные нажмите кнопку Изменить источник данных и нажмите кнопку Изменить источник данных.

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

  3. Выполните одно из действий, указанных ниже.

    • Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, нажмите кнопку выбрать таблицу или диапазон, затем введите первую ячейку в текстовом поле Таблица или диапазон и нажмите кнопку ОК

    • Чтобы использовать другое подключение, сделайте следующее:

      1. Нажмите кнопку выберите использовать внешний источник данных и нажмите кнопку Выбрать подключение.

        Откроется диалоговое окно Существующие подключения.

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

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

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

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

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

Что делать, если отсутствует подключение?

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

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

  1. Выберите нужное подключение и нажмите кнопку Открыть.

  2. Выберите вариант Только создать подключение.

  3. Щелкните пункт Свойства и выберите вкладку Определение.

  4. Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.

  5. Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

  1. Щелкните отчет сводной таблицы.

  2. На вкладке Параметры в группе Данные щелкните пункт Изменить источник данных и затем выберите вариант Изменить источник данных.

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

  3. Выполните одно из действий, указанных ниже.

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

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

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

    Откроется диалоговое окно Существующие подключения.

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

  6. Выберите соединение в списке Выберите подключение и нажмите кнопку Открыть.

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

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

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

Что делать, если отсутствует подключение?

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

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

  1. Выберите нужное подключение и нажмите кнопку Открыть.

  2. Выберите вариант Только создать подключение.

  3. Щелкните пункт Свойства и выберите вкладку Определение.

  4. Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.

  5. Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Не удается изменить источник данных для сводной таблицы в Excel Online. Необходимо, чтобы можно было сделать с помощью классической версии Excel. Читайте в статьеИзменение источника данных для сводной таблицы .

support.office.com

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

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

Вложения:
pivotsources.xlsx [Источники данных сводных таблиц] 30 kB

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

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

  1. Фиксированный диапазон
  2. "Умная таблица" Excel 2007
  3. Динамический именованный диапазон

Фиксированный диапазон

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

В зависимости от практической задачи может потребоваться добавить новые данные в источник сводного отчета. Обычно добавляются новые строки. Это может происходить как при ручном вводе или копировании, так и при автоматизированном получении данных из внешних систем. После простого добавления данных вниз исходного диапазона Excel не включит эту новую строку в источник сводной таблицы. Обновление отчета не приведет к изменениям. Действительно, сводная таблица основана на заранее определенном фиксированном диапазоне данных, не включающих новые строки. Пример такой ситуации показан в файле-примере на листах Data и ReportData – строки 11 и 12 в отчет не попали:

Одним из вариантов решения проблемы является добавление новых строк в середину диапазона – при этом Excel корректно исправит ссылку на источник данных сводной таблицы.

На практике же обычно удобнее и проще заранее определить максимально возможный диапазон строк источника сводной таблицы, добавив про запас достаточное количество строк. В зависимости от задачи можно добавить как 10, так и 10000 строк, как ни странно, это почти не сказывается на производительности сводной таблицы. В файле-примере поменяйте источник данных сводной таблицы ReportData следующим образом:

 

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

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

"Умная таблица"

Начиная с версии Excel 2007, в интерфейсе рабочего листа стала доступна работа с так называемой «умной таблицей» (лента Вставка \ Таблица). Такая таблица имеет расширенные возможности по дизайну, собственную нотацию формул и может быть опубликована на сервере SharePoint для совместного доступа нескольким пользователям. В нашем случае важно, что «умная таблица» Excel может быть указана в качестве источника данных сводного отчета – можно указать ее по имени или выделить мышью (листы Table, ReportTable):

Добавление строк в «умную таблицу» не влияет на источник данных сводного отчета. Ссылка определяется по имени, поэтому анализ всегда проводится на полном объеме информации. Т.е. это вариант решения можно признать удобным в случае, когда исходные данные могут быть преобразованы в «умную таблицу». При ручном заполнении источника данных сводной таблицы этот вариант работы можно даже назвать приоритетным. Недостатком является несовместимость с xls-форматом, а также сложность при автоматизации импорта из внешних систем.

Динамический именованный диапазон

«Продвинутые» пользователи Excel умеют использовать именованные диапазоны рабочего листа. При помощи таких имен можно заменять ссылки в формулах или указывать источники данных. Обычные именованные диапазоны – это просто замена длинной ссылки в координатах рабочего листа на короткое имя. Так в примере на листе Data можно выделить область A1:D12, присвоить ей имя, а затем указать его в качестве источника данных сводной таблицы ReportData.

 

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

Немногие пользователи Excel знают, что в качестве именованного диапазона можно использовать не только простые ссылки на область рабочего листа, но и формулы. Такие именованные диапазоны обычно называют вычисляемыми или динамическими. Создание такого диапазона возможно только в Диспетчере имен (лента Формулы \ Диспетчер имен). При помощи функций вычисляемой адресации можно динамически изменять размер диапазона в зависимости от количество строк с данными.

Пример такого использования имени с формулой на листах Name и ReportName.

Формула имени source выглядит следующим образом:

=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;COUNTIF(Name!$1:$1;"*"))

Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:

=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;4)

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

Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.

Смотри также

» Обработка больших объемов данных. Часть 3. Сводные таблицы

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

» Сводная таблица Excelfin.ru

Надстройка предназначена для создания сводных таблиц на основе нескольких диапазонов данных файла Excel. Пользовательский интерфейс в...

» Сводные таблицы

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

» Сводный отчет на основе нескольких таблиц Excel

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

» Обновление списков сводной таблицы

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

www.excelfin.ru

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

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

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

  1. Щелкните любую ячейку на листе.

  2. Выберите Вставка > Сводная таблица.

  3. В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.

  4. Нажмите кнопку Выбрать подключение.

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

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

  1. Выберите нужное подключение из списка и нажмите кнопку Открыть.

  2. В разделе Укажите, куда следует поместить отчет сводной таблицы:, выберите место.

    • Чтобы поместить сводную таблицу на новый лист, начиная с ячейки A1, выберите вариант На новый лист.

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

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

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

  4. В разделе полей установите флажок рядом с именем поля, и оно появится в области по умолчанию в разделе областей списка полей.

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

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

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

Подключение к новому внешнему источнику данных

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

  1. На вкладке Данные нажмите кнопку Из других источников.

  1. Выберите нужное подключение.

    • Щелкните С сервера SQL Server, чтобы создать подключение к таблице на сервере SQL Server.

    • Щелкните Из служб аналитики, чтобы создать подключение к кубу аналитики на сервере SQL Server.

  2. В мастере подключения к данным установите соединение, выполнив соответствующие действия.

    • На странице 1 укажите имя сервера базы данных и выберите способ подключения к нему.

    • На странице 2 укажите базу данных, таблицу или запрос с необходимыми данными.

    • На странице 3 укажите файл подключения, который требуется создать.

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

  1. На вкладке Данные нажмите кнопку Из Access.

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

  2. В диалоговом окне Выбор таблицы выберите нужную таблицу и нажмите кнопку ОК.

Если есть несколько таблиц, установите флажок Разрешить выбор нескольких таблиц, затем — флажки соответствующих таблиц и нажмите кнопку ОК.

  1. В диалоговом окне Импорт данных укажите, в каком виде данные должны отображаться в книге и куда их следует поместить, и нажмите кнопку ОК.

Таблицы будут автоматически добавлены в модель данных, а база данных Access — в список подключений книги.

Подробнее о сводных таблицах

support.office.com

Подключение к внешним данным и их импорт

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

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

  1. На вкладке " данные " выберите пункт Существующие подключения.

  2. В раскрывающемся списке Показатьвыполните одно из указанных ниже действий.

    • Чтобы отобразить все подключения, выберите вариант Все подключения. Это значение выбрано по умолчанию.

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

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

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

      Этот список создан в папке Мои источники данных, которые обычно хранятся в папке Мои документы на вашем компьютере.

    • Чтобы отобразить только подключения, основанные на файле подключений, доступных в сети, выберите вариант Файлы подключений в сети.Для создания такого списка используется библиотека подключений к данным Excel на сайте служб Microsoft SharePoint. Библиотека подключений к данным — это библиотека документов на сайте служб SharePoint, содержащая коллекцию файлов подключений к данным Office (ODC-файлов). Как правило, эта библиотека настраивается администратором сайта, который также может настроить сайт SharePoint для отображения ODC-файлов из этой библиотеки в диалоговом окне Внешние подключения. Дополнительные сведения см. в справке по центру администрирования служб SharePoint.

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

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

  3. Выберите нужное подключение и нажмите кнопку Открыть.

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

    Важно: Раздел Выберите способ представления данных в книге и его параметры, отображаемые в приведенном ниже списке, недоступны для подключений к данным типа текст, веб-запрос и XML. Если выполняется подключение к таким данным, перейдите к действию 5.

    • Чтобы создать таблицу для простой сортировки и отбора, выберите Таблица.

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

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

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

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

  5. В группе Куда следует поместить данные? выполните одно из следующих действий.

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

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

  6. Чтобы разместить отчет сводной таблицы на новом листе начиная с ячейки A1, щелкните элемент Новый лист.

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

  1. На вкладке Данные в группе Получить внешние данные нажмите кнопку Существующие подключения .

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

    • Чтобы отобразить все подключения, выберите вариант Все подключения. Это значение задано по умолчанию.

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

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

    • Чтобы отобразить только те подключения, которые доступны на компьютере, выберите элемент Файлы подключений на этом компьютере .

      Этот список создается из файлов подключений, находящихся в папке Мои источники данных, которая обычно хранится в папке Мои документы.

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

      Этот список создается из Excel Services данные подключения библиотеки (ODC-ФАЙЛ) на сайте Microsoft Office SharePoint Server 2007. ODC-ФАЙЛ — библиотека документов на сайте SharePoint служб Microsoft Office 2007, содержащий набор файлов для подключения к данным Office (ODC) (.odc). Как правило Удобства Настройка администратором сайта, кто также можно настроить отображение ODC файлы из этой ODC-ФАЙЛ в диалоговом окне Подключения к внешним на сайте SharePoint. Для получения дополнительных сведений просмотра справки Microsoft Office SharePoint Server 2007 центра администрирования.

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

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

  3. Выберите нужное подключение и нажмите кнопку Открыть.

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

    Важно: Раздел Выберите способ представления данных в книге и его параметры, отображаемые в следующем списке, недоступны для подключений к данным типа текст, веб-запрос и XML. Если выполняется подключение к таким данным, перейдите к действию 5.

    • Чтобы создать таблицу для простой сортировки и отбора, выберите Таблица.

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

    • Чтобы создать отчет сводной таблицы или отчет сводной диаграммы для визуального суммирования данных, выберите Сводная диаграмма и отчет сводной таблицы.

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

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

  5. В группе Куда следует поместить данные? выполните одно из следующих действий.

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

      Кроме того нажмите кнопку Свернуть диалоговое окно временно скрыть диалоговое окно, установите начало ячейку на листе и затем нажмите кнопку Развернуть диалоговое окно .

  6. Чтобы разместить отчет сводной таблицы на новом листе начиная с ячейки A1, выберите Новый лист.

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

support.office.com

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

Программа Excel является прекрасным средством обработки и анализа данных. По сути, сводные таблицы сами по себе являются доказательством аналитической мощи Excel. Однако, говоря обо всех достоинствах программы, нельзя не упомянуть и об одном существенном упущении. Программа Excel построена на относительно простой платформе управления данными, обладающей тремя недостатками. [1]

  • Размер набора данных сильно влияет на скорость обработки данных в сводной таблице. Это накладывает серьезные ограничения на эффективность использования сводных таблиц как самодостаточных структур. Причина подобного поведения программы — в специфике управления оперативной памятью. Файл при открытии в Excel полностью перемещается в оперативную память для более быстрой обработки и доступа к данным. Но при этом в программе не реализован надежный механизм оптимального управления оперативной памятью при извлечении из нее даже небольшого фрагмента данных. Несмотря на то что в Excel 2013 предусматривается использование до 1 млн. строк и 16 тыс. столбцов, даже средние по размеру наборы данных приводят к значительным задержкам при обработке.
  • Отсутствие реляционной модели данных вынуждает нас использовать «плоские» таблицы, которые хранят избыточные данные и увеличивают вероятность появления ошибок.
  • Отсутствие индексации полей данных в Excel для оптимизации процесса извлечения больших объемов данных.

Скачать заметку в формате Word или pdf, скачать примеры в формате Excel, скачать базу данных Access в формате архива (по соображениям безопасности провайдер не позволяет размещать файлы в формате .accdb)

Именно поэтому в серьезных организациях для управления данными используется не Excel, а такие СУБД, как Microsoft Access и SQL Server. Эти СУБД применяются для хранения миллионов записей, которые можно быстро находить и извлекать. При подобном разделении производственных задач формируется уровень управления данными (сама база данных) и уровень приложения (Excel). Трудность заключается в том, чтобы найти наилучший способ извлечения информации из уровня управления данными и передачи ее на уровень приложения для эффективного использования в сводной таблице.

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

Создание сводных таблиц на основе данных Microsoft Access

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

Для большинства пользователей Excel базы данных Access представляют ценность только как хранилища информации, данные из которых извлекаются с помощью запросов. После импорта данных в Excel их можно представить в виде сводных таблиц, а потому и быстро проанализировать самым тщательным образом. Проблема такого подхода заключается в том, что он предполагает хранение в одной рабочей книге сразу двух копий одного и того же набора данных: на рабочем листе и в кеше сводной таблицы. Это автоматически делает рабочую книгу громоздкой, а ее файл — в два раза большего размера, чем того требует ситуация. В результате возникают проблемы производительности.

В Excel 2013 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные и в группе Получение внешних данных щелкните на кнопке Из Access (рис. 1).

Рис. 1. Получение данных из Access

На экране появится диалоговое окно с просьбой указать базу данных, из которой будет извлекаться информация. Укажите исходную базу данных. (Ссылка на базу данных, используемую в данном примере, приведена в начале заметки; вам потребуется сохранить базу данных на локальном диске). После выбора базы данных на экране появится диалоговое окно, подобное показанному на рис. 2. В нем перечислены все таблицы и запросы, присутствующие в указанной базе данных. В нашем примере выбран запрос Sales By Employee. Щелкните на кнопке ОК.

Рис. 2. Выберите запрос или таблицу, данные которой нужно проанализировать

В диалоговом окне Выбор таблицы имеется столбец Тип. В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (VIEW) и таблица (TABLE). Представления соответствуют запросам Access. В нашем примере Sales_By_Employee — это запрос Access. Таким образом, в сводную таблицу будет импортироваться результат выполнения запроса. Это очень важный аспект обработки данных. Выборку необходимых данных, соответствующих запросу, выполняет программа Access, a Excel всего лишь выполняет анализ выбранных данных.

Следующим на экране появляется диалоговое окно Импорт данных. В нем указывается формат, в котором будут импортироваться данные (рис. 3). Можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные. Щелкните ОК. На листе появится панель Поля сводной таблицы. Можно начинать работать с только что созданной сводной таблицей (рис. 4).

Рис. 3. Установите переключатель Отчет сводной таблицы

Рис. 4. Сводная таблица готова к использованию

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

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

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

Создание сводных таблиц на основе данных SQL Server

В связи с популяризацией коллективных вычислений в Excel 2013 были серьезно усовершенствованы средства подключения к транзакционным базам данных, таким как SQL Server. С помощью новых, встроенных в Excel инструментов создание сводных таблиц на основе баз данных SQL Server становится предельно простым занятием. Начните с перехода на вкладку ленты Данные. Щелкните на кнопке Из других источников и в раскрывающемся списке выберите команду С сервера SQL Server (рис. 5).

Рис. 5. Выберите в раскрывающемся списке команду С сервера SQL Server

Тем самым вы запустите мастер подключения к данным (рис. 6). С его помощью в Excel настраивается ссылка на внешние данные, расположенные на сервере.

Рис. 6. Введите регистрационные данные и щелкните на кнопке Далее

В рассматриваемом случае файл примера отсутствует. Здесь проиллюстрирована процедура взаимодействия Excel и SQL Server. Действия, которые вы будете выполнять для подключения к собственной базе данных, полностью повторяют описанные ниже. На первом шаге мастера нужно предоставить Excel регистрационные данные. Как видно на рис. 6, от вас требуется ввести имя сервера, а также имя пользователя и пароль доступа к данным. При вводе регистрационных данных в Windows достаточно установить переключатель Использовать проверку подлинности Windows.

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

Рис. 7. Укажите исходную базу данных, а затем выберите таблицу или представление

В следующем диалоговом окне мастера (рис. 8) можно ввести описательную информацию о создаваемом подключении. Все текстовые поля заполнять не обязательно. Даже если вы не введете ни один из параметров, подключение все равно будет работоспособным.

Рис. 8. Описательная информация о создаваемом подключении

Чаще всего в этом диалоговом окне используются такие параметры.

  • Имя файла. В этом поле можно изменить имя файла с расширением .ode (Office Data Connection), генерируемого с целью хранения параметров создаваемого подключения.
  • Сохранить пароль в файле. Этот флажок, расположенный под полем ввода имени файла, обеспечивает хранение пароля доступа к внешнему источнику в файле, содержащем описание параметров конфигурации подключения. Имейте в виду, что пароль не зашифрован, поэтому любой пользователь может узнать ваш пароль, просто просмотрев файл в текстовом редакторе.
  • Описание. В этом поле вводится краткое описание назначения устанавливаемого подключения.
  • Понятное имя. В качестве понятного (для пользователей) имени обычно используется собственное название внешнего источника данных. Это название должно быть более значимым для вас, чем то, которое дал ему его создатель.

Завершив ввод всей необходимой информации, щелкните на кнопке Готово. Вы увидите на экране последнее диалоговое окно — Импорт данных (такое же, как на рис. 3). Установите переключатель Отчет сводной таблицы и щелкните ОК, после чего переходите к непосредственному управлению отчетом сводной таблицы.

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

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

1. Выделите вкладку Данные и перейдите в группу Получение внешних данных. Щелкните на кнопке Из Access (см. рис. 1).

2. Найдите базу Access и откройте ее. На экране появится диалоговое окно Выбор источника данных. Установите флажок Разрешить выбор нескольких таблиц. Отметьте флажками таблицы, которые будут включаться во внутреннюю модель данных. Щелкните ОК (рис. 9).

Рис. 9. Отметьте флажками таблицы, которые будут включены во внутреннюю модель данных

3. На экране появится диалоговое окно Импорт данных. Установите переключатель Отчет сводной таблицы. Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Свойства, и снимите флажок Импорт связей между таблицами (рис. 10). Это исключит вероятность появления ошибки в случае некорректной интерпретации связей между таблицами. Это действие требуется в том случае, если вы собираетесь создавать связи между таблицами самостоятельно. Щелкните на кнопке ОК для создания базовой сводной таблицы

Рис. 10. Снимите флажок Импорт связей между таблицами

4. Выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. На экране появится диалоговое окно Управление связями. Кликните Создать. Откроется окно Изменение связи (рис. 11). Используя раскрывающиеся списки создайте нужные связи через поля, представляющие собою первичные ключи. В нашем примере – это поле Employee_Number. Щелкните OK, чтобы закрыть окно Изменение связи.Щелкните Закрыть, чтобы закрыть окно Управление связями (рис. 12).

Рис. 11. Создайте требуемые связи для только что импортированных таблиц

Рис. 12. Окно Управление связями с только что созданной связью между таблицами Sales_by_Employeeи Employee_Master

5. В вашем распоряжении появилась сводная таблица, основанная на внешних данных, которые были импортированы в модель данных. Можно, например, проанализировать продажи по рынкам сбыта и ФИО торговых представителей (рис. 13).

Рис. 13. Сводная таблица на основе нескольких внешних таблиц

 

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.

baguzin.ru

Как в Excel отключить сводную таблицу от источника данных — Трюки и приемы в Microsoft Excel

У вас может возникнуть ситуация, когда нужно отправить кому-нибудь суммарный отчет в виде сводной таблицы, но при этом вы не хотите включать исходные данные. Другими словами, вы хотите отсоединить сводную таблицу от ее источника данных.В Excel нет прямой возможности отделить сводную таблицу, но если вы выполните несколько простых шагов, то в конечном счете получите совершенную копию сводной таблицы. На рис. 172.1 показана красиво отформатированная сводная таблица. Если вы выберете сводную таблицу и скопируете ее в другое место, скопированный диапазон также будет сводной таблицей. Решение состоит в копировании и вставке значений.

Рис. 172.1. Эта сводная таблица будет отсоединена от своего источника данных

Выполните следующие шаги для создания отсоединенной копии сводной таблицы.

  1. Выберите ячейки сводной таблицы (B3:F9 в данном примере) и нажмите Ctrl+C для копирования диапазона.
  2. Выполните команду Главная ► Буфер обмена ► Вставить ► Вставить значения. Сводная таблица сохранит значения, но форматирование исчезнет (рис. 172.2).

Рис 172.2. Сводная таблица отсоединена, но настроек форматирования больше нет

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

  1. Откройте область Буфер обмена, щелкнув на значке в правом нижнем углу группы Главная ► Буфер обмена.
  2. При выбранной отсоединенной сводной таблице щелкните в буфере обмена на пункте, соответствующем операции копирования сводной таблицы. Это будет последний пункт, если вы только не копировали что-то еще.

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

excelexpert.ru