Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Cоздание пользовательской функции из формулы Exel или R1C1
 
#1
03.05.2019 15:03:36
Есть таблица с уже очень тяжелыми формулами ссылающиеся на другие листы и умные таблицы по свей книге, можно ли ее перевести в пользовательскую функцию, просто используются фунции в формуле, которые я не знаю VBA, например:

=ЕСЛИОШИБКА(ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[0_0];Физ.свойства[0];D6);0))=2;((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))+((P6-ОКРВНИЗ.ТОЧН(P6;0.05))*(((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))-(ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВВЕРХ.ТОЧН(P6;0.05);ghost!D71:R71))))/(-0.05))))+ЕСЛИОШИБКА(((L6-(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25))))*((((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))+((P6-ОКРВНИЗ.ТОЧН(P6;0.05))*(((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))-(ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВВЕРХ.ТОЧН(P6;0.05);ghost!D71:R71))))/(-0.05))))-((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВВЕРХ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВВЕРХ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))+((P6-ОКРВНИЗ.ТОЧН(P6;0.05))*(((ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВВЕРХ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВВЕРХ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВНИЗ.ТОЧН(P6;0.05);ghost!D71:R71)))-(ИНДЕКС(ghost!D84:R87;ПОИСКПОЗ(ЕСЛИ(ОКРВВЕРХ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВВЕРХ.ТОЧН(L6;0.25));ghost!C72:C75);ПОИСКПОЗ(ОКРВВЕРХ.ТОЧН(P6;0.05);ghost!D71:R71))))/(-0.05)))))/(ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25))-ОКРВВЕРХ.ТОЧН(L6;0.25))));0);ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[0_0];Физ.свойства[0];D6);0))=1;ЕСЛИ(K6<=0.07;-1250*P6^4+4500*P6^3-5993.8*P6^2+3468.8*P6-717.01;ЕСЛИ(K6<=0.12;2500*P6^4-8500*P6^3+10713*P6^2-5958.8*P6+1256.2;ЕСЛИ(K6<=0.17;-2083.3*P6^4+6833.3*P6^3-8322.9*P6^2+4432.9*P6-844.68;ЕСЛИ(K6<=0.4;1666.7*P6^4-5666.7*P6^3+7208.3*P6^2-4105.8*P6+913.97))));ЕСЛИ(И(K6<=0.07;L6<0.25);-333.33*P6^3+650*P6^2-439.17*P6+117.38;ЕСЛИ(И(K6<=0.07;L6<=0.75);833.33*P6^4-2333.3*P6^3+2429.2*P6^2-1134.2*P6+215.92;ЕСЛИ(И(K6<=0.07;L6>0.75);(833.33*P6^4-2333.3*P6^3+2429.2*P6^2-1134.2*P6+215.92)/(1.7*L6);ЕСЛИ(И(K6<=0.17;L6<0.25);-10833*P6^5+38125*P6^4-52896*P6^3+36222*P6^2-12318*P6+1711.7;ЕСЛИ(И(K6<=0.17;L6<0.5);5000*P6^5-17500*P6^4+24208.33*P6^3-16512.5*P6^2+5489.45*P6-668.11;ЕСЛИ(И(K6<=0.17;L6<=0.75);-1250*P6^4+4166.7*P6^3-5093.8*P6^2+2669.6*P6-479.26;ЕСЛИ(И(K6<=0.17;L6>0.75);(-1250*P6^4+4166.7*P6^3-5093.8*P6^2+2669.6*P6-479.26)/(1.7*L6);ЕСЛИ(И(K6<=0.4;L6<0.25);16667*P6^5-68333*P6^4+110583*P6^3-88092*P6^2+34373*P6-5160.8;ЕСЛИ(И(K6<=0.4;L6<0.5);-416.67*P6^4+1500*P6^3-1964.6*P6^2+1046.3*P6-130.59;ЕСЛИ(И(K6<=0.4;L6<=0.75);-2500*P6^4+8500*P6^3-10713*P6^2+5888.8*P6-1144.7;ЕСЛИ(И(K6<=0.4;L6>0.75);(-2500*P6^4+8500*P6^3-10713*P6^2+5888.8*P6-1144.7)/(1.7*L6);ЕСЛИ(K6="-";ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[5];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[6];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[7];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[8];Физ.свойства[0];D6);"0"))>50;-10*P6+6.5;ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[5];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[6];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[7];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[8];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[9];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[10];Физ.свойства[0];D6);"0"))>50;-10*P6+7.5;ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[5];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[6];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[7];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[8];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[9];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[10];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[11];Физ.свойства[0];D6);"0"))>=75;-20*P6+15;ЕСЛИ((ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[5];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[6];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[7];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[8];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[9];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[10];Физ.свойства[0];D6);"0")+ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Физ.свойства[11];Физ.свойства[0];D6);"0"))<75;-20*P6+17;))))))))))))))))));"-")
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
#2
03.05.2019 15:06:09
Файл-пример. Как есть - Как надо. И опишите саму ЗАДАЧУ, которую решаете этой суперформулой
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
03.05.2019 15:09:30
https://drive.google.com/file/d/1p8wR9NJHtbYQbCDI3hlS0-3Te2mtCS1i/view?usp=sharing

Эта формула рассчитывает параметр прочности, на основании данных из листа "Ведомость", в которой данные разбиты по категориям (ИГЕ), плюс ко всему алгоритм расчета зависит от типа грунта (таблица "gen"). Типов грунта всего 3, количество ИГЕ может быть разное
Часть данных описаны алгоритмами и зависимостями, а часть берется с таблиц на скрытом листе
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
#4
03.05.2019 15:24:44
Если учесть, что через имена формула мгновенно укорачивается , только за счет повторения
ЕСЛИ(ОКРВНИЗ.ТОЧН(L6;0.25)<0.25;0.25;ОКРВНИЗ.ТОЧН(L6;0.25)) и подобного, то не такая формула и супер и что-то мне подсказывает что упрощение возможно не только там.
По вопросам из тем форума, личку не читаю.
 
 
 
#5
03.05.2019 15:32:18
Я с этим и не спорю, просто скажем ка реализовать функцию СРЗНАЧЕСЛИМН через VBA я не знаю
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
#6
03.05.2019 21:48:30
Название темы общее. А должно отражать задачу, которую решает формула. Уточните. Модераторы заменят.

Нужно описывать саму задачу. Разбираться в длинной формуле мало кому интересно.
 
 
 
#7
04.05.2019 11:36:24
Возможно, что дело не в формуле.. точнее так скажу, если бы данные были организованы правильно, то имеется большая вероятность того, что формула не была бы такой длинной...
 
 
 
#8
06.05.2019 09:55:19
Цитата
vikttur написал:
Название темы общее. А должно отражать задачу, которую решает формула. Уточните. Модераторы заменят.Нужно описывать саму задачу. Разбираться в длинной формуле мало кому интересно.
Согласен!
может тогда как-то так "Как применить в виде VBA функции: ПОИСКПОЗ, ИНДЕС, СРЗНАЧЕСЛИМН для создания пользовательской функции"
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
#9
06.05.2019 09:56:54
Цитата
Konstantin Zhi написал:
Возможно, что дело не в формуле.. точнее так скажу, если бы данные были организованы правильно, то имеется большая вероятность того, что формула не была бы такой длинной...
До этого это была целая таблица, которую нужно было свести в одну формулу
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
#10
06.05.2019 10:11:47
Цитата
lodman_geo написал: Как применить в виде VBA функции: ПОИСКПОЗ, ИНДЕС, СРЗНАЧЕСЛИМН для создания пользовательской функции
И где здесь Ваша задача?

Пример.
Применить гаечный ключ для ремонта. Вам понятно, ЧТО ИМЕННО нужно ремонтировать и в чем проблема?
Читайте правила форума

Забудите о формуле. Создайте тему с названием, отражающим задачу, а не Ваш путь решеия.
 
 
 
#11
06.05.2019 10:34:05
Кажется я понял что мне делать нужно) Вы меня натолкнули на решение)
Но на всякий случай задача: "Поиск значения в таблице по двум переменным"
Лень - двигатель прогресса, а энтузиазм его топливо
 
 
 
Страницы: 1
Наверх