Как редактировать сводную таблицу
Настройка стандартных параметров макета сводной таблицы
Если у вас уже есть скайп-система, вы можете импортировать эти параметры, в противном случае изменить их по отдельности. Изменение параметров по умолчанию для стебли влияет на новые стебли в любой книге. Изменения макета по умолчанию не повлияют на существующие складки.
Примечание: Эта функция доступна в Excel для Windows, если у вас Office 2019 или если у вас Microsoft 365 подписка. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.
В этом видео Дуг из команды Office коротко рассказывает о параметрах макета сводной таблицы по умолчанию:
Чтобы приступить к работе, выберите Файл > Параметры > Данные и нажмите кнопку Изменить макет по умолчанию.
«Параметры» > «Данные»» loading=»lazy»>
Параметры в окне Изменение макета по умолчанию:
Импорт макета: выделите ячейку в существующей сводной таблице и нажмите кнопку Импорт. Параметры сводной таблицы будут автоматически импортированы. Вы можете сбросить их, импортировать новые параметры или изменить отдельные настройки.
Промежуточные итоги: укажите, нужно ли показывать промежуточные итоги вверху или внизу каждой группы сводной таблицы (либо вообще не выводить их).
Общие итоги: позволяет включить или отключить общие итоги для строк и столбцов.
Макет отчета: выберите вариант Компактный, Структура или Табличный.
Пустые строки: позволяет автоматически добавлять пустую строку после каждого элемента.
Параметры сводной таблицы: открывает стандартное диалоговое окно параметров сводной таблицы.
Восстановить значения Excel по умолчанию: восстанавливает стандартные параметры сводной таблицы Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Изменение исходных данных сводной таблицы
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок либо другой внешний источник данных.
Щелкните Отчет сводной таблицы.
На вкладке «Анализ» в группе «Данные» нажмите кнопку «Изменить источник данных» и выберите «Изменить источник данных».
Отобразилось диалоговое окно «Изменение источника данных в pivotTable».
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок, щелкните «Выбрать таблицу или диапазон», а затем введите первую ячейку в текстовом поле «Таблица или диапазон» и нажмите кнопку «ОК».
Чтобы использовать другое подключение, сделайте следующее:
Выберите «Использовать внешний источник данных» инажмите кнопку «Выбрать подключение».
Отобразилось диалоговое окно «Существующие подключения».
В списке «Показать» в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (значение по умолчанию).
Выберите подключение в списке «Выберите подключение» и нажмите кнопку «Открыть».Что делать, если подключения нет в списке?
Примечание: При выборе подключения из категории «Подключения» в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных данных» и найдите нужный источник данных в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Щелкните Отчет сводной таблицы.
На вкладке «Параметры» в группе «Данные» нажмите кнопку «Изменить источник данных» и выберите «Изменить источник данных».
Отобразилось диалоговое окно «Изменение источника данных в pivotTable».
Выполните одно из указанных ниже действий.
Чтобы использовать другую таблицу или диапазон ячеок Excel, щелкните «Выбрать таблицу или диапазон», а затем введите первую ячейку в текстовом поле «Таблица или диапазон».
Вы также можете нажать кнопку «Свернуть 
«, чтобы временно скрыть диалоговое окно, выбрать ячейку в начале, а затем нажать кнопку «Развернуть 
«.
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных» и нажмите кнопку «Выбрать подключение».
Отобразилось диалоговое окно «Существующие подключения».
В списке «Показать» в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (значение по умолчанию).
Выберите подключение в списке «Выберите подключение» и нажмите кнопку «Открыть».
Примечание: При выборе подключения из категории «Подключения» в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных данных» и найдите нужный источник данных в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
В Excel в Интернете изменить исходные данные для #x0. Для этого необходимо использовать настольная версия Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Изменение стиля сводной таблицы
Если после создания вам не нравится, как выглядит создаемая вами pivotable, вы можете выбрать другой стиль. Например, если в вашей pivott имеется много данных, может быть удобно показать строки или столбцы с полосами, чтобы их было проще просматривать, или выделить важные данные, чтобы выделить их.
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
Нажмите кнопкуКонструктор и нажмите кнопку Дополнительные данные в коллекции Стили pivotTable, чтобы увидеть все доступные стили.
Выберите нужный стиль.
Если вам не нравится стиль, вы можете создать собственный. В нижней части коллекции нажмите кнопку Новый стиль pivottable Style (Новый стиль) и укайте имя для пользовательского стиля, а затем выберите нужные параметры.
Совет: Если вы хотите изменить форму и способ отображения полей, столбцов, строк, подытогов, пустых ячеек и строк, можно создать макет и формат для этой сметы.
Показывать строки с полосами для простоты сканирования
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
Нажмите кнопку Конструктор > с полосами (или С полосами).
Вы можете просматривать данные и работать с ними в с помощью стеблей, но инструменты, необходимые для изменения стиля, недоступны в Excel в Интернете. Для этого вам потребуется Excel настольная версия приложения. См. изменение стиля вашей стебли.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Финансы в Excel
Редактирование сводной таблицы
Сводные таблицы Excel является мощным средством анализа данных. При этом исходные данные могут храниться как в электронных таблицах, так и в базах данных. Одним из популярных замечаний к интерфейсу сводных таблиц, высказываемым пользователями Excel, является невозможность редактирования данных. Это замечание, с нашей точки зрения, некорректно, так как сводная таблица – это отчетная форма для анализа различных источников информации и разработка универсального интерфейса редактирования просто невозможна. Однако, в каждом конкретном случае, с помощью программных методов задача, в принципе, решаема. В примере к статье реализовано средство доступа к исходной информации сводной таблицы, основанной на простой таблице Excel.
Подключение макросов
Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.
Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.
Функциональность
Программный код перехватывает двойной клик в области данных сводной таблицы и отображает исходные данные в виде отфильтрованных строк в отдельном окне. После редактирования и возврата в окно сводной таблицы, вторичное окно исходных данных автоматически закрывается, а данные в отчете обновляются.
В примере исходные данные размещены на листе «Data», а сводная таблица на листе «Отчет». В сводной таблице имеются 2 поля данных: «Объем», «Выручка» и вычисляемое поле «Средняя цена». Двойной клик доступен на обычных полях, при выборе вычисляемого поля никаких действий не производится, так как в этом случае, очевидно, нечего редактировать.
В новом окне редактирования выделяется ячейки столбца по выбранным в сводной таблице данным:
Обратите внимание, что в двух окнах находится одна и та же рабочая книга. Это видно по ярлыкам листов. Там же при первом открытии примера вы заметите появление нового листа «Filter». Этот лист используется для организации фильтра на листе исходных данных.
После редактирования, вернитесь на начальное окно со сводной таблицей. При этом служебное окно с исходными данными должно закрыться автоматически:
Программный код примера работает с любыми сводными таблицами, основанными на простых данных Excel. Для практического применения на реальном примере сводной таблицы несложно организовать редактирование в диалоговом окне вместо клонирования окна Excel.
Общий принцип поиска ключевой информации сводной таблицы может помочь создать более сложный интерфейс для выборки и редактирования информации из базы данных.
Алгоритм работы
Программный код
Небольшие комментарии к наиболее интересным частям программного кода VBA.
События рабочей книги (ThisWorkbook):
FlagCheckActiveWindow – публичное свойство для включения/отключения проверки активизации окна.
Работа со сводными таблицами в Excel на примерах
Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).
Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».
А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.
Как сделать сводную таблицу из нескольких файлов
Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).
Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.
Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.
Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:
Первая таблица – приход товара. Вторая – количество проданных единиц в разных магазинах. Нам нужно свести эти две таблицы в один отчет, чтобы проиллюстрировать остатки, продажи по магазинам, выручку и т.п.
Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.
Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.
Покажем, к примеру, количество проданного товара.
Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.
Детализация информации в сводных таблицах
Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:
Как обновить данные в сводной таблице Excel?
Если мы изменим какой-либо параметр в исходной таблице либо добавим новую запись, в сводном отчете эта информация не отобразится. Такое положение вещей нас не устраивает.
Курсор должен стоять в любой ячейке сводного отчета.
Правая кнопка мыши – обновить.
Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:
Изменение структуры отчета
Добавим в сводную таблицу новые поля:
Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.
После изменения диапазона в сводке появилось поле «Продажи».
Как добавить в сводную таблицу вычисляемое поле?
Иногда пользователю недостаточно данных, содержащихся в сводной таблице. Менять исходную информацию не имеет смысла. В таких ситуациях лучше добавить вычисляемое (пользовательское) поле.
Это виртуальный столбец, создаваемый в результате вычислений. В нем могут отображаться средние значения, проценты, расхождения. То есть результаты различных формул. Данные вычисляемого поля взаимодействуют с данными сводной таблицы.
Инструкция по добавлению пользовательского поля:
Группировка данных в сводном отчете
Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.
Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».
В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».
Получаем суммы заказов по годам.
По такой же схеме можно группировать данные в сводной таблице по другим параметрам.