Список с выбором значения

Чтобы предоставить пользователю выпадающий список допустимых значений, его нужно сначала записать, лучше всего – на отдельном листе.

Это может быть общеизвестный список (например, дни недели, список регионов России, налоговые ставки) или что-то из ваших вспомогательных таблиц.

Например, создадим список для выбора месяца. На отдельном листе перечислим все месяцы по порядку:

Для удобства зададим имя диапазона, отметив, что месяцы записаны на русском языке в виде трех букв.

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

В результате при выделении любой ячейки в столбце появляется список:

Проверка типа данных

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

Все функции собраны в группу «Проверка свойств и значений», начинаются с буквы Е (сокращение от «если») и возвращают ИСТИНА/ЛОЖЬ.

Чтобы проверить, является ли число целым, надо округлить его и сравнить результат с исходным числом: если совпадает, значит оно целое.

Если вы задаете условие для диапазона ячеек, то формулу следует писать для первой выделенной ячейки – на остальные проверка распространится автоматически.

Например, контроль цены в столбце E: это число, не больше 2 знаков после запятой, строго больше 0. Нужно выделить весь столбец E и ввести формулу для ячейки E1.

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

Проверка уникальности значения в столбце

Если значения в столбце должны быть строго уникальными (не должны повторяться), то проверку можно реализовать с помощью функции СЧЁТЕСЛИ – она должна быть равна 1 для каждой ячейки.

 

Функции баз данных

Excel содержит группу функций, которые выполняют действия, аналогичные запросам с итогами в Access – суммирование, подсчет значений, дисперсия, минимум и максимум и др.

При этом действие применяется не ко всей таблице, а только к строкам (записям), подпадающим под условие отбора.

Критерии

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

ФУНКЦИЯ(база_данных; поле; критерий)

база_данных – ссылка на ячейки данных таблицы, включая заголовок (лучше всего использовать именованный диапазон).

поле – в данном аргументе можно записать как текст с точным названием столбца ("Месяц", "Цена" или "Количество"), так и число, задающее порядковый номер столбца в таблице.

критерий – ссылка на диапазон ячеек с условиями отбора (A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

Критерий записывается аналогично условиям расширенного фильтра.

Функции

Большинство этих функций называется так же, как и обычные (СУММ, СЧЁТ и т.п.), но с приставкой Д или Б.

В некоторых случаях функции баз данных можно заменить на СУММЕСЛИ, СЧЁТЕСЛИ и т.п., но только для простых критериев. Функции баз данных, как и расширенный фильтр, поддерживают сколь угодно сложные условия.

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

 

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

Пример 4 (продолжение)

Продолжим рассматривать пример с поставщиками товаров.

Создадим новый лист «Запросы» и разместим все результаты на нем.

Диапазон ячеек основной таблицы носит имя «Поставки_товаров». Поэтому во всех функциях в качестве базы данных будем указывать это имя.

Например, найдем суммарную стоимость поставок по выбранному пользователем поставщику.

Функция БДСУММ:

база_данных – таблица «Поставки_товаров».

поле – «Стоимость» или номер 7.

критерий – поле «Поставщик» равно введенному пользователем значению.

Для удобства прикрепим к ячейке список поставщиков из вспомогательной таблицы (через проверку данных).

Другой пример: найдем количество поставок за введенную дату.

Функция БСЧЁТ:

база_данных – таблица «Поставки_товаров».

поле – «Количество» или номер 6.

критерий – поле «Дата поставки» равно введенному пользователем значению.

Следующий пример: найдем для выбранного пользователем товара за выбранный диапазон дат среднюю, минимальную и максимальную цену, а также ее стандартное отклонение.

Функции ДСРЗНАЧ, ДМАКС, ДМИН, ДСТАНДОТКЛ:

база_данных – таблица «Поставки_товаров».

поле – «Цена» или номер 5.

критерий – поле «Товар» равно введенному пользователем значению, поле «Дата поставки» больше или равно минимальному значению и меньше или равно максимальному значению.

Простейший вариант:

Но в таком оформлении есть недостаток: если случайно удалить знаки >= и <=, то запрос перестанет работать. Также не хватает пояснения, где указывать начальную дату, а где – конечную. Нужно либо добавить проверку введенных значений с пояснением, либо вынести ввод в отдельные ячейки.

Добавим отдельные ячейки для ввода значений, а в критерии соберем его с помощью формулы СЦЕПИТЬ.

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

Строки 7 и 8, содержащие критерий в формате Excel, можно вообще скрыть от пользователя.

Последний пример: для выбранного в предыдущем примере товара извлечь из базы данных названия поставщиков с минимальной и с максимальной ценой.

Функция БИЗВЛЕЧЬ:

база_данных – таблица «Поставки_товаров».

поле – «Поставщик» или номер 3.

критерий – поле «Цена» равно найденному в предыдущем примере значению, дополнительно можно проверить название товара и диапазон дат (если часто встречаются товары с одинаковой ценой).

Добавим критерии для цены и ячейки с выводом названия поставщика:

 

Защита листа

После того, как вы довели до совершенства все настройки вашей БД, вам будет крайне неприятно, если какой-то другой пользователь возьмет и все испортит. Иногда нужно не только сберечь нервы, но и иметь полную уверенность, что пользователи не изменяли формулы, которые контролируют правильность введенных ими данных, и не могли «подшаманить» (например, сходится ли бухгалтерский баланс).

Для этого все или только некоторые ячейки на листе можно защитить паролем.

Защита устанавливается для всего листа в целом, но вы можете предварительно снять ее с каких-то ячеек.

В окне формата ячеек есть вкладка «Защита», на которой определяется, можно ли редактировать данные в этой ячейке при блокировке, можно ли просматривать формулы.

Сама защита устанавливается на вкладке «Рецензирование». Заблокировать можно текущий лист или книгу в целом.

Разрешенные действия отмечаются галочками.

Для книги в целом можно запретить удалять, добавлять и перемещать листы, т.е. изменять ее структуру.

Если вы боитесь потерять или забыть пароль – оставьте поле пустым.

В любой момент защиту листа или книги можно отключить.

Общие рекомендации:

1. Блокируйте возможность изменять формулы.

2. Блокируйте все пустые ячейки за пределами таблицы.

3. Блокируйте возможность добавлять и удалять столбцы, изменять форматы ячеек.

4. Оставляйте галочку «выделение заблокированных ячеек», если:

– в заблокированных ячейках есть данные, которые может потребоваться скопировать;

– разрешено добавлять строки/столбцы – это можно сделать, только выделив их целиком.

5. Выделяйте заблокированные (или наоборот – незаблокированные) ячейки отдельным цветом заливки. Например, сделайте зелеными все редактируемые ячейки. а остальные залейте белым.

6. На свободном месте можно оставить пояснения – что можно, а что нельзя делать на листе, что каким цветом выделено.

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

 

Задание

Варианты заданий представлены ниже.