Задание 3. Работа со списками

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

Ø Столбцы списков становятся полями базы данных.

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

Ø Каждая строка списка преобразуется в запись данных.

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

2. Сохраните таблицу под именем Список.xls в вашей папке.

Добавление новой информации в список.

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

3. Добавьте к списку следующие данные:

Последовательность действий:

Ø Задайте для исходного списка (A1: F12) имя База_Данных;

Ø Выполните команду Данные – Форма, в результате чего появится диалоговое окно, заголовок которого совпадает с именем рабочего листа;

Ø Для добавления новой информации необходимо щелкнуть на кнопке Добавить. В правом верхнем углу появится надпись Новая запись, после чего можно начать ввод новой информации. Информация вводится построчно, после заполнения всех полей первой новой записи необходимо щелкнуть на кнопке Добавить, чтобы эта информация занеслась в таблицу, диалоговое окно очистится и можно переходить к вводу второй записи;

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

Ø Для проверки того, что вновь введенные строки тоже определяются именем База_Данных, надо открыть список в поле Имя (рядом со строкой формул); щелкнуть по имени База_Данных. Выделится область, охватывающая как старые, так и новые данные.

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

4. Расположите данные в списке в порядке возрастания цены книг. Порядок выполнения (используется панель инструментов Стандартная): сделайте текущей любую ячейку в колонке Цена; на панели инструментов Стандартная нажмите кнопку Сортировка по возрастанию.

5. Расположите данные в списке в порядке убывания количества книг. Порядок выполнения: сделайте текущей любую ячейку в колонке Количество; на панели инструментов Стандартная нажмите кнопку Сортировка по убыванию.

Сортировка данных по нескольким столбцам

6. Расположите данные в списке в порядке возрастания даты покупки книг, а для одинаковых дат – по алфавиту столбца «Тематика». Порядок выполнения:

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду меню Данные – Сортировка, в результате чего появится диалоговое окно для задания условий сортировки;

Ø в поле Сортировать по выберите из списка имя столбца Дата, условие сортировки – по возрастанию;

Ø в поле Затем выберите из списка имя столбца Тематика, условие сортировки – по возрастанию; нажмите кнопку ОК.

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

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

7. Расположить данные за 3-й квартал в порядке возрастания цены книг. Порядок выполнения (используется пункт меню Данные):

Ø выделите диапазон ячеек, необходимый для сортировки (A10: F13);

Ø выполните команду Данные – Сортировка;

Ø в поле Сортировать по выберите из списка имя столбца D (имя столбца, содержащего информацию о цене); нажмите кнопку ОК.

АНАЛИЗ СПИСКА С ПОМОЩЬЮ РЕЖИМА АВТОФИЛЬТР

8. Выведите для просмотра информацию, относящуюся к покупкам только за 3-й квартал, по тематике Проза. Порядок выполнения:

Ø сделайте текущей любую ячейку в области База_данных;

Ø выполните команду Данные – Фильтр – Автофильтр. Справа от каждого заголовка столбца появится кнопка со стрелкой вниз;

Ø раскройте список столбца Дата и выберите строчку 3 кв. В результате этих действий на экране останется только информация, относящаяся к 3 кварталу;

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

9. Отмените режим Автофильтр. Для этого выполните команду Данные – Фильтр – Автофильтр.

10. Установите возможность отбора информации по столбцу Название и отберите записи с названием книги Война и мир.

Порядок выполнения:

Ø выделите все данные в колонке Название (вариант выделения – сделать текущей ячейку С1, нажать комбинацию клавиш SHIFT + CTRL + стрелка вниз);

Ø выполнить команду Данные – Фильтр – Автофильтр;

Ø раскройте список столбца Название и выберите строчку Война и мир.

11. Отмените режим Автофильтр.

ИСПОЛЬЗОВАНИЕ ПОЛЬЗОВАТЕЛЬСКОГО АВТОФИЛЬТРА

12. Отберите информацию о книгах, цена которых больше или равна 30, но меньше 80 рублей. Порядок выполнения:

Ø выделите все данные в колонке Цена;

Ø выполните команду Данные – Фильтр – Автофильтр;

Ø раскройте список столбца Цена и выберите строчку (Условие…).

Ø в диалоговом окне Пользовательский Автофильтр в поле Цена выберите условие больше или равно;

Ø в правом верхнем поле выберите значение для условия (в нашем случае 30);

Ø поскольку в соответствии с заданием отбор должен удовлетворять обоим условиям, то необходимо установить опцию И;

Ø второе условие (меньше 80) вводится во второй набор полей;

Ø после задания всех условий нажмите кнопку ОК.

13. Отмените режим Автофильтр.

Ø 14. Отберите информацию о книгах, тематика которых начинается с буквы К. Порядок выполнения:

Ø выделите все данные в колонке Тематика;

Ø выполните команду Данные – Фильтр – Автофильтр;

Ø раскройте список столбца Тематика и выберите строчку (Условие…);

Ø в диалоговом окне Пользовательский Автофильтр в первом поле задайте логическое условие больше;

Ø в правом верхнем поле задайте значение для условия (в нашем случае буква К);

Ø установите опцию И;

Ø второе условие следует задать в виде меньше Л и ввести во второй набор полей;

Ø нажмите кнопку ОК.

15. Отмените режим Автофильтр.

16. Сохраните вашу таблицу.