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

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

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

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

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

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

=ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'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 минут
Что разобрать смог. В вашей формуле фрагмент (убрал ссылку на другую книгу)

Код

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

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

Код

='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 с числами можно найти так

Код

=ПРОСМОТР(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

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

Код

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

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

Код

СТРОКА($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

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

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

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

Код

=ПРОСМОТР("яяя";'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

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

Код

=ВПР(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.

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

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
		1
	Не работает функция ДВССЫЛ при закрытом файле, на который ссылаюсь 26.01.2017, 01:55. Показов 21291. Ответов 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

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	26.01.2017, 10:15	2
	Функции для закрытых книг Обычные: ВПР, ИНДЕКС, ПРОСМОТР, ПОИСКПОЗ, СУММПРОИЗВ Массивные: СУММ, СЧЕТ Там где в аргументе массив или таблица. А так же обычные ссылки типа: =[файл]лист!A1 PS. без файла примера формулу понять не получается. Добавлено через 36 минут Что разобрать смог. В вашей формуле фрагмент (убрал ссылку на другую книгу) Код 'Logging Sheet'!$AI$6:ДВССЫЛ("Лист3!"&АДРЕС(СУММПРОИЗВ(('Logging Sheet'!$AI$6:$AI$100=МАКС('Logging Sheet'!$AI$6:$AI$100))СТРОКА($AI$6:$AI$100));СТОЛБЕЦ($AI$6))) можно заменить на Код ='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. 1

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	26.01.2017, 11:40 [ТС]	3
	Сообщение от AlexM т.е. убрана функция ДВССЫЛ() и АДРЕС(), которые не работают с закрытыми книгами. Функции АДРЕС и МАКС работают. Вообще все функции (из используемых) работают, кроме ДВССЫЛ. Спасибо, сейчас разберусь с предложенной формулой, если не подойдет - вставлю упрощенный пример. МАКС использую для определения конца диапазона. Формула универсальная для 400 файлов, одинаковых по структуре, но с разным количеством строк. 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	26.01.2017, 11:57	4
	номер последней строки в столбце AI с числами можно найти так Код =ПРОСМОТР(9^9;AI:AI;СТРОКА(AI:AI)) 1

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	26.01.2017, 12:36 [ТС]	5
	Сообщение от 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;"")) 0

@AlexM 2904 / 1718 / 705 Регистрация: 04.09.2015 Сообщений: 3,445
	26.01.2017, 14:24	6
	Избавьтесь и от этого Код СТРОКА('[исходник.xls]Logging Sheet'!$I:$I) СТОЛБЕЦ('[исходник.xls]Logging Sheet'!$J$6) достаточно писать так Код СТРОКА($I:$I) СТОЛБЕЦ($J$6) В первом случае это массив чисел, а во втором просто число и к книгам и листам привязывать необязательно. Даже вместо СТОЛБЕЦ($J$6) просто 10. Формула получится короче и понятнее. И таких замен можно сделать несколько. 1

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	26.01.2017, 15:17 [ТС]	7
	Относительные ссылки просто на полученное число менять не очень хочу, чтобы впоследствии просто номер столбца автозаменой подменить можно было. Кликните здесь для просмотра всего текста =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'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	8
	Файл с формулой приложите. 0

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

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	27.01.2017, 00:24 [ТС]	13
	Сообщение от AlexM Посмотрите вариант решения, но все равно есть проблема. ППЦ, а я там навертел..... Я проверил, что именно в Вашем варианте не работает. Код =ПРОСМОТР("яяя";'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$6:'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$8) Эта функция работает, если начальная и конечная ячейки массива либо одинаковые, либо соседние. Если между ними еще хотя бы одна ячейка - функция работать перестает. Почему я не понял. 0

	27.01.2017, 11:08

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

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	27.01.2017, 09:59 [ТС]	16
	Сообщение от AlexM Я не понял. Моя формула работает как ваша при открытой книге? Да, конечно работает. Я, кстати, не понимаю как. Мы же вектор просмотра указываем сверху вниз, он должен первое сверху текстовое значение подставлять, я не прав? Сообщение от AlexM Для такого решения пришлось доработать таблицу исходника. В каждой ячейке столбца J есть значение, не смотря на то, что ячейки объединены. Исходных таблиц пока что 400 штук, за пару лет их будет несколько тысяч, вносить в них правки - это проигрышный вариант. Проще тогда просто открывать. С хитрым (макросным) объединением давно знаком. 0

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

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

@Proidoha 646 / 474 / 38 Регистрация: 04.11.2013 Сообщений: 1,949
	27.01.2017, 11:08 [ТС]	20
	Добавлено через 10 минут Сообщение от AlexM Я всего лишь переместил описания на сроку начала диапазона. И сделал объединение ячеек для красоты (не обязательно) (файл исходник_new) Весь мир стремится к стандартизации всего, так проще понимать и работать людям. И вам надо выработать стандарт заполнения исходников, для упрощения их обработки. Над исходными файлами я не властен. Не я их составляю. Диапазон для описания находится в столбце I. Задача как раз из такого кривого файла сделать нормальный. Т.е. совместить столбцы B и C со столбцами I и J. 0