Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Как автоматически копировать формулу с заменой имени переменной
 
#1
08.09.2012 15:57:23
Допустим из внешней программы в ячейки А1, А2 Листа1 я копирую формулы типа =5+2*х+3*х^2. Надо чтобы эти формулы автоматически скопировались на Лист2: формула из ячейки А1 в ячейки 1 - 10 столбца А и вычислились беря значения Х из соответствующих ячеек столбца В, формула из ячейки А2 Листа1 - в ячейки 1 - 10 столбца С и вычислились при значениях х из соответствующих ячеек столбца D. Я так понимаю что при автоматическом копировании формулы из ячейки А1 х должен быть заменён на переменную с именем y, а из А2 на переменную с именем z и соответственно определить переменные у и z как значения в столбцах В и D.    
Подскажите, пожалуйста, как можно сделать чтобы при появлении формул в ячейках А1 и А2 автоматически производилось копирование, замена х на у и z и вычисление.
 
 
 
#2
08.09.2012 16:38:20
Ваш вариант некорректен.  
У (игрек) содержит не одно значение - это именованный диапазон с данными столбца В.  
A1=5+2*y+7*y^2 - на самом деле в вычислениях задействован весь диапазон:  
=5+2*{2:98:5:345:4:45:76:23:111:4}+7*{2:98:5:345:4:45:76:23:111:4}^2  
Если все это просуммировать и ввести как формулу массива, можно увидеть результат:  
=СУММ(5+2*y+7*y^2)=1046863  
 
Зачем столько лишнего?  
A1=5+2*B1+7*B1^2  
 
Если с этим разберетесь, пойдем дальше.
 
 
 
#3
08.09.2012 16:50:11
Мне не нужна сумма, мне нужен результат вычисления по формулам при каждом значении из столбцов В и D
 
 
 
#4
08.09.2012 16:53:06
Я Вам показал, что у Вас в каждой ячейке происходит куча лишних вычислений. Предложил заменить ссылки на имя ссылкой на ячейку.  
Предложил сначала разобраться с этим.
 
 
 
#5
08.09.2012 17:27:42
К сожалению Excel не мой конёк. Т.е. Вы предлагаете фармуле в ячейке А1 присвоить имя скажем H, а А2 имя P и на второй лист копировать Н и Р? Но как указать им откуда брать значения Х? Если вручную прописывать, то это не вариант, формул вставляется много и в каждой до 7 значений Х. Или я Вас не понимаю?
 
 
 
#6
08.09.2012 17:36:50
Не понимаете.  
Ctrl+F3 - присвоенные имена. Там два именованных диапазона.  
В Ваших формулах ссылки на эти диапазоны, т.е. фактически все формулы (например, столбца А) производят одинаковые вычисления, получая массив значений. Но, так как формула не введена, как формула массива, в ячейке мы видим только одно значение, соответствующее значению массива с порядковым номером, который задает текущая строка.
 
 
 
#7
08.09.2012 17:39:45
версия Excel у вас какая?  
до 2002-го в "параметрах" совершенно точно есть флажок "допускать названия диапазонов"  
с 2007-го этот флажок совершенно точно отменили.  
2003-й - не помню.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
08.09.2012 17:42:50
Желаемый перенос можно осуществить макросом.  
Но зачем переносить формулы? Вычисленных значений недостаточно?  
 
В файле изменены имена, теперь в каждом - ссылки на единственную ячейку. Так правильнее.  
 
Еще вариант: сразу вписать формулы-ссылки. Т.е. не перенос исходных формул, а ссылки на ячейки с этими формулами. Так принимается?
 
 
 
#9
08.09.2012 18:29:49
У меня Excel 2003, флажок "допускать названия диапазонов" тут есть, отметил, хотя пока не понял зачем.  
 
Вычисленных значений мне конечно же достаточно, даже это было бы предпочтительнее. Только вот как осуществить такой вариант. В прикреплённом файле я не вижу отличий от моего.
 
 
 
#10
08.09.2012 18:49:36
Возможно, Вы не поняли, о чем речь.  
То, что Вы называете переменными в формулах, на самом деле имена, и назвать их можно было как угодно: х, у, это_переменная... суть от этого не меняется.    
В прикрепленном примере результат тот же, но вычислений гораздо меньше - изменены именованные диапазоны. Где их посмотреть, я вам писал ранее.  
 
Вы не ответили на вопросы предыдущего сообщения:  
- зачем переносить формулы? Вычисленных значений недостаточно?  
- сразу вписать формулы-ссылки. Т.е. не перенос исходных формул, а ссылки на ячейки с этими формулами. Так принимается?  
 
И покажите в файле результат, который Вы хотели бы получить.
 
 
 
#11
08.09.2012 19:10:14
Что-то я всё больше запутываюсь.  
1. Формулы можно не переносить. Меня интересуют исключительно вычисленные результаты.  
2. А как это сделать? Ведь формулы содержат имена (X).  
3. Результат, который меня интересует это столбцы А и С с результатами вычислений по формулам.
 
 
 
#12
08.09.2012 19:15:14
Дополню пункт 2. Ведь скопированная из внешней программы и вставленная сюда формула содержит имя Х, которое равно величине из столбца В или D в зависимости от того в каком столбце (А или С)производится вычисление.
 
 
 
#13
08.09.2012 19:17:46
Покажите результат в файле, иначе непонятно:  
>>формула из ячейки А1 в ячейки 1 - 10 столбца А и вычислились беря значения Х из соответствующих ячеек столбца В...  
Подразумевается столбец В исходного листа? но там "переменная" y... Второго листа? Но там пусто...
 
 
 
#14
08.09.2012 20:21:30
Вот файл с пояснениями.
 
 
 
#15
08.09.2012 21:18:51
Извините, во многом я Вас запутал: понял, что нужно вставлять из Лист2 на Лист1.  
 
Пока нет специалистов: макрос, вставляющий формулы (но для файла со стилем ссылок R1C1):  
 
Sub Insert_Formula()  
Dim Formula1 As String  
Dim Formula2 As String  
 
   With Worksheets("Лист1")  
       Formula1 = .Range("A1").FormulaR1C1  
       Formula2 = .Range("A2").FormulaR1C1  
   End With  
 
   With Worksheets("Лист2")  
       .Range("A1:A10").Value = Formula1  
       .Range("C1:C10").Value = Formula2  
         
       .Range("A1:A10").Replace What:="x", Replacement:="RC[1]"
       .Range("C1:C10").Replace What:="x", Replacement:="RC[1]"
   End With  
End Sub
 
 
 
#16
08.09.2012 21:34:32
Можно в конце процедуры заменить формулы на значения.  
Можно было не вставлять формулы - сразу значения, но, кроме как пройтись циклом или сработать с массивом, не знаю.  
Думаю, Вам в этом помогут. Или я позже напишу.  
 
Мне интересно, как вставить формулы со смещением ссылки для стиля ссылок А1.
 
 
 
#17
08.09.2012 22:08:09
Вставить значения:  
Sub Insert_Value()  
Dim Formula1 As String  
Dim Formula2 As String  
Dim i&  
 
   With Worksheets("Лист1")  
       Formula1 = .Range("A1").FormulaR1C1  
       Formula2 = .Range("A2").FormulaR1C1  
   End With  
 
   With Worksheets("Лист2")  
       For i = 1 To 10  
           .Cells(i, 1).Value = Formula1  
           .Cells(i, 1).Replace What:="x", Replacement:=.Cells(i, 2).Value  
           .Cells(i, 1).Value = .Cells(i, 1).Value  
       Next i  
         
       For i = 1 To 10  
           .Cells(i, 3).Value = Formula2  
           .Cells(i, 3).Replace What:="x", Replacement:=.Cells(i, 4).Value  
           .Cells(i, 3).Value = .Cells(i, 3).Value  
       Next i  
   End With  
End Sub
 
 
 
#18
08.09.2012 22:23:19
Вставить значения (вариант 2)  
 
Sub Insert_Value_2 ()  
Dim Formula1 As String  
Dim Formula3 As String  
Dim i&, j&  
 
   With Worksheets("Лист1")  
       Formula1 = .Range("A1").FormulaR1C1  
       Formula3 = .Range("A2").FormulaR1C1  
   End With  
 
   With Worksheets("Лист2")  
       For j = 1 To 3 Step 2  
           If j = 3 Then Formula1 = Formula3  
           For i = 1 To 10  
               .Cells(i, j).Value = Formula1  
               .Cells(i, j).Replace What:="x", Replacement:=.Cells(i, j + 1).Value  
               .Cells(i, j).Value = .Cells(i, j).Value  
           Next i  
       Next j  
   End With  
End Sub
 
 
 
#19
08.09.2012 23:11:18
Работает!  
А возможно сделать так чтобы это проискодило автоматически, т.е. на первом листе ввёл формулу, а на втором всё посчиталось, или изменил любое значение х и тоже все автоматически посчиталось. А то после любого изменения приходится запускать макрос.
 
 
 
#20
08.09.2012 23:25:57
Вариант: пользовательская функция:
 
 
 
#21
08.09.2012 23:36:30
Лучше (наверное):  
F = Trim(Replace(Replace(CStr(Ячейка_с_текстом_формулы.Cells(1)), ",", "."), "'", ""))
 
 
 
#22
08.09.2012 23:38:58
Вариант с событием листа. В модуле Лист1:  
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
   If Not Application.Intersect(Range("A1:A2"), Target) Is Nothing Then  
       Call Insert_Value2  
   End If  
End Sub
 
 
 
#23
09.09.2012 00:42:14
А я думал, что я Excel более-менее знаю.:(  
 
to vikttur  макрос работает, если происходит изменение формулы на листе 1, но возможно же ещё и изменение значений х. Для исправления этого вероятно надо записать этот макрос в VBAProject ЭтаКнига?  
   
to C.M. А что изменяет эта строчка? F = Trim(Replace(Replace(CStr(Ячейка_с_текстом_формулы.Cells(1)), ",", "."), "'", ""))  
И ещё. А как сделать что бы на первом листе формулы оставить в виде формул, а не переводить их в текст?
 
 
 
#24
09.09.2012 01:09:44
> "А что изменяет эта строчка?"  
Считывает значение ячейки (текст формулы) в VBA-переменную F, при этом заменяет (в русской локали) запятую (в числах) на точку и, на всякий случай, апостроф - на пустую строку.  
 
> "А как сделать что бы на первом листе формулы оставить в виде формул, а не переводить их в текст?"  
А зачем ? ИМХО, лучше ВИДЕТЬ формулу, а не непонятное "#ИМЯ?"
 
 
 
#25
09.09.2012 01:24:38
Согласен, видеть формулу лучше, но когда я вставляю скопированную формулу у которой первое значение отрицательное Excel автоматически ставит перед формулой знак =. Тогда как сделать что бы он этого не делал?
 
 
 
#26
09.09.2012 02:25:49
Перед вставкой задайте ячейкам текстовый формат.
 
 
 
#27
09.09.2012 03:03:10
Спасибо, ребята!  
Всё работает. Завтра продолжу писать программу, пока вроде все понятно.
 
 
 
#28
09.09.2012 03:27:36
> "Перед вставкой задайте ячейкам текстовый формат."  
 
Или макросом [из темы "Как автоматически ставить знак = перед формулой", но наоборот :-)]
Sub Formula_To_Text()  
   Dim Cell As Range  
   For Each Cell In Selection.Cells  
       If Cell.HasFormula Then  
           Cell = Trim(Mid(Cell.Formula, 2)) ' удаляем знак "=" '  
       End If  
   Next  
End Sub
 
 
 
#29
09.09.2012 22:53:01
to C.M. Неожиданная проблемма. Если y или z не целое число, т.е. с точкой или запятой, неважно, то в результате получаю #ЗНАЧ!
 
 
 
#30
10.09.2012 03:04:04
Вариант без применения кода, но макросы должны быть разрешены - применена макрофункция ВЫЧИСЛИТЬ().  
Ячейкам с исходными формулами предварительно присвоен текстовый формат.  
Ctrl+F3 - формулы с макрофункциями там.
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх