Фильтры.
Фильтрация относится к процедурам анализа баз данных, т.е. позволяет из основной таблицы (базы данных) создавать вторичные таблицы, содержащие записи, удовлетворяющие заданным условиям. Чтобы войти в режим фильтрации в EXCEL, нужно выделить область заголовков, затем выбрать пункт меню ДАННЫЕ - ФИЛЬТР – АВТОФИЛЬТР. Следует иметь в виду, что если таблица имеет сложную многоуровневую шапку, то нужно выделять область нижних заголовков.
Инструмент АВТОФИЛЬТР позволяет фильтровать только по одному столбцу (полю). При необходимости фильтровать по нескольким столбцам, следует использовать расширенный фильтр. Но всё-таки для сложного анализа баз данных лучше использовать специальную программу Access.
Упражнение 8. «Аукцион».
Задание. Создать таблицу по образцу рис. 42.
Выполнить следующие фильтрации :
Создать фильтр, в котором отображался список только покупателей из Киева скопировать фильтр под основной таблицей. Дать заголовок новой таблице «Фильтр по городу Киев».
Создать фильтр, в котором отображался список всех покупателей, принявших участие в торгах до 01.07, скопировать этот фильтр под предыдущим. Дать имя таблице «Фильтр по дате».
Сделать сводку по продаже книг №3. Дать имя «Фильтр по книге №3».
Создать сводку клиентов, сделавших самые большие закупки (Сумма>15 000) .Скопировать и дать имя «Фильтр по сумме».
Оформить таблицу с помощью команды автоформат (стиль Объёмный 2).
Отсортировать таблицу по алфавиту городов, а, в свою очередь, каждый город по дате.
Создать условное форматирование столбца "Сумма" по принципу: если сумма>10000,то цвет шрифта красный, а цвет фона жёлтый.
Рис. 42
Чтобы задать режим фильтрации, нужно выделить заголовки таблицы (область А2:Н2) и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. В результате в заголовках появятся стрелочки с разворачивающимися списками (рис. 43). Следует иметь в виду, что если таблица имеет сложную шапку, то нужно выделять нижние заголовки.
Рис. 43
Чтобы выполнить фильтр по городу Киев, нужно развернуть список критериев в столбце «Город» и выбрать Киев. Скопировать новую таблицу ниже на листе под основной и дать имя «Фильтр по городу Киев».
Для выполнения фильтра по дате следует развернуть список критериев в столбце «Дата» и выбрать строку УСЛОВИЕ. Заполнить бланк условия в соответствии с рис. 44.
Рис. 44
Скопировать фильтр ниже по листу. Назвать таблицу «Фильтр по дате».
Фильтр по книге №3 выполнить аналогично фильтру по городу.
Фильтр по сумме выполнить с помощью бланка условия :
БОЛЬШЕ 15000
Скопировать фильтр ниже по листу. В результате получится список фильтров как на рис. 45.
Закончить фильтрацию. Выделить область А2:Н2 и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. При этом исчезнут галочки в шапке основной таблицы.
Выделить область А2:Н15 и пойти в пункт меню ФОРМАТ – АВТОФОРМАТ. Выбрать стиль «Объёмный 2».
Для выполнения сортировки выделить область А2:Н15 и выбрать пункт меню ДАННЫЕ – СОРТИРОВКА. Убедиться, что сортировка будет производиться по городу, а затем по дате. Так как выделена была вся таблица, перестроится не только столбец «Город» и «Дата», но и другие данные (рис. 46).
Для выполнения условного форматирования нужно выделить область Н2:Н15 и выбрать пункт меню ФОРМАТ – УСЛОВНОЕ ФОРМАТИРОВАНИЕ. Заполнить бланк условного форматирования, щёлкнув по кнопке Формат… . В разделе «Шрифт» задать красный цвет шрифта, а в разделе «Вид» заказать желтую заливку (рис. 47):
Рис. 45
Рис. 46
Рис. 47
Самостоятельная работа 10.
Задание. Составить таблицу в соответствии с табл. 7.
Создать фильтры (табл.8):
Всех товаров, поступивших после 01.04.
Всех товаров, имеющих цену прихода менее 20 р.
Товаров полностью реализованных, т.е. имеющих количество остатка 0.
Прибыльных товаров, имеющих прибыль более 200 р.
Отсортировать базу данных по алфавиту наименований товаров.
Создать условное форматирование столбца «Прибыль» таким образом, чтобы отрицательные значения выводились красным цветом, а товары , имеющие прибыль более 100 р., имели желтый фон.
Пояснения к выполнению.
Сумма расхода подсчитывается следующим образом:
(Цена расхода)*(Количество расхода)
Количество остатка подсчитывается как:
(Количество прихода)-(Количество расхода)
Формула для суммы остатка:
(Количество остатка)*(Цена расхода)
Прибыль считается как :
(Сумма расхода )-(Сумма прихода)
При выполнении условного форматирования применить два условия.
№
|
НАИМЕНОВАНИЕ ТОВАРА
|
ЕДИНИЦА ИЗМЕРЕНИЯ
|
Дата поступления товара
|
ПРИХОД
|
РАСХОД
|
ОСТАТОК
|
ПРИБЫЛЬ
|
ЦЕНА ПРИХОДА
|
КОЛ-ВО ПРИХОДА
|
Сумма прихода
|
ЦЕНА РАСХОДА
|
КОЛ-ВО РАСХОДА
|
Сумма расхода
|
КОЛ-ВО ОСТАТКА
|
СУММА ОСТАТКА
|
1
|
Зефир
|
упак.
|
10.янв
|
30 р.
|
15
|
450
|
34
|
15
|
510 р.
|
0
|
0
|
60
|
2
|
Молоко
|
упак.
|
01.апр
|
15 р.
|
50
|
750
|
21
|
43
|
903р.
|
7
|
147
|
153
|
3
|
Колбаса
|
кг
|
10.апр
|
140 р.
|
10
|
1 400
|
164
|
9
|
1 476 р.
|
1
|
164
|
76
|
4
|
Сосиски
|
кг
|
12.апр
|
80 р.
|
12
|
960
|
97
|
10
|
970 р.
|
2
|
194
|
10
|
5
|
Пепси-кола
|
бут.
|
01.фев
|
27 р.
|
32
|
864
|
34
|
25
|
850 р.
|
7
|
238
|
- 14
|
6
|
Сметана
|
упак.
|
12.апр
|
12,00р.
|
20
|
240
|
17,50
|
20
|
350 р.
|
0
|
0
|
110
|
7
|
Песок сахарный
|
кг
|
15.мар
|
15,45р.
|
50
|
773
|
22,00
|
45
|
990 р.
|
5
|
110
|
218
|
8
|
Сигареты "Прима"
|
упак.
|
25.мар
|
5,00р.
|
100
|
500
|
8,00р
|
90
|
720 р.
|
10
|
80
|
220
|
|
|
|
|
|
|
|
|
|
|
|
Итого
|
832,5
|
Таблица 7
Таблица 8
-
Фильтр по дате
|
|
|
|
|
|
|
|
|
|
|
№
|
НАИМЕНОВАНИЕ ТОВАРА
|
ЕДИНИЦА ИЗМЕРЕНИЯ
|
Дата поступ-ления товара
|
ЦЕНА ПРИХОДА
|
КОЛ-ВО ПРИХОДА
|
Сумма прихода
|
ЦЕНА РАСХОДА
|
КОЛ-ВО РАСХОДА
|
Сумма расхода
|
КОЛ-ВО ОСТАТКА
|
СУММА ОСТАТКА
|
ПРИБЫЛЬ
|
3
|
Колбаса
|
кг
|
10.апр
|
140р.
|
10
|
1 400
|
164
|
9
|
1 476р.
|
1
|
164
|
76,0
|
4
|
Сосиски
|
кг
|
12.апр
|
80р.
|
12
|
960
|
97
|
10
|
970р.
|
2
|
194
|
10,0
|
6
|
Сметана
|
упак.
|
12.апр
|
12,00р.
|
20
|
240
|
17,5
|
20
|
350р.
|
0
|
0
|
110,0
|
Дешёвые товары
|
|
|
|
|
|
|
|
|
|
|
2
|
Молоко
|
упак.
|
01.апр
|
15р.
|
50
|
750
|
21
|
43
|
903р.
|
7
|
147
|
153,0
|
6
|
Сметана
|
упак.
|
12.апр
|
12,00р.
|
20
|
240
|
17,5
|
20
|
350р.
|
0
|
0
|
110,0
|
7
|
Песок сахарный
|
кг
|
15.мар
|
15,45р.
|
50
|
773
|
22
|
45
|
990р.
|
5
|
110
|
218,0
|
8
|
Сигареты "Прима"
|
упак.
|
25.мар
|
5,00р.
|
100
|
500
|
8,00р
|
90
|
720р.
|
10
|
80
|
220
|
Проданные товары
|
|
|
|
|
|
|
|
|
|
|
1
|
Зефир
|
упак.
|
10.янв
|
30р.
|
15
|
450
|
34
|
15
|
510р.
|
0
|
0
|
60,0
|
6
|
Сметана
|
упак.
|
12.апр
|
12,00р.
|
20
|
240
|
17,5
|
20
|
350р.
|
0
|
0
|
110,0
|
Прибыльные товары
|
|
|
|
|
|
|
|
|
|
|
7
|
Песок сахарный
|
кг
|
15.мар
|
15,45р.
|
50
|
773
|
22
|
45
|
990р.
|
5
|
110
|
218,0
|
8
|
Сигареты "Прима"
|
упак.
|
25.мар
|
5,00р.
|
100
|
500
|
8,00р
|
90
|
720р.
|
10
|
80
|
220
|
|
