Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ссылки на ещё не созданные листы
 
#1
06.07.2022 10:47:32
Здравствуйте, прошу помощи следующем вопросе. Возможно ли как создавать ссылки на ещё не созданный лист excel.
На примере - есть общая таблица и она должна собирать данные с Лист1, Лист2, и т.д. но в начале работы существует только Лист1. Лист2 и прочие создаются копированием и изменением Листа1. Названия листов заранее известны, но формулу написать невозможно на пока ещё не созданный Лист.
Помогает ДВССЫЛ, но формула получается статической, при добавлении строк путь оказывается неверен.
Пример прикладываю.
Существует ли какое решение без ДВССЫЛ и по возможности макросов?
 
 
 
#2
06.07.2022 10:57:15
Евгений Иванов,
можно попробовать вставить формулу макросом при создании листа
Код
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "создан лист"
End Sub
Изменено: evgeniygeo - 06.07.2022 11:06:52
 
 
 
#3
06.07.2022 11:00:09
evgeniygeo,
спасибо! Как вариант можно.
Странно что стандартными средствами это сделать наверно невозможно.
Ну выдавала бы формула ошибку без листа, а с появлением его начинала работать.
Изменено: Евгений Иванов - 06.07.2022 11:09:29
 
 
 
#4
06.07.2022 11:52:44
Евгений Иванов,
еще вариант
 
 
 
#5
06.07.2022 12:57:29
Код
=ЕСЛИОШИБКА(ДВССЫЛ(A3&"!A1");0)
- для третьей строки, т.е. для Лист2
Изменено: Xel - 06.07.2022 12:58:17
 
 
 
#6
06.07.2022 13:15:47
Xel, принципиальной разницы с формулой =ДВССЫЛ("Лист2!A1") нет. И при смещении строк или столбцов ДВССЫЛ не увидит данных в ячейке, как будто она закреплена $$. При добавлении строки допустим данные перейдут в ячейку А2, но ДВССЫЛ их не увит, у него А1 прописана. Вы просто убираете ошибку когда листа нет, но это не существенно.
ДВССЫЛ тут не подходит видимо.
 
 
 
#7
06.07.2022 13:20:52
видимо, из формул  подходит ТОЛЬКО! ДВССЫЛ и ничего кроме нее
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
06.07.2022 13:20:55
evgeniygeo, этот вариант ещё лучше, благодарю!
Видимо придётся всё же с макросами)
Интересно что при удалении, допустим, Листа4 и опять его добавлении ссылка ломается.
 
 
 
#9
06.07.2022 13:26:48
Ігор Гончаренко, да, не так я выразился. Подходит, но решает только половину проблемы. Может в новых версия офиса добавят такую возможность.
Для меня странно что при удалении листа ссылки на него "ломаются" навсегда. Даже если потом этот лист вернуть. Как бы вот он, ссылки то рабочие, давай работай)
 
 
 
#10
06.07.2022 13:32:18
если обьяснить условия задачи - то, скорее всего, можно и ее решить
и совершенно понятно, никак не решится не описанная вами задача, как бы сильно вы о ней не думали, пока не расскажете тому, кто может решить - результата не будет
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
06.07.2022 13:44:48
Ігор Гончаренко, попробую упростить задачу, может так будет ясней: в книге есть два Листа 1 и 2. В Листе 1 ссылка на любую ячейку Листа 2 типа простой =Лист2!A1.
При удалении Листа2 из книги ссылка ломается и приобретает вид =#ССЫЛКА!A1, вместо того что бы выдать просто ошибку. Соответственно опять добавив Лист 2 мы не получим на Листе 1 никаких значений, ссылка исчезает.
Похожее происходит и если прописать на Листе 1 =Лист2!A1 заранее, т.е. пока Лист 2 не создан. Создать его, но ссылка при этом работать не будет. Не странно ли?
 
 
 
#12
06.07.2022 13:48:46
для меня это очевидно и совершенно не станно
может перестанем выяснять что странно, а что нет и уточним задачу? (если вас все еще интересует решение)
Изменено: Ігор Гончаренко - 06.07.2022 13:49:27
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#13
06.07.2022 13:54:45
Ігор Гончаренко, планировался образец для заполнения на 5 листов и получившиеся данные с 5 листов выборочно собираются на один лист. Но изначально в книге только Лист1, который заполняется, потом копируется как Лист 2 изменяется и т.д. до 5 каждый лист последовательно немного изменяется от предыдущего. Кол-во строк тоже немного может меняться (почему ДВССЫЛ плохо подходит).
И вот на том листе куда всё собирается в итоге нельзя заранее прописать ссылки на Лист2, 3... т.к. их пока ещё нет.
А если таких файлов с десяток в день проходит и это сильно бы упростило работу.
Изменено: Евгений Иванов - 06.07.2022 14:02:06
 
 
 
#14
06.07.2022 15:31:33
см. вложение
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#15
06.07.2022 15:43:05
Ігор Гончаренко, такое же решение уже предлагал Xel, что неплохо работает для таблиц где неизменно количество строк. Однако весь смысл excel теряется, если не можешь добавить ещё строку или столбец и вся формула не работает. Получается калькулятор с ручным пересчётом просто.
Цитата
Xel, написал:
=ЕСЛИОШИБКА(ДВССЫЛ(A3&"!A1");0)
Цитата
Евгений Иванов, написал:
принципиальной разницы с формулой =ДВССЫЛ("Лист2!A1") нет. И при смещении строк или столбцов ДВССЫЛ не увидит данных в ячейке, как будто она закреплена $$. При добавлении строки допустим данные перейдут в ячейку А2, но ДВССЫЛ их не увит, у него А1 прописана. Вы просто убираете ошибку когда листа нет, но это не существенно.ДВССЫЛ тут не подходит видимо.
У меня сейчас так и работает через ДВССЫЛ, но ограничен тем что нельзя менять размеры таблиц(
Но спасибо!
Изменено: Евгений Иванов - 06.07.2022 15:46:47
 
 
 
#16
06.07.2022 15:51:14
Цитата
Евгений Иванов написал:
но ограничен тем что нельзя менять размеры таблиц
это ваше ограничение в знаниях в области Excel а не в возможностях ДВССЫЛ
сможете обьяснить задачу - возможно будет решение, но интереса толочь воду в ступе нет
и интерес к вашей теме и, главное, способу подачи информации катастрофически падает с каждым бестолковым постом в этой теме
не цедите информацию через зубы, как партизан, а четко, понятно и желательно коротко что за задачу вы решаете?

внимательно прочитайте название вашей темы
так же внимательно посмотрите файл из сообщения 14
видите в нем ссылки на несуществующие листы, которые начинают работать как только появляется лист. что еще? это-ли не ответ на поставленный вами вопрос?
есть другой вопрос? создавайте новую тему, формулируйте свой вопрос, может и на него ответ найдется))
а тут дальше без меня
удачи!
Изменено: Ігор Гончаренко - 06.07.2022 16:04:38
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#17
06.07.2022 16:24:49
Ігор Гончаренко, изначально в вопросе я всё прописал. Что конкретно не получается с ДВССЫЛ и даже просил без оной. Надо просто читать и вопрос, а не только название темы. В названии темы все нюансы разве уместишь, зачем тогда сам вопрос? За удачу спасибо!
Цитата
написал:
Помогает ДВССЫЛ, но формула получается статической, при добавлении строк путь оказывается неверен.Пример прикладываю.Существует ли какое решение без ДВССЫЛ и по возможности макросов?
Цитата
написал:
Кол-во строк тоже немного может меняться (почему ДВССЫЛ плохо подходит).
 
 
 
#18
06.07.2022 17:14:58
Если нужна динамическая ссылка, то также нужно и описание того, как эту динамику считать. Не просто - вставили строчку, ссылка сместиться должна, а привязку к чему делать нужно? Названия какие-то в столбце А, шапка в строке 1, еще что-то... Потому и писал Игорь про партизана
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#19
06.07.2022 17:16:46
вы бы пример сделали получше,  с вариантами расположения данных, ведь строки сдвигаются в листах не просто так, а с целью. может есть вод=зможность решить вашу задачу не вашим способом.
 
 
 
#20
06.07.2022 17:31:26
Цитата
Евгений Иванов написал:
Вы просто убираете ошибку когда листа нет, но это не существенно.
Спасибо за объяснение, конечно.
Но вообще я думала, что  добавила в формулу ссылку на название листа из первого столбца, раз уж названия заранее известны.
Что вы считаете "динамикой"  - вопрос очень философский.
Изменено: Xel - 06.07.2022 17:31:44
 
 
 
#21
06.07.2022 17:51:20
_Boroda_, Vik_tor, теперь я понял в чём мой косяк, Игорь как то весьма расплывчато, но красиво говорит.

Выкладываю конкретный пример. В лист ЭЭ ячейки D3:D14 собираются данные с листов С1, С2, С3 и т.д. ячеек I38.
Т.е. в D3 данные из С1, в D4 данные из С2 и т.д.
Лист ЭЭ и С1 сразу существуют в книге, далее С1 копируется, немного корректируется и переименовывается в С2. И так до необходимого количества листов. При этом количество строк на вновь созданном листе может измениться и ячейка I38 станет I40.
Если обычно эксель "понимает" когда мы прибавляем строки и сам переписывает формулу, то через ДВССЫЛ нет и она покажет на ту же ячейку, с неправильными данными.
 
 
 
#22
06.07.2022 18:14:41
Xel, вот что я имел в виду:
Цитата
написал:
При этом количество строк на вновь созданном листе может измениться и ячейка I38 станет I40.Если обычно эксель "понимает" когда мы прибавляем строки и сам переписывает формулу, то через ДВССЫЛ нет и она покажет на ту же ячейку, с неправильными данными.
Просто довольно сложно объяснить на примере с несуществующими листами)
 
 
 
#23
07.07.2022 07:26:32
del
Изменено: esheg - 07.07.2022 07:26:50
 
 
 
#24
07.07.2022 12:10:43
На ютубе можно найти некоторое количество роликов на тему "никогда так не делайте в эксель", и ваш файл - просто бинго по этим советам.
Использование ДВССЫЛ - вишенкой на торте, потому что эта функция идет в разряде "не пользоваться, если можно решить без нее".

Если исходный файл делали вы, и вы хотите все сам-сам, то вам лучше учебник почитать. Функции ВПР, ГПР, СУММПРОИЗВ, СУММЕСЛИМН. И перекроить исходник до осмысленного состояния.
Если исходники вам дают именно в таком виде и надо соорудить свод из них - вам, наверное, лучше в платную ветку.
Задача то именно свод сделать.
 
 
 
#25
07.07.2022 12:14:45
Евгений Иванов, Вот, давно бы так
=ЕСЛИОШИБКА(ИНДЕКС(ДВССЫЛ("'"&V3&"'!I1:I99");ПОИСКПОЗ(ЕСЛИ(ИНДЕКС(ДВССЫЛ("'"&V3&"'!M1:M99");ПОИСКПОЗ("Разрежение в топке котла (Sт)";ДВССЫЛ("'"&V3&"'!C1:C99");))=1;"Удельный расход условного топлива на 1 Гкал выработанной тепловой энергии брутто (Ву)";"Коэффициент полезного действия котла брутто  ");ДВССЫЛ("'"&V3&"'!C1:C99");));)
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#26
07.07.2022 13:11:25
_Boroda_, выражаю благодарность! Всё работает как надо! Получается она просто ищет необходимую строку по словам.
Я думал есть какой то вариант через СМЕЩ ещё, считать сколько прибавилось или убавилось строк и смещать результат. Но тут элегантней!
 
 
 
#27
07.07.2022 13:16:51
Xel, понимаю, просто эта таблица строго установленной формы государством. А в вопросе ДВССЫЛ потому и просил не использовать, но с не созданными листами работать может только он видимо. С ним решение и нашёл кстати _Boroda_.
Если можно обойтись без него буду очень признателен)

Цитата
написал:
Задача то именно свод сделать.
там и надо то выдернуть с каждого листа по одному числу.
 
 
 
#28
07.07.2022 13:19:09
25 сообщение могло бы быть 2-м сообщением в вашей теме, если бы в 1-ом сообщении была описана задача
26 сообщение было бы 3-м
а этого сообщения вообще никогда бы не было в вашей теме)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#29
07.07.2022 13:38:03
Ігор Гончаренко, все мы ошибаемся и можно прямо указать на ошибку, не все понимают витиеватые речи)
Извините за непонятливость)
 
 
 
Страницы: 1
Наверх