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

Сводные таблицы в Excel. Примеры и описание Аналитика
Содержание
  1. Анализ «что-если» в excel: «таблица данных»
  2. Анализ предприятия в excel: примеры
  3. Анализ статистики с помощью сводных таблиц excel
  4. В чем полезность сводных таблиц
  5. Вложение в сводную таблицу
  6. Временная шкала в сводных таблицах
  7. Выбрав символ «развернуть» или свернуть символ слева от выбранного поля
  8. Группируем числа и даты
  9. Дизайн
  10. Добавление новых полей
  11. Добавление фильтра
  12. Изменение формата числовых значений
  13. Инструменты анализа excel
  14. Инструменты сводной таблицы
  15. Использование analyze на ленте
  16. Как обновлять таблицу
  17. Области сводной таблицы
  18. Подготовка исходной таблицы
  19. Поля сводной таблицы
  20. Проанализируйте
  21. Расширяющееся и разрушающееся поле
  22. Рекомендуемые сводные таблицы
  23. Создание сводной таблицы
  24. Срезы
  25. Стили представления отчетов
  26. Суммирование значений по другим расчетам
  27. Удаление сводной таблицы
  28. Условное форматирование ячеек сводной таблицы
  29. Изменение функции итогов
  30. Заключение
  31. Отключаем строки итогов

Анализ «что-если» в excel: «таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» – «Таблица данных».

Важные условия:

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

https://www.youtube.com/watch?v=-hkXzBOg9Ec

Процедура создания «Таблицы данных»:

  1. Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
  2. Создание Таблицы данных.

  3. Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
  4. Параметры Таблицы данных.

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

Результат анализа.

Анализ предприятия в excel: примеры

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

Анализ статистики с помощью сводных таблиц excel

Plarin позволяет выгрузить статистику по рекламным кампаниям и объявлениям ВКонтакте и myTarget в формате таблиц Excel: 

Обратите внимание: выгрузка в Excel через Plarin работает по принципу “что видно в таблице, то и будет выгружено”, например: если у вас выбрана статистика “за все время”, в выгружаемый отчет попадет статистика за все время, если же выбрано “за последние 2 дня” или любой другой промежуток времени – выгрузка произойдет в соответствии с выбранными датами.

А также: помимо выгрузки всех рекламных кампаний или объявлений из таблицы вы можете выделить только нужные – в этом случае только выбранные РК или объявления попадут в Excel.

Для удобства тех, кто не любит много читать – в конце статьи находится видео инструкция.

Для дальнейшей работы нам понадобятся:

  1. MS Excel – при написании данной инструкции использовался Excel 2021 для Windows, при работе с macOS используйте кнопку Command вместо Ctrl

  2. Выгрузить статистику из Plarin в формате .xls

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

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

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

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

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

Далее выполняйте все действия шаг за шагом.

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

1. Откройте файл с выгрузкой. Встаньте на любую заполненную ячейку. Нажмите сочетание клавиш Ctrl A, затем Ctrl T.

2. Далее выберите пункт Вставка -> Сводная таблица и нажмите ОК.

Таким образом мы преобразовали диапазон данных в таблицу и создали на её основе сводную таблицу.

  1. Область, в которой будут отображаться созданные таблицы.

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

  3. Область, при перетаскивании в которую полей с данными происходит формирование сводной таблицы. Эта область делится на 4 блока:

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

На первом этапе мы создали сводную таблицу на основе выгруженных данных из Plarin.

Далее научимся:

Сформируем первый отчёт и посмотрим статистику по кампаниям и объявлениям:

  1. Перетащите поля Название кампании и ID объявления в блок СТРОКИ.

  2. Перетащите поля Расход, Показы, Клики, Конверсии в блок ЗНАЧЕНИЯ.

Обратите внимание, над данными в блоке ЗНАЧЕНИЯ произвелось суммирование для каждой строки отчёта. Однако кроме суммирования возможны и другие вычисления.

Чтобы привести отчёт в более удобный вид, слегка отформатируем его.

1. Свернём все строки. Выделите любую строку, далее правый клик -> Развернуть/свернуть -> Свернуть все поле.

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

3. Отформатируем значение полей. Приведём Расход в денежный вид, а остальные поля в числовой.

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

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

4. При формировании названий столбцов Excel добавляет в название тип вычисления над значениями. В итоге названия получаются длинными и неудобными. Для переименования встаём на заголовок поля и пишем новые названия столбцов _Расход, _Показы, _Клики, _Конверсии.

В итоге мы получили наш первый отчёт, при этом он отформатирован в удобном для восприятия виде.

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

Для примера создадим следующие метрики:
CPC
– стоимость клика
CR
– коэффициент конверсии
CPA
– стоимость конверсии
CTR
– коэффициент кликабельности объявления.

Создадим метрику CPC, остальные делаются по аналогии:

1. Встаём на любую ячейку отчёта, выбираем в меню Анализ -> Поля, элементы и наборы -> Вычисляемое поле.

2. В появившемся диалоговом окне создаём новую метрику. В терминах Excel эти метрики называются вычисляемыми полями.

Заполняем поля в диалогом окне:

Имя – пишем _CPC (хорошей практикой считается начинать названия вычисляемых полей со знака нижнего подчёркивания, это позволяет отличать вычисляемые поля от обычных).

Формула – пишем формулу вычисления CPC, выбирая нужные поля из блока Поля.

Жмём кнопку Добавить и ОК.

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

Для того, чтобы избавиться от ошибок деления на ноль, поменяйте формулу для вычисляемого поля вот на эту: =ЕСЛИОШИБКА(Расход/Клики;Расход)

Для этого откройте Анализ -> Поля, элементы и наборы -> Вычисляемые поля. В диалоговом окне в поле Имя, в списке выберите наше поле _CPC, далее поменяйте формулу в поле Формула. Нажмите ОК для сохранения изменений.

Далее по аналогии создайте еще несколько вычисляемых полей:

Дополнительный анализ:  Проектное финансирование

__CPC =ЕСЛИОШИБКА(Расход/Клики;Расход)
__CR
=ЕСЛИОШИБКА(Клики /Показы;0), отформатируйте как %
__CPA
=ЕСЛИОШИБКА(Расход /Конверсии;Расход )
__CTR
=ЕСЛИОШИБКА(Клики /Показы;0 ), отформатируйте как %.

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

Далее создадим ещё один отчёт и диаграмму на его основе.

Создадим отчёт и диаграмму, отображающий расход по дням.

1. Вернитесь на лист Sheet1 с исходными данными. По аналогии с первым отчётом создаём новую сводную таблицу. Вставка -> Сводная таблица -> OK.

2. В блок СТРОКИ перетащите поле Дата, а в блок ЗНАЧЕНИЯ перетащите поле Расход.

Мы создали новый отчёт, отображающий расход по датам.

3. Выделив любую ячейку отчёта, открываем меню Вставка – Сводная диаграмма. Выберите вид диаграммы График и нажмите ОК.

В итоге у нас получился график колебания расхода по дням.

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

Выделите диаграмму, далее выберите в меню Анализировать -> Вставить временную шкалу. Далее в диалоговом окне выберите поле Дата.

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

Благодаря группировке по полю Дата вы можете проанализировать данные укрупнёнными периодами.

Кликните по ячейке в поле Дата правой кнопкой мыши, выберите Группировать.

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

Выберем группировку по дням и укажем период 7 дней.

В данном видеоролике демонстрируются все действия, описанные в инструкции выше.
https://www.analitik-expert.ru/watch?v=IoLjpRhemkE

  1. Анализируйте статистику в различных срезах.

  2. Создавайте собственные метрики, которых вам не хватает в стандартной статистике.

  3. Визуализируйте отчёты с помощью диаграмм.

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

  5. Прочтение книги Сводные таблицы в Microsoft Excel 2021 поможет вам стать экспертом по сводным таблицам.

В чем полезность сводных таблиц

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

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

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

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

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

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

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

Вложение в сводную таблицу

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

  • Месяцы в столбцах.
  • Регион и продавец в строках в указанном порядке. т.е. ценности продавца вложены под значениями региона.
  • Суммирование производится по сумме суммы заказа.
  • Фильтры не выбраны.

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

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

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

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

Временная шкала в сводных таблицах

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

Создайте сводную таблицу из этого диапазона с —

  • Расположение и продавец в строках в таком порядке
  • Продукт в колоннах
  • Сумма Суммы в Суммирующих значениях
  • Нажмите на сводную таблицу.
  • Нажмите вкладку INSERT.
  • Нажмите Временная шкала в группе Фильтры. Появятся временные рамки вставки.

Нажмите Дата и нажмите ОК. Появится диалоговое окно «Временная шкала», а на ленте появятся «Инструменты временной шкалы».

  • В диалоговом окне Timeline выберите MONTHS.
  • Из раскрывающегося списка выберите КВАРТАЛЫ.
  • Нажмите 2021 Q2.
  • Держите нажатой клавишу Shift и перетащите на 2021 Q4.

Сроки выбраны для Q2 — Q4 2021.

Сводная таблица фильтруется на эту временную шкалу.

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

Все предметы под Востоком будут свернуты. Свернуть символМинус слева от Востока меняется на символ Развернутьплюс ,

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

Нажмите на символ Расширитьплюс слева от востока. Будут отображены все элементы ниже Востока.

Группируем числа и даты


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

  • Очистим ранее созданный отчет: выделите любое значение

    Сводной таблицы

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

    Сводной таблице

    , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
  • Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь

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

показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

  • Выделите одно значение

    Сводной таблицы

    в столбце Названия строк;
  • В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт

    Группировка по полю

    ;
  • Появившееся окно заполните, как показано на рисунке ниже;

Теперь

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

показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт

Разгруппировать

в меню Работа со сводными таблицами/ Параметры/ Группировать .


Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно

Группировка по полю

будет выглядеть так:

Теперь

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

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

Дизайн

Вот некоторые из команд ленты ДИЗАЙН :

Добавление новых полей

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


Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть

Сводную таблицу

, чтобы отобразить только продажи по Регионам.

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Дополнительный анализ:  Годовые отчеты для Инвесторов - официальный сайт АО «Фармстандарт»

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
  • Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
  • Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся

Сводной таблицы

должен быть таким:

Теперь воспользовавшись

Выпадающим (раскрывающимся) списком

в ячейке

B1

(поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.


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

Обратите внимание, что в Списке полей

Сводной таблицы

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

Также

стандартный механизм фильтрации

данных доступен через выпадающий список в заголовках строк и столбцов

Сводной таблицы.

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


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

Числовой формат


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

Разделитель групп разрядов

.

Инструменты анализа excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Анализ что-если.

Средства анализа «Что-если»:

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

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

Графики и диаграммы.

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

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

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

  • Выберите сводную таблицу.

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

Использование analyze на ленте

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

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

Все элементы поля Восток в сводной таблице свернутся.

Нажмите «Развернуть поле» в группе «Активное поле».

Все предметы будут отображаться.

Как обновлять таблицу

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

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

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

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

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

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

В областях сводной таблицы вы можете выбрать —

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

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

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

Начнем с требований к исходной таблице.

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

    Дата

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

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

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

Вместо того, чтобы плодить повторяющиеся столбцы (

регион 1, регион 2, …

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

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

Советы по построению таблиц

.

Несколько облегчит процесс построения

Сводной таблицы

, тот факт, если исходная

таблица будет преобразована в формат EXCEL 2007

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

Создание

таблицы в формате EXCEL 2007

добавляет новые возможности:


В качестве исходной будем использовать

таблицу в формате EXCEL 2007

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

В таблице имеются столбцы:


  • Товар

    – наименование партии товара, например, «

    Апельсины

    »;

  • Группа

    – группа товара, например, «

    Апельсины

    » входят в группу «

    Фрукты

    »;

  • Поставщик

    – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;

  • Дата поставки

    – Дата поставки Товара Поставщиком;

  • Регион продажи

    – Регион, в котором была реализована партия Товара;

  • Продажи

    – Стоимость, по которой удалось реализовать партию Товара;

  • Сбыт

    – срок фактической реализации Товара в Регионе (в днях);

  • Прибыль

    – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через

Диспетчер имен

(

Формулы/ Определенные имена/ Диспетчер имен

)

откорректируем

Имя

таблицы на «

Исходная_таблица

».

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

Заголовки в вашей таблице данных будут отображаться как поля в сводной таблице.

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

Проанализируйте

Некоторые из команд ленты ANALYZE :

  • Установить параметры сводной таблицы
  • Настройки поля значения для выбранного поля
  • Развернуть поле
  • Свернуть поле
  • Вставить слайсер
  • Вставить временную шкалу
  • Обновить данные
  • Изменить источник данных
  • Переместить сводную таблицу
  • Решить заказ (если есть еще расчеты)
  • PivotChart

Расширяющееся и разрушающееся поле

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

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

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

  • Нажмите на таблицу данных.

  • Нажмите вкладку INSERT.

  • Нажмите «Рекомендуемые сводные таблицы» в группе «Таблицы». Откроется диалоговое окно «Рекомендуемые сводные таблицы».

Нажмите на таблицу данных.

Нажмите вкладку INSERT.

Нажмите «Рекомендуемые сводные таблицы» в группе «Таблицы». Откроется диалоговое окно «Рекомендуемые сводные таблицы».

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

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

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

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

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

Сводную таблицу

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

Имея исходную

таблицу в формате EXCEL 2007

, для создания

Сводной таблицы

достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт

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

.


В появившемся окне нажмем ОК, согласившись с тем, что

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

будет размещена на отдельном листе.

На отдельном листе появится заготовка

Сводной таблицы

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

Структура

Сводной таблицы

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

Заполним сначала раздел

Названия строк

. Т.к. требуется определить объемы продаж по каждому Товару, то в строках

Сводной таблицы

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

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

Названия строк

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

ниже

).

Теперь поставим галочку в Списке полей у поля Продажи.

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

Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью

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

).

Срезы

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

  • Нажмите АНАЛИЗ в разделе СРЕДСТВА СЧЕТА на ленте.

  • Нажмите Вставить слайсер в группе Фильтр. Откроется окно «Вставить слайсеры». Он содержит все поля из ваших данных.

  • Выберите поля Регион и месяц. Нажмите ОК.

Нажмите АНАЛИЗ в разделе СРЕДСТВА СЧЕТА на ленте.

Нажмите Вставить слайсер в группе Фильтр. Откроется окно «Вставить слайсеры». Он содержит все поля из ваших данных.

Выберите поля Регион и месяц. Нажмите ОК.

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

  • Выберите Юг и Запад в слайсере для региона.
  • Выберите февраль и март в слайсере на месяц.
  • Удерживайте нажатой клавишу Ctrl при выборе нескольких значений в слайсере.

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

Стили представления отчетов

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

  • Нажмите Восток в сводной таблице.
  • Нажмите АНАЛИЗ.
  • Нажмите Настройки поля в группе Активные поля. Откроется диалоговое окно «Настройки поля».
  • Перейдите на вкладку «Макет и печать».
  • Установите флажок Вставить пустую строку после каждого ярлыка элемента.

Пустые строки будут отображаться после каждого значения поля Region.

Вы также можете вставить пустые строки на вкладке ДИЗАЙН .

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

Сводная таблица в Outline Form с выбранным стилем будет отображаться.

Суммирование значений по другим расчетам

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

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

  • Выберите поле Учетная запись.
  • Отмените выбор суммы заказа поля.
  • Перетащите поле Учетная запись в область «Суммирующие значения». По умолчанию будет отображаться сумма счета.
  • Нажмите на стрелку на правой стороне окна.
  • В появившемся раскрывающемся списке щелкните «Параметры поля значений».

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

  • Выберите Количество в списке.
  • Пользовательское имя автоматически меняется на Количество аккаунтов. Нажмите ОК.

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

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

Удалить

Сводную таблицу

можно несколькими способами. Первый – просто удалить лист со

Сводной таблицей

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

Сводную таблицу

: выделите любую ячейку

Сводной таблицы

, нажмите

CTRL

A

(будет выделена вся

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

), нажмите клавишу

Delete

.

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

К ячейкам

Сводной таблицы

можно применить правила

Условного форматирования

как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

  • Выделите все ячейки содержащие значения продаж;
  • Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов ;
  • Нажмите ОК.

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

При создании

Сводной таблицы

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

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

Сводной таблице

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


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

Теперь немного модифицируем наш

Сводный отчет

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

Сортировка от Я до А

.

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


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

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

Заключение

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

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

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

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

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

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку

Сводной таблицы

.

Оцените статью
Аналитик-эксперт
Добавить комментарий

Adblock
detector