Сводные таблицы в Эксель

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

Начало работы

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

Например, рассмотрим одни и те же финансовые расходы в разных месяцах.

Финансовые расходы

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

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

  1. Для начала ее необходимо полностью выделить.

Полное выделение

  1. Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».

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

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

Параметры

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

На существующий лист

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

Левый клик

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

Восстановление размера

  1. Для завершения настроек нужно нажать на кнопку «OK».

Клик по OK

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

Пустой шаблон

  1. На этом этапе необходимо указать, какое поле будет:
    1. столбцом;
    2. строкой;
    3. значением для анализа.

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

Выбор параметров

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

Внешний вид

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

Повтор процесса

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

Клик по крестику

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

Общий итог

  1. Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.

Смена полей

  1. Снова закрываем помощник для построения.

Открытие помощника

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

Категории расходов

Использование рекомендуемых сводных таблиц

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

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

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

  1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.

Таблица

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

Количество столбцов

  1. При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.

Предварительный просмотр

  1. Можно выбрать то, что нравится больше всего.

Выбор результата

  1. Для вставки выбранного варианта достаточно нажать на кнопку «OK».

Нажатие на OK

  1. В итоге вы получите следующий результат.

Результат работы

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

Анализ

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

Анализ

Рассмотрим каждую из них более детально.

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

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

  • изменить имя;

Смена имени

  • вызвать окно настроек.

Окно настроек

В окне параметров вы увидите много чего интересного.

Окно параметров

Активное поле

При помощи этого инструмента можно сделать следующее:

  1. Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».

Параметры поля

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

Тип операции

  1. Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.

Числовой формат

  1. В результате появится окно «Формат ячеек».

Формат ячеек

Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.

Группировать

Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.

Группировать

Вставить срез

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

  1. Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
  2. В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.

Вставка среза

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

Информация по определенным числам

  1. Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.

Все значения

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

Выбор промежутка

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

Возврат

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

Вставить временную шкалу

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

Дата

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

Пример таблицы

Затем нужно будет построить сводную таблицу.

Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.

Вставка

  1. Затем нас попросят выбрать диапазон значений.

Выбор диапазона

  1. Для этого достаточно выделить всю таблицу целиком.

Выделение всей таблицы

  1. Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

Завершение построения

  1. Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

Выбор варианта

  1. Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

Вставить временную шкалу

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

Поле сортировки

  1. Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.

Бегунок даты

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

Выбор другого месяца

Обновить

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

Обновить

Источник данных

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

Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.

Источник данных

В результате этого появится окно, в котором можно заново выделить нужное количество информации.

Повторное выделение

Действия

При помощи этого инструмента вы сможете:

  • очистить таблицу;
  • выделить;
  • переместить её.

Действие

Вычисления

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

Варианты работы

К ним относятся:

  • вычисляемое поле;

Вычисляемое поле

  • вычисляемый объект;

Вычисляемый объект

  • порядок вычислений (в списке отображаются добавленные формулы);

Порядок вычислений

  • вывести формулы (информации нет, так как нет добавленных формул).

Вывод формул

Сервис

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

Сервис

Показать

При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.

Показать

Благодаря этому вы сможете:

  • настроить отображение боковой панели со списком полей;

Настройка отображения боковой панели

  • включить или выключить кнопки «плюс/мину»с;

Включение кнопки

  • настроить отображение заголовков полей.

Заголовки полей

Конструктор

При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.

Конструктор

Можно настроить:

  • промежуточные итоги:
    • не показывать;
    • показывать все итоги в нижней части;
    • показывать все итоги в заголовке.

Промежуточные итоги

  • общие итоги:
    • отключить для строк и столбцов;
    • включить для строк и столбцов;
    • включить только для строк;
    • включить только для столбцов.

Общие итоги

  • макет отчета:
    • показать в сжатой форме;
    • показать в форме структуры;
    • показать в табличной форме;
    • повторять все подписи элементов;
    • не повторять подписи элементов.

Макет отчета

  • пустые строки:
    • вставить пустую строку после каждого элемента;
    • удалить пустую строку после каждого элемента.

Пустые строки

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

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

  • настроить стиль оформления элементов.

Стиль оформления

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

Клик по треугольнику

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

Применение изменений

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

Собственный стиль оформления

Сортировка значений

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

Для этого нужно сделать следующее.

  1. Кликните на треугольник около нужного поля.
  2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Сортировка значений

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

Дополнительные параметры сортировки

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

Дополнительно

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

Автоматический режим

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

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

  1. Перейти в раздел меню «Данные» и выбрать соответствующий пункт.

Данные

  1. В результате этого появится мастер для созданий подобных объектов.

Мастер для созданий подобных объектов

  1. После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».

Клик по Далее

  1. Для завершения настроек жмем на «Готово».

Клик по Готово

  1. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

Области полей

  1. К примеру, может получиться вот такой результат.

Области полей

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

Заключение

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

Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.

Видеоинструкция

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

Оцените статью
OS Helper
Добавить комментарий