Как вставить скопированное в отфильтрованную таблицу? Без макроса.
Уверена, Вы сталкивались с этой проблемой. Если скопировать данные и обычным CTRL+V вставить их в отфильтрованную таблицу, то они попадут и в скрытые строки тоже. Это совсем не то, что нам нужно - см. скрин ниже. Узнаёте?
Можно решить эту задачу, подтянув данные формулами, без макроса. Ниже показываю 3 варианта.
Функции СМЕЩ + СЧЁТЕСЛИ
Если таблица отфильтрована по понятному однозначному условию (конкретный текст или текст содержит, известное число или число < либо > заданного и т.д.). На скрине ниже я выбрала «Самовывоз из питомника».
И любые из этих условий можно использовать для функций СЧЁТЕСЛИ/МН, СУММЕСЛИ/МН и СРЗНАЧЕСЛИ/МН (на моём онлайн курсе «Расширенные возможности» показываю все варианты условий на примере выборочного суммирования).
И любые из этих условий можно использовать для функций СЧЁТЕСЛИ/МН, СУММЕСЛИ/МН и СРЗНАЧЕСЛИ/МН (на моём онлайн курсе «Расширенные возможности» показываю все варианты условий на примере выборочного суммирования).
В этом случае нашим помощником будет функция СЧЁТЕСЛИ (или СЧЁТЕСЛИМН, если зафильтровано несколько столбцов), которая способна посчитать в каждой строке её уникальный порядковый номер от начала таблицы.
А это и есть тот номер, который можно использовать для смещения ссылки: в первой отфильтрованной строке ссылка нужна на первую ячейку с данными для вставки, во второй отфильтрованной строке ссылка нужна вторую ячейку с данными для вставки и т.д.
Осталось позвать на помощь функцию ссылок и массивов СМЕЩ и вот результат:
D9=СМЕЩ($F$96;СЧЁТЕСЛИ($C$3:C9;"самовывоз из питомника")-1;0)
Осталось позвать на помощь функцию ссылок и массивов СМЕЩ и вот результат:
D9=СМЕЩ($F$96;СЧЁТЕСЛИ($C$3:C9;"самовывоз из питомника")-1;0)
Формулу ячейки D9 я просто скопировала вниз и все данные корректно разместились на своих местах. Если сбросить фильтр, то всё будет ОК. Диапазон D:D можно скопировать и вставить значениями на своё же место, избавившись от формулы.
Функции СМЕЩ + ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Если таблица отфильтрована по нескольким значениям, и они не уникальны. То есть мы вручную выбрали флажками несколько названий. На скрине ниже я выбрала две не соседние даты.
Тогда берём в работу другую функцию, способную посчитать количество неважно каких записей от начала таблицы и таким образом снова получим тот самый номер:
E4=СМЕЩ($G$81;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$D$3:D4)-1;0)
E4=СМЕЩ($G$81;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$D$3:D4)-1;0)
Чтобы данные не поехали после сброса фильтра, я добавила служебный столбец, в котором вписала единички (или любые другие метки). И копировала вниз до конца таблицы уже не одну ячейку с формулой E4, а диапазон ячеек D4:E4. И снова всё получилось.
Функция ВПР
Если таблица отфильтрована по нескольким значениями и они уникальны. То не забывайте про функцию ВПР – это ведь её работа )) На скрине ниже фильтром я выбрала только нужные номера заказов. Конечно, в диапазоне для подстановки эти же названия тоже должны быть:
D4=ВПР(A4;$F$90:$G$96;2;0)
D4=ВПР(A4;$F$90:$G$96;2;0)
Готово!