С ортировка и ф и л ь т рация.

 

Цель работы:

 

1. Работа со структурой электронной таблицы.

 

2. Ознакомление с методами сортировки и фильтрации данных.

 

3. Использование функций просмотра для поиска информации.

 

Возможности Excel позволяют не просто обрабатывать колонки и

 

строки цифр, а различным образом сортировать и систематизировать дан-

 

ные, получать промежуточные итоги, делать выборку нужных данных из

 

одной таблицы и формировать новые таблицы на основе исходной. Для

 

этой цели необходима система управления данными (база данных). Как ба-

 

за данных в Excel используется список.

 

Список – это специальный вид таблицы, содержащей связанные

 

данные). Как правило, список состоит из записей (строк) и полей (столб-

 

цов). Столбцы должны содержать однотипные данные. Представление

 

данных в виде списка обеспечивает большее удобство при сортировках,

 

выборках, подведении итогов и т. п. С другой стороны, в этом случае за-

 

труднено построение диаграмм, снижается наглядность представления

 

данных на листе (рис.8.1.).

 

В виде списка можно представлять как данные информационного

 

характера (номера телефонов, адреса и т. п.), так и данные, подлежащие

 

вычислениям.

 

При создании списков следует придерживаться следующих правил:

 

на одном рабочем листе следует располагать один список;

 

список отделяется от других данных, по крайней мере, одной пустой

 

строкой и одним пустым столбцом;

 

заголовки должны быть отформатированы другим образом, нежели

 

остальные элементы списка;

 

100

 

 

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

 

каждый столбец списка должен содержать однотипные данные;

 

первая строка таблицы должна содержать имена полей списка, опи-

 

сывающих назначение соответствующего столбца;

 

заголовки должны быть отформатированы другим образом, нежели

 

остальные элементы списка.

 

 

Рис. 8.1. Организация данных в виде списка.

 

Сортировка и фильтрация осуществляются выделением заголовка

 

таблицы и нажатием кнопки, которая находится в группе "Сортировка и

 

фильтр" на вкладке "Данные", или кнопки "Сортировка и фильтр" на

 

вкладке "Главная".

 

 

После этого рядом с названиями столбцов появятся стрелочки. Что-

 

бы задать условие сортировки или фильтрации, следует щелкнуть на стре-

 

лочке рядом с названием нужного столбца и выбрать нужный вариант. Ес-

 

 

101

 

 

ли требуемого варианта среди имеющихся не окажется, выберите пользо-

 

вательскую настройку фильтра (вариант "Текстовые фильтры" или "Чи-

 

словые фильтры"). Чтобы увидеть всю таблицу (отменить действие фильт-

 

ра), на вкладке "Данные" в группе "Сортировка и фильтр" нажмите кнопку

 

"Очистить".

 

Кнопки, предназначенные для создания групп и структур, в Excel

 

2007 располагаются в группе "Структура" на вкладке "Данные".

 

Пример1. Сортировка с п и с к о в.

 

1. Сделайте небольшой список для тренировки.

 

 

2. Выделите его и нажмите кнопку " Сортировка и фильтр" на панели

 

"Редактирование" ленты "Главная".

 

3. Выберите " С ортировка от А д о Я ". Список будет отсортирован по

 

первому столбцу, т.е. по полю ФИО

 

4. Отсортировать список по нескольким полям, то для этого предназна-

 

чен пункт " Настраиваемая с ортировка".

 

102

 

Сложная сортировка подразумевает упорядочение данных по нескольким

 

полям. Добавлять поля можно при помощи кнопки " Д обавить у ровень".

 

В итоге список будет отсортирован, согласно установленным параметрам

 

сложной сортировки.

 

 

103

 

 

Если надо отсортировать поле нестандартным способом, то для этого

 

предназначен пункт меню " Настраиваемый с п исок" выпадающего списка

 

" Порядок". Перемещать уровни сортировки можно при помощи кнопок

 

" В верх" и " В низ". Из контекстного меню также можно настроить сортиров-

 

ку списка.

 

Пример 2. Фильтрация с п и с к о в.

 

Основное отличие фильтра от упорядочивания - это то, что во время

 

фильтрации записи, не удовлетворяющие условиям отбора, временно

 

скрываются (но не удаляются), в то время, как при сортировке показыва-

 

ются все записи списка, меняется лишь их порядок.

 

Фильтры бывают двух типов: о бычный фильтр (его еще называют авто-

 

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

 

 

104

 

 

Для применения автофильтра нажмите ту же кнопку, что и при сортировке

 

" С ортировка и фильтр" и выберите пункт " Фильтр" (перед этим должен

 

быть выделен диапазон ячеек).

 

В столбцах списка появятся кнопки со стрелочками, нажав на которые

 

можно настроить параметры фильтра.

 

 

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

 

Если подвести указатель мыши к такой воронке, то будет показано условие

 

фильтрации.

 

105

 

 

Для формирования более сложных условий отбора предназначен

 

пункт "Текстовые фильтры" или "Числовые фильтры". В окне "Пользо-

 

вательский автофильтр" необходимо настроить окончательные условия

 

фильтрации.

 

При использовании расширенного фильтра критерии отбора задаются

 

на рабочем листе.

 

Для этого надо сделать следующее:

 

1). скопируйте и вставьте на свободное место шапку списка;

 

2). в соответствующем поле (полях) задайте критерии фильтрации;

 

3). выделите основной список;

 

 

106

 

 

4). нажмите кнопку " Фильтр" на панели "Сортировка и фильтр"

 

ленты "Данные";

 

5). На той же панели нажмите кнопку " Дополнительно".

 

 

В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек.

 

 

107

 

 

В результате отфильтрованные данные появятся в новом списке.

 

 

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

 

тат отбора желательно поместить отдельно от основного списка.

 

З адание 1.

 

1. Создать базу данных сотрудников фирмы.

 

При создании списка сначала наберите заголовки заданных столбцов,

 

добавьте новые столбцы «Оклад», «Премия», «Всего».

 

Введите записи и задайте формулы для вычисляемых ячеек (столбцы

 

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

 

К% (положить сначала К=10%).

 

108

 

 

№ Ф амилия Имя          Отчество 1.  Болото    Сергей    Петрович

 

2. Бруш       Басса      Юрьевна 3.  Бурмист  Семен     Карлович 4.  Вязена     Ирина     Ленидовна 5.  Друзич    Ирина     Даниловна

6. Иванов    Дмитрий Валерьевич

 

7. Иванов    Олег       Савельевич 8.  Иванов    Петр       Борисович 9.  Иванова  Ирина     Семеновна 10. Иртуш     Эмма      Карловна 11. Конева    Кристина Олеговна

 

12. Марков   Степан   Иванович

 

13. Марков   Валерий Сергеевич 14. Марков   Евгений  Петрович

 

15. Марков   Иван       Юрьевич

Те л.   Улица –   Ленина

607332 Мира 345070 Мира 220000 Мира 222222 Мира

–  Гоголя 455007 Мира 505011 Мира 228707 Попова 666073 Ленина

–  Мира –   Чехова

828107 Горького –   Мира

–  Чехова

Д ом Кв. Должность 87   11  эксперт

 

87  8   специалист 165  73  специалист 68   52  инспектор 256  3   гл. спец. 165  41  эксперт

 

26  54 экономист 11   96  бухгалтер 44   17  инженер 43   85  системщик

165 44 гл. бухгалтер

 

4    78 программист

 

25  39 руководитель 165  80  менеджер

 

22  27 охранник

 

2. Осуществить поиск записей

 

1). Найти сотрудников, фамилии которых заканчиваются на «ов».

 

2). Найти в списке сотрудников, проживающих на улице Мира.

 

3). Найти сотрудников, проживающих на улице Мира, в доме №165.

 

3. С помощью фильтрации:

 

1). Извлечь список Фамилий, Имен, Отчеств и Телефонов сотрудников,

 

у которых фамилия начинается на «И» и телефон содержит послед-

 

ние цифры «07».

 

2). Извлечь список Ф.И.О. сотрудников, проживающих на улице Мира в

 

доме №165.

 

3). Извлечь список Фамилий и адресов сотрудников по имени Ирина с

 

телефоном, содержащим начальные цифры 22 и последнюю 0.

 

4). Извлечь все данные для сотрудников, фамилии которых начинаются

 

на «Б», отчество Владимирович (-вна), и проживающих по улице Ми-

 

ра, имеющих оклад от 5000 до 10000 руб.

 

5). Извлечь список Ф.И.О., нетелефонизированных сотрудников.

 

4. Отсортировать данные: а) по алфавиту улиц; б) «по алфавиту фамилий»

 

и «по убыванию № телефонов».

 

 

109

 

 

З адание 2.

 

Создать таблицу реализации печатной продукции с заголовками

 

столбцов: Месяц, Название, Тип издания (газета, журнал и т.д.), Цена од-

 

ного экземпляра, Кол-во проданных экземпляров, Сумма от реализации.

 

1).Ввести информацию для двух месяцев (например, для января, февра-

 

ля) и трех типов изданий.

 

2).Используя команду автоформат, оформить таблицу в удобном для

 

пользователя виде.

 

3).Отсортировать данные в алфавитном порядке по Типу издания и од-

 

новременно по Кол-ву проданных экземпляров в порядке возраста-

 

ния.

 

4).Используя автофильтр, показать только те издания, у которых коли-

 

чество проданных экземпляров меньше 10 (т.е. не пользующихся

 

спросом).

 

5).Используя расширенный фильтр, показать только те издания, у кото-

 

рых Цена экземпляра больше или равна 6 р. Результат скопировать в

 

другой диапазон.

 

З адание 3. Используя следующую таблицу:

 

Поступление товаров на склад магазина "Ирикон"

 

 

 

Наименование товара

Дата поступле-ния

 

Кол-во

Стоимость Стоимость единицы  партии

 

Телевизор CS-21 K9 MJQ Фотоаппарат A 400 Видеомагнитофон NV-MV6 Телевизор CS-21 K9 MJQ DVD-комбо SV-DVD 240 Фотоаппарат A 520 Видеомагнитофон NV-MV6 Телевизор CS-21 A11 MQQ DVD-комбо SV-DVD 440 Видеомагнитофон NV-MV6 DVD-комбо SV-DVD 546 Фотоаппарат S 60 Телевизор CS-21 K9 MJQ

10.09.2010        50 12.09.2010        45 12.09.2010        20 15.05.2010        10 15.09.2010        25 15.09.2010        35 25.09.2010        26 28.09.2010        30 12.10.2010        50 14.10.2010        15 14.10.2010        25 14.10.2010        40 17.10.2010        15

7 290р. 5 990р. 2 990р. 7 490р. 5 290р. 8 390р. 4 590р. 8 490р. 6 490р. 2 990р. 6 990р. 16 590р. 7 290р.

364 500р. 269 500р. 59 800р. 74 900р. 132 250р. 293 650р. 119 340р. 254 700р. 324 500р. 44 850р. 174 750р. 663 600р. 3 09 350р.

 

110

 

 

DVD-комбо SV-DVD 645 DVD-комбо SV-DVD 240 Телевизор CS-21 M6 MQQ Фотоаппарат S 70 DVD-комбо SV-DVD 645 Видеомагнитофон NV-MV6 Телевизор CS-21 K9 MJQ

 

Фотоаппарат A 95 DVD-комбо SV-DVD 546 Телевизор CS-21 K9 MJQ DVD-комбо SV-DVD 440 Фотоаппарат S 70 Видеомагнитофон NV-MV6

Телевизор CS-21 K9 MJQ Телевизор CS-21 M6 MQQ

 

Фотоаппарат A 95

20.10.2010        35 20.10.2010        10 20.10.2010        25 24.10.2010        15 24.10.2010        45 24.10.2010        20 24.10.2010        10

 

27.10.2010        35 27.10.2010        40 09.11.2010        15 09.11.2010        25 09.11.2010        30 05.11.2010        20

05.11.2010        10 10.11.2010        15

 

10.11.2010        40

7 390р. 5 290р. 8 990р. 18 990р. 7 390р. 4 590р. 7 290р.

 

13 290р. 6 990р. 7 490р. 6 490р. 18 990р. 4 590р.

7 290р. 8 990р.

 

13 290р.

258 650р. 52 900р. 224 750р. 284 850р. 332 550р. 91 800р. 72 900р.

 

466 150р. 279 600р. 112 350р. 162 250р. 569 700р. 91 800р.

72 900р. 134 850р.

 

531 600р.

 

1. Отобрать из базы данных магазина товары, стоимость партии кото-

 

рых находится в пределах от 200 000 до 600 000 руб. включительно.

 

2. Отобрать из базы данных товары двух типов: Телевизор CS-21 K2

 

MJQ, Телевизор CS-21 M6 MQQ.

 

3. Отобрать из базы данных магазина товары, которые поступили на

 

склад в количестве 30 штук, и стоимость партии которых была меньше

 

600 000 руб.

 

4. Отобрать товар: Телевизор CS-21 K2 MJQ, или товары, поступив-

 

шие на склад магазина в количестве менее 20 штук.

 

5. Отобрать из базы данных магазина следующие товары, DVD-комбо

 

SV-DVD 645, стоимость которых менее 8000 р., фотоаппарат S 70, стоимо-

 

стью более 18 000 р. и Телевизор CS-21 K2 MJQ стоимостью менее 7000 р.

 

6. Отобрать из базы данных товар, стоимость партии которого мень-

 

ше среднего значения по магазину.

 

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

 

склад с 15.09 по 31.10.2010., стоимость партий поставки которых была

 

больше среднего по магазину, а также о товаре, стоимость поставки кото-

 

рого была максимальной.

 

 

111

 

 

8. С помощью сортировки и фильтра найдите: а) три самых дорогих

 

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

 

товар чаще всего, а какой больше всего поступает на склад.

 

З адание 4. Используя следующую таблицу:

 

Информационный справочник ЦБ

 

Цифровой код валю-ты

 

36 124 208 352 392 398 578 702 752 756

 

826 840 949 960 974 978

980

буквенный код валю-ты

 

AUD CAD DKK ISK JPY KZT NOK SGD SEK CHF

 

GBP USD TRY XDR BYR EUR

UAH

 

Наименование валюты

 

 

австралийский доллар канадский доллар датские кроны исландские кроны японские иены казахские тенге норвежские кроны сингапурский доллар шведские кроны швейцарский франк

английский фунт стерлин-гов

доллар США

 

новая турецкая лира СДР

белорусские рубли

 

ЕВРО

 

украинские гривны

 

за ед.   курс валюты   руб.

 

1     21,5164 1     22,5417 10    48,7412 100   44,8378 100   26,7552 100   21,5322 10      43,743 1     17,0922 10    39,9225 1     23,2632

 

1    52,6338 1     26,6833 1     21,1115 1     42,5198

1000  12,8903 1     33,7831

10   52,6239

 

1)  Прочитав справку в Excel, изучить формулу ПРОСМОТР и по циф-

 

ровому коду валюты определить ее курс; буквенный код валюты; наиме-

 

нование валюты.

 

2) Используя формулу ПРОСМОТР по буквенному коду валюты опреде-

 

лить ее курс; цифровой код валюты; наименование валюты.

 

3) Найти значения формул ВПР и ГПР и проверить по таблице их истин-

 

ность:

 

=ВПР(840;A3:E19;5;0); =ВПР(A15;A3:E19;1); =ВПР(840;A3:E19;2;0)

 

=ГПР(A3;A3:E19;5;0); =ГПР(A3;A3:E19;5); =ГПР(974;A3:E19;5;0)

 

4) Изучить функцию ПОИСКПОЗ, найти значения следующих формул:

 

=ПОИСКПОЗ(840;A3:A19;0);                =ПОИСКПОЗ(840;A3:A19;1)

 

=ПОИСКПОЗ(840;A3:A19;-1)

 

 

112

 

 

З адание 5. Используя функции просмотра, автоматизировать

 

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

 

товары на сумму более 1000 рублей (скидка 1%, подарок - блокнот);

 

более 2000 руб.(2% скидки, подарок - одеколон); более 5000 руб.

 

(скидка 5%, подарок - калькулятор); более 10000 руб.(7% скидки. по-

 

дарок - билеты на концерт).

	ФИОВыручка

сумма п окупки 1000 1001 2001 3001 5001

скидка                 подарок 0%                         нет 1%                     блокнот 2%                    одекалон

5%                 калькулятор 7%            билеты на концерт