Структурирование рабочих листов в Excel

Размещено на http://www.allbest.ru/

Тема: «Структурирование рабочих листов в Excel»

1. Структура рабочего листа

На этом шаге мы рассмотрим пример структуры рабочего листа.

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

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

Рис. 1 Модель бюджета с промежуточными итогами

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

Рис. 2 Модель бюджета после создания структуры

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

Рис. 3 Второй уровень частично расширенной структуры модели бюджета

На первом уровне структуры будут видны только общие итоги (рис. 4).

Рис. 4 Первый уровень структуры модели бюджета

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

Работая со структурами, нужно иметь в виду следующее:

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

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

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

Можно скрыть символы структуры, сохраняя при этом саму структуру.

Структура может иметь до восьми вложенных уровней.

На этом шаге мы рассмотрим создание структур рабочего листа.

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

Создать структуру можно одним из способов:

автоматически;

вручную.

Чтобы автоматически создать структуру для некоторого диапазона данных, надо выполнить следующее:

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

Выбрать команду Данные | Группа и структура | Создание структуры.

Программа Excel проанализирует формулы из выделенного диапазона и создаст структуру. В зависимости от формул будет создана горизонтальная, вертикальная или смешанная структура.

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

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

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

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

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

Можно выбирать также группы групп. Это приведет к созданию многоуровневых структур. Создание таких структур следует начинать с внутренней группы и двигаться изнутри наружу. В случае ошибки при группировке можно произвести разгруппирование с помощью команды Данные Группа и структура Разгруппировать.

В Excel есть кнопки инструментов, с помощью которых можно ускорить процесс группировки и разгруппировки (рис. 1). Кроме того можно воспользоваться комбинацией клавиш Alt + Shift + для группировки выбранных строк или столбцов, или Alt + Shift +для осуществления операции разгруппирования.

Рис. 1 Инструменты структуризации

Инструмент структуризации содержит следующие кнопки:

Таблица 1 Кнопки панели инструментов Структура

2. На следующем шаге рассмотрим использование структур

На этом шаге мы рассмотрим использование структур рабочего листа.

Отображение уровней

Чтобы отобразить какой-либо уровень структуры, нужно щелкнуть на соответствующем символе структуры, которые представляют собой кнопки с номерами (1, 2 и т.д.) и кнопки со знаками "плюс" и "минус".

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

Можно расширить или сжать некоторую часть рабочей таблицы, щелкнув на соответствующей кнопке + или -.

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

3. Применение стилей к структуре

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

В Excel используются следующие имена стилей (n - номер уровня структуры):

УровеньСтрок_n;

УровеньСтолб_n.

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

Рис. 1 Диалоговое окно Структура документа

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

4. Добавление данных к структуре

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

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

5. Удаление структуры

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

рабочий лист структура модель

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

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

Размещено на Allbest.ru