Excel - Como posso investigar todos os precedentes de uma determinada célula?

Question

Sou um usuário de excel relativamente ingênuo que foi descartado no meio de um oceano de células. Eu fui encarregado de codificar o valor de uma célula em uma pasta de trabalho em python. Claro, esta célula tem múltiplos precedentes, que possuem múltiplos precedentes, etc.

Este livro tem cerca de 20 folhas. Cerca de metade são apenas dados, mas os outros fazem todo tipo de coisas cruéis e perversas com esses dados. A folha principal sozinha possui centenas de células de funções. Eu agora escrevi centenas de linhas de código em python, e não tenho ideia de quanto ainda resta.

Eu realmente gostaria de ter uma ideia de quão imensa e interconectada é essa pasta de trabalho. Existe uma maneira de gerar uma 'árvore precedente' de uma célula para ver quantos níveis ela vai e quantas células ela depende?

    

Answer 1

Existem dois métodos possíveis, primeiro é Não-programação e o segundo é programação (Macro).

Método de não programação:

1. Clique na guia Fórmula.
2. Encontre o Grupo de auditoria de fórmulas & Clique em Mostrar Ícone de fórmula , disponível no canto superior direito.

3. O atalho do teclado é Ctrl + '.

4. No Grupo de auditoria de fórmulas , você encontra Rastreio     Precedentes, basta clicar para ver o     relacionamento.

Método de programação (macro VBA):

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

Como funciona:

1. Insira este código VBA como módulo.
2. Assim que você executar, ele exibirá a caixa de entrada.
3. Escreva um novo nome de planilha & termine com Ok.

Este código listará toda a fórmula na nova planilha.