Excel - Как я могу проверить все прецеденты данной ячейки?

Question

Я относительно наивный пользователь Excel, которого бросили посреди океана клеток. Мне было поручено кодировать значение одной ячейки в книге в python. Конечно, эта ячейка имеет несколько прецедентов, которые имеют несколько прецедентов и т.д.

Эта рабочая тетрадь насчитывает около 20 листов. Около половины - это просто данные, а остальные совершают всевозможные жестокие и злые поступки с этими данными. Один только основной лист содержит сотни таких функциональных ячеек. Я сейчас написал сотни строк кода на Python, и я понятия не имею, сколько еще осталось.

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

Answer 1

Есть два возможных метода: первый - не программирование, а второй - программирование (макрос).

Непрограммируемый метод:

1. Нажмите вкладку формулы.
2. Найдите группу аудита формул и нажмите « Показать значок формулы» , расположенный в правом верхнем углу.

3. Сочетание клавиш - Ctrl+`.

4. В группе аудита формул вы найдете прецеденты трассировки, просто щелкните по ней, чтобы увидеть взаимосвязь.

Метод программирования (VBA Macro):

Sub ExtractAllFormulas()

    Dim sht As Worksheet
    Dim shtName
    Dim myRng As Range
    Dim newRng As Range
    Dim c As Range

ReTry:
    shtName = Application.InputBox("Write name of the new sheet to list all formulas.", "New Sheet Name")
    If shtName = False Then Exit Sub

    On Error Resume Next
    Set sht = Sheets(shtName)
    If Not sht Is Nothing Then
        MsgBox "This sheet already exists"
        Err.Clear
        Set sht = Nothing
        GoTo ReTry
    End If

    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    Application.ScreenUpdating = False
    With ActiveSheet
        .Range("A1").Value = "Formula"
        .Range("B1").Value = "Sheet Name"
        .Range("C1").Value = "Cell Address"
        .Name = shtName
    End With

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> shtName Then
            Set myRng = sht.UsedRange
            On Error Resume Next
            Set newRng = myRng.SpecialCells(xlCellTypeFormulas)
            For Each c In newRng
                Sheets(shtName).Range("A65536").End(xlUp).Offset(1, 0).Value = Mid(c.Formula, 2, (Len(c.Formula)))

                Sheets(shtName).Range("B65536").End(xlUp).Offset(1, 0).Value = sht.Name

                Sheets(shtName).Range("C65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Substitute(c.Address, "$", "")

            Next c
        End If
    Next sht
    Sheets(shtName).Activate
    ActiveSheet.Columns("A:C").AutoFit
    Application.ScreenUpdating = True
End Sub

Как это устроено:

1. Вставьте этот код VBA как модуль.
2. Как только вы запустите, появится окно ввода.
3. Напишите название нового листа и закончите с ОК.

Этот код перечислит все формулы в новом листе.