Джоэл заметил, что когда он закрывает книгу, содержащую тысячи формул, в последнее время он получает следующее сообщение: «Формула на этом листе содержит одну или несколько недопустимых ссылок». Джоэл задается вопросом, как он может узнать, к какому из семи рабочих листов в этой книге идет ссылка. Как я могу найти ошибочную формулу? Никаких проблем с отображением информации в своих отчетах не наблюдаю.

Отслеживание недействительных ссылок может быть неприятным. Есть несколько мест, где вы можете начать поиски. Первый находится в формулах на рабочих листах. (Да, вам необходимо выполнить эти шаги для каждого рабочего листа в книге.) Используйте диалоговое окно «Перейти к специальному» (нажмите F5 и выберите «Специальный»), чтобы выбрать переход только к ячейкам, содержащим ошибки. Затем вы можете использовать клавишу Tab для перемещения между любыми ячейками, которые выбирает Excel.

Вы также можете использовать инструмент «Найти» для поиска возможных ошибок. Просто нажмите Ctrl + F, чтобы открыть вкладку «Найти» диалогового окна «Найти и заменить», а затем выполните поиск символа #. Убедитесь, что вы указали Excel, что нужно искать в формулах. Осматривайте все обнаруженное, чтобы убедиться, что это ошибка.

Вам также следует взглянуть на все именованные диапазоны, определенные в вашей книге. Посмотрите на каждое имя в диалоговом окне «Определить» («Вставить | Имя | Определить») и убедитесь, что все, что указано в поле «Ссылается на», не содержит никаких указаний на ошибку.

Это не все места, где могут быть ошибки; Excel действительно хорошо позволяет ошибкам существовать во многих местах. Если вам нужно часто искать ошибки, вы можете попробовать макрос, который просматривает ваши формулы на предмет возможных ошибок.

Sub CheckReferences()

' Check for possible missing or erroneous links in ' formulas and list possible errors in a summary sheet

Dim iSh As Integer   Dim sShName As String   Dim sht As Worksheet   Dim c As Cell   Dim sChar As String   Dim rng As Range   Dim i As Integer, j As Integer   Dim wks As Worksheet   Dim sChr As String, addr As String   Dim sFormula As String, scVal As String   Dim lNewRow As Long   Dim vHeaders

vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")

'check if 'Summary' worksheet is in workbook   'and if so, delete it   With Application     .ScreenUpdating = False     .DisplayAlerts = False     .Calculation = xlCalculationManual   End With

For i = 1 To Worksheets.Count     If Worksheets(i).Name = "Summary" Then       Worksheets(i).Delete     End If   Next i

iSh = Worksheets.Count

'create a new summary sheet     Sheets.Add After:=Sheets(iSh)

Sheets(Sheets.Count).Name = "Summary"

With Sheets("Summary")

Range("A1:D1") = vHeaders   End With   lNewRow = 2

' this will not work if the sheet is protected,   ' assume that sheet should not be changed; so ignore it   On Error Resume Next

For i = 1 To iSh     sShName = Worksheets(i).Name     Application.Goto Sheets(sShName).Cells(1, 1)

Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)



For Each c In rng       addr = c.Address       sFormula = c.Formula       scVal = c.Text

For j = 1 To Len(c.Formula)

sChr = Mid(c.Formula, j, 1)



If sChr = "[" Or sChr = "!" Or _           IsError(c) Then           'write values to summary sheet           With Sheets("Summary")

.Cells(lNewRow, 1) = sShName             .Cells(lNewRow, 2) = addr             .Cells(lNewRow, 3) = scVal             .Cells(lNewRow, 4) = "'" & sFormula           End With           lNewRow = lNewRow + 1           Exit For         End If       Next j     Next c   Next i

' housekeeping   With Application     .ScreenUpdating = True     .DisplayAlerts = True     .Calculation = xlCalculationAutomatic   End With

Set wks = Nothing   Set sht = Nothing   Set rng = Nothing

' tidy up   Sheets("Summary").Select   Columns("A:D").EntireColumn.AutoFit   Range("A1:D1").Font.Bold = True   Range("A2").Select End Sub

Этот макрос создает рабочий лист под названием «Сводка», который используется для перечисления информации о любых ошибках, обнаруженных в ссылках рабочего листа.

Вы также можете использовать программу Excel MVP Билла Манвилла FindLink, которая отлично справляется с поиском информации в ссылках. Вы можете использовать надстройку для поиска символа # во всех ваших ссылках, что должно помочь вам найти ошибки. Более подробную информацию о FindLink можно найти здесь:

http://www.manville.org.uk/software/findlink.htm

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

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

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

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

Этот совет (8662) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Tracking_Down_Invalid_References [Отслеживание недействительных ссылок].