Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Заменить ячейки в формуле на их значения для выражения - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Заменить ячейки в формуле на их значения для выражения (Формулы/Formulas)
Заменить ячейки в формуле на их значения для выражения
nikitamce2k16 Дата: Четверг, 09.03.2023, 22:40 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Нужно чтобы из формулы, например, 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
Дата добавления - 09.03.2023 в 22:40
cmivadwot Дата: Четверг, 09.03.2023, 23:03 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 439
Репутация: 86 ±
Замечаний: 0% ±

365
nikitamce2k16,
Код
=СЦЕПИТЬ(H48;"+(";C25;"-";C22;")*";F45;"-(";C26;"-";C23;")*";F46)

не совсем короче. и еще варианты...в файле.
К сообщению приложен файл: scepka4.xlsx (12.4 Kb)


Сообщение отредактировал cmivadwot - Пятница, 10.03.2023, 12:14
 
Ответить
Сообщениеnikitamce2k16,
Код
=СЦЕПИТЬ(H48;"+(";C25;"-";C22;")*";F45;"-(";C26;"-";C23;")*";F46)

не совсем короче. и еще варианты...в файле.

Автор - cmivadwot
Дата добавления - 09.03.2023 в 23:03
nikitamce2k16 Дата: Пятница, 10.03.2023, 10:54 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, А существует ли комбинация клавиш, которая вставляет или обособляет например +( знаком " или &?
 
Ответить
Сообщениеcmivadwot, А существует ли комбинация клавиш, которая вставляет или обособляет например +( знаком " или &?

Автор - nikitamce2k16
Дата добавления - 10.03.2023 в 10:54
cmivadwot Дата: Пятница, 10.03.2023, 12:16 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 439
Репутация: 86 ±
Замечаний: 0% ±

365
nikitamce2k16,
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
А существует ли комбинация клавиш, которая вставляет или обособляет например +( знаком " или &?

наверно макросом можно сделать... но не я. для чего это всё?


Сообщение отредактировал cmivadwot - Пятница, 10.03.2023, 12:19
 
Ответить
Сообщениеnikitamce2k16,
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
А существует ли комбинация клавиш, которая вставляет или обособляет например +( знаком " или &?

наверно макросом можно сделать... но не я. для чего это всё?

Автор - cmivadwot
Дата добавления - 10.03.2023 в 12:16
elovkov Дата: Пятница, 10.03.2023, 12:40 | Сообщение № 5
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
комбинация клавиш


Для & - alt+38 на цифровой клавиатуре


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
 
Ответить
Сообщение
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
комбинация клавиш


Для & - alt+38 на цифровой клавиатуре

Автор - elovkov
Дата добавления - 10.03.2023 в 12:40
Nic70y Дата: Пятница, 10.03.2023, 13:14 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 8702
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
двойной клик левой клавишей мыши.
макрос в модуле листа:
[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.]
К сообщению приложен файл: scepka4.xlsm (17.3 Kb)


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Пятница, 10.03.2023, 14:04
 
Ответить
Сообщениедвойной клик левой клавишей мыши.
макрос в модуле листа:
[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
Дата добавления - 10.03.2023 в 13:14
nikitamce2k16 Дата: Пятница, 10.03.2023, 23:19 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, Мне для отчета по учебе нужно много выражений в ворд записывать, раскрывая решение какой-либо формулы, а там может быть 10,15 и тд чисел. И муторно переключаться туда сюда 100 раз чтобы писать каждое число для выражения
 
Ответить
Сообщениеcmivadwot, Мне для отчета по учебе нужно много выражений в ворд записывать, раскрывая решение какой-либо формулы, а там может быть 10,15 и тд чисел. И муторно переключаться туда сюда 100 раз чтобы писать каждое число для выражения

Автор - nikitamce2k16
Дата добавления - 10.03.2023 в 23:19
Gustav Дата: Суббота, 11.03.2023, 00:07 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2695
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
существует ли комбинация клавиш
Существует еще клавиша F9, с помощью которой в режиме редактирования формулы внутри ячейки можно превратить любой выделенный фрагмент этой формулы в локальный результат вычисления (и сохранить в любом промежуточном виде). Можно выделять как отдельный адрес внутри ячейки (например, H49), так и какое-либо действие, связывающее два и более адреса (например, (C26-C23)*F45 ).

Можно делать выделения внутри формулы стрелочками с Shift'ом, но тогда долго тянуться за клавишей F9. А можно взять в правую руку мышку и дабл-кликать ею на адресах, а левой рукой жать F9 - так половчее будет.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата nikitamce2k16, 10.03.2023 в 10:54, в сообщении № 3 ()
существует ли комбинация клавиш
Существует еще клавиша F9, с помощью которой в режиме редактирования формулы внутри ячейки можно превратить любой выделенный фрагмент этой формулы в локальный результат вычисления (и сохранить в любом промежуточном виде). Можно выделять как отдельный адрес внутри ячейки (например, H49), так и какое-либо действие, связывающее два и более адреса (например, (C26-C23)*F45 ).

Можно делать выделения внутри формулы стрелочками с Shift'ом, но тогда долго тянуться за клавишей F9. А можно взять в правую руку мышку и дабл-кликать ею на адресах, а левой рукой жать F9 - так половчее будет.

Автор - Gustav
Дата добавления - 11.03.2023 в 00:07
nikitamce2k16 Дата: Суббота, 11.03.2023, 10:28 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, а в какое место 2 раза кликать? просто у меня ничего не происходит
 
Ответить
СообщениеNic70y, а в какое место 2 раза кликать? просто у меня ничего не происходит

Автор - nikitamce2k16
Дата добавления - 11.03.2023 в 10:28
Nic70y Дата: Суббота, 11.03.2023, 10:44 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 8702
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
nikitamce2k16, в моем файле в столбце B формулы, по этим ячейкам и кликайте
все не правильно((
переделывать макрос надо.


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 11.03.2023, 11:42
 
Ответить
Сообщениеnikitamce2k16, в моем файле в столбце B формулы, по этим ячейкам и кликайте
все не правильно((
переделывать макрос надо.

Автор - Nic70y
Дата добавления - 11.03.2023 в 10:44
AlienSphinx Дата: Суббота, 11.03.2023, 10:51 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

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]
К сообщению приложен файл: 54321.xlsx (18.6 Kb)


Сообщение отредактировал AlienSphinx - Суббота, 11.03.2023, 11:39
 
Ответить
Сообщение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]

Автор - AlienSphinx
Дата добавления - 11.03.2023 в 10:51
nikitamce2k16 Дата: Суббота, 11.03.2023, 11:51 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

AlienSphinx, Спасибо большое :D . правда вначале писало ошибку "запрос ссылается на другие запросы и поэтому не может напрямую обратится к источнику данных. Измените эту комбинацию." Но затем я изменил настройки конфиденциальности power query и всё заработало.
К сообщению приложен файл: 2763155.jpg (65.7 Kb)
 
Ответить
СообщениеAlienSphinx, Спасибо большое :D . правда вначале писало ошибку "запрос ссылается на другие запросы и поэтому не может напрямую обратится к источнику данных. Измените эту комбинацию." Но затем я изменил настройки конфиденциальности power query и всё заработало.

Автор - nikitamce2k16
Дата добавления - 11.03.2023 в 11:51
AlienSphinx Дата: Суббота, 11.03.2023, 12:09 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

nikitamce2k16, да не за что! Хорошо, что быстро разобрались. Значит с PQ знакомы :) Тогда сможете, при желании, поменять код так, чтобы можно было сразу несколько. формул обрабатывать. Для этого надо сделать примерно так:
- передавать в запрос список формул в виде текста, преобразовать его в запросе в список
- убрать из текста в этом списке знаки доллара List.Transform(my_list, each Text.Remove(Text.From(_), "$"))
- строку result превратить в функцию
- пройтись по списку формул вот так: List.Transform(formula_list, each result(_))
Удачи!


Сообщение отредактировал AlienSphinx - Суббота, 11.03.2023, 12:25
 
Ответить
Сообщениеnikitamce2k16, да не за что! Хорошо, что быстро разобрались. Значит с PQ знакомы :) Тогда сможете, при желании, поменять код так, чтобы можно было сразу несколько. формул обрабатывать. Для этого надо сделать примерно так:
- передавать в запрос список формул в виде текста, преобразовать его в запросе в список
- убрать из текста в этом списке знаки доллара List.Transform(my_list, each Text.Remove(Text.From(_), "$"))
- строку result превратить в функцию
- пройтись по списку формул вот так: List.Transform(formula_list, each result(_))
Удачи!

Автор - AlienSphinx
Дата добавления - 11.03.2023 в 12:09
Nic70y Дата: Понедельник, 13.03.2023, 07:34 | Сообщение № 14
Группа: Друзья
Ранг: Экселист
Сообщений: 8702
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
переделывать макрос надо
переделал, раз взялся:
[vba]
Код
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
[/vba]
К сообщению приложен файл: scepka5.xlsm (17.8 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
переделывать макрос надо
переделал, раз взялся:
[vba]
Код
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
[/vba]

Автор - Nic70y
Дата добавления - 13.03.2023 в 07:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Заменить ячейки в формуле на их значения для выражения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!