Мастер сводных таблиц в excel 2010 как сделать

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

В этом курсе:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать таблицу ганта в excel?

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

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

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

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

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

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

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

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

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

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

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

Здравствуй уважаемый, читатель!

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

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

Возникает закономерный вопрос, где же применение сводной таблицы даст наибольший эффект:

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

Хотя я может быть я и не затронул еще какие-то варианты использования, но эти я считаю основными, а остальные — это уже походные от них.

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

  1. Каждый без исключения столбец обязан иметь собственный заголовок шапки;
  2. Все строки и столбики вы обязаны заполнить, пробелы должны отсутствовать.
  3. Для всех столбцов данных, должены быть определенные форматы ячеек, для тех данных, которые должны в них хранятся (пример, для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.)
  4. Значения в этих ячейках должны быть “единоличным”, это значит такими которые не делятся (к примеру, “Договор №23 от 03.09.2016 года” должен быть записан в 3 разных столбцах “Документ”, “Номер” и “Дата”, это позволит создавать гибкую и удобную систему). Также это возможно при помощи функции СЦЕПИТЬ.
  5. Если вы ведете расходно-доходную табличку в которой кроме суммирования еще есть надобность отнимания, то и в базу первоначальных данных вводите данные которые уже изначально со знаком “-” и тогда в свёрнутом виде вы получите нужный вам результат;
  6. Сама конструкция вашей сводной таблицы обязана иметь оптимальный вид.

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

Как создается сводная таблица в Excel

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

На панели управления выбираем вкладку «Вставка» и получаем на выбор 2 варианта создания вашей сводной таблицы:

  1. Рекомендуемые сводные таблицы (этот пункт рекомендуется использовать начинающим, но не бойтесь, это ненадолго, уловите суть создания, попрактикуетесь и всё, будете работать по второму пункту).
  2. Сводная таблица (используется при ручной настройке таблицы в основном используется опытными пользователями)

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

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

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

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

2. Сводная таблица (мастер сводных таблиц)

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

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

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

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

Ну вот форма то та, конечно, но вот результат не тот, а именно поле «Вес, кг» собирает по критерию — количество значений, а нам надо суммировать, а значит подводим курсор мыши к области значений «ЗНАЧЕНИЕ» и на указаном поле «Количество по полю Вес, кг», нажимаем левую кнопку мыши вызывая контекстное меню. Нам нужно выбрать последний пункт «Параметр полей значений».

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

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

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

Ну что же сводная таблица с выборкой фруктов у нас сделана. Но что же делать если нам нужно и интересно знать, а как же всё-таки происходит движение по странам. Да и любому будет интересно получить данные из сводной таблицы под разными углами, а поскольку мы уже отформатировали таблицу и всё сделали для идеальной работы. Мы просто копируем нашу табличку и в поле необходимой области «СТРОКИ» меняем вычисляемые значения местами. Указываем первым вычисляемым значением «Страна», вот и всё с 1 исходной таблицы данных мы получили 2 сводные таблицы нужных нам данных.

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

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

Читать еще:  Как сделать таблицу учета рабочего времени в excel?

Но не стоит забывать что данные в нашей таблице самостоятельно не умеют обновляться, а это значит то что при изменении в исходных данных (для примера я просто убрал данные 2 строк) у нас изменился итог, а значится надо сообщить об этом нашей сводной таблице:

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

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

Пример можно взять здесь.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Золото убило больше душ, чем железо – тел.
В. Скотт

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

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

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

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

Вариант 1: Обычная сводная таблица

Мы будем рассматривать процесс создания на примере Microsoft Excel 2010, однако алгоритм применим и для других современных версий этого приложения.

  1. За основу возьмем таблицу выплат заработной платы работникам предприятия. В ней указаны имена работников, пол, категория, дата и сумма выплаты. То есть каждому эпизоду выплаты отдельному работнику соответствует отдельная строчка. Нам предстоит сгруппировать хаотично расположенные данные в этой таблице в одну сводную таблицу, при этом сведения будут браться только за третий квартал 2016 года. Посмотрим, как это сделать на конкретном примере.
  2. Прежде всего преобразуем исходную таблицу в динамическую. Это нужно для того, чтобы при добавлении строк и других данных они автоматически подтягивались в сводную таблицу. Наводим курсор на любую ячейку, затем в расположенном на ленте блоке «Стили» кликаем по кнопке «Форматировать как таблицу» и выбираем любой понравившийся стиль таблицы.

Открывается диалоговое окно, которое нам предлагает указать координаты расположения таблицы. Впрочем, по умолчанию координаты, которые предлагает программа, и так охватывают всю таблицу. Так что нам остается только согласиться и нажать на «OK». Но пользователи должны знать, что при желании они тут могут изменить эти параметры.

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

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

После этого на новом листе откроется форма создания сводной таблицы.

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

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

Вариант 2: Мастер сводных таблиц

Создать сводную таблицу можно, применив инструмент «Мастер сводных таблиц», но для этого сразу нужно вывести его на «Панель быстрого доступа».

    Переходим в пункт меню «Файл» и кликаем на «Параметры».

Заходим в раздел «Панель быстрого доступа» и выбираем команды из команд на ленте. В списке элементов ищем «Мастер сводных таблиц и диаграмм». Выделяем его, жмем на кнопку «Добавить», а потом «OK».

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

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

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

Затем «Мастер сводных таблиц» предлагает выбрать место, где будет размещаться новый объект: на этом же листе или на новом. Делаем выбор и подтверждаем его кнопкой «Готово».

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

  • Все дальнейшие действия выполняются по тому же алгоритму, который был описан выше (см. Вариант 1).
  • Настройка сводной таблицы

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

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

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

    Сводная таблица приобрела такой вид.

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

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

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

    Можно также отобразить числовые значения таблицы в виде гистограммы. Для этого выделяем ячейку с числовым значением, заходим на вкладку «Главная», жмем «Условное форматирование», выбираем пункт «Гистограммы» и указываем понравившийся вид.

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

    В итоге наша сводная таблица стала выглядеть более презентабельно.

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

    Автор: Леонид Радкевич · Опубликовано 30.01.2013 · Обновлено 06.12.2016

    Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).

    Сводная таблица в Excel 2010 используется для:

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

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

    Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

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

    2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

    Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажмите на стрелку в нижнем правом углу значка «Сводная таблица» и выберите пункт «Сводная диаграмма».

    3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область.

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

    4. Далее нужно будет указать, где размещать отчет сводной таблицы. Удобнее всего это делать на новом листе.

    5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.

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

    Читать еще:  Как сделать нижний индекс в excel?

    Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.

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

    Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

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

    Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.

    1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:

    • «в списке или базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется;
    • «во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
    • «в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников;
    • «данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.

    2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».

    • Если в качестве источника выбран текущий документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область. Далее выберите место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
    • Если же необходимо консолидировать данные из нескольких источников, поставьте переключатель в соответствующую область и выберите тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.

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

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

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

    • При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект поставки Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».
    • Если в документе уже присутствует отчет сводной таблицы или сводная диаграмма — в качестве источника можно использовать их. Для этого достаточно указать их расположение и выбрать нужный диапазон данных, после чего будет создана новая сводная таблица.

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

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

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

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

    Для примера используем таблицу реализации товара в разных торговых филиалах.

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

    Самое рациональное решение – это создание сводной таблицы в Excel:

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

    Просто, быстро и качественно.

    • Первая строка заданного для сведения данных диапазона должна быть заполнена.
    • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
    • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

    

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

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

    Порядок создания сводной таблицы из нескольких листов такой же.

    Создадим отчет с помощью мастера сводных таблиц:

    1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
    2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
    3. Следующий этап – «создать поля». «Далее».
    4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
    5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
    6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

    Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

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

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

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

    Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

    Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

    А вот что получится, если мы уберем «дату» и добавим «отдел»:

    А вот такой отчет можно сделать, если перетащить поля между разными областями :

    Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

    Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

    В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

    Проверка правильности выставленных коммунальных счетов

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

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

    Для примера мы сделали сводную табличку тарифов для Москвы:

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

    Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

    = тариф * количество человек / показания счетчика / площадь

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

    Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

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