Нужно чтобы из формулы, например, H49+(C26-C23)*F45+(C25-C22)*F46 ячейки заменились на их значения, чтобы получилось в итоге в отдельной ячейке выражение. Например, 477+(68-713)*0,9-(52-513)*0,02. Я использую костыли наподобие & и " . В итоге получается формула для конкретно этой ячейки =H48&"+("&C25&"-"&C22&")*"&F45&"-("&C26&"-"&C23&")*"&F46. Хотелось бы узнать может есть более короткий способ сделать такого вида выражения из ячейки с формулой?
Нужно чтобы из формулы, например, H49+(C26-C23)*F45+(C25-C22)*F46 ячейки заменились на их значения, чтобы получилось в итоге в отдельной ячейке выражение. Например, 477+(68-713)*0,9-(52-513)*0,02. Я использую костыли наподобие & и " . В итоге получается формула для конкретно этой ячейки =H48&"+("&C25&"-"&C22&")*"&F45&"-("&C26&"-"&C23&")*"&F46. Хотелось бы узнать может есть более короткий способ сделать такого вида выражения из ячейки с формулой?nikitamce2k16
двойной клик левой клавишей мыши. макрос в модуле листа: [vba]
Код
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) u = Target.FormulaR1C1 a = Replace(u, "R", "") b = Len(u) - Len(a) If b > 0 Then On Error Resume Next d = u For c = b To 1 Step -1 e = InStrRev(d, "R") f = InStrRev(d, "]") h = f - e + 1 g = Mid(d, e, f - e + 1) 'rc i_r = InStr(g, "[") j_r = InStr(g, "]") k_r = Mid(g, i_r + 1, j_r - i_r - 1) 'r i_c = InStrRev(g, "[") j_c = InStrRev(g, "]") k_c = Mid(g, i_c + 1, j_c - i_c - 1) 'c l = Target.Offset(k_r, k_c) If l = "" Then l = 0 d = Replace(d, g, l) Next Target = "'" & d End If Cancel = True End Sub
[/vba]
[p.s.]апдэйт с $ работать не будет, но и в примере их не было[/p.s.]
двойной клик левой клавишей мыши. макрос в модуле листа: [vba]
Код
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) u = Target.FormulaR1C1 a = Replace(u, "R", "") b = Len(u) - Len(a) If b > 0 Then On Error Resume Next d = u For c = b To 1 Step -1 e = InStrRev(d, "R") f = InStrRev(d, "]") h = f - e + 1 g = Mid(d, e, f - e + 1) 'rc i_r = InStr(g, "[") j_r = InStr(g, "]") k_r = Mid(g, i_r + 1, j_r - i_r - 1) 'r i_c = InStrRev(g, "[") j_c = InStrRev(g, "]") k_c = Mid(g, i_c + 1, j_c - i_c - 1) 'c l = Target.Offset(k_r, k_c) If l = "" Then l = 0 d = Replace(d, g, l) Next Target = "'" & d End If Cancel = True End Sub
[/vba]
[p.s.]апдэйт с $ работать не будет, но и в примере их не было[/p.s.]Nic70y
cmivadwot, Мне для отчета по учебе нужно много выражений в ворд записывать, раскрывая решение какой-либо формулы, а там может быть 10,15 и тд чисел. И муторно переключаться туда сюда 100 раз чтобы писать каждое число для выражения
cmivadwot, Мне для отчета по учебе нужно много выражений в ворд записывать, раскрывая решение какой-либо формулы, а там может быть 10,15 и тд чисел. И муторно переключаться туда сюда 100 раз чтобы писать каждое число для выраженияnikitamce2k16
Существует еще клавиша F9, с помощью которой в режиме редактирования формулы внутри ячейки можно превратить любой выделенный фрагмент этой формулы в локальный результат вычисления (и сохранить в любом промежуточном виде). Можно выделять как отдельный адрес внутри ячейки (например, H49), так и какое-либо действие, связывающее два и более адреса (например, (C26-C23)*F45 ).
Можно делать выделения внутри формулы стрелочками с Shift'ом, но тогда долго тянуться за клавишей F9. А можно взять в правую руку мышку и дабл-кликать ею на адресах, а левой рукой жать F9 - так половчее будет.
Существует еще клавиша F9, с помощью которой в режиме редактирования формулы внутри ячейки можно превратить любой выделенный фрагмент этой формулы в локальный результат вычисления (и сохранить в любом промежуточном виде). Можно выделять как отдельный адрес внутри ячейки (например, H49), так и какое-либо действие, связывающее два и более адреса (например, (C26-C23)*F45 ).
Можно делать выделения внутри формулы стрелочками с Shift'ом, но тогда долго тянуться за клавишей F9. А можно взять в правую руку мышку и дабл-кликать ею на адресах, а левой рукой жать F9 - так половчее будет.Gustav
nikitamce2k16, смеха ради сделал на PQ. В А1 вводите формулу, потом сохраняете файл (Ctrl-S) и жмете Refresh All (данные - обновить все). На любые изменения (значений в ячейках или самой формулы) надо сохранить файл Ctrl-S и обновить все. UPD: должен правильно работать только в диапазоне A1 - Z99 [vba]
Код
let param = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], formula_string = param[formula]{0}, file_path = param[location]{0}, Source = Excel.Workbook(File.Contents(file_path), null, true){[Item="формула",Kind="Sheet"]}[Data], rename_columns = Table.FromRows(Table.ToRows(Source), List.FirstN({"A".."Z"}, Table.ColumnCount(Source))), ind = Table.AddIndexColumn(rename_columns, "Index", 1, 1, Int64.Type), ind_text = Table.TransformColumnTypes(ind,{{"Index", type text}}), unpivot = Table.UnpivotOtherColumns(ind_text, {"Index"}, "Attribute", "Value"), cells = Record.FromTable(Table.CombineColumns(unpivot,{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Name")), formula_no_dollar = Text.Remove(formula_string, "$"), cells_ref = List.Buffer(Record.FieldNames(cells)), result = List.Accumulate( cells_ref, formula_no_dollar, (s, c) => Text.Replace(s, c, Text.From(Record.FieldOrDefault(cells, c, ""))) ) in result
[/vba]
nikitamce2k16, смеха ради сделал на PQ. В А1 вводите формулу, потом сохраняете файл (Ctrl-S) и жмете Refresh All (данные - обновить все). На любые изменения (значений в ячейках или самой формулы) надо сохранить файл Ctrl-S и обновить все. UPD: должен правильно работать только в диапазоне A1 - Z99 [vba]
Код
let param = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], formula_string = param[formula]{0}, file_path = param[location]{0}, Source = Excel.Workbook(File.Contents(file_path), null, true){[Item="формула",Kind="Sheet"]}[Data], rename_columns = Table.FromRows(Table.ToRows(Source), List.FirstN({"A".."Z"}, Table.ColumnCount(Source))), ind = Table.AddIndexColumn(rename_columns, "Index", 1, 1, Int64.Type), ind_text = Table.TransformColumnTypes(ind,{{"Index", type text}}), unpivot = Table.UnpivotOtherColumns(ind_text, {"Index"}, "Attribute", "Value"), cells = Record.FromTable(Table.CombineColumns(unpivot,{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Name")), formula_no_dollar = Text.Remove(formula_string, "$"), cells_ref = List.Buffer(Record.FieldNames(cells)), result = List.Accumulate( cells_ref, formula_no_dollar, (s, c) => Text.Replace(s, c, Text.From(Record.FieldOrDefault(cells, c, ""))) ) in result
AlienSphinx, Спасибо большое . правда вначале писало ошибку "запрос ссылается на другие запросы и поэтому не может напрямую обратится к источнику данных. Измените эту комбинацию." Но затем я изменил настройки конфиденциальности power query и всё заработало.
AlienSphinx, Спасибо большое . правда вначале писало ошибку "запрос ссылается на другие запросы и поэтому не может напрямую обратится к источнику данных. Измените эту комбинацию." Но затем я изменил настройки конфиденциальности power query и всё заработало.nikitamce2k16
nikitamce2k16, да не за что! Хорошо, что быстро разобрались. Значит с PQ знакомы Тогда сможете, при желании, поменять код так, чтобы можно было сразу несколько. формул обрабатывать. Для этого надо сделать примерно так: - передавать в запрос список формул в виде текста, преобразовать его в запросе в список - убрать из текста в этом списке знаки доллара List.Transform(my_list, each Text.Remove(Text.From(_), "$")) - строку result превратить в функцию - пройтись по списку формул вот так: List.Transform(formula_list, each result(_)) Удачи!
nikitamce2k16, да не за что! Хорошо, что быстро разобрались. Значит с PQ знакомы Тогда сможете, при желании, поменять код так, чтобы можно было сразу несколько. формул обрабатывать. Для этого надо сделать примерно так: - передавать в запрос список формул в виде текста, преобразовать его в запросе в список - убрать из текста в этом списке знаки доллара List.Transform(my_list, each Text.Remove(Text.From(_), "$")) - строку result превратить в функцию - пройтись по списку формул вот так: List.Transform(formula_list, each result(_)) Удачи!AlienSphinx
Сообщение отредактировал AlienSphinx - Суббота, 11.03.2023, 12:25
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) l = Replace(Target.Formula, "$", "") Target = l u = Target.FormulaR1C1 a = Replace(u, "R", "") b = Len(u) - Len(a) t = Replace(u, "RC", "R[0]C") v = Replace(t, "C", "C[0]") w = Replace(v, "C[0][", "C[") If b > 0 Then On Error Resume Next d = w For c = b To 1 Step -1 e = InStrRev(d, "R") f = InStrRev(d, "]") h = f - e + 1 g = Mid(d, e, f - e + 1) 'rc i_r = InStr(g, "[") j_r = InStr(g, "]") k_r = Mid(g, i_r + 1, j_r - i_r - 1) 'r i_c = InStrRev(g, "[") j_c = InStrRev(g, "]") k_c = Mid(g, i_c + 1, j_c - i_c - 1) 'c l = Target.Offset(k_r, k_c) If l = "" Then l = 0 d = Replace(d, g, l) Next Target = "'" & d End If Cancel = True End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) l = Replace(Target.Formula, "$", "") Target = l u = Target.FormulaR1C1 a = Replace(u, "R", "") b = Len(u) - Len(a) t = Replace(u, "RC", "R[0]C") v = Replace(t, "C", "C[0]") w = Replace(v, "C[0][", "C[") If b > 0 Then On Error Resume Next d = w For c = b To 1 Step -1 e = InStrRev(d, "R") f = InStrRev(d, "]") h = f - e + 1 g = Mid(d, e, f - e + 1) 'rc i_r = InStr(g, "[") j_r = InStr(g, "]") k_r = Mid(g, i_r + 1, j_r - i_r - 1) 'r i_c = InStrRev(g, "[") j_c = InStrRev(g, "]") k_c = Mid(g, i_c + 1, j_c - i_c - 1) 'c l = Target.Offset(k_r, k_c) If l = "" Then l = 0 d = Replace(d, g, l) Next Target = "'" & d End If Cancel = True End Sub
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
. правда вначале писало ошибку "запрос ссылается на другие запросы и поэтому не может напрямую обратится к источнику данных. Измените эту комбинацию." Но затем я изменил настройки конфиденциальности power query и всё заработало.
Тогда сможете, при желании, поменять код так, чтобы можно было сразу несколько. формул обрабатывать. Для этого надо сделать примерно так:
