Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

Список макетов сводных таблиц

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

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

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

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

С правой стороны вы увидите список полей PowerPivot. Это третье изменение в списке полей таблицы pivot.

Список полей PowerPivot

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

Приборная панель содержит два новых поля: Горизонтальные срезы и Вертикальные срезы.

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

Поставьте галочку в поле Выручка. Это поле содержит числовые данные, которые автоматически появятся в поле Значения справа от списка полей. В сводной таблице вы увидите, что поле Выручка содержит 1,8 миллиона строк.

Поле значений

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

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

Перетащите поле StoreName в строку Labels, чтобы оно оказалось ниже поля Region.

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

  1. Перетащите поля Год и Месяц в область вертикального среза.
  2. Перетащите поле Mall Developer в область горизонтального среза.

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

  1. На вкладке Задания -> Построитель в группе Параметры стиля перекрестных таблиц установите флажок Чередующиеся строки.
  2. На той же вкладке в группе Стили перекрестных таблиц выберите стиль, который вы хотите использовать для перекрестной таблицы.
  3. Перейдите на вкладку конструктора. В группе активных полей должно быть написано Доход по сумме. Если метка другая, выберите любое числовое поле в поворотной таблице.
  4. В той же группе нажмите на Параметры поля.
  5. В появившемся диалоговом окне Параметры поля значений в левом нижнем углу нажмите кнопку Числовой формат.
  6. В списке Формат выберите Числовой, установите число десятичных знаков равным нулю и установите флажок Разделитель групп чисел. Нажмите OK, чтобы закрыть окно Параметры поля значений, и нажмите OK, чтобы закрыть окно Формат ячеек.
Читайте так же:
Как посчитать, сколько ячеек содержат определенный текст или значение в Excel?

Числовой формат

Результат — впечатляющий интерактивный отчет.

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

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

Вам также могут быть интересны следующие статьи

Один комментарий

Добрый вечер. Очень информативная статья. Спасибо. Теперь мне нужно попробовать настроить pivot, так как мне очень нужны эти возможности XL в работе.

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

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

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

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group) ;
  3. В поле Начиная с (Starting at) ввести начальную дату для группы;
  4. В поле по (Ending at) ввести конечную дату для группы;
  5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
  6. Нажать OK

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

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше: <110000.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group) ;
  3. В поле Начиная с (Starting at) ввести первый элемент группы (110000);
  4. В поле по (Ending at) ввести последний элемент группы (130000);
  5. В поле с шагом (By) указать шаг для детализации в каждой группе (10000).
Читайте так же:
Как правильно суммировать числа с единицами в диапазоне в Excel?

Что делать, если данные не группируются
При группировке дат и числовых полей есть небольшой нюанс: иногда, при наличии дат или чисел в ячейках и вызове группировки можно получить сообщение «Выделенные объекты нельзя объединить в группу.«:
Выделенные объекты нельзя объединить в группу
И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:
Число как текст
В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(Ctrl+Shift+стрелка вниз)). После чего прокручиваем лист обратно к этой ячейке, нажимаем на значок с воскл.знаком левее ячейки и в раскрывшемся меню выбираем Преобразовать в число(дату).
Преобразовать в число
После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) ). Вполне возможно, что это действие придется повторить еще один-два раза.

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

Если после этого группировка все еще не работает, значит, где-то в тексте все еще записаны номера/даты. Но они не могут быть выделены зеленым треугольником. Такое поведение часто наблюдается в файлах, загруженных из 1С или других программ. Часто это преодолевается с большим трудом: выделите ячейку, нажмите F2 (для входа в режим редактирования ячеек) и Enter. Затем Excel преобразует дату/число в фактическую дату/число. Но если таких клеток хотя бы 100, то это уже не рутина на несколько минут. К счастью, все это можно сделать за считанные секунды. Необходимо быстро преобразовать ячейки, содержащие текстовые даты/числа, в реальные даты/числа:

  • Скопировать пустую ячейку рабочего листа
  • Выделить все ячейки с датами/числами
  • Щелкнуть правой кнопкой мыши на специальном пункте Paste — выбрать в окне Values, операция — Multiply
  • ОК
Читайте так же:
Как подсчитать в Excel ячейки длиной больше определенного числа?

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

К ООРДИНАЦИЮ ТЕКСТОВОГО ПОЛЯ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

  1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
  2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
  3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);

  4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только те элементы, которые имеют одинаковые подуровни. Если поле имеет два уровня, «Страна» и «Город», вы не можете группировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

    1. Выберите имя группы;
    2. Нажмите F2;
    3. Введите новое имя группы.

    1. Выберите группу, которую нужно разгруппировать;
    2. На вкладке Параметры группы нажмите кнопку Разгруппировать (или щелкните правой кнопкой мыши и выберите Разгруппировать в контекстном меню).

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

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

    x

    x

    x

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

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

    • Перерабатывайте все: Добавляйте наиболее часто используемые или сложные формулы, графики и прочее в избранное для быстрого использования в будущем.
    • Более 20 текстовых функций: извлечение числа из текстовой строки; извлечение или удаление частей текста; преобразование чисел и валют в английские слова.
    • Инструменты слияния: объединение нескольких книг и рабочих листов в один; объединение нескольких ячеек/строк/столбцов без потери данных; объединение повторяющихся строк и итогов.
    • Инструменты разделения: разделение данных на несколько рабочих листов на основе значений; из одной книги в несколько файлов Excel, PDF или CSV; из одного столбца в несколько столбцов. Вставка Spring Скрытие скрытых/фильтруемых строк ; Подсчет и суммирование по цвету фона ; Массовая отправка персонализированных писем нескольким получателям.
    • Super Filter:
    • Более 300 расширенных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Легко развертывается на вашем предприятии или в организации.
    стрелка синий правый пузырьПовторять метки строк для всех групп полей в сводной таблице

    Популярные

    Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
    Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

    док-повтор-заголовок-сводный-1-2док-повтор-заголовок-сводный-2

    Процедура осуществляется следующим образом

    Щелкните любую ячейку в сводной таблице и нажмите Макет на вкладке Инструменты сводной таблицы, затем Макет отчета > Показать в структуре, чтобы отобразить сводную таблицу в виде макета, см. скриншоты :

    док-повтор-заголовок-сводный-3
    -1
    док-повтор-заголовок-сводный-4

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

    док-повтор-заголовок-сводный-4

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

    док-повтор-заголовок-сводный-4

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

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

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

    2. Затем щелкните правой кнопкой мыши и выберите пункт Field Settings из контекстного меню, см. скриншот :

    док-повтор-заголовок-сводный-4

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

    док-повтор-заголовок-сводный-4

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector