Фильтрация данных с помощью Автофильтра и Расширенного фильтра в Exel (Практические задания на контрольную работу)

Задание 3.

1.  Выполнить сортировку данных

·  по товару, области, городу, возрастанию цены.

2.  Выбрать данные о продажах с помощью Автофильтра

·  товаров в магазине 5 Гомеля с ценами меньше 1500 и больше 7000.

3.  Выбрать данные о продажах  с помощью Расширенного фильтра

·  в Минске  с ценой больше 1000 или с количеством больше 10000.

4.  Определить, используя подведение итогов

·  общее количество проданного за каждую дату по каждому товару;

Вывести только итоговые данные.

5.  Определить, используя сводные таблицы

·  минимальные цены по каждому товару в каждом городе;

Создание списка.

• создать новый документ (рабочую книгу);
• переименовать Лист 1 в Список;
• скопировать данные из файла Продажи.xls

Работа со списками выполняется с помощью меню Данные.

1.  Сортировка данных

§  скопировать список на лист 2;

§  переименовать лист 2  в Сортировка;

Выполнение:

§  по трем ключам:

-  выделить любую ячейку списка;

-  выполнить команду Данные – Сортировка, появится диалоговое окно (ДО) Сортировка диапазона;

-  в ДО указать ключи и порядок сортировки (выбрать из списка полей):

        Сортировать по                 Товар                        по возрастанию

        Затем по:                             Область                    по возрастанию

 В последнюю очередь по      Город                        по возрастанию

Область	Город	N магазина	Дата	Товар	Цена	Количество	Стоимость
Брестская	Брест	2	15.06.03	колбаса	6200	3400	4488000
Брестская	Брест	2	16.06.03	колбаса	4780	3500	4550000
Брестская	Брест	5	16.06.03	колбаса	6100	5200	6656000
Брестская	Брест	5	14.06.03	колбаса	5690	5200	6656000
Брестская	Брест	5	15.06.03	колбаса	3700	5200	6656000
Брестская	Брест	1	16.06.03	колбаса	6300	5200	6760000
Брестская	Брест	2	14.06.03	колбаса	6300	5200	6760000
Брестская	Брест	1	15.06.03	колбаса	6200	5200	6760000
Брестская	Брест	1	14.06.03	колбаса	5600	5200	6760000
Брестская	Брест	2	13.06.03	колбаса	5600	5200	7800000
Брестская	Брест	1	13.06.03	колбаса	4500	7600	10640000
Брестская	Брест	5	13.06.03	колбаса	4780	12000	18000000

2.  Фильтрация данных с помощью Автофильтра

§  скопировать список на лист 3;

§  переименовать лист 3 в Автофильтр;

§  Выбрать данные о продажах товаров в магазине 5 Гомеля с ценами меньше 1500 и больше 7000

Выполнение:           

-  выделить любую ячейку списка;

-  выполнить команду Данные - Фильтр – Автофильтр. Ячейки с названиями полей превращаются в раскрывающиеся списки;

-  щелкнуть по кнопке раскрытия списка в поле Товар, щелкнуть по названию все

-  раскрыть список в поле Город, выбрать Условие, появится ДО Пользовательский автофильтр. Выбрать из списков: равно  Гомель

-  раскрыть список в поле № магазина, выбрать Условие, в ДО Пользовательский автофильтр указать: равно 5

-  раскрыть список в поле цена, выбрать Условие, появиться ДО Пользовательский автофильтр указать: меньше 1500 или больше 7000

§  если нужно  снять Автофильтр

-  выделить любую ячейку списка;

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

Область	Город	N магазина	Дата	Товар	Цена	Количество	Стоимость
Гомельская	Гомель	5	15.06.03	сахар	1280	7600	9728000
Гомельская	Гомель	5	16.06.03	сахар	1280	7600	9728000
Гомельская	Гомель	5	13.06.03	сахар	1320	7600	10032000
Гомельская	Гомель	5	14.06.03	сахар	1320	7600	10032000
Гомельская	Гомель	5	13.06.03	конфеты	7800	7600	59280000

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

§  скопировать список на лист 4;

§  переименовать лист 4 в Расширенный фильтр;

§  В  Минске  с ценой больше 1000 или с количеством больше 10000.

Выполнение:

-  создать в свободной области Рабочего листа дополнительную таблицу (диапазон условий), заголовки которой скопировать из заголовков исходного списка;

Город	Цена	Количество
Минск	>=1000	>=10000

-  выделить любую ячейку списка;

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

-  заполнить поля, выделяя на Рабочем листе:

Исходный диапазон        выделить исходный список

Диапазон условий          выделить дополнительную таблицу

-      Обработка                      фильтровать список на месте, нажать ОК;

-  если нужно  вывести все записи списка на экран выполнить команду Данные - Фильтр – Отобразить все.    

Область	Город	N магазина	Дата	Товар	Цена	Количество	Стоимость
Минская	Минск	3	16.06.03	макароны	1000	15000	15000000
Минская	Минск	1	15.06.03	колбаса	5600	12000	15600000
Минская	Минск	1	16.06.03	колбаса	4900	12000	15600000
Минская	Минск	1	15.06.03	сахар	1300	12000	15600000
Минская	Минск	1	16.06.03	сахар	1300	12000	15600000
Минская	Минск	3	13.06.03	макароны	1240	15000	18600000
Минская	Минск	3	14.06.03	макароны	1500	15000	22500000
Минская	Минск	3	15.06.03	макароны	1500	15000	22500000
Минская	Минск	1	13.06.03	сок	2100	12000	25200000
Минская	Минск	12	13.06.03	печенье	2600	15000	39000000
Минская	Минск	12	15.06.03	печенье	2800	15000	42000000
Минская	Минск	12	16.06.03	печенье	2800	15000	42000000
Минская	Минск	3	13.06.03	конфеты	3550	12000	42600000
Минская	Минск	12	15.06.03	конфеты	3500	15000	52500000
Минская	Минск	12	16.06.03	конфеты	3500	15000	52500000
Минская	Минск	12	14.06.03	конфеты	3550	15000	53250000
Минская	Минск	13	15.06.03	печенье	4000	15000	60000000
Минская	Минск	13	16.06.03	печенье	4000	15000	60000000
Минская	Минск	12	14.06.03	печенье	3200	20000	64000000
Минская	Минск	12	13.06.03	конфеты	4300	15000	64500000
Минская	Минск	3	14.06.03	конфеты	8000	15000	120000000
Минская	Минск	3	15.06.03	конфеты	8000	15000	120000000
Минская	Минск	3	16.06.03	конфеты	8000	15000	120000000

 4. Определить, используя подведение итогов:

·  общее количество проданного за каждую дату по каждому товару;

Вывести только итоговые данные.

§  скопировать список на лист 4;

§  переименовать лист 4 в Итоги;

§  определить  общее количество проданного за каждую дату по каждому товару;

Выполнение:

-  выделить любую ячейку списка;

-  выполнить сортировку списка по полям Город, Товар (см. п.2);

-  выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

в ДО указать:

При каждом изменении в                  Дата

Операция                                             Сумма

Добавить итоги по                            Количество щелчок по кнопке  ОК

Появиться общее количество проданного за каждую дату.

-  выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

в ДО указать:

При каждом изменении в                Товар

Операция                                           Сумма

Добавить итоги по                          Количество

Убрать флажок в поле                  Заменить текущие итоги

щелчок по кнопке  ОК

Появиться общее количество проданного за каждую дату по каждому товару

Область	Город	N магазина	Дата	Товар	Цена	Количество	Стоимость
				колбаса Итог		309970
				конфеты Итог		346200
				макароны Итог		171400
				молоко Итог		647270
				мука Итог		646270
				печенье Итог		110570
				рис Итог		72000
				сахар Итог		309970
				сок Итог		299600
			13.06.03 Итог			2913250
				колбаса Итог		269740
				конфеты Итог		355600
				макароны Итог		171400
				молоко Итог		3487070
				мука Итог		637320
				печенье Итог		113800
				рис Итог		72000
				сахар Итог		287740
				сок Итог		189300
			14.06.03 Итог			5583970
				колбаса Итог		269870
				конфеты Итог		355600
				макароны Итог		171400
				молоко Итог		581670
				мука Итог		586870
				печенье Итог		130800
				рис Итог		72000
				сахар Итог		269870
				сок Итог		195840
			15.06.03 Итог			2633920
				колбаса Итог		288710
				конфеты Итог		355600
				макароны Итог		171900
				молоко Итог		587100
				мука Итог		583700
				печенье Итог		132300
				рис Итог		72100
				сахар Итог		289510
				сок Итог		207540
			16.06.03 Итог			2688460
				Общий итог		13819600
			Общий итог			13819600

вывести только итоговые строки:

слева от строки заголовков списка на служебном поле щелкнуть по кнопке с номером 3;

§  если нужно  убрать все итоги:

-  выделить любую ячейку списка;

-  выполнить команду Данные – Итоги,

-  в ДО щелкнуть по кнопке Убрать все.

5. Определить, используя сводные таблицы

·  минимальные цены по каждому товару в каждом городе;

• скопировать список на лист 4;
• переименовать лист 4 в Сводные;
• определить минимальную цену каждого товара в каждом городе