646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
1 | |
Не работает функция ДВССЫЛ при закрытом файле, на который ссылаюсь26.01.2017, 01:55. Показов 21369. Ответов 24
Доброго времени!
Полная формула (чисто для ознакомления, в ней сам черт ногу сломит, разбираться не обязательно, проблемный кусок обозначил ниже с уже частично выполненными вычислениями): Кликните здесь для просмотра всего текста
=ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'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
|
26.01.2017, 01:55 | |
Ответы с готовыми решениями:
24
ЦПУ майнер, работает при закрытом диспетчере задач Почему таймер в программе работает медленнее при закрытом eclipse Поиск минимального элемента в массиве, который не меньше N, функция не работает при некоторых значениях Удаление строки в закрытом файле. |
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
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
26.01.2017, 11:40 [ТС] | 3 |
Функции АДРЕС и МАКС работают. Вообще все функции (из используемых) работают, кроме ДВССЫЛ. Спасибо, сейчас разберусь с предложенной формулой, если не подойдет - вставлю упрощенный пример.
МАКС использую для определения конца диапазона. Формула универсальная для 400 файлов, одинаковых по структуре, но с разным количеством строк.
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
26.01.2017, 11:57 | 4 |
номер последней строки в столбце AI с числами можно найти так
Код
=ПРОСМОТР(9^9;AI:AI;СТРОКА(AI:AI))
1
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
26.01.2017, 12:36 [ТС] | 5 |
Спасибо, от громоздкой конструкции с СУМОПРОИЗВ и МАКС избавился.
Я могу после каждого "улучшения" кода заново его сюда вставлять, если требуется. Если так будет проще. Код для ячейки 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
|
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
|
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
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
26.01.2017, 15:36 | 8 |
Файл с формулой приложите.
0
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
26.01.2017, 15:50 [ТС] | 9 |
Блин, вроде прикладывал раньше. Извиняюсь.
По умолчанию работает в папке d:\Excel
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
26.01.2017, 16:51 | 10 |
К сожалению у меня формулы не показываются, видно несовместимость 2003 и 2007. Редко, но так бывает
Словами расскажите, какое значение должна вернуть ваша формула в D5, D6 и D11?
0
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
26.01.2017, 18:15 [ТС] | 11 |
Ага, больше 7 уровней вложенности в формулах 2003 офис не поддерживает.
Убрал одну проверку, теперь в интервал 8.0-21.0 нули, да и пофиг, это "ЕСЛИ" я сам потом вставлю. А формула получилась на 2/3 короче. И в 2003 офисе открывается без проблем.
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
26.01.2017, 20:46 | 12 |
Посмотрите вариант решения, но все равно есть проблема.
Все функции способны работать с закрытой книгой, но возвращается ошибка.
1
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
27.01.2017, 00:24 [ТС] | 13 |
ППЦ, а я там навертел.....
Я проверил, что именно в Вашем варианте не работает. Код
=ПРОСМОТР("яяя";'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$6:'F:\Excel\[исходник_new.xls]Logging Sheet'!$J$8)
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
27.01.2017, 00:41 | 14 |
Я не понял. Моя формула работает как ваша при открытой книге?
Почему при закрытой книге глючит еще буду разбираться.
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
27.01.2017, 01:34 | 15 |
А как такой вариант? С формулой
Код
=ВПР(B5;'[исходник_new1.xls]Logging Sheet'!B$6:J$9999;9;) В каждой ячейке столбца J есть значение, не смотря на то, что ячейки объединены. В столбце N показал что это действительно так. Секрет в способе объединения по шаблону. Как такое объединение делается. Для примера шаблон уже есть в столбце J, это диапазон J6:J18 Объединим по этому шаблону диапазон L6:L18 Для этого копируем J6:J18, после выделяем L6:L18, ПКМ, специальная вставка, форматы, Ок
0
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
27.01.2017, 09:59 [ТС] | 16 |
Да, конечно работает. Я, кстати, не понимаю как. Мы же вектор просмотра указываем сверху вниз, он должен первое сверху текстовое значение подставлять, я не прав?
Исходных таблиц пока что 400 штук, за пару лет их будет несколько тысяч, вносить в них правки - это проигрышный вариант. Проще тогда просто открывать. С хитрым (макросным) объединением давно знаком.
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
27.01.2017, 10:16 | 17 |
Из описания функции Важно. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. В нашем случае значения не по порядку и неверный результат и есть последнее значение.
Если вдруг значения в векторе расположены по возрастанию, то при поиске "яяя", что для текста очень большое значение, почти как для чисел 9^9. В этом случае "работает" другой фрагмент описания функции Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение. Т.е. это опять последнее значение. Мой коллега по другому форуму Николай (Nic70y) вместо "яяя" использует символ "我" Запись формулы сокращается на два символа. PS. А с ВПР() красиво получилось и очень компактно.
0
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
27.01.2017, 10:27 [ТС] | 18 |
А. все, я понял. Вы все-таки изменили исходник, чтобы оно заработало. Так нельзя, к сожалению.
Если взять неизмененный исходник - формула работает неправильно.
0
|
2904 / 1718 / 705
Регистрация: 04.09.2015
Сообщений: 3,445
|
|
27.01.2017, 10:42 | 19 |
А как же описание "21,0-25,2 Аргиллизиты серые с тонкой вкрапленностью пирита до 1%" находится не против своих значений. Это же ошибка.
Я всего лишь переместил описания на сроку начала диапазона. И сделал объединение ячеек для красоты (не обязательно) (файл исходник_new) Весь мир стремится к стандартизации всего, так проще понимать и работать людям. И вам надо выработать стандарт заполнения исходников, для упрощения их обработки.
0
|
646 / 474 / 38
Регистрация: 04.11.2013
Сообщений: 1,949
|
|
27.01.2017, 11:08 [ТС] | 20 |
Добавлено через 10 минут
Над исходными файлами я не властен. Не я их составляю. Диапазон для описания находится в столбце I. Задача как раз из такого кривого файла сделать нормальный. Т.е. совместить столбцы B и C со столбцами I и J.
0
|
27.01.2017, 11:08 | |
27.01.2017, 11:08 | |
Помогаю со студенческими работами здесь
20
Почему ДВССЫЛ на простой именованный диапазон работает, а на динамический именованный не работает? Макрос в файле ексель, который находится на сервере, у 2 пользователей работает по-разному Копирование листов в отдельную книгу при наличии =ДВССЫЛ в листе Обмен Ут-бп При Закрытом Периоде Коллизия при открытом и закрытом хешировании ЛДС мигает при закрытом симисторе Искать еще темы с ответами Или воспользуйтесь поиском по форуму: |