[РЕШЕНО] Скопировать несколько листов в один связью

Автор acer, 12 августа 2016, 20:06

0 Пользователи и 1 гость просматривают эту тему.

acer

Дорогие товарищи!

Что такое консолидация - слышал. Даже честно пробовал разными способами применить функцию "Объединить данные...", но в результате получается какая-то х... :(

В файле несколько листов Лист1, Лист2, Лист3 с одинаковым типом данных на этих листах - на каждом листе информация состоит 3х колонок - год, текстовая информация (начинающаяся с номера) и мои пометки - каталог марок.

На разных, т.к. отсортированы по разным темам.

Очень хочется, но не знаю можно ли как-то сделать так, чтобы на одном сводном листе была информация со всех листов, но обязательно в виде ссылок - скопировать-то у меня ума хватает, а хочется именно ссылками, чтобы изменив информацию на каком-то из тематических листов1-3 она автоматически поменялась на сводном листе13.

А вывод всех листов1-3 в лист13 нужен для возможной последующей сортировки всего этого массива по столбцу B, т.е. по хронологии.

Ни разу таким не занимался вообще, поэтому прошу ногами не пиннать, если брюки легким движеним руки превращаются... (с)  :beer:  O0

Заранее благодарю!

rami

Цитата: acer от 12 августа 2016, 18:06Что такое консолидация - слышал. Даже честно пробовал разными способами применить функцию "Объединить данные...", но в результате получается какая-то х... :(
Применить функцию "Объединить данные..." для ваших данных не получится, объединяемые данные должны быть числовые.
Цитата: acer от 12 августа 2016, 18:06Очень хочется, но не знаю можно ли как-то сделать так, чтобы на одном сводном листе была информация со всех листов, но обязательно в виде ссылок - скопировать-то у меня ума хватает, а хочется именно ссылками, чтобы изменив информацию на каком-то из тематических листов1-3 она автоматически поменялась на сводном листе13.

А вывод всех листов1-3 в лист13 нужен для возможной последующей сортировки всего этого массива по столбцу B, т.е. по хронологии.
Посмотрите такой способ:

acer

Цитата: rami от 12 августа 2016, 19:04Применить функцию "Объединить данные..." для ваших данных не получится, объединяемые данные должны быть числовые.
Ага! Вот оно почему у меня такая непонятность получалось, а в справке ни слова ни полслова, что все эти штуки применительны только к числовым данным
Цитата: rami от 12 августа 2016, 19:04Посмотрите такой способ:
О! Интересно.
...
А есть способ такие ссылки делать быстро, а не вручную к каждой ячейке?
...
Сортировка работает как надо - сначала по году, потом по номерам
...
И всё сломалось - изменение форматирования не передаётся, а это, к сожалению, тоже очень нужная вещь - попробуйте сами в исходных листах сделать стандартные изменения текста - болд, италик, подчёркнутый - они, увы, не меняются на листе13. Попробовал сохраниться и перезагрузиться... Даже закрывал и открывал - всё равно формат шрифта не передается... Печаль.

kompilainenn

Цитата: acer от 12 августа 2016, 21:09всё равно формат шрифта не передается...
имхо, формат шрифта зависит от форматирования целевой ячейки и посредством ссылки формат не передается
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

acer

Цитата: kompilainenn от 12 августа 2016, 23:48
имхо, формат шрифта зависит от форматирования целевой ячейки и посредством ссылки формат не передается
А может быть в формате ячеек есть такой формат as is?

Чем дальше копаю, тем больше удивляюсь - если в екселе 100500 листов, то неужели ни разу не придумали и не подумали, что может понадобиться собрать все листы в одном в том же файле. И это при ноу-хау в виде ссылок на другие файлы и прочих "плюшках".

rami

Цитата: acer от 12 августа 2016, 21:09А есть способ такие ссылки делать быстро, а не вручную к каждой ячейке?
Два варианта:
1. Сделать ссылку на левую верхнюю ячейку и "растянуть" её до размеров исходного диапазона (как я сделал)
2. Скопировать диапазон и Вставить как... см. картинку
Удобно воспользоваться опцией Сдвинуть ячейки вниз, вставка всегда в ячейку A1, ранее вставленные данные смещаются вниз (копировать данные сначала с предпоследнего листа, в конце с первого)
Цитата: acer от 12 августа 2016, 21:09И всё сломалось - изменение форматирования не передаётся, а это, к сожалению, тоже очень нужная вещь - попробуйте сами в исходных листах сделать стандартные изменения текста - болд, италик, подчёркнутый - они, увы, не меняются на листе13. Попробовал сохраниться и перезагрузиться... Даже закрывал и открывал - всё равно формат шрифта не передается... Печаль.
Связями передаются значения, а не форматы.

Вообще ваш документ очень лохматый O0 — отсутствуют заголовки столбцов — из-за чего не понятно сколько столбцов в каждой таблице, ширина столбцов на разных листах разная. С такой организацией данных в документе результатом будет УГ :'(.

acer

#6
Цитата: rami от 13 августа 2016, 08:37Два варианта:
1. Сделать ссылку на левую верхнюю ячейку и "растянуть" её до размеров исходного диапазона (как я сделал)
2. Скопировать диапазон и Вставить как... см. картинку
Удобно воспользоваться опцией Сдвинуть ячейки вниз, вставка всегда в ячейку A1, ранее вставленные данные смещаются вниз (копировать данные сначала с предпоследнего листа, в конце с первого)
Спаcибо, буду пробовать! Вариант "Вставить как..." самый простой и удобный и смещать ячейки без надобности, т.к. итоговый массив сортируется сначала по столбцу А (по году) и по столбцу В (по номеру) - в итоге вся последовательность будет выглядеть иначе, чем вставленная до сортировки.

Цитата: rami от 13 августа 2016, 08:37Вообще ваш документ очень лохматый Афро — отсутствуют заголовки столбцов — из-за чего не понятно сколько столбцов в каждой таблице, ширина столбцов на разных листах разная. С такой организацией данных в документе результатом будет УГ
Заголовки - дело наживное, как и единообразие ширины. Она, кстати, разная потому что изначально делал делал список по-порядку в одном листе, а потом уже разносил из него на листы с тематикой путём копирования ячеек из исходного листа. И при копировании - ширины ячеек не копируются, однако, поэтому ни новых листах делал её "на глаз".
ООО! Придумал - при добавлении чистого листа надо всего лишь его выделить вместе с листом с информацией и тогда при изменении ширины ячеек на листе с текстом - они меняются на всех выделенных листах, включая чистый! Век живи...

А форматы, выходит, вообще никак нельзя перенести? Чисто философски - странно, ведь форматирование ячеек, цвет - очень часто применяется для выделения информации. Если нет - буду думать - на первый взгляд приходит на ум добавить несколько колонок, в которых буду плюсики ставить, например.

PS. УУУ! А на сводном листе, куда вставил связи поиск-то работать перестал :(

rami

Цитата: acer от 13 августа 2016, 14:42... изначально делал делал список по-порядку в одном листе, а потом уже разносил из него на листы с тематикой путём копирования ячеек из исходного листа.
Честно говоря, я не понимаю что вы хотите. Вы сначала сделали список, затем разнесли его, а теперь хотите его снести обратно ??? А не лучше весь каталог (все данные) сделать на одном листе, а потом фильтровать (искать) по критериям?

acer

#8
Цитата: rami от 13 августа 2016, 16:46Честно говоря, я не понимаю что вы хотите. Вы сначала сделали список, затем разнесли его, а теперь хотите его снести обратно Непонимающий А не лучше весь каталог (все данные) сделать на одном листе, а потом фильтровать (искать) по критериям?

Не. не пойдёт.
Объясняю - у меня два таких файла.

Первый по сути - каталог марок, в котором на первом листе заведена хронология вообще всех марок СССР+РФ, с отметками тех, которые у меня есть, а на последующих листах в котором вручную скопировал ячейки по тематике (может быть имело смысл добавить в каждую ячейку какую-нибудь уинкальную комбинацию букв для фильтрации на этом же единственном листе типа "КСМС", "РЧФЛТ", "СМЛТ", "ГДВЩНВМВ" и т.п., но уж сделал как сделал).

Тех марок, которых нет, но есть желание найти - скопировал листы тематик в во второй файл и по мере нахождения строки с этими марками из этого второго файла просто удаляю (а в первом полном каталоге делаю отметку, что эта марка теперь есть).

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

Необходимость же в сохранении формата вызвана исключительно отметками разного статуса заказа - часто бывает так, что например марка есть у нескольких продавцов по разным ценам (ставлю их в разные столбики), когда делаю оплату, то отмечаю марку в пути подчёркиванием, а по факту пришедшего письма оказывается, что её забыли положить или перепутали или не то качество.

Но с форматом - дело наживное, можно будет по-другому цену ставить - например не 50, а 50` - для поиска по столбцу продавца Ctrl-­­­вниз/Ctrl-вверх работает такая ячейка будет работать, а для автосуммы выделением столбца - уже нет. И убирать апостроф после оплаты.

Вот.

Ваш совет "Вставить как... " с отметкой "связь" самый удобный на текущий момент.  :beer: А то, что поиск по этому листу не работает - ну и ладно. Зато работает по остальным с галкой "поиск во всех листах", просто уже привык к Ctrl-F вместо Ctrl-H :)

Пойду внесу вклад в развитие Форума  :beer: с карты 8123

rami

Цитата: acer от 13 августа 2016, 17:25А то, что поиск по этому листу не работает - ну и ладно. Зато работает по остальным с галкой "поиск во всех листах", просто уже привык к Ctrl-F вместо Ctrl-H
А ищите вы где в формулах или в значениях? Надо искать в значениях

acer

#10
Точно! Заработало. Внимательность и еще раз внимательность.

И снова рано радовался, ёлы-палы...

Не понял Ваш способ создания ссылок "№1. Сделать ссылку на левую верхнюю ячейку и "растянуть" её до размеров исходного диапазона (как я сделал)" - у меня почему-то только дублирует эту первую ячейку при попытке "растянуть", но если способы равноценны, то сделал способом №2 - выделил диапазон на листах тематик, скопировал, переключился на итоговый лист, вставить как... "связь" - вроде ура!

Но почему (?!) пустые ячейки из тематик не вставились ссылками на итоговом листе - там просто ПУСТО. => когда я вношу изменения на листе тематики, то это срабатывает только для тех ячеек, где была какая-то информация. Если вместо пустого места вношу чего-то, то оно при этом конечно же не переносится на итоговый лист. Блин!

Вот приложил уже выровненный по ширине, с заголовками столбцов и даже перенесённый список на "лист13" - ссылки ни листе13, как видите, не по всему диапазону, хотя копировал всё (например, для листа НГ диапазон выделил и скопировал - A3:I7) Галка "пропуск пустых ячеек" не стоит.

И заметил глюк-не глюк - если скопировать только одну строку, то она вставляется как надо (пример в самом низу "листа13" - скопировал из листа "пресса" только строку про газету Искра (A2:I2)

PPS. Да ужж... Если копируемый массив больше одной строки, то связи получаются только для ячеек с информацией - пустые тупо игнорируются.
А если копировать построчно (что само по-себе не есть правильно), то связи получаются для всех копируемых ячеек, как и задумывалось, но при этом "волшебным" образом все пустые ячейки начинают пестреть "0" и никак его не убрать - и визуально загромождает лист и мешает быстрому переходу через Ctrl-стрелки вверх/вниз.
То понос, то золотуха... :)

rami

Цитата: acer от 13 августа 2016, 17:58Не понял Ваш способ создания ссылок "№1. Сделать ссылку на левую верхнюю ячейку и "растянуть" её до размеров исходного диапазона (как я сделал)" - у меня почему-то только дублирует эту первую ячейку при попытке "растянуть", но если способы равноценны
Способы не равноценны. В первом способе при растягивании ячейки устанавливаются связи и с пустыми ячейками, во втором пустые ячейки остаются пустыми.

На пустом листе ткните курсором в ячейку (А1) и нажмите =, переключитесь на другой лист, нажмите курсором нужную ячейку и нажмите ENTER — установится связь на выбранную ячейку. Тяните за чёрный квадратик в нижнем правом углу ячейки сначала вправо, отпустите и не снимая выделения снова тяните его же вниз.

Цитата: acer от 13 августа 2016, 17:58Но почему (?!) пустые ячейки из тематик не вставились ссылками на итоговом листе - там просто ПУСТО. => когда я вношу изменения на листе тематики, то это срабатывает только для тех ячеек, где была какая-то информация. Если вместо пустого места вношу чего-то, то оно при этом конечно же не переносится на итоговый лист. Блин!
Экономят связи ;D

Можно заменить абсолютные ссылки на относительные (убрать $ из формул) тогда можно будет растягивать формулы после Вставить как. Ставите курсор в любую ячейку с формулой, жмёте Выбрать всё, затем жмёте на F4 пока $ перед номером строки в формулах не исчезнет. Можно растягивать.

Цитата: acer от 13 августа 2016, 17:58Да ужж... Если копируемый массив больше одной строки, то связи получаются только для ячеек с информацией - пустые тупо игнорируются.
А если копировать построчно (что само по-себе не есть правильно), то связи получаются для всех копируемых ячеек, как и задумывалось, но при этом "волшебным" образом все пустые ячейки начинают пестреть "0" и никак его не убрать
Наверно глюк. Нули можно скрыть. В Параметры —> LibreOffice Calc снять птичку с Нулевые значения

acer

#12
Цитата: rami от 13 августа 2016, 19:50Способы не равноценны. В первом способе при растягивании ячейки устанавливаются связи и с пустыми ячейками, во втором пустые ячейки остаются пустыми.
Да ужжж. Заметил эту разницу, когда стал делать по Вашему описанию метода №1:

Цитата: rami от 13 августа 2016, 19:50На пустом листе ткните курсором в ячейку (А1) и нажмите =, переключитесь на другой лист, нажмите курсором нужную ячейку и нажмите ENTER — установится связь на выбранную ячейку. Тяните за чёрный квадратик в нижнем правом углу ячейки сначала вправо, отпустите и не снимая выделения снова тяните его же вниз.
Отлично! Всё получилось. В таком случае "Вставить как..." вычеркиваем из способа в виду проявления интеллекта и некопировании пустых ячеек.

Результат сортирую по двум колонкам - А и B, т.к. возможно повторение номеров СССР и РФ.

Отлично! Галку "нулевые значения" в параметрах снял - нули пропали, но зараза такая - все-равно пустые ячейки участвуют при быстром перемещении Ctrl-вверх/вниз.

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

А при выделении итогового отсортированного списка и копировании - копирует только отображаемые строки, а скрытые - нет, что тоже не может не радовать! Неужели всё заработало :)

Бью челом, боярин, помог старику! :)  :beer::beer: O0