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

Вход

Регистрация

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

 

= Мир MS Excel/Сравнение совпадающих значений в двух таблицах по строкам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнение совпадающих значений в двух таблицах по строкам (Макросы/Sub)
Сравнение совпадающих значений в двух таблицах по строкам
MET_GALA Дата: Воскресенье, 24.05.2020, 21:54 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Добрый день!
Столкнулся с проблемой, которую не могу решить стандартными способами, которые имеются по схожим проблемам в интернете.

Суть:

Имеются две таблицы на одном листе в файле Excel: Табл1 и Табл2.
В Табл1 присутвуют уникальные идентификационные номера, справа от которых в строках указаны отрезки времени по примеру: "00:15"/"14:30"/"22:40" и т.д.
В Табл2 находятся схожие данные (определенные отрезки времени)
Необходимо сравнить значения из Табл1 со значенями в Табл2 по строкам и выделить значения в Табл1, которые совпадают со значениями в Табл2 красным цветом (Interior.Color = 16777215)
Проблема в том, что значения в двух таблицах необходимо сравнивать по строкам.
Т.е. сначала сравниваются значения в ячейках F2:Q2 (Первая таблица) со значениями в строках T2:AE2 (Вторая таблица), далее сравниваются значения в ячейках F3:Q3 (Первая таблица) со значениями в строках T3:AE3 (Вторая таблица), далее сравниваются значения в ячейках F4:Q4 (Первая таблица) со значениями в строках T4:AE4 (Вторая таблица) и так до 500 строки.

Решения с присвоением имен таблицам и последующим выделением совпадающих значений в таблицах через условное форматирование с помощью формул =СЧЕТЕСЛИ(Табл2;F2)=1 не подходят, так как сравнивать значения необходимо по строкам, а при создании таблиц сравниваются все значения из одной таблицы со значениями в первой, что не подходит для решения.
Готовых решений по схожим вопросам в интернете я не нашел.
Файл-пример прилагаю.
Заранее благодарю за помощь :(
К сообщению приложен файл: T5.xlsx(18.7 Kb)
 
Ответить
СообщениеДобрый день!
Столкнулся с проблемой, которую не могу решить стандартными способами, которые имеются по схожим проблемам в интернете.

Суть:

Имеются две таблицы на одном листе в файле Excel: Табл1 и Табл2.
В Табл1 присутвуют уникальные идентификационные номера, справа от которых в строках указаны отрезки времени по примеру: "00:15"/"14:30"/"22:40" и т.д.
В Табл2 находятся схожие данные (определенные отрезки времени)
Необходимо сравнить значения из Табл1 со значенями в Табл2 по строкам и выделить значения в Табл1, которые совпадают со значениями в Табл2 красным цветом (Interior.Color = 16777215)
Проблема в том, что значения в двух таблицах необходимо сравнивать по строкам.
Т.е. сначала сравниваются значения в ячейках F2:Q2 (Первая таблица) со значениями в строках T2:AE2 (Вторая таблица), далее сравниваются значения в ячейках F3:Q3 (Первая таблица) со значениями в строках T3:AE3 (Вторая таблица), далее сравниваются значения в ячейках F4:Q4 (Первая таблица) со значениями в строках T4:AE4 (Вторая таблица) и так до 500 строки.

Решения с присвоением имен таблицам и последующим выделением совпадающих значений в таблицах через условное форматирование с помощью формул =СЧЕТЕСЛИ(Табл2;F2)=1 не подходят, так как сравнивать значения необходимо по строкам, а при создании таблиц сравниваются все значения из одной таблицы со значениями в первой, что не подходит для решения.
Готовых решений по схожим вопросам в интернете я не нашел.
Файл-пример прилагаю.
Заранее благодарю за помощь :(

Автор - MET_GALA
Дата добавления - 24.05.2020 в 21:54
Pelena Дата: Воскресенье, 24.05.2020, 22:25 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Здравствуйте.
А если немного другую формулу в УФ?
Код
=ИЛИ(F2=$T2:$AE2)*(F2<>"")
К сообщению приложен файл: 2671841.xlsx(19.1 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
А если немного другую формулу в УФ?
Код
=ИЛИ(F2=$T2:$AE2)*(F2<>"")

Автор - Pelena
Дата добавления - 24.05.2020 в 22:25
MET_GALA Дата: Воскресенье, 24.05.2020, 22:31 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Спасибо огромное!
Вы мне очень помогли :)
 
Ответить
СообщениеPelena, Спасибо огромное!
Вы мне очень помогли :)

Автор - MET_GALA
Дата добавления - 24.05.2020 в 22:31
MET_GALA Дата: Воскресенье, 24.05.2020, 23:23 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, заранее извиняюсь, так как не знаю, нужно ли создавать еще одну тему для этого, поэтому спрошу здесь.
Попытался записать настройки условного форматирования макрорекодером и столкнулся с тем, что значения ячеек помечаются красным в хаотичном формате.
Сам код, который записывается автоматически:

[vba]
Код
Sub Макрос8()

    Range("F2:Q500").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ИЛИ(F2=$T2:$AE2)*(F2<>"""")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

[/vba]

В чем ошибка понимаю, но у меня слишком скудные знания в VBA, чтобы понять, как это исправить: :(

Код
=ИЛИ(F2=$T2:$AE2)*(F2<>"""")

После операторов сравнения <> VBA автоматически ставит несколько знаков "", из-за чего условное форматирование становится хаотическим, а при удалении лишних символов "" код выдает ошибку Invalid procedure call or argument.
Не подскажете, как это можно поправить?


Сообщение отредактировал MET_GALA - Воскресенье, 24.05.2020, 23:36
 
Ответить
СообщениеPelena, заранее извиняюсь, так как не знаю, нужно ли создавать еще одну тему для этого, поэтому спрошу здесь.
Попытался записать настройки условного форматирования макрорекодером и столкнулся с тем, что значения ячеек помечаются красным в хаотичном формате.
Сам код, который записывается автоматически:

[vba]
Код
Sub Макрос8()

    Range("F2:Q500").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ИЛИ(F2=$T2:$AE2)*(F2<>"""")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

[/vba]

В чем ошибка понимаю, но у меня слишком скудные знания в VBA, чтобы понять, как это исправить: :(

Код
=ИЛИ(F2=$T2:$AE2)*(F2<>"""")

После операторов сравнения <> VBA автоматически ставит несколько знаков "", из-за чего условное форматирование становится хаотическим, а при удалении лишних символов "" код выдает ошибку Invalid procedure call or argument.
Не подскажете, как это можно поправить?

Автор - MET_GALA
Дата добавления - 24.05.2020 в 23:23
Pelena Дата: Понедельник, 25.05.2020, 10:22 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Ошибки здесь нет, кавычки внутри текстовой строки всегда задваиваются. Можно немного короче
[vba]
Код
Sub Макрос8()

    With Range(Range("F2:Q2"), Range("F2:Q2").End(xlDown))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ИЛИ(F2=$T2:$AE2)*(F2<>"""")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub
[/vba]
НО! У меня творятся странные вещи с этим кодом. Если я его запускаю, правило УФ формируется правильно, но не отрабатывает, не все ячейки закрашиваются. Причём, если зайти в Управление правилами, нажать Изменить правило (ничего не изменять) -- ОК -- Применить, то всё закрашивается %)
Не могу уловить, в чём дело :'(


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеОшибки здесь нет, кавычки внутри текстовой строки всегда задваиваются. Можно немного короче
[vba]
Код
Sub Макрос8()

    With Range(Range("F2:Q2"), Range("F2:Q2").End(xlDown))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ИЛИ(F2=$T2:$AE2)*(F2<>"""")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub
[/vba]
НО! У меня творятся странные вещи с этим кодом. Если я его запускаю, правило УФ формируется правильно, но не отрабатывает, не все ячейки закрашиваются. Причём, если зайти в Управление правилами, нажать Изменить правило (ничего не изменять) -- ОК -- Применить, то всё закрашивается %)
Не могу уловить, в чём дело :'(

Автор - Pelena
Дата добавления - 25.05.2020 в 10:22
Pelena Дата: Понедельник, 25.05.2020, 14:38 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Может, лучше без Условного форматирования, раз уж всё равно макрос
К сообщению приложен файл: T5_1.xlsm(25.8 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМожет, лучше без Условного форматирования, раз уж всё равно макрос

Автор - Pelena
Дата добавления - 25.05.2020 в 14:38
MET_GALA Дата: Понедельник, 25.05.2020, 15:20 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Этот вариант идеально подходит.
Еще раз огромное спасибо!!!
Вы лучше всех! :)
 
Ответить
СообщениеPelena, Этот вариант идеально подходит.
Еще раз огромное спасибо!!!
Вы лучше всех! :)

Автор - MET_GALA
Дата добавления - 25.05.2020 в 15:20
MET_GALA Дата: Четверг, 28.05.2020, 20:33 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, Еще раз вас побеспокою, извините пожалуйста :(
При изменении условий в отчете, в котором я использовал ваш макрос выгрузились новые данные, но теперь макрос напрочь отказывается работать ругаясь на следующую строчку:

[vba]
Код
If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) And Cells(i, k) <> "" Then fl = True: Exit For
[/vba]

Из цельного кода:

[vba]
Код
Public Sub Proverka()

    Dim lrow&, i&, j&, k&, fl As Boolean
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 2 To lrow
        For j = 6 To 17
            fl = False
            If Cells(i, j) <> "" Then
                For k = 20 To 31
                    If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) And Cells(i, k) <> "" Then fl = True: Exit For
                Next k
            End If
            If fl Then Cells(i, j).Interior.Color = 255 Else Cells(i, j).Font.ColorIndex = xlAutomatic
        Next j
    Next i
End Sub
[/vba]

Расположение данных точно такое же, как и в предыдущих файлах, изменились только значения в правой таблице.
Можете подсказать, что нужно подправить для того, что макрос снова заработал? %)
Обещаю подучить VBA и больше вас не трогать... girl_sad
К сообщению приложен файл: 7465767.xlsm(91.7 Kb)
 
Ответить
СообщениеPelena, Еще раз вас побеспокою, извините пожалуйста :(
При изменении условий в отчете, в котором я использовал ваш макрос выгрузились новые данные, но теперь макрос напрочь отказывается работать ругаясь на следующую строчку:

[vba]
Код
If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) And Cells(i, k) <> "" Then fl = True: Exit For
[/vba]

Из цельного кода:

[vba]
Код
Public Sub Proverka()

    Dim lrow&, i&, j&, k&, fl As Boolean
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 2 To lrow
        For j = 6 To 17
            fl = False
            If Cells(i, j) <> "" Then
                For k = 20 To 31
                    If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) And Cells(i, k) <> "" Then fl = True: Exit For
                Next k
            End If
            If fl Then Cells(i, j).Interior.Color = 255 Else Cells(i, j).Font.ColorIndex = xlAutomatic
        Next j
    Next i
End Sub
[/vba]

Расположение данных точно такое же, как и в предыдущих файлах, изменились только значения в правой таблице.
Можете подсказать, что нужно подправить для того, что макрос снова заработал? %)
Обещаю подучить VBA и больше вас не трогать... girl_sad

Автор - MET_GALA
Дата добавления - 28.05.2020 в 20:33
Pelena Дата: Четверг, 28.05.2020, 20:55 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Проверьте так
[vba]
Код
Public Sub Proverka()
    Dim lrow&, i&, j&, k&, fl As Boolean
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    For i = 2 To lrow
        For j = 6 To 17
            fl = False
            If Cells(i, j) <> "" Then
                For k = 20 To 31
                    If Cells(i, k) <> "" Then
                        If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) Then fl = True: Exit For
                    End If
                Next k
            End If
            If fl Then Cells(i, j).Interior.Color = 255 Else Cells(i, j).Interior.Color = xlNone
        Next j
    Next i
End Sub
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПроверьте так
[vba]
Код
Public Sub Proverka()
    Dim lrow&, i&, j&, k&, fl As Boolean
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    For i = 2 To lrow
        For j = 6 To 17
            fl = False
            If Cells(i, j) <> "" Then
                For k = 20 To 31
                    If Cells(i, k) <> "" Then
                        If Round(Cells(i, j), 6) = Round(Cells(i, k), 6) Then fl = True: Exit For
                    End If
                Next k
            End If
            If fl Then Cells(i, j).Interior.Color = 255 Else Cells(i, j).Interior.Color = xlNone
        Next j
    Next i
End Sub
[/vba]

Автор - Pelena
Дата добавления - 28.05.2020 в 20:55
MET_GALA Дата: Четверг, 28.05.2020, 21:00 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, теперь работает!
Еще раз большущее вам спасибо!
Вы мне очень помогли pray
 
Ответить
СообщениеPelena, теперь работает!
Еще раз большущее вам спасибо!
Вы мне очень помогли pray

Автор - MET_GALA
Дата добавления - 28.05.2020 в 21:00
boa Дата: Пятница, 29.05.2020, 00:13 | Сообщение № 11
Группа: Друзья
Ранг: Ветеран
Сообщений: 542
Репутация: 165 ±
Замечаний: 0% ±

2013, 365
Обещаю подучить VBA и больше вас не трогать...

три Ха-Ха, именно знание порождает вопросы...
Ждите Елена ;) вопросы будут :)


 
Ответить
Сообщение
Обещаю подучить VBA и больше вас не трогать...

три Ха-Ха, именно знание порождает вопросы...
Ждите Елена ;) вопросы будут :)

Автор - boa
Дата добавления - 29.05.2020 в 00:13
boa Дата: Пятница, 29.05.2020, 00:23 | Сообщение № 12
Группа: Друзья
Ранг: Ветеран
Сообщений: 542
Репутация: 165 ±
Замечаний: 0% ±

2013, 365
А проблема с УФ связана с представлением формулы в локальных настройках и настройках отображения(A1/RC1, OR/ИЛИ и т.п.)
я до конца еще не разобрался, но действительно, проблемка есть.
Можно вынести в отдельную тему, но суть преобразования я когда-то уже начал.

Осталась проблема с обработкой данных в А1, и некогда додумать...
Но можно, просто, передавать данные функции в формате RC и не париться...




Сообщение отредактировал boa - Пятница, 29.05.2020, 01:16
 
Ответить
СообщениеА проблема с УФ связана с представлением формулы в локальных настройках и настройках отображения(A1/RC1, OR/ИЛИ и т.п.)
я до конца еще не разобрался, но действительно, проблемка есть.
Можно вынести в отдельную тему, но суть преобразования я когда-то уже начал.

Осталась проблема с обработкой данных в А1, и некогда додумать...
Но можно, просто, передавать данные функции в формате RC и не париться...

Автор - boa
Дата добавления - 29.05.2020 в 00:23
djony Дата: Пятница, 05.06.2020, 15:02 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, есть данные. имя фамилия отчество нужно со второго листа найти в 1 м листе и есть совпадения сравнить баллы, если есть различия выдать ответ в виде имя фамилия отчество и данные о балах из 1 го листа и 2 го листа. ответ давать в 3 листе
К сообщению приложен файл: 2466911.xlsx(10.3 Kb)


Сообщение отредактировал djony - Пятница, 05.06.2020, 15:07
 
Ответить
СообщениеPelena, есть данные. имя фамилия отчество нужно со второго листа найти в 1 м листе и есть совпадения сравнить баллы, если есть различия выдать ответ в виде имя фамилия отчество и данные о балах из 1 го листа и 2 го листа. ответ давать в 3 листе

Автор - djony
Дата добавления - 05.06.2020 в 15:02
Pelena Дата: Пятница, 05.06.2020, 15:06 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
djony, Прочитайте Правила форума и создайте свою тему. Эта тема закрыта


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеdjony, Прочитайте Правила форума и создайте свою тему. Эта тема закрыта

Автор - Pelena
Дата добавления - 05.06.2020 в 15:06
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнение совпадающих значений в двух таблицах по строкам (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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