У Рика есть два рабочих листа, которые ему нужно сравнить друг с другом, чтобы выделить различия. При сравнении нужно сравнивать не то, что отображается, а формулы в каждой из ячеек. Таким образом Рик надеется выяснить, где формулы различаются на каждом листе.

Есть несколько способов сравнения формул. В некоторых версиях Excel у вас есть доступ к надстройке сравнения, которая может справиться с этой задачей за вас. Если вы используете Office профессиональный плюс 2013 или Office 365 профессиональный плюс, вы можете использовать надстройку сравнения электронных таблиц.

Информацию об этой надстройке можно найти здесь:

https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986

Если вы используете Office профессиональный плюс 2013 или Office 365, вы также можете использовать надстройку Spreadsheet Inquire. Информация об этой надстройке находится здесь:

https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

Осознавая, что не все используют одну из этих версий Excel или могут не захотеть использовать надстройку, вы можете сделать и другие вещи. В Excel 2013 и более поздних версиях есть удобная функция рабочего листа FORMULATEXT. Вы можете использовать эту функцию для получения формулы, хранящейся в ячейке, следующим образом:

=FORMULATEXT(A7)

Это возвращает формулу, содержащуюся в ячейке A7 (в данном случае). Если в ячейке нет формулы, возвращается ошибка # Н / Д. Вы можете использовать это поведение, чтобы создать на «листе сравнения» индикатор того, равны ли формулы или нет. Просто создайте новый лист и поместите его в ячейку A1:

=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")

Скопируйте формулу как можно дальше вниз и вправо. Он отмечает различия между соответствующими ячейками на листах Sheet1 и Sheet2.

Помните, что FORMULATEXT был представлен в Excel 2013, поэтому этот подход не будет работать в старых версиях Excel. Если вы используете другую версию (или даже Excel 2013), вы можете использовать макрос, чтобы отметить различия между листами. Есть много подходов к макросам, которые вы можете использовать; Ниже приводится краткий способ сравнения.

Sub ComparaFormulas1()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range

Set Check = ActiveSheet     Set Master = Worksheets("Master")



For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 c.Interior.Color = RGB(255, 0, 0)

End If         End If     Next c End Sub

Чтобы использовать этот макрос, откройте книгу, которую вы хотите сравнить. Предполагается, что вы хотите сравнить с теми же ячейками на листе под названием «Мастер».

(Очевидно, вы можете изменить это в макросе, если ваш «стандартный» рабочий лист имеет другое имя.) Каждая ячейка на текущем листе сравнивается с соответствующей ячейкой на «главном» листе. Если ячейки содержат формулы, а эти формулы различны, цвет фона ячейки меняется на красный на текущем листе.

Такой подход, очевидно, меняет форматирование сравниваемого листа. Если вы предпочитаете не изменять форматирование, а просто хотите получить список ячеек с различиями, вы можете использовать следующий вариант макроса:

Sub ComparaFormulas2()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range     Dim sTemp As String     Dim lDif As Long

Set Check = ActiveSheet     Set Master = Worksheets("Master")

sTemp = ""

lDif = 0

For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 lDif = lDif + 1                 sTemp = sTemp & vbCrLf & lDif & ": " & c.Address             End If         End If     Next c     If lDif > 0 Then         sTemp = "These were the differences" & vbCrLf & sTemp     Else         sTemp = "There were no differences"

End If     MsgBox sTemp End Sub

Вы также можете создать определяемую пользователем функцию (UDF), которая принимает диапазоны для сравнений. Таким образом, вы можете использовать его по-разному.

Function CompareFormulas3(rng1 As Range, rng2 As Range)

Dim x As Long

If rng1.Count <> rng2.Count Then         'Range sizes do not match         CompareFormulas = CVErr(xlValue)

Else         CompareFormulas = True    ' Assume all the same         For x = 1 To rng1.Count             If rng1(x).Formula <> rng2(x).Formula Then                 'Formulas do not match                 CompareFormulas = False                 x = rng1.Count    ' No need to keep comparing             End If         Next x     End If End Function

Если вы просто хотите подтвердить, что ряд ячеек на обоих листах имеет идентичные формулы, вы можете просто использовать что-то вроде:

=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)

Функция возвращает ИСТИНА, если все ячейки имеют одинаковые формулы, ЛОЖЬ, если какие-либо из ячеек имеют разные формулы, или ошибку #Value, если два диапазона не одинакового размера.

Если вы хотите выделить различия, вы можете использовать UDF в правиле условного форматирования. Предполагая, что вы хотите применить условный формат к ячейкам в Sheet1, просто укажите, что правило должно использовать формулу, а затем используйте ее как формулу:

=NOT(CompareFormulas3(Sheet2!A1,A1))

Если какая-либо из ячеек в Sheet1 не соответствует соответствующим ячейкам в Sheet2, они форматируются в соответствии с любым форматированием, которое вы определили в правиле условного форматирования.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13400) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.