Функции баз данных в Excel и Google Таблицах
Функции БД - мощный инструмент. Они есть и в Excel и в Google Таблицах и хороши для работы с несколькими условиями, с наборами условий.
Если вы пользовались расширенным фильтром (тоже полезная вещь) в Excel, то вам должен быть знаком формат записи условий для этих функций.
Это таблица в ячейках рабочего листа, представляющая собой исходную таблицу с данными в миниатюре - названия тех полей (столбцов), на которые накладываются условия. Одна строка под этими заголовками - это один набор условий. Если строк более одной - значит, будут подсчитываться все варианты.
Эти функции удобны, когда нужно проанализировать данные по большому списку филиалов/фамилий/рекламных кампаний и любых других признаков. Чтобы не использовать много функций типа СУММЕСЛИ, а сразу получить результат.
Синтаксис функций на примере ДСРЗНАЧ / DAVERAGE (расчет среднего):
ДСРЗНАЧ(данные; столбец; критерии)
данные = исходная таблица с заголовками
столбец = ссылка на заголовок или текст с заголовком того столбца, по которому нужно вести расчет (суммировать/усреднять/извлекать значение)
критерии = ссылка на таблицу с условиями
Критерии записываются как в обычных функциях подсчета и суммирования - в виде точного текстового значения или числа; со знаками <>=; со звездочкой, если нужно искать вхождение определенных символов, а не точный текст; можно ставить условия на даты. Если оставить ячейку пустой - то в соответствующем поле может быть любое значение.
Вот такая таблица критериев позволит нам агрегировать данные по трем значениям в столбце "Канал". Тут три варианта для одного поля (столбца) исходной таблицы. То есть несколько строк в критериях функций баз данных - это ИЛИ, логическое сложение. Мы агрегируем данные по Яндекс Директу или Google Adwords или VK в столбце, в заголовке которого написано "Канал":
В следующем примере у нас появляется еще один столбец с критериями. То есть условия накладываются на два поля - "Канал" и "Название". Несколько столбцов в критериях - это И, логическое умножение. То есть нас интересуют строки исходной таблицы, в которых в столбце "Канал" - Google Adwords И в столбце "Название" GA Поиск. Ну а строки - это ИЛИ, как мы обсуждали ранее. То есть можно продолжить и полностью сформулировать текстом критерии из этой таблицы следующим образом:
("Канал" = "Facebook" И любое значение в "Названии") ИЛИ ("Канал" = "Google Adwords" И "Название" = "GA Поиск")
Обратите внимание, что в этом примере в одной из строк в "Названии" пусто. Так можно делать, если вы хотите в одном из случаев накладывать условия не на все столбцы. Если ячейка пуста - значит, для этой комбинации по конкретному столбцу будут рассматриваться все значения.
В следующем примере мы ведем расчет по тем строкам, в которых в столбце "Название" встречается текст "ЯД" или "GA" (в любом месте текстовой строки - в начале, конце или середине), а показы и клики находятся на уровне более 50 штук.
Типы функций БД:
- БДСУММ / DSUM - суммирование
- ДСРЗНАЧ / DAVERAGE - среднее арифметическое
- БСЧЁТ / DCOUNT - подсчет чисел
- БСЧЁТА / DCOUNTA - подсчет значений любого типа
- БИЗВЛЕЧЬ / DGET - извлечение одного значения (только одного, если критериям соответствует больше значений, будет возвращаться ошибка)
- ДМИН, ДМАКС / DMIN, DMAX - минимум и максимум
- БДПРОИЗВЕД / DPRODUCT - произведение
- БДДИСП / DVAR - дисперсия по выборке
- БДДИСПП / DVARP - дисперсия по генеральной совокупности
Примеры
В таблице по ссылке собраны примеры, рассматриваемые в этой статье. Они помогут вам разобраться с функциями баз данных. Создавайте копию файла, чтобы поработать с функциями и попробовать менять критерии.