Как сослаться на файл, в названии которого присутствует актуальная дата

Автор celler, 23 июля 2018, 19:22

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

celler

Как сослаться на файл, в названии которого присутствует актуальная дата, которая каждый день меняется. Например в функции VLOOKUP есть ссылка на таблицу
'file:///D:/Downloads/23.07.2018aufträge.xlsx'#$Heute.$B1:$D1048576
если я сделаю что-то типа такого =CONCATENATE("'file:///D:/Downloads/";TEXT(TODAY();"TT.MM.JJJJ");"aufträge.xlsx'#$Heute.$B1:$D1048576")
то функция уже не работает.

mikekaganski

Функция INDIRECT. Помните, что этим Вы сделаете документ несовместимым с Excel (ну, или нужно учитывать разный синтаксис строки адреса).

Подробнее:
В Excel ссылка на внешний файл имеет синтаксис (например)
'file:///C:/Users/user/dir/[filename.xlsx]Sheet1'!$A$1
В Calc та же ссылка будет выглядеть
'file:///C:/Users/user/dir/filename.xlsx'#$Sheet1.$A$1

Можно видеть, что есть отличия в разделителе имени файла от остальной части (квадратные скобки вокруг имени (без пути) в Excel, #$ после имени в Calc); в обрамлении одинарными кавычками (полное имя файла плюс имя листа в Excel; только полное имя файла в Calc); в разделителе имени листа и адреса ячейки на листе ("!" в Excel, "." в Calc). Возможны варианты синтаксиса, например, относительные пути или синтаксис типа "C:\Users\..." вместо URL, но принцип остаётся: синтаксис Excel и Calc различны. При сохранении и открытии обычных формул, в которых адреса записаны напрямую, преобразования производятся обеими программами автоматически; а вот если адреса формируются в виде строк, никакого автоматического преобразования не происходит, и если получившийся адрес не подходит программе, то будет ошибка "#REF!".

С учётом того, что Вы ссылаетесь на XLSX, вероятно, что для Вас актуальна совместимость с Excel. Это можно реализовать по-разному.

Во-первых, можно усложнить формулу так, чтобы она включала обработку ошибок:
=IFERROR(INDIRECT("'file:///C:/Users/user/dir/filename.xlsx'#$Sheet1.$A$1");INDIRECT("'file:///C:/Users/user/dir/[filename.xlsx]Sheet1'!$A$1"))

Во-вторых, в Calc можно настроить синтаксис, совместимый с Excel: OptionsLibreOffice CalcFormula. Здесь стоит задать Formula Syntax: Excel A1. Кроме того, отдельно синтаксис адресов можно задать в Detailed Calculation Settings: Custom (Details...)→Reference syntax for string reference. Настройки в последнем случае можно сохранить отдельно для файла.

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

celler

mikekaganski
Спасибо! Однако я пробовал и в Excel и в Calc с помощью Indirect и CONCATENATE сформмировать ссылку и сослаться на неё, пробовал и в другой ячейке её формировать, но почему-то всё это не работает.

С Indirect вообще ерунда какая-то получается,- если я ссылаюсь на область в том же файле, то всё нормально, но если я пытаюсь сослаться на область в другом файле в сети, то всегда выдаёт REF!

JohnSUN

Так, может, проблема не в INDIRECT'е, а в FORMAT'е? Уверен, что =CONCATENATE("'file:///D:/Downloads/";TEXT(TODAY();"TT.MM.JJJJ");"aufträge.xlsx'")
даёт правильное имя файла?
Опять же - возможно! - стоит попробовать просто "'D:\Downloads\<форматированная дата>aufträge.xlsx'" в "традиционной" записи.
И не совсем понятно "в другом файле в сети"... Какой адрес реально пытаешься сформировать? Может быть тебе вместо "file:/" нужен какой-нибудь "smb:/"?
(И как вариант "на крайний случай" - может быть, макрос с выдергиванием нужных данных справится лучше и быстрее, чем формула?)
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

celler

JohnSUN
Уверен, что CONCATENATE формирует правильный адрес с именем файла, поскольку делал много раз и сравнивал варианты с простым текстом, вариант с "D:\Downloads..." тоже пробовал и с кавычками много вариантов перепробовал. Адрес именно такой на диске D того же компьютера брал специально для простоты. Макрос не подойдёт.
А у тебя действительно INDIRECT со ссылкой на другой файл выдаёт корректный результат?

JohnSUN

Врать не буду - диапазон до 1:1048576 брать постеснялся  ;)
Но
{=INDIRECT("'file:///F:/Test/"&TEXT(TODAY();"DD.MM.YYYY")&" test.ods'#$Sheet1.A1:D25")}
отрабатывает вполне пристойно
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

mikekaganski

Цитата: JohnSUN от 24 июля 2018, 18:44
Так, может, проблема не в INDIRECT'е, а в FORMAT'е? Уверен, что =CONCATENATE("'file:///D:/Downloads/";TEXT(TODAY();"TT.MM.JJJJ");"aufträge.xlsx'")
даёт правильное имя файла?

[печально]это тот случай, когда идиотский Excel использует локализованные строки форматирования (при невозможности задать язык книги или ячейки!). OOo, к сожалению, последовал этому примеру для некоторых языков (включая немецкий). К моей радости, в последних ЛО параллельно с локализованными поддерживаются стандартные коды (но это, опять же, несовместимо с Excel).[/печально]

Excel строго требует, чтобы книга, на которую ссылается INDIRECT, была открыта (см. https://support.office.com/en-us/article/How-to-Correct-a-REF-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be).

Вот пример, который у меня сегодня :-) работает и в ЛО, и в Excel (в последнем случае, конечно, предварительно открыв 25.07.2018aufträge.xlsx).
Сам файл 25.07.2018aufträge.xlsx приложить не могу - он 36 Мб; но сформирован он примитивно: переименовал лист в Heute; выделил столбец B и заполнил его (SheetFill CellsSeries...; Start Value: 1); выделил столбцы C и D и заполнил их (SheetFill CellsRandom Number...; Minimum: -1000; Maximum: 1000).
С уважением,
Михаил Каганский

JohnSUN

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

celler

mikekaganski
JohnSUN
Спасибо, буду разбираться. Я уже тоже подумал, может дело в том, что и Calc дома и Excel на работе немецкие и хотел попробовать проинсталлировать английскую версию. А сегодня на работе помучил Excel и обнаружил, что INDIRECT корректно работает с простой ссылкой в виде текста, но совершенно не воспринимает кавычки, поэтому ни с CONCATENATE ни с & ничего и не получается. Ещё интересно, что в Calc если переключиться на английские формулы, то DD.MM.YYYY не работает, а остаётся на немецкий манер TT.MM.JJJJ, но это к проблеме конечно не относится.
А диапазон 1:1048576 совершенно не важен, реально там порядка десятка тысяч строк, так было в Excel, а в нём с этим нет никаких проблем, вот если выбрать хотя бы на одну строку меньше, тогда да.
ЦитироватьExcel строго требует, чтобы книга, на которую ссылается INDIRECT, была открыта
Тогда вероятно моя задача нерешаема.

mikekaganski

Цитата: celler от 25 июля 2018, 18:29
Я уже тоже подумал, может дело в том, что и Calc дома и Excel на работе немецкие и хотел попробовать проинсталлировать английскую версию.
У Вас ЛО или АОО? ЛО не бывает "немецким" - он один для всех языков. В нём есть языковые настройки - локаль и язык интерфейса.
Цитата: celler от 25 июля 2018, 18:29Ещё интересно, что в Calc если переключиться на английские формулы, то DD.MM.YYYY не работает, а остаётся на немецкий манер TT.MM.JJJJ, но это к проблеме конечно не относится.
Но при этом если ячейке задать английский язык в формате ячейки (вкладка Numbers), то будет работать DD.MM.YYYY (правда, в XLSX не сохранится - формат не позволяет - и после перезагрузки документа снова будет работать только TT.MM.JJJJ).
С уважением,
Михаил Каганский

celler

mikekaganski
Спасибо! Как всё сложно. В выходные попробую вплотную позаниматься. У меня ЛО.

celler

В общем, сегодня добился, что в LO 6.0.5.2 (x64) на немецкой локали всё заработало. Для этого пришлось сделать две папки test на дисках C и D и положить в них специально подготовленный файл 28.07.2018.ods, затем сделать ещё одну копию этого же файла в той же папке test на диске C и эту копию постепенно усложнять, проверяя работоспособность. Я приложил результирующий файл, который можно использовать точно также как я, но без модификации только сегодня и только в немецкой локали с её TT.MM.JJJJ. В результате оказалось, что причиной моих мытарств, по крайней мере с LO, были вовсе не кавычки, а тот факт, что функция INDIRECT работает со ссылками корректно только в том случае, если её опциональный параметр вообще не трогать. Достаточно только в ячейке A1 один раз кликнуть и значение становится REF! и никаким образом больше не восстанавливается.
Всем большое спасибо, на работе буду пробовать добиться того же от Excel, в принципе ограничение на необходимость открытия файла для работы ссылок там не столь критично.

kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

mikekaganski

Цитата: celler от 28 июля 2018, 12:16
... а тот факт, что функция INDIRECT работает со ссылками корректно только в том случае, если её опциональный параметр вообще не трогать. Достаточно только в ячейке A1 один раз кликнуть и значение становится REF! и никаким образом больше не восстанавливается.

Это о чём? Что значит "кликнуть в ячейке A1"? Конечно, возможно, что имеется ввиду щелчок мышью в поле ввода опционального параметра с именем А1 в мастере функций... но уж больно формулировка намекает на странную интерпретацию параметра.
С уважением,
Михаил Каганский

celler

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

Добавление
Кстати, до сих пор не знаю как правильно называются клетки? в таблице. По немецки это Zelle, что переводится как ячейка или клетка, не поле ввода же? Даже статья в Википедии Электронная таблица умудрилась обойтись без этого слова.