Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как сделать большую базу в Access из 15 таблиц Excel
 
#1
06.03.2017 11:14:29
Добрый день!
Никак не могу решить такую задачу (и на форумах не могу найти решение): нужно из 15-ти таблиц Excel сделать большую таблицу в Access (это будет база данных), чтобы потом её в формате значений, без формул, выгружать обратно в Excel.
Проблема в том, что таблицы Excel обновляются раз в неделю (в идеале они будут обновляться через день) и я пока не могу решить, как их автоматически синхронизировать с базой в Access.
Сейчас это всё делается вручную и в качестве БД получается огромная сводная таблица Excel, весом в 65 МБ (в неё идут ссылки из всех 15-ти постоянно обновляемых таблиц), работать с таким объемом становится очень сложно.
Кто-нибудь сталкивался с таким? Можно ли как-то упростить работу?
 
 
 
#2
06.03.2017 11:30:25
Доброе время суток.
Собственно, не вижу проблемы. Связываете 15 таблиц книг Excel в базу Access. Пусть связанные таблицы в нём Таблица1, Таблица2 и т. д.. Пишите элементарный запрос
Код
Select * From Таблица1
Union All
Select * From Таблица2
и так далее. Его и используете для связи с большой таблицей в Excel. Только, можете объяснить смысл подобного тяни-толкая?
 
 
 
#3
06.03.2017 11:48:00
Цитата
Андрей VG написал:
Только, можете объяснить смысл подобного тяни-толкая
Андрей, я правильно понимаю, что под этим, вы подразумевали наличие Ассеss в качестве посредника?  

matvey_mm, бывает, что прежде чем объединять данные, нужно сделать предобработку и ряд задач в Аccess сделать проще, например сперва запросом "очищаются даные" , а потом они соединяются, но если нужно просто объеденить , то  написанное Андреем можно проделать и в MS Query , без промежутка.
По вопросам из тем форума, личку не читаю.
 
 
 
#4
06.03.2017 11:49:23
Смысл в том, чтобы не работать в распухающем файле Excel (который и так максимально оптимизирован), а иметь базу в Access, из которой потом брать данные.
В общем, это сейчас выглядит так: есть сводная таблица (объём 65 МБ), в нее ВПР-ом затягиваются данные из всех 15-ти таблиц. Потом я сохраняю огромную сводную таблицу в виде значений и в итоге файл весит 10 МБ вместо 65 МБ и с ним можно работать.

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

Подскажите,пожалуйста , есть ли решение проще?
 
 
 
#5
06.03.2017 12:13:27
Что значит проще? Вы хотите перенести данные, что в ваших 15 файлах в Ассеss базу - это приветвуется.Это разоватя операция и независимо от того, разные это таблицы или "продолжения" одной , делается это простыми действиями импорта данных.
"что исходные таблицы в Excel и их надо постоянно обновлять" если вы под этим подразумеваете, что данные этих таблиц пополня.тся или меняются пользователями, то переместив их в Ассеыы потребуется инструмент для этих действий. Будет ли это Excel, Access ... иное приложение - это на ваш вкус.
 
По вопросам из тем форума, личку не читаю.
 
 
 
#6
06.03.2017 12:37:35
Вариант с Power Query.
С его помощью консолидируете 15 таблиц и получаете одну таблицу сразу, без формул.
При обновлении данных Excel просто обновляете итоговую таблицу.
Если нужно, делаете там же предварительную обработку.
Всё-таки таблица у вас вряд ли самоцель, поэтому из Power Query можно выгружать не на лист, а сразу в модель данных Excel, без вывода на листы.
Дальше строите сводную (или Power Pivot) на основании источника. Размер файла в таком случае будет меньше, и работать будет оперативнее
F1 творит чудеса
 
 
 
#7
06.03.2017 13:35:34
Цитата
Максим Зеленский написал:
Вариант с Power Query.
С его помощью консолидируете 15 таблиц и получаете одну таблицу сразу, без формул.
Вот как вариант я тоже думал так сделать, но не нашел варианта, как в Power Query в итоговую таблицу брать данные из 15-ти таблиц с помощью того же ВПР.

Забыл упомянуть - все 15 таблиц - это различные реестры с разными данными и разной формы (разное количество столбцов и т.д.), у них один "ключ" - регистрационный номер, он уникальный. Вот на него я хотел вначале завязать все таблицы в Access, но не получилось. Когда использовал Power Query (для другой работы, там были просто одинаковые таблицы с разными цифрами, но одной формы), у меня получилась итоговая таблица из множества однородных таблиц. С разнородными таблицами пока не собирал
 
 
 
#8
06.03.2017 13:42:32
Цитата
matvey_mm написал:
Забыл упомянуть
существенное дополнение, хотя просто будет чуть сложнее, что через SQL запрос MSQuery , что через PowerQuery
По вопросам из тем форума, личку не читаю.
 
 
 
#9
06.03.2017 13:48:28
Цитата
matvey_mm написал:
разное количество столбцов и т.д.
Для Power Query не проблема. Недавно делал. Для PQ не принципиально таблицы в книгах или на листах одной книги, чуть код меняется. Впрочем, что это мы вас уговариваем, а вы даже файлы со структурой таблиц не представили? Может там у вас три в одном, ввод, отчёт и печатная форма ? ;)  Чур без меня.
 
 
 
#10
17.03.2017 14:27:26
Хотел приложить файл с примером того, что есть сейчас (и вопросом "как это всё оптимизировать"), но файл весит даже в очень урезанном виде 300 КБ.

В общем, примерно такой по структуре итоговый файл. Там порядка 250 разнородных колонок и на данный момент 7500 строк. Каждый столбец свода тянется ВПР-ом из других таблиц разного формата, тянутся все данные по ключу "Регистрационный Номер Кз" (ну и из парочки таблиц по ID и номеру договора, где нет ключа Регистрационного номера Кз).
Все таблицы - разные (разное количество строк, столбцов), как с ними со всеми сразу работать в Power Query, я так и не понял.
Во всех связанных таблицах данные обновляются практически каждый день, добавляются всё новые и новые значения, в итоге сводный файл весит под 65 МБ.

Может быть, можно это как-то упростить через какой-нибудь макрос? Или как вообще работать с постоянно растущим файлом?
 
 
 
#11
17.03.2017 14:32:42
Вот, например, в таких форматах выглядят остальные таблицы, из которых данные тянутся в Сводную таблицу и которые постоянно обновляются:
 
 
 
#12
17.03.2017 16:25:07
Данные выглядят замечательною. Еcли идет прирост данных, то это приводит к росту файла. В чем у вас вопрос? Как сделать чтоб было хорошо или  ...
Для отчтета желательноиметь файл в котором будет консолилдироваться информация. Будут ли источником для этого этот большой XLS файл, несколько .... или вы перенесете их любую другую базу- не важно. Все линкуется по упомянутому Вами общему полю и получается таблица для аналитики.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Читают тему
Наверх