Створення структури (групування) даних в аркуші

Якщо у вас є список даних, до яких потрібно групувати та підсумовувати, можна створити структуру до восьми рівнів. Кожний внутрішній рівень, представлений більшим номером символи структури, відображає докладні дані для попереднього зовнішнього рівня, представленого меншим номером позначки структури. За допомогою структури можна швидко відображати підсумкові рядки або стовпці, а також докладні дані для кожної групи. Структуру можна створити з рядків (як показано на прикладі нижче), зі стовпців або з рядків і стовпців.

Дані з трьома рівнями структури

1. Щоб відобразити рядки для певного рівня, клацніть відповідний символ структури один два три .

2.  Рівень 1 містить дані про загальний обсяг продажу для всіх рядків із докладними даними.

3.  Рівень 2 містить дані про загальний обсяг продажу за кожний місяць у кожному регіоні.

4. рівень 3 містить рядки з докладними відомостями – у цьому випадку рядки від 17 до 20.

5. Щоб розгорнути або згорнути дані в контурі, клацніть плюс та мінус символи структури або натисніть клавіші ALT + SHIFT + = , щоб розгорнути, а ALT + SHIFT + – до згортання.

  1. Переконайтеся, що кожен стовпець даних, який потрібно створити, має підпис у першому рядку (наприклад, в області), містить аналогічні факти в кожному стовпці, а діапазон, у якому потрібно створити структуру, не має пустих рядків або стовпців.

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

    • Вставлення підсумкових рядків за допомогою команди Проміжні підсумки    

      Використовуйте команду Проміжні підсумки , яка ВСТАВЛЯЄ ФУНКЦІЮ проміжного підсумку безпосередньо під кожною групою рядків докладних даних і автоматично створить структуру для вас. Докладні відомості про використання функції "Проміжний підсумок" наведено в статті функція "Проміжний підсумок".

    • Вставлення власних підсумкових рядків    

      Вставте власні зведені рядки з формулами відразу під або над кожною групою рядків із докладними даними. Наприклад, під (або над) рядками даних продажів за березень і квітень, використовуйте функцію SUM, щоб отримати проміжні підсумки за ці місяці. У таблиці нижче наведено приклад.

  3. За замовчуванням програма Excel шукає Підсумкові рядки під відомостями, які вони підсумовують, але їх можна створити над рядками відомостей. Якщо ви створили Підсумкові рядки під відомостями, перейдіть до наступного кроку (крок 4). Якщо Підсумкові рядки створено над рядками даних, на вкладці дані в групі Структура клацніть запускач діалогових вікон.

    У групі "Структура" клацніть запускач діалогових вікон

    Відкриється діалогове вікно "Параметри".

    Діалогове вікно "Параметри" для контурів

    Потім у діалоговому вікні Настройки зніміть прапорець Підсумкові рядки під елементом докладно , а потім натисніть кнопку OK.

  4. Структуруйте свої дані. Виконайте одну з таких дій:

    Автоматичне структурування даних

    1. Виділіть клітинку в діапазоні клітинок, до яких потрібно створити структуру.

    2. На вкладці Дані у групі Структура клацніть стрілку поруч із кнопкою Група, а потім виберіть Створення структури.

      Клацніть стрілку під командою "Групувати" та виберіть пункт "Автоматична структура"

    Структурування даних вручну

    Увага!: Якщо потрібно згрупувати рівні структури вручну, ми радимо відобразити всі дані, щоб уникнути неправильного групування рядків.

    1. Щоб окреслити зовнішню групу (рівень 1), виберіть усі рядки, які буде містити зовнішня Група (тобто рядки відомостей і якщо їх додано, їх Підсумкові рядки).

      Набір даних, який потрібно створити, для зовнішньої групи

      1. перший рядок містить надписи та не вибрано.

      2. оскільки це зовнішня Група, виділіть усі рядки з проміжними підсумками і відомостями.

      3. не вибирайте загальні підсумки.

    2. На вкладці Дані у групі Структура виберіть команду Групувати. Потім у діалоговому вікні Група натисніть кнопку рядкита натисніть кнопку OK.

      Порада.: Якщо виділити всі рядки, а не лише клітинки, у програмі Excel автоматично Групувати за рядками – діалогове вікно групувати не відкривається.

      Виберіть пункт "рядки" та натисніть кнопку "OK"

      Позначки структури відображаються поруч із групою на екрані.

    3. На вибір, виділіть внутрішню вкладену групу – рядок з подробицями для обраного розділу ваших даних.

      Примітка.: Якщо не потрібно створювати внутрішні групи, перейдіть до кроку f, нижче.

      Для кожної внутрішньої (вкладеної) групи виберіть рядок із докладними даними, суміжний із рядком, який містить підсумковий рядок.

      Дані, виділені для групування на рівні 2 в ієрархії.

      1. ви можете створювати кілька груп на кожному внутрішньому рівні. Тут два розділи вже згруповані на рівні 2.

      2. Цей розділ вибраний і готовий до групи.

      3. не вибирайте підсумковий рядок для даних, які потрібно згрупувати.

    4. На вкладці Дані у групі Структура виберіть команду Групувати.

      На вкладці "Дані" виберіть команду "Групувати"

      Потім у діалоговому вікні Група натисніть кнопку рядкита натисніть кнопку OK. Позначки структури відображаються поруч із групою на екрані.

      Порада.: Якщо виділити всі рядки, а не лише клітинки, у програмі Excel автоматично Групувати за рядками – діалогове вікно групувати не відкривається.

    5. Продовжуйте вибирати та групувати внутрішні рядки, доки не створите всі потрібні рівні структури.

    6. Якщо потрібно розгрупувати рядки, виберіть їх, а потім на вкладці Дані у групі Структура натисніть кнопку Розгрупувати.

      Крім того, можна Розгрупувати розділи структури, не видаляючи весь рівень. Утримуючи натиснутою клавішу SHIFT, клацніть Позначка "плюс" або Позначка "мінус" для групи, а потім на вкладці дані у групі Структура натисніть кнопку Розгрупувати.

      Увага!: Якщо під час розгрупування структури докладні дані приховані, рядки з цими даними можуть залишатися прихованими. Щоб відобразити дані, протягніть вказівник по номерах видимих рядків, суміжних із прихованими рядками. Потім на вкладці основне у групі клітинки натисніть кнопку Формат, наведіть вказівник миші на пункт Приховати & відобразити, а потім виберіть пункт Відобразити рядки.

  1. Переконайтеся, що кожен із рядків із даними, які потрібно структурувати, містить назву в першому стовпці, схожі факти в кожному з рядків і що в діапазоні немає пустих рядків або стовпців.

  2. Вставте власні підсумкові стовпці з формулами відразу справа або зліва від кожної групи стовпців докладних даних. Таблиці у кроці 4 нижче надає приклад цього.

    Примітка.:  Щоб структурувати дані за стовпцями, потрібно мати підсумкові стовпці, які містять формули з посиланнями на клітинки кожного зі стовпців докладних даних із цієї групи.

  3. Якщо зведена колонка знаходиться ліворуч від стовпців "Докладно", на вкладці дані в групі Структура клацніть запускач діалогових вікон.

    У групі "Структура" клацніть запускач діалогових вікон

    Відкриється діалогове вікно "Параметри".

    Діалогове вікно "Параметри" для контурів

    Потім у діалоговому вікні Настройки зніміть прапорець підсумкові стовпці праворуч від докладно та натисніть кнопку OK.

  4. Для структурування даних виконайте одну з наведених нижче дій.

    Автоматичне структурування даних

    1. Виділіть в діапазоні діапазон.

    2. На вкладці Дані у групі Структура клацніть стрілку під кнопкою Група, а потім виберіть Створення структури.

    Структурування даних вручну

    Увага!: Якщо потрібно згрупувати рівні структури вручну, ми радимо відобразити всі дані, щоб уникнути неправильного групування стовпців.

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

      Дані, упорядковані у стовпці для групування

      1. стовпець A містить надписи.

      2. виділіть усі стовпці докладно та проміжні підсумки. Зверніть увагу, що якщо не виділити всі стовпці, після натискання кнопки Групувати (на вкладці дані у групі Структура ) відкриється діалогове вікно Група, у якому буде запропоновано вибрати рядки або стовпці.

      3. не вибирайте стовпець загальні підсумки.

    2. На вкладці Дані у групі Структура виберіть команду Групувати.

      На вкладці "Дані" виберіть команду "Групувати"

      Над групою відображається символ структури.

    3. Щоб окреслити внутрішню, вкладену групу стовпців докладних даних (рівень 2 або вище), виберіть стовпці з відомостями, які примикає до стовпця, який містить зведене стовпець.

      Дані, згруповані у стовпцях

      1. ви можете створювати кілька груп на кожному внутрішньому рівні. Тут два розділи вже згруповані на рівні 2.

      2. Ці стовпці виділені і готові до групи. Зверніть увагу, що якщо не виділити всі стовпці, після натискання кнопки Групувати (на вкладці дані у групі Структура ) відкриється діалогове вікно Група, у якому буде запропоновано вибрати рядки або стовпці.

      3. не вибирайте зведений стовпець для даних, які ви групуте.

    4. На вкладці Дані у групі Структура виберіть команду Групувати.

      На вкладці "Дані" виберіть команду "Групувати"

      Позначки структури відображаються поруч із групою на екрані.

  5. Продовжуйте вибирати та групувати внутрішні стовпці, доки не створите всі потрібні рівні структури.

  6. Якщо потрібно розгрупувати стовпці, виберіть їх, а потім на вкладці Дані у групі Структура натисніть кнопку Розгрупувати.

На вкладці "Дані" виберіть команду "Розгрупувати"

Крім того, можна Розгрупувати розділи структури, не видаляючи весь рівень. Утримуючи натиснутою клавішу SHIFT, клацніть Позначка "плюс" або Позначка "мінус" для групи, а потім на вкладці дані у групі Структура натисніть кнопку Розгрупувати.

Якщо під час розгрупування структури докладні дані приховані, стовпці з цими даними можуть залишатися прихованими. Щоб відобразити дані, протягніть вказівник по буквах видимих стовпців, суміжних із прихованими стовпцями. На вкладці Основне у групі Клітинки натисніть кнопку Форматувати, наведіть вказівник на пункт Приховати або відобразити, а потім виберіть команду Відобразити стовпці

  1. Якщо символи структури не відображаються один два три , плюс та мінус , виберіть пункт> параметри > Додатково, а потім у розділі Параметри відображення цього аркуша встановіть прапорець Відображати символи структури в разі застосування структури , а потім натисніть кнопку OK.

  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. Натисніть кнопку OK, а потім скопіюйте дані.

Примітка.: Приховання або видалення структури не призводить до видалення жодних даних.

Приховання структури

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

Видалення структури

  1. Клацніть аркуш.

  2. На вкладці Дані в групі Структура клацніть Розгрупувати та виберіть Видалити структуру.

    Натисніть кнопку "Розгрупувати" та виберіть пункт "Видалити структуру"

    Увага!: Якщо під час видалення структури докладні дані приховані, рядки з цими даними можуть залишатися приховані. Щоб відобразити дані, перетягніть номери видимих рядків або букви стовпців, суміжних із прихованими рядками та стовпцями. На вкладці Основне у групі Клітинки натисніть кнопку Формат, укажіть команду Приховати або відобразити, відтак виберіть команду Відобразити рядки або Відобразити стовпці.

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

  1. Створення зведеного звіту

    1. Структуруйте свої дані.

      Для отримання додаткових відомостей див. розділ Створення структури з рядків або Створення структури зі стовпців.

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

      Структурований список, у якому відображаються лише підсумкові рядки

    3. Докладні відомості див. в розділі Відображення або приховання структурованих даних.

  2. Діаграма підсумкових звітів

    1. Виберіть підсумкові дані, для яких слід створити діаграму.

      Наприклад, щоб виконати діаграму лише за підсумками «Петренко» і «Давидова», але не для загальних підсумків, виберіть клітинки a1 – C19, як показано в наведеному вище прикладі.

    2. Натисніть кнопку вставити > діаграми > Рекомендовані діаграми, а потім перейдіть на вкладку Усі діаграми та виберіть потрібний тип діаграми.

      Діалогове вікно ' ' Вставлення діаграми ' '

      Наприклад, якщо ви вибрали параметр «звичайна Гістограма», діаграма матиме такий вигляд:

      Діаграма, створена на основі даних проміжних підсумків

      Якщо відобразити або приховати докладні дані в структурованому списку даних, діаграму також буде оновлено, щоб відобразити чи приховати ці дані.

Ви можете групувати (або структурувати) рядки та стовпці в Інтернет-версія Excel.

Примітка.: Хоча ви можете додати Підсумкові рядки або стовпці до даних (за допомогою функцій, як-от SUM або проміжні підсумки), не можна використовувати стилі або встановлювати розташування для зведених рядків і стовпців у Інтернет-версія Excel.

Створення структури рядків або стовпців

Структура рядків у веб-програмі Excel Online

Структура стовпців у веб-програмі Excel Online

Структура рядків у веб-програмі Excel Online

  1. Перший стовпець містить надписи.

  2. Рядки відомостей і зведення підсумків групуються в структурі.

  3. Загальний підсумок не згруповано в контурі.

Структура стовпців у веб-програмі Excel Online

  1. Перший рядок містить надписи.

  2. Стовпці "Докладно" та "зведення" групуються в структурі.

  3. Загальний підсумок не згруповано в контурі.

  1. Переконайтеся, що кожен стовпець (або рядок) даних, який потрібно окреслити, має підпис у першому рядку (або стовпці), містить аналогічні факти в кожному стовпці (або рядку), а діапазон не має пустих рядків або стовпців.

  2. Виберіть дані (включно з будь-якими підсумковими рядками або стовпцями).

  3. На вкладці дані у групі Структура натисніть кнопку Групувати > рядки або групи стовпців.

  4. За потреби, якщо потрібно структурувати внутрішню, вкладену групу, виділіть рядки або стовпці в структурованому діапазоні даних, а потім повторіть крок 3.

  5. Продовжуйте виділяти та групувати внутрішні рядки або стовпці, доки не створите всі потрібні рівні структури.

Розгрупування рядків або стовпців

  • Щоб Розгрупувати елементи, виділіть рядки або стовпці, а потім на вкладці дані в групі Структура натисніть кнопку Розгрупувати та виберіть пункт Розгрупувати рядки або Розгрупувати стовпці.

Відображення або приховання структурованих даних

Виконайте одну або кілька з таких дій:

Відображення або приховання докладних даних для групи    

  • Щоб відобразити докладні дані в групі, клацніть плюс для групи або натисніть клавіші ALT + SHIFT + =.

  • Щоб приховати докладні дані для групи, клацніть мінус для групи або натисніть клавіші ALT + SHIFT +-.

Розгортання або згортання всієї структури до певного рівня    

  • У один два три символи структури виберіть потрібну кількість рівнів. На нижчих рівнях від вибраного докладні дані буде приховано.

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

Відображення або приховання всіх докладних даних у структурі    

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

  • Щоб приховати всі докладні дані, клацніть позначку з номером один .

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Додаткові відомості

Групування або розгрупування даних у зведеній таблиці

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×