При обработке больших таблиц, которые полностью не помещаются на экране, возникает необходимость в фиксации отдельных частей таблиц, чтобы они при перемещении по документу всё время находились на экране. Для этого используется команда Закрепить области.
Закрепить области служит, в основном, для отображения названий строк и столбцов при прокрутке:
1 Чтобы закрепить верхнюю горизонтальную область, выделите строку, над которой требуется разбить лист.
Чтобы закрепить левую вертикальную область, выделите столбец, слева от которого требуется разбить лист.
Чтобы закрепить обе области, укажите ячейку, левее и выше которой должны проходить вертикальная и горизонтальная линии закрепления областей – рисунок 8.2.
Рисунок 8.2 – Закрепление областей левее и выше активной ячейки
2 В меню Вид выберите команду Закрепить области. В появившемся списке предлагается закрепить область, первую строку или первый столбец. Зафиксированная область будет отчеркнута тонкими линиями. Теперь закреплённые строки и столбцы будут всё время находиться на экране. Снять закрепление можно командой Снять закрепление областей из того же списка.
Контрольные вопросы
1 Состав вкладки Вид, раздел Окно.
2 Назначение команды Новое окно. Как вывести на экран три части одного документа?
3 Назначение и использование команды Упорядочить всё.
4 Назначение и использование команд Разделить, Скрыть, Отобразить.
5 Как закрепить первые две строки и три столбца листа?
6 Назначение и использование команды Закрепить области.
7 Как снять разделение и закрепление областей?
Задание
1 Открыть со своего носителя (флэшки) документ MS Excel, разработанный в лабораторной работе №2 «Создание и редактирование диаграмм и графиков в MS Excel», сделать с него копию, расположить окно-оригинал и окно-копию на одном листе и скопировать таблицу и график y= n ·sin(2x) из окна-оригинала в конец окна-копии.
2 Открыть со своей флэшки три любых файла (предыдущие выполненные лабработы) на Листе 1, Листе 2 и Листе 3 соответственно и расположить на экране их окна способом слева направо. Скопировать любую таблицу из первого окна во второе и в третье окно, документ сохранить на флэшку под именем «Разделение».
3 Снять разделение, закрепить первую строку и два первых столбец в окне Листа 1.
Содержание отчёта
1Название, цель, содержание работы, пол, возраст, имя вашего пёсика.
2 Задание своего варианта
3 Письменные ответы на контрольные вопросы
4 Выводы по работе
На флэшке должны быть сохранены результаты работы
Практическое занятие №9
Списки в MS Excel. Сортировка и фильтрация данных
Цель работы: научиться создавать базы данных, выполнять
сортировку и фильтрацию данных
Содержание работы:
1 Создание списка – базы данных
2 Сортировка данных в списке
3 Фильтрация данных в списке
Общие сведения
Список (база данных MS Excel) – это электронная таблица, в которой имена столбцов занимают одну строку, а строки имеют фиксированную структуру.
База данных (БД) – это поименованная совокупность данных, имеющая одно или несколько приложений. Например, список группы является БД студентов. Её приложение для деканата – это список студентов, обучающихся в академической группы № …, приложение для военкомата – список призывников, для телефонной компании – список абонентов и т.д.
В терминах БД таблица MS Excel– это отношение, строка в таблице – запись или кортеж, столбец – поле или атрибут, а шапка таблицы с именами полей называется схемой отношения. Весь блок ячеек с данными называется областью данных.
Строка заголовков (схема таблицы) состоит из ячеек с именами полей, причём имя поля в БД должно располагаться в одной ячейке таблицы MS Excel.
Операции над данными
Над данными в БД можно выполнять различные операции обработки, которые сгруппированы во вкладке Данные: сортировка, фильтрация, группировка, проверка вводимых данных, анализ и др. Чтобы процессор MS Excel воспринимал таблицу БД как список, перед выполнением какой-либо команды таблицу или нужный диапазон нужно выделить.
Для работы с БД используются формы вывода данных (команда Форма). Кнопки Форма нет на ленте, однако эту функцию можно использовать в MS Excel, добавив её кнопку на панель быстрого доступа – рисунок 9.1. Для этого во вкладке Файл (Office) нужно нажать Параметры Excel, выбрать слева - Настройка, справа - Все команды или Команды не на ленте, найти Форма, нажать Добавить и ОК.
Рисунок 9.1 – Кнопка Форма на панели быстрого доступа
В MS Excel можно создавать запросы на данные из созданного списка и из внешних БД (команда Данные\Получить внешних данные и выбрать нужный источник), обновлять данные через Internet и др.
1 Создание списка – базы данных
После запуска приложения MS Excel нужно ввести таблицу БД, но перед этим нужно настроить строку её схемы (заголовка). Поскольку имя каждого поля должно занимать одну ячейку, нужно выделить строку для заголовка таблицы, затем во вкладке Главная \ раздел Ячейки \ Формат \ Формат ячеек (рисунок 9.2) в окне Формат ячеек \вкладкаВыравнивание (или нажать Ctrl + 1) установить параметры:
Рисунок 9.2 – Настройка строки заголовка таблицы БД
▪ по горизонтали: по значению
▪ по вертикали: по верхнему краю
▪ отображение: переносить по словам (если Имя поля – длинное)
Если имя поля ненамного превышает ширину ячейки, то можно ограничиться командой Главная \ Ячейки \ Формат \ Автоподбор ширины столбца.
Теперь можно вводить таблицу (список) БД - рисунок 9.3.
Рисунок 9.3 Список Экзаменационная ведомость
2 Сортировка данных в списке
Сортировка данных в таблице БД MS Excel является основной операцией, т.к. многие операции группировки, которые выполняются из меню Данные, можно использовать только после сортировки.
Цель сортировки – упорядочивание данных в порядке возрастания или убывания, она производится в том же списке. В среде MS Excel сортировка выполняется по трём полям, причём сортировка по второму полю производится для одинаковых значений отсортированного первого поля, а третьего – для одинаковых значений второго поля.
Для сортировки данных нужно выполнить команду Данные\ Сортировка, откроется окно Сортировка диапазона (рис. 9.4), в котором выбираются столбцы и вид упорядочивания – по возрастанию или по убыванию.
При сортировке по возрастанию упорядочивание идёт от меньшего к большему – по алфавиту, по хронологии дат, но согласно приоритету: числа, текст, логические значения, значения ошибок и пустые ячейки.
При сортировке по убыванию используется обратный порядок, только пустые ячейки всегда собираются в конце списка.
Проведём сортировку списка "Экзаменационная ведомость" по полям Код преподавателя, Оценка и Фамилия И.О.
Рисунок 9.4 Окна Сортировка диапазона и Параметры сортировки
Результат сортировки показан на рис. 9.4, где столбецЕ отсортирован по возрастанию, в столбце D по возрастанию отсортированы одинаковые записи столбца Е, а в столбце В – по алфавиту одинаковые значения в столбце D.
Рисунок 9.4 Результат сортировки данных
Дополнительные установки сортировки задаются в окне Параметры сортировки, вызываемой кнопкой Параметры (рис. 9.4). Здесь можно задать сортировку с учётом или без учёта регистра, по столбцам или построкам, обычный или выбранный из списка. Этот список можно создать самостоятельно с помощью команды Сортировка \ Настраиваемая сортировка (выделив таблицу, нажать правой клавишей мыши и выбрать Сортировка\Настраиваемая сортировка).
