Здравствуйте участиники форума и гуру Экселя. Начальная граница диапазона выбирается исходя из результатов вычисленных значений. Прошу смотрите пример.
Function ikki#(k$, n&, r As Range)
Dim i&, nt&, nv&
i = r.Rows.Count
Do While i >= 1
If r.Cells(i, 1) = k Or r.Cells(i, 2) = k Then
nt = nt + 1
nv = nv + IIf(r.Cells(i, 1) = k And r.Cells(i, 3) = "H" Or r.Cells(i, 2) = k And r.Cells(i, 3) = "A", 1, 0)
If nt = n Then Exit Do
End If
i = i - 1
Loop
If nt Then ikki = nv / nt
End Function
кстати - результаты не везде совпали с подсчитанными Вами вручную, но, имхо, ошибка у Вас.
Спасибо. Как видите я не протсо так применял СЧЁТЕСЛИМН(), так как пытался сделать задуманное без промежуточных столбцов. Можно ли сделать сие чудо без промежуточных столбцов? Применяя СЧЁТЕСЛИМН(), ДВССЫЛ(), АДРЕС()? Для меня главное понять, как задать начало диапазона на N меньше чем текущая строка. А N=5 (заданное ко-во игр для расчета), то есть чтоб формула брала диапазон на 5 строк раньше текущего. Помогите пожалуйста
N=5 игр именно этой команды. Грубо говоря диапазон (<текущая ячейка-N >:<текущая ячейка>) Таким образом с каждой новой строкой диапазон смещается на одну ячейку, но всегда просматривает только N последних матчей команды.
Необходима одно, что я не смог придумать как сделать: как задать начало диапазона, ведь команда не играет матчи подряд, понимаете? то есть нельзя просто сделать <текущая строка минус N вниз>, потому что матчи с этой командой не идут подряд. Да черт с ним, пусть не в один столбец будет, а через несколько промежуточных со значениями, лишь бы решение найти. Помогите
Без формул массива, но в несколько столбцов. В большую мегаформулу не пытался объединить, да и скорее всего не получится, ибо используется функция СМЕЩ.
GeorgeDark пишет: чем тогда мой пример не устраивает?
Вам огромное спасибо. Я не успел отписаться, испытывал и проверял на больших диапазонах. Я немного изменил Вашу формулу Вместо =ЕСЛИ(Q4<=$P$1;R4/Q4;(R4-ИНДЕКС($R$3:$R$31;ПОИСКПОЗ(Q4-$P$1;$Q$3:$Q4;0)))/$P$1) поменял конец диапазона на $R4
Спасибо Вам за помощь. Но это очень много столбцов =))) Можете посмотреть вариант от GeorgeDark. У меня был такой же вариант, но лаконичнее с СЧЁТЕСЛИ и СЧЁТЕСЛИМН.
Вообще формулы массива не такие уж и хорошие. Да они помогают там, где обычно простыми формулами не обойтись, но все же надо стараться ограничивать их применение. Если можно обойтись колонками - лучше их использовать. Еще недостаток массивных формул (равно как и мегаформул) состоит в том, что их очень часто сложно отлаживать. Если необходимо немного видоизменить результат, сделать это неподготовленному человеку очень сложно. Более того, производительность с массивами падает тем быстрее, чем больше диапазон применения.
В данном случае признаю, применение массивов оправдано.
Кстати здесь можно написать формулу аналогичную формуле ikki, используя среди прочих двссыл, столбец/строка, индекс, поискпоз, наибольший/наименьший. Факультативно могу поднапрячься и составить такую формулу, да вот наверное уже не нужно.
Почему не нужно? Я просто не ас пока что. С самого начала у меня был вариант с применением СЧЁТЕСЛИМН(), а вот не знал как ссылку задать в формуле. С другой стороны чем легче для экселя будет наличие множества столбцов? Тут 4000 строк, так что и так и так будет думать
на 4000 строк - однозначно доп.столбцы или UDF (кстати - моя UDF в этой теме очень экономная - ничего лишнего не проверяет и не считает, проверят только минимально необходимый объем данных) ещё лучше - макрос. но, если уж с допстолбцами - то их тоже желательно делать с умом, а не СЧЁТЕСЛИМН в каждой ячейке можно ведь взять результат из предыдущей строки и добавить к нему одно-два ЕСЛИ хотя я на 100% уверен - мою мегаформулу можно облегчить но пользоваться ею, даже облегчённой, на таком объеме категорически не советую.
пс. использование ДВССЫЛ и СМЕЩ - это не облегчение.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
вот, имхо, самый лёгкий вариант с двумя доп.столбцами (E, F, нужный результат в G). ни одной тяжёлой СУММЕСЛИМН и СЧЁТЕСЛИМН не говоря уж о летучих ДВССЫЛ и СМЕЩ
прозрачная логика - если вдруг менять что. простейшая арифметика и элементарная ВПР. кстати, ВПР в своей "супербыстрой" ипостаси - с поиском по упорядоченному столбцу
Ух ты! А я и не знал, что без ЕСЛИ() можно использовать ИЛИ. ИЛИ() дает +1 если истина и -1 если ЛОЖЬ? или 0 если ЛОЖЬ? ОГРОМНОЕ СПАСИБО. Изменил свои взгляды на простоту Скажу откровенно - тормозит с мегаформулой нереально, так как у меня еще родная Виста стоит на ноутбуке. Отпишусь о чудо быстроте когда исправлю на ваш облегченный вариант!!!
0 если ЛОЖЬ. Можно проверить, если оставить в формуле только ИЛИ и поставить перед ней -- . Например для строки 4 формула =--ИЛИ(A4=$D$2;B4=$D$2) выдает 0.
Подтверждаю значительное увеличение производительности! Теперь понимаю, что лучше несколько промежуточных столбцов с простыми формулами, чем один с мега-сложной!!! Если со сложной при автоматическом пересчете даже на запуск файла приходилось ждать пару минут загрузку, то теперь "летает" и считает намного шустрее!!! Всем огромное спасибо!