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

Автор milstud, 23 марта 2012, 10:36

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

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, просто "посыпятся"...
Лучше использовать именованные диапазоны и уже их именами манипулировать в формулах.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

milstud

#2
спасибо огромное! заработало! хотя я вчера пол дня убил на эту простую вещь. при этом 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...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

milstud

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

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

JohnSUN

Цитата: milstud от 23 марта 2012, 12:07
на сколько я понял, это для того, что бы формула выдавала ошибку типа Н/д? в случае изменении имени листа
Извини, не понял... Если я переименовываю лист, то в именованном диапазоне адрес автоматически меняется, имя листа меняется на новое имя...
Цитата: milstud от 23 марта 2012, 12:07
но сама процедура создания именнованного диапазона ручная, т.е. на каждом листе надо указывать диапазон?
Может быть можно придумать и другой способ  ;) Зависит от задачи и назначения данных в листах...
Цитата: milstud от 23 марта 2012, 12:07
а если количество листов заранее неизвестно?
Ну, извини... В примере из твоего первого сообщения про это ничего не было. Сослаться в формуле на данные из листа который "то ли будет, то ли нет" довольно сложно.
Цитата: milstud от 23 марта 2012, 12:07
еще попутный вопрос (дабы не плодить новую тему)
есть n-е количество CSV файлов, которые надо открыть в одной книге (Лист1_n). это очень успешно получается, если делать через добавить лист - из файла.
может есть возможность выделить все файлы и открыть их сразу в 1 книге?
Знаешь, это наверное все-таки новая тема. Причем не в этом разделе, а в Base. Ты с Base работать не пробовал? Попробуй создать новую базу данных на основании каталога с набором CSV-файлов (когда запустится Мастер создания базы выбери тип файла "Текст" и дополнительно укажи CSV). С помощью довольно простых действий получишь базу данных, значения из которой сможешь использовать и в Calc, и, при необходимости, во Writer.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

neft

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

milstud

Цитата: neft от 23 марта 2012, 12:53
Постепенно движемся в сторону использования макросов?
так тут и ежу понятно, что макросами было бы удобнее, к сожалению задачу надо решить быстро, а вот макросы писать я не умею. и за 2 дня врядли успею научиться. Но (!) разобраться придется
Цитата: JohnSUN от 23 марта 2012, 11:48Ты с Base работать не пробовал? Попробуй создать новую базу данных на основании каталога с набором CSV-файлов (когда запустится Мастер создания базы выбери тип файла "Текст" и дополнительно укажи CSV). С помощью довольно простых действий получишь базу данных, значения из которой сможешь использовать и в Calc, и, при необходимости, во Writer.
та как всегда хочется быстро и легко, а получается долго и не легко. попробую BASE. Спасибо за ответы, очень помогли!

JohnSUN

А что именно нужно сделать за два дня с пачкой CSV-файлов? Может там и делов-то на полчаса?
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

milstud

#9
Цитата: JohnSUN от 23 марта 2012, 13:17
А что именно нужно сделать за два дня с пачкой CSV-файлов? Может там и делов-то на полчаса?

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


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

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

JohnSUN

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

freeGHost

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

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

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