Работа со списком

БАЗА  ДАННЫХ

 

1. Работа со списком

 

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

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

 

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

 

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

 

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

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

Нажать в меню Данные - Создать список. В этом случае можно использовать свойство EXCEL автоматически включать новую информацию в состав области таблицы, которая определена указанным выше именем.

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

 

Data	Тематика	Название	Цена	Количество	Стоимость
1кв	Компьютеры	Win-98	60	100	6000
2кв	Экономика	Economic	100	50	5000
3кв	Проза	Ревизор	20	50	1000
4кв	Компьютеры	Win-98	60	75	4500

 

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

• Задайте для исходного списка (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. Сохраните вашу таблицу.

 

2. Расширенный фильтр.

Промежуточные итоги

1. Откройте таблицу, созданную в работе 1 (CnucoK.xls).

СОЗДАНИЕ ИНТЕРВАЛА КРИТЕРИЕВ

2. Расположите интервал критериев, начиная с ячейки А19. Для этого: выделите диапазон с именами столбцов (A1:F1); скопируйте выделенный диапазон в ячейки А19:Е19.
3. Отберите информацию о книгах, которые закуплены или в 1-ом квартале или имеют тематику Экономика. Порядок выполнения:

► в ячейку А20 занесите значение 1 кв, в ячейку В21 занесите значение Экономика (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ);

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

► выполните команду Данные — Фильтр — Расширенный фильтр;

►  в диалоговом окне Расширенный фильтр поле Исходный диапазон будет уже заполнено;

►в поле Диапазон условий либо наберите вручную (ввод осуществляется с использованием абсолютных адресов ячеек ), либо выделите с помощью мыши диапазон A19:F21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

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

4 Отмените действие фильтра. Для этого выполните команду Данные — Фильтр — Отобразить все.

5. Отберите информацию о книгах, которые закуплены в 1-м квартале и имеют тематику Экономика. Порядок выполнения:

► переместите значение Экономика из ячейки В21 в ячейку В20 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И);

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

► выполните команду Данные — Фильтр — Расширенный фильтр;

►• выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

6. Отберите информацию о книгах, которые закуплены в 1, 2 и 3-м кварталах. Порядок выполнения:

► очистите ячейку В20 (т.к. по столбцу Тематика условий отбора нет), в ячейки А21 и А22 занесите значения 2 кв и 3 кв соответственно;

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

► выполните команду Данные — Фильтр — Расширенный фильтр;

►- выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$22 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

7. Отберите информацию о книгах, которые закуплены в 1-м квартале по тематике Экономика, а во 2-м квартале по тематике Компьютеры. Порядок выполнения:

► очистите ячейку А22, в ячейки В20 и В21 занесите значения Экономика и Компьютеры соответственно;

Первая строка определяет условие отбора записей покупок книг в 1-м квартале по тематике Экономика, а вторая — во 2-м квартале по тематике Компьютеры. Условия в строчках объединены правилом И. Между собой строки объединены правилом ИЛИ;

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

► выполните команду Данные — Фильтр — Расширенный фильтр;

► выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК

 

ПРИМЕНЕНИЕ ТЕКСТОВЫХ КРИТЕРИЕВ

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

►- очистите диапазон ячеек А20:В21, в ячейку С20 занесите условие <А (русская буква), т.е. отберите все названия, которые начинаются с любой латинской буквы;

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

►- выполните команду Данные — Фильтр — Расширенный фильтр;

►• выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

2. Отмените действие фильтра.

ПРИМЕНЕНИЕ ВЫЧИСЛЯЕМЫХ КРИТЕРИЕВ

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

►- в ячейку С25 введите формулу определения средней цены всех книг: —СРЗНАЧ(D2:D16), в ячейку В25 введите вычисляемый критерий: =D2>$C$25, где D2 — адрес первой ячейки, содержащей цену (адрес этой ячейки должен быть относительным);

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

►• выполните команду Данные — Фильтр — Расширенный фильтр;

► выберите поле Диапазон условий, очистите его и введите в него диапазон $В$24:$В$25 (в диапазон ячеек, который определяет интервал критериев, обязательно включается ячейка над формулой), нажмите кнопку ОК.

 

КОПИРОВАНИЕ ВЫБРАННОЙ ИНФОРМАЦИИ В ДРУГОЕ МЕСТО РАБОЧЕГО ЛИСТА

БЕЗ ИЗМЕНЕНИЯ КОЛИЧЕСТВА СТОЛБЦОВ

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

►- очистите ячейку С20, в ячейку В20 введите слово Компьютеры;