Как в аксессе сделать таблицу excel
Обновлено: 02.07.2024
Предположим, что у вас есть большая база данных. Назовем её "products". И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах. Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к. хранить такое количество данных в книгах Excel не совсем правильно. Поэтому даже если у вас есть несколько книг/листов, забитых нужными данными по полной и надо эти данные объединить для дальнейшего анализа сводной таблицей - то самое лучшее на мой взгляд решение, это объединить их через MS Access в одну таблицу и потом уже построить сводную на основании таблицы не составит труда.
Создание БД в Access из нескольких диапазонов
Для того, чтобы правильно и безболезненно собрать данные нескольких таблиц из Excel в Access необходимо эти таблицы подготовить. Что не так уж сложно. Для этого надо соблюсти следующие правила:
- все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
- заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов - оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
- если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать - убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
- необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек
Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New) - Новая база данных (Blank database) . Указываете имя базы и месторасположение(папку):
После создания базы проходим несколько шагов:
- переходим на вкладку Внешние данные (External Data) -группа Импорт и связи (Import & Link) -Excel.
- Выбираем файл, данные из которого необходимо перенести в Access
- Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database) :
- на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges) :
я выбрал лист "products", т.к. именно так у меня называется лист с данными. Жмем Далее (Next) - на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет - устанавливаем
- жмем Готово (Finish)
Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг. Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table) . Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.
Создание сводной на основе базы данных Access
Прежде чем создавать сводную на основе внешних источников необходимо добавить на панель быстрого доступа кнопку , которая позволит это сделать:
- Excel 2010-2019 - Файл (File) - Параметры (Options) - Панель быстрого доступа (Quick Access Toolbar)
- Excel 2007 - Кнопка офис- Параметры Excel (Excel options) - Панель быстрого доступа (Quick Access Toolbar)
или непосредственно с панели быстрого доступа:
Выбрать команды из: Все команды (All Commands) . Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:
Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source) :
на втором шаге жмем кнопку Получить данные (Get Data) :
В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.
Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.
Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:
Подтверждаем выбор нажатием кнопки ОК.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек ">"
Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы - можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой " (Return Data to Microsoft Excel) и жмем Готово. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные должна появиться надпись Данные получены (Data fields have been retrieved) :
Если у вас данная надпись появилась, то смело жмем Далее (Next) и на последнем шаге мастера выбираем ячейку и лист, в которые необходимо поместить сводную таблицу:
Либо на имеющийся лист(придется указать ячейку, в которой будет размещаться первая строка данных), либо на новый лист.
Жмем Готово (Finish) .
Все, наша сводная готова к работе и теперь вы можете обрабатывать свои миллионы записей быстро и легко в привычном интерфейсе самой сводной.
Подправив код и подключив библиотеку для работы с Excel получил это:
Документ Excel он открывает, а дальше ничего. К тому же данные он для заполнения должен брать из формы, но, похоже не берёт. Да и не доходи до этого этапа, наверное.
И ещё вопрос (это мне на будущее): есть таблица в Excel:
Список генов и в столбцах Генотип должен быть вписан результат исследования. Т.е. клиент заказал, например, 1,2 и 27 гены. В базу в таблицу Гены (этот этап уже пройден) вписывается его заказ и сохраняется под одним idОбращения три раза. Не суть. Исследрвания провели. Для каждого из трёх генов вписали результат - Аллель. И как теперь реализовать заполнение таблицы в Excel? Если для одного клиента могут быть одни гены и результаты, а для другого другие? __________________
Помощь в написании контрольных, курсовых и дипломных работ здесь
Сохранение и закрытие шаблона Excel после внесения данных из формы Access
День добрый. По нажатию кнопки происходит открытие шаблона Excel c последующим его заполнением.
Ошибка при повторном выполнении модуля по заполнению шаблона Excel из формы Access
Есть модуль, который данными из формы и подчиненной формы выбирает данные и присваивает их.
Заполнение шаблона excel из DBGrid при этом сохранив имеющиеся данные в excel
Здравствуйте. у меня есть список который при нажатии на кнопку в делфи заполнятся в excel. Как мне.
autumnrustle, посмотри эту тему Создать Итог в Excel
В конце там выложен полностью рабочий код, выгрузки в Excel
А у вас не выгружает, потому что не от куда, источник вы не указываете ни где
duh_si, а почему так много всего? И начало даже объёмное:Что значит "Сформировать и вывести форму по шаблону TmplName по записи о заказе с идентификатором ID"
У меня уже есть форма. Зачем что-то формировать? И по какому такому шаблону?
Всем привет, сегодня мы поговорим о том, как можно выгрузить данные из Access в такие приложения как Word и Excel. Но не о стандартном способе, который есть в Access (связь с Office), а о способе, который позволяет выгружать данные в заданный шаблон как в Word, так и в Excel.
Другими словами, это нужно тогда, когда создать отчет в Access по шаблону, который уже существует, например, в Word, невозможно или слишком трудоемко. Как Вы знаете, отчет в Access может выводиться просто коряво или, самый распространенный вариант, это когда много текста, который в отчете Access не так хорошо форматируется как в Word, а данных не так много, но отчет необходимо автоматизировать, например это какие-то договора, заявления и так далее.
Использование слияния из самого Word-а не очень удобно, поэтому сегодня я расскажу, как можно заполнять такие шаблоны напрямую из Access, путем нажатия на одну кнопку.
Кроме выгрузки в шаблон Word, иногда возникает и необходимость выгрузки в шаблон Excel, и этот способ мы тоже сегодня рассмотрим.
Экспорт данных из Access в шаблон Word
Вся разработка делится на две части, это:
- Настройка шаблона Word;
- Настройка выгрузки данных в шаблон.
Суть настройки шаблона заключается в том, чтобы проставить необходимые поля в тех местах шаблона, где нужно выводить те или иные данные. Это делается с помощью полей формы.
Примечание! Я использую Microsoft Word 2003.
После добавления поля, у Вас появится серая область, которая свидетельствует о том, что поле добавлено. Теперь необходимо задать имя этого поля, для того чтобы потом из access вставлять в него значения (стандартное названия не очень удобное). Для этого щелкните правой кнопкой мыши по полю и нажмите «Свойства». В поле закладка напишите желаемое имя этого поля, я в примере назвал его MyTestPole.
Создайте столько полей, сколько Вам нужно.
На этом настройка шаблона закончена, рекомендую сделать этот шаблон только для чтения, а то пользователь возьмет, сформирует документ и сохранит его, и шаблон тем самым потеряется, а если сделать его только для чтения, то у него такой возможности не будет, только сохранять через «Сохранить как».
Переходим к более интересной задачи, это к реализации самой выгрузки из Access в этот шаблон на VBA.
Примечание! Я использую Access в связке с MS SQL 2008, поэтому и данные буду брать от туда.
Код VBA для выгрузки данных в шаблон Word
Допустим, у Вас есть форма, сделайте на ней кнопку (я назвал ее testbutton) и в событие нажатие кнопки вставьте следующий код VBA:
Код прокомментирован, поэтому сложностей возникнуть не должно. Здесь весь смысл сводится к созданию объекта word.document и word.application. А после мы уже работаем с нашими объектами, т.е. заполняем их.
Экспорт данных из Access в шаблон Excel
В шаблоне Excel уже не нужно создавать поля как в Word, так как здесь мы уже будем ориентироваться по адресам ячеек.
Код VBA для выгрузки данных в шаблон Excel
Сначала добавьте кнопку на форму (я ее назвал testexcel) и вставьте следующий код в событие «Нажатие кнопки».
Здесь я также все подробно прокомментировал, но если есть вопросы, то задавайте их в комментариях к данной статье.
1. Режим таблицы — позволяет создать новую таблицу в режиме таблицы;
Первоначально представляется таблица с полями, куда необходимо ввести данные. Эта таблица содержит, как правило, 20 столбцов и 30 строк, и этого вполне достаточно для начала. После сохранения Access сам решает, какой тип данных присвоить каждому из полей.
Видео YouTube
2. Конструктор — позволяет создать новую таблицу в конструкторе таблиц;
Создание таблиц в окне конструктора предоставляет более широкие возможности по определению параметров создаваемой таблицы. После выбора этой операции открывается конструктор таблиц следующего вида.
Окно конструктора таблиц разделяется на две области. В верхней области отображается сетка, каждая строка которой описывает один столбец базы данных. В верхней части окна диалога находится таблица, которая содержит следующие атрибуты создаваемой таблицы: наименование поля, тип данных и описание. Имя поля – вводятся имена атрибутов, которые необходимо отразить в таблице.
Для каждого атрибута отдельно определяется его тип данных. Описание - можно внести любую информацию о поле для будущих пользователей БД.
Желательно стараться использовать имена, отличающиеся краткостью, для облегчения их идентификации при просмотре таблиц. Наименование поля используется для ссылки на данные таблицы.
В нижней области окна конструктора таблиц отображаются дополнительные характеристики каждого столбца, выделенного в верхней области (свойства полей).
Свойства полей (выборочно— это набор характеристик, обеспечивающих дополнительные возможности управления хранением, вводом и отображением данных в поле. Число доступных свойств зависит от типа данных поля.
Размер – для числа: 1 байт (0..255), с плавающей точкой 8 байт (от -10308..до 10308)
Формат поля – для задания формата отображения значения
Маска ввода – задание отображения постоянных символов в поле (для текста и даты)
Условие на значение – ограничение на значение вводимых данных ( < 100 AND >50)
Индексированное: Да (совпадения не допускаются) – первичный ключ, Да (совпадения допускаются) – вторичный ключ, Нет (неиндексированное поле)
Видео YouTube
3. Мастер таблиц — позволяет создать новую таблицу с помощью мастера;
MS Access содержит целый ряд таблиц, которые вы можете использовать в качестве прототипов требуемых Вам таблиц. При использовании мастера Вы можете не только сэкономить время на создании таблиц, но и обеспечить стандартные имена и типы данных полей таблиц.
Из набора таблиц можно выбрать нужную, которая будет создана в БД пользователя.
Видео YouTube
4. Импорт таблиц — позволяет осуществить импорт таблиц из внешнего файла в текущую базу данных; Импорт позволяет взять данные из таблиц, находящихся в других приложениях и импортировать их в существующую базу данных пользователя.
Видео YouTube
5. Связь с таблицами — позволяет осуществить создание таблиц, связанных с таблицами из внешних файлов. Устанавливается как и импорт через диалоговое окно, но при этом таблица остается в старом приложении и может использоваться несколькими пользователями.
Читайте также: