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

Работа с базами данных в табличном процессоре Microsoft Excel

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

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

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

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

В списке Excel каждый столбец - это поле, а каждая строка - это запись.

В Excel предусмотрена функция создания списка с помощью формы:

1) Сформируйте заглавную строку. В каждом столбце этой строки введите название соответствующего поля записи.

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

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

В Excel существуют понятия абсолютной и относительной ссылок.

Ссылка на ячейку типа =А1 является относительной. При копировании такая ссылка изменяется автоматически. Если вы в ячейку В1 ввели формулу =А1, а потом скопировали ячейку в С1, то в ячейке С1 будет уже формула =В1. Если же вы скопируете ячейку В1 в ячейку В2, то в ячейке В2 будет формула =А2. То есть при копировании по горизонтали изменяются номера столбцов, и при копировании по вертикали меняются номера строк.

Это очень удобно при вычислениях.

Абсолютные ссылки отличаются от относительных тем, что при копировании не изменяются. Записываются они со знаком «$». Например, =$A$1. Это иногда используется в задачах, если нужна ссылка на одну определенную ячейку, например в ячейке хранится курс рубля к доллару или минимальная заработная плата исходя из которой вы будете рассчитывать остальные показания. В формуле можно использовать абсолютные и относительные ссылки, комбинируя их таким образом, чтобы автозаполнение ячеек было как можно более удобным и результаты пересчитывались автоматически.

Например =А2*$F$1 - ссылка на F1 абсолютная, а на А2 - относительная.

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

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

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

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

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

2) Установки По возрастанию и По убыванию определяют порядок сортировки. Вариант По возрастанию означает сортировку по возрастающим числам (начиная с самого малого и заканчивая самым большим), по буквам от А до Я и по датам и времени - начиная с самых ранних значений и до самых поздних. При сортировке По убыванию значения располагаются в обратном порядке.

) В поле Затем по выбирается установка для вторичной сортировки.

Отбор данных на основе фильтра.

В процессе фильтрации на листе остаются только те записи, которые соответствуют заданному критерию, прочие записи скрываются. В Excel есть два вида фильтрации: автофильтр и расширенный фильтр.

Автофильтр.

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

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

- Если выбран пункт Условие, появиться окно диалога которое позволяет задать правила фильтрации для числовых значений в виде двух неравенств, объединенных логическими операциями И или ИЛИ. В задании условий допустимы символы * и?.

- После установки условий нужно щелкнуть на кнопке ОК.

Таблица 1.1 - Правила фильтрации

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

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

1) Куда поместить отфильтрованный список: фильтровать на месте или скопировать результат в другое место (в этом случае необходимо указать диапазон, в который следует поместить отфильтрованный список).

2) Исходный диапазон - диапазон списка.

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

4) В случае если флажок Только уникальные записи установлен, из совпадающих по содержанию записей выводится только одна.

5) Щелкнуть по кнопке ОК.

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