Главная > Кофеварки > Поиск данных с помощью фильтров. Базы данных


Поиск данных с помощью фильтров. Базы данных




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

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

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

Фильтр набирается в специальном окне фильтра.

Чтобы установить или изменить его, необходимо выбрать в меню “Записи ” команду “Изменить фильтр ”,

затем, после установки всех условий выбрать в меню “Записи ” команду “Применить фильтр ”.

Чтобы восстановить показ всех записей надо выбрать в меню “Записи” команду “Удалить фильтр”.

Также можно воспользоваться соответствующими кнопками панели инструментов –

Есть два вида фильтра – простой и расширенный. В верхней части окна простого фильтра выводится список всех полей таблицы.

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

Какие государства находятся в Азии?

У какого государства столица – Джакарта?

Для более сложных условий отбора используются расширенные фильтры.

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

В строке “Поле” необходимо указать название поля, по которому производится поиск нужной информации. Это можно сделать следующими способами:

· набрать его название на клавиатуре;

· перетащить мышью из списка полей;

· выполнить двойной щелчок по названию нужного поля в списке полей;

· щелкнуть мышью в строке “Поле” и выбрать нужное название поля в раскрывающемся списке.

В строке “Условие отбора” необходимо указать набор условий.

В выражениях для условий отбора допускается использование шаблонов:

· * (звездочка) – заменяет любую группу любых символов;

· ? (знак вопроса) – заменяет один любой символ;

Например:

“ А* ” – все слова, начинающиеся на “А”


“ ????? ” – все слова, состоящие из пяти букв


“ ?и* ” – все слова, у которых вторая буква “и”

Также в условных выражениях можно использовать

· логические операции, >=, =,

· логические функции Or (дизъюнкцию), And (конъюнкцию), Not (отрицание).

Условные выражения, набранные в разных столбцах одной строки “Условия отбора” соединяются функцией AND .

Условные выражения, набранные в разных строках “Условия отбора” соединяются функцией OR.

Например:

Найти все государства, названия которых и названия их столиц состоят из пяти букв

Найти все государства, названия которых или названия их столиц состоят из пяти букв

Найти все государства, в названии которых есть буква «е» или «и»

Найти все государства, площадь которых меньше 100

Найти все государства, площадь которых не меньше 100

Ответьте с помощью фильтров на следующие вопросы:

1) Какие государства находятся в Европе.

2) Какие государства находятся на американском континенте.

3) У каких государств название столицы состоит из пяти или четырех букв.

4) Какие государства, название которых начинается с буквы “М”, находятся в Азии

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

К средствам фильтрации относятся:

    Автофильтр (существуют два способа применения команды Автофильтр: с помощью меню "Данные" - "Фильтр" - "Автофильтр" - "раскрывающийся список команд автофильтра" и с помощью кнопки "Автофильтр" на панели инструментов стандартная)

    Расширенный фильтр ("Данные" - "Фильтр" - "Расширенный фильтр")Автофильтр предназначен для простых условий отборов строк, а расширенный фильтр для более сложных условий отбора записей. Условие отбора - это ограничения, заданные для отбора записей, которые отбираются редактором для отображения на экране.

Использование автофильтра для поиска записей

Перед применением команды "Автофильтр" выделите ячейку в диапазоне списка. Далее выполните команду "Данные" - "Фильтр" - "Автофильтр", справа от заголовков (подписей) столбцов появятся кнопки со стрелками автофильтра (раскрывающиеся списки команд автофильтра).

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

Список команд автофильтра и их назначение:

    Все - все записи будут отображаться на экране

    Первые 10... - откроется окно диалога "Наложение условия по списку" в том случае, если данные в столбце являются числами. С помощью этого окна можно быстро просмотреть до десяти наибольших или наименьших чисел в столбце.

    Условие... - откроется окно диалога "Пользовательский автофильтр". С помощью этого окна можно отсортировать записи, удовлетворяющие одному или двум условиям

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

Алгоритм поиска записей отвечающих одному условию в одном столбце

    Выберите ячейку в списке

    Щелкните на одном из уникальных имен, редактор мгновенно скроет строки, которые не содержат выбранных данных На рисунке представлен отфильтрованный список по столбцу "Категории" при выбранном условии отбора "Конд. продукты"

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

Алгоритм поиска записей отвечающих одному или двум условиям в одном столбце

Для поиска записей по двум условиям применяется "Пользовательский автофильтр":

    Выберите ячейку в списке

    Выполните команду "Данные" - "Фильтр" - "Автофильтр"

    Щелкните на кнопке автофильтра в том столбце, который используете для фильтрации данных

    Щелкните на команде "Условие", откроется окно диалога "Пользовательский автофильтр"

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

    Щелкните на кнопке ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям.Для примера выберем следующие условия отбора: показать в списке строки, содержащие продукты, цены которых больше или равны 69,80 грн и меньше 99,99 грн.

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

    не равно

  • больше или равно

  • меньше или равноКроме того, можно задавать операторы сравнения, которые, используются при фильтрации текста: «начинается с», «не начинается с», «оканчивается на», «не заканчивается на», «содержит» и «не содержит».

На рисунке представлен отфильтрованный список по столбцу "Цена" при выбранных условиях отбора.

Фильтр по выделенному

Фильтрация по выделенному - это быстрый способ отфильтровать записи по одному условию с помощью кнопки "Автофильтр" на панели инструментов стандартная.

Алгоритм фильтрации по выделенному:

 Выделите требуемую запись в списке

 Нажмите кнопку "Автофильтр", чтобы выполнить фильтрацию по выделенному. Редактор включит "Автофильтр" и скроет строки, которые не содержат выбранных данных

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

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

Создание диапазона условий отбора:

    Выделите заголовки фильтруемых столбцов списка и выполните команду "Копировать" любым из известных методов

    Выделите первую строку для ввода условий отбора и выполните команду "Вставить" любым из известных методов

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

После создания диапазона условий отбора, можно применить к списку команду "Расширенный фильтр".

Алгоритм фильтрации с помощью команды "Расширенный фильтр"

    Выделите ячейку в списке

    В меню "Данные" выберите команду "Фильтр" - "Расширенный фильтр"

    Установите переключатель "Обработка" в положение "Фильтровать список на месте", если хотите, чтобы редактор выводил результаты фильтрации в списке, скрыв строки, не удовлетворяющие критериям отбора. Чтобы скопировать отфильтрованные записи в другую область рабочего листа, установите переключатель "Обработка" в положение "Скопировать результаты в другое место" и укажите диапазон для размещения результатов.

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

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

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

На рисунке представлен образец "Расширенного фильтра" выбранными параметрами фильтрации.

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

Включение автофильтра:

  1. Выделить одну ячейку из диапазона данных.
  2. На вкладке Данные найдите группу Сортировка и фильтр .
  3. Щелкнуть по кнопке Фильтр .

Фильтрация записей:

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


  1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно , больше , меньше , Первые 10… и др.
  2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит... , начинается с… и др.
  3. При выборе опции Фильтры по дате варианты фильтрации - завтра , на следующей неделе , в прошлом месяце и др.
  4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И - одновременное выполнение 2 условий, ИЛИ - выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

  1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .
  2. В группе Сервис (или на вкладке Вставка в группе Фильтры ) выбрать кнопку Вставить срез .

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete .

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

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

Задание условий фильтрации

  1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте или скопировать результат в другое место .

  1. Указать Исходный диапазон , выделяя исходную таблицу вместе с заголовками столбцов.
  2. Указать Диапазон условий , отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
  3. Указать при необходимости место с результатами в поле Поместить результат в диапазон , отметив курсором ячейку диапазона для размещения результатов фильтрации.
  4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи .

Поиск с помощью автофильтра

Поиск с помощью формы данных

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

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

1. Поместите указатель ячейки в любое место внутри списка.

2. Выберите команду Данные | Форма, затем нажмите кнопку Критерии (рис. 8.4).

Рис. 8.4. Поиск с помощью формы данных

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

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

Установку необходимых значений полей для поиска точного соответствия рис. 8.5);

Рис. 8.5. Поиск с помощью автофильтра по точному соответствию

Применение пользовательского автофильтра Условие - позволяет осуществить поиск близкого соответствия на основе критериев и/или (рис. 8.6);

Использование команды Первые 10 (рис. 8.7) для отбора некоторого количества наибольших или наименьших элементов списка (в основном необходимо хотя бы одно поле с числами).

Рис. 8.6. Поиск с помощью пользовательского автофильтра

Рис. 8.7. Окно Наложение условия по списку

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

1. Установите указатель ячейки в список данных.

2. Выполните команду Данные | Фильтр | Автофильтр. Возле каждого поля строки заголовка появятся раскрывающиеся списки в виде кнопки с треугольником.

3. Перейдите к необходимому полю.

4. Выберите необходимый критерий поиска или воспользуйтесь пользовательским автофильтром Условие.

5. Для включения в критерий другого поля возвратитесь к пункту 1.

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

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

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

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

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


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

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

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

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

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

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

В диалоговом окне Расширенный фильтр (рис. 8.8):

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

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

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

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

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

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

Рис. 8.8. Окно Расширенный фильтр

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

MS Excel предоставляет широкие возможности для проведения

анализа данных, находящихся в списке. К средствам анализа от-

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

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

Проверка данных рабочих листов и рабочих книг на наличие

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

П автоматическое подведение итогов (включая мастер частич-

ных сумм);

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

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

О специальные средства анализа выборочных записей и дан-

ных - подбор параметра, поиск решения, сценарии, пакет

анализа и др.

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

Цель структуризации заключается в разбиении данных, содер-

жащихся на рабочем листе, на определенные уровни детализа-

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

Если между данными имеется строгая зависимость, то MS Excel

позволяет автоматически создать структуру - в этом случае про-

грамма ищет ячейки, которые содержат формулы, обобщающие

информацию в строках, и расположенные слева от данных. Дан-

ные должны быть согласованы в одном направлении. Для выпол-

нения автоматической структуризации все детальные столбцы

должны стоять по одну сторону от итоговых столбцов, все де-

тальные строки должны находиться по отношению к итоговым

либо только снизу, либо - только сверху. Если это условие не

соблюдается, то структуру следует создать вручную.

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

можно разделить на несколько частей (рис. 8.17).

Отображение и скрытие данных структуры может отразиться на

частях рабочего листа, которые не участвуют в иерархии,

т. к. строки сворачиваются и разворачиваются по всей ширине

рабочего листа, а столбцы - по всей высоте рабочего листа

При выводе структуры по левому и верхнему краю рабочего лис-

та отображаются специальные символы, которые служат для вы-

вода и скрытия уровней детализации (табл. 8.1).__

Рис. 8.17. Пример структуризации данных на рабочем листе

Рис. 8.18. Скрытие низших уровней в структуре данных

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

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

на детальные данные, расположенные в одном направлении

относительно итоговых;

Выделить нужный диапазон ячеек - для структуризации час-

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

зации всего рабочего листа;

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

структуры.

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

О выделить нужные ячейки строк и столбцов, которые подлежат

объединению в структуру (за исключением ячейки с итоговой

формулой);

Выполнить команду Данные | Группа и структура | Группи-

В случае ошибочных действий или для разгруппировки дан-

ных выбрать команду Данные | Группа и структура | Раз-

группировать;

О указать вид организации документа и создать структуру, вы-

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

Для возврата рабочего листа в исходное состояние следует вос-

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

структуру.

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

диаграммы с заданных уровней структуры.

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

Итоги необходимы для создания разнообразных отчетов и для

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

подразделяются на:

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

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

Связанные с вычислением частичных сумм (используется мас-

тер частичных сумм).

Промежуточные итоги формируются с помощью команды Дан-

ные | Итоги. MS Excel автоматически создает необходимые ма-

тематические выражения, вставляет строки промежуточных и

общих итогов, а также структурирует данные. Такую информа-

цию легко обрабатывать- форматировать ячейки, создавать

диаграммы и т. д.

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

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

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

в списке;

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

в списке;

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

Подведение промежуточных итогов предполагает использование

следующей методики:

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

ки. Определиться с тем, какие нужны итоги.

2. Провести сортировку по необходимому полю или полям

(команда Данные | Сортировка).

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

При создании вложенных промежуточных итогов следует четко

представлять уровни итогов и создавать их в порядке увеличения

уровня детализации: сначала - по первому ключу, далее, снимая

флажок Заменить текущие итоги, - по вторичному ключу и т. д.

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

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

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

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

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

(например, вычислить среднее и суммарное значение для

столбца с числовыми данными);

Подвести итоги по отфильтрованным данным (сначала - от-

фильтровать, затем- отсортировать по необходимому полю

(полям) и, наконец, - подвести итоги;

Использовать мастер суммирования (команда Сервис | Мас-

тер | Частичная сумма) позволяет просуммировать только те

данные в списке (рис. 8.19), которые удовлетворяют заданно-

му критерию (например, вычислить сумму продаж для това-

ров, цена которых больше некоторого значения).__

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

Консолидация предназначается для обобщения однородных дан-

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

методики:

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

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

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

консолидации.

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

Согласно расположению в диапазоне - сняты все флажки

области Использовать в качестве имен;

Согласно заголовкам строк и столбцов- установлены

флажки подписи верхней строки и значения левого

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

проводиться с консолидируемыми данными.

6. При необходимости указать добавление структуры - устано-

вить флажок Создавать связи с исходными данными.

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

Сводные таблицы представляют собой средство для группировки,

обобщения и анализа данных, находящихся в списках MS Excel

или в таблицах, созданных в других приложениях. Внешне сводные

Рис. 8.27. Представление консолидированных данных

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

трехмерном виде. Сводные таблицы могут использоваться:

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

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

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

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

Сводные таблицы создаются с помощью мастера сводных таблиц

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

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

тель ячейки в необходимое место на рабочем листе.

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

3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мас-

тера (рис. 8.28 и 8.29). После нажатия кнопки Далее в окне

мастера, приведенном на рис. 8.29, откроется окно 3-го шага

мастера (рис. 8.30).

Рис. 8.28. Определение местоположения данных для сводной таблицы

Рис. 8.29. Диапазон данных для сводной таблицы

Рис. 8.30. Указание местоположения будущей сводной таблицы

4. Прежде чем указать местоположение будущей таблицы

(рис. 8.30), необходимо нажать кнопку Макет и в открывшем-

ся окне (рис. 8.31) сформировать макет сводной таблицы (т. е.

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

поля сводной таблицы).

Рис. 8.31. Формирование макета сводной таблицы

5. Для определения необходимой операции для полей, помещен-

ных в область Данные, либо задания вычисляемого поля два-

жды щелкнуть левой кнопкой мыши на поле, помещенном в

область Данные (рис. 8.31), и выбрать необходимые действия

в окне Вычисление поля сводной таблицы (рис. 8.32).

6. Нажать кнопку Параметры (рис. 8.30) и в открывшемся окне

(рис. 8.33) установить необходимые параметры сводной таб-

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

кнопку Готово (рис. 8.30).

При создании, редактировании и работе со сводными таблицами

необходимо учитывать следующее:

Местоположение сводной таблицы - желательно располагать

ее на отдельном листе, т. к. при обновлении, группировках

Рис. 8.32. Окно Вычисление поля сводной таблицы

Рис. 8.33. Установка параметров сводной таблицы

сводной таблицы информация, содержащаяся на рабочих лис-

тах рядом со сводной таблицей, может оказаться скрытой;

Местонахождение исходных данных - список MS Excel,

внешний источник данных, диапазоны консолидации, нахо-

дящиеся в другой сводной таблице;

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

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

Поля, по которым подводятся итоги (с выбором необходи-

мой операции);

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

в трехмерном виде;

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

данных. Поэтому в самой таблице данные редактировать нель-

зя. Для изменения данных в сводной таблице необходимо вне-

сти изменения в источник данных, а затем обновить сводную

таблицу (кнопкой | \ \ Обновить данные панели инструмен-

тов Сводные таблицы (рис. 8.34);

Рис. 8.34. Панель инструментов Сводные таблицы

В сводных таблицах можно изменять названия полей, что не

влечет изменений в полях исходных данных. Манипулирова-

ние элементами сводной таблицы можно также осуществлять

мышью - для удаления какого-либо поля из сводной таблицы

следует перетащить удаляемый элемент за ее границы. Изме-

нения в перестановке полей для страниц, столбцов и строк

также осуществляется перетаскиванием;

Сводные таблицы допускают возможность группировки эле-

ментов полей по различным уровням иерархии путем объеди-

нения в группы (выделение данных сводной таблицы осуще-

ствляется, например, с помощью мыши). Для этой цели в ме-

ню Данные | Группа и структура существуют две кнопки:

[ + | Группировать и [ _] Разгруппировать. Группы эти мож-

но переименовывать по желанию.

Детали в группе можно скрывать и показывать. Элементы са-

мого высокого уровня группировки (обобщающие элементы)

располагаются по верхней или по крайней левой границе

сводной таблицы (отображение исходных данных в этом слу-

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

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

кнопкой | °1 I Отобразить детали). Применяются _ﾜ_____следующие

варианты группировки сводной таблицы:

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

Группировка чисел по диапазонам (для числовых данных,

кнопка Группировать меню Данные | Группа и струк-

Группировка по временным диапазонам (данные в формате

Дата/Время);

Возможность построения диаграмм на основе сводных таб-

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

(многие из них производятся путем выбора соответствующей

команды контекстного меню необходимого поля или выде-

ленной области):

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

Размещение страниц сводной таблицы на различных рабо-

чих листах (кнопкой Отобразить страницы);

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

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

данных и дополнительных вычислений;

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

Использование автоформата для форматирования сводной

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

¨ Поиск записей по тексту, содержащемуся в любом из полей.

¨ Расстановка записей по возрастанию или убыванию значений конкретного поля.

¨ Изменение количества отображаемых записей с помощью фильтра.

Поиск текста в записях

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

Поиск текста в текущем поле:

1. Отобразите таблицу в Режиме таблицы .

2. Щелкните столбец, в котором Вы надеетесь найти нужный текст.

3. Щелкните кнопку Найти на панели инструментов Таблица в режиме таблицы.

4. Введите искомый текст. Если при поиске нужно учитывать регистр символов, щелкните флажок С учетом регистра , иначе – сбросьте флажок.

5. Для начала поиска щелкните кнопку Найти.

6. Для перехода к следующей записи, содержащий заданный текст, щелкните кнопку Найти далее .


7. Для закрытия диалогового окна и возврата в таблицу щелкните кнопку Закрыть .

Поиск текста во всех полях:

1. Отобразите таблицу в Режиме таблицы.

2. Щелкните кнопку Найти на панели инструментов Таблица в режиме таблицы.

3. Введите искомый текст.

4. Сбросьте флажок Только в текущем поле.

5. Для начала поиска щелкните кнопку Найти.

6. Для перехода к следующей записи, содержащий заданный текст, щелкните кнопку Найти далее.

7. Для закрытия диалогового окна и возврата в таблицу щелкните кнопку Закрыть.

Изменение порядка записей в таблице

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

Расположение записей по возрастанию значений одного поля:

1. Установите курсор в любом месте столбца, по полям которого хотите отсортировать записи.

2. Щелкните кнопку Сортировка по возрастанию на панели инструментов Таблица в режиме таблицы.

Сортировка записей по нескольким полям

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

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

1. Отобразить таблицу в Режиме таблицы .

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

3. Для сортировки записей по возрастанию или по убыванию щелкните кнопку Сортировка по возрастанию или Сортировка по убыванию на панели инструментов Таблица в режиме таблицы .

Поиск и замена текста в таблице

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

Поиск и замена текста:

1. Отобразить таблицу в Режиме таблицы .

2. Щелкните столбец, в котором Вы рассчитываете найти искомый текст.

3. Щелкните меню Правка и затем команду Заменить .

4. В поле Образец введите текст, подлежащий замене.

5. В поле Заменить на введите новый текст.

6. Для поиска первого упоминания текста щелкните Найти далее .

7. Для замены первого найденного фрагмента текста щелкните кнопку Заменить , для замены всех найденных фрагментов – кнопку Заменить все , а для перехода к следующему фрагменту – кнопку Найти далее .


8. Закончив работу, щелкните кнопку Закрыть .

Просмотр заданных записей с помощью фильтра

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

Фильтрование таблицы по выделенному:

1. Отобразить таблицу в Режиме таблицы .

2. Установите курсор в поле, по которому хотите задать фильтрование.

3. Щелкните кнопку Фильтр по выделенному (1 кнопка) на панели инструментов Таблица в режиме таблицы . В нижней части окна таблицы появится сообщение о количестве записей, удовлетворяющих критериям Вашего фильтра. Буквы ФЛТР в строке состояния означают, что фильтр активен.

Отключение фильтра:

Щелкните кнопку Удалить фильтр (3 кнопка) на панели инструментов Таблица в режиме таблицы . Из строки состояния исчезнут буквы ФЛТР .

Сохранение фильтра

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

Сохранение фильтра как запроса:

Режиме таблицы.

2. Щелкните меню Записи , установите указатель мыши на команду Фильтр и в появившемся подменю щелкните команду Расширенный фильтр . В окне расширенного фильтра Вы увидите принятую схему условий отбора записей.

3. Щелкните меню Файл и затем – команду Сохранить как запрос .

4. Введите в текстовое поле Имя запроса имя сохраняемого запроса.

5. Для сохранения фильтра в виде запроса щелкните кнопку ОК . Имя сохраненного запроса появится во вкладке Запросы окна базы данных.

Создание сложных фильтров с помощью Конструктора фильтров

В Access для разработки сложных фильтров можно воспользоваться Конструктором. Когда Вы вписываете в одну вкладку несколько условий одновременно, то создаете И-фильтр . Он отображает только записи, удовлетворяющие всем заданным во вкладке критериям. Записи, удовлетворяющие одному из нескольких условий, просматривают с помощью ИЛИ-фильтра . При его применении отображаются все записи, соответствующие любому из условий, заданных во вкладке Найти или в любой из активных вкладок Или .

Создание И-фильтра:

1. Отобразите отфильтрованную таблицу в Режиме таблицы.

1. Щелкните кнопку Изменить фильтр (2 кнопка) на панели инструментов Таблица в режиме таблицы .

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

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

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

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

6. Щелкните кнопку Применение фильтра на панели инструментов Фильтр .

Создание ИЛИ-фильтра:

1. Отобразить таблицу в Режиме таблицы .

2. Щелкните кнопку Изменить фильтр на панели инструментов Таблица в режиме таблицы .

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

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

5. Щелкните стрелку раскрывающегося списка и выберите значение поля.

6. Выполните аналогичные действия для каждого поля, заданного для фильтрования. Любое из них задает дополнительные критерии отбора записей.

7. Для задания дополнительных условий фильтрования щелкните вкладку Или внизу окна Конструктора.

8. Щелкните кнопку Применение фильтра на панели инструментов Фильтр .

Сортировка данных (в режиме таблицы)

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

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

Фильтрация данных (в режиме таблицы)

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

Чтобы осуществить выборку необходимых данных, следует:

1. Выполнить команду Записи, Фильтр, Изменить фильтр.

2. Щелкнуть на имени требуемого поля в списке и ввести условие (маску) выбора.

3. Выполнить команду Фильтр, Применить фильтр.

На экране появится таблица, которая будет содержать строки, данные которых отвечают указанному условию. Чтобы вновь увидеть всю таблицу целиком, следует выполнить команду Записи, Удалить фильтр.

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

Ниже приведены варианты условий выборки данных из нашей базы и результаты выборки.

Условие: зарплата: >=200 (сотрудники, у которых зарплата больше или равна 200)

код

Фамилия

Имя

Телефон

Зарплата

1

Иванов

Егор

200

3

Ефимов

Евгений

210

Условие: имя: Н* (сотрудники, имена которых начинаются на букву Н)

код

Фамилия

Имя

Телефон

Зарплата

2

Семенов

Николай