Сортировка записей

Сортировка по одному полю:

1. сделать активной любую клетку внутри столбца, по которому прово­дится сортировка;

2. кнопки на панели стандартных инструментов «АЯ» (по возраста­нию) или «ЯА» (по убыванию).

Сортировка по одному - трем полям:

1. сделать активной любую клетку внутри блока БД;

2. меню: «Данные»  «Сортировка»;

3. в появившемся диалоговом окне выбрать первое, второе, третье поля сортировки по возрастанию или убыванию.

Отбор записей

С использованием «Формы” (переход от записи к записи происходит только по тем записям, которые удовлетворяют критерию поиска):

1. меню: «Данные»  «Форма»  «кнопка «Критерии»;

2. ввести в окна полей значения критериев поиска, можно использовать шаблон (* и ?), знаки >, <, >=, <=, =, <>(не равно).

Завершение работы с «Формой»: кнопка «Отменить».

С использованием фильтрации (в БД видны только записи, удовлетво­ряющие критерию поиска):

1. сделать активной любую клетку внутри блока БД,

2. меню «Данные»  «Фильтр»  «Автофильтр» — справа от имен по­лей появляются стрелки выпадающих списков.

3. щелкнуть по стрелке, выбрать значение поля для фильтрации:

• (все) - фильтр отменяется;

• (настройка) - задание сложных фильтров;

• любое значение поля из появившегося списка.

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

4. при необходимости повторить пункт 3) для другого поля.

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

Отмена всех фильтров:

• меню: «Данные» «Фильтр»«Авто фильтр»

Получение итогов

Если в БД есть числовые поля, то можно получить промежуточные итоги по группе записей, а также общий итог:

1. отсортировать БД по полю, по которому группируются записи для итогов (см. п. «Сортировка записей»);

2. меню: «Данные»  «Итоги»;

3. в появившемся диалоговом окне задать:

• поле, при каждом изменении значения в котором, подводится итог (по этому полю проводилась сортировка),

• числовые поля, для которых подводится итог,

• функции, по которым производится подсчет итогов;

4. ОК.

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

В Microsoft Excel имеется 12 функций рабочего листа, используемых для анализа данных из списков или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, ис­пользует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются дан­ной функцией.

Синтаксис

БДФункция(база_данных;поле;критерий)

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

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

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

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

Критерий - это ссылка на интервал ячеек, задающих условия для функ­ции. Функция возвращает данные из списка, которые удовлетворяют усло­виям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов, в спи­ске. Ссылка на критерий может быть введена как интервал ячеек, например A1:E2 в приведенном ниже примере базы данных, или как имя интервала, на­пример «Критерии».

Советы

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

Несмотря на то, что диапазон критериев может располагаться в любом месте таблицы, не стоит помещать его внизу списка. Это связано с тем, что данные, добавляемые в список командой <Форма> меню <Данные>, вставля­ются с первой строки после списка. Если эта строка не пустая, Microsoft Excel не сможет добавить данные в список.

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

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

Примеры

Следующий рисунок является примером базы данных для небольшого фруктового сада. Каждая запись содержит информацию об одном дереве. Диапазон A6:E12 имеет название База_Данных, а диапазон A1:E4 - Критерий.

БСЧЁТ(A6:Е12; «Возраст»;A1:С2) равняется 2. Эта функция просматри­вает записи о яблонях, которые имеют высоту от 5 до 10 и подсчитывает ко­личество числовых полей “Возраст” в этих записях.

БСЧЁТ(A6:Е12; « Прибыль»;A3: С4) равняется 1. Эта функция просматри­вает записи о грушах, которые имеют высоту до 10 и возраст менее 10 и под­считывает количество непустых полей “Прибыль” в этих записях.

ДМАКС(A6:Е12; « Прибыль»;A1: А4) равняется 105,00 руб. — макси­мальный доход от яблонь и груш.

ДМИН(A6:Е12;«Прибыль»;A1:B2) равняется 75,00 руб. — минималь­ный доход от яблонь выше 5.

БДСУММ(A6:Е12;E6;A1:A2) равняется 225,00 руб. — суммар­ный доход от яблонь.

БДСУММ(A6:Е12; «Прибыль»;A1: С2) равняется 180,00 руб. — сум­марный доход от яблонь высотой от 5 до 10 .

ДСРЗНАЧ(A6:Е12; «УРОЖАЙ (кг)»;A1:B2) равняется 12 — средний уро­жай яблонь высотой более 5.

ДСРЗНАЧ(A6:E12;C6;A6:A11) равняется 13 — средний возраст всех де­ревьев.

БИЗВЛЕЧЬ(A6:Е12;D6;A1:B2) возвращает значение ошибки #ЧИСЛО!, потому что критерию удовлетворяет более чем одна запись.

БИЗВЛЕЧЬ(A6:Е12; «УРОЖАЙ (кг)»;A3:С4) равняется 8, возвращает значение поля « УРОЖАЙ (кг)» для груш высотой меньше 10 и возраст, которых меньше 10.

Замечания

Если ни одна из записей не удовлетворяет критерию, то функция воз­вращает значение ошибки #ЗНАЧ!.

Если более чем одна запись удовлетворяет критерию, то функция воз­вращает значение ошибки #ЧИСЛО!

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