Добрый день, друзья! Не знаю вообще это реально или нет... У меня есть два списка с данными людей, каждый из них содержит ФИО и дату рождения, а так же какие-то необходимые уникальные значения(см. пример - место работы, рост, цвет волос и т.д.) Нужно найти повторяющиеся ФИО и даты рождения в списке, и вывести эти данные на третий лист. При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист - см. пример. Буду очень благодарен за советы! Спасибо за внимание!
Добрый день, друзья! Не знаю вообще это реально или нет... У меня есть два списка с данными людей, каждый из них содержит ФИО и дату рождения, а так же какие-то необходимые уникальные значения(см. пример - место работы, рост, цвет волос и т.д.) Нужно найти повторяющиеся ФИО и даты рождения в списке, и вывести эти данные на третий лист. При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист - см. пример. Буду очень благодарен за советы! Спасибо за внимание!komred44rus
Не работает почему-то... Из-за большого количества строк может глючить?(по 30 тыс. в обоих таблицах) Вывод чувствителен к регистру? На примере проверяю - все работает. На своих данных через 5 сек показывается пустая строка и все, ноль эффекта. Совпадающие строки точно есть
Не работает почему-то... Из-за большого количества строк может глючить?(по 30 тыс. в обоих таблицах) Вывод чувствителен к регистру? На примере проверяю - все работает. На своих данных через 5 сек показывается пустая строка и все, ноль эффекта. Совпадающие строки точно естьkomred44rus
Сообщение отредактировал komred44rus - Четверг, 10.08.2017, 11:59
komred44rus, проверьте имена таблиц-источников (Ctrl+F3), они должны совпадать с теми, что в примере (Таблица1, Таблица2). Ну или замените в Источниках запросов имена на те, что у вас. Также надо, чтобы в ваших таблицах по крайней мере присутствовали поля с точно такими же заголовками, причем все. Или, опять же, надо исправлять запросы в PowerQuery.
komred44rus, проверьте имена таблиц-источников (Ctrl+F3), они должны совпадать с теми, что в примере (Таблица1, Таблица2). Ну или замените в Источниках запросов имена на те, что у вас. Также надо, чтобы в ваших таблицах по крайней мере присутствовали поля с точно такими же заголовками, причем все. Или, опять же, надо исправлять запросы в PowerQuery.AndreTM
Вставляю данные прямо в прикрепленный уважаемым Udik-ом файл. Соответственно имена те же самые. В концах таблицы ввожу копипастом ФИО и д.р., обновляю запрос - и ноль эмоций. Единственное что заметил - форматы ячеек дня рождения разные. в одной таблице - "все форматы" - в другой "Общий". И поменять никак не получается))
Вставляю данные прямо в прикрепленный уважаемым Udik-ом файл. Соответственно имена те же самые. В концах таблицы ввожу копипастом ФИО и д.р., обновляю запрос - и ноль эмоций. Единственное что заметил - форматы ячеек дня рождения разные. в одной таблице - "все форматы" - в другой "Общий". И поменять никак не получается))komred44rus
Сначала отключите "умные таблицы" в источниках (Лист1, Лист2) ( ПКМ на таблице - Таблица... - Преобразовать в диапазон). Затем вставьте свои данные. Затем заново создайте "умные таблицы" (щелкнуть внутри диапазона, Главная - Форматировать как таблицу - щелкнуть по любому стилю - подтвердить, что "Таблица с заголовками"). И вот затем уже обновите результат (Данные - Обновить всё).
Либо вам нужно смотреть, какой именно шаг исполнения запросов вызывает ошибку, для этого запускайте PowerQuery - Запустить редактор и смотрите все запросы по всем шагам.
Сначала отключите "умные таблицы" в источниках (Лист1, Лист2) ( ПКМ на таблице - Таблица... - Преобразовать в диапазон). Затем вставьте свои данные. Затем заново создайте "умные таблицы" (щелкнуть внутри диапазона, Главная - Форматировать как таблицу - щелкнуть по любому стилю - подтвердить, что "Таблица с заголовками"). И вот затем уже обновите результат (Данные - Обновить всё).
Либо вам нужно смотреть, какой именно шаг исполнения запросов вызывает ошибку, для этого запускайте PowerQuery - Запустить редактор и смотрите все запросы по всем шагам.AndreTM
Сначала ... Затем ... Затем заново ... щелкнуть ... И вот затем уже ... Либо... смотрите все запросы по всем шагам.
или Подготовьте данные, запустите макрос [vba]
Код
Option Explicit Option Base 1 Sub ДваТоварищаАга_InExSu() Dim arr01(), arr02(), arr03() Dim ub_arr01 As Long, ub_arr02 As Long, ub_arr03 As Long Dim ub2_arr01 As Long, ub2_arr02 As Long, ub2_arr03 As Long Dim i3NonBlank As Long Dim i As Long, j As Long, k As Long Dim str1 As String, str2 As String Worksheets("Лист3").Cells.Clear arr01() = Worksheets("Лист1").Range("a1").CurrentRegion.Value arr02() = Worksheets("Лист2").Range("a1").CurrentRegion.Value ub_arr01 = UBound(arr01, 1): ub_arr02 = UBound(arr02, 1): ub2_arr01 = UBound(arr01, 2): ub2_arr02 = UBound(arr02, 2) ub2_arr03 = ub2_arr01 + ub2_arr02 - 4 ReDim arr03(ub_arr01 + ub_arr02, _ ub2_arr03) i3NonBlank = 1 For i = 1 To ub_arr01 For j = 1 To ub_arr02 str1 = arr01(i, 1) & arr01(i, 2) & arr01(i, 3) & arr01(i, 4) str2 = arr02(j, 1) & arr02(j, 2) & arr02(j, 3) & arr02(j, 4) If StrComp(str1, str2) = 0 Then For k = 1 To ub2_arr03 If k <= ub2_arr01 Then arr03(i3NonBlank, k) = arr01(i, k) Else arr03(i3NonBlank, k) = arr02(i, k - 2) ' разница столбцов End If Next i3NonBlank = i3NonBlank + 1 End If Next Next With Worksheets("Лист3") .Cells.Clear .Range("a1").Resize(UBound(arr03), UBound(arr03, 2)) = arr03 .Select End With End Sub
Сначала ... Затем ... Затем заново ... щелкнуть ... И вот затем уже ... Либо... смотрите все запросы по всем шагам.
или Подготовьте данные, запустите макрос [vba]
Код
Option Explicit Option Base 1 Sub ДваТоварищаАга_InExSu() Dim arr01(), arr02(), arr03() Dim ub_arr01 As Long, ub_arr02 As Long, ub_arr03 As Long Dim ub2_arr01 As Long, ub2_arr02 As Long, ub2_arr03 As Long Dim i3NonBlank As Long Dim i As Long, j As Long, k As Long Dim str1 As String, str2 As String Worksheets("Лист3").Cells.Clear arr01() = Worksheets("Лист1").Range("a1").CurrentRegion.Value arr02() = Worksheets("Лист2").Range("a1").CurrentRegion.Value ub_arr01 = UBound(arr01, 1): ub_arr02 = UBound(arr02, 1): ub2_arr01 = UBound(arr01, 2): ub2_arr02 = UBound(arr02, 2) ub2_arr03 = ub2_arr01 + ub2_arr02 - 4 ReDim arr03(ub_arr01 + ub_arr02, _ ub2_arr03) i3NonBlank = 1 For i = 1 To ub_arr01 For j = 1 To ub_arr02 str1 = arr01(i, 1) & arr01(i, 2) & arr01(i, 3) & arr01(i, 4) str2 = arr02(j, 1) & arr02(j, 2) & arr02(j, 3) & arr02(j, 4) If StrComp(str1, str2) = 0 Then For k = 1 To ub2_arr03 If k <= ub2_arr01 Then arr03(i3NonBlank, k) = arr01(i, k) Else arr03(i3NonBlank, k) = arr02(i, k - 2) ' разница столбцов End If Next i3NonBlank = i3NonBlank + 1 End If Next Next With Worksheets("Лист3") .Cells.Clear .Range("a1").Resize(UBound(arr03), UBound(arr03, 2)) = arr03 .Select End With End Sub
InExSu, при большом количестве данных (десятки-сотни тысяч строк в обоих списках) делать макросы с вложенными циклами и полным перебором - это гарантированный путь к завешиванию программы (даже если в памяти работать). Надо обязательно использовать словари (даже с учетом затрат на первичное создание). Ну и PowerQuery - он такой, он как раз и предназначен для обработки больших объемов
InExSu, при большом количестве данных (десятки-сотни тысяч строк в обоих списках) делать макросы с вложенными циклами и полным перебором - это гарантированный путь к завешиванию программы (даже если в памяти работать). Надо обязательно использовать словари (даже с учетом затрат на первичное создание). Ну и PowerQuery - он такой, он как раз и предназначен для обработки больших объемов AndreTM
Что имеется ввиду под словарями? Не понял... Проблема до сих пор актуальна, испробовал пока все предложенные способы - не находятся нужные строки. Сейчас все же попробовал запустить макрос - все намертво висит, жду, может развиснет) Как посмотреть лог работы PowerQuery? там проскакивает какая-то строка, но прочитать ее невозможно. В настройках запроса особо ничего не понял, но все столбцы подсвечиваются зеленым, я так понял что сам софт до начала выполнения запроса не видит никаких ошибок. Во всяком случае мне так кажется...
Что имеется ввиду под словарями? Не понял... Проблема до сих пор актуальна, испробовал пока все предложенные способы - не находятся нужные строки. Сейчас все же попробовал запустить макрос - все намертво висит, жду, может развиснет) Как посмотреть лог работы PowerQuery? там проскакивает какая-то строка, но прочитать ее невозможно. В настройках запроса особо ничего не понял, но все столбцы подсвечиваются зеленым, я так понял что сам софт до начала выполнения запроса не видит никаких ошибок. Во всяком случае мне так кажется...komred44rus
Словари я ещё не выучил. Может тот же макрос с индикатором? [vba]
Код
Sub ДваТоварищаАга_InExSu()
Dim arr01(), arr02(), arr03() Dim ub_arr01 As Long, ub_arr02 As Long, ub_arr03 As Long Dim ub2_arr01 As Long, ub2_arr02 As Long, ub2_arr03 As Long Dim i3NonBlank As Long Dim i As Long, j As Long, k As Long Dim str1 As String, str2 As String Worksheets("Лист3").Cells.Clear arr01() = Worksheets("Лист1").Range("a1").CurrentRegion.Value arr02() = Worksheets("Лист2").Range("a1").CurrentRegion.Value ub_arr01 = UBound(arr01, 1): ub_arr02 = UBound(arr02, 1): ub2_arr01 = UBound(arr01, 2): ub2_arr02 = UBound(arr02, 2) ub2_arr03 = ub2_arr01 + ub2_arr02 - 4 ReDim arr03(ub_arr01 + ub_arr02, _ ub2_arr03) i3NonBlank = 1 timeStart = Timer
For i = 1 To ub_arr01 For j = 1 To ub_arr02 If StrComp(arr01(i, 1) & arr01(i, 2) & arr01(i, 3) & arr01(i, 4), _ arr02(j, 1) & arr02(j, 2) & arr02(j, 3) & arr02(j, 4)) = 0 Then For k = 1 To ub2_arr03 If k <= ub2_arr01 Then arr03(i3NonBlank, k) = arr01(i, k) Else arr03(i3NonBlank, k) = arr02(i, k - 2) ' разница столбцов End If Next i3NonBlank = i3NonBlank + 1 End If Next j DoEvents 'Call GetFor_By(i, ub_arr01) Application.StatusBar = i & " из " & ub_arr01 Next i Application.StatusBar = False With Worksheets("Лист3") .Cells.Clear .Range("a1").Resize(UBound(arr03), UBound(arr03, 2)) = arr03 .Select End With MsgBox "Макрос ДваТоварищаАга_InExSu отработал", vbOKOnly Or vbInformation, "Завершение!" End Sub
[/vba]
Словари я ещё не выучил. Может тот же макрос с индикатором? [vba]
Код
Sub ДваТоварищаАга_InExSu()
Dim arr01(), arr02(), arr03() Dim ub_arr01 As Long, ub_arr02 As Long, ub_arr03 As Long Dim ub2_arr01 As Long, ub2_arr02 As Long, ub2_arr03 As Long Dim i3NonBlank As Long Dim i As Long, j As Long, k As Long Dim str1 As String, str2 As String Worksheets("Лист3").Cells.Clear arr01() = Worksheets("Лист1").Range("a1").CurrentRegion.Value arr02() = Worksheets("Лист2").Range("a1").CurrentRegion.Value ub_arr01 = UBound(arr01, 1): ub_arr02 = UBound(arr02, 1): ub2_arr01 = UBound(arr01, 2): ub2_arr02 = UBound(arr02, 2) ub2_arr03 = ub2_arr01 + ub2_arr02 - 4 ReDim arr03(ub_arr01 + ub_arr02, _ ub2_arr03) i3NonBlank = 1 timeStart = Timer
For i = 1 To ub_arr01 For j = 1 To ub_arr02 If StrComp(arr01(i, 1) & arr01(i, 2) & arr01(i, 3) & arr01(i, 4), _ arr02(j, 1) & arr02(j, 2) & arr02(j, 3) & arr02(j, 4)) = 0 Then For k = 1 To ub2_arr03 If k <= ub2_arr01 Then arr03(i3NonBlank, k) = arr01(i, k) Else arr03(i3NonBlank, k) = arr02(i, k - 2) ' разница столбцов End If Next i3NonBlank = i3NonBlank + 1 End If Next j DoEvents 'Call GetFor_By(i, ub_arr01) Application.StatusBar = i & " из " & ub_arr01 Next i Application.StatusBar = False With Worksheets("Лист3") .Cells.Clear .Range("a1").Resize(UBound(arr03), UBound(arr03, 2)) = arr03 .Select End With MsgBox "Макрос ДваТоварищаАга_InExSu отработал", vbOKOnly Or vbInformation, "Завершение!" End Sub
_Boroda_, хорошая, конечно, реализация слияния Но, Александр, нам же нужен Inner Join, а не FullOuter, даже если к нему применим (Top 1). Кстати, не пойму, почему Udik использовал в запросе JoinKind.LeftOuter вместо JoinKind.Inner?
Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки - то именно эта операция и скушает много дополнительного времени. Да и цикл все равно будет Кроме того по "результирующей" таблице (при некоторых условиях) будет вообще невозможно понять, какие именно строки оказались "лишними". Например, незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль - мало ли, может в исходных таблицах эти ячейки тоже не были заполнены.
_Boroda_, хорошая, конечно, реализация слияния Но, Александр, нам же нужен Inner Join, а не FullOuter, даже если к нему применим (Top 1). Кстати, не пойму, почему Udik использовал в запросе JoinKind.LeftOuter вместо JoinKind.Inner?
Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки - то именно эта операция и скушает много дополнительного времени. Да и цикл все равно будет Кроме того по "результирующей" таблице (при некоторых условиях) будет вообще невозможно понять, какие именно строки оказались "лишними". Например, незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль - мало ли, может в исходных таблицах эти ячейки тоже не были заполнены.AndreTM
Ну, ТС "себе в голове" как обозвал другие данные (кроме ключей) "уникальными значениями" - так и далее в том же духе продолжил. Хотя в примере же было видно, что ему нужно именно получить только данные совпадающих строк, у него на третьем листе как раз и был пример результата - всего одна совпавшая строка. Кстати, мне вот этим и нравится .NestedJoin в PQ - одной операцией получаем и соединение необходимых таблиц по набору ключей, и все остальные "неключевые" поля таблиц, которые потом можем развернуть до нужного нам набора тех полей, что должны быть в результате.
Ну, ТС "себе в голове" как обозвал другие данные (кроме ключей) "уникальными значениями" - так и далее в том же духе продолжил. Хотя в примере же было видно, что ему нужно именно получить только данные совпадающих строк, у него на третьем листе как раз и был пример результата - всего одна совпавшая строка. Кстати, мне вот этим и нравится .NestedJoin в PQ - одной операцией получаем и соединение необходимых таблиц по набору ключей, и все остальные "неключевые" поля таблиц, которые потом можем развернуть до нужного нам набора тех полей, что должны быть в результате.AndreTM
будет вообще невозможно понять, какие именно строки оказались "лишними"
Вот этого ну вообще никак не понял....
Как еще по понятней можно объяснить даже не знаю. Попробую такой пример. Допустим у меня есть список работников с указанием их места жительства. И есть список людей, обедающих в местной столовой с указанием кто что ел. Не обязательно только "работников". В обоих списках обязательно есть ФИО и дата рождения - по ним и будем искать совпадения. Оба списка упорядочены по алфавиту, в "столовом" списке есть дубли ключевых данных, но доп данные уже другие. Их тоже надо выводить в итоговую таблицу. Мне надо: 1. Найти тех работников, которые обедают в столовой. 2. Доп. данные (это место жительства и что едят) найденных работников вывести в итоговую таблицу. Остальное меня не интересует... Повторюсь - все строки в таблице гарантированно заполнены, пустых полей нет.
будет вообще невозможно понять, какие именно строки оказались "лишними"
Вот этого ну вообще никак не понял....
Как еще по понятней можно объяснить даже не знаю. Попробую такой пример. Допустим у меня есть список работников с указанием их места жительства. И есть список людей, обедающих в местной столовой с указанием кто что ел. Не обязательно только "работников". В обоих списках обязательно есть ФИО и дата рождения - по ним и будем искать совпадения. Оба списка упорядочены по алфавиту, в "столовом" списке есть дубли ключевых данных, но доп данные уже другие. Их тоже надо выводить в итоговую таблицу. Мне надо: 1. Найти тех работников, которые обедают в столовой. 2. Доп. данные (это место жительства и что едят) найденных работников вывести в итоговую таблицу. Остальное меня не интересует... Повторюсь - все строки в таблице гарантированно заполнены, пустых полей нет.komred44rus
Файлики 2-34817-1 и 2478712 работают, но почему то мало совпадений нашлось.. всего 41. У меня в таблице на первом листе 25 тыс строк, на втором листе 47 тыс. Должно быть точно больше. В 2478712 что делается на листе Example? Похоже на объединенный список, но там 27 тыс строк всего получается. И не туда и не сюда
Файлики 2-34817-1 и 2478712 работают, но почему то мало совпадений нашлось.. всего 41. У меня в таблице на первом листе 25 тыс строк, на втором листе 47 тыс. Должно быть точно больше. В 2478712 что делается на листе Example? Похоже на объединенный список, но там 27 тыс строк всего получается. И не туда и не сюдаkomred44rus