prosdo.ru 1



ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL XP

Лабораторная РАБОТА № 4.
СОРТИРОВКА И ФИЛЬТРАЦИЯ ДАННЫХ. структурирование таблиц
Цель работы: дать общее понятие о списке (базе данных Excel XP) и об автоструктурировании таблиц, научить студентов сортировать данные в списке, фильтровать данные с помощью автофильтра, кроме того, структурировать таблицы и т. д.
Теоретические сведения
Понятие о списке (базе данных Excel)

Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel (рис. 1) и используют соответствующую терминологию:


  • строка списка — запись базы данных;

  • столбец списка — поле базы данных.

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

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


Область имен полей

Область данных

Запись

Поле


Рис. 1. Структурные элементы списка
Внимание!


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

  2. Верхняя строка списка содержит имена полей (названия столбцов).

  3. Имя поля может состоять из нескольких слов любого алфавита. Обязательное требование — размещение в одной ячейке.


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

Для размещения имени поля списка в одной ячейке (рис. 1) необходимо: выделить ячейку или всю строку, где будут располагаться имена полей; ввести команду Формат ячеек и выбрать вкладку Выравнивание; на вкладке установить следующие параметры:


По горизонтали: по значению

По вертикали: по верхнему краю или по центру

Отображение: установить флажок переносить по словам



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

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

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

В среде Excel XP предусмотрены три уровня сортировки, которые определяются в диалоговом окне «Сортировка» (рис.2, а) параметром Сортировать по.


а б

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

а - сортировка; б - параметры сортировки.

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

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

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

Кнопка Параметры выводит диалоговое окно «Параметры сортировки», в котором задаются дополнительные установки сортировки (рис. 2, б): с учетом регистра или без учета; по столбцам или по строкам.
Основные технологические операции по сортировке данных


Действие

Содержание действия

Сортировка списка

  1. Установить курсор в области списка;

  2. Выполнить команду Данные, Сортировка;

  3. Указать порядок и направление сортировки для каждого ключа сортировки;
  4. Нажать кнопку Параметры и выбрать параметры сортировки (учет регистра, направление сортировки — по строкам или по столбцам).


Создать новый список для сортировки

  1. Нажать на кнопку Сортировка;

  2. В области «Порядок», в диалоговом окне со стрелочкой выбрать Настраиваемый список… ;

  3. Сформировать элементы списка.

Изменить список для сортировки

  1. Перейти к Настраиваемый список…;

  2. Выделить в окне Списки начало редактируемого списка;

  3. Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать);

  4. Нажать кнопку ОК.

Удалить список для сортировки

  1. Перейти к Настраиваемый список…;

  2. Выделить в окне Списки начало редактируемого списка;

  3. Нажать кнопку Удалить.

Общие сведения о фильтрации данных

Фильтрация данных в списке — это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.
Автофильтрация

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


Рис. 4. Список с автофильтром
По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

  • Выделить все — выбираются все записи без ограничений;


  • Можно так же установить галочки около нужных вам записей;

  • Первые 10 — данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 5) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;



Рис. 5. Диалоговое окно «Наложение условия по списку».


  • условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 6).




Рис. 6. Диалоговое окно «Пользовательский фильтр»
Условие для отбора записей по конкретным значениям в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия включает:

  • оператор отношения:

  • = (равно),

  • <> (не равно),

  • > (больше),

  • >= (больше или равно),

  • <(меньше),

  • <= (меньше или равно), начинается с, содержит и т.п.;

  • значение, которое может выбираться из списка или содержать шаблонные символы *,?

Пример. Для Кода предмета можно сформировать условия:

>=n* — отобрать все записи, которые содержат код предмета, начинающийся с буквы n;

>= n1 И <=n2 — отобрать все записи, которые содержат коды предметов n1 и n2;

<>n1 — отобрать все записи, которые не содержат кода предмета nl.

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


Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные, Автофильтр.
Структурирование таблиц

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

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

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

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


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

  • кнопка <плюс> — для раскрытия групп структурированной таблицы;

  • кнопка <минус> — для скрытия групп структурированной таблицы;

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

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

На рис. 7 дан фрагмент структурированной таблицы по учебным группам (по строкам), что показано в левом поле линией и кнопками со знаком плюс и минус. Кроме того, создан структурный элемент (линия в верхнем поле) в столбцах, позволяющий скрыть или показать столбцы (Код предмета, Таб. № препод., Вид занятий).


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



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

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

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

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

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


Рис.8. Пример исходной таблицы, в которой можно применить автоструктурирование



Рис. 9. Вид таблицы после автоструктурирования по столбцам
ЗАДАНИЕ № 1

ТЕХНОЛОГИЯ РАБОТЫ


  1. Проделайте подготовительную работу: создайте книгу и сохраните ее под именем Spisok, а Лист1 Сортировка.

Для этого необходимо выполнить следующее:

  • создайте новую рабочую книгу командой Office, Создать. Укажите шаблон — Новая Книга;Создать

  • сохраните созданную рабочую книгу под именем Spisok командой Office, Сохранить как;

  • переименуйте Лист1 на Сортировка.

  1. В новой рабочей книге на Листе 1 создайте таблицу, приведенную на рис. 3.

Для этого:

Сформируйте на листе Сортировка таблицу (см. рис. 3). Для этого:

  • выделите первую строку;

  • вызовите контекстное меню и выберите команду Формат ячеек;

  • п
    По горизонтали: по значению

    По вертикали: по верхнему краю

    Переносить по словам: установить флажок
    роизведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание:



  • заполните таблицу данными.

  1. Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.

Для того, чтобы выполнить сортировку по столбцу Таб. № препод., необходимо выполнить следующее:
  • установите курсор в поле списка и введите команду Данные, Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;


  • в диалоговом окне «Сортировка диапазона» установите:



Сортировать по: поле «Таб. № препод.», по возрастанию

Затем по: поле «Номер группы», по возрастанию

В последнюю очередь по: поле «Код предмета», по возрастанию



  • установите флажок Идентифицировать поля по подписям

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


Имена полей





Рис. 3. Пример списка (база данных)
ЗАДАНИЕ № 2

ТЕХНОЛОГИЯ РАБОТЫ

Выберите данные из списка по критерию отбора, используя Автофильтр.

  1. Проведите подготовительную работу — переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (см. рис. 3).

Для этого

Проведите подготовительную работу:

  • переименуйте Лист 2Автофильтр;

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

  1. Выберите из списка данные, используя критерий: для преподавателя — al выбрать сведения о сдаче экзамена на положительную оценку, вид занятий — л.

Для этого:
  • установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка;


  • сформируйте условия отбора записей:

  • в столбце Таб. № препод. нажмите кнопку , из списка условий отбора выберите al;

  • в столбце Оценка нажмите кнопку , из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

  • в столбце Вид занятия нажмите кнопку , из списка условий отбора выберите л.

  1. Отмените результат автофильтрации:

Для этого необходимо выполнить следующее:

установите указатель мыши в список и выполните команду Данные, Фильтр, Автофильтр.

  1. Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

Для этого, необходимо задавать произвольные критерии отбора записей.
ЗАДАНИЕ № 3

ТЕХНОЛОГИЯ РАБОТЫ

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

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

Для этого:

  • откройте книгу с именем Spisok с помощью команды Файл, Открыть;

  • переименуйте Лист 3Структура;

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

  1. Отсортируйте строки списка по номеру учебной группы.

Для этого:

Отсортируйте строки списка по номеру учебной группы:


  1. Вставьте пустые разделяющие строки между учебными группами.

Для этого необходимо:

• выделите первую строку с другим, отличным от предыдущей строки, номером группы;

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

  1. Создайте структурные части таблицы (см. рис. 7).

Для этого:

  • выделите блок строк, относящихся к первой группе;

  • выполните команду Данные, Структура, Группировать. В появившемся окне установите флажок строки;

  • аналогичные действия повторите для других групп.

  1. Создайте структурную часть таблицы для столбцов: (Код предмета, Таб. № препод., Вид занятий) (см. рис. 7).

Для этого необходимо выполнить следующее:

  • выделите столбцы, подводя указатель мыши к имени столбца и, щелкнув левой кнопкой, протащите мышь;

  • выполните команду Данные, Структура, Группировать. В появившемся окне установите флажок столбцы и нажмите кнопку ОК.

  1. Закройте и откройте структурные части таблицы.

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

  1. Отмените структурирование.

С помощью команды: Данные, Структура, Разгруппировать.

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



Список контрольных вопросов

  1. Понятие о списке (базе данных Excel).

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

  3. Общие сведения о фильтрации данных.
  4. Фильтрация данных с помощью автофильтрации.


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

  6. Понятие об автоструктурировании таблиц.


Список литературы:

  1. Карлберг, Кондрад Бизнес-анализ с помощью EXCEL.:Пер. с англ. К.: Диалектика, 1997 г.

  2. Гарнаев А. Ю. Использование MS EXCEL и VBA в экономике и финансах.- СПб.: БХВ – Санкт Петербург,1999 г.

  3. Маркарьян Э.А., Герасименко Г.П. Финансовый анализ – М.: «ПРИОР»,1999 г.

  4. Макарова Н.В. Практикум по технологии работы на компьютере. - 3-е изд., перераб. - М.: Финансы и статистика, 2004 г.

  5. Заботин Ю. Д., Шапошников А. С. Новейшая энциклопедия персонального компьютера 2005. – М.: РИПОЛ классик, 2005 г.