Группировка в сводной таблице excel

Базовые особенности работы с таблицами

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

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

Особенно это важно при работе с Power Query и Power Pivot. Давайте присвоим таблице имя «Отчет»

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

Увидеть название таблицы можно и при ручном вводе формулы.

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

Начинающий пользователь сразу скажет: «Боже, как можно все это выучить»? Но на самом деле, этого не нужно делать, поскольку в ходе набора формулы появляются подсказки. Главное – не забыть открыть квадратную скобку (ее можно найти в английской раскладке там, где у нас находится кнопка «х»).

8

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

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

=Отчет

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

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

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

В этом курсе:

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

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

Вложенные группы

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

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

Примечание! В квартал входит 3 месяца, а в полугодие 6. При составлении таблицы для примера — это правило было нарушено. Здесь в квартал входит 4 месяца, а в полугодие 8, что является фактическим нарушением принятых норм.

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

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

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

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

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

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

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

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

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

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

Становится интересней! Наша сводная таблица начинает обретать форму…

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

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

Как удалить СТ

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

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

Для удаления результатов вычислений выполняем следующие шаги:

• помечаем мышкой любую ячейку СТ;
• выбираем на верхней панели вкладку «Анализ»;
• из перечня доступных действий выбираем «Очистить»;
• кликаем на пункте «Очистить всё».

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

• снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
• выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
• щёлкаем ПКМ внутри выделенной области;
• из контекстного меню выбираем пункт «Скопировать»;
• переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
• выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».

В итоге сводная таблица будет стёрта с сохранением результатов.

СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.

Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:

• выбираем СТ, предназначенную для удаления;
• жмём вкладку «Параметры»;
• в «Группе действий» выбираем пункт «Очистить», а затем – «Очистить всё».

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

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

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

Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.

Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel

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

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

Вторая — используемый для работы файл, взят пользователем из Internet Explorer, что также порождает возникновение ошибки «Недопустимая ссылка на источник данных». Решением проблемы, как и первом случае, будет небольшая корректировка используемого файла, а  точнее места его нахождения. Достаточно сохранить используемый файл и работу с ним можно продолжить.

Предыдущая запись ТОП-5 бесплатных онлайн-игр
Следующая запись Как полностью удалить DirectX с компьютера

Как добавить новую строку в Excel

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

Как вставить новую строку

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

Для начала открываем/создаем таблицу, выбираем любую ячейку в строке, над которой мы хотим вставить новую строку. Щелкаем правой кнопкой мыши по этой ячейке и в выпадающем меню кликаем по команде “Вставить…”.Также, для данной функции можно пользоваться горячими клавишами Ctrl и “+” (одновременное нажатие).

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

Все готово, новая строка добавлена

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

Примечание: Есть еще один способ добавления новой строки. Кликаем правой кнопкой мыши на номер строки, над которой хотим вставить новую строку и выбираем в появившемся меню пункт “Вставка”.

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

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

1. Для начала мы выделяем всю последнюю строку таблицы, щелкнув левой кнопкой мыши по ее номеру. Затем наводим курсор на нижний правый угол строки, пока он не изменит свою форму на “крестик”.
2. Зажав “крестик” левой кнопкой мыши тянем его вниз на то количество строк, которое хотим добавить, и отпускаем кнопку.
3. Как мы видим, все новые строки автоматически заполнились данным из дублируемой ячейки с сохранением форматирования.Чтобы очистить автозаполненные данные, выделяем новые строки, далее нажимаем клавишу “Delete”. Также можно щелкнуть по выделенным ячейкам правой кнопкой мыши и в открывшемся меню выбрать “Очистить содержимое”.
4. Теперь все ячейки из новых строк пусты, и мы можем добавлять в них новые данные.

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

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

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

1. Выделяем область ячеек, которые должны войти в “умную” таблицу. Далее переходим во вкладку «Главная» и нажимаем «Форматировать как таблицу». Нам будет предложено много вариантов дизайна. Можно выбрать любой понравившийся, так как в практическом функционале они все одинаковы.
2. После того, как мы выбрали стиль, перед нами откроется окно с координатами выбранного ранее диапазона. Если он нас устраивает, и мы не хотим внести в него какие-либо изменения, нажимаем кнопку «OK». Также, стоит оставить галочку “Таблица с заголовками”, если в действительности так и есть.
3. Наша «умная” таблица готова для дальнейшей работы с ней.

Как вставить новую строку в “умной” таблице

Чтобы создать новую строку, можно использовать уже описанные выше методы.

1. Достаточно нажать на любую ячейку правой кнопкой мыши, выбрать “Вставить” и далее – пункт “Строки таблицы выше”.
2. Также строку можно добавить горячими клавишами Ctrl и “+”, чтобы не тратить время на дополнительные пункты в меню.

Как вставить новую строку в конце “умной” таблицы

Есть три способа, как добавить новую строку в конце «умной” таблицы.

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

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

• Можно просто начать вводить данные в строке сразу под таблицей, и она автоматически станет частью нашей “умной” таблицы.
• Находясь в самой нижней правой ячейке таблицы просто нажмите клавишу “Tab” на клавиатуре.

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

  Заключение

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

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

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

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

Исходные данные для сводной таблицы

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

Шаг 1. Выделить таблицу Excel

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

Как быстро выделить таблицу:

• Выбрать ее любую ячейку и нажать Crtl + * или Ctrl + A, или
• Выбрать самую первую ячейку в таблице, зажать кнопки Ctrl и Shift, а затем нажать на кнопки вправо, затем вниз (→↓).

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

Шаг 2. Создать сводную таблицу

Создайте сводную таблицу: перейдите на вкладку Вставка и выберите «Сводная таблица».

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

Когда сводная таблица добавлена, на листе появляется область сводной таблицы. Если эта область не активна (вы не выделили ее мышкой), на ней будет подсказка: «Чтобы начать работу с отчетом сводной таблицей, щелкните в этой области». Щелкаем по ней мышкой и происходят две вещи:

1. Справа появится список полей сводной таблицы.
2. В меню — две дополнительные вкладки, связанные с управлением сводной таблицей (Анализ и Конструктор).

Шаг 3. Добавить в сводную таблицу необходимые поля

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

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

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

Как сгруппировать строки в Excel?

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

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

Как сгруппировать строки в Excel с помощью разворачивания / сворачивания?

Например, посмотрите на данные ниже.

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

Выполните следующие шаги, чтобы сгруппировать строки в Excel.

Шаг 1: Сначала создайте промежуточный итог, как показано ниже.

Шаг 2: Теперь выберите первые строки штата (Калифорния), исключая промежуточные итоги.

Шаг 3: Перейдите на вкладку ДАННЫЕ и выберите опцию «Группа».

Шаг 4: Щелкните раскрывающийся список в Excel «Группа» и снова выберите «Группа».

Шаг 5: Теперь он спросит вас, группировать ли строки или столбцы. Поскольку мы группируем «Ряды,»Выберите строки и нажмите ОК.

Шаг 6: В тот момент, когда вы нажмете «ОК», вы увидите линию стыка с левой стороны.

Нажми на «МИНУС»Значок и увидеть волшебство.

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

Теперь снова выберите город «Колорадо»И нажмите«Группа»Вариант.

Теперь он будет сгруппирован для «Колорадо» штат.

Группировать с помощью сочетания клавиш

С помощью простого ярлыка в Excel мы можем очень легко группировать выбранные строки или столбцы. Сочетание клавиш для быстрой группировки данных: SHIFT + ALT + клавиша со стрелкой вправо.

Сначала выберите строки, которые нужно сгруппировать.

Теперь нажмите горячую клавишу SHIFT + ALT + клавиша со стрелкой вправо чтобы сгруппировать эти строки.

Выше мы видели, как сгруппировать данные и как сгруппировать строки с опцией развертывания и свертывания с помощью значков ПЛЮС и МИНУС.

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

Удивительно, не правда ли? Используя «Авто контур,”Мы можем автоматически группировать данные.

Пример # 1 — Использование Auto Outline

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

Теперь поместите курсор в диапазон данных. В раскрывающемся списке «Группа» мы можем увидеть еще один вариант, кроме «Группа», то есть «Авто контур. »

В тот момент, когда вы нажмете на эту опцию «Auto Outline», она сгруппирует все строки, которые находятся над строкой промежуточных итогов.

Как это круто . Очень круто, не правда ли ??

Пример # 2 — Использование промежуточных итогов

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

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

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

На вкладке «Данные» у нас есть опция «Промежуточный итог«Рядом с»Группа»Вариант.

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

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

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

Теперь выберите столбцы, которые нужно суммировать. Нам нужно резюме «Продажи и стоимостьСтолбцы, поэтому выбирайте то же самое. Нажмите «ОК».

Вложенные группы

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

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

Примечание!В квартал входит 3 месяца, а в полугодие 6. При составлении таблицы для примера — это правило было нарушено. Здесь в квартал входит 4 месяца, а в полугодие 8, что является фактическим нарушением принятых норм.

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