Множественная вставка в несмежные ячейки некоторой формулы

[eeigor]

Предлагаю решить ребус. Без макросов. Макросы мы тут уже писали.
Как заполнить в табличке пустые ячейки значениями выше? К примеру для того, чтобы обработать данные в сводной таблице.
С некоторого времени я знаю ответ и готов его вам тут предложить. Макросы "отдыхают".
Но для начала предлагаю желающим высказаться...

[kompilainenn]

В Эксель функция Заполнить->Вниз заполнит по умному ячейки. В ЛО такое не работает. Я сдаюсь и хочу ответ

[eeigor]

@kompilainenn, дадим ещё немного времени до завтра.
Решение существует, и весьма неожиданное.
@sokol92 недавно подсказал тут способ написания чисел словами, существенно сократив объем кода...
А эту задачу мы дружно уже решали и написали много кода. Решение... подбираю слово... изумительное и без макросов. До завтра.

[bigor]

Вариант с доп столбцом и формулами, я так понимаю не подходит

[economist]

Тоже сдаюсь. В Python+Pandas под LO такая задача делается, имхо, максимально просто (миллион строк/сто столбцов - за доли секунды):

Код Выделить Развернуть


df.fillna(method='ffill')


Category1 и SubCategory 2.2 итд - становятся частью Мультииндекса, и задача выборки любой строки и столбца (а она возникнет 100%) - сводится к: df.loc[('Category3', 'SubCategory 3.2'), 'Column 4'] (+ еще парочка способов). Это решает проблему любой адресации как к яч./строкам/столбцам самой сводной таблицы, так и к её исходным данным (яч./строкам/столбцам). И работает это по hash-деревьям в RAM, т.е. в 5 раз быстрее любых других способов. 

[eeigor]

Нет, @Bigor, без дополнительного столбца. А формула у нас одна: ссылка на ячейку выше. Подсказка: задача решается, как ни странно, копированием и вставкой, но есть нетривиальная опция. Всё просто, если знаешь... Я не знал.

Но ведь согласитесь: пример жизненный. Для сводной надо сделать все записи полными, при этом будет налицо избыточность информации (дублирование наименований категорий/подкатегорий). Некоторые пользователи предпочитают именно такую структуру для наглядности.

В отчётах повторение наименований – это отдельная опция. Но тут не отчёт, а исходные данные. Требуется быстро, путём нажатия нескольких клавиш, без макросов, устранить недостаток.

[bigor]

С учётом подсказки, решение найдено. Но подожду от @eeigor

[eeigor]

@Bigor, замечательно!

Для других даю подсказку 2.
Установите курсор в ячейке A3 и, работая мышью, выделите диапазон A3:B20, а затем скопируйте его в буфер обмена. Не снимая выделения, введите в активную ячейку A3 формулу (можно использовать мышь):
=A2
и нажмите Alt+Enter, чтобы вставить заданную формулу во все ячейки выделенного диапазона... Ой! Кажется часть данных была затёрта. Но мы не будем делать откат, потому что в этом, как бы, нет необходимости...

[bigor]

Мой вариант корявее. Там по столбцу только. Пишем в а3 формулу =а2. Копируем ее. Выделяем столбец A4:a20. Спец вставка выбираем формулы и в операциях добавить. Вставляем. Ctrl+H меняем +0 на пусто

ps хотя с небольшими ухищрениями можно и диапазоны тоже заполнять. После копирования а3, выделяем диапазон а3:в20 снимаем выделение с а3 и далее по вышеописанному

[eeigor]

Даю подсказку 3:
А что осталось в буфере обмена?
Допускаю, что об этом моменте все уже добросовестно забыли.
А между тем, мы сейчас в одном шаге от финиша...
Кто придёт первым?

[kompilainenn]

Для сводной надо сделать все записи полными, при этом будет налицо избыточность информации (дублирование наименований категорий/подкатегорий).

В настройках Сводной таблицы есть опция типа учитывать для пустых значений значение верхней ячейки

[economist]

Полные записи нужны до Сводной - для Автофильтра, т.к. без него не обойтись при "чистке" данных.   

[eeigor]

@kompilainenn, спасибо за дополнительную информацию. В параметрах сводной таблицы действительно есть опция "Распознавать категории". Чего она делала, я, читая, догадаться не мог. Теперь знаю. Но это не умаляет ценности решения нашего ребуса. Ответ рядом...

[kompilainenn]

Чего она делала, я, читая, догадаться не мог

Я тоже не мог, пока в переводе гайда не наткнулся и то только после примера с картинками понял

[eeigor]

@kompilainenn, я так и понял, что при переводе. Недавно "пытал" @sokol92, как он (Владимир) добывает информацию. Оказалось, как разведчик.

Но в нашем случае мы имеем дело ещё и с издержками "интуитивно-понятного" интерфейса.

UPD:
Я подгрузил наш пример с добавленной сводной таблицей и установленной опцией "Распознавать категории". Так сказать, ещё одной "чёрной дырой" стало меньше... попутно.