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

Добавил пользователь Morpheus
Обновлено: 04.10.2024

Жизнь человека из мира технологий — это невероятная комбинация цифр, показателей, которые периодически требуют систематизации. Чтобы осуществить последнее, приходится использовать особые технические инструменты. Наша статья докажет, что сводные таблицы в Excel доступны даже для чайников.

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.

Заранее озаглавьте каждый столбик

Обновление данных в сводной таблице в Excel

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

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

Изменение структуры отчёта

Вам откроются на выбор три типа для структуризации информации:

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

Все показатели подаются иерархично: от малого к большому.

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Такую таблицу можно выполнить двумя разными способами.

Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

Рассмотрим первый способ.

Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

Шаг первый.

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

Настройка ленты

либо войти во вкладку

Добавление мастера сводных таблиц

Мастер сводных на панели

Шаг второй. Построение сводной таблицы из нескольких источников данных.

Второй способ.

Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

Данный способ заключается в использовании запроса надстройки Power Query.

Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

Шаг первый.

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

Шаг два.

Power Query из таблицы

Шаг три.

Слияние запросов

Шаг четыре.

Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

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

сумма

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

Как вы можете заметить, при перетаскивании поля Сумма заказа в область ∑ ЗНАЧЕНИЯ оно отображается как Сумма суммы заказа, указывая на то, что расчет принимается как Сумма. В сводной таблице в верхнем левом углу отображается сумма суммы заказа. Кроме того, столбцы итоговой суммы и строки итоговой суммы отображаются для промежуточных итогов по полям в строках и столбцах соответственно.

Настройки поля значения

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

Нажмите на сумму суммы заказа в области ∑ VALUES.
Выберите Value Field Settings из выпадающего списка.

% от общего итога

Вы можете отобразить значения в сводной таблице как% от общего итога.

В поле Пользовательское имя введите% от общей суммы.
Нажмите на поле Показать значения как.
Нажмите на% от общего итога в раскрывающемся списке. Нажмите ОК.

Сводная таблица суммирует значения в% к общему итогу.

Как вы можете заметить, сумма суммы заказа в верхнем левом углу сводной таблицы и в области ∑ ЗНАЧЕНИЯ на панели полей сводной таблицы изменяется на новое пользовательское имя -% от общего итога.

Нажмите на заголовок столбца общей суммы.

Введите% от общего итога в строке формул. Заголовки столбцов и строк изменятся на% от общего итога.

Нажмите на заголовок столбца общей суммы.

Введите% от общего итога в строке формул. Заголовки столбцов и строк изменятся на% от общего итога.

% от общего столбца

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

Нажмите на сумму суммы заказа в области ∑ VALUES.

В поле Пользовательское имя введите% от общей суммы за месяц.

Нажмите на поле Показать значения как.

Выберите% от общего столбца из раскрывающегося списка.

Нажмите на сумму суммы заказа в области ∑ VALUES.

В поле Пользовательское имя введите% от общей суммы за месяц.

Нажмите на поле Показать значения как.

Выберите% от общего столбца из раскрывающегося списка.

Нажмите на заголовок столбца общей суммы.

Введите% общего столбца в строке формул. Заголовки столбцов и строк изменятся на% от общего количества столбцов.

Нажмите на заголовок столбца общей суммы.

% от общего количества строк

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

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

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

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:

Она содержит данные:

Даты заказов;
Регион в котором расположен клиент;
Тип клиента;
Клиент;
Количество продаж;
Выручка;
Прибыль.

Теперь, представим, что наш руководитель поставил задачу вычислить:

Какой объем выручки у региона Север за 2017 год?;
ТОП пять клиентов по выручке;
Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

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

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

Для создания таблицы выполните следующие действия:

Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
- Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

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

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

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:
- Какой объем выручки у региона Север за 2017 год?;
- ТОП пять клиентов по выручке;
- Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Перенос полей из области в область представляет собой удобный интерфейс, в котором, при перемещении, данные автоматически обновляются.

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

Пример 1. Какой объем выручки у региона Север?

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

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

У нас получится следующая таблица:

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

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.

Для сортировки данных выполните следующие шаги:

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

Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе « Сводные таблицы в Excel«. Успей зарегистрироваться по ссылке!

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