Использование умных таблиц в Microsoft Excel


Умные Таблицы Excel – секреты эффективной работы

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

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

Как создать Таблицу в Excel

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

Обычный диапазон данных

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

Создать таблицу Excel с ленты

Есть горячая клавиша Ctrl+T.

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

Создание таблицы Excel

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

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

Структура и ссылки на Таблицу Excel

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

Вкладка Конструктор для таблицы Excel

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

Таблица в диспетчере имен

А также при наборе формулы вручную.

Таблица в подсказке при наборе формулы

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

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбор элемента таблицы в формуле

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

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

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

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

Ссылка на столбец таблицы

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

Заголовки таблицы Excel

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Заголовки таблицы всегда на экране

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

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

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

5. Новые столбцы также автоматически включатся в Таблицу.

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

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

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

Настройки Таблицы

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

С помощью галочек в группе Параметры стилей таблиц

Настройка Таблицы Excel

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

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

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

Стили Таблицы

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

Инструменты Таблицы Excel

Однако самое интересное – это создание срезов.

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

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

Выбор столбцов для среза

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

Срез Таблицы Excel

Для фильтрации Таблицы следует выбрать интересующую категорию.

Фильтрация Таблицы с помощью среза

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

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

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

Параметры среза

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

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

Множество других секретов Excel вы найдете в онлайн курсе.

Преимущества сводной по Модели Данных

Построение сводной по модели данных

При построении сводной таблицы в Excel в первом же диалоговом окне, где нас просят задать исходный диапазон и выбрать место для вставки сводной, есть внизу неприметная, но очень важная галочка — Добавить эти данные в Модель Данных (Add this data to Data Model) и, чуть выше, переключатель Использовать модель данных этой книги (Use Data Model of this workbook) :

Новые статьи:  Расчет НДС в Microsoft Excel

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

Что такое Модель Данных

  • Размер таблиц может достигать 2 млрд. строк, а на лист Excel вмещается чуть больше 1 млн.
  • Не смотря на гигантские размеры, обработка таких таблиц (фильтрация, сортировка, вычисления по ним, построение сводных и т.д.) выполняются очень быстро — гораздо быстрее, чем в самом Excel.
  • С данными в Модели можно производить дополнительные (при желании — весьма сложные) вычисления с помощью встроенного языка DAX.
  • Вся информация, загруженная в Модель Данных, очень сильно сжимается с помощью специального встроенного архиватора и весьма умеренно увеличивает размер исходного Excel-файла.

Управлением Моделью и вычислениями по ней занимается специальная встроенная в Microsoft Excel надстройка — Power Pivot, о которой я уже писал. Чтобы её включить, на вкладке Разработчик нажмите кнопку Надстройки COM (Developer — COM Add-ins) и поставьте соответствующую галочку:

Подключаем надстройку Power Pivot

Если вкладки Разработчик (Developer) у вас на ленте не видно, то включить её можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) . Если же в показанном выше окне в списке COM-надстроек у вас нет Power Pivot, то значит она не входит в вашу версию Microsoft Office 🙁

На появившейся вкладке Power Pivot будет большая салатового цвета кнопка Управление (Manage) , нажатие на которую и откроет поверх Excel окно Power Pivot, где мы и увидим содержимое Модели Данных текущей книги:

Главное окно Power Pivot

Важное замечание по ходу: книга Excel может содержать только одну Модель Данных.

Грузим таблицы в Модель Данных

Для загрузки данных в Модель сначала превращаем таблицу в динамическую «умную» сочетанием клавиш Ctrl + T и даём ей понятное имя на вкладке Конструктор (Design) . Это обязательный этап.

Затем можно использовать любой из трех способов, на выбор:

  • Жмём кнопку Добавить в модель (Add to Data Model) на вкладке Power Pivot на вкладке Главная (Home) .
  • Выбираем команды Вставка — Сводная таблица (Insert — Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model) . В этом случае по загруженным в Модель данным сразу строится ещё и сводная таблица.
  • На вкладке Данные (Data) жмём на кнопку Из таблицы/диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query. Этот путь самый долгий, но, при желании, здесь можно произвести дополнительную зачистку данных, правки и всяческие трансформации, в которых Power Query очень силён.
    Затем причёсанные данные выгружаются в Модель командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. ) . В открывшемся окне выбираем вариант Только создать подключение (Only create connection) и, главное, ставим галочку Добавить эти данные в Модель данных (Add this data to Data Model) .

Строим сводную по Модели Данных

Чтобы построить сводную Модели Данных можно использовать любой из трёх подходов:

  • Нажать кнопку Сводная таблица (Pivot Table) в окне Power Pivot.
  • Выбрать в Excel команды Вставка — Сводная таблица и переключиться в режим Использовать модель данных этой книги (Insert — Pivot Table — Use this workbook’s Data Model) .
  • Выбираем команды Вставка — Сводная таблица (Insert — Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model) . Текущая «умная» таблица будет загружена в Модель и по всей Модели будет построена сводная таблица.

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

Преимущество 1. Связи между таблицами без помощи формул

Обычная сводная может быть построена только по данным из одной исходной таблицы. Если же у вас их несколько, например, продажи, прайс, справочник по клиентам, реестр договоров и т.д., то сначала придется собирать данные из всех таблиц в одну с помощью функций типа ВПР (VLOOKUP) , ИНДЕКС (INDEX) , ПОИСКПОЗ (MATCH) , СУММЕСЛИМН (SUMIFS) и им подобных. Это долго, муторно и вгоняет ваш Excel в «задумчивость» при большом количестве данных.

В случае сводной по Модели Данных всё гораздо проще. Достаточно один раз настроить связи между таблицами в окне Power Pivot — и дело в шляпе. Для этого на вкладке Power Pivot жмём кнопку Управление (Manage) и затем в появившемся окне — кнопку Представление диаграммы (Diagram View) . Останется перетащить общие (ключевые) названия столбцов (поля) между таблицами, чтобы создать связи:

Создание связей между таблицами

После этого в сводной по Модели Данных можно закидывать в области сводной (строки, столбцы, фильтры, значения) любые поля из любых связанных таблиц — всё будет связываться и подсчитываться уже автоматически:

Строим сводну по модели

Преимущество 2. Подсчёт количества уникальных значений

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

Щёлкаем правой кнопкой мыши по полю — команда Параметры полей значений и на вкладке Операция выбираем Число разных элементов (Distinct count) :

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

Преимущество 3. Свои формулы на языке DAX

Иногда в сводных таблицах приходится выполнять различные дополнительные вычисления. В обычных сводных это делается с помощью вычисляемых полей и объектов, а сводной по Модели Данных для этого используются меры на специальном языке DAX (DAX = Data Analysis Expressions).

Для создания меры выберите на вкладке Power Pivot команду Меры — Создать меру (Measures — New measure) или просто щёлкните правой кнопкой мыши по таблице в списке полей сводной и выберите Добавить меру (Add measure) в контекстном меню:

Новые статьи:  Создание диаграммы в PowerPoint

Добавляем меру

В открывшемся окне задаём:

Параметры меры

  • Имя таблицы, где созданная мера будет храниться.
  • Название меры — любое понятное вам имя для нового поля.
  • Описание — по желанию.
  • Формула — самое главное, т.к. здесь мы либо вручную вписываем, либо жмём на кнопку fx и выбираем из списка функцию DAX, которая должна вычислять результат, когда мы потом забросим нашу меру в область Значений.
  • В нижней части окна можно сразу задать для меры числовой формат в списке Категория.

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

Преимущество 4. Свои иерархии полей

Часто при создании типовых отчётов приходится забрасывать в сводные таблицы одни и те же комбинации полей в заданной последовательности, например Год-Квартал-Месяц-День, или Категория-Товар, или Страна-Город-Клиент и т.п. В сводной по Модели Данных эта проблема легко решается созданием собственных иерархий — пользовательских наборов полей.

В окне Power Pivot переключитесь в режим диаграммы кнопкой Представление диаграммы на вкладке Главная (Home — Diagram View) , выделите с Ctrl нужные поля и щёлкните по ним правой кнопкой мыши. В контекстном меню будет команда Создать иерархию (Create hierarchy) :

Создание иерархии полей

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

Добавление иерархии в сводную

Преимущество 5. Свои наборы элементов

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

Для этого на вкладке Анализ сводной таблицы в выпадающем списке Поля, элементы и наборы есть соответствующие команды (Analyze — Fields, I tems & Sets — Create set based on row/column items) :

Создание наборов в сводной по модели данных

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

Создаем набор

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

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

Преимущество 6. Выборочное скрытие таблиц и столбцов

Это хоть и небольшое, но весьма приятное в некоторых случаях преимущество. Щёлкнув правой кнопкой мыши по названию поля или по ярлычку таблицы в окне Power Pivot, можно выбрать команду Скрыть из набора клиентских средств (Hide from Client Tools) :

Скрываем поле или таблицу от пользователя

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

Преимущество 7. Продвинутый drill-down

Если в обычной сводной таблице сделать двойной щелчок левой кнопкой мыши по любой ячейке в области значений, то Excel выводит на отдельном листе копию фрагмента исходных данных, которые участвовали в расчёте этой ячейки. Это очень удобная штука, официально называющаяся Drill-down (на русском обычно говорят «провалиться»).

В сводной по Модели Данных этот удобный инструмент работает более тонко. Встав на любую интересующую нас ячейку с результатом, можно щёлкнуть по всплывающему рядом значку с лупой (он называется Экспресс-тенденции) и выбрать затем любое интересующее вас поле в любой связанной таблице:

Экспресс-тенденции

После этого текущее значение (Модель = Explorer) уйдет в область фильтра, а сводная будет построена уже по офисам:

Результат drill-down

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

Преимущество 8. Преобразование сводной в функции кубов

Если выделить любую ячейку в сводной по Модели Данных и выбрать затем на вкладке Анализ сводной таблицы команду Средства OLAP — Преобразовать в формулы (Analyze — OLAP Tools — Convert to formulas) , то вся сводная будет автоматически преобразована в формулы. Теперь значения полей в области строк-столбцов и результаты в области значений будут извлекаться из Модели Данных с помощью специальных функций кубов: КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ:

Преобразование сводной в функции кубов

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

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

Ссылки по теме

  • План-факт анализ в сводной таблице с Power Pivot и Power Query
  • Сводная по таблице с многострочной шапкой
  • Создание базы данных в Excel с помощью Power Pivot

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

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

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

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

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

Вариант 1: Обычная сводная таблица

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

Форматирование как таблица в Microsoft Excel

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

  • Открывается диалоговое окно, которое нам предлагает указать координаты расположения таблицы. Впрочем, по умолчанию координаты, которые предлагает программа, и так охватывают всю таблицу. Так что нам остается только согласиться и нажать на «OK». Но пользователи должны знать, что при желании они тут могут изменить эти параметры. Указание расположения таблицы в Microsoft Excel
  • Таблица превращается в динамическую и автоматически растягивающуюся. Она также получает имя, которое при желании пользователь может изменить на любое удобное ему. Просмотреть или изменить имя можно на вкладке «Конструктор». Имя таблицы в Microsoft Excel

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

  • В конкретном случае мы переместили поля «Пол» и «Дата» в область «Фильтр отчета», «Категория персонала» — в «Названия столбцов», «Имя» — в «Название строк», «Сумма заработной платы» — в «Значения». Следует отметить, что все арифметические расчеты данных, подтянутых из другой таблицы, возможны только в последней области. Во время того, как мы проделывали такие манипуляции с переносом полей в области, соответственно изменялась и сама таблица в левой части окна. Перенос полей в области в Microsoft Excel
  • Получилась вот такая сводная таблица. Над ней отображаются фильтры по полу и дате. Сводная таблица в программе Microsoft Excel
  • Новые статьи:  Скрытие формул в Microsoft Excel

    Вариант 2: Мастер сводных таблиц

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

      Переходим в пункт меню «Файл» и кликаем на «Параметры».

    Переход в параметры Microsoft Excel

  • Заходим в раздел «Панель быстрого доступа» и выбираем команды из команд на ленте. В списке элементов ищем «Мастер сводных таблиц и диаграмм». Выделяем его, жмем на кнопку «Добавить», а потом «OK». Добавление мастера сводных таблиц в Microsoft Excel
  • В результате наших действий на «Панели быстрого доступа» появился новый значок. Кликаем по нему. Переход в панель быстрого доступа в Microsoft Excel
  • После этого открывается «Мастер сводных таблиц». Есть четыре варианта источника данных, откуда будет формироваться сводная таблица, из которых указываем подходящий. Внизу следует выбрать, что мы собираемся создавать: сводную таблицу или диаграмму. Осуществляем выбор и идем «Далее». Выбор источника сводной таблицы в Microsoft Excel
  • Появляется окно с диапазоном таблицы с данными, который при желании можно изменить. Нам этого делать не надо, поэтому просто переходим «Далее». Выбор диапазона данных в Microsoft Excel
  • Затем «Мастер сводных таблиц» предлагает выбрать место, где будет размещаться новый объект: на этом же листе или на новом. Делаем выбор и подтверждаем его кнопкой «Готово». Выбор места размещения сводной таблицы в Microsoft Excel
  • Откроется новый лист в точности с такой же формой, которая была при обычном способе создания сводной таблицы. Форма для создания сводной таблицы в Microsoft Excel
  • Все дальнейшие действия выполняются по тому же алгоритму, который был описан выше (см. Вариант 1).
  • Настройка сводной таблицы

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

    1. Для приведения таблицы к нужному виду кликаем на кнопку около фильтра «Дата». В нем устанавливаем галочку напротив надписи «Выделить несколько элементов». Далее снимаем галочки со всех дат, которые не вписываются в период третьего квартала. В нашем случае это всего лишь одна дата. Подтверждаем действие. Изменения диапазона периода в Microsoft Excel
    2. Таким же образом мы можем воспользоваться фильтром по полу и выбрать для отчета, например, только одних мужчин. Фильтр по полу в Microsoft Excel
    3. Сводная таблица приобрела такой вид. Изменение сводной таблицы в Microsoft Excel
    4. Для демонстрации того, что управлять информацией в таблице можно как угодно, снова открываем форму списка полей. Переходим на вкладку «Параметры», и щелкаем на «Список полей». Перемещаем поле «Дата» из области «Фильтр отчета» в «Название строк», а между полями «Категория персонала» и «Пол» производим обмен областями. Все операции выполняем с помощью простого перетягивания элементов. Обмен областями в Microsoft Excel
    5. Теперь таблица выглядит совсем по-другому. Столбцы делятся по полам, в строках появилась разбивка по месяцам, а фильтрацию теперь можно осуществлять по категории персонала. Изменение вида сводной таблицы в Microsoft Excel
    6. Если же в списке полей название строк переместить и поставить выше дату, чем имя, тогда именно даты выплат будут подразделяться на имена сотрудников. Перемещение даты и имени в Microsoft Excel
    7. Можно также отобразить числовые значения таблицы в виде гистограммы. Для этого выделяем ячейку с числовым значением, заходим на вкладку «Главная», жмем «Условное форматирование», выбираем пункт «Гистограммы» и указываем понравившийся вид. Выбор гистограммы в Microsoft Excel
    8. Гистограмма появляется только в одной ячейке. Чтобы применить правило гистограммы для всех ячеек таблицы, кликаем на кнопку, которая появилась рядом с гистограммой, и в открывшемся окне переводим переключатель в позицию «Ко всем ячейкам». Применение гистограммы ко всем ячейкам в Microsoft Excel
    9. В итоге наша сводная таблица стала выглядеть более презентабельно. Сводная таблица в Microsoft Excel готова

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


    Оставьте комментарий