Задание 3: Поиск и сортировка информации в базах данных
1. Общая информация о задании
- Уровень сложности: базовый
- Максимальный балл: 1
- Время выполнения: около 3 минут
- Проверяемый навык: поиск и сортировка информации в реляционных базах данных
- Формат файла на экзамене: электронная таблица
.ods(LibreOffice Calc / OpenOffice Calc). Microsoft Excel не используется.
Ответом может быть число или слово (в том числе код вида B18, D01 и т.п.).
Задания: N3.Поиск БД
2. Таблица формулировок и что они означают
В условии задания часто встречаются типовые формулировки. Ниже приведена таблица: что требуется понять и какие действия выполнить.
| Формулировка в задании | Что это значит | Действия |
|---|---|---|
| «Определите, на сколько увеличилось количество…» | Нужно найти сумму «Поступление» минус сумма «Продажа» за указанный период | Фильтр по дате, по объекту (склад/магазин), по типу товара; отдельно сумма поступлений и продаж или разность |
| «Найдите сумму (литров, упаковок, единиц)…» | Нужно сложить значения числового поля по отобранным строкам | Фильтр по всем критериям (вид товара/топлива, организация, район, дата); в отфильтрованной таблице сумма по нужному столбцу |
| «Найдите среднее количество…» | Нужно вычислить среднее арифметическое по отобранным записям | Фильтр по критериям; функция СРЗНАЧ или ПРОМЕЖУТОЧНЫЕ.ИТОГИ; в ответе часто просят целую часть числа |
| «Определите ID (направления, ВУЗа, компании) с самым большим количеством…» | Найти одну запись с максимальным значением указанного поля | Фильтр по условию; сортировка по убыванию по этому полю; при равенстве смотреть второе условие (например, наименьший проходной балл) |
| «Определите ID с самой высокой стоимостью / наибольшим количеством единиц» | Найти запись с максимальным значением в указанном столбце | Фильтр по направлению/дате/региону и т.д.; найти максимум в столбце; записать соответствующий ID |
| «Какой минимальный проходной балл…» | Найти наименьшее значение числового поля среди отобранных записей | Фильтр по всем указанным условиям (город, год, направления); минимум по столбцу «Проходной балл» |
| «В какую организацию было поставлено наибольшее количество единиц товаров» | Нужно для каждой организации посчитать сумму единиц (упаковки × кол-во в упаковке), затем найти максимум | ВПР (или аналог) для подтягивания «Количество в упаковке» по артикулу; новый столбец = упаковки × единицы в упаковке; по каждой организации сумма; выбрать организацию с максимальной суммой |
| «Сколько всего было отгружено упаковок в компании (региона)…» | Сумма по числовому полю (упаковки, литры и т.д.) при отборе по региону/организации | Фильтр по региону в таблице организаций это получить список ID; фильтр в таблице операций по этим ID; сумма по столбцу «Количество упаковок» |
| «Определите год рождения (прабабушки и т.п.)…» | Найти запись по текстовым признакам (ФИО, город), вернуть значение поля «Год рождения» | Работа по связям таблиц (родитель–ребёнок); фильтр по имени и городу; выписать значение года |
| «За период с … по … включительно» | В отбор входят обе граничные даты | В фильтре по дате указать условие «больше или равно» начальной и «меньше или равно» конечной (или отбор по диапазону дат) |
| «Определите задолженность» | Нужно найти сумму «Начисление» минус сумма «Оплата» за указанный период | Фильтр по дате, по объекту, по типу; отдельно сумма начислений и оплат |
Кратко:
- «Сумма» → фильтр + сумма по столбцу.
- «Среднее» → фильтр + СРЗНАЧ или промежуточные итоги; часто ответ это целая часть.
- «Наибольший / максимальный» → фильтр + сортировка по убыванию или поиск максимума.
- «Наименьший / минимальный» → фильтр + минимум.
- «На сколько увеличилось» → разность сумм (поступление − продажа и т.п.) за период.
- «Связь между таблицами» → сначала в справочной таблице по смыслу найти нужные ID, затем эти ID использовать в фильтре основной таблицы.
3. Типовая структура базы данных в задании
Задание обычно содержит три таблицы на разных листах одного файла .ods.
Таблица 1 (операции, движения):
- ID операции, Дата, ID объекта (склад/магазин/АЗС/ВУЗ), Артикул (или ID товара/направления), тип операции (Поступление/Продажа и т.п.), количество (упаковок, литров и т.д.), при необходимости цена.
Таблица 2 (справочник объектов):
- ID объекта, наименование, район/город/регион, адрес и т.п.
Таблица 3 (справочник товаров/направлений):
- Артикул (или ID), наименование, единица измерения, количество в упаковке, производитель/отдел и т.п.
Связи:
- В таблице операций поля «ID объекта» и «Артикул» (или аналоги) связывают её со справочниками. По ним выполняют отбор: сначала в справочниках находят нужные ID по смыслу (район, вид товара, год), затем применяют эти ID в фильтрах к таблице операций.
4. Алгоритм решения
- Прочитать условие и выделить все критерии отбора (дата, район, вид товара, тип операции и т.д.).
- Определить, в какой таблице каждое условие проверяется (дата и количество это в таблице операций; район, название магазина это в таблице магазинов; наименование товара это в таблице товаров).
- В справочных таблицах применить фильтры и выписать нужные ID (например, артикулы чая, ID магазинов Заречного района).
- В таблице операций (движений) задать фильтры по дате, по полученным ID и при необходимости по типу операции.
- По отфильтрованным строкам выполнить требуемое действие: сумма, среднее, минимум, максимум или выбор одной записи по условию.
- Записать ответ в формате, указанном в задании (только число или только слово/код).
5. Инструменты в LibreOffice Calc
5.1. Фильтр (автофильтр)
- Данные → Фильтр → Автофильтр (или кнопка на панели).
- В заголовках столбцов появляются списки: выбор значения или «Условие» для сравнений (равно, больше, меньше, между датами и т.д.).
- Можно задать несколько условий в разных столбцах одновременно (логика «И» по строке).
5.2. Функция ВПР (VLOOKUP)
Подтягивает значение из другой таблицы по ключу (например, по артикулу это количество единиц в упаковке).
Синтаксис в Calc:
=ВПР(Искомое_значение; Таблица; Номер_столбца; 0)
- Искомое_значение это ячейка с артикулом (или другим ключом) в текущей таблице.
- Таблица это диапазон справочной таблицы, в котором первый столбец это ключ (артикул).
- Номер_столбца это номер столбца в этом диапазоне, откуда брать значение (например, 4 для «Количество в упаковке»).
- 0 (или ЛОЖЬ) это точное совпадение.
Если в справочнике артикул не в первом столбце, диапазон нужно выбрать так, чтобы столбец с артикулом был первым в указанном диапазоне.
5.3. Функция ГПР (HLOOKUP)
Горизонтальный просмотр: поиск по первой строке диапазона, возврат значения из указанной строки. Используется реже, чем ВПР.
5.4. Полезные функции
- СУММ это сумма по диапазону (в т.ч. по видимым после фильтра строкам).
- СРЗНАЧ это среднее значение.
- ПРОМЕЖУТОЧНЫЕ.ИТОГИ это при включённом фильтре считает сумму/среднее только по видимым строкам.
- МИН / МАКС это минимум и максимум по диапазону.
6. Типичные ошибки
- Не учесть обе границы периода («с 1 по 10 включительно» это обе даты входят).
- Перепутать таблицы: искать район в таблице операций вместо таблицы магазинов.
- При «среднем» записать дробное число, хотя в задании просят целую часть.
- При нескольких ID (несколько магазинов района, несколько артикулов чая) применить в фильтре только один из них.
- В ВПР указать диапазон, где ключевое поле (артикул) не в первом столбце, и не скорректировать диапазон.
7. Видеоматериал и ресурсы
- Теория и прототипы: egeturbo.ru это задание 3, ctege.info это задание 3
- Работа с данными в LibreOffice Calc: future-step.ru
8. Краткий чек-лист на экзамене
- Определить все условия из формулировки.
- Найти нужные ID в справочных таблицах (фильтр).
- Применить все критерии к таблице операций.
- Выполнить действие: сумма / среднее / мин / макс / одна запись.
- Записать ответ в требуемом формате (число или слово).