Занятие 11 (2 ч., лабораторное)

    Тема: Фильтрация данных в Excel.

 

    Фильтрацию данных в Excel можно проводить с помощью автофильтра или расширенного фильтра. Самым простым видом фильтрации является автофильтр.

 

Постановка задачи

    Из списка всех сотрудников предприятия необходимо произвести отбор тех сотрудников, которые отвечают следующим критериям:

1) фамилии начинаются с букв А или Б или В;

2) имеют стаж работы от 5 до 15 лет включительно;

3) являются членами профсоюза.

 

Ход работы

1. Создайте следующую таблицу на Листе1 новой рабочей книги Excel:

 

	A	B	C	D	E
1	№ п/п	Фамилия И.О.	Должность	Стаж	Член профсоюза
2	1	Гаранина О.И.	Директор	15	Да
3	2	Косиненко Н.С.	Зам. директора	11	Да
4	3	Козлов М.В.	Зам. директора	7	Да
5	4	Матросова В.К.	Гл. инженер	16	Да
6	5	Нугуманов Н.Ю.	Иженер-конструктор	4	Нет
7	6	Одинцов Л.И.	Иженер-конструктор	7	Нет
8	7	Воронин С.Л.	Иженер-конструктор	10	Да
9	8	Губарева М.В.	Иженер-конструктор	8	Нет
10	9	Степанов М.Ф.	Гл. бухгалтер	10	Нет
11	10	Федорова А.Л.	Бухгалтер	3	Да
12	11	Ершов К.Б.	Бухгалтер	7	Да
13	12	Волчек Г.Н.	Бухгалтер	9	Да
14	13	Романов Н.П.	Экономист	2	Нет
15	14	Пугачев А.В.	Экономист	12	Нет
16	15	Волк М.Д.	Сметчик	6	Да
17	16	Борисова В.П.	Делопроизводитель	3	Нет
18	17	Воронин С.Л.	Делопроизводитель	7	Да
19	18	Носков Р.А.	Референт	9	Да
20	19	Орлов В.Д.	Секретарь	1	Нет
21	20	Сомов М.Ю.	Секретарь	5	Нет
22	21	Точилин Н.А.	Секретарь	6	Да
23	22	Ушакова А.Д.	Гл. энергетик	8	Да
24	23	Золотухин Н.Б.	Энергетик	21	Нет
25	24	Федотов М.К.	Энергетик	16	Нет
26	25	Воробей Е.Н.	Гл. механик	8	Да
27	26	Иванов И.Л.	Механик	16	Нет
28	27	Балык Б.Д.	Механик	12	Да
29	28	Губарева М.В.	Нач. цеха	15	Да
30	29	Леонидова Л.Г.	Нач. цеха	5	Да
31	30	Родионов К.И.	Нач. цеха	17	Да
32	31	Петрова В.Э.	Зам. нач. цеха	9	Нет
33	32	Кочубей И.Г.	Зам. нач. цеха	7	Нет
34	33	Галкин С.Л.	Зам. нач. цеха	20	Да
35	34	Громов К.Н.	Инженер	5	Да
36	35	Мазуров А.З.	Инженер	7	Да
37	36	Соловьев В.В.	Инженер	4	Да
38	37	Мамонтов Д.В.	Рабочий	6	Нет
39	38	Клишин Е.Д.	Рабочий	8	Нет
40	39	Аринцев М.Ю.	Рабочий	7	Да
41	40	Бердников П.Д.	Рабочий	8	Да
42	41	Корнева А.А.	Рабочий	7	Да
43	41	Тупикова В.Р.	Рабочий	9	Да
44	43	Третьяк С.С.	Рабочий	1	Нет
45	44	Минский К.С.	Рабочий	5	Нет
46	45	Бердников А.Д.	Рабочий	6	Да
47	46	Соколов К.Н.	Рабочий	8	Да
48	47	Юрский Н.Е.	Рабочий	21	Да
49	48	Шолохов К.Н.	Рабочий	16	Да
50	49	Мишенин Д.В.	Рабочий	8	Нет
51	50	Сергеев Н.Т.	Рабочий	16	Нет

 

2. Присвойте Листу1 текущей рабочей книги имя Список: Фор м ат è Л ист è Пере и меновать.

3. Удалите все другие листы из текущей рабочей книги.

    Для этого:

· перейдите на удаляемый лист;

· для удаления листа используйте команду П равка è Удалить л ист.

4. Сохраните текущую рабочую книгу под именем lesson 7. xls: Ф айл è Сохранить к ак…

5. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Список в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Список;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

6. Присвойте созданному листу Список (2) текущей рабочей книги имя Автофильтр: Фор м ат è Л ист è Пере и меновать.

7. Сохраните текущую рабочую книгу: Ф айл è С охранить.

8. На листе Автофильтр примените автофильтр к списку.

    Для этого:

· выделите первую строку таблицы с заголовками столбцов;

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

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

9. Проведите фильтрацию данных по фамилии.

    Для этого:

· в ячейке Фамилия И.О. нажмите на кнопку установки условий и из раскрывающегося списка выберите пункт (Условие…). Отобразится диалоговое окно Пользовательский автофильтр;

· в диалоговом окне Пользовательский автофильтр задайте критерии отбора, как показано на рис. 11.1, и нажмите на кнопке OK.

 

 

 

    Рис. 11.1 Диалоговое окно Пользовательский автофильтр.

 

    Согласно критериям, установленным на рис. 11.1, из списка всех сотрудников предприятия будут отобраны те сотрудники, фамилии которых начинаются с букв А или Б.

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

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

10. Проведите фильтрацию данных по стажу работы.

    Для этого:

· в ячейке Стаж нажмите на кнопку установки условий и из раскрывающегося списка выберите пункт (Условие…). Отобразится диалоговое окно Пользовательский автофильтр;

· в диалоговом окне Пользовательский автофильтр задайте критерии отбора, как показано на рис. 11.2, и нажмите на кнопке OK.

 

 

    Рис. 11.2 Диалоговое окно Пользовательский автофильтр.

 

    Согласно критериям, установленным на рис. 11.2, из списка сотрудников предприятия будут отобраны те сотрудники, которые имеют стаж работы от 5 до 15 лет включительно.

11. Проведите отбор членов профсоюза.

    Для этого:

· в ячейке Член профсоюза нажмите на кнопку установки условий и из раскрывающегося списка выберите пункт (Условие…). Отобразится диалоговое окно Пользовательский автофильтр;

· в диалоговом окне Пользовательский автофильтр задайте критерии отбора, как показано на рис. 11.3, и нажмите на кнопке OK.

 

 

    Рис. 11.3 Диалоговое окно Пользовательский автофильтр.

 

    Согласно критериям, установленным на рис. 11.3, из списка сотрудников предприятия будут отобраны те сотрудники, которые являются членами профсоюза.

12. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Список в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Список;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

13. Присвойте созданному листу Список (2) текущей рабочей книги имя Расширенный фильтр (1): Фор м ат è Л ист è Пере и меновать.

14. Сохраните текущую рабочую книгу: Ф айл è С охранить.

15. На листе Расширенный фильтр (1) примените расширенный фильтр к списку.

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

15.1. Создайте следующую таблицу критериев для расширенного фильтра в ячейках G1:J4:

 

G	H	I	J
Фамилия И.О.	Стаж	Стаж	Член профсоюза
А*	>=5	<=15	Да
Б*	>=5	<=15	Да
В*	>=5	<=15	Да

 

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

1) фамилии начинаются с букв А или Б или В;

2) имеют стаж работы от 5 до 15 лет включительно;

3) являются членами профсоюза.

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

15.2. Выберите команду Д анные è Ф ильтр è Р асширенный фильтр… Отобразится диалоговое окно Расширенный фильтр, в котором задайте параметры фильтрации, как показано на рис. 11.4, и нажмите на кнопке OK.

 

 

    Рис. 11.4 Диалоговое окно Расширенный фильтр.

 

16. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Список в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Список;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

17. Присвойте созданному листу Список (2) текущей рабочей книги имя Расширенный фильтр (2): Фор м ат è Л ист è Пере и меновать.

18. Сохраните текущую рабочую книгу: Ф айл è С охранить.

19. На листе Расширенный фильтр (2) примените расширенный фильтр к списку.

19.1. Создайте следующую таблицу критериев для расширенного фильтра в ячейках G1:I4:

 

G	H	I
Фамилия И.О.
А*
Б*
В*

 

19.2. В ячейках H2:H4 введите формулу:

 

    = И(D2>=5;D2<=15).

 

19.3. В ячейках I2:I4 введите формулу:

 

    = E2="Да".

 

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

1) фамилии начинаются с букв А или Б или В;

2) имеют стаж работы от 5 до 15 лет включительно;

3) являются членами профсоюза.

    Обратите внимание на то, что при использовании в таблице критериев формул, адресация ячеек должна быть относительной (без знаков $), а имена столбцов должны отсутствовать, так как ссылка на столбцы исходного списка (базы данных), в которых следует искать значения, удовлетворяющие критериям, содержатся в формулах. В нашем случае D2 – первая ячейка столбца, содержащего сведения о стаже работы, а E2 – первая ячейка столбца, содержащего сведения о членстве в профсоюзе.

19.4. Выберите команду Д анные è Ф ильтр è Р асширенный фильтр… Отобразится диалоговое окно Расширенный фильтр, в котором задайте параметры фильтрации, как показано на рис. 11.5, и нажмите на кнопке OK.

 

 

 

    Рис. 11.5 Диалоговое окно Расширенный фильтр.

 

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

· не менее двух строк;

· первую строку – с именами столбцов, строго идентичными именам столбцов исходного списка (базы данных), если критерии не содержат имен ячеек, и – пустую, если критерии содержат имена ячеек.

20. Сохраните текущую рабочую книгу: Ф айл è С охранить.

 

Варианты заданий

    Из списка всех сотрудников предприятия необходимо произвести отбор тех сотрудников, которые отвечают следующим критериям:

1) фамилии начинаются с букв М или К;

2) имеют стаж работы от 5 до 10 лет включительно;

3) не являются членами профсоюза.