Страницы: 1 2 След.
RSS
Запись длинных формул в функцию vba
 
Здравствуйте! Попытаюсь максимально подробно объяснить чего хочу, т.к. нет возможности вставить пример.
Есть динамическая таблица на листе, в которой закрашиваются ячейки в зависимости от выбранного имени таблицы (закраска ячеек происходит при выборе имени таблицы). Имена таблиц вместе со значениями, указывающими какую ячейку выбранной таблицы в какой цвет залить хранятся в базе на другом листе. Есть ячейки, в которых выводится процент ячеек, залитых определенным цветом. Вначале я создал массив на отдельном листе , в  нем получилось много ячеек с длинными формулами, и общий результат вычислений в этом массиве выводился в ячейки на листе вместе с таблицей. Вот пример формулы одной из ячеек массива с множеством условий:
Скрытый текст

Для увеличения скорости обработки данных  решил перенести все вычисления в vba, а только в ячейки выводить общий результат.
Создал функцию в vba, в которую попытался запихнуть такую длинную формулу:
Код
Option explicit
Function Test()
Test=Evaluate("="длинная формула в англ. варианте"")
End Function 

Не получилось, думаю из-за того, что в скобках стоит ограничение на 255 символов.
Подскажите метод чтобы можно было впихнуть всю формулу не разбивая ее на множество функций. Заранее благодарен.
 
Разделил формулу на несколько коротких функций, и когда пытаюсь вызвать одну функцию из другой, вместо значения получаю "Error 2015" (Stack Overflow).
Вот пример вызываемой функции:
Код
Function L1B1Pos()
L1B1Pos = Evaluate("=MyPos()&"".""&NLHE.FR!C8&"".""&NLHE.FR!F2&"".""&LST2Pos()&"".""&LST3Pos()&"".""&SSPos()")
End Function


А в этой функции я ее вызываю и получаю ошибку:
Код
Function L1T()
L1T = Evaluate("=IF(NLHE.FR!C1=1,L1B1Pos())")
End Function


По отдельности вызываемая функция нормально работает, и вызывающая тоже, если вместо "L1B1Pos()" подставить другую функцию с более коротким выражением.
Изменено: OSA913 - 11.11.2017 07:06:19
 
Лучше бы файл приложили с примером.
А так - ...
1. можно посоветовать посмотреть в сторону объекта Application.WorksheetFunction
2. Ваша задача скорее всего имеет более лаконичное решение, чем конструкция со вложенными ЕСЛИ().
Изменено: Михаил Лебедев - 11.11.2017 07:29:46
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Спасибо, попробую убрать функцию "ЕСЛИ" в формулах.
 
OSA913, если рассмотреть первый фрагмент формулы, добавив перенос строки (Alt+Enter внутри ячейки)
Код
=ЕСЛИ(NLHE.FR!$C$1=1;
ЕСЛИ(NLHE.FR!$B$1=1;NLHE.FR!$B$8;
ЕСЛИ(NLHE.FR!$B$1=2;NLHE.FR!$B$9;
ЕСЛИ(NLHE.FR!$B$1=3;NLHE.FR!$B$10;
ЕСЛИ(NLHE.FR!$B$1=4;NLHE.FR!$B$11;
ЕСЛИ(NLHE.FR!$B$1=5;NLHE.FR!$B$12;
ЕСЛИ(NLHE.FR!$B$1=6;NLHE.FR!$B$13;
ЕСЛИ(NLHE.FR!$B$1=7;NLHE.FR!$B$14;
ЕСЛИ(NLHE.FR!$B$1=8;NLHE.FR!$B$15)))))))))
, то видно, что фрагмент можно упростить:
Код
=ЕСЛИ(NLHE.FR!$C$1=1;ИНДЕКС(NLHE.FR!$B$8:$B$15;NLHE.FR!B1))
Видимо, и дальше так же.
 
Спасибо, можно ли как нибудь сократить формулу такого плана?
Код
=ЕСЛИОШИБКА(ЕСЛИ(NLHE.FR!$C$1=1;
ЕСЛИ(ИЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$1;BASE!$A:$A;0))=
{"13995347|13995347";"12611584|12611584";"13020235|13020235";"13995347|12611584";"12611584|13995347";"13995347|13020235";"13020235|13995347";"12611584|13020235";"13020235|12611584"});6;
ЕСЛИ(И(ИЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$1;BASE!$A:$A;0))<>
{"13995347|13995347";"12611584|12611584";"13020235|13020235";"13995347|12611584";"12611584|13995347";"13995347|13020235";"13020235|13995347";"12611584|13020235";"13020235|12611584"});
ЕСЛИ(НЕ(И(ЕОШ(НАЙТИ({"13995347";"12611584";"13020235"};
ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$1;BASE!$A:$A;0))))));3));3));
ЕСЛИ(NLHE.FR!$C$1>1;
ЕСЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$43;BASE!$A:$A;0))="5296274|5296274";6;
ЕСЛИ(И(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$43;BASE!$A:$A;0))<>"5296274|5296274";
ЕСЛИ(НАЙТИ("5296274";ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$43;BASE!$A:$A;0)));3));3));
ЕСЛИ(И(NLHE.FR!$B$1<>9;NLHE.FR!$D$1>=1);ЕСЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$85;BASE!$A:$A;0))="5296274|5296274";6;
ЕСЛИ(И(ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$85;BASE!$A:$A;0))<>"5296274|5296274";
ЕСЛИ(НАЙТИ("5296274";ИНДЕКС(BASE!B:B;ПОИСКПОЗ(NLHE.FR!$C$6&"|"&$K$85;BASE!$A:$A;0)));3));3));"")));"")
Изменено: OSA913 - 12.11.2017 10:38:59
 
Можно, наверно. Если файл-пример приложете.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
OSA913 написал: сократить формулу такого плана
От формул ТАКОГО плана нужно совсем отказываться, тем более, что макросы применяете.
Но без файла-примера, увы...
Согласие есть продукт при полном непротивлении сторон
 
В ячейке Е8 формула, которая проверяет в базе в первом столбце текущее имя таблицы. Если находит проверяет ячейку в строке соответствующую ячейке в таблице на наличие значений которые указаны в формуле, и в зависимости от значений возвращает в ячейку либо 3 либо 6 либо "". Имя таблицы берется из функции "L1_T()" .
Помогите эту формулу превратить в функцию vba.
 
Цитата
OSA913 написал: и в зависимости от значений возвращает...
Каковы эти зависимости? Почему сейчас в целевой ячейке 6, а не 3?
Цитата
OSA913 написал: проверяет в базе в первом столбце текущее имя таблицы
Т.е. в этом первом столбце возможно несколько имен таблиц? Имя листа всегда совпадает с именем таблицы? Нужно ли оно вообще?
Согласие есть продукт при полном непротивлении сторон
 
Крутая формула, OSA913 :)
Вы уверены, что она у Вас правильно считает? /Если уверены, тогда можно смело браться за ее оптимизацию.
Лично я - не уверен. Например, вот этот кусок формулы
...ИЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ($C$6&"|"&L1_T();BASE!$A:$A;0))<>
{"13995347|13995347";"12611584|12611584";"13020235|13020235";"13995347|12611584";"12611584|13995347";"13995347|13020235";"13020235|13995347";"12611584|13020235";"13020235|12611584"})
- всегда будет возвращать ИСТИНА, т.к. ПОИСКПОЗ() возвращает всего одно значение, которое сравнивается с несколькими разными значениями (теми, что в фигурных скобках). И если это значение не будет равно хотя бы одному из тех, что в фигурных скобках, функция ИЛИ() вернет ИСТИНА. А то, что ОДНО значение не может одновременно равняться нескольким РАЗНЫМ значениям - ОЧЕВИДНО.
Значит кусок вашего уравнения можно смело заменить на просто ИСТИНА.

А зачем в формуле ссылки на ячейки К43 и К85? Они же - пустые!

Такое ощущение, что Вы просто всем голову морочите :) Я угадал? Не надеюсь, что это не может быть не так, так как Вы не пытаетесь не думать.

PS

Вот это
ЕСЛИ(ИЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ($D$6;BASE!$A:$A;0))=
{"13995347|13995347";"12611584|12611584";"13020235|13020235";"13995347|12611584";"12611584|13995347";"13995347|13020235";"13020235|13995347";"12611584|13020235";"13020235|12611584"});[длиннющий набор функций];6)

можно смело заменить на вот это
ЕСЛИ(ЕНД(ПОИСКПОЗ($D$6;BASE!$A:$A;0));6;[длиннющий набор функций])

, где в D6 стоит =C6&"|"&L1_T()
Изменено: Михаил Лебедев - 13.11.2017 18:36:32
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
Sanja написал:
Каковы эти зависимости? Почему сейчас в целевой ячейке 6, а не 3?
Значения эти идентичны коду цвета, делятся вертикальной чертой т.к. есть возможность заливки ячейки двумя цветами с градиентом, если код по обе стороны черты одинаковый, тогда ячейка заливается одним цветом.   При первом условии (С1=1) если в ячейке 2 кода 3х первых оттенков синих цветов Скриншот, тогда ячейка принимает значение равное 6, если содержиться только один цвет синего оттенка, тогда =3, если нет такого цвета, тогда "".
Цитата
Sanja написал:
Т.е. в этом первом столбце возможно несколько имен таблиц? Имя листа всегда совпадает с именем таблицы? Нужно ли оно вообще?
Да это база в которой хранятся имена таблиц и она пополняется. Имя листа нужно, потому что есть другие таблицы на других листах, а базу у них одна.
П.С: Конкретно для ячейки в приложенном примере в базе проверяется ячейка столбца В.
 
Цитата
Михаил Лебедев написал:
Лично я - не уверен. Например, вот этот кусок формулы
...ИЛИ(ИНДЕКС(BASE!B:B;ПОИСКПОЗ($C$6&"|"&L1_T();BASE!$A:$A;0))<>
{"13995347|13995347";"12611584|12611584";"13020235|13020235";"13995347|12611584";"12611584|13995347";"13995347|13020235";"13020235|13995347";"12611584|13020235";"13020235|12611584"})
- всегда будет возвращать ИСТИНА, т.к. ПОИСКПОЗ() возвращает всего одно значение, которое сравнивается с несколькими разными значениями (теми, что в фигурных скобках). И если это значение не будет равно хотя бы одному из тех, что в фигурных скобках, функция ИЛИ() вернет ИСТИНА. А то, что ОДНО значение не может одновременно равняться нескольким РАЗНЫМ значениям - ОЧЕВИДНО.
Значит кусок вашего уравнения можно смело заменить на просто ИСТИНА.
Проверял считало вроде правильно, это условие-если ячейка не содержит не одной из комбинаций в которых присутствует два из трех оттенков синего цвета. Возможно ее можно как то упростить..
 
Цитата
Михаил Лебедев написал:
А зачем в формуле ссылки на ячейки К43 и К85? Они же - пустые!
Это были ссылки на ячейки с формулами. Я забыл заменить их ссылкой на функцию. Сейчас поправлю.
 
Исправил
 
Цитата
OSA913 написал:
Сейчас поправлю.
Я откорректировал свое предыдущее сообщение, добавил вопросы (не ожидал, что Вы так быстро отзовётесь :) )
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
OSA913 написал:
Проверял считало вроде правильно
Ответьте себе на вопросы:
1. Сколько аргументов возвращает =ПОИСКПОЗ() ?
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Вставил в пример еще одну таблицу, при выборе которой в ячейке становится значение равное 3. Для этого в ячейке С1 нужно поменять 1 на 2.
 
Цитата
OSA913 написал:
Исправил
Нет, не исправили. Чему равно К85 ?
Изменено: Михаил Лебедев - 13.11.2017 19:14:44
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
На счет последнего аргумента ПОИСКПОЗ не уверен нужен ли он. В D6 не получится поставить функцию т.к. этих функций 9 штук , и будет 9 ячеек с разными результатами вычислений. K85 поправил.
 
Цитата
OSA913 написал:
В D6 не получится поставить функцию т.к. этих функций 9 штук , и будет 9 ячеек с разными результатами вычислений.
??? Диаметр окружности примерно равен 2-м радиусам, поскольку число ПИ() - бесконечно.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
OSA913 написал:
При первом условии (С1=1) если в ячейке 2 кода 3х первых оттенков синих цветов..., тогда ячейка принимает значение равное 6, если содержится только один цвет синего оттенка, тогда =3, если нет такого цвета, тогда "".
Вот - как Вы и написали. Что должно происходить при других значениях С1 ?
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Здравствуйте! При С1>1, если ячейка в базе содержит 2 кода зеленого цвета, тогда значение равно 6, если ячейка в базе содержит только 1 код зеленого, тогда значение равно 3. И 3е условие, когда B1 не равно 9 и D1 не равно 0- получилось точно таким же как и второе. Я когда объединял  3 формулы в одну пропустил этот момент. тогда по логике его можно добавить ко второму условию и формула станет еще меньше.
 
Вы мой пример посмотрели? То, что надо? Попробуйте то же самое сделать для "кода зеленого цвета" сами. Если моя формула - это то, что надо, я могу показать, как ее перенести в VBA. Но это уже завтра, скорее всего.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
OSA913 написал:
И 3е условие, когда B1 не равно 9 и D1 не равно 0- получилось точно таким же как и второе
Вот это - непонятно.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Да, Спасибо большое, то что нужно, и хотелось бы ее перенести в код. Единственное, если так возможно  
Код
=$C$6&"|"&L1_T()

если эта формула должна быть в ячейке, то  отделить функцию от названия листа и сделать ссылку на эту функцию в формуле. А то подобных функций несколько и не хотелось бы выделять для них дополнительные ячейки.
 
Цитата
Михаил Лебедев написал:
Вот это - непонятно.
При B1=9 и D1<>0 , если в таблице встретятся ячейки с зеленым цветом нужно чтобы его не подсчитывало.
Изменено: OSA913 - 14.11.2017 13:53:00
 
Ссылку на функцию в формуле теперь увидел, с этим порядок. Нашел несоответствие- поставил в базе синий вместе с голубым, а в ячейке 3 вместо 6. Любое сочетание двух синих оттенков в ячейке должно давать 6. И поставил голубой в сочетании вместе с белым - показывает 6, а надо 3. Извините что не сразу проверил, но все равно Спасибо что помогаете)
 
Цитата
OSA913 написал:
...если в ячейке 2 кода 3х первых оттенков синих цветов...
...Любое сочетание двух синих оттенков в ячейке должно давать 6...
ОК.
Изменено: Михаил Лебедев - 15.11.2017 05:21:52
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Ну да  12611584|12611584 должно быть 6. Так же 13995347|12611584 или 13995347|13020235 должно быть 6. Т.е. Любая комбинация двух цветов с синим оттенком(их всего три: светло-синий, синий и голубой). А комбинация какого нибудь одного из этих 3х синих цветов с каким нибудь другим цветом (не синего оттенка)- это 3. Иначе- "".
Страницы: 1 2 След.
Наверх