Н.В.Водополова , В.Н. Шибеко

Н.В.Водополова, В.Н. Шибеко

ПРАКТИЧЕСКОЕ ПОСОБИЕ

по теме “ ФИЛЬТРАЦИЯ ДАННЫХ В MS Exsel”

для студентов всех экономических специальностей

Гомель 2000

УДК 681

 

 

     Практическое пособие по теме “Фильтрация данных в MS Exsel” для студентов всех экономических специальностей. – Гомель: ГГТУ, 2000. – 26 с.

 

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

     Рецензент: ст.преподаватель кафедры “Организация и управление отраслевых экономик” Астраханцев С.Е.

 

ВВЕДЕНИЕ

 

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

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

 

 

Глава 1. ОРГАНИЗАЦИЯ СПИСКА ИЛИ БАЗЫ ДАННЫХ

     Поиск или фильтрация данных осуществляется в списках или базах данных.

     Совокупность данных в виде таблицы называется списком или базой данных. В Microsoft Excel понятия список и база данных взаимозаменяемы.

     Каждая строка списка является записью, а каждый столбец строки – полем. Первая строка списка содержит имена полей списка и называется строкой заголовков.Н а рис.1 приведен пример списка в Excel.

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

         

Диапазон базы данных

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

- Если какому-либо диапазону ячеек не текущем листе присвоено имя База_данных, Excel будет считать этот диапазон списком.

- Если имя База_данных не определено, но перед выбором команды из меню Данные выделен диапазон ячеек, то он и будет воспринят как список.

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

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

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

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

- Если в рабочей книге содержится один список, присвойте ему имя База_данных (прямо в поле имен или с помощью команды Вставка-Имя-Присвоить).

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

Пример диапазона базы данных приведен на рис.1

 

 

Диапазон критериев

Условия отбора информации – критерии для всех средств фильтрации и поиска информации в Excel задаются по-разному. В форме данных для этого используется диалоговое окно (см.рис.2), в Автофильтре – раскрывающийся список Автофильтра (см.рис.6), в Расширенном фильтре создается диапазон критериев (см.рис.11). Критерии поиска и фильтрации данных рассмотрены в гл.2.

Диапазон критериев применяется для задания условий отбора записей из списка и состоит как минимум из двух строк: строки с заголовками полей и строк с критериями списка.

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

При создании строки заголовков диапазона критериев можно:

- перечислять все имена, но это не обязательно;

- указать только те заголовки полей, которые определяют условия отбора;

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

- менять взаиморасположение заголовков полей.

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

 

Диапазон для извлечения

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

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

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

Как и диапазон критериев, диапазон для извлечения в обязательном порядке начинается со строки с заголовками полей. Правила ее создания аналогичны созданию строки заголовков диапазона критериев.

Различают ограниченный и неограниченный диапазон для извлечения.

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

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

 

Вычисляемые критерии

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

 

 

Ввод данных

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

1. Если определено имя База_данных, перейдите к шагу 2, иначе выделите ячейку внутри списка.

2. Выберите команду Данные, Форма. Появится форма, аналогичная показанной на рис.2.

3. Нажмите кнопку Добавить.

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

Для быстрого перемещения от поля к полю используйте клавишу <Tab>. Нажатие клавиш <Enter> эквивалентно нажатию кнопки Добавить.

5. Для ввода следующих данных повторите шаги 3 и 4.

6. Нажмите кнопку Закрыть, чтобы вернуться к рабочему месту.

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

Поля новой записи можно редактировать только до нажатия кнопки Добавить или Закрыть. В дальнейшем можно найти и изменить запись с помощью формы данных и команд фильтрации (п.3.2,3.3).

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

 

Поиск информации

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

 

Редактирование данных

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

         

 

Поиск точного соответствия

Раскрывающийся список поля содержит полный перечень значений этого поля, имеющих место в обрабатываемой базе данных (см.рис.6). При выборе в раскрывающемся списке рис.7 значения «Мысль» мы получим следующий результат

Поиск по команде Условие

 

По команде Условие можно отфильтровать данные списка на базе операций сравнения или задать более сложное условие с помощью логических операций И/ИЛИ.

Выбор в раскрывающемся списке Автофильтра команды Условие вызывает появление на экране диалогового окна Пользовательский автофильтр (см.рис.8). В левом раскрывающемся списке диалогового окна можно выбрать операцию сравнения, а в правом – выбрать или ввести значение. Затем, если необходимо, выбрать один из переключателей И, ИЛИ, и задать вторую операцию и значение. Завершение ввода условия поиска – нажатие кнопки ОК.

На рис.9 приведены результаты по критерию поиска рис.8.

Поиск по команде Первые 10

Команда Первые 10 позволяет выбирать ряд записей из начала или конца списка. Число отбираемых записей можно задать непосредственно либо указать необходимый процент от всех записей. Например, можно отобрать записи по тем издательствам, выданный авторам аванс которых попадает в 10% наибольших.

Чтобы воспользоваться этим средством:

1. Раскройте список необходимого поля

2. Выберите команду Первые 10. Откроется диалоговое окно Наложение условия по списку (рис.10).

3. Выберите 3 параметра: число, выбор из наибольших или из наименьших элементов, единицы отсчета (количество или процент).

4. Нажмите кнопку ОК.

Глава 5. РАСШИРЕННЫЙ ФИЛЬТР

     Расширенный фильтр требует большей работы, чем Автофильтр или форма данных, но и предоставляемые возможности поиска и фильтрации значительно шире:

· можно применять сложные критерии, составленные с помощью логических операций И, ИЛИ, количество которых не регламентируется;

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

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

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

Чтобы   отфильтровать список:

1. Выделите любую ячейку списка или выделите список целиком.

2.
Выберите команду Данные, Фильтр, Расширенный фильтр. Появится диалоговое окно (рис.11).

3. В группе Обработка установите параметр Фильтровать список на месте.

4. Если необходимо, перейдите к полю Исходный диапазон и введите ссылку или имя диапазона списка.

5. Если необходимо, перейдите к полю Диапазон условий и введите ссылку или имя диапазона критериев.

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

7. Нажмите кнопку ОК. Результат фильтрации данных рис.11 представлен на рис.12.

Для возвращения к данным полного списка необходимо выбрать команды Данные, Фильтр, Отобразить все.

 

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

     Изменения:

· в группе Обработка установите параметр Скопировать результат в другое место.

· если необходимо, в поле Поместить результат в диапазон введите ссылку или имя диапазона назначения.

Результаты работы Расширенного фильтра сразу же отобразятся на рабочем листе.

Пример простого поиска данных изображен на рис.13, 14.

Расширенный фильтр отключает Автофильтр, поэтому его можно использовать независимо от того, включен или нет Автофильтр.

 

 

5.1.
Использование множественного сравнения

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

Несколько условий введено в одной строке. На рис.15 критерий поиска будет читаться так: найди данные по издательству «Мысль» И гонорар автора не должен превышать 150 руб.,т.е. будут отфильтрованы записи, удовлетворяющие всем условиям одновременно.

По одному условию введено в разных строках. В этом случае будут отфильтрованы записи, которые удовлетворяют хотя бы одному условию. На рис.16 приведены критерии и результаты поиска данных об издательствах «Мысль» или «Наука».

Несколько условий введено в нескольких строках и столбцах. Результатом поиска будут записи, которые удовлетворяют всем условиям первой строки или всем условиям второй строки или всем условиям третьей строки и т.д. На рис.17 видны записи об издательстве «Мысль» по авторам с гонорарами, большими 100, или о договорах издательства «Финансы», заключенные после 31.12.93г.

 

Вычисляемые критерии

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

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

1.В диапазоне критериев вводится дополнительный столбец. В случае поиска данных из определенного интервала это столбец с заголовком поля, по которому осуществляется поиск. Один нужен для ввода нижней, а второй – верхней границы интервала значений поля (см.рис.18). Во всех остальных случаях может быть использован любой уникальный текст для наименования критерия (на рис.19- это поле «Остаток»). Дополнительный критерий может помещаться в любой столбец диапазона критериев.

2.Поскольку критерий поиска – формула, он должен начинаться со знака "равно" (=).

3.Т.к. формула поиска логическая, то она в обязательном порядке должна содержать операцию отношения (см.табл.1) или логическую функцию И (), ИЛИ (), НЕ () (AND, OR, NOT). Подробнее см.п.5.2.2 «Поиск соответствия по простому вычисляемому критерию».

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

Приведем несколько примеров сравнения полей одной записи:

= B12 = E12

= B12 <12/2

= B12 -  E12>10

= B12<>$C$5

5.Если в критерии участвуют данные одной записи, то в формуле необходимо использовать самую первую запись списка, иначе результаты фильтрации будут неверными.

 

Л И Т Е Р А Т У Р А

 

1.Рон Персон. Excel 7.0 для WINDOWS 95 в подлиннике: пер. с англ.-СПб.:BHV-Санкт-Петербург,1996.-1056 с.

2.Уокербах Джон. Библия пользователя Exsel 97.Пер. с англ. К.-Диалектика,1997.-624 с.

3.Рогов И.П. Exsel 97(серия «Без проблем!») – М. Восточная Книжная Компания, 1997.-400 с.

4.Мартин С. Метьюз. Exsel для Windows 95 / Проще простого. – К.:, Диалектика,1996.- 416с.

5.Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Exsel. Издание 2-е дополненное – М.: Информ.издательский дом «Филинъ», 1998.-184с.

6.Р.Винтер, п.Винтер. Microsoft Office 97 в подлиннике: пер с англ. BHV-Санкт-Петербург, 1997. Том 1. 640 с.

 

С О Д Е Р Ж А Н И Е

 

Введение……………………………………………………………….	3
Глава 1. Организация списка или базы данных …………………..	3
1.1. Диапазон базы данных…………………………………………..	3
1.2. Диапазон критериев………………………..……………………..	5
1.3. Диапазон для извлечения... ……………………………………...	5
Глава 2. Критерии и фильтрации данных…………………………...	6
2.1. Критерии поиска на основе сравнения….………………………	6
2.2. Поиск соответствия по дате и времени.. ……………………….	7
2.3. Поиск близкого соответствия с использованием образца……..	7
2.4. Поиск соответствия с использованием множественного   критерия с операциями И/ИЛИ………………………………….	8
2.5. Вычисляемые критерии ……………………………………….…	9
Глава 3. Использование формы данных	9
3.1. Ввод данных………………………………………………………	9
3.2. Поиск информации……………………………………………….	10
3.2.1. Поиск на основе простого сравнения…………………………	10
3.2.2. Поиск на основе множественного сравнения………………...	11
3.3. Редактирование данных………………………………………….	12
Глава 4. Использование Автофильтра………………………………	12
4.1. Поиск точного соответствия……………………………………..	14
4.2. Поиск по команде Условие………………………………………	14
4.3. Поиск по команде Первые 10……………………………………	15
Глава 5. Расширенный фильтр……………………………………….	16
5.1. Использование множественного сравнения ……………………	18
5.2. Вычисляемые критерии…………………………………………..	19
5.2.1. Поиск данных из определенного интервала………………….	21
5.2.2. Поиск соответствия по простому вычисляемому критерию..	22
Литература ……………………………………………………………	24
Содержание …………………………………………………………..	25

 

 

Н.В.Водополова, В.Н. Шибеко

ПРАКТИЧЕСКОЕ ПОСОБИЕ

по теме “ ФИЛЬТРАЦИЯ ДАННЫХ В MS Exsel”

для студентов всех экономических специальностей

Гомель 2000

УДК 681

 

 

     Практическое пособие по теме “Фильтрация данных в MS Exsel” для студентов всех экономических специальностей. – Гомель: ГГТУ, 2000. – 26 с.

 

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

     Рецензент: ст.преподаватель кафедры “Организация и управление отраслевых экономик” Астраханцев С.Е.

 

ВВЕДЕНИЕ

 

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

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