Сортировка и фильтрация данных в списке.

Редактирование записи.С помощью формы данных можно редактировать любое поле, кроме защищенного или вычисляемого. Если поле доступно для редактирования, то данные этого поля выводятся в поле ввода. Чтобы отредактировать запись, нужно вывести ее в окне формы данных, а затем внести необходимые изменения в полях данных. После нажатия клавиши Enter все внесенные изменения отображаются в списке. Чтобы отменить правку в выведенной на экран записи прежде, чем перейти следующей записи нужно нажать кнопку Вернуть.

Ввод новой записи. Чтобы ввести новую запись нужно перейти на рабочий лист, содержащий список, выделить любую ячейку в списке и выбрать в меню Данныекоманду Форма. Затем следует выбрать кнопку Добавить и заполнить нужные поля. Для перемещения от одного поля к другому можно использовать клавишу Tab. По окончании вода нужно нажать клавишу Enter.

Поиск записи.Существует три способа поиска записи при помощи формы данных:

· пролистать записи в списке, пользуясь кнопками Назади Далее;

· пролистать список с помощью полосы прокрутки;

· выбрать кнопку Критерии для поиска записей по заданным критериям.

В последнем случае после щелчка мышью по кнопке Критерии Excel изменяет форму данных таким образом, что вводимые в поля данные интерпретируются как условия поиска, называемые критериями сравнения. Критерий сравнения может представлять собой последовательность символов, которой должна соответствовать запись. Например “Иванов” или “>300000”. Критерии можно задавать и для вычисляемых полей.

Для вывода на экран следующей записи, удовлетворяющей заданным критериям, нужно щелкнуть кнопку Далее, для вывода предыдущей записи - кнопку Назад Когда заданы критерии, эти кнопки позволяют просматривать только те записи, которые удовлетворяют всем заданным критериям. Для восстановления доступа ко всему списку нужно щелкнуть кнопку Поиск, затем кнопку Очистить.

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

Excel предоставляет средства упорядочения, поиска данных списка и создания отчетов на их основе. К ним относятся:Сортировка и фильтрация данных.

· Сортировка данных списка в алфавитном, числовом или хронологическом порядке. Во время сортировки Excel переупорядочивает строки в соответствии с содержимым одного или более столбцов.

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

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

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

Если Excel не выделил автоматически те данные, которые нужно сортировать, сделайте это сами, а затем снова выберете команду Сортировка.

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

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

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

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

Фильтрация списка с помощью автофильтра.Команда Автофильтр дает возможность выводить на экран необходимое подмножество элементов списка. Для вызова этой команды нужно в меню Данные выбрать команду Фильтр, а затем - Автофильтр. По этой команде Excel поместит раскрывающие стрелки непосредственно на метки столбцов. Щелкнув по стрелке, Вы выведете на экран список всех уникальных элементов соответствующего столбца. выделив элемент данного списка, Вы тем самым скроете все строки, кроме тех, которые содержат выделенное значение. Элемент столбца, который Вы выделили в раскрывающемся окне списка, называется критерием фильтра. Можно продолжить фильтрацию с помощью критериев из других столбцов.

Чтобы удалить критерии фильтра для отдельного столбца, нужно выделить в раскрывающемся списке столбца параметр Все. Чтобы показать все скрытые в списке строки, нужно выбрать в меню Данныекоманду Фильтр, а затем - Отобразить все.Для вывода на экран всех скрытых данных и удаления стрелок автофильтра нужно выбрать команду Фильтр и убрать отметку с команды Автофильтр.

Можно использовать автофильтр, чтобы найти заданное число (или заданный процент) наибольших и наименьших элементов в списке. Для этого щелкните по кнопке автофильтра интересующего вас столбца и выберете в списке (Первые 10). Появится окно диалога, которое имеет три раскрывающихся списка. В среднем списке нужно выбрать Наибольшихили Наименьших, в левом можно задать любое число между 1 и 500, а в правом - Элементов списка или % от количества элементов.

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

С помощью авто­фильтра можно для каждого столбца задать пользова­тель­ские критерии. Чтобы задать критерии для столбца, нужно вы­делить в раскрывающемся списке столбца параметр Ус­ловие... За­тем в диалоговом окне Пользовательский автофильтр можно ввести нужные критерии.

При задании пользователь­ского критерия нужно из списка выбрать нужный оператор сравнения и ввести значения для сравнения. Вместо выбора значений для сравнения можно щелкнуть по стрелке и выделить элемент с нужным значением в раскрывающемся списке. Этот список содержит все уникальные элементы столбца. Если задается два пользовательских критерия, нужно определить, как они должны применяться: оба одновременно (И) или либо один, либо другой (ИЛИ).

Фильтрация списка с помощью расширенного фильтра. Чтобы отфильтровать список по более сложным критериям, нужно использовать команду Расширенный фильтр.Для использования этой команды нужно сначала создать интервал критериев. После этого нужно выделить в списке отдельную ячейку, выбрать в меню Данные команду Фильтр, а затем - команду Расширенный фильтр. В появившемся диалоговом окне Расширенный фильтр нужно задать:

· Исходный диапазон -интервал, содержащий список, который нужно отфильтровать. Если выделена одна ячейка в списке, Excel автоматически введет ссылку на весь список;

· Диапазон условий -интервал, содержащий критерии.

В группе Обработка нужно выделить переключатель Фильтровать список на месте, после чего можно щелкнуть кнопку OK или нажать клавишу Enter. Excel скроет все строки, не удовлетворяющие заданным критериям.

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

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

Для использования в интервале критериев предназначены следующие типы критериев сравнения.

· Цепочка символов для проверки на соответствие. Чтобы найти записи, содержащие определенное значение, введите текст, число, дату или логическое выражение в ячейку под меткой критерия. Если в качестве критерия используется текстовое значение, Excel находит все элементы, начало которых совпадает с этим текстом.

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

· Количество, с которым надо сравнивать. Чтобы вывести на экран только строки, попадающие в определенные пределы, введите оператор сравнения и следующее за ним значение в ячейку под меткой критерия.

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

При задании текстовых условий символы > (больше) и < (меньше) означают: найти все значения, которые находятся по алфавиту после или перед введенным текстовым значением. Формула ="=текст" означает: найти значения, которые точно совпадают со строкой символов текст.

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

· Условия в одной строке считаются соединенным логическим оператором и. Вводите все критерии в одну строку, чтобы получить записи, удовлетворяющие сразу всем критериям из этой строки. Например, представленный на рисунке интервал критериев найдет все строки с записью Петров, в которых величина сбыта превышает 1000000.

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

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

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

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

· Формула должна выводить логическое значение ИСТИНА или ЛОЖЬ. При фильтрации списка на экран выводятся только те строки, в которых результатом вычисления формулы будет ИСТИНА.

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

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

Для вывода на экран строк, скрытых с помощью команды Расширенный фильтр, выберете в меню Данные команду Фильтр, а затем - Показать все.

Чтобы отфильтровать повторяющиеся строки или записи, нужно в окне Расширенный фильтр установить флажок Без повторений.

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

Работа с отфильтрованным списком. Фильтрация позволяет редактировать, печатать, сортировать подмножество списка, а также строить на основе этого подмножества диаграммы.

По команде Печатьменю Файл печатаются только данные, выведенные на экран.

При создании итогов для интервала данных в отфильтрованном списке с помощью кнопки Автосуммирование Excel показывает итоговое значение только для выводимых на экран данных. После изменения фильтра или выбора команды Показать все Excel соответственно корректирует итоговое значение.

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

При сортировке отфильтрованного списка меняется порядок только тех строк, которые выводятся на экран. Расположение скрытых строк при этом не меняется.