Уокенбах Формулы в Excel

Глава9

Базы данных исписки

Вэтой главе...

•Знакомство со списками и базами данных электронной таблицы

•Использование средства Автофильтр

•Использование расширенного фильтра

•Определение условий расширенного фильтра

•Использование функций базы данных

•Суммирование списка с помощью таблицы подстановки

•Создание промежуточных итогов

•Резюме

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

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

Знакомство со списками и базами данных электронной таблицы

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

Столбцы списка рабочего листа, как правило, называются полями, а строки — записями. Используя эту терминологию, можно сказать, что данный список (см. рис. 9.1) содержит шесть полей (Name, Annual Salary, Monthly Salary, Location, Date Hired, Exempt) и двадцать записей.

Рис. 9.1. Типичный пример использования списка на рабочем листе

Максимальный размер списка, создаваемого в приложении Excel, ограничен размером одного рабочего листа. Он может содержать не более 256 полей и не более 65 535 записей (одна строка предназначена для имен полей). Список такого размера требует огромного количества памяти и, иногда, его даже невозможно отобразить. Возможна и другая крайность — список, состоящий из одной ячейки, несмотря на то, что он практически бесполезен, тем не менее, может рассматриваться.

В версиях, предшествующих Excel 97, рабочий лист содержит только 16384 строки.

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

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

•Ввод данных

•Фильтрация списка с целью отображения отдельных строк, отвечающих определенным условиям

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

•Вставка формул для вычисления промежуточных итогов

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

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

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

*^1O:

Рис. 9.2. После выбора команды ДанныеоФильтр&Автофильтр Excelдобавляет стрелки раскрывающегося списка к подписям полей в строке заголовка

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

Имейте в виду, чтообласть действия средства Автофильтра имеет ограничения. В раскрывающемся списке отображаются только первые 1000 элементов. Если ваш список превышает этоколичество, вы можете использовать Расширенный фильтр (этосредство описано ниже).

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

•(Все). Отображает все содержащиеся в столбце элементы. Используйте это условие для удаления фильтра изстолбца.

•(Первые 10). Отображает первые 10 строк с максимальными или минимальными значениями ячеек текущего столбца. В действительности этот термин употребляется неправильно, поскольку это условие позволяет отобразить "первые гс" элементов (пользователю остается выбрать необходимое число).

•(Условие). Отображает элементы, удовлетворяющие одному илидвум условиям (рис. 9.3).

•(Пустые). Отображает строки, содержащие в этом столбце пустые ячейки. Эта опция

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

• (Непустые). Отображает строки, содержащие в этом столбце непустые ячейки.Эта опция доступна только в том случае, если в столбце содержатся пустые ячейки.

После выполненияфильтрации Excel автоматически назначает диапазону ячеек,содержащему отфильтрованныйсписок, скрытое имя (_FilterDatabase). Обратите внимание, чтоимя начинается с символа подчеркивания. В дальнейшем пользователь может использовать это имя в макросах VBA или формулах. Чтобы выбрать отфильтрованный диапазон данных, нажмите комбинацию клавиш <Ctr!+G>, — на экране отобразится диалоговое окно Перейти к. Имейте в виду, что скрытое имя не отображается в списке имен, поэтому его необходимо вводить вручную. Просто введите в поле Ссылказначение _ F i l t e r D a t a b a s e ищелкните на кнопкеОК.

Выбор элемента (Условие) при использовании автофильтра весьма полезен, но все же имеет некоторые ограничения. Например, если вы хотите выполнить фильтрацию списка, чтобы отобразить только три значения поля (например, Нью-Йорк, Нью-Джерси илиКоннектикут), использование автофильтра в данном случае неприемлемо. Такие задачи фильтрации требуют применения расширенного фильтра, который будет обсуждаться позже в этой главе.

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

Подсчет исуммирование отфильтрованных данных

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;А5:А400)

Первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ определяет тип выполняемого "подведения итогов". Значение 3 в качестве первого аргумента определяет, что, в данном случае, подведение итогов аналогично использованию функции Excel СЧЕТЗ.

Имейте в виду, что аргумент, определяющий диапазон действия функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, должен начинаться с первой строки и заканчиваться последней строкой списка.

Данная формула должна вводиться в строку, расположенную выше или ниже самого списка. В противном случае, прифильтрации списка строка, содержащая формулу, будет скрыта. Кроме того, имейте в виду, что в итог, возвращаемый функцией ПРОМЕЖУТОЧНЫЕ .итоги, пустыеячейки не включаются.

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

ПРОМЕЖУТОЧНЫЕ .ИТОГИ (9 ; С5 : С4 0 0)

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

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

Функция ПРОМЕЖУТОЧНЫЕ.итоги используется для нумерации строк отфильтрованного списка. Нумерация строк корректируется каждый раз, когда вы используете фильтр, скрывая или отображая необходимые строки списка. Например, если подписи полей списка содержатся в строке 1, введите следующую формулу в ячейку А2, а затем скопируйте ее в каждую строку столбца А:

ПРОМЕЖУТОЧНЫЕ . ИТОГИ ( 3 ; В $ 2 : В2 )

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

Копирование иудаление отфильтрованныхданных

Некоторые стандартные операции, которые выполняются при работе с обычными электронными таблицами, с отфильтрованным списком работают несколько по-другому.

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

То же самое происходит при удалении строк отфильтрованного списка. Excel удаляет только видимые

Рис. 9.4. Формулы вячейках С1 и С2 исполь- строки выбранного диапазона. Строки, скрытые с по- зуют функцию ПРОМЕЖУТОЧНЫЕ .ИТОГИ мощью автофильтра, в данном случае, игнорируются.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Функция ПРОМЕЖУТОЧНЫЕ .итоги достаточно универсальна. Ее уникальность состоит в том, что это единственная функция приложения Excel, которая игнорирует ячейки, находящиеся в скрытых строках. Тем не менее, и она имеет одно "но": строки должны быть скрыты с помощью средства Автофильтр или Схема списка. Обычное скрытие строк вручную никоим образом не отразится на результатах, возвращаемых функцией.

Первый аргумент функции ПРОМЕЖУТОЧНЫЕ .итоги определяет, какая из функций фактически будет использована. Например, если в качестве первого аргумента используется значение 1 — действие функции ПРОМЕЖУТОЧНЫЕ.итоги будет аналогично функции СРЗНАЧ.

Ниже приведена таблица функций, соответствующих первому аргументу функции

ПРОМЕЖУТОЧНЫЕ .итоги:

Использование расширенного фильтра

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

•Использование комплексных условий фильтрации.

•Использование вычисляемых условий фильтрации.

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

Заполнение промежутков

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

Хорошо, если список невелик. В этом случае отсутствующие данные в пустые ячейки можно ввести вручную. Однако, что делать, если размер базы данных огромен? Тогда используйте следующий, более эффективный способ заполнения пустующих ячеек:

1.Выберите диапазон ячеек (например, A3 : А14).

2.Нажмите комбинацию клавиш <Ctrl+G>, чтобы отобразить диалоговое окнд Перейти к.

3.В диалоговом окне Перейти к щелкните на кнопке Выделить.

Выберите опцию Пустые ячейки.

В строке формул введите знак равенства (=), а затем адрес первой ячейки столбца, содержащей запись (например, =АЗ), инажмите комбинацию клавиш <Ctrl+Enter>.

Снова выберитедиапазон ячеек, а затем выполните команду Правка^ Копировать.

Выберите команду Правкам Специальная вставка, опцию Значения, а затем щелкните на кнопке Ок.

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

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

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

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

Диапазон условий может помещаться в любом месте рабочего листа илидаже на от-