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

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

Часть 1

Часть 1 из 3:

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

Загрузить PDF

1
Загрузите файл, из которого вы хотите создать сводную таблицу. Сводные таблицы позволяют вам создавать наглядные отчеты по данным из файла. Вы можете выполнять вычисления без ввода формул или копирования ячеек. Вам понадобится файл с несколькими заполненными полями, чтобы создать сводную таблицу.
- Вы также можете создать сводную таблицу в Excel, используя внешний источник данных, как например Access. Вы можете вставить сводную таблицу в лист Excel.
2
Убедитесь, что ваши данные отвечают требованиям сводной таблицы. Сводная таблица не всегда тот инструмент, который вам нужен. Чтобы вы могли извлечь выгоду из преимуществ такой таблицы, ваш лист должен отвечать нескольким базовым критериям: ^{[1]XИсточник информации}
- Ваш лист должен включать как минимум один столбец с одинаковыми значениями. Это значит, что как минимум в одном столбце у вас должны повторяться данные. В примере из следующего раздела в столбце «Product Type» есть две записи: «Table» or «Chair».
- Она должна содержать численную информацию. Это то, что будет сравниваться и суммироваться в таблице. В примере из следующего раздела столбец «Sales» заполнен числовыми данными.
3
Запустите мастер сводных таблиц. Нажмите на кнопку «Вставить» вверху окна Excel. Щелкните по кнопке «Сводная таблица» на левой стороне ленты вставки.
- Если вы используете Excel 2003 или более раннюю версию, щелкните на пункт меню Данные и выберите Сводные таблицы и диаграммы...
4
Выберите данные, которые хотите использовать. По умолчанию Excel выберет все данные на вашем активном листе. Вы можете щелкнуть и провести мышью, чтобы выбрать определенную часть листа, или вы можете прописать область ячеек вручную.
- Если вы используете внешний источник данных, щелкните по пункту «Использовать внешний источник данных» и нажмите Выбрать подключение.... Задайте путь для подключения базы данных на вашем компьютере.
5
Выберите место для своей сводной таблицы. После выбора области задайте опции расположения в том же окне. По умолчанию Excel расположит таблицу на новом листе, позволяя вам переключаться с помощью закладок внизу окна. Вы также можете выбрать расположить сводную таблицу на том же листе, что и данные, после чего вам нужно будет выбрать ячейку, в которой она будет создана. ^{[2]XИсточник информации}
- Когда вы будете довольны результатом, кликните OK. Ваша сводная таблица будет размещена, а интерфейс изменится.
Реклама

Часть 2

Часть 2 из 3:

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

Загрузить PDF

1
Добавление поля строки. Создавая сводную таблицу, вы, по сути, сортируете свои данные по строкам и столбцам. То, что вы добавляете туда, определяет структуру таблицы. Перетащите поля из списка полей справа в список строк сводной таблицы, чтобы вставить информацию.
- Например, ваша компания продает 2 товара: столы и стулья. У вас есть таблица с количеством товаров (Sales) каждого типа (Product Type), которое было продано в пяти ваших магазинах (Store). Вы хотите узнать, сколько товаров было продано в каждом магазине.
- Перетащите поле Store из списка полей в раздел список строк вашей сводной таблицы. Появится список магазинов. Каждый как отдельная строка.
Так же, как и строки, столбцы позволяют вам сортировать и отображать данные. В примере выше поле Store было добавлено в секцию полей строк. Чтобы увидеть как много товаров каждого типа было продано, перетащите поле Product Type в поля столбцов.
3
Добавление поля значения. Теперь, когда структура выстроена, вы можете добавить данные, которые будут отображаться в таблице. Щелкните и перетащите поле Sales в секцию поля значений сводной таблицы. Вы увидите, как в вашей таблице отобразится информация по продажам для обоих товаров в каждом из магазинов и столбец Total (итого) справа. ^{[3]XИсточник информации}
- Для всех действий, описанных выше, вы можете перетащить поля в соответствующий раздел под списком полей в правой части окна вместо того, чтобы перетаскивать их в таблицу.
4
Добавление нескольких полей в секцию. Сводные таблицы позволяют вам добавлять несколько полей в каждую из секций, давая еще больший контроль над тем, как отображаются данные. Используя пример выше, скажем, вы делаете несколько типов столов и несколько типов стульев. В вашей таблице записывается не только стул это или стол (Product Type), но и точная модель проданного стола или стула (Model).
- Перетащите поле Model в секцию полей столбцов. Теперь столбцы показывают деление продаж по модели и типу. Вы можете поменять порядок того, как стоят эти заголовки, кликнув на кнопку со стрелкой рядом с полем в правом нижнем иглу окна. Выберите «Вверх» или «Вниз» чтобы изменить порядок.
5
Изменение вида отображения данных. Вы можете изменить то, как отображаются значения, кликнув на значок стрелки рядом со значением в поле значений. Выберите «Свойства поля», чтобы изменить то, как рассчитываются значения. Например, вы можете выводить значения в процентах вместо суммы или находить среднее значение вместо суммирования.
- Вы можете добавлять одно и то же поле в значения несколько раз, чтобы использовать дальше. В примере выше выводится сумма продаж по каждому магазину. Добавляя поле Sales снова, вы можете изменять свойства значений, чтобы показать второе поле Sales в процентах от общих продаж.
6
Узнайте о том, как можно управлять этими значениями. При изменении способа расчета значений у вас есть несколько вариантов в зависимости от того, что вам нужно.
- Сумма - значение по умолчанию для полей. Excel вычислит сумму всех значений в выбранном поле.
- Количество – вычисляет количество ячеек с данными в выбранном поле.
- Среднее – вычисляет среднее значение от всех значений в поле.
Меню «Фильтр отчетов» содержит поля, которые позволяют вам пролистать сводки данных из сводной таблицы, отфильтровав наборы данных. Они работают как фильтры для отчета. Например, настроив поле Store как фильтр вместо строки, вы сможете выбрать каждый магазин и увидеть его собственные продажи или просмотреть несколько магазинов одновременно.
Реклама

Часть 3

Часть 3 из 3:

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

Загрузить PDF

Одно из основных преимуществ сводной таблицы – возможность отсортировать результаты и получить отчеты в динамике. Каждый заголовок можно отсортировать и отфильтровать значения, кликнув на кнопку со стрелкой вниз рядом с заголовком. После этого вы можете отсортировать список или отфильтровать его, чтобы показывались только определенные значения.^{[4]XИсточник информации}
Ваша сводная таблица будет автоматически обновляться при каждом изменении в файле. Это может помочь контролировать его и следить за изменениями.
3
Изменение вашей сводной таблицы. Сводные таблицы сильно упрощают изменение позиции и порядка полей. Попробуйте перетащить различные поля в разные места, чтобы построить таблицу так, чтобы она отвечала вашим требованиям.
- Отсюда сводная таблица получила свое название. Перемещая данные в различные места, «сводя» их, вы меняете способ представления данных.
Вы можете создать сводную диаграмму, чтобы сделать наглядные отчеты в динамике. Ее можно создать прямо из готовой сводной таблицы, что делает процесс создания диаграммы очень простым.
Реклама

Советы

Если вы используете команду Импорт данных из меню Данные, вам будет доступен больший выбор опций, как например подключение к базе данных, файлы Excel, базы данных Access, текстовые файлы, данные стандарта ODBC, веб-страницы, OLAP и XML/XSL. Вы можете затем использовать эти данные так же, как список Excel.
Если вы используете автофильтрацию (на вкладке «Данные», «Фильтр»), выключите ее, когда будете создавать сводную таблицу. После создания таблицы ее можно будет снова включить.

Предупреждения

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