Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

22 Август 2018, 08:22 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Вы можете задать вопрос по LibreOffice или Apache OpenOffice  без регистрации, используя форму
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1 2 »   Вниз
  Печать  
Автор Тема: Как сослаться на файл, в названии которого присутствует актуальная дата  (Прочитано 1354 раз)
0 Пользователей и 1 Гость смотрят эту тему.
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Стартовое сообщение: 23 Июль 2018, 19:22 »

Как сослаться на файл, в названии которого присутствует актуальная дата, которая каждый день меняется. Например в функции 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
Ветеран
*****
Offline Offline

Пол: Мужской
Расположение: Хабаровск -> Москва
Сообщений: 990


« Ответ #1: 24 Июль 2018, 00:25 »

Функция 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. Настройки в последнем случае можно сохранить отдельно для файла.

Ну, и конечно, возможны оптимизации типа однократного определения в какой-нибудь ячейке правильного адреса, и использования ссылки на эту ячейку с адресом отовсюду, где Вам нужно обратиться к этому файлу.
« Последнее редактирование: 24 Июль 2018, 08:44 от mikekaganski » Записан

С уважением,
Михаил Каганский
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Ответ #2: 24 Июль 2018, 18:22 »

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

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

Пол: Мужской
Расположение: Киев
Сообщений: 2 530


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #3: 24 Июль 2018, 18:44 »

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

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Ответ #4: 24 Июль 2018, 19:52 »

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

Пол: Мужской
Расположение: Киев
Сообщений: 2 530


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #5: 24 Июль 2018, 20:21 »

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

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
mikekaganski
Ветеран
*****
Offline Offline

Пол: Мужской
Расположение: Хабаровск -> Москва
Сообщений: 990


« Ответ #6: 25 Июль 2018, 00:20 »

Так, может, проблема не в 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).

* ref.xlsx (5.43 Кб - загружено 3 раз.)
Записан

С уважением,
Михаил Каганский
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Online Online

Пол: Мужской
Расположение: Киев
Сообщений: 2 530


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #7: 25 Июль 2018, 09:07 »

Ну да, четыре ячейки чтобы обработать дату для трёх локалей... умельцы, что тут скажешь...
Сегодня у меня твой пример тоже работает...
Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Ответ #8: 25 Июль 2018, 18:29 »

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

Пол: Мужской
Расположение: Хабаровск -> Москва
Сообщений: 990


« Ответ #9: 25 Июль 2018, 20:32 »

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

С уважением,
Михаил Каганский
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Ответ #10: 25 Июль 2018, 22:08 »

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

Сообщений: 191


« Ответ #11: 28 Июль 2018, 12:16 »

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

* 28.07.2018.ods (12.2 Кб - загружено 3 раз.)
« Последнее редактирование: 28 Июль 2018, 12:21 от celler » Записан
kompilainenn
Ветеран
*****
Offline Offline

Сообщений: 2 412



« Ответ #12: 28 Июль 2018, 12:44 »

А это ограничение INDIRECT не баг ли?
Записан

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

Пол: Мужской
Расположение: Хабаровск -> Москва
Сообщений: 990


« Ответ #13: 28 Июль 2018, 19:41 »

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

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

С уважением,
Михаил Каганский
celler
Постоялец
***
Offline Offline

Сообщений: 191


« Ответ #14: 28 Июль 2018, 21:41 »

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

Добавление
Кстати, до сих пор не знаю как правильно называются клетки? в таблице. По немецки это Zelle, что переводится как ячейка или клетка, не поле ввода же? Даже статья в Википедии Электронная таблица умудрилась обойтись без этого слова.
« Последнее редактирование: 29 Июль 2018, 10:32 от celler » Записан
Страниц: 1 2 »   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!