Чтобы предоставить пользователю выпадающий список допустимых значений, его нужно сначала записать, лучше всего – на отдельном листе.
Это может быть общеизвестный список (например, дни недели, список регионов России, налоговые ставки) или что-то из ваших вспомогательных таблиц.
Например, создадим список для выбора месяца. На отдельном листе перечислим все месяцы по порядку:
Для удобства зададим имя диапазона, отметив, что месяцы записаны на русском языке в виде трех букв.
В основной таблице выделяем столбец, в который будут вводиться месяцы, и задаем для него правило. Обратите внимание, в начале ставится символ =, иначе в выпадающем списке отобразится написанный текст.
В результате при выделении любой ячейки в столбце появляется список:
Проверка типа данных
Если вам нужно проверить тип данных, не указывая диапазон значений, то следует использовать соответствующую формулу.
Все функции собраны в группу «Проверка свойств и значений», начинаются с буквы Е (сокращение от «если») и возвращают ИСТИНА/ЛОЖЬ.
Чтобы проверить, является ли число целым, надо округлить его и сравнить результат с исходным числом: если совпадает, значит оно целое.
Если вы задаете условие для диапазона ячеек, то формулу следует писать для первой выделенной ячейки – на остальные проверка распространится автоматически.
Например, контроль цены в столбце 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. Вы можете полностью скрыть содержимое ячейки от пользователя, если заблокируете ее и сделаете одинаковый цвет шрифта и заливки.
Задание
Варианты заданий представлены ниже.