Работа с таблицей Excel Как с базой данных: автофильтр, Расширенный фильтр, Сортировка данных, Создание сводных таблиц

Цель работы: Изучить:

· Автофильтрация.

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

· Сортировка данных.

· Создание сводной таблицы.

Теоретическая часть

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

В Microsoft Excel в качестве базы данных можно использовать список.

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

Пример списка - базы данных приведен в таблице 2.20 (в столбце стоимость задана формула = количество * цена).

Таблица 2.20

Пример списка

Расход материалов со склада № 3
Материал	количество	ед. измер.	Цена(у.е.)	стоимость(у.е)	получил	дата
Доска		м^3			Иванов А.П.	16.10.00
Гвозди		кг			Сидоров С.К.	12.11.00
Цемент		кг			Иванов А.П.	14.10.00
Кирпич		шт.			Петров С.К.	20.11.00
Доска		м^3			Иванов А.П.	16.11.00
Кирпич		шт.			Иванов А.П.	20.01.00
Цемент		кг			Иванов А.П.	14.01.00
Гвозди		кг			Петров С.К.	14.09.00
Гвозди		кг			Петров С.К.	14.10.00
Кирпич		шт.			Петров С.К.	21.09.00

 

Рекомендации по созданию списка на листе книги

Размер и расположение списка

На листе не следует помещать более одного списка.

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

В самом списке не должно быть пустых строк и столбцов.

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

 

Заголовки столбцов

Заголовки столбцов должны находиться в первой строке списка.

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

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

 

 

Содержание строк и столбцов

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

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

Не следует помещать пустую строку между заголовками и первой строкой данных.

 

Основным назначением баз данных является быстрый поиск содержащейся в них информации.

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

Организация ввода, просмотра и поиска данных с помощью форм;

Сортировка данных;

Фильтрация данных;

Подведение итогов и создание сводных таблиц.

Организация ввода, просмотра и поиска данных с помощью форм

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

Сортировка списков

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

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки (рис. 23).

 

Рис. 23. Пример сортировки

 

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

Таблица 2.21

Сортировка

 

Расход материалов со склада № 3
Материал	количество	ед. измер.	цена(у.е.)	стоимость(у.е)	получил	дата
Доска		м^3			Иванов А.П.	16.10.00
Доска		м^3			Иванов А.П.	16.11.00
Кирпич		шт.			Иванов А.П.	20.01.00
Цемент		кг			Иванов А.П.	14.01.00
Цемент		кг			Иванов А.П.	14.10.00
Гвозди		кг			Петров С.К.	12.09.00
Гвозди		кг			Петров С.К.	14.09.00
Гвозди		кг			Петров С.К.	14.10.00
Кирпич		шт.			Петров С.К.	20.09.00
Кирпич		шт.			Петров С.К.	21.09.00

 

Фильтрация данных

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

Автофильтр

Поместите курсор в область, содержащую базу данных или выделите её.

Затем выполнить команды меню Данные/ Фильтр (рис. 24).

 

 

Рис. 24. Фильтрация данных

 

 

На именах полей появятся кнопки с изображением стрелок вниз, которыми можно задать критерии фильтрации(рис. 25):

 

Рис. 25. Критерии фильтрации данных

 

 

Пункт Условие позволяет применить отличные от равенства операторы сравнения (рис. 26).

 

Рис. 26. Фильтрация данных

 

 

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

 

Рис. 27. Выбор условий для фильтрации

 

Допускается использование специальных символов подстановки:

? - Любой символ в той же позиции, что и знак вопроса.

Например: д?м задает поиск "дым" и "дом"

* - Любую последовательность символов в той же позиции, что и звездочка.

Например: *ино задает поиск "Люблино" и "Выхино"

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

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

Примеры: в базе данных, заданной таблицей,

1) выбрать строки, относящиеся к октябрю 2000 года.

Для этого в столбце дата нужно задать соответствующее условие (рис. 28).

 

Рис. 28. Фильтрация данных

 

В результате получим таблицу(в зависимости от исходных данных) (рис. 29).

 

 

Рис. 29. Результаты фильтрации данных

 

2) Выбрать строки с одним наибольшим количеством кирпича.

Для этого в поле материал задать критерий кирпич и в поле количество выбрать пункт с наибольшим количеством (рис. 30).

 

 

Рис. 30. Пример выбора строки

 

В результате получим (рис. 31):

 

 

Рис. 31. Результаты фильтрации данных

 

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

 

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

 

 

Рис. 32. Фильтрация данных

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

 

Создание диапазона условий

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

2. Введите в строки под заголовками условий требуемые критерии отбора.

3. Укажите ячейку в списке.

4. Выберите пункт меню Данные/ Фильтр/ Дополнительно.

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

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

 

Примеры условий отбора расширенного фильтра

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

Условия, записанные в одной строке, автоматически соединяются в одно логическое выражение союзом И (And). Условия, записанные в смежных строках, соединяются союзом ИЛИ (Or).

§ На ячейки одного столбца накладываются три или более условий отбора

Выбираются все строки с условием:

(материал= доска) или (материал=гвозди) или (материал=цемент)

материал

доска

гвозди

цемент

.

§ Условие отбора накладывается на ячейки двух или более столбцов

1) выбираются строки с условием:

(получил=иванов) и (дата>10/10/2000) и (дата<1/11/2000)

Получил	дата	дата
иванов	>10/10/2000	<1/11/2000

2) выбираются строки с условием:

(цена=максимальной цене в списке) или стоимость=максимальной стоимости в списке)

цена(у.е.)	стоимость(у.е)
=МАКС($D$3:$D$20)
	=МАКС($E$3:$E$20)

§ В условии отбора используется возвращаемое формулой значение

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

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

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

Например, выбираются строки с условием:

значение в столбце 'цена(у.е.)' превышает среднее значение в ячейках $D$3:$D$20; заголовок условия критерий.

критерий

='цена(у.е.)'>СРЗНАЧ($D$3:$D$20)

 

Аналогичный результат получится при задании условия в виде:

=D3>СРЗНАЧ($D$3:$D$20)

Подведение итогов

Для подведения итогов по всей базе данных, а также по отдельным группам данных можно использовать функцию Excel ПРОМЕЖУТОЧНЫЕ ИТОГИ (рис. 33),

Рис. 33. Промежуточные итоги

 

а также создание сводных таблиц (рис. 34).

 

Рис. 34. Создание сводных таблиц