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

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

Подготовка

Чтоб сделать сводную таблицу, следует осознавать, что она представляет собой инструмент для работы с данными. Для начальных данных нужно сделать отдельную таблицу в Microsoft excel.

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

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

Создание

Сделать сводную таблицу можно в программках разных версий (2010-2016). Рассматриваемая {инструкция} сотворена на базе Майкрософт Эксель 2013.

В запущенной программке нужно перейти во вкладку «Вставка» и избрать пункт «Сводная таблица».

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

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

Направьте внимание! Данным методом нереально консолидировать (соединить) несколько таблиц.

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

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

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

Направьте внимание! Если перед сиим шагом произвести выделение таблицы, то все поля заполнятся автоматом.

Наполнение

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

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

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

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

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

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

Трудности при работе

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

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

Обновление данных

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

Для того, чтоб получить обновление сходу всей конструкции, требуется зайти во вкладку «Анализ» и избрать строчку «Обновить все».

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

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

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

Интересно почитать: Экспорт данных из эксель в эксель

Система собранная с нескольких листов

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

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

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

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

Опосля рекомендуется избрать пункт «Сделать одно поле».

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

Крайний шаг, выбор расположения новейшего объекта.

Опосля проделанной работы на новейшей страничке покажется непростая сводка.

Удаление

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нажмите “ОК”.

Опосля нажатия клавиши “ОК” таблица будет сотворена.

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

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

Объединение листов различных рабочих книжек в одну

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

Интересно почитать: Закрылся эксель без сохранения как восстановить

Надстройка по объединению разных файлов в один сотворена на базе макроса VBA, но прибыльно различается от него удобством в использовании. Надстройка просто подключается и запускается одним нажатием клавиши, выведенной прямо в основное меню, опосля что возникает диалоговое окно. Дальше все интуитивно понятно, выбираются файлы, выбираются листы этих файлов, выбираются доп характеристики объединения и нажимается клавиша “Запуск”.

макрос (надстройка) для объединения нескольких файлов Excel в одну книжку

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать файлы для объединения, также редактировать перечень избранных файлов;

3. соединять воединыжды все листы избранных файлов в одну рабочую книжку;

4. соединять воединыжды в рабочую книжку лишь непустые листы избранных файлов;

5. собирать в итоговую книжку листы с данным именованием (можно употреблять маску с помощью спец. знаков совпадения);

6. собирать в одну книжку листы избранных файлов с определенным номером (индексом), или спектром номеров;

7. собирать листы с определенным значением в данном спектре ячеек;

8. задавать доп характеристики для объединения, такие как:

а) присвоение листам имен объединяемых файлов;

б) удаление из книжки, в которой происходит объединение данных, собственных листов, которые были в данной нам книжке вначале;

в) подмена формул значениями (плодами вычислений).

По мере необходимости имена листов сформированной рабочей книжки можно стремительно поменять с помощью надстройки для автоматического переименования листов.

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

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

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

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

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

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

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

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

Область “Значения”

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

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

Область “Строчки”

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

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

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

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

Область “Фильтры”

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

Интересно почитать: Как в excel сделать галочку

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

кликните правой клавишей на хоть какой из строчек с данными выручки;
перейдите в меню “Сортировка” => “Сортировка по убыванию”:

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

Изменение функции итогов

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

Изменение порядка сортировки

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

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