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

1. Выделить область для группирования (группу) – несколько смежных строк или столбцов в списке (не менее 2-х). Для группировки выделяется строка или столбец полностью.

2. Нажать кнопку Группировать (вкладка Данные/группа Структура).

В результате создается структура таблицы (рис. 10), содержащая следующие элементы слева и/или сверху на служебном поле:

· линии уровней структуры, показывающие соответствующие группы иерархического уровня;

· кнопка плюс (минус) для раскрытия (скрытия) групп структурированной таблицы;

· кнопки номера уровней 1, 2, 3 для открытия или закрытия соответствующего уровня.

Рис. 10. Таблица со структурированными столбцами

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

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

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

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

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

Поясним это на примере.

Пример 5.

На рисунке (рис. 11) приведена таблица расчета зарплаты для работников. Исходными данными являются столбцы Фамилия, Зарплата (оклад без вычетов и надбавок), Надбавка и Премия. Столбцы Начислено, Подоходный налог, Пенсионный фонд, Общий налог, Сумма к выдаче являются расчетными.

Также расчетной является строка Итого, расположенная ниже фамилий.

Рис. 11. Исходная таблица для автостуктурирования

По каждому виду начислений в строке Итого значения рассчитываются с помощью функции СУММ общая сумма по ячейкам столбца. Порядок следования исходных данных и результатов (итогов) – слева направо, сверху вниз, что позволяет применить автоструктурирование таблицы (Рис. 10).

Для выполнения автоструктурирования необходимо установить курсор в произвольную ячейку списка и выбрать команду Создать структуру из меню кнопки Группировать. Все структурные части таблицы создаются автоматически (рис. 12). На рис. 12 видно, что среда создала 3 уровня структуры по столбцам, потому что «по мнению среды» итоги подводятся в столбцах Начислено, Общий налог и Сумма к выдаче. Это объясняется тем, что в этих столбцах выполняются операции суммирования и вычитания. По строкам создан только 1 уровень, т.к. сумма по строкам выполняется только в одной строке.

Рис. 12. Таблица после автоструктурирования

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

В среде Excel существует инструмент структурирования с одновременным подведением итогов.

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

Таблица 1. Функции для подведения автоматических итогов

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

 

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

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

· в поле Операция указать функцию подведения итогов (таблица 1);

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

· включить (или отключить) флажки Заменить текущие итоги, Конец страницы между группами и Итоги под данными.

 

Рис. 13. Окно Промежуточные итоги

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

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

Флажок Итоги под данными позволяет установить расположение итогов (либо под данными, либо над данными).

Для больших списков можно указать размещение итогов на разных страницах – флажок Конец страницы. Этот флажок устанавливается только для печати листов.

Кнопка Убрать все удаляет все итоги из текущего списка.

Пример 6.

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

Рис. 14. Таблица с промежуточными итогами

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

Практикум 4.5.

Задание 1. Сортировка списка данных

Задание выполняется на основе листа Касса в файле Заготовки Excel. Выполнить различные виды одноуровневой и многоуровневой сортировки.

Критерии одноуровневой сортировки

· по дате;

· по улице;

· по номеру корреспондирующего счета.

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

· по дате и номеру корреспондирующего счета;

· по номеру корреспондирующего счета и дате;

· по улице, номеру участка и дате.

Каждый вид сортировки выполнять в отдельной копии исходного списка.

Выполнить сортировку по своим критериям.

Технология выполнения задания

1. Прочитайте параграфы «4.5.1. Понятие списка (базы данных), 4.5.2. Сортировка данных в списке» темы 4.5.

2. В файле Заготовки Excel создайте копию листа Касcа и переименуйте ее на Касcа-cортировка.

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

4. Установите курсор в области 1-го списка в поле Дата оплаты.

5. Для контроля правильности сортировки залейте цветом какую-нибудь строку в списке.

6. Выполните сортировку. Убедитесь, во-первых, в том, что данные упорядочились по дате, во-вторых, что выделенная строка не «испортилась», и, в-третьих, другие копии списков остались без изменения.

7. Залейте цветом столбец, по которому вы выполняли сортировку.

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

9. Проанализируйте разницу в двух критериях сортировки «по дате и номеру корреспондирующего счета» и «по номеру корреспондирующего счета и дате».

Задание 2. Автофильтр

Задание выполняется на основе листа Касса в файле Заготовки Excel. Для заданного списка данных необходимо выполнить фильтрацию данных по различным критериям. Для каждого критерия необходимо создать копию листа Касса.

Критерии фильтрации:

· данные за один кассовый день;

· данные о взносах за электроэнергию для одного участка;

· данные о сборах за 2-й квартал 2010 года.

Придумать и выполнить свои критерии фильтрации с помощью автофильтра.

Технология выполнения задания

1. Прочитайте параграф «Автофильтр» темы 4.5.

2. В файле Заготовки Excel создайте копии листа Касcа (не менее 3-х) и переименуйте их на Касcа-автофильтр (1, 2, 3…).

3. Установите курсор в области списка и включите фильтр.

4. Отберите данные за один кассовый день.

5. Выполните другие критерии фильтрации.

Задание 3. Расширенный фильтр

Задание выполняется на основе листа Касса в файле Заготовки Excel.

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

Критерии сравнения:

· данные о произведенных расходах;

· данные о сборах членских взносов по улицам Центральная и Солнечная.

 

Вычисляемый критерий:

· данные о сборах за последние две недели;

· данные по сборам за электроэнергию выше среднего показателя.

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

Технология выполнения задания

1. Прочитайте параграф «Расширенный фильтр» темы 4.5.

2. В файле Заготовки Excel создайте копию листа Касcа и переименуйте ее на Касcа-расшир фильтр1.