Страницы: 1 2 След.
RSS
Заполнение одной таблицы из нескольких других с одинаковой структурой данных
 
Добрый вечер.

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

Как через Query подключить каждую книгу в отдельную таблицу я знаю. А вот как сгружать все таблицу в одну общую таблицу - похоже не знаю.
Структуру таблиц одного из 4-х файлов и сводного файла прикладываю.
 
предлагаю макрос. Нажимаете кнопку, которая находится справа от таблицы (или Alt+F8-Выполнить), мышкой выбираете 3 файла (хоть 100 файлов, выделяете их и нажимаете Открыть), данные из указанных файлов будут скопированы в сводный файл.
Изменено: New - 13.02.2021 23:28:00
 
New, хорошо бы, чтобы при нажатии на кнопку все данные сами сгружались, без выбора. Но это ладно не критично.
Критично знаете что? Что если в файл добавить строку (например 15-ую по счету), то при повторной загрузке в сводный файл добавится не только 15-ая строка, а 14-ть старых строк продублируются и добавится новая строчка еще. А хотелось бы, чтобы добавилась только новая строка, 15-ая. То есть какую проверку перед загрузкой. Что вставлять только уникальные строки. Может каждой строке файла, который загружается в сводный, добавлять уникальный код и по нему сверять. Типа если строки с таким кодом еще нет в сводной таблице, то грузи строку. Реально это как-то сделать?
 
ну, теоретически, можно привязаться к дате в столбце С. Указывать за какую дату вы хотите собрать данные. Например, введите дату за какой день вы хотите собрать данные. Вы вводите 13.02.2021 - и далее макрос открывает по очереди выбранные файлы, и выбирает данные за эту дату и копирует их в Сводный файл.
 
New,немного не то. Сводный файл мог неделю не открываться, например. Открыли его в субботу. А в файлы, которые по участкам, за неделю понаписали кучу строк. А я получается выберу только одну дату для подгрузки. Надо именно в момент загрузки исключать дубли как-то.
 
Не знаю, как исключить дубли. Может кто решение на Power Query вам предложит
 
Сводный файл накопительный?
Если нет - Можно удалить все данные в сводной перед загрузкой и не будет никаких дубликатов.

Исходя из информации, что в файле по участку "уже были" 14 предыдущих записей и добавилась только 15-ая.... не похоже на накопительную...
Или кидать все в одну кучу и потом удалить дубликаты через меню Excel "Удалить дубликаты" в разделе "Данные"

Сформулируйте критерий уникальности записи.... )
 
Marat Ta,доброе утро.
Сводный файл - накопительный.
Есть четыре участка, на каждом по книге. Каждый участок заполняет свою книгу.
Сводный файл, условно, лежит на контрольном пункте. При открытии этого сводного файла там должна быть информация со всех четырех участков.
И эта информация должна актуализироваться (обновляться)
Если сегодня в один из файлов на участке добавилась строчка в таблице (или несколько строк), то при открытии сводного файла в нем должна отразится та информация, которая была ранее и вновь добавленная. То есть файл участка не должен снова грузится в сводную целиком, а лишь то, что еще не было загружено на момент предыдущего открытия.
Я знаю, как с помощью Power Query загрузить каждый участок на отдельный лист сводной книги. Но не знаю, как эти четыре участка сгрузить в одну таблицу на один лист сводной книги.
 
Цитата
Маргарита Пыркина написал:
Я знаю, как с помощью Power Query загрузить каждый участок на отдельный лист сводной книги. Но не знаю, как эти четыре участка сгрузить в одну таблицу на один лист сводной книги
В сводной книге нет ни одного запроса. Покажите ваши запросы к четырем участкам
 
Еще раз - критерий уникальности для проверки уже существующей записи?

Раз сводная нарастающая.... предлагаю все без проверки добавлять, а затем удалить дубликаты. Можно и через макрос. Только по какому критерию уникальности?
вариантов несколько - например используя CountIf по диапазону с возвратом >1 и удалением строк
---
Мне интересно, а как вы будете решать проблему при изменении параметра не входящего в проверку по уникальности?
К примеру, номер наряда (уникален) и сумма. Наряд уже был ранее и не должен добавится в сводную, а на участке поменяли ошибочную сумму.
 
Михаил Л,вот прилагаю.
Файл МЛА подключен к файлу сводный.
Добавляю строку в МЛА, перехожу в сводный, нажимаю обновить данные на вкладке данные. Строка добавляется.

Но у меня таких файлов, типа МЛА, 4 штуки. Вот как подключить все файлы на разные листы сводной книги я знаю.
А мне их нужно в одну таблицу на один лист.  
 
Marat Ta, удалять дубликаты - слишком сложно для пользователей. Они там с трудом таблицу заполняют.
Либо кнопка нужна прям под таблицей где-то с надписью. А в ней макрос, удаляющий дубликаты. Наверное. Тогда они хотя бы не запутаются.

Предполагалась какая-то защита от исправления данных, которые были ранее внесены. Я пока до этого еще не дошла. Пока ищу решение, как все в одну таблицу грузить.
 
В 3 раз вопрос - как в вашей таблице определять дубликат? По какому столбцу или группе столбцов?
Изменено: Marat Ta - 14.02.2021 10:30:36
 
Marat Ta,сейчас такого столбца нет. Я думала его создать. В конце таблицы сделать столбец, назвать как-нибудь "код события" и заполнять МЛА1, МЛА2...
Ну и тогда определять, если в сводной таблице еще нет события с кодом МЛА2, то добавить его в конец таблицы.
 
Думаю в вашем случае, во избежание всяких подводных камней, самый простой вариант на участках вести свои нарастающие таблицы и каждый раз создавать сводную с ноля.

Макрос очистки данных в сводной перед загрузкой данных очень простой. Это избавит и от контроля за заменой ошибочных данных, исправленных на участках позднее.
 
Цитата
Маргарита Пыркина написал:
у меня таких файлов, типа МЛА, 4 штуки. Вот как подключить все файлы на разные листы сводной книги я знаю
Покажите запросы
Или попробуйте так
Код
= #"Запрос к первому участку"&#"Запрос ко второму участку"&#"Запрос к третьему участку"&#"Запрос к четвертому участку"
 
Сделайте в ваших файлах уникальную нумерацию.
Не 1, 2, 3, а
в первом 10001, 10002, 10003,
во втором 20001, 20002, 20003,
и будет вам счастье и аналог базы данных.
 
del
Изменено: Marat Ta - 17.02.2021 08:31:58
 
Добавил в макрос пользователя New возможность удаление дубликатов по номеру в 1 колонке, после добавления данных.

Каждый участок должен вести уникальную нумерацию, как предложили выше 10001, 10002... 20001... 40001
Удаляются дубликаты с конца списка, если нужно наоборот (уже обьяснял, если будут изменения в обновлении уже существующих данных) - измените порядок цикла в макросе.
Изменено: Marat Ta - 18.02.2021 15:22:44
 
Marat Ta,огромное спасибо! Вот это прям то, что нужно. Сейчас буду разбираться в коде этого макроса.  
 
вариант на pq
Код
let
    Source = Table.AddColumn(Folder.Files("C:\12"), "q", each Excel.Workbook([Content])),
    a = Table.TransformColumns(Source, {"q", each Table.SelectRows(_, each [Name] = "Лист1")})[[Name], [q]],
    b = Table.TransformColumns(a, {"q", each Table.SelectRows(Table.Skip(Table.PromoteHeaders([Data]{0}), 1), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))}),
    c = Table.ExpandTableColumn(b, "q", Table.ColumnNames(b{0}[q]))
in
    c

где должны находиться файлы и с какого листа берутся данные видно из запроса
 
Какой параметр нужно дать
.InitialFileName = "С:\" - чтобы он стартовал с текущей папки, где активная книга?  
Изменено: Marat Ta - 17.02.2021 08:30:21
 
Цитата
Marat Ta: Какой параметр нужно дать.InitialFileName, чтобы он стартовал с текущей папки, где активная книга?
=ActiveWorkBook.Path
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
del
Изменено: Marat Ta - 17.02.2021 12:27:20
 
Marat Ta,доброе утро.
Смотрите, с какой столкнулась вещью.
Если в одном из файлов участка (например ДПВС) изменить данные в строке (не добавить строку новую, а изменить информацию в существующей строке), например, статус (был "в работе"; стал "выполнено"), то в сводном файле информация не обновится.
Прописала строчку:
Код
Worksheets("Сводный").Range("A3:L100").ClearContents

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


В принципе можно сделать 2 кнопки. В качестве костыля.
Одна кнопка обновить данные и добавить вот эту строку:
Код
Worksheets("Сводный").Range("A3:L100").ClearContents

А вторая кнопка добавить данные (без этой строки).
Тогда добавляется в конец. А при обновлении уже подпрыгнет в середину она.
Изменено: Маргарита Пыркина - 20.02.2021 12:21:50
 
В 19 сообщении написано "измените порядок сортировки"

У вас условие первоначально было сформулировано: добавлять новые, удалять предыдущие дубликаты... так и было сделано.
Изменено: Marat Ta - 20.02.2021 13:54:28
 
Marat Ta, я не понимаю, где в коде этот цикл начинается. Подскажите, пожалуйста.

В приницпе с двумя кнопками не все хорошо.
Если оставить кнопку обновить и добавить, то немного не так работает.
Обновить должна только обновлять. А добавить - только добавлять в конец.
А получается, что добавить - только добавляет, а обновить - и обновляет, и добавляет.
Не знаю, как это отладить.
Изменено: Маргарита Пыркина - 20.02.2021 12:40:58
 
Я сейчас в дороге...
На память - Sub Sort_tab
' удаляем последнюю строку, содержащую одинаковые данные
ниже цикл

Не буду возражать, если вам кто то с форума поможет... я смогу только через час..
Изменено: Marat Ta - 20.02.2021 13:53:47
 
Marat Ta, похоже это наша с вами тема )
За мной никто не гонится.
Если у вас появится время и вы позже поможете - буду очень благодарна.
 
Marat Ta, я опять наверное некорректно объяснила. Простите, пожалуйста.
Смотрите, сейчас я загрузила сначала участок ДПВС (10 строк) и затем файл МЛА (5 строк).
Затем зашла в ДПВС, изменила в 10-ой строке статус (только статус) и добавила 11-строку.
Захожу в сводный. Он теперь все строки ДПВС (10 старых и 1 новую) перекинул вконец таблицы. А надо бы, что прошли изменения в 10-ой строке (но ее позиция в таблице не изменилась), затем шла строки МЛА (как они и были ранее), а в первую свободную строку (под старыми строками) вставилась новая 11-ая строка из ДПВС.
То есть старые строки обновились (но остались на местах), а новая строка всегда вниз добавлялась.
Так можно?
И чтобы файлы сами грузились из всех файлов в этой папке, как в загруженном мной выше сводном файле?
________________________________
В принципе вот в этом файле (прикрепленном), если из работы кнопки "обновить данные" убрать процесс добавления новых строк, а только обновлять имеющие строки в сводном файле ( не меняя позиции строчки в таблице), то можно так и оставить.
Изменено: Маргарита Пыркина - 20.02.2021 13:49:58
Страницы: 1 2 След.
Наверх