Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск текста в диапазоне ячейки вниз до первой пустой
 
#1
18.05.2018 16:02:26
Есть более тысячи диапазонов ячеек под группировкой, диапазоны все разные, от 1 до 12 ячеек
Надо под каждой группировкой найти ячейки по месяцу и вставить цифру из ячейки слева, а если такого месяца не было, то поставить 0.
Через ВПР(R3C;R6C2:R17C3;2;ЛОЖЬ) решаемо, но для каждой группировки надо руками диапазон выделять нужный, а их более 1000, и там где нет месяца - 0 не ставится, #Н/Д
Можно ли сделать, что бы поиск шёл под каждой группировкой сверху вниз до первой пустой ячейки и подставлялись найденные месяцы, а ненайденные 0.
Изменено: GLingeneer - 18.05.2018 16:02:53
 
 
 
#2
18.05.2018 16:13:05
а такой вариант не прокатит
Лень двигатель прогресса, доказано!!!
 
 
 
#3
18.05.2018 16:24:46
Еще вариант
In GoTo we trust
 
 
 
#4
18.05.2018 16:29:58
Формула массива:
Код
=ЕСЛИОШИБКА(ВПР(D$3;$B6:СМЕЩ($C6;ПОИСКПОЗ(ИСТИНА();($B6:$B500="");ЛОЖЬ())-2;0);2;0);"")
Вводить через Ctrl+Shift+Enter

Потом протянуть на все месяцы. Затем как-то отфильтровать строки с цифрами и протянуть до низа.
Либо можно скопировать ячейки всех месяцев и вставлять в нужном месте.
 
 
 
#5
18.05.2018 16:34:11
не совсем то
для 1 группы всё правильно
аналогично надо для остальных
мне по сути нужна получить строку  со всеми  теми же месяцами  и цифрами , что и под группировкой, но в том числе и с нулевыми тоже
итоговая задача посчитать коэффициент вариации, но у меня не везде все 12 мес есть
возможно есть и другие варианты  
 
 
 
#6
18.05.2018 16:36:02
Код
=ЕСЛИОШИБКА(ВПР(D$3;$B6:СМЕЩ($C6;ПОИСКПОЗ(ИСТИНА();($B6:$B500="");ЛОЖЬ())-2;0);2;0);"")
Вместо "" в конце 0
Код
=ЕСЛИОШИБКА(ВПР(D$3;$B6:СМЕЩ($C6;ПОИСКПОЗ(ИСТИНА();($B6:$B500="");ЛОЖЬ())-2;0);2;0);0)
Будут нули там, где нет данных
 
 
 
#7
18.05.2018 16:37:41
В формуле Сергея аналогично, измените в обработчике ошибок "" на 0
 
 
 
#8
18.05.2018 17:01:44
Получить надо то, что в желтых ячейках
Т.е. получить все 12 месяцев в том числе и с нулями, которых нет под группировками..
Или как то по другому сразу считать коэфф-т вариации, чтобы учитывались отсутствующие нулевые показатели месяцев под группировкой
повторюсь группировок более 1000
 
 
 
#9
18.05.2018 17:50:26
GLingeneer, я же Вам написал, измените "" на 0 в обработчике ошибок

формула
Код
{=ЕСЛИОШИБКА(ВПР(D$3;$B6:СМЕЩ($C6;ПОИСКПОЗ(ИСТИНА();($B6:$B500="");ЛОЖЬ())-2;0);2;0);0)}
 
 
 
#10
22.05.2018 18:14:23
Да, то что надо. Спасибо.
А почему при любом изменении формулы, даже просто вход  в ячейку - сразу пересчет на 0.
Не могу в другую книгу формулу нормально вставить

И как эту формулу по-быстрому в тысячу группировок вставить ))) ??
Изменено: GLingeneer - 22.05.2018 18:16:50
 
 
 
#11
22.05.2018 18:31:26
Решение с доп. столбцом.
=ЕСЛИ(ЕЧИСЛО(B5);B5;A4)
=ЕСЛИ(ЕЧИСЛО($B5);СУММЕСЛИМН($C:$C;$B:$B;D$3;$A:$A;$B5);"")
В настройках отключить отображение нулей.
Изменено: Bema - 22.05.2018 18:32:42 (Заменил файл)
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#12
23.05.2018 09:46:52
я не про это
0 которые реально нули, мне как раз и нужны, для этого всё и делается
а вот просто когда формулу щелкнул внутрь, а потом просто нажал Enter - сразу ноль стал, там где до этого была верная цифра. Если esc нажимаю, нормально
или я копирую эту формулу на другой лист, редактирую связанные ячейки и диапазону - и на ноль пересчитывает сразу, почему так?

И подскажите макрос
чтобы эту формула вставлялась в строке каждой группы
например, по жирному тексту или также после строки с пустой ячейкой в столбце B
по нажатию клавиш ctrl+q  
Изменено: GLingeneer - 23.05.2018 13:33:40
 
 
 
Страницы: 1
Наверх