Как изменить параметры сводной таблицы в excel

Настройка стандартных параметров макета сводной таблицы

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

Примечание: Эта функция доступна в Excel для Windows, если у вас Office 2019 или у вас есть подписка на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

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

Чтобы приступить к работе, выберите Файл > Параметры > Данные и нажмите кнопку Изменить макет по умолчанию.

Параметры в окне Изменение макета по умолчанию:

• Импорт макета: выделите ячейку в существующей сводной таблице и нажмите кнопку Импорт. Параметры сводной таблицы будут автоматически импортированы. Вы можете сбросить их, импортировать новые параметры или изменить отдельные настройки.
• Промежуточные итоги: укажите, нужно ли показывать промежуточные итоги вверху или внизу каждой группы сводной таблицы (либо вообще не выводить их).
• Общие итоги: позволяет включить или отключить общие итоги для строк и столбцов.
• Макет отчета: выберите вариант Компактный, Структура или Табличный.
• Пустые строки: позволяет автоматически добавлять пустую строку после каждого элемента.
• Параметры сводной таблицы: открывает стандартное диалоговое окно параметров сводной таблицы.
• Восстановить значения Excel по умолчанию: восстанавливает стандартные параметры сводной таблицы Excel.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали авторы-волонтеры.

Количество просмотров этой статьи: 38 128.

В этой статье:

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

• Вы можете использовать иконку на рабочем столе, в Программах, в меню Пуск или на Панели быстрого запуска.

Откройте файл, содержащий сводную таблицу и данные.

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

Выберите лист книги, содержащий сводную таблицу, нажав на соответствующую вкладку.

• В Excel 2007 и 2010, вы увидите инструменты сводной таблицы в появившемся меню, выделенном красным цветом, над вкладками Параметры и Дизайн на ленте.
• В Excel 2003, выберите «Сводная таблица и отчеты сводной диаграммы » из меню Данные.

• В Excel 2007 и 2010, выберите » Изменение источника данных» из опции группы данных.
• В Excel 2003, Запустите мастер, щелкните внутри сводной таблицы правой кнопкой мыши и выберите «Мастер» во всплывающем меню. Нажмите на кнопку «Далее», пока не увидите экран с источником ​диапазона данных.
• В любой версии Microsoft Excel, выделив источник данных нажмите и перетащите рамку, чтобы выделить новый диапазон для ваших данных.
• Вы можете также включить больше строк и столбцов в диапазон.

• Эта кнопка может иметь красный треугольник с восклицательным знаком, картинку в виде зеленых «стрелок переработки» или просто слово «Обновить», в зависимости от версии и степени персонализации Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Сводные таблицы в Excel — как создать и работать с ними

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

В работе Excel полезен для многих специалистов. Более комплексно программу изучают на курсах для аналитиков данных и бухгалтеров.

Получите промокод 10 000 ₽ на обучение и курсы стоимостью 20 000 ₽ в подарок!

Нажимая на кнопку «Получить», я соглашаюсь на обработку моих персональных данных.

Спасибо! Мы получили вашу заявку. Скоро с вами свяжемся.

Содержание

• Для чего нужны сводные таблицы
• Создание сводной таблицы
• Настройка параметров и фильтров
• Фильтры
• Дополнительное вычисление
• Обновление данных
• Итоги

Для чего нужны сводные таблицы

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

Удобно и то, что сводную таблицу можно редактировать под свои запросы. Например, добавлять фильтр, менять расположение ячеек, добавлять новые и вводить нужные категории. Ещё один плюс сводной таблицы в быстроте её создания — готовый отчёт легко сделать за 5-10 минут. Допустим, вы работаете в агентстве недвижимости. Каждый сотрудник, осуществляющий сделки, вносил информацию о продажах в таблицу. В ней есть все необходимые вводные — площадь, количество комнат, район, стоимость, инициалы агента и другие характеристики. В конце квартала необходимо собрать данные и значения о всех продажах, чтобы проанализировать их. В статье расскажем, как составить для этого сводную таблицу в нужном виде для этого примера.

Создание сводной таблицы

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

После этого в Microsoft Excel в меню нажмите на вкладку «Вставка» и выберите кнопку «Сводная таблица». Перед вами появится окно, где можно выбрать подходящий диапазон для экспорта, и тип листа, куда будут перенесены все данные для создания сводной таблицы. Нам надо выделить всю таблицу и выбрать «новый лист», а затем нажать «ок» в окне.

Теперь работа над сводной таблицей будет идти с новым листом отчёта. Вы можете сразу изменить его название, чтобы не запутаться в таблицах и границах области. Сводная таблица будет располагаться слева, а с правой стороны вы увидите область под названием «Поля сводной таблицы» (в зависимости от версии Excel поле может находиться как во вкладке на правой стороне, так на левой). Она нужна для настройки параметров и функций сводной таблицы.

Настройка параметров и фильтров

«Поля сводной таблицы» состоят из различных составных частей будущей сводной таблицы. Они объединены в один блок и находятся наверху. Вы можете увидеть названия столбцов — «Квадратные метры», «Количество комнат», «Жилой комплекс», «Район», «Цена», «Дата продажи», «ФИО агента».

Чуть правее есть параметры в меню, обладающие важным функционалом:

• Значения — для выбора необходимых полей и произведения вычислений в сводной таблице. По стандарту данные суммируются, но есть и другие варианты. Можно умножить, выбрать среднее, стандартное или несмещённое отклонение, максимум или минимум. Результат зависит и от формата данных в сводной таблице. Для расчёта общей стоимости подойдёт операция сложение, при работе с текстовым форматом можно просмотреть количество соответствующих заданному параметру ячеек.
• Строки и столбцы — нужны для отражения расположения полей и областей. При выборе строк поля будут располагаться соответствующим образом, если выбрать столбец, то поля будут в виде него.
• Фильтры — благодаря им происходит фильтрация данных в сводной таблице. Фильтры находятся отдельно от общей таблицы, поэтому можно выбрать, какие данные нужно отразить обязательно, а какие стоит убрать. Так легко увидеть, в каких жилых комплексах чаще всего продаются квартиры, или понять, на какую стоимость агенты продали недвижимость в апреле или мае.

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

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

• Excel для анализа данных — Skypro
• Excel. Базовый уровень — Бруноям
• Excel для бухгалтера уровень PRO — Высшая школа Главбух

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

Перед нами появилась уже готовая сводная таблица с необходимыми категориями. Мы можем посмотреть, какие именно квартиры продал каждый сотрудник, какая площадь у них была и сколько они стоили. Для менеджера формируется итоговая заработанная за квартал сумма. Вы можете добавить в сводную таблицу те поля, которые нужны для отчётности. Например, посмотреть, в каких ЖК и местах больше всего новых продаж. Или оценить стоимость проданных жилых помещений по районам, где располагаются многоквартирные дома.

Фильтры

Для фильтрации нужно использование поля «Фильтры», описанное выше. В него нужно перетянуть в сводную таблицу все оставшиеся поля: «Жилой комплекс», «Район», «Дата продажи». Фильтрация будет отображаться над основной сводной таблицей. Мы можем попробовать отфильтровать данные по пункту «Жилой комплекс». Для этого достаточно выбрать подходящий вариант ЖК в блоке из фильтров и отменить значение «Выделить все». После закрытия окна в сводной таблице будут отображаться только те квартиры, которые продали в этом ЖК за весь отчётный период. Фильтры, как и другие параметры, удобны тем, что их можно настраивать под разные запросы и списки. Вы можете создать их все или выбрать для сводной таблицы тот, что нужен, как в нашем примере. О применении Excel для решения рабочих задач, оптимизации трудозатрат, повышения качества и эффективности работы подчиненных подробно рассказывают на курсе «Excel для карьеры и поиска работы».

Дополнительное вычисление

Часто в сфере торговли нужно понимать, какой процент продаж у каждого работника. В сводной таблице мы видим сумму, полученную за все сделки. Можно посчитать процент с помощью калькулятора или формул, но это довольно долго. Давайте используем для этого сводную таблицу.

Нажимаем на любую ячейку с ценой товара в сводной таблице. Щёлкните левой кнопкой мыши на «Дополнительные вычисления» и «% от общей суммы». Окно с разными типами значений закроется, а вместо цен в рублях будут проценты — как он продажи каждой квартиры, так и общие. Убирать полученные данные быстро можно при помощи знака «–», который находится рядом с ФИО работника. В дополнительных вычислениях сводной таблицы можно рассчитать следующий процент от суммы по столбцу или строке.

Обновление данных

Обновить данные в сводной таблице нужно в разных ситуациях. Допустим, в исходную таблицу добавили новые продажи с указанной датой. В сводную они не добавятся автоматически в нужном виде, ведь мы установили определённый диапазон для переноса. Для начала надо изменить эти параметры. В листе сводной таблицы в верхней части найдите «Анализ сводной таблицы» и нажмите мышью на кнопку «Изменить источник данных». Excel просто автоматически переключит вас на стартовую таблицу, где нужно будет создать и настроить новый диапазон. Добавьте в него новые строки. Теперь к сотруднику в сводной таблице добавятся эти продажи.

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

Итоги

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

Анна Уженкова

Копирайтер. Увлекается литературой и любит разбираться в сложных вещах. В работе ценит сам процесс — тексты, это не просто тексты, а возможность на что-то повлиять. Среди тем, которые нравятся, — образование, психология, IT.