Сводная таблица в excel как сделать 2013

Сводные таблицы в Excel

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

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

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

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

  1. Диапазон (может находиться в другой книге);
  2. Таблица данных (указывается ее имя);
  3. Данные из внешнего источника, полученные по SQL-запросу из базы данных и т.п.

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

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

После нажатия кнопки «ОК» в окне создания сводной таблицы, создается пустая область для ее размещения, и отображается окно со списком всех полей.

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

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

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

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

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

Также в параметрах поля можно изменить заголовок и применить дополнительные вычисления на одноименной вкладке рассмотренного меню.

Вычисляемые поля сводной таблицы

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

Можно заметить, что результат по новому полю возвращает «странный» результат. Это происходит от того, что предварительно по значению столбца «курс» производится суммирование, а только потом полученный результат используется для вычисления. Если добавить в область названия строк дату, то расчет вернет требуемый результат:

Параметры сводной таблицы в Excel

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

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.
По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

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

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

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

  1. Имя сводной таблицы;
  2. Объединение и выравнивание подписей;
  3. Вывод значений для пустых ячеек;
  4. Автоматическое изменение ширины столбцов;
  5. Отображение общих итогов по строкам и столбцам;
  6. Сортировку;
  7. Печать;
  8. Обновление и др.

Теперь Вы умеете пользоваться сводными таблицами Excel. Полученные здесь знания позволят Вам далее самостоятельно экспериментировать с ними и повышать свой навык.

Сводная таблица в Excel. Как сделать?

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

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

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:

Она содержит данные:

  • Даты заказов;
  • Регион в котором расположен клиент;
  • Тип клиента;
  • Клиент;
  • Количество продаж;
  • Выручка;
  • Прибыль.

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

  • Какой объем выручки у региона Север за 2017 год?;
  • ТОП пять клиентов по выручке;
  • Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.

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

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

Для создания таблицы выполните следующие действия:

  • Выделите любую ячейку в таблице с данными;
  • Нажмите на вкладку “Вставка” => “Сводная таблица”:

  • Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
    • Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
  • Система по умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе “На существующий лист”.
Читать еще:  Как сделать битый файл excel?

После нажатия кнопки “ОК” таблица будет создана.

После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.

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

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

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

Ниже вы узнаете подробней об областях:

  • Кэш
  • Область “Значения”
  • Область “Строки”
  • Область “Столбцы”
  • Область “Фильтры”

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область “Значения”

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

На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.

Область “Строки”

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

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

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

Область “Фильтры”

Область “Фильтры” используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные “Тип клиента” – “Продуктовый магазин” и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

Сводные таблицы в Excel. Примеры

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

  • Какой объем выручки у региона Север за 2017 год?;
  • ТОП пять клиентов по выручке;
  • Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”. Как только вы представили каким образом вы видите итоговую таблицу – начинайте её создание.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

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

Пример 1. Какой объем выручки у региона Север?

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

  • создать сводную таблицу и поле “Регион” перенести в область “Строки”;
  • поле “Выручка” разместить в области “Значения”
  • задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

  • переместить поле “Клиент” в область “Строки”;
  • поле “Выручка” разместить в области “Значения”;
  • задать финансовый числовой формат ячейкам со значениями.

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

  • кликните правой кнопкой на любой из строчек с данными выручки;
  • перейдите в меню “Сортировка” => “Сортировка по убыванию”:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

  • поместим поле “Регион” в область “Строки”;
  • поместим поле “Клиент” в область “Строки” под поле “Регион”;
  • зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента “Регион” и “Клиент” в области “Строки” друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

  • поле “Выручка” разместим в область “Значения”.

В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.

Для сортировки данных выполните следующие шаги:

  • кликните правой кнопкой на любой из строчек с данными выручки;
  • перейдите в меню “Сортировка” => “Сортировка по убыванию”:

В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

Читать еще:  Как сделать частотный анализ в excel?

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.

Источник данных сводной таблицы Excel

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

1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.

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

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

В целом требований немного, но их следует знать.

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

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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

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

В этом курсе:

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

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

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

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

В поле Таблица или диапазон проверьте диапазон ячеек.

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

Нажмите кнопку ОК.

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

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

Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.

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

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

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

Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.

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

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

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

Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.

Рекомендуемые сводные таблицы

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

Щелкните ячейку в диапазоне исходных данных и таблицы.

Читать еще:  Как в excel сделать разделитель разрядов пробелом?

На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» />

Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.

Щелкните ячейку в диапазоне исходных данных и таблицы.

На вкладке Вставка нажмите кнопку Сводная таблица.

Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.

Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».

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

Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

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

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

Соответствующие поля в сводной таблице

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

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

Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.

Отображение значения как результата вычисления и как процента

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

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

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

Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.

Важно: Не рекомендуется создавать сводные таблицы и работать над ними в электронной таблице одновременно с другими пользователями.

Выделите таблицу или диапазон в электронной таблице.

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

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

Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

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

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

Соответствующие поля в сводной таблице

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

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

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.

диалоговое окно «Дополнительные вычисления»» />

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Ссылка на основную публикацию
Adblock
detector