Добрый день! Столкнулся с проблемой, которую не могу решить стандартными способами, которые имеются по схожим проблемам в интернете.
Суть:
Имеются две таблицы на одном листе в файле 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 не подходят, так как сравнивать значения необходимо по строкам, а при создании таблиц сравниваются все значения из одной таблицы со значениями в первой, что не подходит для решения. Готовых решений по схожим вопросам в интернете я не нашел. Файл-пример прилагаю. Заранее благодарю за помощь
Добрый день! Столкнулся с проблемой, которую не могу решить стандартными способами, которые имеются по схожим проблемам в интернете.
Суть:
Имеются две таблицы на одном листе в файле 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
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. Не подскажете, как это можно поправить?
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
Сообщение отредактировал MET_GALA - Воскресенье, 24.05.2020, 23:36
Ошибки здесь нет, кавычки внутри текстовой строки всегда задваиваются. Можно немного короче [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] НО! У меня творятся странные вещи с этим кодом. Если я его запускаю, правило УФ формируется правильно, но не отрабатывает, не все ячейки закрашиваются. Причём, если зайти в Управление правилами, нажать Изменить правило (ничего не изменять) -- ОК -- Применить, то всё закрашивается Не могу уловить, в чём дело :'(
Ошибки здесь нет, кавычки внутри текстовой строки всегда задваиваются. Можно немного короче [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
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
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 и больше вас не трогать...
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 и больше вас не трогать... MET_GALA
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]
Проверьте так [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
А проблема с УФ связана с представлением формулы в локальных настройках и настройках отображения(A1/RC1, OR/ИЛИ и т.п.) я до конца еще не разобрался, но действительно, проблемка есть. Можно вынести в отдельную тему, но суть преобразования я когда-то уже начал.
[vba]
Код
Function Formula_As_Local_Setting$(ByVal sFormula$, Optional RefStyle_xlR1C1_Or_xlA1% = xlR1C1, Optional xlA1_Range As Range) '' Author: boa '' Written: 25.10.2019 '' Edited: ' Description: преобразует встроенные функции Excel к виду согласно региональных настроек ' Description: converts Excel built-in functions to a view according to regional settings With ThisWorkbook.Sheets(1) Select Case RefStyle_xlR1C1_Or_xlA1 'MCl.XLApp.ReferenceStyle Case xlR1C1: .Names.Add Name:="TempName", RefersToR1C1:=sFormula ' Если селектионДапазон не начинается с А1, то формула, в формате А1, имеет смещение на отстояние диапазона от А1 ' Case xlA1: sFormula = Application.Range.Offset(xlA1_Range.Row, xlA1_Range.Column).RefersToLocal Case xlA1: .Names.Add Name:="TempName", RefersTo:=sFormula End Select
With .Names("TempName") Select Case Application.ReferenceStyle Case xlR1C1: sFormula = .RefersToR1C1Local Case xlA1: sFormula = .RefersToLocal End Select .Delete End With If VBA.InStr(1, sFormula, "!") Then sFormula = VBA.Replace(sFormula, .Name & "!", "") End With
Formula_As_Local_Setting = sFormula End Function
[/vba]
Осталась проблема с обработкой данных в А1, и некогда додумать... Но можно, просто, передавать данные функции в формате RC и не париться...
А проблема с УФ связана с представлением формулы в локальных настройках и настройках отображения(A1/RC1, OR/ИЛИ и т.п.) я до конца еще не разобрался, но действительно, проблемка есть. Можно вынести в отдельную тему, но суть преобразования я когда-то уже начал.
[vba]
Код
Function Formula_As_Local_Setting$(ByVal sFormula$, Optional RefStyle_xlR1C1_Or_xlA1% = xlR1C1, Optional xlA1_Range As Range) '' Author: boa '' Written: 25.10.2019 '' Edited: ' Description: преобразует встроенные функции Excel к виду согласно региональных настроек ' Description: converts Excel built-in functions to a view according to regional settings With ThisWorkbook.Sheets(1) Select Case RefStyle_xlR1C1_Or_xlA1 'MCl.XLApp.ReferenceStyle Case xlR1C1: .Names.Add Name:="TempName", RefersToR1C1:=sFormula ' Если селектионДапазон не начинается с А1, то формула, в формате А1, имеет смещение на отстояние диапазона от А1 ' Case xlA1: sFormula = Application.Range.Offset(xlA1_Range.Row, xlA1_Range.Column).RefersToLocal Case xlA1: .Names.Add Name:="TempName", RefersTo:=sFormula End Select
With .Names("TempName") Select Case Application.ReferenceStyle Case xlR1C1: sFormula = .RefersToR1C1Local Case xlA1: sFormula = .RefersToLocal End Select .Delete End With If VBA.InStr(1, sFormula, "!") Then sFormula = VBA.Replace(sFormula, .Name & "!", "") End With
Formula_As_Local_Setting = sFormula End Function
[/vba]
Осталась проблема с обработкой данных в А1, и некогда додумать... Но можно, просто, передавать данные функции в формате RC и не париться...boa
Сообщение отредактировал boa - Пятница, 29.05.2020, 01:16
Pelena, есть данные. имя фамилия отчество нужно со второго листа найти в 1 м листе и есть совпадения сравнить баллы, если есть различия выдать ответ в виде имя фамилия отчество и данные о балах из 1 го листа и 2 го листа. ответ давать в 3 листе
Pelena, есть данные. имя фамилия отчество нужно со второго листа найти в 1 м листе и есть совпадения сравнить баллы, если есть различия выдать ответ в виде имя фамилия отчество и данные о балах из 1 го листа и 2 го листа. ответ давать в 3 листеdjony
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
вопросы будут 
