Как отключить ожидание действия в excel
Здравствуйте.
Подскажите, пож-та, как можно программно при помощи VBA, предотвратить спящий режим (отключение экрана, выход из системы) при отсутствии действий со стороны пользователя?
В Excel запускается написанный макрос, который обрабатывает данные больше часа. Если не трогать ПК, то через некоторое время (примерно 10-15 мин), гаснет монитор и затем происходит автоматический выход из учетной записи. При этих действиях работа программы прерывается.
Нужно внедрить в этот макрос код, который по счетчику (либо по другому действию к примеру сверка времени) будет производить некие действия не дающие компьютеру войти в режим отключения экрана (спящего режима, выхода из системы), т.е. будет препятствовать заложенному таймеру бездействия пользователя.
Нужно именно при помощи VBA. Код будет использоваться для описанной программы, чтобы она корректно завершила свои действия. И нужно именно на время работы программы.
Пользователь
Сообщений: 6031 Регистрация: 05.02.2014
25.10.2022 16:25:36
#If Win32 Then Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal x As Long, ByVal y As Long, ByVal cButton As Long, ByVal dwExtraInfo As Long) #Else Declare PtrSafe Function SetCursorPos Lib "User" (ByVal x As Integer, ByVal y As Integer) Declare PtrSafe Sub mouse_event Lib "User" (ByVal dwFlags As Integer, ByVal x As Integer, ByVal y As Integer, ByVal cButton As Integer, ByVal dwExtraInfo As Integer) #End If Sub myMoveCursor() ' Exit Sub Static dt As Date If dt < Now - TimeSerial(0, 5, 0) Then dt = Now Dim y As Integer Dim x As Integer On Error Resume Next y = ActiveWindow.ActivePane.PointsToScreenPixelsY(ActiveCell.Top + ActiveCell.Height * Rnd()) x = ActiveWindow.ActivePane.PointsToScreenPixelsX(ActiveCell.Left + ActiveCell.Width * Rnd()) On Error GoTo 0 If y >0 Then If x > 0 Then SetCursorPos x, y mouse_event &H2, 0&, 0&, 0&, 0& mouse_event &H4, 0&, 0&, 0&, 0& End If End If DoEvents End If End Sub
Изменено: МатросНаЗебре — 25.10.2022 16:26:10
Пользователь
Сообщений: 444 Регистрация: 15.06.2018
25.10.2022 16:29:36
МатросНаЗебре, я что-то подобное пробовал делать, но комп у меня блокировался все равно 🙂
Но Ваш вариант не пробовал, может и сработает
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
25.10.2022 16:52:26
Может это поможет
Изменено: New — 25.10.2022 17:13:53
Пользователь
Сообщений: 3007 Регистрация: 18.07.2016
25.10.2022 17:03:46
| Цитата |
|---|
| asesja написал: Нужно именно при помощи VBA. Код будет использоваться для описанной программы, чтобы она корректно завершила свои действия. |
Для этого нужен Ваш комп перед глазами, чтобы увидеть все, что может мешать. А так — см.#4. Если не поможет — подробности в студию. Т.к.
| Цитата |
|---|
| asesja написал: Если не трогать ПК, то через некоторое время (примерно 10-15 мин), гаснет монитор и затем происходит автоматический выход из учетной записи. При этих действиях работа программы прерывается |
Пользователь
Сообщений: 2111 Регистрация: 01.01.1970
25.10.2022 17:49:33
В бытность на удаленке пользовался следующим способом — включал небольшой видеоролик в стандартном плеере (должен быть включен повтор и режим Отображать проигрыватель поверх остальных окон) и сдвигал окно за край экрана. Применительно к данной теме — в конце кода VBA можно добавить закрытие окна плеера через winapi.
Пользователь
Сообщений: 396 Регистрация: 13.06.2022
25.10.2022 18:08:09
Для этого есть API функции PowerCreateRequest PowerSetRequest, но как их использовать не подскажу. Видел также совет — включить презенацию Power Point.
Изменено: testuser — 25.10.2022 18:12:13
Пользователь
Сообщений: 459 Регистрация: 17.08.2019
25.10.2022 18:16:31
Может так попробуйте:
Sub TurnOffSleep()' вызвать вначале Вашего макроса Dim WshShell As Object, strCommand As String strCommand = " & powercfg -X -monitor-timeout-ac 0 & powercfg -X -disk-timeout-ac 0 & powercfg -X -standby-timeout-ac 0 & powercfg -X -hibernate-timeout-ac 0" Set WshShell = CreateObject("WScript.Shell") WshShell.Run "cmd.exe /C powercfg -h off " & strCommand, 1, False End Sub Sub TurnOnSleep()'вызвать вконце Вашего макроса Dim WshShell As Object, strCommand As String strCommand = " & powercfg -X -monitor-timeout-ac 30 & powercfg -X -disk-timeout-ac 90 & powercfg -X -standby-timeout-ac 90 & powercfg -X -hibernate-timeout-ac 120" Set WshShell = CreateObject("WScript.Shell") WshShell.Run "cmd.exe /C powercfg -h on " & strCommand, 1, False End Sub
| Цитата |
|---|
| webley написал: закрытие окна плеера |
Sub TurnOffPlayer() Dim WshShell As Object Set WshShell = CreateObject("WScript.Shell") WshShell.Run "cmd.exe /C TASKKILL /IM vlc.exe", 1, False End Sub
У меня плеер vlc, найдете в диспетчере задач в процессах точное имя Вашего видео-плеера, если будете использовать метод от webley.
Изменено: DANIKOLA — 25.10.2022 18:34:34
Пользователь
Сообщений: 3637 Регистрация: 31.03.2014
25.10.2022 18:40:33
Пользователь
Сообщений: 506 Регистрация: 21.10.2019
25.10.2022 21:29:20
| Цитата |
|---|
| написал: В бытность на удаленке пользовался следующим способом — включал небольшой видеоролик в стандартном плеере (должен быть включен повтор и режим Отображать проигрыватель поверх остальных окон) и сдвигал окно за край экрана. Применительно к данной теме — в конце кода VBA можно добавить закрытие окна плеера через winapi. |
Я знаю как обмануть компьютер в этом плане. Но у меня программа, которую будут использовать другие пользователе. Мне им написать, включите плеер с видео или установите что-нибудь типа mouse jiggler (симулятор движения мышки)? Как то некрасиво получается.
Как описал, ищу код VBA, который смогу встроить в свой макрос, который должен работать больше часа и не давать ПК переходить в спящий режим.
Спасибо за предложенные варианты. По возможности опробую.
Пользователь
Сообщений: 506 Регистрация: 21.10.2019
25.10.2022 21:38:03
Предложенные коды ещё не опробовал. Но я так понимаю, что нужно реализовать при помощи VBA симулятор несущественного движения мышкой примерно раз в 5 минут.
Изменено: asesja — 25.10.2022 21:41:01
Пользователь
Сообщений: 4825 Регистрация: 06.01.2013
25.10.2022 21:53:24
asesja, это то, что вам дал МатросНаЗебре в сообщении #2
Пользователь
Сообщений: 506 Регистрация: 21.10.2019
28.10.2022 20:22:02
Спасибо всем кто откликнулся.
Из кода МатросаНаЗебре в сообщении #2, частично получилось решить нужный вопрос.
Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
28.10.2022 21:48:32
частично — это как?
макрос, который частично решает задачу — это гавно-макрос (извините за бедность речи). если задача не решена макросом полностью — она не решена вообще. в программировании с этим строго
Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Пользователь
Сообщений: 506 Регистрация: 21.10.2019
28.10.2022 23:20:12
| Цитата |
|---|
| написал: частично — это как? |
Игорь, я использовал из сообщения #2 часть кода позволяющую производить нажатие мышкой, но не в случайном месте, а в месте где курсор находится. Это позволило решить вопрос про который написал в первом сообщении.
Полностью код не использовал, т.к. перемещение курсора рандомное и к тому-же у меня получилось добиться эффекта только при клике. Перемещение не требуется. В предложенном макросе клик тоже получается в рандомном месте. Это может привести к непредвиденным действиям.
И книга у меня скрыта (возможно это тоже влияет на работу предложенного макроса).
Почему считаю что мой вопрос решен частично? Потому что я описал, что мне нужно встроить предложенный макрос в общую программу. Но мне не подходит нажатие мышкой в рандомном месте или месте где курсор оставил пользователь. Нужно на заголовке имеющейся формы.
Для этого создал ещё одну тему с просьбой помочь или подсказать как это сделать.
Но в любом случае человек помог идеей с примером исполнения, и за это ему Спасибо!
Изменено: asesja — 29.10.2022 00:36:50
Пользователь
Сообщений: 1496 Регистрация: 27.07.2013
Контакты см. в профиле
29.10.2022 06:19:05
В качестве прикола расскажу, как я решал этот вопрос лет 20 тому назад.
Писал макрос, который раз в, допустим, час закрывал дисковод. А сам дисковод заранее открывал и в дырку его петелькой засовывал провод от мыши (то есть, закрыться полностью он не мог, пытался закрыться — провод мешал — он обратно открывался). Макрос закрывал его, провод дергался, мышь дергалась, спящий режим откладывался.
Это ни в коем случае не призыв к повтору, это просто поржать
Скажи мне, кудесник, любимец ба’гов.
Сообщений: 22249 Регистрация: 28.12.2016
Excel 2013, 2016
29.10.2022 08:12:46
| Цитата |
|---|
| _Boroda_ написал: Это ни в коем случае не призыв к повтору, это просто поржать |
Как отключить ожидание действия в excel
Здравствуйте, друзья!
На первый взгляд простая задачка, но не тут-то было.
Суть макроса: в окне UserForm поочерёдно появляются слова и кнопками нужно выбирать, какое сохранить, а какое «удалить». Проблема в том, что макрос пролетает без остановок до последней итерации, а нужно чтобы он ждал ответа, т.е. нажатия на любую кнопку (commandButton).
С помощью стандартного MsgBox задача решается элементарно. Но мы не ищем лёгких путей Хочется покрасивее.
Почти уверен, что нужно использовать [vba]
[/vba]. Но как?
Помогите пожалуйста!
Здравствуйте, друзья!
На первый взгляд простая задачка, но не тут-то было.
Суть макроса: в окне UserForm поочерёдно появляются слова и кнопками нужно выбирать, какое сохранить, а какое «удалить». Проблема в том, что макрос пролетает без остановок до последней итерации, а нужно чтобы он ждал ответа, т.е. нажатия на любую кнопку (commandButton).
С помощью стандартного MsgBox задача решается элементарно. Но мы не ищем лёгких путей Хочется покрасивее.
Почти уверен, что нужно использовать [vba]
[/vba]. Но как?
Помогите пожалуйста! emkub
К сообщению приложен файл: ___.xlsm (24.9 Kb)
Сообщение Здравствуйте, друзья!
На первый взгляд простая задачка, но не тут-то было.
Суть макроса: в окне UserForm поочерёдно появляются слова и кнопками нужно выбирать, какое сохранить, а какое «удалить». Проблема в том, что макрос пролетает без остановок до последней итерации, а нужно чтобы он ждал ответа, т.е. нажатия на любую кнопку (commandButton).
С помощью стандартного MsgBox задача решается элементарно. Но мы не ищем лёгких путей Хочется покрасивее.
Почти уверен, что нужно использовать [vba]
[/vba]. Но как?
Помогите пожалуйста! Автор — emkub
Дата добавления — 11.08.2019 в 03:04
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Замечаний: 0% ±
Excel 2013
Методом тыка что-то получилось. Но положительного результата всё-равно нет. Похоже, что не передаются переменные от нажатий кнопок формы.
Методом тыка что-то получилось. Но положительного результата всё-равно нет. Похоже, что не передаются переменные от нажатий кнопок формы. emkub
К сообщению приложен файл: 7400324.xlsm (24.9 Kb)
Сообщение Методом тыка что-то получилось. Но положительного результата всё-равно нет. Похоже, что не передаются переменные от нажатий кнопок формы. Автор — emkub
Дата добавления — 12.08.2019 в 12:11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4082
Замечаний: 0% ±
Excel 2013/2016
зачем чего-то ждать если на время работы формы надо просто остановить код , сделав форму модальной. А еще корректнее не форму N раз открывать в цикле, а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок.
зачем чего-то ждать если на время работы формы надо просто остановить код , сделав форму модальной. А еще корректнее не форму N раз открывать в цикле, а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок. bmv98rus
Сообщение зачем чего-то ждать если на время работы формы надо просто остановить код , сделав форму модальной. А еще корректнее не форму N раз открывать в цикле, а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок. Автор — bmv98rus
Дата добавления — 12.08.2019 в 15:48
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Замечаний: 0% ±
Excel 2013
Ха! Логично! Сейчас понял не до конца, но суть уловил.
Спасибо.
Ха! Логично! Сейчас понял не до конца, но суть уловил.
Спасибо. emkub
Сообщение Ха! Логично! Сейчас понял не до конца, но суть уловил.
Спасибо. Автор — emkub
Дата добавления — 12.08.2019 в 16:21
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Замечаний: 0% ±
Excel 2013
Цитата bmv98rus, 12.08.2019 в 15:48, в сообщении № 3 ( )
а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок
Когда дело дошло до работы — моя версия работает, но очень коряво.
Вот теперь решил переделать по этому совету. Но не знаю, с какой стороны подступиться, куда вписать цикл, чтобы корректно работал.
Подскажите пожалуйста.
Цитата bmv98rus, 12.08.2019 в 15:48, в сообщении № 3 ( )
а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок
Когда дело дошло до работы — моя версия работает, но очень коряво.
Вот теперь решил переделать по этому совету. Но не знаю, с какой стороны подступиться, куда вписать цикл, чтобы корректно работал.
Подскажите пожалуйста. emkub
Цитата bmv98rus, 12.08.2019 в 15:48, в сообщении № 3 ( )
а формой крутить цикл и принимать решение что делать в зависимости от нажатых кнопок
Когда дело дошло до работы — моя версия работает, но очень коряво.
Вот теперь решил переделать по этому совету. Но не знаю, с какой стороны подступиться, куда вписать цикл, чтобы корректно работал.
Подскажите пожалуйста. Автор — emkub
Дата добавления — 30.11.2019 в 14:54
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Замечаний: 0% ±
Excel 2013
Наверное, достаточно просто показать, как с помощью UserForm крутить цикл.
Наверное, достаточно просто показать, как с помощью UserForm крутить цикл. emkub
Сообщение Наверное, достаточно просто показать, как с помощью UserForm крутить цикл. Автор — emkub
Дата добавления — 30.11.2019 в 15:14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4082
Замечаний: 0% ±
Excel 2013/2016
Не хочется разбираться в коде, но Вам нужно для каждой кнопке прописать что она делает. При инициализации просто загрузить первую для обработки информацию, а далее 4 события,
1. Оставить — значит перейти к следующему ключу
2. Удалить — Удалить ключ и выполнить п.1
3. Перейти к следующему товару
4 Закрыть форму.
Забудьте про циклы, просто продвигайтесь от первого до последнего.
Не хочется разбираться в коде, но Вам нужно для каждой кнопке прописать что она делает. При инициализации просто загрузить первую для обработки информацию, а далее 4 события,
1. Оставить — значит перейти к следующему ключу
2. Удалить — Удалить ключ и выполнить п.1
3. Перейти к следующему товару
4 Закрыть форму.
Забудьте про циклы, просто продвигайтесь от первого до последнего. bmv98rus
Сообщение Не хочется разбираться в коде, но Вам нужно для каждой кнопке прописать что она делает. При инициализации просто загрузить первую для обработки информацию, а далее 4 события,
1. Оставить — значит перейти к следующему ключу
2. Удалить — Удалить ключ и выполнить п.1
3. Перейти к следующему товару
4 Закрыть форму.
Забудьте про циклы, просто продвигайтесь от первого до последнего. Автор — bmv98rus
Дата добавления — 30.11.2019 в 15:53
Excel
На этой странице представлена коллекция простых и изящных инструментов работы в Excel. То, что по-английски называется tips & tricks. В некоторых случаях я даю ссылку на первоисточник. Это означает только то, что именно там я впервые встретил описание метода.
В Excel существует дополнительное (альтернативное) контекстное меню перетаскивания с полезными опциями (Alternate Drag and Drop menu). Чтобы получить к нему доступ выделите ячейку или диапазон ячеек. Перейдите к правому краю данных. Щелкните правой кнопкой мыши и перетащите границу вправо в новую область. Отпустите кнопку мыши.
Опция автофильтр для сводных таблиц заблокирована, но вы можете обмануть Excel и применить обычный автофильтр к заголовкам сводной таблицы, выделив одну ячейку за пределами сводной. Альтернатива — использовать для строк Фильтр по значению.
Если нужно найти сумму цифр в строке текста, можно воспользоваться алгоритмом: 1 * количество единиц в строке + 2 * количество двоек в строке + … + 9 * количество девяток в строке = результат
Некоторые инженерные проблемы проектирования требуют использования таблиц для вычисления значений параметров. Поскольку таблицы являются дискретными, можно использовать линейную интерполяцию для получения промежуточного значения параметра. Как быть, если существует два управляющих параметра? Можно выполнить вычисления с помощью одной мегаформулы.
Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.
Как правило, циклические формулы – зло, но иногда они полезны. Если вам нужно записать текст в ячейке в обратном порядке, можно воспользоваться кодом VBA. Однако, существует нетривиальный способ выполнить задачу с помощью формулы.
Для сортировки значений стандартно Excel использует опцию в таблицах. Но вы также можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ, соответственно, для сортировки диапазона по возрастанию и убыванию.
Если вы хотите сгенерировать неповторяющиеся случайные числа между 1 и 100, Excel стандартно предлагает функции СЛЧИС() и СЛУЧМЕЖДУ(), но обе они, генерируют случайные числа с морем дублей. Задачу можно решить с помощью создания массива из неиспользованных чисел диапазона, и уже из них выбирать случайным образом. Заметка интересна также методикой работы с мегаформулами.
Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k).
Несмотря на то, что привычный тип ссылок А1 является доминирующим, существуют ситуации, когда стиль R1C1 существенно эффективнее. Такой стиль удобнее при записи макросов, программировании VBA, использовании функции ДВССЫЛ и условном форматировании.
Функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Если вы хотите суммировать только видимые строки, используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Эта функция чаще используется в таблицах, но ничего не мешает использовать ее, как обычную функцию листа.
Функция ДВССЫЛ используется, когда нужно сослаться на ячейку, чей адрес будет определен на основе вычислений. ДВССЫЛ также справится, когда предварительно вычисляется лист, на который нужно сослаться. Билл Джелен описывает трюк, который справится, даже если имя листа представляет собой дату. Более того ДВССЫЛ позволяет ссылаться на диапазон ячеек, который затем используется внутри функций ВПР или СУММЕСЛИ.
Если у вас есть множество листов с идентичной структурой, например, 12 листов с коммерческими результатами за каждый месяц, вы можете найти сумму в одной и той же ячейке на каждом листе с помощью 3D-формулы. Если листы имеют имена Янв, Фев и т.д., а найти нужно сумму в ячейке В4 за все месяцы, используйте формулу =СУММ(Янв:Дек!B4).
Мало кто знает, что оператор пробел позволяет находить значение на пересечении множеств. Например, если вы создали горизонтальный именованный диапазон Бостон и вертикальный диапазон Продажи, то формула =СУММ(Бостон Продажи) позволяет найти значение продаж по Бостону.
До введения VBA, макросы писали на языке xlm (Excel Macro). Язык использовал макрофункции, т.е., функции листа макросов Excel 4.0. Этот язык до сих пор поддерживается Microsoft для совместимости с предыдущими версиями Excel. Язык xlm среди прочих содержит функцию Получить.Ячейку (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция ЯЧЕЙКА(). На самом деле, Получить.Ячейку может рассказать о 66 различных атрибутах ячейки, в то время, как функция ЯЧЕЙКА возвращает лишь 12 параметров.
Начиная с версии 2010 в Excel появилась гибкая функция ЧИСТРАБДНИ.МЕЖД, которая позволяет рассчитать количество рабочих дней для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней.
Если вам нужно найти номер первой непустой ячейки в строке (столбце), или значение первой непустой ячейки, можно воспользоваться одним малоизвестным свойством функции ИНДЕКС возвращать не одно значение, а массив.
В то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА/ЛОЖЬ в числовые 1/0, СУММПРОИЗВ не делает этого. Чтобы справиться с проблемами используйте операцию минус минус, или поместите все критерии в один аргумент, используя умножение (*) для Булевого критерия И и сложение (+) – для ИЛИ.
Если вы не начинающий пользователь, вас может заинтересовать книга Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В частности, вы узнаете о Булевой алгебре, операции минус минус, функции ДВССЫЛ, таймере, ссылках R1C1, датах до 1900 г.
Всё о сводных таблицам см. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. В частности, раскрыты следующие темы:
Функции массива — не самые простые для понимания, однако они дают такие возможности, что дух захватывает. Рекомендую книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Если вы хотите защитить лист, но оставить возможность обновлять сводную таблицу, стандартные методы Excel не помогут (странно, так как анонсировано, что должны помочь…). Рекомендую небольшой код VBA для частичной защиты листа.
Серьезный недостаток при создании сводных таблиц на основе больших наборов исходных данных — это ожидание завершения обработки всех данных таблицы при добавлении в нее нового поля. Настоящей пыткой становится добавление в сводную таблицу нескольких больших полей данных! Включите флажок Отложить обновление макета, расставьте все поля, как требуется, и лишь потом обновите сводную таблицу.
Уже стало традицией, что Microsoft от версии к версии что-то улучшает, а что-то ухудшает (по крайней мере, делает нечто не однозначное). Так, начиная с 2013 г. нельзя указать Excel сформировать вторую сводную таблицу на основе нового кеша. Как обойти это ограничение, читайте в заметке Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?
Начиная с версии 2010 г. в Excel добавлены срезы — удобный и наглядный инструмент работы со сводными таблицами; срез — это тот же фильтр, только вынесенный в отдельную область. А с 2013 г. добавлен особый вид срезов — временные шкалы, позволяющие визуализировать работу с датами.
С версии 2013 г. в Excel добавлены рекомендуемые сводные таблицы, которые облегчат работу начинающим пользователям и сократят время для опытных.
Начиная с версии 2010 г. в Excel появились спарклайны — маленькие диаграммы, помещающиеся в одну ячейку листа. Спарклайны позволяют обнаружить тенденции, периодические (сезонные) изменения, выделить максимальное (минимальное) значение.
Excel предоставляет достаточно много встроенных числовых форматов, однако возможны ситуации, в которых ни один вам не подойдет. В таком случае попробуйте создать собственный (пользовательский) числовой формат. Некоторые пользователи Excel стараются обходиться без создания собственных числовых форматов, так как считают этот процесс слишком трудоемким. На самом деле сложность пользовательских числовых форматов сильно преувеличена.
В версии Excel Professional Plus доступна надстройка Inquire. Она позволяет: проанализировать активную книгу, сравнить две книги, отобразить в виде схемы связь книги, листа, ячейки; очистить ненужное форматирование. Последнее особенно ценно, если вам достался файл от начинающего пользователя, любящего раскрашивать целые столбцы или строки.
В настоящее время в большинстве вычислений используются десятичные дроби. Однако в некоторых случаях вы можете столкнуться с простыми (обыкновенными) дробями. Это может быть учебный пример, или диаметр вентиля (в долях дюйма), или, наконец, вес золотого слитка (в долях унции). Excel предлагает 9 стандартных форматов для обработки обыкновенных дробей. Кроме того, вы можете создать большое число пользовательских форматов по своему усмотрению.
Если вам встретились незнакомые единицы измерения, например, баррель, oz, акр, узел, фунт, пинта, функция ПРЕОБР легко переведет их в привычные метры, литры, граммы…
Когда ваша книга Excel содержит множество листов, для удобства навигации вы, возможно, захотите создать оглавление (указатель). Вы можете сделать его вручную или воспользовавшись кодом VВA.
Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. По умолчанию Excel создает не обычные ссылки, типа =С4, а в виде формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вам это неудобно, или вы хотите иметь возможность копировать формулу вдоль столбца, научите Excel ссылаться на ячейку в сводной таблице, как на обычную.
Команда Специальная вставка позволит вам вставлять только значения ячеек (без формул и форматирования), перемещать данные из столбцов в строки и наоборот, преобразовывать диапазон чисел, прибавляя, вычитая, деля и умножая каждое число из диапазона на константу.
Функция ДВССЫЛ одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми.
Познакомьтесь с функцией СМЕЩ. С ее помощью вы сможете находить данные в тех случаях, когда не справляется ВПР. Главное же достоинство СМЕЩ проявляется в создании динамических диапазонов (ранее я касался этой темы).
Стандартные средства Excel не позволяют создавать контекстные выпадающие списки. Например, вы хотите, чтобы состав списка зависел от содержимого ячейки, находящейся слева от ячейки со списком. Создайте именованные массивы и сошлитесь на них с помощью функции непрямого действия ДВССЫЛ (INDIRECT).
Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. Например, только нечетные числа, или текст, начинающийся с буквы А. Реализовать такую возможность позволяет команда Проверка данных.
Некоторые статистические данные могут отображаться на диаграммах, даже без создания отдельных рядов. Многие (но не все) диаграммы позволяют дополнить ряды данных полосами погрешностей. Полосы погрешностей отображают дополнительную информацию о данных. Например, их можно использовать для изображения ошибки или неопределенности, связанной с каждой точкой данных.
В Excel есть малоизвестные биржевые диаграммы. Их еще называют блочными или ящик с усами. На мой взгляд, эти диаграммы незаслуженно обойдены вниманием. Я не исключаю, что знакомство с ними позволит вам сместиться от детерминированного взгляда на мир к вероятностному. А это больше соответствует действительности, особенно при планировании и прогнозировании.
Финансовые функции в Excel кажутся очень сложными. Чтобы их понимать, нужно разбираться в предметной области — что, собственно говоря, рассчитывают эти функции. Прочитайте заметку Сравнение аннуитетных и дифференцированных платежей в погашение ипотечного кредита, и для вас приоткроется завеса над некоторыми финансовыми функциями, в частности: ПЛТ, ОСПЛТ, ПРПЛТ, ПС, КПЕР, СТАВКА.
Если область данных, на основе которой построена диаграмма, расширилась, при этом часть данных ушла за границы видимой области листа Excel, отредактируйте строку формул, и на диаграмме появятся все новые данные.
Если вам надо переместить большой массив формул, содержащих относительные ссылки, в новую область листа с сохранением ссылок на те же ячейки, на которые ссылаются исходные формулы, воспользуйтесь заменой знака = на какой-нибудь редкий знак, а затем обратной заменой.
Если вас напрягает необходимость постоянно изменять область диаграммы после каждого добавления данных, замените в диаграммах ссылки на ячейки ссылками на именованные динамические диапазоны. Этот трюк позволяет также создавать диаграммы, отражающие определенное число последних записей (даже если добавите несколько новых)
Стандартные средства Excel в круговых диаграммах позволяют использовать только один набор данных. Если вы хотите сравнить два набора данных, воспользуйтесь несложным трюком, предложенным Д. Холи и Р. Холи в книге «Excel 2007. Трюки».
Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Однако само условное форматирование использует некие правила для выделения. Вместо того, чтобы анализировать цвет ячеек, достаточно применить эти правила в функциях СУММЕСЛИМН или БДСУММ.
Около двух лет тому назад мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Оказалось, что файл содержал объекты, и мне удалось их удалить с помощью небольшого кода VBA. У этой истории совсем недавно появилось продолжение. Посетитель блога оставил комментарий к той заметке, указав что стандартный Excel-файл, начиная с версии 2007, является zip-архивом. Так что для удаления «паразитного» содержимого достаточно открыть файл как архив и удалить соответствующую папку.
Если вам нужно подсчитать число вхождений подстроки (символа) в текст, можете использовать нехитрый VBA код или стандартные средства Excel на основе функции ПОДСТАВИТЬ()
Многим известна бухгалтерская «заморочка», связанная с расхождением на копейку в итогах суммирования. Как известно, Excel суммирует на основе точных значений, хранящихся в ячейке, а отражает в соответствии с выбранным вами форматом, например, с точностью до копейки. Чтобы преодолеть эту проблему предлагается изящная формула.
Вы думаете, что для моделирования с помощью метода Монте-Карло Excel требует какую-нибудь надстройку? Совсем не обязательно… Простейшие модели можно реализовать с помощью обычных таблиц и функции СЛЧИС. Правда, будьте осторожны, так как эта функция пересчитывается при каждом изменении на листе, и если вы захотите реализовать 100 000 сценариев, то Excel может «загрустить»… Но все же, для решения задач моделирования методом Монте-Карло лучше воспользоваться специализированной надстройкой, например, Crystal Ball. Читайте Моделирование методом Монте-Карло в Crystal Ball для Excel и Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel
Для вычисления среднего значения диапазона данных в Excel есть функция СРЗНАЧ. Она игнорирует пустые ячейки, но, к сожалению, учитывает ячейки с нулевыми значениями. Используйте элегантную и простую формулу массива для вычисления среднего, не учитывающего нулевые значения.
Откройте для себя мир формул массива. Ваши возможности в использовании Excel значительно расширятся. Если вы впервые знакомитесь с этой темой, начните с Введения в формулы массива, а затем изучите Некоторые примеры использования формул массива.
Если вам нужно найти адрес ячейки, содержащей максимальное (минимальное) значение в двумерном диапазоне, можно воспользоваться не очень простой, но весьма изящной формулой массива.
Если при открытии Excel-файла появляется предупреждение о циклической ссылке, существует простой способ найти ячейку, ответственную за это «безобразие».
Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0! Формула массива позволяет преодолеть это ограничение.
Ранее я описал, как транспонировать столбцы в строки с сохранением связи новой области со старой с помощью функции ДВССЫЛ. А недавно обнаружил значительно более простой и изящный способ, основанный на функции массива
Если данные разбросаны не вокруг среднего значения, а подчиняются линейному тренду, воспользуйтесь формулой массива для определения стандартного отклонения от линии тренда.
Иногда возникает желание выделить часть столбиков гистограммы. Как правило, это делают путем изменения цвета заливки, последовательно выделяя те или иные столбики. А можно подложить фон под часть столбиков.
Не все знают, что для расчета таблиц весьма полезными могут быть смешанные ссылки.
Стандартные средства Excel предоставляют ограниченные возможности для отображения подписей данных на пузырьковых диаграммах. Воспользуйтесь макросом, и вы сможете вывести рядом с пузырями практически любые подписи.
Умышленные или непреднамеренные действия способны исказить эффект, наблюдаемый на диаграммах. «Честные» графики должны показывать только то, что действительно содержится в данных. Эдвард Тафти предложил формулу для расчета фактора лжи, измеряющего искажения при визуализации данных.
Некоторые пользователи любят украшать свои диаграммы массой дополнительных элементов, например, горизонтальными и вертикальными линиями сетки, подписями данных и т.п. Живой классик инфографики Эдвард Тафти, наоборот, предлагает минимизировать количество элементов диаграммы.
В Excel нет стандартной функции суммирующей отдельно значения в четных и нечетных строках. Иногда возникает и более сложная задача — например, сложить значения в каждом пятом столбце. Рекомендую для этих целей функции массива. Проверьте мощь этих функций. Они не так сложны, как об этом принято думать.
Для сокращения числа неточностей при вводе данных воспользуйтесь раскрывающимся списком, основанном на диапазоне ячеек. Вы можете полностью запретить ввод недопустимых данных, или только ограничиться сообщением об ошибках.
Широко известна функция проверки правописания в Word или Outlook. Гораздо реже пользователи применяют проверку формул в Excel. В то же время такая проверка позволяет выявить довольно большое число ошибок.
Иногда в ряде данных отсутствуют некоторые значения. Например, если данные собираются вручную, то можно элементарно забыть провести измерение. На графиках Excel пропущенные данные можно не отражать вовсе, можно отразить нулевое значение или среднее значение двух соседних с пропущенной точек.
Затенение пространства между линиями графика. Если вы хотите закрасить пространство между двумя линиями графика, сделать это «в лоб» не получится. Однако реализовать задуманное вам поможет использование на одной диаграмме, как линий, так и областей с накоплением.
Изменение настраиваемого вычисления для поля в отчете сводных таблиц. Вместо привычной суммы, сводная способна выводить динамику (от ячейки к ячейке), сумму накопительным итогом, долю по строке или столбцу и много другое.
Заменить цифры в подписях осей ординат на пользовательское содержание вы можете с помощью дополнительного ряда данных в виде точечной диаграммы.
Если на графике вам необходимо разместить контрольные (опорные) горизонтальные линии, например, среднее значение и сигма-окрестность, добавьте дополнительные ряды данных и специальным образом отформатируйте линии на графике. Если же контрольную (опорную) вертикальную линию вам нужно добавить на гистограмму, воспользуйтесь малоизвестным приемом на основе специальной вставки.
Воспользуйтесь встроенными возможностями Excel для построения графика нормального распределения.
Если вы используете гистограммы с накоплением, то знаете, что стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу. Изящный трюк позволяет преодолеть эту проблему.
Если диаграмма слишком загромождена обилием данных можно воспользоваться элементами управления, например, полосой прокрутки, чтобы последовательно отражать на диаграмме одну часть данных за другой.
Пузырьковые диаграммы позволяют наглядно представить зависимость трех переменных. Могут использоваться при построении матриц типа BCG или в инфографике.
Преобразование массива в столбец или строку. Если необходимо преобразовать массив в столбец или строку, можно воспользоваться функцией Индекс.
Вывести примечания на лист. Если вы хотите вывести на текущий лист текст всех примечаний, можете воспользоваться макросом. Будьте осторожны, чтобы не затереть содержимое ячеек, расположенных справа от ячеек с примечаниями.
Создать сводную на основе данных с нескольких листов. Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Excel создаст сводную, но ее возможности будут существенно ограничены по сравнению со стандартными методами. Как обойти эти ограничения см. здесь.
Скрыть / показать ленту. Чтобы скрыть ленту дважды щелкнете по любой вкладке ленты. Чтобы временно отобразить ленту, щелкните нужную вкладку. Чтобы вернуть ленту, дважды щелкните на нужной вкладке.
Прекратить торможение. Если Excel тормозит, и никаких видимых причин к этому нет, проверьте нет ли на листе паразитных объектов. Пройдите по меню: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты. Если таких объектов много и Excel виснет, примените макрос.
Используйте сводную таблицу на основе другой сводной. Стандартные средства Excel это не позволяют сделать. Но с помощью небольшой хитрости можно обойти это ограничение.
Вызвать окно «Формат ячеек» при помощи клавиатуры (это быстрее, чем любой иной метод): нажмите CTRL+1
Использовать мастер сводных таблиц. В Excel2007 разработчики Microsoft почему-то решили отказаться от мастера сводных таблиц и упрятали его так далеко, что сразу и не найдешь. Для использования мастера сводных таблиц выведите его на панель быстрого доступа. Подробности здесь.
Используйте функцию СТРОКА для создания заполнителя. Иногда возникает задача создать список, содержащий структурированную последовательность, например, для секторов, рядов и мест в театре / стадионе или для стеллажей на складе. Подробности здесь.
Преобразуйте данные в числа. При импорте из внешних источников данные нередко выглядят как числа, но числами не являются. Их нельзя суммировать, и вообще с точки зрения Excel это текст. Воспользуйтесь одной из двух возможностей, описанных здесь, чтобы сделать числа настоящими.
Вручную измените цену делений на оси времени. Возможно, вы уже сталкивались с тем, что шкала времени на графиках иногда отражается «криво». Excel автоматически создает цену деления равную, например, 7 мин 12 сек. Креативненько, неправда ли!? Установите цену деления сами, не доверяя столь ответственную работу мозгам Excel.
Используйте функцию ДВССЫЛ для транспонирования строк в столбцы с сохранением формул. Иногда возникает задача переноса данных из столбцов в строки (или наоборот). Если при этом вам нужно не просто транспонировать значения, а еще и сохранить ссылки, то попробуйте применить функцию ДВССЫЛ.
Используйте формулы в условном форматировании. В частности, это позволяет легко выделять минимальное и максимальное значения в диапазоне ячеек или чередовать цвета строк в таблице. Подробности здесь.
Научите функцию ДЕНЬНЕД возвращать буквенное значение. Не знаю, как вас, а меня нервирует, что функция ДЕНЬНЕД возвращает число… например, 4. Куда как приятнее – «четверг». Эту проблему может легко решить небольшой код VBA или стандартные средства Excel.
Создать именованный динамический диапазон. Если вы работаете с данными, объем которых изменяется с течением времени (увеличивается число строк и/или столбцов), то очень удобно присвоить диапазону имя, а размер диапазона определить с помощью функции СМЕЩ. Подробности здесь.
Суммируйте значения в ячейках определенного цвета. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, у Excel`я нет такой стандартной функции. Воспользуйтесь простеньким кодом VBA. Вы также можете подсчитать число ячеек, содержащих текст определенного цвета.
Установите фильтр сводной таблицы на основе данных в ячейке. Если вы используете сводные таблицы в Excel, в которых содержатся тысячи или десятки тысяч значений, то выбор одного из них фильтром отчета наверняка сводил вас с ума… Введите значение, по которому требуется установить фильтр в ячейку на листе и запустите макрос.
Применяйте анализ чувствительности при построении моделей. Например, при подготовке инвестиционного проекта результатом моделирования явилась внутренняя норма доходности – IRR. Как поведет она себя при том или ином изменении исходных посылок? Подробности здесь.
Используйте функцию ВПР для извлечения данных из таблицы с двумя параметрами. Например, нужно определить заработную плату сотрудника исходя из грейда (квалификации) и режима работы (5-дневка или сменный). Подробности здесь.
Используйте шаблон диаграммы с двумя вертикальными осями. В тех случаях, когда требуется отобразить на одной диаграмме данные разного масштаба, полезно использовать две оси ординат. Типичный случай – абсолютные (рубли) и относительные (проценты) показатели. Например, размер дебиторской задолженности и её доля от реализации. Подробности здесь.
Используйте «говорящие» заголовки диаграмм. Такие заголовки сразу обращают внимание на основную мысль, которую вы хотите донести с помощью диаграммы. При этом обратите внимание на выбор типа диаграммы, который поможет лучше отразить вашу мысль. Подробности здесь.
Обновление подключения к внешним данным в Excel
Чтобы поддерживать импортированные внешние данные в актуальном состоянии, вы можете обновить их, чтобы просмотреть последние обновления и удаления. Excel предоставляет множество вариантов обновления данных, в том числе при открытии книги и с интервалами времени.
Примечание: Чтобы остановить обновление, нажмите клавишу ESC. Чтобы обновить лист, нажмите клавиши CTRL+F5. Чтобы обновить книгу, нажмите клавиши CTRL+ALT+F5.
Сведения об обновлении данных в приложении Excel
Обновление ключа и сводки по командам
В следующей таблице перечислены действия обновления, сочетания клавиш и команды.
Обновление выбранных данных на листе
Щелкните «>» в раскрывающемся списке рядом с кнопкой «Обновить все > «Обновить

»
Обновление всех данных в книге

Выбор > «Обновить все
Проверка состояния обновления
Дважды щелкните сообщение «Извлечение данных» в строке состояния.
прервать фоновое обновление.
Дважды щелкните сообщение в строке состояния.

Затем выберите » Остановить обновление» в диалоговом окне «Состояние обновления внешних данных».
Сведения об обновлении данных и безопасности
Данные в книге могут храниться непосредственно в книге или во внешнем источнике данных, например в текстовом файле, базе данных или облаке. При первом импорте внешних данных Excel создает сведения о подключении, которые иногда сохраняются в ODC-файле, в котором описывается, как найти, войти в систему, запросить и получить доступ к внешнему источнику данных.
При подключении к внешнему источнику данных можно выполнить операцию обновления для получения обновленных данных. При каждом обновлении данных вы увидите последнюю версию данных, включая все изменения, внесенные в данные с момента последнего обновления.
Дополнительные сведения об обновлении данных
В этом разделе объясняется, что происходит при обновлении данных, подключенных к внешнему источнику данных:
- Кто-то начинает обновлять подключения к книге, чтобы получить актуальные данные.
- Соединения создаются с внешними источниками данных, используемыми в книге.
Примечание: Вы можете получить доступ к различным источникам данных, таким как OLAP, SQL Server, поставщики OLEDB и драйверы ODBC.
Сведения о проблемах безопасности
Если вы подключены к внешнему источнику данных и пытаетесь обновить данные, важно знать о потенциальных проблемах безопасности и знать, что можно делать с любыми неполадками безопасности.
Отношения доверия — Внешние данные в настоящее время могут быть отключены на компьютере. Чтобы обновить данные при открытии книги, необходимо включить подключения к данным с помощью панели центра управления безопасностью или поместить книгу в надежное расположение. Дополнительные сведения см. в следующих статьях:
- Добавление, удаление или изменение надежного расположения для файлов
- Надежные документы
- Добавление, удаление и просмотр надежного издателя
- Просмотрите параметры и параметры в центре управления безопасностью.
ODC-файл . Файл подключения к данным (ODC) часто содержит один или несколько запросов, используемых для обновления внешних данных. Заменив этот файл, пользователь с вредоносным намерением может создать запрос для доступа к конфиденциальной информации и распространения ее другим пользователям или выполнить другие вредоносные действия. Поэтому важно убедиться, что файл подключения был создан надежным лицом, а файл подключения является безопасным и поступает из доверенной библиотеки подключений к данным (DCL).
Credentials — Для доступа к внешнему источнику данных обычно требуются учетные данные (например, имя пользователя и пароль), которые используются для проверки подлинности пользователя. Убедитесь, что эти учетные данные предоставлены вам безопасным и безопасным способом и что вы не случайно раскрываете эти учетные данные другим пользователям. Если внешнему источнику данных требуется пароль для получения доступа к данным, можно требовать, чтобы пароль вводится при каждом обновлении внешнего диапазона данных.
Общий доступ — Вы делите эту книгу с другими пользователями, которым может потребоваться обновить данные? Помогите коллегам избежать ошибок обновления данных, напоминая им о необходимости запрашивать разрешения для источников данных, предоставляющих данные.
Настройка параметров обновления при открытии или закрытии книги
Вы можете автоматически обновлять диапазон внешних данных при открытии книги. Вы также можете сохранить книгу, не сохраняя внешние данные, чтобы уменьшить размер файла.
- Выделите ячейку в диапазоне внешних данных.
- Выберите запросы > данных & подключения >« Подключения», щелкните правой кнопкой мыши запрос в списке и выберите «Свойства».
- В диалоговом окне «Свойства подключения» на вкладке «Использование» в разделе «Обновление» установите флажок «Обновить данные» при открытии флажка файла.
- Если требуется сохранить книгу с определением запроса, но без внешних данных, установите флажок Удалить данные из внешнего диапазона перед сохранением книги.
Регулярное автоматическое обновление данных
- Выделите ячейку в диапазоне внешних данных.
- Выберите запросы > данных & подключения >« Подключения», щелкните правой кнопкой мыши запрос в списке и выберите «Свойства».
- Перейдите на вкладку Использование.
- Установите флажок Обновлять каждые, а затем введите число минут между обновлениями.
Выполнение запроса в фоновом режиме или в режиме ожидания
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Рассмотрите возможность выполнения фонового обновления. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.
Примечание: Вы не можете выполнить запрос OLAP в фоновом режиме и не можете выполнить запрос для любого типа подключения, который извлекает данные для модели данных.
- Выделите ячейку в диапазоне внешних данных.
- Выберите запросы > данных & подключения >« Подключения», щелкните правой кнопкой мыши запрос в списке и выберите «Свойства».
- Выберите вкладку «Использование «.
- Установите флажок «Включить фоновое обновление», чтобы выполнить запрос в фоновом режиме. Снимите этот флажок, чтобы выполнить запрос во время ожидания. Совет Когда вы записываете макрос, содержащий запрос, Excel не будет запускать этот запрос в фоновом режиме. Чтобы изменить записанный макрос таким образом, что запрос будет запускаться в фоновом режиме, измените макрос в редактор Visual Basic. Для объекта QueryTable вместо метода обновления BackgroundQuery :=False используйте метод BackgroundQuery := True.
Запрос пароля при обновлении диапазона внешних данных
Сохраняемые пароли не зашифровываются, поэтому использовать их не рекомендуется. Если для подключения к источнику данных необходим пароль, вы можете потребовать, чтобы пользователи вводили пароль перед тем, как обновлять диапазон внешних данных. Приведенная ниже процедура не применяется к данным, полученным из текстового файла (.txt) или веб-запроса (IQY).
Совет: Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Например, надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
Очень важно запомнить пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.
- Выделите ячейку в диапазоне внешних данных.
- Выберите запросы > данных & подключения >« Подключения», щелкните правой кнопкой мыши запрос в списке и выберите «Свойства».
- Откройте вкладку «Определение » и снимите флажок « Сохранить пароль».
Примечание: Excel будет запрашивать у вас пароль только при первом обновлении диапазона внешних данных в каждом сеансе работы в Excel. При следующем запуске приложения Excel оно вновь попросит ввести пароль, если вы откроете книгу, содержащую запрос, и попытаетесь выполнить обновление данных.
Подробная справка по обновлению данных
Обновление данных в Power Query
При создании данных в Power Query, как правило, изменения загружаются на лист или модель данных. Важно понимать разницу при обновлении данных и способе их обновления.
Примечание: При обновлении новые столбцы, добавленные с момента последней операции обновления, добавляются в Power Query. Чтобы просмотреть эти новые столбцы, повторно изучите шаг « Источник» в запросе. Дополнительные сведения см. в разделе «Создание Power Query формул».
Большинство запросов основаны на ресурсах внешних данных того или иного типа. Однако существует ключевое различие между Excel и Power Query. Power Query кэширует внешние данные локально, чтобы повысить производительность. Кроме того, Power Query не обновляет локальный кэш автоматически, чтобы предотвратить затраты на источники данных в Azure.
Важно: Если в желтой строке сообщений в верхней части окна отображается сообщение «Эта предварительная версия может быть не более n дней назад», это обычно означает, что локальный кэш устарел. Чтобы обновить обновление , нажмите кнопку «Обновить».
Обновление запроса в Редактор Power Query
При обновлении запроса из Редактор Power Query вы не только переносите обновленные данные из внешнего источника данных, но и обновляете локальный кэш. Но эта операция обновления не обновляет запрос на листе или в модели данных.
- В Редактор Power Query выберите «Главная«
- Выберите «Обновить предварительный просмотр» > «Обновить предварительный просмотр» (текущий запрос в данных предварительной версии) или «Обновить все » (все открытые запросы на панели «Запросы»).
- В нижней части Редактор Power Query справа отображается сообщение «Предварительный просмотр загружен в am/PM». Это сообщение отображается при первом импорте и после каждой последующей операции обновления в Редактор Power Query.
Обновление запроса на листе
- В Excel выделите ячейку в запросе на листе.
- Выберите вкладку « Запрос» на ленте и нажмите кнопку «Обновить > «Обновить».
- Лист и запрос обновляются из внешнего источника данных и Power Query кэша.
- При обновлении запроса, импортированного из таблицы Excel или именованного диапазона, обратите внимание на текущий лист. Если вы хотите изменить данные на листе, содержащего таблицу Excel, убедитесь, что выбран правильный лист, а не лист, содержащий загруженный запрос.
- Это особенно важно при изменении заголовков столбцов в таблице Excel. Они часто похожи, и их легко спутать. Рекомендуется переименовать листы в соответствии с различиями. Например, можно переименовать их в TableData и QueryTable, чтобы подчеркнуть различие.
Обновление данных в сводной таблице
В любое время можно выбрать «Обновить «, чтобы обновить данные сводных таблиц в книге. Вы можете обновить данные для сводных таблиц, подключенных к внешним данным, таким как база данных (SQL Server, Oracle, Access или другие), куб Analysis Services, веб-канал данных, а также данные из исходной таблицы в той же или другой книге. Сводные таблицы можно обновлять вручную или автоматически при открытии книги.
Примечание: Дополнительные сведения см. в сводная таблица для анализа данных листа и изменения исходных данных для сводная таблица.
Обновление вручную
-
Выберите любое место в сводная таблица, чтобы отобразить вкладку сводная таблица «Анализ» на ленте.
Примечание: Чтобы обновить сводная таблица в Excel для Интернета, щелкните правой кнопкой мыши в любом месте сводная таблица выберите команду «Обновить».

Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.

- Выберите любое место в сводная таблица, чтобы отобразить вкладку сводная таблица «Анализ» на ленте.
- Выберите вкладку сводная таблица «Анализ> в группе сводная таблица выберите «Параметры».
- На вкладке «Формат & макета> установите флажки для автоподбора ширины столбцов при обновлении и сохраняйте форматирование ячеек при обновлении.
Автоматическое обновление данных при открытии книги

- Выберите любое место в сводная таблица, чтобы отобразить вкладку сводная таблица «Анализ» на ленте.
- Выберите вкладку сводная таблица «Анализ> в группе сводная таблица выберите «Параметры».
- На вкладке « Данные» выберите «Обновить данные» при открытии файла.
Обновление данных в автономном файле куба
Обновление файла автономного куба, при котором этот файл создается повторно с использованием самых свежих данных из серверного куба, может потребовать много времени и места на диске для хранения временных файлов. Запускайте этот процесс тогда, когда вам не нужен немедленный доступ к другим файлам в Excel и у вас достаточно места на диске, чтобы повторно сохранить этот файл.
- Выберите сводная таблица к автономному файлу куба.
- На вкладке « Данные» в группе «Запросы & подключения » щелкните стрелку под кнопкой «Обновить все» и выберите команду «Обновить».
Обновление данных в импортированном XML-файле
- На листе щелкните сопоставленную ячейку, чтобы выбрать карту XML, которую требуется обновить.
- Если вкладка Разработчик недоступна, откройте ее, выполнив указанные ниже действия.
- Откройте вкладку Файл, выберите пункт Параметры, а затем — Настроить ленту.
- В группе Основные вкладки установите флажок Разработчик и нажмите кнопку ОК.
Обновление данных в модели данных в Power Pivot
При обновлении модели данных в Power Pivot можно также увидеть, успешно ли выполняется обновление, сбой или отмена. Дополнительные сведения см. в статье «Power Pivot: эффективный анализ данных и моделирование данных в Excel».
Примечание: Добавление данных, изменение или изменение фильтров всегда активирует пересчет формул DAX, которые зависят от этого источника данных.
Обновление и просмотр состояния обновления
- В Power Pivot выберите home >Get External Data >Refresh or Refresh All (Обновить все), чтобы обновить текущую таблицу или все таблицы в модели данных.
- Состояние обновления указывается для каждого подключения, используемого в модели данных. Возможны три результата:
- Успешное выполнение — сообщает о количестве строк, импортированных в каждую таблицу.
- Ошибка . Возникает, если база данных находится в автономном режиме, у вас больше нет разрешений или таблица или столбец удалены или переименованы в источнике. Убедитесь, что база данных доступна, например, создав новое подключение в другой книге.
- Отменено — Excel не выдан запрос на обновление, возможно, из-за отключения обновления в подключении.
Отображение запросов, используемых при обновлении данных, с помощью свойств таблицы
Обновление данных — это просто повторное выполнение того же запроса, который использовался для получения данных в первую очередь. Вы можете просмотреть и иногда изменить запрос, просмотрев свойства таблицы в окне Power Pivot.
- Чтобы просмотреть запрос, используемый во время обновления данных, выберите Power Pivot >Manage , чтобы открыть окно Power Pivot.
- Выберите конструктор >таблицы.
- Перейдите Редактор запросов, чтобы просмотреть базовый запрос.
Запросы не видны для каждого типа источника данных. Например, запросы для импорта веб-канала данных не отображаются.
Настройка свойств подключения для отмены обновления данных
В Excel можно задать свойства подключения, определяющие частоту обновления данных. Если обновление не разрешено для определенного подключения, вы получите уведомление об отмене при запуске команды « Обновить все» или попытке обновить определенную таблицу, использующую подключение.
- Чтобы просмотреть свойства подключения, в Excel выберите запросы > данных & подключения, чтобы просмотреть список всех подключений, используемых в книге.
- Перейдите на вкладку « Подключения», щелкните правой кнопкой мыши соединение и выберите пункт «Свойства».
- Если на вкладке «Использование» в элементе управления «Обновить» снят флажок «Обновить подключение при обновлении всех«, при попытке обновить все в окне Power Pivot вы получите отмену.
Обновление данных в SharePoint Server 2013
В SharePoint Server можно запланировать автоматическое обновление данных на сервере, но для этого необходимо установить и настроить Power Pivot для SharePoint 2013 в среде SharePoint. Обратитесь к администратору SharePoint, чтобы узнать, доступно ли запланированное обновление данных. Дополнительные сведения см. в разделе «Настройка запланированного обновления данных для Power Pivot».
Обновление данных в 3D Maps
Если используемые для карты данные изменились, вы можете обновить их вручную в 3D Maps. После этого изменения отразятся на карте. Вот как это сделать:
-
В 3D Maps выберите «Главная >обновить данные».
Добавление данных в Power Map
Чтобы добавить новые данные в 3D MapsPower Map:
- В 3D Maps перейдите на карту, в которую вы хотите добавить данные.
- Оставьте окно 3D Maps открытым.
- В Excel выберите данные листа, которые вы хотите добавить.
- На ленте Excel щелкните Вставка > стрелка Карта >Добавить выбранные данные в Power Map. В 3D Maps автоматически будут отображены дополнительные данные. Дополнительные сведения см. в статье «Получение и подготовка данных для Power Map».
Обновление данных в службы Excel
Обновление внешних данных в службы Excel имеет уникальные требования.
Управление обновлением данных
Вы можете управлять обновлением данных из внешнего источника данных, выполнив одно или несколько из следующих действий.
Обновление при открытии с помощьюСлужбы Excel
В Excel можно создать книгу, которая автоматически обновляет внешние данные при открытии файла. В этом случае службы Excel всегда обновляет данные перед отображением книги и создает новый сеанс. Используйте его, если вы хотите убедиться, что актуальные данные всегда отображаются при открытии книги в Службы Excel.
- В книге с подключениями к внешним данным выберите вкладку «Данные «.
- В группе «Подключения » выберите > выберите >подключения.
- Перейдите на вкладку «Использование», а затем выберите «Обновить данные» при открытии файла.
Предупреждение: При снятии флажка «Обновить данные» отображаются данные, кэшированные вместе с книгой. Это означает, что при обновлении данных вручную пользователь видит актуальные данные во время текущего сеанса, но данные не сохраняются в книге.
Обновление с помощью ODC-файла
Если вы используете файл подключения к данным Office (ODC), убедитесь, что установлен флажок «Всегда использовать файл подключения»:
- В книге с подключениями к внешним данным выберите вкладку «Данные «.
- В группе «Подключения » выберите > выберите >подключения.
- Перейдите на вкладку «Определение», а затем выберите «Всегда использовать файл подключения».
Параметры сайта расположения надежных файлов, короткое время ожидания сеанса и время существования внешнего кэша данных, также могут повлиять на операции обновления. Дополнительные сведения см. в разделе «Администратор» или «Система справки».
Обновление вручную
- Выделите ячейку в сводная таблица отчета.
- На панели инструментов Excel Web Access в меню «Обновить » выберите «Обновить выбранное подключение».
- Если эта команда « Обновить» не отображается, автор веб-части очистит свойство «Обновить выбранное подключение» и » Обновить все подключения «. Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
- Любая интерактивная операция, которая вызывает повторный запрос к источнику данных OLAP, инициирует операцию обновления вручную.
- Обновите все подключения . На панели инструментов Веб-доступа Excel в меню «Обновление» щелкните «Обновить все подключения».
- Периодическое обновление . Можно указать, что данные автоматически обновляются с указанным интервалом после открытия книги для каждого подключения в книге. Например, база данных инвентаризации может обновляться каждый час, поэтому автор книги определил книгу для автоматического обновления каждые 60 минут. Автор веб-части может выбрать или очистить свойство « Разрешить периодическое обновление данных веб-доступа Excel», чтобы разрешить или запретить периодическое обновление. По истечении интервала времени по умолчанию в нижней части веб-части Excel Web Access появится оповещение об обновлении. Автор веб-части Excel Web Access также может задать свойство display Periodic Data Refresh Prompt для управления поведением сообщения, отображаемого при службы Excel периодических обновлений данных во время сеанса: Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
- Всегда — означает, что сообщение отображается с запросом с каждым интервалом.
- При необходимости — означает, что пользователь может продолжить периодическое обновление без отображения сообщения.
- Никогда — означает, что Excel Web Access периодически обновляется без отображения сообщения или запроса.
- Отмена обновления . Во время обновления книгиСлужбы Excel сообщение с запросом, так как это может занять больше времени, чем ожидалось. Чтобы остановить обновление , нажмите кнопку «Отмена», чтобы завершить обновление позже в более удобное время. Данные, возвращаемые запросами до отмены обновления, будут отображаться.
При использовании книги в браузере вы просматриваете данные, которые хранятся непосредственно в книге или внешних данных, где они хранятся в другом месте и требуют подключения к источнику или базе данных.
Как работает обновление внешних источников данных
В этом разделе объясняется, что происходит при обновлении данных, подключенных к внешнему источнику данных:
- Кто-то начинает обновлять подключения к книге, чтобы получить актуальные данные.
- Соединения создаются с внешними источниками данных, используемыми в книге.
Примечание: Вы можете получить доступ к различным источникам данных, таким как OLAP, SQL Server, поставщики OLEDB и драйверы ODBC.
Обновление данных в режиме просмотра или редактирования
Если вы просматриваете книгу в окне браузера и хотите обновить данные, это можно сделать в одном из двух режимов: режиме чтения или режиме редактирования.
Режим просмотра
В режиме просмотра достаточно просто обновить данные, отображаемые в книге. Это можно сделать в окне браузера или в Excel в режиме только для чтения (не редактирования).
В таком случае файл книги не считается измененным. Это значит, что в месте хранения книги ваши учетные данные не отображаются как учетные данные пользователя, недавно редактировавшего ее.
Режим правки
При обновлении книги в режиме редактирования книга открывается для редактирования (в окне браузера или в Excel). В этом случае файл книги будет считаться измененным. Это означает, что ваши учетные данные будут показаны как учетные данные пользователя, который недавно редактировал книгу.
Если вы редактируется книга и обновляете данные, изменения автоматически сохраняются после обновления данных. Все, у кого есть разрешения на просмотр книги, увидят внесенные вами изменения, как только книга будет сохранена.
Настройка параметров обновления данных в Excel
Для определения подключений к внешним данным для книги необходимо использовать Excel. Эти параметры невозможно задать при просмотре книги в окне браузера. Параметры, задаваемые с помощью Excel, сохраняются при публикации книги в библиотеке SharePoint.
Чтобы указать параметры обновления данных, нажмите кнопку «Свойства» для конкретного подключения к данным в Excel. В таблице ниже кратко описаны различные параметры обновления данных, доступные для выбора.
- Обновлять каждые __ минуты . Этот параметр заставляет книгу пытаться обновлять данные через указанные интервалы времени (в минутах).)
- Обновление данных при открытии файла. Этот параметр приводит к тому, что книга пытается автоматически обновить данные при открытии книги.
- Обновите это подключение на странице «Обновить все». Если этот флажок установлен, данные обновляются, когда вы или другие пользователи нажимаете кнопку «Обновить все «.
Действия в случае ошибки обновления данных
В зависимости от конфигурации среды обновление данных может не быть выполнено. Например, если кто-то создал книгу, использующую безопасные подключения к внешним данным к локальным серверам, эти подключения к данным, вероятно, не будут работать в Microsoft 365. Если вы не можете обновить данные в окне браузера, попробуйте открыть книгу в Excel.
Можно также обратиться к администратору SharePoint.
Для большинства подключений щелкните любую ячейку в диапазоне или таблице, использующей соединение, а затем выполните одно из следующих действий:
- Обновление только выбранных данных Нажмите клавиши ALT+F5 или на вкладке «Данные» в группе «Подключения» щелкните стрелку под кнопкой «Обновить все» и выберите команду «Обновить».

- Обновление всех данных в книге Нажмите клавиши CTRL+ALT+F5 или на вкладке «Данные» в группе «Подключения» нажмите кнопку «Обновить все».

- Управление тем, как и когда обновляется подключение На вкладке « Данные» в группе « Подключения» щелкните стрелку под кнопкой «Обновить все» и выберите пункт «Свойства подключения».
В открывшемся диалоговом окне выберите нужные параметры на вкладке «Использование» в разделе «Элемент управления обновлениями».
Некоторые операции обновления требуют дополнительных действий, как описано в следующих разделах. Если источник данных отображается в списке, просмотрите соответствующий раздел.
Автоматическое обновление данных при открытии книги
Вы можете автоматически обновлять диапазон внешних данных при открытии книги. Кроме того, при сохранении рабочей книги можно не сохранять внешние данные, чтобы уменьшить размер файла.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Перейдите на вкладку Использование.
- В группе Обновление экрана установите флажок Обновить при открытии файла.
- Если требуется сохранить книгу с определением запроса, но без внешних данных, установите флажок Удалить данные из внешнего диапазона перед сохранением книги.
Регулярное автоматическое обновление данных

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Перейдите на вкладку Использование.
- Установите флажок Обновлять каждые, а затем введите число минут между обновлениями.
Запрос пароля при обновлении диапазона внешних данных
Сохраняемые пароли не зашифровываются, поэтому использовать их не рекомендуется. Если для подключения к источнику данных необходим пароль, вы можете потребовать, чтобы пользователи вводили пароль перед тем, как обновлять диапазон внешних данных. Приведенная ниже процедура не применяется к данным, полученным из текстового файла (.txt) или веб-запроса (IQY).
Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Например, надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
Очень важно запомнить пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Откройте вкладку Определение и снимите флажок Сохранить пароль.
Примечание: Excel будет запрашивать у вас пароль только при первом обновлении диапазона внешних данных в каждом сеансе работы в Excel. При следующем запуске приложения Excel оно вновь попросит ввести пароль, если вы откроете книгу, содержащую запрос, и попытаетесь выполнить обновление данных.
Выполнение запроса в фоновом режиме или в режиме ожидания
Очень большие наборы данных рекомендуется обновлять в фоновом режиме. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
Примечание: Невозможно запустить запрос OLAP в фоновом режиме.
Примечание: Невозможно запустить запрос для любого типа подключения, получающего данные для модели данных.
Отмена и проверка состояния обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, вы можете сделать следующее:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление данных из импортированного текстового файла
Если для запроса текстового файла использовался метод Get & Transform, дальнейшие действия не требуются. Если вы импортируете текстовый файл, выполните следующие действия для обновления:

- Выделите любую ячейку в диапазоне или в таблице, которые содержат ссылку на импортированный текстовый файл.
- На вкладке « Данные » в группе «Подключения» нажмите кнопку » Обновить все». Чтобы обновить только выделенные данные, выберите команду Обновить. Вы также можете щелкнуть ячейку в диапазоне или таблице правой кнопкой мыши и выбрать команду Обновить.
- В диалоговом окне Импорт текстового файла выберите нужный текстовый файл, а затем нажмите кнопку Импорт.
Обновление файла автономного куба
Обновление файла автономного куба, при котором этот файл создается повторно с использованием самых свежих данных из серверного куба, может потребовать много времени и места на диске для хранения временных файлов. Запускайте этот процесс тогда, когда вам не нужен немедленный доступ к другим файлам в Excel и у вас достаточно места на диске, чтобы повторно сохранить этот файл.

- Выберите сводную таблицу, подключенную к файлу автономного куба.
- На вкладке « Данные » в группе «Подключения» щелкните «Обновить все» и выберите команду «Обновить».
Обновление данных в импортированном XML-файле

- На листе щелкните сопоставленную ячейку, чтобы выбрать карту XML, которую требуется обновить.
- Если вкладка Разработчик недоступна, откройте ее, выполнив указанные ниже действия.
- Откройте вкладку Файл, выберите пункт Параметры, а затем — Настроить ленту.
- В группе Основные вкладки установите флажок Разработчик и нажмите кнопку ОК.
- На вкладке Разработчик в группе XML нажмите кнопку Обновить данные.
Обновление данных, подключенных к другой книге
- На вкладке « Данные » в группе «Подключения» нажмите кнопку » Обновить все».
Если у вас несколько подключений и вы хотите обновить только определенное, выделите любую ячейку в этом диапазоне данных, щелкните стрелку рядом с кнопкой «Обновить все» и нажмите кнопку «Обновить «.

Управление тем, как и когда обновляется подключение

- Щелкните любую ячейку, содержащую подключенные данные.
- На вкладке « Данные» в группе « Подключения» щелкните стрелку рядом с кнопкой «Обновить все» и выберите пункт «Свойства подключения».
- На вкладке « Использование» задайте все параметры, которые необходимо изменить. Для очень больших наборов данных рассмотрите возможность проверки параметра «Включить фоновое обновление «. Это возвращает вам управление Excel сразу после начала обновления, а не ожидание завершения обновления.
Отмена и проверка состояния обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, вы можете сделать следующее:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление импортированных данных в PowerPivot
При использовании надстройки Power Pivot для обновления ранее импортированных данных можно увидеть, успешно ли выполняется обновление, сбой или отмена.
В Power Pivot нажмите кнопку «Главная > Получить внешние данные >«Обновить» или «Обновить все», чтобы повторно импортировать текущую таблицу или все таблицы в модели данных.
Состояние обновления указывается для каждого подключения, используемого в модели данных. Возможны три результата.

«Успешно» сообщает о количестве строк, импортированных в каждую таблицу.
Ошибка может возникнуть, если база данных находится в автономном режиме, у вас больше нет разрешений или таблица или столбец удалены или переименованы в источнике. Убедитесь, что база данных доступна, например, создав новое подключение в другой книге.
«Отменено» означает, что Excel не выполнял запрос на обновление, возможно, из-за отключения обновления в подключении.
Совет: Вы делите эту книгу с другими пользователями, которым может потребоваться обновить данные? Помогите коллегам избежать ошибок обновления данных, напоминая им о необходимости запрашивать разрешения для источников данных, предоставляющих данные.
Свойства таблицы показывают запросы, используемые при обновлении данных
Обновление данных — это просто повторное выполнение того же запроса, который использовался для получения данных в первую очередь. Вы можете просмотреть и иногда изменить запрос, просмотрев свойства таблицы в Power Pivot окна.
- Чтобы просмотреть запрос, используемый во время обновления данных, щелкните Power Pivot >« Управление», чтобы открыть Power Pivot данных.
- Нажмите кнопку « >таблицы».
- Перейдите Редактор запросов, чтобы просмотреть базовый запрос.
Запросы не видны для каждого типа источника данных. Например, запросы для импорта веб-канала данных не отображаются.
Свойства подключения, которые отменяют обновление данных
В Excel можно задать свойства подключения, определяющие частоту обновления данных. Если обновление не разрешено для определенного подключения, вы получите уведомление об отмене при запуске команды « Обновить все» или попытке обновить определенную таблицу, использующую подключение.
- Чтобы просмотреть свойства подключения, в Excel щелкните «> подключения», чтобы просмотреть список всех подключений, используемых в книге.
- Выберите подключение и нажмите кнопку «Свойства».
- Если в разделе «Использование» в элементе управления «Обновление» снят флажок «Обновить это подключение при обновлении всех«, при попытке обновить все в окне обновления Power Pivot отобразить отмену.
Обновление данных в SharePoint Server 2013 или более поздней версии
В SharePoint Server можно запланировать автоматическое обновление данных на сервере, но для этого необходимо установить и настроить Power Pivot для SharePoint 2013 в среде SharePoint. Обратитесь к администратору SharePoint, чтобы узнать, доступно ли запланированное обновление данных.
Обновление запроса (Редактор Power Query)
Примечание: Этот Редактор Power Query является частью get & Transform. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.
С помощью Редактор Power Query можно обновить запрос для импорта последних данных в таблицу без повторного создания запроса.
Чтобы обновить запрос, выполните следующее.
- Нажмите кнопку «&» >«Показать запросы».
- В области «Запросы книги » выберите запрос, который требуется обновить.
- На ленте Редактор Power Query нажмите кнопку «Главная >» >«Обновить».

Обновление или добавление данных 3D Maps
Если используемые для карты данные изменились, вы можете обновить их вручную в 3D Maps. После этого изменения отразятся на карте. Вот как это сделать:
- В 3D Maps выберите Главная >Обновить данные.

Добавление данных в Power Map
Чтобы добавить новые данные в 3D MapsPower Map:
- В 3D Maps перейдите на карту, в которую вы хотите добавить данные.
- Оставив открытым окно 3D Maps, щелкните в Excel и выберите данные листа, которые вы хотите добавить.
- На ленте Excel щелкните Вставка > стрелка Карта >Добавить выбранные данные в Power Map.

В 3D Maps автоматически будут отображены дополнительные данные.
Обновление внешних данных в службы Excel
Данные в книге Microsoft Office Excel, отображаемые в службы Excel могут поступать из двух разных расположений. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в базе данных или в кубе OLAP.
Если данные поступают из внешнего источника данных, автор книги или администратор определил сведения о подключении, часто в ODC-файле, который описывает, как найти, войти, запросить и получить доступ к внешнему источнику данных.
При подключении к внешнему источнику данных можно выполнить операцию обновления для получения обновленных данных. При каждом обновлении данных вы увидите последнюю версию данных, включая все изменения, внесенные в данные с момента последнего обновления.
Сведения об обновлении данных
На следующем рисунке объясняется базовый процесс обновления данных, подключенных к внешнему источнику данных.
- Операция обновления получает актуальные данные.
- Соединение, часто сохраненное в файле подключения, определяет всю информацию, необходимую для доступа и получения данных из внешнего источника данных.
- Вы можете получить доступ к различным источникам данных, таким как OLAP, SQL Server, поставщики OLEDB и драйверы ODBC.
- Актуальные данные добавляются в текущую книгу.
Сведения о проблемах безопасности
Если вы подключены к внешнему источнику данных и пытаетесь обновить данные, важно знать о потенциальных проблемах безопасности и знать, что можно делать с любыми неполадками безопасности.
Файл подключения к данным часто содержит один или несколько запросов, используемых для обновления внешних данных. Заменив этот файл, пользователь с вредоносным намерением может создать запрос для доступа к конфиденциальной информации и распространения ее другим пользователям или выполнить другие вредоносные действия. Поэтому важно убедиться, что файл подключения был создан надежным лицом, а файл подключения является безопасным и поступает из доверенной библиотеки подключений к данным (DCL).
Дополнительные сведения о том, как администратор может создавать файлы подключения, создавать надежные расположения и файлы безопасных подключений, см. в справке Microsoft Office Excel и справке центра администрирования Office SharePoint Server.
Примечание: Для доступа к внешнему источнику данных обычно требуются учетные данные (например, имя пользователя и пароль), которые используются для проверки подлинности пользователя. Убедитесь, что эти учетные данные предоставлены вам безопасным и безопасным способом и что вы не случайно раскрываете эти учетные данные другим пользователям.
Управление обновлением данных
Вы можете управлять обновлением данных из внешнего источника данных, выполнив одно или несколько из следующих действий:
- Обновление при открытии В Excel автор книги может создать книгу, которая автоматически обновляет внешние данные при открытии книги, выбрав «Обновить данные» при открытии флажка файла в диалоговом окне «Свойства подключения». (На вкладке «Данные» в группе «Подключения» щелкните «Подключения«, выберите подключение, щелкните «Свойства«, откройте вкладку «Использование» и выберите команду «Обновить данные» при открытии файла.) В этом случае службы Excel всегда обновляет данные перед отображением книги и создает новый сеанс. Если автор книги очищает данные обновления при открытии флажка файла, отображаются данные, кэшированные вместе с книгой. Это означает, что когда пользователь вручную обновляет данные, пользователь видит обновленные актуальные данные во время текущего сеанса, но данные не сохраняются в книге. Если вы хотите, чтобы при отображении книги в службы Excel всегда отображались актуальные данные, установите флажок «Обновить» при открытии файла с помощью Office Excel. Если вы используете файл подключения к данным Office (ODC), установите флажок «Всегда использовать файл подключения» в диалоговом окне «Свойства подключения» (на вкладке «Данные» в группе «Подключения» щелкните «Подключения«, выберите подключение, щелкните «Свойства», откройте вкладку «Определение» и выберите пункт «Всегда использовать файл подключения»). Параметры сайта расположения надежных файлов, короткое время ожидания сеанса и время существования внешнего кэша данных, также могут повлиять на операции обновления. Дополнительные сведения см. в разделе администратора или в справочной системе центра администрирования Microsoft Office SharePoint Server.
- Обновление вручную
Примечание: Вы можете вручную обновить только сводная таблица отчета.
- Выберите ячейку в сводная таблица отчета.
- На панели инструментов Веб-доступа Excel в меню « Обновить» щелкните » Обновить выбранное подключение».
- Любая интерактивная операция, которая вызывает повторный запрос к источнику данных OLAP, инициирует операцию обновления вручную.
- Если эта команда « Обновить» не отображается, автор веб-части очистит свойство «Обновить выбранное подключение» и » Обновить все подключения «. Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
- Всегда означает, что сообщение отображается с запросом с каждым интервалом.
- Дополнительно означает, что пользователь может продолжить периодическое обновление без отображения сообщения.
- Нвер означает, что Excel Web Access выполняет периодическое обновление без отображения сообщения или запроса.
Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
Обновление данных из Microsoft Query, мастера подключения данных или веб-запроса

- Выделите любую ячейку в диапазоне или таблице, которые содержат ссылки на внешние данные.
- На вкладке « Данные » в группе «Подключения» нажмите кнопку » Обновить все». Чтобы обновить только выделенные данные, выберите команду Обновить. Вы также можете щелкнуть ячейку в диапазоне или таблице правой кнопкой мыши и выбрать команду Обновить.
Примечание: Если у вас открыто несколько рабочих книг, то необходимо сделать это для каждой рабочей книги.
Обновление данных из импортированного текстового файла

- Выделите любую ячейку в диапазоне или в таблице, которые содержат ссылку на импортированный текстовый файл.
- На вкладке « Данные » в группе «Подключения» нажмите кнопку » Обновить все». Чтобы обновить только выделенные данные, выберите команду Обновить. Вы также можете щелкнуть ячейку в диапазоне или таблице правой кнопкой мыши и выбрать команду Обновить.
- В диалоговом окне Импорт текстового файла выберите нужный текстовый файл, а затем нажмите кнопку Импорт.
Обновление файла автономного куба
Обновление файла автономного куба, при котором этот файл создается повторно с использованием самых свежих данных из серверного куба, может потребовать много времени и места на диске для хранения временных файлов. Запускайте этот процесс тогда, когда вам не нужен немедленный доступ к другим файлам в Excel и у вас достаточно места на диске, чтобы повторно сохранить этот файл.

- Выберите сводную таблицу, подключенную к файлу автономного куба.
- На вкладке « Данные » в группе «Подключения» щелкните «Обновить все» и выберите команду «Обновить».
Обновление данных в импортированном XML-файле

- На листе щелкните сопоставленную ячейку, чтобы выбрать карту XML, которую требуется обновить.
- Если вкладка Разработчик недоступна, откройте ее, выполнив указанные ниже действия.
- Откройте вкладку Файл, выберите пункт Параметры, а затем — Настроить ленту.
- В группе Основные вкладки установите флажок Разработчик и нажмите кнопку ОК.
- На вкладке Разработчик в группе XML нажмите кнопку Обновить данные.
Обновление данных, подключенных к другой книге
- На вкладке « Данные» нажмите кнопку «Обновить все».
Если у вас несколько подключений и вы хотите обновить только определенное, выделите любую ячейку в этом диапазоне данных, щелкните стрелку рядом с кнопкой «Обновить все» и нажмите кнопку «Обновить «.

Управление тем, как и когда обновляется подключение

- Щелкните любую ячейку, содержащую подключенные данные.
- На вкладке « Данные» щелкните стрелку рядом с кнопкой «Обновить все» и выберите пункт «Свойства подключения».
- На вкладке « Использование» задайте все параметры, которые необходимо изменить. Для очень больших наборов данных рассмотрите возможность проверки параметра «Включить фоновое обновление «. Это возвращает вам управление Excel, а не ожидание завершения обновления.
Отмена и проверка состояния обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, вы можете сделать следующее:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление импортированных данных в PowerPivot
При использовании надстройки Power Pivot для обновления ранее импортированных данных можно увидеть, успешно ли выполняется обновление, сбой или отмена.

- В Power Pivot нажмите кнопку «Главная > Получить внешние данные >«Обновить» или «Обновить все», чтобы повторно импортировать текущую таблицу или все таблицы в модели данных. Состояние обновления указывается для каждого подключения, используемого в модели данных. Возможны три результата.
«Успешно» сообщает о количестве строк, импортированных в каждую таблицу.
Ошибка может возникнуть, если база данных находится в автономном режиме, у вас больше нет разрешений или таблица или столбец удалены или переименованы в источнике. Убедитесь, что база данных доступна, например, создав новое подключение в другой книге.
«Отменено» означает, что Excel не выполнял запрос на обновление, возможно, из-за отключения обновления в подключении.
Совет: Вы делите эту книгу с другими пользователями, которым может потребоваться обновить данные? Помогите коллегам избежать ошибок обновления данных, напоминая им о необходимости запрашивать разрешения для источников данных, предоставляющих данные.
Свойства таблицы показывают запросы, используемые при обновлении данных
Обновление данных — это просто повторное выполнение того же запроса, который использовался для получения данных в первую очередь. Вы можете просмотреть и иногда изменить запрос, просмотрев свойства таблицы в Power Pivot окна.
- Чтобы просмотреть запрос, используемый во время обновления данных, щелкните Power Pivot >« Управление», чтобы открыть Power Pivot данных.
- Нажмите кнопку « >таблицы».
- Перейдите Редактор запросов, чтобы просмотреть базовый запрос.
Запросы не видны для каждого типа источника данных. Например, запросы для импорта веб-канала данных не отображаются.
Свойства подключения, которые отменяют обновление данных
В Excel можно задать свойства подключения, определяющие частоту обновления данных. Если обновление не разрешено для определенного подключения, вы получите уведомление об отмене при запуске команды « Обновить все» или попытке обновить определенную таблицу, использующую подключение.
- Чтобы просмотреть свойства подключения, в Excel щелкните «> подключения», чтобы просмотреть список всех подключений, используемых в книге.
- Выберите подключение и нажмите кнопку «Свойства».
- Если в разделе «Использование» в элементе управления «Обновление» снят флажок «Обновить это подключение при обновлении всех«, при попытке обновить все в окне обновления Power Pivot отобразить отмену.
Обновление данных в SharePoint Server 2013
В SharePoint Server 2013 можно запланировать автоматическое обновление данных на сервере, но для этого необходимо установить и настроить Power Pivot для SharePoint 2013 в среде SharePoint. Обратитесь к администратору SharePoint, чтобы узнать, доступно ли запланированное обновление данных.
Обновление запроса (Power Query)
С Power Query можно обновить запрос для импорта последних данных в таблицу без повторного создания запроса.
- Excel 2010 Power Query не включен в Excel 2010, но вы можете скачать, установить и активировать надстройку Power Query надстройки.
- Чтобы активировать надстройку Power Query, щелкните «Параметры >« >надстройки. В раскрывающемся списке Управление в нижней части экрана выберите пункт Надстройки COM и нажмите кнопку Перейти. Установите флажок Power Query и нажмите кнопку ОК. Лента Power Query должна отображаться автоматически, но в противном случае закройте и перезапустите Excel.
Чтобы обновить запрос, выполните следующее.
- На ленте Редактор запросов нажмите кнопку «Обновить предварительный просмотр».

Примечание: Редактор запросов отображается только при загрузке, редактировании или создании нового запроса с помощью Power Query. В видео показано окно редактора запросов, которое отображается после изменения запроса в книге Excel. Чтобы просмотреть Редактор запросов без загрузки или редактирования существующего запроса книги, в разделе «Получение внешних данных» на вкладке ленты Power Query выберите «Из других источников> пустой запрос». В видео показан один из способов отображения редактора запросов.
Обновление подключения к базе данных Access
Если книга Excel подключена к таблице или запросу в базе данных Access, вы можете убедиться, что в ней отображаются самые актуальные сведения. Для этого используйте команду «Обновить» (на вкладке «Данные»).
- На листе щелкните любую ячейку, содержащую данные Access.
- На вкладке « Данные » в группе «Подключения» щелкните стрелку рядом с кнопкой «Обновить все» и выберите команду «Обновить».

Чтобы контролировать, как и как часто обновляется подключение:
- Щелкните любую ячейку, содержащую данные Access.
- В группе «Подключения » щелкните стрелку рядом с кнопкой «Обновить все» и выберите пункт «Свойства подключения».
Очень большие наборы данных рекомендуется обновлять в фоновом режиме. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.

- На вкладке « Использование» задайте все параметры, которые необходимо изменить.
Проверка состояния или отмена обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить обновление или отменить его, выполните одно или несколько из следующих действий:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление подключения SQL Server подключения
Если книга Excel подключена к таблице или запросу в базе SQL Server, вы можете убедиться, что в ней отображаются самые актуальные сведения. Для этого используйте команду «Обновить» (на вкладке «Данные»).
- На листе щелкните любую ячейку, содержащую SQL Server данных.
- На вкладке « Данные » в группе «Подключения» щелкните стрелку рядом с кнопкой «Обновить все» и выберите команду «Обновить».

Чтобы контролировать, как и как часто обновляется подключение:
- Щелкните любую ячейку, содержащую SQL Server данных.
- В группе «Подключения » щелкните стрелку рядом с кнопкой «Обновить все» и выберите пункт «Свойства подключения».
Очень большие наборы данных рекомендуется обновлять в фоновом режиме. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.

- На вкладке « Использование» задайте любые параметры, которые вы хотите изменить, например включение фонового обновления.
Отмена и проверка состояния обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, вы можете сделать следующее:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление подключения OLE DB
Если книга Excel подключена к таблице или запросу в источнике данных OLE DB, вы можете убедиться, что в ней отображаются самые актуальные сведения. Для этого используйте команду «Обновить» (на вкладке «Данные»).
- На листе щелкните любую ячейку, содержащую данные OLE DB.
- На вкладке « Данные » в группе «Подключения» щелкните стрелку рядом с кнопкой «Обновить все» и выберите команду «Обновить».

Чтобы контролировать, как и как часто обновляется подключение:
- Щелкните любую ячейку, содержащую данные OLE DB.
- В группе «Подключения » щелкните стрелку рядом с кнопкой «Обновить все» и выберите пункт «Свойства подключения».
Очень большие наборы данных рекомендуется обновлять в фоновом режиме. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.

- На вкладке « Использование» задайте все параметры, которые необходимо изменить.
Проверка или отмена обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, выполните одно из следующих действий:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.
Обновление или добавление данных 3D Maps
Если используемые для карты данные изменились, вы можете обновить их вручную в 3D Maps. После этого изменения отразятся на карте. Вот как это сделать:
- В 3D Maps выберите Главная >Обновить данные.

Добавление данных в Power Map
Чтобы добавить новые данные в 3D MapsPower Map:
- В 3D Maps перейдите на карту, в которую вы хотите добавить данные.
- Оставив открытым окно 3D Maps, щелкните в Excel и выберите данные листа, которые вы хотите добавить.
- На ленте Excel щелкните Вставка > стрелка Карта >Добавить выбранные данные в Power Map.

В 3D Maps автоматически будут отображены дополнительные данные.
Обновление данных в сводной таблице
Обновление вручную

- Щелкните в любом месте сводной таблицы. Откроется окно сводная таблица Tools, в которое будут добавлены параметры и вкладка «Конструктор«.
- На вкладке « Параметры » в группе данных выполните одно из следующих действий:
- Чтобы обновить сведения в соответствии с источником данных, нажмите кнопку «Обновить» или нажмите клавиши ALT+F5. Вы также можете щелкнуть правой кнопкой мыши сводная таблица кнопку «Обновить».
- Чтобы обновить все сводные таблицы в книге, нажмите кнопку «Обновить» и выберите команду «Обновить все». Если обновление занимает больше времени, чем ожидалось , нажмите кнопку >«Обновить >состояние обновления», чтобы проверить состояние обновления. Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.

- Щелкните «Параметры >«.
- На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое сводная таблица данных при открытии книги

- Щелкните в любом месте сводной таблицы.
- На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
- В диалоговом сводная таблица «Параметры» на вкладке «Данные» установите флажок «Обновить данные».
Обновление внешних данных в службы Excel
Данные в книге Microsoft Office Excel, отображаемые в службы Excel могут поступать из двух разных расположений. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в базе данных или в кубе OLAP.
Если данные поступают из внешнего источника данных, автор книги или администратор определил сведения о подключении, часто в ODC-файле, который описывает, как найти, войти, запросить и получить доступ к внешнему источнику данных.
При подключении к внешнему источнику данных можно выполнить операцию обновления для получения обновленных данных. При каждом обновлении данных вы увидите последнюю версию данных, включая все изменения, внесенные в данные с момента последнего обновления.
Сведения об обновлении данных
На следующем рисунке объясняется базовый процесс обновления данных, подключенных к внешнему источнику данных.
- Операция обновления получает актуальные данные.
- Соединение, часто сохраненное в файле подключения, определяет всю информацию, необходимую для доступа и получения данных из внешнего источника данных.
- Вы можете получить доступ к различным источникам данных, таким как OLAP, SQL Server, поставщики OLEDB и драйверы ODBC.
- Актуальные данные добавляются в текущую книгу.
Сведения о проблемах безопасности
Если вы подключены к внешнему источнику данных и пытаетесь обновить данные, важно знать о потенциальных проблемах безопасности и знать, что можно делать с любыми неполадками безопасности.
Файл подключения к данным часто содержит один или несколько запросов, используемых для обновления внешних данных. Заменив этот файл, пользователь с вредоносным намерением может создать запрос для доступа к конфиденциальной информации и распространения ее другим пользователям или выполнить другие вредоносные действия. Поэтому важно убедиться, что файл подключения был создан надежным лицом, а файл подключения является безопасным и поступает из доверенной библиотеки подключений к данным (DCL).
Дополнительные сведения о том, как администратор может создавать файлы подключения, создавать надежные расположения и файлы безопасных подключений, см. в справке microsoft Office Excel 2007 и справке центра администрирования Office SharePoint Server.
Примечание по безопасности: Для доступа к внешнему источнику данных обычно требуются учетные данные (например, имя пользователя и пароль), которые используются для проверки подлинности пользователя. Убедитесь, что эти учетные данные предоставлены вам безопасным и безопасным способом и что вы не случайно раскрываете эти учетные данные другим пользователям.
Управление обновлением данных
Вы можете управлять обновлением данных из внешнего источника данных, выполнив одно или несколько из следующих действий:
- Обновление при открытии В Microsoft Office Excel 2007 автор книги может создать книгу, которая автоматически обновляет внешние данные при открытии книги, выбрав «Обновить данные» при открытии флажка файла в диалоговом окне «Свойства подключения». (На вкладке «Данные» в группе «Подключения» щелкните «Подключения«, выберите подключение, щелкните «Свойства«, откройте вкладку «Использование» и выберите команду «Обновить данные» при открытии файла.) В этом случае службы Excel всегда обновляет данные перед отображением книги и создает новый сеанс. Если автор книги очищает данные обновления при открытии флажка файла, отображаются данные, кэшированные вместе с книгой. Это означает, что когда пользователь вручную обновляет данные, пользователь видит обновленные актуальные данные во время текущего сеанса, но данные не сохраняются в книге. Если вы хотите, чтобы при отображении книги в службы Excel всегда отображались актуальные данные, установите флажок «Обновить» при открытии файла с помощью Office Excel. Если вы используете файл подключения к данным Office (ODC), установите флажок «Всегда использовать файл подключения» в диалоговом окне «Свойства подключения» (на вкладке «Данные» в группе «Подключения» щелкните «Подключения«, выберите подключение, щелкните «Свойства», откройте вкладку «Определение» и выберите пункт «Всегда использовать файл подключения»). Параметры сайта расположения надежных файлов, короткое время ожидания сеанса и время существования внешнего кэша данных, также могут повлиять на операции обновления. Дополнительные сведения см. в разделе администратора или в справочной системе центра администрирования Microsoft Office SharePoint Server.
- Обновление вручную
Примечание: Вы можете вручную обновить только сводная таблица отчета.
- Выберите ячейку в сводная таблица отчета.
- На панели инструментов Веб-доступа Excel в меню « Обновить» щелкните » Обновить выбранное подключение».
- Любая интерактивная операция, которая вызывает повторный запрос к источнику данных OLAP, инициирует операцию обновления вручную.
- Если эта команда « Обновить» не отображается, автор веб-части очистит свойство «Обновить выбранное подключение» и » Обновить все подключения «. Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
- Всегда означает, что сообщение отображается с запросом с каждым интервалом.
- Дополнительно означает, что пользователь может продолжить периодическое обновление без отображения сообщения.
- Нвер означает, что Excel Web Access выполняет периодическое обновление без отображения сообщения или запроса.
Дополнительные сведения см. в разделе пользовательских свойств веб-части Excel Web Access.
Автоматическое обновление данных при открытии книги
Вы можете автоматически обновлять диапазон внешних данных при открытии книги. Кроме того, при сохранении рабочей книги можно не сохранять внешние данные, чтобы уменьшить размер файла.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Перейдите на вкладку Использование.
- В группе Обновление экрана установите флажок Обновить при открытии файла.
- Если требуется сохранить книгу с определением запроса, но без внешних данных, установите флажок Удалить данные из внешнего диапазона перед сохранением книги.
Регулярное автоматическое обновление данных

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Перейдите на вкладку Использование.
- Установите флажок Обновлять каждые, а затем введите число минут между обновлениями.
Запрос пароля при обновлении диапазона внешних данных
Сохраняемые пароли не зашифровываются, поэтому использовать их не рекомендуется. Если для подключения к источнику данных необходим пароль, вы можете потребовать, чтобы пользователи вводили пароль перед тем, как обновлять диапазон внешних данных. Приведенная ниже процедура не применяется к данным, полученным из текстового файла (.txt) или веб-запроса (IQY).
Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Например, надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
Очень важно запомнить пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
- Откройте вкладку Определение и снимите флажок Сохранить пароль.
Примечание: Excel будет запрашивать у вас пароль только при первом обновлении диапазона внешних данных в каждом сеансе работы в Excel. При следующем запуске приложения Excel оно вновь попросит ввести пароль, если вы откроете книгу, содержащую запрос, и попытаетесь выполнить обновление данных.
Выполнение запроса в фоновом режиме или в режиме ожидания
Очень большие наборы данных рекомендуется обновлять в фоновом режиме. При этом вы сможете продолжать работу в Excel, не дожидаясь завершения обновления в течение нескольких минут или более длительного времени.

- Выделите ячейку в диапазоне внешних данных.
- На вкладке Данные в группе Подключения нажмите кнопку Обновить все, а затем выберите пункт Свойства подключения.
Примечание: Невозможно запустить запрос OLAP в фоновом режиме.
Примечание: Невозможно запустить запрос для любого типа подключения, получающего данные для модели данных.
Отмена и проверка состояния обновления
Если книга подключена к большому источнику данных, ее обновление может занять много времени. Чтобы проверить или отменить обновление, вы можете сделать следующее:
- проверить состояние запроса. В строке состояния Excel появится сообщение о том, что запрос запущен. Дважды щелкните сообщение, чтобы проверить состояние запроса.
- прервать фоновое обновление. Чтобы остановить запрос, работающий в фоновом режиме, дважды щелкните сообщение о состоянии запроса в строке состояния и в появившемся диалоговом окне Состояние обновления внешних данных нажмите кнопку Прервать;
- прервать запрос. Чтобы прервать запрос, когда фоновое обновление отключено, нажмите клавишу ESC.