Преобразование текста в формулу Excel

@Inna1998 · Регистрация: 01.03.2020

Студворк — интернет-сервис помощи студентам

Всем здравствуйте. Имеется пользовательская функция UDF для преобразования формульного текста в формулу и последующего вычисления ее значения с помощью команды Evaluate(text). Она успешно справляется с текстом "А1+В2" возвращая в ячейку, содержащую данную функцию, значение суммы этих ячеек. Однако, данная UDF не коем образом не обрабатывает такой формульный текст , если бы он содержал еще и какие либо екселевские функции, например, даже то же по смыслу выражение "СУММ(А1; В2)" у функции вычислить не удается. Подскажите, чем возможно дополнить или заменить Evaluate(text) для решения данной задачки.

Ниже прикрепляю пользовательскую UDF, о которой идет речь в посте. Всем заранее спасибо

Visual Basic
1
2
3
4
Function Preobrazz(text As String)
Aplication.Volatile
Preobrazz = Evaluate(text)
End Function

@Punkt5 · 21.01.2021, 20:04

Сообщение от Inna1998

Однако, данная UDF не коем образом не обрабатывает такой формульный текст , если бы он содержал еще и какие либо екселевские функции, например, даже то же по смыслу выражение "СУММ(А1; В2)" у функции вычислить не удается.

А зачем? Почему не использовать стандартные функции Excel в самих формулах Excel, добавляя к ним вышеупомянутую UDF, если нужно?
Скорее всего вам в этом случае придётся писать самостоятельный анализатор-преобразователь строк в функционал. Возможно использовать некоторую пустую ячейку, типа ZA1, временно записывая Cells(1,"ZZ").FormulaLocal = "=(здесь ваша строка)", а потом брать полученное значение и возвращать в качестве реультата функции. Но перед этим надо будет всё равно проанализировать строку, приведя координаты ячеек из относительного синтаксиса к абсолютному.
В этом случае самый простой выход для данной UDF: сохранить куда-нибудь формулу из текущей ячейки, вставить на её место рассматриваемую строку со стандартными функциями, отключить отображение изменений, выполнить метод Calculate для данной ячейки, Взять значение и вернуть в качестве результата расчёта в данную ячейку, вернув перед этим формулу и включив отображение результатов расчёта.
Смысла корячиться нет совершенно. Ставьте формулу с участием стандартных функций прямо в формулу ячейки. Чего нет в стандартных - реализуйте через UDF и добавляйте в ту же формулу. Если формула получается длинной, сложной или корявой - пишите макрос для расчёта, или UDF с полноценной логикой.

@Zeag · 21.01.2021, 20:10

Для функций нужно их именование английским вариантом и c заменой точек с запятой на запятую, то есть для СУММ(A1; B2) надо подать на вход Evaluate текст SUM(A1, B2). Не знаю, можно ли тут ссылки на сторонние ресурсы помещать. Если нет, в личке или погуглите computergaga, хорошее описание Evaluate.

@Inna1998 · 21.01.2021, 20:58 **[ТС]**

Zeag, да так заработало, спасибо огромное. Осталось только реализовать перевод с русских функций на английский(без замены их визуально на листе на таковые конечно, а исключительно в теле пользовательской UDF при работе с тем или иным русским! формульным текстом). Печально, что в английской версии функции, помимо перевода слова, еще имеют отличия в разделителях, а возможно где то и не имеют и тогда вобще невозможно быстро осуществить легко таковой транслит, имея просто массив соответствия рус-en). Но excel, я думаю, что влюбом случае каков бы не был пользовательский язык переводит все и сам на английский.. И где то это есть в нем внутри.. Осталось определить это место..

@Punkt5 · 21.01.2021, 21:29

Сообщение от Inna1998

Осталось только реализовать перевод с русских функций на английский

Можно воспользоваться какой-нибудь невидимой ячейкой и использовать функционал Excel: писать строку в FormulaLocal и читать из Formula

Visual Basic
1
2
3
  
  [B5].FormulaLocal = "=СУММ(A1:A3)"
  [A5] = Evaluate([B5].Formula)

Можно ещё добавить потом, если это всё же видимая ячейка

Code
1
[B5].ClearContents

@Zeag · 21.01.2021, 21:40

Inna1998,

Сообщение от Inna1998

еще имеют отличия в разделителях

Это относится не только к Evaluate, а вообще записи формул в макросах. Несмотря на русский эксель, там будет написано

Visual Basic
1
Cells(i,j).Formula="=SUM(A2, B2)"

Или, как сказали, с промежуточной записью в FormulaLocal и чтением.

@Inna1998 · 21.01.2021, 21:46 **[ТС]**

Punkt5, это возможно писать именно в теле кода Function (перевод в formulalocal) или же это надо в макросе Sub и его вызывать потом как то из Function? Спасибо

@Punkt5 · 21.01.2021, 21:52

Сообщение от Inna1998

Punkt5, это возможно писать именно в теле кода Function (перевод в formulalocal) или же это надо в макросе Sub и его вызывать потом как то из Function?

Да, конечно можно. Для вашей же функции будет примерно так:

Code
1
2
3
4
5
6
Function Preobrazz(text As String)
  Aplication.Volatile
  [BB5].FormulaLocal = text
  Preobrazz = Evaluate([BB5].Formula)
  [BB5].ClearContents
End Function

BB5 - чтобы она была подальше и вообще было не видно эту ячейку.

@Inna1998 · 21.01.2021, 21:53 **[ТС]**

Насколько я правильно понимаю UDF не может работать с какими либо другими ячейками excel (кроме той в которой она применена и записана) и делать с ними какие либо операции пусть даже и для собственных вычислений. Или я не права. Тогда поправьте пожалуйста, буду признательна в этом

@Punkt5 · 21.01.2021, 22:13

Сообщение от Inna1998

Насколько я правильно понимаю UDF не может работать с какими либо другими ячейками excel

Да, похоже Вы правы. не работает оно в UDF. только в независимом SUB или Function. И из тела UDF не вижу, как это можно вызвать.

@Inna1998 · 21.01.2021, 22:25 **[ТС]**

Да, я уже тоже и на Range("BB5") пыталась заменить просто [BB5] и через cells() .formula, но что то не хочет работать (ошибка #ЗНАЧ!). В любом случае спасибо большое, что обратили внимание на мои вопросы. Будем ждать, если еще кто то подскажет , если возможно все таки UDF как то с переводом на английский язык русского формульного текста, для последующего преобразования Evaluate(text), где требуется формульный текст в английском формате переписан чтобы был

Новые блоги и статьи Все статьи Все блоги /
AkelPad-скрипты, структуры, и немного лирики.. testuser2 05.04.2026 Такая программа, как AkelPad существует уже давно, и также давно существуют скрипты под нее. Тем не менее, прога живет, периодически что-то не спеша дополняется, улучшается. Что меня в первую очередь. . .	Отображение реквизитов в документе по условию и контроль их заполнения Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "ПланированиеСпецтехники", разработанного в конфигурации КА2. Данный документ берёт данные из другого нетипового документа. . .	Фото всей Земли с борта корабля Orion миссии Artemis II kumehtar 04.04.2026 Это первое подобное фото сделанное человеком за 50 лет. Снимок называют новым вариантом легендарной фотографии «The Blue Marble» 1972 года, сделанной с борта корабля «Аполлон-17». Новое фото. . .	Вывод диалогового окна перед закрытием, если документ не проведён Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: реализовать программный контроль на предмет проведения документа. . .
Программный контроль заполнения реквизитов табличной части документа Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: 1. Реализовать контроль заполнения реквизита. . .	wmic не является внутренней или внешней командой Maks 02.04.2026 Решение: DISM / Online / Add-Capability / CapabilityName:WMIC~~~~ Отсюда: https:/ / winitpro. ru/ index. php/ 2025/ 02/ 14/ komanda-wmic-ne-naydena/	Программная установка даты и запрет ее изменения Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: при создании документов установить период списания автоматически. . .	Вывод данных в справочнике через динамический список Maks 01.04.2026 Реализация из решения ниже выполнена на примере нетипового справочника "Спецтехника" разработанного в конфигурации КА2. Задача: вывести данные из ТЧ нетипового документа. . .

@Zeag 859 / 509 / 187 Регистрация: 09.03.2009 Сообщений: 1,727
	21.01.2021, 20:10
	Сообщение было отмечено Inna1998 как решение Решение Для функций нужно их именование английским вариантом и c заменой точек с запятой на запятую, то есть для СУММ(A1; B2) надо подать на вход Evaluate текст SUM(A1, B2). Не знаю, можно ли тут ссылки на сторонние ресурсы помещать. Если нет, в личке или погуглите computergaga, хорошее описание Evaluate. 1

@Inna1998 2 / 2 / 1 Регистрация: 01.03.2020 Сообщений: 72
	21.01.2021, 20:58 [ТС]
	Zeag, да так заработало, спасибо огромное. Осталось только реализовать перевод с русских функций на английский(без замены их визуально на листе на таковые конечно, а исключительно в теле пользовательской UDF при работе с тем или иным русским! формульным текстом). Печально, что в английской версии функции, помимо перевода слова, еще имеют отличия в разделителях, а возможно где то и не имеют и тогда вобще невозможно быстро осуществить легко таковой транслит, имея просто массив соответствия рус-en). Но excel, я думаю, что влюбом случае каков бы не был пользовательский язык переводит все и сам на английский.. И где то это есть в нем внутри.. Осталось определить это место.. 0

@Inna1998 2 / 2 / 1 Регистрация: 01.03.2020 Сообщений: 72
	21.01.2021, 21:46 [ТС]
	Punkt5, это возможно писать именно в теле кода Function (перевод в formulalocal) или же это надо в макросе Sub и его вызывать потом как то из Function? Спасибо 0

@Inna1998 2 / 2 / 1 Регистрация: 01.03.2020 Сообщений: 72
	21.01.2021, 21:53 [ТС]
	Насколько я правильно понимаю UDF не может работать с какими либо другими ячейками excel (кроме той в которой она применена и записана) и делать с ними какие либо операции пусть даже и для собственных вычислений. Или я не права. Тогда поправьте пожалуйста, буду признательна в этом 0

@Inna1998 2 / 2 / 1 Регистрация: 01.03.2020 Сообщений: 72
	21.01.2021, 22:25 [ТС]
	Да, я уже тоже и на Range("BB5") пыталась заменить просто [BB5] и через cells() .formula, но что то не хочет работать (ошибка #ЗНАЧ!). В любом случае спасибо большое, что обратили внимание на мои вопросы. Будем ждать, если еще кто то подскажет , если возможно все таки UDF как то с переводом на английский язык русского формульного текста, для последующего преобразования Evaluate(text), где требуется формульный текст в английском формате переписан чтобы был 0

Преобразование текста в формулу Excel

Решение