Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Макросом записать в ячейку формулу, возвращающую написание формулы из другой ячейки и ее расчетное значение.
 
#1
27.08.2025 21:15:20
Доброго времени суток, друзья!
Возникла нужда: Макросом записать в ячейку формулу, возвращающую написание формулы из другой ячейки и ее расчетное значение.
В ячейке таблицы, например B2 находится формула: =ОКРУГЛ(0,55*1,75*1,15;3), которая возвращает значение, равное 1,107
нужно в активной ячейке, скажем D2, запустить макрос, по Ctrl+f:
Код
Sub FormulaPlusZnacenie()
'
' FormulaPlusZnacenie Макрос
' В ячейку записывается формула и ее расчет. Возвращает формулу и рассчитанное ее значение.
'
' Сочетание клавиш: Ctrl+f
'
    Dim fRZn As String

    fRZn = InputBox("1. Скопируйте в буфер содержащуюся в ячейке формулу;" & Chr(10) & _
                    "2. Выберите ячейку, в которую нужно вставить формулу и ее расчетное значение;" & Chr(10) & _
                    "3. Запустите <Ctrl + f> данный макрос и в окно ввода вставте содержимое буфера.", "Скопируйте из буфера:")

    'ActiveCell.FormulaR1C1 = "=TEXT(""ОКРУГЛ(0,55*1,75*1,15;3)"","""")&"" = ""&ROUND(0.55*1.75*1.15,3)"
    
    ActiveCell.FormulaR1C1 = "=TEXT(" & Chr(34) & fRZn & Chr(34) & "," & Chr(34) & Chr(34) & ")&"" = " & Chr(34) & fRZn
    
End Sub
на этой строке
Код
ActiveCell.FormulaR1C1 = "=TEXT(" & Chr(34) & fRZn & Chr(34) & "," & Chr(34) & Chr(34) & ")&"" = " & Chr(34) & fRZn
выдает ошибку. Может кто подскажет, что не так?
Изменено: Григорий Калюга - 27.08.2025 21:15:57
 
 
 
#2
27.08.2025 22:08:13
Григорий Калюга, добрый вечер. Может так? (функция взята https://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=8&TID=24328)
Код
Sub FormulaPlusZnacenie()
'
' FormulaPlusZnacenie Макрос
' В ячейку записывается формула и ее расчет. Возвращает формулу и рассчитанное ее значение.
'
' Сочетание клавиш: Ctrl+f
'
    Dim fRZn As String
 
    fRZn = InputBox("1. Скопируйте в буфер содержащуюся в ячейке формулу;" & Chr(10) & _
                    "2. Выберите ячейку, в которую нужно вставить формулу и ее расчетное значение;" & Chr(10) & _
                    "3. Запустите <Ctrl + f> данный макрос и в окно ввода вставте содержимое буфера.", "Скопируйте из буфера:")
 
    'ActiveCell.FormulaR1C1 = "=TEXT(""ОКРУГЛ(0,55*1,75*1,15;3)"","""")&"" = ""&ROUND(0.55*1.75*1.15,3)"
    Dim g
    g = EvalLocal("=" & fRZn)
    ActiveCell.FormulaLocal = "=" & Chr(34) & fRZn & """ " & "=" & g
     
End Sub

Function EvalLocal(FormulaLocal As String, Optional Sh As Worksheet)
 If Sh Is Nothing Then Set Sh = ActiveSheet
 With Sh.Names.Add("My.Temporary.Name", RefersToLocal:=FormulaLocal)
   EvalLocal = Evaluate(.RefersTo)
   .Delete
 End With
End Function
Изменено: Alex - 27.08.2025 22:13:22
 
 
 
#3
28.12.2025 15:10:59
Что-то этот трюк не работает у меня.

Задача такая: в интерфейсе с настройками (msForms) дать пользователю возможность задать параметр с использованием привычных ему формул Excel на "человеческом" языке. К примеру, в поле Параметр1 пользователь может написать СУММ(Лист1!A1;Лист1!A2), а программа должна преобразовать это в нативный формат и вычислить с помощью Evaluate.

Использовать для этого временную ячейку на листе кажется не очень правильным, а с именами какая-то странность: то ошибка 1004 при добавлении имени, то имя добавляется, но RefersTo отдает то же самое, что хранит RefersToLocal, хотя при этом преобразование из A1 в R1C1 работает.

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

Поиск по форуму и гуглам открывает некоторые нюансы:

Microsoft сообщает о проблемах с RefersToLocal
Пользователи пишут, что иногда приходится пошаманить со свойством Comment

Все перепробовал, не помогает. Файл с примером во вложении.

P.S.
Офис 2021, версия Excel 2108, 64-разрядная
Изменено: dhead - 28.12.2025 21:08:23
 
 
 
#4
19.01.2026 09:12:58
Неужели ни у кого такой проблемы не возникало? Пока решения не нашел, а хотелось бы..
 
 
 
#5
19.01.2026 09:18:29
Цитата
dhead написал: с использованием привычных ему формул Excel на "человеческом" языке
Вы уверены, что этот язык человеческий и привычный?
Отслеживать правильность написания пользователем функций Excel от руки...бррр...
Обычный бы человек написал 2+3, а не СУММ(Лист1!A1;Лист1!A2)
Цитата
dhead написал:
Неужели ни у кого такой проблемы не возникало?
И не дай Бог, что-бы возникло  :D
Согласие есть продукт при полном непротивлении сторон
 
 
 
#6
19.01.2026 10:22:24
Если честно - плохо понимаю вообще смысл этого действия. Здесь ошибок может быть куча: начиная от разделителей и заканчивая грамматикой. Все это может быть вперемешку и никакие FormulaLocal не разберутся, что так кто написал.
А если учесть, что применяются только встроенные функции(без всяких там UDF самописных) - то вообще не ясна цель изврата. В Excel более чем вменяемый Мастер функций, который позволяет набирать вложенные и иные функции точно так же, только со всеми проверками "из коробки".
Цитата
dhead написал:
Использовать для этого временную ячейку на листе кажется не очень правильным
и между тем это самый рабочий вариант. Раз работа через макросы - то проблем в этом не вижу. Хоть на временном листе, хоть на копии текущего это можно записывать. И будет куда стабильнее и более наглядно при отладке, чем через имена.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#7
19.01.2026 14:35:27
Цитата
Sanja написал:
Вы уверены, что этот язык человеческий и привычный?

Для русскоязычного и в меру продвинутого Excel-пользователя язык формул с местной локализацией – привычный и человеческий по сравнению с буржуинским оригиналом, как это ни странно:)

Цитата
Дмитрий(The_Prist) Щербаков написал:
Если честно - плохо понимаю вообще смысл этого действия. Здесь ошибок может быть куча: начиная от разделителей и заканчивая грамматикой.

Достаточно того, что юзер умеет писать без ошибок локализованные формулы. Парсить текст и делать валидацию необязательно. Ошибется в локализованном синтаксисе - получит шиш на выходе, проверит и исправит. А с нативным придется напрягаться подольше с непривычки:)

Смысл действия попытался объяснить выше: есть прога, имеющая кучу настроек, которые динамически выводятся в виде полей в UserForm. Поле может быть текстбоксом, комбобоксом и т.д. В текстбоксах может храниться не только обычное текстовое значение, но и ссылка на ячейку или диапазон, например, а кроме того - формулы.

То есть поле может быть вычисляемым.
Все это нормально работает, но только в том варианте, который может прожевать Evaluate. А хотелось бы облегчить юзерам жизнь и дать им возможность вписывать в поля формулы так, как они привыкли (на русском языке, с разделителем ";")

По идее, если формула написана без ошибок в локальном варианте, создание имени с преобразованием из RefersToLocal в RefersTo (что давно предлагал ZVI)  должно прекрасно работать. Но почему-то не получается. А гадить в чужой лист временными ячейками с производственным мусором как-то некрасиво. Создавать\удалять или держать ради этого отдельный лист – тоже.
Изменено: dhead - 19.01.2026 14:39:06
 
 
 
#8
19.01.2026 15:30:15
Цитата
dhead написал:
гадить в чужой лист временными ячейками с производственным мусором как-то некрасиво. Создавать\удалять или держать ради этого отдельный лист – тоже.
а имена создавать красиво? :) Ну не согласен с Вами. Если имена не справляются(а они могут не пропустить из VBA строку длиной более 255 символов) - то чем плохо использование отдельного скрытого листа для таких целей? Чем он хуже создания имени с кучей нюансов? А на листе можно все эти формулы просто соединить и получить готовый вариант. Вот не понимаю я таких манипуляций. Тем более работа идет через форму - при запуске формы создали книгу, скрыли её(wb.Windows(1).Visible = False) и работайте на её листе. Перед закрытием формы - просто закрываете без сохранения и никаких следов. Ну или в самой книге либо держать отдельный скрытый лист, либо создавать и удалять. Опять же - только на время работы формы.
Ладно бы вообще нельзя было использовать листы - но ведь можно, раз можно использовать имена. Так зачем себе жизнь усложнять?  :)
Цитата
dhead написал:
Все перепробовал, не помогает.
а разделитель пробовали менять? Вот так:
Код
With ThisWorkbook.Names.Add("testname2", RefersTo:="=СУММ(Лист1!$A$1,Лист1!$A$2)")
    .RefersToLocal = "=СУММ(Лист1!A1,Лист1!A2)"
End With

Я об этом в первом своем сообщении намек делал - видимо, он не достиг цели :) это опять к слову о том, что диспетчер имен хоть и воспринимает русские названия функций, но живет по своим законам...Вот и подумайте - сможете корректно определить именно разделители аргументов для каждой произвольной функции/формулы, взятой из ячейки? А точно уверены, что у всех пользователей именно тот разделитель, который Вы считаете разделителем? Ведь у кого-то это может быть и точка-с-запятой, а у кого-то - запятая.
Изменено: Дмитрий(The_Prist) Щербаков - 19.01.2026 15:43:38
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#9
20.01.2026 02:02:28
Цитата
Дмитрий(The_Prist) Щербаков написал:
то чем плохо использование отдельного скрытого листа для таких целей? Чем он хуже создания имени с кучей нюансов?

Во первых, плохо чисто идеологически. Это как заказывать служебный автобус для того, чтобы сгонять за батоном в магазин, находящийся в твоем же доме на первом этаже:)  Да, пользователь не заметит разницы, но .. короче, вы поняли:)

Ну и в случае каких-то сбоев, которые при активном использовании Excel происходят довольно часто, допускать ошмётки в виде лишних листов в документах (путь даже очень-очень скрытых:) - плохой стиль. Невидимый документ, повисший в памяти - тоже некрасиво:) Да, я всегда использую обработку ошибок (что при сбоях поможет не всегда), плюс при следующем запуске можно будет все автоматом почистить, но это как-то неряшливо, что ли.

Лишнее скрытое уникальное имя смущает гораздо меньше. Ограничение на длину строки в данном конкретном случае не помешает, не будет там таких формул. И, если бы все работало, как оно должно работать, это было бы простое и надежное решение.

Цитата
Дмитрий(The_Prist) Щербаков написал:
.. а разделитель пробовали менять? Я об этом в первом своем сообщении намек делал - видимо, он не достиг цели

Разумеется, пробовал, я же тоже давал ссылку на описание бага от самих майкрософт:)

Цитата
Дмитрий(The_Prist) Щербаков написал:
сможете корректно определить именно разделители аргументов для каждой произвольной функции/формулы, взятой из ячейки? А точно уверены, что у всех пользователей именно тот разделитель, который Вы считаете разделителем?

Во первых, разделитель в системе пользователя определить не проблема - это xlListSeparator, как пишут в справке. Во-вторых, его и не нужно было бы определять: пользователь на своем компе вводит формулу в соответствии со своими региональными настройками, мы ее конвертим из RefersToLocal в RefersTo (если бы оно работало как заявлено, без багов) и сохраняем. У любого другого пользователя с его региональными настройками конвертим обратно и выводим в поле на понятном ему языке. При этом сохраненная формула в нативном синтаксисе всегда правильно вычисляется с помощью Evaluate.

Все точно так же будет работать с ячейками на временном листе, но, как я уже сказал, мне не нравится автобус:) Поэтому и задавал вопрос. В надежде, что кто-нибудь находил какое-нибудь третье простое решение.
Изменено: dhead - 20.01.2026 02:03:32
 
 
 
#10
20.01.2026 09:18:31
dhead, там из "автобуса" будет ровно одно "сиденье", без кабины и колёс, в воздухе
 
 
 
#11
20.01.2026 09:53:52
nilske, как сказать:) Полноценная книга со всей иерархией объектов ради одной ячейки. Разве что в воздухе. Но есть риск, что рухнет, если что-то пойдет не так:)
 
 
 
#12
20.01.2026 12:32:17
Цитата
dhead написал:
его и не нужно было бы определять: пользователь на своем компе вводит формулу в соответствии со своими региональными настройками
ну т.е. ничего Вы не поняли. Ок. Попробуем разжевать.
xlListSeparator - выдаст Вам точку-с-запятой. Но при этом RefersToLocal может его не принять и потребовать внутренний разделитель - т.е. запятую(как я показал в своем сообщении выше). И это при том, что вся формула будет написана на русской локализации, а разделитель аргументов в формулах в ячейках все та же точка-с-запятой.
Далее, даже если решились подменять разделители - представьте формулу вида:
"=СУММ(Лист;1!A1;Лист;1!A2)"
в чем проблема? выделил красным - имя листа содержит разделитель аргументов. Следовательно, простой Replace не сработает и надо будет парсить формулу. А это поверьте мне - дело весьма не благодарное.
Цитата
dhead написал:
Все точно так же будет работать с ячейками на временном листе
нет и еще раз нет. Во временный лист Вы переносите формулу именно так, как её требуется видеть в той локализации, в которой ведется работа. Не надо думать, какие там разделители требует RefersToLocal. А значит никакие разделители определять/подменять не надо. Надо просто объединить все в одну формул.
Просто попробуйте ту формулу, которую у Вас не принимает имя записать в лист через FormulaLocal. Не удивлюсь, что все запишется и будет вычислено корректно и без ошибок.
Цитата
dhead написал:
Полноценная книга со всей иерархией объектов ради одной ячейки
у Вас просто фантазии или опыта нет. Копии книги и листов лишь пара вариантов. Можно это делать и в отдельных пустых ячейках и даже(!) в уже заполненных. Просто надо понимать, в каких ячейках это можно делать, а в каких нет.
Дальше убеждать Вас в чем-то не вижу смысла - обсуждение заходит в тупик, Вам надо решение исключительно через диспетчер имен. И я почему-то уверен, что проблема именно в разделителе, хоть Вы и говорите, что пробовали его менять в формуле.
Изменено: Дмитрий(The_Prist) Щербаков - 20.01.2026 12:36:20
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
20.01.2026 13:41:54
Цитата
Дмитрий(The_Prist) Щербаков написал:
ну т.е. ничего Вы не поняли. Ок. Попробуем разжевать.

Ну вот, обвинения начались: ничего не понял и фантазии нет:))

Еще раз: я в курсе, что RefersToLocal работает с багом, сам давал ссылку на справку от майкрософт и писал, что все было бы ок с именами, если бы оно работало без багов. Поэтому согласен, что конвертировать с помощью имен с таким багом - плохая затея, это я уже понял и уже искал тут альтернативу именам и записью\чтению ячеек, о чем писал выше.

По поводу фантазии: поверьте, ее дофига, как только не извращался и чего только не делал с Excel, начиная с версии 97 года, не меньше вашего, наверное. Некоторые извращения доводил до коробочного варианта и продавал за большие доллары (с аппаратными ключами для защиты, чтобы копировать неповадно было:) Каких только внутренних багов не отлавливал, когда еще интернетов не было. То есть интернет-то был уже, только нужной инфы в нем было мало. Иногда приходишь к тому, что проще отдельную прогу написать, а Excel дергать только по мере необходимости, либо вообще забыть:)

Цитата
Дмитрий(The_Prist) Щербаков написал:
Копии книги и листов лишь пара вариантов. Можно это делать и в отдельных пустых ячейках и даже(!) в уже заполненных

Да, можно, но это чревато потерей или повреждением данных после экселевсих сбоев, остатками мусора, который потом придется чистить, как ни ухищряйся, потому что все контролировать не получится, слишком много ограничений. Тем более, что файлы чужие.

Ну или я действительно до чего-то не догадался, так поделитесь, буду благодарен.

Возможно, я перфекционист и слишком перестраховываюсь, даже скорее всего, иногда самого себя по рукам бить приходится, ведь не софт для марсианского корабля строишь.

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

Но вопрос все же остается: возможно, кто-то знает или придумает третий вариант, без записи в ячейки и без имен.
Изменено: dhead - 20.01.2026 13:43:19
 
 
 
#14
24.01.2026 04:11:47
Update.

Поторопился написать, что проблема решилась с помощью записи\чтения formulaLocal и formula временной ячейки. Да, все работает, но есть нюанс.

Подразумевается, что адреса в формулах ссылаются на листы в активной на момент вычисления книги. То есть записываются в формате 'Имя листа'!A1 без имени книги, так как заранее оно неизвестно. И тут появляется одна особенность: если на момент записи формулы в ячейку открыта книга (именно книга) с именем Имя листа, либо даже книга, в котором есть связь с книгой с именем Имя листа, то ссылка автоматом преобразуется так, что имя листа в адресе превращается в имя книги, а вместо имени листа подставляется имя первого листа из этой книги. Звучит запутанно, вот пример:

Есть открытая книга Книга.xls, в которой мы пишем в ячейку формулу. В книге нет никаких связей.
Есть книга С:\Тест\тест.xlsx с листом Лист1, которая закрыта.
Есть открытая книга Другая книга.xls, в которой есть линк на С:\Тест\тест.xlsx

Вставляем любую формулу на любом листе в любую ячейку книги Книга.xls. В формуле есть ссылка на лист тест. Например, =ЛЕВСИМВ('тест'!A1;5). И при вставке эта формула автоматом преобразуется в...  =ЛЕВСИМВ('[С:\Тест\тест.xlsx]Лист1'!A1). То есть при вставке формулы эксель судорожно начинает искать лист с именем тест в текущей и во всех открытых книгах, не находит, но зато в одной из соседних книг находит линк на книгу (не лист) с таким именем, меняет ссылку, да еще и внаглую меняет имя листа с тест на Лист1.

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

Пока все, что приходит в голову, это несколько расширить синтаксис и для страховки от подобных совпадений писать все ссылки на листы в таком формате: '[.]тест'!A1. Подразумевая под [.] активную на момент вычисления книгу. Эксель считает это ссылкой на конкретную книгу и в таком случае уже не пытается подсунуть что-то другое.
Ну а перед передачей строки в Evaluate просто удалять из нее все [.].

Или все-таки есть другие варианты обхода?
Изменено: dhead - 24.01.2026 04:42:34
 
 
 
Страницы: 1
Читают тему
Наверх