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

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

ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ

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

Пример. Найти все записи СУММА, который больше >1000000.

Если формула для некоторой записи выдает значение ИСТИНА, значит эта запись удовлетворяет критерию.

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

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

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

ПОИСК соответствия по составному критерию с И и ИЛИ

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

ПРИМЕР, =И(B8=”ИВАНОВ АА”,D8>500)

ПРОСМОТР И РЕДАКТИРОВАНИЕ ОТФИЛЬТРОВАННЫХ СПИСКОВ

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

ОКНО_ЗАКРЕПИТЬ ОБЛАСТИ

Редактирование отфильтрованного списка осуществляется обычными для рабочего листа методами.

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

ФУНКЦИИ баз данных

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

Мастер функций: КАТЕГОРИЯ:РАБОТА С БАЗОЙ ДАННЫХ.

Функций 12:

У каждой из функций 3 аргумента:

1) база данных – интервал ячеек, формирующих БД;

2) поле – аргумент типа ТЕХТ, который должен содержать имя столбца (поля) базы данных; Это имя можно указать как адрес Е3, как текст в двойных кавычках “КОЛИЧЕСТВО», или как число задающее положение столбца в списке.

3) критерий – интервал ячеек, содержащий критерий поиска. Как и для расширенного фильтра строится вне таблицы.
БДСУММ — ищет сумму чисел в указанном поле базы данных, записи которой соответствуют определенному условию. Например, найти общее количество ксероксов.

БСЧЕТ — Количество записей базы данных, ограниченной аргументом поле, которые содержат значения, удовлетворяющие критериям поиска. При отсутствии аргумента ПОЛЕ будет вычислено количество соответствующих критериям поиска записей данных во всей базе.

БИЗВЛЕЧЬ – возвращает найденное в поле базы данных, значение, удовлетворяющее заданному критерию, если такое значение – единственное. Функция возвращает сообщение об ошибке #ЧИCЛО#, если критериям поиска удовлетворяет более одной записи или #ЗНАЧЕНИЕ#если ни одна запись данных не удовлетворяет критериям поиска.

ДМАХ наибольшее число в соответствующих критериям поиска записях данных ПОЛЯ в базе данных.

ДМИН – наименьшее число в соответствующих критериям поиска записях данных ПОЛЯ в базе данных.

ДСРЗНАЧ – Среднее значение, которое вычисляется для значений в указанном поле всех записей, удовлетворяющих критерию.

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

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

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

Чтобы произвести сортировку блока данных, нужно выделить любую ячейку этого блока и выполнить команду ДАННЫЕ_СОРТИРОВКА,

Сортировка в особом порядке

Некоторые последовательности элементов должны выстраиваться не в алфавитно-цифровом порядке, а в некотором специальном. К таким последовательностям, например, относятся дни недели или месяцы:

Если необходимо упорядочить данные в особом порядке, в диалоговом окне СОРТИРОВКА ДИАПАЗОНА выберите кнопку ПАРАМЕТРЫ. Можно определить собственный вариант порядка сортировки. Для этого необходимо создать пользовательский список.

Сортировка по дате и времени

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

Сортировка по трем и более полям

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

Сортировка вычисляемых результатов

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

Переупорядочивание рабочих листов, списков, столбцов в БД

Можно упорядочить не только строки, но и столбцы. Этим можно воспользоваться для переорганизации списка вместо целого ряда операций копирования и вставки. Желательно порядок столбцов надо записать в отдельной смежной строке, затем выделить список вместе с этой отдельной строкой и выбрать команду ДАННЫЕ_СОРТИРОВКА. Затем открыть диалоговое окно ПАРАМЕТРЫ СОРТИРОВКИ и установить там переключатель СТОЛбЦЫ ДИАПАЗОНА, а затем в окне диалога СОРТИРОВКА ДИАПАЗОНА в качестве первичного ключа сортировки указать строку с порядковыми номерами столбцов. Нажмите ОК.

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

Составление отчетов

В процессе подготовки отчетов часто требуется подводить итоги.

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

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

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

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

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

Пример, укажите значение среднее значение цены для каждого типа ТОВАРА.

1. Отсортировать по графе ТОВАР

2. Данные _ИТОГИ

3. Ячейка подытоженных данных выделена

4. Данные-ИТОГИ

5. В появившемся окне выберите кнопку УБРАТЬ ВСЕ.