Страницы: 1
RSS
Поиск формул ячейки, в которых встречаются константы
 
Коллеги, день добрый.
Столкнулся с задачей описания методики расчёта разнообразных показателей. На входе у меня куча Excel-ек, в которых в разнообразном виде эти расчеты представлены.
Как положено, есть ячейки ввода, где только константа какая-то забита, есть ячейки с правильными формулами, а есть "подправленные" формулы, в которых используются неописанные константы.

Подскажите, пожалуйста, какой есть более-менее быстрый способ найти все ячейки, в которых есть формула, но при этом в формуле используются константы, например:
=СУММ(A:A)/3,141529
=A1*5/$B$2

Существует ли такой, или только глазками? Пока ничего сам не нашел.
F1 творит чудеса
 
Максим Зеленский, здравствуйте
может через функцию Ф.ТЕКСТ
Код
=ПОИСК("3.141529";Ф.ТЕКСТ(D2))
 
эх, Максим. Нелегкая это задача. Я в свое время отбирал при помощи регулярок, т.к. там куча нюансов. Правда, я отбирал наоборот - адреса ячеек, именованных диапазонов и т.п.
Но если суть только в том, чтобы определить такие ячейки - то я бы, наверное, сделал так:
разбил бы сначала по всем возможным операндам: */-+^. Внутри этих блоков постараться отобрать регулярками текст, сразу после которого идут скобки(гипотетически, это функция). После этого проверять на IsNumeric. Если это число - определится. Но остается вопрос: а надо ли определять текстовые константы? :)

Вообще, реально сделать. Более чем. Но время потратить придется.
Изменено: Дмитрий(The_Prist) Щербаков - 14.02.2020 13:53:22
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
abc1 написал:
через функцию Ф.ТЕКСТ
предположу, что Максим заранее не знает что за цифры и числа там будут.
Вот, накидал на скорую руку:
Код
Sub GetConstants()
    Dim asp, x, aseps
    Dim txt$, s$
    
    txt = ActiveCell.Formula
    aseps = Array("/", "*", "-", "+")
    s = txt
    For Each x In aseps
        s = Replace(s, x, ":")
    Next
    asp = Split(s, ":")
    For Each x In asp
        s = Replace(Replace(x, ".", ","), " ", "")
        If IsNumeric(s) Then
            Debug.Print x
        End If
    Next
End Sub

естественно, надо тестировать на разных вариантах и подправлять.
Изменено: Дмитрий(The_Prist) Щербаков - 14.02.2020 13:59:17
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, спасибо за идею! Примерно в таком направлении и двигаюсь - сплит по операндам, потом обрезание скобок и ;, потом анализ кусочков. Была изначально мысль проверять кусочек на наличие буквы латиницы и считать это ссылкой, но проверка кусочка на IsNumeric - то, что нужно. Вот что значит давно не писать код - забыл о такой функции.
Конечно, таким образом найдутся еще всякие числовые аргументы функций типа ВПР(value;range;2;0), но это меньшее из зол, такие потом руками все равно отсматривать.
Сначала думал выбрасывать все, что в скобках, но потом встретил формулу типа =(А5+7)/2 и понял, что возни тут больше. Может быть, если будет слишком много таких, тогда займусь

abc1, в том-то и дело, что неизвестно, что там за константа может быть. Непредсказуемо. Задача ее поймать и выдать по ушам автору формулы
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
всякие числовые аргументы функций типа ВПР(value;range;2;0)
вот здесь проблема, ибо что номер столбца, что искомое значение могут быть константами и 100% определить тут нельзя будет правильно ли отбрасывать аргумент или нет. Как вариант можно наиболее часто встречающиеся функции отсекать, но это уже слишком серьезной углубление, т.к. даже в ВПР можно любой аргумент выразить константой: ВПР(5;{1:2:3:4:5};2;0)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Упрощенный алгоритм. Анализируем посимвольно формулу слева направо, пропуская текстовые литералы. Если встречаем цифру, перед которой нет буквы, цифры, знака подчеркивания, знака доллара - то это начало числовой константы. Буква определяется как символ, для которого
Код
UCase (x) <> LCase(x)
Изменено: sokol92 - 14.02.2020 19:09:03
Владимир
 
Добрый день, Михаил! Предвидя Ваше появление я дописал "упрощенный".  :)  

Еще надо разбираться со знаком "." (точка), поскольку этот знак может встречаться в именах ячеек и функций. Но мне кажется, что для поставленной в #1 задачи, подойдет. В 10 строк, правда, не уложишься.
Изменено: sokol92 - 14.02.2020 19:26:20
Владимир
 
Цитата
sokol92 написал:
Еще надо разбираться со знаком "." (точка),
это было в многоточии :-)
По вопросам из тем форума, личку не читаю.
 
В версии Pro есть надстройка Inquire:
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
надстройка Inquire
Поправочка Pro+ судя по описанию https://support.office.com/ru-ru/article/Включение-надстройки-inquire-Запрос-6bc668e2-f3c6-4729-8ce1-75ea20aa9d90

А так же https://www.youtube.com/watch?v=kF4OuLk5e8M
https://www.excelawesome.com/downloads/all-access/
Изменено: БМВ - 18.02.2020 14:46:59
По вопросам из тем форума, личку не читаю.
 
Цитата
buchlotnik написал:
надстройка Inquire
Отлично! Лучший код имеет 0 строк.
Владимир
 
Доброе время суток.
Вариант в развитие идей Дмитрия. Считаем, что формула содержит числовые константы, если за любым из символов =+-*/^({ следует цифра. Правда, что делать если есть ещё и текстовые константы, содержащие внутри числа? Ну, и с такой формулой =СУММ(A2:B3;123) тоже не совсем понятно что делать.
Код
Public Function ContainsConstants(ByVal FormulaText As String) As Boolean
    Static pSpace As Object, pSign As Object
    Dim bResult As Boolean
    bResult = False
    If pSpace Is Nothing Then
        Set pSpace = CreateObject("VBScript.RegExp")
        pSpace.Global = True: pSpace.Pattern = " |\n"
        Set pSign = CreateObject("VBScript.RegExp")
        pSign.Pattern = "[=+\-*/\^({]\d"
    End If
    FormulaText = pSpace.Replace(FormulaText, "")
    If Left$(FormulaText, 1) = "=" Then
        bResult = pSign.Test(FormulaText)
    End If
    ContainsConstants = bResult
End Function
 
Несмотря на реально рабочий вариант с Inquire алгоритм интересен.
По вопросам из тем форума, личку не читаю.
 
Андрей VG, Андрей, вечер добрый.  на =ROW(1:1) спотыкается.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
на =ROW(1:1) спотыкается.
Уф, упарил :)  В JS, VB нет свержадных кванторов, поэтому на шаблон "=\d+(?!: )" при тестировании строки "=123:456", RegExp скромно возвращает "=12" :excl:  - а фигли после 12 нет :  :D   Пришлось костылить. Может Кузьмич или сотоварищи что-нибудь дельное предложат. Сдулся я.
Код
Public Function ContainsConstants(ByVal FormulaText As String) As Boolean
    Static pSpace As Object, pSign As Object
    Dim bResult As Boolean, pItems As Object
    bResult = False
    If pSpace Is Nothing Then
        Set pSpace = CreateObject("VBScript.RegExp")
        pSpace.Global = True: pSpace.Pattern = " |\n"
        Set pSign = CreateObject("VBScript.RegExp")
        pSign.Pattern = "[=+\-*/\^({]\d+:?"
    End If
    FormulaText = pSpace.Replace(FormulaText, "")
    If Left$(FormulaText, 1) = "=" Then
        Set pItems = pSign.Execute(FormulaText)
        If pItems.Count > 0 Then bResult = Right$(pItems(0).Value, 1) <> ":"
    End If
    ContainsConstants = bResult
End Function
Изменено: Андрей VG - 14.02.2020 22:14:56
 
Коллеги, спасибо за варианты, в понедельник их погоняю.

Я очень надеюсь, что не в исходных данных не будет сложных формул типа массивов в функциях, именованных констант (и вообще именованных диапазонов в формулах), функций а-ля СТРОКА и т.п. Там всё, по идее, сильно проще (опять же, надеюсь), только количество файлов большое.

Но в целом годная получилась тема для таких задач, кмк :)

buchlotnik, про Inquire вспоминал перед постом, но с разбега не нашел там указанный функционал, спасибо за наводку!
F1 творит чудеса
 
buchlotnik,
шикардос:


завтра натравлю на рабочие файлы

Еще раз всем спасибо!
Остальные варианты протестирую также
F1 творит чудеса
 
Допилил всё таки вариант с учётом : после последовательности цифр. Проверка вложенности в текстовую константу символов цифр для игнорирования - не производится.
Код
Public Function ContainsConstants(ByVal FormulaText As String) As Boolean
    Static pSpace As Object, pSign As Object
    Dim bResult As Boolean
    bResult = False
    If pSpace Is Nothing Then
        Set pSpace = CreateObject("VBScript.RegExp")
        pSpace.Global = True: pSpace.Pattern = " |\n"
        Set pSign = CreateObject("VBScript.RegExp")
        pSign.Pattern = "[=+\-*/\^({]\d+\b(?!:)"
    End If
    FormulaText = pSpace.Replace(FormulaText, "")
    If Left$(FormulaText, 1) = "=" Then
        bResult = pSign.Test(FormulaText)
    End If
    ContainsConstants = bResult
End Function
 
Андрей VG,  Андрей, как говорится, дай дураку стеклянный орган, он его сломает и руки порежет.  :D
Сломал я  и этот вариант, и Inquire  =SUM(--"10%")  :D
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
он его сломает и руки порежет.
Э, не, тут неприкрытый мухлёж! :)
Цитата
БМВ написал:
"10%"
А на картинке у Максима стоит только числовые константы. С чего бы тогда Inquire находить число?
Ну, а в моих изысканиях я сразу предупреждал, что нечто вроде =A1 & " проект " & B1 & "-2022 года" будет находить числовую константу, там где её нет. По идее тогда нужно поудалять сначала текст между кавычками, а уж затем осуществлять поиск числовых констант.
Страницы: 1
Наверх