Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Фиксация результата формулы
 
На листе 1, к значениям проставляемых в столбцах ABCDE, с помощью ВПР, подтягиваются значения с листа 2.

Как сделать так что бы подтянувшись раз они фиксировались, и при дальнейшем изменении данных на листе 2, не менялись на листе 1 ?
Очень нужно как то придумать следующую схему:
1. На листе 1 вводим данные, в новую строку, к ним подтягиваются данные с листа 2.
2. Результат фиксируется.  
3. На листе 2 обновляются данные (при этом на листе 1 изменения не происходят)
4. При введении данных на новой строке листа 1, данные снова подтягиваются.
5. Результат фиксируется.

В общем нужно как то сделать одноразовый ВПР :) Возможно ли такое в Екселе?  
Изменено: Biblebrox - 1 Апр 2015 12:05:56
 
установить число итераций при вычислениях в 1
написать формулу, ссылающуюся на саму себя( на свою ячейку) - если ячейка пуста, то подтянуть данные с другого листа, в прот случае ничего не делать
Живи и дай жить..
 
Цитата
Слэн написал: установить число итераций при вычислениях в 1
Нашел только как это сделать для всего листа. А как это сделать только для фомулы ВПР в столбцах BDFHJ листа 1 ?
 
В принципе, если строка на Лист1 в столбцах А, С, Е и т.д. заполняется полностью (т.е. отсутствует такая ситуация, когда в А 4 значения, а в С 5), то можно применить простенький макрос:

Код
Sub FixigValue()
Dim lngI As Long
    For lngI = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(lngI, 2) = Cells(lngI, 2).Value: Cells(lngI, 4) = Cells(lngI, 4).Value
        Cells(lngI, 6) = Cells(lngI, 6).Value: Cells(lngI, 8) = Cells(lngI, 8).Value
        Cells(lngI, 10) = Cells(lngI, 10).Value
    Next
End Sub
Кому решение нужно - тот пример и рисует.
 
Самый естественный способ - макросом вставить формулы и заменить их на значения.
Пример с ВПР, достающей значения из закрытой или открытой книги: http://www.cyberforum.ru/ms-excel/thread1405798.html
 
Оооо, спс Пытливый, Казанский,  сейчас буду тестить  
 
Пытливый, как то странно работает. До четвертой строки отрабатывает хорошо. Подтянув в ячейку данные убивает формулу. А дальше четвертой строки почему то не работает.

Не посмотрите файл. Где ошибка?  
 
сейчас ввел в 6 строку все данные в А, С, E, G, I, запустил макрос, формула заменилась на значение с 2 до 6 строки.
Ниже формула не трогается, т.к. пока не введены данные в указанных столбцах, там должна быть формула, макрос обрабатывает строки, заполненные значениями в столбце А.

А что не устраивает? НД? Тогда надо формулу немного изменить, вместо:
Код
=ВПР(A:A;Лист2!A:B;2;0)
надо 
=ЕСЛИОШИБКА(ВПР(A:A;Лист2!A:B;2;0);"")

Тогда и НД в незаполненных строках лезть не будут, и реакция на введение данных не пропадет.

З.Ы. Я процедуру в модуль запихивал и оттуда запускал, а из модуля листа действительно, почему-то ругань.... хм....
"end of statement" ему... ишь....
Изменено: Пытливый - 1 Апр 2015 14:28:19
Кому решение нужно - тот пример и рисует.
 
Пытливый, вы что то меняли в файле? У меня ни в какую не хочет :( Можете мне прислать вашу версию.

И еще , возможно ли сделать так что бы данный скрипт выполнялся автоматически, а не запускался вручную  
 
1.Копия с работающим кодом - прилагаю (код макроса - в стандартном модуле, а не в модуле листа)
2.Можно присобачить запуск кода к какому-либо событию листа, например, изменению в ячейке. Просто надо решить, к какому.
Например, если его "привязать" к изменению значений ячеек в столбце А, то только вы ввели чего в А столбце - он сработал и "убил" формулы (заменил на значения) во всех соответствующих ячейках строки, причем в С, Е и т.д. вы значения еще не вводили. А формулы там уже убитые.
Я поэтому и спрашивал, бывает такая ситуация, когда строка в А, С, Е и т.д. заполнена не вся, т.е. в А заполнено 4 строки, а в С - 3?
Если бывает, и надо убивать конкретно при введении кода в соответствующем столбце - понадобиться слегка подправить макрос и привязать его к соответствующему событию листа.
Если надо - напишите об этом.
Изменено: Пытливый - 1 Апр 2015 19:17:36
Кому решение нужно - тот пример и рисует.
 
Цитата
Пытливый написал: 2.Можно присобачить запуск кода к какому-либо событию листа, например, изменению в ячейке. Просто надо решить, к какому.
У меня к ячейкам столбца А уже привязан макрос, который реагирует на изменение этих ячеек. Если сюда еще привязать - макросы не будут конфликтовать?
Был бы крайне благодарен, чему-то вроде этого:
При добавлении данных в А3, скрипт убивает формулу в B2, D2, F2, H2, J2, L2, N2,
При добавлении данных в А4, скрипт убивает формулу в B3, D3, F3, H3, J3, L3, N3
и т.д.
Изменено: Biblebrox - 2 Апр 2015 00:20:04
 
Посмотрите измененный макрос в модуле лист1.
При изменении в А "убивает" формулы в предшествующей строке в заданных столбцах.

Чтобы понять - будут, или не будут конфликтовать - надо посмотреть на Ваш макрос.
Кому решение нужно - тот пример и рисует.
 
Цитата
Самый естественный способ - макросом вставить формулы и заменить их на значения
.Мне всегда казалось, что при использовании макросов, естественее всего от формул отказаться вовсе, считать все в коде и выгружать на лист готовые значения. Тем более по теме вроде как и нужны именно значения. ИМХО
Согласие есть продукт при полном непротивлении сторон.
 
Цитата
Sanja написал: Мне всегда казалось, что при использовании макросов, естественее всего от формул отказаться вовсе
Зависит от задачи, конечно.
Если нужно получить результат на листе И это можно сделать несложной формулой, то почему бы нет?
Попробуйте сделать то, что по ссылке (ВПР с данными из закрытой книги) - без формул. Сколько строк в коде будет, сколько времени его писать и отлаживать?
 
Алексей, ну это крайне редко, когда нужно что-то типа ВПР с данными из закрытой книги - как правило, простейшие вычисления пытаются сделать формулой, которую нужно макросом размножить))
 
Пытливый, Шикарно. Все работает. Спасибо!

Объединил 2 макроса на 1 листе, но вначале вылезала ошибка ambiguous name detected worksheet_change
Потом понял,  что матерится на одинаковые имена. В итоге засунул оба кода в одну процедуру и все получилось :
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("a")) Is Nothing Then Exit Sub
    lr = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets(2).Range("A" & lr, "M" & lr) = Me.[R12:AD12].Value
    
    Dim intI As Integer
    If Target.Cells.Count > 1 Then
        Exit Sub
    ElseIf Not Intersect(Target, Range("A:A")) Is Nothing Then
'        Call FixigValue
        For intI = 2 To 14 Step 2
            Cells(Target.Row - 1, intI) = Cells(Target.Row - 1, intI).Value
        Next intI
    End If
    
End Sub

Если не сложно, поясните пож-та, не до конца ясен механизм.

Я понял что за то, какие именно ячейки нужно очистить от формул  - B, D, F, H, J, L, N,  отвечает эта часть кода:
Код
For intI = 2 To 14 Step 2
Скажите а где тут указано что нужно убивать в вышестоящей строке, а не в той в которой происходит событие?  
 
Row - 1
 
Код
Cells(Target.Row - 1, intI) = Cells(Target.Row - 1, intI).Value

Вот эта часть кода заменяет формулу на ее значение.
Как совершенно верно указал ув.Юрий М, в данной строке номер строки изменяемой ячейки (Target) уменьшается на 1, т.е. работаем с предыдущей строкой. А переменная itnI определяет, с каким столбцом работаем - она циклически меняется от 2 до 14 с шагом 2, т.е. принимает значения 2, 4, 6 и т.д.
Кому решение нужно - тот пример и рисует.
 
Пытливый, спасибо
Страницы: 1
Читают тему (гостей: 1)