☰

Заменить ссылки на ячейки в функции, а не в результирующей строке

tag-icon • tag-icon microsoft-excel
Заменить ссылки на ячейки в функции, а не в результирующей строке

У меня есть сложная формула, которую я хотел бы использовать в таблице, одновременно с этим отобразив эффект изменения одной ячейки в формуле.

=($E$9+J2)-((($B$9+J2)+($B$23*200*$B$15)+(1E+99-1)*(($B$9+J2)*(1.1+(1-$B$23)*(0.75+($B$22*$B$15*0.4))*$B$15)+($B$23*200*$B$15)+$B$23*(FLOOR.MATH(1E+99/3)*200*$B$15/MAX(1,1E+99-1))))*$B$12/1E+99)*($E$9+J2)/((($E$9+J2)+($E$23*200*$E$15)+(1E+99-1)*(($E$9+J2)*(1.1+(1-$E$23)*(0.75+($E$22*$E$15*0.4))*$E$15)+($E$23*200*$E$15)+$E$23*(FLOOR.MATH(1E+99/3)*200*$E$15/MAX(1,1E+99-1))))*$E$12/1E+99)

Как вы можете видеть, есть некоторые повторяющиеся функции, которые я попытался упростить с помощью замены.

=($E$9+J2)-SUBSTITUTE($B$29,$B$9,($B$9+J2))*$E$9/SUBSTITUTE($E$29,$E$9,($E$9+J2))

Это не работает, потому что замена на самом деле преобразуетрезультатфункций в строки и поиск экземпляров вместо преобразования самой функции.

Если бы мы программировали на компьютерном языке, мы могли бы просто вызвать метод с другими аргументами, но я не уверен, способен ли Excel сделать это.

====

Чтобы проиллюстрировать задачу в этой «упрощенной» таблице, у меня есть две формулы

  |  A  |  B  |  C  | 
1 |    1   101   202      
2 |  100

Ячейка B1 имеет эту формулу

=A1+A2

и ячейка C1 имеет эту формулу

=SUBSTITUTE(B1,A1,A1*2)

Я хочу, чтобы в ячейке C1 было значение 102 вместо 202. Есть ли способ подставить каждое вхождение A1 в формулу, а не результирующую строку?

решение1

Я думаю, что наиболее близким к тому, что вы хотите, исходя из того, что вы показываете в своих комментариях, будет что-то вроде

=LET(x,A1*2,y,A2,x+y).

Если вам нужно разобрать формулу в B2, вам, вероятно, придется написать парсер формулы в VBA. Но может быть проще просто написать всю формулу как VBA UDF.

Примечание: LETдоступно только в последних версиях Excel O365.

Связанный контент