|
eugene.v Пользователь Сообщений: 224 |
Подскажите, может кто сталкивался, мистика какая-то. Из одного файла ссылаюсь на другой ссылкой в виде: «=’C:1[НП.xlsb]рнп’!B2». Ссылка растягивается вниз на 20 тыс. строк. Последовательно (в столбцах A, B, C, D, E, F, G) ссылаюсь на столбцы второго файла: B, D, E, J, K, L, M. Формат файлов — *.xlsb (*.xlsm и *.xlsx проблемы не решают). Проблема в том, что обновление связей занимает около минуты. Проблема решается (обновление связей занимает меньше секунды), если: Не встречалось ли вам такое, и как с этим бороться? Приведенные методы борьбы неприемлемы. |
|
eugene.v Пользователь Сообщений: 224 |
Неужели никто не сталкивался? |
|
@Nik Пользователь Сообщений: 138 |
Получите данные через SQL-запрос к книге Excel. |
|
> Из одного файла ссылаюсь на другой ссылкой в виде: «=’C:1[НП.xlsb]рнп’!B2». Ссылка растягивается вниз на 20 тыс. строк. Попробуйте в диапазон А2:А20000 ввести формулу массива «=’C:1[НП.xlsb]рнп’!B2:B20000». Возможно, при этом столбец будет считан за один раз, а не поячеечно. |
|
|
Serge  Пользователь Сообщений: 11308 |
Никакой мистики здесь нет. Как бороться? Тут надо понять, какие такие уникальные причины побудили идти вразрез с логикой и дублировать данные одного файла на другой. |
|
ZVI  Пользователь Сообщений: 4328 |
Файлы Excel 2007/2010 упакованы в ZIP-архив. |
|
@Nik Пользователь Сообщений: 138 |
ZVI, а почему тогда, как говорит автор, проблема решается, если: |
|
ZVI Пользователь Сообщений: 4328 |
{quote}{login=@Nik}{date=26.12.2010 02:11}{thema=}{post}ZVI, а почему тогда, как говорит автор, проблема решается, если: |
|
@Nik Пользователь Сообщений: 138 |
Да… Как всё сложно |
Нюансов — масса |
eugene.v Пользователь Сообщений: 224 |
Совет Казанского помог. Большое спасибо! По поводу количества строк у разных версий файлов и того, что файлы 2007/2010 являются архивами: если ссылка связывает одноименные столбцы — связи обновляются очень быстро. Если столбцы отстоят более чем на один от того, из которого идёт ссылка (конкретика в первом посте) — то возникают тормоза. Видимо происходит то, что предположил ZVI по поводу кэширования (кому интересно, сброшу оригинальные файлы — около 2 МБ), но тогда непонятно, почему этого не происходит, если файл сохранен в версии 2003… |
|
ZVI Пользователь Сообщений: 4328 |
На всякий случай добавлю еще и общие рекомендации по ускорению работы в Excel 2007/2010 с большими файлами: |
|
eugene.v Пользователь Сообщений: 224 |
{quote}{login=@Nik}{date=26.12.2010 01:27}{thema=}{post}Получите данные через SQL-запрос к книге Excel.{/post}{/quote} Подскажите, пожалуйста, в двух словах, как это осуществить? |
|
@Nik Пользователь Сообщений: 138 |
Данные/Импорт внешних данных/Создать запрос… |
|
Юрий М  Модератор Сообщений: 60570 Контакты см. в профиле |
Добавлю (если ZVI не против), что есть смысл дефрагментировать сам файл подкачки и разместить его ближе к началу диска. Это уменьшит время обращения к диску. С этой задачей отлично справляется программа Vopt. Vopt — это ОЧЕНЬ шустрый дефрагментатор. Графическое представление карты диска очень напоминает DOS-овский Defrag. Доступен в Сети. |
|
ZVI Пользователь Сообщений: 4328 |
{quote}{login=@Nik}{date=26.12.2010 02:42}{thema=}{post}Да… Как всё сложно P.S. Кстати, о глюках и не только: |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
|
|
Igor67  Пользователь Сообщений: 3726 |
И еще, из времени когда компьютеры были ооочень большими: |
|
{quote}{login=ZVI}{date=26.12.2010 02:51}{thema=}{post}…рекомендации по ускорению работы в Excel 2007/2010 с большими файлами: Особый размер делится на две части: Надо вручную приблизить Исходный размер к Максимальному размеру? |
|
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Нужно сделать их одинаковыми. |
|
БАХТР? Пользователь Сообщений: 462 |
#20 01.06.2012 08:42:06 спасибо |
- Remove From My Forums
-
Вопрос
-
Здравствуйте ! Возникла такая проблема с Excel 2007. Существует на компьютере (1) например файл ХХХ.xls файл, в нем есть ячейки которые ссылаются на ячейки из другого файла YYY.xls. Т.е. на компе (1) все нормально работает и отображается все данные. Если
файл ХХХ.xls открыть на другом компе (2), где нету файла YYY.xls (т.е. при открытии файла не обновлять связи, «Автоматическое обновление связей отключено» выскакивает окно) то в ячейках где есть ссылка на YYY.xls выскакивает вместо данных #ССЫЛКА!.
Как это исправить потому что в помощи по «Управление обновлением внешних ссылок (связей)» есть пункт:Если требуется работать с имеющимися данными, а не заменять их новыми данными
Нажмите кнопку Не обновлять
т.е. он должен был взять данные последние, а не пытаться прочитать ссылки
, как было в 2003 , когда он спрашивал обновить или не обновить и если щелкал не обновлять то показывал данные, а не ошибку, (проверят на этом же файле в 2003 все так и работает)
. И самое интересное что скорее всего в 2007 есть нормальный режим работы потому что если файл послать по почте и смотреть в outlook 2007 на просмотре, не открывая, то там он показывает нормально с данными а не ошибки
. Вопрос что делать ? ответы типа как «Переписать формулы» не предлагать , т.к таблицы большие и их много таких, у экономистов они ими обмениваются, уже ведут много лет для своих отчетов, накопительных, и это всплыло пока
только один раз, а потом будет лавинообразно нарастать
Ответы
-
Ваша проблема описана в KB925893 и связана вот с чем: Excel 2007 при открытии документов, созданных в предыдущих версиях, пересчитывает формулы полностью из-за несовпадения форматов. Если документ, на
который идет ссылка, недоступен, в ячейке отображается ошибка. Все обходные пути описаны в приведенной мной статье. Как дополнительный вариант, документы можно пересоздать в новой версии Office — но тогда возможны затруднения с Office 2003. Я бы посоветовал
менять не формулы, а место хранения документов — например, на общем сетевом ресурсе, тогда проблем со ссылками не будет.
Все вышесказанное является моим личным мнением, не имеющим отношения к корпорации Майкрософт.
Доклады на Techdays: http://www.techdays.ru/speaker/Vinokurov_YUrij.html-
Предложено в качестве ответа
17 мая 2010 г. 9:06
-
Помечено в качестве ответа
Vinokurov YuriyModerator
21 мая 2010 г. 6:24
-
Предложено в качестве ответа
В этой статье расскажу, как я решил проблему с Excel у себя на работе раз и навсегда. А теперь постоянно делюсь этим решением с коллегами, а они в свою очередь подтверждают эффективность данных действий. Изначально проблема заключалась в том, что сотрудники каждый день по несколько раз жаловались на то, что Excel зависает при работе: долго открывает файлы, сохраняет, долго пересчитывает формулы и обновляет ячейки, а также наглухо зависает при перемещении по ячейкам, даже при простом клике, окно белеет и вылезает надпись «Программа не отвечает». Зависания Excel от 1 минуты до 30 минут были частой проблемой и головной проблемой для сисадминов, когда я только перешёл на новую работу, поэтому сразу решил заняться этой проблемой. Делалось это для того, чтобы
- снять нагрузку с IT-отдела
- наконец-то чтобы пользователи остались довольными.
- повысить свою квалификацию в решении вопросов пользователей
- выслужиться перед работодателем, куда ж без этого 🙂
- написать данную статью и поделиться с вами, а благодаря вашим коментариям «Спасибо!» я получаю искреннее удовольствие.
Поэтому, если вам поможет моё решение, отпишитесь в комментариях. Итак, я приведу несколько советов в порядке важности, использую которые вы раз и навсегда решите проблема с зависаниями Excel. Если у вас тоже Excel тормозит и зависает при работе с таблицами, воспользуйтесь моими советами в этой статье
Причин на самом деле много, здесь я перечислю пункты по решению основных проблем по мере своей эффективности по убыванию. Бывают и очень узкие проблемы, но во-первых, эти случаи единичны, а во-вторых, они требуют тщательного изучения и исследования. Поэтому ловите 7 способов решения проблемы с Excel, если он тормозит и зависает.
1. Отключить аппаратное ускорение прорисовки (бесплатно)
Пожалуй, это один из самых простых и бесплатных способов ускорить работу Excel, но про который знает не так уж и много айтишников, не говоря уже про простых пользователей. Итак, если у вас Excel зависает и тормозит, сделайте так:
- Откройте меню Файл
- Выберете подпункт Параметры
- Дополнительно
- Внизу найдите раздел Отображение (в старых версиях Экран)
- Найдите пункт «Отключить аппаратное ускорение обработки изображения»
- Поставьте на нём галочку
- Перезапустите программу Excel и заодно окна Microsoft Office
- Готово!
После этого у вас Excel будет работать быстрее и меньше тормозить. Особенно это даёт результат на компьютерах и ноутбуках со встроенной видеокартой, потому что аппаратное ускорение на таких видеокартах более чем сомнительно. Особенно меньше будет подвисать в моменты, когда в большом файле пересчитываются формулы по каждому «чиху» (перемещение по ячейкам, попытки копировать, вызвать контекстное меню, вставить, редактировать) или перерисовываются ячейки при изменении размеров окна.
2. Перейти на SSD (надо купить SSD)
Это универсальный способ для того, чтобы заставить операционную систему и программы быстрее открываться, работать, меньше тормозить. Если до этого Excel и Word у вас открывались за 5-20 секунд, то после перехода на твердотельные накопители это будет происходить за 1-2 секунды. Также Windows будет грузиться не 2-10 минут, а 10-15 секунд. Согласитесь, это очень большой прирост производительности. Вкупе с предыдущим пунктом — это самые действенные способы. Все нижеследующие способы менее эффективные, но тоже позволяют избавиться от тормозов Excel.
Более подробно про SSD я написал в своей статье Срочно переходите на SSD. Плюсы и минусы твердотельных накопителей, сравнение с простыми жёсткими дисками
3. Почистить папку TEMP, Temporary (бесплатно)
Временные файлы — это на самом деле большая проблема на компьютерах, где Windows установлен довольно давно и ни разу эта папка не чистилась. Папка временных файлов у каждого пользователя своя, её легко открыть введя в адресной строке %TEMP% и нажав Enter. Если в этой папке свыше 500-1000 файлов и огромное количество папок с перешифрованными названиями, а также весь список файлов и папок грузится очень долго, значит этот пункт вам очень сильно поможет. Дело в том, что все программы и службы в своей работе используют папку с временными файлами как некоторый отстойник, куда можно ненадолго распаковать файлы, закинуть копии, автосохоранять различные ситуации, закачивать обновления, скрипты. Но не все эти файлы также благополучно удаляются, как создаются. Естественно с течением времени она так набухает, что все программы, использующие эту папку начинают подтормаживать. Excel не исключение, он пытается туда сохранять промежуточные версии документов, пока вы не нажмёте Сохранить, и прочие данные. Соответственно как долго у вас эта папка открывается, также долго Excel может пытаться открыть или восстановить некоторые файлы.
Для решения этой проблемы надо почистить папку со временными файлами. Сделать это можно как вручную, выделив и нажать Delete. Но надо быть аккуратным, не удалить файлы, которые сейчас используются. Но я использую программу Ccleaner. Есть и другие аналоги, однако Ccleaner меня более чем устраивает, главное — не обращать внимание на рекламу и предложения перейти на полную PRO-версию. Скачать последнюю версию можно здесь https://www.ccleaner.com/ru-ru/ccleaner/builds. Выбирайте сокращенную версию, если она там имеется. Одно замечание — можете убирать галочку с «Файлы Cookie» в каждом браузере при стандартной очистке, чтобы у вас не слетала авторизация и не приходилось заново заходить в личные кабинеты и почту. Далее жмёте «Анализ», затем «Очистка». Если у вас удалилось больше 2 Гб, значит у вас очень захламлённый компьютер, данный пункт вам подошёл. Удаляйте временные файлы раз в месяц, полгода. Каждый день и неделю не надо, это уже перебор.
4. Почистить папку %appdata%/Microsoft/Excel от временных файлов (бесплатно)
Кроме папки TEMP, Excel также сохраняет временные копии в %appdata% (которая roaming)/Microsoft/Excel. Также как и в предыдущем пункте, здесь может много файлов лишь с одним отличием — все они принадлежат Excel, ничего лишнего не удалите. Но если вдруг в будущем вам потребуется восстановить какой-то файл, то у вас это не получится. Поэтому опять-таки будьте аккуратны, удаляйте вручную, здесь Ccleaner не поможет.
5. Удалите лишние стили (форматирование), которые наслаиваются при копировании из разных файлов (долго, бесплатно)
Этот способ поможет, если у вас Excel зависает при работе именно с одной конкретной таблицей. Бывает такое, что файл могут редактировать множество пользователей и каждый из них добавляет в файл что-то своё, применяет свои стили. Или, например, когда вы в один файл копируете таблицы и их куски с кучи разных файлов, собираете всё в одном файлике и он начинает тормозить. Особенно это заметно, когда применяется условное форматирование (кто пользуется Excel, тот знает), чересстрочные выделения и прочие стили заголовков, строк и столбцов. Из-за этого «компота», как говорил Медведев, Excel и тормозит. Поэтому если вам все эти форматирования неважны, а важнгы именно данные, то надо из удалить. Выделить диапазон ячеек или весь лист и в меню выбрать «Очистить форматирование». Иногда это не срабатывает ,потому что остаются ссылки и зависимости, а также невидимые объекты. На моей памяти одна сотрудница запихивала в маленькие ячейки 1 на 1 см фотографии по 5Мбайт и искренне удивлялась, почему этот файл долго открывается, обрабатывается и сохраняется.
Если вы сделали всё из предыдущих пунктов и очистили форматирование, но ничего не помогло, то вам надо скопировать все данные таблицы в новый файл и работать далее с новым файлов.
6. Купите лицензию MS Office или последнюю версию офисного пакета (дорого, проблема может быть не в этом)
Вы не поверите, но бывает, что Excel зависает, если у вас пиратская версия, поэтому однозначно купите лицензию. Хоть Майкрософт Офис стоит дорого, но он стоит каждой копейки, ведь благодаря ему вы можете зарабатывать. А лицензионная версия часто работает без сбоев из-за ошибок активации, а также ошибок, связанных с патчами, кряками и активаторами. Просто купите, для дома версия стоит недорого.
И в том случае, если вы ещё пользуетесь версиями Microsoft Office 2007 или даже 2003, то пришла пора поставить новую версию. Некоторые функции в старых версиях отсутствует, также потихоньку сокращается совместимость файлов. Так, например, файлы, запароленные в свежей версии Excel, допустим 2016 или 2019, могут просто не открыться в версии 2007. Я уж молчу, что в 2003 изначально не было поддержки файлов xlsx и docx, над оставить патч, а в 2007 нет поддержки файлов формата odf и ods, также нет экспорта в PDF напрямую, возможности работы с pdf и многих других функций.
7. Отключите лишние надстройки
Чуть не забыл про этот пункт. Так как Excel очень мощная программа, то другие программы интегрируются в неё с помощью разных надстроек. Есть надстройки OneDrive, КонсультантПлюс, Foxit, антивирусов, конвертеров файлов и прочие. Пользуетесь вы ими редко, либо не пользуетесь совсем, а из-за них Excel зависает частенько. Поэтому лишние надстройки надо удалять. Делается это так:
- Файл
- Параметры
- Надстройки
- в пункте Активные надстройки приложений выбрать ненужные и отключить
- Перезапустить Excel.
В принципе всё. Пользуйтесь на здоровье, работайте с Excel без тормозов, лагов и зависаний, редактируйте таблицы и не испытывайте неудобств. Если статья помогла и оказалась полезна, буду благодарен за комментарии и замечания или дополнения!
Отблагдарить автора статьи также можно переводом, +100 вам в карму!
СУТЬ ПРОБЛЕМЫ:
Необходимо чтобы при открытии Excel-файла происходило обновление его данных из других (закрытых) файлов. Ссылки на книги с исходными данными прямые, т.е. данные просто копируются из источника в определенные ячейки файла с отчетом. При открытии файла с отчетом (файла-приемника) появляется сообщение «Не удается обновить одну или несколько связей в книге Excel» и предлагается «Продолжить» (без обновления) или «Изменить связи». При нажатии на кнопку «Изменить связи», появляется окно, где указаны существующие связи и в поле «состояние» для каждой связи стоит «ОК»! Как это понять? Это значит «Мол связь в норме, только обновиться почему-то не могу»?? 
Жму в этом окне кнопку «Обновить» и только после этого данные обновляются.
Почему Excel не может сам обновить данные при открытии файла?? 
Кто-нибудь знает как решить эту проблему?
(OS Windows Vista, Office 2007)
#1
28.04.2010, 14:13
Последнее редактирование: 28.04.2010, 14:16 от Василий Алибабаевич
А если отключить автообновление связей и запустить его вручную, то нет ошибки?
Надо в этом окне нашимать не кнопку обновить, а кнопку Состояние и посмотреть состояние связей.
Обновить, если не ошибаюсь, обновляет состояние только одной связи (выделенной).
Добрый день! Спасибо за быстый отклик.
Цитата: Василий Алибабаевич от 28.04.2010, 14:13
Надо в этом окне нашимать не кнопку обновить, а кнопку Состояние и посмотреть состояние связей.
Так состояние связей и так показывается сразу как «ОК» (см. предыдущее сообщение). Это то меня и удивляет — состояние связей ОК, а обновить данные Excel сам не может! И после нажатия на кнопку «Состояние» по-прежнему все остается «ОК».
Цитата: Василий Алибабаевич от 28.04.2010, 14:13
Обновить, если не ошибаюсь, обновляет состояние только одной связи (выделенной).
А если отключить автообновление связей и запустить его вручную, то нет ошибки?
Кнопка «Обновить» обновляет действительно только одну (выбранную) связь. Вот я и обновляю последовательно связи со всеми книгами нажатием этой кнопки. При этом загружаются необходимые данные из источников.
Если отключить автоматическое обновление, то после открытия окна «Изменение связей» в графе «Состояние» для всех связей пишется «неизвестно». Нажатие кнопки «Обновить» переводит состояние в «ОК» и данные загружаются. Сообщения об ошибке не возникает.
Проблема как раз в автоматическом обновлении! Во-первых, обновлять связи вручную просто замучаешься, а, во-вторых, файл-отчет предназначен для других пользователей (руководителей), которые просто не знают как в Excel что-то обновлять и объяснить им вряд-ли получится 
#3
29.04.2010, 17:50
Последнее редактирование: 29.04.2010, 17:57 от Василий Алибабаевич
Теперь и я задаю вопрос:
Отказывается работать макрос. Есть книга «книга1», со связями с двумя другими книгами.
Макрос отказывается работать. Все связи доступны, файлы открыты все.
Grauer
Повесьте на событие открытия книги макрос
Private Sub Workbook_Open()
ActiveWorkbook.UpdateLink (ActiveWorkbook.LinkSources(xlOLELinks))
End Sub
Василий Алибабаевич
Нужно добавить
xlOLELinks
Скажи мне, кудесник, любимец ба’гов…
Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995
Добрый день. Возникла проблема у пользователя:
Работает с файлом, в котором есть ссылки на другие книги Excel, по идее, эти другие книги Excel должны открываться при установки курсора на ячейку с данными и нажатии Ctrl+Х (русская).
Но этого не происходит. Я так понимаю, это дело в настройках, тк в данном файле у других пользователях данная фишка работает. Прошу помощи в решении данного вопроса. Пока удалось только обновить ссылки, как описано выше.
вид листа:см вложение
Возможно проблема в раскладке клавиатуры.
у этого пользователя стоит не стандартная раскладка как у остальных или вообще забыли переключить на «русский» 
Цитата: Grauer от 28.04.2010, 12:21
СУТЬ ПРОБЛЕМЫ:
Необходимо чтобы при открытии Excel-файла происходило обновление его данных из других (закрытых) файлов …Почему Excel не может сам обновить данные при открытии файла??
Кто-нибудь знает как решить эту проблему?
(OS Windows Vista, Office 2007)
Наткнулся на пост, у меня та же проблема. Так и не вижу здесь четкого ответа …
Думаю дело в скрытых (забытых) формулах.
Если знаете решение — поделитесь
Цитата: Василий Алибабаевич от 28.04.2010, 14:13
А если отключить автообновление связей и запустить его вручную, то нет ошибки?
Надо в этом окне нашимать не кнопку обновить, а кнопку Состояние и посмотреть состояние связей.
Обновить, если не ошибаюсь, обновляет состояние только одной связи (выделенной).
Читайте внимательней или сформулируйте правильно свою «проблему» и заведите новый топик
- Профессиональные приемы работы в Microsoft Excel
-
►
Обмен опытом -
►
Microsoft Excel -
►
Обновление связей с другими книгами Excel (прямые ссылки)