Поиск с помощью расширенного фильтра

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

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

1. Подготовить диапазон критериев для расширенного фильтра:

• верхняя строка должна содержать заголовки полей, по ко­торым будет производиться отбор (точное соответствие за­головкам полей списка);

• условия критериев поиска записываются в пустые строки под подготовленной строкой заголовка, причем следует учитывать, что:

- выполнение условия и требует располагать критерии по­иска рядом в одной строке;

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

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

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

2. Поместить указатель ячейки в список (или выделить весь не­обходимый список).

3. Выполнить команду Данные | Фильтр | Расширенный фильтр. В диалоговом окне Расширенный фильтр:

• указать в области Обработка место, куда будут помещать­ся результаты выборки данных;

• в поле Исходный диапазон пометить весь список, подле­жащий фильтрации (как правило, после помещения указа­теля ячейки в список данный диапазон выделяется по умолчанию);

• в поле Диапазон условий указать подготовленный диапа­зон условий отбора записей (удобно выделить мышью на рабочем листе);

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

• для отбора уникальных записей (без повторений) необходимо установить флажок Только уникальные записи.

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

 

Анализ данных

MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К ним носятся:

§ обработка списка с помощью различных формул и функций;

§ построение диаграмм и использование карт MS Excel;

§ проверка данных рабочих листов и рабочих книг на наличие ошибок;

§ структуризация рабочих листов;

§ автоматическое подведение итогов (включая мастер частич­ных сумм);

§ консолидация данных;

§ сводные таблицы;

§ специальные средства анализа выборочных записей и дан­ных— подбор параметра, поиск решения, сценарии, пакет анализа и др.

 

Структуризация рабочих листов

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

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

Рабочий лист может содержать только одну структуру, хотя ее можно разделить на несколько частей.

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

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

Для автоматического создания структуры следует:

§ удостовериться, что в итоговых формулах содержатся ссылки на детальные данные, расположенные в одном направлении относительно итоговых;

§ выделить нужный диапазон ячеек — для структуризации час­ти рабочего листа или выбрать одну ячейку — для структури­зации всего рабочего листа;

§ выполнить команду Данные | Группа и структура | Создание структуры.

При структуризации рабочего листа "вручную" необходимо:

§ выделить нужные ячейки строк и столбцов, которые подлежат объединению в структуру (за исключением ячейки с итоговой формулой);

§ выполнить команду Данные | Группа и структура | Группи­ровать.

В случае ошибочных действий или для разгруппировки дан­ных выбрать команду Данные | Группа и структура | Раз­группировать;

§ указать вид организации документа и создать структуру, вы­полнив команду Данные | Группа и структура | Настройка.

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

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

 

Автоматическое подведение итогов

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

§ простые промежуточные;

§ сложные промежуточные;

§ связанные с вычислением частичных сумм (используется мас­тер частичных сумм).

Промежуточные итоги формируются с помощью команды Дан­ные | Итоги. MS Excel автоматически создает необходимые ма­тематические выражения, вставляет строки промежуточных и общих итогов, а также структурирует данные. Такую информа­цию легко обрабатывать — форматировать ячейки, создавать диаграммы и т. д.

С помощью операции Итоги можно:

§ указать способ группировки данных;

§ вывести промежуточные и общие итоги для одной группы в списке;

§ вывести промежуточные и общие итоги для нескольких групп в списке;

§ выполнить расчеты над данными.

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

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

2. Провести сортировку по необходимому полю или полям (команда Данные | Сортировка).

3. Подвести итоги (команда Данные | Итоги).

Чтобы убрать итоги, необходимо установить указатель в список с итогами и выполнить команду Данные | Итоги | Убрать все.

При создании итогов можно:

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

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

подвести итоги по отфильтрованным данным (сначала — от­фильтровать, затем — отсортировать по необходимому полю (полям) и, наконец, — подвести итоги;

использовать мастер суммирования (команда Сервис | Мас­тер | Частичная сумма), что позволяет просуммировать только те данные в списке, которые удовлетворяют заданно­му критерию (например, вычислить сумму продаж для това­ров, цена которых больше некоторого значения).

Консолидация данных

Консолидация предназначается для обобщения однородных дан­ных и производится следующим образом:

1. Указать местоположение будущих консолидированных данных.

2. Выбрать команду Данные | Консолидация.

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

4. Указать способ консолидации:

• согласно расположению в диапазоне — сняты все флажки области Использовать в качестве имен;

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

5. Выбрать тип консолидации, т. е. указать, какая операция будет проводиться с консолидируемыми данными.

6. При необходимости указать добавление структуры — устано­вить флажок Создавать связи с исходными данными.

Сводные таблицы

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

§ для обобщения большого количества однотипных данных;

§ для реорганизации данных (с помощью перетаскивания);

§ для отбора и группировки данных;

§ для построения диаграмм.

Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) следующим образом:

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

2. Выполнить команду Данные | Сводная таблица.

3. Задать исходный диапазон данных. После нажатия кнопки Далее в окне мастера, откроется следующее окно.

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

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

6. Нажать кнопку Параметры и в открывшемся окне установить необходимые параметры сводной таб­лицы.

7. После проведения всех подготовительных операций нажать кнопку Готово.

При создании, редактировании и работе со сводными таблицами необходимо учитывать следующее:

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

§ местонахождение исходных данных — список MS Excel, внешний источник данных;

§ необходимость при задании структуры сводной таблицы опре­делить:

- поля, находящиеся в строках и столбцах таблицы;

- поля, по которым подводятся итоги (с выбором необходи­мой операции);

- поля для страниц, что позволяет представить информацию в трехмерном виде;

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

§ в сводных таблицах можно изменять названия полей, что не влечет изменений в полях исходных данных. Манипулирова­ние элементами сводной таблицы можно также осуществлять мышью — для удаления какого-либо поля из сводной таблицы следует перетащить удаляемый элемент за ее границы. Изме­нения в перестановке полей для страниц, столбцов и строк также осуществляется перетаскиванием;

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

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

- группировка элементов по их именам;

- группировка чисел по диапазонам (для числовых данных, кнопка Группировать меню Данные | Группа и струк­тура);

- группировка по временным диапазонам (данные в формате Дата/Время);

§ возможность построения диаграмм на основе сводных таб­лиц;

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

- сортировка элементов в сводной таблице;

- управление общими и промежуточными итогами;

- использование различных итоговых функций для анализа данных и дополнительных вычислений;

- вставка в сводную таблицу вычисляемого поля;

- использование автоформата для форматирования сводной таблицы.

Пояснения к работе

- Созданный список должен иметь не менее 7 полей (столбцов) и 20 записей (строк). Столбцы должны быть всевозможных типов: числовые, даты, тексты, формулы.

- Работа со списками.

Большинство операций со списками выполняется в меню Данные:

1) Сортировка данных:

Данные/Сортировка;

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

а).Данные/Фильтр/Автофильтр;

б)Фильтрация с помощью диалогового окна «Пользовательской автофильтр»: Данные/Фильтр/Автофильтр – параметр «Настройка»;

в).Фильтрация с помощью интервала критериев для команды «Расширенный фильтр»: Данные/Фильтр/Расширенный фильтр. Интервал критериев задается пользователем.

- Подведение итогов данных:

1) Общие итоги проще всего определяются кнопкой «Автосуммирование»;

2) Промежуточные итоги. Перед проведением промежуточных итогов по заданным элементам (столбцам) списка сначала необходимо провести сортировку (см. п. 3.1.). Затем выполнить в меню «Данные» команду «Итоги»;

3) В списке «При каждом изменении» выберите поле, по значениям которого нужно сгруппировать строки;

4) В списке «Использовать функцию» («Операция») выберите функцию, при помощи которой будут подводиться итоги. Например: Сумма, Среднее и т.д.;

 

 

Варианты заданий

№	Предметная область списка (база данных)
	Библиотечный каталог
	Прокат видеокассет
	ГИБДД (Государственная инспекция безопасности дорожного движения)
	Записная книжка
	Кадры предприятия
	Телефонный справочник
	ГТС - городская телефонная сеть
	Автокаталог
	Склад предприятия
	Кадры (преподаватели)
	Экзаменационная сессия
	Турагенство
	Торговый дом …..
	Налоговая инспекция (счет – фактура)
	Продуктовый магазин
	Оптовая база
	Спортивный комплекс
	Расчет кварплаты

 

 

ЛАБОРАТОРНАЯ РАБОТА № 7

Работа в СУБД Access

1. Цель работы:

- знакомство с базами данных;

- создание базы данных с помощью системы управления базами данных (СУБД)
Access;

-изучение основных возможностей СУБД Access по ведению баз данных.