Страницы: 1
RSS
Заливка цветом ячеек учитываемых формулой в расчете
 
Доброго всем времени.

Возможно ли каким-то способом залить каким-либо цветом ячейки участвующие в расчете формулы? К примеру: =ОКРУГЛ((G62+G66+G67+G72+G73)*G86*1,02;2).Понятно, что при нажатии F2 на ячейке с формулой, все ячейки расчета подсвечиваются. Но бывает, что надо проверить учтена ли вот эта ячейка в формуле? Значит надо переместиться в ячейку с формулой, жать  F2, скролить лист в поисках нужной строки/ячейки, и при этом ни в коем случае не выходя из режима редактирования итоговой ячейки. А так, раскрасил - и сразу видно, где и что надо поправить.
 
отмечаете ячейку с формулой
лента Формулы
Влияющие ячейки
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, Да, но если все ячейки находятся вразнобой в одном столбце, то стрелки просто сливаются, и за ними не очень понятно что с ячейками находящимися в середине диапазона.
 
А не боитесь, что залитый массив аналогичным образом не даст никакой информации? (будет очень много ячеек залито цветом)  
Не перестаю удивляться возможностям excel и VBA.
 
Приведу реальный пример из жизни. Не буду заниматься рекламой или антирекламой, в одной сметной программе формируются акты выполненных работ по форме КС-2. Все расчеты производятся в программе, в ексель выгружаются результаты вычислений в виде значений. Так вот эта программа по неясным причинам допускает некоторые погрешности, а именно - при сложении значений по-позиционно результат может не сойтись с итоговым значением, ненамного, но этого достаточно чтобы получить замечания от бухгалтерии, которая по документу на бумаге реально с калькулятором проверяет расчет. Так что приходится проверять за программой. Всё бы ничего, но прежде чем получить итоговое значения, надо сначала получить стоимость по трем составляющим: строительные работы, монтажные работы, оборудование. И в смете они могут идти вперемешку, в разнобой, структура строк может различаться. Самый простой способ не ошибиться - раскраска.
 
Если Вас правильно понял, ваше решение(или его намек):
Код
Sub Fill_influencing()
For Each TSPcol In Cells(2, 1).Precedents
    Cells(TSPcol.Row, TSPcol.Column).Interior.Color = vbYellow
Next
End Sub
Изменено: Shama - 12.02.2021 22:13:14
Не перестаю удивляться возможностям excel и VBA.
 
Shama,спасибо за участие. Но выдало Run-time error 1004, хотя в Вашем примере сработало.
Изменено: cuprum - 12.02.2021 22:16:21
 
Цитата
А так, раскрасил - и сразу видно, где и что надо поправить
для активной ячейки
Код
Sub iColorCell()
Dim temp As String
Dim mo As Object
Dim n As Integer
  temp = ActiveCell.Formula
  ActiveSheet.Cells.Interior.ColorIndex = xlNone
     With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "[A-Z]\d+"
    If .Test(temp) Then
      Set mo = .Execute(temp)
      For n = 0 To mo.Count - 1
        Range(mo(n)).Interior.ColorIndex = 6
      Next
    End If
   End With
End Sub
 
Shama, а, вижу, у Вас завязано на конкретную ячейку.
 
cuprum, Ну от Вас примера не было. Создал сам, как сам и представил. Ранее написал, что это "намек" на решение)  
Не перестаю удивляться возможностям excel и VBA.
 
Kuzmich, Ваш вариант рабочий. Как добавить умножение?
Я изменил строку
Код
.Pattern = "[A-Z]\d+"
на
Код
.Pattern = "[A-Z]*\d+"
стало выдавать ошибку 1004 в строке
Код
Range(mo(n)).Interior.ColorIndex = 6
Что я сделал не так?

Для варианта: =D27*$D$86+E27*$E$86+F27*$F$86+G27*$G$86
Изменено: cuprum - 12.02.2021 22:35:38 (Добавил вариант формулы)
 
Shama, да, конечно, спасибо Вам.
Слегка доработал Ваш вариант напильником ))) и тоже вполне годится
Код
Sub Fill_influencing()
For Each TSPcol In Selection.Cells.Precedents
    Cells(TSPcol.Row, TSPcol.Column).Interior.Color = vbYellow
Next
End Sub
 
Цитата
cuprum написал:
Selection.
Я вот только только сам хотел предложить :)  
Не перестаю удивляться возможностям excel и VBA.
 
Цитата
Что я сделал не так?
Код
.Pattern = "[A-Z]+\d+"

или
Код
.Pattern = "[A-Z]{1,3}\d+"
Изменено: Kuzmich - 12.02.2021 22:58:49
 
Kuzmich, Спасибо за идеи. Видимо я с самого начала неправильно понял причину несрабатывания. Методом научного тыка выяснил что проблема в знаке $, в абсолютной ссылке. Стоит его убрать, и выделяются все участники расчета. Как устранить эту проблему?
 
Цитата
Как устранить эту проблему?
Код
.Pattern = "[\$A-Z]{1,3}\d+"
 
Цитата
Kuzmich написал:
.Pattern = "[\$A-Z]{1,3}\d+"
Большое спасибо, всё получилось.
 
$AB$2 - не влезет в шаблон((
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ну значит мне повезло, у меня таблицы чаще длинные, чем широкие ))
 
и опять это везение))
может лучше шаблон взять "[$A-Z]+\d+"
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, написал
Цитата
$AB$2 - не влезет в шаблон((
У меня эта ячейка выделяется при шаблоне
Код
.Pattern = "[\$A-Z]{1,3}\d+"
 
Цитата
Ігор Гончаренко написал:
$AB$2 - не влезет в шаблон((
Взял $BP$101 - всё по прежнему замечательно работает. Спасибо Kuzmich,
 
извините, согласен
из $AB$2 шаблон "[\$A-Z]{1,3}\d+" вытянет AB$2 - и отметит ее)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Shama, неожиданно для себя, при проверке работы Вашего кода на реальном файле,  обнаружил что он раскрашивает не только ячейки непосредственно указанные  в формуле проверяемой ячейки, но и идет дальше по ходу, если в расчетах  участвуют не значения, а тоже формулы использующие другие ячейки. Сразу  появилась  "гениальная" мысль - а вдруг для такого случая, по мере  вложенности, можно как-то менять цвет, например с каждым уровнем  вложенности делать его бледнее, или наоборот насыщеннее? Но я совершенно  не понимаю как организовать эту градацию в Вашем коде, (при наличии  циклов еще бы понял), да и возможно ли это?
В моем примере проверяем  ячейку E55. В столбце P набросал как это могло бы выглядеть... и сам  засомневался, а надо ли. В общем вопрос чисто академический, т.к. для  большого количества вложенностей может не хватить оттенков, или они  будут слабо различимы.
 
cuprum, Вы знаете, эта такая же неожиданность и для меня) Использовал этот пример только однажды и только для ячеек с "одним уровнем зависимости формул", поэтому даже не задался этим вопросом. Но он действительно интересен. Моих навыков хватило только на это(пример во вложении) - экспериментировал с ячейкой Е30. Но скажу сразу что работает не корректно - видно по ячейкам в столбце I и J.
По сути макрос пробегается по влияющим ячейкам, и если там формула - то влияющих ячеек, этой влияющей ячейки, заливает зеленым.
Но ячейки в в столбце I и J он проходит в самом конце ,поэтому меняет зеленый на желтый(что по заданию не верно). Если макрос чистит всю заливку перед работой - возможно стоит "зацепиться" на отсутствие/наличие заливки.
Подумаю еще над этим на досуге, но скорее всего основная надежда на других участников форума)

 
Не перестаю удивляться возможностям excel и VBA.
 
Цитата
cuprum написал:
как организовать эту градацию
по простому никак не организовать, так как .Precedents - это не указанные, а влияющие  а влияют все что вызывают пересчет, независимо от вложенности., хотя если рекурсивно обходить все ячейки влияющие и в зависимости от уровня рекурсии подкрашивать, то может получится.
По вопросам из тем форума, личку не читаю.
 
Цитата
по мере  вложенности, можно как-то менять цвет
Для активной ячейки
Код
Sub iZalivka()
Dim rng As Range
Dim Adr As String
Dim FormulaText As String
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    FormulaText = ActiveCell.Formula
   For Each rng In ActiveCell.Precedents.Cells
     Adr = rng.Address(0, 0)
     If InStr(1, FormulaText, Adr) > 1 Then
       rng.Interior.Color = vbYellow
     Else
       rng.Interior.Color = vbGreen
     End If
   Next
End Sub
 
и все это чрезвычайно полезно на высокохудожественно оформленном листе: с заливками, подсветками и пр.
лист был строгим и четким, выполнили макрос и все (многое) раскрасилось как после взрыва (хаотично и в разных местах)
задача с точки зрения реализации (раскрасить) довольно проста, вернуть все к исходному состоянию чуть по сложнее, но тоже решается
а вот с практической стороны - очень сомнительно!
1. ячейки с условным форматированием перекрашиваются?
2. ячейки в скрытых строках (столбцах), понятнго, они не видны, а информация о том, что они есть как-то преподносится пользователю?
3. ну и вернуть все как было до того
предложенный код решает эти 3 пункта?
п.3 можно считать отдельной задачей, но если о ней помнить, то и непосредственная задача становиться немного сложнее
а если 2 первых пункта не решены - этот код просто не решает поставленную задачу - раскрасить влияющие ячейки (дать пользователю ВИЗУАЛЬНУЮ ИНФОРМАЦИЮ о ячейках влияющих на результат в рассматриваемой ячейке).
а знаете как называется код, который не решает задачу, для решения которой он написан? должно быть знаете))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Частично согласен с Ігор Гончаренко, особенно принимая во внимание наличие встроенного инструмента, который покажет зависимость визуально стрелками, без форматирования и проблем с конфликтом.

Что касаемо кода Kuzmich,  то в общем случае такое приметь опасно, могут возникнуть проблемы  из-за наличия в ячейках текстовых выражений  похожих на адрес ячейки, относительными и абсолютными ссылками и наконец с диапазонами. Отсекать не сложно, использовать Range.DirectPrecedents
Код
Sub iZalivka()
Dim rng As Range
Dim Adr As String
Dim FormulaText As String
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    'FormulaText = ActiveCell.Formula
   For Each rng In ActiveCell.DirectPrecedents.Cells
     Adr = rng.Address(0, 0)
     'If InStr(1, FormulaText, Adr) > 1 Then
       rng.Interior.Color = vbYellow
    ' Else
    '   rng.Interior.Color = vbGreen
    ' End If
   Next
End Sub

а вот если нужно иерархически показать, то проверять нужно не закрашена ли ячейка уже и бродить под дереву прямых зависимостей опускаясь по уровням вниз рекурсивно.

А если просто указать прямые и кривые, то
Код
Sub iZalivka()
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    ActiveCell.Precedents.Cells.Interior.Color = vbGreen
    ActiveCell.DirectPrecedents.Cells.Interior.Color = vbYellow
End Sub
Изменено: БМВ - 28.02.2021 08:56:56
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх