Обновление данных в сводных таблицах Excel

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

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

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

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

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

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

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

Информация.

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

Рационально.

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

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

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

Каталог.

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

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

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

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

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

Вручную:

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

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

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

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

  1. Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
  2. Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».

Теперь сводный отчет при открытии будет обновляться программно.