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

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

Расчет средневзвешенного значения в Excel

Что такое средневзвешенное значение и чем оно отличается от обычного среднего арифметического. Как вычислить средневзвешенное в Excel с помощью формул и в сводной таблице (в том числе с помощью DAX и Power Pivot).

Сравнение двух таблиц

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними — добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

Независимая группировка сводных таблиц

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

Создание базы данных в Excel

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

Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

Фильтр исходных данных в сводной таблице

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

Подробнее… Новые возможности сводных таблиц в Excel 2013

Рекомендованные сводные таблицы. Построение сводной по нескольким исходным таблицам. Фильтрация с помощью Временной шкалы. Новые функции вычислений полей значений. «Погружение» в данные с помощью Экспресс-просмотра.

Подробнее… Группировка в сводных таблицах

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

Редизайнер таблиц

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

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

Видео

Лирическое вступление или мотивация

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

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

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

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

Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Например:

• Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?  
• Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается? 
• Кто входит в пятерку наших самых крупных заказчиков? 

… и т.д.

Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel — сводные таблицы.

Поехали…

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок

Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице…» нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

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

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.

Работа с макетом

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

Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс — делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет. 

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table)

на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

• Названия строк (Row labels)
• Названия столбцов (Column labels)
• Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
• Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages), смысл тот же.

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

P.S.

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

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

 

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

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

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

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

Что такое сводная таблица?

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

Основным методом ввода данных в Excel является обычная плоская таблица, например:

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

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

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

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

Microsoft Excel имеет отличную встроенную опцию сводной таблицы. Обратите внимание, что Excel — далеко не единственная программа для работы с электронными таблицами, которая использует сводные таблицы. Альтернативы Microsoft Office, такие как Libre Office и G-Suite Google, поддерживают все сводные таблицы в своих программах для работы с электронными таблицами.

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

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

Как видите, есть варианты подключения внешнего источника данных (например, MS Access) или размещения сводной таблицы в существующей рабочей таблице. Для последнего варианта обязательно убедитесь, что ваша новая сводная таблица не затеняет и не разрушает ваши существующие данные (хотя вы всегда можете нажать CTRL + Z, чтобы отменить, если случится что-то ужасное!).

Сортировка данных сводной таблицы

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

Отсюда у вас есть два варианта добавления данных в сводную таблицу:

1. Перетаскивание: поля сводной таблицы, перечисленные справа, можно перетащить в четыре области ниже (Фильтры, Столбцы, Строки и Значения). Вы можете выбрать конкретные элементы данных, которые вы хотите пересечь с любым количеством различий.
2. Добавить в отчет: при нажатии на отдельные поля в наборе данных они будут добавлены непосредственно в таблицу ожидающих отчетов. Используйте этот метод для быстрого создания, анализа, сопоставления и редактирования доступных данных.

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

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

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

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

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

Фильтрация данных сводной таблицы

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

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

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

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

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

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

Если вы хотите расширить свои возможности Excel, ознакомьтесь с этими формулами Excel, которые делают удивительные вещи !

Прочитайте статью полностью: Как создать сводную таблицу для анализа данных в Microsoft Excel

Связанный

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

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

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

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

Дополнительно расскажем, как настраиваются такие объекты.

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

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

1. За основу возьмем таблицу выплат заработной платы работникам предприятия. В ней указаны имена работников, пол, категория, дата и сумма выплаты. То есть каждому эпизоду выплаты отдельному работнику соответствует отдельная строчка. Нам предстоит сгруппировать хаотично расположенные данные в этой таблице в одну сводную таблицу, при этом сведения будут браться только за третий квартал 2016 года. Посмотрим, как это сделать на конкретном примере.
2. Прежде всего преобразуем исходную таблицу в динамическую. Это нужно для того, чтобы при добавлении строк и других данных они автоматически подтягивались в сводную таблицу. Наводим курсор на любую ячейку, затем в расположенном на ленте блоке «Стили» кликаем по кнопке «Форматировать как таблицу» и выбираем любой понравившийся стиль таблицы.
3. Открывается диалоговое окно, которое нам предлагает указать координаты расположения таблицы. Впрочем, по умолчанию координаты, которые предлагает программа, и так охватывают всю таблицу. Так что нам остается только согласиться и нажать на «OK». Но пользователи должны знать, что при желании они тут могут изменить эти параметры.
4. Таблица превращается в динамическую и автоматически растягивающуюся. Она также получает имя, которое при желании пользователь может изменить на любое удобное ему. Просмотреть или изменить имя можно на вкладке «Конструктор».
5. Чтобы непосредственно начать создание, выбираем вкладку «Вставка». Здесь жмем на первую кнопку в ленте, которая так и называется «Сводная таблица». Откроется меню, где следует выбрать, что мы собираемся создавать: таблицу или диаграмму. В конце нажимаем «Сводная таблица».
6. В новом окне нам опять надо выбрать диапазон или название таблицы. Как видим, программа уже сама подтянула имя нашей таблицы, так что тут ничего больше делать не нужно. В нижней части диалогового окна можно выбрать место, где будет создаваться сводная таблица: на новом листе (по умолчанию) или на этом же. Конечно, в большинстве случаев намного удобнее держать ее на отдельном листе.
7. После этого на новом листе откроется форма создания сводной таблицы.
8. В правой части окна расположен список полей, а ниже четыре области: названия строк, названия столбцов, значения, фильтр отчета. Просто перетаскиваем мышкой необходимые нам поля таблицы в соответствующие потребностям области. Тут не существует какого-либо четкого установленного правила, какие поля следует перемещать, ведь все зависит от таблицы-первоисточника и от конкретных задач, которые могут меняться.
9. В конкретном случае мы переместили поля «Пол» и «Дата» в область «Фильтр отчета», «Категория персонала» — в «Названия столбцов», «Имя» — в «Название строк», «Сумма заработной платы» — в «Значения». Следует отметить, что все арифметические расчеты данных, подтянутых из другой таблицы, возможны только в последней области. Во время того, как мы проделывали такие манипуляции с переносом полей в области, соответственно изменялась и сама таблица в левой части окна.
10. Получилась вот такая сводная таблица. Над ней отображаются фильтры по полу и дате.

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

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

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

Настройка сводной таблицы

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Как сделать сводную в access?

Для того чтобы продемонстрировать возможности сводных таблиц, рассмотрим запрос «Счета» (Invoices). Раскройте список запросов и откройте этот запрос в режиме Таблицы. Вы увидите таблицу, которая содержит свыше двух тысяч записей и более двух десятков полей. Теперь попробуем представить этот запрос в виде сводной таблицы.

Когда таблица отображается в режиме Сводная таблица, в верхней части окна приложения видна специальная панель инструментов Сводные таблицы (Pivot Table). Эта панель инструментов представлена на рис. 8.32.

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

Автофильтр (AutoFilter)Сводная таблица, Автофильтр (PivotTable, AutoFilter)Устанавливает и сбрасывает фильтрыПоказать верхние и нижние элементы (Show Top/Bottom Items)Сводная таблица, Показать верхние и нижние элементы (PivotTable, Show Top/Bottom Items)Устанавливает фильтр, отображая заданное число первых или последних строк в’ отсортированной таблицеАвтовычисления (AutoCalc)Сводная таблица, Автовычисления (PivotTable, AutoCalc)Позволяет вычислить промежуточные итоги по каждой группе записейИтоги (Subtotal)Сводная таблица, Итоги (PivotTable, Subtotal)Позволяет отображать или скрывать итоговые столбцы или строкиВычисляемые итоги и поля (Calculated Total and Fields)Сводная таблица, Вычисляемые итоги и поля (PivotTable, Calculated Total and Fields)Позволяет задать формулы для расчета как специальных полей, так и итоговых данныхОтобразить как (Show As)Сводная таблица, Отобразить как (PivotTable, Show As)Позволяет отображать итоговые значения в процентах относительно общего итога по столбцу, строке или родительскому элементу на оси столбца или строкиСвернуть (Collapse)Сводная таблица, Свернуть (PivotTable, Collapse)Уменьшает на один шаг уровень детализации отображаемых данных в выделенной области таблицыРазвернуть (Expand)Сводная таблица, Развернуть (PivotTable, Expand)Увеличивает на один шаг уровень детализации отображаемых данных в выделенной области таблицыСкрыть подробности (Hide Details)Сводная таблица, Скрыть подробности (PivotTable, Hide Details)Позволяет скрыть детальные данные и оставить только подытоги и итогиПодробности (Show Details)Сводная таблица, Подробности (PivotTable, Show Details)Отображает все данные в строках или столбцах таблицыОбновить (Refresh)Сводная таблица, Обновить (PivotTable, Refresh)Обновляет сводную таблицу, выполняя повторный запрос к базе данныхЭкспорт в Microsoft Excel (Export to Microsoft Excel)Сводная таблица, Экспорт в Microsoft Excel (PivotTable, Export to Microsoft Excel)Обеспечивает экспорт сводной таблицы в интерактивный сводный отчет Microsoft ExcelСписок полей (Field List)Вид, Список полей (Field List)Отображает диалоговое окно со списком полей таблицы

Таблица 8. 2. Описание кнопок панели инструментов Сводные таблицы

Определим итоговые столбцы.

Окончательный вид таблицы представлен на рис. 8.33.

Сводная таблица (Pivot Table) и сводная диаграмма (Pivot Chart) дают возможность выполнять комплексный анализ данных (по аналогии с MS Excel).

Задание: необходимо проанализировать продажи по каждой категории
товаров.

1. Создайте запрос «Анализ_Продаж_Категорий», включив в него
следующие поля:

2. Выполните запрос, убедитесь в правильности его выполнения.

3. Перейдите на вкладку Создание (Create) и выберите тип формы Сводная
таблица (Pivot Table). Откроется рабочее окно, на котором необходимо
разместить поля из запроса.

4. Для отображения списка полей (Pivot Table Field List) запроса кликните
мышкой на пустом поле или выберите кнопку Список полей (Field List) в
группе Показать или скрыть (Show/Hide) на вкладке Конструктор
(Design).

5. Добавьте поля строк создаваемой сводной таблицы:
«НазваниеКатеогрии», «НаименованиеТовара»; поле «Количество»
добавьте в поле итогов для деталей.

Рис. 1. Окно размещения элементов сводной таблицы.

6. Сохраните форму (сводную таблицу) под именем
«Анализ_Продаж_Категорий».

7. Выделите поле «Количество» и на вкладке Конструктор (Design) в
группе Сервис (Tools) выберите Автовычисления – Сумма (Sum). Эта
функция позволит просматривать количество проданных единиц товаров
по каждой категории без указания подробностей.

Рис. 2. Группа Сервис.

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

Рис. 3. Сводная таблица «Анализ_Продаж_Категорий»

9. На основании запроса «Анализ_Продаж_Категорий» необходимо создать
сводную диаграмму. Для этого на вкладке создание необходимо выбрать
кнопку Сводная диаграмма (Pivot Chart).

10. Для открытия окна Список полей данных (Pivot Chart Field List)
необходимо щелкнуть на пустом поле области диаграммы или нажать
кнопку Список полей (Field List) в группе Показать или скрыть
(Show/Hide) на вкладке Конструктор (Design).

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

12. Сохраните форму (диаграмму) под именем
«Анализ_Продаж_Диаграмма».

Программа Microsoft Access очень часто применяется для управления наборами таблиц, которые связаны согласно определенным правилам или полям (как, например, таблицы Покупатели, Заказы и Доход одной базы данных).

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

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

В Excel 2010 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel на выполнение и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные (Data) и в группе Получение внешних данных (Get External Data) щелкните на кнопке Из Access (From Access), как показано на рис. 7.19.

Рис. 7.19. Щелкните на кнопке Из Access, чтобы получить данные из базы данных Access

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

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

На рис. 7.20 показано, что в диалоговом окне Выделить таблицу (Select Table) присутствует столбец Тип (Туре). В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (view) и таблица (table). Представления соответствуют запросам Access.

В нашем примере Sales_By_Employee — это запрос Access. Таким образом, в сводную таблицу будет импортироваться результат выполнения запроса. Это очень важный аспект обработки данных. Сбор необходимых данных, соответствующих запросу, выполняет программа Access, a Excel всего лишь обеспечивает их анализ.

Следующим на экране появляется диалоговое окно Импорт данных (Import Data). В нем указывается формат, в котором будут импортироваться данные. Как видно на рис. 7.21, можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные.

Рис. 7.21. Установите переключатель Отчет сводной таблицы

Установите переключатель Отчет сводной таблицы (PivotTable Report) и щелкните на кнопке ОК. Начиная с этого момента вы будете наблюдать на экране рабочий лист Excel с диалоговым окном Список полей сводной таблицы (PivotTable Field List). Теперь можно выполнять любые операции с только что созданной сводной таблицей (рис. 7.22).

Рис. 7.22. Сводная таблица готова к применению

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

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

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

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

Рис. 7.23. Двойной щелчок на итоговом значении в области данных сводной таблицы приводит к выводу всех строк, принимающих участие в вычислении этого общего итога

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

• Размер набора данных сильно влияет на скорость обработки данных в сводной таблице. Это накладывает серьезные ограничения на эффективность использования сводных таблиц как самодостаточных структур. Причина подобного поведения программы — в специфике управления оперативной памятью. Файл при открытии в Excel полностью перемещается в оперативную память для более быстрой обработки и доступа к данным. Но при этом в программе не реализован надежный механизм оптимального управления оперативной памятью при извлечении из нее даже небольшого фрагмента данных. Несмотря на то что в Excel 2013 предусматривается использование до 1 млн. строк и 16 тыс. столбцов, даже средние по размеру наборы данных приводят к значительным задержкам при обработке.
• Отсутствие реляционной модели данных вынуждает нас использовать «плоские» таблицы, которые хранят избыточные данные и увеличивают вероятность появления ошибок.
• Отсутствие индексации полей данных в Excel для оптимизации процесса извлечения больших объемов данных.

Скачать заметку в формате Word или pdf, скачать примеры в формате Excel, скачать базу данных Access в формате архива (по соображениям безопасности провайдер не позволяет размещать файлы в формате . accdb)

Именно поэтому в серьезных организациях для управления данными используется не Excel, а такие СУБД, как Microsoft Access и SQL Server. Эти СУБД применяются для хранения миллионов записей, которые можно быстро находить и извлекать. При подобном разделении производственных задач формируется уровень управления данными (сама база данных) и уровень приложения (Excel). Трудность заключается в том, чтобы найти наилучший способ извлечения информации из уровня управления данными и передачи ее на уровень приложения для эффективного использования в сводной таблице.

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

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

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

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

В Excel 2013 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные и в группе Получение внешних данных щелкните на кнопке Из Access (рис. 1).

Рис. 1. Получение данных из Access

На экране появится диалоговое окно с просьбой указать базу данных, из которой будет извлекаться информация. Укажите исходную базу данных. (Ссылка на базу данных, используемую в данном примере, приведена в начале заметки; вам потребуется сохранить базу данных на локальном диске). После выбора базы данных на экране появится диалоговое окно, подобное показанному на рис. 2. В нем перечислены все таблицы и запросы, присутствующие в указанной базе данных. В нашем примере выбран запрос Sales By Employee. Щелкните на кнопке ОК.

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

В диалоговом окне Выбор таблицы имеется столбец Тип. В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (VIEW) и таблица (TABLE). Представления соответствуют запросам Access. В нашем примере Sales_By_Employee — это запрос Access. Таким образом, в сводную таблицу будет импортироваться результат выполнения запроса. Это очень важный аспект обработки данных. Выборку необходимых данных, соответствующих запросу, выполняет программа Access, a Excel всего лишь выполняет анализ выбранных данных.

Следующим на экране появляется диалоговое окно Импорт данных. В нем указывается формат, в котором будут импортироваться данные (рис. 3). Можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные. Щелкните ОК. На листе появится панель Поля сводной таблицы. Можно начинать работать с только что созданной сводной таблицей (рис. 4).

Рис. 3. Установите переключатель Отчет сводной таблицы

Рис. 4. Сводная таблица готова к использованию

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

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

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

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

В связи с популяризацией коллективных вычислений в Excel 2013 были серьезно усовершенствованы средства подключения к транзакционным базам данных, таким как SQL Server. С помощью новых, встроенных в Excel инструментов создание сводных таблиц на основе баз данных SQL Server становится предельно простым занятием. Начните с перехода на вкладку ленты Данные. Щелкните на кнопке Из других источников и в раскрывающемся списке выберите команду С сервера SQL Server (рис. 5).

Рис. 5. Выберите в раскрывающемся списке команду С сервера SQL Server

Тем самым вы запустите мастер подключения к данным (рис. 6). С его помощью в Excel настраивается ссылка на внешние данные, расположенные на сервере.

Рис. 6. Введите регистрационные данные и щелкните на кнопке Далее

В рассматриваемом случае файл примера отсутствует. Здесь проиллюстрирована процедура взаимодействия Excel и SQL Server. Действия, которые вы будете выполнять для подключения к собственной базе данных, полностью повторяют описанные ниже. На первом шаге мастера нужно предоставить Excel регистрационные данные. Как видно на рис. 6, от вас требуется ввести имя сервера, а также имя пользователя и пароль доступа к данным. При вводе регистрационных данных в Windows достаточно установить переключатель Использовать проверку подлинности Windows.

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

Рис. 7. Укажите исходную базу данных, а затем выберите таблицу или представление

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

Рис. 8. Описательная информация о создаваемом подключении

Чаще всего в этом диалоговом окне используются такие параметры.

• Имя файла. В этом поле можно изменить имя файла с расширением .ode (Office Data Connection), генерируемого с целью хранения параметров создаваемого подключения.
• Сохранить пароль в файле. Этот флажок, расположенный под полем ввода имени файла, обеспечивает хранение пароля доступа к внешнему источнику в файле, содержащем описание параметров конфигурации подключения. Имейте в виду, что пароль не зашифрован, поэтому любой пользователь может узнать ваш пароль, просто просмотрев файл в текстовом редакторе.
• Описание. В этом поле вводится краткое описание назначения устанавливаемого подключения.
• Понятное имя. В качестве понятного (для пользователей) имени обычно используется собственное название внешнего источника данных. Это название должно быть более значимым для вас, чем то, которое дал ему его создатель.

Завершив ввод всей необходимой информации, щелкните на кнопке Готово. Вы увидите на экране последнее диалоговое окно — Импорт данных (такое же, как на рис. 3). Установите переключатель Отчет сводной таблицы и щелкните ОК, после чего переходите к непосредственному управлению отчетом сводной таблицы.

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

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

1. Выделите вкладку Данные и перейдите в группу Получение внешних данных. Щелкните на кнопке Из Access (см. рис. 1).

2. Найдите базу Access и откройте ее. На экране появится диалоговое окно Выбор источника данных. Установите флажок Разрешить выбор нескольких таблиц. Отметьте флажками таблицы, которые будут включаться во внутреннюю модель данных. Щелкните ОК (рис. 9).

Рис. 9. Отметьте флажками таблицы, которые будут включены во внутреннюю модель данных

3. На экране появится диалоговое окно Импорт данных. Установите переключатель Отчет сводной таблицы. Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Свойства, и снимите флажок Импорт связей между таблицами (рис. 10). Это исключит вероятность появления ошибки в случае некорректной интерпретации связей между таблицами. Это действие требуется в том случае, если вы собираетесь создавать связи между таблицами самостоятельно. Щелкните на кнопке ОК для создания базовой сводной таблицы

Рис. 10. Снимите флажок Импорт связей между таблицами

4. Выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. На экране появится диалоговое окно Управление связями. Кликните Создать. Откроется окно Изменение связи (рис. 11). Используя раскрывающиеся списки создайте нужные связи через поля, представляющие собою первичные ключи. В нашем примере – это поле Employee_Number. Щелкните OK, чтобы закрыть окно Изменение связи.Щелкните Закрыть, чтобы закрыть окно Управление связями (рис. 12).

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

Рис. 12. Окно Управление связями с только что созданной связью между таблицами Sales_by_Employeeи Employee_Master

5. В вашем распоряжении появилась сводная таблица, основанная на внешних данных, которые были импортированы в модель данных. Можно, например, проанализировать продажи по рынкам сбыта и ФИО торговых представителей (рис. 13).

Рис. 13. Сводная таблица на основе нескольких внешних таблиц

Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.

Приложение СУБД MS Access – это полноценный помощник для создания и ведения баз данных, заключенных в таблицы и массивы. Если база имеет слишком большой объем, быстро найти необходимые значения довольно сложно.

Именно поэтому в Access существует такая функция, как запросы. Рассмотрим, что это такое, как работает, какие имеет особенности.

Создание запросов в Microsoft Access

Чтобы разобраться, как создавать запросы в Access, нужно знать основные положения работы с СУБД.

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

• Конструктор запросов.
• Мастер запросов.

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

Легкий путь для новичков

Знающий человек за несколько кликов мышью выбирает те компоненты, которые потребуются пользователю для выполнения запроса, а затем быстро формирует реестр, в соответствии с собранными ключевыми значениями. Если это первое знакомство с СУБД, и пользователь не представляет, как создавать запросы в Access, то выбирается программа Мастер.

В данном режиме можно ознакомиться и разобраться со следующими типами запросов:

• Простой.
• Перекрестный.
• Записи без подчиненных.
• Повторяющиеся записи.

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

Простой запрос

Этот инструмент работы с таблицами собирает нужные данные из указанных пользователем полей. Уже по названию видно, что это самый популярный тип запросов для новичков. Его удобство заключается в том, что такая процедура открывается в новой вкладке. Поэтому ответ на вопрос, как создать запрос в Access 2010, становится очевидным уже после открытия первого меню Мастера.

Перекрестный запрос

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

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

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

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

Повторяющиеся записи

Как понятно из названия, основное предназначение данного запроса – выборка всех одинаковых строк в таблице по указанным параметрам. Выглядит это так:

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

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

Записи без подчиненных

Это последний тип запросов, доступный в режиме «Мастер – Записи без подчиненных».

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

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

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

Функции запросов в MS Access

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

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

Запрос на выборку

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

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

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

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

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

Чтобы завершить операцию, нужно нажать на кнопку «Выполнить».

Запрос с параметрами

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

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

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

Расширенный перекрестный запрос

Продолжаем усложнять ситуацию. Еще труднее для понимания является информация о том, как создавать запросы в Access, если присутствует несколько таблиц с данными. Перекрестный запрос уже рассматривался выше, как один из вариантов работы с Мастером. Однако, и в режиме «Конструктора» можно создавать подобный запрос.

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

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

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

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

Конечно, присутствуют и «подводные камни», которые могут помешать в работе. Например, при создании запроса на сортировку базы данных по значению столбцов система выдает ошибку. То есть доступна только сортировка по стандартным пунктам – «возрастание и убывание».

Краткие рекомендации

Подводя итоги, нужно сказать, что решить, как создавать запросы в Access – с помощью Мастера или Конструктора, должен сам пользователь. Хотя, для большинства людей, которые используют СУБД MS Access, больше подойдет первый вариант. Ведь Мастер сам сделает всю работу, оставив для пользователя только несколько кликов мышью, при выборе условий запроса.

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

Есть один момент, который доступен лишь программистам. Так как основным языком СУБД является SQL, то нужный запрос можно написать в виде программного кода. Чтобы работать в данном режиме, достаточно нажать на строку уже созданного запроса, и в открывшемся контекстном меню выбрать «Режим SQL».

Преобразовать сводную таблицу в обычную.

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

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

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

Узнайте о стандартные сценарии преобразования сводные таблицы в формулы листа

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

Изменение расположения и удаление ячеек

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

Вставка строк или столбцов

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

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

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

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

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

Создание неоднородного макета строк или столбцов (асимметричное создание отчета)

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

Создание собственных формул куба и многомерных выражений

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

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

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

Преобразование ячейки в формулы, использующие функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

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

Примечание: Команда Создать GetPivotData управляет параметром Использовать функции GetPivotData для ссылок в сводной таблице в категории Формулы раздела Работа с формулами в диалоговом окне Параметры Excel .

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

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

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

Завершите ввод формулы и нажмите клавишу ВВОД.

Примечание: В случае удаления из отчета любой из ячеек, на которые ссылается формула ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ формула возвращает ошибку #ССЫЛКА!.

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

Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

• Редизайн сводной таблицы в список в секунды
• Преобразование сложных таблиц с многоуровневыми заголовками
• Корректный редизайн таблиц с объединёнными или пустыми ячейками
• Сохранение заголовков столбцов
• Сохранение форматирования ячеек

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

Добавить «Редизайн таблицы» в Excel 2019, 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2019 — 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
2. 
   Совет
3. Укажите размер заголовков:
   В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1
4. 
   Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).
5. Нажмите ОК >

Как преобразовать сложную сводную таблицу с многоуровневыми заголовками

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.
   Совет : нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
3. Укажите размер заголовков:
   • Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
   • Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
4. Укажите, следует ли поместить результат на новый или на существующий лист.
5. Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

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

1. Выделите сводную таблицу, включая заголовки.
2. Укажите размер заголовков.
3. Отметьте флажком «Пропустить пустые значения».
4. Нажмите ОК > Готово.

Как выполнить редизайн таблицы с объединёнными ячейками

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Дублировать значение в объединённых ячейках»:
   • Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
   • Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
5. Укажите, куда поместить результат.
6. Нажмите ОК > Готово.

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Сохранить заголовки»:
   • Где это возможно, надстройка продублирует заголовки из сводной таблицы.
   • Категориям таблицы будет автоматически присвоен заголовок «Категория».
   • Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
5. Укажите, куда поместить результат.
6. Нажмите ОК > Готово.

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

1. Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком «Сохранить формат ячеек»:
   Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
5. Укажите, куда поместить результат.
6. Нажмите ОК > Готово.

Внимание : обработка больших таблиц с множеством форматов займёт больше времени.

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

• «Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
• Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
• Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

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

Появились вопросы или предложения? Оставьте комментарий ниже.

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

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

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

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

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

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

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

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

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

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

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

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

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

Изменение структуры отчета

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

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

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

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

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

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

Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.

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

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите в контекстном меню пункт Настройка панели быстрого доступа .
2. В разделе Панель быстрого доступа диалогового окна Параметры Excel выберите Команды на ленте из раскрывающегося списка слева.
3. Прокрутите список и выберите пункт .
4. Нажмите кнопку Добавить .
5. Нажмите 0К , чтобы закрыть диалоговое окно Параметры Excel .

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

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

1. Активизируйте любую ячейку в сводной таблице.
2. Щелкните на значке Мастер сводных таблиц и диаграмм , который вы добавили на панель быстрого доступа.
3. В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее .
4. В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее .
5. В шаге 2b в поле Диапазон укажите диапазон сводной таблицы (А1:Е4 для выборки из примера) и нажмите кнопку Добавить ; затем нажмите кнопку Далее , чтобы перейти к шагу 3.
6. В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово . Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы .
7. В области Список полей сводной таблицы снимите флажки Строка и Столбец .

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

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

Столкнулся недавно с проблемой:

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

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

Сводные_

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

После создания сводной таблицы и настройки ее структуры выделите всю таблицу и скопируйте ее в буфер обмена. Далее перейдите на вкладку Главная (Ноте) и щелкните на кнопке Вставка (Insert), а затем выберите в раскрывающемся меню команду Вставить значения (Insert Values), как показано на рис. 6.19.

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

Рис 6.19. Команда Вставить значения применяется для получения на основе сводной таблицы обычной таблицы со статическими значениями

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Видим такую таблицу.

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

Стало видно отображение чисел, а нам необходим именно числовой формат

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

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

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

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

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

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

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

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

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

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

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

Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в 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 легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Методика создания сводной таблицы с нуля

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

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

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

2. После перехода на вкладку “Вставка”, воспользуйтесь пиктограммой “Сводная таблица”.
3. Обратитесь к разделу “Выберите данные для анализа” и затем выставьте галочку “Выбрать таблицу или диапазон”.
4. Далее, обратившись к полю “Таблица или диапазон” проверьте соответствующий диапазон выбранных ячеек.
5. В секции “Укажите, куда следует поместить отчет сводной таблицы” выставьте индикатор “На новый лист” для размещения сводной таблицы на другом, незаполненном листе. В качестве альтернативного варианта, можете выбрать опцию “На существующий лист”, после чего придется указать месторасположение для отображения на экране сводной таблицы.
6. Щелкните мышью на кнопке ОК.

Настройка сводной таблицы

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

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

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

Как создать сводную диаграмму?

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

Данные о расходах семьи	Итоговая сводная диаграмма

Пошаговая процедура создания сводной диаграммы

Ниже приведено детальное описание создания сводной диаграммы в пошаговом представлении:

1. Прежде всего, необходимо выделить ячейку в таблице.
2. Далее стоит выбрать элементы “Вставка” -> “Сводная диаграмма”
3. Для подтверждения нажмите кнопку ОК.

Как создать диаграмму на базе сводной таблицы?

1. В первую очередь, требуется выделить ячейку в таблице.
2. Отметьте элементы “Работа со сводными таблицами” -> “Анализ” -> “Сводная диаграмма”
3. Сделайте выбор требуемого графика.
4. Воспользуйтесь кнопкой ОК.

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

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

Если щелкнуть мышью в любой ячейке или на полях сводной таблицы, на экране должен отобразиться список полей. Если в результате щелчка в пределах сводной таблицы список полей не отображается на дисплее, откройте его, кликнув в любой позиции сводной таблицы. Затем на главной ленте команд в категории “Работа со сводными таблицами” выберите опцию “Анализ” -> “Список полей”.

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

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

365, 2019, 2016 и 2013 [включая сводную диаграмму]

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

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

Я собираюсь выполнить этот процесс шаг за шагом при установке Excel 365.Процесс также будет более или менее точно таким же для версий 2013, 2016 и 2019.

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

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

Использование инструментов SEO для Excel для экспорта данных через SEMrush API.

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

После получения данных создайте таблицу, выбрав весь набор данных (CTRL + SHIFT + ВНИЗ, затем ВПРАВО), затем CTRL + L создает таблицу:

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

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

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

Перейдите к Вставка> Сводная таблица и добавьте на новый лист, как это:

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

Добавьте поля осей, значения, метки столбцов и фильтры

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

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

Начните с подбора ключевых слов, перетащив поле «ключевое слово» в поле «Строки».Затем перетащите цифру объема поиска в поле «Значения».

Если вы смотрите на «Сумму объема поиска», а не на «Подсчет объема поиска», ваша таблица слева начнет иметь большой смысл:

Вы очень быстро заметите, что вы создали вещь красоты.

Сводная таблица со всеми ключевыми словами в вашем списке и всеми соответствующими значениями объема поиска.

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

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

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

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

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

Перейдите к Insert> Column or Bar chart , чтобы вставить диаграмму. Тем не менее, это может выглядеть немного грубовато по краям, поэтому давайте сделаем его намного лучше, чем это:

Во-первых, нам нужно организовать ключевые слова по количеству запросов, чтобы мы могли смотреть на нашу диаграмму как на хвостовой граф. Выделите данные по количеству запросов (столбец, который вы хотите отсортировать по количеству) и выберите « Data> Sort ».

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

Сортировать рейтинги по ключевым словам

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

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

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

Вместо этого выберите диаграмму и перейдите на панель « Формат ».

Вам нужно раскрывающееся меню « Current Selection » в дальнем левом углу экрана. Выберите раскрывающийся список и щелкните серию данных рейтинга, а теперь выберите «Выбор формата».

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

Работа с дополнительной осью и изменение типа диаграммы

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

Выберите «Пользовательский» из параметров в этом диалоговом окне и выберите параметр «Линия с накоплением» для вторичной оси:

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

Для этого нам нужно отредактировать вторичную ось.

Щелкните правой кнопкой мыши вторичную ось на диаграмме и выберите « Format Axis ».Установка минимального значения «1.0» исключает все нули в ваших данных рейтинга, а установка максимального значения, скажем, 15, исключает любой рейтинг выше 15-го. Вы выбираете диапазон, который подходит именно вам.

Наконец, при прокрутке вниз по этим параметрам появится флажок: выберите «значения в обратном порядке», и все почти готово.

Последние штрихи

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

Выберите данные рейтинга столбцов и перейдите к «Дизайн> Изменить тип диаграммы».Выберите вариант линейного графика с видимыми маркерами данных в строке. Теперь выньте цвет линии из «Форматировать ряд данных> Цвет линии», и все готово.

Вот диаграмма, которую я воспроизвел во время написания сообщения в блоге:

И вот она. Как сделать сводную диаграмму и таблицу в Excel. Удачи, делая свои собственные!

Как максимально эффективно использовать сводные таблицы в Excel 2019

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

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

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

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

могут быть созданы на основе сложных или простых данных, но они обычно полезны, когда у вас есть длинный список неорганизованных записей, для которых не задана сортировка или нормализация. Вы можете извлекать записи из базы данных, файла CSV, файла JSON, файла XML, Access или любого объекта, имеющего структуру, чтобы Excel 2019 мог определять строки и столбцы, используемые для суммирования данных.Структурированные данные распространены во многих базах данных, но извлечение данных из источника, такого как веб-страница, не будет иметь ту же структуру, которая может использоваться для сводных таблиц. Некоторые веб-страницы предоставляют структурированные данные, но во многих случаях вам потребуется очистить данные, если вы извлечете их с веб-страницы и захотите использовать их для анализа сводной таблицы.

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

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

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

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

(импорт сводной таблицы с помощью инструмента «Получить данные»)

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

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

(конфигурация сводной таблицы)

Excel необходимо знать, какие поля вы хотите использовать в своей таблице. В разделе «Поля сводной таблицы» панели конфигурации выберите поля (столбцы), которые вы хотите проанализировать.В этом примере будут проанализированы платежи клиентов, чтобы определить, сколько денег было зарабатывалось каждый день в феврале. Для этой цели нам нужны AmountPaid, AmountPaidDate и MemberId. Установите эти флажки и обратите внимание, что Excel автоматически передает данные столбца на активный рабочий лист.

(Сводная таблица создана)

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

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

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

(Отредактированная сводная таблица)

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

Редактирование функций суммирования

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

(опция настройки функций)

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

(Настройки выбора функции суммирования)

Текстовое поле «Пользовательское имя» — это место, где вы устанавливаете заголовок столбца. Excel 2019 автоматически устанавливает его как «Сумма <имя поля>», где «имя поля» — это имя столбца, настроенного в базе данных. Вы можете изменить это значение на любое значение заголовка столбца.

Во втором разделе окна настроек в списке «Суммировать поле значения по» показаны внутренние функции Excel, которые можно использовать для суммирования данных.Обратите внимание, что вы можете использовать Count, Average, Min, Max, Product и многие другие. Например, измените используемую функцию на «Подсчет», и Excel будет подсчитывать количество платежей вместо суммирования значений.

(функция СУММ изменена на СЧЁТ)

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

Редактирование поля «Группировать по»

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

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

(изменение функции строки)

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

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

(Неверные значения суммирования)

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

Теперь вы можете указать столбец «Группировать по» и столбец, который необходимо суммировать. Excel предоставляет функцию перетаскивания для сводных таблиц, где вы можете указать столбцы для раздела «Строки» (сгруппировать по) и столбцы для раздела «Значения» (суммировать). Перетащите столбец MemberId в раздел «Строки». Затем перетащите столбец AmountPaid в раздел «Значения». Теперь вы можете видеть, что Excel знает, что делать с указанными вами данными.

(Скорректированная сводная таблица)

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

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

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

(добавлен фильтр PaymentId)

После добавления PaymentId в раздел «Фильтр» Excel добавляет его в верхнюю часть таблицы.Вы увидите, что по умолчанию выбран фильтр «Все», что означает отображение всех данных. Щелкните стрелку раскрывающегося списка, чтобы просмотреть список идентификаторов платежей, включенных в ваши данные. Хотя вы не можете видеть идентификаторы платежей в сводной таблице, они по-прежнему являются частью импортированной информации из базы данных. Вы по-прежнему можете фильтровать столбец, который не включен в данные, отображаемые в таблице, поскольку он является частью включенных полей, перечисленных на панели конфигурации.

Щелкните любой идентификатор платежа в раскрывающемся списке, и Excel удалит все данные, кроме выбранного идентификатора.

(данные отфильтрованы по PaymentId)

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

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

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

Создание вычисляемого поля

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

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

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

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

(раскрывающийся список полей, элементов и наборов для добавления вычисляемого поля)

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

(окно «Вставить вычисляемое поле»)

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

(добавлено вычисляемое поле)

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

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

(сводная таблица с вычисляемым полем)

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

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

Добавление более сложных вычисляемых полей

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

Предположим, у вас есть комиссия по скользящей шкале для платежей. Вы можете заплатить 3% за комиссию менее 5 долларов и 4% за транзакцию на сумму более 5 долларов. Функцию ЕСЛИ можно добавить в вычисляемое поле, чтобы выполнить конкретный расчет на основе значения каждой строки сводной таблицы.

Нажмите кнопку «Поля, элементы и наборы» в разделе «Вычисления» на вкладке «Анализ», а затем выберите пункт меню «Вычисляемое поле», чтобы открыть окно конфигурации.Все остальные конфигурации аналогичны простому вычисляемому полю, но теперь вы добавляете в формулу функцию ЕСЛИ.

(Расчетное поле с функцией ЕСЛИ)

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

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

Использование слайсеров

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

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

(кнопка «Вставить слайсер»)

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

(окно настройки вставки слайсеров)

Установите флажок хотя бы рядом с одним столбцом. Данные в столбце используются для фильтра, поэтому вы должны использовать срезы только для определенных столбцов, которые имеют ограниченные, но уникальные данные. В этом примере в сводной таблице отображаются платежи за каждый день месяца.Вы можете использовать столбец «AmountPaidDate», чтобы фильтровать платежи по дням, чтобы получить сводку платежей за любой выбранный день. PaymentId — плохой выбор для среза, потому что каждый платеж имеет уникальный идентификатор. Если бы у вас было 1000 платежей в таблице, у среза было бы 1000 вариантов фильтра, и каждый фильтр имел бы только один результат.

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

(добавлен слайсер)

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

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

Скрытие и отображение промежуточных и итоговых результатов

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

(Варианты компоновки)

Щелкните одну из кнопок в разделе «Макет» и просмотрите параметры. Например, кнопка «Промежуточные итоги» отображает раскрывающийся список со списком вариантов отображения этого значения.

(варианты промежуточного итога)

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

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

Добавление временной шкалы

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

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

(кнопка «Вставить шкалу времени»)

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

(Вставить окно временной шкалы)

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

(фильтр временной шкалы)

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

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

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

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

(параметры обновления данных)

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

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

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

Пошаговое руководство (с видео)

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

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

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

Что такое сводная таблица?

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

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

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

Как использовать сводные таблицы

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

1. Сравните общие продажи различных продуктов.

Допустим, у вас есть рабочий лист, содержащий данные о ежемесячных продажах трех разных продуктов — продукта 1, продукта 2 и продукта 3 — и вы хотите выяснить, какой из трех принес больше всего денег.Вы, конечно, можете просмотреть рабочий лист и вручную добавлять соответствующий показатель продаж к промежуточной сумме каждый раз, когда появляется продукт 1. Затем вы можете сделать то же самое для продукта 2 и продукта 3, пока не получите итоговые значения для всех из них. Кусок торта, правда?

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

2. Отображение продаж продукта в процентах от общего объема продаж.

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

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

С помощью сводной таблицы вы можете настроить каждый столбец так, чтобы он отображал процентное соотношение всех трех столбцов, а не только общее количество столбцов. Если, например, продажи трех продуктов составили 200 000 долларов, а первый продукт принес 45 000 долларов, вы можете изменить сводную таблицу, указав, что этот продукт составил 22,5% от всех продаж компании.

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

3. Объедините повторяющиеся данные.

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

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

4.Получите количество сотрудников для отдельных отделов.

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

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

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

5. Добавьте значения по умолчанию в пустые ячейки.

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

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

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

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

1. Введите данные в ряд строк и столбцов.
2. Отсортируйте данные по определенному атрибуту.
3. Выделите ячейки, чтобы создать сводную таблицу.
4. Перетащите поле в область «Ярлыки строк».
5. Перетащите поле в область «Значения».
6. Точно настройте свои расчеты.

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

1. Введите данные в ряд строк и столбцов.

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

Например, чтобы создать таблицу Excel с данными о производительности сообщений в блоге, у вас может быть столбец со списком каждого «URL», столбец со списком каждого URL «Заголовок сообщения», столбец со списком «Просмотров до даты» и т. .(Мы будем использовать этот пример в следующих шагах.)

2. Отсортируйте данные по определенному атрибуту.

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

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

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

3. Выделите ячейки, чтобы создать сводную таблицу.

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

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

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

Примечание: Если вы используете версию Excel более раннюю, чем Excel 2016, «Сводные таблицы» могут находиться под «Таблицы» или «Данные» в верхней части навигации, а не «Вставить».»В Google Таблицах вы можете создавать сводные таблицы из раскрывающегося списка » Данные « в верхней части навигации.

4. Перетащите поле в область «Ярлыки строк».

После того, как вы выполните шаг 1, Excel создаст для вас пустую сводную таблицу. Ваш следующий шаг — перетащить поле, помеченное в соответствии с именами столбцов в вашей электронной таблице, в область «Ярлыки строк» ​​. Это определит, по какому уникальному идентификатору — заголовку сообщения в блоге, названию продукта и т. Д. — сводная таблица упорядочит ваши данные.

Например, предположим, вы хотите организовать группу данных блогов по заголовкам сообщений. Для этого просто щелкните и перетащите поле «Заголовок» в область «Ярлыки строк».

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

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

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

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

6. Настройте свои расчеты.

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

На Mac это можно сделать, щелкнув маленький « i» рядом со значением в области «Значения», выбрав нужный вариант и нажав «ОК».»После того, как вы сделаете свой выбор, ваша сводная таблица будет обновлена ​​соответствующим образом.

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

Копаем глубже с помощью поворотных столов

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

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

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

Хотите больше советов по Excel? Ознакомьтесь с этими советами по дизайну для создания диаграмм и графиков.

Примечание редактора: этот пост был первоначально опубликован в декабре 2018 года и был обновлен для полноты.

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

в Excel, как ее создать?

Что такое сводная таблица?

Microsoft описывает сводную таблицу в Excel (или сводную таблицу, если вы используете название функции, охраняемой товарным знаком!), Как «интерактивный способ быстрого суммирования больших объемов данных». Хорошее описание.

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

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

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

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

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

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

Как работает сводная таблица?

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

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

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

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

После того, как вы превратили свой диапазон в таблицу (используйте Ctrl-T, чтобы сделать это быстро!), Вам нужно выбрать ячейку в этой таблице, перейти к Вставить на ленте и выбрать Сводная таблица на крайний левый.

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

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

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

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

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

Добавление данных в сводную таблицу

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

1. Фильтры
2. Столбцы
3. Строки
4. Значения

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

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

1. Числа почти всегда попадают в квадрант ценностей.Это позволяет вам выполнять вычисления, сводные данные, средние значения и т. Д. Прямо из вашей сводной таблицы.
2. Даты часто помещаются в столбец «Строки», потому что…
3. Все, что вы помещаете в столбец строк, становится заголовками строк, все, что находится в квадранте столбцов, становится заголовками столбцов, поэтому для простоты использования поместите данные с дополнительными параметрами в столбец «Строки». (легче прокручивать вниз, чем целую вечность).
4. Квадрант «Фильтры» делает то, что вы ожидаете, он применяет фильтр ко всему набору данных.Супер полезно, если вы просто хотите показать что-то конкретное.

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

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

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

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

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

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

Настройки поля значений

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

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

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

Все это и многое другое объясняется в этом видео:

Слайсеры и фильтры

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

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

В этом видео наш эксперт по Excel Тоби покажет вам все, что вам нужно знать:

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

Саймон Сез ИТ преподает Excel более десяти лет. За небольшую ежемесячную плату вы можете получить доступ к более чем 90 обучающим курсам в области ИТ.

Другие классы Excel, которые могут вам понравиться:

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

Excel: средний уровень

Описание курса

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

Дэвид демонстрирует каждую технику как минимум дважды: во-первых, на слайде PowerPoint с пронумерованными шагами, а во-вторых, в версии Excel для Office 365 на основе подписки.Дэвид обращает ваше внимание на любые различия в старых версиях Excel (2019, 2016, 2013 и ранее) во время презентации, а также в своих подробных раздаточных материалах. Дэвид также предоставляет книгу Excel, которая включает большинство примеров, которые он использует во время веб-трансляции.

Office 365 — это продукт на основе подписки, который предоставляет обновления новых функций не реже, чем ежемесячно. И наоборот, в версиях Excel с бессрочной лицензией есть неизменные наборы функций. Версии с бессрочной лицензией имеют номера года, например Excel 2019, Excel 2016 и т. Д.

Темы:

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

Цели обучения:

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

Правила отмены курса

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

Excel — 5 простых шагов для начинающих

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

Голова кружится? Твои глаза остекленели? Вы не одиноки. Разглядывать все эти строки, столбцы и числа ошеломляет.

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

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

Практика ведет к совершенству!

Загрузите бесплатные таблицы Excel для практики

Что такое сводная таблица?

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

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

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

Любопытно? Посмотрите это видео, чтобы увидеть все различные возможности сводной таблицы Excel:

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

Как построить сводную таблицу: пример из практики

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

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

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

Например, люди пьют больше темного пива зимой? Лучшее понимание сезонности ему очень поможет, но для начала у него есть только электронная таблица, в которой разбиты его продажи каждого типа пива (стаут, пилснер, IPA и янтарь) за квартал в 2016 и 2017 годах.

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

Пройдем по ступенькам вместе с Джейсоном.

1. Очистите свои данные

Прежде чем вы начнете создавать что-либо в Excel, полезно быстро взглянуть на свои данные, чтобы убедиться, что все выглядит правильно.

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

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

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

2. Вставить поворотный стол

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

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

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

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

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

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

4. Выберите, что должна включать ваша сводная таблица.

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

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

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

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

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

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

5. Проведите анализ

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

Если нет, то все готово — вы готовы использовать сводную таблицу для определения тенденций!

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

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

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

Готовы начать?

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

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

Повысьте свои навыки работы с Excel

Пройдите курс по сводным таблицам сегодня!

Начать бесплатную пробную версию

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

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

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

Что такое сводная таблица?

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

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

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

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

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

Шаг 1 : Откройте лист Excel Online и выберите все ячейки, содержащие данные, которые вы хотите просмотреть. Шаг 2 : выберите Insert > PivotTable . Шаг 3 : Во всплывающем окне выберите Новый лист и нажмите ОК . Шаг 4 : В редакторе сводной таблицы перетащите строки и столбцы, которые вы хотите суммировать, в соответствующее поле. Шаг 5 : В разделе Значения выберите поля со значениями, которые вы хотите добавить или вычислить. Шаг 6 : Если вы хотите отобразить только значения, соответствующие определенным критериям, используйте раздел Фильтры .

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

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

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

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

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

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

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

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

Редактор разделен на два горизонтальных раздела.

• В верхнем разделе перечислены все поля — это все столбцы из данных вашей таблицы.

• В нижней части вы найдете фактическую область для управления сводной таблицей.Он разделен на четыре части: Фильтры , Столбцы , Строки и Значения .

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

Для начала вот анализ, который мы собираемся провести в качестве примера из демонстрационного листа:

Сколько мы выставили счет в 2017 году для каждого из наших клиентов по разным типам проектов?

В данном случае мы ищем четыре вещи.

• Для каждого клиента

• по всем типам проектов

• Общая сумма выставленного счета

• в 2017 году

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

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

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

• Параметр Фильтр поможет нам отфильтровать данные только за 2017 год и скроет все остальное.

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

Добавить строки

Начнем с добавления поля «Имя клиента» в раздел « строк, ». Есть несколько способов сделать это (например, щелкнуть галочку рядом с заголовком Client Name на боковой панели, щелкнуть раскрывающийся список в конце раздела Client Name и выбрать Add to Row Labels ), но лучший способ продолжить — использовать функцию перетаскивания.

Щелкните и удерживайте поле Client Name , перетащите его в раздел Rows в нижней половине боковой панели и отпустите.

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

Добавить столбцы

Затем мы добавим поле «Тип проекта» в качестве столбца. Теперь, когда вы знаете подробности, просто перетащите поле Project Types в раздел Columns в нижней части редактора сводной таблицы.

Добавить значения

Теперь у вас есть двумерная таблица, и пора добавить значения в эту таблицу.

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

Добавить фильтры

У нас еще нет полного ответа.Теперь нам нужно отфильтровать данные, чтобы отображались только значения с 2017 года.

Для этого перетащите поле Year в раздел Filters . Вы увидите две новые ячейки вверху листа. В ячейке рядом с Годом сейчас указано Все . Щелкните раскрывающийся список рядом с ячейкой, снимите флажок рядом с кнопкой 2018 и нажмите ОК .

Все записи за 2018 год мгновенно исчезнут, и вы получите ответ на исходный вопрос: Сколько мы выставили счет в 2017 году для каждого из наших клиентов по разным типам проектов?

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

Diving Deeper

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

Какому клиенту мы выставляли больше всего счетов в 2017 году?

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

Итак, начните с удаления поля Project Type из раздела Columns : перетащите его из раздела (альтернативно, вы можете щелкнуть поле и выбрать Remove Field ).

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

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

Давайте ответим еще на один вопрос: У какого типа проекта был самый высокий счет почасовая ставка в среднем?

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

Для этого удалите Client Name из раздела Rows и замените его на Project Type .