Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формулы работающие с закрытыми файлами, Где узнать какие формулы работают с закрытыми файлами
 
#1
09.07.2013 15:22:38
Добрый день собственно вопрос в описании ...темы... может кто помнит на память ?
 
 
 
#2
09.07.2013 15:37:43
и Вам добрый!ИДЕКС и ПОИСКПОЗ точно работают.про ВПР точно сказать не могу.надо попробовать.но у меня на работе ВПР на другие книги не ссылаются.про другие ничего сказать не могу
Вполне такой нормальный кинжальчик. Процентов на 100
 
 
 
#3
09.07.2013 15:52:17
Миш, нет такого списка
Надо методом тыка :)
К ИДЕКС, ПОИСКПОЗ и ВПР добавлю СУММПРОИЗВ и может ещё чего
 
 
 
#4
09.07.2013 16:15:01
Я рад тебя видеть сереж ..меня интересует СУММ, СУММЕСЛИ ...и простая ссылка на ячейку
 
 
 
#5
09.07.2013 16:31:14
Я тебя тоже
Немного обидно что ты моё имя с маленькой буквы пишешь, ну да ладно

Простая ссылка на ячейку - работает, а остальное проверь сам
 
 
 
#6
09.07.2013 16:32:46
СУММЕСЛИ не работает точно, но заменяющая её СУММПРОИЗВ - работает (правда, при открытом файле получаем потерю в скорости пересчета)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
09.07.2013 18:16:49
Цитата
Микки пишет: Где узнать какие формулы работают с закрытыми файлами
 :?:  Отрубили поиск? Удалили F1?
Из недавнего - http://www.planetaexcel.ru/forum/?PAGE_NAME=message&FID=1&TID=49503 ...  ;)
Изменено: Z - 09.07.2013 18:17:55
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#8
09.07.2013 18:20:15
UDF работают :)
 
 
 
#9
09.07.2013 20:19:36
Цитата
Hugo пишет:
UDF работают
Игорь, работают ограниченно, видят не более 65536 строк
 
 
 
#10
09.07.2013 20:40:33
Как узнать какие функции работают с закрытыми книгами? Следующее довольно для многих функций справедливо:
Как правило функция, которая может работать с массивами(именно с массивами, а не диапазонами) работает и с закрытыми книгами. Если же для аргумента функции жестко указано диапазон ячеек(как в СУММЕСЛИ, СЧЁТЕСЛИ и т.п.), то она точно не сможет работать с закрытой книгой.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#11
09.07.2013 21:02:30
Владимир, только что проверил - с трудом и вылетами (при правке формулы), но отработало и на 75907 строк:
Код
=VLOOKUPCOUPLE('C:\Documents and Settings\Игорь\Мои документы\[testbig.xlsx]Лист1'!$A$1:$B$75907;1;A1;2;", ";0)

Эксперимент был чистым - ввёл формулу при открытом файле, всё сохранил, всё закрыл, открыл источник, изменил значение, сохранил, закрыл всё, открыл снова файл с UDF - получил новые данные.
 
 
 
#12
10.07.2013 09:43:51
Цитата
Serge 007 пишет:
Я тебя тоже
Немного обидно что ты моё имя с маленькой буквы пишешь, ну да ладно

Простая ссылка на ячейку - работает, а остальное проверь сам
Извини Сережа торопился....так обрадовался что ты на планете. Всем спасибо за помощь и участие.
 
 
 
#13
10.07.2013 09:46:08
Дима а твоя УДФ будет работать с закрытыми книгами ?
' DateTime  : 11.01.2010 14:02
' Author    : The_Prist
' Purpose   : http://www.planetaexcel.ru/forum.php?thread_id=12600
'             Автор: PIKA4Y
'             Функция суммирует значения указанного диапазона
'             со всех листов книги в указанном диапзаоне по указанному критерию.
'             Аналог стандартной СУММЕСЛИ, только со всех листов
'---------------------------------------------------------------------------------------
Option Explicit

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range)
   Dim wsSh As Worksheet, sRange As String, sSumRange As String
   sRange = Right(rRange.Address, Len(rRange.Address) - InStr(rRange.Address, "!"))
   sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) - InStr(rSumRange.Address, "!"))
   For Each wsSh In Sheets
       If wsSh.Name <> Application.Caller.Parent.Name Then
       All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange))
       End If
   Next wsSh
End Function
 
 
 
#14
10.07.2013 10:23:42
Цитата
Hugo пишет: Владимир, только что проверил ...
Игорь, я был не прав  :)  В UDF нельзя передавать ссылку на весь столбец, а ограничение в 65536 строк действуют, если в UDF используются функции WorksheetFunction типа WorksheetFunction.Max(Ref).

Михаил Юрьевич, функция All_Sum работает только с открытой книгой, в которой она записана
Изменено: ZVI - 10.07.2013 10:27:10
 
 
 
#15
10.07.2013 16:22:53
Цитата
Микки пишет:
Дима а твоя УДФ будет работать с закрытыми книгами ?
Там же используется стандартная СУММЕСЛИ - значит не будет.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#16
02.03.2018 14:11:26
Цитата
Serge 007 написал:
Миш, нет такого спискаНадо методом тыка К ИДЕКС, ПОИСКПОЗ и ВПР добавлю СУММПРОИЗВ и может ещё чего
ВПР не работает, а комбинация ИНДЕКС+ПОИСКПОЗ -ДА!!!
 
 
 
#17
02.03.2018 15:23:20
Цитата
kan6346 написал:
ВПР не работает,
Чем докажете? ВПР тоже работает с закрытыми книгами. И всегда работала.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#18
02.03.2018 16:58:06
Цитата
Дмитрий Щербаков написал:
Чем докажете? ВПР тоже работает с закрытыми книгами. И всегда работала.


Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
=ЕСЛИОШИБКА(ИНДЕКС('Проданные комплектующие.xlsx'!Таблица11[перечень];ПОИСКПОЗ([@[№ и дата стало]];'Проданные комплектующие.xlsx'!Таблица11[№ дата];0));"не найден") - работает при закрытой книге

=ЕСЛИОШИБКА(ВПР([@[№ и дата стало]];'Проданные комплектующие.xlsx'!Таблица11[#Все];4;ЛОЖЬ);"не найден") - работает ТОЛЬКО с открытой книгой

и жаль потерянных 3 недели :))))
 
 
 
#19
02.03.2018 17:15:44
Потому что вы ссылаетесь не на диапазоны, а на именованные таблицы. Нельзя ссылаться на смарт-таблицы в закрытых книгах вообще никакими формулами.
Вот горшок пустой, он предмет простой...
 
 
 
#20
02.03.2018 17:22:46
Цитата
Цитата
PooHkrd написал:
Потому что вы ссылаетесь не на диапазоны, а на именованные таблицы. Нельзя ссылаться на смарт-таблицы в закрытых книгах вообще никакими формулами.
Просьба отписать "правильную" по Вашему мнению,формулу с ВПР. я проверю и честно отпишусь :)
 
 
 
#21
02.03.2018 17:25:57
Цитата
kan6346 написал: Просьба отписать "правильную" по Вашему мнению,формулу с ВПР. я проверю...
OFF Да, блин - заявочка... А своя рука не поднимается на подвиг?!. ;)
ps Правила - пп 2.2. 2.3.
Изменено: Z - 02.03.2018 17:27:24
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#22
02.03.2018 17:32:26
Цитата
Z написал:
OFF Да, блин - заявочка... А своя рука не поднимается на подвиг?!. ps Правила - пп 2.2. 2.3.
своя рука уже устала за 3 недели :)))))  
 
 
 
#23
02.03.2018 17:37:19
"Рука бойца колоть устала!"
примерно так:
Код
=ЕСЛИОШИБКА(ВПР(A:A;[Проданные комплектующие.xlsx]Лист1!$A:$D;4;ЛОЖЬ);"не найден")

Проверяйте.
Вы уж не обессудьте, но какой файл-пример - такая и формула  8)
Вот еще вариант:
Код
=ВПР(A:A;'C:\1\[Книга2.xlsx]Лист1'!$A:$B;2;0)

У меня шикарно работает!
Изменено: PooHkrd - 02.03.2018 17:44:48
Вот горшок пустой, он предмет простой...
 
 
 
#24
20.01.2022 14:32:34
Здравствуйте
Подниму данную тему и вопрос вновь: Задача доставать данные из параллельной закрытой книги. Название книги не задано заранее, вычисляется по названию текущей (года-1): (2022-1=2021)

Пробовал индекс
=ИНДЕКС("'"&ПСТР(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename"))-10)&ПСТР(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))+1;НАЙТИ("]";ЯЧЕЙКА("filename"))-НАЙТИ("[";ЯЧЕЙКА("filename"))-6)-1&".xlsx]01'!A:A";5)

И ВПР
=ВПР(5;СЦЕПИТЬ("'";ПСТР(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename"))-10);ПСТР(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))+1;НАЙТИ("]";ЯЧЕЙКА("filename"))-НАЙТИ("[";ЯЧЕЙКА("filename"))-6)-1;".xlsx]01'!A:A");1)

В обоих случаях безуспешно, выдает ССЫЛКА.
Прилагаю файлы примеров, столбце B видно, что путь к соседней книге вычисляется совершенно верно.
Так почему формула не работает в связке?
Пожалуйста укажите на ошибку.
Заранее спасибо!
 
 
 
#25
20.01.2022 15:19:13
Цитата
Сергей Саныч написал:
Так почему формула не работает в связке?
потому что путь к книге текстом и ссылка на диапазон - разные вещи.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#26
20.01.2022 17:26:25
Цитата
написал:
потому что путь к книге текстом и ссылка на диапазон - разные вещи.
То есть это не реализуемо?
 
 
 
#27
20.01.2022 17:30:58
Стандартными функциями - нет. Только через VBA. Вот, для общего образования: Как получить данные из закрытой книги?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#28
20.01.2022 20:12:38
Преобразовал текст в ссылку:

=ИНДЕКС(ДВССЫЛ("'"&ПСТР(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename"))-10)&ПСТР(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))+1;НАЙТИ("]";ЯЧЕЙКА("filename"))-НАЙТИ("[";ЯЧЕЙКА("filename"))-6)-1&".xlsx]01'!A:A");1)

=ВПР(2;ДВССЫЛ(СЦЕПИТЬ("'";ПСТР(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename"))-10);ПСТР(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))+1;НАЙТИ("]";ЯЧЕЙКА("filename"))-НАЙТИ("[";ЯЧЕЙКА("filename"))-6)-1;".xlsx]01'!A:A"));1)

Работает только при открытой книге, почему?
 
 
 
#29
20.01.2022 20:58:08
Потому что ДВССЫЛ работает только с открытыми книгами.
Цитата
Дмитрий(The_Prist) Щербаков написал: Стандартными функциями - нет. Только через VBA.
 
 
 
#30
21.01.2022 18:34:23
Не вникал в ваш вариант.

У меня была необходимость сделать так, чтобы когда в ячейку написать:
2021 --> тянулись данные из закрытого файла D:\Отчёты\2021.xlsm
2020 --> тянулись данные из закрытого файла D:\Отчёты\2020.xlsm
2019 --> тянулись данные из закрытого файла D:\Отчёты\2019.xlsm

Я решил это такой формулой:
=ВЫБОР(2022-D1;ВПР(C3;'D:\Отчёты\[2021.xlsm]Лист1'!K1:N400;2;0);ВПР(C3;'D:\Отчёты\[2020.xlsm]Лист1'!K1:N400;2;0);ВПР(C3;'D:\Отчёты\[2019.xlsm]Лист1'!K1:N400;2;0);ВПР(C3;'D:\Отчёты\[2018.xlsm]Лист1'!K1:N400;2;0))

она же, для наглядности:
=ВЫБОР(2022-D1;
ВПР(C3;'D:\Отчёты\[2021.xlsm]Лист1'!K1:N400;2;0);
ВПР(C3;'D:\Отчёты\[2020.xlsm]Лист1'!K1:N400;2;0);
ВПР(C3;'D:\Отчёты\[2019.xlsm]Лист1'!K1:N400;2;0);
ВПР(C3;'D:\Отчёты\[2018.xlsm]Лист1'!K1:N400;2;0))

То есть в ячейку D1 я вводил год, и данные тянулись из закрытого файла этого года, менял значение ячейки D1 и данные тянулись из другого закрытого файла.
Изменено: Бахтиёр - 21.01.2022 18:38:53
 
 
 
Страницы: 1
Читают тему
Наверх