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

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

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

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

1. Данные вставляются в таблицу со столбцами и списками с именами.
2. Отсутствие пустых форм.
3. Отсутствие скрытых предметов.

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

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

Чтобы создать сводную таблицу, вам необходимо:

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

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



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

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



Разместим категорию «Товары» в виде строк. Переносим обязательное поле в «Названия строк.



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

Столбец «Единицы», находящийся в основной таблице, показывает количество товаров, проданных конкретным продавцом по определенной цене.



Например, чтобы просмотреть продажи за каждый месяц, вам нужно заменить поле «Дата» на «Имена столбцов». Выберите команду «Группировать», щелкнув дату.



Указываем периоды даты и шага. Подтверждаем выбор.

Посмотрим вот такую ​​таблицу.



Переносим поле «Сумма» в область «Значения».



Отображение числа стало видимым, но нам нужен именно формат чисел



Для ее решения выделите ячейки, вызовите окно мышкой, выберите «Формат числа».

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

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

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



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



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



Также для сокращения данных в поле выберите «Количество». Подтверждаем выбор.



Взгляните на стол. На нем четко видно, что за один из месяцев продавец продал 2 рубашки.



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

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

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

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

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

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

Сводная таблица в Excel

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

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

Наиболее рациональное решение — создать сводную таблицу в Excel:

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

Просто, быстро и качественно.

Важные нюансы:

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

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

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

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

Создадим отчет с помощью мастера сводных таблиц:

1. Вызовем меню «Мастер сводных таблиц и графиков». Для этого нажмите кнопку настроек панели быстрого доступа и нажмите «Дополнительные команды». Здесь, на вкладке «Настройки», мы находим «Мастер сводной таблицы». Добавьте инструмент на панель быстрого доступа. После добавления:
2. Поместите курсор на первую тарелку и нажмите инструмент «Мастер». В открывшемся окне отметьте, что мы хотим создать таблицу в «нескольких интервалах консолидации». То есть мы должны совмещать разные места с информацией. Тип отчета — «сводная таблица». «Дальше».
3. Следующим шагом будет «создание полей». «Дальше».
4. Прописываем диапазон данных, по которым будем формировать консолидированный отчет. Выбираем первый диапазон вместе с заголовком — «добавить». Второй диапазон, вместе с именами столбцов, снова «добавить».
5. Теперь выберите первый диапазон в списке. Объединим птицу в единство. Это первое поле в итоговом отчете. Даем ему название: «Магазин 1». Выберите второй диапазон данных: поставьте галочку рядом с цифрой «2». Название поля — «Магазин 2». Нажмите «Далее».
6. Выберите, где разместить сводную таблицу. На существующем или новом листе. Лучше выбрать новый лист, чтобы не было нахлестов и смещений. Получилось вот так:

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

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

Начнем с самого простого: добавления и удаления столбцов. Например, рассмотрим сводную таблицу продаж для разных отделов (см. Выше).

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

Добавим еще одно поле отчета в сводную таблицу. Для этого поставьте галочку напротив «Дата» (или напротив «Продукт»). Соотношение меняется сразу: динамика продаж за день появляется в каждом отделе.

Группируем данные в отчете по месяцам. Для этого щелкните правой кнопкой мыши поле «Дата». Щелкните «Группа». Выбираем «по месяцам». Получается такая сводная таблица:

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

И вот что произойдет, если мы удалим «дату» и добавим «отдел»:

Но такую ​​связь можно сделать, перетаскивая поля между разными областями:

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

Ставим поле «Отдел» перед наименованиями товаров. С помощью раздела меню «в начало».

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

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

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

Проверка правильности выставленных коммунальных счетов

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

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

Например, мы создали сводную таблицу тарифов по Москве:

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

Первый столбец = первый столбец сводной таблицы. Вторая — это формула для расчета формы:

= тариф * количество человек / показания счетчиков / площадь

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

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

Скачать все примеры сводных таблиц

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

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

На рисунке показан описанный мною принцип. Этот диапазон A2: E5 содержит исходную сводную таблицу, которая преобразуется в список данных (диапазон H2: J14). Вторая таблица представляет тот же набор данных, только с другой точки зрения. Каждое значение в исходной сводной таблице отображается в виде строки, состоящей из элемента поля строки, поля столбца и соответствующего значения. Это представление данных полезно, когда вам нужно сортировать данные и управлять ими другими способами.

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

Перейдите на вкладку Файл -> Параметры. В появившемся диалоговом окне «Параметры Excel» на вкладке панели быстрого доступа в левом поле найдите элемент «Мастер сводных таблиц и диаграмм» и добавьте его справа. Щелкните ОК.

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

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

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

На шаге 2a укажите, как вы хотите создать поля страницы. Поместите переключатель Create Page Fields -> Next.

На шаге 2b в поле «Диапазон» выберите диапазон, содержащий данные, и нажмите «Добавить». В нашем случае это будет расположение исходной сводной таблицы A1: E4.

На третьем шаге вам нужно решить, где разместить сводную таблицу, и нажать «Готово.

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

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

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

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