Следующий диапазон данных не был обновлен excel

Обновлено: 05.07.2024

Я в Excel 2010 подключаюсь к нескольким,отдельно доступ к БД 2010 из Excel через подключения к данным сводной таблицы.

обновление всех моих соединений приводит к сбою последнего обновления. Порядок не имеет значения, я вручную обновился в разных порядках, та же ошибка.

однако, если я сохраню и закрою после обновления нескольких, а затем вернусь и обновлю последний, нет вообще проблема.

приводит меня к мысли, что я нажимаю какую-то крышку памяти, которая сбрасывается, когда я сохраняю и закрываю.

могу ли я воссоздать этот эффект через VBA без сохранения/закрытия? есть ли лучшее решение этой проблемы?

запрос не запущен, или не удалось открыть таблицу базы данных.
проблемы с получением данные.
не удалось обновить сводную таблицу, функцию куба или срез с помощью соединения.

Код

Строку Подключения

Попытки Решения

Отключить Фоновое Обновление - уже отключен
отключить автоматическое восстановление (для сохранения памяти)
очистить "отменить стек" (для сохранения память)
'DoEvents' для задержки выполнения кода до завершения каждого обновления, изменение:

решение!

Примечание, у меня есть несколько дополнительных соединений, которые я не хотел обновлять через этот код, и добавил некоторую дополнительную, простую логику, чтобы указать, какие соединения я хотел обновить. Этот код здесь работает, чтобы обновить каждое соединение в вашем книги:

Я не знаю специфики почему это работает, какая часть этого позволяет Excel преодолеть свое самоограничение. Я хотел бы услышать, если кто знает!

Это не полный ответ, а попытка помочь отладки, так что, надеюсь, мы сможем найти решение.

Я считаю, что вы можете решить эту проблему, отладив соединения. Попробуйте заменить код обновления выше (и замену на DoEvents)следующим подразделом. Во-первых, возможно, что отображение диалога между обновлениями исправит проблему (если проблема заключается в одновременном обновлении и т. д.). Во-вторых, каждый раз, когда он работает, тщательно проверьте, что ничего не имеет измененный. Пожалуйста, сообщайте о любых открытиях или информации. Если вы все еще получаете ошибки, шаг через код и доложить строку, которая вызывает ошибку.

дополнительные вопросы вы можете ответить, если вы все еще получаете ошибки:

был ли BackgroundQuery всегда ложным?
была ли заметная задержка между каждым набором диалоговых окон (указывающая, что Excel ждет завершения обновления) или все они появились сразу после последнего один?
какая строка кода вызывает начальную ошибку? Если вы обновите соединения в обратном порядке (раскомментировав строку "шаг -1"), вы получите ошибку при том же соединении?
когда вы говорите, что можете обновить соединения вручную, это через другой макрос или через Data > > Connections > > Refresh?
ошибки, если вы вручную выберите "RefreshAll"?

извините за все вопросы, но вы должны думать о все при отладке неприятных ошибок подключения, как это.

поэтому у меня была аналогичная ошибка, когда я пытался создать скрипт VBA для автоматического обновления книги excel в данный момент времени, и было несколько вещей, которые я сделал в своем скрипте VBA, чтобы заставить это работать. Один из них отключение фонового обновления. Это может быть ваша проблема, и вы можете легко отключить ее, перейдя в свойства подключения и отключив обновление фона.

вот что я сделал в VBA, когда я получал эту ошибку, хотя я скажу, что я не использовал его с БД MS access. У меня была одна книга excel, которую я использовал как "бегун", и она открывала другие книги одну за другой и обновляла их соединения. В основном у меня была переменная для path и extension и поместите имена каждой книги в массив и выполните цикл через массив.

Я объединил путь и расширение, чтобы дать мне полное имя файла, вы увидите это в цикле.

вот как выглядела моя петля :

получить имя соединения есть несколько способов, в том числе, просто пытаюсь понять, что это вручную. Для меня, потому что я хотел сделать это так, чтобы мне не нужно было вручную вводить каждое имя соединения, я использовал неотъемлемый шаблон, который я видел с именами соединений.

в моем случае это был baseNameOfWorkbook & " POS Report"

Я считаю, что вы можете получать ошибки из-за обновления фона. Поэтому, если вам не нужно делать это в VBA, я бы просто предложил переход к свойствам подключения и его отключение.

Дайте мне знать, если это сработает.

вы можете использовать VBA для вызова обновлений индивидуально через activeworkbook.объект подключения. См.этот столб переполнения стека для некоторых подсказок по этому методу. Более атомистическое приложение может позволить лучше понять и контролировать. Например, как только у вас есть все шаги на месте, вы можете попробовать вставить DoEvents до решить вопрос.

очистить системную память, вы всегда можете запустить что-то вроде этого:

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

пожалуйста, расценивайте мое предыдущее предложение, поскольку я думал о решении, которое помогло мне в доступе.

Вы можете изменить свойства диапазона внешних данных для управления данными, импортируемыми на сайт с помощью подключения к внешнему источнику данных, такому как база данных, веб-запрос или текстовый файл.

Дополнительные сведения о диапазонах внешних данных и их свойствах

Диапазон внешних данных — это определенное имя или имя таблицы, которое определяет расположение данных, которые были доставлены на таблицу. При подключении к внешним данным Excel автоматически создается диапазон внешних данных. Единственным исключением из этого является отчет, подключенный к источнику данных, так как в отчете не используется диапазон внешних данных. В Excel вы можете форматирование и размыкать диапазон внешних данных или использовать его в вычислениях, как и любые другие данные.

Свойства диапазона внешних данных

У диапазона внешних данных есть дополнительные свойства (их не следует путать с свойствами запроса или подключения), которые можно использовать для управления данными, например для сохранения форматирования ячеок и ширины столбцов. Эти свойства диапазона внешних данных можно изменить, нажав кнопку Свойства в группе Подключения на вкладке Данные. Затем внести изменения, вы можете сделать следующее:

Для диапазонов внешних данных, созданных с помощью Microsoft Query и мастера подключения данных, используйте диалоговое окно Свойства внешних данных.

Для диапазонов внешних данных, созданных из импортируемого текстового файла или веб-запроса, который извлекает HTML-данные, используйте диалоговое окно Свойства диапазона внешних данных.

Для диапазонов внешних данных, созданных из веб-запроса, который извлекает данные XML, используйте диалоговое окно Свойства карты XML.

Выберите нужный диапазон внешних данных.

На строка формул щелкните стрелку рядом с полем Имя ивыберите имя нужного диапазона внешних данных.

Выберите Формулы >Диспетчер имен.

В диалоговом окне Диспетчер имен щелкните имя диапазона внешних данных и нажмите кнопку Изменить. Можно также дважды щелкнуть имя.

В диалоговом окне Изменение имени введите новое имя ссылки в поле Имя.

Примечание: Кнопка Закрыть закрывает только диалоговое окно Диспетчер имен. Вам не нужно нажимать кнопку Закрыть, чтобы зафиксировать уже внесенные изменения.

Вы можете изменить запрос для диапазона внешних данных, созданного из Microsoft Query, импортируемого текстового файла, веб-запроса или мастера подключения к данным.

Изменение запроса для диапазона внешних данных, созданного из Microsoft Query

Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.

Выберите Свойства >данных.

В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения .

В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.

Дополнительные сведения о построении и редактировании запросов в Microsoft Query см. в справке Microsoft Query.

Изменение запроса для диапазона внешних данных, созданного из импортируемого текстового файла

Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.

Выберите > подключения & запросы> Connections, щелкните правой кнопкой мыши нужное подключение и выберите Свойства.

В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.

В диалоговом окне Импорт текстового файла нажмите кнопку Импорт.

Внести изменения в импортируемый текстовый файл в мастере импорта текста и нажмите кнопку Готово.

Дополнительные сведения об импорте текстовых файлов см. в документе Импорт и экспорт текстовых файлов.

Изменение основному запросу для диапазона внешних данных, созданного из веб-запроса

Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.

Выберите Свойства> данных.

В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.

В диалоговом окне Изменение веб-запроса внести изменения в веб-запрос и нажмите кнопку Готово.

Дополнительные сведения о создании и редактировании веб-запросов см. в Подключение на веб-страницу.

Изменение запроса для диапазона внешних данных, созданного с помощью мастера подключения к данным

Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.

Выберите Свойства> данных.

В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения .

В диалоговом окне Свойства подключения перейдите на вкладку Определение.

Выполните одно из указанных ниже действий.

В поле Тип команды выберите таблицу, а затем в текстовом поле Команда измените значение на имя соответствующей таблицы, представления или запроса.

В поле Тип команды выберите SQL или По умолчанию, а затем в текстовом поле Команда введите SQL команд.

Примечание: В зависимости от того, каким образом было определено подключение, поле Тип команды может быть недоступно (оно отображается затемнено).

Копирование диапазона внешних данных и его запроса

В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, который вы хотите скопировать.

Для Excel выберите имя диапазона и нажмите CTRL+A, чтобы выбрать заглавные таблицы.

Если вы хотите включить подписи столбцов или формулы, которые не являются частью внешнего диапазона данных, выберем ячейки, содержащие подписи столбцов или формулы, которые нужно скопировать. Щелкните стрелку рядом с полем Имя в панели формул и выберите имя диапазона внешних данных, который вы хотите скопировать.

Выберите Главная >Копировать.

Переключение на книгу, в которую нужно вклеить диапазон внешних данных.

Щелкните левую верхнюю ячейку область вставки.

Чтобы гарантировать, внешние данные данные не заменяют существующие, убедитесь в том, что в ячейке или справа от нее нет данных.

На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

Примечание: Если скопировать только часть диапазона внешних данных, запрос не будет скопирован, а скопированные данные не будут обновлены.

Укажите, как новые данные добавляются во внешний диапазон данных

Вы можете управлять обработкой меньшего или большего набора данных, который возвращается в Excel при обновлении данных.

Выберите Свойства> данных.

В диалоговом окне Свойства диапазона внешних данных в поле Если количество строк в диапазоне данных изменяется при обновлении, выберите одно из следующих вариантов:

Вставка ячеек для новых данных, удаление неиспользованых ячеек

При добавлении одной или более строк в источник данных ячейки непосредственно под диапазоном внешних данных перемещаются вниз, но ячейки справа от диапазона внешних данных не перемещаются.

При удалении одной или более строк в источнике данных ячейки непосредственно под диапазоном внешних данных перемещаются вверх, но ячейки справа от диапазона внешних данных не перемещаются.

Вставка строк для новых данных, очистка неиспользованых ячеек

При добавлении в источник данных одной или двух строк ячейки непосредственно под и справа от диапазона внешних данных перемещаются вниз.

При удалении одной или двух строк из источника данных ячейки непосредственно под и справа от диапазона внешних данных не перемещаются.

Переописывание существующих ячеек с помощью новых данных, очистка неиспользовавших ячеек

При добавлении одной или более строк в источник данных ячейки непосредственно под диапазоном внешних данных перезаписываются, но ячейки справа от диапазона внешних данных не перемещаются.

Выберите Свойства> данных.

В области Форматирование и макет данныхсделайте следующее:

Чтобы включить имена полей в качестве первой строки, выберите поле Включить имена полей.

Примечание: Этот квадратик доступен только для диапазона внешних данных, преобразованного из программы Microsoft Office 2003 или в диапазон внешних данных, созданный программным путем.

Чтобы добавить столбец с номерами строк, выберите включить номера строк.

Примечание: Этот флажок не доступен для импортируемого текстового файла, XML-файла или веб-запроса.

Чтобы сохранить форматирование ячейки, которое вы применяли, выберите сохранить форматирование ячеок.

Примечание: Для веб-запроса этот параметр автоматически очищается при выборе параметра Полное форматирование HTML в диалоговом окне Параметры. Для доступа к диалоговом окну Параметры вы можете присоединения к диалоговом окну Изменить запрос.

Чтобы сохранить за настраиваемую ширину столбцов, установите установите в поле Изменить ширину столбца.

Чтобы сохранить применяемую сортировку, фильтрацию и макет столбцов, выберите сохранить столбец для сортировки, фильтрации и макета.

Примечание: Этот поле не доступно для веб-запроса.

Чтобы сохранить применяемую фильтрацию столбцов, выберите сохранить фильтр столбцов.

Примечание: Этот флажок доступен только для веб-запроса, основанного на данных XML.

Копирование формул при расширении диапазона внешних данных

Примечание: Следующая процедура относится только к диапазону внешних данных, преобразуемого из программы Microsoft Office 2003, или к диапазону внешних данных, который создается программным путем. Все диапазоны внешних данных, созданные с помощью пользовательского интерфейса, создаются Excel таблицами. Эти таблицы автоматически расширяются при добавлении новых строк и заполняют вниз вычисляются столбцы.

Введите формулу в ячейку, смежную с первой строкой данных во внешнем диапазоне данных.

Примечание: Первая строка данных может быть первой или второй в диапазоне внешних данных в зависимости от того, содержит ли первая строка заглавные строки.

Вы выберите ячейку и дважды щелкните маркер заполнения формулу, чтобы скопировать формулу во все строки во внешнем диапазоне данных.

Выделите ячейку в диапазоне внешних данных.

Выберите Свойства> данных.

В диалоговом окне Свойства диапазона внешних данных выберите вариант Заполнить вниз формулы в столбцах, смежных с данными.

Примечание: Если диапазон внешних данных расширяется при обновить, Excel копирует только формулы, смежные с диапазоном внешних данных или внутри них.

При заморозке диапазона внешних данных сохраняются данные, но не их запрос, поэтому заблокированный диапазон внешних данных невозможно обновить.

Щелкните на нем таблицу Excel, из которой вы хотите удалить подключение к данным.

В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, из которого вы хотите удалить подключение к данным. Затем будет выбран диапазон данных.

На вкладке Инструменты в группе Данные внешней таблицы нажмите кнопку Оторужить связь. Диапазон данных остается без названия, но подключение удаляется.

Выберите Свойства> данных.

В окне Определениезапроса разоберите поле Определение запроса сохранить.

Примечание: При сохранение книги из нее удаляется ее запрос. Однако сохраненные файлы запросов к базе данных (DQY или ODC-файлы) не удаляются. Если вы сохранили запрос при его создания в мастере запросов или в Microsoft Query, файл запроса будет сохранен на вашем компьютере, и вы сможете использовать его повторно для извлечения внешних данных. Дополнительные сведения см. в справке Microsoft Query.

Примечание: Следующая процедура относится только к диапазону внешних данных, который создается с помощью мастера подключения данных или Microsoft Query (а не к импортируемму текстовом файлу или веб-запросу), к диапазону внешних данных, преобразованного из программы Microsoft Office 2003, или к внешнему диапазону данных, созданному программным путем. Все диапазоны внешних данных, созданные с помощью пользовательского интерфейса, создаются Excel таблицами.

Вставьте пустую строку над диапазоном внешних данных одним из следующих образом:

Вы выберите ячейку, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки щелкните стрелку рядом с кнопкой Вставить ивыберите Вставить строки на лист. Поскольку вы выбрали одну ячейку, над текущей строкой вставляется одна новая строка.

Щелкните селектор строки, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки нажмите кнопку Вставить. Поскольку вы выбрали одну строку целиком, над этой строкой будет вставлена одна новая строка.

Введите нужные подписи в ячейки пустой строки.

Выделите ячейку в диапазоне внешних данных.

Выберите Свойства> данных.

В диалоговом окне Свойства диапазона внешних данных в списке Форматирование и разметка данных сберитеполе Включить имена полей и нажмите кнопку ОК.

Чтобы удалить существующие имена полей и обновить диапазон внешних данных, щелкните ячейку в диапазоне внешних данных и выберите обновить данные .

Примечание: При извлечении данных из базы данных изменения имен столбцов в Microsoft Query сохраняются во внешнем диапазоне данных. Сведения об изменении имен столбцов в запросе см. в справке Microsoft Query.

Щелкните на нем нужный диапазон внешних данных.

В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, который вы хотите удалить.

Если диапазон внешних данных является Excel, нажмите CTRL+A, чтобы выбрать всю таблицу.

На этом шаге мы рассмотрим как обрабатываются данные, возвращаемые из MS Query и записанные в рабочий лист.

Данные, возвращаемые после выполнения запроса, записываются либо в рабочий лист, либо в сводную таблицу. Данные, сохраняемые в рабочем листе, записываются в специально поименованный диапазон, так называемый диапазон внешних данных ( Excel присваивает имя этому диапазону).

Установка свойств диапазона внешних данных

В диалоговом окне Свойства внешнего диапазона (рис. 1) можно корректировать различные свойства диапазона внешних данных.

Рис. 1. Диалогово окно Свойства внешнего диапазона

Чтобы вызвать это диалоговое окно, указатель ячейки должен находиться в пределах диапазона внешних данных. Доступ к диалоговому окну можно получить, воспользовавшись одним из трех методов:

Щелкните правой кнопкой мыши и из контекстного меню выберите команду Свойства диапазона данных .
Выберите команду Данные | Внешние данные | Свойства диапазона данных .
Щелкните на кнопке Свойства диапазона данных на панели инструментов Внешние данные (это окно появляется автоматически, когда Вы выполняете запрос).

В диалоговом окне Свойства внешнего диапазона имеются следующие опции:

Имя. Имя диапазона внешних данных, создаваемое Excel по умолчанию. Это имя можно изменить или оставить.
Определение запроса. Если флажок Сохранить определение запроса установлен, то определение запроса сохраняется вместе с диапазоном внешних данных. Если для базы данных требуется пароль, можно также задать пароль, и тогда не нужно будет его вводить при обновлении запроса.
Обновление экрана. Определяет, как и когда обновляются данные.
Формат и разметка данных. Определяет, как будет размещен диапазон внешних данных.

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

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

Щелкнуть правой кнопкой мыши и выберать из контекстного меню команду Обновить данные .
Выберать команду Данные | Обновить данные .
Щелкнуть на кнопке Обновить данные панели инструментов Внешние данные .

Excel запустит Query и использует первоначальный запрос для получения текущих данных из внешней базы данных.

Использование нескольких запросов

Отдельная рабочая книга, если это необходимо, может содержать много диапазонов внешних данных. Каждому диапазону присваивается уникальное имя, поэтому с каждым из них можно работать независимо от других. Excel автоматически отслеживает запрос, который используется для получения каждого из диапазонов внешних данных.

Копирование или перемещение внешних данных

После выполнения запроса иногда требуется скопировать или переместить диапазон внешних данных. При этом можно использовать обычные приемы копирования, вырезания и вставки. Однако необходимо убедиться, что копируется или вырезается целый диапазон внешних данных, иначе соответствующий запрос не будет скопирован, а скопированные данные не будут обновляться.

Если данные, возвращаемые по запросу, больше не нужны, их можно удалить. Для этого следует выделить весь диапазон внешних данных и выберать команду Правка | Удалить .

Если Вы нажмете клавишу Delete , то содержимое ячеек будет удалено, но соответствующий запрос останется. А это означает, что можно обновить запрос, и значения удаленных ячеек появятся снова.

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

Щелкнуть правой кнопкой мыши и выберать из контекстного меню команду Изменить запрос .
Выберать команду Данные | Внешние данные | Изменить запрос .

Excel запустит Query , после чего можно изменить первоначальный запрос. По окончании корректировки выберите команду Файл | Вернуть данные в Microsoft Excel . Excel вновь будет активизирован, выполнится модифицированный запрос, а диапазон внешних данных обновится.

На следующем шаге мы рассмотрим создание запроса в MS Query без использования Мастера запросов .

В Excel, когда вы удаляете или добавляете строки или столбцы в диапазон данных, относительная сводная таблица не обновляется одновременно. Теперь это руководство расскажет вам, как обновить сводную таблицу при изменении строк или столбцов таблицы данных.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Обновить диапазон сводной таблицы в Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Выполните следующие действия, чтобы обновить диапазон сводной таблицы.

1. После изменения диапазона данных щелкните соответствующую сводную таблицу и щелкните Опция (в Excel 2013 щелкните АНАЛИЗ )> Изменить источник данных. Смотрите скриншот:

2. Затем во всплывающем диалоговом окне выберите новый диапазон данных, который необходимо обновить. Смотрите скриншот:

3. Нажмите OK. Теперь сводная таблица обновлена.

Внимание: Только строки добавляются в нижнюю часть исходных данных таблицы или столбцы добавляются в самый правый угол, диапазон сводной таблицы обновляется при нажатии Option (или Analyze)> Change Data Source.

Большой диапазон данных в таблице Excel рационально представить в виде сводного отчета. Структура данного инструмента позволяет получить быстрый доступ к итогам, информации по определенному параметру. Рассмотрим создание и обновление сводных таблиц.

О целесообразности и возможности сводных таблиц

Оптимально формировать сводный отчет на основе исходной таблицы, если она отвечает следующим параметрам:

содержится несколько сотен строк;
пользователю нужно представлять одни и те же данные в разных разрезах, выбирать информацию по заданному условию, группировать, а встроенный фильтр и группировка справляются плохо.

Требования к исходной таблице:

у каждого столбца есть заголовок (первый вариант – неправильный; второй – правильный);
значения в одном столбце имеют одинаковый формат (число, дата, текст);
все ячейки в строках и столбцах заполнены значениями;
данные из одной ячейки нельзя разнести в разные столбцы (без объединения ячеек).

Нерациональная организация информации:

Нельзя будет вывести итоги, например, только по городу.

Лучше значения ввести следующим таким образом.

Как сделать сводную таблицу в Excel

Чтобы создать сводную таблицу в качестве исходного диапазона возьмем каталог учебной литературы:

Подразумевается, что данная таблица состоит из сотен строк. Создадим сводный отчет для выведения списка книг из конкретной категории или определенного года. Задача сформулирована – перейдем к реализации.

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

Сводная таблица сделана. С помощью нескольких кликов. Такой способ представления информации удобен для финансовых отчетов.

Как обновить сводную таблицу в Excel

От сводной таблицы больше пользы, если она динамическая. То есть при внесении новых данных в исходный диапазон поля отчета можно обновить. Как это сделать?

Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

Как настроить автоматическое обновление сводной таблицы в Excel:

Открыть лист со сводным отчетом. Щелкнуть в любом месте таблицы левой кнопкой мыши. Это нужно для того, чтобы активизировалась «Работа со сводными таблицами».
На вкладке «Параметры» находим группу «Сводная таблица». Нажимаем – открывается меню кнопки. Выбираем пункт «Параметры».
В открывшемся меню «Параметры сводной таблицы» нажать кнопку «Разметка и формат». Поставить галочки напротив следующих пунктов:

Читайте также: