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

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подготовка

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

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

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

Создание

Создать сводную таблицу можно в программах различных версий (2010-2016). Рассматриваемая инструкция создана на основе Майкрософт Эксель 2013.

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

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

Обратите внимание! Данным способом невозможно консолидировать (объединить) несколько таблиц.

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

Обратите внимание! Если перед этим этапом произвести выделение таблицы, то все поля заполнятся автоматически.

Заполнение

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

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

В появившемся окне можно выбрать желаемый тип операции.

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

Сложности при работе

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

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

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

Добавление строк и столбцов

Конструкция собранная с нескольких листов

Желаемая функция отобразится рядом с треугольником вызывающим меню.

Последний этап, выбор расположения нового объекта.

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

Удаление

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

Поделись с друзьями!

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

1. Подготовка данных

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

2. Создание таблицы

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

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

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

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

Давайте переместим поле "Дата" в фильтры, "Клиент" в строки, "Магазин" в колонны, а "Сумма" в значения.

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

4. Фильтры данных в сводной таблице

Для того что бы отфильтровать данные в сводной таблице - есть два основных способа.

Читайте также: