Презентация к уроку по теме: Функции из раздела Работа с базами данных

Электронные таблицы Excel и LibreOffice Calc позволяют работать с данными как с простейшими базами данных. Для этого предусмотрены специальные функции, которые обрабатывают структурированные таблицы с заголовками столбцов.
В лекции разберём функции категории «Работа с базой данных», их синтаксис, особенности и примеры применения.

Функции для работы с базами данных имеют единый формат и требуют строгой структуры данных:
Требования к таблице‑базе данных:
первая строка — заголовки столбцов (имена полей);
все последующие строки — записи (данные);
нет пустых строк или столбцов внутри диапазона;
данные в каждом столбце должны быть одного типа (текст, числа, даты).

Общий синтаксис функций:=Функция(база_данных; поле; критерии)
Где:
база_данных — диапазон всей таблицы, включая заголовки (например, A1:D100);
поле — столбец, по которому выполняется расчёт. Можно указать:
текстом в кавычках — название столбца ("Зарплата");
числом — порядковый номер столбца (3 для третьего столбца);
критерии — диапазон ячеек с условиями отбора. Должен содержать:
хотя бы одну ячейку с заголовком столбца (как в базе данных);
под заголовком — условие отбора (например, "=Москва" или ">1000").

1. БДСУММ (DSUM) — суммирует значения в указанном столбце, удовлетворяющие заданным условиям.
Пример: сумма зарплат сотрудников отдела «Бухгалтерия»:
База данных: A1:C100 (столбцы: «ФИО», «Отдел», «Зарплата»).
Поле: "Зарплата" или 3.
Критерии: диапазон E1:E2, где E1 = "Отдел", E2 = "Бухгалтерия".
Формула: =БДСУММ(A1:C100; "Зарплата"; E1:E2).

2. ДСРЗНАЧ (DAVERAGE) — вычисляет среднее значение в столбце для записей, соответствующих условиям.
Пример: средняя зарплата мужчин в компании:
Критерии: G1:G2, где G1 = "Пол", G2 = "Мужской".
Формула: =ДСРЗНАЧ(A1:C100; "Зарплата"; G1:G2).

3. БСЧЁТ (DCOUNT) — подсчитывает количество числовых записей в столбце, удовлетворяющих условиям.
Пример: количество сотрудников в отделе «Продажи»:
Формула: =БСЧЁТ(A1:C100; "Зарплата"; H1:H2), где H1 = "Отдел", H2 = "Продажи".

4. БСЧЁТА (DCOUNTA) — подсчитывает все непустые ячейки (текст и числа) в столбце по условиям.
Пример: сколько сотрудников имеют заполненное поле «Должность»:
Формула: =БСЧЁТА(A1:C100; "Должность"; I1:I2), если есть соответствующий столбец.

5. ДМИН (DMIN) / ДМАКС (DMAX) — находят минимальное/максимальное значение в столбце по критериям.
Пример: минимальная зарплата в отделе «IT»:
Формула: =ДМИН(A1:C100; "Зарплата"; J1:J2), где J1 = "Отдел", J2 = "IT".

6. БИЗВЛЕЧЬ (DGET) — извлекает одно значение, соответствующее условиям. Если найдено несколько записей — возвращает ошибку #ЧИСЛО!.
Пример: извлечь фамилию сотрудника с ID=105:
Формула: =БИЗВЛЕЧЬ(A1:C100; "Фамилия"; K1:K2), где K1 = "ID", K2 = 105.
7. БДПРОИЗВЕД (DPRODUCT) — перемножает значения в столбце по условиям.

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

Примеры условий:
точное совпадение: "=Иванов" или просто Иванов;
больше/меньше: ">5000", "<10.01.2024";
частичный текст: "*ов" (фамилии, оканчивающиеся на «ов»);
несколько условий в одной строке — И (например, «Отдел=Бухгалтерия» И «Зарплата>40000»);
несколько строк под заголовками — ИЛИ (например, «Отдел=IT» ИЛИ «Отдел=Продажи»).

Пример диапазона критериев для поиска сотрудников:



Это найдёт:
всех из отдела «Продажи» (любая зарплата);
сотрудников из «IT» с зарплатой выше 50 000 руб.

Задача 1. Найти среднюю оценку студентов факультета «Экономика», сдававших экзамен после 01.09.2023.
База данных: A1:D200 («ФИО», «Факультет», «Дата экзамена», «Оценка»).
Формула: =ДСРЗНАЧ(A1:D200; "Оценка"; F1:G2), где:
F1 = "Факультет", F2 = "Экономика";
G1 = "Дата экзамена", G2 = ">01.09.2023".

Задача 2. Подсчитать количество заказов от клиента «ООО Вектор» стоимостью выше 10 000 руб.
База данных: A1:E150 («Номер», «Клиент», «Дата», «Сумма», «Статус»).
Формула: =БСЧЁТ(A1:E150; "Сумма"; H1:I2), где:
H1 = "Клиент", H2 = "ООО Вектор";
I1 = "Сумма", I2 = ">10000".

Задача 3. Найти максимальную сумму заказа в статусе «Выполнен» за январь 2024 года.
Формула: =ДМАКС(A1:E150; "Сумма"; J1:K3), где:
J1 = "Статус", J2 = "Выполнен";
K1 = "Дата", K2 = ">=01.01.2024", K3 = "<=31.01.2024".

Excel: функции полностью поддерживаются, есть автодополнение формул.
LibreOffice Calc: синтаксис идентичен, но:
иногда требуется явное указание кавычек в критериях ("=Москва" вместо Москва);
для сложных условий лучше использовать Расширенный фильтр перед применением функций.

Плюсы функций БД:
работают с множеством условий без вложенных ЕСЛИ;
условия задаются визуально (в диапазоне), а не внутри формулы;
легко менять критерии без редактирования формулы;
подходят для динамических отчётов.
Минусы:
требуют правильной структуры базы данных;
менее интуитивны для простых задач.
Когда что использовать:
для 1–2 условий — СУММЕСЛИ, СРЗНАЧЕСЛИ;
для сложных фильтров с несколькими столбцами — БДСУММ, ДСРЗНАЧ.


Функции работы с базой данных — мощный инструмент для анализа структурированных данных в Excel и Calc. Они позволяют:
быстро получать сводные показатели по сложным критериям;
автоматизировать создание отчётов;
избегать громоздких формул с вложенными условиями;
гибко менять условия отбора.

Контрольные вопросы:

Какие требования предъявляются к структуре таблицы, чтобы её можно было использовать как базу данных для функций категории «Работа с базой данных»?
Что означают три обязательных аргумента в синтаксисе функций работы с базой данных: база_данных, поле и критерии? Приведите пример для каждого.
В чём разница между функциями БСЧЁТ и БСЧЁТА? Для каких задач подходит каждая из них?
Какая функция вернёт ошибку #ЧИСЛО!, если по заданным критериям найдено несколько записей? Почему это происходит?
Зачем нужен диапазон критериев? Опишите пошагово, как его создать.
Как в условиях отбора задать:
точное совпадение значения;
значение больше определённого числа;
текст, оканчивающийся на определённые символы?
В чём ключевое отличие функций работы с базами данных от обычных функций типа СУММЕСЛИ и СРЗНАЧЕСЛИ?