Настройка диапазона условий

В Excel есть семейство функций базы данных, названия которых начинаются с буквы D: DSUM, DCOUNT, DAVERAGE, DGET и другие.

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

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

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

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

Таким образом, форма всегда выглядит так: DSUM(database, field, criteria). Именно с аргументом criteria начинающие чаще всего ошибаются, поэтому сначала сосредоточимся на нём.

Диапазон условий — это небольшой блок ячеек, содержащий как минимум две строки:

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

Представьте данные о продажах с заголовками Region, Rep, Amount. Чтобы выбрать только регион East, диапазон условий будет состоять из двух расположенных друг под другом ячеек: сверху — Region, снизу — East.

Заголовок в диапазоне условий должен в точности совпадать с написанием заголовка базы данных. Если столбец данных называется Amount, а в условии указано Amounts или amt, D-функция не найдёт столбец и может вернуть ошибку или ноль.

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

Предположим, в диапазоне A1:C13 находится таблица с заголовками Region, Rep, Amount. В ячейку E1 введите Region, а в E2 — East. Этот блок из двух ячеек, E1:E2, и есть диапазон условий.

Теперь DSUM суммирует только строки для East. Функция читает заголовок в E1, видит его совпадение со столбцом Region, а затем оставляет только строки, где Region равно East.

По умолчанию текстовое условие вроде East совпадает со значениями, которые начинаются с этого текста. Поэтому East также совпадёт с Eastern.

Чтобы потребовать точного совпадения, используйте сравнение в форме формулы: введите ="=East" в ячейку условия. Можно также использовать подстановочные знаки: E* совпадает с любой строкой, начинающейся с E, а ?at — с Cat, Bat или Hat.

Условия не ограничиваются текстом. Для чисел можно использовать операторы сравнения:

• >1000 выбирает суммы больше 1000.
• <=50 выбирает значения 50 или меньше.
• <>0 выбирает всё, что не равно нулю.

Поместите сверху заголовок, например Amount, а под ним — текст сравнения. D-функция проверяет значение каждой записи по этому правилу.

Условия, расположенные рядом в одной строке, объединяются с помощью AND — должны выполняться все условия.

Чтобы выбрать регион East с Amount больше 1000, создайте диапазон условий из двух столбцов: в верхней строке разместите заголовки Region и Amount, а в строке ниже — East и >1000. Запись проходит отбор только в том случае, если она относится к East и превышает 1000.

Условия, расположенные в отдельных строках, объединяются с помощью OR — подходит любая совпадающая строка.

Чтобы выбрать East или West, поместите заголовок Region сверху, затем в следующей строке укажите East, а строкой ниже — West. Теперь диапазон условий занимает три строки, и запись проходит отбор, если совпадает с любым из этих значений.

Не забудьте включить все эти строки в аргумент criteria.

Можно объединять оба варианта. Предположим, Вам нужно условие (East AND >1000) OR (West AND >500).

Используйте два столбца: Region и Amount. В одной строке укажите East и >1000, а в следующей — West и >500. Каждая строка представляет группу AND, а отдельные строки действуют как OR. Именно с помощью такой сетки D-функции выражают сложную логику без вложения функций.

Остерегайтесь следующих ошибок:

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

Храните диапазон условий в отдельной свободной области листа.

Как в диапазоне условий D-функции объединяются два условия, записанные в одной строке?

Диапазон условий — основа каждой D-функции. Главное:

• Он должен содержать строку заголовков, названия в которой в точности совпадают с названиями в таблице.
• Условия записываются в расположенных ниже строках — это может быть текст, подстановочные знаки или сравнения вроде >1000.
• Одна строка = AND, отдельные строки = OR.
• Избегайте пустых строк условий: они соответствуют всему.

Подготовив чистый диапазон условий, Вы сможете передать его функциям DSUM, DCOUNT, DAVERAGE и DGET в следующих уроках.