Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Очень медленно обновляются связи с другим файлом в формате Excel 2007/2010
 
#1
23.12.2010 15:38:00
Подскажите, может кто сталкивался, мистика какая-то.  
 
Из одного файла ссылаюсь на другой ссылкой в виде: "='C:\1\[НП.xlsb]рнп'!B2". Ссылка растягивается вниз на 20 тыс. строк. Последовательно (в столбцах A, B, C, D, E, F, G) ссылаюсь на столбцы второго файла: B, D, E, J, K, L, M. Формат файлов - *.xlsb (*.xlsm и *.xlsx проблемы не решают).
 
Проблема в том, что обновление связей занимает около минуты. Проблема решается (обновление связей занимает меньше секунды), если:  
1. Второй файл (на который ссылаюсь) имеет формат Excel 2003 (идиотизм, получается, что Excel 2010 работает со своим родным форматом хуже, чем со старым).  
2. Столбцы первого файла соответствуют столбцам ссылок (то есть если я из столбца "С" ссылаюсь на столбец "С" второго файла) или расстояние между нахождением столбца ссылки и ссылкой не превышает одного столбца (если я из столбца "А" ссылаюсь на столбец "К", то уже возникают тормоза с обновлением связей).  
 
Не встречалось ли вам такое, и как с этим бороться? Приведенные методы борьбы неприемлемы.
 
 
 
#2
24.12.2010 12:19:18
Неужели никто не сталкивался?
 
 
 
#3
26.12.2010 01:27:43
Получите данные через SQL-запрос к книге Excel.
 
 
 
#4
26.12.2010 01:42:28
> Из одного файла ссылаюсь на другой ссылкой в виде: "='C:\1\[НП.xlsb]рнп'!B2". Ссылка растягивается вниз на 20 тыс. строк.
 
Попробуйте в диапазон А2:А20000 ввести формулу массива "='C:\1\[НП.xlsb]рнп'!B2:B20000". Возможно, при этом столбец будет считан за один раз, а не поячеечно.
 
 
 
#5
26.12.2010 01:55:06
Никакой мистики здесь нет.  
Посчитайте сами.  
В Excel 2003 - 65536 строк.  
В Excel 2010 - 1 048 576 строк.  
Если Вы ссылаетесь на диапазон А1:К20000, то экс просматривает около 700 тысяч ячеек книги 2003 или около 11 с половиной миллионов ячеек в книге 2010.И это ещё без смещения по столбцам. А процессор Вашего компа один и тот же в обоих случаях. Так что никакого идиотизма ;-)  
Опять таки, если в файл, на который есть ссылки вносились изменения или он закрывался, то при обновлении экс проверяет ВЕСЬ файл источник на наличие изменений. Естественно "маленький" файл 2003 проверить быстрее, чем имеющий такое же кол-во информации 2010.  
 
Как бороться? Тут надо понять, какие такие уникальные причины побудили идти вразрез с логикой и дублировать данные одного файла на другой.  
Я вразумительных причин делать это не нахожу, поэтому такого вопроса передо мной никогда и не возникало.
 
 
 
#6
26.12.2010 02:05:51
Файлы Excel 2007/2010 упакованы в ZIP-архив.  
Ссылка на внешний файл в формуле ячейки – отдельный запрос.  
При каждом запросе этот архив распаковывается, на это требуется время.  
Прислушайтесь к совету Алексея (Казанского) – там один запрос
 
 
 
#7
26.12.2010 02:11:30
ZVI, а почему тогда, как говорит автор, проблема решается, если:  
......  
2. Столбцы первого файла соответствуют столбцам ссылок (то есть если я из столбца "С" ссылаюсь на столбец "С" второго файла) или расстояние между нахождением столбца ссылки и ссылкой не превышает одного столбца ??
 
 
 
#8
26.12.2010 02:31:41
{quote}{login=@Nik}{date=26.12.2010 02:11}{thema=}{post}ZVI, а почему тогда, как говорит автор, проблема решается, если:  
......  
2. Столбцы первого файла соответствуют столбцам ссылок (то есть если я из столбца "С" ссылаюсь на столбец "С" второго файла) или расстояние между нахождением столбца ссылки и ссылкой не превышает одного столбца ??{/post}{/quote}Несовпадение столцов вряд ли является причиной тормозов, но это нужно бы протестировать. Пока могу лишь предположить, что замедление может происходить из-за необходимости обращения к далеко отстоящим друг от друга участкам файла. Чтение файла обычно производится не побайтно (это очень долго), а кусками через некоторый буфер, запас кэшируется в памяти, за счет этого происходит ускорение считывания данных "вперед".  
Известно, что даже обращение к несмежным участкам памяти тормозит. Помнится, что Слэн это замедление отлавливал.
 
 
 
#9
26.12.2010 02:42:59
Да... Как всё сложно :-) Нюансов - масса :-) Я уже когда-то говорил, что работа со сложной задачей в экселе зачастую превращается в борьбу с глюками экселя :-)
 
 
 
#10
26.12.2010 02:48:31
Совет Казанского помог. Большое спасибо!  
 
По поводу количества строк у разных версий файлов и того, что файлы 2007/2010 являются архивами: если ссылка связывает одноименные столбцы - связи обновляются очень быстро. Если столбцы отстоят более чем на один от того, из которого идёт ссылка (конкретика в первом посте) - то возникают тормоза.  
 
Видимо происходит то, что предположил ZVI по поводу кэширования (кому интересно, сброшу оригинальные файлы - около 2 МБ), но тогда непонятно, почему этого не происходит, если файл сохранен в версии 2003...
 
 
 
#11
26.12.2010 02:51:51
На всякий случай добавлю еще и общие рекомендации по ускорению работы в Excel 2007/2010 с большими файлами:  
1. Использование формата двоичной книги (XLSB)  
2. Установка фиксированного размера файла подкачки операционной системы по правой кнопке на ярлыке "Мой компьютер" - Свойства - Дополнительно - Быстродействие - Параметры - Дополнительно - Виртуальная память - Изменить - Особый размер - установить побольше, но в разумных пределах (у меня - 4605 MB)- Ok (3 раза)
 
 
 
#12
26.12.2010 02:56:14
{quote}{login=@Nik}{date=26.12.2010 01:27}{thema=}{post}Получите данные через SQL-запрос к книге Excel.{/post}{/quote}  
 
Подскажите, пожалуйста, в двух словах, как это осуществить?
 
 
 
#13
26.12.2010 03:05:56
Данные/Импорт внешних данных/Создать запрос...  
База данных: Файлы Excel*  
Ну а там уже разберётесь... если чё - спрашивайте.Хотя в поиске по форуму найдёте много примеров.
 
 
 
#14
26.12.2010 03:09:08
Добавлю (если ZVI не против), что есть смысл дефрагментировать сам файл подкачки и разместить его ближе к началу диска. Это уменьшит время обращения к диску. С этой задачей отлично справляется программа Vopt. Vopt - это ОЧЕНЬ шустрый дефрагментатор. Графическое представление карты диска очень напоминает DOS-овский Defrag. Доступен в Сети.
 
 
 
#15
26.12.2010 03:24:51
{quote}{login=@Nik}{date=26.12.2010 02:42}{thema=}{post}Да... Как всё сложно :-) Нюансов - масса :-) Я уже когда-то говорил, что работа со сложной задачей в экселе зачастую превращается в борьбу с глюками экселя :-){/post}{/quote}Найти бы ещё оптимальное соотношение своих глюков и Excel-я :-)  
 
P.S. Кстати, о глюках и не только:  
- Компьютер выполняет те команды, которые  Вы ему даете, а не то, что Вы от него хотите  
- Компьютер не подчиняется законам физики. Только в нем глюки возникают из ничего, файлы исчезают в никуда, а объем измеряется в метрах и называется весом.  
- Компьютер создан для решения проблем, которых до его появления не существовало  
- Появились компьютеры и... дети стали зависать  
- Компьютер позволяет сделать из мухи слона легким движением мыши  
- Компьютер не работал. Раньше этим занимался целый отдел  
- "Компьютер никогда не заменит человека" © Людоед  
- Не надо очеловечивать компьютеры, они этого не любят  
- Самое главное при работе с компьютером - не давать ему понять что ты торопишься  
- Любая программа должна иметь ровно столько недоработок, чтобы она и сама неплохо работала, и позволяла неплохо зарабатывать на её улучшениях  
- Если программа дает неверный результат, то в логике её работы – ошибка. А если результат правильный, то логических ошибок, как минимум, две  
- На старости лет начал изучать английский. Очень напоминает VBA  
- Аквариум с плавающей запятой  
- Числа с булькающей и тонущей запятой  
- Настоящий программист никогда не ставит комментариев. То, что писалось с трудом, должно пониматься с трудом  
- Ошибки в программах - следствие ошибок в ДНК  
- Если бы программистам за их ошибки отрывали части тела, в конце концов им пришлось бы использовать голову  
- Надо ли материально поощрять специалиста, если ему и так дана возможность попрограммировать  
- Мои программы не зацикливаются... не зацикливаются...  
- Не пей из чайника - чайником станешь  
- Сообщение: "Вашу жену зовут Наташа, детей - Юля и Павлик. Вы всё ещё хотите выйти из Excel?!"  
- Пуск - Программы - Стандартные - Развлечения – Громкость  
- Приглючений им мало    
- Приложение выполнило невыполнимую операцию  
- Человеку свойственно ошибаться, но с помощью компьютера это ему удается намного лучше
 
 
 
#16
26.12.2010 03:29:17
:-) В этой связи вспомнилось ещё вот это:  
Если написанная программа сразу заработала правильно - смотри компилятор.
 
 
 
#17
26.12.2010 15:52:17
И еще, из времени когда компьютеры были ооочень большими:  
если программа транслятор не нашла ошибок в написанной Вами программе - обратитесь к администратору, он исправит ошибку в программе трансляторе:)
 
 
 
#18
01.06.2012 00:27:24
{quote}{login=ZVI}{date=26.12.2010 02:51}{thema=}{post}...рекомендации по ускорению работы в Excel 2007/2010 с большими файлами:  
...  
2. Установка фиксированного размера файла подкачки операционной системы по правой кнопке на ярлыке "Мой компьютер" - Свойства - Дополнительно - Быстродействие - Параметры - Дополнительно - Виртуальная память - Изменить - Особый размер - установить побольше, но в разумных пределах (у меня - 4605 MB)- Ok (3 раза){/post}{/quote}  
 
Особый размер делится на две части:  
Исходный размер  
Максимальный размер  
 
Надо вручную приблизить Исходный размер к Максимальному размеру?
 
 
 
#19
01.06.2012 00:49:24
Нужно сделать их одинаковыми.  
Ещё я читал такие рекомендации:    
1. Переместить файл подкачки в начало диска (будет быстрее доступ к нему).  
2. Дефрагментировать файл подкачки.
 
 
 
#20
01.06.2012 08:42:06
спасибо
 
 
 
Страницы: 1
Читают тему
Наверх