Усреднение и извлечение с DAVERAGE и DGET

В этом уроке рассматриваются две последние функции для работы с базами данных:

• DAVERAGE — среднее значение числового поля для совпавших записей.
• DGET — извлекает единственное значение из строки, соответствующей критериям.

Обе функции используют уже знакомый вам шаблон (database, field, criteria), поэтому вы в основном изучаете возвращаемые ими результаты, а не новый синтаксис.

DAVERAGE(database, field, criteria) вычисляет среднее значение столбца поля по строкам, соответствующим диапазону критериев.

• База данных — полная таблица с заголовками.
• Поле — числовой столбец, для которого вычисляется среднее.
• Критерии — блок правил сопоставления.

Это аналог AVERAGEIFS, но условия берутся из ячеек.

Для данных о продажах в A1:C13 с заголовками Region, Rep, Amount поместите Region в E1, а East — в E2.

Формула возвращает среднее значение сумм для строк восточного региона. Если суммы заказов равны 1200, 800 и 1000, среднее составляет 1000. Измените E2 на West — и среднее обновится автоматически.

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

Условия в одной строке означают AND, поэтому функция вычисляет среднее значение сумм заказов восточного региона на сумму свыше 1000. Пустые поля игнорируются — в среднем учитываются только числовые значения.

В отличие от DSUM, которая возвращает 0, DAVERAGE возвращает ошибку #DIV/0!, если ни одна строка не соответствует критериям: среднее для нулевого количества значений вычислить нельзя.

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

DGET устроена иначе: она возвращает единственное значение из поля единственной строки, соответствующей критериям.

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

Предположим, в таблице продавцы не повторяются и нужно получить сумму для продавца Сары. Поместите Rep в E1, а Sara — в E2.

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

DGET требует, чтобы совпадала ровно одна строка:

• Если ни одна строка не соответствует критериям, функция возвращает #VALUE!.
• Если критериям соответствует более одной строки, функция возвращает #NUM!.

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

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

Логика AND сужает результаты до одной строки, и DGET возвращает её сумму. Так DGET выполняет надёжный поиск по нескольким ключам.

Поскольку DGET выдаёт ошибку при отсутствии совпадений или при нескольких совпадениях, оберните её для более удобной работы. IFERROR превращает любую из этих ошибок в понятное сообщение.

Если нужно отдельно обозначать дубликат и отсутствие совпадения, сначала проверьте количество с помощью DCOUNTA и в зависимости от результата выберите соответствующее сообщение.

Краткое руководство по выбору подходящего инструмента:

• Нужен итог для совпавших строк? Используйте DSUM.
• Нужно количество совпавших строк? Используйте DCOUNT или DCOUNTA.
• Нужно среднее для совпавших строк? Используйте DAVERAGE.
• Нужно одно значение из единственной совпавшей строки? Используйте DGET.

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

Вы завершили изучение семейства D-функций:

• DAVERAGE вычисляет среднее числового поля для совпавших строк; при отсутствии совпадений возвращает #DIV/0!.
• DGET возвращает одно значение из единственной совпавшей строки; при отсутствии совпадений возвращает #VALUE!, а при нескольких совпадениях — #NUM!.
• Обе функции используют шаблон (database, field, criteria) и правила AND/OR для диапазонов критериев.
• Оберните их в IFERROR, чтобы получить аккуратный профессиональный результат.

Вместе с DSUM и DCOUNT вы теперь можете создавать полноценные отчёты на основе критериев непосредственно из структурированной таблицы.