Список Полей Сводной Таблицы в Excel • Область значений

Содержание

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

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

Создание сводной Эксель таблицы требует соблюдения определенных условий:

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

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

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

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

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

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

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

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

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

Самое рациональное решение – это создание сводной таблицы в Excel:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

Список Полей Сводной Таблицы в Excel • Область значений Список Полей Сводной Таблицы в Excel • Область значений Список Полей Сводной Таблицы в Excel • Область значений

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

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

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

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

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дальше выбираем, с какой по какую дату хотим выполнить группировку, и с каким шагом.

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

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

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

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

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

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

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

Теперь, смотря на таблицу, можно понять, что Рома в феврале продал две рубашки.

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата», в «Названия столбцов» – «Продавец».

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

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

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Данная возможность является одним из мощнейших инструментов excel, не забывайте про него, когда столкнетесь с необходимостью серьезного анализа. Если же вы хотите что-то уточнить, обращайтесь ко мне!
В этой области происходят все расчеты исходных данных. На рисунке область значений выделена красным прямоугольником. На этом примере здесь отображены основные итоговые показатели, разбитые по федеральным округам.
sozday svodnayu tablicu 12 Как создать сводную таблицу в Excel

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

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

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

Excel для бизнеса: как строить сводные таблицы

cover.exel-60896ee101699579267744.jpg

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

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

Как должны выглядеть источники данных

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

На основании этих данных и будем строить сводный отчет.

Сначала убедитесь, что исходные данные правильно оформлены:

#1. Каждый столбец должен содержать единый тип данных — даты должны находиться в поле с датами и иметь формат дат. В поле «Наименование» не должно быть информации о городе покупки.

#2. Не желательно оставлять пустые строки. Сводная таблица будет построена, но визуально наличие строк «(пусто)» неприятно. Их можно убрать с помощью фильтров.

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

Выделите любую ячейку уже «умной» таблицы и дайте ей подходящее имя.

Список Полей Сводной Таблицы в Excel • Область значений

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

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

Список Полей Сводной Таблицы в Excel • Область значений

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

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

Список Полей Сводной Таблицы в Excel • Область значений

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

#2. Использование внешних источников данных — это могут быть подключения через встроенную в Excel (начиная с версии 2013 года) среду Power Query или с помощью других надстроек. В нашем случае нет потребности использовать эту функцию.

#3. Выбираем лист, где будем создавать таблицу.

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

#1. «Анализ сводной таблицы». Панель инструментов, в которой можно найти дополнительные функции обработки таблицы и форматирования данных.

#2. «Конструктор». Здесь можно подобрать другой стиль таблицы или создать свой, настроить тип отображения данных, итоговые поля и другие полезные функции.

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Ничего сложного в данном варианте нет, пробуйте экспериментируйте, и вы улучшите свои практические навыки создания сводной таблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Вот мы получили и наш первый результат, но он нас не устраивает так как у нас не суммируется количество фруктов которые были проданы, а значит, нам нужно с области «СТРОКИ» перетянуть заголовок столбца «Вес, кг» и у нас создаётся та конструкция сводной таблицы, которую мы хотим.

Как создать сводную таблицу в Excel | Понятный Excel

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

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

Сводные таблицы в Excel: Создание, Фильтрация, Форматирование

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

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

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

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

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

1

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

3

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

Список Полей Сводной Таблицы в Excel • Область значений

4

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

Список Полей Сводной Таблицы в Excel • Область значений

5

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

Список Полей Сводной Таблицы в Excel • Область значений

6

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

Список Полей Сводной Таблицы в Excel • Область значений

7

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

8

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

Список Полей Сводной Таблицы в Excel • Область значений

9

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

Список Полей Сводной Таблицы в Excel • Область значений

10

Список Полей Сводной Таблицы в Excel • Область значений

11

Подготовка исходной таблицы

  • каждый столбец должен иметь заголовок;
  • в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
  • в таблице должны отсутствовать полностью незаполненные строки и столбцы;
  • в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
  • избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Список Полей Сводной Таблицы в Excel • Область значений

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

Список Полей Сводной Таблицы в Excel • Область значений

  • Товар – наименование партии товара, например, « Апельсины »;
  • Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
  • Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

12

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

Список Полей Сводной Таблицы в Excel • Область значений

13

Список Полей Сводной Таблицы в Excel • Область значений

14

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

15

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

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

16

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

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

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

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

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

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

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

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

Изменение формата числовых значений

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Список Полей Сводной Таблицы в Excel • Область значений

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

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

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

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

Список Полей Сводной Таблицы в Excel • Область значений

19

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

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

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

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

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

Фильтрация полей отчета

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

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

Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.

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

Список Полей Сводной Таблицы в Excel • Область значений

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

Как быстро сделать сводную таблицу в excel?
Сводные таблицы в Excel позволяют проанализировать данные, которые находятся в одной большой таблице. Причем исходная таблица, которая будет использоваться для анализа, может быть создана как в Excel, так и в другой базе данных, или другом документе.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Чтобы сделать сводную таблицу, выделяем любую ячейку из таблицы исходной, затем переходим на вкладку Вставка и нажимаем на кнопочку Сводная таблица. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Как строить сводные таблицы для бизнеса в Excel | Бизнес-школа LABA (ЛАБА)

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector