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

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

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

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

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

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

Сводные таблицы из других источников

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

Примечание: В зависимости от ит-параметров вашей организации в списке может отображаться имя вашей организации. Например, «Из Power BI (Майкрософт)».

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

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

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

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

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

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

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

Работа с значениями сводной таблицы

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

Затем измените функцию в разделе Операция. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. Если выбран параметр Числовой формат, можно изменить числовой формат для всего поля.

Совет: Так как при изменении вычисления в разделе Суммирование значений по изменяется имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы до завершения настройки сводной таблицы. Один из способов заключается в использовании функции Поиска & Замены (CTRL+H) >Найти то, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.

Дополнительные вычисления

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

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

Отображение значения как результата вычисления и как процента

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

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

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

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

Примечание: Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.

Изменение исходных данных

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

1. В области Вставка сводной таблицы выберите текстовое поле в разделе Источник. При изменении источника карточки в области будут недоступны.
2. Выберите данные в сетке или введите диапазон в текстовое поле.
3. Нажмите клавишу ВВОД на клавиатуре или кнопку, чтобы подтвердить выбор. Панель обновляется с новыми рекомендуемыми сводных таблицами на основе нового источника данных.

Получение из Power BI

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

Работа с областью «Поля сводной таблицы»

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

По умолчанию нечисловые поля добавляются в область Строк , поля даты и времени добавляются в область Столбцы , а числовые — в область Значения .

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

Работа с значениями сводной таблицы

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

Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр Суммировать значения по .

Дополнительные вычисления

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

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

Сделайте выбор из списка.

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

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

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

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

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

Перед началом работы

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

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

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.
2. На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

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

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

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

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

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

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

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

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

Значения в сводной таблице

• Суммировать по По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр Параметры поля . Затем измените функцию в разделе Суммировать по. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. При выборе параметра Число. можно изменить формат чисел для всего поля.

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

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

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

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

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

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

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

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в раздел Вставка > сводной таблицы.
3. Выберите место размещения сводной таблицы. Выберите Вставить на новом листе, чтобы поместить сводную таблицу на новый лист, или выберите ячейку, в которой новая сводная таблица будет помещена в поле Назначение.
4. Нажмите кнопку Вставить.

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

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

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

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

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

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

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

Советы и рекомендации по форматированию данных

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

Отформатируйте данные как таблицу Excel (выберите в любом месте данных, а затем на ленте выберите Вставить > таблицу ).

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Microsoft Privacy Policy

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

Структурирование (группировка) данных на листе

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

1. Чтобы отобразить строки для уровня, щелкните соответствующий символы структуры.

2. Уровень 1 содержит сумму продаж по всем строкам с подробными данными.

3. Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.

4. Уровень 3 содержит подробные строки— в этом случае строки от 17 до 20.

5. Чтобы развернуть или свернуть данные в контуре, щелкните и символы структуры или нажмите клавиши ALT+SHIFT+= для развертывания и ALT+SHIFT+- для сворачивания.

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

1. Убедитесь, что каждый столбец данных, который требуется структурировать, имеет метку в первой строке (например, Регион), содержит аналогичные факты в каждом столбце, а диапазон, который требуется обвести, не содержит пустых строк или столбцов.
2. При необходимости сгруппированные строки сведений могут иметь соответствующую сводную строку — промежуточный итог. Чтобы создать их, выполните одно из следующих действий.
   • Вставка итоговых строк при помощи команды Промежуточные итоги Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции «Итоги» читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
   • Вставка собственных итоговых строк Вставьте собственные итоговые строки с формулами непосредственно под или над каждой группой строк с подробными данными. Например, под (или над) строками данных о продажах за март и апрель используйте функцию СУММ, чтобы подсчитать промежуточные итоговые данные по продажам за эти месяцы. Этот пример показан в таблице далее в этой статье.
3. По умолчанию Excel ищет сводные строки под сведениями, которые они обобщают, но их можно создать над строками сведений. Если вы создали строки сводки под подробными сведениями, перейдите к следующему шагу (шаг 4). Если вы создали строки сводки над строками сведений, на вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры. Затем в диалоговом окне Параметры снимите флажок Сводка строк ниже и нажмите кнопку ОК.
4. Структурируйте данные. Выполните одно из перечисленных ниже действий. Автоматическое структурирование данных
   1. Выделите ячейку в диапазоне ячеек, которые нужно выделить.
   2. На вкладке Данные в группе Структура щелкните стрелку рядом с пунктом Группировать, а затем выберите пункт Создать структуру.

   Структурирование данных вручную

   Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок группировки строк.

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

      1. Первая строка содержит метки и не выбрана. 2. Так как это внешняя группа, выделите все строки с промежуточными и подробными данными. 3. Не выбирайте общий итог.

   2. На вкладке Данные в группе Структура выберите Группировать. Затем в диалоговом окне Группа щелкните Строки и нажмите кнопку ОК.

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

   

   Рядом с группой на экране появятся знаки структуры.
   Или структурируйте внутреннюю вложенную группу — строки с данными для определенного раздела данных.

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

   1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже группируются на уровне 2. 2. Этот раздел выбран и готов к группировке. 3. Не выбирайте строку сводки для группирования данных.

   На вкладке Данные в группе Структура выберите Группировать. Затем в диалоговом окне Группа щелкните Строки и нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.

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

   Важно: Если во время разгруппировки структуры подробные данные скрыты, строки с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем на вкладке Главная в группе Ячейки нажмите кнопку Формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите команду Отобразить строки.

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

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

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

5. Если столбец сводки находится слева от столбцов сведений, на вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры. Затем в диалоговом окне Параметры очистите столбцы Сводка справа от подробных проверка и нажмите кнопку ОК.
6. Чтобы структурировать данные, выполните одно из перечисленных ниже действий. Автоматическое структурирование данных
   1. Выделите ячейку в диапазоне.
   2. На вкладке Данные в группе Структура щелкните стрелку под пунктом Группировать, а затем выберите Создать структуру.

   Структурирование данных вручную

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

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

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

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

      1. Можно создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже группируются на уровне 2. 2. Эти столбцы выбраны и готовы к группировке. Обратите внимание, что если вы не выберете целые столбцы, при нажатии кнопки Группа (на вкладке Данные в группе Структура) откроется диалоговое окно Группа с запросом на выбор строк или столбцов. 3. Не выбирайте столбец сводки для группирования данных.

   4. На вкладке Данные в группе Структура выберите Группировать. Рядом с группой на экране появятся знаки структуры.
7. Продолжайте выделение и группировку внутренних столбцов до тех пор, пока не будут созданы все необходимые уровни структуры.
8. Для разгруппировки столбцов выделите необходимые столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать.

Вы также можете разгруппировать разделы структуры, не удаляя весь уровень. Удерживая нажатой клавишу SHIFT, щелкните или группы, а затем на вкладке Данные в группе Структура щелкните Разгруппировать.

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

Отображение и скрытие структурированных данных

1. Если символы структуры не отображаются , и , перейдите в раздел Параметры >файлов>Дополнительно, а затем в разделе Параметры отображения для этого листа выберите поле Показывать символы структуры, если структура применяется проверка, а затем нажмите кнопку ОК.
2. Выполните одно или несколько из указанных ниже действий.
   • Отображение или скрытие подробных данных для группы Чтобы отобразить подробные данные в группе, нажмите кнопку для группы или нажмите клавиши ALT+SHIFT+=.
   • Чтобы скрыть подробные данные для группы, нажмите кнопку для группы или нажмите клавиши ALT+SHIFT+-.
   • Развертывание или свертывание структуры до определенного уровня В символы структуры щелкните нужный номер уровня. Подробные данные более низких уровней будут скрыты. Например, если в структуре четыре уровня, можно скрыть четвертый уровень, нажав кнопку , при этом оставшиеся три уровня будут отображаться.
   • Отображение или скрытие всех структурированных данных Чтобы отобразить все подробные данные, щелкните самый нижний уровень в символов структуры. Например, если в структуре три уровня, нажмите .
   • Чтобы скрыть все подробные данные, нажмите .

Настройка многоуровневой структуры со стилями

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

Выполните одно или несколько из указанных ниже действий.

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

1. На вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры.
2. Установите флажок Автоматические стили.

Назначение стиля существующей итоговой строке или столбцу

1. Выделите ячейки, к которым требуется применить стиль.
2. На вкладке Данные в группе Структура щелкните средство запуска диалогового окна. Откроется диалоговое окно Параметры.
3. Выберите поле Автоматические стили проверка и нажмите кнопку Применить стили.

Для форматирования структурированных данных также можно применять автоформаты.

Копирование структурированных данных

1. Если символы структуры не отображаются , и , перейдите в раздел Параметры >файлов>Дополнительно, а затем в разделе Параметры отображения для этого листа выберите поле Показать символы структуры, если структура применяется проверка.
2. Используйте символы структуры , и , чтобы скрыть подробные данные, которые не нужно копировать. Подробную информацию см. в разделе Отображение и скрытие структурированных данных.
3. Выделите диапазон итоговых строк.
4. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
5. Нажмите кнопку Выделить группу ячеек.
6. Выберите пункт только видимые ячейки.
7. Нажмите кнопку ОК, а затем скопируйте данные.

Скрытие или удаление многоуровневой структуры

Примечание: При скрытии или удалении структуры удаления данных не происходит.

Скрытие многоуровневой структуры

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

Удаление многоуровневой структуры

1. Выберите лист.
2. На вкладке Данные в группе Структура щелкните Разгруппировать, а затем выберите пункт Удалить структуру.

Важно: Если во время удаления многоуровневой структуры подробные данные скрыты, то строки или столбцы с подробными данными могут также не отображаться. Для просмотра данных выделите номера или буквы видимых соседних строк или столбцов соответственно. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отобразить строки или кнопку Отображение столбцов.

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

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

1. Создание сводного отчета
   1. Структурируйте данные. Подробнее читайте в разделе Создание многоуровневой структуры из строк или Создание многоуровневой структуры из столбцов.
   2. Скройте сведения, щелкнув символы структуры , и , чтобы отобразить только итоги, как показано в следующем примере структуры строки:
   3. Подробную информацию см. в разделе Отображение и скрытие структурированных данных.
2. Диаграмма сводного отчета
   1. Выделите итоговые данные, которые требуется представить в виде диаграммы. Например, для диаграммы только итоги Бьюкенен и Даволио, но не общие итоги, выделите ячейки от A1 до C19, как показано в приведенном выше примере.
   2. Щелкните Вставить >диаграммы >Рекомендуемые диаграммы, а затем откройте вкладку Все диаграммы и выберите тип диаграммы. Например, если выбран параметр Кластеризованный столбец, диаграмма будет выглядеть следующим образом: При отображении или скрытии данных в структурированном списке эти изменения отображаются и на диаграмме.

Можно группировать (или структурировать) строки и столбцы в Excel в Интернете.

Примечание: Хотя в данные можно добавлять сводные строки или столбцы (с помощью таких функций, как СУММ или ПРОМЕЖУТОЧНЫЕ ИТОГИ), нельзя применять стили или задавать позицию для строк и столбцов сводки в Excel в Интернете.

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

Структура строк в Excel Online

1. Первый столбец содержит метки.
2. Строки сведений и строки сводки группируются в структуре.
3. Общий итог не сгруппирован в контуре.

Структура столбцов в Excel Online

1. Первая строка содержит метки.
2. Столбцы сведений и столбцы сводки группируются в структуре.
3. Общий итог не сгруппирован в контуре.

1. Убедитесь, что каждый столбец (или строка) данных, которые требуется создать, имеет метку в первой строке (или столбце), содержит аналогичные факты в каждом столбце (или строке), а диапазон не содержит пустых строк или столбцов.
2. Выберите данные (включая строки или столбцы сводки).
3. На вкладке Данные в группе Структура щелкните Группировать >Строки группы или Столбцы группы.
4. При необходимости, если вы хотите создать структуру внутренней вложенной группы, выберите строки или столбцы в пределах указанного диапазона данных и повторите шаг 3.
5. Продолжайте выбирать и группировать внутренние строки или столбцы, пока не создадите все нужные уровни в структуре.

Разгруппирование строк или столбцов

• Чтобы разгруппировать, выберите строки или столбцы, а затем на вкладке Данные в группе Структура нажмите кнопку Разгруппировать и выберите Разгруппировать строки или Разгруппировать столбцы.

Отображение и скрытие структурированных данных

Выполните одно или несколько из указанных ниже действий.

Отображение или скрытие подробных данных для группы

• Чтобы отобразить подробные данные в группе, щелкните группы или нажмите клавиши ALT+SHIFT+=.
• Чтобы скрыть подробные данные для группы, щелкните группы или нажмите клавиши ALT+SHIFT+-.

Развертывание или свертывание структуры до определенного уровня

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

Отображение или скрытие всех структурированных данных

• Чтобы отобразить все подробные данные, щелкните самый нижний уровень в символов структуры. Например, если в структуре три уровня, нажмите .
• Чтобы скрыть все подробные данные, нажмите .

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Структурирование данных в Excel

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

1. Для начала отсортируем данные по столбцу Company.
2. На вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal).
3. В списке При каждом изменении в (At each change in) выберите Company. Этот столбец мы будем использовать, чтобы структурировать нашу таблицу.
4. В списке Операция (Use function) выберите Количество (Count).
5. Поставьте галочку напротив Company (см. рисунок ниже).
6. Нажмите ОК.Результат:
7. Чтобы свернуть группу ячеек, нажмите знак «–» (минус). Можно использовать цифры, чтобы свернуть или развернуть группы по уровню. К примеру, нажмите 2, чтобы отображались только подуровни.

Примечание: Нажмите 1, чтобы отображать только итоги, или 3, чтобы отобразить всё.

Чтобы свернуть группу столбцов, выполните следующие действия:

1. Выделите столбцы A и B.
2. На вкладке Данные (Data), кликните по Группировать (Group).
3. Нажмите на знак «–«.Результат:
4. Чтобы удалить структуру, выделите любую ячейку из набора данных и на вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal). Затем кликните по Убрать все (Remove All).

Аналитические функции в Excel (функции кубов)

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

• «правильно» организованные исходные данные
• математика (логика) обработки данных
• представление данных

Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Функции кубов и сводные таблицы

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

Дальнейшее изложение относится к формулам кубов и сводным таблицам на основе модели Power Pivot и в нескольких случаях на основе кубов OLAP.

Простой способ получить функции кубов

Когда (если) вы начинали изучать код VBA, то узнали, что проще всего получить код, используя запись макроса. Далее код можно редактировать, добавить циклы, проверки и др. Аналогично проще всего получить набор функций кубов, преобразовав сводную таблицу (рис. 2). Встаньте на любую ячейку сводной таблицы, перейдите на вкладку Анализ, кликните на кнопке Средства OLAP, и нажмите Преобразовать в формулы.

Рис. 2. Преобразование сводной таблицы в набор функций куба

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

Рис. 3. Таблица на основе формул кубов

Функция КУБЗНАЧЕНИЕ()

Это, пожалуй, основная функция кубов. Она эквивалентна области Значения сводной таблицы. КУБЗНАЧЕНИЕ извлекает данные из куба или модели Power Pivot, и отражает их вне сводной таблицы. Это означает, что вы не ограничены пределами сводной таблицы и можете создавать отчеты с бесчисленными возможностями.

Написание формулы «с нуля»

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

= КУБЗНАЧЕНИЕ ( «ThisWorkbookDataModel» ;
«[Measures].[Total Sales]» ;
«[Products].[Category].[All].[Bikes]»

Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно, находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter. Рис. 5. Свернуть окно

Синтаксис функции КУБЗНАЧЕНИЕ()

Справка Excel абсолютно точна и абсолютно бесполезна для начинающих: КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …) Подключение – обязательный аргумент; текстовая строка, представляющая имя подключения к кубу. Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба. Прежде, чем перейти к объяснению синтаксиса функции КУБЗНАЧЕНИЕ, пару слов о кубах, моделях данных, и загадочном кортеже.

Некоторые сведения о кубах OLAP и моделях данных Power Pivot

Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов OLAP – дело ИТ-специалистов. Рис. 5а. Трехмерный куб OLAP Аналитические формулы Excel (формулы кубов) извлекают названия осей (например, Время), названия элементов на этих осях (август, сентябрь), значения мер на пересечении координат. Именно такая структура и позволяет сводным таблицам на основе кубов и формулам кубов быть столь гибкими, и подстраиваться под нужды пользователей. Сводные таблицы на основе листов Excel не используют меры, поэтому они не столь гибки в целях анализа данных. Power Pivot – относительно новая фишка Microsoft. Это встроенная в Excel и отчасти независимая среда с привычным интерфейсом. Power Pivot значительно превосходит по своим возможностям стандартные сводные таблицы. Вместе с тем, разработка кубов в Power Pivot относительно проста, а самое главное – не требует участия ИТ-специалиста. Microsoft реализует свой лозунг: «Бизнес-аналитику – в массы!». Хотя модели Power Pivot не являются кубами на 100%, о них также можно говорить, как о кубах (подробнее см. вводный курс Марк Мур. Power Pivot и более объемное издание Роб Колли. Формулы DAX для Power Pivot). Основные компоненты куба – это измерения, иерархии, уровни, элементы (или члены; по-английски members) и меры (measures). Измерение – основная характеристика анализируемых данных. Например, категория товаров, период времени, география продаж. Измерение – это то, что мы можем поместить на одну из осей сводной таблицы. Каждое измерение помимо уникальных значений включает элемент [ALL], выполняющий агрегацию всех элементов этого измерения. Измерения построены на основе иерархии. Например, категория товаров может разбиваться на подкатегории, далее – на модели, и наконец – на названия товаров (рис. 5б) Иерархия позволяет создавать сводные данные и анализировать их на различных уровнях структуры. В нашем примере иерархия Категория включает 4 Уровня. Рис. 5б. Иерархия категорий товаров Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы. ­Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом [Measures] (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары, разместить ось Меры с элементами Объем продаж, Прибыль, Проданные единицы. Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж. Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию [All]. Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки. Набор элементов – несколько элементов одного измерения. Например, яблоки и груши. Набор кортежей – несколько кортежей, каждый из которых состоит из одинаковых измерений в одной и той же последовательности. Например, набор из двух кортежей: Москва, яблоки и Пермь, бананы.

Автозавершение в помощь

Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке: =КУБЗНАЧЕНИЕ( » Excel предложит все доступные в книге Excel подключения: Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel Рис. 7. Подключения к кубам Продолжим ввод формулы (в нашем случае для модели данных): =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » Автозавершение предложит все доступные таблицы и меры модели данных: Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен) Выберите значок Measures. Поставьте точку: =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures]. Автозавершение предложит все доступные меры: Рис. 9. Доступные элементы второго уровня в наборе мер Выберите меру [Total Sales]. Добавьте кавычки, закрывающую скобку, нажмите Enter. =КУБЗНАЧЕНИЕ( » ThisWorkbookDataModel » ; » [Measures].[Total Sales] » ) Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel Аналогичным образом можете добавить третий аргумент в формулу:

Похожие публикации:

1. Как вставить картинку в microsoft word
2. Как обновить сертификат безопасности сайта
3. Как очистить принтер canon через компьютер
4. Как поставить будильник в виндовс 10