Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как собрать данные в большие таблицы (более 1,048 млн. строк) и поместить в модель данных PowerPivot?, как "подтянуть" данные при большом количестве строк
 
#1
14.11.2017 12:00:38
Добрый день, уважаемые форумчане. Имеются 2 таблицы, нужно подтянуть данные из одной таблицы в другую - 5 столбцов. Классически легко решается с помощью ВПР, но есть нюанс - каждая таблица размещена в трех файлах: в одном 1,5 млн. строк, в другом 2,2 млн. Собрал каждую таблицу в файл на 3-х листах. Попробовал проВПРить 1-й лист одной таблицы (ок. 0,5 млн строк) с 1-м листом 2-й таблицы, затем со 2-м листом, затем с 3-м (на каждом ок. 1 млн. строк) - и увяз на целый день. Может, кто-то подскажет другой способ решения задачи (PQ или что-то еще?). Буду признателен как идеям, в каком направлении двигаться, так и конкретному решению. Для простоты в  примере обе таблицы разместил в одном файле: на листах 1-3 что ищем, на листах 4-6 где ищем; данные условные и их немного. На листе 1 под зеленой шапкой найденные данные с листов 4-6. Офис 2016, 32 разр.
Excel непознаваем как атом.
 
 
 
#2
14.11.2017 12:03:31
файла нет :(
Excel + SQL = Activetables
 
 
 
#3
14.11.2017 12:05:13
Прикрепил повторно :)  
Excel непознаваем как атом.
 
 
 
#4
14.11.2017 12:07:26
Можно при помощи PQ, можно при помощи Power Pivot. Можно при помощи VBA.
Если по сокрости, то самое быстрое сопоставление будет происходить в Power Pivot. Результат поместите в модель данных из неё можете уже делать сводные отчеты.
Ну и файла с примером не видно.
Вот горшок пустой, он предмет простой...
 
 
 
#5
14.11.2017 12:37:56
Если данные на листе со справочником можно отсортировать (в примере на листе 6), то можно вот так (см. файл - сделал только для листа 6, думаю смысл понятен). А вообще, как уже говорил PooHkrd, Power Pivot - быстрее всего.
Если все вышеизложенное неприемлемо, то в общем случае (если из справочника надо подтягивать несколько столбцов) - имеет смысл заменить ВПР на ПОИСКПОЗ+ИНДЕКС (прирост скорости кратен кол-ву столбцов, на больших массивах данных несколько столбцов по времени будут подтягиваться как один столбец с ВПР)
 
 
 
#6
14.11.2017 12:38:49
Спасибо. Буду пробовать. С этими "зверями" пока не работал.
Excel непознаваем как атом.
 
 
 
#7
14.11.2017 12:44:38
Поскольку задача позиционируется как еженедельная, надо осваивать Power Pivot  :cry:  
Excel непознаваем как атом.
 
 
 
#8
14.11.2017 12:54:56
А что за файлы то? :)
Какие тут ВПР - нужно парсить один построчно, набивать словарь, затем парсить второй и писать в третий, добавляя из словаря...
 
 
 
#9
14.11.2017 13:01:43
Код
Public Sub RefreshData()

'Created using add-in ActiveTables
Dim strConnection As String
Dim strSQL As String
strConnection = IIf(Val(Application.Version) < 12, "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';", "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';")
strSQL = "SELECT   Лист1.Номер AS Номер,  Лист4.АДРЕС AS АДРЕС  FROM   [Лист1$] AS Лист1    inner JOIN [Лист4$] AS Лист4   ON Лист1.Номер=Лист4.Nonber   union all  SELECT   Лист1.Номер AS Номер,  Лист5.АДРЕС AS АДРЕС  FROM   [Лист1$] AS Лист1    inner JOIN [Лист5$] AS Лист5   ON Лист1.Номер=Лист5.phone"
With ThisWorkbook.ActiveSheet
    .UsedRange.Clear
    With .QueryTables.Add(strConnection, .Range("A1"), strSQL)
         .Refresh False
         .Delete
    End With
End With

End Sub
Изменено: PowerBoy - 14.11.2017 13:02:48
Excel + SQL = Activetables
 
 
 
#10
14.11.2017 13:13:32
PowerBoy,  Код в стандартный модуль? И если можно, небольшие комментарии к коду.

webley, спасибо за идею насчет сортировки и замены ВПР на более производительную связку.
Изменено: mtts54 - 14.11.2017 13:17:42
Excel непознаваем как атом.
 
 
 
#11
14.11.2017 13:51:23
Мне не ответите?
 
 
 
#12
14.11.2017 15:02:05
Цитата
mtts54 написал:
И если можно, небольшие комментарии к коду.
А какие там комментарии? При помощи SQL запроса товарищ вам сделал консолидацию таблиц и джойн нужных столбцов из одной таблицы к другой.
Аналогичную операцию можно сделать и в PQ за 3-4 нажатия кнопок, либо в PP, но нажатий будет по-больше и придется формулу ручками прописывать.
А вообще вот здесь уже давно разбирался ваш вопрос.
Изменено: PooHkrd - 14.11.2017 15:31:14
Вот горшок пустой, он предмет простой...
 
 
 
#13
14.11.2017 15:32:44
Hugo, с парсингом и словарями не знаком, а результат нужен сегодня... Почитаю позже.
PooHkrd, спасибо за коммент, я примерно так и представлял работу этого кода. Пока 6 строка кода красная. Разберусь потом.
Прочитал статью уважаемого Николая Павлова "Поиск совпадений в двух списках" и пытаюсь приспособить под свою задачу. Нажатий кнопок было поболее 3-4х, но у меня получается при слиянии, что я могу сравнить один (на выбор) лист 1-го списка с одним листом (на выбор) 2-го списка. PQ отрапортовал, что нашел ок. 460 тыс совпадений, но вот вывалить на лист пока не может, вижу, как он тужится, бедненький... Merge1 висит десяток минут, но без результата. Можно, конечно перебрать все варианты, но это затратный метод. Как бы сначала получить весь первый и весь второй список, а потом уже искать совпадения. Merge1 остановил и запустил обновление - может, сподобится выгрузить на лист...
Excel непознаваем как атом.
 
 
 
#14
14.11.2017 15:33:43
А на вопрос ответите? Вопрос это то, что оканчивается вопросительным знаком.
 
 
 
#15
14.11.2017 15:49:58
Hugo, файлы-источники: 1-й список это 3 csv-файла общим весом 280 мб, собрал в 1 xlsx-файл 113 мб; второй - 3 xls-файла, также собрал в 1 xlsx-файл 101 мб, в каждом файле получилось по 3 листа, общее количество строк - 2,2 млн и 1,2 млн соответственно.
Excel непознаваем как атом.
 
 
 
#16
14.11.2017 16:20:03
Цитата
mtts54 написал:
Как бы сначала получить весь первый и весь второй список, а потом уже искать совпадения.
PQ лучше использовать для консолидации таблиц (чтобы получить единую таблицу на 1-2 млн строк), зачистки их от "мусора", результат тащить в модель данных, а в ней уже через Power Pivot настраивать взаимосвязи между таблицами по ключевым столбцам и через функцию RELATED подтягивать нужные вам значения в итоговую таблицу. Это самый быстрый способ подстановки значений без использования макросов.
Опять же в модели данных не обязательно подставлять значения из одной таблицы в другую, достаточно настроить взаимосвязи между ними и нужные поля сразу выводить в область значений сводной таблицы. Ну или меру сварганить какую-нибудь, если потребуются сложные вычисления.
Вот горшок пустой, он предмет простой...
 
 
 
#17
14.11.2017 16:20:39
Я веду к тому, что скорее всего нет нужды собирать/открывать/мучиться с csv в Экселе, можно сразу их и дербанить. Скорее всего, но нужно видеть что там за содержимое, можно ли тупо резать строки по разделителям, и вся ли строка в одной строке...
Если второй список в xlsx на листах - ну пусть.
А вот куда складывать эти миллионы - так и не сказали. А если сказали - ткните носом где :)
Если в csv - тогда проще всего (если эти csv обычные) - генерим в процессе новый с миллионами строк.
 
 
 
#18
14.11.2017 16:38:11
Доброе время суток
Цитата
PooHkrd написал:
и через функцию RELATED подтягивать нужные вам значения в итоговую таблицу
Коллега, ну зачем вы учите человека плохому? Power Pivot и сводные для обобщения, по дате/времени, по группам, по товарам. Зачем вам визуально просматривать таблицу на 2 миллиона строк? Что там можно найти? CSV можно и через Power Query готовить для сводной, а лучше грузить в Access и там выполнять манипуляции. Такие задачи уже практически не имеют смысла для обработки в Excel, разве что как инструмент представления в виде сводных.
 
 
 
#19
14.11.2017 16:44:42
Андрей VG,
Я плохому не учу, я объясняю как средствами Экселя решить поставленную задачу.  8)
При этом я упомянул, что ВПР-ить не обязательно, достаточно затащить таблицы в модель данных и настроить между ними связи. А дальше лепить сводные на любой вкус.
Про слияние данных через PQ топикстартер уже указал, что запрос подвисает. Собственно поэтому я ему и предложил вариант с PP.
mtts54,
Кстати, а Excel то у вас хотя бы 64 бит?
Изменено: PooHkrd - 14.11.2017 16:48:21
Вот горшок пустой, он предмет простой...
 
 
 
#20
14.11.2017 16:57:00
Спасибо всем откликнувшимся!
Hugo, Андрей VG, начальство дало это задание, рассчитывая получить на выходе список, пусть даже на нескольких листах, и там автофильтром сравнивать значения в колонках "состояние" обеих таблиц (для простоты я ее не показал в 1-й таблице). Задание, возможно, изменится после первого же просмотра начальством... Совпадающих строк будет ок. миллиона, но и это количество способно либо загнать в тоску, либо обработать данные для удобоваримого созерцания. Так что все советы освою и опробую. Спасибо.
Excel непознаваем как атом.
 
 
 
#21
14.11.2017 17:09:13
автофильтром миллион? ....
Если делать моим подходом - можно сразу генерить файл с нужными строками, и отдать его начальству - пусть смотрит :)
 
 
 
#22
14.11.2017 17:18:37
Мне всегда непонятно: кому нужны километры информационных строк?
Ваша информация не просто грандиозна - она чрезвычайно грандиозна!
И она, информация, однозначно, экспорт из ПО, работающего в Вашей организации.
Уверена, что в такой грандиозной БД существуют шаблоны экспорта не только для тупой выгрузки данных!
Однозначно, должен быть шаблон, позволяющий "включать мозги" при экспорте.  :D
Ваша ситуация:
1. Поставленная перед Вами задача реально никому не нужна.
Цитата
mtts54 написал:
начальство дало это задание
2. Ваше начальство просто "не в адеквате" перед потоком информации.
 
 
 
#23
14.11.2017 18:19:37
PowerBoy,не работает код
 
 
 
#24
16.11.2017 10:47:10
Вначале проблема виделась в том, чтобы ВПР-ом (или иначе) подтянуть данные в 2-х больших таблицах (с такой задачей столкнулся впервые и о современных средствах бизнес-анализа что-то слышал но ими не пользовался). Почитав Ваши и другие ответы понял, что нужен другой подход к решению задачи. Получив здесь советы, что-то почитав и посмотрев ютуб, с помощью PowerQuery собрал исходные данные из 6 файлов в два запроса; далее пытался выполнить слияние, но каждый раз неудачно - были какие-то ошибки. Оказалось, были дубликаты в столбцах "Номер" и "Nonber". Дубли удалил, слияние внутреннее сделал, получил строки 1-го файла,в которых номера совпадают со 2-м файлом. А как привинтить к ним данные из 5 колонок 2-го файла? Пока сделал с помощью ИНДЕКС+ПОИСКПОЗ. Начальство уточнило задачу - в ответе сгруппировать данные по районам (на листах 1-3 я не показал, рассчитывая на автофильтр и копипаст). Я видел, что в PowerPivot есть кнопка "Группировка", но PowerPivot работает с умными таблицами (я так понял из увиденного и прочитанного), а как мне перевести мои таблицы, не умещающиеся на один лист, в умные? И все еще остается вопрос с привинчиванием 5 колонок :cry: .
Excel непознаваем как атом.
 
 
 
#25
16.11.2017 10:59:21
Я PowerPivot в глаза не видел - нет ни на работе, ни дома, поэтому тут я пас. Я говорил о вообще другом подходе к проблеме.
 
 
 
#26
16.11.2017 11:16:28
Не понятно, как вы смогли провести слияние двух таблиц, но при этом не смогли вытащить из одной таблицы столбцы в другую?
Операция "Слияние" как раз и подразумевает подстановку значений в таблицу из столбцов другой.
Дайте примеры с запросами, которые у вас получились.

Power Pivot работает не с умными таблицами, а с таблицами, помещенными в модель данных (просто умные таблицы видны в модели данных автоматически). Чтобы загрузить те таблицы, которые вы сформировали при помощи PQ в модель данных, нужно клацнуть правой кнопкой мыши по запросу в списке, потом выбрать пункт "Загрузить в...", в вывалившемся окне нужно нажать галку напротив надписи "Добавить эти данные в модель данных" и жмите ОК. (Надписи могут слегка отличаться в зависимости от версии вашего PQ.
Вот горшок пустой, он предмет простой...
 
 
 
#27
16.11.2017 13:13:29
PooHkrd, спасибо за отклик. Прикладываю 2 маленьких файла для примера и запрос. Запросы делал по схеме: 1-й запрос: данные - создать запрос - из файла - из папки (там у меня 3 файла) - ок - комбинировать - объединить и изменить - ок. 2-й запрос: создать источник - папка - далее аналогично. В запросах PQ: комбинировать - слияние запросов - указал колонки Phone - тип соединения - внутреннее (только совпадающие строки). Что получил - это файл Запрос PQ. На листе2 - совпадающие строки, а как привинтить в запросах данные колонок 2-5 из файла "Ст" (файл выгрузился на лист3 почему-то).
В запросах клацал ПКМ по запросу в списке слева, но не нашел "Загрузить в..." и "Добавить эти данные в модель данных".
Изменено: mtts54 - 16.11.2017 13:14:03
Excel непознаваем как атом.
 
 
 
#28
16.11.2017 13:15:49
Файлы повторно пришлось прицепить.
Excel непознаваем как атом.
 
 
 
#29
16.11.2017 14:07:20
Цитата
mtts54 написал:
файлы-источники: 1-й список это 3 csv-файла общим весом 280 мб,
- и где?
Будете частями в xlsx запихивать и частями обрабатывать?
 
 
 
#30
16.11.2017 14:46:10
Hugo, нет, PowerQuery легко собрал и xls и csv файлы в 2 запроса. Дальше, правда, у меня заминка. Не отправляются они у меня в PowerPivot для дальнейшей обработки. А при слиянии запросов в PQ по столбцам "Phone" после 15 мин. раздумья  :cry:  происходит ошибка: Ключу не соответствует ни одна строка в таблице, Key=Record, Table=Table, о чём это он? Подозреваю, что в столбец "Phone" могли записать №125469 или 958-47-55 или еще что-нибудь. Как найти в 1,5-2 млн. строк?
Что касается парсинга - не занимался этим, ничего не могу сказать. Судя по Вашим комментам, это мощная вещь. Но ведь быстро ее не освоить... :cry:  
Excel непознаваем как атом.
 
 
 
Страницы: 1 2 След.
Наверх