Сортировка данных, фильтры, работа с листами рабочей книги.

Краткие теоретические сведения

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

Для сортировки записей выделите все строки таблицы, выполните команду ДАННЫЕ > СОРТИРОВКА, выберите столбец, по которому нужно отсортировать данные, и установите переключатель в положение по возрастанию или убыванию. Если требуется сортировка одновременно по нескольким полям, то в порядке сверху вниз выбирается приоритетность полей.

Базу данных можно просматривать в режиме таблицы и в режиме формы, для чего используется команда верхнего меню ДАННЫЕ > ФОРМА.

Для перехода в этот режим:

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

• выберите команду ДАННЫЕ > ФОРМА, получите форму данных, например как на рис. 1, содержащую статический текст (имена полей базы данных), и окон редактирования, в которых можно вводить и редактировать текст.

Рис. 1. Режим формы

Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования («Кол-во Остатка» и «Сумма Остатка»).

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

Перемешаться между записями можно либо при помощи кнопок «Назад», «Далее», либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных.

Перемещаться между окнами редактирования (в которые вносятся данные) удобно клавишей Tab.

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

Заполнять таблицу в режиме формы довольно удобно.

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

Как и в любой базе данных, информацию можно фильтровать (т. е. выводить на экран только те данные, которые интересуют пользователя). Для этого есть удобное средство автофильтр: выделите таблицу с заголовками столбцов и выберите команду ДАННЫЕ > ФИЛЬТР> АВТОФИЛЬТР. После этого в правом нижнем углу названия столбика появится стрелочка, при нажатии на которую мышкой раскрываются критерии поиска по данному столбику (рис. 2).

Рис. 2. Автофильтр

Если условие поиска сложное, то из выпадающего меню надо выбрать УСЛОВИЕ и с помощью диалогового окна пользовательского автофильтра (рис. 3) связать нужные запросы.

Рис. 3. Пользовательский автофильтр

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

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

• для группировки листов, следующих друг за другом, надо щелкнуть мышью на ярлык первого листа, затем, удерживая клавишу SHIFT, щелкнуть на последний, при этом все листы между указанными окажутся с беленькими ярлыками;

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

Листам можно давать имя, для чего устанавливается курсор на ярлык листа и из контекстного меню выбирается команда ПЕРЕИМЕНОВАТЬ, затем вводится необходимое имя.

Данные в столбцах или строках можно скрывать (не отображать на экране). Для этой операции выделяются нужные столбцы или строки и выбирается команда ФОРМАТ > СТРОКА или СТОЛБЕЦ, затем пункт меню СКРЫТЬ.

Задание

1. Откройте таблицу «ПАРТНЕРЫ Microsoft» (файл с именем л6.х1s).

2. Снимите с экрана линии сетки.

3. Выделите название жирным шрифтом 16 пунктов красного цвета и сместите на ячейку вправо.

4. Для заголовков столбцов задайте полужирный шрифт 11 пунктов, центральное выравнивание.

5. Отсортируйте список по полю «Название фирмы» в возрастающем порядке.

6. Дайте имя листу «Партнеры».

7. Для столбцов «Телефон» и «Факс» задайте специальный фор­мат ячейки.

8. Выберите с помощью автофильтра всех партнеров, работаю­щих в Москве.

9. Во всех ячейках с кодом Москвы задайте свой формат и измените код «95» на «095».

10. Скопируйте этот список на второй лист, дайте имя листу «Москва».

11. Оформите список цветом: строку с заголовком темно-желтым, столбец с названием фирмы светло-желтым, установить границы ячеек.

12. Вернитесь на лист «Партнеры» и выберите из списка «Авторизированные учебные центры». Скопируйте полученный список на лист 3.

13. Листу 3 дайте имя «Учебные центры».

14. Оформите строку с заголовком синим цветом, столбец с на­званием фирм салатовым, установить границы ячеек.

Образец выполнения задания

Таблица 1.

Таблица 2.

Таблица 3.

Контрольные вопросы

1. Какая команда производит сортировку данных в таблице?

2. Как происходит фильтрация данных?

Лабораторная работа № 7