Green-sell.info

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

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

Структурирование таблиц в Excel

Цель работы: научиться структурировать таблицы ручным

способом и способом автоструктурирования

Содержание работы:

1 Ручной способ структурирования

3 Структурирование с подведением итогов

Структурирование таблиц

структурирование таблицы —это группирование строк и столбцов в таблице. Оно необходимо при работе с большими таблицами , когда часто приходится временно закрывать или открывать вложенные друг в друга части таблицы на разных иерархических уровнях. Структурированный по строкам документ имеет вид (рис. 1). Значки «+» означают, что строки 3-7 и 15-19 в таблице свёрнуты. При щелчке по ним они разворачиваются, т.е. полностью появляются на экране и появляется линия уровня, а значок «+» заменяется на «-«. При щелчке по значку «-» группа строк сворачивается в значок «+».

Рисунок 1 Структурированная по строкам таблица

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

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

Ручной способ структурирования

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

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

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

Для отмены одного структурного компонента производится выделение области и выполняется командаДанныеГруппа и Структура Разгруппировать.

Для отмены всех структурных компонентов таблицы нужно выполнить командаДанныеГруппа и СтруктураУдалить структуру.

Автоструктурирование

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

Пример такой таблицы приведен на рис. 2. В ней, по сравнению с таблицей рис. 1 введены столбец, в котором рассчитывается максимальная оценка студента с помощью функции МАКС(может быть любая формула Excel) и строка Итого, в которойрассчитывается с помощью функцииСРЗНАЧ средний балл в группе. Порядок следования исходных данных и результатов (итогов) — слева направо и сверху вниз, что позволяет применить к таблице автоструктурирование.

После ввода в таблицу исходных данных и формул курсор устанавливается в произвольную ячейку списка и выполняется командаДанные Группа и Структура Создать структуру. Все структурные части таблицы создаются автоматически (рис. 3 – строки группы КТ-31 свёрнуты).

Рисунок 2Таблица, пригодная для автоструктурирования

Рисунок 3 Вид таблицы после автоструктурирования

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

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

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

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

  • Чтобы свернуть группу ячеек, нажмите знак “” (минус). Можно использовать цифры, чтобы свернуть или развернуть группы по уровню. К примеру, нажмите 2, чтобы отображались только подуровни.
  • Примечание: Нажмите 1, чтобы отображать только итоги, или 3, чтобы отобразить всё.

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

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

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

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

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

    Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.

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

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

    Как сгруппировать данные в Excel

    Есть два способа создать структуру листа: автоматический и ручной.

    Автоматическое создание структуры в Excel

    Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру .

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

    Ручное создание структуры

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

    1. Оформите и наполните таблицу, создайте итоговые строки и столбцы
    2. Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура . В открывшемся окне настройте расположение строк и столбцов итогов

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

    1. Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо ). Будет создана группировка

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

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

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

    Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру .

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

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

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

    Добавить комментарий Отменить ответ

    8 комментариев

    Добрый день!
    Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
    Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.

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

    Добрый день!
    Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)

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

    Здравствуйте! Почему при создании структуры с первого столбца в документе не отображается плюсик? Только точки над структурируемыми столбцами и цифры уровня структуры… Что делать, если структура нужна именно с 1го столбца? Excel 2016. Спасибо.

    Здравствуйте, Елена. Не отображается плюсик, потому что он проставляется над столбцом промежуточных итогов. Например, в столбцах у Вас данные по месяцам, и они сворачиваются в итоговые цифры года. Плюсик будет над столбцом соответствующего года.
    Итоговый столбец может быть слева или справа от группируемого массива. У Вас в настройках выставлено «слева», однако столбца слева не существует, Вы группируете начиная с первого. Потому, плюсика и нет. Вы можете пойти двумя путями:
    1. Скрывать и раскрывать группировку кнопками уровней в верхнем левом углу окна
    2. Выбрать расположение столбца итогов справа от группируемого массива. Тогда плюсик будет отображаться в следующем столбце после группируемых. Как это сделать — я рассказал в пункте «Ручное создание структуры»

    Спасибо.
    Научился группировать столбцы за 15 секунд.

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

    Хочу облегчить жизнь тем, кто работает с большими таблицами. Для этого мы сейчас разберемся с понятием группировка строк в excel. Благодаря ему ваши данные примут структурированный вид, вы сможете сворачивать ненужную в настоящий момент информацию, а потом быстро ее находить. Удобно, правда?

    Инструкция

    Открываем файл excel и приступаем к группировке:

    • Выделите нужные строки;
    • Откройте вкладку «Данные» в меню сверху;
    • Под ним в поле «Структура» найдите команду «Группировать»;

    • В появившемся окошке поставьте галочку напротив строк;

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

    Задаем название

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

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

    Теперь нам необходимо исправить заданную ранее систематизацию:

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

    Автоматическая структуризация

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

    Благодаря этому таблица не занимает много места.

    Правда, не всегда программа структурирует то, что нужно. Тогда приходиться прибегать к ручным настройкам. Однако если вы имеете дело с формулами, то автоматическая группировка вероятнее всего вам подойдет. Чтобы ее применить, команде «Группировать» выберите подпункт «Создание структуры» — готово.

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

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

    Максимально оптимизировать вашу таблицу поможет такая функция экселя как сортировка данных. Ее можно производить по разным признакам. Я расскажу об основных моментах, которые помогут вам в работе.

    Цветовое деление

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

    • Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
    • В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

    • В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
    • В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
    • Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

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

    Объединение значений

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

    В группе «Порядок» есть пункт «Настраиваемый список», нажав на который вы можете воспользоваться предложением сортировки по спискам экселя или настроить собственный. Таким способом можно объединить данные по дням недели, с одинаковыми значениями и пр.

    Упрощаем большую таблицу

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

    • Проверьте, чтобы в начале всех столбцов, которые мы будем объединять, был заголовок, что все они содержат информацию одинакового типа, и нет пустых мест.
    • Чтобы столбцы имели опрятный вид, в поле сортировки выберите команду «Сортировать от А до Я» или наоборот.
    • Вставьте итоговые строки, то есть, те, что имеют формулы и ссылаются на объединяемые нами ячейки. Сделать это можно с помощью команды «Промежуточные итоги», которая находится в том же поле, что и кнопка «Группировать».
    • Выполните группировку всех столбцов, как мы делали раньше. Таким образом, у вас получится гораздо больше плюсиков и минусов с левой стороны. Вы можете также переходить от одного уровня к другому путем нажатия вкладок с цифрами в той же панели сверху.

    На этом всё, друзья.

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

    Структурирование таблиц в MS Excel

    Цель работы: научиться структурировать таблицы ручным

    способом и способом автоструктурирования

    Содержание работы:

    1 Ручной способ структурирования

    3 Структурирование с подведением итогов

    Структурирование таблиц

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

    Символы структуры – это символы, используемые для изменения вида структурированного листа. Ими являются знаки «плюс» и «минус» или цифры 1, 2, 3, 4, показывающие уровень структуры (рис. 10.1).

    Структурированный по строкам документ приведён на рисунке 10.1. Значки «+» означают, что строки 3-5 и 15-18 в таблице свёрнуты. При щелчке по ним они разворачиваются, т.е. полностью появляются на экране и появляется вертикальная линия уровня, а значок «+» заменяется на «-«. В развёрнутом виде представлена структура строк 7-9 (отмечены точками и линией уровня со значком «-«). При щелчке по значку «-» группа строк сворачивается в значок «+».

    Рисунок 10.1 — Структурированная по строкам таблица

    Перед структурированием таблицы необходимо:

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

    2. Произвести сортировку записей для выделения необходимых групп по порядку (например, по номерам групп).

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

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

    Рисунок 10.2 Раздел Структура со стрелкой справа и Окно Настройка

    Структурирование выполняется с помощью командыГруппироватьизвкладкиДанные Структура , удаление структуры – командой Разгруппировать.

    Ручной способ структурирования

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

    В результате создается структура таблицы (рис. 10.1). Кнопки 1 и 2 — номера уровней структуры – служат для открытия или скрытия соответствующего уровня.

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

    Для отмены одного структурного компонента производится выделение области и выполняется командаДанные Разгруппировать.

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

    Автоструктурирование

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

    Пример такой таблицы приведен на рис. 10.3. В ней, по сравнению с таблицей рис. 10.1. введены столбец, в котором рассчитывается максимальная оценка студента с помощью функции МАКС(может быть любая формула Excel) и строка Итог, в которойрассчитывается с помощью функцииСРЗНАЧ средний балл в группе. Порядок следования исходных данных и результатов (итогов) — слева направо и сверху вниз, что позволяет применить к таблице автоструктурирование.

    После ввода в таблицу исходных данных и формул курсор устанавливается в произвольную ячейку списка и выполняется командаДанныеГруппироватьСоздание структуры. Все структурные части таблицы создаются автоматически, как по строкам, так и по столбцам (рис. 10.4 – строки группы КТ-32 свёрнуты, можно также свернуть столбцы A-D и всю таблицу).

    Рисунок 10.3Таблица, пригодная для автоструктурирования

    Рисунок 10.4 Вид таблицы после автоструктурирования

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

    Читать еще:  Как подвести итоги в excel
    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector