Что такое сводная таблица?
Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:
- представить большие объемы данных в удобной для пользователя форме.
- группировать информацию по категориям и подкатегориям.
- фильтровать, сортировать и условно форматировать различные сведения, чтобы вы могли сосредоточиться на самом актуальном.
- поменять строки и столбцы местами.
- рассчитать различные виды итогов.
- разворачивать и сворачивать уровни данных, чтобы узнать подробности.
- представить в Интернете сжатые и привлекательные таблицы или печатные отчеты.
Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:
И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.
Однако, когда вы хотите сравнить несколько показателей по каждому продавцу либо по отдельным товарам, использование сводных таблиц является гораздо более эффективным способом. Ведь при использовании функций вам придется писать много формул с достаточно сложными условиями. А здесь всего за несколько щелчков мыши вы можете получить гибкую и легко настраиваемую форму, которая суммирует ваши цифры как вам необходимо.
Вот посмотрите сами.
Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2020, 2013, 2010 и 2007.
Фильтрация отдельных полей строк и столбцов
Кнопки фильтра в полях строк и столбцов, прикрепленные к названиям, позволяют фильтровать записи в конкретных группах значений источника данных. Для фильтрации данных в столбцах или строках сводной таблицы щелкните на такой кнопке и установите флажок равным значению (Выделить все) в верхней части раскрывающегося списка. Затем установите флажки всех групп или отдельных записей, итоговые значения которых вам нужны в сводной таблице, и щелкните на кнопке ОК.
Как и в случае фильтрации по полю фильтра, Excel заменяет стандартный значок конусообразной пиктограммой фильтра, свидетельствующей о том, что поле в настоящий момент отфильтровано по одному или нескольким своим значениям, которые и отображаются в сводной таблице. Чтобы снова отобразить все значения поля столбца или строки, щелкните на кнопке фильтра, а затем — на пункте (Выделить все) в самом верху раскрывающегося списка.
На скриншоте ниже показан пример сводной таблицы после фильтрации по дате (выбрана дата 1 января) и категории (выбраны Одежда, Питание и Хоз. Расходы).
Категория Хоз. Расходы не отображается в списке по причине отсутствия этой категории расходов за 1 января.
Помимо отдельных записей, в сводной таблице можно фильтровать группы записей, которые соответствуют определенным критериям (например, названия городов начинаются с заданных букв или величина зарплаты находится в определенных рамках). Для выполнения подобной фильтрации используются фильтры по подписи или фильтры по значению, доступные в дополнительных меню.
Работа со списком показателей сводной таблицы
Панель, которая формально называется списком полей, является основным инструментом, который используется для упорядочения таблицы в соответствии с вашими требованиями. Вы можете настроить её по своему вкусу, чтобы удобнее .
Чтобы изменить способ отображения вашей рабочей области, нажмите кнопку «Инструменты» и выберите предпочитаемый макет.
Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.
Закрытие и открытие панели редактирования.
Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 
Чтобы снова отобразить его, щелкните правой кнопкой мыши в любом месте таблицы и выберите «Показать …» в контекстном меню.
Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».
Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010
Мы уже разобрались, что сводные таблицы обладают сплошными плюсами. Однако, для того чтобы приступить к их созданию, необходимо иметь уже наработанную базу данных, которая будет внесена в таблицу.
Так как Excel довольно сложная программа с большим набором инструментов и функций, мы не будем углубляться в её изучение с головой, а лишь покажем общий принцип создания базы данных. Итак, приступим:
Шаг 1.
- Запустите программу. На главной странице Вы увидите стандартное поле с ячейками, куда необходимо будет водить нашу базу данных.
- Для начала необходимо создать четыре основных столбца с заголовками.
- Для этого дважды кликните по одной из верхних ячеек и впишите в неё название первого заголовка «Продавец» и нажмите Enter.
- Далее проделайте то же самое с тремя соседними ячейками в той же строке, вписывая в них названия заголовков.
Рисунок 1. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010
Шаг 2.
- После того, как Вы ввели названия заголовков, необходимо увеличить им размер шрифта и сделать жирными, чтобы они выделялись на фоне других данных.
- Для этого зажмите левую кнопку мышки на первой ячейке и ведите мышь вправо, выделяя соседние три.
- После выделения всех ячеек на рабочей панели с текстом установите для них размер шрифта 12, выделите его жирным и выровняйте по левому краю.
- Текст наверняка не будет помещаться в ячейки, поэтому зажмите левую кнопку мыши на границе ячейки и поведите мышь вправо, тем самым увеличивая её.
- То же самое Вы можете проделать и с высотой ячеек.
Рисунок 2. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010
Шаг 3.
- Далее, под каждым заголовком вписывайте соответствующий ему параметр точно таким же способом, как Вы вписывали заголовки.
- Для того, чтобы не мучиться с редактированием каждой ячейки, после ввода параметров выделите их все мышкой и примените сразу к ним одинаковые свойства.
Рисунок 3. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010
Воспользуйтесь рекомендациями программы.
Как вы только что видели, создание сводных таблиц — довольно простое дело, даже для «чайников». Однако Microsoft делает еще один шаг вперед и предлагает автоматически сгенерировать отчет, наиболее подходящий для ваших исходных данных. Все, что вам нужно, это 4 щелчка мыши:
- Нажмите любую ячейку в исходном диапазоне ячеек или таблицы.
- На вкладке «Вставка» выберите «Рекомендуемые сводные таблицы». Программа немедленно отобразит несколько макетов, основанных на ваших данных.
- Щелкните на любом макете, чтобы увидеть его предварительный просмотр.
- Если вас устраивает предложение, нажмите кнопку «ОК» и добавьте понравившийся вариант на новый лист.
Как вы видите на скриншоте выше, Эксель смог предложить несколько базовых макетов для моих исходных данных, которые значительно уступают сводным таблицам, которые мы создали вручную несколько минут назад. Конечно, это только мое мнение
Но при всем при этом, использование рекомендаций — это быстрый способ начать работу, особенно когда у вас много данных и вы не знаете, с чего начать. А затем этот вариант можно легко изменить по вашему вкусу.
Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Теперь, когда у Вас имеется база данных, можно переходить непосредственно к созданию сводной таблицы. Для этого проделайте следующие шаги:
Шаг 1.
- Первым делом необходимо выделить одну из ячеек имеющейся у Вас стартовой таблицы. Далее в левом верхнем углу перейдите на вкладку «Вставка» и кликните по кнопке «Сводная таблица».
Рисунок 1. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 2.
- Перед Вами откроется окошко, в котором потребуется выбрать диапазон или ввести название таблицы.
- Также в окне есть возможность выбора места для отчета. Создадим его на новом листе.
- Пометьте точками пункты, которые указаны на скриншоте ниже и нажмите кнопку «ОК».
Рисунок 2. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 3.
- Перед Вами откроется новый листок, на который будет помещена ещё незаполненная сводная таблица. С правой стороны окна появится колонка с полями и областями.
- Под полями подразумеваются заголовки столбиков, которые вписаны в вашу начальную таблицу с данными. Мышкой их можно будет перетащить в имеющиеся области, что позволит Вам сформировать сводную таблицу.
- Задействованные заголовки будут помечаться галкой. В одну область можно вносить несколько заголовков и изменять их местоположения для того, чтобы получить наиболее приемлемый для Вас вид сортировки данных.
Рисунок 3. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 4.
- Далее необходимо решить, что мы хотим проанализировать. К примеру, мы хотим понять, какой конкретно продавец, продал какой товар за определённый период времени и на какую сумму. Фильтрация данных в таблице будет осуществляться по продавцам.
- В колонке справа выберите заголовок «Продавец», зажмите на нём левую кнопку мышки и перетаскивайте в область «Фильтр отчёта».
- Как можно заметить, внешний вид таблицы поменялся, заголовок стал отмеченным.
Рисунок 4. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 5.
- Далее по той же схеме в колонке справа необходимо зажать левую кнопку мышки на заголовке «Товары» и перетащить его в область «Названия строк».
Рисунок 5. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 6.
- Далее в графу «Названия столбцов» попробуем перетащить заголовок «Дата».
- Чтобы список продаж отображался не ежедневный а, например, ежемесячный, необходимо кликнуть по любой ячейке с датой правой кнопкой мышки и выбрать из списка пункт «Группировать».
Рисунок 6. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- В открывшемся окне потребуется выбрать период группировки (с какого числа по какое), и выбрать шаг из списка. После чего следует нажать «ОК».
Рисунок 7. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- Далее Ваша таблица преобразуется таким вот образом.
Рисунок 8. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 7.
- Следующем пунктом перетяните заголовок «Сумма» в графу «Значение».
- Как можно заметить, стали отображаться просто числа, несмотря на то, что к данному столбцу в стартовой версии таблицы был применён формат отображения «Числовой».
Рисунок 9. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- Чтобы поправить данную проблему, выделите в сводной таблице необходимые ячейки, кликните правой кнопкой мышки из списка щёлкните по пункту «Числовой формат».
Рисунок 10. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- В открывшемся окошке в разделе «Условные форматы» выберите пункт «Числовой» и по желанию можете отметить галочкой пункт «Разделитель групп разрядов».
- Далее нажмите «ОК». Как можно заметить, простые числа изменились и теперь отображаются не просто 650, а 650,00.
Рисунок 11. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Шаг 8.
- По идее сводная таблица готова и с ней можно начинать работу. Например, при выборе продавца Вы сможете выбрать одного, двух, трёх или сразу всех, поставив маркер напротив пункта «Отметить несколько элементов».
Рисунок 12. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- Точно таким же способом можно фильтровать строки и столбцы. К примеру, наименования товаров и даты.
- Отметив галочками поля «Брюки» и «Костюм», легко выяснить, сколько с их продажи получилось выручки в целом, или конкретными продавцами.
Рисунок 13. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- Также в графе «Значения» можно задать определённые параметры заголовку. На данный момент там установлен параметр «Сумма».
- То есть, если посмотреть на таблицу, можно увидеть, что продавцом Ромой в феврале было продано рубашек на общую сумму 1 800,00 рублей.
- Для того, чтобы узнать их количество, необходимо щёлкнуть мышкой по пункту «Сумма» и выбрать из меню строчку «Параметры полей значений».
Рисунок 14. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
- Далее откроется окошко, где необходимо выбрать пункт «Количество» и нажать кнопку «ОК». После этого Ваша таблица поменяется и, глядя на неё, Вы поймёте, что за февраль Роман продал рубашек в количестве двух штук.
Рисунок 15. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Как уже выше было оговорено, программа Excel имеет обширный ассортимент инструментов и возможностей, поэтому говорить о работе с таблицами можно до бесконечности.
Для того, чтобы её лучше понять, советуем самостоятельно посидеть и поэкспериментировать с изменениями значений. Если что Вам будет непонятно, обращайтесь к встроенной справке Microsoft Excel. Она в нём исчерпывающая.
Давайте улучшим результат.
Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2020 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.
Вы также можете получить доступ к параметрам и функциям, доступным для определенного элемента, щелкнув его правой кнопкой мыши (об этом мы уже говорили при создании).
После того, как вы построили таблицу на основе исходных данных, вы, возможно, захотите уточнить ее, чтобы провести более серьёзный анализ.
Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».
Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2020 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.
На снимке экрана ниже показан новый дизайн и макет.
Я изменил цветовой макет, а также постарался, чтобы таблица была более компактной. Для этого поменяем параметры представления товара. Какие параметры я использовал – вы видите на скриншоте.
Думаю, стало даже лучше.
