Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Задать диапазон по количеству результатов в прошлом
 
#1
27.12.2014 02:18:42
Здравствуйте участиники форума и гуру Экселя.
Начальная граница диапазона выбирается исходя из результатов вычисленных значений.
Прошу смотрите пример.
 
 
 
#2
27.12.2014 04:06:34
мне проще UDF
вдруг пригодится  :)
Код
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

кстати - результаты не везде совпали с подсчитанными Вами вручную, но, имхо, ошибка у Вас.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#3
27.12.2014 12:10:53
накидал формул (столбцы P,Q,R), если я правильно понял автора, то считает правильно
 
 
 
#4
27.12.2014 15:21:09
Спасибо. Как видите я не протсо так применял СЧЁТЕСЛИМН(), так как пытался сделать задуманное без промежуточных столбцов. Можно ли сделать сие чудо без промежуточных столбцов? Применяя  СЧЁТЕСЛИМН(), ДВССЫЛ(), АДРЕС()? Для меня главное понять, как задать начало диапазона на N меньше чем текущая строка. А N=5 (заданное ко-во игр для расчета), то есть чтоб формула брала диапазон на 5 строк раньше текущего. Помогите пожалуйста
 
 
 
#5
27.12.2014 15:42:49
так Вам нужны 5 любых игр?
или именно этой команды?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
27.12.2014 18:43:27
N=5 игр именно этой команды. Грубо говоря диапазон (<текущая ячейка-N >:<текущая ячейка>)
Таким образом с каждой новой строкой диапазон смещается на одну ячейку, но всегда просматривает только N последних матчей команды.
 
 
 
#7
27.12.2014 18:47:25
смутило это:
Цитата
Alexey Ko пишет: чтоб формула брала диапазон на 5 строк раньше текущего
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
27.12.2014 20:30:53
Необходима одно, что я не смог придумать как сделать:
как задать начало диапазона, ведь команда не играет матчи подряд, понимаете? то есть нельзя просто сделать <текущая строка минус N вниз>, потому что матчи с этой командой не идут подряд.
Да черт с ним, пусть не в один столбец будет, а через несколько промежуточных со значениями, лишь бы решение найти. Помогите
Изменено: Alexey Ko - 27.12.2014 20:31:50
 
 
 
#9
27.12.2014 20:36:31
из интереса - чем не подходит UDF?
или это не реальная задача, а просто способ изучить формулы?

пс. я по сложным формулам не спец.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#10
27.12.2014 20:50:08
Мне сложно редактировать UDF. А формулы можно изменить потом по ситуации. Не хотелось бы каждый раз за вопросом сюда заглядывать =)
 
 
 
#11
28.12.2014 05:19:49
Цитата
Alexey Ko пишет: Да черт с ним, пусть не в один столбец будет, а через несколько
Вопрос, чем тогда мой пример не устраивает? Считает процент побед только за последние пять игр.
 
 
 
#12
28.12.2014 07:59:09
Цитата
Alexey Ko пишет: Да черт с ним, пусть не в один столбец будет
да чёрт с ним - пусть в один
Цитата
Alexey Ko пишет: через несколько промежуточных со значениями
нет уж, теперь не уговаривайте
Цитата
Alexey Ko пишет: лишь бы решение найти
оно таки нашлось
в D3 и тянем вниз
Код
=ЕСЛИОШИБКА((СУММ((ИНДЕКС(A:A;ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A$3:A3=$D$2)+(B$3:B3=$D$2);СТРОКА(A$3:A3));$D$1);СТРОКА(A$3))):A3=$D$2)*(ИНДЕКС(C:C;ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A$3:A3=$D$2)+(B$3:B3=$D$2);СТРОКА(A$3:A3));$D$1);СТРОКА(A$3))):C3="H"))+СУММ((ИНДЕКС(B:B;ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A$3:A3=$D$2)+(B$3:B3=$D$2);СТРОКА(A$3:A3));$D$1);СТРОКА(A$3))):B3=$D$2)*(ИНДЕКС(C:C;ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A$3:A3=$D$2)+(B$3:B3=$D$2);СТРОКА(A$3:A3));$D$1);СТРОКА(A$3))):C3="A")))/(ЕСЛИ(СЧЁТЕСЛИ($A$3:$B3;$D$2)<$D$1;СЧЁТЕСЛИ($A$3:B3;$D$2);СЧЁТЕСЛИ(ИНДЕКС(A:A;ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A$3:A3=$D$2)+(B$3:B3=$D$2);СТРОКА(A$3:A3));$D$1);СТРОКА(A$3))):B3;$D$2)));0)

Цитата
Alexey Ko пишет: формулы можно изменить потом по ситуации
эт да! кто ж спорит?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#13
28.12.2014 08:08:56
забыл сказать - формула массива, конечно же
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#14
28.12.2014 13:20:49
Без формул массива, но в несколько столбцов.
В большую мегаформулу не пытался объединить, да и скорее всего не получится, ибо используется функция СМЕЩ.
С уважением,
Федор/Все_просто
 
 
 
#15
28.12.2014 15:52:07
Цитата
GeorgeDark пишет: чем тогда мой пример не устраивает?
Вам огромное спасибо. Я не успел отписаться, испытывал и проверял на больших диапазонах. Я немного изменил Вашу формулу
Вместо =ЕСЛИ(Q4<=$P$1;R4/Q4;(R4-ИНДЕКС($R$3:$R$31;ПОИСКПОЗ(Q4-$P$1;$Q$3:$Q4;0)))/$P$1) поменял конец диапазона на $R4
Цитата
ikki пишет: оно таки нашлось... в D3 и тянем вниз
Что-то не то. Вот вставил Вашу формулу в столбец H, сравните с результатами в столбце P
Цитата
ikki пишет: формула массива, конечно же
Теперь все как нужно =))) Премного благодарен!!!
Цитата
Все_просто пишет: в несколько столбцов
Спасибо Вам за помощь. Но это очень много столбцов =)))
Можете посмотреть вариант от  GeorgeDark. У меня был такой же вариант, но лаконичнее с СЧЁТЕСЛИ и СЧЁТЕСЛИМН.
Изменено: Alexey Ko - 28.12.2014 15:55:14
 
 
 
#16
28.12.2014 16:31:56
ikki, и после того произведения #13 вы скажете "пс. я по сложным формулам не спец ." !?!?!  :)   (я про саму формулу)
Работать надо не 12 часов, а головой.
 
 
 
#17
28.12.2014 16:44:52
Leanna, это не "сложная".
это "мегаформула".
две большие разницы.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#18
28.12.2014 21:09:57
Вообще формулы массива не такие уж и хорошие. Да они помогают там, где обычно простыми формулами не обойтись, но все же надо стараться ограничивать их применение. Если можно обойтись колонками - лучше их использовать. Еще недостаток массивных формул (равно как и мегаформул) состоит в том, что их очень часто сложно отлаживать. Если необходимо немного видоизменить результат, сделать это неподготовленному человеку очень сложно. Более того, производительность с массивами падает тем быстрее, чем больше диапазон применения.

В данном случае признаю, применение массивов оправдано.

Кстати здесь можно написать формулу аналогичную формуле ikki, используя среди прочих двссыл, столбец/строка, индекс, поискпоз, наибольший/наименьший. Факультативно могу поднапрячься и составить такую формулу, да вот наверное уже не нужно.
Изменено: Все_просто - 28.12.2014 21:10:27
С уважением,
Федор/Все_просто
 
 
 
#19
28.12.2014 22:03:35
Почему не нужно? Я просто не ас пока что. С самого начала у меня был вариант с применением СЧЁТЕСЛИМН(), а вот не знал как ссылку задать в формуле. С другой стороны чем легче для экселя будет наличие множества столбцов? Тут 4000 строк, так что и так и так будет думать
 
 
 
#20
28.12.2014 23:34:38
на 4000 строк - однозначно доп.столбцы или UDF (кстати - моя UDF в этой теме очень экономная - ничего лишнего не проверяет и не считает, проверят только минимально необходимый объем данных)
ещё лучше - макрос.
но, если уж с допстолбцами - то их тоже желательно делать с умом, а не СЧЁТЕСЛИМН в каждой ячейке
можно ведь взять результат из предыдущей строки и добавить к нему одно-два ЕСЛИ
хотя я на 100% уверен - мою мегаформулу можно облегчить
но пользоваться ею, даже облегчённой, на таком объеме категорически не советую.

пс. использование ДВССЫЛ и СМЕЩ - это не облегчение.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#21
29.12.2014 01:05:32
А почему не советуете использовать вашу мегаформулу на 4000 строк?!  8-0  Я уже использую. Думает конечно, но результат вроде бы нормальный.
 
 
 
#22
29.12.2014 01:40:28
она тяжёлая.
она считает много ненужного.
причём в каждой ячейке.

раз не виснет (может, у вас процессор восьмиядерный) - пользуйтесь, ради Бога.
начнёт недопустимо тормозить - возвращайтесь ;)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#23
29.12.2014 04:13:47
вот, имхо, самый лёгкий вариант с двумя доп.столбцами (E, F, нужный результат в G).
ни одной тяжёлой СУММЕСЛИМН и СЧЁТЕСЛИМН
не говоря уж о летучих ДВССЫЛ и СМЕЩ

прозрачная логика - если вдруг менять что.
простейшая арифметика и элементарная ВПР.
кстати, ВПР в своей "супербыстрой" ипостаси - с поиском по упорядоченному столбцу
Изменено: ikki - 29.12.2014 04:40:43
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#24
29.12.2014 11:26:02
Ух ты! А я и не знал, что без ЕСЛИ() можно использовать ИЛИ. ИЛИ() дает +1 если истина и -1 если ЛОЖЬ? или 0 если ЛОЖЬ?
ОГРОМНОЕ СПАСИБО. Изменил свои взгляды на простоту  ;)
Скажу откровенно - тормозит с мегаформулой нереально, так как у меня еще родная Виста стоит на ноутбуке. Отпишусь о чудо быстроте когда исправлю на ваш облегченный вариант!!!
Изменено: Alexey Ko - 29.12.2014 11:28:22
 
 
 
#25
29.12.2014 11:47:37
0 если ЛОЖЬ. Можно проверить, если оставить в формуле только ИЛИ и поставить перед ней -- . Например для строки 4 формула =--ИЛИ(A4=$D$2;B4=$D$2) выдает 0.
 
 
 
#26
08.01.2015 13:02:23
Подтверждаю значительное увеличение производительности! Теперь понимаю, что лучше несколько промежуточных столбцов с простыми формулами, чем один с мега-сложной!!!
Если со сложной при автоматическом пересчете даже на запуск файла приходилось ждать пару минут загрузку, то теперь "летает" и считает намного шустрее!!! Всем огромное спасибо!
 
 
 
Страницы: 1
Наверх