Не работает функция ДВССЫЛ при закрытом файле, на который ссылаюсь

@Proidoha · Регистрация: 04.11.2013

Студворк — интернет-сервис помощи студентам

Доброго времени!

Полная формула (чисто для ознакомления, в ней сам черт ногу сломит, разбираться не обязательно, проблемный кусок обозначил ниже с уже частично выполненными вычислениями):

Кликните здесь для просмотра всего текста

=ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))*СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))<>"";ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))*СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6))); ПРОСМОТР(2;1/('F:\[книга.xls]Logging Sheet'!$AK$12:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))*СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))<>"");'F:\[книга.xls]Logging Sheet'!$AK$12:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))*СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))) );ЕСЛИ('F:\[книга.xls]Logging Sheet'!$M6<>"";K6;""))

Формула работает, если открыт и файл с формулой, и файл F:\книга.xls. Если файл F:\книга.xls закрыт, то функция ДВССЫЛ возвращает ошибку #Ссылка. Что можно вместо нее использовать, или как побороть эту беду?

Как работаю с ДВССЫЛ (на каком этапе возвращает #Ссылка, последние 3 вычисления программы. проверял пошагово):
ДВССЫЛ('F:\[книга.xls]Logging Sheet'!&АДРЕС(15;35))
т.е. склеиваю ссылку из адреса листа в книге и адреса ячейки.Следующим шагом программа получает абсолютно правильную строку:
ДВССЫЛ('F:\[книга.xls]Logging Sheet'!&$AI$15)
Следующий шаг тоже без ошибок:
ДВССЫЛ('F:\[книга.xls]Logging Sheet'!$AI$15) - и вот после этого оно мне вместо ДВССЫЛ ставит #Ссылка.

Как мне "склеить" ссылку на ячейку в файле? Все предыдущие вычисления (которые ведутся по данным из этого же файла) работают, не работает только эта функция и ТОЛЬКО если файл F:\книга.xls закрыт.

@AlexM · 26.01.2017, 10:15

Функции для закрытых книг
Обычные: ВПР, ИНДЕКС, ПРОСМОТР, ПОИСКПОЗ, СУММПРОИЗВ
Массивные: СУММ, СЧЕТ
Там где в аргументе массив или таблица.

А так же обычные ссылки типа: =[файл]лист!A1

PS. без файла примера формулу понять не получается.

Добавлено через 36 минут
Что разобрать смог. В вашей формуле фрагмент (убрал ссылку на другую книгу)

Code
1
'Logging Sheet'!$AI$6:ДВССЫЛ("Лист3!"&АДРЕС(СУММПРОИЗВ(('Logging Sheet'!$AI$6:$AI$100=МАКС('Logging Sheet'!$AI$6:$AI$100))*СТРОКА($AI$6:$AI$100));СТОЛБЕЦ($AI$6)))

можно заменить на

Code
1
='Logging Sheet'!$AI$6:ИНДЕКС('Logging Sheet'!$AI:$AI;СУММПРОИЗВ(('Logging Sheet'!$AI$6:$AI$100=МАКС('Logging Sheet'!$AI$6:$AI$100))*СТРОКА($AI$6:$AI$100)))

т.е. убрана функция ДВССЫЛ() и АДРЕС(), которые не работают с закрытыми книгами.
Осталась МАКС(), но вдруг сработает. Еще понять зачем МАКС(), может быть без него можно обойтись. Посмотреть бы на данные столбца AI.

@Proidoha · 26.01.2017, 11:40 **[ТС]**

Сообщение от AlexM

т.е. убрана функция ДВССЫЛ() и АДРЕС(), которые не работают с закрытыми книгами.

Функции АДРЕС и МАКС работают. Вообще все функции (из используемых) работают, кроме ДВССЫЛ. Спасибо, сейчас разберусь с предложенной формулой, если не подойдет - вставлю упрощенный пример.

МАКС использую для определения конца диапазона. Формула универсальная для 400 файлов, одинаковых по структуре, но с разным количеством строк.

@AlexM · 26.01.2017, 11:57

номер последней строки в столбце AI с числами можно найти так

Code
1
=ПРОСМОТР(9^9;AI:AI;СТРОКА(AI:AI))

@Proidoha · 26.01.2017, 12:36 **[ТС]**

Сообщение от AlexM

номер последней строки в столбце AI с числами можно найти так

Спасибо, от громоздкой конструкции с СУМОПРОИЗВ и МАКС избавился.
Я могу после каждого "улучшения" кода заново его сюда вставлять, если требуется. Если так будет проще.

Код для ячейки D5 файла счет:

Кликните здесь для просмотра всего текста

=ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА('[исходник.xls]Logging Sheet'!$I:$I));СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$I$6)));0) + 5;СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6)))<>"";ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА('[исходник.xls]Logging Sheet'!$I:$I));СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$I$6)));0) + 5;СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6))); ПРОСМОТР(2;1/('[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА('[исходник.xls]Logging Sheet'!$I:$I));СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$I$6)));0) + 5;СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6)))<>"");'[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА('[исходник.xls]Logging Sheet'!$I:$I));СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$I$6)));0) + 5;СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6)))) );ЕСЛИ('[исходник.xls]Logging Sheet'!$C6<>""

6;""))

@AlexM · 26.01.2017, 14:24

Избавьтесь и от этого

Code
1
2
СТРОКА('[исходник.xls]Logging Sheet'!$I:$I)
СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6)

достаточно писать так

Code
1
2
СТРОКА($I:$I)
СТОЛБЕЦ($J$6)

В первом случае это массив чисел, а во втором просто число и к книгам и листам привязывать необязательно.
Даже вместо СТОЛБЕЦ($J$6) просто 10. Формула получится короче и понятнее. И таких замен можно сделать несколько.

@Proidoha · 26.01.2017, 15:17 **[ТС]**

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

Кликните здесь для просмотра всего текста

=ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))<>"";ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6))); ПРОСМОТР(2;1/('[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))<>"");'[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))) );ЕСЛИ('[исходник.xls]Logging Sheet'!$C6<>""

6;""))

Не по теме:

П.С. формула уже на 2 строки короче исходной, но сделать без ДВССЫЛ у меня пока не получается.

@AlexM · 26.01.2017, 15:36

Файл с формулой приложите.

@Proidoha · 26.01.2017, 15:50 **[ТС]**

Сообщение от AlexM

Файл с формулой приложите.

Блин, вроде прикладывал раньше. Извиняюсь.

По умолчанию работает в папке d:\Excel

@AlexM · 26.01.2017, 16:51

К сожалению у меня формулы не показываются, видно несовместимость 2003 и 2007. Редко, но так бывает
Словами расскажите, какое значение должна вернуть ваша формула в D5, D6 и D11?

@Proidoha · 26.01.2017, 18:15 **[ТС]**

Сообщение от AlexM

К сожалению у меня формулы не показываются, видно несовместимость 2003 и 2007. Редко, но так бывает

Ага, больше 7 уровней вложенности в формулах 2003 офис не поддерживает.

Убрал одну проверку, теперь в интервал 8.0-21.0 нули, да и пофиг, это "ЕСЛИ" я сам потом вставлю. А формула получилась на 2/3 короче. И в 2003 офисе открывается без проблем.

@AlexM · 26.01.2017, 20:46

Посмотрите вариант решения, но все равно есть проблема.
Все функции способны работать с закрытой книгой, но возвращается ошибка.

@Proidoha · 27.01.2017, 00:24 **[ТС]**

Сообщение от AlexM

Посмотрите вариант решения, но все равно есть проблема.

ППЦ, а я там навертел.....

Я проверил, что именно в Вашем варианте не работает.

Code
1
=ПРОСМОТР("яяя";'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$6:'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$8)

Эта функция работает, если начальная и конечная ячейки массива либо одинаковые, либо соседние. Если между ними еще хотя бы одна ячейка - функция работать перестает. Почему я не понял.

@AlexM · 27.01.2017, 00:41

Я не понял. Моя формула работает как ваша при открытой книге?
Почему при закрытой книге глючит еще буду разбираться.

@AlexM · 27.01.2017, 01:34

А как такой вариант? С формулой

Code
1
=ВПР(B5;'[исходник_new1.xls]Logging Sheet'!B$6:J$9999;9;)

Для такого решения пришлось доработать таблицу исходника.
В каждой ячейке столбца J есть значение, не смотря на то, что ячейки объединены.
В столбце N показал что это действительно так.
Секрет в способе объединения по шаблону.
Как такое объединение делается.
Для примера шаблон уже есть в столбце J, это диапазон J6:J18
Объединим по этому шаблону диапазон L6:L18
Для этого копируем J6:J18, после выделяем L6:L18, ПКМ, специальная вставка, форматы, Ок

@Proidoha · 27.01.2017, 09:59 **[ТС]**

Сообщение от AlexM

Я не понял. Моя формула работает как ваша при открытой книге?

Да, конечно работает. Я, кстати, не понимаю как. Мы же вектор просмотра указываем сверху вниз, он должен первое сверху текстовое значение подставлять, я не прав?

Сообщение от AlexM

Для такого решения пришлось доработать таблицу исходника.
В каждой ячейке столбца J есть значение, не смотря на то, что ячейки объединены.

Исходных таблиц пока что 400 штук, за пару лет их будет несколько тысяч, вносить в них правки - это проигрышный вариант. Проще тогда просто открывать.
С хитрым (макросным) объединением давно знаком.

@AlexM · 27.01.2017, 10:16

Сообщение от Proidoha

Я, кстати, не понимаю как

Из описания функции Важно. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. В нашем случае значения не по порядку и неверный результат и есть последнее значение.
Если вдруг значения в векторе расположены по возрастанию, то при поиске "яяя", что для текста очень большое значение, почти как для чисел 9^9. В этом случае "работает" другой фрагмент описания функции Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение. Т.е. это опять последнее значение.
Мой коллега по другому форуму Николай (Nic70y) вместо "яяя" использует символ "我" Запись формулы сокращается на два символа.
PS. А с ВПР() красиво получилось и очень компактно.

@Proidoha · 27.01.2017, 10:27 **[ТС]**

А. все, я понял. Вы все-таки изменили исходник, чтобы оно заработало. Так нельзя, к сожалению.

Если взять неизмененный исходник - формула работает неправильно.

@AlexM · 27.01.2017, 10:42

А как же описание "21,0-25,2 Аргиллизиты серые с тонкой вкрапленностью пирита до 1%" находится не против своих значений. Это же ошибка.
Я всего лишь переместил описания на сроку начала диапазона. И сделал объединение ячеек для красоты (не обязательно) (файл исходник_new)
Весь мир стремится к стандартизации всего, так проще понимать и работать людям.
И вам надо выработать стандарт заполнения исходников, для упрощения их обработки.

@Proidoha · 27.01.2017, 11:08 **[ТС]**

Добавлено через 10 минут

Сообщение от AlexM

Я всего лишь переместил описания на сроку начала диапазона. И сделал объединение ячеек для красоты (не обязательно) (файл исходник_new)
Весь мир стремится к стандартизации всего, так проще понимать и работать людям.
И вам надо выработать стандарт заполнения исходников, для упрощения их обработки.

Над исходными файлами я не властен. Не я их составляю. Диапазон для описания находится в столбце I. Задача как раз из такого кривого файла сделать нормальный. Т.е. совместить столбцы B и C со столбцами I и J.

Новые блоги и статьи Все статьи Все блоги /
Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .	Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и напряжениями. Надо найти токи в ветвях. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и решает её. Последовательность действий:. . .
Новый CodeBlocs. Версия 25.03 palva 04.01.2026 Оказывается, недавно вышла новая версия CodeBlocks за номером 25. 03. Когда-то давно я возился с только что вышедшей тогда версией 20. 03. С тех пор я давно снёс всё с компьютера и забыл. Теперь. . .	Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .

@Proidoha 646 / 474 / 39 Регистрация: 04.11.2013 Сообщений: 1,951

	Не работает функция ДВССЫЛ при закрытом файле, на который ссылаюсь 26.01.2017, 01:55. Показов 25214. Ответов 24 Метки excel, адрес ячейки, двссыл (Все метки) Доброго времени! Полная формула (чисто для ознакомления, в ней сам черт ногу сломит, разбираться не обязательно, проблемный кусок обозначил ниже с уже частично выполненными вычислениями): Кликните здесь для просмотра всего текста =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))<>"";ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6))); ПРОСМОТР(2;1/('F:\[книга.xls]Logging Sheet'!$AK$12:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))<>"");'F:\[книга.xls]Logging Sheet'!$AK$12:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('F:\[книга.xls]Logging Sheet'!$M6;'F:\[книга.xls]Logging Sheet'!$AI$6:ДВССЫЛ("'F:\[книга.xls]Logging Sheet'!"&АДРЕС(СУММПРОИЗВ(('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100=МАКС('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100))СТРОКА('F:\[книга.xls]Logging Sheet'!$AI$6:$AI$100));СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AI$6)));0) + 5;СТОЛБЕЦ('F:\[книга.xls]Logging Sheet'!$AK$6)))) );ЕСЛИ('F:\[книга.xls]Logging Sheet'!$M6<>"";K6;"")) Формула работает, если открыт и файл с формулой, и файл F:\книга.xls. Если файл F:\книга.xls закрыт, то функция ДВССЫЛ возвращает ошибку #Ссылка. Что можно вместо нее использовать, или как побороть эту беду? Как работаю с ДВССЫЛ (на каком этапе возвращает #Ссылка, последние 3 вычисления программы. проверял пошагово): ДВССЫЛ('F:\[книга.xls]Logging Sheet'!&АДРЕС(15;35)) т.е. склеиваю ссылку из адреса листа в книге и адреса ячейки.Следующим шагом программа получает абсолютно правильную строку: ДВССЫЛ('F:\[книга.xls]Logging Sheet'!&$AI$15) Следующий шаг тоже без ошибок: ДВССЫЛ('F:\[книга.xls]Logging Sheet'!$AI$15) - и вот после этого оно мне вместо ДВССЫЛ ставит #Ссылка. Как мне "склеить" ссылку на ячейку в файле? Все предыдущие вычисления (которые ведутся по данным из этого же файла) работают, не работает только эта функция и ТОЛЬКО если файл F:\книга.xls закрыт. 0

@Proidoha 646 / 474 / 39 Регистрация: 04.11.2013 Сообщений: 1,951
	26.01.2017, 15:17 [ТС]
	Относительные ссылки просто на полученное число менять не очень хочу, чтобы впоследствии просто номер столбца автозаменой подменить можно было. Кликните здесь для просмотра всего текста =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))<>"";ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6))); ПРОСМОТР(2;1/('[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))<>"");'[исходник.xls]Logging Sheet'!$J$12:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПОИСКПОЗ('[исходник.xls]Logging Sheet'!$C6;'[исходник.xls]Logging Sheet'!$I$6:ДВССЫЛ("'D:\Excel\[исходник.xls]Logging Sheet'!"&АДРЕС(ПРОСМОТР(9^9;'[исходник.xls]Logging Sheet'!$I:$I;СТРОКА($I:$I));СТОЛБЕЦ($I$6 )));0) + 5;СТОЛБЕЦ($J$6)))) );ЕСЛИ('[исходник.xls]Logging Sheet'!$C6<>""6;"")) Не по теме: П.С. формула уже на 2 строки короче исходной, но сделать без ДВССЫЛ у меня пока не получается. 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	26.01.2017, 15:36
	Файл с формулой приложите. 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	26.01.2017, 16:51
	К сожалению у меня формулы не показываются, видно несовместимость 2003 и 2007. Редко, но так бывает Словами расскажите, какое значение должна вернуть ваша формула в D5, D6 и D11? 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	27.01.2017, 00:41
	Я не понял. Моя формула работает как ваша при открытой книге? Почему при закрытой книге глючит еще буду разбираться. 0

@Proidoha 646 / 474 / 39 Регистрация: 04.11.2013 Сообщений: 1,951
	27.01.2017, 10:27 [ТС]
	А. все, я понял. Вы все-таки изменили исходник, чтобы оно заработало. Так нельзя, к сожалению. Если взять неизмененный исходник - формула работает неправильно. 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	27.01.2017, 10:42
	А как же описание "21,0-25,2 Аргиллизиты серые с тонкой вкрапленностью пирита до 1%" находится не против своих значений. Это же ошибка. Я всего лишь переместил описания на сроку начала диапазона. И сделал объединение ячеек для красоты (не обязательно) (файл исходник_new) Весь мир стремится к стандартизации всего, так проще понимать и работать людям. И вам надо выработать стандарт заполнения исходников, для упрощения их обработки. 0