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

Вход

Регистрация

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

 

= Мир MS Excel/Сравнение значений двух таблиц на 2 листах в одной книге. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнение значений двух таблиц на 2 листах в одной книге. (Макросы/Sub)
Сравнение значений двух таблиц на 2 листах в одной книге.
skrpv1 Дата: Среда, 29.01.2020, 11:35 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Возникла задача, сравнить две таблицы на соответствие числовых значений. Две таблицы отчётности, в ячейках значения примерно с 10 знаками после запятой. Если есть несоответствие в какой-то ячейке, нужно подкрасить её, либо подкрасить все ячейки, где значения соответствуют. Есть макрос на сравнение, но он возможно не точно сравнивает, т.к. бывает, что значения в одной и той же ячейке в двух таблицах идентичные, но отмечает как несоответствие. Хотелось бы узнать у знающих, в чём может быть проблема.
[vba]
Код

Private Sub Find_Matches()
    Dim CompareRange As Range, x As Range, y As Range
    Set CompareRange = Worksheets("Лист2").Range("B8:S295") 'диапазон с которым сравнивают
    
    Application.ScreenUpdating = False
    Selection.Interior.ColorIndex = xlNone
    
    For Each y In CompareRange
        If Not IsEmpty(y) Then
           For Each x In Selection
               If InStr(1, x, y, vbTextCompare) > 0 Then x.Interior.Color = vbGreen
           Next x
        End If
    Next y
    
    Application.ScreenUpdating = True
    
    MsgBox "Данные проверены"
End Sub

В данном макросе выделяется первый проверяемый диапазон, в самом макросе прописывает диапазон ячеек, с которым нужно сравнить. Данный макрос также был взять из нета, часть понимаю, но углубленно нет т.к. в vba человек новый. Всем спасибо.
[/vba]
К сообщению приложен файл: 3547485.xlsx(14.6 Kb)


Сообщение отредактировал skrpv1 - Среда, 29.01.2020, 14:39
 
Ответить
СообщениеВозникла задача, сравнить две таблицы на соответствие числовых значений. Две таблицы отчётности, в ячейках значения примерно с 10 знаками после запятой. Если есть несоответствие в какой-то ячейке, нужно подкрасить её, либо подкрасить все ячейки, где значения соответствуют. Есть макрос на сравнение, но он возможно не точно сравнивает, т.к. бывает, что значения в одной и той же ячейке в двух таблицах идентичные, но отмечает как несоответствие. Хотелось бы узнать у знающих, в чём может быть проблема.
[vba]
Код

Private Sub Find_Matches()
    Dim CompareRange As Range, x As Range, y As Range
    Set CompareRange = Worksheets("Лист2").Range("B8:S295") 'диапазон с которым сравнивают
    
    Application.ScreenUpdating = False
    Selection.Interior.ColorIndex = xlNone
    
    For Each y In CompareRange
        If Not IsEmpty(y) Then
           For Each x In Selection
               If InStr(1, x, y, vbTextCompare) > 0 Then x.Interior.Color = vbGreen
           Next x
        End If
    Next y
    
    Application.ScreenUpdating = True
    
    MsgBox "Данные проверены"
End Sub

В данном макросе выделяется первый проверяемый диапазон, в самом макросе прописывает диапазон ячеек, с которым нужно сравнить. Данный макрос также был взять из нета, часть понимаю, но углубленно нет т.к. в vba человек новый. Всем спасибо.
[/vba]

Автор - skrpv1
Дата добавления - 29.01.2020 в 11:35
t330 Дата: Четверг, 30.01.2020, 14:49 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день.
Ваш исходный макрос ( в коде ниже - Вар1 ) подсвечивает все ячейки , если находит в них строку из CompareRange ...

Например , если в ячейке Листа 1 будет цифра 46.10056546546 (это x) , а в ячейке из диапазона CompareRange будет цифра 100 (это y ) ,

то debug.Print instr(1,46.10056546546,100,vbTextCompare) выдаст 4

Если Вам действительно нужно сравнить значения в ячейках двух одинаковых таблиц , то лучше воспользоваться варинатом номер 3 из кода ниже...

[vba]
Код


Option Explicit

Private Sub Find_MisMatches()
    Dim CompareRange As Range, x As Range, y As Range
    Set CompareRange = Worksheets("Лист2").Range("B1:B12") 'диапазон с которым сравнивают
    
    Application.ScreenUpdating = False
    Worksheets("Лист1").UsedRange.Interior.ColorIndex = xlNone
    
    'Вар1. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Range("D1:D12")
    'если внутри строки из символов этой ячейки найдена хоть одно совпадение с ячейками из диапазона CampareRange
    For Each y In CompareRange
       ' Debug.Print y
        If Not IsEmpty(y) Then
        For Each x In Worksheets("Лист1").Range("D1:D12")
            If InStr(1, x, y, vbTextCompare) > 0 Then x.Interior.Color = vbGreen
        'Debug.Print x, y, InStr(1, x, y, vbTextCompare)
        Next x
        End If
    Next y
    
    Application.ScreenUpdating = True
    
    
    'Вар2. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Range("F1:F12")
    'если ячейка из этого диапазона совпадает по значению с ЛЮБОЙ из ячеек из диапазона CompareRange
    For Each y In CompareRange
        
        If Not IsEmpty(y) Then
        For Each x In Worksheets("Лист1").Range("F1:F12") ' циклом берем любую ячейку из диапазона CompareRange и сравниваем её значение с ячейкой в сравниваемом диапазоне Worksheets("Лист1").Range("F1:F12")
            If x = y Then x.Interior.Color = vbGreen
        Next x
        End If
    Next y
    
    'Вар3. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Usedrange
    'если ячейка из диапазона в Листе 1 совпадает с ячейкой с таким же адресом из диапазона CompareRange
    
    Dim i As Integer, j As Integer  ' переменные для записи номеров строк и столбов в ячейках из дипазона Comparerange
    
    Set x = Worksheets("Лист1").UsedRange ' устанавливаем диапазон в котором надо подсветить совпадающие ячейки при сравнении с Comparerange
    
    For Each y In CompareRange
        
        If Not IsEmpty(y) Then
        i = y.Row ' записываем номер строки ячейки из Comparerange
        j = y.Column 'записываем номер столбца ячейки из Comparerange
        If x(i, j) = y Then x(i, j).Interior.Color = vbGreen  'если данные в ячейке из сравниваемого диапазона на листе 1  совпадают с данными в ячейке из диапазона CampareRange с тем же номером строки и с тем же номер столбца, то подсвечиваем
        End If
    Next y
    
    
    
    
    MsgBox "Данные проверены"
End Sub

[/vba]
К сообщению приложен файл: 11111111111.xlsm(23.7 Kb)


Сообщение отредактировал t330 - Четверг, 30.01.2020, 14:58
 
Ответить
СообщениеДобрый день.
Ваш исходный макрос ( в коде ниже - Вар1 ) подсвечивает все ячейки , если находит в них строку из CompareRange ...

Например , если в ячейке Листа 1 будет цифра 46.10056546546 (это x) , а в ячейке из диапазона CompareRange будет цифра 100 (это y ) ,

то debug.Print instr(1,46.10056546546,100,vbTextCompare) выдаст 4

Если Вам действительно нужно сравнить значения в ячейках двух одинаковых таблиц , то лучше воспользоваться варинатом номер 3 из кода ниже...

[vba]
Код


Option Explicit

Private Sub Find_MisMatches()
    Dim CompareRange As Range, x As Range, y As Range
    Set CompareRange = Worksheets("Лист2").Range("B1:B12") 'диапазон с которым сравнивают
    
    Application.ScreenUpdating = False
    Worksheets("Лист1").UsedRange.Interior.ColorIndex = xlNone
    
    'Вар1. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Range("D1:D12")
    'если внутри строки из символов этой ячейки найдена хоть одно совпадение с ячейками из диапазона CampareRange
    For Each y In CompareRange
       ' Debug.Print y
        If Not IsEmpty(y) Then
        For Each x In Worksheets("Лист1").Range("D1:D12")
            If InStr(1, x, y, vbTextCompare) > 0 Then x.Interior.Color = vbGreen
        'Debug.Print x, y, InStr(1, x, y, vbTextCompare)
        Next x
        End If
    Next y
    
    Application.ScreenUpdating = True
    
    
    'Вар2. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Range("F1:F12")
    'если ячейка из этого диапазона совпадает по значению с ЛЮБОЙ из ячеек из диапазона CompareRange
    For Each y In CompareRange
        
        If Not IsEmpty(y) Then
        For Each x In Worksheets("Лист1").Range("F1:F12") ' циклом берем любую ячейку из диапазона CompareRange и сравниваем её значение с ячейкой в сравниваемом диапазоне Worksheets("Лист1").Range("F1:F12")
            If x = y Then x.Interior.Color = vbGreen
        Next x
        End If
    Next y
    
    'Вар3. Подсвечивает ячейки в диапазоне Worksheets("Лист1").Usedrange
    'если ячейка из диапазона в Листе 1 совпадает с ячейкой с таким же адресом из диапазона CompareRange
    
    Dim i As Integer, j As Integer  ' переменные для записи номеров строк и столбов в ячейках из дипазона Comparerange
    
    Set x = Worksheets("Лист1").UsedRange ' устанавливаем диапазон в котором надо подсветить совпадающие ячейки при сравнении с Comparerange
    
    For Each y In CompareRange
        
        If Not IsEmpty(y) Then
        i = y.Row ' записываем номер строки ячейки из Comparerange
        j = y.Column 'записываем номер столбца ячейки из Comparerange
        If x(i, j) = y Then x(i, j).Interior.Color = vbGreen  'если данные в ячейке из сравниваемого диапазона на листе 1  совпадают с данными в ячейке из диапазона CampareRange с тем же номером строки и с тем же номер столбца, то подсвечиваем
        End If
    Next y
    
    
    
    
    MsgBox "Данные проверены"
End Sub

[/vba]

Автор - t330
Дата добавления - 30.01.2020 в 14:49
skrpv1 Дата: Четверг, 30.01.2020, 15:51 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Огромное вам спасибо. Да, мне подходит 3 вариант вашего кода. Но такой вопрос. Таблица оригинальная, к которой я это применяю имеет диапазон ячеек для проверки B7:R291, как мне сделать проверку каждой ячейки листа 1 с каждой ячейкой листа 2 такого же диапазона? В первоначальном случае это делалось циклом "for", если я правильно понимаю. И получается в вашем 3-ем варианте, диапазон где подсвечиваются ячейки (х), он выделяется сам, т.е. целиком лист 1? Или мне самому нужно прописывать необходимые значения? Извините за глупые вопросы, повторюсь, нахожусь в стадии изучения.


Сообщение отредактировал китин - Четверг, 30.01.2020, 15:57
 
Ответить
СообщениеИзлишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Огромное вам спасибо. Да, мне подходит 3 вариант вашего кода. Но такой вопрос. Таблица оригинальная, к которой я это применяю имеет диапазон ячеек для проверки B7:R291, как мне сделать проверку каждой ячейки листа 1 с каждой ячейкой листа 2 такого же диапазона? В первоначальном случае это делалось циклом "for", если я правильно понимаю. И получается в вашем 3-ем варианте, диапазон где подсвечиваются ячейки (х), он выделяется сам, т.е. целиком лист 1? Или мне самому нужно прописывать необходимые значения? Извините за глупые вопросы, повторюсь, нахожусь в стадии изучения.

Автор - skrpv1
Дата добавления - 30.01.2020 в 15:51
t330 Дата: Четверг, 30.01.2020, 20:31 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Обратите внимание , что в вашем исходном файле форматы некоторых чисел в листах не совпадают, хотя сами числа равны друг другу...

Например в ячейке B7 и в первом и во втором листе стоит цифра 11,6633333541895
но функция =ВПР(Лист1!B7;Лист2!B:B;1;0) показывает Н/Д (см в ячейке Лист1!B15)

Поэтому, чтобы сравнивать цифры в этих таблицах их приходится сначала преобразовывать в строки и потом сравнивать (строка 10 в коде ниже)...

Запускайте макрос и в диалоговом окне укажите диапазон ячеек из той таблицы с которой нужно сравнить таблицу в листе 1 в ячейках с одинаковым адресом.

[vba]
Код


Option Explicit

Private Sub Find_MisMatches()
    Dim CompareRange As Range, x As Range, y As Range, InitialSheet As Worksheet
    Dim i As Long, j As Long  ' переменные для записи номеров строк и столбов в ячейках из дипазона Comparerange
     
    Set InitialSheet = Worksheets("Лист1") ' устанавливаем диапазон в котором надо подсветить совпадающие ячейки из листа 1 при сравнении с Comparerange. В данном случае выбирается диапазон со всеми когда-либо заполненными ячейками в Листе1
    Set CompareRange = Application.InputBox("Укажите диапазон ячеек для сравнения", "Запрос данных", "B2:S10", Type:=8) 'диапазон в листе 2 с которым сравнивают. В данном случае это все когда-либо заполненные ячейки в листе2

    
    Application.ScreenUpdating = False
    InitialSheet.UsedRange.Interior.ColorIndex = xlNone  ' очищаем заливку в  диапазоне где будем заливать совпадающие ячейки
    
    On Error Resume Next
    
    
    'Вар3. Подсвечивает ячейки в сравниваемом диапазоне , но только в тех ячейках ,
    'у которых тот же самый адрес , что и выбранном диаппазоне CompareRange
    
    
    'Если выбрано менее двух ячеек
     If CompareRange.Count = 1 Then
        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation
        Exit Sub
     End If
     
     'если указаны только пустые ячейки вне рабочего диапазона
    If CompareRange Is Nothing Then
        MsgBox "Недостаточно данных для выбора значений", vbInformation
        Exit Sub
    End If
        
       
'Запускаем цикл по каждой ячейке из Comparerange
    For Each y In CompareRange
               
        i = y.Row ' записываем номер строки ячейки из Comparerange
        j = y.Column 'записываем номер столбца ячейки из Comparerange
       
10      If CStr(InitialSheet.Cells(i, j).Value) = CStr(CompareRange.Parent.Cells(i, j).Value) Then   ' здесь преобразовываем данные в ячейках в текстовый формат и сравниваем
        InitialSheet.Cells(i, j).Interior.Color = vbGreen 'если данные в ячейке из сравниваемого диапазона на листе 1  совпадают с данными в ячейке из диапазона CompareRange с тем же номером строки и с тем же номер столбца, то подсвечиваем зеленым
      End If
        
    Next y
    MsgBox "Данные проверены"
End Sub

[/vba]
К сообщению приложен файл: 3834271.xlsm(27.4 Kb)
 
Ответить
СообщениеОбратите внимание , что в вашем исходном файле форматы некоторых чисел в листах не совпадают, хотя сами числа равны друг другу...

Например в ячейке B7 и в первом и во втором листе стоит цифра 11,6633333541895
но функция =ВПР(Лист1!B7;Лист2!B:B;1;0) показывает Н/Д (см в ячейке Лист1!B15)

Поэтому, чтобы сравнивать цифры в этих таблицах их приходится сначала преобразовывать в строки и потом сравнивать (строка 10 в коде ниже)...

Запускайте макрос и в диалоговом окне укажите диапазон ячеек из той таблицы с которой нужно сравнить таблицу в листе 1 в ячейках с одинаковым адресом.

[vba]
Код


Option Explicit

Private Sub Find_MisMatches()
    Dim CompareRange As Range, x As Range, y As Range, InitialSheet As Worksheet
    Dim i As Long, j As Long  ' переменные для записи номеров строк и столбов в ячейках из дипазона Comparerange
     
    Set InitialSheet = Worksheets("Лист1") ' устанавливаем диапазон в котором надо подсветить совпадающие ячейки из листа 1 при сравнении с Comparerange. В данном случае выбирается диапазон со всеми когда-либо заполненными ячейками в Листе1
    Set CompareRange = Application.InputBox("Укажите диапазон ячеек для сравнения", "Запрос данных", "B2:S10", Type:=8) 'диапазон в листе 2 с которым сравнивают. В данном случае это все когда-либо заполненные ячейки в листе2

    
    Application.ScreenUpdating = False
    InitialSheet.UsedRange.Interior.ColorIndex = xlNone  ' очищаем заливку в  диапазоне где будем заливать совпадающие ячейки
    
    On Error Resume Next
    
    
    'Вар3. Подсвечивает ячейки в сравниваемом диапазоне , но только в тех ячейках ,
    'у которых тот же самый адрес , что и выбранном диаппазоне CompareRange
    
    
    'Если выбрано менее двух ячеек
     If CompareRange.Count = 1 Then
        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation
        Exit Sub
     End If
     
     'если указаны только пустые ячейки вне рабочего диапазона
    If CompareRange Is Nothing Then
        MsgBox "Недостаточно данных для выбора значений", vbInformation
        Exit Sub
    End If
        
       
'Запускаем цикл по каждой ячейке из Comparerange
    For Each y In CompareRange
               
        i = y.Row ' записываем номер строки ячейки из Comparerange
        j = y.Column 'записываем номер столбца ячейки из Comparerange
       
10      If CStr(InitialSheet.Cells(i, j).Value) = CStr(CompareRange.Parent.Cells(i, j).Value) Then   ' здесь преобразовываем данные в ячейках в текстовый формат и сравниваем
        InitialSheet.Cells(i, j).Interior.Color = vbGreen 'если данные в ячейке из сравниваемого диапазона на листе 1  совпадают с данными в ячейке из диапазона CompareRange с тем же номером строки и с тем же номер столбца, то подсвечиваем зеленым
      End If
        
    Next y
    MsgBox "Данные проверены"
End Sub

[/vba]

Автор - t330
Дата добавления - 30.01.2020 в 20:31
skrpv1 Дата: Пятница, 31.01.2020, 15:09 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Обратите внимание , что в вашем исходном файле форматы некоторых чисел в листах не совпадают, хотя сами числа равны друг другу...

Например в ячейке B7 и в первом и во втором листе стоит цифра 11,6633333541895
но функция =ВПР(Лист1!B7;Лист2!B:B;1;0) показывает Н/Д (см в ячейке Лист1!B15)

Поэтому, чтобы сравнивать цифры в этих таблицах их приходится сначала преобразовывать в строки и потом сравнивать (строка 10 в коде ниже)...

Запускайте макрос и в диалоговом окне укажите диапазон ячеек из той таблицы с которой нужно сравнить таблицу в листе 1 в ячейках с одинаковым адресом.


Спасибо вам огромное за вашу помощь. Все предельно понятно. Удачи вам и успехов!
 
Ответить
Сообщение
Обратите внимание , что в вашем исходном файле форматы некоторых чисел в листах не совпадают, хотя сами числа равны друг другу...

Например в ячейке B7 и в первом и во втором листе стоит цифра 11,6633333541895
но функция =ВПР(Лист1!B7;Лист2!B:B;1;0) показывает Н/Д (см в ячейке Лист1!B15)

Поэтому, чтобы сравнивать цифры в этих таблицах их приходится сначала преобразовывать в строки и потом сравнивать (строка 10 в коде ниже)...

Запускайте макрос и в диалоговом окне укажите диапазон ячеек из той таблицы с которой нужно сравнить таблицу в листе 1 в ячейках с одинаковым адресом.


Спасибо вам огромное за вашу помощь. Все предельно понятно. Удачи вам и успехов!

Автор - skrpv1
Дата добавления - 31.01.2020 в 15:09
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнение значений двух таблиц на 2 листах в одной книге. (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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