Опубликован: 26.05.2021 | Уровень: для всех | Доступ: платный
Лекция 6:

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

< Лекция 5 || Лекция 6 || Лекция 7 >
Аннотация: Цель работы: научиться структурировать таблицы ручным способом и способом автоструктурирования. Содержание работы: Ручной способ структурирования. Автоструктурирование. Структурирование с подведением итогов Порядок выполнения работы: Изучить методические указания. Выполнить задания. Оформить отчет и ответить на контрольные вопросы.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Команда Данные > Итоги создает структуру таблицы и одновременно вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной функцией. Функции для подведения автоматических итогов приведены в таблице 6.1.

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

Примечания:

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

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

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

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

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

При нажатии кнопки ОК автоматически создаются промежуточные итоги. Слева таблицы на служебном поле появляются символы структуры:

  • линии уровней структуры;
  • кнопка "+" показа групп;
  • кнопка "-" скрытия групп;
  • кнопки уровней структуры.
Окно Промежуточные итоги для установки параметров

Рис. 6.4. Окно Промежуточные итоги для установки параметров

автоматического подведения итогов

На рис. 6.5 рис. 6.5 представлена структура с итогами для таблицы рисунка 6.2. рис. 6.2

В ней: 1-й уровень – средняя оценка по всему списку (КТ-11 Среднее, КТ-32 Среднее, КТ-61 Среднее и Общее среднее);

2-й уровень – средняя оценка по коду преподавателя (1 Среднее и 2 Среднее).

Команда Данные > Итоги > Убрать всё убирает структурирование и все предыдущие итоги.

Пример структуры с промежуточными итогами

Рис. 6.5. Пример структуры с промежуточными итогами

ЗАДАНИЕ

1. Подготовить для структурирования таблицу своего варианта из лабораторной работы № 4 "Списки в Excel. Сортировка и фильтрация данных".

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

Например, в первом и втором – Зарплата и Налог, в третьем – Сумма к выдаче (как разность зарплаты и налога), или Цена и Количество = Общая стоимость (произведение цены на количество), или Доза и Количество приёмов = Общая доза (произведение дозы и количества), или Стоимость бензина и Пробег = Затраты на топливо (произведение стоимости и пробега) и т.п.

Снизу добавить – ещё одну строку, ввести в её ячейки формулы для обработки данных в столбцах (например, сумма, среднее, произведение, максимум и т.п.)

2. Выполнить в подготовленной таблице:

  • Ручное структурирование
  • Автоструктурирование с введением нового иерархического уровня по строкам.
  • Структурирование с подведением итогов.

В результате выполнения работы должно получиться три таблицы – аналогично рис. 6.1 рис. 6.1, 6.3 рис. 6.3 и 6.5. рис. 6.5

Пояснения к выполнению:

1. Для ручного структурирования:

  • отсортировать таблицу по любому какому-нибудь столбцу, например, по фамилии, должности, маршруту;
  • создать структурную группу для строк 1-3. Для этого нужно выделить строки 1-3 и выполнить команду Данные > Группа и Структура > Группировать (для строк). Аналогично создать структуру для двух других групп (строки 4-6 и 7-10);
  • создать структурные группы для столбцов. Для этого выделить 2-3 столбца (начиная от ячейки с именем и вниз – до последней строки) и выполнить команду Данные > Группа и Структура > Группировать (для столбцов). Аналогично повторить для оставшихся столбцов.

Сохранить результат на дискете.

2. Для автоструктурирования таблицы и введения нового уровня ручным способом:

  1. Создание автоструктуры: установить курсор в любую ячейку области данных, выполнить команду Данные > Группа и Структура > Создать структуру;
  2. Введение нового иерархического уровня ручным способом – по строкам:
    • выделить строки 1-3 (с данными) и выполнить команду Данные > Группа и Структура > Группировать;
    • выделить остальные строки таблицы и выполнить команду Данные > Группа и Структура > Группировать.

В результате внутри первого уровня структурирования (1) появится второй уровень (2), созданный вручную.

3. Структурирование с подведением итогов (на примере таблицы на рис. 6.2 рис. 6.2, 6.5).

  • отсортировать список записей таблицы (рис. 6.2 рис. 6.2) командой Данные > Сортировка, в окне Сортировка диапазона указать те столбцы, которые нужны для вычисления итогов. Для таблицы на рисунке 6.5 рис. 6.5 сначала задавалась сортировка для № группы, затем для Код преп, чтобы получить итоги – средний балл по каждой группе, всей группе и по каждому преподавателю;
  • подобрать вид итогов, которые можно сделать для таблицы своего варианта и отсортировать нужные для этого столбцы;
  • создать 1-й уровень итогов – средний балл по группе. Для таблицы (рис. 6.2 рис. 6.2) устанавливался курсор в любую ячейку списка и выполнялась команду Данные > Итоги. В диалоговом окне Промежуточные итоги указывалось:
    • При каждом изменении в № группы
    • Операция: Среднее
    • Добавить итоги по: Оценка
    • Заменять текущие итоги: нет
    • Конец страницы между группами: нет
    • Итоги по данными: да.

После нажатия ОК был рассчитан средний балл по каждой группе и общее среднее.

  • создать 2-й уровень итогов – средний балл по каждому преподавателю для каждой группы: установить курсор в любую ячейку списка и выполнить команду Данные > Итоги. В окне Промежуточные итоги указать:
    • При каждом изменении в - Код преп;
    • Операция: Среднее;
    • Добавить итоги по: Оценка;
    • Заменять текущие итоги: нет;
    • Конец страницы между группами: нет;
    • Итоги по данными: да.

После нажатия ОК будет рассчитан средний балл по каждому преподавателю в каждой группе.

Для получения промежуточных итогов можно скомпоновать любые поля исходного списка, например, на 1-м уровне – по маршруту (званию, коду предмета…), на 2-м уровне – по № зачётной книжки (зарплате, должности…, на 3-м уровне – по дате, стоимости, телефону…).

КОНТРОЛЬНЫЕ ВОПРОСЫ

  1. Что такое структурированная таблица?
  2. Виды структурирования таблиц в Excel.
  3. Как выполняется ручное структурирование?
  4. Как выполняется автоструктурирование?
  5. Как выполняется структурирование с подведением итогов?
  6. Для чего применяется структурирование таблиц?
  7. Как выглядит структура таблицы?
  8. Как отменить структуру таблицы?
< Лекция 5 || Лекция 6 || Лекция 7 >
Арсен Никифоров
Арсен Никифоров

Есть такие задания, и они никак не принимаются. Притом ошибки только по этим заданиям, в какой бы последовательности я их не заполнял. Как их заполнять??? Инструкций в заданиях нет. Там через запятые, подряд как число, через пробел, или надо текст весь писать через запятую или точку?

Задание: Пронумеруйте шаги Создание имени путем выделения ячеек на листе:
​1) На вкладке Формулы в группе Присвоенные имена выберите команду Создать из выделенного.
2) В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки.
3) Выберите диапазон, которому нужно присвоить имя.

Дмитрий Мошенский
Дмитрий Мошенский
Россия, Краснодар
Артём Пастушенко
Артём Пастушенко
Россия, г.Краснодар кл. московская 125к1