динамические листы в формуле

[milstud]

Привет всем. Столкнулся с проблемой как в формуле подставить значение динамического листа, т.е. что бы при растягивании формулы менялся лист книги.
например формула =VLOOKUP("текст";Лист1_2.$E$1:$F$99;2;0) при растягивании не не меняет значение листа (что естественно, т.к. "лист1_2" название). а надо что бы при растягивании значение листа менялось на "Лист1_3", "Лист1_4" и т.д.
не найдя красивого решения решил сделать долгим путем: создал вспомогательную ячейку с формулой =CONCATENATE("Лист1_";ROW( )) которая дает нужный мне результат, но при добавлении в первую формулу =VLOOKUP("Системная плата";concatenate("Лист1_";ROW( )).$E$1:$F$99;2;0)  выдается ошибка использования скобок.
все пальцы истоптал уже в поиске решения, все глаза просмотрел(

[JohnSUN]

Добро пожаловать на форум!
Можно использовать функции INDIRECT (адрес по текстовой строке)
=VLOOKUP("текст";INDIRECT(CONCATENATE("Лист1_";ROW();".$E$1:$F$99"));2;0)
При этом диапазон ячеек (подчеркнут) тоже включается в CONCATENATE.

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

[milstud]

спасибо огромное! заработало! хотя я вчера пол дня убил на эту простую вещь. при этом INDIRECT тоже использовал. видимо где то ошибся, где то недосмотрел.
по поводу именнованного диапазона: при определении диапазона "данные-определить диапазон" диапазон определяется как "$Лист1_2.$E$1:$F$99" т.е. имя там тоже фигурирует, как в таком случае сделать изменяющийся лист?
и как этот диапазон вставить в формулу? имя диапазона сделал "hard"
VLOOKUP("текст";Лист1_2.$E$1:$F$99;2;0)

[JohnSUN]

Я имел в виду, что в имени диапазона добавляем число:
Spravochik_1 - $Лист1_2.$E$1:$F$99
Spravochik_2 - $Лист1_2.$K$18:$AA$199
Spravochik_3 - $Лист1_4.$A$1:$B$20
Spravochik_4 - $Данные.$B$4:$D$25... и так далее
Сами диапазоны мы теперь можем таскать по всей книге, переименовывать листы и творить прочие безобразия, а имена так и будут оставаться Spravochik_n. И значит в любом месте мы обращаемся к нему как
INDIRECT("Spravochik_"&ROW())
Ну, или с использованием CONCATENATE...

[milstud]

на сколько я понял, это для того, что бы формула выдавала ошибку типа Н/д? в случае изменении имени листа
но сама процедура создания именнованного диапазона ручная, т.е. на каждом листе надо указывать диапазон?
а если количество листов заранее неизвестно?

еще попутный вопрос (дабы не плодить новую тему)
есть n-е количество CSV файлов, которые надо открыть в одной книге (Лист1_n). это очень успешно получается, если делать через добавить лист - из файла.
может есть возможность выделить все файлы и открыть их сразу в 1 книге?

[JohnSUN]

на сколько я понял, это для того, что бы формула выдавала ошибку типа Н/д? в случае изменении имени листа

Извини, не понял... Если я переименовываю лист, то в именованном диапазоне адрес автоматически меняется, имя листа меняется на новое имя...

но сама процедура создания именнованного диапазона ручная, т.е. на каждом листе надо указывать диапазон?

Может быть можно придумать и другой способ  Зависит от задачи и назначения данных в листах...

а если количество листов заранее неизвестно?

Ну, извини... В примере из твоего первого сообщения про это ничего не было. Сослаться в формуле на данные из листа который "то ли будет, то ли нет" довольно сложно.

еще попутный вопрос (дабы не плодить новую тему)
есть n-е количество CSV файлов, которые надо открыть в одной книге (Лист1_n). это очень успешно получается, если делать через добавить лист - из файла.
может есть возможность выделить все файлы и открыть их сразу в 1 книге?

Знаешь, это наверное все-таки новая тема. Причем не в этом разделе, а в Base. Ты с Base работать не пробовал? Попробуй создать новую базу данных на основании каталога с набором CSV-файлов (когда запустится Мастер создания базы выбери тип файла "Текст" и дополнительно укажи CSV). С помощью довольно простых действий получишь базу данных, значения из которой сможешь использовать и в Calc, и, при необходимости, во Writer.

[neft]

Постепенно движемся в сторону использования макросов?

[milstud]

Постепенно движемся в сторону использования макросов?

так тут и ежу понятно, что макросами было бы удобнее, к сожалению задачу надо решить быстро, а вот макросы писать я не умею. и за 2 дня врядли успею научиться. Но (!) разобраться придется

Ты с Base работать не пробовал? Попробуй создать новую базу данных на основании каталога с набором CSV-файлов (когда запустится Мастер создания базы выбери тип файла "Текст" и дополнительно укажи CSV). С помощью довольно простых действий получишь базу данных, значения из которой сможешь использовать и в Calc, и, при необходимости, во Writer.

та как всегда хочется быстро и легко, а получается долго и не легко. попробую BASE. Спасибо за ответы, очень помогли!

[JohnSUN]

А что именно нужно сделать за два дня с пачкой CSV-файлов? Может там и делов-то на полчаса?

[milstud]

А что именно нужно сделать за два дня с пачкой CSV-файлов? Может там и делов-то на полчаса?

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


понимаю конечно что извращение)

[вложение удалено Администратором]

[JohnSUN]

Давай образцы! Тебе просто удивительно повезло! Совсем недавно здесь спрашивали именно про это - слить в один лист данные из пачки файлов... Если структура всех CSV одинакова, то эта задача, считай, уже решена!

[freeGHost]

Забрёл в тему из поисковика, специально зарегистрировался на форуме. Не корите за некромантию к теме, не мог удержаться.

Как я понял, изначально задача у автора темы была в том, чтобы собрать данные об аппаратном и программном обеспечении большого количества ПК.
Возможно, я кому-то помогу в реализации подобного решения без костылей и ручной обработки CSV.

Если задача стоит в сборе и актуализации сведений о парке ПК в компании, не нужно изобретать велосипед с ПО для локального ПК, можно развернуть клиент-серверную структуру на базе кросплатформенного ПО OCSInventory.
К серверу с заданной периодичностью подключаются клиенты и сообщают всю необходимую информацию о ПК.
А еще это всё можно совместить с системой OTRS через GLPI, чтобы управлять процессами в ИТ в соответствии с ITSM.