Как в access создать сводную таблицу в excel
Обновлено: 07.07.2024
Программа Microsoft Access очень часто применяется для управления наборами таблиц, которые связаны согласно определенным правилам или полям (как, например, таблицы Покупатели, Заказы и Доход одной базы данных).
При управлении данными в Access в ваше распоряжение предоставляются все преимущества реляционных баз данных: целостность информации, отсутствие избыточных данных и получение необходимых сведений с помощью запросов. Для большинства пользователей Excel базы данных Access предоставляют ценность только как хранилища информации, данные из которых извлекаются с помощью запросов. После импорта данных в Excel их можно представить в виде сводных таблиц, а потому и быстро проанализировать самым тщательным образом.
Проблема такого подхода заключается в том, что он предполагает хранение в одной рабочей книге сразу двух копий одного и того же набора данных: на рабочем листе и в кеше сводной таблицы. Это автоматически делает рабочую книгу громоздкой, а ее файл — в два раза большего размера, чем того требует ситуация. Конечно, существует способ повышения эффективности обработки внешних данных Access.
В Excel 2010 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel на выполнение и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные (Data) и в группе Получение внешних данных (Get External Data) щелкните на кнопке Из Access (From Access), как показано на рис. 7.19.
Рис. 7.19. Щелкните на кнопке Из Access, чтобы получить данные из базы данных Access
На экране появится диалоговое окно, где запрашивается база данных, которой будет извлекаться информация. Укажите исходную базу данных. После выбора базы данных на экране появится диалоговое окно, подобное показанному на рис. 7.20. В нем перечислены все таблицы и запросы, присутствующие в указанной базе данных. В нашем примере выбран запрос Sales_By_Employee. Щелкните на кнопке ОК.
Рис. 7.20. Выберите запрос или таблицу, данные которой нужно проанализировать
На рис. 7.20 показано, что в диалоговом окне Выделить таблицу (Select Table) присутствует столбец Тип (Туре). В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (view) и таблица (table). Представления соответствуют запросам Access.
Следующим на экране появляется диалоговое окно Импорт данных (Import Data). В нем указывается формат, в котором будут импортироваться данные. Как видно на рис. 7.21, можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные.
Рис. 7.21. Установите переключатель Отчет сводной таблицы
Установите переключатель Отчет сводной таблицы (PivotTable Report) и щелкните на кнопке ОК. Начиная с этого момента вы будете наблюдать на экране рабочий лист Excel с диалоговым окном Список полей сводной таблицы (PivotTable Field List). Теперь можно выполнять любые операции с только что созданной сводной таблицей (рис. 7.22).
Рис. 7.22. Сводная таблица готова к применению
Преимущество описанного метода заключается в сохранении в рабочем файле только одной копии исходных данных, которая заносится в кеш сводной таблицы. Ни в каких рабочих листах в явном виде импортированные данные не сохраняются. Представляет ли это проблему, если вы захотите вывести импортированные данные? Конечно же, нет!
Можно указать в сводной таблице представить исходные данные в нужном виде, для чего достаточно дважды щелкнуть на соответствующем значении. На рис. 7.23 показан результат двойного щелчка на значении Общий итог для менеджера с фамилией Gall (Галл). В результате на рабочем листе будут выведены все записи, в которых содержатся значения, принимающие участие в вычислении общего итога для мистера Галла. Все указанные данные автоматически помещаются на отдельный рабочий лист.
При создании сводной таблицы на основе базы данных Access вы сможете обновить ее данные только в случае доступности исходной таблицы или запроса. Таким образом, удаление, перемещение или переименование базы данных или отдельных ее компонентов приводит к нарушению связи сводной таблицы с внешним источником, а потому и невозможности ее обновления.
Именно по этой причине многие клиенты, использующие связанные с базой данных сводные таблицы, не могут своевременно обновлять информацию, и из-за этого отказываются от применения внешних источников информации. Поэтому лучше всего при создании сводных таблиц использовать внешние данные, располагаемые на общедоступных файловых серверах.
Рис. 7.23. Двойной щелчок на итоговом значении в области данных сводной таблицы приводит к выводу всех строк, принимающих участие в вычислении этого общего итога
Анализ всех данных помогает принимать более важные деловые решения. Но иногда бывает сложно понять, с чего начать, особенно если у вас много данных, которые хранятся за пределами Excel, например в базе данных Microsoft Access или Microsoft SQL Server или в файле куба OLAP. В этом случае нужно подключиться к внешнему источнику данных, а затем создать сводимую, анализировать, изучать и представлять эти данные.
Вот как можно создать с помощью существующего подключения к внешним данным:
Щелкните любую ячейку на листе.
Выберите Вставка > Сводная таблица.
В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.
Выберите вариант Выбрать подключение.
На вкладке Подключения в поле Показать выберите все подключения или выберите категорию подключения с источником данных, к который вы хотите подключиться.
Чтобы повторно использовать существующее подключение или поделиться им, используйте подключение из Connections в этой книге.
В списке подключений выберите нужное подключение и нажмите кнопку Открыть.
В области Выберите, куда следуетпоместить отчет, выберите расположение.
Чтобы разместить таблицу на новом месте, начиная с ячейки A1, выберите На новый.
Чтобы разместить таблицу на активном месте, выберите на существующем месте ,а затем в поле Расположение введите ячейку, с которой нужно начать работу.
Excel добавляет пустую pivottable и отображает список полей, чтобы вы могли отдемонстрировать нужные поля и изменить их расположение для создания собственного макета.
В разделе список полей, чтобы разместить поле в области по умолчанию в разделе областей списка полей, рядом с именем поля.
Как правило, ненумерные поля добавляются в область строк, числовые — в область значения, а поля даты и времени — в область Столбцы. При необходимости вы можете переместить поля в другую область.
Совет: Вы также можете щелкнуть имя поля правой кнопкой мыши и выбрать добавить в фильтр отчета ,Добавить в названия столбцов, Добавить в названия строк илиДобавить в значения, чтобы разместить поле в этой области раздела областей, или перетащить поле из раздела полей в область в разделе областей.
С помощью списка полей можно более точно спроектировать макет и форматирование с помощью правой кнопки мыши полей в разделе областей и выбора нужной области или путем перетаскиванием полей между областями раздела областей.
Подключение к новому внешнему источнику данных
Чтобы создать новое подключение к внешним данным SQL Server и импортировать данные в Excel как таблицу или таблицу, сделайте следующее:
Щелкните Данные > из других источников.
Выберите нужное подключение.
Щелкните Из SQL Server, чтобы создать подключение к SQL Server таблице.
Щелкните Из служб Analysis Services, чтобы создать подключение к кубу SQL Server Analysis.
В мастере подключения данныхвыполните действия, чтобы установить подключение.
На странице 1 введите сервер базы данных и укажите, как вы хотите войти на сервер.
На странице 2 введите базу данных, таблицу или запрос, которые содержат нужные данные.
На странице 3 введите файл подключения, который вы хотите создать.
Чтобы создать новое подключение к базе данных Access и импортировать данные в Excel как таблицу или таблицу, сделайте следующее:
Щелкните Данные > из Access.
В диалоговом окне Выбор источника данных найдите базу данных, к которой нужно подключиться, и нажмите кнопку Открыть.
В диалоговом окне Выбор таблицы выберите таблицу и нажмите кнопку ОК.
Если таблиц несколько, в поле Включить выбор нескольких таблиц можно включить флажки нужных таблиц, а затем нажмите кнопку ОК.
В диалоговом окне Импорт данных выберите, как вы хотите просмотреть данные в книге и куда их поместить, а затем нажмите кнопку ОК.
Таблицы автоматически добавляются вмодель данных, а база данных Access — к подключениям к книге.
= Мир MS Excel/Статьи об Excel
| Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
| Приёмы работы с формулами [13] |
| Настройки Excel [3] |
| Инструменты Excel [4] |
| Интеграция Excel с другими приложениями [4] |
| Форматирование [1] |
| Выпадающие списки [2] |
| Примечания [1] |
| Сводные таблицы [1] |
| Гиперссылки [1] |
| Excel и интернет [1] |
| Excel для Windows и Excel для Mac OS [2] |
Прежде всего, следует разобраться, как создать подключение к внешнему источнику данных.
Это можно сделать несколькими способами
Раз речь идет о сводных таблицах, то логично использовать Мастер сводных таблиц. В Excel 2003 Мастер запускается сразу при попытке создания сводной таблицы через меню Данные -- Сводная таблица. Начиная с Excel 2007 для использования Мастера сводных таблиц, нужно вынести на Панель быстрого доступа одноимённую кнопку.
На первом шаге Мастера сводных таблиц выбираем вариант Во внешнем источнике данных -- Далее
На втором шаге Мастера для создания нового подключения нажимаем кнопку Получить данные.
После нажатия кнопки Получить данные. в открывшемся диалоговом окне предлагается выбрать источник данных для будущей сводной таблицы. Дальнейшая работа будет рассмотрена на примере файла Access. Если Вы импортируете данные из файла .mdb, то следует выбрать вариант База данных MS Access , а если более поздних версий .accdb, - то MS Access Database. Флажок Использовать Мастер запросов должен быть установлен
В связи со всеобщей глобализацией в Excel 2010 были серьезно усовершенствованы средства подключения к транзакционным базам данных, таким как SQL Server. С помощью новых, встроенных в Excel инструментов создание сводных таблиц на основе баз данных SQL Server становится предельно простым занятием.
Начните с перехода на вкладку ленты Данные (Data). Щелкните на кнопке Из других источников (From Other Sources) и в раскрывающемся меню выберите команду С сервера SQL Server (From SQL Server), как показано на рис. 7.24.
Рис. 7.24. Выберите в раскрывающемся списке команду С сервера SQL Server
Тем самым вы запустите мастер подключения данных (рис. 7.25). С его помощью в Excel настраивается ссылка на внешние данные, расположенные на сервере. В рассматриваемом случае готовый файл примера отсутствует. В нашей демонстрации проиллюстрирована процедура взаимодействия Excel и SQL Server. Действия, которые вы будете выполнять для подключения к собственной базе данных, полностью повторяют описанные ниже.
На первом шаге мастера нужно снабдить Excel регистрационными данными. Как видно на рис. 7.25, от вас требуется ввести имя сервера, а также имя пользователя и пароль доступа к данным.
Рис. 7.25. Введите регистрационные данные и щелкните на кнопке Далее
При вводе регистрационных данных в Windows достаточно установить переключатель Использовать проверку подлинности Windows (Windows Authentication). Далее укажите базу данных, из которой будут извлекаться данные, выбрав ее в раскрывающемся меню, в котором перечисляются все базы данных, хранящиеся на заданном сервере. В данном случае была выбрана база данных ReportServer. Выбор этой базы данных приводит к отображению в нижней части окна всех ее таблиц и видов. Вам осталось только указать анализируемую таблицу или вид, а затем щелкнуть на кнопке Далее.
Обратите внимание на установку флажка, расположенного под раскрывающимся меню доступных баз данных. В большинстве случаев вы будете подключаться к одной таблице или запросу, которые представляют агрегацию или просто подмножество данных. Именно поэтому данный флажок установлен по умолчанию, чтобы предотвратить выбор в списке сразу нескольких объектов.
Если сбросить флажок, то в списке можно будет сразу выделить все обьекты, представленные в нем. В подобном случае вы сможете создавать собственные запросы и наборы данных, для чего автоматически запускается утилита MS Query. В следующем диалоговом окне мастера можно ввести описательную информацию о создаваемом подключении. Все текстовые поля в диалоговом окне заполнять не обязательно. Даже если вы не введете ни один из предложенных типов данных, подключение все равно будет работоспособным.
Чаще всего в этом диалоговом окне используются такие параметры.
- Имя файла (File Name). В этом поле можно изменить имя файла с расширением .ode (Office Data Connection), генерируемого с целью хранения параметров создаваемого подключения.
- Сохранить пароль в файле (Save Password in File). Этот флажок, расположенный под полем ввода имени файла, заставляет хранить пароль доступа к внешнему файлу в самом файле параметров конфигурации подключения. Не забывайте о том, что пароль вводится в файл в незашифрованном виде, поэтому любой заинтересованный пользователь может легко получить к нему доступ. Имейте в виду, что пароль не зашифрован, поэтому любой пользователь может узнать ваш пароль путем простого просмотра файла в текстовом редакторе.
- Описание (Description). В этом поле вводится краткое описание назначения устанавливаемого подключения.
- Понятное имя (Friendly Name). В качестве понятного (для пользователей) имени обычно используется собственное название внешнего источника данных. Это название должно быть более значимым для вас, чем то, которое дал ему его создатель.
Закончив со вводом всей необходимой информации, щелкните на кнопке Готово (Finish). Вы увидите на экране последнее диалоговое окно — Импорт данных (Import Data). Теперь установите переключатель Отчет сводной таблицы и щелкните на кнопке ОК, после чего переходите к непосредственному управлению отчетом сводной таблицы.
Читайте также: