Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Анализ данных на двух листах и вывод их на третий - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Анализ данных на двух листах и вывод их на третий (Формулы/Formulas)
Анализ данных на двух листах и вывод их на третий
komred44rus Дата: Вторник, 08.08.2017, 18:22 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день, друзья! Не знаю вообще это реально или нет...
У меня есть два списка с данными людей, каждый из них содержит ФИО и дату рождения, а так же какие-то необходимые уникальные значения(см. пример - место работы, рост, цвет волос и т.д.)
Нужно найти повторяющиеся ФИО и даты рождения в списке, и вывести эти данные на третий лист. При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист - см. пример.
Буду очень благодарен за советы! Спасибо за внимание!
К сообщению приложен файл: 3838018.xlsx(10Kb)
 
Ответить
СообщениеДобрый день, друзья! Не знаю вообще это реально или нет...
У меня есть два списка с данными людей, каждый из них содержит ФИО и дату рождения, а так же какие-то необходимые уникальные значения(см. пример - место работы, рост, цвет волос и т.д.)
Нужно найти повторяющиеся ФИО и даты рождения в списке, и вывести эти данные на третий лист. При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист - см. пример.
Буду очень благодарен за советы! Спасибо за внимание!

Автор - komred44rus
Дата добавления - 08.08.2017 в 18:22
Udik Дата: Вторник, 08.08.2017, 18:54 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1589
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Чет меня на Power Query сегодня тянет.
К сообщению приложен файл: 2316361.xlsx(22Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеЧет меня на Power Query сегодня тянет.

Автор - Udik
Дата добавления - 08.08.2017 в 18:54
komred44rus Дата: Четверг, 10.08.2017, 11:56 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Чет меня на Power Query сегодня тянет.

Не работает почему-то...
Из-за большого количества строк может глючить?(по 30 тыс. в обоих таблицах) Вывод чувствителен к регистру?
На примере проверяю - все работает. На своих данных через 5 сек показывается пустая строка и все, ноль эффекта. Совпадающие строки точно есть


Сообщение отредактировал komred44rus - Четверг, 10.08.2017, 11:59
 
Ответить
Сообщение
Чет меня на Power Query сегодня тянет.

Не работает почему-то...
Из-за большого количества строк может глючить?(по 30 тыс. в обоих таблицах) Вывод чувствителен к регистру?
На примере проверяю - все работает. На своих данных через 5 сек показывается пустая строка и все, ноль эффекта. Совпадающие строки точно есть

Автор - komred44rus
Дата добавления - 10.08.2017 в 11:56
AndreTM Дата: Четверг, 10.08.2017, 20:35 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
komred44rus, проверьте имена таблиц-источников (Ctrl+F3), они должны совпадать с теми, что в примере (Таблица1, Таблица2). Ну или замените в Источниках запросов имена на те, что у вас.
Также надо, чтобы в ваших таблицах по крайней мере присутствовали поля с точно такими же заголовками, причем все. Или, опять же, надо исправлять запросы в PowerQuery.


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщениеkomred44rus, проверьте имена таблиц-источников (Ctrl+F3), они должны совпадать с теми, что в примере (Таблица1, Таблица2). Ну или замените в Источниках запросов имена на те, что у вас.
Также надо, чтобы в ваших таблицах по крайней мере присутствовали поля с точно такими же заголовками, причем все. Или, опять же, надо исправлять запросы в PowerQuery.

Автор - AndreTM
Дата добавления - 10.08.2017 в 20:35
komred44rus Дата: Пятница, 11.08.2017, 00:07 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
проверьте имена таблиц-источников

Вставляю данные прямо в прикрепленный уважаемым Udik-ом файл. Соответственно имена те же самые. В концах таблицы ввожу копипастом ФИО и д.р., обновляю запрос - и ноль эмоций. Единственное что заметил - форматы ячеек дня рождения разные. в одной таблице - "все форматы" - в другой "Общий". И поменять никак не получается))
 
Ответить
Сообщение
проверьте имена таблиц-источников

Вставляю данные прямо в прикрепленный уважаемым Udik-ом файл. Соответственно имена те же самые. В концах таблицы ввожу копипастом ФИО и д.р., обновляю запрос - и ноль эмоций. Единственное что заметил - форматы ячеек дня рождения разные. в одной таблице - "все форматы" - в другой "Общий". И поменять никак не получается))

Автор - komred44rus
Дата добавления - 11.08.2017 в 00:07
AndreTM Дата: Пятница, 11.08.2017, 02:02 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Сначала отключите "умные таблицы" в источниках (Лист1, Лист2) ( ПКМ на таблице - Таблица... - Преобразовать в диапазон).
Затем вставьте свои данные.
Затем заново создайте "умные таблицы" (щелкнуть внутри диапазона, Главная - Форматировать как таблицу - щелкнуть по любому стилю - подтвердить, что "Таблица с заголовками").
И вот затем уже обновите результат (Данные - Обновить всё).

Либо вам нужно смотреть, какой именно шаг исполнения запросов вызывает ошибку, для этого запускайте PowerQuery - Запустить редактор и смотрите все запросы по всем шагам.


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеСначала отключите "умные таблицы" в источниках (Лист1, Лист2) ( ПКМ на таблице - Таблица... - Преобразовать в диапазон).
Затем вставьте свои данные.
Затем заново создайте "умные таблицы" (щелкнуть внутри диапазона, Главная - Форматировать как таблицу - щелкнуть по любому стилю - подтвердить, что "Таблица с заголовками").
И вот затем уже обновите результат (Данные - Обновить всё).

Либо вам нужно смотреть, какой именно шаг исполнения запросов вызывает ошибку, для этого запускайте PowerQuery - Запустить редактор и смотрите все запросы по всем шагам.

Автор - AndreTM
Дата добавления - 11.08.2017 в 02:02
InExSu Дата: Пятница, 11.08.2017, 02:18 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 183
Репутация: 22 ±
Замечаний: 60% ±

Excel 2010
Сначала ... Затем ... Затем заново ... щелкнуть ... И вот затем уже ... Либо... смотрите все запросы по всем шагам.

или
Подготовьте данные, запустите макрос
[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]


Сообщение отредактировал InExSu - Пятница, 11.08.2017, 02:21
 
Ответить
Сообщение
Сначала ... Затем ... Затем заново ... щелкнуть ... И вот затем уже ... Либо... смотрите все запросы по всем шагам.

или
Подготовьте данные, запустите макрос
[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]

Автор - InExSu
Дата добавления - 11.08.2017 в 02:18
AndreTM Дата: Пятница, 11.08.2017, 04:03 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
InExSu, при большом количестве данных (десятки-сотни тысяч строк в обоих списках) делать макросы с вложенными циклами и полным перебором - это гарантированный путь к завешиванию программы (даже если в памяти работать). Надо обязательно использовать словари (даже с учетом затрат на первичное создание).
Ну и PowerQuery - он такой, он как раз и предназначен для обработки больших объемов :)


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеInExSu, при большом количестве данных (десятки-сотни тысяч строк в обоих списках) делать макросы с вложенными циклами и полным перебором - это гарантированный путь к завешиванию программы (даже если в памяти работать). Надо обязательно использовать словари (даже с учетом затрат на первичное создание).
Ну и PowerQuery - он такой, он как раз и предназначен для обработки больших объемов :)

Автор - AndreTM
Дата добавления - 11.08.2017 в 04:03
InExSu Дата: Пятница, 11.08.2017, 11:40 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 183
Репутация: 22 ±
Замечаний: 60% ±

Excel 2010
макросы с вложенными циклами и полным перебором

Да, тут я "погорячился" %) .
Что ж, есть повод поучить словари ... deal
 
Ответить
Сообщение
макросы с вложенными циклами и полным перебором

Да, тут я "погорячился" %) .
Что ж, есть повод поучить словари ... deal

Автор - InExSu
Дата добавления - 11.08.2017 в 11:40
komred44rus Дата: Пятница, 11.08.2017, 12:09 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Надо обязательно использовать словари

есть повод поучить словари

Что имеется ввиду под словарями? Не понял...
Проблема до сих пор актуальна, испробовал пока все предложенные способы - не находятся нужные строки.
Сейчас все же попробовал запустить макрос - все намертво висит, жду, может развиснет)
Как посмотреть лог работы PowerQuery? там проскакивает какая-то строка, но прочитать ее невозможно. В настройках запроса особо ничего не понял, но все столбцы подсвечиваются зеленым, я так понял что сам софт до начала выполнения запроса не видит никаких ошибок. Во всяком случае мне так кажется...
 
Ответить
Сообщение
Надо обязательно использовать словари

есть повод поучить словари

Что имеется ввиду под словарями? Не понял...
Проблема до сих пор актуальна, испробовал пока все предложенные способы - не находятся нужные строки.
Сейчас все же попробовал запустить макрос - все намертво висит, жду, может развиснет)
Как посмотреть лог работы PowerQuery? там проскакивает какая-то строка, но прочитать ее невозможно. В настройках запроса особо ничего не понял, но все столбцы подсвечиваются зеленым, я так понял что сам софт до начала выполнения запроса не видит никаких ошибок. Во всяком случае мне так кажется...

Автор - komred44rus
Дата добавления - 11.08.2017 в 12:09
InExSu Дата: Пятница, 11.08.2017, 13:15 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 183
Репутация: 22 ±
Замечаний: 60% ±

Excel 2010
Словари я ещё не выучил.
Может тот же макрос с индикатором?
[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]


Сообщение отредактировал InExSu - Пятница, 11.08.2017, 13:15
 
Ответить
СообщениеСловари я ещё не выучил.
Может тот же макрос с индикатором?
[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]

Автор - InExSu
Дата добавления - 11.08.2017 в 13:15
_Boroda_ Дата: Пятница, 11.08.2017, 16:26 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11545
Репутация: 4754 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Без циклов, без словарей, только на штатных средствах Excel.
Вообще ни разу не оптимизированный отрабатывает 2 х 30 000 за секунду с кусочком
[vba]
Код
Sub Zagr()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Лист1")
    With sh1
        r11_ = .Range("A" & .Rows.Count).End(3).Row
    End With
    Set sh2 = ThisWorkbook.Sheets("Лист2")
    With sh2
        r12_ = .Range("A" & .Rows.Count).End(3).Row
    End With
    Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Clear
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    sh1.Range("A1").Resize(r11_, 6).Copy
    Range("A1").PasteSpecial (xlPasteAll)
    Range("AA" & r12_ + 1).PasteSpecial (xlPasteAll)
    sh2.Range("A1").Resize(r12_, 4).Copy
    Range("A" & r11_ + 1).PasteSpecial (xlPasteAll)
    Range("AA1").PasteSpecial (xlPasteAll)
    sh2.Range("E1").Resize(r12_, 3).Copy
    Range("G" & r11_ + 1).PasteSpecial (xlPasteAll)
    Range("AG1").PasteSpecial (xlPasteAll)
    n_ = r11_ + r12_
    With Me.Sort.SortFields
        .Clear
        .Add Key:=Range("A1:A" & n_)
        .Add Key:=Range("B1:B" & n_)
        .Add Key:=Range("C1:C" & n_)
        .Add Key:=Range("D1:D" & n_)
    End With
    With Me.Sort
        .SetRange Range("A1:I" & n_)
        .Apply
    End With
    With Me.Sort.SortFields
        .Clear
        .Add Key:=Range("AA1:AA" & n_)
        .Add Key:=Range("AB1:AB" & n_)
        .Add Key:=Range("AC1:AC" & n_)
        .Add Key:=Range("AD1:AD" & n_)
    End With
    With Me.Sort
        .SetRange Range("AA1:AI" & n_)
        .Apply
    End With
    Range("AE1:AI2" & n_).Copy
    Range("E1").PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True
    Range("AA1:AI2" & n_).Clear
    Range("A1").Resize(n_, 9).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
    Range("a1").Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]
К сообщению приложен файл: 3838018_3.xlsm(25Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеБез циклов, без словарей, только на штатных средствах Excel.
Вообще ни разу не оптимизированный отрабатывает 2 х 30 000 за секунду с кусочком
[vba]
Код
Sub Zagr()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Лист1")
    With sh1
        r11_ = .Range("A" & .Rows.Count).End(3).Row
    End With
    Set sh2 = ThisWorkbook.Sheets("Лист2")
    With sh2
        r12_ = .Range("A" & .Rows.Count).End(3).Row
    End With
    Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Clear
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    sh1.Range("A1").Resize(r11_, 6).Copy
    Range("A1").PasteSpecial (xlPasteAll)
    Range("AA" & r12_ + 1).PasteSpecial (xlPasteAll)
    sh2.Range("A1").Resize(r12_, 4).Copy
    Range("A" & r11_ + 1).PasteSpecial (xlPasteAll)
    Range("AA1").PasteSpecial (xlPasteAll)
    sh2.Range("E1").Resize(r12_, 3).Copy
    Range("G" & r11_ + 1).PasteSpecial (xlPasteAll)
    Range("AG1").PasteSpecial (xlPasteAll)
    n_ = r11_ + r12_
    With Me.Sort.SortFields
        .Clear
        .Add Key:=Range("A1:A" & n_)
        .Add Key:=Range("B1:B" & n_)
        .Add Key:=Range("C1:C" & n_)
        .Add Key:=Range("D1:D" & n_)
    End With
    With Me.Sort
        .SetRange Range("A1:I" & n_)
        .Apply
    End With
    With Me.Sort.SortFields
        .Clear
        .Add Key:=Range("AA1:AA" & n_)
        .Add Key:=Range("AB1:AB" & n_)
        .Add Key:=Range("AC1:AC" & n_)
        .Add Key:=Range("AD1:AD" & n_)
    End With
    With Me.Sort
        .SetRange Range("AA1:AI" & n_)
        .Apply
    End With
    Range("AE1:AI2" & n_).Copy
    Range("E1").PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True
    Range("AA1:AI2" & n_).Clear
    Range("A1").Resize(n_, 9).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
    Range("a1").Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 11.08.2017 в 16:26
AndreTM Дата: Пятница, 11.08.2017, 22:14 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
_Boroda_, хорошая, конечно, реализация слияния hands
Но, Александр, нам же нужен Inner Join, а не FullOuter, даже если к нему применим (Top 1).
Кстати, не пойму, почему Udik использовал в запросе JoinKind.LeftOuter вместо JoinKind.Inner? :)

Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки - то именно эта операция и скушает много дополнительного времени. Да и цикл все равно будет :)
Кроме того по "результирующей" таблице (при некоторых условиях) будет вообще невозможно понять, какие именно строки оказались "лишними". Например, незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль - мало ли, может в исходных таблицах эти ячейки тоже не были заполнены.
К сообщению приложен файл: 2-34817-1.xlsm(24Kb)


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение_Boroda_, хорошая, конечно, реализация слияния hands
Но, Александр, нам же нужен Inner Join, а не FullOuter, даже если к нему применим (Top 1).
Кстати, не пойму, почему Udik использовал в запросе JoinKind.LeftOuter вместо JoinKind.Inner? :)

Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки - то именно эта операция и скушает много дополнительного времени. Да и цикл все равно будет :)
Кроме того по "результирующей" таблице (при некоторых условиях) будет вообще невозможно понять, какие именно строки оказались "лишними". Например, незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль - мало ли, может в исходных таблицах эти ячейки тоже не были заполнены.

Автор - AndreTM
Дата добавления - 11.08.2017 в 22:14
_Boroda_ Дата: Пятница, 11.08.2017, 23:16 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11545
Репутация: 4754 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки.

Андрей, это ты о чем?
И это
(при некоторых условиях)

незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль

Я тебя не понимаю. Покажи на примере


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Так что если затем мы будем вынуждены ещё и удалять лишние "незаполнившиеся" строчки.

Андрей, это ты о чем?
И это
(при некоторых условиях)

незаполненность соответствующих ячеек данных (вне ключа) ещё не будет означать дубль

Я тебя не понимаю. Покажи на примере

Автор - _Boroda_
Дата добавления - 11.08.2017 в 23:16
AndreTM Дата: Пятница, 11.08.2017, 23:28 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Покажи на примере
Так просто если исполнить этот макрос Zagr() на исходных данных от ТС, что получим?
У меня получается никак не одна строчка на выходе...
К сообщению приложен файл: 2478712.xlsm(36Kb)


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Покажи на примере
Так просто если исполнить этот макрос Zagr() на исходных данных от ТС, что получим?
У меня получается никак не одна строчка на выходе...

Автор - AndreTM
Дата добавления - 11.08.2017 в 23:28
_Boroda_ Дата: Суббота, 12.08.2017, 00:31 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11545
Репутация: 4754 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Я так понял эту фразу
При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист
что нужно те значения, которые совпадают, совместить, а которые не совпадают - просто перенести. Потому-то я и не понял тебя.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЯ так понял эту фразу
При чем нужно чтобы другие уникальные значения с обоих листов так же переносились в третий лист
что нужно те значения, которые совпадают, совместить, а которые не совпадают - просто перенести. Потому-то я и не понял тебя.

Автор - _Boroda_
Дата добавления - 12.08.2017 в 00:31
AndreTM Дата: Суббота, 12.08.2017, 00:54 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Ну, ТС "себе в голове" как обозвал другие данные (кроме ключей) "уникальными значениями" - так и далее в том же духе продолжил.
Хотя в примере же было видно, что ему нужно именно получить только данные совпадающих строк, у него на третьем листе как раз и был пример результата - всего одна совпавшая строка.
Кстати, мне вот этим и нравится .NestedJoin в PQ - одной операцией получаем и соединение необходимых таблиц по набору ключей, и все остальные "неключевые" поля таблиц, которые потом можем развернуть до нужного нам набора тех полей, что должны быть в результате.


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеНу, ТС "себе в голове" как обозвал другие данные (кроме ключей) "уникальными значениями" - так и далее в том же духе продолжил.
Хотя в примере же было видно, что ему нужно именно получить только данные совпадающих строк, у него на третьем листе как раз и был пример результата - всего одна совпавшая строка.
Кстати, мне вот этим и нравится .NestedJoin в PQ - одной операцией получаем и соединение необходимых таблиц по набору ключей, и все остальные "неключевые" поля таблиц, которые потом можем развернуть до нужного нам набора тех полей, что должны быть в результате.

Автор - AndreTM
Дата добавления - 12.08.2017 в 00:54
komred44rus Дата: Понедельник, 14.08.2017, 11:01 | Сообщение № 18
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день! Вот и начался понедельник, начинаем снова работать)))
Половину сам не понял что понаписали, но -
может в исходных таблицах эти ячейки тоже не были заполнены

Все ячейки до одной обязательно заполнены.
нужно именно получить только данные совпадающих строк

Вы меня поняли совершенно верно.
 
Ответить
СообщениеДобрый день! Вот и начался понедельник, начинаем снова работать)))
Половину сам не понял что понаписали, но -
может в исходных таблицах эти ячейки тоже не были заполнены

Все ячейки до одной обязательно заполнены.
нужно именно получить только данные совпадающих строк

Вы меня поняли совершенно верно.

Автор - komred44rus
Дата добавления - 14.08.2017 в 11:01
komred44rus Дата: Понедельник, 14.08.2017, 11:24 | Сообщение № 19
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
будет вообще невозможно понять, какие именно строки оказались "лишними"

Вот этого ну вообще никак не понял....

Как еще по понятней можно объяснить даже не знаю.
Попробую такой пример. Допустим у меня есть список работников с указанием их места жительства.
И есть список людей, обедающих в местной столовой с указанием кто что ел. Не обязательно только "работников". В обоих списках обязательно есть ФИО и дата рождения - по ним и будем искать совпадения. Оба списка упорядочены по алфавиту, в "столовом" списке есть дубли ключевых данных, но доп данные уже другие. Их тоже надо выводить в итоговую таблицу.
Мне надо:
1. Найти тех работников, которые обедают в столовой.
2. Доп. данные (это место жительства и что едят) найденных работников вывести в итоговую таблицу.
Остальное меня не интересует... Повторюсь - все строки в таблице гарантированно заполнены, пустых полей нет.
 
Ответить
Сообщение
будет вообще невозможно понять, какие именно строки оказались "лишними"

Вот этого ну вообще никак не понял....

Как еще по понятней можно объяснить даже не знаю.
Попробую такой пример. Допустим у меня есть список работников с указанием их места жительства.
И есть список людей, обедающих в местной столовой с указанием кто что ел. Не обязательно только "работников". В обоих списках обязательно есть ФИО и дата рождения - по ним и будем искать совпадения. Оба списка упорядочены по алфавиту, в "столовом" списке есть дубли ключевых данных, но доп данные уже другие. Их тоже надо выводить в итоговую таблицу.
Мне надо:
1. Найти тех работников, которые обедают в столовой.
2. Доп. данные (это место жительства и что едят) найденных работников вывести в итоговую таблицу.
Остальное меня не интересует... Повторюсь - все строки в таблице гарантированно заполнены, пустых полей нет.

Автор - komred44rus
Дата добавления - 14.08.2017 в 11:24
komred44rus Дата: Понедельник, 14.08.2017, 11:56 | Сообщение № 20
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Файлики 2-34817-1 и 2478712 работают, но почему то мало совпадений нашлось.. всего 41. У меня в таблице на первом листе 25 тыс строк, на втором листе 47 тыс. Должно быть точно больше. В 2478712 что делается на листе Example? Похоже на объединенный список, но там 27 тыс строк всего получается. И не туда и не сюда
 
Ответить
СообщениеФайлики 2-34817-1 и 2478712 работают, но почему то мало совпадений нашлось.. всего 41. У меня в таблице на первом листе 25 тыс строк, на втором листе 47 тыс. Должно быть точно больше. В 2478712 что делается на листе Example? Похоже на объединенный список, но там 27 тыс строк всего получается. И не туда и не сюда

Автор - komred44rus
Дата добавления - 14.08.2017 в 11:56
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Анализ данных на двух листах и вывод их на третий (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!