Сортировка данных в таблицах. Автоматические и расширенные фильтры

1.    Какой порядок расположения символов в таблице Ишсойе?

2.    Какие значения логических функций И, ИЛИ в зависимости от значений аргументов?

3.    Каково назначение символов * и ? в масках имен файлов?

СОРТИРОВКА ДАННЫХ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ

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

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

В Excel 2007 сортировка по возрастанию происходит по следующим правилам:

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

•    числа упорядочиваются от наименьшего значения к наибольшему;

•    даты упорядочиваются от самой старой к самой новой;

•    сначала располагаются тексты, написанные латиницей, затем - кириллицей;

•    сначала размещаются тексты, написанные прописными буквами, потом - заглавными;

•    тексты упорядочиваются так: сначала сравниваются первые символы, в случае их равенства - вторые символы и т. д.;

•    логическое значение ЛОЖЬ размещается перед значением ИСТИНА;

•    пустые ячейки располагаются последними (хотя символ Пробел в таблице Unicode стоит первым и имеет наименьший код).

При сортировке по убыванию порядок расположения будет обратным.

Примеры размещения значений различных типов после сортировки по возрастанию и по убыванию представлены на рисунке 7.12.

Для того чтобы быстро выполнить сортировку строк некоторого диапазона по значениям первого столбца этого диапазона, следует:

1.    Выделить нужный диапазон ячеек.

2.    Выполнить Главная ^ Редактирование ^ Сортировка и фильтр ^ Сортировка от минимального к максимальному или Сортировка от максимального к минимальному (рис. 7.13).

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

Обращаем ваше внимание:

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

•    для числовых смешанных данных - Сортировка от минимального к максимальному и Сортировка от максимального к минимальному;

•    для текстовых данных - Сортировка от А до Я и Сортировка от Я до А;

•    для даты и времени - Сортировка от старших к новым и Сортировка от новых к старшим.

2.    Если выполнять сортировку, виделив даные только в одном столбце, открывается окно Обнаружены данные вне указанного диапазона (рис. 7.14), в котором предлагается автоматически расширить выделенный диапазон ячееек или сортировать в пределах указанного выделения.

Сортировку в выделенном диапазоне можно осуществлять и по значениям нескольких столбцов. Это происходит так:

•    сначала строки сортируются по значениям в первом из указанных столбцов;

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

Каждый из указанных для сортировки столбцов называют уровнем сортировки.

Объясним такую сортировку на конкретном примере. На рисунке 7.15 приведен пример таблицы годовых оценок учащихся школы, а на рисунке 7.16 эту же таблицу отсортировано по значениям в четырех столбцах:    Фамилия

(от А до Я), Имя (от А до Я), Сумма (от наименьшего к наибольшему) и Информатика (от наименьшего к наибольшему).

Процесс сортировки происходил так. Сначала строки переставлялись так, чтобы даные в столбце Фамилия были расположены в алфавитном порядке (от А до Я). И если бы у всех учащихся фамилии были разные, то больше никакой сортировки этой таблицы не было бы. Но таблица содержит три группы учащихся с одинаковыми фамилиями: Бурчак, Левчук, Штейн.

Дальнейшая сортировка происходит уже по значениям во втором из выбранных столбцов (Имя) и только в пределах каждой из этих трех групп строк, по каждой отдельно. В результате этого шага сортировки переставились (а может и нет) первая и вторая строки, пятая и шестая строки, одиннадцатая и двенадцатая строки так, чтобы имена были расположены в алфавитном порядке (от А до Я).

Сортировка по значениям в третьем из выбранных столбцов (Сумма) происходила только в пределах пятой и шестой строк, в которых значения в первых из двух выбранных столбцов совпали (Левчук Алексей). Сортировки по значениям в четвертом из выбранных столбцов не происходило, потому что не оказалось хотя бы двух строк, в которых значения в первых из трех выбранных для сортировки столбцов (Фамилия, Имя, Сумма) совпали.

Чтобы отсортировать строки в выделенном диапазоне ячеек по значениям в нескольких столбцах, следует:

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

2.    Выполнить Данные ^ Сортировка и фильтр ^ Сортировка.

3.    Установить или изменить значения параметров сортировки: в списке поля Сортировать по выбрать имя первого столбца, по значениям в

котором нужно отсортировать строки, в списке поля Сортировка по -Значение, а в списке поля Порядок - порядок сортировки (рис. 7.15).

4.    Определить условия сортировки по значениям в другом столбце, выбрав кнопку Добавить уровень или Копировать уровень, и установить в полях строки Затем значения параметров сортировки по значениям во втором выбранном столбце.

5.    При необходимости повторить команду 4 несколько раз.

6.    Если в выделенный диапазон вошла строка заголовка, но отметка флажка Мои данные содержат заголовки не установлена, установить ее (тогда строка заголовка НЕ БУДЕТ учитываться при сортировке и останется на своем месте).

7.    Выбрать кнопку ОК.

Для тех, кто хочет знать больше

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

АВТОМАТИЧЕСКИЕ ФИЛЬТРЫ

Фильтрация — это выбор значений в ячейках электронной таблицы, соответствующих определенным условиям.

После выполнения фильтрации в таблице отображаются только те строки, данные в которых соответствуют условиям фильтрации. Остальные -временно скрываются.

Чтобы установить режим фильтрации данных, следует:

1.    Установить табличный курсор в произвольную ячейку таблицы или выделить необходимый диапазон ячеек.

2.    Выполнить Главная ^ Редактирование ^ Сортировка и фильтр ^ ^ Фильтр (или Данные ^ Сортировка и фильтр ^ Фильтр).

После выполнения этих действий у правой границы каждой ячейки первой строки таблицы или выделенного диапазона ячеек появятся кнопки открытия списка команд фильтрации 0 (рис. 7.17). В этих списках находятся команды сортировки, фильтрации по цвету, а также числовые и текстовые фильтры.

Рассмотрим некоторые примеры выполнения фильтрации.

Чаще всего выполняют фильтрацию по значениям в некотором столбце данных. Например, по представленной таблице оценок отобразить список учеников, которые имеют по информатике оценку 9. Для этого следует:

1.    Открыть список команд фильтрации в столбце Информатика.

2.    Снять отметку флажка Выделить все.

3.    Установить отметку флажка у значения 9.

4.    Выбрать кнопку ОК.

После выполнения этих действий в

таблице будут отображены только те строки, значение в которых в нужном столбце равны указанным. Выполнение и результат описанного примера представлены на рисунках 7.18 и 7.19.

Обратите внимание: вид кнопки фильтра в столбце Информатика на рисунке 7.19 изменился на такой |.7. Наличие такой кнопки в ячейке означает наличие установленного фильтра для данных в этом столбце таблицы.

Фильтрацию можно осуществлять в столбце по нескольким значениям. Например, нужно отобразить данные об учениках с именами, Иван или Алексей. Для этого следует:

1.    Открыть список команд фильтрации в столбце Имя.

2.    Снять отметку флажка Выделить все.

3.    Установить отметку флажка у значений Иван и Алексей (рис. 7.20).

4.    Выбрать кнопку ОК.

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

Например, нужно отобразить данные о тех учениках, которые имеют суммарный балл по предметам I 35 и < 40. Для этого следует выполнить:

1.    Открыть список команд фильтрации в столбце Сумма.

2.    Выбрать Числовые фильтры ^ между.

3.    Ввести в соответствующие поля окна необходимые данные - 35 и 40 (рис. 7.23).

4.    Выбрать кнопку ОК.

Применив фильтр на рисунке 7.23, получим таблицу, как на рисунке 7.24.

Еще один числовой фильтр, команда Первые 10 открывает окно Наложение условия по списку (рис. 7.25), в котором можно установить условие фильтрации, по которому в таблице будут отображаться только те строки, которые содержат в данном столбце 10 (или другое установленное количество) наибольших (наименьших) значений.

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

Для текстовых данных применяются несколько иные команды фильтрации (рис. 7.26): равно, не равно, начинается с, заканчивается на, содержит и др.

Например, нужно отобразить данные о тех учениках, фамилии которых начинаются с буквы П. Для этого следует выполнить:

1.    Открыть список команд фильтрации в столбце Фамилия.

2.    Выбрать Текстовые фильтры ^ начинается с...

3.    Ввести в соответствующее поле букву П (рис. 7.27).

4.    Выбрать кнопку ОК.

Фильтрацию можно выполнять последовательно для нескольких столбцов таблицы. Тогда каждый следующий фильтр будет применяться уже только для тех строк таблицы, которые отображаются после применения всех предыдущих фильтров. То есть общее условие фильтрации - это условия фильтрации для каждого столбца, объединенные логической операцией И. Например, если провести фильтрацию по данным столбца Сумма с условием > 35, а затем по данным столбца Информатика с условием > 8, то общее условие фильтрации будет таким - (сумма баллов > 35) И (балл по информатике > 8). Результат такой фильтрации приведен на рисунке 7.28.

Отменить фильтрацию данных можно несколькими способами:

•    выполнить Главная ^ Редактирование ^ Сортировка и фильтр ^ Очистить;

•    выполнить команду Снять фильтр с в списке столбца, по данным которого была выполнена фильтрация;

•    установить отметку флажка Выделить все в списке столбца, по данным которого была выполнена фильтрация, после чего выбрать кнопку ОК.

Чтобы вообще отменить режим, в котором можно проводить фильтрацию, следует повторно выполнить одну из команд, которая устанавливает этот режим.

ДОПОЛНИТЕЛЬНЫЕ ФИЛЬТРЫ

Использование автофильтров дает возможность выполнить фильтрацию не во всех случаях. Так, например, рассмотренными способами нельзя выполнить фильтрацию при условии, которое является объединением условий фильтрации двух столбцов логической операцией ИЛИ, например (сумма баллов больше 35) ИЛИ (балл по информатике больше 8).

Выполнить такие виды фильтрации в табличном процессоре Excel 2007 можно с помощью расширенных фильтров. Для этого следует:

1.    Скопировать или ввести в свободные ячейки электронной таблицы названия тех столбцов, по которых будет осуществляться фильтрация (например, на рисунке 7.29 названия столбцов Сумма, Информатика).

2.    Ввести в ячейки под названиями столбцов условия фильтрации. Если эти условия должны объединяться логической операцией И, то условия фильтрации должны располагаться в одной строке. Если логической операцией ИЛИ - то в разных (на рисунке 7.29 условия >35, >8).

3.    Выполнить Данные ^ Сортировка и фильтр ^ Дополнительно.

4.    В окне Расширенный фильтр:

а)    Выбрать один из переключателей для определения области расположения результата фильтрации (на рисунке 7.29 - фильтровать список на месте).

б)    Ввести в поле Исходный диапазон диапазон ячеек, данные в котором должны фильтроваться (на рисунке 7.29 - В2:Н14).

в)    Ввести в поле Диапазон условий диапазон ячеек, в которых расположены названия столбцов и условия фильтрации.

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

5. Выбрать кнопку ОК.

На рисунке 7.30 представлен результат фильтрации, выполненной по условиям, представленным на рисунке 7.29.

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

Для того чтобы быстро выполнить сортировку выделенного связанного диапазона по значениям первого столбца этого диапазона, следует выполнить Главная ^ Редактирование ^ Сортировка и фильтр ^ Сортировка от минимального к максимальному или Сортировка от максимального к минимальному.

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

Фильтрация — это выбор значений в ячейках электронной таблицы, соответствующих определенным условиям. После выполнения фильтрации в таблице отображаются только те строки, данные в которых соответствуют условиям фильтрации. Остальные - временно скрываются.

Для выбора режима фильтрации нужно установить табличный курсор в произвольную ячейку таблицы или выделить диапазон ячеек и выполнить Главная ^ Редактирование ^ Сортировка и фильтр ^ Фильтр или Данные ^ Сортировка и фильтр ^ Фильтр.

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

Фильтрацию можно осуществлять последовательно для нескольких столбцов таблицы. Тогда каждый следующий фильтр будет применен только для тех строк таблицы, которые отображаются после применения всех предыдущих фильтров.

Для отмены фильтрации нужно выполнить Данные ^ Сортировка и фильтр ^ Очистить.

Для выполнения фильтрации при условии, которое является объединением условий фильтрации двух столбцов логической операцией ИЛИ, используются расширенные фильтры. Для этого в свободных ячейках таблицы необходимо написать названия столбцов и условия фильтрации. Затем выполнить Данные ^ Сортировка и фильтр ^ Дополнительно и установить параметры фильтрации в окне Расширенный фильтр.

1°. В чем заключается сортировка данных в Excel 2007?

2*. Какие правила сортировки данных по возрастанию в Excel 2007? 3*. Каким образом можно провести быструю сортировку?

4*. Когда команда Сортировка от наименьшего к наибольшему меняется на команду Сортировка от А до Я?

5*. Как осуществляется сортировка данных в таблицах по значениям нескольких столбцов?

6°. Что такое фильтрация?

7*. Какие виды фильтрации можно осуществлять для числовых данных; текстовых данных?

8*. Для чего используется расширенный фильтр?

9°. Как задать условия для расширенного фильтра?

10*. В чем отличия автоматического и расширенного фильтров?

1°. Откройте указанный учителем файл (например, Глава 7\Пункт 7.4\образец 7.4.1х1ях). На листе Лист1 представлена таблица вылета самолетов из аэропортов Киева. Отсортируйте таблицу по возрастанию времени вылета самолетов. На листе Лист2 установите режим фильтрации. Отобразите данные только о самолетах, вылетающих из аэропорта «Борисполь». Сохраните книгу в вашей папке в файле с именем задание 7.4.1.х1ях.

2*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.4\образец 7.4.2.х1ях). На листе Лист1 представлена таблица результатов участия спортсменов в соревнованиях по троеборью: фехтование (очки), бег (время), прыжки в высоту (м). Отсортируйте таблицу по значениям в трех столбцах: Очки (по убыванию), Время (по возрастанию), Высота (по убыванию). На листе Лист2 установите режим фильтрации. Отобразите данные об участниках, которые набрали в фехтовании не менее 40 очков и прыгнули в высоту от 2,20 до 2,25 м. Сохраните книгу в вашей папке в файле с именем задание 7.4.2.х1ях.

3*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.4\образец 7.4.3.х1ях). На листе Лист1 представлена таблица вылета самолетов из аэропортов Киева. Отсортируйте таблицу так, чтобы даные о самолетах, вылетающих из одного аэропорта, шли подряд, данные об аэропорте назначения размещались в порядке от Я до А, а время вылета увеличивалось. На листе Лист2 установите режим фильтрации. Отобразите данные о самолетах, вылетающих в Варшаву с 9 до 15 часов. Сохраните книгу в вашей папке в файле с именем задание 7.4.3.х1ях.

4*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.4\образец 7.4.4.х1ях). На листе Лист1 представлена таблица с данными о жестких магнитных дисках. Задайте условие расширенной фильтрации для отбора данных о ЖМД, в которых или емкость больше 500 Гбайт, или цена меньше 1000 грн. Сохраните книгу в вашей папке в файле с именем задание 7.4.4.х1ях.

5*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.4\образец 7.4.5.х1ях). На листе Лист1 представлена таблица с данными о ТБТ-мониторах. Задайте условие расширенной фильтрации для отбора данных о мониторах, в которых или диагональ больше 19", или цена меньше 3000 грн. Сохраните книгу в вашей папке в файле с именем задание 7.4.5.х1ях.

Это материал учебника Информатика 8 класс Ривкинд