Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Создание пользовательсткой функции, если ячейки в формате R1C1, Смотрел материал по созданию пользовательской функции... попробовал, не получается... думаю причина в том, что формат R1C1
 
#1
23.05.2016 12:59:57
Друзья, сделайте, пожалуйста, подсказу.
Прочитал материал (правда уже потерял ссылку), что можно создать собственную функцию WhaTIF, что мне и нужно, собственно.
История такая... Ты вводишь ячейку, которую отслеживаешь, ячейку, которая изменяется и новое значение для изменяемой ячейки.
Например:
A1=1
A2=2
A3=A1+A2
A4=WHATIF(A3;A1;A5)
A5=4

В ячейке A3 получится 3, а в ячейке A4 - 6.

Был приведён текст этой функции
Код
Function WHATIF(output_ref As Range, input_ref As Range, input_value)
    WHATIF = Evaluate(Replace(Application.ConvertFormula(output_ref.Formula, xlA1, xlA1, xlRelative), input_ref.Address(False, False), input_value))
End Function
Не работает у меня что-то.
Полагаю, что в связи с тем, что формат ссылок задан R1C1.
Помогите поправить, пожалуйста.
Изменено: Kirill Gureev - 23.05.2016 13:01:08
 
 
 
#2
23.05.2016 13:39:43
так попробуйте
Код
Function WHATIF(output_ref As Range, input_ref As Range, input_value)
    WHATIF = Evaluate(Replace(Application.ConvertFormula(output_ref.Formula, xlA1, Application.ReferenceStyle, xlRelative), _
                              input_ref.Address(False, False, Application.ReferenceStyle, , Application.Caller), input_value))
End Function
Надстройки для Excel  Парсинг сайтов  Мониторинг цен конкурентов
 
 
 
#3
23.05.2016 13:57:25
Код
Function WHATIF(output_ref As Range, input_ref As Range, input_value)
    WHATIF = Application.Evaluate(Replace(Replace(output_ref.Formula, "$", ""), input_ref.Address(0, 0), input_value))
End Function
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
23.05.2016 14:00:56
Хотя, конечно, в любом случае подход неверный. Легко убедиться, если ввести в A3 формулу:
=A10+A2
По хорошему надо парсить формулу, вытаскивая каждую ссылку в полном виде и уже потом заменяя её. Как пример подобного изврата можно посмотреть код из этой статьи: Отобразить в формулах вместо ссылок на ячейки значения ячеек
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
23.05.2016 19:39:44
Всё работает на в новом файле.
Подскажите, пожалуйста, а можно теперь заставить это как-то работать здесь...
https://drive.google.com/file/d/0B84Vo6Ho0RaVWjk0akpudnBnUEE/view?usp=sharing
 
 
 
#6
23.05.2016 19:43:00
Кстати, версия  
Игорь отлично работает, а вот версия The_Prist не пашет!
 
 
 
#7
23.05.2016 19:45:56
Цитата
Kirill Gureev написал:
а вот версия  The_Prist  не пашет!
файл покажете на котором не пашет? Я проверил - все работает без проблем.
И что будете делать с этим:
Цитата
The_Prist написал:
если ввести в A3 формулу:
=A10+A2

А в Google Tables это работать никак не будет - там скрипты другие совершенно.
Изменено: The_Prist - 23.05.2016 19:47:56
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#8
23.05.2016 19:51:22
Цитата
The_Prist написал:
файл покажете на котором не пашет?
Не надо. Вам же стиль R1C1 надо было побороть. Для наглядности можно так в моем варианте:
Код
Function WHATIF(output_ref As Range, input_ref As Range, input_value)
    Dim s As String
    s = Replace(Replace(output_ref.Formula, "$", ""), input_ref.Address(0, 0), input_value)
    s = Application.ConvertFormula(s, xlA1, Application.ReferenceStyle)
    WHATIF = Application.Evaluate(s)
End Function
хотя вариант Игоря покороче.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#9
23.05.2016 20:03:07
Сделал небольшой файлик с применением всех вариантов написанного кода, включая последний....
The_Prist
, Ваша первая версия не работает.
А вот последняя также работает отлично.

Проблемы с вводом через формулу одну переменную я так и не заметил.
Может неправильно понял проблему.

А вот с моим проблемный файлом почему-то не применяется.
Также интересно, будет ли это работать, если первоначальные расчёты выполнены на одном листе, а пересчёт по новому значению переменной - на другом листе.
(пока писал, сразу и попробовал).
НЕТ! Товарищи! Не работает оно, если расчёт ведётся на другом листе!
А как сделать так, чтобы и на другом листе всё воспринималось нормально?
Спасибо
 
 
 
#10
23.05.2016 20:28:56
Цитата
Kirill Gureev написал:
Проблемы с вводом через формулу одну переменную я так и не заметил.
Цитата
The_Prist написал:
если ввести в A3 формулу:
=A10+A2
при этом формула должна выглядеть так:
=WHATIF(A3;A1;A5)

Т.е. А10 будет заменена не полностью, а только часть А1. Т.е. получится не 4, а 40.

Возможно, проблема будет нагляднее при таких исходных данных.
=WHATIF(A3;A2;A5)
а в А3:
=WHATIF(A3;A2;A5)
по факту А2 будет заменена на 4, а А20 на 40. Что неверно. В данном случае логично предположить, что должна замениться исключительно А2, а А20 должна остаться без изменений. Но, возможно, я неверно трактую Ваши требования к функции WHATIF.

Что до неработы функции со ссылками на другие листы
Вы указываете функцию как:
=WHATIF(D3;D2;D6)
Что смущает:
1. В третьей строке вообще нет формулы. Она в 5-ой.
2. Вы в формуле в строке 5 указываете ссылки на столбец Е, а к замене указываете D. Что ожидаете от такой замены?
3. Как я уже выше упоминал - самое правильно это парсить и вытаскивать все ссылки. Тогда есть шанс сделать функцию рабочей с любыми ссылками.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#11
23.05.2016 20:34:25
С другими листами можно чуть проще сделать, если правильно понял. Хотя тоже есть нюансы, в свое время пробовал такой вариант. С формулами, содержащими вложенные стандартные функции работать будет через раз. Сделал в Вашем же файле, в столбце со своей формулой.
Изменено: The_Prist - 23.05.2016 20:35:30
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#12
23.05.2016 20:55:35
The_Prist так действительно работает.
Однако, то для чего я это искал так и не пашет.
Мой проект
Может у Вас будет идея, по какой причине.
Формулы там конечно страшные...
Как вообще это можно реализовать?
 
 
 
#13
23.05.2016 21:02:44
Это в другой форум - здесь по Гугл Таблицам народ не очень.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#14
23.05.2016 21:08:18
Цитата
The_Prist написал:
Это в другой форум - здесь по Гугл Таблицам народ не очень.
Нет-нет.... это  просто большой файл, который не влазит просто как вложение.
По этой причине я его выложил на свой google-диск
Но это стандартный файл с макросом Excel.
Просто, скачайте, пожалуйста, посмотрите.
Я вставил Вашу функцию на лист "Модель" и лист "Чувствительность".
Не работает.
Посмотрите, пожалуйста.
Изменено: Kirill Gureev - 23.05.2016 21:08:53
 
 
 
#15
23.05.2016 22:06:05
Цитата
Kirill Gureev написал:
Я вставил Вашу функцию на лист "Модель" и лист "Чувствительность".
Не работает.
Цитата
The_Prist написал:
Хотя тоже есть нюансы, в свое время пробовал такой вариант. С формулами, содержащими вложенные стандартные функции работать будет через раз
Больше сказать нечего. ConvertFormula в этом случае часто выдает ошибки, отказываясь нормально преобразовывать ссылки, участвующие в функциях листа.
А Evaluate может отказать работать с очень длинными формулами.
Изменено: The_Prist - 23.05.2016 22:07:38
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Читают тему
Наверх