Excel 2010 - выделение ячеек, используемых в любой формуле

Question

Мне нужно будет сложить длинный столбец значений, разделив их на несколько групп. И я не хочу пропустить какой-то скандал. Поэтому мне нужно выделить все ячейки, которые я уже использовал в некоторой формуле (например, = D1+D2).

Когда я дважды щелкаю по формуле, используемые ячейки подсвечиваются. Я хочу этот эффект. Я обнаружил, что CTRL [ будет делать этот трюк, но он не влияет на мой компьютер с Excel 2010. Есть ли какая-то другая возможность? Спасибо

Например, у меня есть два столбца, первый - данные, второй - группировка. А4 не используется, А1 ..A3 должен быть выделен как уже использованный.

**1**    =A1+A2
**3**    =A3
**2**
4

Answer 1

Я не думаю, что макет - это то, что вам нужно, но этого может быть достаточно: вы можете использовать трассировку зависимых / прецедентов

Он находится на панели инструментов Формулы, в разделе Аудит формул (и в том же месте есть опция для удаления стрелок).

Подробнее

Тем не менее, мне не нравится выше. Теперь, это действительно зависит от ваших потребностей, но я быстро написал этот VBa для вас

Sub Button5_Click()

'PLEASE EDIT THIS FIRST BIT. 

Dim row As Integer
row = 1    ' THE STARTING ROW IN YOUR EXCEL SHEET

Dim numberOfRows As Integer
numberOfRows = 5    'THE TOTAL NUMBER OF ROWS YOUR WORKSHEET HAS

Dim columnWithFormula As String
columnWithFormula = "E"    ' THE COLUMN WHERE THE FORMULAs ARE (I suspect you are using B if your example is accurate))

Dim colourIndex As Integer
colourIndex = 26     ' WHAT COLOUR TO HIGHLIGHT COLUMNS. GOOGLE VBa COLOR INDEX

'AND STOP EDITING :)

For row = 1 To numberOfRows

If range(columnWithFormula & row).Value <> "" Then

   Dim result As String

   result = range(columnWithFormula & row).Formula

   result = Replace(result, "(", " ")
   result = Replace(result, ")", " ")
   result = Replace(result, "-", " ")
   result = Replace(result, "+", " ")
   result = Replace(result, "*", " ")
   result = Replace(result, "/", " ")
   result = Replace(result, "=", " ")
   result = Replace(result, ",", " ")

   Dim cells() As String
   cells = Split(Trim(result), " ")

   For j = 0 To UBound(cells)
    range(cells(j)).Interior.ColorIndex = colourIndex
   Next j

End If

Next row


End Sub

Выше приведено следующее (я включил формулу, чтобы вы могли видеть, в каких ячейках была формула)

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

Обратите внимание, это изменит цвет фона ячейки, и отмена не будет работать! Так что, если вы уже используете подсветку, вам нужно обновить код! Это также означает, что после запуска этого макроса вам нужно будет вручную выбрать рабочий лист и вернуть цвет фона на ваш выбор ...

Чтобы расширить это, вы можете обновить цикл for и заменить его на

For Each Cell in ActiveSheet.UsedRange.Cells
      'logic
Next

проверить каждую ячейку на листе, чтобы увидеть, есть ли где-нибудь формула ...

Answer 2

Я подготовил следующий макрос для классификации выбранных ячеек на "Вход", "Расчет" и "Выход", и код назначит стили с именами "Вход", "Расчет" и "Выход". Это полезно для понимания листов, подготовленных кем-то другим, или листов отладки.

Вывод кода будет выглядеть следующим образом (я включил "Показать формулы").

Поместите приведенный ниже код в "Код листа", а не в модуле.

'Macro to classify the cells from selected range into "input", "calculation" and "output"
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    With ActiveSheet.Cells
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = xlAutomatic
        .Font.Bold = False
    End With
    Dim rCell As Range
    Application.EnableEvents = False
    For Each rCell In Target.Cells
        If IsEmpty(rCell.Value) = True Then GoTo AllDone:
        If Not rCell.HasFormula Then
            If HasDependents(rCell) Then
                rCell.Style = "Input"
                GoTo AllDone:
            Else
                GoTo AllDone:
            End If
        Else
            If HasDependents(rCell) Then
                rCell.Style = "Calculation"
                GoTo AllDone:
            Else
                rCell.Style = "Output"
                GoTo AllDone:
            End If
        End If
AllDone:
Err.Clear
    Next rCell
    Application.EnableEvents = True
End Sub
Public Function HasDependents(ByVal Target As Excel.Range) As Boolean
    On Error Resume Next
    HasDependents = Target.Dependents.Count
End Function

Answer 3

перейти к пустой ячейке - в этом типе ячейки = V40 = 1:1048576, где первая ссылка (в данном случае v40) ссылается на другую пустую ячейку. Вторая часть выделяет весь лист.

Нажмите ввод, а затем нажмите на ячейку, щелкните на формулах просмотра и дважды щелкните прецеденты трассировки. Это нарисует синюю ссылку на каждую ячейку, на которую ссылается другая ячейка. Ищите те без линий - они были пропущены.

ясень