Сводная таблица в экселе пошаговая инструкция для чайников - Arganabio.ru - инструкции по эксплуатации и многое другое

Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников

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

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

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

Что такое сводная таблица?
Как создать сводную таблицу.
- 1. Организуйте свои исходные данные
- 2. Создаем и размещаем макет
- 3. Как добавить поле
- 4. Как удалить поле из сводной таблицы?
- 5. Как упорядочить поля?
- 6. Выберите функцию для значений (необязательно)
- 7. Используем различные вычисления в полях значения (необязательно)
Работа со списком показателей сводной таблицы
Закрытие и открытие панели редактирования.
Воспользуйтесь рекомендациями программы.
Давайте улучшим результат.
Как обновить сводную таблицу.
Как переместить на новое место?
Как удалить сводную таблицу?

Что такое сводная таблица?

Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

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

Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры.

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

1. Организуйте свои исходные данные

Перед созданием сводного отчета организуйте свои данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».

Использование «умной» таблицы в качестве исходных данных дает вам очень хорошее преимущество — ваш диапазон данных становится «динамическим». Это означает, что он будет автоматически расширяться или уменьшаться при добавлении или удалении записей. Поэтому вам не придется беспокоиться о том, что в свод не попала самая свежая информация.

Полезные советы:

Добавьте уникальные, значимые заголовки в столбцы, они позже превратятся в имена полей.
Убедитесь, что исходная таблица не содержит пустых строк или столбцов и промежуточных итогов.
Чтобы упростить работу, вы можете присвоить исходной таблице уникальное имя, введя его в поле «Имя» в верхнем правом углу.

2. Создаем и размещаем макет

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

Откроется окно «Создание ….. ». Убедитесь, что в поле Диапазон указан правильный источник данных. Затем выберите местоположение для свода:

Выбор нового рабочего листа поместит его на новый лист, начиная с ячейки A1.
Выбор существующего листа разместит в указанном вами месте на существующем листе. В поле «Диапазон» выберите первую ячейку (то есть, верхнюю левую), в которую вы хотите поместить свою таблицу.

Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:

Полезные советы:

В большинстве случаев имеет смысл размещать на отдельном рабочем листе. Это особенно рекомендуется для начинающих.
Ежели вы берете информацию из другой таблицы или рабочей книги, включите их имена, используя следующий синтаксис: [workbook_name]sheet_name!Range. Например, [Книга1.xlsx] Лист1!$A$1:$E$50. Конечно, вы можете не писать это все руками, а просто выбрать диапазон ячеек в другой книге с помощью мыши.
Возможно, было бы полезно построить таблицу и диаграмму одновременно. Для этого в Excel 2016 и 2013 перейдите на вкладку «Вставка», щелкните стрелку под кнопкой «Сводная диаграмма», а затем нажмите «Диаграмма и таблица». В версиях 2010 и 2007 щелкните стрелку под сводной таблицей, а затем — Сводная диаграмма.

Организация макета.

Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:

Раздел «Поле» содержит названия показателей, которые вы можете добавить. Они соответствуют именам столбцов исходных данных.
Раздел «Макет» содержит область «Фильтры», «Столбцы», «Строки» и «Значения». Здесь вы можете расположить в нужном порядке поля.

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

3. Как добавить поле

Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его именем.

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

Нечисловые добавляются в область Строки;
Числовые добавляются в область значений;
Дата и время добавляются в область Столбцы.

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете выполнить следующее:

Снимите флажок напротив него, который вы ранее установили.
Щелкните правой кнопкой мыши поле и выберите «Удалить……».

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

5. Как упорядочить поля?

Вы можете изменить расположение показателей тремя способами:

Перетащите поле между 4 областями раздела с помощью мыши. В качестве альтернативы щелкните и удерживайте его имя в разделе «Поле», а затем перетащите в нужную область в разделе «Макет». Это приведет к удалению из текущей области и его размещению в новом месте.

Щелкните правой кнопкой мыши имя в разделе «Поле» и выберите область, в которую вы хотите добавить его:

Нажмите на поле в разделе «Макет», чтобы выбрать его. Это сразу отобразит доступные параметры:

Все внесенные вами изменения применяются немедленно.

Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).

6. Выберите функцию для значений (необязательно)

По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».

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

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

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

В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте — на вкладке «Параметры» в группе «Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

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

Это называется «Дополнительные вычисления». Доступ к ним можно получить, открыв вкладку «Параметры …», как это описано чуть выше.

Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!

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

Работа со списком показателей сводной таблицы

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

Чтобы изменить способ отображения вашей рабочей области, нажмите кнопку «Инструменты» и выберите предпочитаемый макет.

Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.

Закрытие и открытие панели редактирования.

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

Чтобы снова отобразить его, щелкните правой кнопкой мыши в любом месте таблицы и выберите «Показать …» в контекстном меню.

Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».

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

Как вы только что видели, создание сводных таблиц — довольно простое дело, даже для «чайников». Однако Microsoft делает еще один шаг вперед и предлагает автоматически сгенерировать отчет, наиболее подходящий для ваших исходных данных. Все, что вам нужно, это 4 щелчка мыши:

Нажмите любую ячейку в исходном диапазоне ячеек или таблицы.
На вкладке «Вставка» выберите «Рекомендуемые сводные таблицы». Программа немедленно отобразит несколько макетов, основанных на ваших данных.
Щелкните на любом макете, чтобы увидеть его предварительный просмотр.
Если вас устраивает предложение, нажмите кнопку «ОК» и добавьте понравившийся вариант на новый лист.

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

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

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2016 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

Я изменил цветовой макет, а также постарался, чтобы таблица была более компактной. Для этого поменяем параметры представления товара. Какие параметры я использовал – вы видите на скриншоте.

Думаю, стало даже лучше. 😊

Как избавиться от заголовков «Метки строк» и «Метки столбцов».

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

Простой способ избавиться от этих нелепых заголовков — перейти с сжатого макета на структурный или табличный. Для этого откройте вкладку «Конструктор», щелкните раскрывающийся список «Макет отчета» и выберите « Показать в форме структуры» или « Показать в табличной форме» .

И вот что мы получим в результате.

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

Другое решение — перейти на вкладку «Анализ», нажать кнопку «Заголовки полей», выключить их. Однако это удалит не только все заголовки, а также выпадающие фильтры и возможность сортировки. А для анализа данных отсутствие фильтров – это чаще всего нехорошо.

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

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

Как обновить вручную.

Нажмите в любом месте на свод.
На вкладке «Анализ» нажмите кнопку «Обновить» или же нажмите клавиши ALT + F5.

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

Чтобы обновить все сводные таблицы в файле, нажмите стрелку кнопки «Обновить», а затем — «Обновить все».

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

После запуска обновления вы можете просмотреть статус или отменить его, если вы передумали. Просто нажмите на стрелку кнопки «Обновить», а затем — «Состояние обновления» или «Отменить обновление».

Автоматическое обновление сводной таблицы при открытии файла.

Откройте вкладку параметров, как это мы только что делали.
В диалоговом окне «Параметры … » перейдите на вкладку «Данные» и установите флажок «Обновить при открытии файла».

Как переместить на новое место?

Может быть вы захотите переместить своё творение в новую рабочую книгу? Перейдите на вкладку «Анализ», нажмите кнопку «Действия» и затем — «Переместить ….. ». Выберите новый пункт назначения и нажмите ОК.

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

Если вам больше не нужен определенный сводный отчет, вы можете удалить его несколькими способами.

Если таблица находится на отдельном листе, просто удалите этот лист.
Ежели она расположена вместе с некоторыми другими данными на листе, выделите всю её с помощью мыши и нажмите клавишу Delete.
Щелкните в любом месте в сводной таблице, которую хотите удалить, перейдите на вкладку «Анализ» (см. скриншот выше) => группа «Действия», нажмите небольшую стрелку под кнопкой «Выделить», выберите «Вся сводная таблица», а затем нажмите Удалить.

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

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

Возможно, вам также будет полезно:

Источник

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

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

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

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

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

Примечание: В зависимости от настроек вашей организации на кнопке может отображаться название организации. Например, «Из Power BI (Майкрософт)»

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

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

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

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

Операция

По умолчанию поля сводной таблицы, помещенные в область Значения , будут отображаться в виде СУММ. Если Excel интерпретирует данные как текст, они будут отображаться как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите параметр Параметры поля значений .

Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

Дополнительные вычисления

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

Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

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

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

Примечание: Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.

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

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

Получение из Power BI

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

По умолчанию нечисловые поля добавляются в область Строк , поля даты и времени добавляются в область Столбцы , а числовые — в область Значения .

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

Операция

По умолчанию поля сводной таблицы в области Значения будут отображаться в виде СУММ. Если Excel интерпретирует данные как текст, они будут отображаться как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений.

Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр Суммировать значения по .

Дополнительные вычисления

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

Сделайте выбор из списка.

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

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

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

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

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

Щелкните ячейку в диапазоне исходных данных и таблицы.
На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.
Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:
Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и отобразит список полей сводной таблицы .

Щелкните ячейку в диапазоне исходных данных и таблицы.
На вкладке Вставка нажмите кнопку Сводная таблица.
Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

Список полей сводной таблицы

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

Список полей сводной таблицы

Суммировать по

По умолчанию поля сводной таблицы, помещенные в область Значения , будут отображаться в виде СУММ. Если Excel интерпретирует данные как текст, они будут отображаться как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите параметр Параметры поля .

Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число….

Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления

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

Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента

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

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

Советы и рекомендации по форматированию данных

Используйте чистые табличные данные для достижения наилучших результатов.
Упорядочение данных по столбцам, а не по строкам.
Убедитесь, что все столбцы имеют заголовки с одной строкой уникальных, непустых меток для каждого столбца. Избегайте двойных строк заголовков или объединенных ячеек.
Отформатируйте данные как таблицу Excel (выберите любое место в данных, а затем на ленте выберите Вставить > таблицу ).
Если у вас есть сложные или вложенные данные, используйте Power Query для их преобразования (например, для отмены сворачивания данных), чтобы они были упорядочены по столбцам с одной строкой заголовка.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Рекомендации сводной таблицы являются частью работы с подключением в Microsoft 365 и анализируют данные с помощью служб искусственного интеллекта. Если вы решили отказаться от работы с подключением в Microsoft 365, ваши данные не будут отправляться в службу искусственного интеллекта, и вы не сможете использовать рекомендации сводной таблицы. Дополнительные сведения см. в заявлении о конфиденциальности Майкрософт.

Статьи по теме

Создание сводной диаграммы

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

Создание временной шкалы сводной таблицы для фильтрации дат

Создание сводной таблицы с моделью данных для анализа данных в нескольких таблицах

Создание сводной таблицы, подключенной к наборам данных Power BI

Упорядочение полей сводной таблицы с помощью списка полей

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

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

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

Источник

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

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

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

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

4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.

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

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

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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

Скачать файл с примером.

Поделиться в социальных сетях:

Источник

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

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

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

Эта сводная таблица использовалась для сведения длинного списка клиентских счетов в один, легко читаемый отчёт.

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

Что такое сводная таблица?

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

Вот простой пример: у вас есть большой список клиентов, которым вы выставили счета, как часть внештатной работы. Каждая строка представляет собой проект или продукт, на который мы выставили клиенту счёт. В конце года, как я смогу просмотреть проделанную работу и увидеть, как я провел своё время?

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

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

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

Сводная таблица в Excel, показан пример счетов клиентов.

Сводная таблица, показанная справа является отличным способом подытоживания данных. Я показал каждого клиента «Client» в отдельной строке и подвел итоги «amount billed».

Обратите внимание, что в исходной таблице я выполнил несколько разных видов работы для клиента под «Schoen-Jacobson». Сводная таблица подводит итог этих счетов в той же строке, что очень удобно.

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

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

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

Как создать сводную таблицу Excel (смотрите и учитесь)

Когда-нибудь, вам придётся рассмотреть инструмент «сводные таблицы» в действии и задумаетесь о том, как его использовать. Ознакомьтесь с видео ниже, чтобы узнать, как быстро создать сводную таблицу в Excel:

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

1. Подготовьте сводную таблицу Excel

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

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

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

2. Создайте сводную таблицу в Excel

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

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

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

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

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

3. Перетащите поля

После создания сводной таблицы, Excel перекинет вас на новый лист для создания сводной таблицы. Я думаю, об этом листе, как о конструкторе отчётов. Вот как читать это меню:

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

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

Конструктор отчётов сводных таблиц Excel.

Начнем с перетаскивания Client в область Стро́ки. Нажмите и перетащите Client из списка полей в пустую область под надписью Строки.

Когда мы перетаскиваем Client в область Стро́ки (Rows), сводная таблица Excel помещает каждого клиента в отдельную строку.

Вот, у нас что-то получается! Так как мы поместили поле Clients в область Строки, сводная таблица размещает каждого клиента в отдельную строку.

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

На скриншоте ниже ещё один пример. Я перетащил Project Type в область Столбцы. Теперь наша сводная таблица Excel показывает каждый тип проекта из наших исходных данных в качестве отдельного столбца.

На этот раз я перетащил Project Type в область столбцов. Теперь, каждый тип проекта показывается в отдельном столбце.

Я отменю эти изменения и верну обратно поле Client в область Стро́ки.

Что, если мы хотим узнать, на сколько мы выставили счёт каждому клиенту? Помните что у нас в исходных данных в столбце Amount Billed (Сумма выставленных счетов). Нам также нужно добавить эти суммы в таблицу.

Поскольку мы хотим видеть эти суммы в виде числовых значений, перетащите Amount Billed в область Значения (н правый ижний угол).

Количество клиентов Client в сводной таблице Excel.

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

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

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

После того, как вы перебросили Amount Billed в область Значения, кликните правой кнопкой мыши по данным и выберите Итоги по > Сумма, чтобы подсчитать общую сумму на клиента, вместо подсчета количества раз.

Мы создали нашу первую полезную сводную таблицу, суммируя суммы, на которые выставили клиентам счета. Теперь давайте добавим второе поле, чтобы увидеть ещё более подробную информацию.

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

Для этого давайте возьмем поле Project Type и перетащим его в область Строки, поместите его над Client.

Project Type добавлен в сводную таблицу Excel.

Теперь, в нашей сводной таблице есть два типа информации: сумма, на которую мы поработали над каждым типом проекта (Accounting, Legal Work, Screencasts, Training и Tutorials); а так же показаны клиенты для каждого вида работ. Так как мы добавили два типа данных в сводную таблицу Excel, то мы можем увидеть «лучших» клиентов по каждому виду работ.

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

Повторение — мать учения!

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

У Боба Флиссера есть расширенное руководство по сводным таблицам, которое научит вас такому навыку, как Объединение данных из нескольких листов.
Если вам нужно очистить таблицу перед созданием сводной таблицы, вот как найти и удалить дубликаты — How to Find and Remove Duplicates.
Если вы хотите более подробно ознакомиться с функциями Excel, ознакомьтесь с нашей серией Как создавать и использовать формулы в Excel.

У вас есть вопросы о том, как использовать сводную таблицу? Если так — дайте мне знать в комментариях ниже.

Источник

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

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

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

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

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

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

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

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

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

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

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

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

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

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

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

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

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

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

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

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

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

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

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

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

В параметрах полей значений вы найдете множество вариантов для анализа.

Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

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

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

Также можно выбрать и значения для отдела.

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

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

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

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».

Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

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

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».

Выберите все команды.

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.

Щелкайте «Далее» и создавайте на новом листе.

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

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.

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

Как видите, у нас одно значение в соответствующей области.

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

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

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

Сжатая форма

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

Структурированная форма

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

Табличная форма

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

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

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

Отличного Вам дня!

Источник

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

Содержание

Как создается сводная таблица в Excel
Классический способ составления сводной таблицы
Использование Мастера сводных таблиц
Как сделать сводную таблицу из нескольких таблиц
Как сделать сводную таблицу в Excel из нескольких листов
Важные моменты, которые нужно учитывать при формировании сводных таблиц
Плюсы сводных таблиц
Поля сводной таблицы
Настройка внешнего вида таблицы
Как работать со сводными таблицами в Excel
Как в сводную таблицу Excel добавить столбец или таблицу
Обновление данных в сводной таблице в Excel
Заключение

Как создается сводная таблица в Excel

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

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

Рассмотрим два метода создания табличного отчета.

Классический способ составления сводной таблицы

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

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

Нажимаем на одну из ячеек источника и открываем вкладку «Главная» в верхней части экрана. Нужно найти раздел «Стили», а в нем – функцию «Форматировать как таблицу». Выбираем понравившийся стиль.

На экране возникнет окошко, куда требуется добавить диапазон данных. Обычно строка уже заполнена, остается проверить координаты, поставить галочку в графе «Таблица с заголовками» и нажать «ОК».

На панели инструментов появится вкладка «Конструктор таблиц». Она будет возникать в верхней части экрана каждый раз при выборе ячеек отформатированной таблицы. Программа дает таблице имя, его всегда можно изменить. Переходим к основному этапу – составлению табличного отчета:

Необходимо открыть вкладку «Вставка», кликнуть по пункту «Сводная таблица» в левой части экрана.

На экране появится окошко для составления сводной таблицы. Выбираем диапазон данных, из него будет создан отчет. Сначала в строке появится имя первой таблички – если есть необходимость, можно выбрать другие ячейки или указать имя другой таблицы из того же документа.
Выберем место, где будет размещена сводная таблица. Ее можно поместить на тот же лист или на новый в одном документе с начальными данными.
После заполнения всех полей нажимаем «ОК».

Откроется окно для формирования таблички. В нем находится список полей и области настроек. В верхней части окна выбираем нужные поля. После этого перетаскиваем их в нужные области.

В заданной ситуации нужны несколько полей. «Пол» и «Вид спорта» попадают в категорию «Фильтры», «Наименование» – в область «Строки», «Сумма» перемещается в «Значение». Раздел «Столбцы» остается пустым.

Когда таблица сформирована, нужно применить выбранные фильтры. Разберем этот этап по шагам. Стоит вспомнить условие: нужно определить продажи женских теннисных кроссовок.

Открываем раздел «Пол» в таблице и выбираем «Женский», после этого кликаем «ОК».

Применяем фильтр к виду спорта – согласно условию, необходимо поставить галочку в графе «Теннис» и кликнуть «ОК».

Результат: на листе представлены только запрошенные данные. Информация о сумме в нескольких строчках с одинаковым наименованием суммируется.

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

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

Открываем вкладку «Файл», ее раздел «Параметры». Находим пункт «Панель быстрого доступа», в появившемся списке выбираем пункт «Мастер сводных таблиц и диаграмм». Кнопка «Добавить» станет активной, по ней нужно кликнуть, а после – нажать «ОК».

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

Второй шаг работы с Мастером – определение диапазона данных. Программа автоматически выбирает диапазон, поэтому нужно проверить правильный ли он. Если данные выбраны неправильно, выбираем ячейки вручную. После настройки диапазона нажимаем «Далее».

Определяем, где собираемся разместить сводную табличку. Если выбираете «Существующий лист», укажите конкретный лист из документа.

Заполняем форму сводной таблички по правилам классического метода и выставляем фильтры.

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

Зачастую важные для отчета данные размещены в разных таблицах, и их требуется объединить. Воспользуемся Мастером сводных таблиц и совместим несколько баз данных в одну. Представим, что есть две таблицы с данными о наличии товаров на складе. Нужно воспользоваться инструментами Excel, чтобы объединить их в одну базу данных.

Выбираем ячейку и открываем Мастер сводных таблиц и диаграмм. Если он уже есть на панели быстрого доступа, нужно нажать на соответствующий значок. О том, как добавить инструмент на панель, рассказано в разделе «Использование Мастера сводных таблиц».
Данные находятся в двух источниках, поэтому выбираем пункт «В нескольких диапазонах консолидации». Также нужно выбрать разновидность отчета – «сводная таблица».

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

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

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

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

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

Откройте Мастер сводных таблиц и диаграмм. В окне первого шага нужно выбрать источник данных «в разных диапазонах консолидации» и тип «сводная таблица».
На втором шаге выбираем пункт «Создать поля страницы». Откроется окно выбора диапазонов. Сначала выделяем ячейки с информацией на первом листе и нажимаем кнопку «Добавить». Далее нужно переключиться на следующий лист с данными, выделить их и нажать кнопку «Добавить». После внесения всех ячеек в список выбираем количество полей и их очередность. Если информация добавлена правильно, можно перейти к следующему шагу по кнопке «Далее».

Размещаем табличку на новом листе или на одном из существующих листов и нажимаем «Готово».

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

Следует учесть несколько особенностей, проявляющихся при работе с отчетами в форме таблиц:

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

Важно! Обновить сводную табличку можно через вкладку «Данные». Для этого выбираем ячейку в таблице и находим на вкладке функцию «Обновить все».

Плюсы сводных таблиц

Отчеты в формате сводных таблиц обладают серьезными преимуществами перед другими типами отчетов в Excel. Рассмотрим каждое из них:

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

Поля сводной таблицы

Обратим особое внимание на один из шагов составления сводной таблички – выбор полей и распределение по областям. Для того чтобы понять метод работы с окном «Поля таблицы», рассмотрим его элементы по отдельности.

В более новых версиях Microsoft Excel окно выглядит немного иначе, но функции сохраняются.

Форматы окна «Поля таблицы». В меню можно выбрать, какие разделы будут показаны на экране.
Список полей, которые добавляются в отчет.
В поле «Фильтры» нужно переместить показатели для дальнейшей фильтрации данных.
Поле «Столбцы» должно содержать указания о том, какие данные вывести в столбцах.
Назначение области «Строки» почти то же, что и у области «Столбцы» – указываем данные, которые будут выведены в строках.
В области «Значения» должны оказаться поля с числовым форматом для вычислений.

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

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

Изменения во внешнем виде сводной таблицы вносятся с помощью инструментов вкладки «Конструктор». Она появляется на панели управления, когда отчет уже составлен. Функции раздела «Макет» позволяют изменить структуру итоговой таблицы. Раздел «Стили сводной таблицы» содержит темы разных цветов, которые применяются к отчету.

Как работать со сводными таблицами в Excel

Бывает недостаточно создать отчет в виде таблицы – требуются дополнительные действия. Рассмотрим несколько способов работы со сводной таблицей, ее структурой и данными.

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

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

Открываем вкладку «Анализ» (в более ранних версиях – «Параметры») и кликаем по кнопке «Источник данных».

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

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

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

Если отчет не переведен в динамический формат, все еще есть способы обновить информацию в нем после внесения изменений в источник. Обновим сводную таблицу вручную. Для этого кликнем по любой ячейке отчета и по пункту «Обновить» в контекстном меню. Также можно воспользоваться кнопкой с тем же названием на вкладке «Параметры»/«Анализ».

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

Выбираем любую ячейку отчета и открываем вкладку «Параметры»/«Анализ». Находим пункт «Сводная таблица» – в нем нужно выбрать пункт «Параметры».

Находим в настройках пункт «Разметка и формат». Этот раздел состоит из нескольких пунктов. Требуется поставить галочки в графах, показанных на изображении ниже, и нажать кнопку «ОК».

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

Содержание

Видеоурок: Как создать сводную таблицу в Excel
Что такое сводные таблицы в Excel? Пошаговая инструкция
Как сделать сводную таблицу в Excel
Области сводной таблицы в Excel
Что такое кэш сводной таблицы
Область «Значения»
Область «Строки»
Область»Столбцы»
Область «Фильтры»
Сводные таблицы в Excel. Примеры
Пример 1. Какой объем выручки у региона Север?
Пример 2. ТОП пять клиентов по продажам
Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

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

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

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

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

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

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

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

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

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

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

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

Выделите любую ячейку в таблице с данными;
Нажмите на вкладку «Вставка» => «Сводная таблица»:

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

Нажмите «ОК».

После нажатия кнопки «ОК» таблица будет создана.

После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.

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

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

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

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

Кэш
Область «Значения»
Область «Строки»
Область «Столбцы»
Область «Фильтры»

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

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

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

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

Область «Значения»

Область «Значения» включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области «Значения».

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

Область «Строки»

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

Область»Столбцы»

Заголовки вверху значений таблицы называются «Столбцы».

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

Область «Фильтры»

Область «Фильтры» используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные «Тип клиента» — «Продуктовый магазин» и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

Больше лайфхаков в нашем Telegram Подписаться

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

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

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

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

В окне «Поля сводной таблицы» размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» — это место, где вы размещаете элементы таблицы.

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

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

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

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

создать сводную таблицу и поле «Регион» перенести в область «Строки»;
поле «Выручка» разместить в области «Значения»
задать финансовый числовой формат ячейкам со значениями.

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

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

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

переместить поле «Клиент» в область «Строки»;
поле «Выручка» разместить в области «Значения»;
задать финансовый числовой формат ячейкам со значениями.

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

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

кликните правой кнопкой на любой из строчек с данными выручки;
перейдите в меню «Сортировка» => «Сортировка по убыванию»:

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

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

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

Для этого:

поместим поле «Регион» в область «Строки»;
поместим поле «Клиент» в область «Строки» под поле «Регион»;
зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента «Регион» и «Клиент» в области «Строки» друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

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

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

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

кликните правой кнопкой на любой из строчек с данными выручки;
перейдите в меню «Сортировка» => «Сортировка по убыванию»:

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

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле «Регион» в область «Фильтры» и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

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

Источник

Что такое сводная таблица Excel

Что такое сводная таблица (Pivot Table – англ.)? Pivot Table дословно переводится как «таблица, которую можно крутить, показывать в разных разворотах». Это инструмент, который позволяет представлять данные в виде, удобном для анализа. Вид сводной таблицы можно быстро менять с помощью одной только мышки, помещая данные в строки или столбцы, выбирать уровни группировки, фильтровать и «перетаскивать» мышкой столбцы с одного места на другое.

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

Отчет о динамике и структуре продаж
Анализ исполнения бюджета (БДР)

Исходные данные для сводной таблицы

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

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

Шаг 1. Выделить таблицу Excel

Выделите одну ячейку таблицы (тогда Excel автоматически определит границы таблицы на следующем шаге) или выделите всю таблицу вместе с заголовками.

Как быстро выделить таблицу:

Выбрать ее любую ячейку и нажать Crtl + * или Ctrl + A, или
Выбрать самую первую ячейку в таблице, зажать кнопки Ctrl и Shift, а затем нажать на кнопки вправо, затем вниз (→↓).

Если выделить больше одной ячейки, но не всю таблицу, в качестве источника данных будет захвачена только выделенная область.

Шаг 2. Создать сводную таблицу

Создайте сводную таблицу: перейдите на вкладку Вставка и выберите «Сводная таблица».

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

Когда сводная таблица добавлена, на листе появляется область сводной таблицы. Если эта область не активна (вы не выделили ее мышкой), на ней будет подсказка: «Чтобы начать работу с отчетом сводной таблицей, щелкните в этой области». Щелкаем по ней мышкой и происходят две вещи:

Справа появится список полей сводной таблицы.
В меню — две дополнительные вкладки, связанные с управлением сводной таблицей (Анализ и Конструктор).

Шаг 3. Добавить в сводную таблицу необходимые поля

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

Правило следующее: если поле содержит текст или числа хотя бы с одной пустой или текстовой ячейкой, то Excel автоматически поместит эти данные в область «Названия строк».

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

Поля можно «перетаскивать» мышкой из одной области в другую:

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

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

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

Если изменился сам источник данных (добавлены новые строки или столбцы), выберите любую ячейку сводной таблицы и перейдите в меню Анализ → Источник данных.

В появившемся окне выберите источник данных.

Один из оптимальных способов задать источник данных – это указать в качестве него «умную» smart-таблицу Excel. О преимуществах этого способа и вообще о плюсах использования «умных» таблиц читайте в следующей статье.

Резюме

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

То, что написано о сводных таблицах в статье — это еще не все. Для «продвинутой» аналитики в Excel есть надстройки и инструменты, которые расширяют функционал сводных таблиц до действительно серьезного уровня и переносят его в область Business Intelligence. Сейчас мы говорим о надстройках Power Query и Power Pivot, которые также задействованы в продукте Microsoft Power BI.

Источник

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

Для чего в Excel нужны сводные таблицы

В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.

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

Большинство регулярных пользователей Excel даже не догадываются о наличии такого мощного инструмента. Для наглядности приведём небольшой и показательный пример использования сводных таблиц Excel «для чайников».

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

Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.

В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!

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

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

Допустим, у нас есть исходная таблица продаж:

Рассмотрим, как на основании этих табличных данных создать сводную таблицу. Для этого выделяем любую ячейку, после чего в главной панели выбираем вкладку «Вставить». Появится две дополнительные вкладки: «Сводная таблица» и левее – «Рекомендуемые сводные таблицы». Если вы новичок и пока не совсем понимаете, каким образом можно организовать исходные данные, рекомендуем воспользоваться второй кнопкой. Это мастер, который предложит вам готовые варианты отчётов. Вам останется только выбрать подходящий вариант, и получить в своё распоряжение готовую сводную таблицу. Останется только подкорректировать её, если по каким-то критериям она вас не удовлетворяет.

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

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

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

В ней внизу указаны четыре области, на основании которых будет сформирована сводная таблица. Рассмотрим их более подробно:

область значений является главной частью СТ, поскольку именно здесь указывается, каким образом обрабатывать столбцы и строки. Обычно это суммирование – этот способ агрегирования будет установлен по умолчанию при условии, что все ячейки диапазона принимают числовые значения. При наличии хотя бы одной пустой ячейки или с данными в формате, отличном от числового, вместо суммы значений ячеек будет подсчитано их количество, так что этот факт нужно обязательно учитывать. Для нашего примера суммирование будет производиться по каждому товару в соответствующем регионе. Кроме суммирования, можно задать любой другой способ вычислений, которых имеется около двух десятков (вычисление среднего значения, доли и пр.). Проще всего это сделать непосредственно в сводной таблице, кликнув ПКМ по любой ячейке и выбрав необходимый способ вычислений;
область строк – состоит из уникальных значений ячеек, расположенных в левом столбце. Если вставить сюда несколько полей, сводная таблица будет уже не плоской, а многоуровневой;
область столбцов также содержит значения полей, но уже относительно верхней строки исходной таблицы;
область фильтра позволяет накладывать ограничения на условия вычислений значений ячеек. Обычно сюда помещают поле, по которому должна фильтроваться выборка, заданная в трёх других областях.

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

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

Итак, пошаговая инструкция, как сделать сводную таблицу в Excel:

из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
поле «Товар» помещаем в область столбцов.

Всего три действия – и сводная таблица, пригодная для отправки начальству, готова! На её составление у нас ушло порядка 10 секунд.

А как выполнить остальные пожелания директора?

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

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

Чтобы получить результаты по каждому менеджеру, достаточно в область фильтра поместить поле «Менеджер», тогда вверху появится селектор, в котором нужно указать нужную фамилию для получения отчёта. Имеется возможность объединять отчёт по нескольким продавцам.

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

Об источнике данных

Мы уже упоминали, что к исходным данным, используемым для правильного создания сводных таблиц в программе Excel, предъявляются определённые требования. Перечислим их:

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

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

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

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

Обновить данные в нашей сводной таблице Excel можно двумя способами:

кликнув правой кнопкой мыши на любой ячейке и выбрав в контекстном меню пункт «Обновить»;
используя вкладку «Данные» и кнопку «Обновить всё».

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

Добавление в СТ Excel столбца или строки

В целом операция тривиальная, если нужный параметр имеется в исходной таблице. Вам останется только перетащить нужное поле в нужную область. Если же строка или столбец в исходной таблице отсутствует, добавьте её, введите, если нужно, значения. Далее кликаем на вкладке «Анализ» и открываем нашу изменённую таблицу. Останется только обновить данные (см. предыдущий раздел), в результате в правой панели СТ перечень полей изменится – в него будет добавлен новый параметр.

Часто сводная таблица имеет не совсем удобный для визуализации вид. Например, когда в области строк имеется несколько полей. Тогда одно из них выводится в полном объёме, а значения другого нужно указывать в верхней части таблицы. Если таких значений немного (скажем, в нашем примере регионов продаж всего 6), то имеет смысл добавить столбец «Регионы» сводной таблицы в соответствующую область, перенеся его из области строк.

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

Формирование сводной таблицы из нескольких файлов (таблиц) Excel

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

Итак, слева, возле пункта «Файл», жмём на стрелочку, направленную вниз, выбираем пункт «Другие команды». Откроется новое окно, в средней панели щёлкаем на опции «Все команды». Находим в открывшемся перечне «Мастер сводных таблиц», выбираем его и кликаем на кнопе «Добавить». Соответствующий значок появится в верхней панели.

Запускаем мастер. Допустим, нам нужно объединить данные из двух листов, майского и за июнь. Главное условие – они должны совпадать по структуре. Если это так, то в первом окне мастера выбираем опции «Сводная таблица» и следующий параметр – «Несколько диапазонов консолидации».

На втором шаге указываем Мастеру, что нам нужно несколько полей. На третьем – выбираем диапазон ячеек для суммирования по первому листу, жмём «Добавить», и проделываем то же самое для второго листа Excel. Теперь нужно дать имена двум полям. Делаем это сначала для первого поля (жмём селектор «1», в графу «1 поле» вписываем «Май»), затем, по аналогии, для второго. При этом нужно будет указать в списке диапазонов соответствующую строку, первую или вторую.

После нажатия кнопки «Готово» откроется новое окно, в котором указываем «Новый лист» и снова жмём кнопку «Готово».

ВНИМАНИЕ. Получение многомерных таблиц неудобно тем, что в ней сложно ориентироваться при большом количестве полей, да и нужные расчёты приходится проводить, каждый раз указывая нужные значения из верхней области таблицы.

Как удалить СТ

Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.

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

Для удаления результатов вычислений выполняем следующие шаги:

помечаем мышкой любую ячейку СТ;
выбираем на верхней панели вкладку «Анализ»;
из перечня доступных действий выбираем «Очистить»;
кликаем на пункте «Очистить всё».

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

снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
щёлкаем ПКМ внутри выделенной области;
из контекстного меню выбираем пункт «Скопировать»;
переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».

В итоге сводная таблица будет стёрта с сохранением результатов.

СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.

Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:

выбираем СТ, предназначенную для удаления;
жмём вкладку «Параметры»;
в «Группе действий» выбираем пункт «Очистить», а затем – «Очистить всё».

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

В старых версиях программы для этого нужно выделить диаграмму, щёлкнуть на вкладке «Анализ», выбрать группу данных и нажать последовательно «Очистить» и «Очистить всё».

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

Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.

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

Источник