Как Перетащить Столбец в Сводной Таблице Excel • Область строки

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

Мы разберем 3 основных варианта группировки полей сводной таблицы в зависимости от типа данных:

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Если выполнить сортировку сводной таблицы по столбцу Grand Total в порядке убывания, то месяц с наибольшим результатом продаж будет расположен вверху таблицы, следующим окажется второй по величине и так далее. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Разметка страницы» главного меню, затем щелкните на пункте «Параметры страницы», в открывшемся окне перейдите на закладку «Лист» и укажите значения в полях «сквозные строки» и (или) «сквозные столбцы».

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

После этого кликните правой кнопкой мыши по сводной таблице и нажмите Обновить (Refresh). Данные в сводной таблице будут обновлены, и теперь группировка строк или столбцов должна быть доступна.

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

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

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

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

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

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

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

    специалист
    Мнение эксперта
    Витальева Анжела, консультант по работе с офисными программами
    Со всеми вопросами обращайтесь ко мне!
    Задать вопрос эксперту
    Если нужно пойти еще дальше и объединить возрастные группы в категории 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
    Часто возникает необходимость выполнить группировку в сводной таблице по заголовкам строки или столбца. Для численных значений Excel может сделать это автоматически (в том числе для дат и времени). Далее это продемонстрировано на примерах.

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

    Обратите внимание, что данные в таблице можно сортировать и по алфавиту, упорядочив имена продавцов. Для этого нужно кликнуть по одному из имён (ячейки B4 или C4) и выполнить сортировку в порядке возрастания или убывания.

    Пример 2: Группировка в сводной таблице по диапазону

    Предположим, мы создали сводную таблицу (как на картинке ниже), которая группирует список из 150 детей по возрасту. Группы делятся по возрастам от 5 до 16 лет.

    Если нужно пойти еще дальше и объединить возрастные группы в категории 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так:

    1. Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с возрастами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для чисел.Excel автоматически заполнит поля Начиная с (Starting At) и По (Ending At) минимальным и максимальным значениями из наших исходных данных (в нашем примере это 5 и 16).
    2. Мы хотим объединить возрастные группы в категории по 4 года, следовательно, в поле С шагом (By) вводим значение 4. Жмём ОК.Таким образом возрастные группы будут объединены в категории начиная с 5-8 лет и далее с шагом по 4 года. В итоге получится вот такая таблица:
    Группировка данных в сводной таблице в Excel.
    Сортировка» состоит из единственного флажка: «Использовать списки при сортировке». Если флажок включен, Excel сможет сделать сортировку строк сводного отчета по спискам пользователя. В таком списке, например, могут быть перечислены названия дней недели.
    специалист
    Мнение эксперта
    Витальева Анжела, консультант по работе с офисными программами
    Со всеми вопросами обращайтесь ко мне!
    Задать вопрос эксперту
    Сохранять исходные данные вместе с файлом при включенном состоянии обеспечивает хранение данных из внешнего источника прямо внутри рабочей книги. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
    Сводные таблицы — это замечательный встроенный инструмент отчетности в Excel. Обычно они используются для суммирования данных с итогами, но вы также можете использовать их для вычисления процента изменения между значениями. Даже лучше: это просто сделать.
    Как Перетащить Столбец в Сводной Таблице Excel • Область строки

    Сводная таблица в Excel. Как сделать? ЭКСЕЛЬ ХАК

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

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

    Excel 2007: настройка параметров сводных таблиц

    Бухгалтер&Компьютер

    Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

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

    1) открываем сводный отчет. Я выбрал таблицу, изображенную на рис. 1 на с. 32. Сейчас в ней все данные оформлены шрифтом

    Arial обычного начертания, размером 8 пт. Формат представления чисел: для стоимостных показателей — два знака после запятой, для количественных показателей — ноль знаков после запятой;

    3) не снимая выделения, щелкаем на блоке правой кнопкой мыши;

    Формат ячеек…». Появится одноименное окно «Формат ячеек»;

    Число» и выбираем вариант «Числовой» с тремя десятичными знаками после запятой;

    Формат ячеек» нажимаем «ОК». Значения в блоке «C3:E3» приобрели новое форматирование;

    7) опять щелкаем правой кнопкой мыши в области сводной таблицы;

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

    Важно! По умолчанию при обновлении сводной таблицы ячейки отчета получают параметры форматирования, характерные для стиля «

    Обычный». Конкретные значения этих параметров зависят от настроек в вашей версии программы Excel.

    Снять указанную проблему очень просто — нужно определить параметры форматирования непосредственно в сводном отчете. Вот как это сделать:

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

    щелкаем правой кнопкой мыши. Появится контекстное меню, как на рис. 2;

    Параметры полей значений». Появится окно «Параметры поля значений», как на рис. 3 (на с. 33);

    Числовой формат». Появится знакомое окно «Формат значений». Правда, в усеченном варианте: в нем представлена единственная закладка — «Число»;

    5) выбираем параметры форматирования для выбранного значения в сводном отчете (в нашем случае — это объемы реализации в гривнях). Согласно примеру в окне «

    Формат ячеек» нужно выбрать числовой формат с округлением до трех знаков;

    ОК». Форматирование данных в сводном отчете изменилось;

    7) щелкаем правой кнопкой мыши в области сводной таблицы;

    Обновить». Форматирование таблицы осталось без изменений.

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

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

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

    1) открываем сводный отчет, я выбрал таблицу, изображенную на рис. 4 (см. с. 33). В ней значениями являются «

    Сумма, грн.» и «Кол., шт.». Ключевыми полями этой таблицы являются «Дата» и «Покупатель»;

    2) щелкаем правой кнопкой мыши на любой ячейке в колонке «

    Покупатель» Например, на «A3». Появится контекстное меню, как на рис. 5 (см. с. 33);

    Параметры поля…». Появится окно, как на рис. 6 (см. с. 34). В этом окне нам предстоит определить параметры для ключевого поля.

    Как видим, форма окна изменилась. Теперь в нем две закладки: «

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

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

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

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

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

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

    4) среди параметров форматирования полей в области значений доступны только настройки представления чисел;

    5) для ключевых полей можно определить способ подведения промежуточных итогов и макет сводного отчета.

    Теперь вернемся к четвертому пункту наших выводов. Бросается в глаза тот факт, что окно «

    Формат ячеек» при работе с полями в области значений представлено единственной закладкой для форматирования чисел. И это не случайно. Делаем так:

    1) выделяем в отчете на рис. 1 любой блок ячеек (например, «

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

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

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

    Работа со сводными таблицами». Щелкаем на этом пункте левой кнопкой мыши, переходим в режим работы со сводными таблицами. Здесь доступны два раздела: «Параметры» и «Конструктор» (рис. 8). Нас сейчас интересует раздел «Параметры». Посмотрим на его состав подробнее.

    Лента меню раздела параметров состоит из восьми групп иконок. Первая группа — «

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

    Объединить и выровнять по центру ячейки с подписями» (рис. 9) работает в тех случаях, когда в области данных сводного отчета расположено несколько полей. Применительно к таблице на рис. 1 этот параметр объединит ячейки в колонке «Покупатель» таким образом: «A3:A4», «A5:A6» и т. д. Наименование покупателя в объединенных ячейках будет расположено по центру (рис. 10):

    Отступ для названий строк в сжатой форме» позволяет указать отступ строк в области заголовка строки при отображении отчета в компактном формате. Уровень отступа может принимать значения от «0» до «127»;

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

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

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

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

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

    Следующие два параметра имеют непосредственное отношение к форматированию сводной таблицы:

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

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

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

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

    Внешний вид окна этой закладки показан на рис. 11. Первой в окне расположена группа «

    Параметры», затем на иконке «Параметры» группы «Сводная таблица»;

    Параметры сводной таблицы» переходим на закладку «Итоги и фильтры»;

    Показывать общие итоги для строк», «Показывать общие итоги для столбцов». Отчет примет форму, как на рис. 12 (см. с. 37).

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

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

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

    Совет Если вы не пользуетесь специфическими правилами сортировки, отключите флажок «

    Использовать списки при сортировке». Особенно при работе с большими таблицами. Это существенно ускорит обработку данных.

    Экран» (в некоторых версиях Excel 2007 она называется «Отображение») и «Сортировка списка полей». В группе «Экран» есть семь параметров (рис. 13). Вот основные из них:

    названия строк» добавляем поле «Дата». В области «Значения» оставляем только поле «Сумма, грн». Теперь в сводном отчете появились символы группировки (рис. 14 на с. 38);

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

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

    — флажок «Показывать контекстные всплывающие подсказки» работает только при работе с документом на экране. Чтобы посмотреть на действие этого параметра, сделайте так:

    2) наведите указатель мыши на любое поле внутри сводной таблицы. Через некоторое время появится всплывающее окно с описанием этого поля;

    3) откройте окно «Параметры сводной таблицы» на закладке «Вывод».

    4) отключите флажок «Показывать контекстные всплывающие подсказки»;

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

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

    — флажок «Показывать названия полей и списки фильтров» влияет на заголовки сводной таблицы. Его работу покажем на таком примере:

    4) отключите флажок «Показывать названия полей и списки фильтров». Отчет примет форму, как на рис. 15.

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

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

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

    Есть база данных, как на рис. 1. Объем файла большой, но меня интересует перечень товаров, которые содержатся в этой базе. Делаем так:

    Параметры» (раздел меню по работе со сводными таблицами);

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

    Вставить», из появившегося меню выбираем «Специальная вставка…»;

    11) в появившемся окне специальной вставки ставим переключатель «

    В списке на рис. 16 нет полей в области данных, однако есть заголовок списка (колонка «

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

    Вывод» является переключатель «Сортировка списка полей». Он может быть установлен в одно из двух положений: «от А до Я» — сортировать по алфавиту или «как в источнике данных» — сохранить порядок следования записей как в исходной базе. Такое значение принято по умолчанию. Изменять его, как правило, не нужно.

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

    Excel 2007 вынесли на отдельную закладку (рис. 17). Она так и называется — «Печать». На закладке всего три элемента:

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

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

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

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

    Параметры этой закладки (рис. 18 на с. 40) влияют на способ хранения данных с обновлением сводного отчета:

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

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

    С5» (объем реализации на сумму 236,20 грн. для ЧП «Лапина» за «03/01/2009»);

    С5». Excel создаст новый рабочий лист и перенесет в него те записи исходной базы данных, которые связаны с выбранным элементом отчета (рис. 19).

    Разрешить отображение деталей» отключить, после двойного щелчка в области данных Excel сообщит о невозможности корректировать сводную таблицу;

    Совет Работая со сводным отчетом, оставляйте включенным флажок «

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

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

    Обновление при открытии файла» действует индивидуально для каждой сводной таблицы.

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

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

    Число элементов, сохраняемых для каждого поля» в состояние «Максимум». В этом случае в сводном отчете Excel 2007 сможет хранить до 1 048 576 различных значений ключа.

    На этом мы завершим работу с параметрами сводных таблиц. А в следующий раз поработаем с Конструктором сводных таблиц и познакомимся с настройкой источников данных для сводных отчетов.

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

    Excel 2007: настройка параметров сводных таблиц. Бухгалтер&Компьютер, № 13, Июль, 2010 | Factor

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

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

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