Добавление столбцов в сводную таблицу в excel
Сводные таблицы Excel - очень удобный и гибкий инструмент, позволяющий быстро сгруппировать данные нужным образом. И вот пять настроек, которые позволят вам использовать этот инструмент максимально эффективно.
Макет сводной таблицы
Часто приходится создавать однотипные сводные таблицы и затем для каждой из них настраивать внешний вид, например, изменять макет отчета, добавлять пустые и итоговые строки.
Получившийся макет можно установить в качестве настройки по умолчанию и он будет использоваться при создании новых сводных таблиц.
Через меню Файл заходим в Параметры Excel, раздел Данные и нажимаем на Изменить макет по умолчанию .
Здесь можно выбрать нужные настройки с помощью выпадающих меню и чек-боксов (1, см.рис.ниже), либо произвести импорт настроек (3), указав на листе (2) подходящую сводную таблицу.
После применения изменений все новые сводные таблицы будут использовать этот макет.
Объединение ячеек
Если сводная таблица используется в качестве готового отчета, то возникает желание привести ее вид к более привычному, например, объединить ячейки некоторых полей.
Стандартными средствами Excel сделать это не получится, но через контекстное меню обратимся к параметрам сводной таблицы.
Выбираем пункт, который позволит объединить и выровнять по центру ячейки с подписями полей. Останется лишь задать для них подходящее форматирование - шрифт, его размер, начертание и т.д.
Изменение ширины столбцов
По умолчанию сводная таблица подстраивается под данные, которые в ней выводятся, поэтому при обновлении сводной таблицы может измениться и ширина столбцов, что не всегда удобно.
Например, такое может случиться при использовании фильтров. Так как сам фильтр находится над сводной таблицей, то его значение будет определять ширину ячейки фильтра, а значит и ширину всего столбца листа, в том числе и сводной таблицы.
Кроме этого, и текст в заголовках таблицы определят ширину соответствующих столбцов, а значит каждый раз поле обновления таблицы будут «слетать» и произведенные вами настройки.
Зафиксировать ширину столбцов можно через параметры сводной таблицы. Здесь на вкладке Макет и формат снимаем флажок с параметра Автоматически изменять ширину столбцов при обновлении .
После применения настроек ширина столбцов при обновлении сводной таблицы изменяться не будет.
Автообновление
Сводную таблицу можно построить на основе данных из внешнего источника, например, из другого документа.
Если данные в источнике изменятся, то сводную таблицу нужно будет обновлять вручную. Но можно облегчить задачу, включив в параметрах сводной обновление при открытии файла.
Теперь данные в сводной таблице будет автоматически обновляться каждый раз при открытии файла, в котором она содержится.
Фильтры
Ну и в заключение расскажу о решении проблемы, с которой можно столкнуться при использовании фильтров.
Проблема заключается в том, что после обновления сводной таблицы в фильтре остаются значения, которые уже были удалены из источника данных.
Соавтор(ы): Jack Lloyd. Джек Ллойд — автор и редактор, пишущий о технологиях для wikiHow. Имеет более двух лет опыта в написании и редактировании статей на технические темы. Энтузиаст всего связанного с технологиями и учитель английского языка.
В этой статье мы расскажем вам, как добавить данные в существующую сводную таблицу Excel. Это можно сделать на компьютерах под управлением Windows и Mac OS X.
Откройте сводную таблицу Excel. Дважды щелкните по файлу Excel со сводной таблицей, чтобы открыть ее.
- Например, если данные находятся в ячейках с A1 по E10, введите дополнительные данные в столбце «F» или в строке «11».
- Если вы просто хотите изменить существующие данные, сделайте это.
- На компьютере Mac перейдите на вкладку «Анализ сводной таблицы».
Нажмите Изменить источник данных . Эта опция находится в разделе «Данные» панели инструментов «Анализ». Раскроется меню.
Выберите данные. Удерживайте кнопку мыши и перетащите указатель из верхней левой ячейки в нижнюю левую ячейку с данными, включая добавленные вами данные в столбце (столбцах) или в строке (строках).
- Если вы добавили новый столбец в сводную таблицу, отметьте его в правой части окна Excel, чтобы отобразить его.
- Сохраните сводную таблицу, прежде чем вносить в нее изменения, чтобы вернуться к сохраненной версии, если что-то пойдет не так.
Дополнительные статьи
Об этой статье
В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.
Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.
Данная статья расскажет вам о том, как с помощью инструмента создания сводных таблиц можно создать и добавить новый столбец в сводную таблицу Microsoft Excel. В столбец сводной таблицы можно преобразовать уже существующее поле или колонку либо создать совершенно новый расчетный столбец с необходимой вам формулой.
- Если вы еще не создали сводную таблицу, создайте новый или откройте существующий файл Excel и подготовьте сводную таблицу, прежде чем двигаться дальше.
Кликнете по любой ячейке в сводной таблице. Это позволит вам выделить саму сводную таблицу и автоматически открыть раздел меню “Работа со сводными таблицами”, в который входят вкладки “Анализ” и “Конструктор”.
- В некоторых версиях Excel вкладка называется “Анализ”, а в других — “Параметры”; в обоих случаях эти вкладки располагаются в разделе меню “Работа со сводными таблицами”.
Нажмите на кнопку Список полей в ленте меню. Данная кнопка располагается на вкладке меню “Анализ” в группе кнопок “Показать”. После нажатия на эту кнопку справа откроется окно “Поля сводной таблицы”, в котором будут отображены списки полей, строк, столбцов и значений выбранной сводной таблицы.
- Заметьте, что обычно по умолчанию нечисловые поля добавляются в строки, а числовые — в столбцы.
- Чтобы удалить ненужный столбец из сводной таблицы, можно в любое время снять галочку рядом с наименованием соответствующего поля.
Перетащите любое поле, строку или значение в область столбцов. Это позволит автоматически преобразовать выбранную категорию данных в список столбцов и перестроить сводную таблицу, включив в нее новый столбец.
wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали авторы-волонтеры.
Бывают случаи, когда вам нужно добавить чуть больше информации в сводную таблицу по сравнению с тем, на что она рассчитывалась, но при этом нет смысла изменять исходные данные, включая туда эту самую информацию. В таких редких случаях может быть полезным добавить в сводную таблицу пользовательские вычисляемые поля. Такие пользовательские поля можно добавить, например, для отображения средних значений, процентов, расхождения или даже минимального или максимального значения поля. Читайте инструкции по добавлению пользовательских полей в сводную таблицу, чтобы вы могли получить нужную вам информацию, не прилагая особых усилий.
Определите, какое пользовательское поле вам нужно, и на какие другие поля оно будет ссылаться для получения желаемого результата.
Отобразите меню "PivotTable Tools" ("Работа со сводными таблицами"), просто кликнув в любом месте сводной таблицы.
Выберите вкладку "Options" ("Параметры") и затем выберите пункт "Calculated Field" ("Вычисляемое поле") в меню "Formulas" ("Формулы").
- Вычисляемые поля в формулах сводных таблиц не поддерживают диапазоны. Поэтому в формулах вместо диапазона вам следует использовать имя соответствующей колонки. Щелкните внутри поля для ввода текста "Formula" ("Формула") и из предлагаемого списка выберите поле, используемое в расчете.
- Щелкните "Insert Field" ("Добавить поле"), чтобы вставить правильное имя колонки в свою формулу. Допишите формулу, добавив нужные вычисления.
- Например, вы хотите вычислить 6-процентный налог на продажи в сводной таблице, в которой показываются продажи продукта в различных регионах. Имя колонки "Регион" находится в разделе "Columns"("Колонки"), название "Продажи" находится в разделе "Values"("Значения"), а название "Продукт" находится в разделе "Rows"("Строки").
После создания сводной таблицы становится доступным список полей. Вы можете изменять структуру сводной таблицы путем добавления и упорядочения ее полей. Если необходимо отсортировать или отфильтровать содержимое столбцов в сводной таблице, см. разделы Сортировка данных в сводной таблице и Фильтрация данных в сводной таблице.
Если щелкнуть в любом месте сводной таблицы, должен появиться список полей. Если после щелчка внутри сводной таблицы список полей не отображается, откройте его, щелкнув в любом месте сводной таблицы. Затем на ленте в разделе Работа со сводными таблицами щелкните Анализ> Список полей.
В этом списке есть раздел полей, в котором выбираются поля для сводной таблицы, и раздел областей (в нижней части), в котором поля можно упорядочить нужным образом.
Совет: Чтобы изменить способ отображения разделов в списке полей, нажмите кнопку Сервис и выберите подходящий макет.
Добавление и изменение расположения полей в списке
С помощью раздела полей в списке можно добавлять поля в сводную таблицу, устанавливая флажки рядом с именами полей, которые нужно разместить в стандартной области списка полей.
Примечание: Как правило, нечисловые поля добавляются в область строк, числовые — в область значений, а иерархии даты и времени OLAP — в область столбцов.
С помощью раздела областей (в нижней части) в списке полей можно менять порядок полей, перетаскивая их между четырьмя областями.
Поля из различных областей отображаются в сводной таблице описанным ниже образом.
Поля из области фильтров отображаются как фильтры отчетов верхнего уровня над сводной таблицей, как показано ниже.
Поля из области столбцов отображаются как названия столбцов в верхней части сводной таблицы, как показано ниже.
В зависимости от иерархических отношений между полями столбцы более низкого уровня могут быть вложены в столбцы более высокого.
Поля из области строк отображаются как названия строк в левой части сводной таблицы, как показано ниже.
В зависимости от иерархических отношений между полями строки более низкого уровня могут быть вложены в строки более высокого.
Поля из области значений отображаются в сводной таблице как просуммированные числовые поля, как показано ниже.
Если в области больше одного поля, их порядок можно изменить перетаскиванием в нужное положение. Чтобы удалить поле из сводной таблицы, перетащите его за пределы раздела областей.
Дополнительные сведения о сводных таблицах
Если в книге, открытой в Excel в Интернете, есть Excel в Интернете, вы можете добавлять, удалять и упорядо здесь поля с помощью списка полей. Он появляется при первом щелчке в любом месте сводной таблицы. Если список полей не отображается, попробуйте щелкнуть правой кнопкой мыши в любом месте сводной таблицы и выбрать команду Показать список полей.
В этом списке есть раздел полей, в котором вы можете выбрать поля для отображения в сводной таблице, и раздел областей, в котором поля можно упорядочить нужным образом, перетаскивая их из одной области в другую.
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля, и оно появится в одной из областей в разделе областей списка полей. Обычно происходит следующее:
нечисловые поля добавляются в область Строки;
числовые поля добавляются в область Значения;
иерархии дат и времени OLAP добавляются в область Столбцы.
Чтобы удалить поле из сводной таблицы, просто снимите флажок рядом с ним.
С помощью раздела областей в списке полей можно менять порядок полей, перетаскивая их между четырьмя областями.
Поля из различных областей отображаются в сводной таблице описанным ниже образом.
Поля из области фильтров отображаются как фильтры отчетов верхнего уровня над сводной таблицей.
Поля из области столбцов отображаются как названия столбцов в верхней части сводной таблицы.
В зависимости от иерархических отношений между полями столбцы более низкого уровня могут быть вложены в столбцы более высокого.
Поля из области строк отображаются как названия строк в левой части сводной таблицы.
В зависимости от иерархических отношений между полями строки более низкого уровня могут быть вложены в строки более высокого.
Поля из области значений отображаются в сводной таблице как просуммированные числовые поля.
Если в области больше одного поля, их порядок можно изменить перетаскиванием. Чтобы удалить поле, перетащите его за пределы раздела областей.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: