Сводные таблицы в Excel – мощный инструмент для создания отчетов. Он особенно полезен в тех случаях, когда пользователь плохо работает с формулами и ему сложно самостоятельно сделать анализ данных. В данной статье мы рассмотрим, как правильно создавать подобные таблицы и какие для этого существуют возможности в редакторе Эксель. Для этого никаких файлов скачивать не нужно. Обучение доступно в режиме онлайн.
Начало работы
Первым делом нужно создать какую-нибудь таблицу. Желательно, чтобы там было несколько столбцов. При этом какая-то информация должна повторяться, поскольку только в этом случае можно будет сделать какой-нибудь анализ введенной информации.
Например, рассмотрим одни и те же финансовые расходы в разных месяцах.
Создание сводных таблиц
Для того чтобы построить подобную таблицу, необходимо сделать следующие действия.
- Для начала ее необходимо полностью выделить.
- Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».
- В результате этого появится окно, в котором вам нужно указать несколько основных параметров для построения сводной таблицы. Первым делом необходимо выбрать область данных, на основе которых будет проводиться анализ. Если вы предварительно выделили таблицу, то ссылка на нее подставится автоматически. В ином случае ее нужно будет выделить.
- Затем вас попросят указать, где именно будет происходить построение. Лучше выбрать пункт «На существующий лист», поскольку будет неудобно проводить анализ информации, когда всё разбросано на несколько листов. Затем необходимо указать диапазон. Для этого нужно кликнуть на иконку около поля для ввода.
- Сразу после этого мастер создания сводных таблиц свернется до маленького размера. Помимо этого, изменится и внешний вид курсора. Вам нужно будет сделать левый клик мыши в любое удобное для вас место.
- В результате этого ссылка на указанную ячейку подставится автоматически. Затем нужно нажать на иконку в правой части окна, чтобы восстановить его до исходного размера.
- Для завершения настроек нужно нажать на кнопку «OK».
- В результате этого вы увидите пустой шаблон, для работы со сводными таблицами.
- На этом этапе необходимо указать, какое поле будет:
- столбцом;
- строкой;
- значением для анализа.
Вы можете выбрать что угодно. Всё зависит от того, какую именно информацию вы хотите получить.
- Для того чтобы добавить любое поле, по нему нужно сделать левый клик мыши и, не отпуская пальца, перетащить в нужную область. При этом курсор изменит свой внешний вид.
- Отпустить палец можно только тогда, когда исчезнет перечеркнутый круг. Подобным образом, нужно перетащить все поля, которые есть в вашей таблице.
- Для того чтобы увидеть результат целиком, можно закрыть боковую панель настроек. Для этого достаточно кликнуть на крестик.
- В результате этого вы увидите следующее. При помощи этого инструмента вы сможете свести сумму расходов в каждом месяце по каждой позиции. Кроме того, доступна информация об общем итоге.
- Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.
- Снова закрываем помощник для построения.
- На этот раз мы видим, что сводная таблица стала намного больше, поскольку сейчас в качестве столбцов выступают не месяцы, а категории расходов.
Использование рекомендуемых сводных таблиц
Если у вас не получается самостоятельно построить таблицу, вы всегда можете рассчитывать на помощь редактора. В Экселе существует возможность создания подобных объектов в автоматическом режиме.
Для этого необходимо сделать следующие действия, но предварительно выделите всю информацию целиком.
- Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.
- Сразу после этого появится окно, в котором будут различные примеры для построения. Подобные варианты предлагаются на основе нескольких столбцов. От их количества напрямую зависит число шаблонов.
- При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.
- Можно выбрать то, что нравится больше всего.
- Для вставки выбранного варианта достаточно нажать на кнопку «OK».
- В итоге вы получите следующий результат.
Обратите внимание: таблица создалась на новом листе. Это будет происходить каждый раз при использовании конструктора.
Анализ
Как только вы добавите (неважно как) сводную таблицу, вы увидите на панели инструментов новую вкладку «Анализ». На ней расположено огромное количество различных инструментов и функций.
Рассмотрим каждую из них более детально.
Сводная таблица
Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:
- изменить имя;
- вызвать окно настроек.
В окне параметров вы увидите много чего интересного.
Активное поле
При помощи этого инструмента можно сделать следующее:
- Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».
- Сразу после этого вы увидите следующее окно. Здесь можно указать тип операции, которую следует использовать для сведения данных в выбранном поле.
- Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.
- В результате появится окно «Формат ячеек».
Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.
Группировать
Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.
Вставить срез
Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.
- Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
- В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.
- В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.
- Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.
- Таким образом получится выбрать любой промежуток времени.
- В любой момент всё можно вернуть в исходный вид. Для этого нужно кликнуть на иконку в правом верхнем углу этого окошка.
В данном случае мы смогли сортировать отчет по месяцам, поскольку у нас существовало соответствующее поле. Но для работы с датами есть более мощный инструмент.
Вставить временную шкалу
Если вы кликните на соответствующую кнопку на панели инструментов, то, скорее всего, увидите вот такую ошибку. Дело в том, что в нашей таблице нет ячеек, у которых будет формат данных «Дата» в явном виде.
В качестве примера создадим небольшую таблицу с различными датами.
Затем нужно будет построить сводную таблицу.
Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.
- Затем нас попросят выбрать диапазон значений.
- Для этого достаточно выделить всю таблицу целиком.
- Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».
- Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).
- Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).
- На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».
- Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.
- Выбираем другой месяц и данных нет, поскольку все расходы в таблице указаны только за март.
Обновить
Если вы внесли какие-нибудь изменения в исходные данные и по каким-то причинам это не отобразилось в сводной таблице, вы всегда можете обновить её вручную. Для этого достаточно нажать на соответствующую кнопку на панели инструментов.
Источник данных
Если вы решили изменить поля, на основе которых должно происходить построение, то намного проще сделать это в настройках, а не удалять таблицу и создавать её заново с учетом новых предпочтений.
Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.
В результате этого появится окно, в котором можно заново выделить нужное количество информации.
Действия
При помощи этого инструмента вы сможете:
- очистить таблицу;
- выделить;
- переместить её.
Вычисления
Если расчетов в таблице недостаточно или они не отвечают вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие варианты.
К ним относятся:
- вычисляемое поле;
- вычисляемый объект;
- порядок вычислений (в списке отображаются добавленные формулы);
- вывести формулы (информации нет, так как нет добавленных формул).
Сервис
Здесь вы сможете создать сводную диаграмму либо изменить тип рекомендуемой таблицы.
Показать
При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.
Благодаря этому вы сможете:
- настроить отображение боковой панели со списком полей;
- включить или выключить кнопки «плюс/мину»с;
- настроить отображение заголовков полей.
Конструктор
При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.
Можно настроить:
- промежуточные итоги:
- не показывать;
- показывать все итоги в нижней части;
- показывать все итоги в заголовке.
- общие итоги:
- отключить для строк и столбцов;
- включить для строк и столбцов;
- включить только для строк;
- включить только для столбцов.
- макет отчета:
- показать в сжатой форме;
- показать в форме структуры;
- показать в табличной форме;
- повторять все подписи элементов;
- не повторять подписи элементов.
- пустые строки:
- вставить пустую строку после каждого элемента;
- удалить пустую строку после каждого элемента.
- параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
- заголовки строк;
- заголовки столбцов;
- чередующиеся строки;
- чередующиеся столбцы.
- настроить стиль оформления элементов.
Для того чтобы увидеть больше различных вариантов, нужно кликнуть на треугольник в правом нижнем углу этого инструмента.
Сразу после этого появится огромный список. Можете выбрать что угодно. При наведении на каждый из шаблонов ваша таблица будет меняться (это сделано для предварительного просмотра). Изменения не вступят в силу, пока вы не кликните на что-нибудь из предложенных вариантов.
Помимо этого, при желании, вы можете создать свой собственный стиль оформления.
Сортировка значений
Также тут можно изменить порядок отображения строк. Иногда это нужно для удобства анализа расходов. Особенно, если список очень большой, поскольку необходимую позицию проще найти по алфавиту, чем листать список по несколько раз.
Для этого нужно сделать следующее.
- Кликните на треугольник около нужного поля.
- В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).
Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».
В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».
Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.
Сводные таблицы в Excel 2003
Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.
Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.
- Перейти в раздел меню «Данные» и выбрать соответствующий пункт.
- В результате этого появится мастер для созданий подобных объектов.
- После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».
- Для завершения настроек жмем на «Готово».
- В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.
- К примеру, может получиться вот такой результат.
Становится очевидно, что создавать подобные отчеты намного лучше в современных редакторах.
Заключение
В данной статье были рассмотрены все тонкости работы со сводными таблицами в редакторе Excel. Если у вас что-то не получается, возможно, вы выделяете не те поля или же их очень мало – для создания такого объекта необходимо несколько столбцов с повторяющимися значениями.
Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.
Видеоинструкция
Для тех, у кого всё равно остались вопросы без ответов, ниже прилагается видеоролик с комментариями к описанной выше инструкции.
