Сводные таблицы. Часть 1
Создание сводной таблицы и настройки отчёта





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

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

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

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

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Вставка сводной таблицы, интерфейсные изменения

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

Теперь мы готовы к созданию сводной таблицы. Выделите любую заполненную ячейку таблицы данных и выполните команду на ленте Вставка – Таблицы – Сводная таблица [Insert Tables PivotTable]. Excel автоматически определит границы таблицы и выведет на экран диалоговое окно, в котором его можно скорректировать при необходимости.  Стоит проверить, все ли строки захвачены пунктирной рамкой, для этого протащите вертикальную полосу прокрутки в самый низ – ее размеры изменелись в соответствии с количеством использованных строчек

В случае необходимости выделите правильный диапазон таблицы начиная со строки заголовка, и далее нажмите ОК. Excel добавит новый лист перед листом с исходными данными и изменит интерфейс, а именно: в правой части окна появится Поля сводной таблицы [PivotTable_Fields](в 2010 и более ранних версиях программы – Список полей сводной таблицы), а в правой части ленты две контекстные вкладки группы Работа со сводными таблицами [PivotTables_Tools]Анализ [Analyze](в 2010 и более ранних версиях программы – Параметры [Options]) и Конструктор [Design]. В рабочей части листа активна ячейка A3– начиная с нее будет выводиться отчет сводной таблицы. В общих чертах хотелось бы проговорить – для чего нужен каждый из перечисленных инструментов:

 

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

Начинаем собирать наш отчет и потом обращаемся к окну с Полями сводной таблицы [PivotTable_Fields]– в верхней его части содержится список заголовков исходной таблицы, а в нижей – четыре области, которые отвечают за структуру таблицы (области названия столбцов – Колонны [Columns]и область названия строк – Строки [Rows]), вывод итоговых значений (область Значения [Values]) и дополнительные возможности по созданию страниц сводной таблицы (область Фильтры [Filters]). В отчете сводной таблицы могут участвовать далеко не все свтолбцы исходной таблицы, а только те, которые интересны для анализа. Перемещать их из верхней части Списка полейв нижние области можно при помощи флажков – окошко установки флажка предусмотрено слева от названия поля, но так как Excel очень часто некорректно определяет местоположение поля, я предпочитаю вручную перетащить его в нужное место, а для этого навожу курсор на название поля в верхней части списка – курсор принимает вид четырехнаправленной стрелки – и только тогда нажмимаю и удерживаю левую кнопку мыши, далее выполняю перенос заголовка столбца в небходимую область. Только что описанным способом расположите поля, чтобы таблица приняла вид как на картинке (синими стрелками обозначены все выполненные переносы полей)

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

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

Далее продолжаем работать с таблицей в компактном виде (то есть все поля свернуты). Если список полей занимает на экране много места – скройте его, для этого предусмотрен крестик в его верхней правой части. Чтобы вернуть список полей – вызовите контекстное меню, находясь в любой ячейке отчета сводной таблицы, и выберите нижнюю команду Показать список полей [Show_Field_List]. Список полей однозначно стоит прятать при отправке отчета пользователю

 

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

ИЗМЕНЕНИЕ ФУНКЦИИ ИТОГОВ: В получившейся сводной таблице сразу хочется обратить внимание на итоговые значения – это явно не общая сумма по столбцу Кол-во, который мы помещали в эту область. А вот что это – можно прочитать в ячейке A3– здесь программа всегда отчитывается о том, какую функцю применила для расчета итоговых значений. Сейчас там написано Количество по полю Кол-во кг.Почему не сумма, повсеместно используемая программой по умолчанию, а кименно оличество? Дело в том, что при работе со сводными таблицами Excel тоже любит суммировать значения, но если в диапазоне данных встречаются пустые ячейки, то может быть просто посчитано количество значений, а не их сумма. Чтобы вернуть Excel на путь истинный, на любом значении нажмите правую кнопку мыши и в контекстном меню доберитесь до команды Итоги по [Summarize_Values_By]– далее в дополнительном списке останется только указать правильную функцию и итоговые значения будут пересчитаны. В нашем случае – просуммированы:

РАСШИФРОВКА ЗНАЧЕНИЯ: Если в процессе работы с отчетом сводной таблицы заинтересовало какое-либо конкретное значение – выполните на нем двойной щелчок левой кнопкой мыши и на отдельном листе, которые программа заботливо добавить перед листом сводной таблицы, будет выведена расшифровка этого значения. Созданный лист никак не влияет на отчет сводной таблицы и при желании его можно удалить. Обратите внимание, что сформированная на нем выборка данных оформлена в виде умнойтаблицы

НАСТРОЙКА ФОРМАТОВ: Для удобства восприятия данных однозначно хочется настроить числовой формат значений – убрать знаки после запятой и добавить визуальный пробел между тысячными разрадами. Для решения такой задачки можно пойти стандартным путем – выделить все ячейки и использовать команду Формат ячеек [Format_Cells], а можно оптимизировать и ускорить эту процедуру. Наступите на любую ячейку с числовым значением и в контекстном меню выберите команду Числовой формат [Number_Format](кстати, в контекстном меню сводной таблицы все команды уникальны начиная с третьей) – далее совершенно привычным образом настройте формат ячейки в появившемся окне (от классического его отличает число вкладок – всего одна) и нажимаете ОК– точно такой же формат будет применен ко всем ячейкам сводной таблицы, относящимся к этому же полю

ОБНОВЛЕНИЕ ОТЧЕТА: Следом предлагаю посмотреть, как поведет себя сводная таблица при изменениях в исходной. Вернемся на лист Продажии в любой пустой ячейке по количеству, в H6например, введите любое числовое значение – я введу 500. И проверьте итоговое значение отчета – оно не изменилось. Обновляется отчет сводной таблицы по пользовательскому запросу и сделать его очень легко – вызовите контексное меню, находясь на любой ячейке отчета сводной таблицы и выберие в нем команду Обновить [Refresh]– значения тут же будут пересчитаны. Если же данные не просто изменились, а добавились новые строки и/или столбцы к исходной таблице, то диапазон обработки нужно будет скорректировать при помощи команды, расположенной на контекстной вкладке ленты Анализ – Данные – Источник данных [Analyze DataChange_Data_Source]. При нажатии на указанную кнопку Excel перебросит вас на лист с исходной таблицей и пунктирной рамкой выделит диапазон ячеек, по которому сейчас работает – просто выделите заново нужный диапазон и нажмите ОК

ПЕРЕИМЕНОВАНИЕ ЭЛЕМЕНТОВ: Откройте пару разделов в строках таблицы путем нажатия на значок плюса, расположенного слева от номенклатурных названий, и станет доступна расшифровка данных по сортам, обозначенным в нашем случае цифрами 1, 2 и 3. Для нас такие обозначения вполне понятны, но для коллег и руководства эти цифры могу остаться загадкой. Поэтому лучше дать таким элементам более понятные и информативные имена, к тому же это достаточно просто сделать. Выделите ячейку с цифрой 1 в любом из разделов и просто начните вводить новое название Первый сорт(не выполняя двойного щелчка по ячейке!), завершите ввод нажатием клавиши Enter– название изменится, причем во всех разделах сразу.

Если есть необходимость перейти в режим редактирования – например, чтобы Склад 1переименовать в Склад № 1– делаем это не через двойной щелчок по ячейке, а через строку формул, либо при помощи клавиши F2, после чего корректируем текст нужным образом и завершаем ввод опять-таки нажатием на калвишу Enter

АВТОПОДБОР ШИРИНЫ СТОЛБЦОВ: Названия столбцов перестали помещаться по ширине и следующее вполне объяснимое желание – исправить эту ситуацию. Предлагаю настроить одинаковую ширину всем столбцам, а для этого сначала выделяем такие столбцы и далее регулируем ширину любого из них за правую границу (либо через контексное меню и команду Ширина столбца [Column_Width]) – выполненная настройка применится ко всем элементам выделенного фрагмента

На первый взгляд все очень хорошо получилось, но попробуйте теперь открыть любой из разделов в строчках – нажмите на любой значок плюс или минус слева от названия строк – и ширина столбцов будет сброшена, а точнее – индивидальным образом подобрана для каждого из них по самой длинной текстовой строке. Такая особенность отчета сводной таблицы не всегда удобна и ее можно отключить. На любой ячейке отчета вызовите контекстное меню и выберие в нем команду Параметры сводной таблицы [PivotTables_Options]. В открывшемся окне на первой же из вкладок Макет и формат [Layout_&_Format]снимите флажок Автоматически изменять ширину столбцов при обновлении [Autofit_column_width_on_update]и нажмите ОК. Теперь повторно настройте нужную ширину всем столбцам и убедитесь, что она будет неизменна при любых действиях над элементами столбцов или строк

ПЕРЕМЕЩЕНИЕ ЭЛЕМЕНТОВ И СОРТИРОВКА: Все элементы в строчках и столбцах отчета сводной таблицы выводятся в соответствии с алфавитным порядком названий; и конечно может потребоваться изменить порядок следования элементов. В моем примере хочу отделить грибы от фруктов и овощей, а для этого нужно каким-то образом расположить рядом элементы Груздии Шампиньоны. Операция перемещения элементов одинаково выполняться и в строчках, и в столбцах отчета сводной таблицы; и для ее осуществления есть пара способов:

Чтобы вернуться к первоначальному списку элементов (алфавитному), на любом номенклатурном названии вызовите контекстное меню и найдите в нем команду Сортировка– в дополнительном списке команд найдите алфавитный порядок и выберите его левой кнопкой мыши

КАК УБРАТЬ ФОРМУЛА 1: Если все же Вам посчастливилось потянуть ячейку с названием за нижний правый уголок и наделать таким образом толпу строчек или столбцов типа Формула 1, то попытку отменить действие или удалить такие строки можно оставить – вопрос решается другим способом. Удалить вычисляемые элементы с формулами в сводной таблице можно через контекстную вкладку ленты Анализ – Вычисления – Поля, элементы и наборы – Порядок вычислений [AnalyzeCalculationsFields_ Items_&_SetsSolver_Order]. В открывшемся окне выделите один из элементов и жмите кнопку Удалить [Delete] в нижней его части. Эту кнопку придется нажать столько раз, сколько ненужных Формулуспели создать, удалить их оптом сразу все Excel не позволяет. Из отчета сводной таблицы ненужные строки пропадут после нажатия на кнопку Закрыть [Close]

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

Похожие материалы

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

Функции для вычислений по дням
Функции даты и времени
Как посчитать календарные или рабочие дни? Можно ли прибавить к дате несколько месяцев? Как это делать правильно, используя функции РАБДЕНЬ, ЧИСТРАБДНИ, ДАТАМЕС, СЕГОДНЯ и пр.

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

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



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